MySQL資料庫教學

Transcription

1 SQL: 結構化查詢語言 國立聯合大學資訊管理學系陳士杰老師

2 Outlines Database Languages SQL 資料型態 Data Query Language, DQL ( 資料查詢語言 ) Data Definition Language, DDL ( 資料定義語言 ) Data Manipulation Language, DML ( 資料處理語言 ) Data Control Language, DCL ( 資料控制語言 ) 講義 :Ch. 6 原文 :Ch. 8, Ch. 9

3 Database Languages SQL (Structured Query Language, 結構化查詢語言 ) 是一種用來與關連式資料庫系統對話而使用的標準語言, 由 IBM 於 1970 年代所研發出來的, 目前所有市場的資料庫管理系統幾乎都支援 SQL 美國國家標準協會 (ANSI) 與國際標準組織 (ISO) 於 1987 年認定 SQL 的標準版本 (SQL/87 或稱 SQL/1) 後來的 SQL/2 (SQL/92) 則對 SQL/1 進行大量的擴充 目前版本為 1999 年所提出的 SQL/99 或稱 SQL/3, 對物件導向 DB 與分散式 DB 有提供支援, 並加入了程式設計的功能預存程式 (stored procedure)

4 SQL 指令的種類 : 資料定義語言 (Data Definition Language, DDL) 用來宣告 ( 或建立 ) 資料庫物件 針對 Table, View 或 Database 做建立 (Create) 刪除(Drop) 更改(Alter) 等動作 資料處理語言 (Data Manipulation Language, DML) 用來操作資料庫中的資料 針對 Table 內的 Data, 做插入 (Insert) 更新(Update) 刪除(Delete) 等動作 資料控制語言 (Data Control Language, DCL) 用來從事資料庫的權限控管, 如 Grant Revoke Alter Password 等動作 資料查詢語言 (Data Query Language, DQL) 用來查詢資料庫中的資料 ( 某些書將 DQL 併入 DML 一起討論 ) 資料管理指令 (Data Administration Commands) 用來從事資料庫的稽核與分析 交易控制指令 (Transactional Control Commands) 用來管理資料庫的交易動作

5 SQL 資料型態 字串 (Character Strings) CHAR(n): 固定長度字元串 (n 為字元個數 ) VARCHAR(n): 變動長度字元串 BIT(n): 固定長度位元串 (n 為位元個數 ) BIT VARING(n): 變動長度位元串 MySQL 的字串型態 :

6 數值串 (Numeric Strings) INT, INTEGER: 整數 DEC(m,n), DECIMAL(m,n), NUMERIC(m,n): 格式化數值 (m: 總位數或精確度,n: 小數位數 ) SMALLINT: 短整數 FLOAT: 浮點數 REAL: 單精度實數 (32bits) DOUBLE PRECISION: 雙精度實數 (64bits)

7 MySQL 的數值串型態 :

8 日期 / 時間 (Date/Time) 資料型態 DATE: 一般格式為 YYYY-MM-DD TIME: 一般格式為 HH:MM:SS TIMESTAMP: 時間戳記, 由 DATE+TIME+ 六位以上小數秒數 用以記錄交易進入系統的時間順序 INTERVAL: 時間區間 MySQL 的日期 / 時間型態 :

9 供應商 (Supplier) 專案供應零件 (Project_supp_Component) 專案 (Project) 零件 (Component)

10 Data Definition Language, DDL ( 資料定義語言 ) -(1) DDL 主要有 CREATE, DROP, ALTER 三個指令, 並可針 對以下三個資料庫物件進行操作 : 資料庫 (database, 或稱 Schema) 表格 (Table) 觀點 景觀 (View)

11 建立 刪除資料庫 在進行 Create Drop 資料庫的指令操作時, 對 資料庫 這個關鍵字有時是用 Schema 來表示 CREATE DATABASE (SCHEMA): 建立一個新的 DB (DB 綱要 ) CREATE SCHEMA < 資料庫名 >; 或 CREATE DATABASE < 資料庫名 >; 例 :CREATE SCHEMA (DATABASE) Jacy_Database; DROP SCHEMA (DATABASE): 刪除一個 DB (DB 綱目 ) DROP SCHEMA < 資料庫名 >; 或 DROP DATABASE < 資料庫名 >; 例 :DROP SCHEMA (DATABASE) Jacy_Database; 上述語法在 MySQL 中亦適用, 且指令不區分大小寫

