第 七 章 SQL的查詢語言 課程名稱：資料庫系統

本章內容 7-1 單一資料表的查詢 7-2 常用的函數 7-3 使用Select子句 7-4 使用「比較運算子條件」 7-1 單一資料表的查詢 7-2 常用的函數 7-3 使用Select子句 7-4 使用「比較運算子條件」 7-5 使用「邏輯比較運算子條件」 7-6 使用「模糊條件與範圍」 7-7 使用「算術運算子」 7-8 使用「聚合函數」 7-9 使用「排序及排名次」 7-10 使用「群組化」 7-11 使用「刪除重覆」 1-1 認識資料與資訊的關係: 其中，「資料」轉換成「資訊」必須要經過一連串處理過程，而這一連串的處理過程就是透過「程式」來處理。 1-2 何謂資料結構？ 「資料結構」(Data Structures)主要是探討如何將資料更有組織地存放到電腦記憶體中，以提昇程式之執行效率的一 門學問。 1-3 何謂演算法?演算法就是「解決問題的方法」 1-4 程式設計概念: 步驟1. 分析所要解決的問題 步驟2. 設計解題的步驟 步驟3. 編寫程式 步驟4. 上機測試、偵測錯誤 步驟5. 編寫程 式說明書 1-5 結構化程式設計 利用「由上而下」的技巧，將程式分解成許多個獨立功能的模組。並且每一個模組都是由三種結構所組成。分別為循序結構、選擇結構及重複結構。 1-6 演算法的效率評估 指用來計算某些演算法所撰寫的程式，在經過編譯之後，實際執行所需要的時間。

7-1 單一資料表的查詢　 在SQL語言所提供三種語言(DDL,DML,DCL)中，其中第二種為資料操作語言(Data Manipulation Language; DML)，主要是提供給使用者對資料庫進行異動(新增、修改、刪除)操作及「查詢」操作等功能。 而在異動操作方面比較單純，已經在第六章有詳細介紹了，但在「查詢」操作方面是屬於比較複雜且變化較大的作業，因此，筆者特別將資料庫的查詢單元，利用本章節介紹。

7-1.1 SQL的基本語法 【說明】 SELECT [* | DISTINCT | Top n] <欄位串列> FROM (資料表名稱{<別名>} | JOIN資料表名稱) [WHERE <條件式>] [GROUP BY <群組欄位> ] [HAVING <群組條件>] [ORDER BY <欄位> [ASC | DESC]] 【說明】 1. Select後面要接所要列出的欄位名稱。 2. [* / Distinct|Topn]中括號的部份可以省略。 (1) ”*” 表示列印出所有的欄位(欄位1,欄位2,……,欄位n) (2) Distinct代表從資料表中選擇不重複的資料。 它是利用先排序來檢查是否有重複，因此，已經內含order by的功能。 所以，如果使用Distinct時，就不須要再撰寫order by。 (3) Top n指在資料表中取出名次排序在前的n筆記錄。 (4)INTO 新資料表：是指將SELECT查詢結果存入另一個新資料表中。

7-1.1 SQL的基本語法<續> 【說明】 3.From後面接資料表名稱，它可以接一個以上的資料表。 SELECT [* | DISTINCT | Top n] <欄位串列> FROM (資料表名稱{<別名>} | JOIN資料表名稱) [WHERE <條件式>] [GROUP BY <群組欄位> ] [HAVING <群組條件>] [ORDER BY <欄位> [ASC | DESC]] 【說明】 3.From後面接資料表名稱，它可以接一個以上的資料表。 4.Where後面要接條件式(它包括了各種運算子)

7-1.1 SQL的基本語法<續> 【說明】 5. Group By欄位1,欄位2,…,欄位n [Having 條件式] SELECT [* | DISTINCT | Top n] <欄位串列> FROM (資料表名稱{<別名>} | JOIN資料表名稱) [WHERE <條件式>] [GROUP BY <群組欄位> ] [HAVING <群組條件>] [ORDER BY <欄位> [ASC | DESC]] 【說明】 5. Group By欄位1,欄位2,…,欄位n [Having 條件式] (1)Group By 可單獨存在，它是將數個欄位組合起來，以做為每次動作的依據。 (2) [Having 條件式]是將數個欄位中加以有條件的組合。它不可以單獨存在。

7-1.1 SQL的基本語法<續> 【說明】 6. Order By 欄位1,欄位2,…,欄位n [Asc|Desc] SELECT [* | DISTINCT | Top n] <欄位串列> FROM (資料表名稱{<別名>} | JOIN資料表名稱) [WHERE <條件式>] [GROUP BY <群組欄位> ] [HAVING <群組條件>] [ORDER BY <欄位> [ASC | DESC]] 【說明】 6. Order By 欄位1,欄位2,…,欄位n [Asc|Desc] 它是依照某一個欄位來進行排序。 例如：(1) ORDER BY 成績 Asc 可以省略(由小至大) (2) ORDER BY成績 Desc 不可以省略(由大至小)

7-1.2 建立學生選課資料庫　 在本單元中，為了方便撰寫SQL語法所須要的資料表，我們以「學生選課系統」的資料庫系統為例，建立資料庫關聯圖，以便後續的查詢分析之用，如圖7-1所示。 資料庫名稱：ch7_DB.mdf 圖7-1學生選課系統之資料庫關聯圖

因此我們利用SQL Server建立七個資料表，分別為：

7-2 SQL常用的函數　 在SQL Server中的函數種類非常的多，本單元將介紹最見被使用的兩種函數，分別為： 1.字串函數 2.數值函數

7-2.1 使用「字串函數」 Left( ) Right( ) Substring( ) Len( ) LTrim( ) RTrim( ) 7-2.1 使用「字串函數」　 運算子 功能 Left( ) 傳回字串左邊指定數量的字元 Right( ) 傳回字串右邊指定數量的字元 Substring( ) 傳回字串中間指定數量的字元 Len( ) 傳回字串的長度 LTrim( ) 刪除字串中的左邊空白字元 RTrim( ) 刪除字串中的右邊空白字元 Lower( ) 將英文字轉換成小寫字母 Upper() 將英文字轉換成大寫字母 Stuff( ) 將字串中某些特定字串取代成另一字串 Replicate( ) 傳回指定重複次數的資料

一、 Left( )函數 【定義】傳回字串左邊指定數量的字元 【語法】Left(Str,n) //取出Str字串的左邊n個字元 【實例】在「學生資料表」中查詢學生姓名是姓’李’字開頭的名單 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT * FROM 學生資料表 WHERE Left(姓名,1)='李'

二、 Right( ) 函數 【定義】傳回字串右邊指定數量的字元 【語法】Right(Str,n) //取出Str字串的右邊n個字元 【實例】在「學生資料表」中查詢學生姓名的最後一個字是’安’的學生 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT * FROM 學生資料表 WHERE Right(姓名,1)='安'

三、 Substring ( )函數 【定義】傳回字串中間指定數量的字元。 【實例】在「科系代碼表」中，查詢是「資管系」的系主任 【解答】 【查詢結果】 SQL指令 use ch7_DB Go SELECT * FROM 科系代碼表 WHERE Substring(系名,1,2)='資管'

