2025-10-13-联合查询

背景

-- 删除的顺序应该是 student > class
drop table if exists score;
drop table if exists student;
drop table if exists course;
drop table if exists class;


create table course (
  course_id int primary key auto_increment,
  name varchar(20)
);

create table class (
  class_id int primary key auto_increment,
  name varchar(20)
);

create table student (
  student_id int primary key auto_increment,
  name varchar(20),
  sno varchar(20),
  age int,
  gender tinyint,
  enroll_date datetime,
  class_id int,
  foreign key (class_id) references class(class_id)
);

create table score (
  student_id int,
  course_id int,
  score double
);

# 课程表
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);

内连接

查询"唐三藏"同学的成绩

select student.`name`, score, course.`name`
from student 
join score on student.student_id = score.student_id 
join course on course.course_id = score.course_id
where student.`name` = '唐三藏';

查询所有同学的总成绩，及同学的个人信息

select student.student_id, student.`name`, sum(score.score) 
from student 
join score on student.student_id = score.student_id 
group by student.student_id

外连接

查询没有参加考试的同学信息

-- 由于缺考的学生是有学生信息，但是没有考试信息，需要保留学生信息，
-- 所以要连接学生，那么就用 left join 就是保留学生数据
select student.* from student
left join score on student.student_id = score.student_id
where score.course_id is null;

自连接

这个是适用于行与行之间的比较

显示所有"MySQL'成绩比"JAVA"成绩高的成绩信息

select * from score as s1, score as s2
where s1.student_id = s2.student_id and 
s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score

子查询

不推荐使用，因为一般一条SQL语句是要比较简单的，而这个是违背了初衷

查询与"不想毕业"同学的同班同学

select name from student where name != '不想毕业' and class_id in (
  select class_id from student where name = '不想毕业'
);

合并查询

了解即可

查询student表中 id < 3 的同学和student1表中的所有同学

drop table if exists student1;
-- 创建一样的表
create table student1 like student;
-- 批量插入
insert into student1 select * from student where student_id < 4;

-- 查询student表中 id < 3 的同学和student1表中的所有同学
select * from student where student_id < 3
union all -- 没有 all 那么就会去重
select * from student1;