多表关系
各个表之间存在着各种联系，一般分为三种，与他们的实现：
- 一对多/多对一：在多的一方建立外键，指向一的一方的主键
- 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
- 一对一：多用于单表拆分，在任意一方加入外键，关联另一方的主键，并设置外键为UNIQUE
多表查询
SELECT * FROM a, b;可以查询到a表和b表的笛卡尔积，要想获得有效的查询，我们需要消除多余的笛卡尔积SELECT * FROM a, b WHERE a.id = b.id;进行了等值连接，仅查询了两个表的关联数据
分类
- 连接查询
- 内连接：查询a，b表的交集部分数据
- 左外连接：查询左表所有数据，以及两表交集部分数据
- 右外连接：查询右表所有数据，以及两表交集部分数据
- 自连接：当前表与自身的连接查询，必须使用别名
内连接
隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;显式内连接：
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
e.g. 查询员工表内员工姓名及关联的部门名称（分为员工表emp和部门表dept）
1 | SELECT e.name, d.name FROM emp e JOIN dept d WHERE e.dept_id = d.id; |
外连接
- 左外连接：
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件...; - 右外连接：
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件...;
e.g.1 查询emp表的所有数据和对应的部门信息(dept表)
1 | SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id; |
e.g.2 查询dept表的所有数据和对应的员工信息(emp表)
1 | SELECT e.*, d.* FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id; |
自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表B 别名B ON 条件...
自连接可以是内连接查询，也可以是外连接查询
e.g. 查询员工表内员工及其所属领导的名字（所属领导id和员工id在同一张表emp上）
这里我们要查询同一张表两次，把他当作两张表
1 | SELECT a.name, b.name FROM emp a JOIN emp b WHERE a.managerid = b.id; |
联合查询
把多次查询的结果合并起来，形成一个新的查询结果集
1 | SELECT 字段列表 FROM 表A... UNION SELECT 字段列表 FROM 表A...; |
- UNION合并并去重
- UNION ALL只会合并
条件：联合查询中多张表的列数、字段类型必须一致
子查询
嵌套SELECT语句，成为嵌套查询或子查询
外部语句可以是SELECT/INSERT/UPDATE/DELETE中的任意一个
分类
根据结果：
- 标量子查询：子查询结果为单个值
- 列子查询：子查询结果为一列
- 行子查询：子查询结果为一行
- 表子查询：子查询结果为多行多列
根据位置：
- WHERE之后
- FROM之后
- SELECT之后
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式
常用操作符：= > < >= <=
e.g. 将以下两个查询合并
1 | SELECT id FROM dept WHERE name='销售部'; |
可以得到
1 | SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部'); |
列子查询
子查询返回结果是一列（多行）
常用操作符：
e.g. 查询比研发部中任意一人工资高的员工信息
1 | SELECT * FROM emp WHERE salary>ANY(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部')); |
行子查询
子查询返回结果是一行（多列）
常用操作符：= <> IN NOT IN
e.g. 查询与李华薪资喝直属领导相同的员工信息
1 | SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = '李华'); |
这里可以用括号括起多个条件，一一对应
表子查询
子查询返回结果是多行多列
常用操作：IN
e.g. 查询与张三和李四的职位和薪资相同的员工信息
1 | SELECT * FROM emp WHERE (job, salary) IN (SELECT job, salary FROM emp WHERE name='张三' OR name='李四'); |
关联子查询
某些子查询中会引用外部查询中的列，与外部查询产生关联
没有关联的叫做非关联子查询
关联子查询对外查询中的每一行都会运行一次，有的数据库可能会对其优化
e.g. 查询所有部门信息，并统计部门员工人数
1 | SELECT d.id, d.name, (SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) '人数' FROM dept d; |
