MySQL笔记三：DQL（2）——连接查询、笛卡尔积、union和limit

3.1连接查询的概念
从一张表单独查询称为单表查询，多张表联合起来查询数据，被称为连接 
根据表连接的方式分类：
        内连接：等值连接、非等值连接、自连接
        外连接：左外连接（左连接）、右外连接（右连接）
        全连接（较少用到）
 
3.2笛卡尔积现象
两张表连接没有限制，则总数据数时两张表条数的乘积，称为笛卡尔积现象
表的连接次数越多，连接效率越低，所以要尽量避免表的连接
select ename,dname from emp,dept;//一共n1*n2条数据
避免笛卡尔积现象：连接时加上条件
select ename,dname from emp,dept where emp.deptno = dept.deptno;//一共n1条数据，匹配次数没有减少，只不过在where处进行了筛选，SQL92，以后少用SQL92
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//起别名，在select中加上对应的表，可以提升效率，很重要
 
3.3内连接——等值连接（连接条件为等值关系）
要求：查询员工姓名、员工所在部门名称
select e.ename,d.dname 
from emp e 
inner join dept d 
on e.deptno = d.deptno;//SQL99，后续需要进一步筛选，可以在后面继续添加where,结构比SQL92语法更清晰,inner可以省略，但是写上可读性更好
 
3.4内连接——非等值连接（连接条件不是一个等值关系）
要求：查询员工姓名、薪资与薪资等级
select e.ename,e.sal,s.grade 
from emp e 
inner join salgrade s 
on e.sal between s.losal and s.hisal;
 
3.5内连接——自连接（同一张表自己和自己连接）
要求：查询员工的上级领导，要求显示员工名和对应的领导名
技巧：一张表看成两张表
select e.name as ‘员工’,m.name as ‘领导’
from emp e
join emp m
on e.mgr = m.empno;//如果出现null,则该条数据不显示
 
3.6外连接
//右连接
select e.ename,d.dname 
from emp e 
right outer join dept d 
on e.deptno = d.deptno;//right代表将join关键字右边的表看成主表，主要是为了将右边的表的数据全部查询出来，捎带查询左边的表,outer可以省略，加上可读性更强一点
//左连接
select e.ename,d.dname 
from emp e 
right outer join dept d 
on e.deptno = d.deptno;//leftt代表将join关键字左边的表看成主表
 
外连接：两张表产生了主次关系
内连接：将所有满足连接条件的数据显示出来，两张表没有主次关系,如果有null的数据，则该条数据就不显示了
外连接的查询结果一定大于内连接查询结果
 
3.7三张表、四张表连接
select …
from a
join b
on  a和b连接的条件
join c
on a和c连接的条件
right join d
on a和d连接的条件
 
select e.ename,e.sal,d.dnane,s.grade
from emp e
join dept d
on e.deptno=d.deptno
join salgrade s
on a.sal between s.losal and s.hisal;
 
3.8子查询
select语句中嵌套select语句，被嵌套的select语句被称为子查询
select 
        …(select)
from
        …(select)
where
        …(select);
例子1：要求找出比最低工资高的员工姓名和工资——where中的子查询
技巧：先找出最低工资，然后找出大于最低工资的数据，最后合并
select ename,sal from emp where sal>(select min(sal) from emp);
 
例子2：找出每个岗位的平均工资的薪资等级——from中的子查询
技巧：from后面的子查询，可以将子查询的结果作为一张临时表
预备知识：先找出每个岗位的平均工资
select job,avg(sal)
from  emp
group by job
解决方法：将查询结果当成真实存在的表
select t.job,t.avgsal,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;//需要注意将avg(sal）起别名，不然会被当成关键字会报错
 
例子3：找出每个员工的名字和部门名——select中的子查询
注意：对于select后面的子查询，这个子查询只能返回一条结果，返回多余一条则报错
select 
        e.name,(select d.dname from dept d where e.deptno=d.deptno) as dname
from
        emp e;
 
3.9union合并查询结果
union的效率比连接的效率更高一点，union把乘法变成了加法
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;
注意：union进行查询结果合并时，MySQL要求两个结果的列数相同；oracle还要求合并时不仅列数相同，列的类型也要求相同
 
3.10limit取出部分查询结果
limit将查询结果的一部分取出来，通常用于分页查询中，例如百度一页显示10条内容，分页的作用是提高用户的体验
limit startIndex,length;//完整用法：startIndex是起始下标，从0开始，length是长度
limit length;//缺省用法：从0开始，取前length个
limit在order by之后执行，在排序之后再使用
例子：按照薪资降序，取前5名员工
select ename,sal 
from emp
order by sal desc
limit 0,5;
 
分页
每页显示pageSize条记录，则第page页的limit约束：limit (pageNum-1)*pageSize, pageSize;
 
综合顺序
select …
from …
where …
order by ….
having …
limit …