第七章 SQL的查詢語言課程名稱：資料庫系統各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，

第七章 SQL的查詢語言課程名稱：資料庫系統各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，
今天所要為各位介紹的是第一章「資料結構導論」

本章學習目標 1.讓讀者瞭解SQL語言的各種使用方法。 2.讓讀者瞭解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-3 使用Select子句【定義】 Select是指在資料表中，選擇全部或部份欄位顯示出來，這就是所謂的「投影運算」。【格式】
From 資料表名稱

查詢全部欄位　【定義】是指利用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 學生成績表

請撰寫一段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】　假設有一個「學生成績表」，其目前的欄位名稱及內容如下所示：請撰寫一段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 …. % (餘除) 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 》【查詢結果】
【解答】《參見7-1.2 》【查詢結果】 SQL指令 use ch7_DB SELECT 學號, AVG(成績) AS 平均成績 FROM 選課資料表 GROUP BY 學號說明：本題必須要使用到分群的技術，請參考CH Group By 欄位

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

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

【隨堂練習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; 【查詢結果】

【老師上課動態展示】　【解答】在附書光碟的範例程式中，檔名為：ch7-8/ch7-8.sln

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

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

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

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

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

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

【隨堂練習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 學號; 相同

第七章 SQL的查詢語言課程名稱：資料庫系統各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，

Similar presentations

Presentation on theme: "第七章 SQL的查詢語言課程名稱：資料庫系統各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，"— Presentation transcript:

Similar presentations

About project

反馈

请登录

Auth with social network:

第 七 章 SQL的查詢語言 課程名稱：資料庫系統 各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，

Similar presentations

Presentation on theme: "第 七 章 SQL的查詢語言 課程名稱：資料庫系統 各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，"— Presentation transcript:

Similar presentations

About project

反馈

第七章 SQL的查詢語言課程名稱：資料庫系統各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，

Presentation on theme: "第七章 SQL的查詢語言課程名稱：資料庫系統各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」，"— Presentation transcript: