MySQL经典习题

文章目录

一.建表 1)请写出这5张表,你认为可行的建表语句(注:表中插入数据只是演示) 二.查询 1)查询“生物”课程比“物理”课程成绩高的所有学生的学号 2)查询平均成绩大于60分的同学的学号和平均成绩 3)查询所有同学的学号、姓名、选课数、总成绩 4)查询姓“李”的老师的个数 5)查询没学过“李平”老师课的同学的学号、姓名 6)查询学过“1”并且也学过编号“2”课程的同学的学号、姓名 7)查询学过“李平”老师所教的所有课的同学的学号、姓名 8)查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 9)查询有课程成绩小于60分的同学的学号、姓名 10)查询没有学全所有课的同学的学号、姓名 11)查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名 12)查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名 13)删除学习“叶平”老师课的SC表记录 14)向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩 15)按平均成绩从低到高显示所有学生的“java”、“Mysql”、“linux”三门的课程成绩,按如下形式显示: 学生ID,java,Mysql,linux三门课的有效课程数,有效平均分 16)上题基础上罗列出java,Mysql,linux这三种课程的学习状态,若学习了显示具体分数 否则为null 17)查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分 18)按各科平均成绩从低到高和及格率的百分数从高到低顺序 19)课程平均分从高到低显示(显示任课老师) 20)查询各科成绩前三名的记录 21)查询每门课程被选修的学生数 22)查询出只选修了一门课程的全部学生的学号和姓名 23)查询男生、女生的人数; 24)查询姓“张”的学生名单; 25)查询同名同姓学生名单,并统计同名人数 26)查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 27)查询平均成绩大于85的所有学生的学号、姓名和平均成绩 28)查询课程名称为“java”,且分数低于60的学生姓名和分数 29)求选了课程的学生人数 30)查询选修“波多”老师所授课程的学生中,成绩最高的学生姓名及其成绩 31)查询各个课程及相应的选修人数 32)查询不同课程但成绩相同的学生的学号、课程号、学生成绩 33)查询每门课程成绩最好的前两名 34)检索至少选修两门课程的学生学号 35)查询全部学生都选修的课程的课程号和课程名 36)查询没学过“叶平”老师讲授的任一门课程的学生姓名 37)查询两门以上不及格课程的同学的学号及其平均成绩 38)检索“004”课程分数小于60,按分数降序排列的同学学号; 39)删除“002”同学的“001”课程的成绩; 40)查询成绩表中成绩为85,86或88的学生姓名 41)查询“kb03”班的学生人数 42)查询“苍空“教师任课的学生平均成绩 43)查询和学号为8的同学班级相同的所有学生的姓名 44)查询成绩比该课程平均成绩低的同学的成绩表 45)查询至少有3名女生的班号 46)查询“男”教师及其所上的课程成绩表 47)查询分别展示男,女学员的平均成绩 48)查询选修某课程的同学人数多于5人的教师姓名 49)查询课程表中至少有5名学生选修的并以java开头的课程的平均分数

一.建表

1)请写出这5张表,你认为可行的建表语句(注:表中插入数据只是演示)

具体表结构如下:
1)班级表:tb_class

cid cname
1 KB01
2 KB02
3 KB03

2)学生表:tb_student

sid sname gender class_id
1 C罗 1
2 梅西 1
3 碧昂斯 2

3)教师表:tb_teacher

tid tname
1 波多
2 苍空
3 饭岛

4)课程表:tb_course

cid cname teacher_id
1 Java 1
2 Java web 1
3 Mysql 2

5)成绩表:tb_score

