原文地址：
https://blog.csdn.net/xiushuiguande/article/details/79476964
实验数据
CREATE DATABASE IF NOT EXISTS SQL50; USE SQL50;
CREATE TABLE Student(sid VARCHAR(10),sname VARCHAR(10),sage DATETIME,ssex NVARCHAR(10)); INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男'); INSERT INTO Student VALUES('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 Student VALUES('07' , '郑竹' , '1989-07-01' , '女'); INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
CREATE TABLE Course(cid VARCHAR(10),cname VARCHAR(10),tid VARCHAR(10)); INSERT INTO Course VALUES('01' , '语文' , '02'); INSERT INTO Course VALUES('02' , '数学' , '01'); INSERT INTO Course VALUES('03' , '英语' , '03');
CREATE TABLE Teacher(tid VARCHAR(10),tname VARCHAR(10)); INSERT INTO Teacher VALUES('01' , '张三'); INSERT INTO Teacher VALUES('02' , '李四'); INSERT INTO Teacher VALUES('03' , '王五');
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);
4张表的结构关系
Course课程表：
课程ID、课程名、教师ID
SC成绩表：
学生ID、课程ID、成绩
Student学生表：
学生ID、学生姓名、学生年龄、学生性别、
Teacher老师表：
教师ID、教师名
1、查询“01”课程比“02”课程成绩高的所有学生的学号
先查询 01课程的成绩表 & 02课程的成绩表
SELECT * FROM `sc` WHERE `cid` = 1; SELECT * FROM `sc` WHERE `cid` = 2;
使用连接查询，附加 01表的成绩 > 02表的成绩
SELECT sc1.`sid` FROM (SELECT `sid`,`cid`,`score` FROM `sc` WHERE `cid` = 1) AS sc1, (SELECT `sid`,`cid`,`score` FROM `sc` WHERE `cid` = 2) AS sc2 where sc1.`sid` = sc2.`sid` and sc1.`score` > sc2.`score`;
2、查询平均成绩大于60分的同学的学号和平均成绩
信息都在成绩表里面，要每个学生的全部成绩的平均成绩，
使用AVG聚合函数，然后对学生ID进行分组GROUP BY
查出上面的结果之后再对平均分进行 > 60的筛选
但是为什么SQL查不出结果？？？
SELECT `sid`,AVG(`score`) AS 'avg_sc' FROM `sc` GROUP BY `sid` HAVING 'avg_sc' > 60;
3、查询所有同学的学号、姓名、选课数、总成绩
因为在成绩表里面，可以使用分组+计数+求和，求出成绩表内的学号，选课数和总成绩
然后再和学生表进行一个左连接，学生表作为主表，上面的结果表作为从表，因为还存在学生表没有选课的情况
成绩表的筛选
SELECT `sid`,COUNT(`cid`) AS '选课数',SUM(`score`) as '总成绩' FROM `sc` GROUP BY `sid`;
左连接学生表
SELECT S1.`sid`,S1.`sname`,S2.选课数,S2.总成绩 FROM `student` AS S1 LEFT JOIN ( SELECT `sid`,COUNT(`cid`) AS '选课数',SUM(`score`) AS '总成绩' FROM `sc`
GROUP BY `sid`) AS S2 ON S1.`sid` = S2.`sid`;
已采用内连接：
SELECT S1.`sid`,S1.`sname`,S2.选课数,S2.总成绩 FROM `student` AS S1 INNER JOIN ( SELECT `sid`,COUNT(`cid`) AS '选课数',SUM(`score`) AS '总成绩' FROM `sc` GROUP BY `sid`) AS S2 ON S1.`sid` = S2.`sid`;
4、查询姓“李”的老师的个数
只在李老师表内查询，使用计数函数
SELECT COUNT(*) FROM `teacher` WHERE `tname` LIKE '李%';
5、查询没学过“张三”老师课的同学的学号、姓名；
先筛选张三老师教授的课程ID，可以看到是01课程
SELECT `tid` FROM `teacher` WHERE `tname` = '张三';
通过上面的线索，查找课程表，其对应的课程ID是02
select `cid` -- ,`cname`,`tid` from `course` where `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三');
在成绩表查询没学02课程太难排查了，所以反过来，我们查学了02的学生ID
-- 我们查询学了该课程的学生ID SELECT DISTINCT `sid` FROM `sc` WHERE `cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三'))
再用上面这张表连接学生表查询【左连接】
SELECT S1.`sid`,S1.`sname` FROM `student` AS S1 LEFT JOIN (SELECT DISTINCT `sid` FROM `sc` WHERE `cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三'))) AS S2 ON S1.`sid` = S2.`sid` WHERE S1.`sid` NOT IN(S2.`sid`);
6、查询学过“```”并且也学过编号“```”课程的同学的学号、姓名；
select s.sid,sname from student s join (select a.sid from (select sid from sc where cid = "01") a join (select sid from sc where cid = "02") b on a.sid = b.sid) temp on temp.sid = s.sid;
7、查询学过“张三”老师所教的所有课的同学的学号、姓名；
查询张三老师的教师编号：
SELECT `tid` FROM `Teacher` WHERE `tname` = '张三'
然后根据编号查询所授课程：
SELECT `cid` FROM `Course` WHERE `tid` = (SELECT `tid` FROM `Teacher` WHERE `tname` = '张三');
然后得到对应的所学课程的学生编号
SELECT `sid` FROM `SC` WHERE `cid` IN( SELECT `cid` FROM `Course` WHERE `tid` = ( SELECT `tid` FROM `Teacher` WHERE `tname` = '张三' ) );
最后再联合学生表：
SELECT `sid`,`sname` FROM `Student` WHERE `sid` IN ( select `sid` from `SC` where `cid` IN( SELECT `cid` FROM `Course` WHERE `tid` = ( SELECT `tid` FROM `Teacher` WHERE `tname` = '张三' ) ) );
8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
select a.sid,s.sname from (select sid,score from sc where cid = "01") a join (select sid,score from sc where cid = "02") b on a.sid = b.sid left join student s on a.sid = s.sid where a.score < b.score;
9、查询所有课程成绩小于60分的同学的学号、姓名；
也就是说只要其中一个课程成绩大于60就不算在内
所以就可以先查询大于60分，再取反就行了
大于60分的学生ID：
SELECT `sid` FROM `SC` WHERE `score` > 60
然后取反得到：
SELECT `sid`,`sname` FROM `Student` WHERE `sid` NOT IN(SELECT `sid` FROM `SC` WHERE `score` > 60)
10、查询没有学全所有课的同学的学号、姓名；
首先是需要知道全部课程个数
然后这个查询的意思是，计数学生学习课程的个数，如果小于总课数即没学全
但是需要跟学生表关联，他这里的联动真的难理解。。。
SELECT `sid`,`sname` FROM `Student` WHERE (SELECT COUNT(*) FROM `SC` WHERE `Student`.`sid` = `SC`.`sid`) < (SELECT COUNT(*) FROM `Course`)
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
select sid,sname from student where sid in ( select DISTINCT sid from sc join ((select cid from sc where sid = "01"))temp on temp.cid = sc.cid where sc.sid <> "01" )
24、查询学生平均成绩及其名次
SELECT a.sid, a.avg_score, SUM(CASE WHEN a.avg_score<b.avg_score THEN 1 ELSE 0 END)+1 scc FROM (SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) a INNER JOIN (SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) b GROUP BY a.sid, a.avg_score ORDER BY scc
25、查询各科成绩前三名的记录
SELECT * FROM ( SELECT s.*,a.cid,count(DISTINCT b.score) + 1 AS 名次 FROM sc a LEFT JOIN sc b ON b.cid = a.cid and a.score < b.score LEFT JOIN student s ON s.sid = a.cid group by a.cid,a.sid order by a.cid , 名次 ) resultTab where 名次 BETWEEN 1 AND 3
26、查询每门课程被选修的学生数
SELECT `cid`,COUNT(`sid`) '人数' FROM `sc` GROUP BY `cid`
27、查询出只选修了一门课程的全部学生的学号和姓名
# 查询选课数只有1的学生编号 SELECT `sid`,COUNT(`cid`) '选课数' FROM `sc` GROUP BY `sid` HAVING `选课数` = 1 # 然后关联学生表进行查询 SELECT `student`.`sname`,`ss`.`sid` FROM `student`, (SELECT `sid`,COUNT(`cid`) '选课数' FROM `sc` GROUP BY `sid` HAVING `选课数` = 1) `ss` WHERE `student`.`sid` IN (`ss`.`sid`)
28、查询男生、女生人数
SELECT `ssex`,COUNT(1) '人数' FROM `student` GROUP BY `ssex`
29、查询名字中含有"风"字的学生信息
SELECT * FROM `student` WHERE `sname` LIKE '%风%';
使用正则表达式方式实现：
select * from student where sname REGEXP "风";
另外：
regexp "^风" regexp "风$"
30、查询同名同姓学生名单，并统计同名人数
SELECT `sname`,COUNT(1) AS '同名人数' FROM `student` GROUP BY `sname` HAVING `同名人数` > 1
30、查询同名同性学生名单，并统计同名男生人数
SELECT `sname`,`ssex`, COUNT(1) AS num FROM Student GROUP BY `sname`,`ssex` HAVING COUNT(1) > 1 AND `ssex`='男'
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT * FROM `student` WHERE YEAR(`sage`) = 1990 ;
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
# 对课程编号分组，求平均分，然后分数升序排列，对课程编号降序排列 SELECT `cid`,AVG(`score`) FROM `sc` GROUP BY `cid` ORDER BY `cid` DESC;
37、查询不及格的课程，并按课程号从大到小排列
# 在成绩表中的查询 SELECT * FROM `sc` WHERE `score` < 60 ORDER BY `cid` DESC; # 关联课程表 SELECT `sc`.*, `course`.`cname` FROM `sc`,`course` WHERE `sc`.`cid` = `course`.`cid` AND `sc`.`score` < 60 ORDER BY `sc`.`cid` DESC
38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
# 1、现在成绩表查询课程编号为1的 SELECT * FROM `sc` WHERE `cid` = 01; # 2、再添加分数要求大于60的 SELECT * FROM `sc` WHERE `cid` = 01 AND `score` > 60; # 3、最后联合学生表获取名字 SELECT `sc`.*,`student`.`sname` FROM `sc`,`student` WHERE `sc`.`sid` = `student`.`sid` AND `sc`.`cid` = 01 AND `score` > 60
40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
# 查询老师编号 SELECT `tid` FROM `teacher` WHERE `tname` = '张三'; # 通过编号查询所授课程编号 SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三'); # 查询到成绩表学生编号和成绩 SELECT `sid`,`score` FROM `sc` WHERE`cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三')) ORDER BY `score` DESC LIMIT 1 # 关联学生表 SELECT `student`.`sname`,`ss`.`score` FROM `student`, (SELECT `sid`,`score` FROM `sc` WHERE`cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三')) ORDER BY `score` DESC LIMIT 1) `ss` WHERE `student`.`sid` = `ss`.`sid`
42、查询每门功课成绩最好的前两名学生
43、统计每门课程的学生选修人数（超过5人的课程才统计）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
# 查询成绩表，按照课程字段分组，计数整个表的记录 # 要求课程大于5记录的倒序排序，并返回人数记录 SELECT cid,COUNT(1) COUNT FROM SC GROUP BY cid HAVING COUNT(1)>=5 ORDER BY COUNT DESC,cid ;
44、检索至少选修两门课程的学生学号
# 查询成绩表，并计数课程字段 # 对学号字段分组，之后计数的课程字段大于1的记录返回 SELECT `sid`,COUNT(`cid`) AS C FROM `sc` GROUP BY `sid` HAVING C > 1;
45、查询选修了全部课程的学生信息
# 1 先查询课程表的总课程数量 SELECT COUNT(1) FROM Course # 2 在成绩表中，查询选课累计等于总课程数的学生ID SELECT sid ,COUNT(cid) count_c FROM SC GROUP BY sid HAVING count_c=(SELECT COUNT(1) FROM Course); # 3 查询学生表得到信息 SELECT `student`.* from `student`, (SELECT sid ,COUNT(cid) count_c FROM SC GROUP BY sid HAVING count_c=(SELECT COUNT(1) FROM Course)) as t WHERE `student`.`sid` in (t.`sid`)
46、查询各学生的年龄
# 用当前日期求得年份值，再减去字段日期的年份值即可 SELECT Student.*,YEAR(CURDATE())-YEAR(Student.sage) AS '年龄' FROM Student;
47、查询本周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=0;
48、查询下周过生日的学生
# 使用周按年换算函数，然后和月一样，下一周就取-1 SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=-1;
49、查询本月过生日的学生
# 先求出现在的时间的月，再和字段月相减得为0的记录即可 SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=0;
50、查询下月过生日的学生
# 本月值，减去字段月的值，相差一个月 # 因为本月大于字段月相减求得的差一定是大于0的 # 所以要查出下一个月过生日的人应该为负数，例如下一个月就-1，下3个月就-3 SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=-1;