Microsoft PowerPoint - EMDBCh08SQL1.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - EMDBCh08SQL1.ppt"

登浑
9 years ago
Views:

1 第 8 章 SQL-99: 綱要定義基本限制與查詢學習重點 CREATE TABLE DROP TABLE ALTER TABLE CREATE SCHEMA 參考完整性限制的選項 SQL2 與 SQL-99 的新增資料型態 SQL 中的基本查詢語法 SELECT-FROM-WHERE 結構簡單的 SQL 查詢別名 * 和 DISTINCT 的用法未指定的 WHERE 子句集合運算巢狀查詢與相互關聯的巢狀查詢 2

2 學習重點 EXISTS 函數明確指定的集合 SQL 查詢中的 NULL SQL2 的合併表格功能聚合函數分組與 HAVING 子句子字串比對與算術運算 ORDER BY SQL 查詢語法總整理 INSERT DELETE UPDATE 3 變更資料定義限制或綱要用來建立 (CREATE) 刪除 (DROP) 和修改 (ALTER) 資料庫中表格 ( 關聯 ) 的描述 4

3 CREATE TABLE 用來建立一個新的關聯, 其方式為指定關聯的名稱每個屬性即它們的資料型態 (INTEGER FLOAT DECIMAL(i,j) CHAR(n) VARCHAR(n)) 在屬性上還可能會指定限制 ( 如 NOT NULL) CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) ); 5 CREATE TABLE 在 SQL2 可使用 CREATE TABLE 命令來指定主鍵屬性替代鍵和參考完整性限制 ( 外來鍵 ) 鍵值屬性是使用 PRIMARY KEY 和 UNIQUE 子句來指定 CREATE TABLE DEPT ( DNAMEVARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ); 6

4 DROP TABLE 用來刪除綱要裡的關聯 ( 基底表格 ) 及其定義之後在任何查詢或其他命令中就不能再用到這個關聯, 因為它的定義資訊已經不存在了範例 : DROP TABLE DEPENDENT; 7 ALTER TABLE 用來變更基底關聯的定義假如是新增一個屬性, 則在剛執行完此命令時, 關聯中所有值組的這個新屬性其值都是 NULL, 因此這時不允許有 NOT NULL 限制範例 : ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); 之後資料庫使用者還是必須替每筆 EMPLOYEE 值組輸入新的屬性 JOB 的值這可以使用 UPDATE 命令來做 8

5 SQL2 與 SQL-99 的新增功能 CREATE SCHEMA REFERENTIAL INTEGRITY OPTIONS 9 CREATE SCHEMA 指定名稱建立一個新的資料庫綱要 10

6 參考完整性限制的選項可針對參考完整性限制 ( 外來鍵 ) 指定 RESTRICT CASCADE SET NULL 或 SET DEFAULT CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ON DELETE SET DEFAULT ON UPDATE CASCADE ); 11 參考完整性限制的選項 ( 續 ) CREATE TABLE EMP ( ENAME VARCHAR(30) NOT NULL, ESSN CHAR(9), BDATE DATE, DNO INTEGER DEFAULT 1, SUPERSSN CHAR(9), PRIMARY KEY (ESSN), FOREIGN KEY (DNO) REFERENCES DEPT ON DELETE SET DEFAULT ON UPDATE CASCADE, FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE ); 12

7 SQL2 與 SQL-99 的新增資料型態包括 DATE TIME 和 TIMESTAMP 資料型態 DATE: 由年 - 月 - 日所組成, 格式為 yyyy-mm-dd TIME: 由時 : 分 : 秒所組成, 格式為 hh:mm:ss TIME(i): 由時 : 分 : 秒加上代表幾分之一秒的數字 i 所組成格式為 hh:mm:ss:ii...i 13 SQL2 與 SQL-99 的新增資料型態 TIMESTAMP ( 時間戳記 ): 由 DATE 和 TIME 元件所組成 INTERVAL ( 期間 ): 指定一個相對值, 而不是絕對值可能是 YEAR/MONTH 期間或 DAY/TIME 期間當它相加或相減另一個絕對值, 結果可能是正數或負數, 結果也會是絕對值 14

