slide1
Download
Skip this Video
Download Presentation
第三章

Loading in 2 Seconds...

play fullscreen
1 / 157

第三章 - PowerPoint PPT Presentation


  • 102 Views
  • Uploaded on

第三章. 資料庫之資料選取查詢. 章節概要. 3.1 撰寫 SQL 指令 3.2 如何編輯及執行 SQL 指令 3.3 使用 SELECT 選取資料 3.4 使用 WHERE 設定條件選取資料 3.5 使用運算式及函數 3.6 使用聚合函數來統計資料  3.7 使用 GROUP BY 於資料群組化  3.8 使用 HAVING 子句篩選群組化資料  3.9 限定最大輸出筆數  3.10 關於 NULL. 3.1  撰寫 SQL 指令.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' 第三章' - aisha


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

第三章

資料庫之資料選取查詢

slide2
章節概要
  • 3.1 撰寫SQL指令
  • 3.2 如何編輯及執行 SQL 指令
  • 3.3 使用SELECT選取資料
  • 3.4 使用WHERE設定條件選取資料
  • 3.5 使用運算式及函數
  • 3.6 使用聚合函數來統計資料 
  • 3.7 使用GROUP BY於資料群組化 
  • 3.8 使用HAVING子句篩選群組化資料 
  • 3.9 限定最大輸出筆數 
  • 3.10 關於NULL
3 1 sql
3.1 撰寫SQL指令
  • SQL語言是一種非程序性語言(Non-procedure Language),是一種對資料庫逐次以一個個命令來操作資料庫的簡單語言。在SQL語法上和一般程式語言一樣,也有所謂的保留字或關鍵字(Keyword)。一般而言,保留字不可以用來當作資料表或欄位屬性的名稱。
sql mysql
SQL保留字(以MySQL為例)
  • alter
  • asc
  • between
  • cross
  • distinct
  • from
  • having
  • avg
  • case
  • else
  • for
  • and
  • char
  • constraint
  • desc
  • as
  • by
  • creat
  • describe
  • drop
  • exists
  • group
  • identified
sql mysql1
SQL保留字(以MySQL為例)(續)
  • if
  • jey
  • lock
  • natural
  • null
  • select
  • in
  • insert
  • is
  • keys
  • left
  • on
  • or
  • set
  • use
  • order
  • update
  • like
  • not
  • outer
  • table
  • to
  • unique
slide6
撰寫SQL指令(續)
  • 使用SQL時通常是以一個敘述或指令(Statement)來描述,並以分號(;)作為結束符號。
  • 執行:有些系統需使用 go 或 run
  • 在撰寫SQL敘述時,可以在保留字、資料表或欄位名稱之間加入空白或換行符號,以便於閱讀。
3 2 sql
3.2 如何編輯及執行 SQL 指令
  • 操作方式列舉如下:
    • A. 使用ODBC Viewer (可連接任何ODBC連線資料庫)
    • B. 使用MS Access 查詢精靈 (使用 QBE)
    • C. 使用SQL Server
    • D. 使用MySQL Query Browser
    • E. 使用 DB Visualizer
    • F. 使用Oracle SQL Developer
  • 例如:我們想要選取部門資料表(Departments)中的部門編號(DepartmentID)及部門名稱(Name),則下達如下的SQL指令即可:(SQL指令下一節會詳細說明)
    • SELECT DepartmentID, NameFROM Departments;