【隨堂練習】 請在ch7_hwDB資料庫中，找出「客戶資料表」中，客戶的電話 區域代號。 【解答】 【查詢結果】 SQL指令 【隨堂練習】　 請在ch7_hwDB資料庫中，找出「客戶資料表」中，客戶的電話 區域代號。 【解答】 【查詢結果】 SQL指令 use ch7_hwDB Go SELECT 客戶姓名,SUBSTRING(電話,1,2) AS 電話區域代號 FROM dbo.客戶資料表

四、 Len( ) 函數 【定義】傳回字串的長度。 【語法】Len(Str) //指取出Str字串長度的值 【實例】在「選課資料表」中，查詢學生成績是三位數(也就是100分) 的學生的「學號、課號及成績」 【解答】 注意：由於沒有同學考100分，以上的查詢沒有記錄顯示。 SQL指令 use ch7_DB Go SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE Len(成績)=3

【隨堂練習】 請在ch7_DB資料庫中，計算出「課程資料表」中每一門課程之課名的字數。 【解答】 【查詢結果】 SQL指令 【隨堂練習】　 請在ch7_DB資料庫中，計算出「課程資料表」中每一門課程之課名的字數。 【解答】 【查詢結果】 SQL指令 use ch7_DB Go SELECT 課號,課名, Len(課名) As 課名的字數 FROM dbo.課程資料表

五、LTrim( ) 與 Trim( ) 與 RTrim( )函數 【定義】刪除字串中的空白字元 【舉例】 SQL指令 Select '印出：' + LTrim(' 資料庫系統 ') -- 印出「資料庫系統△△△」 Select '印出：' + RTrim(' 資料庫系統 ') -- 印出「△△△資料庫系統」

六、 Lower( ) 與 Upper()函數 【定義】轉換小寫與大寫字母 【舉例】 SQL指令 Select '印出：' + Lower('Visual Basic 2010') -- 印出「visual basic 2010」 Select '印出：' + Upper('Visual Basic 2010') -- 印出「VISUAL BASIC 2010」

七、 Stuff( )函數 【定義】將字串中某些特定字串取代成另一字串 【舉例】 SQL指令 Select '印出：' + Stuff('Visual Basic 2010', 2,1,'') --Vsual Basic 2010 Select '印出：' + Stuff('Visual Basic 2010', 8,5,'C#') --Visual C# 2010

【隨堂練習】 由於縣市合併，所以請在ch7_hwDB資料庫中，找出「客戶資料表」中，區域中有「鄉」改為「區」。 【解答】 修改前： 修改後： 【隨堂練習】　 由於縣市合併，所以請在ch7_hwDB資料庫中，找出「客戶資料表」中，區域中有「鄉」改為「區」。 【解答】 修改前： 修改後： SQL指令 use ch7_hwDB Go UPDATE dbo.客戶資料表 SET 區域=STUFF(區域,3,1,'區') WHERE RIGHT(區域,1)='鄉‘

八、 Space( )函數 【定義】傳回重複空白的字串字元 【語法】Space(正整數 ) 【舉例】 請在ch7_hwDB資料庫中，找出「客戶資料表」中，將城市與區域合併顯示，並在中間空二個空白格。 【執行結果】 SQL指令 use ch7_hwDB Go SELECT 客戶姓名,城市+SPACE(2)+區域 AS 城市區域名稱 FROM dbo.客戶資料表

九、Replicate函數 【定義】傳回指定重複次數的資料 【語法】Replicate (‘欲重複的資料’, 重複次數) 【舉例】請列出「我超愛打桌球」三次 【執行結果】 SQL指令 SELECT Replicate ('我超愛打桌球',3)

7-2.2使用「數值函數」 定義：在SQL Server中的函數種類非常的多，首先，我們先來介紹最常使用的數值函數資料。 表7-2 數值函數表 運算子 功能 Abs( ) 取絕對值 ACOS(n) 反三角函數 ASIN(n) ATAN(n) CEILING(n) 傳回大於或等於n的最小整數值 COS(n) 傳回n的餘弦值，結果為FLOAT資料型態 DEGREES(n) 傳回n弧度對應的度數 EXP(n) 指數函數 FLOOR(n) 傳回小於或等於n的最小整數值 LOG(n) 傳回n的自然(基數為e)對數值 LOG10(n) 傳回n的對數值(基數為10) PI() 傳回圓周率(3.14…) POWER(x,y) 傳回x~y的值 RAND 傳回0-1之間的随機值，結果為FLOAT資料型態 ROUND(n,p,[t]) 取四捨五入 ROWCOUNT_BIG 傳回系统執行的、受最後一行T-SQL語句影響的行數 SIGN(n) 傳回n值的符號數字（正數為，負數為-1，0為0） SIN(n) 傳回n的正弦值，結果為FLOAT資料型態 SQRT(n) 傳回n的平方根值 SQUARE(n) 傳回n的平方值 TAN(n) 傳回n的正切值，結果為FLOAT資料型態

一、Abs(x) 【定義】取x的絕對值 【實例】Abs(-3.14) 【範例】 Print ‘印出：’ + CONVERT(char,Abs(100)) --印出100 Print '印出：' + CONVERT(char,Abs(-100)) --印出100 Print ‘印出：’ + CONVERT(char,Abs(-3.14)) --印出3.14 Print ‘印出：’ + CONVERT(char,Abs(0.11)) --印出.11 Print '印出：' + CONVERT(char,Abs(0)) --印出0

二、 ASin ( ) 、ACos ( ) 、ATan ( ) 反三角函數 【語法1】Sin_value= ASin(X) ' 傳回X數值的反正弦值 【語法2】Cos_value=ACos(X) ' 傳回X數值的反餘弦值 【語法3】Tan_value= ATan(X) ' 傳回X數值的反正切值 【說明】將角度轉成徑度的計算公式如下所示： 徑度＝角度＊PI / 180 ， 其中PI是指圓週率3.14159265358979 撰寫SQL指令 輸出結果 SELECT ASin(30 * PI() / 180) as [ASin(30)] SELECT ACos(30 * PI() / 180) as [ACos(30)] SELECT ATan(45 * PI() / 180) as [ATan(45)] ASin(30)：0.551069600201632 ACos(30)：1.01972672659326 ATan(45)：0.665773763545805

三、 CEILING(n)函數 【語法】Ceiling(n) 【說明】取≧n的最小整數值 【舉例】 撰寫SQL指令 輸出結果 SELECT Ceiling(99.9) as [Ceiling(99.9)] SELECT Ceiling(-99.9) as [Ceiling(-99.9)] SELECT Ceiling(1.99) as [Ceiling(1.99)] Ceiling (99.9)=100 Ceiling (-99.9)=-99 Ceiling (1.99)=2

【隨堂練習】　 SELECT CEILING(4.8) ;  結果為：??? SELECT CEILING(-4.8) ;  結果為：???

【隨堂練習】　 SELECT CEILING(4.8) ;  結果為：5 SELECT CEILING(-4.8) ;  結果為：-4