8 SQL 中的基本查詢語法 SQL 有個很重要的基本敘述, 可以讓我們從資料庫中擷取資訊, 也就是 SELECT 敘述它與第 6 章所討論的關聯式代數的 SELECT 運算是不一樣的 SQL 與關聯式模型之間有個很重要的差異 :SQL 允許表格 ( 即關聯 ) 中存在兩筆或多筆所有屬性值完全相同的值組因此, 通常 SQL 表格並非一個值組的集合, 因為集合不允許有重複的值組 SQL 表格是值組的多重集合 (multiset, 或稱為 bag) 不過 SQL 關聯如果有指定 PRIMARY KEY 或 UNIQUE 屬性, 或是在 SELECT 命令中加上 DISTINCT 選項, 此時的 SQL 關聯就必須是值組的集合 15 SELECT-FROM-WHERE 結構基本的 SELECT 敘述, 有時也稱為 SELECT-FROM- WHERE 區塊 (block) SELECT FROM WHERE <attribute list> <table list> <condition> <attribute list> 是屬性名稱的列表, 在查詢時需要參考這些屬性的值 <table list> 列出處裡查詢時會用到的關聯 <condition> 是條件 ( 布林 ) 運算式, 用來識別查詢時所要擷取的值組 16

9 範例關聯式資料庫綱要 ( 圖 5.5) 17 範例資料庫的某個狀態 ( 圖 5.6) 18

10 簡單的 SQL 查詢基本的 SQL 查詢是對應關聯式代數中的 SELECT PROJECT 和 JOIN 運算接下來所有的範例都是使用 COMPANY 資料庫每個簡單查詢範例是針對一個關聯查詢範例 0: 擷取名叫 'John B. Smith' 員工的生日與住址 Q0: SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME='John' AND MINIT='B AND LNAME='Smith 類似於關聯式代數的 SELECT-PROJECT 運算配對, 其中 SELECT 子句負責指定投影屬性, 而 WHERE 子句則負責指定選擇條件不過, 查詢的結果可能會有重複的值組 19 簡單的 SQL 查詢 ( 續 ) 查詢範例 1: 擷取所有在 'Research' 部門工作的員工的姓名與住址 Q1:SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO 類似關聯式代數運算中的 SELECT-PROJECT-JOIN (DNAME= Research ) 是選擇條件 ( 相當於關聯式代數的 SELECT 運算 ) (DNUMBER=DNO) 則是合併條件 ( 相當於在關聯式代數的 JOIN 運算 ) 20

11 簡單的 SQL 查詢 ( 續 ) 查詢範例 2: 列出所有位在 'Stafford' 地點的計畫其計畫編號控管部門編號, 以及部門經理的姓氏住址和生日 Q2:SELECT ADDRESS FROM WHERE AND PNUMBER, DNUM, LNAME, BDATE, PROJECT, DEPARTMENT, EMPLOYEE DNUM=DNUMBER AND MGRSSN=SSN PLOCATION='Stafford' 在 Q2 裡有兩個合併條件合併條件 DNUM=DNUMBER 使得計劃與其控管部門產生關聯合併條件 MGRSSN=SSN 則讓控管部門與管理此部門的員工產生關聯 21 別名 * 和 DISTINCT 空的 WHERE 子句在 SQL 中, 只要屬性是屬於不同的關聯, 就可以讓兩個或多個屬性使用同樣的名稱若查詢會參考到兩個或多個同名的屬性, 就必須用關聯名稱來修飾 (qualify) 屬性名稱, 做法是將關聯名稱放在屬性名稱之前, 並用英文的句點 (.) 來分隔範例 : EMPLOYEE.LNAME DEPARTMENT.DNAME 22

12 別名有些查詢需要對同一個關聯參考兩次在這類情況要對關聯名稱 aliases are given to the relation name 查詢範例 8: 擷取每一位員工的姓名與其直屬上司的姓名 Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S WHERE E.SUPERSSN=S.SSN 在 Q8 中的替代關聯名稱 E 和 S 被稱作 EMPLOYEE 關聯的別名 (alias) 或值組變數 (tuple) 23 別名 ( 續 ) 我們可以將 E 和 S 想像成 EMPLOYEE 的兩份不同的副本 ; 是代表扮演部屬角色的員工, 而 S 則是代表扮演上司角色的員工別名可以用在任何 SQL 查詢中也可以使用 AS 關鍵字來指定別名 Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPERSSN=S.SSN 24

