MySQL知识点总结（二）DQL续篇

一： 多表操作—多表联合查询

-- 内连接查询（查询多张表的交集）
-- 格式
-- 隐式内连接 select * from A,B where 条件；
-- 显示内连接 select * from A inner join B on 条件；（inner可以省略）

-- 外连接查询
-- 左外连接 (left outer join) select * from A left outer join B on 条件；
SELECT * FROM dept3 a LEFT OUTER JOIN emp3 b ON a.deptno = b.dept_id;
-- 右外连接 （right outer join）select * from A right outer join B on 条件；
SELECT * FROM dept3 a RIGHT OUTER JOIN emp3 b ON a.deptno = b.dept_id;
-- 满外连接（union）
-- union是将两个查询结果上下拼接，并去重
SELECT * FROM dept3 a LEFT OUTER JOIN emp3 b ON a.deptno = b.dept_id
UNION
SELECT * FROM dept3 a RIGHT OUTER JOIN emp3 b ON a.deptno = b.dept_id
-- union all是将两个查询结果上下拼接，不去重
SELECT * FROM dept3 a LEFT OUTER JOIN emp3 b ON a.deptno = b.dept_id
UNION ALL
SELECT * FROM dept3 a RIGHT OUTER JOIN emp3 b ON a.deptno = b.dept_id

二：子查询（子查询就是指在一个完整的查询语句中，嵌套若干个不同功能的小查询）

-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字、员工年龄
-- 1：查询最大年龄
SELECT MAX(age) FROM emp3;
-- 2:让每一个员工的年龄和最大年龄比较，相等则满足条件
SELECT * FROM emp3 WHERE age = (SELECT MAX(age) FROM emp3); -- 单行单列，可以作为一个值来用

-- 查询研发部和销售部的员工信息，包含员工号、员工名字
-- 方式一：关联查询
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno =  b.dept_id AND (NAME = '研发部'OR NAME ='销售部');
-- 方式二：子查询
-- 1：先查研发部和销售部的部门号(1001,1002)
SELECT deptno FROM dept3 WHERE NAME ='研发部' OR NAME ='销售部';
-- 2:查询哪个员工的部门号是1001，1002
SELECT * FROM emp3 WHERE dept_id IN (SELECT deptno FROM dept3 WHERE NAME ='研发部' OR NAME ='销售部');-- 多行单列，多个值

-- 查询研发部20岁以下的员工信息，包括员工号，员工名字，部门名字
-- 方式一：关联查询
SELECT * FROM dept3 a JOIN emp3 ON a.deptno = b.dept_id AND (NAME = '研发部'AND age < 20);
-- 方式二：子查询
-- 1：在部门表中查询研发部信息
SELECT * FROM dept3 WHERE NAME = '研发部';-- 一行多列
-- 2:在员工表中查询年龄小于30岁的员工信息
SELECT * FROM emp3 WHERE age < 30;
-- 3:将以上两个查询的结果进行关联查询
SELECT * FROM （SELECT * FROM dept3 WHERE NAME = '研发部'）t1 JOIN (SELECT * FROM emp3 WHERE age < 30)t2 ON t1.deptno = t2.dept_id;-- 多行多列

三： 子查询-关键字

-- 关键字（all）格式：select ... from ... where c > all(查询语句)
-- 1：查询年龄大于'1003'部门所有年龄的员工信息
SELECT * FROM emp3 WHERE age > ALL(SELECT age FROM emp3 WHERE dept_id ='1003');
-- 2:查询不属于任何一个部门
SELECT * FROM emp3 WHERE dept_id != ALL(SELECT deptno FROM dept3);

-- 关键字（any和some）格式：select ... from where c > any(查询语句)
-- 查询年龄大于'1003'部门任意一个员工年龄的员工信息
SELECT * FROM emp3 WHERE age > ANY (SELECT age FROM emp3 WHERE dept_id = '1003')AND dept_id !='1003';

-- 关键字（in）格式：select ... from ... where c in(查询语句)
-- 查询研发部和销售部的员工信息，包含员工号，员工名字
SELECT eid,ename FROM emp3 WHERE dept_id IN (SELECT deptno FROM dept3 WHERE NAME = '研发部'OR NAME = '销售部');

-- 关键字（exists）格式：select ... from ... where exists(查询语句)
-- 后面查询结果是'true'外层查询执行，反之则不执行
-- 查询公司是否有大于60岁的员工，有则输出
SELECT * FROM emp3 a WHERE EXISTS(SELECT * FROM emp3 WHERE a.age > 60);
SELECT * FROM emp3 a WHERE eid IN (SELECT eid FROM emp3 WHERE a.age > 60);
-- 查询有所属部门的员工信息
SELECT * FROM emp3 a WHERE EXISTS(SELECT * FROM dept3 b WHERE a.dept_id = b.deptno);
SELECT * FROM emp3 a WHERE EXISTS (SELECT * FROM emp3 WHERE a.age > 60);

四：进行自关联查询

-- 1：查询每个三国人物及他的上级信息，如：关羽  刘备
SELECT a.ename,b.ename FROM t_sanguo a,t_sanguo b WHERE a.manager_id = b.eid;
SELECT a.ename,b.ename FROM t_sanguo a JOIN t_sanguo b ON a.manager_id = b.eid;
-- 2:查询所有人物及上级
SELECT a.name,b.name FROM t_sanguo a LEFT JOIN t_sanguo b ON a.manager_id = b.eid;
-- 3:查询所有人物，上级，上上级 比如：张飞 刘备 刘协
SELECT
   a.ename,b.name,c.name 
FROM t_sanguo a 
LEFT JOIN t_sanguo b ON a.manager_id = b.eid
LEFT JOIN t_sanguo c ON b.manager_id = c.eid;