MySQL 实验 11：数据查询（4）—— 连接查询与子查询

最新推荐文章于 2025-04-07 11:12:25 发布
最新推荐文章于 2025-04-07 11:12:25 发布
阅读量1.9k 收藏 32
点赞数 26
分类专栏： MySQL基本操作 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44377973/article/details/142758772
版权

MySQL 实验 11：数据查询（4）—— 连接查询与子查询

目录

连接查询是从多张表中查询数据，通常进行连接的两张表中的数据具有一对多的联系，依靠子表中的外键和对应的父表中的主键建立关联条件。

一、连接查询的语法格式

MySQL 连接查询的语法格式如下：

-- 格式一：
FROM 表1 [[AS] 别名] [INNER|left outer|right outer] JOIN 表2 [AS] 别名 ON 表1.外键 = 表2.主键
[ [INNER|left outer|right outer] JOIN 表3 [AS] 别名 ON 表2.外键 = 表3.主键 ...]

-- 格式二：
FROM 表1 [AS] 别名, 表2 [AS] 别名, 表3 [AS] 别名,... 
WHERE 表1.外键 = 表2.主键 and 表2.外键 = 表3.主键 ...

--说明：
（1）连接类型：[inner] join|（内连接），left [outer] join（左连接），right [outer] join（右连接）。
（2）第一种格式如果省略连接类型则默认为内连接。
（3）第二种格式的连接类型为内连接。

二、两张表的连接查询

两张表进行连接查询格式如下：

-- 格式一：
FROM 表1 [[AS] 别名] [INNER|left [outer]|right [outer] JOIN 表2 [AS] 别名 
ON 表1.外键 = 表2.主键

-- 格式二：
FROM 表1 [AS] 别名, 表2 [AS] 别名
WHERE 表1.外键 = 表2.主键 and 筛选条件

在 dept 表中添加一条新记录。内容如下：

mysql> insert into dept values('D05','机电学院','0373-3025666','3号教学楼321');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+---------+--------------+--------------+----------------+
| dept_id | dept_name    | phone        | address        |
+---------+--------------+--------------+----------------+
| D01     | 经济学院     | 0373-3025111 | 10号教学楼1101 |
| D02     | 管理学院     | 0373-3025122 | 12号教学楼602  |
| D03     | 生命科技学院 | 0373-3025133 | 8号教学楼204   |
| D04     | 农学院       | 0373-3025144 | 7号教学楼612   |
| D05     | 机电学院     | 0373-3025666 | 3号教学楼321   |
+---------+--------------+--------------+----------------+
5 rows in set (0.00 sec)

在 stu 表中添加一条新记录。内容如下：

mysql> insert into stu values('20230244101','李刚','男','2004-12-8','13735214567',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124001 | 薛智玲 | 男     | 2000-01-15 | 13637358097 | D01     |
| 20220124002 | 杨铭华 | 男     | 2001-03-06 | 13637381407 | D01     |
| 20220124003 | 张从超 | 女     | 2003-12-09 | 13637389129 | D01     |
| 20220124004 | 孙金航 | 女     | 2003-03-07 | 13637387244 | D01     |
| 20220214001 | 谭宝名 | 男     | 2002-08-05 | 13637332439 | D02     |
| 20220214002 | 赵文瀚 | 女     | 2001-03-25 | 13637381710 | D02     |
| 20220214003 | 裴天馨 | 女     | 2003-04-15 | 13637381374 | D02     |
| 20220325101 | 董雯林 | 男     | 2003-12-27 | 13637357085 | D03     |
| 20220325102 | 周宜可 | 男     | 2002-07-04 | 13637333898 | D03     |
| 20220325103 | 张春晶 | 男     | 2001-06-21 | 13637340698 | D03     |
| 20220410101 | 夏飞研 | 女     | 2002-09-02 | 13637326693 | D04     |
| 20220410102 | 聂小龙 | 男     | 2002-12-04 | 13637331432 | D04     |
| 20220410103 | 赵晨祥 | 女     | 2002-05-22 | 13637388564 | D04     |
| 20220410104 | 江沐洪 | 男     | 2002-02-12 | 13637345052 | D04     |
| 20230244101 | 李刚   | 男     | 2004-12-08 | 13735214567 | NULL    |
+-------------+--------+--------+------------+-------------+---------+
15 rows in set (0.00 sec)
1、内连接（JOIN | INNER JOIN）

查询结果中包含两张表的公共字段（子表的外键和父表的主键）的值相等的记录。

连接查询时如果需要使用的列在多个表中出现，需要在列名前面加上表名加以限定，即采用【表名.列名】的形式。

内连接查询有两种格式：

格式一：表之间用 INNER JOIN 或 JOIN 表示，连接条件放在 ON 后面。

格式二：表之间用逗号隔开，连接条件放在 WHERE 后面。

例如：查询每个学生的 s_id，s_name，phone 以及其所在学院的编号（dept_id）与名称（dept_name）

-- 其中 phone 和 dept_id 两个列必须添加表名，否则会报错：ERROR 1052 (23000): Column '***' in field list is ambiguous
select s_id, s_name, stu.phone, dept.dept_id, dept_name
from stu join dept on stu.dept_id = dept.dept_id;

-- 查询结果如下：
mysql> select s_id, s_name, stu.phone, dept.dept_id, dept.dept_name
    -> from stu join dept on stu.dept_id = dept.dept_id;
+-------------+--------+-------------+---------+--------------+
| s_id        | s_name | phone       | dept_id | dept_name    |
+-------------+--------+-------------+---------+--------------+
| 20220124001 | 薛智玲 | 13637358097 | D01     | 经济学院     |
| 20220124002 | 杨铭华 | 13637381407 | D01     | 经济学院     |
| 20220124003 | 张从超 | 13637389129 | D01     | 经济学院     |
| 20220124004 | 孙金航 | 13637387244 | D01     | 经济学院     |
| 20220214001 | 谭宝名 | 13637332439 | D02     | 管理学院     |
| 20220214002 | 赵文瀚 | 13637381710 | D02     | 管理学院     |
| 20220214003 | 裴天馨 | 13637381374 | D02     | 管理学院     |
| 20220325101 | 董雯林 | 13637357085 | D03     | 生命科技学院 |
| 20220325102 | 周宜可 | 13637333898 | D03     | 生命科技学院 |
| 20220325103 | 张春晶 | 13637340698 | D03     | 生命科技学院 |
| 20220410101 | 夏飞研 | 13637326693 | D04     | 农学院       |
| 20220410102 | 聂小龙 | 13637331432 | D04     | 农学院       |
| 20220410103 | 赵晨祥 | 13637388564 | D04     | 农学院       |
| 20220410104 | 江沐洪 | 13637345052 | D04     | 农学院       |
+-------------+--------+-------------+---------+--------------+
14 rows in set (0.00 sec)

以上的查询可以为表指定别名，代码如下：

-- 说明：一旦为表指定了别名，在所有使用表名的地方必须替换为别名
select s_id, s_name, s.phone, d.dept_id, dept_name
from stu s join dept d on s.dept_id = d.dept_id;

-- 查询结果如下：
mysql> select s_id, s_name, s.phone, d.dept_id, dept_name
    -> from stu s join dept d on s.dept_id = d.dept_id;
+-------------+--------+-------------+---------+--------------+
| s_id        | s_name | phone       | dept_id | dept_name    |
+-------------+--------+-------------+---------+--------------+
| 20220124001 | 薛智玲 | 13637358097 | D01     | 经济学院     |
| 20220124002 | 杨铭华 | 13637381407 | D01     | 经济学院     |
| 20220124003 | 张从超 | 13637389129 | D01     | 经济学院     |
| 20220124004 | 孙金航 | 13637387244 | D01     | 经济学院     |
| 20220214001 | 谭宝名 | 13637332439 | D02     | 管理学院     |
| 20220214002 | 赵文瀚 | 13637381710 | D02     | 管理学院     |
| 20220214003 | 裴天馨 | 13637381374 | D02     | 管理学院     |
| 20220325101 | 董雯林 | 13637357085 | D03     | 生命科技学院 |
| 20220325102 | 周宜可 | 13637333898 | D03     | 生命科技学院 |
| 20220325103 | 张春晶 | 13637340698 | D03     | 生命科技学院 |
| 20220410101 | 夏飞研 | 13637326693 | D04     | 农学院       |
| 20220410102 | 聂小龙 | 13637331432 | D04     | 农学院       |
| 20220410103 | 赵晨祥 | 13637388564 | D04     | 农学院       |
| 20220410104 | 江沐洪 | 13637345052 | D04     | 农学院       |
+-------------+--------+-------------+---------+--------------+
14 rows in set (0.00 sec)
2、外连接

外连接包括左连接（LEFT OUTER JOIN | LEFT JOIN）、右连接（RIGHT OUTER JOIN | RIGHT JOIN）与完全连接（FULL OUTER JOIN，MySQL不支持）。各种外连接的含义如下：

左连接：结果集中包括满足指定连接条件的行，还显示 JOIN 关键字左侧表中所有满足检索条件的行，如果左表的某行在右表中没有匹配行，则在结果中，右表所有选择的列均为 NULL。

右连接：是左连接的反向连接。

完全连接：完全连接的查询结果包括两表内连接的结果和左表与右表中不满足条件的行。

（1）利用 stu 表与 dept 表查询所有学生的 s_id，s_name，phone 以及其所在学院的编号（dept_id）与名称（dept_name）——包括 stu 表中 dept_id 为空的记录

select s_id, s_name, s.phone, d.dept_id, dept_name
from stu s left join dept d on s.dept_id = d.dept_id;

-- 查询结果如下：
mysql> select s_id, s_name, s.phone, d.dept_id, dept_name
    -> from stu s left join dept d on s.dept_id = d.dept_id;
+-------------+--------+-------------+---------+--------------+
| s_id        | s_name | phone       | dept_id | dept_name    |
+-------------+--------+-------------+---------+--------------+
| 20220124001 | 薛智玲 | 13637358097 | D01     | 经济学院     |
| 20220124002 | 杨铭华 | 13637381407 | D01     | 经济学院     |
| 20220124003 | 张从超 | 13637389129 | D01     | 经济学院     |
| 20220124004 | 孙金航 | 13637387244 | D01     | 经济学院     |
| 20220214001 | 谭宝名 | 13637332439 | D02     | 管理学院     |
| 20220214002 | 赵文瀚 | 13637381710 | D02     | 管理学院     |
| 20220214003 | 裴天馨 | 13637381374 | D02     | 管理学院     |
| 20220325101 | 董雯林 | 13637357085 | D03     | 生命科技学院 |
| 20220325102 | 周宜可 | 13637333898 | D03     | 生命科技学院 |
| 20220325103 | 张春晶 | 13637340698 | D03     | 生命科技学院 |
| 20220410101 | 夏飞研 | 13637326693 | D04     | 农学院       |
| 20220410102 | 聂小龙 | 13637331432 | D04     | 农学院       |
| 20220410103 | 赵晨祥 | 13637388564 | D04     | 农学院       |
| 20220410104 | 江沐洪 | 13637345052 | D04     | 农学院       |
| 20230244101 | 李刚   | 13735214567 | NULL    | NULL         |
+-------------+--------+-------------+---------+--------------+
15 rows in set (0.00 sec)

（2）利用 stu 表与 dept 表查询所有学院对应的学生的 s_id，s_name，phone 以及学院的编号（dept_id）与名称（dept_name）——包括 dept 表中没有对应学生的学院信息

select s_id, s_name, s.phone, d.dept_id, dept_name
from stu s right join dept d on s.dept_id = d.dept_id;

-- 查询结果如下：
mysql> select s_id, s_name, s.phone, d.dept_id, dept_name
    -> from stu s right join dept d on s.dept_id = d.dept_id;
+-------------+--------+-------------+---------+--------------+
| s_id        | s_name | phone       | dept_id | dept_name    |
+-------------+--------+-------------+---------+--------------+
| 20220124001 | 薛智玲 | 13637358097 | D01     | 经济学院     |
| 20220124002 | 杨铭华 | 13637381407 | D01     | 经济学院     |
| 20220124003 | 张从超 | 13637389129 | D01     | 经济学院     |
| 20220124004 | 孙金航 | 13637387244 | D01     | 经济学院     |
| 20220214001 | 谭宝名 | 13637332439 | D02     | 管理学院     |
| 20220214002 | 赵文瀚 | 13637381710 | D02     | 管理学院     |
| 20220214003 | 裴天馨 | 13637381374 | D02     | 管理学院     |
| 20220325101 | 董雯林 | 13637357085 | D03     | 生命科技学院 |
| 20220325102 | 周宜可 | 13637333898 | D03     | 生命科技学院 |
| 20220325103 | 张春晶 | 13637340698 | D03     | 生命科技学院 |
| 20220410101 | 夏飞研 | 13637326693 | D04     | 农学院       |
| 20220410102 | 聂小龙 | 13637331432 | D04     | 农学院       |
| 20220410103 | 赵晨祥 | 13637388564 | D04     | 农学院       |
| 20220410104 | 江沐洪 | 13637345052 | D04     | 农学院       |
| NULL        | NULL   | NULL        | D05     | 机电学院     |
+-------------+--------+-------------+---------+--------------+
15 rows in set (0.00 sec)

三、多个表的连接查询

针对多张表进行连接查询可以这样理解：先进行两张表的连接查询生成一张新表，然后用这张新表再和第三张表进行连接查询，以此类推。语法格式如下：

-- 格式一：
FROM 表1 [[AS] 别名] [INNER|left outer|right outer] JOIN 表2 [AS] 别名 ON 表1.外键 = 表2.主键
[ [INNER|left outer|right outer] JOIN 表3 [AS] 别名 ON 表2.外键 = 表3.主键 ...]

-- 格式二：
FROM 表1 [AS] 别名, 表2 [AS] 别名, 表3 [AS] 别名,... 
WHERE 表1.外键 = 表2.主键 and 表2.外键 = 表3.主键 ...

例如：

（1）利用 stu，course 与 xk 表查询学生选课的详细信息，查询结果包括学生的 s_id，s_name 以及课程的 c_id，c_name 与每门课的考试成绩（score）

格式一：

select s.s_id, s_name, c.c_id, c_name, score
from stu s join xk on s.s_id=xk.s_id
join course c on xk.c_id=c.c_id;

-- 查询结果如下：
mysql> select s.s_id, s_name, c.c_id, c_name, score
    -> from stu s join xk on s.s_id=xk.s_id
    -> join course c on xk.c_id=c.c_id;
+-------------+--------+--------+--------------+-------+
| s_id        | s_name | c_id   | c_name       | score |
+-------------+--------+--------+--------------+-------+
| 20220124001 | 薛智玲 | C01001 | 微观经济学   | 61.00 |
| 20220124002 | 杨铭华 | C01001 | 微观经济学   | 88.00 |
| 20220124003 | 张从超 | C01001 | 微观经济学   | 95.00 |
| 20220124004 | 孙金航 | C01001 | 微观经济学   | 81.00 |
| 20220124001 | 薛智玲 | C01002 | 宏观经济学   | 68.00 |
| 20220124002 | 杨铭华 | C01002 | 宏观经济学   | 69.00 |
| 20220124001 | 薛智玲 | C01003 | 财务管理     | 92.00 |
| 20220124002 | 杨铭华 | C01003 | 财务管理     | 69.00 |
| 20220124003 | 张从超 | C01003 | 财务管理     | 94.00 |
| 20220124004 | 孙金航 | C01003 | 财务管理     | 96.00 |
| 20220214001 | 谭宝名 | C02101 | 企业管理概论 | 80.00 |
| 20220214002 | 赵文瀚 | C02101 | 企业管理概论 | 63.00 |
| 20220214003 | 裴天馨 | C02101 | 企业管理概论 | 72.00 |
| 20220214001 | 谭宝名 | C02102 | 管理学原理   | 80.00 |
| 20220214002 | 赵文瀚 | C02102 | 管理学原理   | 67.00 |
| 20220214003 | 裴天馨 | C02102 | 管理学原理   | 96.00 |
| 20220214001 | 谭宝名 | C02103 | 管理信息系统 | 97.00 |
| 20220214002 | 赵文瀚 | C02103 | 管理信息系统 | 74.00 |
| 20220214003 | 裴天馨 | C02103 | 管理信息系统 | 94.00 |
| 20220325101 | 董雯林 | C03201 | 遗传学       | 83.00 |
| 20220325102 | 周宜可 | C03201 | 遗传学       | 90.00 |
| 20220325103 | 张春晶 | C03201 | 遗传学       | 65.00 |
| 20220325101 | 董雯林 | C03202 | 药物学       | 83.00 |
| 20220325102 | 周宜可 | C03202 | 药物学       | 84.00 |
| 20220325103 | 张春晶 | C03202 | 药物学       | 77.00 |
| 20220325101 | 董雯林 | C03203 | 药理学       | 74.00 |
| 20220325102 | 周宜可 | C03203 | 药理学       | 67.00 |
| 20220325103 | 张春晶 | C03203 | 药理学       | 66.00 |
| 20220410101 | 夏飞研 | C04111 | 农业环境学   | 68.00 |
| 20220410102 | 聂小龙 | C04111 | 农业环境学   | 62.00 |
| 20220410103 | 赵晨祥 | C04111 | 农业环境学   | 64.00 |
| 20220410104 | 江沐洪 | C04111 | 农业环境学   | 64.00 |
| 20220410101 | 夏飞研 | C04112 | 土壤肥料学   | 97.00 |
| 20220410102 | 聂小龙 | C04112 | 土壤肥料学   | 78.00 |
| 20220410103 | 赵晨祥 | C04112 | 土壤肥料学   | 82.00 |
| 20220410104 | 江沐洪 | C04112 | 土壤肥料学   | 75.00 |
| 20220410101 | 夏飞研 | C04113 | 作物学       | 83.00 |
| 20220410102 | 聂小龙 | C04113 | 作物学       | 72.00 |
| 20220410103 | 赵晨祥 | C04113 | 作物学       | 62.00 |
+-------------+--------+--------+--------------+-------+
39 rows in set (0.00 sec)

格式二：

select s.s_id, s_name, c.c_id, c_name, score
from stu s, xk, course c
where s.s_id=xk.s_id and xk.c_id=c.c_id;

-- 查询结果如下：
mysql> select s.s_id, s_name, c.c_id, c_name, score
    -> from stu s, xk, course c
    -> where s.s_id=xk.s_id and xk.c_id=c.c_id;
+-------------+--------+--------+--------------+-------+
| s_id        | s_name | c_id   | c_name       | score |
+-------------+--------+--------+--------------+-------+
| 20220124001 | 薛智玲 | C01001 | 微观经济学   | 61.00 |
| 20220124002 | 杨铭华 | C01001 | 微观经济学   | 88.00 |
| 20220124003 | 张从超 | C01001 | 微观经济学   | 95.00 |
| 20220124004 | 孙金航 | C01001 | 微观经济学   | 81.00 |
| 20220124001 | 薛智玲 | C01002 | 宏观经济学   | 68.00 |
| 20220124002 | 杨铭华 | C01002 | 宏观经济学   | 69.00 |
| 20220124001 | 薛智玲 | C01003 | 财务管理     | 92.00 |
| 20220124002 | 杨铭华 | C01003 | 财务管理     | 69.00 |
| 20220124003 | 张从超 | C01003 | 财务管理     | 94.00 |
| 20220124004 | 孙金航 | C01003 | 财务管理     | 96.00 |
| 20220214001 | 谭宝名 | C02101 | 企业管理概论 | 80.00 |
| 20220214002 | 赵文瀚 | C02101 | 企业管理概论 | 63.00 |
| 20220214003 | 裴天馨 | C02101 | 企业管理概论 | 72.00 |
| 20220214001 | 谭宝名 | C02102 | 管理学原理   | 80.00 |
| 20220214002 | 赵文瀚 | C02102 | 管理学原理   | 67.00 |
| 20220214003 | 裴天馨 | C02102 | 管理学原理   | 96.00 |
| 20220214001 | 谭宝名 | C02103 | 管理信息系统 | 97.00 |
| 20220214002 | 赵文瀚 | C02103 | 管理信息系统 | 74.00 |
| 20220214003 | 裴天馨 | C02103 | 管理信息系统 | 94.00 |
| 20220325101 | 董雯林 | C03201 | 遗传学       | 83.00 |
| 20220325102 | 周宜可 | C03201 | 遗传学       | 90.00 |
| 20220325103 | 张春晶 | C03201 | 遗传学       | 65.00 |
| 20220325101 | 董雯林 | C03202 | 药物学       | 83.00 |
| 20220325102 | 周宜可 | C03202 | 药物学       | 84.00 |
| 20220325103 | 张春晶 | C03202 | 药物学       | 77.00 |
| 20220325101 | 董雯林 | C03203 | 药理学       | 74.00 |
| 20220325102 | 周宜可 | C03203 | 药理学       | 67.00 |
| 20220325103 | 张春晶 | C03203 | 药理学       | 66.00 |
| 20220410101 | 夏飞研 | C04111 | 农业环境学   | 68.00 |
| 20220410102 | 聂小龙 | C04111 | 农业环境学   | 62.00 |
| 20220410103 | 赵晨祥 | C04111 | 农业环境学   | 64.00 |
| 20220410104 | 江沐洪 | C04111 | 农业环境学   | 64.00 |
| 20220410101 | 夏飞研 | C04112 | 土壤肥料学   | 97.00 |
| 20220410102 | 聂小龙 | C04112 | 土壤肥料学   | 78.00 |
| 20220410103 | 赵晨祥 | C04112 | 土壤肥料学   | 82.00 |
| 20220410104 | 江沐洪 | C04112 | 土壤肥料学   | 75.00 |
| 20220410101 | 夏飞研 | C04113 | 作物学       | 83.00 |
| 20220410102 | 聂小龙 | C04113 | 作物学       | 72.00 |
| 20220410103 | 赵晨祥 | C04113 | 作物学       | 62.00 |
+-------------+--------+--------+--------------+-------+
39 rows in set (0.00 sec)

（2）利用 stu，course 与 xk 表查询【赵文瀚】同学【企业管理概论】课程的考试成绩

格式一：

select score
from stu s join xk on s.s_id=xk.s_id
join course c on xk.c_id=c.c_id
where s_name='赵文瀚' and c_name='企业管理概论';

-- 查询结果如下：
mysql> select score
    -> from stu s join xk on s.s_id=xk.s_id
    -> join course c on xk.c_id=c.c_id
    -> where s_name='赵文瀚' and c_name='企业管理概论';
+-------+
| score |
+-------+
| 63.00 |
+-------+
1 row in set (0.00 sec)

格式二：

select score
from stu s, xk, course c
where s.s_id=xk.s_id and xk.c_id=c.c_id and
s_name='赵文瀚' and c_name='企业管理概论';

-- 查询结果如下：
mysql> select score
    -> from stu s, xk, course c
    -> where s.s_id=xk.s_id and xk.c_id=c.c_id and
    -> s_name='赵文瀚' and c_name='企业管理概论';
+-------+
| score |
+-------+
| 63.00 |
+-------+
1 row in set (0.00 sec)

四、自连接查询

所谓自连接就是一张表和自身进行连接查询。进行自连接查询时把一张表看成两张表，使用别名进行区分。

自连接查询格式和普通的连接查询书写格式完全相同，只不过我们需要把一张表想象成两张表使用。自连接查询是自己和自己连接，分别给一张表取两个不同的别名，然后附上连接条件。

自连接查询的语法格式如下：

-- 格式一：
FROM 表1 [AS] 别名1 [INNER | left outer | right outer] JOIN 表2 [AS] 别名2
ON 别名1.列1 = 别名2.列2
-- 格式二：
FROM 表1 [AS] 别名1, 表2 AS [别名2]
WHERE 别名1.列1 = 别名.列2

例如：有下面的表，并输入数据

-- class_president（该列保存的是班长的学号）为外键，与表中的列 s_id 对应
create table student(
    s_id int primary key,
    s_name char(20),
    birth datetime,
    phone char(20),
    class_president int,
    foreign key(class_president) references student(s_id)
);

insert into student values(1101,'张涛','2001-1-23','13637321547',null);
insert into student values(1102,'刘明','2000-8-15','13637322455',null);
insert into student values(1103,'刘刚','2000-12-5','13637321666',1103);
insert into student values(1104,'张丽丽','2002-8-6','13637333366',1103);
insert into student values(1201,'李静伊','1999-8-9','13637377766',null);
insert into student values(1202,'王云飞','2001-11-12','13637355566',1202);
insert into student values(1203,'张家友','1998-10-16','13637335566',1202);
insert into student values(1204,'王光辉','2003-7-12','13637345666',1202);
insert into student values(1205,'张鹏飞','2001-6-9','13637336566',1202);
insert into student values(1301,'刘青云','2000-4-17','13637222666',null);
insert into student values(1302,'赵佳燕','2002-8-11','13637444666',null);
insert into student values(1303,'王军伟','1999-1-22','13637555666',1303);

update student set class_president=1103 where s_id=1101;
update student set class_president=1103 where s_id=1102;
update student set class_president=1202 where s_id=1201;
update student set class_president=1303 where s_id=1301;
update student set class_president=1303 where s_id=1302;

-- 查看 student 表中的数据
mysql> select * from student;
+------+--------+---------------------+-------------+-----------------+
| s_id | s_name | birth               | phone       | class_president |
+------+--------+---------------------+-------------+-----------------+
| 1101 | 张涛   | 2001-01-23 00:00:00 | 13637321547 |            1103 |
| 1102 | 刘明   | 2000-08-15 00:00:00 | 13637322455 |            1103 |
| 1103 | 刘刚   | 2000-12-05 00:00:00 | 13637321666 |            1103 |
| 1104 | 张丽丽 | 2002-08-06 00:00:00 | 13637333366 |            1103 |
| 1201 | 李静伊 | 1999-08-09 00:00:00 | 13637377766 |            1202 |
| 1202 | 王云飞 | 2001-11-12 00:00:00 | 13637355566 |            1202 |
| 1203 | 张家友 | 1998-10-16 00:00:00 | 13637335566 |            1202 |
| 1204 | 王光辉 | 2003-07-12 00:00:00 | 13637345666 |            1202 |
| 1205 | 张鹏飞 | 2001-06-09 00:00:00 | 13637336566 |            1202 |
| 1301 | 刘青云 | 2000-04-17 00:00:00 | 13637222666 |            1303 |
| 1302 | 赵佳燕 | 2002-08-11 00:00:00 | 13637444666 |            1303 |
| 1303 | 王军伟 | 1999-01-22 00:00:00 | 13637555666 |            1303 |
+------+--------+---------------------+-------------+-----------------+
12 rows in set (0.00 sec)

使用自连接查询，查询每个学生的信息以及对应的班长的姓名。代码如下：

select stu.*, cp.s_name cp_name
from student stu join student cp
on stu.class_president = cp.s_id;

-- 查询结果
mysql> select stu.*, cp.s_name cp_name
    -> from student stu join student cp
    -> on stu.class_president = cp.s_id;
+------+--------+---------------------+-------------+-----------------+---------+
| s_id | s_name | birth               | phone       | class_president | cp_name |
+------+--------+---------------------+-------------+-----------------+---------+
| 1101 | 张涛   | 2001-01-23 00:00:00 | 13637321547 |            1103 | 刘刚    |
| 1102 | 刘明   | 2000-08-15 00:00:00 | 13637322455 |            1103 | 刘刚    |
| 1103 | 刘刚   | 2000-12-05 00:00:00 | 13637321666 |            1103 | 刘刚    |
| 1104 | 张丽丽 | 2002-08-06 00:00:00 | 13637333366 |            1103 | 刘刚    |
| 1201 | 李静伊 | 1999-08-09 00:00:00 | 13637377766 |            1202 | 王云飞  |
| 1202 | 王云飞 | 2001-11-12 00:00:00 | 13637355566 |            1202 | 王云飞  |
| 1203 | 张家友 | 1998-10-16 00:00:00 | 13637335566 |            1202 | 王云飞  |
| 1204 | 王光辉 | 2003-07-12 00:00:00 | 13637345666 |            1202 | 王云飞  |
| 1205 | 张鹏飞 | 2001-06-09 00:00:00 | 13637336566 |            1202 | 王云飞  |
| 1301 | 刘青云 | 2000-04-17 00:00:00 | 13637222666 |            1303 | 王军伟  |
| 1302 | 赵佳燕 | 2002-08-11 00:00:00 | 13637444666 |            1303 | 王军伟  |
| 1303 | 王军伟 | 1999-01-22 00:00:00 | 13637555666 |            1303 | 王军伟  |
+------+--------+---------------------+-------------+-----------------+---------+
12 rows in set (0.00 sec)

五、子查询

在一个 SELECT 语句中嵌入另外一个 SELECT 语句，被嵌入的 SELECT 语句称之为子查询，子查询所在的查询称为外部查询。

常见的子查询有两种情况：

第一种情况：子查询放在外部查询的 where 子句中，使用子查询的结果构造外部查询的条件

第二种情况：子查询的结果作为外部查询的数据源（此时子查询的结果是一张临时表）。

根据子查询返回的结果可以把子查询分为以下几类：

（1）标量子查询：子查询结果是一个数据（单行单列）。

（2）列子查询：子查询结果是一列（多行单列）。

（3）表子查询：子查询结果是一张临时表（多行多列）。

（4）EXISTS 子查询：根据子查询是否有返回值决定主查询的返回结果。

1、标量子查询

标量子查询的结果是一个单行单列的数据。一般和 =、>、<、>=、<=、!= 等操作符一起使用来构造外部查询的条件。语法格式如下：

select ...
where 列名 比较运算符 (标量子查询)

-- 说明：标量子查询的查询结果一个单行单列的数据，此时外部查询的运算符可以使用 =、>、<、>=、<=、!= 等比较运算符。
--      如果子查询结果为空，则外部查询的条件也为 NULL。
--      如果查询结果为多行单列，则出现错误。

例如：

（1）查询学生的年龄低于所有学生平均年龄的学生信息

select * from stu 
where year(now())-year(birth) < (select avg(year(now())-year(birth)) from stu);

-- 查询结果
mysql> select * from stu
    -> where year(now())-year(birth) < (select avg(year(now())-year(birth)) from stu);
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124003 | 张从超 | 女     | 2003-12-09 | 13637389129 | D01     |
| 20220124004 | 孙金航 | 女     | 2003-03-07 | 13637387244 | D01     |
| 20220214003 | 裴天馨 | 女     | 2003-04-15 | 13637381374 | D02     |
| 20220325101 | 董雯林 | 男     | 2003-12-27 | 13637357085 | D03     |
| 20230244101 | 李刚   | 男     | 2004-12-08 | 13735214567 | NULL    |
+-------------+--------+--------+------------+-------------+---------+
5 rows in set (0.00 sec)

（2） 查询与【李刚】出生日期相同的学生信息

select * from stu 
where birth = (select birth from stu where s_name = '李刚');

-- 查询出现错误：因为 stu 表中有多个同学的姓名为【李刚】，导致子查询返回多个值
mysql> select * from stu
    -> where birth = (select birth from stu where s_name = '李刚');
ERROR 1242 (21000): Subquery returns more than 1 row

-- 单独运行子查询，返回多个结果
mysql> select birth from stu where s_name = '李刚';
+------------+
| birth      |
+------------+
| 2001-06-21 |
| 2004-12-08 |
+------------+
2 rows in set (0.00 sec)
2、列子查询

列子查询的结果是一列多行，查询结果相当于一个数据集合。列子查询一般使用 IN、ANY | SOME、 ALL 等比较运算符。

（1）运算符 IN

表示外部查询的某个列的取值和查询结果中的任意一个数据相等即可。

例如：查询与【李刚】出生日期相同的学生信息

select * from stu 
where birth IN (select birth from stu where s_name = '李刚');

-- 查询结果
mysql> select * from stu
    -> where birth IN (select birth from stu where s_name = '李刚');
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220325103 | 李刚   | 男     | 2001-06-21 | 13637340698 | D03     |
| 20230244101 | 李刚   | 男     | 2004-12-08 | 13735214567 | NULL    |
+-------------+--------+--------+------------+-------------+---------+
2 rows in set (0.00 sec)

（2）运算符 ANY | SOME

运算符 ANY | SOME 一般与大于号或小于号一起使用，SOME 和 ANY 的用法和查询结果完全相同。形式为如下：

-- 表示比查询结果中的任意一个大即满足条件（比最小的大即可）
select ... where 列名 > ANY | SOME(子查询)  
等价于： select ... where 列名 > (select min(列名) from ...)  

-- 表示比查询结果中的任意一个小即满足条件（比最大的小即可）
select ... where 列名 < ANY | SOME(子查询)  
等价于： select ... where 列名 < (select max(列名) from ...)

例如：

查询比任意的一个【李刚】更晚出生的学生信息：

-- 显示的是比【2001-06-21】出生的【李刚】更晚的日期
mysql> select * from stu where birth >= some(select birth from stu where s_name = '李刚');
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124003 | 张从超 | 女     | 2003-12-09 | 13637389129 | D01     |
| 20220124004 | 孙金航 | 女     | 2003-03-07 | 13637387244 | D01     |
| 20220214001 | 谭宝名 | 男     | 2002-08-05 | 13637332439 | D02     |
| 20220214003 | 裴天馨 | 女     | 2003-04-15 | 13637381374 | D02     |
| 20220325101 | 董雯林 | 男     | 2003-12-27 | 13637357085 | D03     |
| 20220325102 | 周宜可 | 男     | 2002-07-04 | 13637333898 | D03     |
| 20220325103 | 李刚   | 男     | 2001-06-21 | 13637340698 | D03     |
| 20220410101 | 夏飞研 | 女     | 2002-09-02 | 13637326693 | D04     |
| 20220410102 | 聂小龙 | 男     | 2002-12-04 | 13637331432 | D04     |
| 20220410103 | 赵晨祥 | 女     | 2002-05-22 | 13637388564 | D04     |
| 20220410104 | 江沐洪 | 男     | 2002-02-12 | 13637345052 | D04     |
| 20230244101 | 李刚   | 男     | 2004-12-08 | 13735214567 | NULL    |
+-------------+--------+--------+------------+-------------+---------+
12 rows in set (0.00 sec)

（3）运算符 ALL

ALL 一般和大于号或小于号一起使用。形式为如下：

-- 表示比查询结果中所有的数据都大（比最大的还大）
select ... where 列名 > ALL(子查询)  
等价于： select ... where 列名 > (select max(列名) from ...)  

-- 表示比查询结果中所有的数据都小（比最小的还小）
select ... where 列名 < ALL(子查询)  
等价于： select ... where 列名 < (select min(列名) from ...)

查询比所有的【李刚】更早出生的学生信息：

-- 显示的是比【2001-06-21】出生的【李刚】更早的日期
mysql> select * from stu where birth <= all(select birth from stu where s_name = '李刚');
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124001 | 薛智玲 | 男     | 2000-01-15 | 13637358097 | D01     |
| 20220124002 | 杨铭华 | 男     | 2001-03-06 | 13637381407 | D01     |
| 20220214002 | 赵文瀚 | 女     | 2001-03-25 | 13637381710 | D02     |
| 20220325103 | 李刚   | 男     | 2001-06-21 | 13637340698 | D03     |
+-------------+--------+--------+------------+-------------+---------+
4 rows in set (0.00 sec)
3、表子查询

表子查询结果是一张临时表（多行多列），一般作为外部查询的数据源。

在 MySQL 数据库中，如果子查询是一张表，并且作为外部查询的数据源放在 from 后面，必须为子查询指定别名。

分组查询中对组的筛选可以使用表子查询完成。例如：

-- 分组查询
select d.dept_id, d.dept_name, count(*) as cnt_stu 
from stu s join dept d on s.dept_id = d.dept_id 
group by d.dept_id, d.dept_name
having cnt_stu > 3;

-- 查询结果
mysql> select d.dept_id, d.dept_name, count(*) as cnt_stu
    -> from stu s join dept d on s.dept_id = d.dept_id
    -> group by d.dept_id, d.dept_name
    -> having cnt_stu > 3;
+---------+-----------+---------+
| dept_id | dept_name | cnt_stu |
+---------+-----------+---------+
| D01     | 经济学院  |       4 |
| D04     | 农学院    |       4 |
+---------+-----------+---------+
2 rows in set (0.03 sec)

-- 用表子查询代替以上的分组查询
select * from 
(select d.dept_id, d.dept_name, count(*) as cnt_stu 
from stu s join dept d on s.dept_id = d.dept_id 
group by d.dept_id, d.dept_name) a
where cnt_stu > 3;

-- 查询结果
mysql> select * from
    -> (select d.dept_id, d.dept_name, count(*) as cnt_stu
    -> from stu s join dept d on s.dept_id = d.dept_id
    -> group by d.dept_id, d.dept_name) a
    -> where cnt_stu > 3;
+---------+-----------+---------+
| dept_id | dept_name | cnt_stu |
+---------+-----------+---------+
| D01     | 经济学院  |       4 |
| D04     | 农学院    |       4 |
+---------+-----------+---------+
2 rows in set (0.00 sec)
4、EXISTS 子查询

EXISTS 子查询根据子查询是否有返回值决定主查询的返回结果。EXISTS 子查询语法格式如下：

select ... where [NOT] EXISTS (子查询)

例如：创建如下两张表，并输入数据

create table dept(
    dept_id int primary key,
    dept_name char(20)
);

create table emp(
    e_id int primary key,
    e_name char(20),
    salary decimal(10,2),
    dept_id int,
    foreign key(dept_id) references dept(dept_id)
);

insert into dept values(1,'财务部');
insert into dept values(2,'研发部');
insert into dept values(3,'生产部');
insert into dept values(4,'人事部');
insert into dept values(5,'销售部');

insert into emp values(1101, '李明', 5000, 1);
insert into emp values(1102, '张静静', 4200, 1);
insert into emp values(1103, '王涛', 5700, 1);
insert into emp values(1104, '李东方', 4600, 2);
insert into emp values(1105, '王光明', 6400, 2);
insert into emp values(1106, '李小强', 5800, 2);
insert into emp values(1107, '马云飞', 6000, 3);

-- 查看表中的数据
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 财务部    |
|       2 | 研发部    |
|       3 | 生产部    |
|       4 | 人事部    |
|       5 | 销售部    |
+---------+-----------+
5 rows in set (0.00 sec)

mysql> select * from emp;
+------+--------+---------+---------+
| e_id | e_name | salary  | dept_id |
+------+--------+---------+---------+
| 1101 | 李明   | 5000.00 |       1 |
| 1102 | 张静静 | 4200.00 |       1 |
| 1103 | 王涛   | 5700.00 |       1 |
| 1104 | 李东方 | 4600.00 |       2 |
| 1105 | 王光明 | 6400.00 |       2 |
| 1106 | 李小强 | 5800.00 |       2 |
| 1107 | 马云飞 | 6000.00 |       3 |
+------+--------+---------+---------+
7 rows in set (0.00 sec)

（1）查询有员工的部门

方法一：使用连接查询（内连接）

select distinct d.*
from dept d join emp e on d.dept_id = e.dept_id;

-- 查询结果
mysql> select distinct d.*
    -> from dept d join emp e on d.dept_id = e.dept_id;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 财务部    |
|       2 | 研发部    |
|       3 | 生产部    |
+---------+-----------+
3 rows in set (0.00 sec)

方法二：使用 EXISTS 子查询

select * from dept where exists
(select * from emp where dept_id = dept.dept_id);

-- 查询结果
mysql> select * from dept where exists
    -> (select * from emp where dept_id = dept.dept_id);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 财务部    |
|       2 | 研发部    |
|       3 | 生产部    |
+---------+-----------+
3 rows in set (0.00 sec)

（2）查询没有员工的部门

方法一：使用连接查询（外连接）

select distinct d.*
from dept d left join emp e on d.dept_id = e.dept_id
where e_id is null;

-- 查询结果
mysql> select distinct d.*
    -> from dept d left join emp e on d.dept_id = e.dept_id
    -> where e_id is null;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       4 | 人事部    |
|       5 | 销售部    |
+---------+-----------+
2 rows in set (0.00 sec)

方法二：使用 EXISTS 子查询

select * from dept where not exists
(select * from emp where dept_id = dept.dept_id);

-- 查询结果
mysql> select * from dept where not exists
    -> (select * from emp where dept_id = dept.dept_id);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       4 | 人事部    |
|       5 | 销售部    |
+---------+-----------+
2 rows in set (0.00 sec)
MySQL -- 连接查询
Afison的博客
06-03 3509
MySQL使用连接查询（JOIN）是为了从多个相关表中获取数据。
MySQL 实验 8：数据查询（1）—— select 语句的基本用法
睿思达DBA
10-07 1913
介绍 select 语句的基本用法
mysql连接查询
m0_74009862的博客
09-20 2430
左连接查询的结果为students和scores两个表匹配到的数据加students表特有的数据，对于scores表中不存在的数据使用null填充；右连接查询的结果为scores和courses两个表匹配到的数据加courses表特有的数据，对于scores表中不存在的数据使用null填充；表a和表b的列都会出现在查询结果中，由于表a和表b都有列名叫id，为了区分，表b的id列名显示为id1.语法：select *from 表1 left join 表2 on 表1.字段=表2.字段;
2025.4.2 MySQL学习笔记：连接查询
最新发布
2503_90960068的博客
04-07 796
连接查询是关系型数据库中非常重要的查询方式之一，它允许我们将多个表的数据通过某种关联关系组合在一起进行查询，从而获取更全面、更有意义的信息。在MySQL中，连接查询主要包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（MySQL中不直接支持，但可以通过其他方式实现）。连接查询是MySQL中非常强大的功能，通过内连接、左连接、右连接和全连接，我们可以根据不同的需求组合多个表的数据。
MySQL连接查询
weixin_45392969的博客
03-17 2112
内连接（INNER JOIN）查询、外连接查询 、LEFT JOIN（左连接）、RIGHT JOIN（右连接）：
mysql数据查询——复杂查询
qq_45159070的博客
07-05 1963
《实验五 数据查询——复杂查询》实验说明 一、 适用课程：《数据库原理与应用》 实验学时：3 二、实验目的： (1) 掌握SELECT语句的语法格式； (2) 熟练运用SELECT语句进行多表连接查询； (3) 熟练运用SELECT语句进行各种复杂类型的查询。 三、实验内容： 完成各类多表连接查询、嵌套查询和集合查询等。 四、实验要求： (1) 熟练掌握连接查询：  交叉连接查询  内连接查询：掌握内连接所对应的SQL语句的显示和隐式结构形式，包括：等值与非等值连接查询、自然连接查询、表的
MySQL数据库9——数据表的复合查询（多表连接，自连接，子查询，合并查询）
范玖久的博客
03-24 907
数据表的复合查询 （一）多表查询 笛卡儿积的介绍： 从第一张表中取出第一条记录与第二张表的所有记录组合； 从第一张表中取出第二条记录与第二张表的所有记录组合； … 以此类推，得到的结果称为笛卡儿积。 （1）student表的信息 （2）teacher表的信息 （3）查询两表得到的笛卡儿积 注意： 通常情况下，我们不会在没有条件的情况下查询两表的信息； 大部分情况下是两个表中有相同的字段且相同...
MySQL—多表查询—小结
岁岁岁平安的博客
06-10 581
这篇博客的主要内容是关于MySQL当中的多表查询的一个总结。其中包括对多表关系，多表查询中的内连接查询、外连接查询，子连接查询以及多表联查等等内容......
数据库实验之数据查询和更新
11-11
在本次的数据库实验中，我们将重点关注数据查询、索引的创建与删除以及使用聚集函数这三个关键知识点。 首先，让我们来谈谈索引。索引是数据库中加速数据检索的重要机制，类似于书籍的目录。在MySQL Server中，我们...
Mysql连接查询
每天进步一点点
05-11 1151
Mysql连接查询(一) 分类： 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接 右外连接 交叉连接： 为表起别名 ①提高语句的简介度 ②区分多个重名的字段 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定 案例： select a.name，b.score from 学生表 a，成绩表 b where a.student_id=b.student_id； ...
MySql中的连接查询
欢迎阅读我的博客
02-13 2638
外连接: 在外连接当中，两张表连接产生了主次关系 , 主要查询的是主表的数据 , 捎带着关联查询次表 , 即如果对方没有记录和我主表匹配 , 那么默认对方是null , outer关键字可以省略。内连接：A表和B表连接，A和B两张表没有主次关系是平等的 , 查询时只是将能够匹配上连接条件的数据查询出来 , 即如果没有匹配的就查询不出来 , inner关键字可以省略。当两张表进行连接查询，没有指定连接条件的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为：笛卡尔积现象（笛卡尔发现的，这是。
MySQL 连接查询和子查询
sshi9的博客
02-15 1377
连接查询 连接查询：也可以叫跨表查询，需要关联多个表进行查询 连接查询分类 根据语法出现的年代来划分的话，包括： SQL92 SQL99 根据表的连接方式来划分，包括： 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接（左连接） 右外连接（右连接） 全连接 笛卡尔积现象：当两张表进行连...
MySQL数据库_连接查询
weixin_46769840的博客
04-26 782
自学python进阶过程中的学习笔记
mysql 连接查询和子查询
一路向前
04-02 1133
连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询。
MySQL的连接查询和子查询练习
weixin_44893721的博客
10-25 724
连接查询和子查询练习 谁和刘晨同岁 select sno,sname from student where sage = (select sage from student where sname =‘刘晨’) and sname !=‘刘晨’; 查询各个学生的姓名、所选修课程号及成绩。（连接查询） 查询刘晨选修了几门课程。(两种方法) 可以用子查询 Select count(cno) //连接...
mysql子查询与连接查询
weixin_30699955的博客
05-11 244
表结构以及数据： CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

睿思达DBA_WGX

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值