MySQL資料庫教學

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

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

Database Languages SQL (Structured Query Language, 結構化查詢語言 ) 是一種用來與關連式資料庫系統對話而使用的標準語言, 由 IBM 於 1970 年代所研發出來的, 目前所有市場的資料庫管理系統幾乎都支援 SQL 美國國家標準協會 (ANSI) 與國際標準組織 (ISO) 於 1987 年認定 SQL 的標準版本 (SQL/87 或稱 SQL/1) SQL 語法的版本持續演進中 SQL-87, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011

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) 用來管理資料庫的交易動作

市面上所有商用資料庫系統皆支援 ANSI SQL 語法 由於各廠商為突顯自家資料庫系統之獨特性, 會對部份的 ANSI SQL 語法做些微修改, 以支援自家資料庫系統所發展之各項功能 若需操作不同資料庫系統, 建議準備有介紹各家資料庫系統 SQL 語法之辭典, 以應付不時之需

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

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

MySQL 的數值串型態 :

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

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

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

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

建立 刪除 更改表格 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 ] ); 對具有特定用途之欄位加以括號內最後一行指令不需要逗號!! 設定

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) );

CREATE TABLE Employee ( Ssn CHAR(10) NOT NULL, Name 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 );

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

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

在 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 標準語法

CREATE TABLE Employee_01 ( Ssn CHAR(10) NOT NULL, Name 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;

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

若表格間有外來鍵存在時之建表 刪表順序 建表順序 : 先建立 被參考表格 ( 即 :Project, Department), 再建立 參考表格 ( 要讓外來鍵有對象可以參考 ) 在 MySQL 中, 不論是參考表格, 還是被參考表格, 只要與外來鍵設定有關的表格, 皆需設定成 InnoDB 類型的表格, 參考完整性限 制方可正常執行 參考表格 A 被參考表格 B 刪表順序 : 先刪除參考表格, 再刪除被參考表格 ( 要讓外來鍵有對象可 以參考 )

假設有三個表格需要建立 :Department, Project, Employee 其中: Employee 有兩個外來鍵, 分別指向 Department 與 Project 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;

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

ALTER TABLE: 更改一個關聯 ( 表格 ) 中之某欄位的基本定義與限制 包括 : 增加 (ADD) 欄位 刪除 (DROP) 欄位 修改 (ALTER/CHANGE/MODIFY ) 欄位預設值 定義 或名稱 更改表格名稱 (RENAME) 更改表格類型 (ENGINE) ALTER TABLE < 表格名 > ADD/DROP/ALTER/CHANGE/MODIFY/ENGINE/R ENAME

增加欄位 : 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;

修改欄位 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;

修改欄位 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;

練習範例 請建立 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

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

INSERT: 插入一筆新的紀錄到關聯中 INSERT INTO <table name>[(attribute1, attribute2, )] VALUES ( ) 例 : 假設現有一關聯表格如下 : employee(ssn, Name, Address, Dept_id, Proj_id, Salary) 範例 1: 此例為插入部份欄位資料 INSERT INTO employee (Ssn, Name, Address, Salary) VALUES ( F111111111, 操勞哥, 苗栗, 20000); 範例 2: 此例為插入全部欄位資料 INSERT INTO employee VALUES ( F111111112, 打混王, 台北, 021, 035, 25000); 若多個表格間有外來鍵時, 先插入 被參考表格 的資料, 再插入 參考表格 的資料

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

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

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

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

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

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

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

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

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

欄位名稱重覆處理 複雜的查詢任務可能需要多個來源表格, 而使用這些表格時, 可能會發生位於不同表格中之同名欄位被放在一起使用的情況 若沒有明確指示, 資料庫系統不知道這些同名欄位是隸屬於哪一個表格 因此, 需進行欄位名稱重覆處理 作法 : 這些同名欄位之名稱在使用時, 需加上表格名稱 : table_name.attribute_name 如 : 專案. 城市, 供應商. 城市 專案 (Project) 供應商 (Supplier)

範例題組 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 數量 ;

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

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

欄位與表格之別名設定 可以對使用到的關聯表格或是欄位取別名 (Alias): < 原表格名稱 > AS < 別名 > 或是 < 原表格名稱 > < 別名 > < 原欄位名稱 > AS < 別名 > 或是 < 原欄位名稱 > < 別名 >

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

聚合函數中 COUNT 的用法 : COUNT(*): 計算表格中共有幾筆非空記錄 COUNT( 欄位名稱 ): 此屬性有幾筆非空值 COUNT(DISTINCT 欄位名稱 ): 此屬性有幾筆不同的非空值

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

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

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

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

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

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

範例題組 6 ( 學習重點 : 巢狀查詢運算子 ; Multi-row Subquery; 標準子查詢 ) 依照 slide 9 的四個表格, 列出供應商 大勝 有供應給專案使用的零件平均重量 Sol: SELECT avg(component. 重量 ) FROM Component WHERE Component. 零件代號 IN (SELECT Distinct PSC. 零件代號 FROM Supplier AS S, Project_supp_Component AS PSC WHERE S. 供應商代號 =PSC. 供應商代號 AND S. 供應商名稱 =' 大勝 '); 列出重量大於供應商代號 S2 有供應給專案使用之所有零件的零件名稱 Sol: SELECT 零件名稱 FROM Component WHERE 重量 >ALL (SELECT C. 重量 FROM Project_supp_Component AS PSC, Component AS C WHERE PSC. 零件代號 =C. 零件代號 AND PSC. 供應商代號 ='S2');

列出重量大於供應商代號 S2 有供應給專案使用之任一零件的零件名稱 Sol: SELECT 零件名稱 FROM Component WHERE 重量 >ANY (SELECT C. 重量 FROM Project_supp_Component AS PSC, Component AS C WHERE PSC. 零件代號 =C. 零件代號 AND PSC. 供應商代號 ='S2');

[NOT] EXISTS 的用法 EXISTS: 此運算子在當子查詢有資料產生時, 會回傳 TRUE, 否則便回傳 FALSE 同時, 子查詢若有資料產生, 會暫存於記憶體以待主查詢使用 NOT EXISTS: EXISTS 的反義詞

[NOT] EXISTS 在標準子查詢中 : 主查詢使用的表格與子查詢的表格是完全無關的 ( 互為獨立 ) 子查詢是否有結果產生, 對主查詢來說只 有 / 沒有 TRUE/FALSE, 通常只是做為主查詢工作的開關 若子查詢的回傳結果為 TRUE, 則主查詢中的 SELECT FROM 便可被執行 ; 反之, 便不會被執行 [NOT] EXISTS 在關聯子查詢中 : 主查詢使用的表格與子查詢是有關的 子查詢是否有結果產生, 對主查詢來說除了是 TRUE/FALSE 的開關功能外, 子查詢的結果也會影響主查詢

範例題組 7 [NOT] EXISTS 在標準子查詢 : SELECT 供應商名稱 FROM Supplier WHERE EXISTS (SELECT * FROM Supplier WHERE 城市 =' 台南 '); 主查詢與子查詢雖用同樣的表格, 但彼此無關 子查詢在此僅告知主查詢它是否有回傳值, 所以用 SELECT * 即可 ( 要用實際欄位名亦可, 只是結果相同, 沒啥意義 )

[NOT] EXISTS 在關聯子查詢 : 依照 slide 9 的四個表格, 列出有供應零件 'P1' 的供應商名稱 Sol: SELECT S. 供應商名稱 FROM Supplier AS S WHERE EXISTS (SELECT * FROM Project_supp_Component AS PSC WHERE PSC. 供應商代號 =S. 供應商代號 AND PSC. 零件代號 ='P1'); 主查詢的表格, 在子查詢中會使用到, 它將與子查詢之表格做合併處理, 所以是有關的 子查詢在此除告知主查詢它是否有回傳值, 其查詢結果也會影響主查詢

子查詢結果 主查詢結果 回傳 TRUE 時, 主查詢對象

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

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

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

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

EMPLOYEES (Base Table) View

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

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

Data Control Language, DCL ( 資料控制語言 ) 此語言主要從事資料庫的權限控管, 包含 Grant, Revoke, Alter Password 等指令 Grant: 建立新的 user, 並增加該 user 在資料庫的相關操作權限 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; 若沒有設密碼, 表示此使用者帳號不需密碼即可登入資料庫系統

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