1、學生表 student（s_id：學生id，s_name：學生姓名，s_birth：學生生日，s_sex：學生性別）：
2、教師表teacher（t_id：教師id，t_name：教師姓名)
3、課程表 course（c_id：課程id，c_name：課程名稱，t_id：教師id）：
4、成績表 score（s_id：學生id，c_id：課程id，score：分數）
初始化數據的sql附在文章末尾
挑戰
1、查詢各科成績前三名的記錄
--方法1：SELECT s_name,a.s_id,a.c_id,a.score FROM score a LEFT JOIN score b ON a.c_id = b.c_id AND a.score<b.scoreJOIN studentON a.s_id=student.s_idGROUP BY a.s_id,a.c_id,a.score HAVING COUNT(b.s_id)<3ORDER BY a.c_id,a.score DESC--方法2：SELECT a.s_id,a.c_id,a.score FROM score aWHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.score>=a.score)<=2 ORDER BY a.c_id思路：
SELECT * FROM score a JOIN score b ON a.c_id=b.c_id WHERE a.c_id=『03』 這一句，形成了03課程的所有學員的兩兩組合（帶上03，是為了簡化數據，便於分析）SELECT * FROM score a JOIN score b ON a.c_id=b.c_id WHERE a.c_id=『03』 AND a.score<b.score又篩選出每個學員03課程比其他人低的情況（某個學員分越低，記錄越多，沒記錄說明是最高分）將這些記錄按s_id分組，篩選出記錄數小於3的（說明最多有兩個人比自己分數高），即是前3名2、查詢「01」課程比「02」課程成績高的所有學生的學號
SELECT *FROM (SELECT s_id,score,c_id FROM score WHERE c_id='01') aJOIN (SELECT s_id,score,c_id FROM score WHERE c_id='02') bON a.s_id=b.s_id WHERE a.score>b.score思路：
通過SELECT s_id,score,c_id FROM score WHERE c_id='01』和SELECT s_id,score,c_id FROM score WHERE c_id='02』的兩個臨時表聯查，得到每個學員的01、02課程成績情況：接下來，a.score>b.score條件直接篩選就可以了。
3、查詢和"04"號的同學學習的課程完全相同的其他同學的信息
SELECT score.s_id,COUNT(DISTINCT c_id) FROM student JOIN score ON student.s_id=score.s_id GROUP BY score.s_id HAVING COUNT(DISTINCT c_id)= (SELECT COUNT(c_id) FROM score WHERE s_id='04') AND s_id NOT IN -- 篩選出學過（04號同學未學課程）的學生，通過not in排除掉 (SELECT s_id FROM score WHERE c_id IN (SELECT DISTINCT(c_id) FROM course WHERE c_id NOT IN (SELECT c_id FROM score WHERE s_id='04'))) AND score.s_id!='04'思路：
通常思路，我們先獲取04號同學的課程，再遍歷其他同學，以此篩選。這種方式適用於編程，單純的sql實現不了，我們應該從下面這個思路去考慮->我和04號同學學習的課程數目一樣多，且04號同學沒學過的我也沒學過，那麼我不就是和04號同學學習的課程一樣嗎？
4、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
SELECT a.s_id,b.s_name, MAX(CASE a.c_id WHEN '01' THEN a.score END ) 內功, MAX(CASE a.c_id WHEN '02' THEN a.score END ) 劍法, MAX(CASE a.c_id WHEN '03' THEN a.score END ) 拳法, AVG(a.score) FROM score a JOIN student b ON a.s_id=b.s_id GROUP BY a.s_id ORDER BY AVG(a.score) DESC思路不難，關鍵在於理解透徹case when語法。
SELECT a.c_id,b.c_name,MAX(score),MIN(score),ROUND(AVG(score),2), ROUND(100*(SUM(case when a.score>=60 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 及格率, ROUND(100*(SUM(case when a.score>=70 and a.score<=80 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 中等率, ROUND(100*(SUM(case when a.score>=80 and a.score<=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 優良率, ROUND(100*(SUM(case when a.score>=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 優秀率FROM score a LEFT JOIN course b ON a.c_id = b.c_id GROUP BY a.c_id,b.c_name在case when語法的基礎上，進一步掌握sql語句中進行邏輯運算的技巧
5、按各科成績進行排序，並顯示排名
SELECT a.s_id,a.c_id, @i:=@i + 1 AS 排名, @j:=(CASE WHEN @score=a.score THEN @j ELSE @i END) AS 並排排名, @score:=a.score AS scoreFROM (SELECT s_id,c_id,score FROM score GROUP BY s_id,c_id ORDER BY score DESC) a, (SELECT @i:= 0,@j:= 0,@score:= 0) s簡評：Mysql中沒有rank函數，只能使用偽列的概念去實現排名算法。
思路：
SELECT s_id,c_id,score FROM score GROUP BY s_id,c_id ORDER BY score DESC先進行了排序(SELECT @i:=0,@j:=0,@score:=0) s對@i、@j、@score進行了初始化，初始值都為0@i:=@i + 1，遍歷每一行，@i逐行自增@j:=(CASE WHEN @score=a.score THEN @j ELSE @i END)，遍歷每一行，邏輯判斷，@score與上一行的score相等則@j=@i，否則則自增一次6、查詢學生的總成績並進行排名
SELECT a.s_id, @i:=@i+1 as i, @j:=(CASE WHEN @score=a.sum_score THEN @j ELSE @i END) AS rank, @score:=a.sum_score AS scoreFROM (SELECT s_id,SUM(score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC)a, (SELECT @i:=0,@j:=0,@score:=0) s與上一題基本差不多，不同的是a這個臨時表的數據內容而已。
7、查詢所有課程的成績第2名到第3名的學生信息及該課程成績
SELECT student.*,c.rank,c.score,c.c_id FROM (SELECT a.s_id,a.score,a.c_id,@i:=@i+1 as rank from score a,(SELECT @i:=0)s WHERE a.c_id='01' ORDER BY a.score DESC) cLEFT JOIN student ON c.s_id=student.s_idWHERE rank BETWEEN 2 AND 3UNION ALL SELECT student.*,c.rank,c.score,c.c_id FROM (SELECT a.s_id,a.score,a.c_id,@j:=@j+1 as rank from score a,(SELECT @j:=0)s WHERE a.c_id='02' ORDER BY a.score DESC) cLEFT JOIN student ON c.s_id=student.s_idWHERE rank BETWEEN 2 AND 3UNION ALL SELECT student.*,c.rank,c.score,c.c_id FROM (SELECT a.s_id,a.score,a.c_id,@k:=@k+1 as rank from score a,(SELECT @k:=0)s WHERE a.c_id='03' ORDER BY a.score DESC) cLEFT JOIN student ON c.s_id=student.s_idWHERE rank BETWEEN 2 AND 3思路：
先將01課程的所有人的分數排序，再進一步根據rank BETWEEN 2 AND 3篩選出2、3名使用同樣方法，篩選02、03課程數據，使用 UNION ALL合併查詢結果8、統計各科成績各分數段人數：課程編號、課程名稱、[80-100]，[60-80]，[0-60]及所佔百分比
SELECT DISTINCT e.c_name,a.c_id,b.`80-100`,b.百分比,c.`60-80`,c.百分比,d.`0-60`,d.百分比 FROM score aLEFT JOIN (SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS `80-100`, ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 end)/count(*)),2) AS 百分比 FROM score GROUP BY c_id)b ON a.c_id=b.c_idLEFT JOIN (SELECT c_id,SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 END) AS `60-80`, ROUND(100*(SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 end)/count(*)),2) AS 百分比 FROM score GROUP BY c_id)c ON a.c_id=c.c_idLEFT JOIN (SELECT c_id,SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END) AS `0-60`, ROUND(100*(SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END)/count(*)),2) AS 百分比 FROM score GROUP BY c_id)d ON a.c_id=d.c_idLEFT JOIN course e ON a.c_id = e.c_id思路：SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS80-100, ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END)/count(*)),2) AS 百分比 FROM score GROUP BY c_id得到所有課程80-100分成績的統計臨時表：然後再用同樣的方式，得到0-60、60-80的統計臨時表，三表聯查。
9、查詢學生平均成績及其名次
SELECT a.s_id, @i:=@i+1 as '不保留空缺排名', @k:=(CASE WHEN @avg_score=a.avg_s THEN @k ELSE @i END) AS '保留空缺排名', @avg_score:=avg_s AS '平均分'FROM (SELECT s_id,ROUND(AVG(score),2) AS avg_s FROM score GROUP BY s_id ORDER BY avg_s DESC)a, (SELECT @avg_score:=0,@i:=0,@k:=0)b;
10、查詢各學生的年齡
SELECT 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)) AS ageFROM student;本身的實現思路並不難，DATE_FORMAT掌握其用法即可。
11、分別查詢本周、下周、本月、下月過生日的學生
--本周SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)--下周SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = WEEK(s_birth)--本月SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)--下月SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)
進階
1、查詢及格學生（每門課程的分數>=60）的學生姓名
SELECT s_name FROM student WHERE s_id NOT IN (SELECT DISTINCT(s_id) FROM score WHERE score<60)或者：
SELECT s_name FROM student a JOIN score b ON a.s_id=b.s_id GROUP BY b.s_id HAVING MIN(score)>602、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績 (包括有成績的和無成績的)
SELECT s_name,ROUND(avg(score),2) AS avg FROM student JOIN score ON student.s_id=score.s_id GROUP BY score.s_id HAVING avg<60 UNION ALL SELECT s_name,0 AS avg FROM student WHERE s_id NOT IN(SELECT DISTINCT(s_id) FROM score)-- 方法2SELECT s_name,IFNULL(ROUND(avg(score),2),0) AS avg FROM student LEFT JOIN score ON student.s_id=score.s_id GROUP BY score.s_id HAVING avg<60 OR avg is NULL3、查詢學過"張三丰"老師授課的同學的信息
SELECT * FROM score JOIN student ON score.s_id=student.s_id WHERE c_id IN (SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='張三丰')4、查詢沒學過"張三丰"老師授課的同學的信息
SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM score WHERE c_id IN (SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='張三丰'))5、查詢沒有學全所有課程的同學的信息
SELECT s_name,COUNT(c_id) AS count FROM student LEFT JOIN score ON student.s_id=score.s_id GROUP BY student.s_id HAVING count<(SELECT COUNT(1) FROM course);6、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息
SELECT * FROM student WHERE s_id IN (SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN (SELECT a.c_id FROM score a WHERE a.s_id='01'));7、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
SELECT a.s_id,a.s_name,ROUND(AVG(b.score)) FROM student a LEFT JOIN score b ON a.s_id = b.s_idWHERE a.s_id IN (SELECT s_id from score WHERE score<60 GROUP BY s_id HAVING count(1)>=2)GROUP BY a.s_id,a.s_name8、查詢所有學生的課程及分數情況；
SELECT a.s_id,a.s_name, SUM(CASE c.c_name WHEN '內功' THEN b.score ELSE 0 END) AS '內功', SUM(CASE c.c_name WHEN '劍法' THEN b.score ELSE 0 END) AS '劍法', SUM(CASE c.c_name WHEN '拳法' THEN b.score ELSE 0 END) AS '拳法', SUM(b.score) as '總分'FROM student a LEFT JOIN score b ON a.s_id = b.s_id LEFT JOIN course c ON b.c_id = c.c_id GROUP BY a.s_id,a.s_name9、查詢選修"張三丰"老師所授課程的學生中，成績最高的學生信息及其成績
SELECT s_id,MAX(score) FROM score WHERE c_id IN (SELECT c_id FROM teacher JOIN course ON teacher.t_id=course.t_id WHERE t_name='張三丰')10、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
SELECT * FROM score a,score b where a.c_id != b.c_id and a.score = b.score11、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
SELECT c_id,COUNT(*) AS total FROM score GROUP BY c_id HAVING total>4 ORDER BY total DESC,c_id ASC12、查詢選修了全部課程的學生信息
SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM course))
基礎
1、查詢不及格的課程
SELECT a.s_id,a.c_id,b.c_name,a.score FROM score a LEFT JOIN course b ON a.c_id = b.c_idWHERE a.score<602、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名
SELECT a.s_id,b.s_name FROM score a LEFT JOIN student b ON a.s_id = b.s_idWHERE a.c_id = '01' AND a.score>803、查詢每個同學的姓名、選課數、總成績
SELECT s_name,COUNT(c_id),SUM(score) FROM student a LEFT JOIN score b ON a.s_id=b.s_id GROUP BY b.s_id4、查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績
SELECT s_name,ROUND(avg(score),2) AS avg FROM student JOIN score ON student.s_id=score.s_id GROUP BY score.s_id HAVING avg>605、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績
SELECT student.s_name,COUNT(c_id),IFNULL(SUM(score),0) AS sum FROM student LEFT JOIN score ON student.s_id=score.s_id GROUP BY student.s_id ORDER BY sum DESC16、查詢"張"姓老師的數量
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '張%'7、查詢"01"課程分數小於60，按分數降序排列的學生信息
SELECT a.*,b.c_id,b.scoreFROM student a,score bWHERE a.s_id=b.s_id AND b.c_id='01' AND b.score<60ORDER BY b.score DESC;8、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數
SELECT a.s_name,b.c_name,c.score FROM course b LEFT JOIN score c ON b.c_id = c.c_idLEFT JOIN student a ON a.s_id=c.s_id WHERE c.score>=709、查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息
SELECT a.* FROM student a,score b,score c WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id='01' AND c.c_id='02';10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息
SELECT a.* FROM student a WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id='01' ) AND a.s_id NOT IN(SELECT s_id FROM score WHERE c_id='02')11、查詢男生、女生人數
SELECT s_sex,COUNT(s_sex) FROM student GROUP BY s_sex12、查詢名字中含有"圓"字的學生信息
SELECT * FROM student WHERE s_name LIKE '%圓%';13、查詢同名同性學生名單，並統計同名人數
SELECT a.s_name,a.s_sex,count(*) FROM student a JOIN student b ON a.s_id !=b.s_id and a.s_name = b.s_name AND a.s_sex = b.s_sexGROUP BY a.s_name,a.s_sex14、查詢1990年出生的學生名單
SELECT s_name FROM student WHERE s_birth LIKE '1337%'15、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
SELECT c_id,ROUND(AVG(score),2) AS avg_score FROM score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC16、查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
SELECT a.s_id,b.s_name,ROUND(avg(a.score),2) AS avg FROM score aLEFT JOIN student b on a.s_id=b.s_id GROUP BY s_id HAVING avg>=8517、查詢課程名稱為"內功"，且分數低於60的學生姓名和分數
SELECT a.s_name,b.score FROM score b JOIN student a ON a.s_id=b.s_id WHERE b.c_id=(SELECT c_id FROM course WHERE c_name ='內功') AND b.score<6018、求每門課程的學生人數
SELECT c_id,count(*) FROM score GROUP BY c_id;19、檢索至少選修兩門課程的學生學號
SELECT s_id,count(*) AS count FROM score GROUP BY s_id HAVING count>=2數據初始化