多表关系

各个表之间存在着各种联系，一般分为三种，与他们的实现：

一对多/多对一：在多的一方建立外键，指向一的一方的主键
多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一：多用于单表拆分，在任意一方加入外键，关联另一方的主键，并设置外键为UNIQUE

多表查询

SELECT * FROM a, b;可以查询到a表和b表的笛卡尔积，要想获得有效的查询，我们需要消除多余的笛卡尔积
SELECT * FROM a, b WHERE a.id = b.id;进行了等值连接，仅查询了两个表的关联数据

内连接

隐式内连接：SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

e.g. 查询员工表内员工姓名及关联的部门名称（分为员工表emp和部门表dept）

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表)

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

e.g.2 查询dept表的所有数据和对应的员工信息(emp表)

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上）

这里我们要查询同一张表两次，把他当作两张表

SELECT a.name, b.name FROM emp a JOIN emp b WHERE a.managerid = b.id;

联合查询

把多次查询的结果合并起来，形成一个新的查询结果集

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

UNION合并并去重
UNION ALL只会合并

条件：联合查询中多张表的列数、字段类型必须一致

子查询

嵌套SELECT语句，成为嵌套查询或子查询

外部语句可以是SELECT/INSERT/UPDATE/DELETE中的任意一个

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

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

e.g. 将以下两个查询合并

SELECT id FROM dept WHERE name='销售部';
SELECT * FROM emp WHERE dept_id = 4;

可以得到

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

列子查询

子查询返回结果是一列（多行）

常用操作符：

e.g. 查询比研发部中任意一人工资高的员工信息

SELECT * FROM emp WHERE salary>ANY(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));

行子查询

子查询返回结果是一行（多列）

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

e.g. 查询与李华薪资喝直属领导相同的员工信息

SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = '李华');

这里可以用括号括起多个条件，一一对应

表子查询

子查询返回结果是多行多列

常用操作：IN

e.g. 查询与张三和李四的职位和薪资相同的员工信息

SELECT * FROM emp WHERE (job, salary) IN (SELECT job, salary FROM emp WHERE name='张三' OR name='李四');

关联子查询

某些子查询中会引用外部查询中的列，与外部查询产生关联

没有关联的叫做非关联子查询

关联子查询对外查询中的每一行都会运行一次，有的数据库可能会对其优化

e.g. 查询所有部门信息，并统计部门员工人数

SELECT d.id, d.name, (SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) '人数' FROM dept d;

红魔咖啡馆

【MySQL】多表查询