a odbc viewer
A. 使用ODBC Viewer
  • 首先請自行安裝 ODBC Viewer (可連接任何ODBC連線資料庫),安裝程式見於光碟 software 目錄下的 odbcv310.exe 或自行到網站下載最新版的ODBC Viewer (http://www.slik.co.nz )。
  • 請執行[開始]/[程式集]/[Slik software]下的ODBC Viewer。即如下圖。(注意:若尚未建立 orders_access ODBC連線名稱,請參考前章的ODBC設定說明。)
slide11

選取位於[機器資料來源]頁中的orders_access或orders_mssql或orders_mysql。選取位於[機器資料來源]頁中的orders_access或orders_mssql或orders_mysql。

b ms access
B. 使用MS Access 查詢精靈
  • 首先開啟 orders.mdb,即如下圖所示:
slide17

選取資料表Departments,然後按[新增]即可新增欲選取的資料表。選取資料表Departments,然後按[新增]即可新增欲選取的資料表。

slide20

選取[檢視]下的[SQL檢視]即可觀看相關的SQL指令。選取[檢視]下的[SQL檢視]即可觀看相關的SQL指令。

SQL 檢視的結果指令。

註:以上示範的查詢建立方法稱為依範例查詢(Query By Example; QBE)。

c sql server
C. 使用SQL Server(一)
  • 法一:使用 SQL Query Analyzer
  • 方法二:使用 Enterprise Manager
  • 首先開啟 SQL Query Analyzer (SQL 查詢分析器),即如下:
slide22

首先選取要使用的資料庫為 orders,接著輸入 SQL 指令,最後按[ ! ]執行鈕即可。

slide23

可以選取[顯示評估的執行計劃]鈕以檢視該SQL指令是如何地被執行。可以選取[顯示評估的執行計劃]鈕以檢視該SQL指令是如何地被執行。

c sql server1
C. 使用SQL Server(二)
  • 以下接著示範如何於 Enterprise Manager 中下達 SQL 指令。首先開啟SQL Server Enterprise Manager 如下:
slide25

打開資料庫列表中的 orders 資料庫,接著選取 Departments 資料表,並按滑鼠右鍵,並選取查詢。

slide27

可以於別名欄上按滑鼠右鍵並選取刪除以刪除不適用的別名,或自行更改別名。可以於別名欄上按滑鼠右鍵並選取刪除以刪除不適用的別名,或自行更改別名。

slide28

這是刪除掉多餘的資料行及別名後重新執行的結果。這是刪除掉多餘的資料行及別名後重新執行的結果。

注意:我們也可以直接在 SQL 指令區中直接輸入要執行的 SQL 指令。

c sql server2
C. 使用SQL Server(三)
  • 以下示範如何於SQL Server 2005/2008 的 SQL Server Management Studio Express 中下達 SQL 指令。首先開啟SQL Server Management Studio Express 如下:
slide30

首先點選orders資料,然後按「新增查詢(N)」,以開啟SQL編輯器。最後於編輯區內輸入查詢指令,再按「執行(X)」即可。

QBE查詢介面開啟方式:點擊工具列中的 或按快捷鍵 Ctrl+Shift+Q或選取「查詢」功能表下的「在編輯器中設計查詢」。

d mysql query browser
D. 使用MySQL Query Browser
  • 首先執行 MySQL Query Browser 程式
  • 其後相關的執行操作如下示範:
slide35

於 SQL 指令輸入區輸入SQL指令,接著按執行鍵即可。(注意:資料表和欄位名稱是不分大小寫的。)

e db visualizer
E. 使用DB Visualizer
  • 首先執行 DB Visualizer程式。
  • 其後相關的執行操作程序如下:
    • 建立資料庫連線名稱。
    • 設定資料庫驅動程式(使用JDBC)。
    • 建立SQL查詢指令和執行SQL指令。
f oracle sql developer
F. 使用Oracle SQL Developer
  • 首先執行 Oracle SQL Developer 程式。
  • 其後相關的執行操作如下示範:
3 3 select
3.3 使用SELECT選取資料
  • 最基本的SELECT指令是由SELECT子句(SELECT Clause)和FROM子句(FROM Clause)所組成的,其指令格式如下:
    • 【SELECT指令格式一】 SELECT 欄位列表FROM 資料表來源列表
select
使用SELECT選取資料(續)
  • SELECT的部分設定/列舉要選取的資料表欄位名稱,而FROM的部分則指定資料表來源名稱。
  • 例如:我們想要選取部門資料表(Departments)中的部門編號(DepartmentID)及部門名稱(Name),則下達如下的SQL指令即可:
    • SELECT DepartmentID, NameFROM Departments;
  • 執行結果如下圖:
select1
使用SELECT選取資料(續)
  • 由於我們時常會想要選取資料表中的所有欄位的資料,此時我們可以使用 SELECT * 來選取所有的欄位。其指令格式如下:
    • 【SELECT指令格式二】 SELECT * FROM 資料表來源名稱
select2
使用SELECT選取資料(續)
  • 我們想要選取部門資料表(Departments)中的所有欄位,則下達如下的SQL指令即可:
    • SELECT *FROM Departments;
  • 執行結果如下圖:
select3
使用SELECT選取資料(續)
  • 設定在 SELECT 子句中的欄位名稱可以在空白之後加入欄位的別名(Alias)或利用 AS 來設定別名(建議使用 AS 而不要使用空白)。其SQL指令格式如下:
    • 【SELECT指令格式三】SELECT 欄位名稱一 AS 別名一, 欄位名稱二 AS別名二, ...FROM 資料表來源名稱
select4
使用SELECT選取資料(續)
  • 如將 DepartmentID 欄位命名其別名為 Department,則下達指令如下:
    • SELECT DepartmentID AS Department, NameFROM Departments;
  • 若有需要,我們也可以取中文別名,如:
    • SELECT DepartmentID AS 部門代號, Name AS 部門名稱FROM Departments;
    • SELECT DepartmentID AS \'部門代號\', Name AS \'部門名稱\'FROM Departments;
  • 執行結果如下圖:
select5
使用SELECT選取資料(續)
  • 有時候執行一個SQL指令可能會產生多筆相同的資料,如下達查詢部門位址列表的SQL指令:
    • SELECT LocationFROM Departments;
  • 則會出現如下的結果:
select6
使用SELECT選取資料(續)
  • 此時我們會想要指定相同的資料只列出一次,則可於欄位列表之前加上 DISTINCT這個識別字。即使用如下格式的SQL指令
    • 【SELECT指令格式四】SELECT DISTINCT 欄位名稱一 AS 別名一, 欄位名稱二 AS 別名二, ……

FROM 資料表來源名稱

  • 如上述的 SQL 指令可更改為:
    • SELECT DISTINCT LocationFROM Departments;
  • 此時執行的結果如下:
3 4 where
3.4 使用WHERE設定條件選取資料
  • 設定簡單的條件
    • 在SQL指令中,我們可以使用WHERE子句來過濾資料,只取出合乎特定條件的資料,即於WHERE子句中指定條件運算式。
    • 在WHERE子句中設定條件時需用到比較運算子(和一般的程式語言相似)。
slide58
表3.3 比較運算子列表

註:文字用單引號括起來。

slide59
設定簡單的條件
  • 我們可以使用的SQL指令格式如下:
    • 【SELECT指令格式五】SELECT 欄位名稱一 AS 別名一, 欄位名稱二 AS 別名二,……FROM 資料表來源名稱WHERE 條件表示式
  • 例如我們想要列出部門代號為10的資料,則可下達如下SQL指令:
    • SELECT *FROM DepartmentsWHERE DepartmentID = 10;
slide61
設定簡單的條件(續)
  • 我們想列出所有部門代號為20(業務部門)的所有員工的資料,則可下達如下指令:
    • SELECT *FROM EmployeesWHERE DepartmentID = 20;
  • 執行的結果如下:
is null
設定簡單的條件(IS NULL)
  • 我們可以發現某些業務員的佣金未知,如上圖中的員工1002和1008。
  • 假如我們想要選取佣金未知(即為NULL)的所有員工時應下達如下的指令:
    • SELECT *FROM EmployeesWHERE Commission IS NULL;
  • 其執行的結果如下:
slide65
設定簡單的條件(無效的指令)
  • 我們不可以使用如下的指令:(無效的指令)
    • SELECT *FROM EmployeesWHERE Commission = NULL;
    • -- ‘’ 為兩個 ‘ 符號SELECT *FROM EmployeesWHERE Commission = \'\';
slide66
設定簡單的條件(續)
  • 由於 NULL 值代表是未知、未定的資料,故任何一般比較的符號(=、>、>=、<、<= ...)和NULL運算的結果皆為假(FALSE)。所以 WHERE Commission = NULL 是不正確的用法。
  • 我們若想要選出所有佣金不為NULL的員工資料,應下達如下的指令:
    • SELECT*FROM EmployeesWHERE Commission IS NOT NULL;
slide68
設定複雜的條件
  • 在WHERE子句中可以設定由多個條件組合而成的複雜條件式。此時,我們可使用邏輯運算子(Logical Operator)來組合比較條件運算式,以設定出含多個條件的複雜條件式。
  • SQL中可運用的邏輯運算子列表如表3.4。
slide70
設定複雜的條件(續)
  • AND是用來設定「(條件一) 且(條件二) 須同時成立」這樣子的條件的運算子。
  • OR 是用來設定「(條件一) 或(條件二) 須成立」這樣子的條件的運算子;換言之,用來設定「(條件一) 或者 (條件二) 任一條件成立」這樣子的條件的運算子。
  • NOT 是用來設定否定的條件,即「不是(條件)」這樣子的條件的運算子。
slide71
設定複雜的條件(續)
  • 我們想選取出職務為業務且稱呼為先生的男性經理之員工編號、姓名和年薪,則可下達如下的指令:
    • SELECT EmployeeID, Name, Salary, TitleFROM EmployeesWHERE Title =\'業務\' AND TitleOfCourtesy = \'先生\';
  • 其執行的結果為如下:
slide73
設定複雜的條件(續)
  • 例如我們想找出職稱為業務或者佣金不為NULL的員工的員工編號、姓名、年薪、職稱和佣金資料。則可下達如下指令:
    • SELECT EmployeeID, Name, Salary, Title, CommissionFROM EmployeesWHERE Title = \'業務\' OR Commission IS NOT NULL;
  • 其執行的結果如下:
slide75
設定複雜的條件(續)
  • 例如我們想找出非職稱為業務的員工資料,則可下達如下的指令:
    • SELECT *FROM EmployeesWHERE NOT (Title = \'業務\');
  • 其執行的結果如下:
slide77
設定複雜的條件(續)
  • 注意:NOT 後的那一對括號是必要的。假如改成如下,則執行的結果可能為0筆資料(有些資料庫系統)。
    • -- 可能不正確的 SQL 指令SELECT *FROM EmployeesWHERE NOT Title = \'業務\';
slide78
設定複雜的條件(續)
  • 當然我們想找出非業務的員工資料,也以可下達如下的指令:
    • SELECT *FROM EmployeesWHERE Title <> \'業務\';
  • 或者(非標準SQL語法)
    • SELECT *FROM EmployeesWHERE Title != \'業務\';
slide79
設定模糊的條件與限制範圍
  • 在WHERE子句中設定條件時,我們可以指定模糊條件和限制資料範圍。此時即需要使用到模糊條件。如:
    • LIKE
    • BETWEEN … AND …
    • IN
slide81
設定模糊的條件與限制範圍(續)
  • 例如我們想找出所有員工的職稱中含有 ‘經理’ 兩個字的員工(職稱以經理字眼結尾者),則可下達如下的模糊指令:
    • SELECT *FROM EmployeesWHERE Title LIKE \'%經理\';
  • 執行的結果如下:
slide83
設定模糊的條件與限制範圍(續)
  • 例如我們想找出姓氏為 \'張\' 的所有員工(以張字開頭的員工姓名),則可下達指令如下:
    • SELECT *FROM EmployeesWHERE Name LIKE \'張%\';
  • 其執行結果如下:
slide85
設定模糊的條件與限制範圍(續)
  • 我們想找出員工的英文字為 \'M___\' (三個底線),即M開頭共計有四個字元的英文姓名,則可下達如下的指令:
    • SELECT *FROM EmployeesWHERE EngName LIKE \'M___\';
  • 其執行的結果如下:
slide87
設定模糊的條件與限制範圍(續)
  • 例如我們想找出員工的英文字含有 \'ar\' 這兩字元的員工,則可下達如下的指令:
    • SELECT *FROM EmployeesWHERE EngName LIKE \'%ar%\';
  • 其執行的結果如下:
slide89
設定模糊的條件與限制範圍(續)
  • 如我們想找出所有員工的年薪介於420000~540000之間的所有員工,則此時可以利用BEWTEEN...AND運算,即可下達如下的指令:
    • SELECT *FROM EmployeesWHERE Salary BETWEEN 420000 AND 540000;
  • 其執行的結果如下:
slide91
設定模糊的條件與限制範圍(續)
  • 當然,我們也可以把上述的需求撰寫成如下的指令:
    • SELECT *FROM EmployeesWHERE Salary >= 420000 AND Salary <= 540000;
  • 然而使用 BEWTEEN …AND 比較容易被了解的。
slide92
設定模糊的條件與限制範圍(續)
  • 在某些埸合裡,我們會想用列舉的方式選取資料,例如:部門代號為 10,20,30 的資料,或如某一代碼為 ‘A’, ‘B’, ‘C’ 其中之一。此時,我們即可使用IN運算子於條件之中。
  • 如我們想列出員工代號為1002及1005的部屬列表(員工編號、姓名、上司員工編號),即可下達如下的指令:
    • SELECT EmployeeID AS \'員工編號\', Name AS \'姓名\', SupervisorID AS \'上司之員工編號‘FROM EmployeesWHERE SupervisorID IN (1002, 1005);
  • 其執行的結果如下:
slide94
設定模糊的條件與限制範圍(續)
  • 我們想列出英文姓名為 \'Mary\'、 \'Marua\'、 \'Mike\' 和 \'Linda\' 等人的資料,則可下達如下的指令:
    • SELECT *FROM EmployeesWHERE EngName IN (\'Mary\', \'Marua\', \'Mike\', \'Linda\');
  • 其執行的結果如下:
slide96
進行資料排序
  • 關聯式資料庫中的資料是由多列資料所組成的,而這些資料列的儲存順序並不具有特殊的意義。
  • 一般而言,資料列的順序就是依資料存入(新增)至資料庫時的先後次序。
slide97
進行資料排序(續)
  • 若要對選取出來的資料列進行排序,那麼我們可以用ORDER BY子句來設定排序的方式。此時我們可以使用如下格式的SQL指令:
    • 【SELECT指令格式六】SELECT 欄位名稱一 AS 別名一, 欄位名稱二 AS 別名二, ...FROM 資料表來源名稱WHERE 條件表示式ORDER BY 排序欄位名稱一 [ASC|DESC],排序欄位名稱二 [ASC|DESC], ...
slide98
進行資料排序(續)
  • 例如我們想列出所有的部門資料,而且依部門代號由大到小排序,則可下達如下的指令:
    • SELECT *FROM DepartmentsORDER BY DepartmentID DESC;
  • 其執行的結果如下:
slide100
進行資料排序(續)
  • 我們想列出員工的部分資料(部門代號、員工編號、姓名、英文名字),而列出時依照部門代號由小到大及員工編號由小到大的方式排序。則可下達如下的指令:
    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY DepartmentID, EmployeeID
  • 其執行的結果如下:
slide102
進行資料排序(續)
  • 我們想要依部門代號由大到小(主排序)及員工編號由小到大(次要排序),則可將上述指令更改為如下即可:
    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY DepartmentID DESC, EmployeeID ASC
  • 則其執行的結果變成如下:
slide103

資料先依部門代號由大到小排序,再依員工編號排序由小到大。資料先依部門代號由大到小排序,再依員工編號排序由小到大。

slide104
進行資料排序(續)
  • 對於欲排序的欄位名稱,我們可以直接標明其欄位編號(從1開始),而無須列出欄位名稱。
  • 例如上述的SQL指令可以簡寫成:
    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY 1 DESC, 2
slide105
3.5 使用運算式及函數
  • 在SQL中我們也可以使用運算式(Expression)和使用資料庫內建的函數(Function)等功能,使用運算子(Operator)和函數對指定的數值或欄位的值進行加工處理。
  • 而算術運算子可以對數字型(Numeric)的資料進行運算,其運算的方式有四則運算及取餘數(模數)等。
  • 算術運算子列表如下:
slide107
使用運算式及函數(續)
  • 我們想計算訂單編號10251的貨品價格明細(產品編號、單價、數量、總價)(資料檔為OrderDetails訂單明細檔),則可下達如下的指令:
    • SELECT ProductID AS \'產品編號\', UnitPrice AS \'單價\', Quantity AS \'數量\',UnitPrice*Quantity AS \'總價\' FROM OrderDetailsWHERE OrderID = 10251

(Oracle用 "總價")

  • 以下則是執行的結果:
slide109
使用運算式及函數(續)
  • 除了數學運算之外,我們也可以對整數的欄位資料或整數數值進行位元(Bitwise)運算。
  • 依DBMS而異,不一定有支援此項功能。
  • MySQL的位元運算和C/C++語言一樣,如下列表:
slide111
使用運算式及函數(續)
  • 若我們想要得知12和24之間各項位元運算的結果,則可以下達一個不含FROM子句(沒有用到)的SQL指令,如下:
    • SELECT 12 & 24, 12 | 24, 12 ^ 24
  • 注意:Oracle 的SELECT指令限定一定要有FROM子句,習慣上,執行一般運算會指定資料表為DUAL。如:SELECT 1+2 FROM DUAL
  • 其執行的結果如下:
slide113
使用運算式及函數(續)
  • 各家RDBMS所提供的功能與函數的名稱不盡相同,而且函數的數量也是不盡相同。使用上大致可分為如下幾個類別:
    • 數學函數:根據函數的參數輸入值執行計算,然後傳回數值。
    • 字串函數:在字串輸入值(CHAR/VARCHAR)上執行一個運算 ,並傳回一個字串值或數值。
    • 日期與時間函數:執行日期與時間輸出值的運算,並傳回字串、數字或日期與時間值。
slide116
使用運算式及函數(續)
  • 例如:
    • SELECT ROUND(12.5,0), LEAST(\'A\', \'B\', \'C\'), RAND(0), SIGN(-4)
  • 其執行的結果如下:
slide117

=\'ABCDEF\'

LEFT 可以用 SUBSTR(str, 1, N) 取代。(Oracle無LEFT)

slide118

REVERSE(‘AB張三’) = ‘三張BA’

SUBSTR

RIGHT 可以用 SUBSTR(str, LENGTH(str)+1-N) 取代。(Oracle無RIGHT)

slide119
使用運算式及函數(續)
  • 我們想要列出部門10的所有員工的姓名、生日、生年、生月與日子部分等資料,則可下達如下的指令:
    • SELECT Name, BirthDate, LEFT(BirthDate, 4), MID(BirthDate, 6, 2), RIGHT(BirthDate, 2)FROM EmployeesWHERE DepartmentID = 10
  • 以下為執行的結果:
slide121
使用運算式及函數(續)
  • 我們想要列出部門編號10的員工編號、員工姓名、英文名字、中英姓名組合名稱,則可下達如下指令:(MySQL/ORACLE)
    • SELECT EmployeeID, Name, EngName,CONCAT(Name,\' - \',EngName) AS CombinedNameFROM EmployeesWHERE DepartmentID = 10
slide123
使用運算式及函數(續)
  • 而於 SQL Server/Access則可下達如下指令:
    • SELECT EmployeeID, Name, EngName, Name + \' - \' + EngName AS CombinedNameFROM EmployeesWHERE DepartmentID = 10
  • 以下則為常用的日期與時間函數方面的部分列表。
slide125
使用運算式及函數(續)
  • 計算部門代號10之員工雇用滿三年的日期,於MySQL之下可下達如下的指令:
    • SELECT Name, HireDate, DATE_ADD(HireDate, INTERVAL 3 YEAR)FROM EmployeesWHERE DepartmentID=10
  • 而於 SQL Server下則可下達如下的指令:
    • SELECT Name, HireDate,DATEADD(year,3,HireDate)FROM EmployeesWHERE DepartmentID=10
slide126
3.6 使用聚合函數來統計資料
  • SQL的聚合函數或彙總函數(Aggregate Functions)可用來進行資料表欄位的統計分析,可以取得筆數、平均值及總計等各式的統計量。聚合函數列表如下:
slide128
使用聚合函數來統計資料(續)
  • 我們想取得員工資料檔的總筆數,則可下達如下的指令:
    • SELECT COUNT(*)FROM Employees;
  • 例如我們想要取得員工的總人數、最低/最高/總計/平均薪資及標準差,則可下達如下的指令:
    • SELECT COUNT(*) AS \'員工數\', MIN(Salary) AS \'最低薪資\', MAX(Salary) AS \'最高薪資\', SUM(Salary) AS \'薪資總計\', AVG(Salary) AS \'平均薪資\', STDDEV(Salary) AS \'標準差\'FROM Employees;
  • 以下為執行的結果:
slide130
使用聚合函數來統計資料(續)
  • 我們想要取得部門代號為10的員工的總人數、最低/最高/總計/平均薪資及標準差,則可下達如下的指令:
    • SELECT COUNT(*) AS \'員工數\', MIN(Salary) AS \'最低薪資\', MAX(Salary) AS \'最高薪資\', SUM(Salary) AS \'薪資總計\', AVG(Salary) AS \'平均薪資\', STDDEV(Salary) AS \'標準差\'FROM EmployeesWHERE DepartmentID = 10;
  • 其執行的結果則為如下:

註:SQL Server/Access 使用 STDEV( Salary )

slide132
使用聚合函數來統計資料(續)
  • 想列出佣金欄位的總筆數、非NULL值欄位數及不同數值的資料數,則可以下達如下的指令:
    • SELECT COUNT(*), COUNT(Commission), COUNT(DISTINCT Commission)FROM Employees;
  • 以下則是執行的結果:
3 7 group by
3.7 使用GROUP BY於資料群組化
  • 我們可以在SQL指令中使用GROUP BY子句來將資料群組化,然後使用聚合函數來取得我們需要的統計值。此時SQL指令的格式如下:
    • 【SELECT指令格式七】SELECT 表示式一 AS 別名一, 表示式二 AS 別名二, ...FROM 資料表來源名稱WHERE 條件表示式GROUP BY 群組名稱一, 群組名稱二, ...ORDER BY 排序欄位名稱一 [ASC|DESC], 排序欄位名稱二 [ASC|DESC], ...
group by
使用GROUP BY於資料群組化(續)
  • 使用GROUP BY子句時必須設定要進行群組化的欄位名稱,接著,我們就可以對群組分類後的資料進行聚合統計來取得筆數、最小值、最大值、總計、平均值和標準差等等統計量。
group by1
使用GROUP BY於資料群組化(續)
  • 我們想要取得各部門(依部門群組化)的員工的總人數、最低/最高/總計/平均薪資及標準差,則可下達如下的指令:
    • SELECT DepartmentID, COUNT(*) AS \'員工數\', MIN(Salary) AS \'最低薪資\', MAX(Salary) AS \'最高薪資\', SUM(Salary) AS \'薪資總計\', AVG(Salary) AS \'平均薪資\', STDDEV(Salary) AS \'標準差\'FROM EmployeesGROUP BY DepartmentID;
  • 其執行的結果則為如下:
group by2
使用GROUP BY於資料群組化(續)
  • 注意:使用群組化GROUP BY子句時,SELECT中可用的選取清單只能用聚合函數及和GROUP BY中指定的欄位名稱相關的表示式。
  • 例如下列的SQL指令是錯誤的(無效的):
    • SELECT DepartmentID, Name, COUNT(*) AS \'員工數\', MIN(Salary) AS \'最低薪資\', MAX(Salary) AS \'最高薪資\', SUM(Salary) AS \'薪資總計\', AVG(Salary) AS \'平均薪資\'FROM EmployeesGROUP BY DepartmentID;
3 8 having
3.8 使用HAVING子句篩選群組化資料
  • 有時候我們會想對已使用GROUP BY群組化後的資料列進行篩選,再設定一些條件以過濾資料。
    • 【SELECT指令格式八】SELECT 表示式一 AS 別名一, 表示式二 AS 別名二, ...FROM 資料表來源名稱WHERE 條件表示式GROUP BY 群組名稱一, 群組名稱二, ...HAVING群組或聚合的篩選條件ORDER BY 排序欄位名稱一 [ASC|DESC],排序欄位名稱二 [ASC|DESC], ...
having
使用HAVING子句篩選群組化資料(續)
  • HAVING子句和WHERE子句的差別在於HAVING子句是對群組化後的資料進行篩選,而WHERE子句則是對尚未群組化的資料進行篩選。
  • 對於整個SELECT指令的執行設計流程如圖 3.2 所示。
having1
使用HAVING子句篩選群組化資料(續)
  • 我們想要列出各部門(依部門群組化)的員工的總人數、最低/最高/總計/平均薪資及標準差,但是限制部門員工總人數須達5人以上(含),則可下達如下指令。

SELECT DepartmentID AS \'部門代號\', COUNT(*) AS \'員工數\', MIN(Salary) AS \'最低薪資\', MAX(Salary) AS \'最高薪資\', SUM(Salary) AS \'薪資總計\', AVG(Salary) AS \'平均薪資\', STDDEV(Salary) AS \'標準差\'FROM EmployeesGROUP BY DepartmentIDHAVING COUNT(*) >= 5;

  • 其執行的結果則如下:
slide144
3.9 限定最大輸出筆數
  • 我們有時候會想限定資料輸出量,尤其用於開發WEB化(網頁式)的資訊系統時。然而這個功能並未列入標準的SQL語法之中,因而各家RDBMS系統大都不相同。
  • 於SQL Server/Access中,我們可於用TOP n來選取結果資料集的前n筆資料錄,或用TOP n PERCENT來選取結果資料集的前n個百分比的資料錄。例如選取員工資料的指令為:
    • SELECT *FROM Employees;
slide145
限定最大輸出筆數(續)
  • 那麼只要選取前5筆員工資料,可下達如下的指令:(SELECT之後加上TOP 5)
    • SELECT TOP 5 *FROM Employees;
  • 假如要選取前5%的員工資料,可下達如下的指令:(SELECT之後加上TOP 5 PERCENT)
    • SELECT TOP 5 PERCENT *FROM Employees;
slide146
限定最大輸出筆數(續)
  • 要選取的資料要剔除相同的資料錄,那麼SQL指令改成如下樣式的SQL即可:
    • SELECT DISTINCT TOP 25 PERCENT EmployeeID, Name, SalaryFROM Employees;
    • 注意:DISTINCT不可以用於TEXT/BLOB/IMAGE 這一類型的欄位上。
slide147
限定最大輸出筆數(續)
  • 對於MySQL而言,則於原有的SQL指令之後加上 LIMIT n1, n2 即可,其中 n1 表示開始的資料錄編號(從0開始),n2則是要選取的最大資料錄數。例如要選取前5筆員工資料,則下達如下的指令即可:
    • SELECT *FROM EmployeesLIMIT 0,5
3 10 null
3.10 關於NULL
  • NULL值意味著幾個可能:
    • 不會有該項資料
    • 資料目前未知
    • 資料不明
slide149
關於NULL(續)
  • 因為我們無法確切的和NULL值比較大小,故也無法和NULL值執行運算。
  • 例如我們要計算員工的薪資時下達如下的指令:
    • SELECT EmployeeID, Name, Salary, Commission, Salary+Commission AS TotalFROM Employees
  • 將得到如下的結果: (部份Total為NULL)
slide151
關於NULL(續)
  • 我們對含有NULL值的欄位做聚合統計時也會發生問題。
  • 例如下達如下的SQL指令以統計各部門的薪資資料:
    • SELECT DepartmentID, SUM(Salary), SUM(Commission), SUM(Salary+Commission) AS TotalFROM EmployeesGROUP BY DepartmentID
  • 其執行的結果如下:
slide153
關於NULL(續)
  • 此時對付NULL值的方法就是用一個預設的值來取代它,以方便執行運算。取代NULL值的函數如下表。
slide154
關於NULL(續)
  • 我們要計算員工的薪資時的原不良SQL指令:
    • SELECT EmployeeID, Name, Salary, Commission, Salary+Commission AS TotalFROM Employees
  • 改成 (NULL值用0取代)(MySQL)
    • SELECT EmployeeID, Name, Salary, Commission,Salary + IFNULL(Commission,0) AS TotalFROM Employees
  • 則其執行時的結果如下:
slide156
關於NULL(續)
  • 若要統計各部門的薪資資料則可改成:
    • SELECT DepartmentID, SUM(Salary), SUM(Commission), SUM(Salary + IFNULL(Commission,0) ) AS TotalFROM EmployeesGROUP BY DepartmentID
  • 則其執行的結果如下:
ad