1、第 8 章,SQL-99：綱要定義、基本限制與查詢,資料庫管理,2,SQL的定義、功能與敘述 CREATE TABLE語法 DROP語法 ALTER語法 SQL中的基本查詢語法 SELECT-FROM-WHERE結構 簡單的SQL查詢 別名、* 和DISTINCT的用法 集合運算 子字串的比對 算術運算 區間值的比較運算 查詢結果的排序,學習重點 (1/2),資料庫管理,3,空值的比較 巢狀查詢 EXISTS函數 明確指定的集合 屬性的重新命名 合併表格功能 聚合函數、GROUP BY與HAVING子句 子字串比對與算術運算 SQL查詢語法總整理 修改命令：INSERT、DELETE、UPDA
2、TE 視界 (View),學習重點 (2/2),資料庫管理,4,SQL的定義與功能 (1/2),SQL (Structural Query Language)：是結構化程式語言的簡稱，它已經成為關聯式DBMS的標準語言 SQL包含下列功能 SQL包含資料定義、查詢與更新的敘述 SQL不但是一種DDL(資料定義語言)，也是一種DML(資料操作語言) SQL還具備定義資料庫視界、設定安全性與權限、定義完整性限制以及設定交易控制的功能 可以將SQL敘述嵌入Java、COBOL或C/C+等通用用途程式語言的機制,資料庫管理,5,SQL的定義與功能 (2/2),SQL關聯式模型與理論性(theoreti
3、cal)關聯式模型的差異： SQL使用表格(table)、列(row)及欄(column)，來代表關聯(relation)、值組(tuple)和屬性(attribute) SQL中的table可能包含重覆列(duplicated row)，但relation中不包含重覆tuple SQL中的table可能沒有指定主鍵(primary key),資料庫管理,6,SQL的定義敘述,定義綱要(schema) 語法：CREATE SCHEMA 範例：(須經由DBA下此語法)CREATE SCHEMA COMPANY AUTHORIZATION Jsmith 定義表格(table) 語法：CREATE
4、TABLE 範例：CREATE TABLE EMPLOYEE 基底表格(base table)：透過CREATE TABLE宣告的關聯稱之。 與透過CREATE VIEW建立的關聯不同,資料庫管理,7,CREATE TABLE 語法,CREATE TABLE: the following are defined: table name attribute name attribute type CHAR(), VARCHAR(), NUMBER or INTEGER, DECIMAL(x.x), TIME, DATE, BLOB, etc. attribute constraints: PRI
5、MARY KEY (attribute-set) FOREIGN KEY (attribute-set) REFERENCES table-name(attribute set) ON DELETE . ON UPDATE . UNIQUE (attribute-set) attribute-name attribute-type NOT NULL attribute-name attribute-type DEFAULT value,資料庫管理,8,CREATE TABLE 範例 (1/2),資料庫管理,9,CREATE TABLE 範例 (2/2),資料庫管理,10,SQL的屬性資料型態和
6、定義域,INT SMALLINT FLOAT REAL DOUBLE DECIMAL(i, j) CHAR(n) VARCHAR(n) BIT(n) BOOLEAN：TRUE/FALSE/UNKNOWN DATE TIMESTAMP INTERVAL,資料庫管理,11,SQL Server的資料型態 (1/2),資料庫管理,12,SQL Server的資料型態 (2/2),資料庫管理,13,以SQL語法指定基本限制的範例,資料庫管理,14,SQL指定限制的例子,指定屬性的限制和屬性的預設值 Dnumber INT NOT NULL CHECK (Dnumber 0 AND Dnumber 0
7、AND D_NUM 21) 指定鍵值和參考完整性限制 Dnumber INT PRIMARY KEY; 使用CHECK指定值組的限制 CHECK (Dept_create_date = Mgr_start_date);,資料庫管理,15,SQL Server指定限制的語法範例,資料庫管理,16,DROP 的語法,DROP：用來移除某個有名稱的綱要元件，如表格、定義域、限制或綱要 移除綱要： DROP SCHEMA COMPANY CASCADE; 移除綱要與它所有的表格、定義域和其他元素 DROP SCHEMA COMPANY RESTRICT; 只有在綱要內沒有任何元素時才能執行移除 移除表
8、格： DROP TABLE DEPENDENT CASCADE;,資料庫管理,17,ALTER 的語法 (1/2),ALTER：用來變更基底關聯的定義或其他綱要元件 變更基底關聯/表格的動作包括： 新增或移除欄位(即屬性) 變更欄位的定義 新增或移除表格限制 新增表格的欄位/屬性 ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12); 新增一個用來記錄員工職務的新屬性 移除表格的欄位/屬性 ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE; 移除欄位時，有CASCADE和R
9、ESTRICT兩種方式可以選擇,資料庫管理,18,ALTER 的語法 (2/2),移除原來的預設子句 ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT; 定義新的預設子句 ALTER TABLE COMPANY.DEPARTMEN ALTER COLUMN Mgr_ssn SET DEFAULT 333445555; 新增/移除表格的限制 ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE,資料庫管理,19,DDL的摘要整理,基底關聯表上的
10、 DDL: Create table: 定義 base relations 上的 schema Alter table: 修改 base relations 上的 schema Drop schema: 刪除 database 的 schema Drop table: 刪除 base relations 上的 schema和資料(data) Truncate table: 僅刪除 base relations 上的資料,資料庫管理,20,CREATE SCHEMA REFERENTIAL INTEGRITY OPTIONS,SQL2與SQL-99的新增功能,資料庫管理,21,DATE： 由年-
11、月-日所組成，格式為yyyy-mm-dd TIME： 由時:分:秒所組成，格式為hh:mm:ss TIME(i)： 由時:分:秒加上代表幾分之一秒的數字 i 所組成 格式為hh:mm:ss:ii.i TIMESTAMP (時間戳記)： 由DATE和TIME元件所組成 INTERVAL (期間)： 指定一個相對值，而不是絕對值 可能是YEAR/MONTH期間或DAY/TIME期間 當它相加或相減另一個絕對值，可能是正數或負數，結果也會是絕對值,SQL2與SQL-99的新增資料型態,資料庫管理,22,SQL中的基本查詢語法,SQL有個很重要的基本敘述，可以讓我們從資料庫中擷取資訊，也就是SELEC
12、T敘述 不同於關聯式代數(relational algebra)的SELECT運算 SQL與關聯式模型正式定義間有個很重要差異： SQL允許表格 (即關聯) 中存在兩筆或多筆所有屬性值完全相同的值組 因此，通常SQL表格並非一個值組的集合，因為集合不允許有重複的值組。 SQL表格是值組的多重集合 (multiset，或稱為bag) 不過，SQL關聯如果有指定PRIMARY KEY或UNIQUE屬性，或是在SELECT命令中加上DISTINCT選項，此時的SQL關聯就必須是值組的集合,資料庫管理,23,SELECT-FROM-WHERE結構,基本的SELECT敘述，有時也稱為SELECT-FRO
13、M-WHERE區塊 (block) SELECT FROM WHERE ：屬性名稱的列表 在查詢時需要參考這些屬性的值 ：列出處裡查詢時會用到的關聯：條件（布林）運算式 用來識別查詢時所要擷取的值組,資料庫管理,24,SQL的基本語法,資料庫管理,25,範例關聯式資料庫綱要 (圖5.5),資料庫管理,26,範例關聯式資料庫狀態 (圖5.6),資料庫管理,27,基本的SQL查詢 (單一表格),查詢範例0：擷取名叫 John B. Smith 員工的生日與住址SELECT Bdate, AddressFROM EMPLOYEEWHERE Fname=John AND Minit=B AND Lna
14、me=Smith;類似於關聯式代數的SELECT-PROJECT運算配對 其中SELECT子句負責指定投影屬性 而WHERE子句則負責指定選擇條件 查詢的結果可能會有重複的值組,資料庫管理,28,基本的SQL查詢結果 (單一表格),範例0 的查詢結果,資料庫管理,29,基本的SQL查詢 (雙表格),查詢範例1：擷取在 Research 部門工作的所有員工的姓名與住址 SELECT Fname, Lname, AddressFROM EMPLOYEE, DEPARTMENTWHERE Dname=Research AND Dnumber=Dno;類似關聯式代數運算中的SELECT-PROJECT
15、-JOIN (DNAME=Research) 是選擇條件 相當於關聯式代數的SELECT運算 (DNUMBER=DNO) 則是合併條件 相當於在關聯式代數的JOIN運算,資料庫管理,30,基本的SQL查詢結果 (雙表格),範例1 的查詢結果,資料庫管理,31,基本的SQL查詢 (三表格),查詢範例2：列出所有位在 Stafford 地點的計畫，其計畫編號、控管部門編號，以及部門經理的姓氏、住址和生日 SELECT Pnumber, Dnum, Lname, Address, BdateFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE Dnum=Dnumber AN
16、D Mgr_ssn=Ssn ANDPlocation=Stafford;在範例2裡有兩個合併條件 合併條件DNUM=DNUMBER，使得計劃與其控管部門產生關聯 合併條件MGRSSN=SSN，則讓控管部門與管理此部門的員工產生關聯,資料庫管理,32,基本的SQL查詢結果 (三表格),範例2 的查詢結果,資料庫管理,33,模稜兩可的屬性名稱,在SQL中，只要屬性是屬於不同的關聯，就可以讓兩個或多個屬性使用同樣的名稱 若查詢會參考到兩個或多個同名的屬性，就必須用關聯名稱來修飾 (qualify) 屬性名稱，做法是將關聯名稱放在屬性名稱前，並用英文的句點 (.) 來分隔 範例1A:SELECT Fn
17、ame, EMPLOYEE.Name, Address FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.Name=Research ANDDEPARTMENT.Dnumber=EMPLOYEE.Dnumber;,資料庫管理,34,模稜兩可的關聯使用別名 (1/2),假如查詢對同一個關聯參考兩次，也會發生模稜兩可的情形 在這類情況下要對關聯名稱指定別名(aliases) 查詢範例8：擷取每一位員工的姓名與其直屬上司的姓名 SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE E S WHERE E.S
18、uper_ssn=S.Ssn;在範例8中的替代關聯名稱E和S被稱作EMPLOYEE關聯的別名 (alias) 或值組變數 (tuple) E 扮演部屬角色的員工 S 扮演上司角色的員工,資料庫管理,35,模稜兩可的關聯使用別名 (2/2),別名可以用在任何SQL查詢中 也可以使用AS關鍵字來指定別名 查詢範例8： SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn; 查詢範例1B： SELECT Fname, EMPLOYEE.Name, Addr
19、essFROM EMPLOYEE E, DEPARTMENT DWHERE D.Name=Research ANDD.Dnumber=E.Dnumber;,資料庫管理,36,未指定的WHERE子句,假如沒有WHERE的子句，代表沒有選擇條件 因此FROM子句裡所指定關聯的所有值組都會被選取 查詢範例9：在資料庫中選擇所有員工的SSN資料SELECT SsnFROM EMPLOYEE;假如在FROM子句中指定一個以上的關聯，而且沒有WHERE子句，則表示結果等於這些關聯的CROSS PRODUCT 也就是所有可能的值組組合都會被選取 查詢範例10：選擇EMPLOYEE SSN與DEPARTMEN
20、T DNAME的所有組合SELECT Ssn, DnameFROM EMPLOYEE, DEPARTMENT;,資料庫管理,37,星號(*) 的使用 (1/2),在SQL中，假如要擷取所選值組的所有屬性值，只需要用一個星號 (*) 即可，這個星號就代表所有的屬性 查詢範例1C：擷取在DEPARTMENT編號為5的部門工作的員工SELECT *FROM EMPLOYEEWHERE Dno=5;,資料庫管理,38,查詢範例1D：擷取每個在 Research部門工作的員工，其所有的EMPLOYEE屬性值與DEPARTMENT的全部屬性值SELECT *FROM EMPLOYEE, DEPARTMEN
21、TWHERE Dname=Research AND Dno=Dnumber;查詢範例10A：擷取EMPLOYEE與DEPARTMENT關聯的CROSS PRODUCTSELECT *FROM EMPLOYEE, DEPARTMENT;,星號(*) 的使用 (2/2),資料庫管理,39,DISTINCT的用法,SQL通常不會將關聯視為集合，因此可以出現重複的值組 為了消除查詢結果中的重複值組，可使用關鍵字DISTINCT Q11的結果可能會有重複的SALARY值 Q11A則不會有任何重複值查詢範例11：擷取每一位員工的薪資SELECT ALL SalaryFROM EMPLOYEE; 查詢範例1
22、1A：擷取所有不同的薪資值SELECT DISTINCT SalaryFROM EMPLOYEE;,資料庫管理,40,SQL提供某些集合運算 聯集運算 (UNION) 有些SQL 版本還會有差集 (EXCEPT) 和交集(INTERSECT) 運算 這些集合運算所產生的關聯是值組的集合 也就是說，重複的值組會在結果中被除去 集合運算只能應用在聯集相容(union compatible)的關聯上 所以必須先確定要運算的兩個關聯具有相同的屬性，而且這些屬性出現在兩個關聯的順序也相同,集合運算 (1/3),資料庫管理,41,查詢範例4：列出姓氏為 Smith 的員工所參與的所有計畫，不論該員工是計畫
23、的工作人員或是管理此計畫的部門經理 (SELECT DISTINCT PnumberFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname=Smith)UNION (SELECT DISTINCT PnumberFROM PROJECT, WORKS_ON, EMPLOYEEWHERE Pnumber=Pno AND Essn=Ssn AND Lname=Smith);,集合運算 (2/3),資料庫管理,42,集合運算 (3/3),SQL針對多重集合的運算，是利用ALL關鍵字 UNION AL
24、L EXCEPT ALL INTERSECT ALL,資料庫管理,43,子字串的比對 (1/2),SQL利用LIKE運算，進行字串比對 %字元可取代任何數目的字元 _字元則可取代單一字元 查詢範例12：擷取所有住址在Houston, Texas的員工SELECT Fname, LnameFROM EMPLOYEEWHERE Address LIKE %Houston, TX%;,資料庫管理,44,子字串的比對 (2/2),查詢範例12A：擷取所有在1950年代出生的員工SELECT Fname, LnameFROM EMPLOYEEWHERE Bdate LIKE 1 9 5 _ _ _ _
25、_ _ _;,資料庫管理,45,算術運算,標準的四則運算 加法(+)、減法(-)、乘法(*)、除法(/) 查詢範例13：顯示出所有工作於 ProductX 計畫的員工加薪 10% 後的薪資結果SELECT Fname, Lname, 1.1*Salary AS Increased_salFROM EMPLOYEE, WORK_ON, PROJECTWHERE Ssn=Essn AND Pno=Pnumber AND Pname=ProductX;,資料庫管理,46,區間值的比較運算,區間值的比較運算：BETWEEN 查詢範例14：擷取5號部門中所有薪資在$30,000到$40,000之間的員工
26、SELECT *FROM EMPLOYEEWHERE (Salary BETWEEN 30000 AND 40000)AND Dno=5;,資料庫管理,47,查詢結果的排序,ORDER BY子句是用來針對值組內的一或多個屬性值，將查詢結果的值組加以排序 預設的順序是遞增排序(ASC)，可用關鍵字DESC變成遞減 例如，ORDER BY Dname DESC, Lname ASC, Fname ASC 查詢範例15：擷取員工與他們所工作計畫的清單，在清單中先針對部門號碼排序，每個部門內再依員工姓名的字母順序排序 SELECT Dname, Lname, Fname, PnameFROM DEPA
27、RTMENT, EMPLOYEE, WORKS_ON, PROJECTWHERE Dnumber=Dno AND Ssn=Essn AND Pno=PnumberORDER BY Dname, Lname, Fname;,資料庫管理,48,空值(NULL)的比較 (1/2),在資料庫中，每個空值(NULL)都會被視為不同 所以，當空值在進行比較運算時，結果會是未知(UNKNOWN) SQL中，使用3種邏輯值：TRUE、FALASE和UNKNOWN 標準的布林邏輯理論只有TRUE和FALSE兩種值,資料庫管理,49,空值(NULL)的比較 (2/2),SQL在查詢中檢查數值是否為NULL 不是用
28、 = 或 來比較 而是用 IS 或 IS NOT 進行比較查詢範例18：擷取所有無上司的員工姓名SELECT Fname, LnameFROM EMPLOYEEWHERE Super_ssn IS NULL;,資料庫管理,50,巢狀查詢 (nested query)：指在一個查詢的WHERE子句內，含有完整的SELECT-FROM-WHERE區塊。 此時這個外部的WHERE查詢被稱為外部查詢 (outer query) 之前的查詢範例可以使用巢狀查詢來改寫查詢範例1：擷取所有在 Research 部門工作的員工的姓名與住址 SELECT Fname, Lname, Address FROM E
29、MPLOYEE WHERE Dno IN (SELECT Dnumber FROM DEPARTMENT WHERE Dname=Research );由巢狀查詢先選出 Research 部門的編號 外部查詢來選擇其DNO值屬於巢狀查詢結果中的EMPLOYEE值,巢狀查詢 (1/4),資料庫管理,51,巢狀查詢 (2/4),查詢範例4：列出姓氏為 Smith 的員工所參與的所有計畫，不論該員工是計畫的工作人員或是管理此計畫的部門經理SELECT DISTINCT PnumberFROM PROJECTWHERE Pnumber IN (SELECT PnumberFROM PROJECT, D
30、EPARTMENT, EMPLOYEEWHERE Dnum=Dnumber AND Mgr_ssn=SsnAND Lname=Smith);ORPnumber IN (SELECT PnoFROM WORKS_ON, EMPLOYEEWHERE Essn=Ssn AND Lname=Smith);,資料庫管理,52,巢狀查詢 (3/4),若巢狀查詢結果只有一筆值組，而且只有一個屬性，也就是只有單一值 可以使用 = 而不必使用IN範例：SELECT SsnFROM EMPLOYEEWHERE Dno = (SELECT DnumFROM PROJECTWHERE Pnumber = 2);,資料
31、庫管理,53,巢狀查詢 (4/4),SQL也可以將資料值的值組(tuple)放在一對括號內來比較範例：SELECT DISTINCT EssnFROM WORKS_ONWHERE (Pno, Hours) IN (SELECT Pno, HoursFROM WORKS_ONWHERE Ssn=123456789);,資料庫管理,54,其他比較運算子,除IN運算子外，還有其他比較運算子 = (, =, , =, ALL (SELECT Salary FROM EMPLOYEE WHERE Dno=5);,資料庫管理,55,假如巢狀查詢中WHERE子句中的條件，會參考到宣告在外部查詢的關聯裡的某些
32、屬性，就稱這兩個查詢是相互關聯的 (correlated)查詢範例16：擷取眷屬的名字和性別與員工本人相同的員工姓名 SELECT E.Fname, E.LnameFROM EMPLOYEE AS EWHERE E.Ssn IN (SELECT EssnFROM DEPENDENTWHERE E.Fname=DEPENDENT_nameAND E.Sex=Sex);,相互關聯的巢狀查詢 (1/2),資料庫管理,56,以巢狀SELECT-FROM-WHERE區塊所撰寫，而且使用=或IN比較運算子的查詢，一定可以改寫成單一區塊的查詢。例如範例16可以改寫成範例16A。查詢範例16 SELECT E
33、.Fname, E.LnameFROM EMPLOYEE AS EWHERE E.Ssn IN (SELECT EssnFROM DEPENDENTWHERE E.Fname=DEPENDENT_nameAND E.Sex=Sex);查詢範例16A SELECT E.Fname, E.LnameFROM EMPLOYEE AS E, DEPENDENT AS DWHERE E.Ssn=D.Essn AND E.Sex=D.Sex ANDE.Fname=D.DEPENDENT_name;,相互關聯的巢狀查詢 (2/2),資料庫管理,57,EXISTS函數：用來檢查相互關聯的巢狀查詢的結果是否為空
34、的 (沒有任何值組) 將範例16用EXISTS改寫成範例16B查詢範例16 SELECT E.Fname, E.LnameFROM EMPLOYEE AS EWHERE E.Ssn IN (SELECT EssnFROM DEPENDENTWHERE E.Fname=DEPENDENT_nameAND E.Sex=Sex);查詢範例16B SELECT E.Fname, E.LnameFROM EMPLOYEE AS EWHERE EXISTS (SELECT *FROM DEPENDENTWHERE E.Ssn=Essn AND E.Sex=SexAND E.Fname=DEPENDENT_
35、name);,EXISTS函數 (1/5),資料庫管理,58,查詢範例6：擷取沒有眷屬的員工姓名 SELECT Fname, LnameFROM EMPLOYEEWHERE NOT EXISTS (SELECT *FROM DEPENDENTWHERE Ssn=Essn);在範例6中，相互關聯的巢狀查詢會擷取所有與EMPLOYEE值組相關聯的DEPENDENT值組，如果不存在則此EMPLOYEE值組會被選取,EXISTS函數 (2/5),資料庫管理,59,EXISTS函數 (3/5),查詢範例7：列出至少有一位眷屬的經理姓名 SELECT Fname, LnameFROM EMPLOYEEWH
36、ERE EXISTS ( SELECT *FROM DEPENDENTWHERE Ssn=Essn)ANDEXISTS ( SELECT *FROM DEPARTMENTWHERE Ssn=Mgr_ssn);,資料庫管理,60,EXISTS函數 (4/5),查詢範例3：找出有參與5號部門所管理的全部計劃的員工姓名語法3A:SELECT Fname, LnameFROM EMPLOYEEWHERE NOT EXISTS ( SELECT PnumberFROM PROJECTWHERE Dnum=5)EXCEPT(SELECT PnoFROM WORKS_ONWHERE Ssn=Essn); 第
37、一個子查詢，選出所有由5號部門控管的計劃 第二個子查詢，選出某員工參與的所有計劃,資料庫管理,61,EXISTS函數 (5/5),語法3B:SELECT Lname, FnameFROM EMPLOYEEWHERE NOT EXISTS (SELECT *FROM WORKS_ON BWHERE (B.Pno IN (SELECT PnumberFROM PROJECTWHERE Dnum=5)ANDNOT EXISTS (SELECT *FROM WORKS_ON CWHERE C.Essn=Ssn ANDC.Pno=B.Pno);,資料庫管理,62,可以在WHERE子句中，使用明確數值的集
38、合來取代巢狀查詢查詢範例17：擷取所有在1、2或3號計畫工作的員工的社會安全號碼SELECT DISTINCT EssnFROM WORKS_ONWHERE Pno IN (1,2,3);,明確指定的集合,資料庫管理,63,屬性的重新命名,在SQL中，可以在屬性名稱後面加上AS關鍵字與想要的新名稱，來重新命名出現在查詢結果中的任何屬性 查詢範例8A：擷取每一位員工的姓名與其直屬上司的姓名 SELECT E.Lname AS EMPLOYEE_name, S.Lname AS SUPERVISOR_nameFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.Supe
39、r_ssn=S.Ssn;,資料庫管理,64,SQL中可以合併表格(關聯) 在查詢中指定FROM子句的合併運算所得結果的表格 使用者可以指定各種不同類型的合併運算(INNER JOIN、NATURAL JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、CROSS JOIN等) 範例8：擷取每一位員工的姓名與其直屬上司的姓名 語法8: SELECT E.Lname AS EMPLOYEE_name, S.Lname AS SUPERVISOR_nameFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.Super_ssn=S.Ssn;語法8B
40、: SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM (EMPLOYEE AS E LEFT OUTER JOINEMPLOYEE AS S ON E.Super_ssn=S.Ssn);,合併表格功能 (1/3),資料庫管理,65,合併表格功能 (2/3),範例1：擷取在 Research 部門工作的所有員工的姓名與住址 語法1: SELECT Fname, Lname, AddressFROM EMPLOYEE, DEPARTMENTWHERE Dname=Research AND Dnumber=Dno;語法1A: SELECT Fname, Ln
41、ame, AddressFROM (EMPLOYEE JOIN DEPARTMENTON Dno=DnumberWHERE Dname=Research;語法1B: SELECT Fname, Lname, AddressFROM (EMPLOYEE NATURAL JOIN(DEPARTMENT AS DEPT(Dname, Dno, Mssn, Msdate)WHERE Dname=Research;,資料庫管理,66,合併表格功能 (3/3),合併條件也可能是巢狀的 查詢範例2：列出所有位在 Stafford 地點的計畫，其計畫編號、控管部門編號，以及部門經理的姓氏、住址和生日 語法2:
42、 SELECT Pnumber, Dnum, Lname, Address, BdateFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE Dnum=Dnumber AND Mgr_ssn=Ssn ANDPlocation=Stafford;語法2A: SELECT Pnumber, Dnum, Lname, Address, BdateFROM (PROJECT JOIN DEPARTMENTON Dnum = Dnumber) JOINEMPLOYEE ON Mgr_ssn = Ssn)WHERE Plocation=Stafford;,資料庫管理,67,常見的
43、聚合函數： COUNT：傳回滿足查詢條件的值組或資料值個數 SUM：傳回滿足查詢條件的資料值的總和 MAX ：傳回滿足查詢條件的資料值的最大值 MIN ：傳回滿足查詢條件的資料值的最小值 AVG ：傳回滿足查詢條件的資料值的平均值查詢範例19：計算出所有員工薪資的總和、最高薪資、最低薪資和平均薪資 SELECT SUM (Salary), MAX (Salary),MIN (Salary), AVG (Salary)FROM EMPLOYEE;有些SQL版本可能不允許在SELECT子句中有一個以上的函數,聚合函數 (1/4),資料庫管理,68,查詢範例20：計算出所有在 Research 部門
44、工作的員工其薪資總和，以及此部門員工的最高薪資、最低薪資與平均薪資 SELECT SUM (Salary), MAX (Salary),MIN (Salary), AVG (Salary)FROM EMPLOYEE, DEPARTMENTWHERE Dno=Dnumber AND Dname=Research;查詢範例23：計算在資料庫中個別薪資值的個數 SELECT COUNT (DISTINCT Salary) FROM EMPLOYEE;,聚合函數 (2/4),資料庫管理,69,聚合函數 (3/4),查詢範例21：計算公司裡員工的總人數 SELECT COUNT (*) FROM EMP
45、LOYEE;查詢範例22：計算公司裡在 Research部門工作的員工總人數 SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENTWHERE Dno=Dnumber AND Dname=Research;,資料庫管理,70,聚合函數 (4/4),查詢範例5：擷取所有具有兩個或兩個以上眷屬的員工姓名 SELECT Lname, Fname FROM EMPLOYEEWHERE (SELECT COUNT(*)FROM DEPENDENTWHERE Ssn=Essn) = 2;,資料庫管理,71,在很多時候會需要將聚合函數應用在關聯中，根據某些屬性值分類的值組子群組
46、上 每個部門的平均薪資 每個計劃的工作員工人數 分組的作法： 先分割關聯，將屬性值相同的值組組成一個群組 這些屬性被稱為群組化屬性(grouping attribute) 再對每個群組分別使用函數 SQL提供GROUP BY子句來指定群組化屬性 群組化屬性要同時出現在SELECT子句中,分組：Group By子句 (1/4),資料庫管理,72,查詢範例24：列出每個部門的編號，以及此部門的工作員工人數和平均薪資 SELECT Dno, COUNT (*), AVG (Salary)FROM EMPLOYEEGROUP BY Dno;將EMPLOYEE值組分成數個群組，每個群組都有相同的群組化屬
47、性Dno值 而在每個值組群組中分別執行COUNT與AVG函數 SELECT子句只包含群組化屬性及在每個值組群組上執行的函數,分組：Group By子句 (2/4),資料庫管理,73,查詢範例25：擷取每一個計畫的計畫編號、計畫名稱、以及在此計畫裡工作的員工人數 SELECT Pnumber, Pname, COUNT(*)FROM PROJECT, WORKS_ONWHERE Pnumber=PnoGROUP BY Pnumber, Pname;此例的分組與函數要等到兩個關聯合併(JOIN)之後才會執行,分組：Group By子句 (3/4),資料庫管理,74,分組：Group By子句 (4
48、/4),查詢範例27：擷取每個計畫的計畫編號、計畫名稱、以及在此計畫中工作的5號部門員工人數 SELECT Pnumber, Pname, COUNT(*)FROM PROJECT, WORKS_ON, EMPLOYEEWHERE Pnumber=Pno AND Ssn=Essn AND Dno=5GROUP BY Pnumber, Pname;,資料庫管理,75,有時候只需要擷取滿足特定條件群組的函數值 此時可使用HAVING子句在群組 (而不是個別值組) 上指定選擇條件查詢範例26：對工作員工在兩人以上的每個計畫，擷取其計畫編號、計畫名稱以及計畫中的工作員工人數 SELECT Pnumbe
49、r, Pname, COUNT(*)FROM PROJECT, WORKS_ONWHERE Pnumber=PnoGROUP BY Pnumber, PnameHAVING COUNT(*) 2;,HAVING子句 (1/3),資料庫管理,76,HAVING子句 (2/3),資料庫管理,77,HAVING子句 (3/3),查詢範例28：對每個有超過5名員工的部門，擷取部門號碼及薪資超過 $40,000的員工總人數 SELECT Dnumber, COUNT (*)FORM DEPARTMENT, EMPLOYEEWHERE Dnumber=Dno AND Salary40000 AND Dno IN (SELECT DnoFROM EMPLOYEEGROUP BY DnoHAVING COUNT (*)5)GROUP BY Dnumber;查詢範例28A：對有5名以上員工且每位員工的薪資皆超過 $40,000的部門，擷取部門號碼及其員工總人數 SELECT Dname, COUNT (*)FROM DEPARTMENT, EMPLOYEEWHERE Dnumber=Dno AND Salary40000GROUP BY DnameHAVING COUNT (*) 5;,