四、 Sin ( ) 、Cos ( ) 、Tan ( ) 正三角函數 【語法1】Sin_value= Sin(X) //傳回X數值的正弦值 【語法2】Cos_value= Cos(X) //傳回X數值的餘弦值 【語法3】Tan_value=Tan(X) //傳回X數值的正切值 【說明】將角度轉成徑度的計算公式如下所示： 徑度＝角度＊PI / 180 ， 其中PI是指圓週率3.14159265358979 【舉例】 撰寫SQL指令 輸出結果 SELECT Sin(30 * PI() / 180) as [Sin(30)] SELECT Cos(30 * PI() / 180) as [Cos(30)] SELECT Tan(45 * PI() / 180) as [Tan(45)] Sin(30)=0.5 Cos(30)=0.867 Tan(45)=1.0

五、 DEGREES(n)函數 【語法】DEGREES(n) 【說明】傳回n弧度對應的度數 【舉例】 撰寫SQL指令 輸出結果 SELECT DEGREES(PI()/2); SELECT DEGREES(PI()/4); 90 45

六、 Exp (x) 指數函數 【語法】Exp_value= Exp(x) //傳回e的x次方，也就是ex 當x＞709.782712893時，將會產生溢位。因為超出Double(雙精 準度)的表示範圍。 撰寫SQL指令 輸出結果 SELECT Exp(1) as [Exp(1)] SELECT Exp(2) as [Exp(2)] Exp(1)=2.71828182845905 Exp(2)=7.38905609893065

七、 Floor ( ) 取≦x的最大整數值函數 【舉例】 撰寫SQL指令 輸出結果 SELECT Floor(99.9) as [Floor(99.9)] SELECT Floor(-99.9) as [Floor(-99.9)] SELECT Floor(1.99) as [Floor(1.99)] Floor (99.9)=99 Floor (-99.9)=-100 Floor (1.99)=1

【隨堂練習】　 SELECT FLOOR(4.8) ;  結果為：??? SELECT FLOOR (-4.8) ;  結果為：???

【隨堂練習】　 SELECT FLOOR(4.8) ;  結果為：4 SELECT FLOOR (-4.8) ;  結果為：-5

八、 Log ( )函數 【語法】Log (x); 【說明】取x以e為底數的對數值，而Log與Exp互為反函數Exp(Log(x))=Log(Exp(x)) 在數學上的ex =Y 則LogeY=x ，也就是說： 如果Y= Exp(x)時，則x= Log(Y) 【舉例】 撰寫SQL指令 輸出結果 SELECT Exp(Log(100)) as [Exp(Log(100))] Exp(Log(100))=100

九、 Log10 ( )函數 【語法】Log (x); 【說明】以10為底數取對數值(基數為10) 【舉例】 撰寫SQL指令 輸出結果 SELECT LOG10(100) as [LOG10(100)] SELECT LOG10(0.1) as [LOG10(0.1)] SELECT Exp(Log10(100)) as [Exp(Log10(100))] 2 -1 7.38905609893065

十、 PI() 取圓周率函數　 【語法】PI() 【說明】取得圓周率π值 撰寫SQL指令 輸出結果 SELECT PI() as [PI]

十一、 power ( ) 取次方函數 【語法】power(x,y) 【說明】取x的y次方。 【範例】 撰寫SQL指令 輸出結果 Print 'Power(2,10)=' + CONVERT(char,Power(2, 10)) 1024

【隨堂練習】 SELECT POWER(100,0.5)； 結果為：10

十二、 Rand ( ) 亂數函數 【語法】Rand_value=Rand( ) 【說明】 如果我們要取得某一特定範圍的亂數時，我們可以套用以下的公式： 亂數 = CONVERT(Int,Rand()* ( 上限 - 下限 + 1) + 下限) 如果我們拿投擲骰子的每一個點當作 1 到 6 的亂數，其上限值=6 ； 下限值=1 則我們可以套用上面的公式得： Point_Num = CONVERT(Int,Rand()*(6-1+1))+1 或化簡為： Point_Num = CONVERT(Int,Rand()*6)+1

【範例】 撰寫SQL指令 輸出結果 Print Rand() Print CONVERT(Int,Rand()*6)+1 --產生0<=X<1的亂數 --產生1~6的亂數值

【實作1】 利用while迴圈來投擲骰子10次，並印出每一位的點數。 (請參考9-6.3章節中的while迴圈之使用方法) 【實作1】　 利用while迴圈來投擲骰子10次，並印出每一位的點數。 (請參考9-6.3章節中的while迴圈之使用方法) declare @i int=1 while (@i<=10) begin Print ‘第’ + CONVERT(nchar,@i) + ‘次出現：’ + CONVERT(nchar,CONVERT(int,Rand()*6)+1) set @i+=1 End

【實作2】 承實作1，利用變數將投擲骰子10次的點數加總。 declare @i int=1 , @Total int=0 【實作2】　 承實作1，利用變數將投擲骰子10次的點數加總。 declare @i int=1 , @Total int=0 while (@i<=10) begin Print ‘第’ + CONVERT(nchar,@i) + ‘次出現：’ + CONVERT(nchar,CONVERT(int,Rand()*6)+1) set @i+=1 set @Total+=(CONVERT(int,Rand()*6)+1) End select @Total AS 投擲骰子次的點數加總

十三、 round ( ) 取四捨五入 【語法】Round(num,length [,function]) 當為1時，代表無條件捨去。 【範例】 撰寫SQL指令 輸出結果 Print 'Round(100.15)=' + CONVERT(char,Round(100.25,1)) ; Print 'Round(100.15)=' + CONVERT(char,Round(100.25,1,0)) ; Print 'Round(100.15)=' + CONVERT(char,Round(100.25,1,1)) ; 100.30 100.20

十四、 Sign ( ) 取正負符號函數 【語法】Sign(x); 【說明】取x的正負符號，當X>0時，則Sgn_value= 1 【舉例】 撰寫SQL指令 輸出結果 SELECT Sign(100) as [Sign(100)] SELECT Sign(100-100) as [Sign(100-100)] SELECT Sign(100-200) as [Sign(100-200)] 1 -1

十五、 Sqrt ( ) 取平方根函數 【語法】sqrt(x); 【說明】取x的平方根，x必須≧0，否則程式會產生錯誤。 【範例】 撰寫SQL指令 輸出結果 Print 'Sqrt(2)=' + CONVERT(nchar,Sqrt(2)) Print 'Sqrt(4)=' + CONVERT(nchar,Sqrt(4)) Print 'Sqrt(8)=' + CONVERT(nchar,Sqrt(8)) 1.41421 2 2.82843

十六、 SQUARE(n) 取平方值函數 【語法】SQUARE(n) ; 【說明】取n的平方值。 【舉例】 撰寫SQL指令 輸出結果 SELECT SQUARE(4) as [SQUARE(4)] 16

7-3 使用Select子句 【定義】 Select是指在資料表中，選擇全部或部份欄位顯示出來，這就是所謂的「投影運算」。 【格式】 From 資料表名稱