13 未指定的 WHERE 子句假如沒有 WHERE 的子句, 代表沒有選擇條件, 因此 FROM 子句裡所指定關聯的所有值組都會被選取這就相當於條件是 WHERE TRUE 查詢範例 9: 在資料庫中選擇所有員工的 SSN 資料 Q9: SELECT SSN FROM EMPLOYEE 假如在 FROM 子句中指定一個以上的關聯, 而且沒有 WHERE 子句, 則表示結果等於這些關聯的 CROSS PRODUCT, 也就是所有可能的值組組合都會被選取 25 未指定的 WHERE 子句 ( 續 ) 查詢範例 10: 選擇 EMPLOYEE SSN 與 DEPARTMENT DNAME 的所有組合 Q10:SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT 在 WHERE 子句中確實指定每一個選擇條件與合併條件是非常重要的, 否則可能會產生不正確或者是相當龐大的關聯 26

14 * 的用法在 SQL 中, 假如要擷取所選值組的所有屬性值, 只需要用一個星號 (*) 即可, 這個星號就代表所有的屬性, 例如 : Q1C: SELECT * FROM EMPLOYEE WHERE DNO=5 Q1D: SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNO=DNUMBER 27 DISTINCT 的用法 SQL 通常不會將關聯視為集合, 因此可以出現重複的值組為了消除查詢結果中的重複值組, 可使用關鍵字 DISTINCT 例如 Q11 的結果可能會有重複的 SALARY 值, 而 Q11A 則不會有任何重複值 Q11:SELECT SALARY FROM EMPLOYEE Q11A: SELECT DISTINCT SALARY FROM EMPLOYEE 28

15 集合運算 SQL 已經直接整合關聯式代數的某些集合運算通常都會有聯集運算 (UNION), 有些 SQL 版本還會有差集 (MINUS) 和交集 (INTERSECT) 運算這些集合運算所產生的關聯是值組的集合 ; 也就是說, 重複的值組會在結果中被除去集合運算只能應用在聯集相容的關聯上, 所以必須先確定要運算的兩個關聯具有相同的屬性, 而且這些屬性出現在兩個關聯的順序也相同 29 集合運算 ( 續 ) 查詢範例 4: 列出姓氏為 'Smith' 的員工所參與的所有計畫, 不論該員工是計畫的工作人員或是管理此計畫的部門經理 Q4: (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION (SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith') 30

16 巢狀查詢所謂的巢狀查詢 (nested query) 是指在一個查詢的 WHERE 子句內, 含有完整的 SELECT-FROM- WHERE 區塊此時這個外部的 WHERE 查詢被稱為外部查詢 (outer query) 很多之前的查詢範例都可以使用巢狀查詢來改寫查詢範例 1: 擷取所有在 'Research' 部門工作的員工的姓名與住址 Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' ) 31 巢狀查詢 ( 續 ) 由巢狀查詢先選出 Research 部門的編號外部查詢來選擇其 DNO 值屬於巢狀查詢結果中的 EMPLOYEE 值組這裡的比較運算子 IN 是 v 值與由 V 值所組成的集合 ( 或多重集合 ), 若 v 隸屬於 V 則結果為 TRUE 通常可以使用好幾層巢狀查詢假如屬性沒有明確指定是哪個關聯, 那麼就以最內層 (innermost) 的查詢所宣告的關聯為準此例的巢狀查詢與外部查詢沒有關聯 32

17 相互關聯的巢狀查詢假如巢狀查詢中 WHERE 子句中的條件, 會參考到宣告在外部查詢的關聯裡的某些屬性, 就稱這兩個查詢是相互關聯的 (correlated) 相互關聯的巢狀查詢其結果與外部查詢關聯的每個值組 ( 或值組組合 ) 不同查詢範例 16: 擷取眷屬的名字和性別與員工本人相同的員工姓名 Q16: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE E.FNAME=DEPENDENT_NAME AND E.SEX=D.SEX); 33 相互關聯的巢狀查詢 ( 續 ) 在 Q16 中, 巢狀查詢其結果與外部查詢關聯的結果值組不同以巢狀 SELECT-FROM-WHERE 區塊所撰寫, 而且使用 = 或 IN 比較運算子的查詢, 一定可以改寫成單一區塊的查詢例如 Q16 可以改寫成 Q16A: Q16A: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E.SSN=D.ESSN AND E.SEX=D.SEX AND E.FNAME=D.DEPENDENT_NAME 34

18 EXISTS 函數 EXISTS 函數, 是用來檢查相互關聯的巢狀查詢的結果是否為空的 ( 沒有任何值組 ) 將查詢範例 16 用 EXISTS 改寫成 Q16B 如下 : Q16B: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE EXISTS (SELECT * FROM DEPENDENT WHERE E.SSN=ESSN AND E.SEX=SEX AND E.FNAME=DEPENDENT_NAME 35 EXISTS 函數 ( 續 ) 查詢範例 6: 擷取沒有眷屬的員工姓名 Q6: SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) 在 Q6 中, 相互關聯的巢狀查詢會擷取所有與 EMPLOYEE 值組相關聯的 DEPENDENT 值組, 如果不存在則此 EMPLOYEE 值組會被選取對於 SQL 的表達能力而言,EXISTS 是必要的 36

19 明確指定的集合也可以在 WHERE 子句中, 使用明確數值的集合來取代巢狀查詢查詢範例 17: 擷取所有在 1 2 或 3 號計畫工作的員工的社會安全號碼 Q13: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3) 37 SQL 查詢中的 NULL SQL 可以檢查某個值是否為 NULL ( 未知或無法取得或不適用 ) SQL 是使用 IS 或 IS NOT 來比較 NULL, 因為 SQL 將每一個空值都視為與其他的空值不同, 所以不應該比較是否相等查詢範例 18: 擷取所有沒有上司的員工姓名 Q14: SELECT FNAME, LNAME FROMEMPLOYEE WHERE SUPERSSN IS NULL 注意 : 在指定合併條件時, 合併屬性為空值的值組不會被包含在結果內 38