12 建立 刪除 更改表格 CREATE TABLE: 建立一個新的關聯 ( 表格 ) CREATE TABLE <table name>( 指定表格中的欄位 < 欄位名 1> <data type> [Null/Not null] [DEFAULT < 預設值 >], < 欄位名 2> <data type> [Null/Not null] [DEFAULT < 預設值 >], < 欄位名 n> <data type> [Null/Not null] [DEFAULT < 預設值 >], PRIMARY KEY(< 欄位名 >), UNIQUE(< 欄位名 >), FOREIGN KEY(< 欄位名 >) REFERENCES < 表格名 ( 欄位名 )> [ON DELETE /ON UPDATE ] ); 對具有特定用途之欄位加以括號內最後一行指令不需要逗號!! 設定

13 CREATE TABLE Department (Dname CHAR(10) NOT NULL, Dno INT NOT NULL, Dadd CHAR(20), PRIMARY KEY(Dno) ); CREATE TABLE Project (Pname CHAR(10) NOT NULL, Pno INT NOT NULL, PRIMARY KEY(Pno) );

14 CREATE TABLE Employee ( Ssn CHAR(10) NOT NULL, Emp_id CHAR(10) NOT NULL, Address VARCHAR(50), Dept_id INT, Proj_id INT, Salary NUMERIC(8,1) NOT NULL DEFAULT 18000, PRIMARY KEY(Ssn), UNIQUE(Emp_id), FOREIGN KEY(Proj_id) REFERENCES Project(Pno), FOREIGN KEY(Dept_id) REFERENCES Department(Dno) ON DELETE Cascade );

15 註 : 設定外來鍵時, 關於 ON DELETE 與 ON UPDATE 的處理動作有以下幾種 : RESTRICT (No Action): 發生違反完整性限制的操作時,DBMS 不允許該操作執行 CASCADE: 發生違反參考完整性限制的操作時, 外來鍵內的資料連帶更新或刪除 SET NULL: 發生違反參考完整性限制的操作時, 外來鍵內的資料設為空值 SET DEFAULT: 發生違反完整性限制的操作時, 外來鍵內的資料設為預設值

16

17

18

19 關於 MySQL 資料表格有兩種常用的類型 : MyISAM 為 MySQL 預設的表格類型 ( 當 ENGINE 那一行指令未填寫的話 ) 成熟 穩定 容易管理 若無特殊需求, 應以此類型為主 InnoDB 支援交易 (Transaction) 機制 外來鍵 (Foreign Key) 當機復原( 若系統未遭受損壞時使用 ) MyISAM v.s. InnoDB 若想追求使用空間與執行效率, 建議採用 MyISAM 若著重交易工作 安全性考量 有外來鍵的使用需求或是可能有多人同時修改資料的情況, 則建議採用 InnoDB

20 在 MySQL 中, 建立一個新的表格 : CREATE TABLE <table name> ( <attribute name 1> <data type> <(not) null> <default value>, <attribute name 2> <data type> <(not) null> <default value>, <attribute name n> <data type> <(not) null> <default value>, PRIMARY KEY(<attribute name>), UNIQUE(<attribute name>), FOREIGN KEY(<attribute name>) REFERENCES < 表格名 ( 欄位名 )> [ON DELETE /ON UPDATE ] ) ENGINE = MyISAM/InnoDB; ( 此行若不打, 則預設為 MyISAM 格式 ) 於 MySQL 刪除 更改一個表格, 可採用後面即將介紹的 SQL 標準語法

21 CREATE TABLE Employee_01 ( Ssn CHAR(10) NOT NULL, Emp_id CHAR(10) NOT NULL, Address VARCHAR(50), Dept_id INT not null, Proj_id INT not null, Salary NUMERIC(8,1) NOT NULL DEFAULT 18000, Primary Key(Ssn) ) ENGINE = InnoDB;

22 DROP TABLE: 刪除一個關聯 ( 表格 ) DROP TABLE < 表格名 >; 例 : DROP TABLE Employee_01; 注意 : 當要刪除掉彼此有參考關係的表格時, 先刪除參考表格, 再刪除被參考表格 參考表格 A 被參考表格 B