sid stu_id corse_id scores
1 1 1 60
2 1 2 85
3 2 2 100
#创建数据库
CREATE DATABASE IF NOT EXISTS Practice;
USE Practice;
/*表结构如下:
班级表:tb_class
cid	cname
1	KB01
2	KB02
3	KB03
*/
-- 创建班级表
CREATE TABLE IF NOT EXISTS class(
	classId INT(11),
	className VARCHAR(50)
);
-- 插入数据
INSERT INTO class VALUES(1,'KB01'),(2,'KB02'),(3,'KB03');
/*
学生表:tb_student
sid	sname	gender	class_id
1	C罗	男	1
2	梅西	男	1
3	碧昂斯	女	2
*/
-- 创建学生表
CREATE TABLE IF NOT EXISTS student(
	studentId INT(11),
	studentName VARCHAR(50),
	sex VARCHAR(50),
	classId INT(11)
);
-- 插入数据
INSERT INTO student VALUES(1,'c罗','男',1),(2,'梅西','男',1),(3,'碧昂斯','女',2);
/*
教师表:tb_teacher
tid	tname
1	波多
2	苍空
3	饭岛
*/
-- 创建教师表
CREATE TABLE IF NOT EXISTS teacher (
	teacherId INT(11),
	teacherName VARCHAR(50)
);
-- 插入数据
INSERT INTO teacher VALUES(1,'波多'),(2,'苍空'),(3,'饭岛');
/*
课程表:tb_course
cid	cname	teacher_id
1	Java	1
2	Java web	1
3	Mysql	2
*/
-- 创建课程表
CREATE TABLE IF NOT EXISTS course(
	courseId INT(11),
	courseName VARCHAR(50),
	teacherId INT(11)
);
-- 插入数据
INSERT INTO course VALUES(1,'java',1),(2,'java web',1),(3,'Mysql',2);
/*
成绩表:tb_score
sid	stu_id	corse_id	scores
1	1	1	60
2	1	2	85
3	2	2	100
*/
-- 创建分数表
CREATE TABLE IF NOT EXISTS score(
	scoreId INT(11),
	studentId INT(11),
	courseId INT(11),
	scores INT(11)
);
#课程表增加python、hive、linux课程,每个教师补齐2门
INSERT INTO course VALUES(4,'python',1),(5,'hive',2),(6,'linux',3);

二.查询

1)查询“生物”课程比“物理”课程成绩高的所有学生的学号

#1、查询“生物”课程比“物理”课程成绩高的所有学生的学号;
/*
思路分析:需要用到自连接(条件学号相同)
然后where条件该同学生物成绩>物理成绩
*/
SELECT DISTINCT s2.`studentId` 学号
FROM score s1 JOIN score s2 
ON s1.`studentId`=s2.`studentId`
WHERE s1.`scores`>s2.`scores` AND s1.`courseId`= (SELECT course.`courseId` FROM course WHERE courseName='生物') AND
s2.`courseId`= (SELECT course.`courseId` FROM course WHERE courseName='物理');

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

#2、查询平均成绩大于60分的同学的学号和平均成绩;
/*
思路分析每个人的平均成绩-->按学生分组
平均成绩>60-->分组之后再次筛选大于60分的人
*/
SELECT s.`studentId` 学号, stu.`studentName` 姓名,AVG(s.`scores`) 平均分
FROM score s JOIN student stu
ON stu.`studentId`=s.`studentId`
GROUP BY s.`studentId`
HAVING 平均分>60;

3)查询所有同学的学号、姓名、选课数、总成绩

#3、查询所有同学的学号、姓名、选课数、总成绩
/*
思路分析:学生表按照学生学号分组(学号唯一)内连分数表
计算选课数count函数和总成绩sum函数
*/
SELECT s.`studentId` 学号,stu.`studentName` 姓名,COUNT(s.`courseId`) 选课数, SUM(s.`scores`) 总成绩
FROM student stu JOIN score s
ON s.`studentId`=stu.`studentId`
GROUP BY s.`studentId`;

4)查询姓“李”的老师的个数

#4、查询姓“李”的老师的个数
/*
思路分析:姓李需要用到模糊查询like 个数需要用到count函数
*/
SELECT COUNT(1) 李老师个数 
FROM teacher t
WHERE t.`teacherName`  LIKE '李%';

5)查询没学过“李平”老师课的同学的学号、姓名

