Mysql数据库（三）

连接查询（多表查询）

多张表联合起来查询数据，成为连接查询
连接查询分类：
按年代分类
SQL92：1992年的时候出现的语法
SQL99：1999年的时候出现的语法
根据表的连接方式分类：
内连接：等值连接、非等值连接、自连接
外连接：左外连接（左连接）、右外连接（右链接）
全连接：

当两张表进行连接查询，没有任何条件限制，最终查询的结果条数，是两张表的=乘积，这种现象被称为：笛卡尔积现象。

#查询每个员工的部门名称
select ename, dname from emp, dept;
#添加where条件，避免笛卡尔积现象，但查询次数没变--56次
select ename, dname from emp, dept where emp.deptno = dept.deptno;
#表起别名，字段的查询结果加表，效率提升 
//SQL92语法
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数

内连接

内连接的表无主次之分

内连接之 等值连接

SQL92：表的连接条件和筛选条件都放在where后面，使得糅杂在一起，结构不清晰。
SQL99：表的连接条件是独立的，使用 join…on…接之后，如果还要进一步筛选，再往后继续添加where语句。

#查询每个员工所在的部门名称，显示员工名和部门名？
//SQL92
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
//SQL99
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;
#内连接的inner可以省略
select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno;

内连接之 非等值连接

条件不是等量关系，称为非等值连接

#找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？
#inner可以省略
select e.ename, e.sal, s.grade  from salgrade s inner join emp e on e.sal between s.losal and s.hisal;

内连接之 自连接

技巧：一张表看做两张表

#查询员工的上级领导，要求显示员工名和对应的领导名？
#技巧：一张表看成两张表，员工一张表，领导一张表
#13条记录，没有KING，因为KING的领导为null

#员工的领导编号等于领导的员工编号
select e.ename as '员工名', ep.ename as '领导名'from emp e join emp ep on e.mgr = ep.empno;

外连接

内连接：能够匹配上的条件全部查询出来；A和B两张表没有主次关系，匹配上就查出来，匹配不上就不查询
外连接：匹配不上的也查询出来；A和B两张表有主次关系

外连接之 左（右）外连接

right代表将join关键字右边的这张表看成主表，主要是为了将这张彪的数据全部查询出来，捎带着关联查询左边的表。
left代表将join关键字左边的这张表看成主表，主要是为了将这张彪的数据全部查询出来，捎带着关联查询右边的表。

#内连接
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;

#下面两种查询结果一致
#右外连接,outer可以省略
select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;
select e.ename, d.dname from emp e right outer join dept d on e.deptno = d.deptno;
#左外连接，outer可以省略
select e.ename, d.dname from dept d left join emp e on e.deptno = d.deptno;
select e.ename, d.dname from dept d left outer join emp e on e.deptno = d.deptno;

思考

1、外连接的查询结果条数一定是 >= 内连接的查询结果条数？
答：正确

案例

#查询每个员工的上级领导，要求显示所有员工的名字和领导名？
#外连接（员工表为主表）--->有KING
select a.ename as '员工名', b.ename as '领导名' from emp a left join emp b on a.mgr = b.empno;

三张及以上的表联合查询

#语法
select ... from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d的连接条件;

#找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级
select e.ename,e.sal,d.dname,s.grade from emp e 
join dept d on e.deptno = d.deptno 
join salgrade s on e.sal between s.losal and s.hisal;

#找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级
//这就要求必须显示KING了，需要使用外连接
#错误的，因为没有外连接，员工没有KING
select e.ename as '员工名', e.sal,d.dname,s.grade, l.ename as '领导名' from emp e 
join dept d on e.deptno = d.deptno 
join salgrade s on e.sal between s.losal and s.hisal 
join emp l on e.mgr = l.empno;
#正确的，加上外连接left
select e.ename,e.sal,d.dname,s.grade 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 l on e.mgr = l.empno;
===============================================================================
#正确，增加领导名
select e.ename as '员工名', e.sal,d.dname,s.grade, l.ename as '领导名' 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 l on e.mgr = l.empno;

子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询
select … (select)… from …(select)… where …(select)…;

where后面子查询

#找出比最低工资高的员工姓名和工资？
select ename, sal from emp where sal > (select min(sal) from emp);

#一个错误例子xxxx
select ename,sal from emp where sal > min(sal); //错误查询
# ERROR 1111 (HY000): Invalid use of group function
# where字句中不能啊直接使用分组函数

from后面子查询

from后面的子查询，可以将子查询的查询结果当做一张临时表。

#找出每个岗位的平均薪资的薪资等级？
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;

select后面子查询

select后面的子查询，该子查询只能一次返回一条结果，否则报错。

#找出每个员工的部门名称，要求显示员工名，部门名？
select e.ename, e.deptno, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;


#错误示例
select e.ename, e.deptno, (select dname from dept) as dname from emp e;
#报错 ERROR 1242 (21000): Subquery returns more than 1 row

union合并查询结果集

union的效率高一些。
对表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的增长。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

例子：
a表 连接 b表 连接 c表
a 10条记录
b 10条记录
c 10条记录
匹配次数：1000

使用union连接
a连接 b一个结果： 10 * 10 --> 100次
a连接 c一个结果： 10 * 10 --> 100次
使用union的总次数：100+100=200次 (把乘法变成加法)

union在进行结果集合并时，要求两个结果集的列数（字段数）相同。
Mysql结果集合并时列和列的数据类型可以不同，但是Oracle必须相同

#查询工作岗位是MANAGER和SALESMAN的员工
select ename, job from emp where job = 'MANAGER' or job = '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';

=================================================================================================
#下面查询语句，mysql可以，Oracle不可以，Oracle要求结果集的数据类型必须相同；
select ename, job from emp where job = 'MANAGER' union select ename, sal from emp where job = 'SALESMAN';

limit

limit是将查询结果集的一部分取出来，通常使用在分页查询当中。
完整用法：limit startIndex, length; startIndex是起始下标，length是长度；起始下表默认从0开始。
缺省用法：limit 5; 取前5

注：mysql当中 limit在 order by 之后执行。

#按照薪资降序，取出排名在前5的员工？
select ename, sal from emp order by sal desc limit 0,5;
#取出工资排名在【3-5】名的员工？
//2表示起始位置从下标2开始，就是第三条记录；3表示长度；
select ename,sal from emp order by sal desc limit 2,3;  

分页查询

每页显示3条记录
第1页：limit 0, 3
第2页：limit 3, 3
第3页：limit 6, 3
第4页：limit 9, 3

每页显示pageSize条记录
第pageNo页：limit (pageNo - 1) * pageSize, pageSize;

DQL语句总结

select ... from ... where ... group by ... having ... order by ... limit ...;
#执行顺序
1、from
2、where
3、group by
4、having
5、select
6、order by
7、limit...

例子

#获取每个部门最高薪水的人员名称
select a.ename,a.sal,a.deptno from emp a join (select max(sal) maxsal, deptno from emp group by deptno) b on a.sal = b.maxsal;

#哪些人的薪水在部门的平均薪水之上
select a.ename, a.sal, a.deptno from emp a join (select deptno, avg(sal) avgsal from emp group by deptno) b on a.deptno = b.deptno where a.sal > b.avgsal;