MySQL数据库—多表查询

绪论

多表查询顾名思义就是数据同时从多张表中获取，查询语句同时涉及到多张表，多表查询有多种语法，即：交叉连接，内连接，左外连接，右外连接，联合查询

现有如下一个数据库：

mysql> select * from class;
+----+-----------+
| id | classname |
+----+-----------+
|  1 | python    |
|  2 | java      |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+--------------+------+-------+----------+
| id | name         | sex  | score | class_id |
+----+--------------+------+-------+----------+
|  1 | 叶无道       | 男   |    98 |        1 |
|  2 | 慕容雪痕     | 女   |    95 |        2 |
|  3 | 苏惜水       | 女   |    85 |        1 |
|  4 | 燕清舞       | 女   |    85 |        2 |
+----+--------------+------+-------+----------+
4 rows in set (0.00 sec)

现针对这个数据库进行讲解多表查询

交叉连接

格式如下：

select  字段1,字段2... from 表1,表2... [where 条件]

如果不加条件直接进行查询，则会出现以下效果，这种结果我们称之为交叉连接（笛卡尔乘积）

例如：

mysql> select * from student,class;
+----+--------------+------+-------+----------+----+-----------+
| id | name         | sex  | score | class_id | id | classname |
+----+--------------+------+-------+----------+----+-----------+
|  1 | 叶无道       | 男   |    98 |        1 |  1 | python    |
|  1 | 叶无道       | 男   |    98 |        1 |  2 | java      |
|  2 | 慕容雪痕     | 女   |    95 |        2 |  1 | python    |
|  2 | 慕容雪痕     | 女   |    95 |        2 |  2 | java      |
|  3 | 苏惜水       | 女   |    85 |        1 |  1 | python    |
|  3 | 苏惜水       | 女   |    85 |        1 |  2 | java      |
|  4 | 燕清舞       | 女   |    85 |        2 |  1 | python    |
|  4 | 燕清舞       | 女   |    85 |        2 |  2 | java      |
+----+--------------+------+-------+----------+----+-----------+
8 rows in set (0.29 sec)

交叉连接还可以用如下方式：

mysql> select * from student cross join class;

如果我们对多张表使用交叉连接查询，而且每张表数据非常多，可以想象，得到结果的时间可能会非常长或者结果没有什么太大的意义。所以通过交叉连接进行多表查询这种方法并不常用，而且应该尽量避免使用

内连接

内连接可以理解成两张表中同时符合某种条件的数据记录的组合，与交叉连接的不同之处在于内连接比交叉连接有更多的限制条件

例1：第一种内连接写法

mysql> select student.name,class.classname from student,class where student.class_id=class.id;
+--------------+-----------+
| name         | classname |
+--------------+-----------+
| 叶无道       | python    |
| 慕容雪痕     | java      |
| 苏惜水       | python    |
| 燕清舞       | java      |
+--------------+-----------+
4 rows in set (0.00 sec)

例2：第二种内连接写法

mysql> select student.name,class.classname from student inner join class on student.class_id=class.id;
+--------------+-----------+
| name         | classname |
+--------------+-----------+
| 叶无道       | python    |
| 慕容雪痕     | java      |
| 苏惜水       | python    |
| 燕清舞       | java      |
+--------------+-----------+
4 rows in set (0.00 sec)

左外连接

左边表中的数据优先全部显示

例如：

mysql> select class.classname,student.name from class left join student on student.class_id=class.id;
+-----------+--------------+
| classname | name         |
+-----------+--------------+
| python    | 叶无道       |
| java      | 慕容雪痕     |
| python    | 苏惜水       |
| java      | 燕清舞       |
| c         | NULL         |
+-----------+--------------+
5 rows in set (0.00 sec)

表A left join 表B：

表A与表B匹配的行会出现在结果集中，外加表A中独有的数据，未对应的数据使用null填充

右外连接

右边表中的数据优先全部显示

例如：

mysql> select class.classname,student.name from student right join class on student.class_id=class.id;
+-----------+--------------+
| classname | name         |
+-----------+--------------+
| python    | 叶无道       |
| java      | 慕容雪痕     |
| python    | 苏惜水       |
| java      | 燕清舞       |
| c         | NULL         |
+-----------+--------------+
5 rows in set (0.00 sec)

表A right join 表B

表A与表B匹配的行会出现在结果集中，外加表B中独有的数据，未对应的数据使用null填充

联合查询

可以理解为把多个查询语句的查询结果集中在一起显示

例如：

mysql> select name from student union select classname from class;
+--------------+
| name         |
+--------------+
| 叶无道       |
| 慕容雪痕     |
| 苏惜水       |
| 燕清舞       |
| python       |
| java         |
| c            |
+--------------+
7 rows in set (0.00 sec)

注意：

1. 当使用union连接两个查询语句时，两个语句查询的字段数量必须相同，否则无法使用union进行联合查询
2. 使用union将两个结果集集中显示时，重复的数据会被合并为一条
3. 使用union all进行联合查询时，重复的数据会展示出来

mysql> select name from student union select * from class;
ERROR 1222 (21000): The used SELECT statements have a different number of columns