MySQL經典50題
1、
#1、查詢“01”課程比“02”課程成績高的所有學生的學号；
SELECT s1.sid
FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02' AND s1.score>s2.score
2、
#2、查詢平均成績大于60分的同學的學号和平均成績；
SELECT sid as 學号, avg(score) as 平均成績
FROM SC GROUP BY sid HAVING 平均成績>60
3、
#3、查詢所有同學的學号、姓名、選課數、總成績
SELECT s2.sid as 學号, s2.sname as 姓名, a.b as 選課數, a.c as 總成績
FROM Student s2,
(SELECT s1.sid as id ,count(s1.cid) as b, sum(s1.score) as c
FROM SC s1 JOIN Student s2 ON s1.sid=s2.sid GROUP BY s1.sid)a
WHERE s2.sid=a.id
4、
#4、查詢姓“李”的老師的個數；
SELECT * FROM Teacher WHERE tname LIKE '李%'
5、
#5、查詢沒學過“張三”老師課的同學的學号、姓名；
SELECT DISTINCT(s1.sid) as 學号,s1.sname as 姓名
FROM Student s1 JOIN SC s2 ON s1.sid=s2.sid AND s2.cid IN
(SELECT c1.cid FROM Teacher t1 JOIN Course c1 ON t1.tid=c1.tid AND t1.tname NOT LIKE '張三')
6、
#6、查詢學過編号“01”并且也學過編号“02”課程的同學的學号、姓名；
SELECT sid as 學号, sname as 姓名 FROM Student WHERE sid in
(SELECT s1.sid
FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02')
7、
#7、查詢學過“張三”老師所教的課的同學的學号、姓名；
SELECT s2.sid as 學号, s2.sname as 姓名
FROM SC s1 JOIN Student s2 ON s1.sid=s2.sid AND s1.cid IN
(SELECT cid FROM Course WHERE tid IN
(SELECT tid FROM Teacher WHERE tname='張三'))
8、
#8、查詢課程編号“01”的成績比課程編号“02”課程低的所有同學的學号、姓名；
SELECT a.sid as 學号, s3.sname as 姓名
FROM (SELECT s1.sid FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02' AND s1.score<s2.score)a JOIN Student s3 ON a.sid=s3.sid
9、
#9、查詢所有課程成績小于60分的同學的學号、姓名；
SELECT DISTINCT(s2.sid) as 學号, s2.sname as 姓名
FROM SC s1 JOIN Student s2 ON s1.sid=s2.sid WHERE s1.score<60
10、
#10、查詢沒有學全所有課的同學的學号、姓名；
SELECT s2.sid as 學号, s2.sname as 姓名
FROM (SELECT s1.sid as s3, count(1) as n
FROM SC s1 GROUP BY s1.sid HAVING n!=(SELECT COUNT(1) FROM Course))a JOIN Student s2 ON a.s3=s2.sid
11、
利用01的課程号當作連表查詢的條件
#11、查詢至少有一門課與學号為“01”的同學所學相同的同學的學号和姓名；
SELECT s2.sid as 學号, s2.sname as 姓名
FROM(SELECT DISTINCT(s1.sid)as s3
FROM(SELECT cid FROM SC WHERE sid='01')a JOIN SC s1 ON a.cid=s1.cid
WHERE s1.sid!='01')b JOIN Student s2 ON b.s3=s2.sid
12、
#12、查詢和"01"号的同學學習的課程完全相同的其他同學的學号和姓名
##01學生上課的數量
SELECT COUNT(1) FROM SC WHERE sid='01'
##
SELECT s3.sid as 學号, s3.sname as 姓名
FROM Student s3 JOIN (SELECT s1.sid as s2,count(1) as n
FROM(SELECT * FROM SC WHERE sid='01')a JOIN SC s1 ON a.cid=s1.cid GROUP BY s1.sid HAVING n=3)b
WHERE sid=b.s2 AND sid!='01'
13、
#13、把“SC”表中“張三”老師教的課的成績都更改為此課程的平均成績；
##這裡不用更新了，篩選出來，計算平均數，生成搜尋視圖就行
SELECT avg(score) FROM SC WHERE cid IN(
SELECT cid FROM Course WHERE tid IN (SELECT tid FROM Teacher WHERE tname='張三'))
14、
#14、查詢沒學過"張三"老師講授的任一門課程的學生姓名
SELECT sname as 學生姓名 FROM Student WHERE sid NOT IN
(SELECT sid FROM SC s1 JOIN (SELECT cid FROM Course WHERE tid IN(SELECT tid FROM Teacher WHERE tname='張三'))a ON s1.cid=a.cid)
15、
#15、查詢兩門及其以上不及格課程的同學的學号，姓名及其平均成績
SELECT s2.sid as 學号, s2.sname as 姓名, a.c1 as 平均成績
FROM Student s2 JOIN (SELECT sid as id,count(1) as n,AVG(score) as c1
FROM SC s1 WHERE score<60 GROUP BY sid HAVING n>=2)a WHERE s2.sid=a.id
16、
#16、檢索"01"課程分數小于60，按分數降序排列的學生資訊
SELECT s4.* ,a.s3
FROM Student s4 JOIN
(SELECT sid as s2, score as s3 FROM SC s1 WHERE cid='01' AND score<60)a ON s4.sid=a.s2 ORDER BY a.s3 DESC
17、
#17、按平均成績從高到低顯示所有學生的平均成績
##排序錯位
SELECT s1.sid as 學号, s1.sname as 姓名, a.av as 平均成績
FROM Student as s1 JOIN
(SELECT sid, avg(score) as av FROM SC s1 GROUP BY sid ORDER BY av DESC)a
ON s1.sid=a.sid
#要把排序寫在外面
SELECT s1.sid as 學号, s1.sname as 姓名, a.av as 平均成績
FROM Student as s1 JOIN
(SELECT sid, avg(score) as av FROM SC s1 GROUP BY sid )a
ON s1.sid=a.sid ORDER BY a.av DESC
18、
#18、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率
SELECT a.cid as 課程ID, Course.cname as 課程name,a.ma as 最高分, a.mi as 最低分, a.av as 平均分,a.p as 及格率
FROM(SELECT cid,MAX(score) as ma ,MIN(score) as mi ,AVG(score) as av, CONCAT((COUNT(if (score>=60,score,NULL))/COUNT(score))*100,'%') as p
FROM SC s1 GROUP BY cid)a, Course WHERE a.cid=Course.cid
##精簡版
SELECT s1.cid,MAX(score) as ma ,MIN(score) as mi ,AVG(score) as av, CONCAT((COUNT(if (score>=60,score,NULL))/COUNT(score))*100,'%') as p
FROM SC s1 JOIN Course ON s1.cid=Course.cid GROUP BY s1.cid
COUNT(IF（條件，true時結果，false時結果））這個語句要記住！！！
19、
#19、按各科平均成績從低到高和及格率的百分數從高到低順序
SELECT cid, AVG(score) as av,COUNT(IF(score>=60,score,NULL))/COUNT(score) as p FROM SC GROUP BY cid ORDER BY av ASC,p DESC
20、
#20、查詢學生的總成績并進行排名
SELECT sid,SUM(score) as sscore
FROM SC as s1 GROUP BY sid ORDER BY sscore DESC
21、
#21、查詢不同老師所教不同課程平均分從高到低顯示
SELECT s1.cid as 課程編号, t1.tname as 老師姓名,AVG(s1.score) as av
FROM SC s1 JOIN Course c1 ON s1.cid=c1.cid JOIN Teacher t1 ON c1.tid=t1.tid GROUP BY t1.tname , s1.cid ORDER BY av DESC
22、
23、
24、
#24、查詢學生平均成績及其名次
SELECT (@i:=@i+1) as 排名,a.s as 學号,a.av as 平均成績
FROM(SELECT sid as s,AVG(score) as av
FROM SC GROUP BY sid ORDER BY av DESC)a,(SELECT @i:=0) as j
25、
26、
#26、查詢每門課程被選修的學生數
SELECT cname as 課程, count(DISTINCT(sid))as 選課學生人數
FROM SC s1 JOIN Course c1 ON s1.cid=c1.cid GROUP BY cname
27、
#27、查詢出隻選修了一門課程的全部學生的學号和姓名
###資料中沒有之選了一門的，是以改成選兩門的吧
SELECT sid as 學号, sname as 姓名 FROM Student WHERE sid IN
(SELECT sid as id
FROM SC s1 GROUP BY sid HAVING count(1)=2)
28、
#28、查詢男生、女生人數
SELECT ssex as 性别, COUNT(1) as 人數
FROM Student GROUP BY ssex
29、
#29、查詢名字中含有"風"字的學生資訊
SELECT * FROM Student WHERE sname LIKE '%風%'
30、
#30、查詢同名同性學生名單，并統計同名人數
####同名同性其實就是一類，GROUP BY 就行!!!
SELECT sname,ssex, count(1) as n
FROM Student GROUP BY sname,ssex HAVING n>1
31、
#31、查詢1990年出生的學生名單(注：Student表中Sage列的類型是datetime)
SELECT * FROM Student WHERE YEAR(sage)=1990
32、
SELECT cid, AVG(score) as av
FROM SC s1 GROUP BY cid ORDER BY av asc,cid DESC
37、
#37、查詢不及格的課程，并按課程号從大到小排列
SELECT sid,cid,score FROM SC WHERE score<60 ORDER BY cid DESC
38、
#38、查詢課程編号為"01"且課程成績在60分以上的學生的學号和姓名；
SELECT s1.sid as 學号,s1.sname as 姓名
FROM(SELECT sid FROM SC WHERE cid='01' AND score>60)a LEFT JOIN Student s1 ON a.sid=s1.sid
40、
#40、查詢選修“張三”老師所授課程的學生中，成績最高的學生姓名及其成績
SELECT a.s as 學号, s1.sname as 姓名,a.sc1 as 成績
FROM(SELECT sid as s,score as sc1 FROM SC WHERE cid=(SELECT cid FROM Course WHERE tid=(SELECT tid FROM Teacher WHERE tname='張三')))a LEFT JOIN Student s1 ON a.s=s1.sid ORDER BY a.sc1 DESC LIMIT 1
42、查詢每門功課成績最好的前兩名
43、
#43、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程号和選修人數，查詢結果按人數降序排列，若人數相同，按課程号升序排列
SELECT cid as 課程号, count(1) as number
FROM SC s1 GROUP BY cid HAVING number>5 ORDER BY number DESC,cid ASC
44、
#44、檢索至少選修兩門課程的學生學号
SELECT s2.sid as 學号, s2.sname as 姓名
FROM(SELECT sid FROM SC s1 GROUP BY sid HAVING COUNT(1)>=2)a LEFT JOIN Student s2 ON a.sid=s2.sid
45、
#45、查詢選修了全部課程的學生資訊
SELECT a.sid as 學号, s1.sname as 姓名
FROM(SELECT sid
FROM SC GROUP BY sid HAVING count(1)=(SELECT COUNT(DISTINCT(cname)) as n FROM Course))a, Student s1 WHERE a.sid=s1.sid
46、
#46、查詢各學生的年齡
SELECT sid as 學号, sname as 姓名,(YEAR(NOW())-YEAR(sage)) as 年齡
FROM Student
47-48
#47、查詢本周過生日的學生
SELECT WEEK(NOW())#檢視目前星期
SELECT WEEK(sage) FROM Student#檢視所有學生生日對應的星期
SELECT sid,sname FROM Student WHERE WEEK(sage)=WEEK(NOW())
#48、查詢下周過生日的學生
SELECT sid,sname FROM Student WHERE WEEK(sage)=WEEK(NOW())+1
49-50
#49、查詢本月過生日的學生
SELECT sid,sname FROM Student WHERE MONTH(sage)=MONTH(NOW())
#50、查詢下月過生日的學生
SELECT sid,sname FROM Student WHERE MONTH(sage)=MONTH(NOW())+1