粤嵌打卡第29天（MYSQL数据库---DML复杂查询语句详解）

今天我们来一起聊聊Mysql数据库复杂查询吧

1、SQL合并查询（UNION、UNION ALL）

作用：主要用来在开发、维护阶段做数据验证。

如果要合并两个结果集，需要：

1- 列个数必须一致

2- 每个对应列的类型必须一致(MySQL对此条自动容错)

UNION与UNION的区别：

UNION: 取多个查询结果的并集并去掉重复元素

UNION ALL: 取多个查询结果的并集并不去掉重复元素

• 1-- 查出员工表中所有job是manager的员工信息

SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno 
FROM emp WHERE job = 'MANAGER';

• 2、查出员工表中所有工资高于2500的员工信息

SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno 
FROM emp WHERE sal>2500;

• 3、合并结果集并去重（UNION）

SELECT ename,empno,job,hiredate,mgr,sal,comm,deptno 
FROM emp WHERE job = 'MANAGER'
UNION
SELECT ename,empno,job,hiredate,mgr,sal,comm,deptno 
FROM emp WHERE sal>2500;

• 4、合并结果集并不去重(UNION ALL)

SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno
FROM emp WHERE job = 'MANAGER'
UNION ALL
SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno 
FROM emp WHERE sal>2500;

• MYSQL不支持直接的交集和并集(ORACLE支持)

-- MySQL不支持直接的交集和差集。
/*
# 取交集：intersect
SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno
FROM emp WHERE job = 'MANAGER'
intersect
SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno 
FROM emp WHERE sal>2500;
# 取差集：minus
SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno
FROM emp WHERE job = 'MANAGER'
minus
SELECT empno,ename,job,hiredate,mgr,sal,comm,deptno 
FROM emp WHERE sal>2500;
*/

2、多表连接查询（重点）

作用：当最终报表需要的数据来自多张表，就需要多表连接

• 1、输出所有员工的基本信息和所在部门信息

SELECT emp.* FROM emp;
SELECT dept.* FROM dept;

笛卡尔积：当多表连接时，无连接条件，或者连接条件失效，造成表中的数据进行完全匹配时出现的现象。也可以理解为两张表所有列相乘的结果集

• 笛卡尔积造成两张查询结果在没有条件的情况下输出所有结果

SELECT emp.* , dept.*
FROM emp,dept;

---

敲重点：

多张表连接查询书写规范：

• 1- 先写数据源
• 2- 写数据源的连接，如果有N张表，至少有N-1个连接条件。
• 3- 写逻辑条件
• 4- 需要查询的列信息

---

• 1、写出所有20部分的人员信息和部门信息

1、等值连接方式：

SELECT e.*,d.dname,d.`LOC`
FROM emp e,dept d
WHERE e.DEPTNO = d.DEPTNO AND e.`DEPTNO` = 20;

2、JOIN ON内连接方式：

# SQL 92标准
#- join on 连接
SELECT e.*,d.dname,d.`LOC`
FROM emp e JOIN dept d
ON( e.DEPTNO = d.DEPTNO )
WHERE e.`DEPTNO` = 20;

2.1 JOIN ON方式也支持多表连接查询：

SELECT e.*,d.dname,d.`LOC`,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`)
WHERE e.`DEPTNO` = 20;

3、join using 指定同名列的连接

• 1、对using指定的同名列进行等值连接，和内连接的区别：同名列直接下在USING括号内，不进行等值连接。

注意，不能在using指定的同名列前加表限定，或者 表.*,只能写要查询的列名（例如Oracle 会报运行错误）

SELECT e.empno,e.ename,e.`MGR`,d.dname,deptno,d.`LOC`
FROM emp e JOIN dept d
USING( DEPTNO)
WHERE e.`DEPTNO` = 20;

4、自然连接： 自动对表中同名列进行等值连接
注意，不能自然连接后的同名列前加表限定，或者 表.*，只能写要查询的列名（例如Oracle 会报运行错误）

SELECT e.*,`DEPTNO`,d.dname,d.`LOC`
FROM emp e NATURAL JOIN dept d
WHERE `DEPTNO` = 20;

5、Cross join 笛卡尔积连接: 表的数据全匹配

SELECT e.*,e.`DEPTNO`,d.dname,d.`LOC`,s.*
FROM emp e CROSS JOIN dept d CROSS JOIN salgrade s

6、case when表达式解决分级问题

• 输出所有20部门人员的信息，以及部门信息和工资等级

SELECT e.*, d.`DNAME`,d.`LOC`,
	CASE WHEN s.`GRADE`<= 2 THEN '初级'
	     WHEN s.`GRADE`<= 4 THEN '中级'
	     ELSE '高级'
	END  grade
FROM emp e,dept d,salgrade s
WHERE e.`DEPTNO`=d.`DEPTNO` 
	AND (e.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`)
	AND e.`DEPTNO`=20;

6.1 case when另一种写法：when值条件确定唯一，列名写在case后。

SELECT e.*, d.`DNAME`,d.`LOC`,
	CASE s.`GRADE`
	     WHEN 1 THEN '初级'
	     WHEN 2 THEN '中级'
	     WHEN 3 THEN '中上级'
	     WHEN 4 THEN '上级'
	     ELSE '高级'
	END  grade
FROM emp e,dept d,salgrade s
WHERE e.`DEPTNO`=d.`DEPTNO` 
	AND (e.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`)
	AND e.`DEPTNO`=30;

• 不使用case when 表达式的查询语句

SELECT e.*,s.`GRADE`
FROM emp e, salgrade s
#where e.`SAL` between s.`LOSAL` and s.`HISAL`;
WHERE e.`SAL` >= s.`LOSAL` AND e.`SAL`<=s.`HISAL`;

---

连接查询的分类：

• 1- 内连接：只显示符合连接条件的记录

• 2- 外连接：除了显示符合连接条件的记录以外，
  还显示不符合连接条件的记录。对于没有
  匹配的数据则显示为null列

• 3-如果需要跨行取数据，则用自连接

---

7、外连接 (OUTER JOIN ON)

右外连接: RIGHT OUTER JOIN ON

• 1、显示20部门的员工和部门信息以外，再显示所有其他部门信息

# 显示20部门的员工和部门信息以外，再显示所有其他部门信息
SELECT e.*, d.*
FROM emp e RIGHT OUTER JOIN dept d
ON( e.`DEPTNO` = d.`DEPTNO` AND e.deptno=20);

• 2、显示20部门的员工和部门信息以外，再显示所有其他人员信息

左外连接: LEFT OUTER JOIN ON

# 显示20部门的员工和部门信息以外，再显示所有其他人员信息
SELECT e.*, d.*
FROM emp e LEFT OUTER JOIN dept d
ON( e.`DEPTNO` = d.`DEPTNO` AND e.deptno=20);

8、全外连接： MySQL语法不支持 FULL OUTER JOIN （Oracle支持）

• 1、显示20部门的员工和部门信息以外，再显示所有其他人员信息,以及其他部门信息

SELECT e.*, d.*
FROM emp e FULL OUTER JOIN dept d
ON( e.`DEPTNO` = d.`DEPTNO` AND e.deptno=20);

• 2、可以使用union进行全外连接实现

# 可以使用union进行全外连接实现
SELECT e.*, d.*
FROM emp e RIGHT OUTER JOIN dept d
ON( e.`DEPTNO` = d.`DEPTNO` AND e.deptno=20)
UNION
SELECT e.*, d.*
FROM emp e LEFT OUTER JOIN dept d
ON( e.`DEPTNO` = d.`DEPTNO` AND e.deptno=20);

9、自连接：需要跨行取数据时（挺重要的）

自连接：需要跨行取数据时

例如：查询所有员工并输出其管理者的编号和姓名，在自己的表中，查询出自己所对应的管理者信息。此时就要自连接

• 输出所有员工以及其管理者姓名

SELECT e1.*, e2.`ENAME` mgr_name
FROM  emp e2 RIGHT OUTER JOIN emp e1
ON( e1.`MGR` = e2.`EMPNO`);

---

好了！今天Mysql的9种查询内容就分享到这了。后面会继续更新Mysql的相关知识哦！！！

---