MySQL经典习题

最新推荐文章于 2023-06-14 14:20:15 发布
最新推荐文章于 2023-06-14 14:20:15 发布
阅读量1.6k 收藏 11
点赞数 2
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sun_0128/article/details/106814065
版权

文章目录

一.建表

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

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

cidcname
1KB01
2KB02
3KB03

2)学生表:tb_student

sidsnamegenderclass_id
1C罗男1
2梅西男1
3碧昂斯女2

3)教师表:tb_teacher

tidtname
1波多
2苍空
3饭岛

4)课程表:tb_course

cidcnameteacher_id
1Java1
2Java web1
3Mysql2

5)成绩表:tb_score

sidstu_idcorse_idscores
11160
21285
322100
#创建数据库
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;
sun_0128
关注
  • 2
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL习题
大熊的博客
07-13 3万+
搜集的一些MySQL的基础习题附自己的答案，不涉及排名。答案不是最好的写法，是比较基本的思路。 建表 一共有四个表，主键对应关系为学生表对应成绩表，再到课程表，最后到教师表。 ## 学生表 create table Student(id varchar(10),name varchar(10),age datetime,sex nvarchar(10)); insert into Stude...
mysql经典例题50道52
weixin_51630304的博客
08-12 276
mysql经典例题50道52初学mysql写了50道例题 初学mysql写了50道例题 建表：create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做出来才能达到最好的训练效果。（感觉做这种题是会上瘾的……） 我使用的Mysql版本是5.7.19。SQL语句可能会因数据库系统的不同会有少许差异。 这里放上MySQL语法执行的先后顺序： 7:SELECT 查询列表 1:FROM 表 2:连接类型 JOIN 表2 3:ON 连接条件 4:WHERE 筛选条件 5:GROUP BY 分组 6:HAV
数据库练习
qq_45593650的博客
05-15 144
#创建数据库 create database EMS DEFAULT CHARSET 'utf8'; use EMS; #创建学生表 create table Student( s_id varchar(20) primary key, s_name varchar(20) not null, s_birth varchar(20) not null, s_sex varchar(10) no...
MySQL经典例题
zhangxuelong461的博客
01-10 562
MySQL经典例题 学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 教师表 Teacher(t_id,t_name) –教师编号,教师姓名 成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数 ...
MYSQL(经典50题）
qq_50575277的博客
08-11 860
mysql
MySQL经典练习题.sql
05-27
mysql经典练习题以及答案
MySQL经典习题项目
01-20
MySQL经典例题，全程手敲，之间有两三道实在想不出来，借鉴的之前的大佬的，欢迎大家批评指正，一起学习
mysql经典练习题
10-07
经常练习有好处，很好用的mysql基础练习题，非常建议拿来练手！
mysql练习题
05-16
mysql练习题，一些简单的mysal练习题，适合初学者哦
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
经典SQL练习题(MySQL版).sql
01-04
网上有一篇关于SQL的经典文章，超经典SQL练习题，做完这些你的SQL就过关了，引用和分析它的人很多，于是今天复习SQL的时候找来练了练手。
mysql经典50题
04-01
mysql经典50题。以及注释，经过实战运行及改善，可以直接用。挺有用的，对于sql入门及网申笔试来说。
MySQL经典50道练习题及全网最详细解析
热门推荐
hujieliang123的博客
01-16 1万+
MySQL经典50道练习全网最详细解析
Mysql经典例题练习与总结
qq_42265608的博客
06-14 3851
Mysql练习题
mysql练习_Mysql 经典练习题
weixin_39534395的博客
12-05 2913
我使用的Mysql版本是5.7.27-log，答案可能会因版本会有少许出入。# 一、数据源准备及说明：## 1.1、数据字段说明:1.学生表 Student(SId,Sname,Sage,Ssex)SId ：学生编号Sname：学生姓名Sage ：出生年月Ssex：学生性别2.课程表 Course(CId,Cname,TId) CId ：课程编号Cname ：课程名称TId ：教师编号3.教师表 ...
MySQL经典练习题（一）
weixin_39562364的博客
07-02 456
建立学生成绩表scores create table if not exists scores( name varchar(2) not null default ' ', course varchar(4) not null default ' '); 改变表结构，插入列： alter table scores add grade tinyint(1) not null default '0'; 插入数据 单行插入： insert into scores v...
MYSQL经典题型详情解析
gongpulin的博客
09-24 299
https://blog.csdn.net/jianchilu/article/details/81429433
MySql例题
凉快
03-11 336
例题一级目录二级目录三级目录DQL基础查询分组查询、分组函数的使用连接查询子查询联表查询DML插入修改删除DDL创建表 ★修改表删除表复制表自增长列 一级目录 二级目录 三级目录 DQL 基础查询 #查询员工号为176的员工的姓名和部门号和年薪(加上奖金率) SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees; #查询没有奖金，且工资小于18000的salary，last_name(
mysql经典50道练习题pdf
最新发布
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值