🌸雨落在了我的手上：个人主页
🐟个人仓库：Gitee仓库
❄️个人专栏：<<JaveSe>> <<C语言>> <<C语言数据结构>> <<Java数据结构 >>
<<MySQL数据库基础 >>
🔥🔥🔥人生格言：无人扶我青云志，我自踏雪至山巅
🎬 博主简介
目录
内容大纲：
本文系统讲解MySQL联合查询技术，涵盖内连接、外连接、自连接、子查询、合并查询及插入查询结果等核心内容。通过案例演示多表数据关联的实现过程，重点解析笛卡尔积计算机制与连接条件优化，结合学生、班级、成绩等表数据，展示如何高效获取跨表完整信息，提升复杂查询能力。
1. 本节目标
了解联合查询的计算过程
掌握内连接、左外连接、右外连和自连接接查询
掌握子查询和合并查询
掌握插入查询结果和根据查询结果创建表
2. 简介
2.1 为什么要使用联合查询
在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询一个条数据的完整信息，就要从多个表中获取数据，如下图所示：要获取学生的基本信息和班级信息就要从学生表和班级表中获取，这时就需要使用联合查询，这里的联合指的是多个表的组合。
2.2 多表联合查询时MYSQL内部是如何进行计算的
- 参与查询的所有表取笛卡尔积，结果集在临时表中
- 观察哪些记录是有效数据，根据两个表的关联关系过滤掉无效数据
如果联合查询表的个数越多，表中的数据量越大，临时表就会越大，所以根据实际情况确定联合查询表的个数
2.3 构造练习案例数据
# 课程表
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计算机网络'), ('数据结构');
# 班级表
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');
# 学生表
insert into student (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('孙悟空', '100002', 18, 1, '1986-09-01', 1),
('猪悟能', '100003', 18, 1, '1986-09-01', 1),
('沙悟净', '100004', 18, 1, '1986-09-01', 1),
('宋江', '200001', 18, 1, '2000-09-01', 2),
('武松', '200002', 18, 1, '2000-09-01', 2),
('李逹', '200003', 18, 1, '2000-09-01', 2),
('不想毕业', '200004', 18, 1, '2000-09-01', 2);
# 成绩表
insert into score (score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);2.4 案例：一个完整的联合查询的过程
- 查询学生姓名为孙悟空的详细信息，包括学生个人信息和班级信息
1. 确定参与查询的表，学生表和班级表
# 在from后同时写所有参与查询的表，并用逗号隔开
mysql> select * from student, class;
+----+--------------+--------+------+--------+-------------+----------+----+--------------+
| id | name | sno | age | gender | enroll_date | class_id | id | name |
+----+--------------+--------+------+--------+-------------+----------+----+--------------+
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 | 3 | 前端001班 |
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 | 2 | C++001班 |
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 2 | 孙悟空 | 100002 | 18 | 1 | 1986-09-01 | 1 | 3 | 前端001班 |
| 2 | 孙悟空 | 100002 | 18 | 1 | 1986-09-01 | 1 | 2 | C++001班 |
| 2 | 孙悟空 | 100002 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 3 | 猪悟能 | 100003 | 18 | 1 | 1986-09-01 | 1 | 3 | 前端001班 |
| 3 | 猪悟能 | 100003 | 18 | 1 | 1986-09-01 | 1 | 2 | C++001班 |
| 3 | 猪悟能 | 100003 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 4 | 沙悟净 | 100004 | 18 | 1 | 1986-09-01 | 1 | 3 | 前端001班 |
| 4 | 沙悟净 | 100004 | 18 | 1 | 1986-09-01 | 1 | 2 | C++001班 |
| 4 | 沙悟净 | 100004 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 1 | 3 | 前端001班 |
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 1 | 2 | C++001班 |
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
| 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 1 | 3 | 前端001班 |
| 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 1 | 2 | C++001班 |
| 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
| 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 1 | 3 | 前端001班 |
| 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 1 | 2 | C++001班 |
| 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 1 | 3 | 前端001班 |
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 1 | 2 | C++001班 |
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
+----+--------------+--------+------+--------+-------------+----------+----+--------------+
24 rows in set (0.00 sec)2. 确定连接条件，student表中的class_id与class表中id列的值相等
# 在where子句中加入连接条件
mysql> select * from student, class where student.class_id = class.id;
+----+--------------+--------+------+--------+-------------+----------+----+------------+
| id | name | sno | age | gender | enroll_date | class_id | id | name |
+----+--------------+--------+------+--------+-------------+----------+----+------------+
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 2 | 孙悟空 | 100002 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 3 | 猪悟能 | 100003 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 4 | 沙悟净 | 100004 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
| 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
| 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
+----+--------------+--------+------+--------+-------------+----------+----+------------+
8 rows in set (0.00 sec)3. 加入查询条件
# 依题意添加where条件
mysql> select * from student, class where student.class_id = class.id and name = '宋江';
ERROR 1052 (23000): Column 'name' in where clause is ambiguous
# 由于两个表中都有name列，所以MySQL不清楚具体要使用哪个列，这时可以用“表名.列号”的方式指定具体的列
# 得到目标记录行
mysql> select * from student, class where student.class_id = class.id and student.name = '宋江';
+----+--------+--------+------+--------+-------------+----------+----+------------+
| id | name | sno | age | gender | enroll_date | class_id | id | name |
+----+--------+--------+------+--------+-------------+----------+----+------------+
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 1 | 1 | Java001班 |
+----+--------+--------+------+--------+-------------+----------+----+------------+
1 row in set (0.00 sec)4. 精减查询结果字段
select
student.id,
student.name,
student.sno,
student.age,
student.gender,
student.enroll_date,
class.name
from
student, class
where
student.class_id = class.id
and
student.name = '宋江';
+----+--------+--------+------+--------+-------------+------------+
| id | name | sno | age | gender | enroll_date | name |
+----+--------+--------+------+--------+-------------+------------+
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | Java001班 |
+----+--------+--------+------+--------+-------------+------------+
1 row in set (0.00 sec)5. 可以为表名指定别名
select
s.id,
s.name,
s.sno,
s.age,
s.gender,
s.enroll_date,
c.name
from
student s , class c
where
s.class_id = c.id
and
s.name = '宋江';
+----+--------+--------+------+--------+-------------+------------+
| id | name | sno | age | gender | enroll_date | name |
+----+--------+--------+------+--------+-------------+------------+
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | Java001班 |
+----+--------+--------+------+--------+-------------+------------+
1 row in set (0.00 sec)3. 内连接
3.1 语法
select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;3.2 示例
- 查询"唐三藏"同学的成绩
mysql> select s.name, sc.score from student s join score sc on sc.student_id = s.id where s.name = '唐三藏';
+-----------+-------+
| name | score |
+-----------+-------+
| 唐三藏 | 70.5 |
| 唐三藏 | 98.5 |
| 唐三藏 | 33 |
| 唐三藏 | 98 |
+-----------+-------+
4 rows in set (0.00 sec)- 查询所有同学的总成绩，及同学的个人信息
mysql> select s.name, sum(sc.score) from student s, score sc where sc.student_id = s.id group by (s.id);
+-----------+---------------+
| name | sum(sc.score) |
+-----------+---------------+
| 唐三藏 | 300 |
| 孙悟空 | 119.5 |
| 猪悟能 | 200 |
| 沙悟净 | 218 |
| 宋江 | 118 |
| 武松 | 178 |
| 李逹 | 172 |
+-----------+---------------+
7 rows in set (0.00 sec)Group by 使用了student.id进行分组，查询表列表中的student.name没有出现在Group by分组中，也没有包含在聚合函数中，这是因为SQL规定在Group by分组查询时，如果查询列表中的列没有出现在GROUP BY子句中，但这些列的值在每个分组内部是相同的，那么它们可以出现在查询结果中。
- 查询所有同学每门课的成绩，及同学的个人信息
select
s.id as id,
s.name as 姓名,
s.sno as 学号,
s.gender as 性别,
c.name as 班级,
sc.score as 分数
from
student s, course c, score sc
where
s.id = sc.student_id
and
c.id = sc.course_id
order by
s.id;
# 结果集中没有"不想毕业"同学的成绩，因为score表中没有这位同学的记录
+----+-----------+--------+--------+-----------------+--------+
| id | 姓名 | 学号 | 性别 | 班级 | 分数 |
+----+-----------+--------+--------+-----------------+--------+
| 1 | 唐三藏 | 100001 | 1 | Java | 70.5 |
| 1 | 唐三藏 | 100001 | 1 | MySQL | 98.5 |
| 1 | 唐三藏 | 100001 | 1 | 计算机网络 | 33 |
| 1 | 唐三藏 | 100001 | 1 | 数据结构 | 98 |
| 2 | 孙悟空 | 100002 | 1 | Java | 60 |
| 2 | 孙悟空 | 100002 | 1 | 计算机网络 | 59.5 |
| 3 | 猪悟能 | 100003 | 1 | Java | 33 |
| 3 | 猪悟能 | 100003 | 1 | MySQL | 68 |
| 3 | 猪悟能 | 100003 | 1 | 计算机网络 | 99 |
| 4 | 沙悟净 | 100004 | 1 | Java | 67 |
| 4 | 沙悟净 | 100004 | 1 | MySQL | 23 |
| 4 | 沙悟净 | 100004 | 1 | 计算机网络 | 56 |
| 4 | 沙悟净 | 100004 | 1 | 数据结构 | 72 |
| 5 | 宋江 | 200001 | 1 | Java | 81 |
| 5 | 宋江 | 200001 | 1 | 计算机网络 | 37 |
| 6 | 武松 | 200002 | 1 | C++ | 56 |
| 6 | 武松 | 200002 | 1 | 操作系统 | 43 |
| 6 | 武松 | 200002 | 1 | 数据结构 | 79 |
| 7 | 李逹 | 200003 | 1 | C++ | 80 |
| 7 | 李逹 | 200003 | 1 | 数据结构 | 92 |
+----+-----------+--------+--------+-----------------+--------+
20 rows in set (0.00 sec)4. 外连接
- 外连接分为左外连接、右外连接和全外连接三种类型，MySQL不支持全外连接。
- 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显示为NULL。
- 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显示为NULL。
- 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某一边表中没有匹配的记录，则结果集中对应字段会显示为NULL。
4.1 语法
-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;4.2 示例
- 查询没有参加考试的同学信息
# 左连接以JOIN左边的表为基准，左表显示全部记录，右表中没有匹配的记录用NULL填充
mysql> select s.id, s.name, s.sno, s.age, sc.* from student s LEFT JOIN score sc on sc.student_id = s.id;
+----+--------------+--------+------+------+-------+------------+-----------+
| id | name | sno | age | id | score | student_id | course_id |
+----+--------------+--------+------+------+-------+------------+-----------+
| 1 | 唐三藏 | 100001 | 18 | 1 | 70.5 | 1 | 1 |
| 1 | 唐三藏 | 100001 | 18 | 2 | 98.5 | 1 | 3 |
| 1 | 唐三藏 | 100001 | 18 | 3 | 33 | 1 | 5 |
| 1 | 唐三藏 | 100001 | 18 | 4 | 98 | 1 | 6 |
| 2 | 孙悟空 | 100002 | 18 | 5 | 60 | 2 | 1 |
| 2 | 孙悟空 | 100002 | 18 | 6 | 59.5 | 2 | 5 |
| 3 | 猪悟能 | 100003 | 18 | 7 | 33 | 3 | 1 |
| 3 | 猪悟能 | 100003 | 18 | 8 | 68 | 3 | 3 |
| 3 | 猪悟能 | 100003 | 18 | 9 | 99 | 3 | 5 |
| 4 | 沙悟净 | 100004 | 18 | 10 | 67 | 4 | 1 |
| 4 | 沙悟净 | 100004 | 18 | 11 | 23 | 4 | 3 |
| 4 | 沙悟净 | 100004 | 18 | 12 | 56 | 4 | 5 |
| 4 | 沙悟净 | 100004 | 18 | 13 | 72 | 4 | 6 |
| 5 | 宋江 | 200001 | 18 | 14 | 81 | 5 | 1 |
| 5 | 宋江 | 200001 | 18 | 15 | 37 | 5 | 5 |
| 6 | 武松 | 200002 | 18 | 16 | 56 | 6 | 2 |
| 6 | 武松 | 200002 | 18 | 17 | 43 | 6 | 4 |
| 6 | 武松 | 200002 | 18 | 18 | 79 | 6 | 6 |
| 7 | 李逹 | 200003 | 18 | 19 | 80 | 7 | 2 |
| 7 | 李逹 | 200003 | 18 | 20 | 92 | 7 | 6 |
| 8 | 不想毕业 | 200004 | 18 | NULL | NULL | NULL | NULL |
+----+--------------+--------+------+------+-------+------------+-----------+
21 rows in set (0.00 sec)
# 过滤参加了考试的同学
mysql> select s.* from student s LEFT JOIN score sc on sc.student_id = s.id where sc.score is null;
+----+--------------+--------+------+--------+-------------+----------+
| id | name | sno | age | gender | enroll_date | class_id |
+----+--------------+--------+------+--------+-------------+----------+
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 1 |
+----+--------------+--------+------+--------+-------------+----------+
1 row in set (0.00 sec)- 查询没有学生的班级
# 右连接以JOIN右边的表为基准，右表显示全部记录，左表中没有匹配的记录用NULL填充
mysql> select * from student s RIGHT JOIN class c on c.id = s.class_id;
+------+--------------+--------+------+--------+-------------+----------+----+--------------+
| id | name | sno | age | gender | enroll_date | class_id | id | name |
+------+--------------+--------+------+--------+-------------+----------+----+--------------+
| 4 | 沙悟净 | 100004 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 3 | 猪悟能 | 100003 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 2 | 孙悟空 | 100002 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 | 1 | Java001班 |
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 2 | 2 | C++001班 |
| 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 2 | 2 | C++001班 |
| 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 2 | 2 | C++001班 |
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 2 | 2 | C++001班 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 3 | 前端001班 |
+------+--------------+--------+------+--------+-------------+----------+----+--------------+
# 过滤有学生的班级
mysql> select c.* from student s RIGHT JOIN class c on c.id = s.class_id where s.id is null;
+----+--------------+
| id | name |
+----+--------------+
| 3 | 前端001班 |
+----+--------------+
1 row in set (0.00 sec)5. 自连接
5.1 应用场景
自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者说实现行与行之间的比较。在做表连接时为表起不同的别名。
# 不为表指定别名
mysql> select * from score, score;
ERROR 1066 (42000): Not unique table/alias: 'score'
# 指定别名
mysql> select * from score s1, score s2;5.2 示例
- 显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息
# 首先分两步进行，先查出JAVA和MySQL的课程Id，分别为1和3
mysql> select * from course where name = 'Java' or name = 'MySQL';
+----+-------+
| id | name |
+----+-------+
| 1 | Java |
| 3 | MySQL |
+----+-------+
2 rows in set (0.00 sec)
# 再查询成绩表中，JAVA成绩比MySQL成绩好的信息
mysql> select s1.* from score s1, score s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 2 | 98.5 | 1 | 3 |
| 8 | 68 | 3 | 3 |
+----+-------+------------+-----------+
2 rows in set (0.00 sec)
# 结合在一起进行查询
select s1.* from
score s1,
score s2,
course c1,
course c2
where
s1.student_id = s2.student_id
and
s1.course_id = c1.id
and
s2.course_id = c2.id
and
s1.score > s2.score
and
c1.`name` = 'MySQL'
and
c2.`name` = 'Java';
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 2 | 98.5 | 1 | 3 |
| 8 | 68 | 3 | 3 |
+----+-------+------------+-----------+
2 rows in set (0.00 sec)5.3 表连接练习
- 显示所有"MySQL"成绩比"JAVA"成绩高的学生信息和班级以及成绩信息
# 相关的表全部加入连接，并确定连接条件
select stu.name as 姓名, c.name as 班级, s1.score as MySQL分数, s2.score as Java分数 from
score s1,
score s2,
course c1,
course c2,
student stu,
class c
where
s1.student_id = s2.student_id
and
s1.course_id = c1.id
and
s2.course_id = c2.id
and
s1.score > s2.score
and
stu.id = s1.student_id
and
stu.class_id = c.id
and
c1.`name` = 'MySQL'
and
c2.`name` = 'Java';
+-----------+------------+-------------+------------+
| 姓名 | 班级 | MySQL分数 | Java分数 |
+-----------+------------+-------------+------------+
| 唐三藏 | Java001班 | 98.5 | 70.5 |
| 猪悟能 | Java001班 | 68 | 33 |
+-----------+------------+-------------+------------+
2 rows in set (0.00 sec)6. 子查询
子查询是把一个SELECT语句的结果当做别一个SELECT语句的条件，也叫嵌套查询
6.1 语法
select * from table1 where col_name1 {= | IN} (
select col_name1 from table2 where col_name2 {= | IN} [(
select ...)
] ...
)6.2 单行子查询
嵌套的查询中只返回一行数据
- 示例：查询与"不想毕业"同学的同班同学
mysql> select * from student where class_id = (select class_id from student where name = '不想毕业');
+----+--------------+--------+------+--------+-------------+----------+
| id | name | sno | age | gender | enroll_date | class_id |
+----+--------------+--------+------+--------+-------------+----------+
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 2 |
| 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 2 |
| 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 2 |
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 2 |
+----+--------------+--------+------+--------+-------------+----------+
4 rows in set (0.00 sec)6.3 多行子查询
嵌套的查询中返回多行数据，使用[NOT] IN关键字
- 示例：查询"MySQL"或"Java"课程的成绩信息
mysql> select * from score where course_id in (select id from course where name = 'Java' or name = 'MySQL');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 1 | 70.5 | 1 | 1 |
| 5 | 60 | 2 | 1 |
| 7 | 33 | 3 | 1 |
| 10 | 67 | 4 | 1 |
| 14 | 81 | 5 | 1 |
| 2 | 98.5 | 1 | 3 |
| 8 | 68 | 3 | 3 |
| 11 | 23 | 4 | 3 |
+----+-------+------------+-----------+
8 rows in set (0.00 sec)
# 使用NOT IN 可以查询除了"MySQL"或"Java"课程的成绩
mysql> select * from score where course_id not in (select id from course where name = 'Java' or name = 'MySQL');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 3 | 33 | 1 | 5 |
| 4 | 98 | 1 | 6 |
| 6 | 59.5 | 2 | 5 |
| 9 | 99 | 3 | 5 |
| 12 | 56 | 4 | 5 |
| 13 | 72 | 4 | 6 |
| 15 | 37 | 5 | 5 |
| 16 | 56 | 6 | 2 |
| 17 | 43 | 6 | 4 |
| 18 | 79 | 6 | 6 |
| 19 | 80 | 7 | 2 |
| 20 | 92 | 7 | 6 |
+----+-------+------------+-----------+
12 rows in set (0.00 sec)6.4 多列子查询
单行子查询和多行子查询都只返回一列数据，多列子查询中可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配
- 示例：查询重复录入的分数
# 插入重复的分数：score, student_id, course_id列重复
mysql> insert into score(score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);
# 子查询中返回多个列
mysql> SELECT * FROM score WHERE (score, student_id, course_id ) IN (
SELECT score, student_id,
course_id FROM score GROUP BY score, student_id, course_id HAVING
count( 0 ) > 1
);
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 1 | 70.5 | 1 | 1 |
| 2 | 98.5 | 1 | 3 |
| 5 | 60 | 2 | 1 |
| 21 | 70.5 | 1 | 1 |
| 22 | 98.5 | 1 | 3 |
| 23 | 60 | 2 | 1 |
+----+-------+------------+-----------+
6 rows in set (0.00 sec)6.5 在from子句中使用子查询
当一个查询产生结果时，MySQL自动创建一个临时表，然后把结果集放在这个临时表中，最终返回给用户，在from子句中也可以使用临时表进行子查询或表连接操作
- 示例：查询所有比"Java001班"平均分高的成绩信息
# 首先分步进行，第一步先查出Java001班的平均分
mysql> select avg(sc.score) score from student s
join class c on s.class_id = c.id
join score sc on s.id = sc.student_id
where
c.name = 'Java001班';
+----------+
| score |
+----------+
| 66.65625 |
+----------+
1 row in set (0.00 sec)
# 把以上查询做为临时表，与真实表进行比较
mysql> select * from score s, (
select avg(sc.score) score from student s
join class c on s.class_id = c.id
join score sc on s.id = sc.student_id
where
c.name = 'Java001班') tmp
where s.score > tmp.score;
+----+-------+------------+-----------+----------+
| id | score | student_id | course_id | score |
+----+-------+------------+-----------+----------+
| 1 | 70.5 | 1 | 1 | 66.65625 |
| 2 | 98.5 | 1 | 3 | 66.65625 |
| 4 | 98 | 1 | 6 | 66.65625 |
| 8 | 68 | 3 | 3 | 66.65625 |
| 9 | 99 | 3 | 5 | 66.65625 |
| 10 | 67 | 4 | 1 | 66.65625 |
| 13 | 72 | 4 | 6 | 66.65625 |
| 14 | 81 | 5 | 1 | 66.65625 |
| 18 | 79 | 6 | 6 | 66.65625 |
| 19 | 80 | 7 | 2 | 66.65625 |
| 20 | 92 | 7 | 6 | 66.65625 |
| 21 | 70.5 | 1 | 1 | 66.65625 |
| 22 | 98.5 | 1 | 3 | 66.65625 |
+----+-------+------------+-----------+----------+
13 rows in set (0.00 sec)tmp 是临时表的别名
7. 合并查询
在实际应用中，为了合并多个select操作返回的结果，可以使用集合操作符 union，union all
7.1 创建新表并初始化数据
# 创建一个新表并初始化数据
mysql> create table student1 like student;
Query OK, 0 rows affected (0.03 sec)
insert into student1 (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('刘备', '300001', 18, 1, '1993-09-01', 3),
('张飞', '300002', 18, 1, '1993-09-01', 3),
('关羽', '300003', 18, 1, '1993-09-01', 3);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings:
mysql> select * from student1;
+----+-----------+--------+------+--------+-------------+----------+
| id | name | sno | age | gender | enroll_date | class_id |
+----+-----------+--------+------+--------+-------------+----------+
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 |
| 2 | 刘备 | 300001 | 18 | 1 | 1993-09-01 | 3 |
| 3 | 张飞 | 300002 | 18 | 1 | 1993-09-01 | 3 |
| 4 | 关羽 | 300003 | 18 | 1 | 1993-09-01 | 3 |
+----+-----------+--------+------+--------+-------------+----------+
4 rows in set (0.00 sec)7.2 Union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
- 示例：查询student表中 id < 3 的同学和student1表中的所有同学
# 结果集中有两张表中的数据，但是唐三藏只返回了一条记录
mysql> select * from student where id < 3 union select * from student1;
+----+-----------+--------+------+--------+-------------+----------+
| id | name | sno | age | gender | enroll_date | class_id |
+----+-----------+--------+------+--------+-------------+----------+
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 |
| 2 | 孙悟空 | 100002 | 18 | 1 | 1986-09-01 | 1 |
| 2 | 刘备 | 300001 | 18 | 1 | 1993-09-01 | 3 |
| 3 | 张飞 | 300002 | 18 | 1 | 1993-09-01 | 3 |
| 4 | 关羽 | 300003 | 18 | 1 | 1993-09-01 | 3 |
+----+-----------+--------+------+--------+-------------+----------+
5 rows in set (0.00 sec)7.3 Union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
# 结果集中有两张表中的数据，返回了所有唐三藏的记录
mysql> select * from student where id < 3 union all select * from student1;
+----+-----------+--------+------+--------+-------------+----------+
| id | name | sno | age | gender | enroll_date | class_id |
+----+-----------+--------+------+--------+-------------+----------+
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 |
| 2 | 孙悟空 | 100002 | 18 | 1 | 1986-09-01 | 1 |
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 |
| 2 | 刘备 | 300001 | 18 | 1 | 1993-09-01 | 3 |
| 3 | 张飞 | 300002 | 18 | 1 | 1993-09-01 | 3 |
| 4 | 关羽 | 300003 | 18 | 1 | 1993-09-01 | 3 |
+----+-----------+--------+------+--------+-------------+----------+
6 rows in set (0.00 sec)8. 插入查询结果
8.1 语法
INSERT INTO table_name [(column [, column ...])] SELECT ...8.2 示例
- 将student表中C++001班的学生复制到student1表中
mysql> insert into student1 (name, sno, age, gender, enroll_date, class_id)
select s.name, s.sno, s.age, s.gender, s.enroll_date, s.class_id
from student s, class c where s.class_id = c.id and c.name = 'C++001班';
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student1;
+----+--------------+--------+------+--------+-------------+----------+
| id | name | sno | age | gender | enroll_date | class_id |
+----+--------------+--------+------+--------+-------------+----------+
| 1 | 唐三藏 | 100001 | 18 | 1 | 1986-09-01 | 1 |
| 2 | 刘备 | 300001 | 18 | 1 | 1993-09-01 | 3 |
| 3 | 张飞 | 300002 | 18 | 1 | 1993-09-01 | 3 |
| 4 | 关羽 | 300003 | 18 | 1 | 1993-09-01 | 3 |
| 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 2 |
| 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 2 |
| 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 2 |
| 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 2 |
+----+--------------+--------+------+--------+-------------+----------+
8 rows in set (0.00 sec)转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/2502_93282244/article/details/167703256
