目录
数据表介绍
-- 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 分数
数据SQL
-- 数据SQL -- 学⽣表 Student 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' , '男'); insert into Student values('03' , '孙⻛' , '1990-12-20' , '男'); insert into Student values('04' , '李云' , '1990-12-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '⼥'); insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥'); insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥'); insert into Student values('09' , '张三' , '2017-12-20' , '⼥'); insert into Student values('10' , '李四' , '2017-12-25' , '⼥'); insert into Student values('11' , '李四' , '2012-06-06' , '⼥'); insert into Student values('12' , '赵六' , '2013-06-13' , '⼥'); insert into Student values('13' , '孙七' , '2014-06-01' , '⼥'); -- 科⽬表 Course create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)); insert into Course values('01' , '语⽂' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03'); -- 教师表 Teacher create table Teacher(TId varchar(10),Tname varchar(10)); insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五'); -- 成绩表 SC create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)); 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.查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数
思路: -- 1.先分别查询01和02课程的学员id和分数 SELECT SId,score from sc WHERE CId=01; SELECT SId,score from sc WHERE CId=02; -- 2.对比两个结果,发现两个结果中有一些sid是不对应的 -- 因此可以对两个结果做jion联结,条件是sid要相等 -- 并且01的成绩要大于02的 select s1.sid,s1.score from (SELECT sid,score from sc WHERE cid=01) as s1 JOIN (SELECT sid,score from sc WHERE cid=02) as s2 ON s1.sid = s2.sid WHERE s1.score > s2.score; -- 3.通过以上的sql,得到了符合条件的学员id和分数,再联结学生表,获取学员信息 SELECT stu.sid,stu.sname,s.score from student as stu join ( select s1.sid,s1.score from (SELECT sid,score from sc WHERE cid=01) as s1 JOIN (SELECT sid,score from sc WHERE cid=02) as s2 ON s1.sid = s2.sid WHERE s1.score > s2.score ) as s on stu.sid = s.sid;
2.查询同时学习" 01 "课程和" 02 "课程的学生信息情况
select s1.* from (SELECT sid,score from sc WHERE cid=01) as s1 JOIN (SELECT sid,score from sc WHERE cid=02) as s2 ON s1.sid = s2.sid ;
3.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select s1.*,s2.* from (SELECT sid,score from sc WHERE cid=01) as s1 left jOIN (SELECT sid,score from sc WHERE cid=02) as s2 ON s1.sid = s2.sid ;
4.查询不存在" 01 "课程但存在" 02 "课程的情况(存在02但不存在01的课程信息)
SELECT * FROM sc WHERE sid not in (SELECT sid from sc WHERE cid=01) and cid = 02;
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
select sc.sid,sname,round(avg(score),2) as avg_score from sc,student WHERE sc.SId = student.SId GROUP BY sc.sid,sname HAVING avg_score >=60; #having实现的是group by分组聚合后的过滤 #在select里面有几列,那么在group by 后面就要写几列,否则会报1055错误 #round(avg(score),2):把平均分精确到小数点后两位
6.查询在 SC 表存在成绩的学⽣信息
select DISTINCT stu.* from student as stu JOIN sc on sc.SId =stu.sid; #DISTINCT:数据去重
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select stu.sid,stu.sname, count(sc.cid) as num,sum(sc.score) as total_score from student as stu left join sc on stu.sid=sc.sid GROUP BY stu.sid,stu.sname;
8.查询「李」姓⽼师的数量
SELECT count(t.tname) from (select * FROM teacher WHERE tname like '李%') as t;
9.查询学过「张三」⽼师授课的同学的信息
#sid-cid-tid-tname #多表联结 select stu.*,sc.cid,t.tname from student as stu left join sc on stu.sid=sc.sid left join course on sc.cid = course.cid left join teacher as t on course.tid = t.tid WHERE t.Tname='张三';
10.查询没有学全所有课程的同学的信息
- 1.先找学过所有课程的同学 - 先统计课程表有几门课 - 拿成绩表按sid分组做cid的count统计 -用having进行过滤 - 2.再用一个select语句,从student表中用sid不在上表的过滤条件进行过滤。 select DISTINCT stu.* from student as stu,sc WHERE stu.sid not in (SELECT sid from sc GROUP BY sc.SId having count(sc.cid)=3);
11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息
- 从表sc中先查01同学学的所有课程,并得到sid - 学生表与成绩表sc join之后,再嵌套一下 SELECT DISTINCT stu.* from student as stu left join sc on sc.SId=stu.SId WHERE sc.cid in(SELECT cid from sc WHERE sid = 01);
12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
#使用自联结 - 先用自联结把自己的表与自己的表联结起来,联结条件为:左右两表的课程id相同,且左表为sid=01的信息,右表为sid!=01的信息 。 - 然后用s2的sid和学生表的学生名字作为分组聚合的条件, - 聚合后,用s2的cid的计数count,与学生id为01的成绩计数是否相等作为条件,对s2进行排序 - 加上学生姓名等信息,需要在group by前再把student表的sname信息join进来。 SELECT s2.sid,student.Sname from sc as s1 join sc as s2 on s1.cid = s2.cid and s1.sid=01 and s2.sid!=01 join student on s2.sid = student.sid GROUP BY s2.sid,student.Sname HAVING count(s2.sid)=(SELECT count(*) from sc WHERE sid=01);
13.查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名
select stu.*,sc.cid,t.tname from student as stu left join sc on stu.sid=sc.sid left join course on sc.cid = course.cid left join teacher as t on course.tid = t.tid WHERE t.Tname !='张三';
14.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select stu.sid,stu.sname,avg(sc.score) as avg_score from student as stu join sc on stu.SId = sc.SId WHERE sc.score<60 GROUP BY stu.sid,stu.sname HAVING count(sc.CId)>=2;
15.检索" 01 "课程分数⼩于 60,按分数降序排列的学⽣信息
#用两个表join一下,并限制cid和score,最后再排序 select stu.*,sc.score from student as stu join sc on stu.sid = sc.SId WHERE cid=01 and sc.score<60 ORDER BY sc.score desc;
16.按平均成绩从⾼到低显示所有学⽣的所有课程的成绩以及平均成绩
#法1: SELECT sc.*,s2.avg_score from sc join (SELECT sid,avg(sc.score) as avg_score from sc GROUP BY sid) as s2 on sc.sid = s2.sid ORDER BY s2.avg_score desc ;
#法2: select stu.sname, a.score as '语文', b.score as '数学', c.score as '英语', avg(d.score) as '平均成绩' from student as stu left join sc as a on stu.sid = a.sid and a.cid = '01' left join sc as b on stu.sid = b.sid and b.cid = '02' left join sc as c on stu.sid = c.sid and c.cid = '03' left join sc as d on stu.sid = d.sid group by stu.sname,语文,数学,英语 order by 平均成绩 desc;
17.查询各科成绩最⾼分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最⾼分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修⼈数,查询结果按⼈数降序排列,若⼈数相同,按课程号升序排列
CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END 相当于编程中 if if sc.score >= 60: return 1 else: return 0 ---------------------------------------------------------- select sc.cid, c.cname, max(sc.score) as '最高分', min(sc.score) as '最低分', round(avg(sc.score),2) as '平均分', count(sc.cid) as '选修人数', sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / count(sc.cid) as '及格率', sum(CASE WHEN sc.score >= 70 and sc.score < 80 THEN 1 ELSE 0 END) / count(sc.cid) as '中等率', sum(CASE WHEN sc.score >= 80 and sc.score < 90 THEN 1 ELSE 0 END) / count(sc.cid) as '优良率', sum(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / count(sc.cid) as '优秀率' from sc join course as c on sc.cid = c.cid group by sc.cid,c.cname order by '选修人数' desc,sc.cid;
18.按各科平均成绩进⾏排序,并显示排名, Score 重复时保留名次空缺
保留名次空缺含义如下:
-- 按照各学科进行分组,计算平均成绩 select cid,avg(score) as avg_sc from sc group by cid; -- 按照各学科的平均成绩,做自联结,进行比较 SELECT s1.*,s2.* from (select cid,avg(score) as avg_sc from sc group by cid) as s1 join (select cid,avg(score) as avg_sc from sc group by cid) as s2 on s1.avg_sc >= s2.avg_sc; -- 按照s2进行分组,统计s1的平均分出现的次数 SELECT s2.cid,s2.avg_sc,count(DISTINCT s1.avg_sc) as rank from (select cid,avg(score) as avg_sc from sc group by cid) as s1 join (select cid,avg(score) as avg_sc from sc group by cid) as s2 on s1.avg_sc >= s2.avg_sc GROUP BY s2.cid,s2.avg_sc ORDER BY rank;
19.按各科平均成绩进⾏排序,并显示排名, Score 重复时不保留名次空缺
不保留名次空缺含义如下图:
-- 按照各学科进行分组,计算平均成绩并排序 select cid,avg(score) as avg_sc from sc group by cid; #也就是按照排好序后的结果,给你添加了一个排名,你排序后是第几就添加第几 # @i是sql中定义变量的意思 SELECT b.cid,b.avg_sc,@i := @i+1 as rank from (SELECT @i:=0) as a, (select cid,avg(score) as avg_sc from sc group by cid ORDER BY avg_sc desc) as b;
20.查询学⽣的总成绩,并进⾏排名,总分重复时保留名次空缺
SELECT s2.sid,s2.sum_sc,count(DISTINCT s1.sum_sc) as rank from (select sid,sum(score) as sum_sc from sc group by sid) as s1 join (select sid,sum(score) as sum_sc from sc group by sid) as s2 on s1.sum_sc >= s2.sum_sc GROUP BY s2.sid,s2.sum_sc ORDER BY rank;
21.查询学⽣的总成绩,并进⾏排名,总分重复时不保留名次空缺
SELECT b.sid,b.sum_sc,@i := @i+1 as rank from (SELECT @i:=0) as a, (select sid,sum(score) as sum_sc from sc group by sid ORDER BY sum_sc desc) as b;
22.统计各科成绩各分数段⼈数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0]及所占百分⽐
- 类似17题,用case when 做百分比的计算 select sc.cid, c.cname, sum(CASE WHEN sc.score > 85 and sc.score<=100 THEN 1 ELSE 0 END) as '[100-85]', sum(CASE WHEN sc.score > 70 and sc.score<=85 THEN 1 ELSE 0 END) as '[85-70]', sum(CASE WHEN sc.score > 60 and sc.score<=70 THEN 1 ELSE 0 END) as '[70-60]', sum(CASE WHEN sc.score<=60 THEN 1 ELSE 0 END) as '[60-0]', count(sc.cid) as '选修人数', sum(CASE WHEN sc.score > 85 and sc.score<=100 THEN 1 ELSE 0 END) / count(sc.cid) as '[100-85]百分比', sum(CASE WHEN sc.score > 70 and sc.score<=85 THEN 1 ELSE 0 END) / count(sc.cid) as '[85-70]百分比', sum(CASE WHEN sc.score > 60 and sc.score<=70 THEN 1 ELSE 0 END)/ count(sc.cid) as '[70-60]百分比', sum(CASE WHEN sc.score<=60 THEN 1 ELSE 0 END) / count(sc.cid) as '[60-0]百分比' from sc join course as c on sc.cid = c.cid group by sc.cid,c.cname order by '选修人数' desc,sc.cid;
23.查询各科成绩前三名的记录
大坑比。mysql不能group by 了以后取limit,所以不要想着讨巧了,我快被这一题气死了。思路有两种,第一种比较暴力,计算比自己分数大的记录有几条,如果小于3 就select,因为对前三名来说不会有3个及以上的分数比自己大了,最后再对所有select到的结果按照分数和课程编号排名即可。 select * from sc where ( select count(*) from sc as a where sc.cid = a.cid and sc.score<a.score )< 3 order by cid asc, sc.score desc; 第二种比较灵巧一些,用自身左交,但是有点难以理解。 先用自己交自己,条件为a.cid = b.cid and a.score<b.score,其实就是列出同一门课内所有分数比较的情况。 想要查看完整的表可以 select * from sc a left join sc b on a.cid = b.cid and a.score<b.score order by a.cid,a.score; 结果 查看,发现结果是47行的一个表,列出了类似 01号课里“30分小于50,也小于70,也小于80,也小于90”“50分小于70,小于80,小于90”..... 所以理论上,对任何一门课来说,分数最高的那三个记录,在这张大表里,通过a.sid和a.cid可以联合确定这个同学的这门课的这个分数究竟比多少个其他记录高/低, 如果这个特定的a.sid和a.cid组合出现在这张表里的次数少于3个,那就意味着这个组合(学号+课号+分数)是这门课里排名前三的。 所以下面这个计算中having count 部分其实count()或者任意其他列都可以,这里制定了一个列只是因为比count()运行速度上更快。 select a.sid,a.cid,a.score from sc as a left join sc as b on a.cid = b.cid and a.score<b.score group by a.cid, a.sid,a.score having count(b.cid)<3 order by a.cid; # 待解决:因为03课程第三名是有多位的,但是我们限制了只有前三名,索引查询03的前三名时只显示了前两名 select s1.cid,s1.sid,s1.score from sc as s1 WHERE (SELECT count(1) from sc as s2 where s1.cid =s2.cid and s2.score >=s1.score ORDER BY s2.score)<=3 ORDER BY s1.cid desc;
24.查询每门课程被选修的学⽣数
select cid,count(sid)as '学生人数' from sc GROUP BY cid;
25.查询出只选修两⻔课程的学⽣学号和姓名
select stu.sid,stu.Sname from student as stu WHERE stu.sid in (select sid as '学生人数' from sc GROUP BY sid having count(cid)=2);
26.查询男⽣、⼥⽣⼈数
select Ssex as '性别',count(Ssex) as '人数' from student as stu GROUP BY Ssex ;
27.查询名字中含有「风」字的学⽣信息
SELECT stu.* FROM student as stu WHERE stu.Sname LIKE '%风%';
28.查询同名同性学⽣名单,并统计同名⼈数
- 用学生表做join自联结,条件是sname要一样,但sid不一样,并列出名字和次数 select s1.Sname,count(s1.Sname = s2.Sname) as '同名人数' from student as s1 join student as s2 on s1.Sname = s2.Sname and s1.SId!=s2.SId GROUP BY s1.Sname;
29.查询 1990 年出⽣的学⽣名单
用year()函数进行统计 SELECT stu.SId,stu.Sname from student as stu WHERE YEAR(stu.Sage)=1990;
30.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT cid,avg(score) as avg_score from sc GROUP BY CId ORDER BY avg_score desc,cid;
31.查询平均成绩⼤于等于 85 的所有学⽣的学号、姓名和平均成绩
SELECT stu.sid,stu.sname, avg(sc.score) from student as stu JOIN sc on stu.sid = sc.SId GROUP BY stu.sid,stu.sname HAVING avg(score)>=85;
32.查询课程名称为「数学」,且分数低于 60 的学⽣姓名和分数
select stu.Sname,c.Cname,sc.score FROM student as stu left JOIN sc on stu.SId = sc.SId left join course as c on sc.CId = c.CId WHERE c.Cname = '数学' GROUP BY stu.Sname,c.Cname,sc.score HAVING sc.score<60 ;
33.查询所有学⽣的课程及分数情况(存在学⽣没成绩,没选课的情况)
- 参考16题法2 select DISTINCT stu.sname, a.score as '语文', b.score as '数学', c.score as '英语' from student as stu left join sc as a on stu.sid = a.sid and a.cid = '01' left join sc as b on stu.sid = b.sid and b.cid = '02' left join sc as c on stu.sid = c.sid and c.cid = '03' left join sc as d on stu.sid = d.sid group by stu.sname,语文,数学,英语 order by stu.Sname desc;
34.查询任何⼀门课程成绩在 70 分以上的姓名、课程名称和分数
select stu.sname,c.Cname,sc.score from student as stu join sc on stu.sid = sc.sId join course as c on c.CId = sc.CId WHERE sc.score >=70 GROUP BY stu.sname,c.Cname,sc.score;
35.查询不及格的课程
select stu.sname,c.Cname,sc.score from student as stu join sc on stu.sid = sc.sId join course as c on c.CId = sc.CId WHERE sc.score <60 GROUP BY stu.sname,c.Cname,sc.score;
36.查询课程编号为 01 且课程成绩在 80 分以上的学⽣的学号和姓名
SELECT stu.sid,stu.sname from student as stu join sc on sc.sid = stu.sid WHERE sc.CId=01 and sc.score>=80;
37.求每门课程的学⽣⼈数
select sc.cid,count(sc.CId) as '学生人数' from sc join course as c GROUP BY sc.cid;
38.成绩不重复,查询选修「张三」⽼师所授课程的学⽣中,成绩最⾼的学⽣信息及其成绩
将4张表join起来,然后限制条件教师表的tname=张三,然后做降序排序,用limit(1)只取第一个 SELECT stu.*,sc.score from student as stu join sc on stu.sid = sc.sid join course as c on sc.CId = c.CId join teacher as t on t.TId = c.TId WHERE t.Tname = '张三' ORDER BY sc.score desc LIMIT 1;
39.成绩有重复的情况下,查询选修「张三」⽼师所授课程的学⽣中,成绩最⾼的学⽣信息及其成绩
将4张表join起来,然后限制条件教师表的tname=张三,然后做降序排序,找出最高分的分数是多少,然后取出分数为最高分的学生信息。下题也是同样操作。 SELECT stu.*,sc.score from student as stu join sc on stu.SId = sc.SId WHERE sc.score = ( SELECT sc.score from student as stu join sc on stu.sid = sc.sid join course as c on sc.CId = c.CId join teacher as t on t.TId = c.TId WHERE t.Tname = '张三' ORDER BY sc.score desc LIMIT 1 );
40.查询不同课程成绩相同的学⽣的学⽣编号、课程编号、学⽣成绩
用成绩表sc做自联结,限制条件为分数一样但cid不一样,最后再join学生表,条件是两表的sid相同。 SELECT s1.sid,s1.cid,s1.score from sc as s1 join sc as s2 on (s1.score = s2.score) and s1.cid!=s2.CId join student as stu on s1.SId = stu.sid GROUP BY s1.sid,s1.cid,s1.score;
41.查询每门课程成绩最好的前两名
select s1.cid,s1.sid,s1.score from sc as s1 WHERE (SELECT count(1) from sc as s2 where s1.cid =s2.cid and s2.score >=s1.score ORDER BY s2.score)<=3 ORDER BY s1.cid desc;
42.统计每门课程的学⽣选修⼈数(超过 5 ⼈的课程才统计)。
select sc.cid,c.Cname,count(sc.cid) as '选修人数' from sc join course as c on sc.CId = c.CId GROUP BY sc.cid,c.Cname HAVING count(sc.cid)>5;
43.检索⾄少选修两门课程的学⽣学号
SELECT stu.sid,stu.Sname from student as stu join sc on stu.sid =sc.SId GROUP BY stu.sid,stu.Sname HAVING count(stu.sid)>=2;
44.查询选修了全部课程的学⽣信息
SELECT stu.sid,stu.Sname from student as stu join sc on stu.sid =sc.SId GROUP BY stu.sid,stu.Sname HAVING count(sc.sid)=3;
45.查询各学⽣的年龄,只按年份来算
select stu.Sname,@age := YEAR(CURRENT_DATE) -YEAR(stu.Sage) from student as stu;
46.按照出⽣⽇期来算,当前⽉⽇ < 出⽣年⽉的⽉⽇则,年龄减⼀
TIMESTAMPDIFF() 从⽇期时间表达式中减去间隔,会自动精确计算
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
select student.SId as 学生编号,student.Sname as 学生姓名, TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄 from student
47.查询本周过⽣⽇的学⽣
返回⽇期从范围内的数字⽇历星期1到53
select stu.* from student as stu WHERE WEEKOFYEAR(stu.Sage) = WEEKOFYEAR(CURDATE());
48.查询下周过⽣⽇的学⽣
select stu.* from student as stu WHERE WEEKOFYEAR(stu.Sage) = WEEKOFYEAR(CURDATE())+1;
49.查询本⽉过⽣⽇的学⽣
select stu.* from student as stu WHERE MONTH(stu.Sage) =MONTH(CURDATE());
50.查询下⽉过⽣⽇的学⽣
select stu.* from student as stu WHERE MONTH(stu.Sage) =MONTH(CURDATE())+1;