五餅二魚工作室

James Fu 的技術學習之路

2021-12-26

[心得分享] 閱讀 "Mysql 经典50题练习" 後的語法練習與整理

雖然 Mysql 和 MSSQL 看起來有點類似 , 但語法上還是有些不同的細節要注意

前幾天有朋友傳了一個網址詢問一些 SQL 指令的問題 , 而剛好文章介紹的題目 , 之前在對岸的網站上也曾經看過 , 就順便整理一下幾個之前幫同事將 Mysql 的指令轉換成 MSSQL 上所採到的地雷。

首先在資料產生上的指令，我將原本網站上的語法調整成為 T-SQL 適合的格式



-- 建表
-- 學生表
CREATE TABLE [Student](
 [s_id] VARCHAR(20),
 [s_name] VARCHAR(20) NOT NULL DEFAULT '',
 [s_birth] VARCHAR(20) NOT NULL DEFAULT '',
 [s_sex] VARCHAR(10) NOT NULL DEFAULT '',
 PRIMARY KEY([s_id])
);
-- 課程表
CREATE TABLE [Course](
 [c_id]  VARCHAR(20),
 [c_name] VARCHAR(20) NOT NULL DEFAULT '',
 [t_id] VARCHAR(20) NOT NULL,
 PRIMARY KEY([c_id])
);
-- 教師表
CREATE TABLE [Teacher](
 [t_id] VARCHAR(20),
 [t_name] VARCHAR(20) NOT NULL DEFAULT '',
 PRIMARY KEY([t_id])
);
-- 成績表
CREATE TABLE [Score](
 [s_id] VARCHAR(20),
 [c_id]  VARCHAR(20),
 [s_score] SMALLINT,
 PRIMARY KEY([s_id],[c_id])
);
GO

-- 插入學生表測試資料
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' , '女');
-- 課程表測試資料
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');

-- 教師表測試資料
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成績表測試資料
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
GO

在上述的腳本中，我將原本 Mysql 中的分隔識別碼採用 ` ( 是 backticks , 而不是單引號喔 ) 換成 MSSQL 中所採用的方括號 ( 可參考微軟文件 )，這個也是我之前轉換 MySQL 比較麻煩的一點，因為 MSSQL 中有許多的規範比較嚴格一點，像是欄位名稱不能用數字開頭的，如果你特別要用數字開頭當欄位名稱，就一定要加入分隔識別碼，否則就會有出錯的狀況。

以下我也針對幾個原作者寫的指令，我針對 MSSQL 開發時的習慣，也來做個簡單的範例說明

題目要求
查詢”01"課程比”02"課程成績高的學生的資訊及課程分數

select 
a.*
,b.s_score as [1_score]  
,c.s_score as [2_score]
from Student a
inner join Score b on a.s_id = b.s_id and b.c_id = '01'   -- 方法1兩個表透過學號連線，指定01
inner join Score c on a.s_id = c.s_id and c.c_id = '02'   -- 指定02
where b.s_score > c.s_score;

上述這個題目在寫的時候，我就不會去採用 outer join 的方式，因為當採用 outer join 時，表示學生可能沒有 01 或 02 的課程分數，但如果那樣的狀況發生，後續就算比對兩者分數差異的時候，就可能其中一個是 NULL 的值，而 NULL 在數值判斷時又不會成立，因此我會覺得沒有必要畫蛇添足客戶去採用 outer join ，直接使用 inner join 來做處理就可以了。

 

題目需求
查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

基本上這個看起來是個很稀鬆平常的問題 , 但這個也是我們在修改 Mysql 轉換到 MSSQL 的時候 , 一個很雷的狀況。在 Mysql 下面是可以這樣子的處理

-- ** Mysql **
-- 執行順序：先執行分組，再執行avg平均操作
select 
	b.s_id
	,b.s_name
	,round(avg(a.s_score), 2) as avg_score
from Student b
join Score a on b.s_id = a.s_id
group by b.s_id   -- 分組之後查詢每個人的平均成績
having avg_score >= 60;

但對於學習 MSSQL 的人來，從上面的語法中看起來至少有兩個的錯誤。在 MSSQL 的教材當中，都會有個 SELECT 語法順序的列表

步驟指令
1FROM
2WHERE
3GROUP BY
4HAVING
5SELECT
6ORDER BY

那當我們用這樣的順序來看在 Mysql 可以執行的語法 , 就會發現因為 Having 的順序比 SELECT  優先，因此那個 avg_score 的別名就不應該使用在那裏。除此之外，GROUP BY 也是一個差異很大的地方，在 MSSQL 中沒有辦法接受這種當有 Aggregate Function 在使用的時候，其他 SELECT 的欄位沒有加入到 GROUP BY 裡面，但是這樣的寫法在 Mysql 中卻是認為是正常的。因此如果以上述的題目和欄位型態來看，我會建議在 MSSQL 中改成這樣的寫法：

-- 因為分數欄位型態是整數 , 因此平均值也會整數 , 不需要再做四捨五入
with ScoreAvg as
(
	select s_id,avg(s_score) as avg_score from Score group by s_id having avg(s_score) > 60
)
select 
	b.s_id, b.s_name, a.avg_score
from Student b
inner join ScoreAvg a on b.s_id = a.s_id;


-- 附加題：總分超過200分的同學
with ScoreSum as
(
	select s_id,sum(s_score) as sum_score from Score group by s_id having sum(s_score) > 200
)
select 
	b.s_id, b.s_name, a.sum_score
from Student b
inner join ScoreSum a on b.s_id = a.s_id;

這裡先用 CTE 的方式找到符合的學生代號，再利用 JOIN 的方式去抓學生資料檔案，而且因為成績欄位是整數型態，在 MSSQL 中，搭配這類的 Aggregate Function 的時候，結果還是會跟原本來源欄位的型態一致，因此也不用特別去加入 Round 這類的函數再去處理。

題目要求
查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績（包括有成績的和無成績的）

這個題目針對有成績的來說 , 跟前面一個答案有點類似 , 但如果要包含沒有成績紀錄的，就會有點小麻煩。因為如果直接從成績檔裡面來查，只能找有資料的，但如果要包含無成績的，則寫法上就要有點調整

-- 有成績
with ScoreAvg as
(
	select s_id,avg(s_score) as avg_score from Score group by s_id having avg(s_score) < 60
)
select 
	b.s_id, b.s_name, a.avg_score
from Student b
inner join ScoreAvg a on b.s_id = a.s_id;


-- 包含無成績 
with ScoreAvg as
(
	select s_id,avg(s_score) as avg_score from Score group by s_id 
)
select 
	b.s_id, b.s_name, a.avg_score
from Student b
left join ScoreAvg a on b.s_id = a.s_id 
where a.avg_score < 60 or a.avg_score IS NULL ;

-- 包含無成績 
with ScoreAvg as
(
	select s.s_id,avg(s_score) as avg_score 
	from Student s
	left join Score sc on s.s_id = sc.s_id
	group by s.s_id 
	having avg(s_score) < 60 or count(s_score) = 0
)
select 
	b.s_id, b.s_name, a.avg_score
from Student b
join ScoreAvg a on b.s_id = a.s_id

當然上述只是一種方式，針對沒有成績的，也是可以把學生放到 CTE 內關聯，這兩種方式都算是蠻直接的方法。只是在 Mysql 上用 union 或者是 sub query 的方式，我個人習慣就比較不偏好，因為那樣讓整段 SQL 重複性高，也增加閱讀識別上的難度，因此像是這類的處理我個人都還是偏向先用 CTE 預作處理再來做關聯，會比較容易思考和閱讀的。

題目需求
查詢學過張三老師授課的同學的資訊

以這個題目來說，也是在玩 SQL 指令的時候，很容易有陷阱的一個題目，主要是在於資料表的設計中，我們只能透過成績檔知道學生有沒有修某個課程，但是如果有休課但沒有成績呢 ? 從目前資料表的設計中，比較沒有辦法知道還能怎麼來取得，因此也只能假設有修課的學生一定有成績；而另外還有個問題，也就是一個老師可能不只有一堂課，雖然目前資料上是沒有，但資料結構上是允許這樣的狀況發生，因此這樣的時候，我會建議用以下的寫法來處理，資料表之間用 inner join ，另外也避免學生資料重複，可以再加入 distinct 來避免重複的學生資料

select distinct s.* 
from Teacher t
inner join Course c on t.t_id=c.t_id	-- 教師表和課程表
inner join Score sc on c.c_id=sc.c_id	-- 課程表和成績表
inner join Student s on s.s_id=sc.s_id  -- 成績表和學生資訊表
where t.t_name='張三';

題目需求
找出沒有學過張三老師課程的學生

而至於沒有修過課程的學生，如同之前所建議的方式，我還是會習慣避免用 Sub Query 的方式來處理，而改用另外一個比較少被使用的 Except 的方式來做處理

with NoStudy as
(
	select s.s_id from Student s
	except
	select sc.s_id 
	from Teacher t
	inner join Course c on t.t_id=c.t_id	-- 教師表和課程表
	inner join Score sc on c.c_id=sc.c_id	-- 課程表和成績表
	where t.t_name='張三'
)
select s.*
from NoStudy n
join Student s ON n.s_id = s.s_id

我個人在教課的過程中，偶爾也有使用類似的例子來說明，雖然這樣的指令可以用 Sub Query 串起來一長串，但以往後續我們要增加欄位或者是調整相關處理的時候，卻是非常難去修改，但是如果類似上述的作法，在 CTE 裡面兩段的 SQL ，都可以分別去做測試，然後再把 CTE 進行測試，如果後續還要串接甚麼樣的資料表去關聯，在最後面那段 select 中再去處理就可以了，處理上也就比較結構化了。

題目需求
查詢學過編號為01，並且學過編號為02課程的學生資訊

而這個題目也是很容易放在練習 Self Join 的場景中，因為需要跟成績檔關聯兩次，因此會特別利用別名的方式，將同個資料表分別用不同的別名來取代

select s.*
from Student s
inner join Score sc1 on s.s_id=sc1.s_id	and sc1.c_id = '01'
inner join Score sc2 on s.s_id=sc2.s_id	and sc2.c_id = '02'

這個看起來就沒有甚麼特別，但主要是透過這個題目，也連帶影響到下一個題目的處理

題目需求
查詢學過01課程，但是沒有學過02課程的學生資訊（注意和上面👆題目的區別）

select s.*
from Student s
inner join Score sc1 on s.s_id=sc1.s_id	and sc1.c_id = '01'
left  join Score sc2 on s.s_id=sc2.s_id	and sc2.c_id = '02'
where sc2.s_id is null

後面這兩個題目很多時候我們都是分別練習 Self Join 或者是 Outer Join 的觀念，但算是設計很不錯的一個題目，合併再一起練習。

上述這幾個是剛好該網頁作者有提供寫法的 10 個題目中的部分，剛好有些也是我之前幫忙處理一些 Mysql 時候所踩到的地雷，其實我沒有要論戰到底是 Mysql 或者是 MSSQL 哪個比較好，只是每種關聯式資料庫在處理語法的時候，或多或少都有些不同的差異，有些時候那些特性是比較方便，但為了比較容易維護和理解。

系列文章

標籤雲