25道多表查询练习题，做完包会

最新推荐文章于 2024-04-08 03:20:30 发布

自学Java小白

最新推荐文章于 2024-04-08 03:20:30 发布

阅读量343

点赞数 1

分类专栏：学习笔记文章标签： mysql sql

本文链接：https://blog.csdn.net/zmy766138/article/details/127928189

版权

学习笔记专栏收录该内容

12 篇文章 0 订阅

订阅专栏

原文连接(54条消息) 多表查询练习—25道经典题目_因为美好所以在奔跑的博客-CSDN博客

题目来自原文，答案自写，可以参考一下，有问题欢迎指出

建表：

use test_01;
-- 学生表
CREATE TABLE `student`
(
    `s_id`    VARCHAR(20),
    `s_name`  VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex`   VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY (`s_id`)
);
-- 课程表
CREATE TABLE `course`
(
    `c_id`   VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id`   VARCHAR(20) NOT NULL,
    PRIMARY KEY (`c_id`)
);
-- 教师表
CREATE TABLE `teacher`
(
    `t_id`   VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY (`t_id`)
);
-- 成绩表
CREATE TABLE `score`
(
    `s_id`    VARCHAR(20),
    `c_id`    VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY (`s_id`, `c_id`)
);


-- 插入数据
# ---------------------------------------------------

-- 插入学生表测试数据
INSERT INTO Student
VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO Student
VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO Student
VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO Student
VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO Student
VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO Student
VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO Student
VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO Student
VALUES ('08', '王菊', '1990-01-20', '女');
-- 课程表测试数据
INSERT INTO Course
VALUES ('01', '语文', '02');
INSERT INTO Course
VALUES ('02', '数学', '01');
INSERT INTO Course
VALUES ('03', '英语', '03');

-- 教师表测试数据
INSERT INTO Teacher
VALUES ('01', '张三');
INSERT INTO Teacher
VALUES ('02', '李四');
INSERT INTO Teacher
VALUES ('03', '王五');

-- 成绩表测试数据
INSERT INTO Score
VALUES ('01', '01', 80);
INSERT INTO Score
VALUES ('01', '02', 90);
INSERT INTO Score
VALUES ('01', '03', 99);
INSERT INTO Score
VALUES ('02', '01', 70);
INSERT INTO Score
VALUES ('02', '02', 60);
INSERT INTO Score
VALUES ('02', '03', 80);
INSERT INTO Score
VALUES ('03', '01', 80);
INSERT INTO Score
VALUES ('03', '02', 80);
INSERT INTO Score
VALUES ('03', '03', 80);
INSERT INTO Score
VALUES ('04', '01', 50);
INSERT INTO Score
VALUES ('04', '02', 30);
INSERT INTO Score
VALUES ('04', '03', 20);
INSERT INTO Score
VALUES ('05', '01', 76);
INSERT INTO Score
VALUES ('05', '02', 87);
INSERT INTO Score
VALUES ('06', '01', 31);
INSERT INTO Score
VALUES ('06', '03', 34);
INSERT INTO Score
VALUES ('07', '02', 89);
INSERT INTO Score
VALUES ('07', '03', 98);

# 添加外键
alter table score
    add constraint FK_score_student foreign key (s_id) references student (s_id);

alter table score
    add constraint FK_score_course foreign key (c_id) references course (c_id);

alter table course
    add constraint FK_course_teacher foreign key (t_id) references teacher (t_id);

所有题目

可以复制下来然后尝试写，不会的再过来看答案，欢迎交流

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询"李"姓老师的数量（简单）
4、查询学过"张三"老师授课的同学的信息
5、查询没学过"张三"老师授课的同学的信息
6、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
7，查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
8，查询没有学全所有课程的同学的信息
9、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
10、查询没学过"张三"老师讲授的任一门课程的学生姓名
11、检索"01"课程分数小于60，按分数降序排列的学生信息
12、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
13、查询不同老师所教不同课程平均分从高到低显示
14,查询每门课程被选修的学生数
15、查询出只有两门课程的全部学生的学号和姓名
16、查询男生、女生人数
17,、查询名字中含有"风"字的学生信息
18,查询1990年出生的学生名单
19，查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
20,查询课程名称为"数学"，且分数低于60的学生姓名和分数
21，查询所有学生的课程及分数情况
22,查询任何一门课程成绩在70分以上的姓名、课程名称和分数
23，检索至少选修两门课程的学生学号
24，统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，
25,求每门课程的学生人数

题目以及答案

# 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select s.*, s1.s_score 课程1分数, s2.s_score 课程2分数
from Student s,
     score s1,
     score s2
where s.s_id = s1.s_id
  and s.s_id = s2.s_id
  and s1.c_id = '01'
  and s2.c_id = '02'
  and s1.s_score > s2.s_score;
# 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select s.*, s1.s_score 课程1分数, s2.s_score 课程2分数
from Student s,
     score s1,
     score s2
where s.s_id = s1.s_id
  and s.s_id = s2.s_id
  and s1.c_id = '01'
  and s2.c_id = '02'
  and s1.s_score < s2.s_score;
# 3、查询"李"姓老师的数量 （简单）
select count(*) 姓李的老师数
from teacher
where t_name like '李%';
# 4、查询学过"张三"老师授课的同学的信息
select *
from (select s_id
      from score
      where c_id = (select course.c_id from course where t_id = (select t_id from teacher where t_name = '张三'))) sco
         left join student stu on sco.s_id = stu.s_id;