#5、查询没学过“李平”老师课的同学的学号、姓名;
/*
思路分析:首先查询李平老师教的课程,在查询选了李平老师的课程学生,最后再排除掉这些学生即可
*/
#(1)查出李平老师所受的课
SELECT c.`courseName` 课程
FROM course c JOIN teacher t
ON t.`teacherId`=c.`teacherId`
WHERE teacherName='李平';
#(2)查出选择李平老师讲课的学生
SELECT s.`studentId` 学号
FROM score sc JOIN student s JOIN course c
ON sc.`studentId`=s.`studentId` AND c.`courseId`=sc.`courseId`
WHERE c.`courseName`=(
	SELECT c.`courseName` 课程
	FROM course c JOIN teacher t
	ON t.`teacherId`=c.`teacherId`
	WHERE teacherName='李平');
#(3)排除选择李平老师讲课的学生
SELECT stu.`studentId` 学号,stu.`studentName` 姓名
FROM student stu
WHERE stu.`studentId` NOT IN(
	SELECT s.`studentId` 学号
	FROM score sc JOIN student s JOIN course c
	ON sc.`studentId`=s.`studentId` AND c.`courseId`=sc.`courseId`
	WHERE c.`courseName`=(
		SELECT c.`courseName` 课程
		FROM course c JOIN teacher t
		ON t.`teacherId`=c.`teacherId`
		WHERE teacherName='李平'));

6)查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

#6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名;
/*
思路分析:按照学号分组先查学过1或2的学生然后再筛选选了两门课程的学生
*/
SELECT s.`studentId` 学号,stu.`studentName` 姓名
FROM score s JOIN student stu
ON s.`studentId`=stu.`studentId`
WHERE s.`courseId` IN(1,2)
GROUP BY s.`studentId`
HAVING COUNT(s.`courseId`)=2;

7)查询学过“李平”老师所教的所有课的同学的学号、姓名

#7、查询学过“李平”老师所教的所有课的同学的学号、姓名;
/*
思路分析:内连学生表 分数表 和课程表(条件学生id相同 课程id相同)
		条件:学生分数表里内连的课程名和李平老师教的课完全相同(子查询作为条件)
*/
SELECT s.`studentId` 学号,s.`studentName` 姓名
FROM score sc JOIN student s JOIN course c
ON sc.`studentId`=s.`studentId` AND c.`courseId`=sc.`courseId`
WHERE c.`courseName`=(
	SELECT c.`courseName` 课程
	FROM course c JOIN teacher t
	ON t.`teacherId`=c.`teacherId`
	WHERE teacherName='李平');

8)查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

#8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
/*
思路分析:分数表自连接内连学生表(条件是学号相同)
		where条件为:第一张分数表的课程id为1 第二张分数表id为2 并且 第一张分数表的分数大于第二章分数表的分数
*/
SELECT stu.`studentId` 学号,stu.`studentName` 姓名
FROM score sc1 JOIN score sc2 JOIN student stu
ON sc1.`studentId`=sc2.`studentId` AND stu.`studentId`=sc1.`studentId`
WHERE sc1.`courseId`=1 AND sc2.`courseId`=2 AND sc1.`scores`>sc2.`scores`;

9)查询有课程成绩小于60分的同学的学号、姓名

#9、查询有课程成绩小于60分的同学的学号、姓名;
/*
思路分析:查询小于60分的分数表内连学生表(条件:学号相同)即可
*/
SELECT s.`studentId` 学号,stu.`studentName` 姓名,s.`scores` 成绩
FROM score s JOIN student stu
ON s.`studentId`=stu.`studentId`
WHERE s.`scores`<60;

10)查询没有学全所有课的同学的学号、姓名

#10、查询没有学全所有课的同学的学号、姓名
/*
思路分析:分数表内连学生表(条件:学号相同)
		分数表按照学号分组 再筛选:每个学生所学的课程数小于总课程数
*/
SELECT s.`studentId` 学号,stu.`studentName`
FROM score s JOIN student stu
ON s.`studentId`=stu.`studentId`
GROUP BY s.`studentId`
HAVING COUNT(s.`courseId`) < (SELECT COUNT(1) FROM course);

11)查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

