（四）连接查询

1、查询结果去重（distinct）

selece distinct job from emp;

select ename,distinct job from emp; 是错误

select distinct job,deptno from emp;正确，两个字段联合去重

 注意：distinct 只能出现在所有的字段的最前面（的）

 举例：

（1）统计岗位的数量

select count(distinct job) from emp;

2、连接查询

（1）什么是连接查询？

         在实际开发中，大部分情况下都不是从单表查询数据，一般是多张表联合查询去取最终的结果。   在实际开发中，一般一个业务对应多张表，比如：学生与班级，起码两张表。

（2）连接查询分类

根据表的连接方式来划分，包括：
      ①内连接
                     等值连接
                     非等值连接
                     自连接
      ②外连接
                    左外连接（左连接）
                    右外连接（右连接）
      ③全连接

（3）在表的连接查询方面有一种现象被称为：笛卡儿积现象

例如：将员工表 emp  和 部门表 dept 联合起来

select ename,dname from emp,dept;

这里如果 emp 有 10 条记录，dept 有 5条记录，则最终查出来的有 10 * 5 = 50 条数据。（这便是 笛卡儿积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终查询的结果条数是两张表记录条数的乘积）

（4）类的别名

select e.ename,d.dname from emp e,dept d;

（5）怎样避免笛卡儿积现象？

回答：加条件进行过滤

思考：避免了笛卡儿积现象，会减少记录的匹配次数吗？----- 不会，次数还是56次，只不过显示的是有效记录。

3、内连接

语法：（inner 可以省略）

    ...
            A
    inner join
            B
    on
          连接条件
    where
           ...

（1）等值连接

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

     select
        e.ename,d.dname
     from
        emp e
     join
        dept d
     on 
        e.deptno=d.deptno;

（2）非等值连接

案例：找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级

   select
         e.ename,e.sal,s.grade
    from
         emp e
    join 
         salgrade s
    on 
          e.sal between s.losal and hisal;

 （3）自连接

（最大的特点：一张表可以看作两张表。自己连接自己）

select 
   a.ename as '员工名',b.ename as '领导名'
from
   emp a
inner join 
   emp b
on 
   a.mgr = b.empno;

4、外连接

（1） 什么是外连接，和内连接有什么区别？

 内连接：
   假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
   AB两张表没有主副之分，两张表是平等的
 

外连接：
   假设A和B进行连接，使用外连接的话，AB两张表中有一张是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表上的数据匹配上，副表自动模拟出null与之匹配。

特点：主表数据 无条件都查询出来

外连接的分类：
     ① 左外连接（左连接）：表示左边的这张表是主表
     ② 右外连接（右连接）：表示右边的这张表是主表

     左连接有右连接的写法，右连接也会有对应的左连接

案例：

（1）找出每个员工的上级领导？（采用外连接的方式，对于没有领导的员工，比如老总，也能查出数据，而内连接就会丢失该项数据）

外连接(左外连接/左连接)

//outer可以省略
select 
   a.ename as '员工名',b.ename as '领导名'
from
   emp a
left outer join 
   emp b
on 
   a.mgr = b.empno;

外连接(右外连接/右连接)结果一样
select 
   a.ename as '员工名',b.ename as '领导名'
from
   emp b
right join 
   emp a
on 
   a.mgr = b.empno;

（2）找出那个部门没有员工？

select 
    d.*
from
   emp e
right join
   dept d
on
   e.deptno = d.deptno
where
   e.empno is null;

5、三张表的连接查询

...
        A

join
        B
on
       ...
join
        C
on
       ...

案例：

（1）找出每一个员工的部门名称以及工资等级

   select 
      e.ename,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;

（2）找出每一个员工的部门名称、工资等级以及上级领导

       select 
          e.ename '员工',d.dname,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;

 6、子查询

（1）什么是子查询？子查询都可以出现在哪里？

 select 当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在哪里？
    select
      ...(select).
    from
       ..(select).
    where
       ..(select)

（2） where子句中使用子查询

案例：找出高于平均薪资的员工信息

 select * from emp where sal > avg(sal);//错误的写法，where后面不能直接使用分组语句

  第一步，找出平均薪资
       select avg(sal) from emp ;

  第二步，where过滤

        select * from emp where sal > (select avg(sal) from emp);

 （3）from后面嵌套子查询

案例：找出每个部门平均薪资的薪资等级

第一步：找出每个部门平均薪资(按照部门编号分组，求sal)
           select deptno,avg(sal) as avgsal from emp group by deptno; 

第二步，将以上的查询结果当作临时表t,让t 表何salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal;

select 
     t.*,s.grade
from 
     (select deptno,avg(sal) as avgsal from emp group by deptno) t
join
     salgrade s
on
     t.avgsal between s.losal and s.hisal;

找出每个部门平均的薪资等级

第一步，找出每个员工的薪资等级

select 
    e.ename ,e.sal,e.deptno,s.grade 
from 
    emp e 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal;

第二步，基于以上结果，继续按照deptno分组，求grade平均值

select 
    deptno,avg(s.grade)
from
     emp e
join
     salgrade s
on 
     e.sal between s.losal and s.hisal
group by
     e.deptno;

（4） 在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;

7、union （可以将查询的结果集相加）

案例：找出工作岗位是SALESMAN和MANAGER的员工？  

 第一种：select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER'; 
 第二种：select ename,job from emp where job in('SALESMAN','MANAGER');

第三种：
select ename,job from emp where job = 'SALESMAN'
union
select ename,job from emp where job = 'MANAGER';

8、limit 

（1）limit是MySQL特有的，其他数据库中没有，不通用（Oracle中有一个相同的机制，叫做rownum）

（2）limit取结果集 中的部分数据，这是它的作用。

（3）语法机制：

 limit startIndex, length
      startIndex表示起始位置，从0开始，0表示第一条数据
      length表示取几个

 案例：取出工资前五名的员工（思路：降序取前五个）

select ename,sal from emp order by sal desc limit 0,5;
(select ename,sal from emp order by sal desc limit 5;)

找出工资排名在第四到第九名的员工

 select ename,sal from emp order by sal desc limit 3,6;

（4）limit是sql语句最后执行的一个环节

select    5
   ...
from      1
   ...
where     2
   ...
group by  3
   ...
having    4
   ...
order by  6
   ...
limit     7
    ...

 

参考：动力节点数据库课程