MySQL
0. 数据表(建表语句在文末)
1.
student表:
sid, sname, sage, ssex
2.course表:
cid, cname, tid
3.teacher表:
tid, tname
4.sc表:
sid, cid, score
1. 查询相关
# 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
# 思路:查询sid相等且'01'课程>'02'课程的信息,然后与student表联结
select * from student RIGHT JOIN(
select t1.sid,score1,score2 from
(SELECT sid, cid, score as score1 FROM sc where cid='01') as t1,
(SELECT sid, cid, score as score2 FROM sc where cid='02') as t2
where t1.sid=t2.sid and score1>score2) as r
on student.sid=r.sid;
# 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
# 思路:分别查询课程号='01'和'02'表,然后做内联结
select * from
(select * FROM sc where cid='01') as t1,
(select * FROM sc where Cid='02') as t2
where t1.sid=t2.sid;
select * from
(select * FROM sc where cid='01') as t1 INNER JOIN
(select * FROM sc where Cid='02') as t2
on t1.sid=t2.sid;
# 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
# 思路:分别查询课程号='01'和'02'表,然后做左联结
select * from
(select * FROM sc where cid='01') as t1 LEFT JOIN
(select * FROM sc where Cid='02') as t2
on t1.sid=t2.sid;
# 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
# 思路1:分别查询课程号='01'和'02'表,然后做有右联结,再用where is NULL
select * from
(select * FROM sc where cid='01') as t1 RIGHT JOIN
(select * FROM sc where Cid='02') as t2
on t1.sid=t2.sid
where t1.cid is null;
# 思路2:先查询课程号='01'的学生Id,然后用where判断学生Id不在这里面的,并且课程号='02'
select * from sc
where sc.sid not in (
select sid from sc
where sc.cid = '01'
)
AND sc.cid= '02';
# 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
# 思路:使用group by之后再having判断平均成绩,与student表联结得到学生姓名
SELECT t1.sid,t2.sname,avg(score) as average FROM sc as t1
LEFT JOIN student as t2
ON t1.sid=t2.sid
GROUP BY t1.sid,t2.sname
having average>=60;
# 3. 查询在 SC 表存在成绩的学生信息
# 思路:查询成绩表中成绩不为0的学生sid,然后where判断学生表中的sid在其中
SELECT * FROM student
where sid in (SELECT DIStiNCT(sid) FROM sc WHERE score IS NOT NULL);
# 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
# 思路:先对成绩表进行group by然后用学生表left join
SELECT student.sid,student.sname,t1.cc,t1.ss FROM student left JOIN
(SELECT sc.Sid,COUNT(sc.cid) as cc,SUM(sc.score) as ss from sc
GROUP BY sc.sid) as t1
ON student.sid=t1.sid;
# 4.1 查有成绩的学生信息
# IN()适用于student表大于score表的情况
SELECT * from student
WHERE student.sid IN
(SELECT sid FROM sc WHERE score IS NOT NULL);
# exists()适用于score表大于student表的情况
SELECT * FROM student
WHERE EXISTS (SELECT sc.sid FROM sc WHERE student.sid=sc.sid);
# 5. 查询「李」姓老师的数量
SELECT '李老师' as name,count(tname) as num FROM teacher
WHERE tname like '李%';
# 6. 查询学过「张三」老师授课的同学的信息
# 思路1:使用子查询
SELECT * FROM student
WHERE sid in (
SELECT sid FROM sc WHERE cid in(
SELECT cid FROM course,teacher WHERE course.tid=teacher.tid and teacher.tname='张三'));
# 思路2:多表联查
SELECT student.* from student,sc,course,teacher
WHERE student.sid=sc.siD
AND sc.cid=course.cid
AND course.tid=teacher.tid
AND teacher.tname='张三';
# 7. 查询没有学全所有课程的同学的信息
SELECT * FROM student
WHERE sid NOT IN(
SELECT sc.sid from sc
GROUP BY sc.sid
HAVING COUNT(sc.cid)=(SELECT COUNT(cid) FROM course));
# 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
# 思路:先求出学号为'01'同学的课程,然后成绩表sc中的课程cid在里面
select * from student
where student.sid in (
select sc.sid from sc
where sc.cid in(
select sc.cid from sc
where sc.sid = '01'
)
);
# 9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
# 思路:否定之否定,等于肯定。先查询 只要学过的课程不在"01"同学课程中 的sid,那么其它的sid对应课程肯定都在01同学的课程中,最后限制数量相等
SELECT * FROM sc
WHERE sid IN(
SELECT sid FROM sc
WHERE sid NOT in(
SELECT sid FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid='01')
)
GROUP BY sid
HAVING count(*)=(SELECT count(*) FROM sc WHERE sid='01')
);
# 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
# 思路:查询有学过"张三"老师的课的学生sid,然后排除这些sid
SELECT sid,sname FROM student
WHERE sid NOT IN(
SELECT sid FROM sc,course,teacher
WHERE sc.cid=course.cid
AND course.tid=teacher.tid
AND teacher.tname='张三'
);
# 11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
# 先查询大于2门不及格的学生sid,再联查其平均成绩和姓名
SELECT t1.sid,t2.avgs,student.sname FROM
(SELECT sid FROM sc
WHERE score<60
GROUP BY sid
HAVING count(*)>=2) as t1
LEFT JOIN(
SELECT sid,avg(score)as avgs FROM sc GROUP BY sid) as t2
ON t1.sid=T2.sid
LEFT JOIN student
ON t1.sid=student.sid;
# 12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
SELECT student.*,sc.score from sc,student
WHERE sc.sid=student.sid AND sc.cid='01' AND sc.score<60
ORDER BY score DESC;
# 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
# 思路:使用开窗函数over()
SELECT sid,cid,score,
avg(score) over(PARTITION by sid) as avgs
FROM sc;
# 14. 查询各科成绩最高分、最低分和平均分
# 思路:直接使用max()/min()/avg()/sum()和case语句
/*
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,
优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
*/
SELECT sc.cid,course.cname,max(score) as maxs,min(score)as mins,avg(score)as avgs,
SUM(case when score>=60 then 1
else 0 end)/count(*) as '及格率',
SUM(case when score>=70 AND score<80 then 1
else 0 end)/count(*) as '中等率',
SUM(case when score>=80 AND score<90 then 1
else 0 end)/count(*) as '优良率',
SUM(case when score>=90 then 1
else 0 end)/count(*) as '优秀率',
count(*) as '选修人数'
FROM sc LEFT JOIN course
ON sc.cid=course.cid
GROUP BY sc.cid,course.cname
ORDER BY '选修人数' desc,sc.cid ;
#15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
SELECT sc.*,rank() over(PARTITION by cid ORDER BY score desc) as 'rank' FROM sc;
# 15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
SELECT sc.*,dense_rank() over(PARTITION by cid ORDER BY score desc) as 'rank' FROM sc;
# 15.2 按各科成绩进行排序,并显示排名, Score 重复时随机排名
SELECT sc.*,row_number() over(PARTITION by cid ORDER BY score desc) as 'rank' FROM sc;
# 16. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
SELECT sc.sid,SUM(score) as 'sums',rank() over(ORDER BY sum(score) DESC) as 'rank'
FROM sc
GROUP BY sid;
# 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
SELECT sc.sid,SUM(score) as 'sums',dense_rank() over(ORDER BY sum(score) DESC) as 'rank'
FROM sc
GROUP BY sid;
# 17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT sc.cid,course.cname,
sum(case when score<=60 then 1 else 0 end) as '[0-60]',
concat(round(sum(case when score<60 then 1 else 0 end)/count(*)*100,2),'%') as '[0-60]%',
sum(case when score>=60 AND score<70 then 1 else 0 end) as '[70-60]',
concat(round(sum(case when score>=60 AND score<70 then 1 else 0 end)/count(*)*100,2),'%') as '[70-60]%',
sum(case when score>=70 AND score<85 then 1 else 0 end) as '[85-70]',
concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(*)*100,2),'%') as '[85-70]%',
sum(case when score>=85 then 1 else 0 end) as '[100-85]',
concat(round(sum(case when score>=85 then 1 else 0 end)/count(*)*100,2),'%') as '[100-85]%'
FROM sc LEFT JOIN course
ON sc.cid=course.cid
GROUP BY sc.cid,course.cname;
# 18. 查询各科成绩前三名的记录
SELECT * FROM sc as a
WHERE 3>(SELECT count(*) FROM sc as b WHERE a.cid=b.cid AND a.score<b.score)
ORDER BY a.cid,a.score desc;
# 采用rank()/dense_rank()/row_number()和over()
SELECT * FROM
(SELECT *,
row_number() over(PARTITION by cid ORDER BY score DESC) as 'rank'
FROM sc)as t1 WHERE t1.rank<4;
SELECT * FROM
(SELECT *,
rank() over(PARTITION by cid ORDER BY score DESC) as 'rank'
FROM sc) as t1 WHERE t1.rank<4;
SELECT * FROM
(SELECT *,
dense_rank() over(PARTITION by cid ORDER BY score DESC) as 'rank'
FROM sc)as t1 WHERE t1.rank<4;
# 19. 查询每门课程被选修的学生数
SELECT cid,count(*) as num
FROM sc
GROUP BY cid;
# 20. 查询出只选修两门课程的学生学号和姓名
SELECT sc.sid, student.sname
FROM sc LEFT JOIN student
ON sc.sid=student.sid
GROUP BY sid,sname
HAVING count(*)=2;
# 21.查询男生、女生人数
SELECT ssex,count(*) as num
FROM student
GROUP BY ssex;
# 22.查询名字中含有「风」字的学生信息
SELECT * FROM student
WHERE sname LIKE '%风%';
# 23.查询同名学生名单,并统计同名人数
SELECT * FROM student
WHERE sname in(
SELECT sname FROM student GROUP BY sname HAVING count(*)>1
);
# 24.查询 1990 年出生的学生名单
SELECT * FROM student
WHERE YEAR(Sage)=1990;
# 25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT cid,avg(score) as avgs
FROM sc
GROUP BY cid
ORDER BY avg(score) DESC,cid;
# 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT sc.sid,student.sname,avg(score) as 'avgs'
FROM sc LEFT JOIN student
ON sc.sid=student.sid
GROUP BY sc.sid,student.sname
HAVING avg(score)>=85;
# 27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT student.sname,sc.score,course.cname
FROM student,sc,course
WHERE course.cname='数学'
AND course.cid=sc.cid
AND sc.sid=student.sid
AND sc.score<60;
# 28. 查询所有学生的课程及分数情况
SELECT * FROM student
LEFT JOIN sc
ON student.sid=sc.sid;
# 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
# 思路:有分数低于70的学生sid就排除
SELECT student.sname,course.cname,sc.score
FROM sc LEFT JOIN student
ON sc.sid=student.sid
LEFT JOIN course
ON sc.cid=course.cid
WHERE sc.sid NOT IN(SELECT sid FROM sc WHERE score<=70)
ORDER BY student.sname;
# 30.查询存在不及格的课程
SELECT * FROM sc WHERE score<60;
# 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT sc.sid,student.sname
FROM sc LEFT JOIN student
ON sc.sid=student.sid
WHERE sc.cid='01' AND sc.score>=80;
# 32. 求每门课程的学生人数
SELECT cid,count(*) as 'Snum'
FROM sc
GROUP BY
cid;
# 33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT student.*,sc.score FROM sc,student,course,teacher
WHERE teacher.tname='张三'
AND teacher.tid=course.tid
AND course.cid=sc.cid
AND sc.sid=student.sid
ORDER BY sc.score DESC
LIMIT 1;
# 34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT student.*,sc.score,
rank() over(ORDER BY score DESC) as 'rank'
FROM sc,student,course,teacher
WHERE teacher.tname='张三'
AND teacher.tid=course.tid
AND course.cid=sc.cid
AND sc.sid=student.sid;
#35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DIStiNCT t1.sid,t1.cid,t1.score FROM sc as t1
INNER JOIN sc as t2
ON t1.sid=t2.sid
WHERE t1.score=t2.score AND t1.cid<>t2.cid;
#36.查询每门功成绩最好的前两名
# 感觉用dense_rank()更公平一些,并列算一个人,排名连续。
SELECT * FROM(
SELECT *,
dense_rank() over(PARTITION by cid ORDER BY score DESC) as 'rank'
FROM sc) as t1
WHERE t1.rank<3;
SELECT * FROM(
SELECT *,
rank() over(PARTITION by cid ORDER BY score DESC) as 'rank'
FROM sc) as t1
WHERE t1.rank<3;
#37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)
SELECT cid,count(*) as 'Snum'
FROM sc
GROUP BY cid
HAVING count(*)>5;
# 38. 检索至少选修两门课程的学生学号
SELECT sid,count(*) as 'Cnum'
FROM sc
GROUP BY sid
HAVING count(*)>2;
# 39. 查询选修了全部课程的学生信息
SELECT * FROM student
WHERE sid IN(
SELECT sid FROM sc
GROUP BY sid
HAVING count(sc.cid)=(SELECT count(course.cid) FROM course)
);
# 40. 查询各学生的年龄,只按年份来算
SELECT student.*,YEAR(NOW())-YEAR(Sage) as 'age' from student;
# 41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
SELECT student.*,
(
CASE WHEN MONTH(NOW())<MONTH(Sage) OR(MONTH(NOW())=MONTH(Sage) AND DAY(NOW())<MONTH(Sage)) THEN YEAR(NOW())-YEAR(Sage)-1
ELSE YEAR(NOW())-YEAR(Sage)
END
)as 'age'
FROM student;
# 42. 查询本周过生日的学生
SELECT * FROM student
WHERE WEEKOFYEAR(NOW())=WEEKOFYEAR(Sage);
# 43. 查询下周过生日的学生
SELECT * FROM student
WHERE WEEKOFYEAR(NOW())+1=WEEKOFYEAR(Sage);
# 44. 查询本月过生日的学生
SELECT * FROM student
WHERE MONTH(NOW())=MONTH(Sage);
# 45. 查询下月过生日的学生
SELECT * FROM student
WHERE MONTH(NOW())+1=MONTH(Sage);
2. 建表SQL
drop TABLE if EXISTS `student`;
CREATE TABLE `student` (
`sid` varchar(10) DEFAULT NULL COMMENT '学生ID',
`sname` varchar(10) DEFAULT NULL COMMENT '学生名字',
`sage` datetime DEFAULT NULL COMMENT '学生年龄',
`ssex` varchar(10) DEFAULT NULL COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='学生表';
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' , '女');
drop TABLE if EXISTS `course`;
CREATE TABLE `course` (
`cid` varchar(10) DEFAULT NULL COMMENT '课程ID',
`cname` varchar(10) DEFAULT NULL COMMENT '课程名字',
`tid` varchar(10) DEFAULT NULL COMMENT '代课老师ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
drop TABLE if EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` varchar(10) DEFAULT NULL COMMENT '教师ID',
`tname` varchar(10) DEFAULT NULL COMMENT '教师名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='教师表';
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
drop TABLE if EXISTS `sc`;
CREATE TABLE `sc` (
`sid` varchar(10) DEFAULT NULL COMMENT '学生ID',
`cid` varchar(10) DEFAULT NULL COMMENT '课程ID',
`score` decimal(18,1) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='成绩表';
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);