20 SQL2 的合併表格功能可以在 FROM 子句中指定合併運算所得結果的表格看起來和其他關聯一樣, 不過它是合併運算的結果使用者可以指定各種不同類型的合併運算 ( 一般的 theta JOIN NATURAL JOIN LEFT OUTER JOIN RIGHT OUTER JOIN CROSS JOIN 等 ) 39 SQL2 的合併表格功能 ( 續 ) 範例 : Q8: SELECTE.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S WHERE E.SUPERSSN=S.SSN 可以改寫成 : Q8: SELECTE.FNAME, E.LNAME, S.FNAME, S.LNAME FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEES ON E.SUPERSSN=S.SSN) 40

21 SQL2 的合併表格功能 ( 續 ) Q1: SELECTFNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO 可以改寫成 : Q1A:SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO) WHERE DNAME= Research 或者是 : Q1B:SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEPT(DNAME, DNO, MSSN, MSDATE) WHERE DNAME='Research 41 SQL2 的合併表格功能 ( 續 ) 另一個範例 : Q2 可以改寫如下合併條件也可能是巢狀的 ; 也就是說, 要合併的表格本身就是一個已合併的表格 Q2A: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM (PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN) ) WHERE PLOCATION='Stafford 42

22 聚合函數包括 COUNT SUM MAX MIN 和 AVG 查詢範例 19: 計算出所有員工薪資的總和最高薪資最低薪資和平均薪資 Q19: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROMEMPLOYEE 有些 SQL 版本可能不允許在 SELECT 子句中有一個以上的函數 43 聚合函數 ( 續 ) 查詢範例 20: 計算出所有在 'Research' 部門工作的員工其薪資總和, 以及此部門員工的最高薪資最低薪資與平均薪資 Q20: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROMEMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research' 44

23 聚合函數 ( 續 ) 查詢範例 21 和 22: 計算公司裡員工的總數 (Q21), 以及在 Research 部門工作的員工總數 (Q22) Q21: SELECT COUNT (*) FROM EMPLOYEE Q22: SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research 45 分組在很多時候會需要將聚合函數應用在關聯中, 根據某些屬性值分類的值組子群組上每個子群組是由指定的群組化屬性 (grouping attribute) 分組而成, 每一組的群組化屬性值是相同的真對每個子群組分別使用函數 SQL 提供 GROUP BY 子句用來指定群組化屬性, 它一定要出現在 SELECT 子句中 46

