MySQL查询
#三表联合查询:查询患者信息:患者表,关联详情表,关联家庭表
select p.*,d.*,f.* from patient p
join patient_detail d on d.id = p.patient_detail_id
join patient_family f on f.id = p.patient_family_id
where p.id = #{id}
查询总览:单表、多表
#单表查询(六子句)
select(结果集)..from(指定表)..where(条件)..group by(分组)..having(再过滤)..order by(排序)..limit(分页)..
#多表查询
嵌套查询:非关联子查询、关联子查询
组合查询:union 使用前提:两个结果集必须有相同结构(列的个数、列顺序、列类型相同)。
关联查询:join 内连接、外连接
结果集书写:select 字段
#结果集书写:select 字段
通配符*、字段名、别名、限定名、算术运算符、关键字、函数、不区分大小写
select * //通配符*,表示所有字段结果
select 字段名 //单个字段结果
select 字段名,字段名 //多个字段结果,使用逗号分隔
select salary*12 年薪 //字段可以起别名,结果集可以计算,可以使用函数
select distinct 字段名,字段名 //结果集可以去重
select id,concat(name,ifnull(position,'No Position')) message //字段可以使用函数
select round(123.456)
select truncate(123.456,2)
select concat('(',ltrim(' abc '),')')
select upper('abc')
select ename,length(ename)
select ename,substring(ename,2,2)
select now()
select date(now())
select time(now())
select adddate('2021-05-13',interval -3 day) //interval间隔
select date_add('2021-05-13',interval -3 day) //效果同上
select adddate('2021-05-13',3) //3默认表示'天'
select date_format(now(),'%X-%m-%d %H:%i:%s')
select count(*)
select count(*),sum(salary),avg(ifnull(salary,0)) avg_salary
select max(salary),min(salary)
#运算符:+ - * /
#函数:
空值处理函数:ifnull(e1,e2):如果e1为空则取e2代替
连接:concat('a','b',...):连接字符串,连接字段名
concat(ename,ifnull(position,'No Position'))
数字函数:
round(数字,位数):用于对数字进行四舍五入处理保留小数点后指定位数
truncate(数字,位数):用于对数字进行截取到指定小数位
abs(数字):返回绝对值
rand():返回0-1随机数
sqrt(数字):返回数据的平方根
pow(x,y):返回x的y次方
mod(x,y):返回x除以y的余数
去除空格函数:trim(字符串):字符串中左右空格,left->左空格 right->右空格
文本处理函数:
upper(小写字符串):转换成大写
lower(大写字符串):转换成小写
length(字符串):返回字符串字节(编码utf-8)长度
substring(字符串,起始位置,最大字符数量):返回指定字符串内容
日期、时间函数:
now():返回当前系统时间
date(时间):返回日期部分
time(时间):返回时间部分
adddate(时间,范围):增加一个日期day\week\month\year
date_format(时间,格式):格式化日期时间
组函数:多行数据处理后返回一个结果 注意:组函数中使用列名默认都是会忽略空值
count(字段或*):返回记录数,count(*)不忽略空值,count(字段)忽略空值
sum(字段):求和
avg(字段):求平均,avg(ifnull(salary,0)
max(字段):求最大值
min(字段):求最小值
#关键字:
去重distinct
#别名:
#限定名:表别名.字段名
条件书写:where 字段 条件
#条件书写(记录过滤):where 字段 条件
操作符、两者之间、满足之一、模糊、空值、否定
where name='张三';
where salary>=5000 and salary<=10000;
where salary between 5000 and 10000;
where position='Manager' or position='Analyst';
where position in('Manager','Analyst',null); //使用in时,列表项中有空值对结果没有影响
where ename like '%张%'; //模糊查询
where position like '_a%'; //模糊查询
where bonus is null; //没有奖金
where bonus is not null; //有奖金
where salary not between 5000 and 10000;//薪水不在5000到10000之间
where deptno!=20 and deptno!=30; //不是20号部门和30号部门
where deptno not in(20,30); //不是20号部门和30号部门,列表项中有空值有影响
#操作符
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
!=或<> 不等于
and 并且
or 或者
#介于两者之间
between 低值 and 高值 肯定形式:[低值,高值]
#满足之一
in(列表项):判断等于列表项中任意一项,即满足一个即可
#模糊查询
like 占位符:_表示1个字符,%表示0到多个字符
#空值判断
测试空值时,肯定使用is null,否定使用is not null
#否定 not
空值null
a.空值参与算术运算结果为空
b.空值参与连接操作结果为空
c.任何数据类型都可以取空值(insert)
排序书写:order by 字段 规则
#排序书写:order by 字段 规则(升序asc|降序desc)
order by salary asc;//升序(小到大):薪水从低到高进行排序
order by deptno asc,salary desc;//字段名 按照部门号升序,同一个部门按照薪水降序查询信息
order by d asc,s desc; //列别名
order by 1 asc,2 desc; //数字
order by convert(ename using gbk); //按照首个字母排序
分组书写:group by 字段名
#分组书写:group by 字段名
#查询每个部门的最高薪水和最低薪水,要求没有部门的不算在内
select deptno,max(salary),min(salary) from emp_xu where deptno is not null
group by deptno;
#查询每个部门的薪水总和和平均薪水,要求没有部门的不算在内
select deptno,sum(salary),avg(ifnull(salary,0)) avg_salary from emp_xu where deptno is not null
group by deptno;
#按照职位分组,每个职位的最高薪水、最低薪水、人数总和,要求没有职位的不算在内
select position,max(salary),min(salary),count(*) from emp_xu where position is not null
group by position;
#select后面内容,要么被组函数包围,要么出现在group by后面的。
#group_concat:处理分组中一对多数据
select position,group_concat(ename) from emp_xu where position is not null group by position;
再过滤书写:having 字段 条件
#再过滤书写:having:对分组之后的数据再进行过滤
#查询平均薪水大于5000的部门和其平均薪水,没有部门不算在内
select deptno,avg(ifnull(salary,0)) avg_salary from emp_xu where deptno is not null
group by deptno
having avg_salary>5000;
#查询薪水总和大于20000的部门号和其薪水总和,要求没有部门的不算在内
select deptno,sum(salary) from emp_xu where deptno is not null
group by deptno
having sum(salary)>20000;
#查询哪些职位的人数超过2个人,没有职位的不算在内,计算每个职位的平均薪水并且按照平均薪水升序排列
select position,count(*),avg(ifnull(salary,0)) avg_salary from emp_xu
where position is not null
group by position
having count(*)>2
order by avg_salary asc;
#总结:基础查询(6个子句)
写法顺序:select->from->where->group by->having->order by
执行顺序:(分析)from(指定表)->where(记录过滤)->group by(分组)->having(分组后过滤)->select(结果集)->order by(排序)
指定表:from 表名
form 表A; //指定一张表
form 表A join 表B on 条件; //指定两张表
form 表A join 表B on 条件 join 表C on 条件; //指定三张表
分页:limit 下标,数量
limit 数量; //从第一条记录开始,显示指定数量
limit 每页开始下标,每页数量; //从索引开始展示,显示指定数量
#查询每页显示5条记录
select 字段 from 表 limit beginIndex,pageSize; //分页查询核心SQL
第几页:page
每页记录数:pageSize
每页开始下标:beginIndex
int beginIndex=(page-1)*pageSize;
List<Emp> findAll(int page,int pageSize); //分页展示