7-3.1 查詢全部欄位　 【定義】 是指利用SQL語法來查詢資料表中的資料時，可以依照使用者的權限及需求來查詢所要看的資料。如果沒有指定欄位的話，我們可以直接利用星號「*」代表所有的欄位名稱。 【優點】不需輸入全部的欄位名稱。 【缺點】 1.無法隱藏私人資料。 2.無法自行調整欄位順序。 3.無法個別指定欄位的別名。

[實例] 在「學生資料表」中顯示「所有學生基本資料」《參見7-1.2 》 【解答】 【查詢結果】 SQL指令1 use ch7_DB [實例]　 在「學生資料表」中顯示「所有學生基本資料」《參見7-1.2 》 【解答】 【查詢結果】 SQL指令1 use ch7_DB SELECT * FROM 學生資料表 SQL指令2與SQL指令1有相同的結果 use ch7_DB SELECT 學號,姓名,系碼 FROM 學生資料表

【隨堂練習】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示學生成績表中的全部記錄。 【隨堂練習】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示學生成績表中的全部記錄。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT * FROM 學生成績表

7-3.2 查詢指定欄位(垂直篩選) 【定義】 由於上一種方法，只能直接選擇全部的欄位資料，無法顧及隱藏私人 7-3.2 查詢指定欄位(垂直篩選)　 【定義】 由於上一種方法，只能直接選擇全部的欄位資料，無法顧及隱藏私人 資料及自行調整欄位順序的問題，因此，我們利用指定欄位來查詢資 料。 【優點】 1.顧及私人資料。 2.可自行調整欄位順序。 3.可以個別指定欄位的別名。 【缺點】 如果確定要顯示所有欄位，則必須花較多時間輸入。

[實例] 在「學生資料表」中查詢所有學生的「姓名及系碼」 《參見7-1.2 》 【解答】 【查詢結果】 [實例]　 在「學生資料表」中查詢所有學生的「姓名及系碼」 《參見7-1.2 》 【解答】 【查詢結果】 說明：在「學生資料表」中將「姓名」及「系碼」投射出來。 SQL指令 use ch7_DB SELECT 姓名, 系碼 FROM 學生資料表 欄位與欄位名稱之間，必須要以逗號「，」隔開

【隨堂練習】 資料庫名稱：ch7_hwDB.mdf 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示學生的「姓名」、「資料庫」及「程式設計」三個欄位的所有記錄。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT 姓名, 資料庫, 程式設計 FROM 學生成績表

7-3.3 使用「別名」來顯示 【定義】 使用AS運算子之後，可以使用不同名稱顯示原本的欄位名稱。 【表示式】原本的欄位名稱 AS 別名 7-3.3 使用「別名」來顯示　 【定義】 使用AS運算子之後，可以使用不同名稱顯示原本的欄位名稱。 【表示式】原本的欄位名稱 AS 別名 《AS可省略不寫，只寫「別名」》 【舉例】系碼 AS 科系代碼 或寫成 系碼 科系代碼 【注意】 AS只是暫時性地變更列名，並不是真的會把原本的名稱覆蓋過去。 【適用時機】1.欲「合併」的資料表較多並且名稱較長時。 2.一個資料表扮演多種不同角色(自我合併)。 3.暫時性地取代某個欄位名稱(系名 AS 科系代碼) 【替代欄位名稱字串】 替代字元 功能 語法 AS 設定別名 Select 系碼 AS 系所班別 + 結合兩個欄位字串 SELECT 學號+姓名 AS 資料

[實例1] 在「學生資料表」中將所有學生的「系碼」設定別名為「科系代碼」」之後，再顯示「姓名、科系代碼」 《參見7-1.2 》 【解答】 [實例1]　 在「學生資料表」中將所有學生的「系碼」設定別名為「科系代碼」」之後，再顯示「姓名、科系代碼」 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 姓名, 系碼 AS 科系代碼 FROM 學生資料表 利用AS來設定 欄位的別名 設定別名

[實例2] 在「選課資料表」中將所有學生的「成績」各加5分，並且設定別名為「調整後成績」之後，再顯示「學號，課號，成績，調整後成績」 [實例2]　 在「選課資料表」中將所有學生的「成績」各加5分，並且設定別名為「調整後成績」之後，再顯示「學號，課號，成績，調整後成績」 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績,成績+5 AS 調整後成績 FROM 選課資料表

【隨堂練習1】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來將成績表中的「姓名」欄位改為「學生姓名」、「資料庫」欄位改為「資料庫成績」。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT 姓名 AS 學生姓名, 資料庫 AS 資料庫成績 FROM 學生成績表

【隨堂練習2】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來將成績表中的「姓名」及三科目的「總平均成績」。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT 姓名, (資料庫+資料結構+程式設計)/3 AS 總平均成績 FROM 學生成績表;

7-3.4 使用「Into」來新增資料到新資料表中 【定義】 使用Into運算子來將查詢出來的結果，存入到另一個資料表中。 【表示式】SELECT 欄位串列 INTO 新資料表名稱 【注意】新資料表名稱不須事先建立。 【適用時機】資料備份或測試時。

【實例1】 在「學生資料表」中將所有學生資料備份一份，另存入「測試用學生資料表_1」 《參見7-1.2 》 【解答】 【執行結果】 SQL指令 use ch7_DB SELECT * INTO 測試用學生資料表_1 FROM 學生資料表

【實例2】 在「學生資料表」中將所有學生資料備份一份，另存入「測試用學生資料表_2」 並且加入「流水號」 《參見7-1.2 》 【解答】 【執行結果】 SQL指令 use ch7_DB SELECT IDENTITY(int,1,1) As 流水號,學號,姓名,系碼 INTO 測試用學生資料表_2 FROM 學生資料表

7-4 使用「比較運算子條件」　 如果我們所想要的資料是要符合某些條件，而不是全部的資料時，那就必須要在Select子句中再使用Where條件式即可。並且也可以配合使用「比較運算子條件」來搜尋資料。若條件式成立的話則會傳回「True(真)」，若不成立的話則會傳回「False(假)」。 比較運算子表(註：設A代表「成績欄位名稱」,B代表「字串或數值資料」) SQL指令 Select 欄位集合 From 資料表名稱 Where 條件式 運算子 功能 例子 條件式說明 ＝ (等於) 判斷A與B是否相等 A=B 成績=60 <> (不等於) 判斷A是否不等於B A<>B 成績<>60 < (小於) 判斷A是否小於B A<B 成績<60 <= (小於等於) 判斷A是否小於等於B A<=B 成績<=60 > (大於) 判斷A是否大於B A>B 成績>60 >= (大於等於) 判斷A是否大於等於B A>=B 成績>=60

7-4.1 查詢滿足條件的值組(水平篩選) 【定義】 當我們所想要的資料是要符合某些條件，而不是全部的資料時，那就 7-4.1 查詢滿足條件的值組(水平篩選)　 【定義】 當我們所想要的資料是要符合某些條件，而不是全部的資料時，那就 必須要在Select子句中再使用Where條件式即可。 【優點】1.可以依照使用者的需求來查詢。 2.資訊較為集中。

[實例] 在「選課資料表」中查詢修課號為「C005」的學生的「學號及成績」 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 [實例]　 在「選課資料表」中查詢修課號為「C005」的學生的「學號及成績」 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 成績 FROM 選課資料表 WHERE 課號='C005’

