MySQL的多表查询_mysql多表查询语句

MySQL的多表查询

什么是多表联合查询

多表查询就是同时查询两个或两个以上的表。

在 MySQL 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。

交叉连接(CROSS JOIN)

笛卡尔积

交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。

笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。

例如，有 A 和 B 两个集合，它们的值如下：

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

集合 A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
1
2

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

并且，从以上结果我们可以看出：

• 两个集合相乘，不满足交换率，即 A×B≠B×A。
• A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。

多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。

在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。

交叉连接

交叉连接的语法格式如下：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
1

或

SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
1

语法说明如下：

• 字段名：需要查询的字段名称。
• <表1><表2>：需要交叉连接的表名。
• WHERE 子句：用来设置交叉连接的查询条件。

注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

交叉连接可以查询两个或两个以上的表，为了更好的理解，我们就讲解两个表的交叉连接查询。

例 1

查询学生信息表和科目信息表，并得到一个笛卡尔积。

为了方便观察学生信息表和科目表交叉连接后的运行结果，我们先分别查询出这两个表的数据，再进行交叉连接查询。

1）查询 tb_students_info 表中的数据，SQL 语句和运行结果如下：

mysql> create database school;
Query OK, 1 row affected (0.01 sec)

mysql> use school;
Database changed
mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(30),age tinyint,sex varchar(6),height int,course_id int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_students_info;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(30) | YES  |     | NULL    |                |
| age       | tinyint(4)  | YES  |     | NULL    |                |
| sex       | varchar(6)  | YES  |     | NULL    |                |
| height    | int(11)     | YES  |     | NULL    |                |
| course_id | int(11)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> insert tb_students_info(name,age,sex,height,course_id) values('tom',22,'male',172,1),('mary',25,'female',170,4),('lily',23,'male',170,2),('jim',26,'male',176,1),('henry',24,'male',180,3),('sean',39,'male',178,3),('lisi',25,'female',176,2),('lisa',24,'male',181,2),('green',22,'female',188,1),('jerry',29,'male',175,3);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb_students_info;
+----+-------+------+--------+--------+-----------+
| id | name  | age  | sex    | height | course_id |
+----+-------+------+--------+--------+-----------+
|  1 | tom   |   22 | male   |    172 |         1 |
|  2 | mary  |   25 | female |    170 |         4 |
|  3 | lily  |   23 | male   |    170 |         2 |
|  4 | jim   |   26 | male   |    176 |         1 |
|  5 | henry |   24 | male   |    180 |         3 |
|  6 | sean  |   39 | male   |    178 |         3 |
|  7 | lisi  |   25 | female |    176 |         2 |
|  8 | lisa  |   24 | male   |    181 |         2 |
|  9 | green |   22 | female |    188 |         1 |
| 10 | jerry |   29 | male   |    175 |         3 |
+----+-------+------+--------+--------+-----------+
10 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

2）查询 tb_course 表中的数据，SQL 语句和运行结果如下：

mysql> create table tb_course(id int not null primary key auto_increment,course_name varchar(30));Query OK, 0 rows affected (0.07 sec)

mysql> desc tb_course;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(30) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+
1
2
3
4
5
6
7
8