Chapter 8

Size: px

Start display at page:

Download "Chapter 8"

堀牲束
9 years ago
Views:

1 第 8 章 SQL-99: 綱要定義限制查詢與視界 1

2 CONTENTS Create Table Drop Table Alter Table SQL 的修改命令 INSERT, DELETE, UPDATE CREAT ASSERTION VIEW 2

3 變更資料定義限制或綱要用來建立 (CREATE) 刪除 (DROP) 和修改 (ALTER) 資料庫中表格 ( 關聯 ) 的描述 3

4 CREATE TABLE 用來建立一個新的關聯指定關聯的名稱指定每個屬性及它們的資料型態 INTEGER FLOAT DECIMAL(i,j) CHAR(n) VARCHAR(n) 4

5 DEP-EMP Database 5

6 CREATE TABLE 在屬性上還可能會指定限制如 NOT NULL 預設是 NULL CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) ); 6

7 CREATE TABLE 屬性可以指定限制預設是 NULL 可以指定預設值 DNO INTEGER DEFAULT 1, 限制定義域 Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21); CREATE DOMAIN D_NUM AS INTEGER CHECK (D_NUM > 0 AND D_NUM < 21) 7

8 CREATE TABLE 指定主鍵屬性替代鍵和參考完整性限制 ( 外來鍵 ) 鍵值屬性是用 PRIMARY KEY 和 UNIQUE 子句來指定 CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPLOPYEE ); 8

9 DROP TABLE 用來刪除綱要裡的關聯 ( 基底表格 ) 及其定義之後在任何查詢或其他命令中就不能再用到這個關聯, 因為它的定義資訊已經不存在了範例 : DROP TABLE DEPENDENT; 9

10 ALTER TABLE 用來變更基底關聯的定義假如是新增一個屬性, 則在剛執行完此命令時, 關聯中所有值組的這個新屬性其值都是 NULL, 因此這時不允許有 NOT NULL 限制範例 : ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); 之後資料庫使用者還是必須替每筆 EMPLOYEE 值組輸入新的屬性 JOB 的值這可以使用 UPDATE 命令來做 10

11 CREATE SCHEMA 指定名稱建立一個新的資料庫綱要 CREATE SCHEMA COMPANY AUTHORIZATION Jsmith; 11

12 參考完整性限制的選項可針對參考完整性限制 ( 外來鍵 ) 指定 RESTRICT CASCADE SET NULL 或 SET DEFAULT CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE ON DELETE SET DEFAULT ON UPDATE CASCADE); 12

