【sql】常用命令三多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多(多对一)、多对多、一对一

多表查询方式主要分为两类：
1 连接查询。连接查询有以下几种方式：
内连接：相当于查询A、B交集部分数据
外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名
2 子查询

准备数据：
创建两张表：部门表、雇员表，部门和雇员表是一对多的关系。雇员表中包含部门id，两张表靠这个字段关联起来。

内连接：内连接的语法分为两种: 隐式内连接、显式内连接。
场景1:查询每一个员工的姓名 , 及关联的部门的名称。
隐式内连接语法：

SELECT emp.name,dept.name FROM emp ,dept where emp.dept_id = dept.id 

显示内连接语法：

SELECT emp.name,dept.name FROM emp INNER JOIN dept ON emp.dept_id = dept.id 

外连接：
场景2:查询emp表的所有数据, 和对应的部门信息。
左外连接：

SELECT emp.*,dept.name FROM emp LEFT JOIN dept ON emp.dept_id = dept.id

查询结果包含emp表中没有部门属性的雇员数据

右外连接写法：

select emp.*,dept.name from dept RIGHT JOIN emp on emp.dept_id = dept.id

场景3：
查询dept表的所有数据, 和对应的员工信息
左外连接写法：

SELECT dept.* ,emp.* from dept LEFT JOIN emp on emp.dept_id = dept.id

右外连接写法：

select dept.*, emp.* from emp RIGHT JOIN dept on emp.dept_id = dept.id;

注意：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接：
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。而对于自连接查询，可以是内连接查询，也可以是外连接查询。
场景4:查询员工及其所属领导的名字

SELECT a.name AS empName ,b.name AS leaderName FROM emp AS a JOIN emp AS b ON a.managerid = b.id

场景5:查询所有员工及其领导的名字, 如果员工没有领导, 也需要查询出来

SELECT a.name AS empName ,b.name AS leaderName FROM emp AS a LEFT JOIN emp AS b ON a.managerid = b.id

注意：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询：
对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

注意：
1 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
2 union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

场景6:将薪资低于 5000 的员工 , 和年龄大于 50 岁的员工全部查询出来。
当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 也可以通过 union / union all 来联合查询。

SELECT * FROM emp WHERE salary>5000 UNION ALL SELECT * FROM emp WHERE age > 50

注意：union all查询出来的结果，仅仅进行简单的合并，并未去重。

SELECT * FROM emp WHERE salary>5000 UNION SELECT * FROM emp WHERE age > 50

union 联合查询，会对查询出来的结果进行去重处理。

子查询
根据子查询结果不同，分为：
1 标量子查询（子查询结果为单个值）
2 列子查询 (子查询结果为一列)
3 行子查询 (子查询结果为一行)
4 表子查询 (子查询结果为多行多列)
根据子查询位置，分为：
1 WHERE之后
2 FROM之后
3 SELECT之后

标量子查询：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= <> > >= < <=
场景7:查询 “销售部” 的所有员工信息

SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name='销售部')

列子查询：子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
场景8:查询 “销售部” 和 “市场部” 的所有员工信息

SELECT * FROM emp WHERE dept_id in (SELECT id FROM dept WHERE name='销售部' OR name='市场部')

场景9:查询比 财务部 所有人工资都高的员工信息

select * from emp where salary> ALL (select salary from emp where dept_id = (select id from dept where name = '财务部'))

行子查询：子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。常用的操作符：= 、<> 、IN 、NOT IN
场景10:查询与 “安小鸟” 的薪资和直属领导都相同的员工信息 ;

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '安小鸟');

表子查询：子查询返回的结果是多行多列，这种子查询称为表子查询。常用的操作符：IN
场景11: 查询与 “眉庄” , “年世兰” 的职位和薪资相同的员工信息

SELECT * from emp where (job,salary ) in (select job,salary from emp where name= '眉庄' or name = '年世兰')

场景12:查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;