DAY3
distinct
1、把查询结构去除重复记录,原表数据不会被修改
select distinct job from emp;
- distinct只能出现在所有字段的最前方
- distinct若出现在job、deptno两个字段之前,表示两个字段联合起来去重
统计工作岗位的数量?
select count(distinct job) from emp;
连接查询(重要)
1、什么是连接查询?
从一张表中单独查询,称为单表查询。
emp表和dept表联合起来查询数据,从emp表中取员工姓名,从dept表中取名字,这种跨表查询,多张表联合起来查询数据,被称为连接查询。
2、连接查询的分类
根据表连接的方式分类:
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接(左连接)
- 右外连接(右连接)
- 全连接
根据语法的年代分类:
- SQL92
- SQL99
3、当两张表进行连接查询时,没有任何条件的限制,最终查询的结果条数,是两张表条数的乘积,这种现象被称为笛卡尔积现象。
怎么避免笛卡尔积现象?
连接时加条件,满足这个条件的记录筛选出来!
查询员工姓名和部门名称?
select ename,dname from emp,dept where emp.deptno = dept.deptno;
查询效率会高一些
select emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno;
表起别名,很重要 效率问题
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92语法
思考:最终查询的结果条数减少了,匹配次数减少了吗?其实次数没有减少,只是把满足条件的结果筛选出来。
通过笛卡尔积现象,得出表的连接次数越多效率越低,尽量避免表的连接次数
4、内连接之等值连接
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92语法
SQL92语法的缺点:表连接的条件和后期进一步筛选的条件,都放在where后面,混合在一起,结构不清晰
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;//SQL99语法
SQL99 优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where。
//inner 可以省略,带着inner可读性更好,可以看出是内连接
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;//SQL99语法
等值连接是因为条件是等量关系
5、内连接之非等值连接
案例:找出每个员工的薪资等级,要求显示员工名,薪资,薪资等级?
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
条件不是一个等量关系,称为非等值连接。
6、内连接之自连接
案例:查询员工的上级领导,要求显示员工名和对应的领导名?
select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;
员工的领导编号 = 领导的员工编号
技巧:将一张表看成两份,两张表。
7、外连接
内连接的特点:完成能够匹配上这个条件的数据查询出来
select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;右外连接
right代表什么?
表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
在外连接中,两张表的连接,产生了主次关系。内连接中两张表是平等的,没有主次关系
左外连接:将right改成left
任何一个右连接都有左连接的写法,任何一个左连接都有右连接的写法。
//左右连接outer可以省略,带着可读性强
select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;右外连接
外连接的查询结果条数一定是 >= 内连接的查询结果条数。
案例:查询每个员工的上级领导,要求显示所有员工的名字和领导名
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
全连接:两张表都是主表
8、三、四张表连接
语法:
select … from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d 的连接条件
一条SQL中内连接和外连接可以混合,都可以出现
案例:找出每个员工的部门名称以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级?
select e.ename,d.dname,e.sal,s.grade,e1.ename from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp e1 on e.mgr = e1.empno;
子查询
1、什么是子查询
select语句中嵌套select语句,被嵌套的select语句称为子查询。
2、子查询可以出现在哪里呢?
- select后面
- from后面
- where后面
3、where子句中的子查询
案例:找出比最低工资高的员工姓名和工资?
实现思路:
第一步,查询最低工资是多少?select min(sal) from emp; 800
第二步,找出>800的 select ename ,sal from emp where sal>800;
第三步,合并
select ename,sal from emp where sal > (select min(sal) from emp);
4、from子句中的子查询
技巧:from后面的子查询,可以将子查询的查询结果当做一张临时的表。
案例:找出每个岗位的平均工资的薪资等级
思路:①找出每个岗位的平均工资②把上一步的查询结果看成一张真实的表t
select job,avg(sal) avgsal from emp group by job;
select t.*,s.grade from (select job,avg(sal) avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
5、select子句中的子查询(了解)
案例:找出每个员工的部门名称,要求显示员工名,部门名?
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
注意:对于select后面的子查询来说,这个子查询只能一次返回一条结果,多于一条就报错。
union合并查询结果集
案例:查询工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('MANAGER','SALESMAN');
select ename,job from emp where job ='MANAGER'
union
select ename,job from emp where job ='SALESMAN';
union的效率要高一些,对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的翻。但是union可以减少匹配的次数,在减少匹配次数的情况下,还可以完成两个结果集的拼接。
比如:a(10条记录)连接b(10条记录)连接c(10条记录)
10*10*10=1000
a连接b一个结果:100 a连接c一个结果100
使用union是:100+100=200
union把乘法变成了加法运算
union注意事项:
- union在进行结果集合并时,要求两个结果集的列数相同
- 结果集合并时,列和列的数据类型也要一致
limit的使用(重要)
limit是将查询结果集的一部分取出来,通常使用在分页查询当中。
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差,可以一页一页翻页看。
案例:按照薪资降序,取出排在前5名的员工?
select ename,sal from emp order by sal desc limit 5;
select ename,sal from emp order by sal desc limit 0,5;
limit 怎么用?
-
完整用法:limit startIndex,length。
-
startIndex是起始下标,length是长度。
-
缺省用法:limit 5;取前五。
-
起始下标从0开始
mysql中limit在order by之后执行。
取出工资排名在[3-5]名的员工?
select ename,sal from emp order by sal desc limit 2,3;
limit的使用
因为起始下标从0开始,2表示起始位置从下标2开始,就是第三条记录(0,1,2),长度为3(3,4,5)
分页
每页显示3条记录
第一页:limit(0,3)[0,1,2]
第二页:limit(3,3)[3,4,5]
第三页:limit(6,3)[6,7,8]
第四页:limit(9,3)[9,10,11]
每页显示pageSize条记录
第pageNo页: limit (pageNo - 1)*pagesize,pagesize
**DQL语句总结:**select … from …where… group by … having … order by …limit
执行顺序:
- from
- where
- group by
- having
- select
- order by
- limit