24 分組 ( 續 ) 查詢範例 24: 列出每個部門的編號, 以及此部門的工作員工人數和平均薪資 Q24:SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO 在 Q24 將 EMPLOYEE 值組分成數個群組, 每個群組都有相同的群組化屬性 DNO 值而在每個值組群組中分別執行 COUNT 與 AVG 函數 SELECT 子句只包含群組化屬性及在每個值組群組上執行的函數合併條件可與分組功能搭配使用 47 分組 ( 續 ) 查詢範例 25: 擷取每一個計畫的計畫編號計畫名稱以及在此計畫裡工作的員工人數 Q25:SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME 此例的分組與函數要等到兩個關聯合併之後才會執行 48

25 HAVING 子句有時候會只需要擷取滿足特定條件群組的函數值此時可使用 HAVING 子句在群組 ( 而不是個別值組 ) 上指定選擇條件 49 HAVING 子句 ( 續 ) 查詢範例 26: 對工作員工在兩人以上的每個計畫, 擷取其計畫編號計畫名稱以及計畫中的工作員工人數 Q26: SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2 50

26 子字串比對 LIKE 比較運算子可用來比對子字串部份字串的指定方式是藉由兩個保留字元 : 百分比 % 字元可取代任意數目的字元, 底線 _ 字元則可取代單一字元 51 子字串比對 ( 續 ) 查詢範例 12: 擷取所有住址在 Houston, Texas 的員工也就是說 ADDRESS 屬性的值必須包含子字串 'Houston,TX Q12: SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston,TX% 52

27 子字串比對 ( 續 ) 查詢範例 12A: 擷取所有在 1950 年代出生的員工根據日期的格式, 此例 5 必須是字串的第 3 個字元, 因此 BDATE 值是 5 _, 這裡的底線字元可以是任何一個字元 Q12A: SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE 5 _ 53 算術運算在數值的資料或屬性上可使用一般的標準四則運算 (+ - * /) 查詢範例 13: 顯示出所有工作於 'ProductX' 計畫的員工加薪 10% 後的薪資結果 Q13: SELECT FNAME, LNAME, 1.1*SALARY FROMEMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX 54

28 ORDER BY ORDER BY 子句是用來針對值組內的一或多個屬性值, 將查詢結果的值組加以排序查詢範例 15: 擷取員工與他們所工作計畫的清單, 在清單中先針對部門號碼排序, 每個部門內再依員工姓名的字母順序排序 Q15: SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME 55 ORDER BY ( 續 ) 預設的順序是遞增排序使用關鍵字 DESC 可變成遞減排序, 而關鍵字 ASC 則是用來更明確的指定遞增排序 56

29 SQL 查詢語法總整理 SQL 的查詢最多可包含 6 個子句, 但只有前兩個 SELECT 與 FROM 子句是必要的子句是以下列的順序來指定 : SELECT FROM [WHERE <attribute list> <table list> <condition>] [GROUP BY <grouping attribute(s)>] [HAVING <group condition>] [ORDER BY <attribute list>] 57 SQL 查詢語法總整理 ( 續 ) SELECT 子句中列出被擷取的屬性或函數 FROM 子句是指定所有在查詢時需要的關聯 ( 表格 ), 包括合併的關聯, 但不包括巢狀查詢所需的關聯 WHERE 子句是指定從這些關聯裡選取值組的條件, 同時視需要加入合併條件 GROUP BY 子句中指定群組化屬性 HAVING 子句所指定的是群組的選取條件 ORDER BY 用來指定查詢結果的顯示順序理論上查詢最早執行的會是 FROM 子句, 接著是 WHERE 子句, 然後是 GROUP BY 和 HAVING 子句, 最後是 ORDER BY 子句用來將查詢結果排序 58

30 SQL 的修改命令在 SQL 中有 3 個命令可以用來修改資料庫 : INSERT DELETE 和 UPDATE 59 INSERT 最簡單的 INSERT 形式是在關聯中加入一筆值組在指定值組內欄位的值時, 必須以 CREATE TABLE 命令指定的屬性順序來排列 60

31 INSERT ( 續 ) 範例 : U1: INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', ' ', '30-DEC-52', '98 Oak Forest,Katy,TX', 'M', 37000,' ', 4 ) 另一種形式是允許使用者明確的指定對應到 INSERT 命令裡的數值的屬性名稱可以有 NULL 或 DEFAULT 值的屬性則可以不指定資料值範例 : 輸入一筆只指定 FNAME LNAME 和 SSN 屬性的新 EMPLOYEE 值組 U1A: INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES ('Richard', 'Marini', ' ') 61 INSERT ( 續 ) 注意 : 只有以 DDL 命令指定的限制,DBMS 才會在變更資料庫時自動遵守 INSERT 命令有種變化形式, 就是在一個查詢中插入多個值組到關聯 62