23 若表格間有外來鍵存在時之建表 刪表順序 假設有三個表格需要建立 :Department, Project, Employee 其中: Employee 有兩個外來鍵, 分別指向 Department 與 Project 建表順序 : 先建立 被參考表格 ( 即 :Project, Department), 再建立 參考表格 ( 即 :Employee) 在 MySQL 中, 不論是參考表格, 還是被參考表格, 只要與外來鍵設定有關的表格, 皆需設定成 InnoDB 類型的表格, 參考完整性限制方可正常執行 反之, 表格刪除時, 先刪除參考表格, 再刪除被參考表格

24 CREATE TABLE Department (Dname CHAR(10) NOT NULL, Dno INT NOT NULL, Dadd CHAR(20), PRIMARY KEY(Dno) ) ENGINE=INNODB; CREATE TABLE Project (Pname CHAR(10) NOT NULL, Pno INT NOT NULL, PRIMARY KEY(Pno) ) ENGINE=INNODB;

25 CREATE TABLE Employee( Ssn CHAR(10) NOT NULL, Emp_id CHAR(10) NOT NULL, Address VARCHAR(50), Dept_id INT, Proj_id INT, Salary NUMERIC(8,1) NOT NULL DEFAULT 18000, PRIMARY KEY(Ssn), UNIQUE(Emp_id), FOREIGN KEY(Proj_id) REFERENCES Project(Pno), FOREIGN KEY(Dept_id) REFERENCES Department(Dno) ON Delete CASCADE ) ENGINE=INNODB;

26 ALTER TABLE: 更改一個關聯 ( 表格 ) 中之某欄位的基本定義與限制 包括 : 增加欄位 刪除欄位 修改欄位定義或條件等 ALTER TABLE < 表格名 > ADD/DROP/ALTER

27 增加欄位 : ALTER TABLE < 表格名 > ADD < 新欄位名 > <data type> [Null/Not null] [DEFAULT < 預設值 >]; 例 :ALTER TABLE Emp ADD SEX CHAR(1); 刪除欄位 : ALTER TABLE < 表格名 > DROP < 欄位名 > [RESTRICT/CASCADE]; 例 :ALTER TABLE Emp DROP Sex;

28 修改欄位 1 ( 增改 / 刪除預設值 ): ALTER TABLE < 表格名 > ALTER < 欄位名 > DROP DEFAULT; ALTER TABLE < 表格名 > ALTER < 欄位名 > SET DEFAULT < 預設值 >; 例 1:ALTER TABLE Emp ALTER Salary DROP DEFAULT; 例 2:ALTER TABLE Emp ALTER Salary SET DEFAULT 18000; 修改欄位 2 ( 更改欄位定義, 不含修改欄位名 ): ALTER TABLE < 表格名 > MODIFY < 欄位名 > <data type> [Null/Not null]; Null/Not null 若不設定, 則預設為 Null 例 :ALTER TABLE Emp MODIFY Salary decimal(8,1) Not null;

29 修改欄位 3 ( 更改欄位定義, 含修改欄位名 ): ALTER TABLE < 表格名 > CHANGE < 舊欄位名 > < 新欄位名 > <data type> [Null/Not null]; Null/Not null 若不設定, 則預設為 Null 例 :ALTER TABLE Emp 更改表格類型 : CHANGE Salary Sal decimal(8,1); ALTER TABLE < 表格名 > ENGINE < 類型 > ; 例 :ALTER TABLE Emp 更改表格名稱 : ENGINE InnoDB; ALTER TABLE < 舊表格名 > RENAME < 新表格名 > ; 例 :ALTER TABLE Emp RENAME Emp_01;

30 練習範例 請建立 slide 9 的四個表格 令 : Supplier 供應商代號 CHAR(4) 供應商名稱 CHAR(10) Component 零件代號 CHAR(4) 零件名稱 CHAR(10) 城市 CHAR(6) 顏色 CHAR(4) Project 重量 INT 專案代號 CHAR(4) 專案名稱 CHAR(10) Project_supp_Component 供應商代號 CHAR(4) 城市 CHAR(6) 專案代號 CHAR(4) 零件代號 CHAR(4) 數量 INT 註 : 外來鍵皆設成 ON Delete CASCADE 表格類型為 InnoDB

