第三章
Download
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

第三章

資料庫之資料選取查詢


章節概要

  • 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


撰寫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設定說明。)


選取[Data Source]以連接ODBC資料庫。


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


輸入SQL指令於編輯視窗後,按[Execute]執行。(示範為連接 orders_access)


B ms access
B. 使用MS Access 查詢精靈

  • 首先開啟 orders.mdb,即如下圖所示:



選取[新增]。



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


於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按[ ! ]執行。


執行完後的畫面。於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按


選取於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按[檢視]下的[SQL檢視]即可觀看相關的SQL指令。

SQL 檢視的結果指令。

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


C sql server
C. 於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按使用SQL Server(一)

  • 法一:使用 SQL Query Analyzer

  • 方法二:使用 Enterprise Manager

  • 首先開啟 SQL Query Analyzer (SQL 查詢分析器),即如下:


首先選取要使用的資料庫為 於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按orders,接著輸入 SQL 指令,最後按[ ! ]執行鈕即可。


可以選取於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按[顯示評估的執行計劃]鈕以檢視該SQL指令是如何地被執行。


C sql server1
C. 於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按使用SQL Server(二)

  • 以下接著示範如何於 Enterprise Manager 中下達 SQL 指令。首先開啟SQL Server Enterprise Manager 如下:


打開資料庫列表中的 於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按orders 資料庫,接著選取 Departments 資料表,並按滑鼠右鍵,並選取查詢。


勾選想要顯示的欄位,接著按於資料表的欄位上用滑鼠雙擊即可選取要顯示的欄位。選取完後,按[ ! ]執行。


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


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

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


C sql server2
C. 這是刪除掉多餘的資料行及別名後重新執行的結果。使用SQL Server(三)

  • 以下示範如何於SQL Server 2005/2008 的 SQL Server Management Studio Express 中下達 SQL 指令。首先開啟SQL Server Management Studio Express 如下:


首先點選這是刪除掉多餘的資料行及別名後重新執行的結果。orders資料,然後按「新增查詢(N)」,以開啟SQL編輯器。最後於編輯區內輸入查詢指令,再按「執行(X)」即可。

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


D mysql query browser
D. 這是刪除掉多餘的資料行及別名後重新執行的結果。使用MySQL Query Browser

  • 首先執行 MySQL Query Browser 程式

  • 其後相關的執行操作如下示範:


若尚未建立任何連線名稱,則按這是刪除掉多餘的資料行及別名後重新執行的結果。[...]以建立連線名稱。否則,請指定連線使用者名稱(Username)、密碼(Password)及欲連接的主機名稱(Hostname)和連接埠(Port)。若想預定連接的資料庫(Schema; 綱要)則直接輸入資料庫名稱,如:orders。


如圖為連接 這是刪除掉多餘的資料行及別名後重新執行的結果。orders 資料庫的示範連接資料。


選取適當的連線名稱或輸入資料。這是刪除掉多餘的資料行及別名後重新執行的結果。


這是刪除掉多餘的資料行及別名後重新執行的結果。SQL 指令輸入區輸入SQL指令,接著按執行鍵即可。(注意:資料表和欄位名稱是不分大小寫的。)


E db visualizer
E. 這是刪除掉多餘的資料行及別名後重新執行的結果。使用DB Visualizer

  • 首先執行 DB Visualizer程式。

  • 其後相關的執行操作程序如下:

    • 建立資料庫連線名稱。

    • 設定資料庫驅動程式(使用JDBC)。

    • 建立SQL查詢指令和執行SQL指令。


設定連線名稱。(如:這是刪除掉多餘的資料行及別名後重新執行的結果。orders)


選取驅動程式。這是刪除掉多餘的資料行及別名後重新執行的結果。(範例為MySQL)


設定連線的目標資料和使用者資料。這是刪除掉多餘的資料行及別名後重新執行的結果。


這是刪除掉多餘的資料行及別名後重新執行的結果。SQL Commander中輸入SQL指令。


F oracle sql developer
F. 這是刪除掉多餘的資料行及別名後重新執行的結果。使用Oracle SQL Developer

  • 首先執行 Oracle SQL Developer 程式。

  • 其後相關的執行操作如下示範:


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


這是刪除掉多餘的資料行及別名後重新執行的結果。SQL編輯區內下達SQL指令 。


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子句中設定條件時需用到比較運算子(和一般的程式語言相似)。