#11、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
/*
思路分析:查询001号同学所学课程的课程号(子查询作为条件)
		查询所学课程号和001号同学至少有一门课程相同的同学(用in条件,注意别忘记排除1号同学)
*/
SELECT DISTINCT stu.`studentId` 学号,stu.`studentName` 姓名
FROM student stu JOIN score sc
ON sc.`studentId`=stu.`studentId`
WHERE sc.`courseId` IN(
	SELECT s.`courseId`
	FROM score s 
	WHERE s.`studentId`=1)
	AND stu.`studentId`!=1;

12)查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名

#12、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名;
/*
思路分析:课程完全相同(不是值相等而是两个结果集相同)需要用到group_concat函数合并查出来的行集合(一般来说需要排序否则不匹配)
	具体语法group_concat(需要合并的字段,按照这个字段排序)如:group_concat(courseId,order by courseId)
	注意最后需要排除2号同学
*/
#使用group_concat函数-->合并行集合可使集合相等
SELECT a.id,stu.`studentName`
FROM 	(
	SELECT GROUP_CONCAT(s.`courseId` ORDER BY s.`courseId`) c, s.`studentId` id
	FROM score  s
	GROUP BY s.`studentId`) a JOIN student stu ON stu.`studentId`=a.id
WHERE a.c=(
	SELECT GROUP_CONCAT(s.`courseId` ORDER BY s.`courseId`)
	FROM score s
	WHERE s.`studentId`=2) AND a.id!=2;

13)删除学习“叶平”老师课的SC表记录

#13、删除学习“叶平”老师课的SC表记录;
/*
思路分析:子查询搞定!
*/
DELETE FROM score 
WHERE score.`courseId`=
	(SELECT  c.`courseId`
	FROM course c
	WHERE c.`teacherId`=(
		SELECT t.`teacherId`
		FROM teacher t
		WHERE t.`teacherName`='叶平'
		));

14)向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩

#14、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩
/*
思路分析:需要用到insert into ... select ...语法,如下
*/
INSERT INTO score(studentId,corseId,scores) SELECT a.studentId,2,b.av #课程id没有要求随便给的一个
FROM
(
SELECT s.`studentId`
FROM score s
WHERE s.`studentId` NOT IN(
	SELECT s.`studentId`
	FROM score s
	WHERE s.`courseId`=2)
	) a,(SELECT AVG(s.`scores`) av
            FROM score s
            WHERE s.`courseId`=2) b;

15)按平均成绩从低到高显示所有学生的“java”、“Mysql”、“linux”三门的课程成绩,按如下形式显示: 学生ID,java,Mysql,linux三门课的有效课程数,有效平均分

#15、按平均成绩从低到高显示所有学生的“java”、“Mysql”、“linux”三门的课程成绩,按如下形式显示: 
#学生ID,java,Mysql,linux三门课的有效课程数,有效平均分;
#1)简单版:没有具体罗列出每门课程直接查的数量
SELECT s.`studentId` 学号 ,COUNT(s.`courseId`) 有效课程数,AVG(s.`scores`)有效平均分
FROM score s JOIN course c
ON c.`courseId`=s.`courseId`
WHERE s.`courseId` IN  (
	(SELECT c.`courseId`
	FROM course c
	WHERE c.`courseName`='java'),
	(SELECT c.`courseId`
	FROM course c
	WHERE c.`courseName`='Mysql'),
	(SELECT c.`courseId`
	FROM course c
	WHERE c.`courseName`='linux')
	)
GROUP BY s.`studentId`;

16)上题基础上罗列出java,Mysql,linux这三种课程的学习状态,若学习了显示具体分数 否则为null

