MySQL多表查询

1. 什么是多表查询和笛卡尔积

多表查询的作用在于我们将多个不同的表中的列组合起来，查到我们想要的数据。

多表查询的分类：

• 内连接
• 外连接

笛卡尔积指的是两个集合的乘积
例如有两个集合A和B

A={1,2}
B={3,4,5}

则A*B的集合为
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };

现有如下两张表class和course

MariaDB [lxr]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |    1 |
|  2 | cym  |   27 |
|  3 | lxr  |   29 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [lxr]> select * from course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | shuxue      |
|  2 | yuwen       |
|  3 | yingyu      |
|  4 | wuli        |
+----+-------------+
4 rows in set (0.000 sec)

//它们的笛卡尔积组合如下
MariaDB [lxr]> select * from student,course;
+----+------+------+----+-------------+
| id | name | age  | id | course_name |
+----+------+------+----+-------------+
|  1 | tom  |    1 |  1 | shuxue      |
|  2 | cym  |   27 |  1 | shuxue      |
|  3 | lxr  |   29 |  1 | shuxue      |
|  1 | tom  |    1 |  2 | yuwen       |
|  2 | cym  |   27 |  2 | yuwen       |
|  3 | lxr  |   29 |  2 | yuwen       |
|  1 | tom  |    1 |  3 | yingyu      |
|  2 | cym  |   27 |  3 | yingyu      |
|  3 | lxr  |   29 |  3 | yingyu      |
|  1 | tom  |    1 |  4 | wuli        |
|  2 | cym  |   27 |  4 | wuli        |
|  3 | lxr  |   29 |  4 | wuli        |
+----+------+------+----+-------------+
12 rows in set (0.001 sec)

2. 内连接

使用 inner join 或者 join 关键字连接两张表，并使用 on 子句来设置连接条件，连接条件必须在左表中与右表中相同最终才会保留结果,否则不保留

显式内连接
两张表
语法：select <字段名> from <表1>inner join <表2> on 表1.字段 = 表2.字段

三张表
语法：select <字段名> (表1 join 表2 on 表1.字段=表2.字段) join 表3 ON 表1.字段=表3.字段

//有如下三张表
MariaDB [lxr]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |    1 |
|  2 | cym  |   27 |
|  3 | lxr  |   29 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [lxr]> select * from course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | shuxue      |
|  2 | yuwen       |
|  3 | yingyu      |
|  4 | wuli        |
+----+-------------+
4 rows in set (0.000 sec)

MariaDB [lxr]> select * from class;
+----+------+------------+
| id | name | class_name |
+----+------+------------+
|  1 | tom  |          8 |
|  2 | cym  |         10 |
+----+------+------------+
2 rows in set (0.000 sec)

//两张表联合查询
MariaDB [lxr]> select * from student s inner join course c on s.id = c.id;
+----+------+------+----+-------------+
| id | name | age  | id | course_name |
+----+------+------+----+-------------+
|  1 | tom  |    1 |  1 | shuxue      |
|  2 | cym  |   27 |  2 | yuwen       |
|  3 | lxr  |   29 |  3 | yingyu      |
+----+------+------+----+-------------+
3 rows in set (0.001 sec)

MariaDB [lxr]> select s.name,c.course_name from student s inner join course c on s.id = c.id;
+------+-------------+
| name | course_name |
+------+-------------+
| tom  | shuxue      |
| cym  | yuwen       |
| lxr  | yingyu      |
+------+-------------+
3 rows in set (0.001 sec)

//三张表联合查询
MariaDB [lxr]> select * from (student s join class c on s.id = c.id) join course u on s.id = u.id;
+----+------+------+----+------+------------+----+-------------+
| id | name | age  | id | name | class_name | id | course_name |
+----+------+------+----+------+------------+----+-------------+
|  1 | tom  |    1 |  1 | tom  |          8 |  1 | shuxue      |
|  2 | cym  |   27 |  2 | cym  |         10 |  2 | yuwen       |
+----+------+------+----+------+------------+----+-------------+

//如果表名较长，可以给表设置别名，这样就可以直接在 select 后写 表的别名.列名
//上面的s和c就是student和course的别名

隐式内连接
隐式内连接：看不到 join 关键字。
语法：select <字段名> from <表1>,<表2> where 表1.字段 = 表2.字段

MariaDB [lxr]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |    1 |
|  2 | cym  |   27 |
|  3 | lxr  |   29 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [lxr]> select * from class;
+----+------+------------+
| id | name | class_name |
+----+------+------------+
|  1 | tom  |          8 |
|  2 | cym  |         10 |
+----+------+------------+
2 rows in set (0.000 sec)

MariaDB [lxr]> select * from student s,class c where s.id = c.id;
+----+------+------+----+------+------------+
| id | name | age  | id | name | class_name |
+----+------+------+----+------+------------+
|  1 | tom  |    1 |  1 | tom  |          8 |
|  2 | cym  |   27 |  2 | cym  |         10 |
+----+------+------+----+------+------------+
2 rows in set (0.001 sec)

3. 外连接

3.1 左外连接

以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL。

它使用 left outer join 关键字连接两个表，也可以简写为 left join，并使用 on 设置连接条件。

语法：select <字段名> from <表1> left join <表2> on 表1.字段 = 表2.字段

上述语法中，表1为基主表，表2 为参考表，左连接查询时，可以查询到 表1 中的所有记录和 表2 中匹配连接条件的记录。如果 表1 的某行在 表2 中没有匹配行，那么在返回结果中，表2 的字段值均为空值 NULL。

MariaDB [lxr]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |    1 |
|  2 | cym  |   27 |
|  3 | lxr  |   29 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [lxr]> select * from class;
+----+------+------------+
| id | name | class_name |
+----+------+------------+
|  1 | tom  |          8 |
|  2 | cym  |         10 |
+----+------+------------+
2 rows in set (0.001 sec)

MariaDB [lxr]> select * from student s left join class c on s.id = c.id;
+----+------+------+------+------+------------+
| id | name | age  | id   | name | class_name |
+----+------+------+------+------+------------+
|  1 | tom  |    1 |    1 | tom  |          8 |
|  2 | cym  |   27 |    2 | cym  |         10 |
|  3 | lxr  |   29 | NULL | NULL |       NULL |
+----+------+------+------+------+------------+

3.2 右外连接

右外连接是左外连接的反向连接，使用 right outer join 关键字连接两个表，可以简化为 right join ，并使用 on 子句来设置连接条件。

右表不管能不能匹配上条件，最终都会保留。能匹配，正确的保留； 若不能匹配,左表的字段都置NULL。

语法：select <字段名> from <表1> right join <表2> on 表1.字段 = 表2.字段

MariaDB [lxr]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |    1 |
|  2 | cym  |   27 |
|  3 | lxr  |   29 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [lxr]> select * from course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | shuxue      |
|  2 | yuwen       |
|  3 | yingyu      |
|  4 | wuli        |
+----+-------------+

MariaDB [lxr]> select * from student s right join course c on s.id = c.id;
+------+------+------+----+-------------+
| id   | name | age  | id | course_name |
+------+------+------+----+-------------+
|    1 | tom  |    1 |  1 | shuxue      |
|    2 | cym  |   27 |  2 | yuwen       |
|    3 | lxr  |   29 |  3 | yingyu      |
| NULL | NULL | NULL |  4 | wuli        |
+------+------+------+----+-------------+
4 rows in set (0.001 sec)

与左连接相反，右连接以 表2 为主表，表1 为参考表，可以查询出 表2 中所有记录和 表1 中匹配连接条件的记录。如果 表2 的某行在 表1 中没有匹配项，那么在返回结果中，表1 的字段值均为 NULL。

4. 用图来理解多表查询

内连接

左外连接

右外连接

笛卡尔积