多表查询

多表关系

一对多（多对一）

多对多

一对一

概述

1. 多表查询介绍：指从多张表中查询数据。

2. 笛卡尔积：笛卡尔乘积是指在数学中，两个集合中A与B的所有组合情况。

3. 注意：在多表查询时，需要消除无效的笛卡尔积。

4. 

内连接

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件;

例子

SELECT e.name,d.name FROM emp e,dept d WHERE e.dept_id = d.id;

显式内连接

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件;

例子

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

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 条件;

例子

## 查询emp的所有数据，和对应的部门名字。
SELECT e.*,d.name  FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id; 

右外连接

SELECT 字段列表 FRON 表1 RIGHT OUTER JOIN 表2 ON 条件;

例子

可以把右连接转为左连接

自连接

语法

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;

例子

## 获取每个员工对应的管理
SELECT a.name '员工',b.name '管理' FROM emp a JOIN emp b ON a.id = b.managerid;
OR
SELECT a.name '员工',b.name '管理' FROM emp a,emp b WHERE a.id = b.managerid;

注意

自连接查询，可以是内连接查询，也可以是外连接查询。

联合查询

语法

SELECT 字段列表C FROM  表A ....;

UNION[ALL]

SELECT 字段列表C FROM 表B ....;

例子

## 将工低于5000的员工 和 年龄大于50岁的员工 全部查询出来
SELECT * FROM emp WHERE salary < 5000
UNION
SELECT * FROM emp WHERE age > 50;

OR
## 查询的列数不同，会报错。
SELECT name,salary FROM emp WHERE salary < 5000
UNION
SELECT name FROM emp WHERE age > 50;

注意

1. 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

2. UNION 对合并后的数据去重。

3. UNION ALL 不去重。

子查询

概念

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

语法

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

分类

标量子查询

概念

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询被称为标量子查询。

例子

1. 查询‘销售部’的所有员工信息
a. 查询销售部的部门ID
SELECT id FROM dept WHERE name = '销售部';
b. 根据销售部ID，查询员工信息
SELECT  * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部';)

2. 查询在小白入职之后的员工信息
a. 查询小白的入职时间
SELECT hire_date FROM emp WHERE name = '小白';
b. 根据小白的入职时间，查询之后的员工信息
SELECT * FROM emp WHERE hire_date > (SELECT hire_date FROM emp WHERE name = '小白');

列子查询

概念

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：

1. IN 在指定的集合范围之内，多选一。

2. NOT IN 不在指定的集合范围之内。

3. ANY 子查询返回列表中，有任意一个满足即可。

4. SOME = ANY

5. ALL 子查询返回列表的所有值都必须满足。

例子

1. 查询销售部和市场部的所有员工信息。
a. 查询销售部与市场部的部门ID,结果返回一列多行
SELECT id FROM dept WHERE name = '销售部' OR name = '市场部';
b. 根据ID,查询员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = '销售部' OR name = '市场部');

2. 查询比 财务部所有人工资都高的员工 信息。
a. 查询财务部的ID
SELECT id FROM dept WHERE name = '财务部';
b. 根据ID,查询员工工资
SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部');
c. 查询信息
SELECT * FROM emp WHERE salary > ALL(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));

行子查询

概念

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

例子

1. 查询与 张无忌的工资及直属领导 相同的员工信息
a. 查询张无忌的工资及直属领导,结果返回一行多列。
SELECT salary,managerid FROM emp WHERE name = '张无忌';
b. 查询与 张无忌的工资及直属领导 相同的员工信息
SELECT * FROM emp WHERE salary = 12500 AND id = 1;
=
SELECT * FROM emp WHERE (salary,id) = (12500,1);
=
SELECT * FROM emp WHERE (salary,managerid) IN (SELECT salary,managerid FROM emp WHERE name = '张无忌');

表子查询

概念

子查询返回的结果是多行多列，这种子查询称为表子查询。

例子

1. 查询与 杨过或张无忌的职位和工资相同的 员工信息
a. 查询杨过或张无忌的职位和工资
SELECT job,salary WHERE name = '杨过' OR name = '张无忌';
b. 查询与 杨过或张无忌的职位和工资相同的 员工信息
SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary WHERE name = '杨过' OR name = '张无忌');

2. 查询员工信息以及对应的部门信息(2表合1)
SELECT e.*,d.* FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;


3. 查询入职日期是'2006-01-01' 之后的员工信息，以及部门信息。
a. 查询入职日期是'2006-01-01' 之后的员工信息
SELECT * FROM emp WHERE entrydate > 2006-01-01;
b. 将 入职日期是'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;