[心得分享] 閱讀 "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 哪個比較好,只是每種關聯式資料庫在處理語法的時候,或多或少都有些不同的差異,有些時候那些特性是比較方便,但為了比較容易維護和理解。