31 Data Manipulation Language, DML ( 資料處理語言 ) DML 是針對關聯中的資料部份從事處理, 包含 Insert, Update, Delete 指令 INSERT: 插入一筆新的紀錄到關聯中 DELETE: 根據 WHERE 條件刪除關聯中的紀錄 UPDATE: 根據 WHERE 條件更改關聯中的屬性值

32 INSERT: 插入一筆新的紀錄到關聯中 INSERT INTO <table name>[(attribute1, attribute2, )] VALUES ( ) 例 : 假設現有一關聯表格 EMPLOYEE(Ssn, Emp_id, Address, Dept_id, Proj_id, Salary) // 此例為插入部份欄位資料 INSERT INTO EMPLOYEE(Ssn, Emp_id, Address, Salary) VALUES ( F , 會計一部, 苗栗, 20000); // 此例為插入全部欄位資料 INSERT INTO EMPLOYEE VALUES ( F , 會計二部, 台北, 021, 035, 25000); 若多個表格間有外來鍵時, 先插入 被參考表格 的資料, 再插入 參考表格 的資料

33 DELETE: 根據 WHERE 條件刪除關聯中的紀錄 DELETE FROM <table name> WHERE <condition> 例 : 假設現有一關聯表格 EMPLOYEE(Ssn, Emp_id, Address, Dept_id, Proj_id, Salary) DELETE FROM EMPLOYEE WHERE Salary < 20000; DELETE FROM EMPLOYEE WHERE Ssn = F ; DELETE FROM EMPLOYEE;

34 UPDATE: 根據 WHERE 條件更改關聯中的屬性值 UPDATE < 表格名 > SET < 屬性名 >=<new value> [, < 屬性名 2>=<new value 2> ] WHERE < 條件 > 例 : 假設現有一關聯表格 EMPLOYEE(Ssn, Emp_id, Address, Dept_id, Proj_id, Salary) UPDATE EMPLOYEE SET Address = ' 台東 ', Salary =10000 WHERE Ssn = 'F ';

35 練習範例 請為 slide 9 所建立的四個空表格插入其應有的資料

36 Data Query Language, DQL ( 資料查詢語言 ) DQL 用以查詢資料庫的相關資料, 語法如下 : SELECT <attribute list> FROM <table list> WHERE <condition> GROUP BY <grouping attributes> HAVING <grouping condition> ORDER BY <column name> ASC/DESC 執行順序 : FROM WHERE GROUP BY HAVING SELECT ORDER BY

37 各個子句的說明 : SELECT: 指定查詢所欲輸出的欄位 FROM: 指定查詢所牽涉到的表格 WHERE: 指定查詢的限制條件 GROUP BY: 將查詢資料依照某個指定的欄位加以分群 HAVING: GROUP BY 的限制條件, 必須配合 GROUP BY 使用 ORDER BY: 依照某屬性值作遞增 (ASE) 或遞減 (DESC) 排序 ( 預設為 ASC)

38 SQL 查詢語言的六個子句中, 只有 Select 和 From 是必要的, 其它的依需求而定 例 :SELECT * FROM Supplier; ( 將表格 Supplier 中, 所有欄位的資料列出 ) SELECT * 表示選擇 FROM 中關聯表格的所有屬性 查詢中若沒有 WHERE 子句, 表示沒有限制條件, 即 SELECT 出 FROM 中關聯表格的所有紀錄 例 :SELECT * FROM Supplier WHERE 城市 = 台南 ; ( 將表格 Supplier 中, 所在城市位於台南的所有供應商資料列出 )

39 SQL 語法的運算子 SQL 語法中可能會用到的運算子 :

40 範例題組 1 依照 slide 9 的四個表格, 用 SQL 回答下列問題 : 列出所有供應商名稱 Sol: SELECT 供應商名稱 FROM Supplier; 列出所有重量在 20 以上, 且不為黑色的零件名稱 顏色 重量 ( 學習重點 : 用到不同的運算子 ) Sol: SELECT 零件名稱, 顏色, 重量 FROM Component WHERE 重量 >=20 AND 顏色!= 黑 ;