32 INSERT ( 續 ) 範例 : 假設要建立具有每個部門的名稱員工人數與總薪資的暫存表格首先使用 U3A 建立 DEPTS_INFO 表格, 然後再使用 U3B 從資料庫中擷取摘要資訊 U3A: CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10), NO_OF_EMPSINTEGER, TOTAL_SAL INTEGER); U3B: INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME ; 63 INSERT ( 續 ) 注意 :DEPTS_INFO 表格可能不是最新的 ; 也就是說, 假如在執行過 U3B 之後才更新 DEPARTMENT 或 EMPLOYEE 關聯, 則 DEPTS_INFO 內的資訊就會變成過時的資訊因此最好是建立所謂的視界 (view), 才能讓表格的資料維持最新 64

33 DELETE 用來將值組從關聯中移除包括 WHERE 子句來選取要被刪除的值組一次只能從一個表格中刪除值組 ( 除非在參考完整性限制上有指定 CASCADE) 假如沒有指定 WHERE 子句, 則會將值組全部刪除, 最後把這個表格當作一個空的表格留在資料庫中被刪除的值組個數是根據符合 WHERE 子句條件的個數一定會遵守參考完整性限制 65 DELETE ( 續 ) 範例 : U4A: DELETE FROM EMPLOYEE WHERE LNAME='Brown U4B: DELETE FROM EMPLOYEE WHERE SSN=' U4C: DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research') U4D: DELETE FROM EMPLOYEE 66

34 UPDATE 用來修改一或多個被選取值組的屬性值 WHERE 子句從關聯中選取要修改的值組 SET 子句是用來指定被修改的屬性和它的新數值每個命令修改同一個關聯裡的值組一定會遵守參考完整性限制 67 UPDATE ( 續 ) 範例 : 將 10 號計劃的位置與控管部門編號分別改成 'Bellaire' 和 5 U5: UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10 68

35 UPDATE ( 續 ) 範例 : 將所有在 'Research' 部門工作的員工加薪 10% U6: UPDATE EMPLOYEE SET SALARY = SALARY *1.1 WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research') 因為修改後的薪資值是依據每個值組原先的薪資值, 所以需要參考 SALARY 屬性兩次 U6 中右邊的 SALARY 屬性的參考動作是參考在修改前的舊 SALARY 值左邊的是參考在修改之後的新 SALARY 值 69 學習評量請列出 SQL 所允許的合法的屬性資料型態請描述 SQL 查詢中的 6 個子句的語法? 並分別說明這 6 個子句中分別可以指定什麼種類的元件? 這 6 個子句中哪些是必要的? 那些是選用的? 請說明在理論上 SQL 查詢的 6 個子句的執行順序請說明 SQL 在執行比較運算時如何處理 NULL 值假如在 SQL 查詢中有用到聚合函數時, 又是如何處理 NULL 值? 如果在群組化屬性中有 NULL 時又是如何處理? 70

36 學習評量以圖 1.2 的資料庫為例, 其綱要如圖 2.1 在此綱要中哪一個是必須保持的參考完整性限制? 請撰寫適當的 SQL DDL 敘述來定義資料庫請以 SQL 語言來撰寫下列關於圖 5.5 資料庫的查詢, 假設每個查詢都應用在圖 5.6 的資料庫上請分別說明查詢的結果 a. 對員工平均薪資超過 $30,000 的每個部門, 擷取其部門名稱與此部門的工作員工人數 b. 假設想要擷取在每個部門工作的男性員工人數, 而不是如習題 (a) 的全部員工人數請問能夠用 SQL 來寫出這樣的查詢嗎? 理由為何? 71

Chapter 8

Chapter 8 第 8 章 Part I SQL 查詢第 8 章 SQL 查詢 SQL 簡介結構化查詢語言 SQL (Structured Query Language) 關連式資料庫系統的標準語言 SQL 功能包括資料定義查詢與更新敘述既是 DDL, 也是 DML 定義資料庫視界安全性與權限完整性及交易控制具備嵌入 Java COBOL 或 C/C++ 等語言的機制第 8 章 SQL 查詢 2