13 參考完整性限制的選項 ( 續 ) CREATE TABLE EMPLOYEE( ENAME VARCHAR(30) NOT NULL, ESSN CHAR(9), BDATE DATE, DNO INTEGER DEFAULT 1, SUPERSSN CHAR(9), PRIMARY KEY (ESSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT ON DELETE SET DEFAULT ON UPDATE CASCADE, FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE ON DELETE SET NULL ON UPDATE CASCADE); 13

14 SQL2 與 SQL-99 的新增資料型態包括 DATE TIME 和 TIMESTAMP 資料型態 DATE: 由年 - 月 - 日所組成, 格式為 yyyy-mm-dd TIME: 由時 : 分 : 秒所組成, 格式為 hh:mm:ss TIME(i): 由時 : 分 : 秒加上代表幾分之一秒的數字 i 所組成格式為 hh:mm:ss:ii...i 14

15 SQL2 與 SQL-99 的新增資料型態 TIMESTAMP ( 時間戳記 ): 由 DATE 和 TIME 元件所組成 INTERVAL ( 期間 ): 指定一個相對值, 而不是絕對值可能是 YEAR/MONTH 期間或 DAY/TIME 期間當它相加或相減另一個絕對值, 結果可能是正數或負數, 結果也會是絕對值 15

16 SQL 的修改命令在 SQL 中有 3 個命令可以用來修改資料庫資料內容 : INSERT DELETE 和 UPDATE 16

17 INSERT 最簡單的 INSERT 形式是在關聯中加入一筆值組在指定值組內欄位的值時, 必須以 CREATE TABLE 命令指定的屬性順序來排列範例 : U1: INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', ' ', '30-DEC-52', '98 Oak Forest,Katy,TX', 'M', 37000,' ', 4 ) 17

18 INSERT ( 續 ) 另一種形式是允許使用者明確的指定對應到 INSERT 命令裡的數值的屬性名稱可以有 NULL 值的屬性則可以不指定資料值範例 : 輸入一筆只指定 FNAME LNAME 和 SSN 屬性的新 EMPLOYEE 值組 U1A: INSERT INTO EMPLOYEE (FNAME, LNAME,SSN) VALUES ('Richard', 'Marini', ' ') 18

19 INSERT ( 續 ) 注意 : 只有以 DDL 命令指定的限制,DBMS 才會在變更資料庫時自動遵守 INSERT 命令有種變化形式, 就是在一個查詢中插入多個值組到關聯 19

20 INSERT ( 續 ) 範例 : 假設要建立具有每個部門的名稱員工人數與總薪資的暫存表格首先使用 U3A 建立 DEPTS_INFO 表格, 然後再使用 U3B 從資料庫中擷取摘要資訊 U3A: CREATE TABLE DEPTS_INFO (DEPT_NAME NO_OF_EMPS TOTAL_SAL VARCHAR(10), INTEGER, 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 ; 20

21 DELETE 用來將值組從關聯中移除包括 WHERE 子句來選取要被刪除的值組一次只能從一個表格中刪除值組 ( 除非在參考完整性限制上有指定 CASCADE) 假如沒有指定 WHERE 子句, 則會將值組全部刪除, 最後把這個表格當作一個空表格留在資料庫中被刪除的值組個數是根據符合 WHERE 子句條件的個數一定會遵守參考完整性限制 21

22 DELETE ( 續 ) 範例 : U4A: DELETE FROM WHERE U4B: DELETE FROM WHERE EMPLOYEE LNAME='Brown EMPLOYEE SSN=' U4C: DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research') U4D: DELETE FROM EMPLOYEE 22

23 UPDATE 用來修改一或多個被選取值組的屬性值 WHERE 子句從關聯中選取要修改的值組 SET 子句是用來指定被修改的屬性和新數值每個命令修改同一個關聯裡的值組一定會遵守參考完整性限制 23

24 UPDATE ( 續 ) 範例 : 將 10 號計劃的位置與控管部門編號分別改成 'Bellaire' 和 5 U5: UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10 24

25 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 值 25

26 使用宣示敘述指定一般的限制一般性的限制 : 那些不屬於基本 SQL 分類的限制機制 :CREAT ASSERTION 元件包括 : 限制名稱之後的是 CHECK 關鍵字緊接著是一個條件 26

27 宣示敘述範例指定員工的薪資不得超過其工作部門經理的薪資這個限制 CREAT ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS (SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.SALARY > M.SALARY AND E.DNO=D.NUMBER AND D.MGRSSN=M.SSN)) 限制名稱, CHECK, 條件 27

28 使用一般的宣示敘述指定查詢選出那些會違反要求條件的值組, 並在查詢中加入 NOT EXISTS 子句查詢的結果必須是空集合假如查詢結果不是空集合, 那就代表違反宣示敘述 28

29 SQL 中的視界視界 (view) 是指一個從其他表格所衍生出來的虛擬表格 (virtual table) 這會限制可能應用在視界上的更新運算動作因為這個表格可能在實際上並不存在在視界上進行查詢時並不會有任何限制方便用來表達某些運算 29

30 視界的規格 SQL 命令 :CREATE VIEW 一個表格 ( 視界 ) 名稱屬性名稱清單和 ( 例如視界的屬性全都不是由函數或算術運算子產生的, 就不需要指定視界的屬性名稱, 它們將會與定義表格裡的屬性名稱相同 ) 指定視界內容的查詢 30

31 SQL 視界的範例指定另一個不同的 WORKS_ON 表格 CREATE VIEW WORKS_ON_NEW AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME; 31

32 使用虛擬表格我們可以在新建立的視界上指定 SQL 查詢 : SELECT FNAME, LNAME FROM WORKS_ON_NEW WHERE PNAME= Seena ; 等到不需要它的時候, 再刪除該視界 : DROP WORKS_ON_NEW; 32

33 有效率的視界查詢查詢修改 : 將視界查詢修改為針對基底表格的查詢缺點 : 對於複雜的視界執行時會變得很沒有效率特別是假如在一段很短的時間內, 有很多個查詢同時在使用此視界時 33

34 有效率的視界查詢 ( 續 ) 視界具體化 (view materialization): 在實體上真正建立一個暫存的視界表格並維護它假設 : 視界的其他查詢也會跟著使用和維護這個暫存表格注意 : 在基底表格更新時, 必須維護基底表格與視界之間的一致策略 : 遞增更新 34

35 視界的更新假如視界是定義在單一表格上, 而且不含有聚合函數 : 則它的更新動作可以直接對應成基底表格的更新動作如果視界是合併的 : 更新動作可能對映成基底關聯的更新動作但不一定可以 35

36 無法更新的視界有定義使用群組化或聚合函數的視界不能執行更新動作假如視界是定義在使用合併的多個表格上, 通常不能執行更新動作 WITH CHECK OPTION: 假如視界是需要被更新的, 必須在視界定義的結尾加上它這樣系統就會去檢查視界是否可進行更新, 並且去規劃出視界更新時的執行策略 36

37 學習評量 ( 問題回顧 ) 8.1 請列出 SQL 所允許的合法的屬性資料型態 8.2 SQL 是用什麼方式來實作第 5 章所描述的實體完整性與參考完整性限制? 而參考所觸發的動作是什麼? 8.3 請描述 SQL 擷取查詢中的 6 個子句的語法並分別說明這 6 個子句中分別可以指定什麼種類的元件? 這 6 個子句中哪些是必要的? 那些是選用的? 8.4 請說明在理論上 SQL 查詢中的 6 個子句的執行順序 8.5 請說明 SQL 在執行比較運算時如何處理 NULL 值假如在 SQL 查詢中有用到聚合函數時, 是如何處理 NULL 值? 如果在群組化屬性中有 NULL 時又是如何處理? 37

38 學生與課程資訊的資料庫 38

39 學習評量 ( 習題 ) 8.1 上頁學生生與課程資訊的資料庫, 那些欄位是有保持參考完整性的限制? 試撰寫適當的 SQL DDL 敘述來定義資料庫 8.5 針對學生生與課程資訊的資料庫, 替下列工作撰寫 SQL 敘述 (a) 插入一位新學生 <'Johnson', 25, 1, 'MATH'> in (b) 將學生 Smith 的班級改為 2. (c) 插入一筆新課程 <'Knowledge Engineering','COSC4390', 3,'COSC'>. (d) 刪除一位名叫 Smith 且學號為 17 的學生 39

Microsoft PowerPoint - EMDBCh08SQL1.ppt

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