#16复杂版罗列出学习这三门课的情况
/*
思路分析:需要单独计算使用case when 语句
*/
SELECT 
	s.studentid,
	CASE WHEN EXISTS(SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId` FROM course c WHERE c.`courseName`='java') AND sc1.Studentid = s.studentid) THEN (SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId` FROM course c WHERE c.`courseName`='java') AND sc1.Studentid = s.studentid) ELSE NULL END AS 'java',
	CASE WHEN EXISTS(SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='Mysql') AND sc1.Studentid = s.studentid) THEN (SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='Mysql') AND sc1.Studentid = s.studentid) ELSE NULL END AS 'Mysql',
	CASE WHEN EXISTS(SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='linux') AND sc1.Studentid = s.studentid) THEN (SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='linux') AND sc1.Studentid = s.studentid) ELSE NULL END AS 'linux',
	(SELECT COUNT(courseid) FROM  score  WHERE Courseid IN( (SELECT c.`courseId` FROM course c WHERE c.`courseName`='java'),
	(SELECT c.`courseId`FROM course c WHERE c.`courseName`='Mysql'),
	(SELECT c.`courseId`FROM course c WHERE c.`courseName`='linux')) AND s.Studentid = score.Studentid GROUP BY Studentid ) AS '有效课程数',
	(CASE WHEN EXISTS(SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId` FROM course c WHERE c.`courseName`='java') AND sc1.Studentid = s.studentid) THEN (SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId` FROM course c WHERE c.`courseName`='java') AND sc1.Studentid = s.studentid) ELSE 0 END + 
	CASE WHEN EXISTS(SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='Mysql') AND sc1.Studentid = s.studentid) THEN (SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='Mysql') AND sc1.Studentid = s.studentid) ELSE 0 END +
	CASE WHEN EXISTS(SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='linux') AND sc1.Studentid = s.studentid) THEN (SELECT sc1.scores FROM score sc1 WHERE sc1.Courseid = (SELECT c.`courseId`FROM course c WHERE c.`courseName`='linux') AND sc1.Studentid = s.studentid) ELSE 0 END ) /
(SELECT COUNT(Courseid) FROM  score  WHERE Courseid IN ((SELECT c.`courseId` FROM course c WHERE c.`courseName`='java'),
	(SELECT c.`courseId`FROM course c WHERE c.`courseName`='Mysql'),
	(SELECT c.`courseId`FROM course c WHERE c.`courseName`='linux')) AND s.Studentid = score.Studentid GROUP BY Studentid ) AS '有效平均分'
FROM student s;

效果如下图:
17题效果图

17)查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

#17、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
#思路:通过课程id来进行分组,然后用聚合函数max,min来找出最大值和最小值。
SELECT s.`courseId` 课程id,MAX(s.`scores`) 最高分,MIN(s.`scores`) 最低分 
FROM score s 
GROUP BY  s.`courseId`

18)按各科平均成绩从低到高和及格率的百分数从高到低顺序

mysql常用流程控制语句见以下链接:https://blog.csdn.net/sun_0128/article/details/106771090

#18、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
/*
思路分析:及格率需要case when语句和sum count函数结合使用
*/
SELECT s.`scoreId` 科目号,AVG(s.`scores`) 平均分,(SUM(CASE 
							WHEN s.`scores`>=60 THEN 1 	#大于60+1
							ELSE 0
							END))/COUNT(1) 合格率
FROM score s
WHERE TRUE
GROUP BY s.`courseId`
ORDER BY 平均分 ASC,合格率 DESC;

19)课程平均分从高到低显示(显示任课老师)

#19、课程平均分从高到低显示(显示任课老师);
/*
思路分析:分数表按按课程id分组内连课程表和教师表(条件:课程id相同 教师id相同),再按平均分降序排序
*/

SELECT c.`courseId` 科目编号,c.`courseName` 科目名,AVG(s.`scores`) 平均分,t.`teacherName` 任课老师
FROM score s JOIN course c JOIN teacher t
ON s.`courseId`=c.`courseId` AND t.`teacherId`=c.`teacherId`
GROUP BY s.`courseId`
ORDER BY  平均分 DESC;

20)查询各科成绩前三名的记录

思路分析见下面这个作者的博客链接(ps:很详细):

mysql查询分组之后排名(不考虑并列)

#20、查询各科成绩前三名的记录:(不考虑成绩并列情况)?-->前三名怎么确定?
SELECT s.`courseId` 科目号,s.`scores` 分数,s.`studentId` 学号,COUNT(s2.`studentId`)	-- >显示了并列的情况
FROM score s LEFT JOIN score s2
ON s.`courseId`=s2.`courseId` AND s.`scores`<s2.`scores`  
GROUP BY s.`courseId` ,s.`studentId`
HAVING COUNT(s2.`studentId`)<3		#越大的次数越小
ORDER BY s.`courseId` ,s.`scores` DESC;

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

