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:

mysql四-2:多表查询 | 老男孩-标杆班级-MySQL-lesson03-SQL基础应用