今天我们来一起聊聊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的相关知识哦!!!