工作之后發(fā)現(xiàn)自己已經(jīng)不太會寫sql了，除了業(yè)務(wù)方面的學(xué)習(xí)，技術(shù)上也不能落下啊。于是打算做一遍這50題，通過實際操作來加深對sql的理解。
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' , '孫風(fēng)' , '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' , '數(shù)學(xué)' , '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);
- 查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分數(shù)
keyword : 自連接（SELF JOIN）
SELECT student.*, sc1.score AS 'course-01', sc2.score AS 'course-02'
FROM student, sc sc1, sc sc2
WHERE student.sid = sc1.sid
AND sc1.sid = sc2.sid
AND sc1.cid = '01'
AND sc2.cid = '02'
AND sc1.score > sc2.score
1.1 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
keyword : LEFT JOIN
SELECT *
FROM
( SELECT * FROM sc WHERE sc.cid = '01' ) sc1
LEFT JOIN ( SELECT * FROM sc WHERE sc.cid = '02' ) sc2 ON sc1.sid = sc2.sid
1.2 查詢同時存在01和02課程的情況
keyword : 子查詢
SELECT *
FROM
( SELECT * FROM sc WHERE sc.cid = '01' ) sc1,
( SELECT * FROM sc WHERE sc.cid = '02' ) sc2
WHERE sc1.sid = sc2.sid
1.3 查詢選擇了02課程但沒有01課程的情況
keyword : NOT IN
SELECT *
FROM sc
WHERE cid = '02' AND sid NOT IN ( SELECT sid FROM sc WHERE cid = '01' )
2.查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
keyword : GROUP BY、HAVING
--注意版本默認的model問題
SELECT sc.sid,s.sname,avg(score)
FROM student s INNER JOIN sc ON s.sid = sc.sid
GROUP BY sid
HAVING (avg(score) >= 60)
3.查詢在 SC 表存在成績的學(xué)生信息
keyword : DISTINCT
SELECT DISTINCT student.*
FROM sc,student
WHERE sc.sid = student.sid
4.查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的成績總和
keyword : INNER JOIN、COUNT（）、SUM（）
SELECT s.sid, s.sname,COUNT(sc.cid),SUM(sc.score)
FROM student s INNER JOIN sc ON s.sid = sc.sid
GROUP BY sc.sid
5.查詢「李」姓老師的數(shù)量
keyword : COUNT（）、LIKE
SELECT COUNT(1)
FROM teacher
WHERE tname LIKE ('李%');
6.查詢學(xué)過「張三」老師授課的同學(xué)的信息
keyword : 多表連接
SELECT s.*
FROM student s INNER JOIN sc ON s.sid = sc.sid
INNER JOIN course c ON sc.cid = c.cid
INNER JOIN teacher t ON c.tid = t.tid
WHERE t.tname = '張三'
7.查詢沒有學(xué)全所有課程的同學(xué)的信息
keyword : LEFT JOIN、HAVING 、COUNT()
SELECT s.*,COUNT(sc.cid)
FROM student s LEFT JOIN sc ON s.sid = sc.sid
GROUP BY sc.sid
HAVING COUNT(sc.cid) < (
SELECT COUNT(1) FROM course
)
8.查詢至少有一門課與學(xué)號為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
keyword : INNER JOIN、GROUP BY
SELECT s.sid,s.sname
FROM student s INNER JOIN sc on s.sid = sc.sid
WHERE sc.cid IN (
SELECT cid FROM sc WHERE sid = '01'
) AND s.sid != '01'
GROUP BY sid
9.查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
keyword : GROUP_CONCAT、ORDER BY
SELECT s.sid,sname,ssex,GROUP_CONCAT( cid ORDER BY cid )
FROM sc INNER JOIN student s ON sc.sid = s.sid
GROUP BY sc.sid
HAVING GROUP_CONCAT(cid ORDER BY cid) = (
SELECT GROUP_CONCAT(cid ORDER BY cid)
FROM sc
GROUP BY sid
HAVING sid = '01')
AND sid != '01'
-- 然而實際上去掉GROUP_CONCAT中的ORDER BY也一樣能得出正確答案
-- 去掉后執(zhí)行子查詢會發(fā)現(xiàn)04號的學(xué)生得到的課程是"03,01,02"
-- 不太清楚GROUP_CONCAT的拼接規(guī)則
10.查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
keyword : NOT IN、LEFT JOIN
SELECT sid,sname
FROM student
WHERE sid NOT IN (
SELECT sid
FROM sc LEFT JOIN course ON sc.cid=course.cid
LEFT JOIN teacher ON course.tid=teacher.tid
WHERE tname='張三')
11.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績
keyword : INNER JOIN、GROUP BY、HAVING
SELECT s.sid,s.sname,COUNT(cid),AVG(score)
FROM student s INNER JOIN sc ON s.sid = sc.sid
WHERE sc.score < 60
GROUP BY sc.sid
HAVING COUNT(cid) >= 2
12.檢索" 01 "課程分數(shù)小于 60，按分數(shù)降序排列的學(xué)生信息
keyword : ORDER BY
SELECT * FROM student s,sc
WHERE s.sid = sc.sid
AND sc.cid = '01'
AND sc.score < 60
ORDER BY sc.score DESC
13.按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
keyword : GROUP BY、ORDER BY
SELECT s.sid,s.sname,cid,score,avg_score
FROM student s INNER JOIN sc ON s.sid = sc.sid
LEFT JOIN (SELECT sid ,AVG(score) as avg_score FROM sc GROUP BY sid) t ON s.sid = t.sid
WHERE avg_score is NOT NULL
ORDER BY avg_score DESC
14.查詢各科成績最高分、最低分和平均分,以如下形式顯示：
以如下形式顯示：
課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列
keyword : GROUP BY、CASE WHEN
SELECT cid as '課程ID',cname as '課程name',COUNT(sid) as '選修人數(shù)',
MAX(score) as '最高分',MIN(score) as '最低分',AVG(score) as '平均分',
SUM(及格)/COUNT(sid) AS '及格率',
SUM(中等)/COUNT(sid) AS '中等率',
SUM(優(yōu)良)/COUNT(sid) AS '優(yōu)良率',
SUM(優(yōu)秀)/COUNT(sid) AS '優(yōu)秀率'
FROM (
SELECT sc.*,c.cname,
CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END as '及格',
CASE WHEN sc.score >= 70 AND score < 80 THEN 1 ELSE 0 END as '中等',
CASE WHEN sc.score >= 80 AND score < 90 THEN 1 ELSE 0 END as '優(yōu)良',
CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END as '優(yōu)秀'
FROM sc,course c
WHERE sc.cid = c.cid
) temp
GROUP BY temp.cid
15.1 按各科成績進行排序，并顯示排名,Score 重復(fù)時保留名次空缺
成績排序題參考如下：
keyword : 子查詢
-- mysql5.7及以下（下文有分析） --
SELECT *, (
SELECT COUNT(DISTINCT score) + 1
FROM sc b
WHERE b.cid = a.cid AND b.score > a.score) AS 名次
FROM sc a
ORDER BY cid, 名次
-----------------------------------------------
-- mysql8.0及以上可以使用窗口函數(shù) --
SELECT
*, DENSE_RANK() OVER(PARTITION BY cid ORDER BY score DESC) AS 'rank'
FROM
sc
分析：這應(yīng)該是最直觀的排名方法，對a表中每條記錄，都會去b表查詢比改記錄分數(shù)高的數(shù)量，并且對score排重，保留名次空缺
15.2 按各科成績進行行排序，并顯示排名， Score 重復(fù)時合并名次
keyword : 對LEFT JOIN以及 GROUP BY需要有一定的理解
先上最終答案
-- mysql5.7及以下（下文有分析） --
SELECT a.*,COUNT(b.score)+1 AS 'rank'
FROM sc AS a LEFT JOIN sc AS b ON a.cid = b.cid AND a.score < b.score
GROUP BY a.sid,a.cid
ORDER BY a.cid,COUNT(b.score)
-----------------------------------------------
-- mysql8.0及以上可以使用窗口函數(shù) --
SELECT
*, RANK() OVER(PARTITION BY cid ORDER BY score DESC) AS 'rank'
FROM
sc
接下來是步驟分解，為了方便理解，我們建一張簡易版的test表
- test表自交，得到cid相等，并且a表中score小于b表score的結(jié)果集
SELECT *
FROM test AS a LEFT JOIN test AS b ON a.cid = b.cid AND a.score < b.score
2.先將結(jié)果集排序一下，尋找排名的原理
SELECT *
FROM test AS a LEFT JOIN test AS b ON a.cid = b.cid AND a.score < b.score
ORDER BY a.cid,a.sid
分析：
我們直觀上看a的語文90分是最高，左邊是null。b的語文85分是第二，左邊有a-語文。c的語文60分是第三，左邊有a-語文，b-語文。結(jié)合sql語句中的“test AS a LEFT JOIN test AS b ON a.cid = b.cid AND a.score < b.score”。因此test表自交并且a.score < b.score的意思就是每條a記錄對應(yīng)的就是分數(shù)比它高的記錄。
我們列出來看看
a 語文 90 (對應(yīng)為null，說明沒有比它高的)
b 語文 85 a 語文 90 (對應(yīng)有a-語文，說明有a-語文比它高)
c 語文 60 a 語文 90 (對應(yīng)有a-語文，b-語文，說明有a-語文，b-語文比它高)
c 語文 60 b 語文 85
3.從步驟二得到規(guī)律之后就應(yīng)該分組了，只要group by就能獲取到對應(yīng)的COUNT(b.score)作為排名，注意group by的字段選取，我們要通過sid和cid才能獲取每個人每個科目的成績，最終答案如下
SELECT a.*,COUNT(b.score)+1 AS 'rank'
FROM test AS a LEFT JOIN test AS b ON a.cid = b.cid AND a.score < b.score
GROUP BY a.sid,a.cid /*不懂的話可以拿步驟2的數(shù)據(jù)group by a.sid,a.cid一次*/
ORDER BY a.cid,COUNT(b.score)
16.1 查詢學(xué)生的總成績，并進行排名，總分重復(fù)時保留名次空缺
keyword : 用戶變量
SELECT t1.*,
@curRank := IF (@perScore = sum_score,@curRank, @curRank+1) as 'rank',
@perScore := sum_score
FROM
(SELECT sid,SUM(score) AS sum_score FROM sc GROUP BY sid) AS t1 ,
(SELECT @curRank:=0,@perScore := NULL) AS t2
ORDER BY sum_score DESC
分析：整體還是比較好理解的，創(chuàng)建兩個用戶變量@curRank （當前排名）和@perScore（前一個學(xué)生的總分），在遍歷時比較當前的總分和前一個的總分是否相等，相等的話@curRank 不變，否則@curRank +1，每次遍歷都需要更新一次@perScore
16.2 查詢學(xué)生的總成績，并進行排名，總分重復(fù)時不保留名次空缺
keyword : 用戶變量
SELECT
t1.*, @curRank := IF(sum_score=@preScore, @curRank, @totalRank) AS 'rank',
@preScore := sum_score,
@totalRank := @totalRank+1
FROM
(SELECT sid,SUM(score) AS sum_score FROM sc GROUP BY sid) AS t1,
(SELECT @curRank:=1, @totalRank:=1, @preScore:=NULL) t2
ORDER BY sum_score DESC
分析：和上一題基本一致，再加多了一個用戶變量@totalRank，每次遍歷都+1，然后當前總分和上一個學(xué)生總分相同時，排名是@curRank不變，否則
更新@curRank為@totalRank
- 統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
keyword : CASE WHEN
SELECT
sc.cid AS 課程編號,
cname AS 課程名稱,
SUM( CASE WHEN score >= 0 AND score <= 60 THEN 1 ELSE 0 END ) AS '[60-0]',
SUM( CASE WHEN score >= 0 AND score <= 60 THEN 1 ELSE 0 END ) / COUNT( sid ) AS '[60-0]百分比',
SUM( CASE WHEN score >= 60 AND score <= 70 THEN 1 ELSE 0 END ) AS '[70-60]',
SUM( CASE WHEN score >= 60 AND score <= 70 THEN 1 ELSE 0 END ) / COUNT( sid ) AS '[70-60]百分比',
SUM( CASE WHEN score >= 70 AND score <= 85 THEN 1 ELSE 0 END ) AS '[85-70]',
SUM( CASE WHEN score >= 70 AND score <= 85 THEN 1 ELSE 0 END ) / COUNT( sid ) AS '[85-70]百分比',
SUM( CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END ) AS '[100-85]',
SUM( CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END ) / COUNT( sid ) AS '[100-85]百分比'
FROM
sc JOIN course ON sc.cid = course.cid
GROUP BY sc.cid,cname
- 查詢各科成績前三名的記錄
keyword :HAVING
SELECT *, (
SELECT COUNT(score) + 1
FROM sc b
WHERE b.cid = a.cid AND b.score > a.score) AS ranking
FROM sc a
HAVING ranking <= 3
ORDER BY cid, ranking
- 查詢每門課程被選修的學(xué)生數(shù)
keyword : GROUP BY
SELECT cid,COUNT(sid) AS num
FROM sc
GROUP BY cid
- 查詢出只選修兩門課程的學(xué)生學(xué)號和姓名
keyword : INNER JOIN、HAVING
SELECT sc.sid,sname
FROM sc INNER JOIN student ON sc.sid = student.sid
GROUP BY sc.sid
HAVING COUNT(cid) = 2
- 查詢男生、女生人數(shù)
keyword : GROUP BY
SELECT ssex,COUNT(sid) as num
FROM student
GROUP BY ssex
- 查詢名字中含有「風(fēng)」字的學(xué)生信息
keyword : LIKE
SELECT *
FROM student
WHERE sname LIKE '%風(fēng)%'
- 查詢同名同性學(xué)生名單，并統(tǒng)計同名人數(shù)
keyword : LEFT JOIN、GROUP BY
SELECT a.sname,a.ssex,COUNT(1) AS 人數(shù)
FROM student a LEFT JOIN student b ON a.sname=b.sname
WHERE a.ssex=b.ssex AND a.sid!=b.sid
GROUP BY a.sname,a.ssex
- 查詢 1990 年出生的學(xué)生名單
keyword : 時間函數(shù)
SELECT *
FROM student
WHERE YEAR (sage) = '1990'
- 查詢每門課程的平均成績，結(jié)果按平均成績降序排列，平均成績相同時，按課程編號升序排列
keyword : ORDER BY
SELECT cid,AVG(score) as avgScore
FROM sc
GROUP BY cid
ORDER BY avgScore DESC,cid ASC
- 查詢平均成績大于等于 85 的所有學(xué)生的學(xué)號、姓名和平均成績
keyword : AVG（）、HAVING
SELECT s.sid,sname,AVG(score) as avgScore
FROM sc,student s
WHERE sc.sid = s.sid
GROUP BY sid
HAVING avgScore > 85
- 查詢課程名稱為「數(shù)學(xué)」，且分數(shù)低于 60 的學(xué)生姓名和分數(shù)
keyword : INNER JOIN
SELECT s.sname,sc.score
FROM student AS s INNER JOIN sc ON s.sid = sc.sid
WHERE sc.score < 60
AND sc.cid IN (SELECT cid FROM course WHERE cname = '數(shù)學(xué)')
- 查詢所有學(xué)生的課程及分數(shù)情況（存在學(xué)生沒成績，沒選課的情況）
keyword : INNER JOIN
SELECT sname,cid,score
FROM sc INNER JOIN student AS s ON sc.sid = s.sid
- 查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分數(shù)
keyword : INNER JOIN
SELECT sname,cid,score
FROM sc INNER JOIN student AS s ON sc.sid = s.sid
WHERE score > 70
- 查詢不及格的課程
keyword : DISTINCT
SELECT DISTINCT sc.cid
FROM sc INNER JOIN student AS s ON sc.sid = s.sid
WHERE score < 60
31.查詢課程編號為 01 且課程成績在 80 分以上的學(xué)生的學(xué)號和姓名
keyword : INNER JOIN
SELECT s.sid,sname
FROM sc INNER JOIN student AS s ON sc.sid = s.sid
WHERE sc.cid = '01' AND score >= 80
32.求每門課程的學(xué)生人數(shù)
keyword :GROUP BY、COUNT()
SELECT cid,COUNT(sid) AS num
FROM sc
GROUP BY cid
33.成績不重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
keyword :INNER JOIN、MAX()
SELECT *,MAX(score)
FROM student s INNER JOIN sc ON s.sid =sc.sid
WHERE sc.cid IN (
SELECT cid
FROM course c,teacher t
WHERE c.tid = t.tid AND t.tname = '張三'
)
34.成績重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
keyword :多重JOIN、MAX()
SELECT student.*, sc.cid, score
FROM student
INNER JOIN sc ON student.sid = sc.sid
LEFT JOIN course ON sc.cid = course.cid
LEFT JOIN teacher ON course.tid = teacher.tid
WHERE tname = '張三'
AND score = (
SELECT MAX(score)
FROM sc
INNER JOIN course ON sc.cid = course.cid
LEFT JOIN teacher ON course.tid = teacher.tid
WHERE tname = '張三'
)
35.查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績
keyword :DISTINCT
SELECT DISTINCT a.*
FROM sc AS a INNER JOIN sc AS b
WHERE a.score = b.score AND a.cid != b.cid
- 查詢每門成績最好的前兩名
keyword :窗口函數(shù)
SELECT *
FROM
( SELECT *, RANK ( ) OVER ( PARTITION BY cid ORDER BY score DESC ) AS ranking FROM sc ) t
WHERE ranking <= 2
37.統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過 5 人的課程才統(tǒng)計）
keyword :GROUP BY、HAVING
SELECT cid,COUNT(sid)
FROM sc
GROUP BY cid
HAVING COUNT(sid) > 5
38.檢索至少選修兩門課程的學(xué)生學(xué)號
*keyword :GROUP BY、HAVING *
SELECT sid,COUNT( cid )
FROM sc
GROUP BY sid
HAVING COUNT( cid ) >= 2
39.查詢選修了全部課程的學(xué)生信息
keyword:INNER JOIN
SELECT s.*
FROM sc INNER JOIN student AS s ON sc.sid = s.sid
WHERE cid = ( SELECT COUNT( 1 ) FROM course );
40.查詢各學(xué)生的年齡，只按年份來算
keyword:YEAR()
SELECT sname,YEAR(NOW()) - YEAR(sage) AS 年齡
FROM student
- 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
keyword:CASE WHEN、YEAR()
SELECT sname,
CASE WHEN ( DATE_FORMAT( NOW( ), '%m-%d' ) - DATE_FORMAT( sage, '%m-%d' ) ) < 0
THEN YEAR ( NOW( ) ) - YEAR ( sage ) - 1
ELSE YEAR ( NOW( ) ) - YEAR ( sage )
END AS 年齡
FROM student
42.查詢本周過生日的學(xué)生
keyword:WEEK()
SELECT sname
FROM student
WHERE WEEK(sage) = WEEK(NOW())
43.查詢下周過生日的學(xué)生
keyword:WEEK()
SELECT sname
FROM student
WHERE WEEK(sage) = WEEK(NOW()) + 1
44.查詢本月過生日的學(xué)生
keyword:MONTH ()
SELECT sname,sage
FROM student
WHERE MONTH (sage) = MONTH (NOW())
45.查詢下月過生日的學(xué)生
keyword:MONTH ()
SELECT sname,sage
FROM student
WHERE MONTH (sage) = MONTH (NOW()) + 1
結(jié)語：終于做完了（有一些題目是有小題的，總體還是有50題的），如果有錯誤或者更好的方法，歡迎指出