#21、查询每门课程被选修的学生数;
/*
思路分析:分数表按课程id分组,使用count函数即可
*/
SELECT s.`courseId` 科目号,COUNT(s.`studentId`) 人数
FROM score s
GROUP BY s.`courseId`;

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

#22、查询出只选修了一门课程的全部学生的学号和姓名;
/*
思路分析:分数表按学生id分组,在筛选只有一门课程的学生(count函数) 内连学生表(条件:学生di相同)即可
*/
SELECT s.`studentId` 学号, stu.`studentName` 姓名
FROM score s JOIN student stu
ON s.`studentId`=stu.`studentId`
GROUP BY s.`studentId`
HAVING COUNT(s.`courseId`)=1;

23)查询男生、女生的人数;

#23、查询男生、女生的人数;
/*
思路分析:学生表按性别分组,并使用count函数即可
*/
SELECT s.`sex` 性别, COUNT(s.`sex`) 人数 
FROM student s
GROUP BY s.`sex`;

24)查询姓“张”的学生名单;

#24、查询姓“张”的学生名单;
/*
思路分析:姓张-->模糊查询like
*/
SELECT s.`studentId` 学号, s.`studentName` 姓名
FROM student s
WHERE s.`studentName` LIKE '张%';

25)查询同名同姓学生名单,并统计同名人数

#25、查询同名同姓学生名单,并统计同名人数;
/*
思路分析:何为同名同姓?-->学生姓名相同且学号不同(学生表自连接)
*/
SELECT s1.`studentName` 姓名,COUNT(s2.`studentName`) 人数
FROM student s1 JOIN student s2
WHERE s1.`studentName`=s2.`studentName` AND s1.`studentId`!=s2.`studentId`
GROUP BY s2.`studentName`;

26)查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

#26、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
/*
思路分析:分数表按课程id分组,先按平均分升序,再按课程号降序
*/
SELECT s.`courseId` 科目号,AVG(s.`scores`) 平均分
FROM score s
GROUP BY s.`courseId`
ORDER BY 平均分,s.`courseId` DESC;

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

#27、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;
/*
思路分析:分数表按学生id分组,再筛选平均分大于85的人
*/
SELECT s.`studentId` 学号,stu.`studentName` 姓名,AVG(s.`scores`) 平均分
FROM score s JOIN student stu
ON s.`studentId`=stu.`studentId`
GROUP BY s.`studentId`
HAVING AVG(s.`scores`)>85;

28)查询课程名称为“java”,且分数低于60的学生姓名和分数

#28、查询课程名称为“java”,且分数低于60的学生姓名和分数;
SELECT s.`studentId`  学号,stu.`studentName` 姓名
FROM score s JOIN course c JOIN student stu
ON c.`courseId`=s.`courseId` AND stu.`studentId`=s.`studentId`
WHERE	 c.`courseName`='java' AND s.`scores`<60;

29)求选了课程的学生人数

#29、求选了课程的学生人数
/*
思路分析:分数表按照学生id分组再筛选选课程数>=1的人,在用一次count函数即可
*/
SELECT COUNT(1) 选课人数
FROM(
	SELECT s.`studentId`
	FROM score s
	GROUP BY s.`studentId`
	HAVING COUNT(s.`courseId`)>=1) s;

30)查询选修“波多”老师所授课程的学生中,成绩最高的学生姓名及其成绩

#30、查询选修“波多”老师所授课程的学生中,成绩最高的学生姓名及其成绩;
/*
思路分析:分数表内连课程表内连教师表内连学生表(条件:课程id相同 教师id相同 学生id相同)
		where条件:老师姓名=波多,再用max函数即可
*/
SELECT stu.`studentName` 姓名,c.`courseName` 科目名,MAX(s.`scores`) 最高分
FROM score s JOIN course c JOIN teacher t JOIN student stu
ON s.`courseId`=c.`courseId` AND t.`teacherId`=c.`teacherId` AND stu.`studentId`=s.`studentId`
WHERE t.`teacherName`='波多';

