【多表查询】技能get

 

先上图~~你将会干掉这些敌人.....

第一个：合并结果集

门槛：操作的两张表必须 列数、列类型 都完全相同才行。

-- UNION 去除重复的行
SELECT * FROM employee_china UNION SELECT * FROM employee_usa;
-- UNION ALL 不去除重复的行
SELECT * FROM employee_china UNION ALL SELECT * FROM employee_usa;

第二个：连接查询 （灰常重要）

 笛卡尔积：

 笛卡尔积过滤：过滤掉无效的记录。

举例：tony的部门id是10001,对应是销售部，查询两张表的sql写成：

select * from department , employee 

得出的结果集中，会包括tony的其他三条记录，tony咨询,tony人事,tony技术。所以我们要过滤掉这三个无效的记录。

笛卡尔积过滤做法：

select * from department  d, employee e where d.id = e.id;

这样就会产生正确的11条记录。（因为有11个员工）

然后，从上图可以看到，depo和id1重复输出了，所以可以再优化下sql语句：

select e.id "员工ID",e.name "员工名字",d.id "部门编号",d.name "部门名称" 

from department  d, employee e 

where d.id = e.id;

你看，是不是很漂亮~：

 第三个：内连接。

何为内连接？其实就是合并两张表来查询，上sql语句：

SELECT * FROM employee e , department d WHERE e.depno = d.id;

上面的sql写法就是内连接，只不过这不是标准的内连接写法，而是mysql的方言写法。

mysql标准的内连接写法是下面这样的：  （INNER JOIN ....ON）

SELECT * FROM employee e INNER JOIN department d ON e.depno = d.id;#工作中用这种写法,用ON

第四个：外连接。

何为外连接？就是查询出来的结果存在不满足条件的可能而显示出null。

外连接又分为左连接和右连接。

 左连接（也称为左外连接）：就是 右表满足条件的会显示空，而上面内连接，右表不满足条件的就不会显示。

左连接[LEFT JOIN] == 左外连接[LEFT OUTER JOIN]:这两种写法等价，是一样的意思。

其特性：左右表条件不满足时，左表显示数据,右表显示数据为NULL

我在employee表插入一条员工记录，背景：ela这个人还没有部门编号。那么我现在执行以下sql：

SELECT e.id,e.name,e.depno,d.name FROM employee e LEFT OUTER JOIN department d ON e.depno = d.id;
SELECT e.id,e.name,e.depno,d.name FROM employee e LEFT JOIN department d ON e.depno = d.id;

可以看到结果输出为：（ela这一行也输出了，只不过她的部门编号和部门名称显示为null而已）

 右连接（也称为右外连接）：就是 左表满足条件的会显示空，如下图显示，左表不满足条件的就不会显示。

右连接[RIGHT JOIN] == 右外连接[RIGHT OUTER JOIN]:这两种写法等价，是一样的意思。

其特性：左右表条件不满足时，右表显示数据,左表显示数据为NULL。

-----也就是刚好和左外连接相反的意思！！！-------------。。。

结果输出：可以看到现在是左表显示为null.

 连接查询心得：

 看~，员工表的depno是主键，部门表的id是外键。

 

 第五个：自连接。

何为自连接？就是自己连接自己，起别名。

先造一张员工表如下图，看清楚了~:

 现在有个需求要查7369员工编号、姓名、经理编号和经理姓名，sql要怎样写？

select e1.empno '员工编号',e1.ename '员工姓名',e1.mgr '经理编号', e2.empno 
from emp e1,emp e2 where e1.empno = '7369' and e1.mgr = e2.empno

 

第六个：自然连接。

何为自然连接？就是不需要添加主外键的等式，也可以去除笛卡集的数据。

但是有个前提条件：两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno

先造两张表~：

  《员工表》       《部门表》

现在要查员工表所属部门的信息：sql如下：

SELECT * FROM t_employee e INNER JOIN t_department d ON e.depno =  d.depno;#内连接

SELECT * FROM t_employee NATURAL JOIN t_department;#自然连接

SELECT * FROM t_employee INNER JOIN t_department USING(depno);#通过使用using关键词，这种内连接方式等效于自然连接

执行第一条内连接sql，结果：

执行第二条自然连接sql，结果：（自然连接，天生就会找两表中列类型一致的也就是天然主外键关系的等式来构建查询）

再来看，自然右连接的写法和结果：

SELECT * FROM t_employee  NATURAL RIGHT JOIN t_department;#自然右连接 效果等于右连接

  php研发部目前没有人。哈哈~，这很右连接。

 第七个：子查询。

 何为子查询？就是嵌套查询。内涵多个select 关键词。

看下图，需求场景是：

查询工资高于30号部门所有人的员工信息：

# 第一种写法
SELECT MAX(sal) FROM emp WHERE depno = 30;#单行单列
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE depno = 30);

# 第二种写法
SELECT sal FROM emp WHERE depno = 30;#多行单列

SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE depno = 30);#内部会执行最大值操作

分两步走：

第一步，先构建一个查找30号部门所有员工的工资sql。 

SELECT sal FROM emp WHERE depno = 30;#多行单列

第二步：将已上sql插入主sql：

SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE depno = 30);

或者

SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE depno = 30);#内部会执行最大值操作

查询结果：

 

需求：查询工作和工资与MARTIN（马丁）完全相同的员工信息

SELECT * FROM emp 
WHERE ename != 'MARTIN' 
AND (job,sal) IN (SELECT job,sal FROM emp WHERE ename = 'MARTIN');//单行多列

 查询结果：

 需求："有2个以上直接下属" 的员工信息【查找经理信息，并且这个经理有2个以上下属】

第一步：查询，到所有符合mgr>2的mgr记录数。

# 查出经理编号，和mgr出现的次数SELECT mgr,COUNT(mgr) FROM emp GROUP BY(mgr) HAVING COUNT(mgr) >= 2;

原始表：

查询结果：

第二步：

select * from emp where empno in (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

查询结果：（这个结果是看不到下属个数）

 

 

#需求迭加：要求显示下属个数：

select * from emp t1,(SELECT mgr,COUNT(mgr) FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2) t2 where t1.empno = t2.mgr;

需求：查询员工编号为7788的员工名称、员工工资、部门名称、部门地址。（多行多列，用于表）

写法一：常见

select e.ename,e.sal,d.name,d.location from emp e NATURAL JOIN depart d where e.empno = 7788;//恰逢主外键名称一样的话，可以这样写

select e.ename,e.sal,d.name,d.location from emp e，depart d where e.empno = 7788;

写法二：利用子查询节省内存 ，因为过滤掉了depart表中其他无用字段（比如setuptime），笛卡尔积数据会比较少

select e.ename,e.sal,d.name,d.location from emp e,(SELECT depno,name,location from depart) d where e.depno = d.depno and e.empno = 7788;

上面sql语句中，把 (SELECT depno,name,location from depart) d  多行多列，当作一张表来使用。

depart表原始数据：

 查询结果：

 需求：求各个部门薪水最高的员工所有信息

#查出每个部门最高工资的工资金额
select MAX(sal) from emp e GROUP BY depno;
#找出符合以上工资条件的所有人的所有员工信息
select * from emp where emp.sal in(select MAX(sal) from emp e GROUP BY depno);

还可以这样写：多表查询的方式

select t1.* from emp t1,(select MAX(sal) maxsal from emp e GROUP BY depno) t2 where t1.sal = t2.maxsal;

             

=====查到最高工资做索引后，就可以查到对应员工符合这个工资金额的所有信息=>>>>>