MySQL多表查询
一、什么是多表查询
MySQL多表查询是指在MySQL数据库中,通过使用多个表来执行查询操作。它允许您在多个表之间建立关联,并根据这些关联来检索数据。通过多表查询,您可以根据不同表之间的关系来获取更复杂的查询结果。例如,您可以使用多表查询来获取两个表中的匹配行,或者根据一个表中的条件过滤另一个表中的数据。多表查询是数据库中非常常见和重要的操作之一。
多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种
分组查询可以看我之前的文档 文档链接
现在有两张表:
student表和class表
mysql> select * from class
-> ;
+----+--------+
| id | name |
+----+--------+
| 1 | java |
| 2 | python |
| 3 | C++ |
| 4 | html |
+----+--------+
4 rows in set (0.00 sec)
mysql> select * from student;
+----+------+------+------+------+
| id | name | age | cid | sex |
+----+------+------+------+------+
| 1 | lxy | 18 | 4 | 男 |
| 2 | lc | 21 | 3 | 男 |
| 3 | gxj | 22 | 1 | 女 |
| 4 | zmq | 21 | 2 | 男 |
| 5 | xz | 32 | NULL | 女 |
| 6 | wjw | 22 | 3 | 男 |
+----+------+------+------+------+
二、交叉连接(cross join)
交叉连接:cross join,不需要连接条件的连接
- 交叉连接产生的结果就是笛卡尔积
- 左表的每一条记录都会与右表的所有记录连接并保留
- 交叉连接没有实际数据价值,只是丰富了连接查询的完整性
示例
//交叉连接产生的结果就是笛卡尔积,这个结果是混乱的,无意义的。应尽量避免使用交叉链接
mysql> select * from student cross join class;
+----+------+------+------+------+----+--------+
| id | name | age | cid | sex | id | name |
+----+------+------+------+------+----+--------+
| 1 | lxy | 18 | 4 | 男 | 1 | java |
| 1 | lxy | 18 | 4 | 男 | 2 | python |
| 1 | lxy | 18 | 4 | 男 | 3 | C++ |
| 1 | lxy | 18 | 4 | 男 | 4 | html |
| 2 | lc | 21 | 3 | 男 | 1 | java |
| 2 | lc | 21 | 3 | 男 | 2 | python |
| 2 | lc | 21 | 3 | 男 | 3 | C++ |
| 2 | lc | 21 | 3 | 男 | 4 | html |
| 3 | gxj | 22 | 1 | 女 | 1 | java |
| 3 | gxj | 22 | 1 | 女 | 2 | python |
| 3 | gxj | 22 | 1 | 女 | 3 | C++ |
| 3 | gxj | 22 | 1 | 女 | 4 | html |
| 4 | zmq | 21 | 2 | 男 | 1 | java |
| 4 | zmq | 21 | 2 | 男 | 2 | python |
| 4 | zmq | 21 | 2 | 男 | 3 | C++ |
| 4 | zmq | 21 | 2 | 男 | 4 | html |
| 5 | xz | 32 | NULL | 女 | 1 | java |
| 5 | xz | 32 | NULL | 女 | 2 | python |
| 5 | xz | 32 | NULL | 女 | 3 | C++ |
| 5 | xz | 32 | NULL | 女 | 4 | html |
| 6 | wjw | 22 | 3 | 男 | 1 | java |
| 6 | wjw | 22 | 3 | 男 | 2 | python |
| 6 | wjw | 22 | 3 | 男 | 3 | C++ |
| 6 | wjw | 22 | 3 | 男 | 4 | html |
+----+------+------+------+------+----+--------+
24 rows in set (0.00 sec)
三、内连接
内连接:[inner] join,将两张表根据指定的条件连接起来,严格连接
- 内连接是将一张表的每一条记录去另外一张表根据条件匹配
- 匹配成功:保留连接的数据
- 匹配失败:都不保留
- 内连接语法:
左表 join 右表 on 连接条件
内连接(INNER JOIN)主要通过设置连接条件的方式,来移除查询结果中某些数据行的交叉连接。简单来说,就是利用条件表达式来消除交叉连接的某些数据行。
内连接使用 INNER JOIN 关键字连接两张表,并使用 ON 子句来设置连接条件。如果没有连接条件,INNER JOIN 和 CROSS JOIN 在语法上是等同的,两者可以互换。
//在student表和class表里面,使用内连接查询学生的姓名和其选修的课程名
mysql> select student.name,class.name from student join class on student.cid = class.id;
+------+--------+
| name | name |
+------+--------+
| lxy | html |
| lc | C++ |
| gxj | java |
| zmq | python |
| wjw | C++ |
+------+--------+
5 rows in set (0.00 sec)
//在student表和class表里面,使用内连接查询选修C++的学生的姓名
mysql> select student.name from student join class on student.cid = class.id where class.id = 3;
+------+
| name |
+------+
| lc |
| wjw |
+------+
2 rows in set (0.00 sec)
在这里的查询语句中,两个表之间的关系通过 INNER JOIN指定,连接的条件使用ON子句给出。
四、外连接
外连接:outer join,是一种不严格的连接方式
- 外连接分为两种
- 左外连接(左连接):left join
- 右外连接(右连接):right join
- 外连接有主表和从表之分
- 左连接:左表为主表
- 右连接:右表为主表
- 外连接是将主表的记录去匹配从表的记录
- 匹配成功保留
- 匹配失败(全表):也保留,只是从表字段置空
左连接
左外连接又称为左连接,使用 LEFT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
语法
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
示例
//将student作为主表。查询学生的姓名和该学生选修的课程名
mysql> select student.name,class.name from student left join class on student.cid = class.id;
+------+--------+
| name | name |
+------+--------+
| gxj | java |
| zmq | python |
| lc | C++ |
| wjw | C++ |
| lxy | html |
| xz | NULL |
+------+--------+
6 rows in set (0.00 sec)
根据全表数据可以知道,学生xz的cid是空值,正常来说应该不会显示这条记录。但是此处使用的是左连接,student表是主表。所以该条记录只取出了student 表中相应的值,而从 class 表中取出的值为 NULL。
右连接
右外连接又称为右连接,右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
右连接语法格式:
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
示例
//增加一个新数据
mysql> insert class(name) values('go');
Query OK, 1 row affected (0.00 sec)
//将class作为主表。查询学生的姓名和该学生选修的课程名
mysql> select student.name,class.name from student right join class on student.cid = class.id;
+------+--------+
| name | name |
+------+--------+
| lxy | html |
| lc | C++ |
| gxj | java |
| zmq | python |
| wjw | C++ |
| NULL | go |
+------+--------+
6 rows in set (0.00 sec)
根据全表数据可以知道,student表中没有任何一个学生的cid是5,也就是没有任何学生选修了html。正常来说应该不会显示这条记录。但是此处使用的是右连接,class表是主表。所以该条记录只取出了 class 表中相应的值,而从 student 表中取出的值为 NULL。
五、子查询
子查询是 MySQL 中比较常用的查询方法,通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用,而且可以进行多层嵌套。在实际开发时,子查询经常出现在 WHERE 子句中。
子查询在 WHERE 中的语法格式如下:
WHERE <表达式> <操作符> (子查询)
其中,操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时,
返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回值正好相反。
EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空,若子查询的结果集不为空,
返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回的值正好相反。
注意:习惯上,外层的 SELECT 查询称为父查询,圆括号中嵌入的查询称为子查询(子查询必须放在圆括号内)。MySQL 在处理上例的 SELECT 语句时,执行流程为:先执行子查询,再执行父查询。
示例
使用子查询在 student表和 class 表中查询学习 Java 课程的学生姓名
mysql> select student.name from student where cid = (select id from class where class.name = 'jav
a');
+------+
| name |
+------+
| gxj |
+------+
1 row in set (0.00 sec)
//拆分查询过程
//先是进行了括号内的查询语句(子查询语句)
mysql> select id from class where class.name = 'java';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
//这个1就作为父查询语句的条件判断
mysql> select name from student where cid =1;
+------+
| name |
+------+
| gxj |
+------+
1 row in set (0.00 sec)
示例2
在 SELECT 语句中使用 NOT IN 关键字,查询没有学习 Java 课程的学生姓名
(not in <> != 这三个的效果一样)
mysql> select student.name from student where cid not in (select id from class where class.name =
'java');
+------+
| name |
+------+
| lxy |
| lc |
| zmq |
| wjw |
+------+
4 rows in set (0.00 sec)
mysql> select student.name from student where cid != (select id from class where class.name = 'ja
va');
+------+
| name |
+------+
| lxy |
| lc |
| zmq |
| wjw |
+------+
4 rows in set (0.00 sec)
mysql> select student.name from student where cid <> (select id from class where class.name = 'ja
va');
+------+
| name |
+------+
| lxy |
| lc |
| zmq |
| wjw |
+------+
4 rows in set (0.00 sec)
EXISTS 关键字可以和其它查询条件一起使用,条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。
示例3
查询 class 表中是否存在 id=3 的课程,如果存在,就查询出 student 表中 age 字段大于 20的记录
mysql> select * from student where age > 24 and exists(select class.name from class where id = 1)
;
+----+------+------+------+------+
| id | name | age | cid | sex |
+----+------+------+------+------+
| 5 | xz | 32 | NULL | 女 |
+----+------+------+------+------+
1 row in set (0.00 sec)