MySQL连接查询

使用到的表

连接查询

什么是连接查询

当我们从一张表中进行数据的查询时称为单表查询。
当我们从emp表和dept表联合起来查询数据时，从emp表中取员工名字，从dept表中取部门名字，这种跨表查询，多张表联合起来查询数据，被称为连接查询。

连接查询的分类

根据语法的年代分类：
    SQL92：1992年时出现的语法
    SQL99：1999年时出现的语法
根据表连接的方式进行分类：
     内连接：
            等值连接
            非等值连接
            自连接
     外连接：
            左外连接（左连接）
            右外连接（右连接）
    全连接

笛卡尔积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

将员工表和部门表进行连接查询，没有任何条件限制，查询出员工姓名和部门名

select ename,dname from emp,dept;    

可以看到总共有56条数据，员工表中的每一个员工都与部门表中的4个部门进行了一次匹配，得到 14*4=56条数据，即两张表数据条数的乘积。

想要避免笛卡尔积的现象，就需要在表连接时附加条件，将满足所给条件的记录筛选出来。

给数据库表取别名，并根据部门编号deptno进行连接查询

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;  // （SQL92的语法）

内连接

内连接之等值连接

案例：查询每个员工所在部门名称，显示员工名和部门名。
需要emp e表和dept d表进行连接，条件是e.deptno=d.deptno

// 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;


// join前面的inner可以省略
select
        e.ename,d.dname
from
        emp e
inner join
        dept d
on
        e.deptno=d.deptno;

SQL92语法：结构不清晰，表的连接条件和后期进一步筛选的条件都放到了where后面
SQL99语法：表连接的条件是独立的，连接之后如果还需要进一步筛选，则继续添加where进行条件查询
等值连接的查询条件是等量关系

内连接之非等值连接

案例：查询出每个员工的薪资等级，要求显示员工名，薪资和薪资等级。

select
    e.ename,e.sal,s.grade
from
    emp e
join 
    salgrade s
on 
    e.sal between s.losal and s.hisal;    // 这里的条件不是一个等量关系

内连接之自连接

案例：查询emp表中员工的领导，自连接，连接条件e1.mgr=e2.empno

select
   e1.ename as '员工名',e2.ename as '领导名'
from
   emp e1
join
   emp e2
on
   e1.mgr=e2.empno;

13条记录，因为king没有领导。

自连接的技巧就是将一张表看作两张表去操作。

外连接

案例：查询部门名以及部门所对应的员工名

先采用内连接进行查询

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

因为在员工表中，没有对应部门号为40的，所以查询结果中也没有部门编号为40的部门。
内连接的特点：查询出完全能够匹配上条件的数据

外连接：

// 外连接(右外连接)
select
    e.ename,d.dname
from 
    emp e
right outer join 
    dept d
on
    e.deptno=d.deptno;


// 外连接(左外连接)
select
    e.ename,d.dname
from
    dept d
left outer join
    emp e
on
    e.deptno=d.deptno;

right：表示将join关键字右边的表当作主表，主要是为了将这张表的数据全部查询出来，捎带着查询左边的表。在外连接中，两张表产生了主次关系。
而上面的查询语句的意思就是右外连接查询部门表中的部门，即使员工表中没有对应的部门员工，也将部门查出，如上图所示，部门“OPERATIONS”在员工表中不能存在，ename就为null
带有right的是右外连接，又叫右连接。带有left的是左外连接，又叫左连接。任何一个右连接都有左连接的写法，反之亦然。

right和left的后面的outer是可以省略的

内连接和外连接的区别

内连接和外连接：
    内连接时，表之间的关系是平等的，没有主次之分，匹配得上的数据就查询到并进行显示，匹配不到的数据就不显示。
    而外连接时表分主次关系，对于主表来说，无论能不能被匹配到，数据都会被显示。

区分内连接和外连接：
    关键字inner和outer，不过这两个关键字是可以省略的。
    所以当我们要判断一个SQL语句是内连接还是外连接时主要看其是否带有right或left关键字，
    带有right或left关键字的连接是外连接，不带的是内连接。

需要注意的是，外连接的查询结果条数一定是大于内连接的查询结果条数的。

多表联查

语法：

select
    ...
from
    ...
(right/left) join
    ...
on
    ...
(right/left) join
    ...
on
    ...
(right/left) join
    ...
on
    ...

案例：找出每个员工的部门名称以及工资等级，要求显示员工姓名、部门名、薪资和薪资等级。
涉及到三张表，emp、dept、salgrade。

select
    e.ename,d.dname,e.sal,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
order by
    e.sal desc;

在一条SQL语句中，内连接和外连接是可以混合使用的

案例：找出每个员工的部门名称以及工资等级和上级领导，要求显示员工姓名、领导名、部门名、薪资和薪资等级。
这里要求显示每个员工的上级领导，如果再采取外连接进行查询，KING 的数据将查不出来

select
    e.ename,e1.ename,d.dname,e.sal,s.grade
from
    emp e
join
    emp e1
on
    e.mgr=e1.empno
join
    dept d
on
    e.deptno=d.deptno
join
    salgrade s
on
    e.sal between s.losal and s.hisal
order by
    e.sal desc;

如下图所示，可以看到，查询的结果中没有关于"KING"的信息，这是因为我们所采用的是内连接进行的查询，查询不到KING的领导，则他的数据就被忽略了。

采用左外连接，将员工表作为主表，即使KING匹配不到条件，也会将他的数据进行显示

select
    e.ename,e1.ename,d.dname,e.sal,s.grade
from
    emp e
left join
    emp e1
on
    e.mgr=e1.empno
join
    dept d
on
    e.deptno=d.deptno
join
    salgrade s
on
    e.sal between s.losal and s.hisal
order by
    e.sal desc;