41 欄位重覆名稱處理與別名設定 若有多個表格中的欄位名稱重覆, 可改寫成 table_name.attribute_name 加以區分 可以對使用到的關聯表格或是欄位取別名 : < 表格名稱 > AS < 別名 > 或是 < 表格名稱 > < 別名 > < 欄位名稱 > AS < 別名 > 或是 < 欄位名稱 > < 別名 >

42 範例題組 2 依照 slide 9 的四個表格, 用 SQL 回答下列問題 : 依照數量由小到大列出供應商 S1 所參與之專案名稱 零件名稱, 以及數量 ( 學習重點 :1. 排序子句 ORDER BY; 2. 多個表格的欄位名稱重覆之處理 ) Sol: SELECT 專案名稱, 零件名稱, 數量 FROM Project, Component, Project_supp_Component WHERE Project. 專案代號 = Project_supp_Component. 專案代號 AND Component. 零件代號 = Project_supp_Component. 零件代號 AND Project_supp_Component. 供應商代號 = S1 ORDER BY 數量 ;

43 由專案供應零件中, 統計各零件之總數量並列出零件代號 ( 學習重點 : 分群子句 GROUP BY) Sol: SELECT 零件代號, SUM( 數量 ) FROM Project_supp_Component GROUP BY 零件代號 ; 由專案供應零件中, 統計總數超過 1000 之零件的總數量並列出其代號 ( 學習重點 :1. GROUP BY 專用的限制條件子句 HAVING; 2. 對欄位取別名 ) Sol: SELECT 零件代號, SUM( 數量 ) AS 總數量 FROM Project_supp_Component GROUP BY 零件代號 HAVING SUM( 數量 )>1000;

44 SQL 語法的函數 聚合函數 : COUNT(attribute_name): 計算屬性值個數 SUM(attribute_name): 計算屬性中數值的總合 AVG(attribute_name): 計算屬性中數值的平均 MAX(attribute_name): 找出屬性中數值的最大值 MIN(attribute_name): 找出屬性中數值的最小值 ( 其它類型的 SQL 函數請參考網路講義 )

45 範例題組 3 依照 slide 9 的四個表格, 用 SQL 回答下列問題 : 列出所有供應商 大勝 有供應的零件中, 重量最重之重量為何 Sol: ( 學習重點 :1. 聚合函數 MAX; 2. 對表格取別名 ) SELECT MAX( 數量 ) FROM Supplier as S, Component as C, Project_supp_Component P WHERE S. 供應商代號 = P. 供應商代號 AND C. 零件代號 = P. 零件代號 AND S. 供應商名稱 = 大勝 ; 列出位於高雄的供應商數目 Sol: ( 學習重點 : 聚合函數 COUNT) SELECT COUNT(*) FROM Supplier WHERE 城市 = 高雄 ;

46 聚合函數中 COUNT 的用法 : COUNT(*): 計算有幾筆記錄 COUNT( 欄位名稱 ): 此屬性有幾筆非空值 COUNT(DISTINCT 欄位名稱 ): 此屬性有幾筆不同的非空值 例 : 表格 Employee 共有 1000 筆記錄, 其中有 900 人分別屬於 10 個不同的部門,100 人無部門 SELECT COUNT(*) FROM Employee; 共 1000 筆 SELECT COUNT(Dept) FROM Employee; 共 900 筆 SELECT COUNT(Eno) FROM Employee; 共 1000 筆 SELECT COUNT(DISTINCT Dept) FROM Employee; 共 10 筆

