Mysql
表:
dept
emp
salgrade
1. 分组函数(多行处理函数)
-
多行处理函数的特点:输入多行,最终输出一行。
-
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注意:分组函数在使用的时候,必须先分组再使用,如果没有分组,默认整张表为一组
-
分组函数使用时要注意哪些?
- 分组函数自动忽略null,不需要提前对null进行处理
- 分组函数中count(*) 和 count(具体字段)有什么区别?
- count(具体字段):表示统计该字段下所有不为null的元素的总数
- count(*):统计表当中的总行数。(只要有一行数据就count++)
- 分组函数不能直接使用在where子句中
- 所有的分组函数可以组合起来一起用
2. 分组查询(重要)
2.1 group by
-
selecy ... from ... group by ...
-
将之前的关键字顺序组合,执行顺序为
select ... from ... where ... group by ... order by ...
-
为什么分组函数不能直接使用在where后面?
-
分组函数在使用的时候必须先分组
-
select sum(sal) from emp;为什么可以?
因为select 在group by之后执行
-
-
找出每个工作岗位的最高工资和?
-
思路:按照工作岗位分组,然后对工资求和
select job,sum(sal) from emp group by job;
-
-
找出每个部门的最高工资
- 思路:按照部门编号分组,求每一组的最大值
select deptno,max(sal) from emp group by deptno;
-
找出每个部门,不同工作岗位的最高工资
- 思路:两个字段联合成一个字段看
select deptnp,job,max(sal) from emp group by deptno,job;
2.2 having
使用having可以对分完组的数据再一次过滤,having不能代替where,必须和group by 联合使用
-
找出每个部门的最高薪资,要求显示最高薪资大于3000
思路1:1.找出最高薪资,按照部门编号分组,求每一组最大值 2.再找大于3k
select
deptno,max(sal)
from
emp
group by
deptno
having
max(sal) > 3000;
<--以上sql语句,执行效率比较低!-->
思路2:先将大于3k 的都找到,再分组
优化策略:优先使用where
select
deptno,max(sal)
from
emp
where
sal > 3000
group by
deptno;
-
where没办法的情况,找出每个部分平均薪资,要求显示平均薪资高于2500的
思路:1.找出部门平均薪资 2.在找高于2500
select
deptno,avg(sal)
from
emp
group by
deptno
having avg
(sal)>2500;s
2.3 select 语句总结
执行顺讯:from -> where -> group by -> having -> select -> order by
从某张表中查询数据,先经过where条件筛选出有价值的数据,对有价值的数据进行分组,再使用having继续筛选。
-
找出每个岗位的平均薪资,要求显示平均薪资大于1500,除MANAGER之外,要求按照平均薪资降序
思路:
select job,avg(sal) as avgsal from emp where job <> 'MANAGER' group by job having avg(sal) > 1500; order by avgsal desc;
3. distinct 关键字
-
把查询结果取出重复记录
注意:原表数据不会被修改,只是查询结果去重。
-
distinct只能出现在所有字段的最前方
4. 连接查询
4.1 什么是连接查询
从一张表单独查询,称为单表查询。
跨表查询,多张表联合起来查询数据,被称为连接查询。
4.2 表连接的分类方式
- 内连接:
- 等值连接
- 非等值连接
- 自连接
- 外连接:
- 左外连接(左连接)
- 右外连接(右链接)
- 全连接
4.3 怎么避免笛卡尔积现象?
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘积,这种现象被称为笛卡尔积现象。
连接时加条件,满足这个条件的记录被筛选出来就可以避免笛卡尔积现象。
-
查询每个员工所在的部门名称
select ename,dname from emp,dept where emp.deptno = dept.deptno // 最终查询结果条数为14,但是匹配次数还是56,只不过是4选1 //表起别名 select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno
4.4 内连接
4.4.1 等值连接
-
查询每个员工所在部门名称,显示员工名和部门名
// SQL92语法 select e.name,d.name from emp e,dept d where e.deptnp = d.deptno // SQL99语法 // inner 可以省略,带着inner可读性更好 select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno; // 条件是等量关系
SQL99优点:表连接的条件是独立的,链接之后,如果还需要进一步筛选,再往后继续添加where
4.4.2 非等值连接
-
找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;// 条件不是等量关系
4.4.3 自连接
- 查询员工的上级领导,要求显示员工名和领导名
技巧:一张表看成两张表,emp a 员工表,emp b 领导表
select
a.ename as '员工名', b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno;
4.5 外连接
-
找出员工的部门名
// 内连接 select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; // 内连接的特点:完成能够匹配上这个条数的数据查询出来
// 外连接(右外连接) select e.ename,d.dname from emp e right outer join // outer可以省略 dept d on e.deptno = d.deptno; // 左外连接 select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;
right:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表
在外连接当中,两张表连接,产生主次关系
内连接,两张表平等,没有主次
思考:外连接的查询结果条数一定是 大于等于 内连接的查询条数
-
查询每个员工的上级领导,要求显示所有员工的名字和领导名
select a.ename as '员工名', b.ename as '领导名' from emp a left join emp b on a.mgr = b.empnp;
内连接14条,外连接15条,有KING
4.6 多表查询
语法:
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
join
d
on
a和d的连接条件
一条SQL中内连接和外连接可以混合,都可以出现。
- 找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级
select
e.ename,e.sal,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptnp
join
salgrade s
on
e.sal between s.losal and s.hisal;
-
找出每个员工的部门名称以及工资等级还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级
select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno = d.deptnp join salgrade s on e.sal between s.losal and s.hisal left join // 必须加left外连接,内连接不显示空值,会少一条数据 emp m on e.mgr = m.empno;
5. 子查询
- 什么是子查询?
- select语句中嵌套select语句,被嵌套的称为子查询
5.1 where子句中的子查询
-
找出比最低工资高的员工姓名和工资
思路:1.查询最低工资 2.找出大于最低的 3.合并前两条语句
select ename,sal from emp where sal > (select min(sal) from emp);
5.2 from子句中的子查询
注意:from后面的子查询,可以将子查询的查询结果当做一张临时表
-
找出每个岗位的平均工资的薪资等级
思路:
1.找出平均工资(按照岗位分组求平均值)
2.把以上的查询结果当做一张真实存在的表 t,查询工资等级表 s
3.t表和s表进行表连接,条件:t表avg(sal) between s.losal and s.hisal
select
t.*,s.grade
from
(select job,avg(sal) as avgsal from emp group by job) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
5.3 select后面出现的子查询(了解)
-
找出每个员工的部门名称,要求显示员工名,部门名
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
6. union 合并查询结果集
-
查询工作岗位是MANAGER和SALESNAM的员工
select ename,job from emp where job = 'MANAGER' or 'SALESMAN'; select ename,job from emp where job in ('MANAGER','SALESMAN'); // 使用union select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN'
union的效率更高一些,对于表连接来说,没连接一次新表,则匹配的次数满足笛卡尔积,
但是union可以减少匹配的次数,在减少匹配次数的情况下,还可以完成两个结果集的拼接。
比如 :a 连接 b 连接 c
a 、b、c 都是10条记录,匹配次数是1000次
使用union,a连接b:10*10->100 b连接c:10*10->100 匹配次数200次
使用union注意事项:union在进行结果集合并的时候,要求两个结果集列数相同
7. limit
- limit是将结果集的一部分取出来,通常使用在分页查询中
7.1 limit 使用
完整语法:limit startIndex,length
startIndex :起始下标,不写默认0 length:长度
limit在order by之后执行
-
按照薪资降序,取出排名在前5名的员工
select ename,sal from emp order by sal desc limit 5;
-
取出工资排名在3-5名的员工
select ename,sal from emp order by sal desc limit 2,3; // 2 表示起始位置从下标2开始
7.2 通用分页
每页显示3条记录
第一页:limit 0,3 [0,1,2]
第二页:limit 3,3 [3,4,5]
第三页:limit 6,3 [6,7,8]
每页显示pageSize条记录
第pageNo页:limit (pageNo-1)*pageSize,pageSize
public static void main(String[] args){
// 用户提交过来一个页码,以及每页显示的记录条数
int pageNo = 5; // 第5页
int pageSize = 10; // 每页显示10条
int startIndex = (pageNo - 1) * pageSize;
String sql = "select ... limit " + startIndex + "," + pageSize;
}