【隨堂練習】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示「資料庫」剛好”及格”的學生之「學號」、「姓名」及「資料庫」成績。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT 學號, 姓名, 資料庫 FROM 學生成績表 WHERE 資料庫=60;

7-4.2 查詢比較大小的條件　 【定義】 當我們所想要的資料是要符合某些條件，例如：顯示出及格或不及格的學生名單等情況。此時，我們就必須要在Where 條件式中使用「比較運算子」來篩選。

[實例] 在「選課資料表」中查詢任何課程成績「不及格60」的學生的「學號、課程代號及成績」 《參見7-1.2 》 【解答】 【查詢結果】 [實例]　 在「選課資料表」中查詢任何課程成績「不及格60」的學生的「學號、課程代號及成績」 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績<60 「60分」是數值資料不須要加「左右單引號」

【隨堂練習】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示「資料庫」不及格的學生之「學號」、「姓名」 及「資料庫」成績。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT 學號, 姓名, 資料庫 FROM 學生成績表 WHERE 資料庫<60;

7-5 使用「邏輯比較運算子條件」 在Where條件式中除了可以設定「比較運算子」之外，還可以設定「邏輯運算子」來將數個「比較運算子」條件組合起來，成為較複雜的條件式。其常用的邏輯運算子如表6-6所示： 註：設A代表「左邊條件式」,B代表「右邊條件式」 運算子 功能 條件式說明 And(且) 判斷A且B兩個條件式是否皆成立 成績>=60 And 課程代號='C005' Or(或) 判斷A或B兩個條件式是否有一個成立 課程代號='C004' Or 課程代號='C005' Not(反) 非 A的條件式 Not 成績>=60

7-5.1 And(且) 【定義】判斷A且B兩個條件式是否皆成立。 【實例】 在「選課資料表」中查詢修課號為「C001」且成績是「及格60分」的學生的「學號及成績」 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號,成績 FROM 選課資料表 WHERE成績>=60 And 課號='C005'

【隨堂練習1】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習1】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示「資料結構」與「程式設計」兩科同時及格的學生 之「學號」、「姓名」及這兩科成績。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT 學號, 姓名, 資料結構, 程式設計 FROM 學生成績表 WHERE 資料結構>=60 AND 程式設計>=60;

7-5.2 Or(或) 【定義】判斷A或B兩個條件式是否至少有一個成立。 【實例】 在「選課資料表」中查詢學生任選一科「課程代號為C004 或 課程代號為C005」的學生的「學號、課程代號及成績」 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號,課號,成績 FROM 選課資料表 WHERE 課號='C004' Or 課號='C005'

【隨堂練習】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示「資料結構」與「程式設計」兩科之中至少有一科”不及格”的學生之「學號」、「姓名」及這兩科成績。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT 學號, 姓名, 資料結構, 程式設計 FROM 學生成績表 WHERE 資料結構<60 OR 程式設計<60;

7-5.3 Not(反) 【定義】當判斷結果成立時，則變成不成立。而判斷結果不成立時， 則變成成立。 【實例】 在「選課資料表」中，查詢有修課程代號為C001且成績不及格的學生的「學號及成績」。《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號,成績 FROM 選課資料表 WHERE 課號='C001’ And Not 成績>=60

7-5.4 IS NULL(空值) 【定義】NULL值是表示沒有任何的值(空值)，在一般的資料表中有些欄位中並 【實例1】 沒有輸入任何的值。例如：學生月考缺考，使用該科目成績是空值。 【實例1】 在「選課資料表」中查詢那些學生「缺考」的「學號、課號及成績」。 《參見7-1.2 》 【解答】 【查詢結果】 注意：這裡的「IS」不能用等號(=)代替它。 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績 IS NULL 設定IS NULL條件，其回傳的值True或False

[實例2] 在「選課資料表」中查詢那些學生「沒有缺考」的「學號、課號及成績」 【解答】 《參見7-1.2 》 【查詢結果】 [實例2]　 在「選課資料表」中查詢那些學生「沒有缺考」的「學號、課號及成績」 【解答】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績 IS NOT NULL 設定 IS NOT NULL條件

7-6 使用「模糊條件與範圍」 【定義】 在Where條件式中除了可以設定「比較運算子」與「邏輯運算子」之外，還可以設定「模糊或範圍條件」來查詢。 【例如】奇摩的搜尋網站，使用者只要輸入某些關鍵字，就可以即時查詢出相關的資料。其常用的模糊或範圍運算子如表6-7所示： 運算子 功能 條件式 1. Like 模糊相似條件 Where 系所 LIKE '資管%' 2. IN 集合條件 Where課程代號IN('C001','C002') 3. Between……And 範圍條件 Where成績 Between 60 And 80

7-6.1 Like模糊相似條件 【定義】 LIKE運算子利用萬用字元(%及 _ )來比較相同的內容值。 (1)萬用字元(%)百分比符號代表零個或一個以上的任意字元； (2)萬用字元( _ )底線符號代表單一個數的任意字元。 【注意事項】Like模糊相似條件的萬用字元之比較 撰寫SQL 語法環境 Access SQL Server 比對一個字元 「?」 「_」 比對多個字元 「*」 「%」 比對一個數字 「#」 包含指定範圍 [A-C]代表包含A到C的任何單一字元 排除包含指定範圍 [^A-C]代表排除A到C的任何單一字元

【以SQL Server 的環境為例】 1.Select * 意義：「*」 代表在資料表中的所有欄位 2. WHERE 姓名 Like '李%' 意義：查詢姓名開頭為 '李' 的所有學生資料 3. WHERE 姓名 Like '%李' 意義：查詢姓名結尾為 '李' 的所有學生資料 4. WHERE 姓名 Like ‘%李%' 意義：查詢姓名含有為 '李' 的所有學生資料 5. WHERE 姓名 Like '李__' 意義：查詢姓名中姓 '李'且3個字的學生資料

【實例1】 在「學生資料表」中查詢姓名開頭姓”李”的學生基本資料。 【解答】 《參見7-1.2 》 【查詢結果】 use ch7_DB 【解答】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT * FROM 學生資料表 WHERE 姓名 Like '李%'

【實例2】 在「學生資料表」中查詢姓名開頭姓「李」或「王」的學生基本資料。 【解答】 《參見7-1.2 》 【查詢結果】 【解答】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT * FROM 學生資料表 WHERE 姓名 Like '[李王] %';

【實例3】 在「學生資料表」中查詢姓名開頭不是姓「李」或「王」的學生基本資料。 【解答】 《參見7-1.2 》 【查詢結果】 【解答】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT * FROM 學生資料表 WHERE 姓名 NOT Like '[李王] %';

7-6.2 IN集合條件 【定義】IN為集合運算子，只要符合集合之其中一個元素，將會被選取。 【使用時機】篩選的對象是兩個或兩個以上。 【實例 1】 在「選課資料表」中查詢學生任選一個「課程代號為C004 或 課程代號為C005」的學生的「學號、課號及成績」 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 課號 In ('C004','C005')