這是刪除掉多餘的資料行及別名後重新執行的結果。3.3 比較運算子列表

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


設定簡單的條件這是刪除掉多餘的資料行及別名後重新執行的結果。

  • 我們可以使用的SQL指令格式如下:

    • 【SELECT指令格式五】SELECT 欄位名稱一 AS 別名一, 欄位名稱二 AS 別名二,……FROM 資料表來源名稱WHERE 條件表示式

  • 例如我們想要列出部門代號為10的資料,則可下達如下SQL指令:

    • SELECT *FROM DepartmentsWHERE DepartmentID = 10;


設定簡單的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 結果如下:


設定簡單的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 我們想列出所有部門代號為20(業務部門)的所有員工的資料,則可下達如下指令:

    • SELECT *FROM EmployeesWHERE DepartmentID = 20;

  • 執行的結果如下:


Is null
設定簡單的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(IS NULL)

  • 我們可以發現某些業務員的佣金未知,如上圖中的員工1002和1008。

  • 假如我們想要選取佣金未知(即為NULL)的所有員工時應下達如下的指令:

    • SELECT *FROM EmployeesWHERE Commission IS NULL;

  • 其執行的結果如下:


設定簡單的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(無效的指令)

  • 我們不可以使用如下的指令:(無效的指令)

    • SELECT *FROM EmployeesWHERE Commission = NULL;

    • -- ‘’ 為兩個 ‘ 符號SELECT *FROM EmployeesWHERE Commission = '';


設定簡單的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 由於 NULL 值代表是未知、未定的資料,故任何一般比較的符號(=、>、>=、<、<= ...)和NULL運算的結果皆為假(FALSE)。所以 WHERE Commission = NULL 是不正確的用法。

  • 我們若想要選出所有佣金不為NULL的員工資料,應下達如下的指令:

    • SELECT*FROM EmployeesWHERE Commission IS NOT NULL;


設定複雜的條件這是刪除掉多餘的資料行及別名後重新執行的結果。

  • 在WHERE子句中可以設定由多個條件組合而成的複雜條件式。此時,我們可使用邏輯運算子(Logical Operator)來組合比較條件運算式,以設定出含多個條件的複雜條件式。

  • SQL中可運用的邏輯運算子列表如表3.4。


這是刪除掉多餘的資料行及別名後重新執行的結果。3.4 邏輯運算子列表

(

)


設定複雜的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • AND是用來設定「(條件一) 且(條件二) 須同時成立」這樣子的條件的運算子。

  • OR 是用來設定「(條件一) 或(條件二) 須成立」這樣子的條件的運算子;換言之,用來設定「(條件一) 或者 (條件二) 任一條件成立」這樣子的條件的運算子。

  • NOT 是用來設定否定的條件,即「不是(條件)」這樣子的條件的運算子。


設定複雜的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 我們想選取出職務為業務且稱呼為先生的男性經理之員工編號、姓名和年薪,則可下達如下的指令:

    • SELECT EmployeeID, Name, Salary, TitleFROM EmployeesWHERE Title ='業務' AND TitleOfCourtesy = '先生';

  • 其執行的結果為如下:


設定複雜的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 例如我們想找出職稱為業務或者佣金不為NULL的員工的員工編號、姓名、年薪、職稱和佣金資料。則可下達如下指令:

    • SELECT EmployeeID, Name, Salary, Title, CommissionFROM EmployeesWHERE Title = '業務' OR Commission IS NOT NULL;

  • 其執行的結果如下:


設定複雜的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 例如我們想找出非職稱為業務的員工資料,則可下達如下的指令:

    • SELECT *FROM EmployeesWHERE NOT (Title = '業務');

  • 其執行的結果如下:


設定複雜的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 注意:NOT 後的那一對括號是必要的。假如改成如下,則執行的結果可能為0筆資料(有些資料庫系統)。

    • -- 可能不正確的 SQL 指令SELECT *FROM EmployeesWHERE NOT Title = '業務';


設定複雜的條件這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 當然我們想找出非業務的員工資料,也以可下達如下的指令:

    • SELECT *FROM EmployeesWHERE Title <> '業務';

  • 或者(非標準SQL語法)

    • SELECT *FROM EmployeesWHERE Title != '業務';


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。

  • 在WHERE子句中設定條件時,我們可以指定模糊條件和限制資料範圍。此時即需要使用到模糊條件。如:

    • LIKE

    • BETWEEN … AND …

    • IN


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 例如我們想找出所有員工的職稱中含有 ‘經理’ 兩個字的員工(職稱以經理字眼結尾者),則可下達如下的模糊指令:

    • SELECT *FROM EmployeesWHERE Title LIKE '%經理';

  • 執行的結果如下:


LIKE這是刪除掉多餘的資料行及別名後重新執行的結果。中使用樣式 '%經理'。


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 例如我們想找出姓氏為 '張' 的所有員工(以張字開頭的員工姓名),則可下達指令如下:

    • SELECT *FROM EmployeesWHERE Name LIKE '張%';

  • 其執行結果如下:


LIKE這是刪除掉多餘的資料行及別名後重新執行的結果。中使用樣式 '張%'。


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 我們想找出員工的英文字為 'M___' (三個底線),即M開頭共計有四個字元的英文姓名,則可下達如下的指令:

    • SELECT *FROM EmployeesWHERE EngName LIKE 'M___';

  • 其執行的結果如下:


LIKE這是刪除掉多餘的資料行及別名後重新執行的結果。中使用樣式 'M___'。


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 例如我們想找出員工的英文字含有 'ar' 這兩字元的員工,則可下達如下的指令:

    • SELECT *FROM EmployeesWHERE EngName LIKE '%ar%';

  • 其執行的結果如下:


LIKE這是刪除掉多餘的資料行及別名後重新執行的結果。中使用樣式 '%ar%'。


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 如我們想找出所有員工的年薪介於420000~540000之間的所有員工,則此時可以利用BEWTEEN...AND運算,即可下達如下的指令:

    • SELECT *FROM EmployeesWHERE Salary BETWEEN 420000 AND 540000;

  • 其執行的結果如下:


注意:資料區間是含頭尾,即這是刪除掉多餘的資料行及別名後重新執行的結果。[42000, 54000]。


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 當然,我們也可以把上述的需求撰寫成如下的指令:

    • SELECT *FROM EmployeesWHERE Salary >= 420000 AND Salary <= 540000;

  • 然而使用 BEWTEEN …AND 比較容易被了解的。


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 在某些埸合裡,我們會想用列舉的方式選取資料,例如:部門代號為 10,20,30 的資料,或如某一代碼為 ‘A’, ‘B’, ‘C’ 其中之一。此時,我們即可使用IN運算子於條件之中。

  • 如我們想列出員工代號為1002及1005的部屬列表(員工編號、姓名、上司員工編號),即可下達如下的指令:

    • SELECT EmployeeID AS '員工編號', Name AS '姓名', SupervisorID AS '上司之員工編號‘FROM EmployeesWHERE SupervisorID IN (1002, 1005);

  • 其執行的結果如下:


設定模糊的條件與限制範圍這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 我們想列出英文姓名為 'Mary'、 'Marua'、 'Mike' 和 'Linda' 等人的資料,則可下達如下的指令:

    • SELECT *FROM EmployeesWHERE EngName IN ('Mary', 'Marua', 'Mike', 'Linda');

  • 其執行的結果如下:


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

  • 關聯式資料庫中的資料是由多列資料所組成的,而這些資料列的儲存順序並不具有特殊的意義。

  • 一般而言,資料列的順序就是依資料存入(新增)至資料庫時的先後次序。


進行資料排序這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 若要對選取出來的資料列進行排序,那麼我們可以用ORDER BY子句來設定排序的方式。此時我們可以使用如下格式的SQL指令:

    • 【SELECT指令格式六】SELECT 欄位名稱一 AS 別名一, 欄位名稱二 AS 別名二, ...FROM 資料表來源名稱WHERE 條件表示式ORDER BY 排序欄位名稱一 [ASC|DESC],排序欄位名稱二 [ASC|DESC], ...


進行資料排序這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 例如我們想列出所有的部門資料,而且依部門代號由大到小排序,則可下達如下的指令:

    • SELECT *FROM DepartmentsORDER BY DepartmentID DESC;

  • 其執行的結果如下:


進行資料排序這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 我們想列出員工的部分資料(部門代號、員工編號、姓名、英文名字),而列出時依照部門代號由小到大及員工編號由小到大的方式排序。則可下達如下的指令:

    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY DepartmentID, EmployeeID

  • 其執行的結果如下:


資料先依部門代號排序,再依員工編號排序。這是刪除掉多餘的資料行及別名後重新執行的結果。


