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. 用图来理解多表查询
内连接
左外连接
右外连接
笛卡尔积