7-6.2 IN集合條件<續> 註：以上的WHERE 課號 In ('C004','C005') 亦可寫成如下： SQL指令 SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 課號 In ('C004','C005') 註：以上的WHERE 課號 In ('C004','C005') 亦可寫成如下： WHERE 課程代號='C004' OR 課程代號='C005'

請在「學生資料表」中，列出 學號為S0001~S0003的同學之「學號，姓名及系碼」 【實例2】 請在「學生資料表」中，列出 學號為S0001~S0003的同學之「學號，姓名及系碼」 【解答 】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號,姓名,系碼 FROM 學生資料表 WHERE 學號 In ('S0001', 'S0002', 'S0003')

請在「學生資料表」中，列出 系碼不是「D001」及「D002」的同學之「學號，姓名及系碼」 【實例3】 請在「學生資料表」中，列出 系碼不是「D001」及「D002」的同學之「學號，姓名及系碼」 【解答 】 SQL指令 use ch7_DB SELECT 學號,姓名,系碼 FROM 學生資料表 WHERE NOT 系碼 In ('D001', 'D002‘) 【查詢結果】

7-6.3 Between／And範圍條件 【定義】 Between/And是用來指定一個範圍，表示資料值必須在最小值(含)與最大值(含)之間的範圍資料。註：等同於「最小值≧ And ≦最大值」 【實例】在「選課資料表」中查詢成績60到90 之間的學生的「學號、 課號及成績」 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績 Between 60 And 90 等同於 成績>=60 And成績<=90

【隨堂練習1】 在「選課資料表」中查詢修課號為C004或C005的成績60到90之間的 學生的「學號、課號及成績」 <利用Between/And> 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 課號 In ('C004','C005') AND 成績 Between 60 And 90

【隨堂練習2】 在「選課資料表」中查詢修課號為C004或C005的成績60到90之間的 學生的「學號、課號及成績」<利用比較運算式> 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 課號 In ('C004','C005') AND 成績>=60 And 成績<=90;

【隨堂練習3】 在「選課資料表」中查詢不是介於成績60到90之間的學生的「學號、課號及成績」 【解答 】 use ch7_DB 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績 NOT Between 60 And 90; 【查詢結果】

7-7 使用「算術運算子」　 【定義】 在Where條件式中還提供算術運算的功能，讓使用者可以設定某些欄位的數值作四則運算。其常用的算術運算子如表6-8所示： 運算子 功能 例子 執行結果 ┼ (加) A與B兩數相加 14+28 42 ─ (減) A與B兩數相減 28-14 14 ＊ (乘) A與B兩數相乘 5*8 40 ／ (除) A與B兩數相除 10/3 3.33333333…. % (餘除) A與B兩數相除後，取餘數 10 % 3 1

【實例】在「選課資料表」中查詢學生成績乘1.2倍後還尚未達70分的學生顯示「學號、課程代號及成績」 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績*1.2<70

7-8 使用「聚合函數」　 【定義】 在SQL中提供聚合函數來讓使用者統計資料表中數值資料的最大值、最小值、平均值及合計值等等。其常用的聚合函數的種類如表6-9所示： 聚合函數 說明 Count(*) 計算個數函數 Count(欄位名稱) 計算該欄位名稱之不具NULL值列的總數 Avg 計算平均函數 Sum 計算總合函數 Max 計算最大值函數 Min 計算最小值函數

7-8.1 記錄筆數(Count) 【定義】COUNT函數是用來計算橫列記錄的筆數。 【實例1 】 在「學生資料表」中查詢目前選修課程的全班人數 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT Count(*) AS 全班人數 FROM 學生資料表

【隨堂練習1】 在「選課資料表」中查詢己經有選課，並且沒有缺考的「筆數」 【解答 】 use ch7_DB 【查詢結果】 SQL指令 SELECT Count(*) AS 全班人數 FROM 選課資料表; 【查詢結果】

【隨堂練習2】 在「選課資料表」中查詢己經的「成績」記錄的筆數 【解答 】 use ch7_DB 【查詢結果】 SQL指令 use ch7_DB SELECT Count(成績) AS 有成績總筆數 FROM 選課資料表; 【查詢結果】 註： Count(欄位名稱)計算該欄位名稱之不具NULL值列的總數

7-8.2 平均數(AVG) 【定義】AVG函數用來傳回一組記錄在某欄位內容值中的平均值。 【實例】在「選課資料表」中查詢有選修「課程代號為C005」的全班 平均成績 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT AVG(成績) AS 資料庫平均成績 FROM 選課資料表 WHERE 課號='C005'

【隨堂練習1】 【實例】在「選課資料表」中查詢「學號為S0001」的各科總平均成績 【解答 】 use ch7_DB 【查詢結果】 SQL指令 use ch7_DB SELECT AVG(成績) AS 平均成績 FROM 選課資料表 WHERE 學號='S0001'; 【查詢結果】

【隨堂練習2】 在「選課資料表」中計算每一位同學所修之科目的平均成績 《參見7-1.2 》 【查詢結果】

【隨堂練習2】 在「選課資料表」中計算每一位同學所修之科目的平均成績 【解答】 《參見7-1.2 》 【查詢結果】

【隨堂練習2】 在「選課資料表」中計算每一位同學所修之科目的平均成績 【解答】 《參見7-1.2 》 【查詢結果】 use ch7_DB 【解答】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, AVG(成績) AS 平均成績 FROM 選課資料表 GROUP BY 學號

【隨堂練習3】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的總平均成績。 【隨堂練習3】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的總平均成績。 【執行結果】

【隨堂練習3】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示各科目的總平均成績。 【解答】 【隨堂練習3】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示各科目的總平均成績。 【解答】 【執行結果】 SQL指令 SELECT AVG(資料庫) AS 資料庫平均成績, AVG(資料結構) AS 資料結構平均成績, AVG(程式設計) AS 程式設計平均成績 FROM 學生成績表;

【隨堂練習4】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習4】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示「一心、二聖、三多」三位同學之「資料庫」的總平均成績。 【解答】 【執行結果】

【隨堂練習4】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習4】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示「一心、二聖、三多」三位同學之「資料庫」的總平均成績。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT AVG(資料庫) AS 三位同學的資料庫平均成績 FROM 學生成績表 WHERE 姓名 IN('一心','二聖','三多');

7-8.3 總和(Sum) 【定義】SUM函數是用來傳回一組記錄在某欄位內容值的總和。 【實例】在「選課資料表」中查詢有選修「課程代號為C005」的全班 總成績 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT SUM(成績) AS 資料庫總成績 FROM 選課資料表 WHERE 課號='C005'

【隨堂練習1】 【實例】在「選課資料表」中查詢「學號為S0001」的各科總成績 【解答 】 use ch7_DB 【查詢結果】 SQL指令 SELECT SUM(成績) AS 總成績 FROM 選課資料表 WHERE 學號='S0001'; 【查詢結果】

【隨堂練習2】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的總成績。 【執行結果】

【隨堂練習2】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的總成績。 【隨堂練習2】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的總成績。 【解答】 【執行結果】 SQL指令 use ch7_hwDB SELECT SUM(資料庫) AS 資料庫總成績 FROM 學生成績表;

7-8.4 最大值(Max) 【定義】MAX函數用來傳回一組記錄在某欄位內容值中的最大值。 【實例】在「選課資料表」中查詢有選修「課程代號為C005」的全班成績最高分 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT MAX(成績) AS 資料庫最高分 FROM 選課資料表 WHERE 課號='C005'

【隨堂練習1】 【實例】在「選課資料表」中查詢「成績介於60~80」中最高分為何？ 【解答 】 use ch7_DB 【查詢結果】 SQL指令 use ch7_DB SELECT MAX(成績) AS 資料庫成績介於60至80之最高分 FROM 選課資料表 WHERE 成績 Between 60 And 80; 【查詢結果】

7-8.5 最小值(Min) 【定義】MIN函數用來傳回一組記錄在某欄位內容值中的最小值。 【實例】在「選課資料表」中查詢有選修「課程代號為C005」的全班成績最低分 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT MIN(成績) AS 資料庫最低分 FROM 選課資料表 WHERE 課程代號='C005'

【隨堂練習1】 【實例】在「選課資料表」中查詢「及格成績」中最低分為何？ 【解答 】 use ch7_DB 【查詢結果】 SQL指令 SELECT MIN(成績) AS 「及格成績」中最低分 FROM 選課資料表 WHERE 成績 Between 60 And 100; 【查詢結果】

學號A0001同學的平均成績 每位同學的平均成績 每位同學的平均成績，並且排序 每個科目的平均成績 每個科目的最高分

學號A0001同學的平均成績 select avg(score) from student_course where studentId='A0001'

每位同學的平均成績 select studentId, avg(score) as average from student_course group by studentId

每位同學的平均成績，並且排序 select studentId, avg(score) as average from student_course group by studentId order by average

每個科目的平均成績 select courseId, avg(score) as average from student_course group by courseId

每個科目的最高分 select courseId, max(score) as average from student_course group by courseId

7-9 使用「排序及排名次」 【定義】 雖然撰寫SQL指令來查詢所須的資料非常容易，但如果顯示的結果筆數非常龐大而沒有按照某一順序及規則來顯示，可能會顯得非常混亂。還好SQL指令還有提供排序的功能。 其常用的排序及排名次的子句種類如表6-10所示： [註]ASC：Ascending(遞增) DESC：Descending(遞減) 聚合函數 說明 ORDER BY成績 Asc Asc 可以省略(由小至大) <預設模式> ORDER BY成績 Desc Desc 不可以省略(由大至小) Top N 取排名前N名 Top N Percent 取排名前N%名

7-9.1 Asc遞增排序 【定義】資料記錄的排序方式是由小至大排列。 【實例】在「選課資料表」中查詢全班成績由低到高分排序 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 ORDER BY 成績 Asc

【隨堂練習1】 在「選課資料表」中查詢全班成績由低到高分排序，但缺考的除外。 《參見7-1.2 》 【查詢結果】

【隨堂練習1】 在「選課資料表」中查詢全班成績由低到高分排序，但缺考的除外。 【解答 】 《參見7-1.2 》 【查詢結果】 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績 IS NOT NULL ORDER BY 成績;

【隨堂練習2】 在「選課資料表」中查詢有選修「課號為C005」的全班成績由低到高分排序 《參見7-1.2 》 【查詢結果】

【隨堂練習2】 在「選課資料表」中查詢有選修「課號為C005」的全班成績由低到高分排序 【解答 】 《參見7-1.2 》 【查詢結果】 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 課號='C005' ORDER BY 成績 Asc

【隨堂練習3】 在「選課資料表」中查詢有選修「課號為C005」的全班成績由低到高分排序，但缺考的除外。 《參見7-1.2 》 【查詢結果】

【隨堂練習3】 在「選課資料表」中查詢有選修「課號為C005」的全班成績由低到高分排序，但缺考的除外。 【解答 】 《參見7-1.2 》 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 課號='C005' AND 成績 IS NOT NULL ORDER BY 成績 Asc

7-9.2 Desc遞減排序 【定義】資料記錄的排序方式是由大至小排列。 【實例】在「選課資料表」中查詢的全班成績由高到低分排序 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 ORDER BY 成績 DESC

【隨堂練習1】 在「選課資料表」中查詢全班成績由高到低分排序，但缺考的除外。 【解答 】 《參見7-1.2 》 【查詢結果】 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 成績 IS NOT NULL ORDER BY 成績 DESC

【隨堂練習2】 在「選課資料表」中查詢「學號為S0004」的同學成績由高到低分排序 【解答 】 《參見7-1.2 》 【查詢結果】 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 學號='S0004' ORDER BY 成績 DESC

7-9.3 比較複雜的排序 【定義】指定一個欄位以上來做排序時，則先以第一個欄位優先排序， 7-9.3 比較複雜的排序　 【定義】指定一個欄位以上來做排序時，則先以第一個欄位優先排序， 當資料相同時，則再進行第二個欄位進行排序，依此類堆。 【實例】在「選課資料表」中查詢結果按照學號昇冪排列之後，再依 成績昇冪排列。 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 ORDER BY 學號,成績 欄位名稱之間必須要以「,(逗點)來做區隔」 依成績 低高 依學號 低高 未依成績 依學號

【隨堂練習1】 在「選課資料表」中查詢結果按照「學號」昇冪排列之後，再依「成績」降冪排列(亦即由高分到低分) 《參見7-1.2 》 【查詢結果】

【隨堂練習1】 在「選課資料表」中查詢結果按照「學號」昇冪排列之後，再依「成績」降冪排列(亦即由高分到低分) 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 ORDER BY 學號 ASC, 成績 DESC;

【隨堂練習2】　 在「選課資料表」中查詢有選修「學生為S0003與S0004」二位同學其結果按照「學號」昇冪排列之後，再依「成績」昇冪排列(亦即由低分到高分)《參見7-1.2 》 【查詢結果】

【隨堂練習2】　 在「選課資料表」中查詢有選修「學生為S0003與S0004」二位同學其結果按照「學號」昇冪排列之後，再依「成績」昇冪排列(亦即由低分到高分)《參見7-1.2 》【解答 】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號, 成績 FROM 選課資料表 WHERE 學號 IN('S0003','S0004') ORDER BY 學號, 成績;

7-9.4 Top N 【定義】資料記錄在排序之後，取排名前N名。 【使用時機】總筆數已知，例如：全班10人中取前三名 【實例】在「選課資料表」中查詢有選修「課程代號為C005」的5個同學中成績前二名的同學 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT TOP 2 * FROM 選課資料表 WHERE 課號='C005' ORDER BY 成績 DESC

【隨堂練習1】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習1】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為前三名的同學名單。 【執行結果】

【隨堂練習1】 【解答】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習1】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為前三名的同學名單。 【執行結果】 【解答】 SQL指令 use ch7_hwDB SELECT TOP 3 * FROM 學生成績表 ORDER BY 資料庫 DESC;

【隨堂練習2】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習2】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為後三名的同學名單。 【執行結果】

【隨堂練習2】 【解答】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習2】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為後三名的同學名單。 【執行結果】 【解答】 SQL指令 use ch7_hwDB SELECT TOP 3 * FROM 學生成績表 ORDER BY 資料庫 ASC;

7-9.5 Top N Percent 【定義】資料記錄在排序之後，取排名前N%名。 【使用時機】總筆數未知，例如：全班中的前30%是高分群學生 【實例】在「選課資料表」中查詢有選修「課程代號為C005」的5個同學中成績前30%的同學 【解答 】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT TOP 30 PERCENT * FROM 選課資料表 WHERE 課號='C005' ORDER BY 成績 DESC 若未指明ASC或DESC則系統自動選用ASC

【隨堂練習1】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習1】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為高分群(前30%)的同學名單。 【執行結果】

【隨堂練習1】 【解答】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習1】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為高分群(前30%)的同學名單。 【執行結果】 【解答】 SQL指令 use ch7_hwDB SELECT TOP 30 PERCENT * FROM 學生成績表 ORDER BY 資料庫 DESC;

【隨堂練習2】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習2】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為低分群(後30%)的同學名單。 【執行結果】

【隨堂練習2】 【解答】 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 【隨堂練習2】　 假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示： 請撰寫一段SQL指令來顯示全班「資料庫」的成績為低分群(後30%)的同學名單。 【執行結果】 【解答】 SQL指令 use ch7_hwDB SELECT TOP 30 PERCENT * FROM 學生成績表 ORDER BY 資料庫 ASC

7-10 使用「群組化」　 【定義】 利用SQL語言，我們可以將某些特定欄位的值相同的記錄全部組合起來，以進行群組化，接著就可以在這個群組內求出各種統計分析。 【語法】 Group By欄位1,欄位2,…,欄位n [Having 條件式] (1)Group By 可單獨存在，它是將數個欄位組合起來，以做 為每次動作的依據。 (2) [Having 條件式]是將數個欄位中以有條件的組合。 它不可以單獨存在。 (3) WHERE子句與HAVING子句之差別 WHERE子句 HAVING子句 執行順序 GROUP BY之前 GROUP BY之後 聚合函數 不能使用聚合函數 可以使用

(4) SQL的執行順序　

7-10.1 Group By 欄位 【定義】 Group By 可單獨存在，它是將數個欄位組合起來，以做為 每次動作的依據。 【語法】 說明：在Select的非聚合函數內容一定要出現在Group By中，因為 先群組化才能Select。 Select 欄位1，欄位2，聚合函數運算 From 資料表 Where 過濾條件 Group By 欄位1，欄位2

[實例1] 在「選課資料表」中，查詢每一位同學各選幾門科目。 《參見7-1.2 》 【解答】 【註】在Select所篩選的非聚合函數。 [實例1]　 在「選課資料表」中，查詢每一位同學各選幾門科目。 《參見7-1.2 》 【解答】 【註】在Select所篩選的非聚合函數。 例如：學號，一定會在 Group By後出現。 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, Count(*) AS 選科目數 FROM 選課資料表 GROUP BY 學號

[實例2] 在「選課資料表」中計算每一位同學所修之科目的平均成績 【解答】 《參見7-1.2 》 【查詢結果】 use ch7_DB [實例2]　 在「選課資料表」中計算每一位同學所修之科目的平均成績 【解答】 《參見7-1.2 》 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, AVG(成績) AS 平均成績 FROM 選課資料表 GROUP BY 學號

[實例3] 在「選課資料表」中，將每個課程的選修人數印出來，印出之結果並按課程代號由大到小排序 《參見7-1.2 》 【解答】 【查詢結果】 [實例3]　 在「選課資料表」中，將每個課程的選修人數印出來，印出之結果並按課程代號由大到小排序 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 課號, Count(*) AS 選課學生人數 FROM 選課資料表 GROUP BY 課號 ORDER BY 課號 DESC

[實例4] 在「選課資料表」中，將每個課程的選修人數及該科最高分數印出來，印出之結果並按課程代號由小到大排序 《參見7-1.2 》 【解答】 [實例4]　 在「選課資料表」中，將每個課程的選修人數及該科最高分數印出來，印出之結果並按課程代號由小到大排序 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 課號, Count(*) AS 選課學生人數, MAX(成績) AS 最高分成績 FROM 選課資料表 GROUP BY 課號 ORDER BY 課號

[實例5] 在「選課資料表」中，將每個課程的選修人數及該科平均分數印出來，印出之結果並按課程代號由小到大排序 《參見7-1.2 》 【解答】 [實例5]　 在「選課資料表」中，將每個課程的選修人數及該科平均分數印出來，印出之結果並按課程代號由小到大排序 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 課號, Count(*) AS 選課學生人數, AVG(成績) AS 平均成績 FROM 選課資料表 GROUP BY 課號 ORDER BY 課號

7-10.2 Having 條件式 【定義】Having 條件式是將數個欄位中以有條件的組合。 它不可以單獨存在。 【實例1 】在「選課資料表」中，計算所修之科目的平均成績，大於等 於70者顯示出來。 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, AVG(成績) AS 平均成績 FROM 選課資料表 GROUP BY 學號 HAVING AVG(成績)>=70

[實例2] 在「選課資料表」中，將選修課程在二科及二科以上的學生學號資料列出來。 《參見7-1.2 》 【解答】 【查詢結果】 [實例2]　 在「選課資料表」中，將選修課程在二科及二科以上的學生學號資料列出來。 《參見7-1.2 》 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, Count(*) AS 選修數目 FROM 選課資料表 GROUP BY 學號 HAVING COUNT(*)>=2

Where子句與 HAVING子句之差異 1. Where子句是針對未尚群組化的欄位來進行篩選。

7-11 使用「刪除重覆」　 【定義】 利用Distinct指令來將所得結果有重覆者，去除重覆。若有一學生選了3門課程，其學號只能出現一次。

7-11.1 ALL(預設)使查詢結果的 記錄可能重複 【定義】沒有利用Distinct指令 【實例】在「選課資料表」中，將有選俢課程的學生之學號、課程代號 印出來。 【解答】 【查詢結果】 SQL指令 use ch7_DB SELECT 學號, 課號 FROM 選課資料表 註：沒有利用Distinct指令時 ，產生重覆出現的現象。

7-11.2 DISTINCT使查詢結果的 記錄不重複出現 【定義】 如果使用DISTINCT句，則可以將所指定欄位中重複的資料去除掉之後再顯示。指定欄位的時候，可以指定一個以上的欄位，但是必須使用「,(逗點)」來區隔欄位名稱。 【DISTINCT的注意事項】 ①不允許配合COUNT(*)使用 ②允許配合COUNT(屬性)使用 ③對於MIN()與MAX()是沒有作用的

【實例】 在「選課資料表」中，將有選俢課程的學生之「學號」印出來。 【解答】 【查詢結果】 註：利用Distinct指令時，刪除重覆的現象。 【實例】　 在「選課資料表」中，將有選俢課程的學生之「學號」印出來。 【解答】 【查詢結果】 註：利用Distinct指令時，刪除重覆的現象。 如果沒有指定Distinct指令時，則預設值為ALL，其查詢結果會重複。 SQL指令 use ch7_DB SELECT DISTINCT 學號 FROM 選課資料表 SELECT 學號 FROM 選課資料表 GROUP BY 學號; 相同