47 範例題組 4 若表格 Supplier 加入下列資料 : INSERT INTO Supplier( 供應商代號, 供應商名稱 ) VALUES ( S5, 歐羅肥 '); 請執行並區分以下三個 SQL 指令 ( 學習重點 : 聚合函數 COUNT) 1. SELECT COUNT(*) FROM Supplier; 2. SELECT COUNT( 城市 ) FROM Supplier; 3. SELECT COUNT(DISTINCT 城市 ) FROM Supplier;

48 巢狀 SQL 查詢 又稱子查詢 (Subquery), 即查詢中又包含另一個查詢 問題 : 有哪些員工的 Salary 高於員工編號為 F 的 員工之 Salary? Main query ( 主查詢 ): 哪些員工的 Salary 高於員工編號為 F 的員工之 Salary? Subquery ( 子查詢 ): F 的員工之 Salary 為何?

49 Sol: SELECT Ssn, Emp_id, Salary FROM Employee WHERE Salary > (SELECT Salary FROM Employee WHERE Ssn='F '); 巢狀查詢的語法 SELECT FROM WHERE 欄位名表格名 expr operator (SELECT 欄位名 FROM 表格名 ); 在實作上, 子查詢不會僅出現在 WHERE 子句後!! 巢狀查詢語法的變化非常多, 在此不多做介紹 但是請記住一個概念 : 子查詢的回傳結果也是一個二維表格!!!

50 巢狀查詢的分類 根據 Subquery 回傳結果的筆數, 巢狀查詢可分成 : Single-row Subquery Multi-row Subquery 根據 Subquery 是否可以獨立執行, 巢狀查詢可分成 : 標準子查詢 : 子查詢可以獨立運作 關聯子查詢 : 子查詢會使用到主查詢的表格, 無法獨立運作 前述的範例為 Single-row 的標準子查詢 其 Subquery 僅回傳單一筆的資料, 且子查詢可以獨立運作

51 Single-row Subquery 巢狀查詢運算子 (Operator) Multi-row Subquery IN, ANY, ALL Operator Meaning = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to

52 IN: 比較 IN 之前的資料, 是否存在於 IN 後面子查詢所回傳的結果集合中 NOT IN 則為 IN 的反義 ALL, ANY: 用來與子查詢所回傳的結果集合做數值的比較 ( 通常會配合 < > 或 NOT 等運算子來用 )

53 範例題組 5 ( 學習重點 : 巢狀查詢運算子 ; Multi-row Subquery) 依照 slide 9 的四個表格, 列出有哪些供應商所在的城市沒有任何專案? Sol: SELECT 供應商代號, 供應商名稱 FROM Supplier WHERE 城市 NOT IN (SELECT Distinct 城市 FROM Project); 有哪些員工的 Salary 是大於所有住在台北的員工之 Salary? Sol: SELECT Ssn, Address, Salary FROM Employee WHERE Salary >ALL (SELECT Salary FROM Employee WHERE Address=' 台北 ') AND Address<>' 台北 ';

54 有哪些員工的 Salary 是大於任何一位住在台北的員工之 Salary? Sol: SELECT Ssn, Address, Salary FROM Employee WHERE Salary >ANY (SELECT Salary FROM Employee WHERE Address=' 台北 ') AND Address<>' 台北 ';

55 EXISTS: 此運算子僅是在搜尋子查詢的回傳結果集合中, 是否存在某個 Row 的資料!! 只要有存在, 則便不會再搜尋下去了 ( 即便後面還有存在合乎要求的 Row), 且會設定為 TRUE 若不存在, 則會設定為 FALSE NOT EXISTS: EXISTS 的反義詞, 所有 Row 皆搜尋過, 且皆必須為 FALSE,NOT EXISTS 才成立

56 [NOT] EXISTS 的不同用法 [NOT] EXISTS 在標準子查詢中, 通常只是做為開關 若子查詢的回傳結果讓 [NOT] EXISTS 為 TRUE, 則主查詢中的 SELECT FROM 便可被執行 反之, 主查詢中的 SELECT FROM 便不會被執行 [NOT] EXISTS 在關聯子查詢中較有意義

57 範例題組 6 [NOT] EXISTS 在標準子查詢 : SELECT 供應商名稱 FROM Supplier WHERE [NOT] EXISTS (SELECT * FROM Component WHERE 重量 >=80); 子查詢中用 SELECT *, 是因為只是想測試回傳給主查詢的結果集合中, 滿足條件的列是否存在, 不用考慮回傳的欄位是哪一些!!

58 [NOT] EXISTS 在關聯子查詢 : 依照 slide 9 的四個表格, 查詢與專案所在城市相同的供應商資料 Sol: SELECT 供應商代號, 供應商名稱, 城市 FROM Supplier WHERE EXISTS (SELECT * FROM Project WHERE Project. 城市 =Supplier. 城市 ); 依照 slide 9 的四個表格, 查詢與專案所在城市不同的供應商資料 Sol: SELECT 供應商代號, 供應商名稱, 城市 FROM Supplier WHERE NOT EXISTS (SELECT * FROM Project WHERE Project. 城市 =Supplier. 城市 );

59 其它函數 IS NULL: 判斷屬性值是否為 NULL BETWEEN: 指定屬性值必須介於一個最小值 ( 含 ) 與最大值 ( 含 ) 之間 LIKE: 利用萬用字元 (% 及 _) 做相似字串的比對 百分符號 % 表示 0~ 多個任意字元 底線符號 _ 表示單一個任意字元 例 :% 大 在 大 字前可有任意個字元 ; 在 大 字後必須恰有兩個字元, 比兩個字元多或少都不行!!

60 範例題組 7 依照 slide 9 的四個表格, 用 SQL 回答下列問題 : 找出所在城市未知的供應商代號與名稱 Sol: SELECT 供應商代號, 供應商名稱 FROM Supplier WHERE 城市 IS NULL; 找出零件名稱第一個字為 螺 的零件之名稱 顏色 重量 Sol: SELECT 零件名稱, 顏色, 重量 FROM Component WHERE 零件名稱 LIKE 螺 % ;

61 找出專案名稱第二個字為 星 的專案之代號 名稱 所在城市 Sol: SELECT 專案代號, 專案名稱, 城市 FROM Project WHERE 專案名稱 LIKE _ 星 ; 找出重量介於 16~63 的零件之名稱 顏色 重量 Sol: SELECT 零件名稱, 顏色, 重量 FROM Component WHERE 重量 BETWEEN 16 AND 63;

62 Data Definition Language, DDL ( 資料定義語言 ) -(2) DDL 主要有 CREATE, DROP, ALTER 三個指令, 並可針對以下三個物件進行操作 : 資料庫 (database) 表格 (Table) 觀點 (View) 觀點 (View): 是由其它表格所衍生出來的關聯表格,View 不需要以實體的形式存在, 即 :View 並不需要實際儲存資料, 可視為虛擬表格 (Virtual Table) 其內容是以 SELECT 指令的執行結果構成, 呈現的方式仍是以二維表格為主 所以其定義方式正是以 SELECT 為基礎

63 建立 查詢與刪除觀點 (View) CREATE VIEW: 建立一個新的觀點 CREATE VIEW <view name> AS SELECT FROM WHERE 例 : 建立 薪資大於等於 元的員工 的 View CREATE VIEW EMP_Salary AS SELECT Ssn, Emp_id, Salary FROM EMPLOYEE WHERE Salary >= 30000; DROP VIEW: 刪除一個觀點 DROP VIEW <view name> CASCADE/RESTRICT 例 : 刪除 薪資大於等於 元的員工 的 View DROP VIEW EMP_Salary CASCADE;

64 觀點的優點 : 可提供邏輯資料獨立性 (Logical Data Independence) 隱藏不需要或具私密性的資料 同一關聯可建立多種不同的觀點, 讓使用者以不同的角度看同一份資料 觀點的缺點 : 觀點的更新 (Update) 有諸多限制, 無法提供與實際表格完全相同的操作 在 MySQL 中, 可使用前面所介紹之 SQL 標準語法來建構 查詢與刪除一個 view 利用 Show Tables, 也可以看到所建立出來的 view 和其它的表格是放在一起的, 就像是一個真正的表格一樣, 只是它是屬於虛擬表格

65 Data Control Language, DCL ( 資料控制語言 ) 此語言主要從事資料庫的權限控管, 包含 Grant, Revoke, Alter Password 等指令 Grant: 增加資料庫的權限內容 GRANT <authority> ON <object> TO <users> [identified by 密碼 ] 例 : GRANT Select ON EMPLOYEE(FName,LName) TO Jacy1 identified by 123 ; GRANT Delete ON EMPLOYEE TO Jacy2; 若沒有設密碼, 表示此使用者帳號不需密碼即可登入資料庫系統

66 Revoke: 取消某使用者之權限 REVOKE <authority> ON <object> FROM <users> 例 : REVOKE Delete ON EMPLOYEE FROM Jacy2; <DCL 的 MySQL 實作已於實務課上機說明, 請自行參考 >