数据表介绍
1、学生表 Student(SId,Sname,Sage,Ssex) SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 2、课程表 Course(CId,Cname,TId) CId 课程编号,Cname 课程名称,TId 教师编号 3、教师表 Teacher(TId,Tname) TId 教师编号,Tname 教师姓名 4、成绩表 SC(SId,CId,score) SId 学生编号,CId 课程编号,score 分数
学生表 Student
# 创建学生表格与插入学生student信息create table Student( Sid nvarchar(10) NOT NULL, Sname nvarchar(20) NOT NULL default '', Sage nvarchar(10) NOT NUll default '', Ssex nvarchar(10) NOT NULL default '', PRIMARY KEY (Sid) );insert into Student values('01' , '赵雷' , '19900101' , '男');insert into Student values('02' , '钱电' , '19901221' , '男');insert into Student values('03' , '孙风' , '19901220' , '男');insert into Student values('04' , '李云' , '19901206' , '男');insert into Student values('05' , '周梅' , '19911201' , '女');insert into Student values('06' , '吴兰' , '19920101' , '女');insert into Student values('07' , '郑竹' , '19890101' , '女');insert into Student values('09' , '张三' , '20171220' , '女');insert into Student values('10' , '李四' , '20171225' , '女');insert into Student values('11' , '李四' , '20120606' , '女');insert into Student values('12' , '赵六' , '20130613' , '女');insert into Student values('13' , '孙七' , '20140601' , '女'); 科目表 Course
# 创建科目信息表格与插入科目信息create table Course( Cid nvarchar(10) NOT NULL, Cname nvarchar(20) NOT NULL default '', Tid nvarchar(10) NOT NULL default '', primary key(Cid) );insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03'); 教师表 Teacher
# 创建教师表格与插入教师信息create table Teacher( Tid nvarchar(10) NOT NULL, Tname nvarchar(20) NOT NULL default '', primary key(Tid) );insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五'); 成绩表 SC
# 创建成绩表格与插入成绩信息create table SC( Sid nvarchar(10) NOT NULL, Cid nvarchar(10) NOT NULL default '', score decimal(3,1) NOT NULL default 0, primary key(Sid, Cid), foreign key(Sid) references student(Sid), foreign key(Cid) references course(Cid) );insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);
问题及答案
1-5
1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT s.*, a.cid, a.score, b.cid, b.scoreFROM student s JOIN sc a ON s.sid = a.sid and a.cid = '01' JOIN sc b ON s.sid = b.sid and b.cid = '02'WHERE a.score > b.score;
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT *FROM student s JOIN sc a ON s.sid = a.sid and a.cid = '01' JOIN sc b ON s.sid = b.sid and b.cid = '02';
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT *FROM sc a LEFT JOIN sc b ON (a.sid = b.sid and b.cid = '02')WHERE a.cid = '01';
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT a.sid sid, a.cid a_cid, a.score a_score, b.cid b_cid, b.score b_scoreFROM sc a LEFT JOIN sc b ON a.sid = b.sid and b.cid = '01'WHERE a.cid = '02' and b.cid is null;
2、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.*, sc.cid, avg(sc.score) meanFROM student s JOIN sc ON s.sid = sc.sidGROUP BY s.sid HAVING mean >= 60;
3、查询在 SC 表存在成绩的学生信息
SELECT * FROM studentWHERE sid IN (SELECT DISTINCT sid FROM sc);
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT s.sid, s.sname, count(sc.cid), sum(sc.score)FROM student s LEFT JOIN sc ON s.sid = sc.sidGROUP BY s.sid, s.sname;
4.1 查有成绩的学生信息
SELECT DISTINCT s.* FROM student s JOIN sc ON s.sid = sc.sid;# 4.1延伸-查没有成绩的学生信息SELECT DISTINCT s.*FROM student s LEFT JOIN sc ON s.sid = sc.sidWHERE sc.score IS NULL;
5、 查询「李」姓老师的数量
SELECT COUNT(*) FROM teacherWHERE tname LIKE '李%';
6-10
6、 查询学过「张三」老师授课的同学的信息
SELECT s.* FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid JOIN teacher t ON c.tid = t.tidWHERE t.tname = '张三';
7、 查询没有学全所有课程的同学的信息
SELECT s.*FROM student s LEFT JOIN sc ON s.sid = sc.sid LEFT JOIN course c ON sc.cid = c.cidGROUP BY s.sidHAVING COUNT(s.sid) < (SELECT count(*) FROM course);
8、 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT DISTINCT s.*FROM student s JOIN sc ON s.sid = sc.sidWHERE sc.cid IN (SELECT cid FROM sc WHERE sid = '01');
9、 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
SELECT s.*FROM sc a JOIN sc b ON a.cid = b.cid and a.sid = '01' and a.sid != b.sid JOIN student s ON b.sid = s.sidGROUP BY b.sidHAVING COUNT(b.sid) = (SELECT count(*) FROM sc WHERE sid = '01');
10、 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT DISTINCT s.sid, s.snameFROM student s LEFT JOIN sc ON s.sid = sc.sidWHERE s.sid NOT IN (SELECT sc.sid FROM course c JOIN teacher t ON c.Tid = t.Tid JOIN sc ON sc.cid = c.Cid WHERE Tname = '张三');
11-15
11、 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.sid, s.sname, ROUND(avg(sc.score),2) mean_scoreFROM student s JOIN sc ON s.sid = sc.sidWHERE sc.score < 60GROUP BY s.sidHAVING COUNT(s.sid) >= 2;
12、 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT s.*, sc.cid, sc.score FROM student s JOIN sc ON s.sid = sc.sidWHERE sc.cid = '01' AND sc.score < 60ORDER BY sc.score DESC;
13、 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s.sid, s.sname, a.cid, a.score, b.cid, b.score, c.cid, c.score, d.mean_scoreFROM student s LEFT JOIN sc a ON s.sid = a.sid AND a.cid = '01' LEFT JOIN sc b ON s.sid = b.sid AND b.cid = '02' LEFT JOIN sc c ON s.sid = c.sid AND c.cid = '03' LEFT JOIN (SELECT sid, ROUND(avg(score), 2) mean_score FROM sc GROUP BY sid) d ON s.sid = d.sidWHERE mean_score is NOT NULLORDER BY mean_score DESC;
14、 查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率， 中等率，优良率，优秀率
及格为>=60，中等为：70~80，优良为：80~90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程 号升序排列SELECT c.cname, sc.cid, max(score) as max_score, min(score) as min_score, ROUND(avg(score), 2) as mean_score, ROUND(100*sum(case WHEN score < 60 THEN 1 ELSE 0 END)/COUNT(*),2) as '不及格率(%)', ROUND(100*sum(case WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END)/COUNT(*),2) as '及格率(%)', ROUND(100*sum(case WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)/COUNT(*),2) as '中等率(%)', ROUND(100*sum(case WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)/COUNT(*),2) as '优良率(%)', ROUND(100*sum(case WHEN score >= 90 THEN 1 ELSE 0 END)/COUNT(*),2) as '优秀率(%)'FROM sc JOIN course c ON sc.cid = c.cidGROUP BY cid;
15、 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
########################################################## 1-依次排序（忽略分数相等情况，依次12345）SELECT sc.*, CASE WHEN @curid = cid THEN @ranknum:=@ranknum+1 WHEN @curid := cid THEN @ranknum:=1END as ranknum,FROM sc, (SELECT @ranknum := 0, @curid := 0) rORDER BY cid, score DESC;########################################################## 2-出现并列情况，但排名依次排序（1223345）SELECT sc.*,CASEWHEN (@curid = cid) and (@curscore = score) THEN @ranknum:=@ranknumWHEN (@curid = cid) and (@curscore := score) THEN @ranknum:=@ranknum+1WHEN (@curid := cid) and (@curscore := score) THEN @ranknum:=1END as ranknumFROM sc, (SELECT @ranknum:=0, @curid:=0, @curscore:=NULL) as rORDER BY cid, score DESC;########################################################## 出现并列情况，但排名不连续（12245）SELECT sc.*,CASE WHEN (@curid = cid) and (@curscore = score) THEN @ranknum:=@ranknum+(@currow:=@currow+1)-@currowWHEN (@curid = cid) and (@curscore := score) THEN @ranknum:=(@currow:=@currow+1)WHEN (@curid := cid) and (@curscore := score) THEN @ranknum:=(@currow:=1) END as ranknumFROM sc, (SELECT @ranknum:=0, @curid:=0, @curscore:=NULL, @currow:=0) as rORDER BY cid, score DESC;# 另一种写法SELECT a.*, count(b.score)+1 as ranknumFROM sc a LEFT JOIN sc b ON a.cid = b.cid and a.score < b.scoreGROUP BY a.sid, a.cidORDER BY a.cid, a.score DESC;
16-20
16、 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺 # 创建一个view，后面直接引用，偷懒少写几次CREATE VIEW total ASSELECT sid, sum(score) as total_score FROM sc GROUP BY sid order by sum(score) DESC;########################################################## 分别列出3种情况# 1-没有并列【12345】SELECT total.sid, total.total_score, @ranknum:=@ranknum+1 as ranknumFROM (SELECT @ranknum:=0) as r, total;########################################################## 2-并列【122345】SELECT total.sid, total.total_score, CASEWHEN @curscore = total.total_score THEN @ranknumWHEN @curscore := total.total_score THEN @ranknum:=@ranknum+1END as ranknumFROM total, (SELECT @ranknum:=0, @curscore:=Null) as r;########################################################## 3-并列【12245】SELECT total.sid, total.total_score,CASEWHEN @curscore = total.total_score THEN @ranknum:=@ranknum+(@currow:=@currow+1)-@currowWHEN @curscore := total.total_score THEN @ranknum:=(@currow:=@currow+1)END as ranknumFROM total, (SELECT @ranknum:=0, @curscore:=Null, @currow:=0) as r;# 另一种写法，需要在view里增加一列辅助列create view new_total asSELECT * , count(sid) class FROM total GROUP BY sid;SELECT *, count(t2.total_score)+1 as ranknumFROM new_total t1 LEFT JOIN new_total t2 ON t1.class = t2.class and t1.total_score < t2.total_scoreGROUP BY t1.sidORDER BY ranknum;
17、 统计各科成绩各分数段人数：课程编号，课程名称，[10085]，[8570]，[7060]，[600] 及所占百分比
SELECT sc.cid, c.Cname, sum(CASE WHEN score >= 85 THEN 1 ELSE 0 END) AS '[10085]', sum(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) AS '[8570]', sum(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) AS '[7060]', sum(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS '[600]', ROUND(100*sum(CASE WHEN score >= 85 THEN 1 ELSE 0 END)/count(*),2) AS '[10085]%', ROUND(100*sum(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END)/count(*),2) AS '[8570]%', ROUND(100*sum(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END)/count(*),2) AS '[7060]%', ROUND(100*sum(CASE WHEN score < 60 THEN 1 ELSE 0 END)/count(*),2) AS '[600]%'FROM course c JOIN sc ON c.cid = sc.cidGROUP BY c.cid;
18、 查询各科成绩前三名的记录
SELECT a.*, b.*, count(b.score)+1 as ranknumFROM sc a LEFT JOIN sc b ON a.cid = b.cid and a.score < b.scoreGROUP BY a.sid, a.cidHAVING count(b.score)+1 <= 3ORDER BY a.cid, a.score DESC;
19、 查询每门课程被选修的学生数
SELECT cid, count(*)FROM scGROUP BY cid;
20、 查询出只选修两门课程的学生学号和姓名
SELECT s.sid, s.sname, sc.*FROM student s JOIN sc ON s.sid = sc.sidGROUP BY s.sidHAVING count(s.sid) = 2;
21-25
21、 查询男生、女生人数
SELECT ssex, COUNT(sid) FROM student GROUP BY ssex;
22、 查询名字中含有「风」字的学生信息
SELECT * FROM student WHERE sname LIKE '%风%';
23、 查询同名同性学生名单，并统计同名人数
SELECT sname, COUNT(sid) FROM student GROUP BY sname HAVING COUNT(sid) > 1;
24、 查询 1990 年出生的学生名单
SELECT sname FROM student WHERE year(sage) = 1990; # 另一种写法 SELECT sname FROM student WHERE sage LIKE '%1990%';
25、 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按 课程编号升序排列
SELECT cid, ROUND(avg(score), 2) as mean_scoreFROM scGROUP BY cidORDER BY mean_score DESC, cid;
26-30
26、 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT *, ROUND(avg(score), 2) as mean_scoreFROM student s JOIN sc ON s.sid = sc.sidGROUP BY s.sidHAVING ROUND(avg(score), 2) >= 85;
27、 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT s.*, sc.cid, c.Cname, sc.score FROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cidWHERE c.Cname = '数学' AND score < 60;
28、 查询所有学生的课程及分数情况
SELECT s.*, sc.cid, sc.scoreFROM student s LEFT JOIN sc ON s.sid = sc.sid;
29、 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT s.sid, s.sname, c.Cname, sc.scoreFROM student s JOIN sc ON s.sid = sc.sid and sc.score > 70 JOIN course c ON sc.cid = c.cid;
30、 查询不及格的课程
SELECT s.sid, s.sname, c.cname, sc.scoreFROM student s JOIN sc ON s.sid = sc.sid and sc.score < 60 JOIN course c ON sc.cid = c.cid;
31-35
31、 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT s.sid, s.snameFROM student s JOIN sc ON s.sid = sc.sid and sc.score >= 80 and sc.cid = '01';
32、 求每门课程的学生人数
SELECT cid, COUNT(sid) FROM scGROUP BY cid;
33、 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信 息及其成绩
SELECT s.*, sc.scoreFROM student s JOIN sc ON s.sid = sc.sid JOIN course c ON sc.cid = c.cid JOIN teacher t ON c.tid = t.TidWHERE t.Tname = '张三'ORDER BY sc.score DESCLIMIT 1;
34、 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT s.*, a.*FROM student s JOIN sc a ON s.sid = a.sid JOIN course c ON a.cid = c.cid JOIN teacher t ON c.tid = t.Tid and t.Tname = '张三' LEFT JOIN sc b ON a.cid = b.cid and a.score < b.scoreGROUP BY s.sidHAVING count(b.score)+1 = 1;
35、 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT a.*FROM sc a JOIN sc b ON a.score = b.score and a.cid != b.cid;
36-40
36、 查询每门功成绩最好的前两名
SELECT a.*FROM sc a LEFT JOIN sc b ON a.cid = b.cid and a.score < b.scoreGROUP BY a.sid, a.cidHAVING count(b.score)+1 < 3ORDER BY a.cid, a.sid;
37、 统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECT cid, COUNT(sid) FROM scGROUP BY cidHAVING COUNT(sid) > 5;
38、 检索至少选修两门课程的学生学号
SELECT sid , count(*)FROM scGROUP BY sidHAVING count(*) >= 2;
39、 查询选修了全部课程的学生信息
SELECT s.*,count(*)FROM student s JOIN sc ON s.sid = sc.sidGROUP BY s.sidHAVING count(*) = (select COUNT(*) FROM course);
40、 查询各学生的年龄，只按年份来算
SELECT sname, YEAR(curdate())-YEAR(sage) age FROM student;
41-45
41、 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT sname, sage, curdate(), CASEWHEN ((month(sage) = month(curdate()) and day(sage) < day(curdate())) or month(sage) < month(curdate())) THEN year(curdate())-year(sage)WHEN ((month(sage) = month(curdate()) and day(sage) > day(curdate())) or month(sage) > month(curdate())) THEN year(curdate())-year(sage)-1END ageFROM student;
42、 查询本周过生日的学生
SELECT * FROM studentWHERE weekofyear(sage) = weekofyear(curdate());
43、 查询下周过生日的学生
SELECT * FROM studentWHERE weekofyear(sage) = weekofyear(curdate())+1;
44、 查询本月过生日的学生
SELECT * FROM student WHERE month(sage) = month(curdate());
45、查询下月过生日的学生
SELECT * FROM student WHERE month(sage) = month(curdate())+1;