Mysql 經(jīng)典練習題
我使用的Mysql版本是5.7.27-log变抽，答案可能會因版本會有少許出入。
一、數(shù)據(jù)源準備及說明：
1.1、數(shù)據(jù)字段說明:
1.學生表 Student(SId,Sname,Sage,Ssex)
SId ：學生編號
Sname：學生姓名
Sage ：出生年月
Ssex：學生性別
2.課程表 Course(CId,Cname,TId)
CId ：課程編號
Cname ：課程名稱
TId ：教師編號
3.教師表 Teacher(TId,Tname)
TId ：教師編號
Tname ：教師姓名
4.成績表 SC(SId,CId,score)
SId ：學生編號
CId ：課程編號
score： 分數(shù)
1.2些椒、將數(shù)據(jù)導入數(shù)據(jù)庫：
導入數(shù)據(jù)方法：
1、將以下 mysql 語句掸刊，完整復制到 workbench 語句窗口（或者是mysql 的黑窗口或者Navicat窗口也可以）
2免糕、然后運行即可導入，不需要另外創(chuàng)建表忧侧，下面表的操作一樣石窑。
注釋：這些語句第一條是創(chuàng)建表（create table），后面都是插入數(shù)據(jù)到表中（insert into table ）：
學生表 Student：
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' , '孫風' , '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('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '趙六' , '2017-01-01' , '女');
insert into Student values('13' , '孫七' , '2018-01-01' , '女');
科目表 Course:
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學' , '01');
insert into Course values('03' , '英語' , '03');
教師表 Teacher:
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成績表 SC:
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);
二蚓炬、練習題目：
1松逊、查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數(shù)
分析：題意就是想查詢所有學生中01課程分數(shù)比02課程分數(shù)高的學生有哪些？
思路：分別查出01和02的sid和分數(shù)肯夏，然后與student表內(nèi)連接查詢學生姓名经宏，輸出樣式：Sid,Sname,score01,score02犀暑，然后用where條件a.score > b.score過濾一下即可實現(xiàn)查詢結(jié)果。
SELECT a.sid
,st.Sname
,a.score
,b.score
FROM(SELECT SId
,score
FROM sc
WHERE CId = 01) AS a # 查詢01課程的分數(shù)
INNER JOIN (SELECT SId
,score
FROM sc
WHERE CId = 02) AS b ON a.sid = b.sid # 查詢02課程的分數(shù)
INNER JOIN student AS st ON st.SId = a.SId # 2個內(nèi)連接查交集
WHERE a.score > b.score;
1.1烁兰、查詢同時學過" 01 "課程和" 02 "課程的學生信息
思路：使用子查詢耐亏，先分別查詢出01課程和02課程的sid，然后將這兩個表內(nèi)連接沪斟，即是同時學01和02課程的sid广辰，此時再與studen表中的sid匹配查到學生詳細信息。
解法1：標量子查詢
SELECT student.*
FROM student
WHERE student.SId IN (SELECT a.SId
FROM (SELECT sid
FROM sc
WHERE CId = "01") AS a
INNER JOIN (SELECT *
FROM sc
WHERE CId = "02") AS b ON a.SId = b.SId);
但這個查詢結(jié)果僅僅只能看到學生信息主之，看不到學生各個課程的分數(shù)择吊，輸出樣式不是最優(yōu)，所以我們希望輸出的樣式是這樣的：sid,sname,score01,score02,那么就可以如題1一樣杀餐，使用多表連接對三個表做內(nèi)連接干发，然后直接取需要的字段即可：
SELECT a.sid
,st.Sname
,a.score
,b.score
FROM(SELECT SId
,score
FROM sc
WHERE CId = 01) AS a
INNER JOIN (SELECT SId
,score
FROM sc
WHERE CId = 02) AS b ON a.sid = b.sid
INNER JOIN student AS st ON st.SId = a.SId;
1.2、查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
分析：所謂"情況"史翘，無非就是查這樣一些人：必須學過01課課程枉长，不一定學過02課程的學生是哪些，那既然知道了學生id琼讽，那很有可能想知道這些學生的信息必峰，所以我們希望查詢結(jié)果以這樣的格式進行輸出：sid,sname,score01,score02.
思路：多表連接的時候使用左連接即可，分別查詢出學過01和02課程的sid和分數(shù)钻蹬，然后將兩個查詢結(jié)果進行左連接吼蚁，確保01課程的所有人都在，然后與student表左連接问欠，這樣就得到了想要查詢結(jié)果了肝匆。
SELECT a.sid
,st.Sname
,a.score
,b.score
FROM(SELECT SId
,score
FROM sc
WHERE CId = 01) AS a
LEFT JOIN (SELECT SId
,score
FROM sc
WHERE CId = 02) AS b ON a.sid = b.sid
LEFT JOIN student AS st ON st.SId = a.SId;
1.3、查詢不存在" 01 "課程但存在" 02 "課程的情況
思路：過濾掉cid = 01 課程的所有sid顺献，再選取cid = 02的學生
解法1：使用子查詢過濾掉學過01課程的學生旗国，然后再選出那些學了02課程的學生，用AND將兩個約束條件進行連接就可以得到最終的結(jié)果集了注整。
SELECT a.sid
,b.sname
,a.cid
,a.score
FROM(SELECT *
FROM sc
WHERE SId NOT IN (SELECT SId
FROM sc
WHERE CId='01') AND CId='02') AS a #查詢滿足要求的sid能曾，cid，score
INNER JOIN student AS b ON a.sid = b.sid; # 查詢對應的學生信息
解法2：雖然結(jié)果一樣肿轨，但寫法感覺別扭寿冕，再回頭看時自己都忘了咋寫出來的了
SELECT b.*
FROM(SELECT *
FROM sc
GROUP BY sid
HAVING cid != "01") AS a
INNER JOIN(SELECT *
FROM sc
WHERE cid = "02") AS b ON a.sid = b.sid
GROUP BY b.sid;
2、查詢平均成績大于等于 60 分的同學的學生編號椒袍、學生姓名驼唱、平均成績
思路：內(nèi)連接student表和sc表，然后按照sid分組計算平均成績且過濾掉那些平均分小于60分的人
解法一：使用子查詢槐沼，先查詢出平均成績大于等于60的學生的sid和平均分曙蒸，然后與student表內(nèi)連接獲取學生姓名字段捌治。
SELECT a.sid
,b.sname
,a.avg_score
FROM(SELECT sid
,AVG(score) AS avg_score
FROM sc
GROUP BY sid
HAVING AVG(score) >= 60) AS a
INNER JOIN student AS b ON a.sid = b.sid岗钩；
解法二：先對表sc和student內(nèi)連接纽窟，然后按照sid分組計算后取值，這里需要注意的點是student和sc表是一對多的關系兼吓，進行內(nèi)連接之后臂港，新表里student的記錄是自動補全的，如下圖：
SELECT s.SId
,st.sname
,AVG(score)
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
GROUP BY s.SId,st.sname
HAVING AVG(score) >= 60;
3视搏、查詢在 SC 表存在成績的學生信息
思路：使用子查詢审孽，先在sc表按sid去重看看sc表都有哪些sid，然后在student表中查詢對應sid的學生信息即可
SELECT student.*
FROM student
WHERE sid IN (SELECT DISTINCT sid
FROM sc)
GROUP BY sid; # 以防萬一再在student表中按sid group by去重一下浑娜。
4佑力、查詢所有同學的學生編號、學生姓名筋遭、選課總數(shù)打颤、所有課程的總成績(沒成績的顯示為 0 )
思路：student和sc表左連接之后，按照sid漓滔、sname分組統(tǒng)計取需要字段即可
SELECT st.SId
,st.Sname
,COUNT(DISTINCT s.CId)
,SUM(CASE WHEN s.score IS NULL THEN 0 ELSE s.score END) # 這里用到的的case when是需要特別注意的技巧
FROM student AS st
LEFT JOIN sc AS s ON st.SId = s.SId
GROUP BY st.SId,st.Sname
ORDER BY st.SId
注意：student表和sc表進行做連接后的結(jié)果集如下：
4.1 编饺、查有成績的學生信息
思路：先看sc表中有成績的sid是哪些，然后就可以根據(jù)sid查看學生信息了
解法一：使用子查詢 IN
SELECT *
FROM student AS a
WHERE a.sid IN (SELECT DISTINCT sid
FROM sc
GROUP BY sid);
解法二：如上題响驴，對student表和sc表左內(nèi)連接之后透且，按照sid進行分組去重，得到學生信息
SELECT st.*
FROM student AS st
INNER JOIN sc AS s ON st.sid = s.sid
GROUP BY s.sid
ORDER BY s.sid
5豁鲤、查詢「李」姓老師的數(shù)量
思路：通配符和聚合函數(shù)的使用秽誊，這題就很簡單，沒什么說的
SELECT COUNT(*)
FROM teacher
WHERE Tname LIKE '李%'
6琳骡、查詢學過「張三」老師授課的同學的信息
思路：多表連接或者子查詢
解法一：使用子查詢書寫
SELECT *
FROM student
WHERE sid IN (SELECT sid
FROM sc
WHERE cid = (SELECT cid
FROM course
WHERE tid = (SELECT tid
FROM teacher
WHERE tname = "張三")));
解法二：直接內(nèi)連接所有需要用到的表
SELECT st.SId
,st.Sname
,st.sage
,st.ssex
,s.cid
,s.score
,c.cname
,t.tid
,t.tname # 字段選取可根據(jù)需要去留
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '張三';
注意點：所有表內(nèi)連接之后的結(jié)果集：可以看到是以數(shù)據(jù)量最多的表格為準锅论，就知道有幾行數(shù)據(jù)了
6.1、查詢沒學過張三老師課程的學生的學號和姓名
思路：所有學過張三老師課程的學生進行取反
SELECT *
FROM student
WHERE SId NOT IN(SELECT st.SId
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '張三');
7日熬、查詢沒有學全所有課程的同學的信息
思路：將student表和sc表進行左連接棍厌，這樣不會漏掉一門課也沒有學的同學，然后按照sid分組竖席，統(tǒng)計分組后的同學的課程數(shù)量耘纱，如果小于總的課程數(shù)量，那么就是沒學全
SELECT st.SId
,st.Sname
,COUNT(DISTINCT s.CId)
FROM student AS st
LEFT JOIN sc AS s ON st.SId = s.SId
GROUP BY st.SId
HAVING COUNT(DISTINCT s.CId) < (SELECT COUNT(DISTINCT CId)
FROM course);
8毕荐、查詢至少有一門課與學號為" 01 "的同學所學課程相同的同學的信息
思路：子查詢嵌套束析，先找01號學生的課程，再找包含在這里面的其他sid憎亚，然后查信息员寇，需要注意的是最后還要排除01號這個sid弄慰。
解法一：使用子查詢
SELECT SId
,Sname
FROM student
WHERE SId IN(SELECT SId
FROM sc
WHERE cid IN(SELECT cid
FROM sc
WHERE SId = 01)) AND SId <> 01;
解法二：外層student表和子查詢結(jié)果表內(nèi)連接，提升查詢效率蝶锋，具體如下：
注釋：數(shù)據(jù)較多時陆爽，inner join查詢效率比子查詢高
select a.sid
,a.sname
FROM student AS a
INNER JOIN(SELECT distinct sid
FROM sc
WHERE cid in (SELECT cid
FROM sc
WHERE sid = "01") AND sid <> "01") AS b ON a.sid =b.sid;
解法三：使用多表連接查詢的思路也是不錯的，直接右連接student表和sc表（此題目中其實用什么類型的連接結(jié)果都是一樣的）扳缕，因為表關系是一對多慌闭，所以直接使用過濾條件去重查詢，然后再去掉01號同學就可以了
SELECT distinct b.*
FROM sc a
LEFT JOIN student b
ON a.sid=b.sid
WHERE cid IN (SELECT cid
FROM sc
WHERE sid='01') AND a.sid != "01";
9躯舔、查詢和" 01 "號同學學習的課程 完全相同的其他同學的信息驴剔。
思路：先查詢出與01號同學所學課程完全不同的學生的sid，然后對其取反粥庄，那么得到的就是與01號同學所學課程至少有一門課程相同的學生丧失，如果此時他們所學的課程數(shù)量又相等，那么該學生必然與01號同學所學課程是完全相同的
SELECT student.*
FROM student
WHERE SId IN(SELECT SId
FROM sc
WHERE SId <> 01 # 先過濾掉01號同學本身
GROUP BY SId
HAVING COUNT(DISTINCT CId) = (SELECT COUNT(DISTINCT CId) #計算01號同學所學課程數(shù)量
FROM sc
WHERE SId = 01) AND SId NOT IN(SELECT SId# 再取反排除這些與01號同學所學課程完全不同的學生
FROM sc # 對下級查詢結(jié)果取反惜互，得到哪些跟01號同學所學課程完全不同的學生編號
WHERE CId NOT IN(SELECT CId
FROM sc
WHERE SId = '01');#查01號同學學了哪幾門課程的編號
-- 這題還是蠻有意思的布讹，思路不錯
10、查詢沒學過"張三"老師講授的任一門課程的學生姓名
思路：查詢出“張三”老師所教的課程id有哪些载佳，然后看誰選了他教的課程炒事，然后對其取反即可，就是那些一門都沒有學習張三老師課程的學生信息蔫慧。
SELECT *
FROM student
WHERE SId NOT IN(SELECT st.SId
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '張三');
思考：假設使用子查詢挠乳，先查詢出張三老師所教課程的全部編號，然后在sc表中對這個子查詢結(jié)果進行取反姑躲，然后根據(jù)sc表中的sid是否就可以查到學生的信息了呢睡扬？
答：不可以，因為在sc表中進行排除的時候黍析，比如01號同學同時學些了01/02/03課程卖怜，使用where條件過濾掉02，實際上01號同學還是被選出來了阐枣。那用先分組再having過濾呢马靠？（我的答案是不可以）
10.1、查詢學過張三老師所教的所有課程的同學的學號和姓名
思路：只要明白多表連接之后的“表”是什么樣子的就很容易寫出答案來了
SELECT st.SId
,st.Sname
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '張三';
11蔼两、查詢兩門及以上不及格課程的同學的學號甩鳄，姓名及其平均成績
思路：先查出成績小于60的所有記錄，然后按sid分組额划，統(tǒng)計課程數(shù)大于等于2的sid是哪些妙啃，然后內(nèi)連接student和sc ，然后按sid俊戳，sname分組揖赴，計算分組后某個sid的平均分
SELECT st.sid
,st.sname
,AVG(s.score)
FROM student AS st
INNER JOIN sc AS s ON st.sid = s.sid
WHERE st.SId IN(SELECT SId
FROM sc
WHERE score < 60
GROUP BY SId
HAVING COUNT(DISTINCT CId) >= 2)
GROUP BY st.sid,st.sname;
12馆匿、檢索" 01 "課程分數(shù)小于 60，按分數(shù)降序排列的學生信息
思路：直接內(nèi)連接student和sc表燥滑，然后where過濾條件渐北，按分數(shù)降序排列（子查詢也可以實現(xiàn)，這里就不寫具體代碼了）
SELECT st.*
,s.CId
,s.score
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
WHERE s.CId = 01 AND s.score <60
ORDER BY s.score DESC;
13突倍、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
思路：可以使用子查詢腔稀，也可以使用case when
解法一：使用子查詢盆昙，先查詢出學生平均分羽历，然后與sc表內(nèi)連接，這樣連接之后的表每一條數(shù)據(jù)后都有了平均分淡喜，然后排序就好了秕磷。
SELECT s.SId
,s.score
,a.avg_score
FROM sc AS s
INNER JOIN (SELECT SId
,AVG(score) AS avg_score
FROM sc
GROUP BY SId) AS a ON s.SId = a.SId
ORDER BY a.avg_score DESC;
值得注意的是：這樣寫結(jié)果是出來了，但顯示方式不是很好(這里其最重要的作用是驗證多表聯(lián)結(jié)時炼团，出現(xiàn)多對一的情況的時候澎嚣，“一”會在后面自動補全)
推薦使用下面這種寫法：展示樣式為：sid-語文-數(shù)學-英語-平均分，更符合實際業(yè)務場景
SELECT SId
,MAX(CASE WHEN CId = 01 THEN score ELSE NULL END) AS 語文
,MAX(CASE WHEN CId = 02 THEN score ELSE NULL END) AS 數(shù)學
,MAX(CASE WHEN CId = 03 THEN score ELSE NULL END) AS 英語
,AVG(score) AS 平均分
FROM sc
GROUP BY SId
ORDER BY 平均分 DESC;
這里的case when用法不錯瘟芝，因為只有一個值易桃，用max、avg等其實是一樣的結(jié)果锌俱。
14晤郑、查詢各科成績最高分、最低分和平均分： 以如下形式顯示：
- 課程 ID贸宏，課程 name造寝，最高分，最低分吭练，平均分诫龙，及格率，中等率鲫咽，優(yōu)良率签赃，優(yōu)秀率。
- 及格為>=60分尸，中等為：70-80锦聊，優(yōu)良為：80-90，優(yōu)秀為：>=90
- 要求輸出課程號和選修人數(shù)寓落，查詢結(jié)果按人數(shù)降序排列括丁，若人數(shù)相同，按課程號升序排列伶选。
思路：實際就是按照cid進行分組統(tǒng)計史飞，查看每門課程的最大尖昏、最小、平均值构资，及相應的XX率抽诉。從題意得知需要用到的表為sc表和course表，所以直接將這兩個表做一個內(nèi)連接吐绵，然后進行取字段求值迹淌。
這里特別要注意的是case when的用法，當用sum進行人數(shù)統(tǒng)計的時候己单，需要分門別類的進行數(shù)量統(tǒng)計唉窃，所以就想到使用case when來進行分類匯總。
SELECT s.cid
,c.cname
,MAX(s.score)
,MIN(s.score)
,AVG(s.score)
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 60 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid) * 100,2),"%") AS 及格率
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 70 AND s.score < 80 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid),2) * 100,"%") AS 中等率
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 80 AND s.score < 90 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid),2) * 100,"%") AS 優(yōu)良率
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 90 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid) * 100,2),"%") AS 優(yōu)秀率
FROM sc AS s
INNER JOIN course AS c ON s.cid = c.cid
GROUP BY s.cid;
注意：還需要注意的是concat函數(shù)的用法和truncate的用法纹笼，與truncate（不進行四舍五入）用法類似的函數(shù)round（四舍五入）的區(qū)別纹份。
15、按各科成績進行排序廷痘，并顯示排名蔓涧， Score 重復時名次空缺 （1224格式）
思路：用窗口函數(shù)，需Mysql8.0以上版本
SELECT CId
,RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS rank_num
FROM sc;
15.1 按各科成績進行排序笋额，并顯示排名元暴， Score 重復時合并名次（假設是1223類型的排序）
分析：窗口函數(shù)，需Mysql8.0以上版本
SELECT CId
,score
,DENSE_RANK() over(PARTITION BY CId ORDER BY score DESC) AS rank
FROM sc兄猩；
16茉盏、查詢學生的總成績，并進行排名厦滤，總分重復時名次空缺 (假設按照1224)
解法一：窗口函數(shù),需Mysql8.0以上版本
SELECT SId
,total_score
,RANK() OVER (ORDER BY total_score DESC) AS rank_num
FROM(SELECT SId
,SUM(score) AS total_score
FROM sc
GROUP BY SId) AS t;
解法二：定義變量：
SELECT SId
,sum_score
,@rank := IF(@score1 = sum_score,@rank,@rank + 1) AS rank_num
,@score1 := sum_score AS 總成績 # 保存上一次的分數(shù)
FROM (SELECT SId
,SUM(score) AS sum_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS a
JOIN (SELECT @rank := '',@score1 := '') AS b;
也可以用case when的語法：
將：
@rank := IF(@score1 = sum_score,@rank,@rank + 1) AS rank_num
替換成：
CASE WHEN @score1 = sum_score THEN @rank := @rank ELSE @rank := @rank + 1 END AS 名次涩维。
跟用IF邏輯是一樣的夺克，都是做判斷,具體如下：
SELECT SId
,sum_score
,CASE WHEN @score1 = sum_score THEN @rank := @rank ELSE @rank := @rank + 1 END AS 名次
,@score1 := sum_score AS 總成績
FROM (SELECT SId
,SUM(score) AS sum_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS a
JOIN (SELECT @rank := '',@score1 := '') AS b;
總結(jié)：
需要定義兩個空變量拳亿，@rank變量用于排名漓库，@score1變量用于與總成績進行比較，從而得到排名趟咆，需要特別注意的是“”@score1 := sum_score AS 總成績“”必須要寫添瓷，因為這一步是將比較后的值賦給@score1，以便后續(xù)的比較排名值纱。
16.1鳞贷、 查詢學生的總成績，并進行排名虐唠，總分重復時名次不空缺（1223）
解法一：窗口函數(shù)：需Mysql8.0以上版本
SELECT SId
,total_score
,DENSE_RANK() OVER (ORDER BY total_score DESC) AS rank_num
FROM(SELECT SId
,SUM(score) AS total_score
FROM sc
GROUP BY SId) AS t;
解法二：定義變量：所謂空缺不空缺到底啥意思搀愧？這里我分兩種情況寫：
# 1.假設直接1234排序：結(jié)果同ROW_NUMBER()
SET @rank = 0;
SELECT SId
,total_score
,@rank := @rank + 1 AS rank_num
FROM (SELECT SId
,SUM(score) AS total_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS t；
# 2.假設是1223的順序：
SELECT SId
,sum_score
,CASE WHEN @score1 = sum_score THEN @rank := @rank ELSE @rank := @rank + 1 END AS 名次
,@score1 := sum_score AS 總成績
FROM (SELECT SId
,SUM(score) AS sum_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS a
JOIN (SELECT @rank := '',@score1 := '') AS b;
17、統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號咱筛，課程名稱搓幌，[100-85]，[85-70]迅箩，[70-60]溉愁，[60-0] 及所占百分比
SELECT s.cid
,c.cname
,SUM(CASE WHEN s.score <= 100 AND s.score > 85 THEN 1 ELSE 0 END) AS "[100,85)"
,SUM(CASE WHEN s.score <= 85 AND s.score > 70 THEN 1 ELSE 0 END) AS "[85,70)"
,SUM(CASE WHEN s.score <= 70 AND s.score > 60 THEN 1 ELSE 0 END) AS "[70,60)"
,SUM(CASE WHEN s.score <= 60 THEN 1 ELSE 0 END) AS "(0,60]"
FROM sc AS s
INNER JOIN course AS c ON s.cid = c.cid
GROUP BY s.cid,c.cname;
18、查詢各科成績前三名的記錄
解法一：窗口函數(shù)饲趋，需Mysql8.0以上版本
SELECT *
FROM(SELECT CId
,score
,ROW_NUMBER() OVER (PARTITION BY CId ORDER BY score DESC) AS rank_num
FROM sc) AS t
WHERE rank_num <= 3;
19拐揭、查詢每門課程被選修的學生數(shù)
SELECT s.CId
,c.Cname
,COUNT(DISTINCT s.SId)
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
GROUP BY s.CId,c.Cname;
20、查詢出只選修兩門課程的學生學號和姓名
SELECT s.SId
,st.Sname
,COUNT(DISTINCT s.CId) AS cnt
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
GROUP BY s.SId,st.Sname
HAVING cnt = 2
也可以使用子查詢奕塑，但性能可能較低
SELECT SId
,Sname
FROM student
WHERE SId IN (SELECT SId
FROM sc
GROUP BY SId
HAVING COUNT(DISTINCT CId) = 2);
21堂污、查詢男生、女生人數(shù)
SELECT Ssex
,COUNT(DISTINCT sid)
FROM student
GROUP BY Ssex;
用case when來實現(xiàn)的注意事項：
SELECT SUM(CASE WHEN Ssex = "男" THEN 1 else 0 END) AS '男生人數(shù)'
,SUM(CASE WHEN Ssex = "女" THEN 1 else 0 END) AS '女生人數(shù)'
FROM student;
需要注意的是如果用count爵川，那么上面的0要改成null才行敷鸦，因為null是不參與計算的，而0參與寝贡。
SELECT COUNT(CASE WHEN Ssex = "男" THEN 1 else NULL END) AS '男生人數(shù)'
,COUNT(CASE WHEN Ssex = "女" THEN 1 else NULL END) AS '女生人數(shù)'
FROM student;
# 如果將NULL修改為0的話，那么查詢出來的結(jié)果無論男女數(shù)量都是12值依，因為0也被計入count的計算中了
22圃泡、查詢名字中含有「風」字的學生信息
SELECT *
FROM student
WHERE Sname LIKE "%風%";
23、查詢同名同性學生名單愿险，并統(tǒng)計同名人數(shù)
解法一：按照姓名分組颇蜡，姓名形同的情況下按照性別分組統(tǒng)計人數(shù)，如果統(tǒng)計人數(shù)大于等于2辆亏，那說明這個人就是同名同性的
SELECT Sname
,Ssex
,COUNT(Sname)
FROM student
GROUP BY Sname,Ssex
HAVING COUNT(Sname) >= 2;
解法二：自連接风秤，過濾條件為性別相同，且sid不相同
SELECT st1.*
,COUNT(1) AS cons
FROM student AS st1
INNER JOIN student AS st2 ON st1.sname=st2.sname AND st1.ssex=st2.ssex AND st1.sid != st2.sid;
解法三：
select *
from student LEFT JOIN (select Sname
,Ssex,COUNT(*)同名人數(shù)
from Student
group by Sname,Ssex) as t1 on student.Sname =t1.Sname and student.Ssex=t1.Ssex
where t1.同名人數(shù)>1
24扮叨、查詢 1990 年出生的學生名單
SELECT *
FROM student
WHERE YEAR(Sage) = 1990;
25缤弦、查詢每門課程的平均成績，結(jié)果按平均成績降序排列彻磁，平均成績相同時碍沐，按課程編號升序排列
SELECT s.CId
,c.Cname
,AVG(score) AS avg_score
FROM sc AS s
INNER JOIN course AS c ON c.CId = s.CId
GROUP BY s.CId
,c.Cname
ORDER BY avg_score DESC,s.CId ASC;
26、查詢平均成績大于等于 85 的所有學生的學號衷蜓、姓名和平均成績
SELECT st.SId
,st.Sname
,AVG(s.score)
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
GROUP BY st.SId,st.Sname
HAVING AVG(s.score) >= 85;
27累提、查詢課程名稱為「數(shù)學」，且分數(shù)低于 60 的學生姓名和分數(shù)
SELECT st.SId
,st.Sname
,s.score
,c.Cname
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON s.CId = c.CId
WHERE c.Cname = "數(shù)學" AND s.score < 60;
28磁浇、查詢所有學生的課程及分數(shù)情況（存在學生沒成績斋陪，沒選課的情況）
# 按實際需求希望表頭如：sid sname 語文 數(shù)學 英語 進行展示
SELECT st.SId
,st.Sname
,MAX(CASE WHEN c.Cname = "語文" THEN s.score ELSE NULL END) AS "語文"
,MAX(CASE WHEN c.Cname = "數(shù)學" THEN s.score ELSE NULL END) AS "數(shù)學"
,MAX(CASE WHEN c.Cname = "英語" THEN s.score ELSE NULL END) AS "英語"
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
INNER JOIN student AS st ON s.SId = st.SId
GROUP BY st.SId
,st.Sname;
需要注意的是：本題如果只按sid，sname分組查詢，成績只能返回第一個无虚，所以必須用case when進行分類后逐一進行判斷然后返回最大值（當然這里用MIN其實也無所謂）
29鞍匾、查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分數(shù)
思路：題意應該是想查詢那些任何一門成績都在70分以上的人的情況
SELECT s.SId
,st.Sname
,c.Cname
,s.score
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
INNER JOIN student AS st ON s.SId = st.SId
WHERE s.score > 70;
30骑科、查詢不及格的課程并按課程編號從大到小排列
思路：就是查詢有哪些課程存在不及格的情況橡淑，是誰。
SELECT s.sid
,st.Sname
,s.cid
,c.cname
,s.score
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
INNER JOIN course AS c ON s.CId = c.CId
WHERE score < 60
ORDER BY s.CId DESC;
31咆爽、查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名
SELECT s.SId
,st.Sname
,s.CId
,c.Cname
,s.score
FROM sc as s
INNER JOIN student AS st ON s.SId = st.SId
INNER JOIN course AS c ON s.CId = c.CId
WHERE s.CId = "01" AND s.score > 80;
32梁棠、求每門課程的學生人數(shù)
SELECT s.CId
,c.Cname
,COUNT(DISTINCT SId)
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
GROUP BY s.CId;
33、成績不重復的情況下斗埂，查詢選修「張三」老師所授課程的學生中符糊，成績最高的學生信息及其成績
分析：成績不重復，排序后取第一個或者直接取MAX（score）就可以了
SELECT s.SId
,st.Sname
,s.score
,s.CId
,t.Tname
FROM sc AS s
INNER JOIN student as st ON s.SId = st.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = "張三"
ORDER BY s.score DESC
LIMIT 1;
或者
SELECT s.SId
,st.Sname
,MAX(s.score)
,s.CId
,t.Tname
FROM sc AS s
INNER JOIN student as st ON s.SId = st.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = "張三";
34呛凶、成績有重復的情況下男娄，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
分析：成績重復的情況下漾稀，那么第一名有可能是多個人模闲，這時候用limit限制就不行了，所以使用窗口函數(shù)中的RANK()是完美的解決辦法崭捍。
SELECT * #3.1.查看第一名的信息
FROM(SELECT *
,RANK() OVER (ORDER BY score desc) AS rank_num #2.對子查詢結(jié)果進行排名
FROM(SELECT st.SId #1.先把學張三老師課的人找出來
,st.Sname
,s.score
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '張三')AS x)AS y
WHERE rank_num = 1; #3.2. 篩選排名第一的
35尸折、查詢不同課程成績相同的學生的學生編號、課程編號殷蛇、學生成績
思路：意思是查詢哪些同學每門課程成績都一樣（這里是我的理解）实夹，所以先查詢所學課程總數(shù)大于1的學生id，然后與sc表做內(nèi)連接粒梦，再按sid和score的組合進行分組統(tǒng)計亮航，如果幾率條數(shù)只有一條，那么就意味著這位同學的不同科目的成績都是一樣的匀们。
SELECT *
FROM(SELECT a.sid
,a.score
,b.cnt
FROM sc AS a
INNER JOIN(SELECT SId #查詢課程數(shù)大于1的學生id
,COUNT(DISTINCT CId) AS cnt
FROM sc
GROUP BY SId
HAVING cnt > 1) AS b ON a.sid = b.sid
GROUP BY a.sid
,a.score) AS c
GROUP BY sid
HAVING COUNT(sid) = 1;
這里需要特別注意的是：group by 之后的字段的分組明細缴淋，必須所有字段值都一樣才會輸出一條記錄，否則就是多條記錄昼蛀。
36宴猾、查詢每門課程成績最好的前兩名
分析：前兩名，如果碰到成績一樣的情況叼旋，按照實際需求仇哆，應該前兩名包含不止2人才對，是名次排在第一和第二的都要包含進去才符合實際夫植，所以這里我用rank（）
# 使用窗口函數(shù)
SELECT *
FROM(SELECT CId
,score
,RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS rank_num
FROM sc) AS t
WHERE rank_num <= 2;
37讹剔、統(tǒng)計每門課程的學生選修人數(shù)（超過 5 人的課程才統(tǒng)計）,要求輸出課程號和選修人數(shù)油讯，查詢結(jié)果按照人數(shù)降序，若人數(shù)相同延欠，按照課程號升序
SELECT CId
,COUNT(DISTINCT SId) AS cnt
FROM sc
GROUP BY CId
HAVING cnt >= 5
ORDER BY cnt DESC
,CId ASC;
38陌兑、檢索至少選修兩門課程的學生學號
SELECT SId
,COUNT(DISTINCT CId) AS cnt
FROM sc
GROUP BY SId
HAVING cnt >= 2;
39、查詢選修了全部課程的學生信息
SELECT SId
,COUNT(DISTINCT CId) AS cnt
FROM sc
GROUP BY SId # 按照sid分組之后count課程數(shù)量
HAVING cnt = (SELECT COUNT(DISTINCT Cid)
FROM course);
40由捎、查詢各學生的年齡兔综，只按年份來算
解法一：用DATEDIFF函數(shù)，計算出生到現(xiàn)在的時間天數(shù)狞玛，然后除以365得到年份软驰，然后用FLOOR函數(shù)向下取整得到最終的年齡
SELECT student.*
,FLOOR(DATEDIFF(NOW(),Sage)/365) AS "年齡"
FROM student;
總結(jié)：
DATEDIFF() 函數(shù)返回兩個日期之間的天數(shù)。
FLOOR(X)根據(jù)官方文檔的提示,floor函數(shù)返回小于等于該值的最大整數(shù).
解法二：用YEAR()函數(shù)和NOW()函數(shù)進行相減
SELECT student.*
,YEAR(now())-YEAR(sage) AS age
FROM student;
41心肪、按照出生日期來算锭亏，當前月日 < 出生年月的月日則年齡減一
分析：比如01號同學，當前日期減去出生日期為30.9年硬鞍，這取30.
# 這里 timestampdiff 會用年月日去計算 年 的相隔時間慧瘤，
如果相差1.9年則為1年，所以實際上是已經(jīng)相減了的固该，正好用來計算生日
SELECT SId AS 學生編號
,Sname AS 學生姓名
,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) AS 學生年齡
FROM student;
總結(jié)：SELECT NOW(),CURDATE(),CURTIME()的區(qū)別：
42锅减、查詢本周過生日的學生
SELECT sid
,sname
,YEARWEEK(sage)
,YEARWEEK(NOW())
FROM student
WHERE YEARWEEK(sage) = YEARWEEK(NOW());
小結(jié)：
YEARWEEK(date, mode)
返回年份及第幾周（0到53），mode為可選參數(shù)蹬音，其中 中 0 （默認參數(shù)）表示從周天開始上煤，1表示周一開始，以此類推：
SELECT YEARWEEK("2017-06-15"); -> 201724
select WEEK('2019-07-11',1);
返回值是28
select YEARWEEK('2019-07-11',1);
返回值是201928
43著淆、查詢下周過生日的學生
解法一：用YEARWEEK()函數(shù)
SELECT sid
,sname
,YEARWEEK(sage)
,YEARWEEK(NOW())
FROM student
WHERE YEARWEEK(sage) = YEARWEEK(NOW()) + 1；
解法二：
SELECT sid
,sname
,EXTRACT(week FROM sage) as sweek
,EXTRACT(week FROM curdate()) as nweek
FROM student
WHERE EXTRACT(week FROM sage) = EXTRACT(week FROM curdate()) + 1;
小結(jié)：extract()函數(shù)的用法
EXTRACT() 函數(shù)用于返回日期/時間的單獨部分拴疤，比如年永部、月、日呐矾、周苔埋、小時、分鐘等等蜒犯。
44组橄、查詢本月過生日的學生
解法一：判斷月份是否相等
SELECT sid
,sname
,MONTH(sage) AS "生日"
,MONTH(NOW())
FROM student
WHERE MONTH(sage) = MONTH(NOW());
解法二：EXTRACT()函數(shù)獲取月份時間，判斷是否相等
SELECT *
FROM student
WHERE EXTRACT(MONTH FROM Sage) = EXTRACT(MONTH FROM CURDATE())罚随；
45玉工、查詢下月過生日的學生
解法一：
SELECT sid
,sname
,MONTH(sage) AS "生日"
,MONTH(NOW())
FROM student
WHERE MONTH(sage) = MONTH(NOW()) + 1;
解法二：EXTRACT()函數(shù)獲取月份時間，DATE_ADD()函數(shù)計算下月時間
SELECT *
FROM student
WHERE EXTRACT(MONTH FROM Sage) = EXTRACT(MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))淘菩；
小結(jié)：DATE_ADD()函數(shù)
定義和用法
DATE_ADD() 函數(shù)向日期添加指定的時間間隔遵班。
語法
DATE_ADD(date,INTERVAL expr type)
date： 參數(shù)是合法的日期表達式
expr ：參數(shù)是您希望添加的時間間隔
type ：參數(shù)可以是年屠升、月、日狭郑、周腹暖、小時、分鐘等等翰萨。