# 5、查询没学过"张三"老师授课的同学的信息
select *
from (select distinct s_id
      from score
      where s_id not in (select s_id
                         from score
                         where c_id =
                               (select course.c_id
                                from course
                                where t_id = (select t_id from teacher where t_name = '张三')))) sco
         left join student stu on sco.s_id = stu.s_id;
# 6、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
# 内连接实现
select *
from student
where student.s_id in (select c1.s_id
                       from (select s_id from score where c_id = 01) c1
                                inner join (select s_id from score where c_id = 02) c2 on c1.s_id = c2.s_id);
# 7，查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select *
from student
where s_id = (select s_id
              from score
              where c_id = 01
                and s_id not in (select s_id from score where c_id = 02));
# 8，查询没有学全所有课程的同学的信息
# 法一
select stu.*
from (select s_id, count(*) a from score group by s_id) a1
         inner join student stu on a1.s_id = stu.s_id
where a < 3;
# 法二
select *
from student
where s_id in (select s_id from score group by s_id having count(s_id) < 3);
# 9、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
# 法一
select *
from student
where s_id in (select distinct s_id from score where c_id in (select c_id from score where s_id = 01));
# 法二
select *
from student a1
         left join (select distinct s_id from score where c_id in (select c_id from score where s_id = 01)) a2
                   on a1.s_id = a2.s_id;
# 10、查询没学过"张三"老师讲授的任一门课程的学生姓名
select *
from student
where s_id = (select distinct s_id
              from score
              where s_id not in (select s_id
                                 from score
                                 where c_id in
                                       (select course.c_id
                                        from course
                                        where t_id = (select t_id from teacher where t_name = '张三'))));
# 11、检索"01"课程分数小于60，按分数降序排列的学生信息
select *
from student
where s_id in (select s_id from score where s_score < 60 order by s_score desc);
# 12、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select student.s_id                                          学号,
       student.s_name                                        姓名,
       max(case when c_id = '01' then s_score ELSE NULL end) 语文,
       max(case when c_id = '02' then s_score ELSE NULL end) 数学,
       max(case when c_id = '03' then s_score ELSE NULL end) 英语,
       avg(s_score)                                          平均成绩
from score
         left join student on score.s_id = student.s_id
group by score.s_id
order by avg(s_score) desc;
# 13、查询不同老师所教不同课程平均分从高到低显示
select cou.c_name   课程名称,
       t.t_name     代课老师,
       avg(s_score) 平均成绩
from score sco
         left join course cou on sco.c_id = cou.c_id
         left join teacher t on cou.t_id = t.t_id
group by sco.c_id
order by avg(sco.s_score) desc;
# 14,查询每门课程被选修的学生数
select cou.c_name 课程名称,
       count(*)   选课人数
from score sco
         left join course cou on sco.c_id = cou.c_id
group by sco.c_id;
# 15、查询出只有两门课程的全部学生的学号和姓名
select stu.s_id   学号,
       stu.s_name 学生姓名,
       count(*)   选修门数
from score sco
         left join student stu on sco.s_id = stu.s_id
group by sco.s_id
having count(*) = 2;
# 16、查询男生、女生人数
SELECT COUNT(1)
FROM `student`
GROUP BY `s_sex`;
select sum(case when s.s_sex = '男' then 1 else 0 end) 男生人数,
       sum(case when s.s_sex = '女' then 1 else 0 end) 女生人数
from student s;
# 17,、查询名字中含有"风"字的学生信息
select *
from student
where s_name like '%风%';
# 18,查询1990年出生的学生名单
select *
from student
where s_birth > 1990;
# 19，查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select sco.s_id         学号,
       stu.s_name       姓名,
       avg(sco.s_score) 平均成绩
from student stu
         right join score sco on sco.s_id = stu.s_id
group by sco.s_id
having avg(sco.s_score) > 85;
# 20,查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.s_name    姓名,
       cou.c_name  课程名,
       sco.s_score 分数
from score sco
         left join student s on sco.s_id = s.s_id
         left join course cou on sco.c_id = cou.c_id
where cou.c_name = '数学'
  and sco.s_score < 60;
# 21，查询所有学生的课程及分数情况
select s.s_name    姓名,
       cou.c_name  课程名,
       sco.s_score 分数
from student s
         left join score sco on sco.s_id = s.s_id
         left join course cou on sco.c_id = cou.c_id;
# 22,查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s.s_name    姓名,
       cou.c_name  课程名,
       sco.s_score 分数
from score sco
         left join student s on sco.s_id = s.s_id
         left join course cou on sco.c_id = cou.c_id
where s.s_id in (select a2.s_id
                 from score a1
                          left join student a2 on a1.s_id = a2.s_id
                          left join course a3 on a1.c_id = a3.c_id
                 group by a2.s_id
                 having min(a1.s_score) > 70);
# 23，检索至少选修两门课程的学生学号
select stu.s_id   学号,
       stu.s_name 学生姓名,
       count(*)   选修门数
from score sco
         left join student stu on sco.s_id = stu.s_id
group by sco.s_id
having count(*) >= 2;
# 24，统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，
select sco.c_id        课程号,
       cou.c_name      课程名称,
       count(sco.c_id) 选修人数
from score sco
         left join course cou on sco.c_id = cou.c_id
group by sco.c_id
having count(sco.s_id)>=5
order by count(sco.s_id) desc;
# 25,求每门课程的学生人数
select sco.c_id        课程号,
       cou.c_name      课程名称,
       count(sco.c_id) 选修人数
from score sco
         left join course cou on sco.c_id = cou.c_id
group by sco.c_id
order by count(sco.s_id) desc;