31)查询各个课程及相应的选修人数

#31、查询各个课程及相应的选修人数;
/*
思路分析:分数表按照课程id分组,然后使用count函数即可
*/
SELECT s.`courseId` 科目号,COUNT(s.`studentId`) 人数
FROM score s
GROUP BY s.`courseId`;

32)查询不同课程但成绩相同的学生的学号、课程号、学生成绩

#32、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
/*
思路分析:分数表自连接(条件:第一个和第二个分数表的分数相等且课程id不等)
*/
SELECT s.`studentId` 学号1,s.`courseId` 课程号1 , s2.`studentId` 学号2, s2.`courseId` 课程号2,s.`scores` 成绩
FROM score s JOIN score s2 
ON s.`scores`=s2.`scores` AND s.`courseId`!=s2.`courseId`;

33)查询每门课程成绩最好的前两名

#33、查询每门课程成绩最好的前两名; -- 这不与21题一样的吗
SELECT s.`courseId` 科目号,s.`scores` 分数,s.`studentId` 学号 -- >会出现并列
FROM score s LEFT JOIN score s2
ON s.`courseId`=s2.`courseId` AND s.`scores`<s2.`scores`
GROUP BY s.`courseId` ,s.`studentId`
HAVING COUNT(s2.`courseId`)<2 
ORDER BY s.`courseId`;
#每门课的第一名
/*
思路分析:分数表按课程id分组然后用max函数即可
*/
SELECT s.`courseId`,MAX(s.`scores`)
FROM score s 
GROUP BY s.`courseId`;
#某门课的前两名
/*
思路分析:查询分数表条件课程id为某个值,按分数降序排列,分页展示2行
*/
SELECT *
FROM score s
WHERE s.`courseId`=1
ORDER BY s.`scores` DESC
LIMIT 2;

34)检索至少选修两门课程的学生学号

#34、检索至少选修两门课程的学生学号;
/*
思路分析:按照学生id分组后再筛选选两门课程以上的学生
*/
SELECT s.`studentId` 学号
FROM score s
GROUP BY s.`studentId`
HAVING COUNT(s.`courseId`)>=2;

35)查询全部学生都选修的课程的课程号和课程名

#35、查询全部学生都选修的课程的课程号和课程名;
/*
思路分析:分数表按照课程id分组,再筛选课程选课学生数与全部学生数相等的课程
*/
SELECT s.`courseId` 课程号,c.`courseName` 课程名
FROM score s JOIN course c
ON c.`courseId`=s.`courseId`
GROUP BY s.`courseId`
HAVING COUNT(s.`studentId`)=(
	SELECT COUNT(s.`studentId`)
	FROM student s);

36)查询没学过“叶平”老师讲授的任一门课程的学生姓名

#36、查询没学过“叶平”老师讲授的任一门课程的学生姓名;
SELECT stu.`studentId` 学号,stu.`studentName` 姓名	#排除选课的学生
FROM student stu
WHERE stu.`studentId` NOT IN(
	SELECT DISTINCT s.`studentId` 		#查选了老师课的学生
	FROM score s
	WHERE s.`courseId`  IN(
		SELECT c.`courseId`		#查老师课程id
		FROM course c
		WHERE c.`teacherId`=(	
			SELECT t.`teacherId`	#查老师id
			FROM teacher t
			WHERE t.`teacherName`='叶平')));

37)查询两门以上不及格课程的同学的学号及其平均成绩

#37、查询两门以上不及格课程的同学的学号及其平均成绩;
/*
思路分析:分数表按照学生id分组,再筛选两门课以上不及格的人(使用case when 语句)
*/
SELECT s.`studentId`,AVG(s.`scores`)
FROM score s
GROUP BY s.`studentId`
HAVING SUM(CASE 
		WHEN s.`scores`<60 THEN 1
		ELSE 0
		END)>=2;

38)检索“004”课程分数小于60,按分数降序排列的同学学号;

