07、MySQL-多表查询

1、内连接

1.1 隐式内连接

1.2 显式内连接

2、外连接

2.1 左外连接

2.2 右外连接

3、自连接

4、联合查询

5、子查询

5.1 标量子查询

5.2 列子查询

5.3 行子查询

5.4 表子查询

1、内连接

概念：相当于查询A、B表交集的部分数据

1.1 隐式内连接

语法：SELECT 字段列表 FROM 表1,表2 WHERE 条件
-- 例：查询每一个员工的姓名 及关联的部门的名称(隐式内连接的实现)
SELECT emp.name,dept.name from emp,dept WHERE emp.dept_id = dept.id
1.2 显式内连接

语法：SELECT 字段列表 FORM 表1 [INNER] JOIN 表2 ON 连接条件
-- 例：查询每一个员工的姓名 及关联的部门的名称(显式内连接的实现)
SELECT emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id

2、外连接

2.1 左外连接

概念：查询左表所有数据以及两张表交集的部分数据

语法：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...
SELECT emp.name,dept.name '部门名称' FROM emp LEFT OUTER JOIN dept ON emp.dept_id = dept.id
2.2 右外连接

概念：查询右表所有数据以及两张表交集的部分数据
SELECT emp.name, dept.* FROM emp RIGHT OUTER JOIN dept ON emp.dept_id = dept.id

3、自连接

概念：当前表与自身的连接查询，自连接必须使用表别名可以是内连接查询也可以是外连接查询

语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件
-- 查询员工所属领导的信息
SELECT a.name '员工',b.name '领导' from emp a JOIN emp b on a.managerid = b.id

4、联合查询

概念：就是把多次查询的结果合并起来行程一个新的查询结果集

语法：SELECT 字段列表 FROM 表A... UNION[ALL] SELECT 字段列表 FROM 表B...
-- 将薪资低于5000的员工和年龄大于50的员工全部查找出来SELECT * FROM emp WHERE salary < 5000 UNION ALL SELECT * FROM emp WHERE age > 50-- 去掉ALL可以去重SELECT * FROM emp WHERE salary < 5000 UNION SELECT * FROM emp WHERE age > 50
注意：

条件字段列表需要保持一致，字段类型也要保持一致
UNION ALL 会将全部数据合并在一起 UNION后会对合并的数据去重

5、子查询

概念：SQL语句中嵌套SELECT语句称为嵌套查询又称子查询

语法：SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2)

5.1 标量子查询

概念：子查询结果为单个值

常用操作符：=、<>、>、>=、< 、<=

-- 查询研发部的所有员工信息
-- a. 查询"销售部" 部分ID
SELECT id FROM dept WHERE name = '研发部'
-- b. 根据销售部部门id 查询员工信息
SELECT * FROM emp WHERE dept_id = 1
-- c. 结合
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部')

5.2 列子查询

概念：子查询结果为一列

常用操作符：IN、NOT IN、ANY、SOME、ALL

-- 查询“研发部” 和 “市场部” 的所有员工信息
-- a. 查询“销售部” 和 “市场部” 的部门id
SELECT id FROM dept WHERE name = '研发部' OR name = '市场部'
-- b. 根据部门ID，查询员工信息
SELECT * FROM emp WHERE dept_id IN(1,2)
-- c. 结合
SELECT * FROM emp WHERE dept_id IN((SELECT id FROM dept WHERE name = '研发部' OR name = '市场部'))

5.3 行子查询

概念：子查询结果为一行

常用操作符：=、<>、IN、NOT、IN

-- 查询与"晓飞"的薪资及直属领导相同的员工信息
-- a. 查询"晓飞"的薪资与直属领导
SELECT salary,managerid FROM emp WHERE name='晓飞'-- b. 查询与"晓飞"的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary,managerid FROM emp WHERE name='晓飞')

5.4 表子查询

概念：子查询结果为多行多列

常用操作符：IN

-- 例：查询与“赵四”，“王炸” 的职位和薪资相同的员工信息
-- a. 查询赵四和王炸的职位和薪资
SELECT job,salary FROM emp WHERE name IN('赵四','王炸')
-- b. 查询与“赵四”，“王炸” 的职位和薪资相同的员工信息
SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE name IN('赵四','王炸'))-- 例：查询入职日期是“2004-01-01”之后的员工信息 及其部门信息
-- a. 查询入职日期是“2004-01-01”之后的员工信息
SELECT * FROM emp WHERE entrydate > '2004-01-01'
-- b. 查询入职日期是“2004-01-01”之后的员工信息 及其部门信息
SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate > '2004-01-01') e LEFT JOIN dept d ON e.dept_id = d.id