©2020-2024 ioDraw All rights reserved
<>数据准备
建表、插入数据
-- 学生表 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_nameVARCHAR(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
Studentvalues('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
Studentvalues('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
Scorevalues('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'
, '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);
<>题目
<>– 1
、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT stu.*,sco.s_score FROM student stu LEFT JOIN score sco ON stu.s_id=sco.
s_idWHERE stu.s_id IN( SELECT stu.s_id FROM score sco1 WHERE sco1.c_id='01' AND
sco1.s_score > ( SELECT sco2.s_score FROM score sco2 WHERE sco1.s_id=sco2.s_id
AND sco2.c_id='02' ) ); <>– 2
、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT stu.*,sco.s_score FROM student stu LEFT JOIN score sco on stu.s_id=sco.
s_idWHERE stu.s_id in( SELECT sco1.s_id FROM score sco1 where sco1.c_id='01' AND
sco1.s_score <( SELECT sco2.s_score FROM score sco2 where sco2.c_id='02' AND
sco1.s_id=sco2.s_id ) ); <>– 3
、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT stu.s_id,stu.s_name,avg(sco.s_score) AS '平均成绩' FROM student stu LEFT
JOIN score sco ON stu.s_id=sco.s_id GROUP BY stu.s_id HAVING avg(sco.s_score)>=
60; <>– 4
、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
(包括有成绩的和无成绩的)
SELECT stu.s_id,stu.s_name,avg(sco.s_score) AS '平均成绩' FROM student stu LEFT
JOIN score sco ON stu.s_id=sco.s_id GROUP BY stu.s_id HAVING avg(sco.s_score)<60
or avg(sco.s_score) is null; <>– 5
、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT stu.s_id, stu.s_name, count(sco.c_id) AS '选课总数', sum(sco.s_score) as
'总成绩' from student stu LEFT JOIN score sco ON stu.s_id=sco.s_id GROUP BY stu.
s_id; <>– 6
、查询"李"姓老师的数量
SELECT COUNT(*) FROM teacher t WHERE t.t_name LIKE '李%'; <>– 7
、查询学过"张三"老师授课的同学的信息
SELECT stu.* FROM student stu LEFT JOIN score sco on stu.s_id=sco.s_id LEFT
JOIN course cou ON sco.c_id=cou.c_id LEFT JOIN teacher tea ON cou.t_id=tea.t_id
where tea.t_name='张三'; <>– 8
、查询没学过"张三"老师授课的同学的信息
SELECT stu.* FROM student stu WHERE stu.s_id IN( SELECT te.t_id FROM teacher te
left JOIN course co ON co.t_id = te.t_id left JOIN score so ON co.c_id=so.c_id
LEFT JOIN student st ON st.s_id = so.s_id WHERE te.t_name <> '张三' ); <>– 9
、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT stu.* FROM score sco LEFT JOIN student stu ON stu.s_id=sco.s_id WHERE
sco.c_id='01' AND sco.s_id =( SELECT sco1.s_id from score sco1 WHERE sco1.c_id=
'02' and sco.s_id=sco1.s_id) <>10
、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT stu.* FROM score sco LEFT JOIN student stu ON stu.s_id=sco.s_id WHERE
sco.c_id='01' AND stu.s_id NOT IN( SELECT sco1.s_id FROM score sco1 WHERE sco1.
c_id='02' AND stu.s_id=sco1.s_id) <>11
、查询没有学全所有课程的同学的信息
SELECT stu.* FROM student stu LEFT JOIN score sco ON sco.s_id=stu.s_id where
stu.s_id NOT IN( SELECT sco1.s_id FROM score sco1 WHERE sco1.c_id='01'OR sco1.
c_id='02' OR sco1.c_id='03' AND sco1.s_id=sco.s_id) <>12
、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT stu.* FROM student stu LEFT JOIN score sco ON sco.s_id=stu.s_id WHERE
sco.c_id IN ( SELECT sco1.c_id FROM score sco1 WHERE sco.s_id='01') GROUP BY stu
.s_id <>13
、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT stu.* FROM student stu WHERE stu.s_id IN( SELECT sco.s_id FROM score sco
WHERE sco.c_id IN( SELECT sco1.c_id FROM score sco1 WHERE sco1.s_id='01') GROUP
BY sco.s_id HAVING COUNT(*)=( SELECT COUNT(*) FROM score sco2 WHERE sco2.s_id=
'01') AND SUM(sco.c_id)=( SELECT SUM(sco3.c_id) FROM score sco3 WHERE sco3.s_id=
'01')) <>14
、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * FROM student WHERE s_id NOT IN( SELECT st.s_id FROM student st LEFT
JOIN score sc ON st.s_id=sc.s_id LEFT JOIN course co ON sc.c_id= co.c_id LEFT
JOIN teacher te ON co.t_id = te.t_id WHERE t_name = '张三'); <>15
、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT stu.s_id,stu.s_name,avg(sco.s_score) FROM student stu LEFT JOIN score
scoON stu.s_id=sco.s_id WHERE sco.s_id IN( SELECT sco1.s_id FROM score sco1
WHERE sco1.s_score < 60 GROUP BY sco1.s_id HAVING COUNT(sco1.s_id>=2)) GROUP BY
stu.s_id <>16
、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT stu.* FROM student stu LEFT JOIN score sco ON sco.s_id=stu.s_id WHERE
sco.c_id='01' AND sco.s_score<60 ORDER BY sco.s_score DESC <>17
、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT stu.*, avg(sco.s_score) AS '平均成绩', (SELECT s_score from score WHERE c_id
='01' AND s_id=stu.s_id) as '数学成绩', (SELECT s_score from score WHERE c_id='02'
AND s_id=stu.s_id) as '语文成绩', (SELECT s_score from score WHERE c_id='03' AND
s_id=stu.s_id) as '英语成绩' FROM student stu LEFT JOIN score sco on sco.s_id=stu.
s_idGROUP BY sco.s_id ORDER BY avg(sco.s_score) DESC <>18
.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT cou.c_id,cou.c_name, (SELECT s_score FROM score WHERE c_id=cou.c_id
ORDER BY s_score DESC LIMIT 1)AS '最高分', (SELECT s_score FROM score WHERE c_id=
cou.c_id ORDER BY s_score LIMIT 1)AS '最低分', ((SELECT COUNT(*) FROM score WHERE
c_id=cou.c_id AND s_score>= 60)/(SELECT COUNT(*) FROM score WHERE c_id=cou.c_id)
)AS'及格率', ((SELECT COUNT(*) FROM score WHERE c_id=cou.c_id AND s_score >=70 AND
s_score<80)/(SELECT COUNT(*)FROM score WHERE c_id=cou.c_id))AS '中等率', ((SELECT
COUNT(*) FROM score WHERE c_id=cou.c_id AND s_score >=80 AND s_score<90)/(SELECT
COUNT(*)FROM score WHERE c_id=cou.c_id))AS '优良率', ((SELECT COUNT(*) FROM score
WHERE c_id=cou.c_id AND s_score >=90)/(SELECT COUNT(*) FROM score WHERE c_id=cou
.c_id))AS '优秀率' FROM course cou LEFT JOIN score sco ON cou.c_id=sco.c_id GROUP
BY cou.c_id <>19
、按各科成绩进行排序，并显示排名(实现不完全)
SELECT c_id,s_id,s_score,IF(@prec=c_id,@rank:=@rank+1,@rank:=1)AS '名次',@prec:=
c_idFROM score,(SELECT @rank:=0)r,(SELECT @prec:=-1)p ORDER BY c_id,s_score DESC
<>20
、查询学生的总成绩并进行排名
SELECT s_id , SUM(s_score) sum1 FROM score GROUP BY s_id ORDER BY sum1 DESC <>
21
、查询不同老师所教不同课程平均分从高到低显示
SELECT cou.c_name,avg(sco.s_score) FROM course cou LEFT JOIN score sco ON cou.
c_id=sco.c_id GROUP BY cou.c_id ORDER BY avg(sco.s_score) DESC <>22
、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT * FROM( SELECT c_id, s_id, s_score, IF(@prec=c_id,@rank:=@rank+1,@rank:=
1)AS 名次, @prec:=c_id FROM score ORDER BY c_id,s_score DESC)t WHERE 名次=2 OR 名次=3;
<>23
、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT cou.c_id AS '课程编号',cou.c_name AS '课程名称', ((SELECT COUNT(*) FROM score
WHERE c_id = cou.c_id AND (s_score>85 AND s_score<=100))/(SELECT COUNT(*) FROM
scoreWHERE c_id=cou.c_id)) AS '[100-85]', ((SELECT COUNT(*) FROM score WHERE
c_id= cou.c_id AND (s_score>70 AND s_score<=85))/(SELECT COUNT(*) FROM score
WHERE c_id=cou.c_id)) AS '[85-70]', ((SELECT COUNT(*) FROM score WHERE c_id =
cou.c_id AND (s_score>60 AND s_score<=70))/(SELECT COUNT(*) FROM score WHERE
c_id=cou.c_id)) AS '[70-60]', ((SELECT COUNT(*) FROM score WHERE c_id = cou.c_id
AND (s_score>=0 AND s_score<=60))/(SELECT COUNT(*) FROM score WHERE c_id=cou.
c_id)) AS '[60-0]' FROM course cou LEFT JOIN score sco ON cou.c_id = sco.c_id
GROUP BY cou.c_id; <>24
、查询学生平均成绩及其名次
SELECT s_name,avg_score,@num:=@num+1 '名次' FROM (SELECT stu.*, avg(s_score)
avg_score, (SELECT @num:=0) '名次' FROM score sco LEFT JOIN student stu ON sco.
s_id=stu.s_id GROUP BY stu.s_id ORDER BY avg(sco.s_score) DESC)t <>25
、查询各科成绩前 三名的记录
SELECT * FROM( SELECT c_id, s_id, s_score, IF(@prec=c_id,@rank:=@rank+1,@rank:=
1)AS 名次, @prec:=c_id FROM score, (SELECT @rank:=0)r, (SELECT @prec:=-1)p ORDER
BY c_id,s_score DESC)t WHERE 名次 = 1 OR 名次 = 2 OR 名次 = 3; <>26
、查询每门课程被选修的学生数
SELECT c_name, COUNT(s_id)AS '学生数' FROM score sc LEFT JOIN course co ON co.c_id
=sc.c_id GROUP BY sc.c_id <>27
、查询出只有两门课程的全部学生的学号和姓名
SELECT s_sex, COUNT(*) FROM student GROUP BY s_sex <>28
、查询男生、女生人数
SELECT s_sex, COUNT(*) FROM student GROUP BY s_sex <>29
、查询名字中含有"风"字的学生信息
SELECT * FROM student WHERE s_name LIKE '%风%' <>30
、查询同名同性学生名单，并统计同名人数
SELECT *, COUNT(stu.s_name)AS '同名人数' FROM student stu WHERE stu.s_name IN(
SELECT stu1.s_name FROM student stu1 WHERE stu.s_id != stu1.s_id) GROUP BY stu.
s_name <>31
、查询1990年出生的学生名单
SELECT c_id, avg(s_score) FROM score GROUP BY c_id ORDER BY avg(s_score) DESC,
c_id <>32
、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT c_id, avg(s_score) FROM score GROUP BY c_id ORDER BY avg(s_score) DESC,
c_id <>33
、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT sc.s_id, st.s_name, avg(s_score)AS '平均成绩' FROM score sc LEFT JOIN
student stON st.s_id=sc.s_id GROUP BY sc.s_id HAVING avg(s_score) >= 85
34
、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT co.c_name, st.s_name, sc.s_score FROM score sc LEFT JOIN course co ON sc
.c_id=co.c_id LEFT JOIN student st ON sc.s_id=st.s_id WHERE co.c_name='数学' AND
sc.s_score<60 -- 嵌套查询 SELECT st.s_name, t.s_score FROM student st INNER JOIN (
SELECT * FROM score WHERE c_id IN( SELECT c_id FROM course WHERE c_name = '数学' )
AND s_score<60) t ON t.s_id=st.s_id <>35
、查询所有学生的课程及分数情况；
SELECT st.s_name, co.c_name, sc.s_score FROM score sc LEFT JOIN course co ON sc
.c_id=co.c_id LEFT JOIN student st ON sc.s_id=st.s_id <>36
、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT stu.s_name, co.c_name, sc.s_score FROM score sc LEFT JOIN student stu ON
stu.s_id=sc.s_id LEFT JOIN course co ON co.c_id=sc.c_id WHERE sc.s_score > 70
<>37
、查询不及格的课程
SELECT co.c_name, stu.s_name, sc.s_score FROM score sc LEFT JOIN student stu ON
stu.s_id=sc.s_id LEFT JOIN course co ON co.c_id=sc.c_id WHERE sc.s_score < 60
<>38
、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT s_id, s_name FROM student WHERE s_id IN (SELECT s_id FROM score WHERE
c_id= '01' AND s_score > 80) <>39
、求每门课程的学生人数
SELECT co.c_name, sc.c_id, COUNT(sc.s_id)AS '学生人数' FROM score sc LEFT JOIN
course coON co.c_id=sc.c_id GROUP BY sc.c_id <>40
、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT st.*, co.c_name, sc.s_score FROM score sc LEFT JOIN student st ON st.
s_id=sc.s_id LEFT JOIN course co ON sc.c_id=co.c_id LEFT JOIN teacher te ON te.
t_id=co.t_id WHERE te.t_name='张三' ORDER BY sc.s_score DESC LIMIT 1
41
、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT stu.s_id, sc.c_id, sc.s_score FROM student stu LEFT JOIN score sc ON stu
.s_id=sc.s_id WHERE sc.s_id IN( SELECT sc1.s_id FROM score sc1 LEFT JOIN score
sc2ON sc1.s_id=sc2.s_id AND sc1.c_id!=sc2.c_id WHERE sc2.s_score=sc1.s_score
GROUP BY sc1.s_id ) GROUP BY stu.s_id <>42
、查询每门功成绩最好的前两名
SELECT sc.* FROM score sc WHERE ( SELECT COUNT(*) FROM score sc1 WHERE sc.c_id=
sc1.c_id AND sc.s_score<=sc1.s_score )<=2 ORDER BY sc.c_id,sc.s_score DESC <>43
、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
with t AS( SELECT sc.c_id, COUNT(sc.s_id)AS '选修人数' FROM score sc LEFT JOIN
course coON co.c_id=sc.c_id GROUP BY sc.c_id ORDER BY COUNT(sc.s_id) DESC,sc.
c_id) SELECT t.c_id,t.`选修人数` FROM t WHERE t.`选修人数` > 5
44
、检索至少选修两门课程的学生学号
SELECT sc.s_id, COUNT(sc.c_id) FROM score sc GROUP BY sc.s_id HAVING COUNT(sc.
c_id)>=2
45
、查询选修了全部课程的学生信息
SELECT * FROM student stu WHERE stu.s_id IN( SELECT sc.s_id FROM score sc GROUP
BY sc.s_id HAVING COUNT(sc.c_id)=3) <>46
、查询各学生的年龄
SELECT s_name, s_birth, (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y')- (
CASE WHEN DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d')THEN 0 ELSE 1 END
)) FROM student <>47
、查询本周过生日的学生
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth) <>
48
、查询下周过生日的学生
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1=WEEK(s_birth)
<>49
、查询本月过生日的学生
SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))=MONTH(s_birth)
<>50
、查询下月过生日的学生
SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1=MONTH(s_birth)
SELECT * FROM student stu WHERE stu.s_id IN( SELECT sc.s_id FROM score sc GROUP
BY sc.s_id HAVING COUNT(sc.c_id)=3) <>46
、查询各学生的年龄
SELECT s_name, s_birth, (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y')- (
CASE WHEN DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d')THEN 0 ELSE 1 END
)) FROM student <>47
、查询本周过生日的学生
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth) <>
48
、查询下周过生日的学生
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1=WEEK(s_birth)
<>49
、查询本月过生日的学生
SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))=MONTH(s_birth)
<>50
、查询下月过生日的学生
SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1=MONTH(s_birth)