1-MySQL - 多表查询
before
准备数据：world.sql
准备数据
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
NAME VARCHAR(32) NOT NULL COMMENT '部门姓名'
)ENGINE=INNODB CHARSET=utf8 COMMENT '部门表';
INSERT INTO department(id,NAME) VALUES
(1, '人事部'),
(2, '财务部'),
(3, '行政部'), -- 该部门没有员工
(4, '运营部');
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
NAME VARCHAR(32) NOT NULL COMMENT '员工姓名',
age INT NOT NULL COMMENT '员工年龄',
gender ENUM('male', 'female') NOT NULL DEFAULT 'male',
dep_id INT NOT NULL COMMENT '所在部门id' -- 这里故意不绑定外键，为了后续展开讲接
)ENGINE=INNODB CHARSET=utf8 COMMENT '员工表';
INSERT INTO employee(NAME,age,gender,dep_id) VALUES
('小黄',18, 'male', 1),
('小李',19, 'male', 2),
('小红',20, 'female', 2),
('小兰',20, 'female', 1),
('小六',32, 'male', 1),
('小王',23, 'male', 4),
('小华',52, 'male', 5); -- 实际上id为5的部门并不存在
多表连接查询
多表连接，就是将几张表拼接为一张表，然后进行查询，先来看基本语法：
SELECT col1, col2
FROM t1 INNER/LEFT/RIGHT JOIN t2
ON 连接条件(t1.col=t2.col)
;
接下来，来研究连接时的几种情况。
交叉连接
交叉连接时，不使用任何匹配条件，生成笛卡尔积：
SELECT * FROM employee,department;
+----+--------+-----+--------+--------+----+-----------+
| id | NAME | age | gender | dep_id | id | name |
+----+--------+-----+--------+--------+----+-----------+
| 1 | 小黄 | 18 | male | 1 | 1 | 人事部 |
| 1 | 小黄 | 18 | male | 1 | 2 | 财务部 |
| 1 | 小黄 | 18 | male | 1 | 3 | 行政部 |
| 1 | 小黄 | 18 | male | 1 | 4 | 运营部 |
| 2 | 小李 | 19 | male | 2 | 1 | 人事部 |
| 2 | 小李 | 19 | male | 2 | 2 | 财务部 |
| 2 | 小李 | 19 | male | 2 | 3 | 行政部 |
| 2 | 小李 | 19 | male | 2 | 4 | 运营部 |
| 3 | 小红 | 20 | female | 2 | 1 | 人事部 |
| 3 | 小红 | 20 | female | 2 | 2 | 财务部 |
| 3 | 小红 | 20 | female | 2 | 3 | 行政部 |
| 3 | 小红 | 20 | female | 2 | 4 | 运营部 |
| 4 | 小兰 | 20 | female | 1 | 1 | 人事部 |
| 4 | 小兰 | 20 | female | 1 | 2 | 财务部 |
| 4 | 小兰 | 20 | female | 1 | 3 | 行政部 |
| 4 | 小兰 | 20 | female | 1 | 4 | 运营部 |
| 5 | 小六 | 32 | male | 1 | 1 | 人事部 |
| 5 | 小六 | 32 | male | 1 | 2 | 财务部 |
| 5 | 小六 | 32 | male | 1 | 3 | 行政部 |
| 5 | 小六 | 32 | male | 1 | 4 | 运营部 |
| 6 | 小王 | 23 | male | 4 | 1 | 人事部 |
| 6 | 小王 | 23 | male | 4 | 2 | 财务部 |
| 6 | 小王 | 23 | male | 4 | 3 | 行政部 |
| 6 | 小王 | 23 | male | 4 | 4 | 运营部 |
| 7 | 小华 | 52 | male | 5 | 1 | 人事部 |
| 7 | 小华 | 52 | male | 5 | 2 | 财务部 |
| 7 | 小华 | 52 | male | 5 | 3 | 行政部 |
| 7 | 小华 | 52 | male | 5 | 4 | 运营部 |
+----+--------+-----+--------+--------+----+-----------+
28 rows in set (0.00 sec)
由展示结果可以看到，笛卡尔积的结果是左表的每条记录都跟右表的每条记录都连接一次，这就是所谓的笛卡尔积的结果。虽然看起来没啥用，但它确是一切连接的基础。
内连接
内连接(INNER JOIN)是找几张表的交集，即根据条件筛选出来正确的结果。
SELECT emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
FROM employee AS emp INNER JOIN department AS dep
ON emp.dep_id=dep.id;
+----+--------+-----+--------+--------+----+-----------+
| id | name | age | dep_id | gender | id | name |
+----+--------+-----+--------+--------+----+-----------+
| 1 | 小黄 | 18 | 1 | male | 1 | 人事部 |
| 2 | 小李 | 19 | 2 | male | 2 | 财务部 |
| 3 | 小红 | 20 | 2 | female | 2 | 财务部 |
| 4 | 小兰 | 20 | 1 | female | 1 | 人事部 |
| 5 | 小六 | 32 | 1 | male | 1 | 人事部 |
| 6 | 小王 | 23 | 4 | male | 4 | 运营部 |
+----+--------+-----+--------+--------+----+-----------+
6 rows in set (0.00 sec)
由于部门表中没有id=5的部门，所以员工表dep_id=5的这条记录没有返回;而由于行政部没有员工，所以这条记录也没返回。
外连接之左连接
左连接(LEFT JOIN)是以左表为准，如果右表中没有合适的记录，用NULL补全；其本质是在内连接的基础上增加左表有结果而右表没有的记录(内连接时，这种情况的记录会忽略)。
SELECT emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
FROM employee AS emp LEFT JOIN department AS dep
ON emp.dep_id=dep.id;
+----+--------+-----+--------+--------+------+-----------+
| id | name | age | dep_id | gender | id | name |
+----+--------+-----+--------+--------+------+-----------+
| 1 | 小黄 | 18 | 1 | male | 1 | 人事部 |
| 4 | 小兰 | 20 | 1 | female | 1 | 人事部 |
| 5 | 小六 | 32 | 1 | male | 1 | 人事部 |
| 2 | 小李 | 19 | 2 | male | 2 | 财务部 |
| 3 | 小红 | 20 | 2 | female | 2 | 财务部 |
| 6 | 小王 | 23 | 4 | male | 4 | 运营部 |
| 7 | 小华 | 52 | 5 | male | NULL | NULL |
+----+--------+-----+--------+--------+------+-----------+
7 rows in set (0.00 sec)
可以看到，员工小华所在的id=5的部门并不存在，但是左连接时，这种缺省使用NULL补全了。
外连接之右连接
跟左连接正好相反，右连接(RIGHT JOIN)是以右表为准，如果左表中某些字段没有合适的结果，用NULL补全；其本质是在内连接的基础上增加右表有结果而左表没有的记录(内连接时，这种情况的记录会忽略)。
SELECT emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
FROM employee AS emp RIGHT JOIN department AS dep
ON emp.dep_id=dep.id;
+------+--------+------+--------+--------+----+-----------+
| id | name | age | dep_id | gender | id | name |
+------+--------+------+--------+--------+----+-----------+
| 1 | 小黄 | 18 | 1 | male | 1 | 人事部 |
| 2 | 小李 | 19 | 2 | male | 2 | 财务部 |
| 3 | 小红 | 20 | 2 | female | 2 | 财务部 |
| 4 | 小兰 | 20 | 1 | female | 1 | 人事部 |
| 5 | 小六 | 32 | 1 | male | 1 | 人事部 |
| 6 | 小王 | 23 | 4 | male | 4 | 运营部 |
| NULL | NULL | NULL | NULL | NULL | 3 | 行政部 |
+------+--------+------+--------+--------+----+-----------+
7 rows in set (0.00 sec)
由结果可知，记录展示以右表为准，因为右表没有id=5的部门，所以，左表中dep_id=5的记录不展示。
全外连接
全外连接，在内连接的基础上，展示左右表的所有的记录，而左右表中缺省记录以NULL补全。
SELECT emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
FROM employee AS emp LEFT JOIN department AS dep
ON emp.dep_id=dep.id
UNION -- 全外连接使用 union
SELECT emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
FROM employee AS emp RIGHT JOIN department AS dep
ON emp.dep_id=dep.id;
+------+--------+------+--------+--------+------+-----------+
| id | name | age | dep_id | gender | id | name |
+------+--------+------+--------+--------+------+-----------+
| 1 | 小黄 | 18 | 1 | male | 1 | 人事部 |
| 4 | 小兰 | 20 | 1 | female | 1 | 人事部 |
| 5 | 小六 | 32 | 1 | male | 1 | 人事部 |
| 2 | 小李 | 19 | 2 | male | 2 | 财务部 |
| 3 | 小红 | 20 | 2 | female | 2 | 财务部 |
| 6 | 小王 | 23 | 4 | male | 4 | 运营部 |
| 7 | 小华 | 52 | 5 | male | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 3 | 行政部 |
+------+--------+------+--------+--------+------+-----------+
8 rows in set (0.00 sec)
注意，MySQL中并没有全外连接的FULL JOIN语法，而是借助UNION语句实现。
这里复习下union和union all的区别，union具有去重功能。
使用连表查询示例：
-- 找出年龄大于20岁的员工及员工所在部门
SELECT emp.name,emp.age,dep.name
FROM employee AS emp INNER JOIN department AS dep
ON emp.dep_id=dep.id
WHERE emp.age>20;
-- 查询世界上人口数量小于100人的城市名和国家名
SELECT country.name,city.name,city.population
FROM city INNER JOIN country
ON city.countrycode=country.code
WHERE city.population<100;
-- 查询城市shenyang的城市人口，所在国家(name)及国土面积(surfacearea)
SELECT city.name,city.population,country.name,country.surfacearea
FROM city INNER JOIN country
ON city.countrycode=country.code
WHERE city.name='shenyang';
子查询
子查询是将一个查询语句嵌套再另一个查询语句中的查询方式：
- 子查询的内层查询结果，可以作为外层查询语句提供查询条件。
- 子查询中可以包含
IN、NOT IN、AND、ALL、EXISTS、NOT EXISTS等关键字。 - 子查询中还可以包含比较运算符，如
=、!=、>、<等。
来看示例：
-- 查询平均年龄在20以上的部门名称
SELECT NAME
FROM department
WHERE id IN (
SELECT dep_id
FROM employee
GROUP BY dep_id
HAVING AVG(age) > 20);
-- 查询财务部员工姓名
SELECT NAME
FROM employee
WHERE dep_id IN (
SELECT id
FROM department
WHERE NAME='财务部');
-- 查询所有大于平均年龄的员工的年龄和姓名
SELECT NAME,age
FROM employee
WHERE age > (
SELECT AVG(age) FROM employee);
查询中别名的应用
虽然之前也在使用别名，这里再次回顾下别名相关的知识。
别名共分为两类：
- 表别名
- 列别名
先来看列别名：
-- 使用别名之前
SELECT city.name,city.population,country.name,country.surfacearea
FROM city INNER JOIN country
ON city.countrycode=country.code
WHERE city.name='shenyang';
+----------+------------+-------+-------------+
| name | population | name | surfacearea |
+----------+------------+-------+-------------+
| Shenyang | 4265200 | China | 9572900.00 |
+----------+------------+-------+-------------+
1 row in set (0.00 sec)
-- 使用别名之后
SELECT
city.name AS 城市名,
city.population AS 城市人口,
country.name AS 国家名,
country.surfacearea AS 国土面积
FROM city INNER JOIN country
ON city.countrycode=country.code
WHERE city.name='shenyang';
+-----------+--------------+-----------+--------------+
| 城市名 | 城市人口 | 国家名 | 国土面积 |
+-----------+--------------+-----------+--------------+
| Shenyang | 4265200 | China | 9572900.00 |
+-----------+--------------+-----------+--------------+
1 row in set (0.01 sec)
列别名的优点就是自定义返回的字段名称，主要是显式好看些！不过不推荐如上文中使用中文，可能会出现意外情况！
当然，AS也可以省略不写，效果一样的：
SELECT
city.name 城市名,
city.population 城市人口,
country.name 国家名,
country.surfacearea 国土面积
FROM city INNER JOIN country
ON city.countrycode=country.code
WHERE city.name='shenyang';
+-----------+--------------+-----------+--------------+
| 城市名 | 城市人口 | 国家名 | 国土面积 |
+-----------+--------------+-----------+--------------+
| Shenyang | 4265200 | China | 9572900.00 |
+-----------+--------------+-----------+--------------+
1 row in set (0.00 sec)
再来看表别名：
SELECT a.name,a.population,b.name,b.surfacearea
FROM city AS a INNER JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';
-- 表别名和列别名一起使用
SELECT
a.name AS 城市名,
a.population AS 城市人口,
b.name AS 国家名,
b.surfacearea AS 国土面积
FROM city AS a INNER JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';
别名的优点就是简化了编写SQL时的复杂性，因为有的表名和列名又臭又长！
that's all,see also:
