MySQL中的连接查询

本是古典何须时尚 2022-11-25 00:51 134阅读 0赞

**MySQL中的连接查询：**

（1）什么是连接查询？  
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终结果。

--------------------

（2）连接查询的分类  
根据语法出现的年代来划分的话，包括：

SQL92（一些老的DBA可能还在使用这种语法。  
DBA：DataBase Administrator，数据库管理员）

SQL99（比较新的语法）

--------------------

根据表的连接方式来划分，包括：

内连接：等值连接，非等值连接，自连接。  
外连接：左外连接（左连接），右外连接（右连接）。  
全连接（这个不讲，很少用）

--------------------

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

笛卡尔积现象：A表中有m行记录，B表中有n行记录，A和B连接，会产生m\*n条数据结果。

--------------------

例题：找出每一个员工的部门名称，要求显示员工名和部门名。  
select ename, 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）怎么避免笛卡尔积现象？当然是加条件进行过滤。  
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？  
不会，次数还是m\*n次，只不过显示出来的是有效数据。

**注意：加了筛选条件，并不能提高程序的效率，只是显示出来的数据是有效的数据。**

--------------------

（5）内连接之等值连接：最大的特点是，连接条件中的关系是等量关系。

例题：查询每个员工的部门名称，要求显示员工姓名和部门名称。  
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，where改成on即可。

--------------------

SQL99语法格式：select 字段 from 表1 join 表2 on 连接条件 where 过滤条件;

**注意：内连接中的等值连接sql语句中的join其实是inner join，省略了inner。**

--------------------

（6）内连接之非等值连接：  
最大的特点是，连接条件中的关系是非等量关系。

例题：找出每个员工的工资等级，要求显示员工名，工资，工资等级。  
select e.ename, e.sal, s.grade  
from emp e join salgrade s  
on e.sal between s.lowsal and s.highsal; // 同样，join前面省略了inner。

--------------------

（7）自连接：最大的特点是，一张表看做两张表，自己和自己连接。

例题：找出每个员工的上级领导，要求显示员工姓名和对应的领导名。  
select e1.ename as ‘员工名’, e2.ename as ‘领导名’  
from emp e1 inner join emp e2  
on e1.mgr = e2.empno; // 同样inner也可以省略

思考：这样确实能找出所有员工的上级领导，但如果有些员工没有上级领导呢？比如CEO，公司总裁，他就没有上级领导。  
但也想把这样的员工显示出来，怎么办？见外连接例题。

--------------------

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

内连接：  
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的数据查询出来，这就是内连接。

内连接中的连接表AB没有主副之分，两张表是平等的。

--------------------

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

--------------------

外连接的分类：  
左外连接，即左连接：表示左边的这张表是主表。  
右外连接，即右连接：表示右边的这张表是主表。

左连接和右连接能够相互转换，左连接有右连接的写法，右连接也有对应的左连接写法。

--------------------

例题：找出所有员工的领导，员工包括CEO，要求显示员工姓名，领导姓名。  
左连接写法：  
select e1.ename, e2.ename  
from emp e1 left join emp e2  
on e1.mgr = e2.empno;

对应的右连接写法：  
select e1.ename, e2.ename  
from emp e1 right join emp e2  
on e1.mgr = e2.empno;

**注意：同样外连接的join前面省略了outer，例如：左连接中的left join其实是left outer join。**

所以说区分sql语句是内连接，还是外连接，不是靠inner和outer关键字。

**而是看是否有left或者right关键字**，没有left和right的连接就是内连接，有left就是左外连接，有right就是右外连接。

例题：找出哪个部门没有员工，要求显示部门的全部信息。  
思路：先确定使用外连接，再确定哪张表是主表，显然部门表是主表，员工表是副表，主要查主表的信息。  
右连接写法：  
select d.\*  
from emp e right join dept d  
on e.deptno = d.deptno  
where e.empno is null;

--------------------

（9）三张表怎么连接查询？

例题：找出每一个员工的部门名称以及工资等级，要求显示员工姓名，部门名称，工资等级。  
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.lowsal and s.highsal;

例题：找出每一个员工的部门名称，工资等级，以及上级领导。  
select e.ename ‘员工’, d.dname ‘部门’, e1.name ‘领导’  
from emp e join dept d  
on e.deptno = d.deptno  
join salgrade s  
on e.sal between s.lowsal and s.highsal  
join emp e1  
on e.mgr = e1.empno;