進行資料排序這是刪除掉多餘的資料行及別名後重新執行的結果。(續)

  • 我們想要依部門代號由大到小(主排序)及員工編號由小到大(次要排序),則可將上述指令更改為如下即可:

    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY DepartmentID DESC, EmployeeID ASC

  • 則其執行的結果變成如下:


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


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

  • 對於欲排序的欄位名稱,我們可以直接標明其欄位編號(從1開始),而無須列出欄位名稱。

  • 例如上述的SQL指令可以簡寫成:

    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY 1 DESC, 2


3.5 資料先依部門代號由大到小排序,再依員工編號排序由小到大。使用運算式及函數

  • 在SQL中我們也可以使用運算式(Expression)和使用資料庫內建的函數(Function)等功能,使用運算子(Operator)和函數對指定的數值或欄位的值進行加工處理。

  • 而算術運算子可以對數字型(Numeric)的資料進行運算,其運算的方式有四則運算及取餘數(模數)等。

  • 算術運算子列表如下:


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 我們想計算訂單編號10251的貨品價格明細(產品編號、單價、數量、總價)(資料檔為OrderDetails訂單明細檔),則可下達如下的指令:

    • SELECT ProductID AS '產品編號', UnitPrice AS '單價', Quantity AS '數量',UnitPrice*Quantity AS '總價' FROM OrderDetailsWHERE OrderID = 10251

      (Oracle用 "總價")

  • 以下則是執行的結果:


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 除了數學運算之外,我們也可以對整數的欄位資料或整數數值進行位元(Bitwise)運算。

  • 依DBMS而異,不一定有支援此項功能。

  • MySQL的位元運算和C/C++語言一樣,如下列表:


MySQL資料先依部門代號由大到小排序,再依員工編號排序由小到大。的位元運算子


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 若我們想要得知12和24之間各項位元運算的結果,則可以下達一個不含FROM子句(沒有用到)的SQL指令,如下:

    • SELECT 12 & 24, 12 | 24, 12 ^ 24

  • 注意:Oracle 的SELECT指令限定一定要有FROM子句,習慣上,執行一般運算會指定資料表為DUAL。如:SELECT 1+2 FROM DUAL

  • 其執行的結果如下:


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 各家RDBMS所提供的功能與函數的名稱不盡相同,而且函數的數量也是不盡相同。使用上大致可分為如下幾個類別:

    • 數學函數:根據函數的參數輸入值執行計算,然後傳回數值。

    • 字串函數:在字串輸入值(CHAR/VARCHAR)上執行一個運算 ,並傳回一個字串值或數值。

    • 日期與時間函數:執行日期與時間輸出值的運算,並傳回字串、數字或日期與時間值。


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 例如:

    • SELECT ROUND(12.5,0), LEAST('A', 'B', 'C'), RAND(0), SIGN(-4)

  • 其執行的結果如下:


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

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


REVERSE(‘AB資料先依部門代號由大到小排序,再依員工編號排序由小到大。張三’) = ‘三張BA’

SUBSTR

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


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 我們想要列出部門10的所有員工的姓名、生日、生年、生月與日子部分等資料,則可下達如下的指令:

    • SELECT Name, BirthDate, LEFT(BirthDate, 4), MID(BirthDate, 6, 2), RIGHT(BirthDate, 2)FROM EmployeesWHERE DepartmentID = 10

  • 以下為執行的結果:


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 我們想要列出部門編號10的員工編號、員工姓名、英文名字、中英姓名組合名稱,則可下達如下指令:(MySQL/ORACLE)

    • SELECT EmployeeID, Name, EngName,CONCAT(Name,' - ',EngName) AS CombinedNameFROM EmployeesWHERE DepartmentID = 10


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 而於 SQL Server/Access則可下達如下指令:

    • SELECT EmployeeID, Name, EngName, Name + ' - ' + EngName AS CombinedNameFROM EmployeesWHERE DepartmentID = 10

  • 以下則為常用的日期與時間函數方面的部分列表。


註:資料先依部門代號由大到小排序,再依員工編號排序由小到大。Access 使用 NOW() 取得目前的時間


使用運算式及函數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 計算部門代號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


3.6 資料先依部門代號由大到小排序,再依員工編號排序由小到大。使用聚合函數來統計資料

  • SQL的聚合函數或彙總函數(Aggregate Functions)可用來進行資料表欄位的統計分析,可以取得筆數、平均值及總計等各式的統計量。聚合函數列表如下:


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


