Mysql的多表查询操作练习

一、准备数据

直接运行下面的SQL即可

CREATE DATABASE school CHARSET utf8;
USE school;

CREATE TABLE student (
 sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
 sname VARCHAR(20) NOT NULL COMMENT '姓名',
 sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
 ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course (
 cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
 cname VARCHAR(20) NOT NULL COMMENT '课程名字',
 tno INT NOT NULL COMMENT '教师编号'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE sc (
 sno INT NOT NULL COMMENT '学号',
 cno INT NOT NULL COMMENT '课程编号',
 score INT NOT NULL DEFAULT 0 COMMENT '成绩'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher (
 tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
 tname VARCHAR(20) NOT NULL COMMENT '教师名字'
) ENGINE=INNODB CHARSET=utf8;
   
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(1,'zhang3',18,'m'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f'),
(8,'oldboy',20,'m'),
(9,'oldgirl',20,'f'),
(10,'oldp',25,'m');

INSERT INTO teacher(tno,tname)
VALUES
(101,'oldboy'),
(102,'xiaoQ'),
(103,'xiaoA'),
(104,'xiaoB');

INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103),
(1004,'go',105);

INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

4张表关联：
        student -（sno）- sc -（cno）- course -（tno）-  teacher

二、题目

01 查询平均成绩大于60分的同学的学号和平均成绩   
02 查询所有同学的学号，姓名。选课数，总成绩
03 查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分
04 统计各位老师，所教课程的及格率
05 查询每门课程被选修的学生数

06 查询出只选修了一门课程的全部学生的学号和姓名

07 查询选修课程门数超过1门的学生信息

08 统计每门课程：优秀（85分以上），良好（70-85），一般（60-70），不及格（小于60）的学生列表
09 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

三、答案

01 查询平均成绩大于60分的同学的学号和平均成绩   

select student.sno,avg(sc.score) as avg_score from student 
join sc on student.sno = sc.sno
group by sc.sno
having avg_score > 60;

02 查询所有同学的学号，姓名。选课数，总成绩

select student.sno,student.sname,count(course.cno) as '选课数量',sum(sc.score) as '总成绩' from student
join sc on student.sno = sc.sno
join course on sc.cno = course.cno
group by student.sno

03 查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分

select course.cno as '课程ID',max(sc.score) as '最高分',min(sc.score) as '最低分' from sc
join course on sc.cno = course.cno
group by sc.cno;

04 统计各位老师，所教课程的及格率

select t1.tno,t1.tname,t1.cno,t1.cname,concat(round(t2.passing_num/total_num*100,2),'%') as '及格率'from 
(
	select teacher.tno,teacher.tname,course.cno,course.cname,count(1) as 'total_num' from teacher
	join course on course.tno = teacher.tno
	join sc on sc.cno = course.cno
	group by teacher.tno,course.cno
) t1
join
(
	select sc.cno,count(1) AS 'passing_num' from student
	join sc on sc.sno = student.sno
	where sc.score >= 60
	group by sc.cno
) t2 on t1.cno = t2.cno;
或者
select teacher.tno,teacher.tname,course.cno,course.cname,
concat(round(
	count(case when sc.score >= 60 then 1 end)/count(1)*100,2
	),'%')
from teacher
join course on course.tno = teacher.tno
join sc on sc.cno = course.cno
group by teacher.tno,course.cno;

CASE WHEN sc.score >= 60 THEN 1 END：

• 这是一个条件表达式，用于检查 sc.score 是否大于或等于 60。
• 如果条件为真（即成绩大于或等于 60），返回 1。
• 如果条件为假（即成绩小于 60），返回 NULL。

05 查询每门课程被选修的学生数

select course.cno,course.cname,count(1) as '学生数量',group_concat(student.sname) 
from student
join sc on sc.sno = student.sno
join course on sc.cno = course.cno 
group by course.cno;

06 查询出只选修了一门课程的全部学生的学号和姓名

select student.sno as '学号',student.sname,count(1) as '选修课程数量' 
from student
join sc on sc.sno=student.sno
join course on sc.cno = course.cno
group by student.sno
having `选修课程数量` = 1;

07 查询选修课程门数超过1门的学生信息

select student.sno as '学号',student.sname,count(1) as '选修课程数量' 
from student
join sc on sc.sno=student.sno
join course on sc.cno = course.cno
group by student.sno
having `选修课程数量` > 1;

08 统计每门课程：优秀（85分以上），良好（70-85），一般（60-70），不及格（小于60）的学生列表

select course.cno,course.cname,
group_concat(case when sc.score > 85 then concat(student.sno,'-',student.sname) end) as '优秀',
group_concat(case when sc.score >= 70 and sc.score <= 85 then concat(student.sno,'-',student.sname) end) as '良好',
group_concat(case when sc.score >= 60 and sc.score < 70 then concat(student.sno,'-',student.sname) end) as '一般',
group_concat(case when sc.score < 60 then concat(student.sno,'-',student.sname) end) as '不及格'
from student
join sc on sc.sno=student.sno
join course on sc.cno = course.cno
group by course.cno;

09 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select student.sno as '学号',student.sname,avg(sc.score) as 'avg_score'
from student
join sc on sc.sno=student.sno
join course on sc.cno = course.cno
group by student.sno
having avg_score > 85;