数据库造神计划第十五天---联合查询（2）

寻星探路

发布于 2025-12-17 19:12:39

2870

文章被收录于专栏：CSDN博客CSDN博客

一、自连接

1、应用场景

自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者说实现行与行之间的比较。在做表连接时为表起不同的别名。

把行关系转换为列关系，把未知问题转成已知问题！！！

# 不为表指定别名
select * from score, score;

# 指定别名
select * from score s1, score s2;

2、示例

（1）显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息

# ⾸先分两步进⾏，先查出JAVA和MySQ的课程Id，分别为1和3
select * from course where name = 'Java' or name = 'MySQL';

# 再查询成绩表中，JAVA成绩⽐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;

# 结合在⼀起进⾏查询
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';

3、表连接练习

（1）显示所有"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';

二、子查询

子查询是把⼀个SELECT语句的结果当做别⼀个SELECT语句的条件，也叫嵌套查询

实际开发中慎用！！！

1、语法

 select * from table1 where col_name1 {= | IN} (
     select col_name1  from table2 where col_name2 {= | IN} [(
         select ...)
     ] ...
 )

2、单行子查询

嵌套的查询中只返回⼀行数据

（1）示例：查询与"不想毕业"同学的同班同学

select * from student where class_id = (select class_id from student where name = '不想毕业');

3、多行子查询

嵌套的查询中返回多行数据，使用[NOT]IN关键字

（1）示例：查询"MySQL"或"Java"课程的成绩信息

select * from score where course_id in (select id from course where name = 'Java' or name = 'MySQL');

# 使⽤NOT IN 可以查询除了"MySQL"或"Java"课程的成绩
select * from score where course_id not in (select id from course where name = 'Java' or name = 'MySQL');

4、多列子查询

单行子查询和多行子查询都只返回⼀列数据，多列子查询中可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配

（1）示例：查询重复录入的分数

# 插⼊重复的分数：score, student_id, course_id列重复
insert into score(score, student_id, course_id) values (70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);

# ⼦查询中返回多个列
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 
);

5、在from子句中使用子查询

当⼀个查询产生结果时，MySQL自动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回给用户，在from子句中也可以使用临时表进行子查询或表连接操作

（1）示例：查询所有比"Java001班"平均分高的成绩信息

# ⾸先分步进⾏，第⼀步先查出Java001班的平均分
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班';

# 把以上查询做为临时表，与真实表进⾏⽐较
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;

tmp是临时表的别名

三、合并查询

在实际应用中，为了合并多个select操作返回的结果，可以使用集合操作符union，unionall

1、创建新表并初始化数据

# 创建⼀个新表并初始化数据
create table student1 like student;

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);

select * from student1;

2、Union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

（1）示例：查询student表中id<3的同学和student1表中的所有同学

# 结果集中有两张表中的数据，但是唐三藏只返回了⼀条记录
select * from student where id < 3 union select * from student1;

3、Union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

# 结果集中有两张表中的数据，返回了所有唐三藏的记录
select * from student where id < 3 union all select * from student1;

四、插入查询结果

1、语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

2、示例

（1）将student表中C++001班的学生复制到student1表中

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班';

select * from student1;