使用聚合函數來統計資料資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 我們想取得員工資料檔的總筆數,則可下達如下的指令:

    • SELECT COUNT(*)FROM Employees;

  • 例如我們想要取得員工的總人數、最低/最高/總計/平均薪資及標準差,則可下達如下的指令:

    • SELECT COUNT(*) AS '員工數', MIN(Salary) AS '最低薪資', MAX(Salary) AS '最高薪資', SUM(Salary) AS '薪資總計', AVG(Salary) AS '平均薪資', STDDEV(Salary) AS '標準差'FROM Employees;

  • 以下為執行的結果:


使用聚合函數來統計資料資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 我們想要取得部門代號為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 )


使用聚合函數來統計資料資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 想列出佣金欄位的總筆數、非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;

  • 其執行的結果則如下:


3.9 資料先依部門代號由大到小排序,再依員工編號排序由小到大。限定最大輸出筆數

  • 我們有時候會想限定資料輸出量,尤其用於開發WEB化(網頁式)的資訊系統時。然而這個功能並未列入標準的SQL語法之中,因而各家RDBMS系統大都不相同。

  • 於SQL Server/Access中,我們可於用TOP n來選取結果資料集的前n筆資料錄,或用TOP n PERCENT來選取結果資料集的前n個百分比的資料錄。例如選取員工資料的指令為:

    • SELECT *FROM Employees;


限定最大輸出筆數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 那麼只要選取前5筆員工資料,可下達如下的指令:(SELECT之後加上TOP 5)

    • SELECT TOP 5 *FROM Employees;

  • 假如要選取前5%的員工資料,可下達如下的指令:(SELECT之後加上TOP 5 PERCENT)

    • SELECT TOP 5 PERCENT *FROM Employees;


限定最大輸出筆數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 要選取的資料要剔除相同的資料錄,那麼SQL指令改成如下樣式的SQL即可:

    • SELECT DISTINCT TOP 25 PERCENT EmployeeID, Name, SalaryFROM Employees;

    • 注意:DISTINCT不可以用於TEXT/BLOB/IMAGE 這一類型的欄位上。


限定最大輸出筆數資料先依部門代號由大到小排序,再依員工編號排序由小到大。(續)

  • 對於MySQL而言,則於原有的SQL指令之後加上 LIMIT n1, n2 即可,其中 n1 表示開始的資料錄編號(從0開始),n2則是要選取的最大資料錄數。例如要選取前5筆員工資料,則下達如下的指令即可:

    • SELECT *FROM EmployeesLIMIT 0,5


3 10 null
3.10資料先依部門代號由大到小排序,再依員工編號排序由小到大。 關於NULL

  • NULL值意味著幾個可能:

    • 不會有該項資料

    • 資料目前未知

    • 資料不明


關於資料先依部門代號由大到小排序,再依員工編號排序由小到大。NULL(續)

  • 因為我們無法確切的和NULL值比較大小,故也無法和NULL值執行運算。

  • 例如我們要計算員工的薪資時下達如下的指令:

    • SELECT EmployeeID, Name, Salary, Commission, Salary+Commission AS TotalFROM Employees

  • 將得到如下的結果: (部份Total為NULL)


關於資料先依部門代號由大到小排序,再依員工編號排序由小到大。NULL(續)

  • 我們對含有NULL值的欄位做聚合統計時也會發生問題。

  • 例如下達如下的SQL指令以統計各部門的薪資資料:

    • SELECT DepartmentID, SUM(Salary), SUM(Commission), SUM(Salary+Commission) AS TotalFROM EmployeesGROUP BY DepartmentID

  • 其執行的結果如下:


關於資料先依部門代號由大到小排序,再依員工編號排序由小到大。NULL(續)

  • 此時對付NULL值的方法就是用一個預設的值來取代它,以方便執行運算。取代NULL值的函數如下表。


關於資料先依部門代號由大到小排序,再依員工編號排序由小到大。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

  • 則其執行時的結果如下:


關於資料先依部門代號由大到小排序,再依員工編號排序由小到大。NULL(續)

  • 若要統計各部門的薪資資料則可改成:

    • SELECT DepartmentID, SUM(Salary), SUM(Commission), SUM(Salary + IFNULL(Commission,0) ) AS TotalFROM EmployeesGROUP BY DepartmentID

  • 則其執行的結果如下:


ad