多表连接查询
多表连接查询
多表连接，就是将几张表拼接为一张表，然后进行查询，先来看基本语法：
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)
外连接之右连接
跟左连接正好相反，右连接(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时的复杂性，因为有的表名和列名又臭又长！