#38、检索“004”课程分数小于60,按分数降序排列的同学学号;
/*
思路分析:分数表查询条件:课程id为4且分数小于60然后按分数降序排列
*/
SELECT s.`studentId` 学号,s.`scores` 分数
FROM score s
WHERE s.`courseId`=4 AND s.`scores`<60
ORDER BY s.`scores` DESC;

39)删除“002”同学的“001”课程的成绩;

#39、删除“002”同学的“001”课程的成绩;
DELETE FROM score  WHERE studentId=2 AND courseId=1;

40)查询成绩表中成绩为85,86或88的学生姓名

#41、查询成绩表中成绩为85,86或88的学生姓名
SELECT DISTINCT stu.`studentName` 姓名
FROM score s JOIN student stu
ON s.`studentId`=stu.`studentId`
WHERE s.`scores` IN(85,86,88);

41)查询“kb03”班的学生人数

#41、查询“kb03”班的学生人数
SELECT COUNT(1) 人数
FROM student stu
WHERE stu.`classId`=(
	SELECT c.`classId`
	FROM class c
	WHERE c.`className`='KB03');

42)查询“苍空“教师任课的学生平均成绩

#42、查询“苍空“教师任课的学生平均成绩
/*
思路分析:子查询
*/
SELECT AVG(s.`scores`) 平均成绩
FROM score s
WHERE s.`courseId` IN(
	(SELECT c.`courseId`
	FROM course c
	WHERE c.`teacherId`=
		(SELECT t.`teacherId`
		FROM teacher t
		WHERE t.`teacherName`='苍空')));

43)查询和学号为8的同学班级相同的所有学生的姓名

#43、查询和学号为8的同学班级相同的所有学生的姓名
SELECT stu.`studentName` 姓名
FROM student stu
WHERE stu.`classId` = (
	SELECT s.`classId`
	FROM student s
	WHERE s.`studentId`=8) AND stu.`studentId`!=8;

44)查询成绩比该课程平均成绩低的同学的成绩表

#44、查询成绩比该课程平均成绩低的同学的成绩表
SELECT s.`scores` 成绩,s.`courseId` 课程号
FROM score s
GROUP BY s.`courseId`
HAVING s.`scores`<AVG(s.`scores`);

45)查询至少有3名女生的班号

#45、查询至少有3名女生的班号
SELECT stu.`classId`
FROM student stu
WHERE stu.`sex`='女'
GROUP BY stu.`classId`
HAVING COUNT(1)>=3;

46)查询“男”教师及其所上的课程成绩表

#46、查询“男”教师及其所上的课程成绩表
SELECT s.`courseId`,s.`scores`
FROM score s
WHERE s.`courseId` IN
	(SELECT c.`courseId`
	FROM course c
	WHERE c.`teacherId` IN(
		SELECT t.`teacherId`
		FROM teacher t
		WHERE t.`sex`='男'));

47)查询分别展示男,女学员的平均成绩

#47、查询分别展示男,女学员的平均成绩
SELECT stu.`sex` 性别,AVG(s.`scores`) 平均分
FROM score s JOIN student stu
ON stu.`studentId`=s.`studentId`
GROUP BY stu.`sex`;

48)查询选修某课程的同学人数多于5人的教师姓名

#48、查询选修某课程的同学人数多于5人的教师姓名
SELECT DISTINCT t.`teacherName` 姓名
FROM score s JOIN teacher t JOIN course c
ON s.`courseId`=c.`courseId` AND c.`teacherId`=t.`teacherId`
GROUP BY s.`courseId`
HAVING COUNT(s.`studentId`)>5;

49)查询课程表中至少有5名学生选修的并以java开头的课程的平均分数

#49、查询课程表中至少有5名学生选修的并以java开头的课程的平均分数
SELECT c.`courseName` 课程名,AVG(s.`scores`) 平均分
FROM score s JOIN course c
ON c.`courseId`=s.`courseId`
WHERE c.`courseName` LIKE 'java%'
GROUP BY s.`courseId`
HAVING COUNT(s.`studentId`)>=5;
栏目