MySQL資料庫教學

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

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

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)

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

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 (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 中 亦 適 用, 且 指 令 不 區 分 大 小 寫

建 立 刪 除 更 改 表 格 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, 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 );

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

關 於 MySQL 資 料 表 格 有 兩 種 常 用 的 類 型 : MyISAM 為 MySQL 預 設 的 表 格 類 型 ( 當 ENGINE 那 一 行 指 令 未 填 寫 的 話 ) 成 熟 穩 定 容 易 管 理 若 無 特 殊 需 求, 應 以 此 類 型 為 主 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 格 式 ) 刪 除 更 改 一 個 表 格 可 採 用 後 面 即 將 介 紹 的 SQL 標 準 語 法

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;

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

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

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

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

增 加 欄 位 : 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, Emp_id, Address, Dept_id, Proj_id, Salary) // 此 例 為 插 入 部 份 欄 位 資 料 INSERT INTO EMPLOYEE(Ssn, Emp_id, Address, Salary) VALUES ( F111111111, 會 計 一 部, 苗 栗, 20000); // 此 例 為 插 入 全 部 欄 位 資 料 INSERT INTO EMPLOYEE VALUES ( F111111112, 會 計 二 部, 台 北, 021, 035, 25000); 若 多 個 表 格 間 有 外 來 鍵 時, 先 插 入 被 參 考 表 格 的 資 料, 再 插 入 參 考 表 格 的 資 料

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 = F111111111 ; DELETE FROM EMPLOYEE;

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 = '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)

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

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

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

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

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

由 專 案 供 應 零 件 中, 統 計 各 零 件 之 總 數 量 並 列 出 零 件 代 號 ( 學 習 重 點 : 分 群 子 句 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;

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

範 例 題 組 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 城 市 = 高 雄 ;

聚 合 函 數 中 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 筆

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

巢 狀 SQL 查 詢 又 稱 子 查 詢 (Subquery), 即 查 詢 中 又 包 含 另 一 個 查 詢 問 題 : 有 哪 些 員 工 的 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 等 運 算 子 來 用 )

範 例 題 組 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<>' 台 北 ';

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

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

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

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

[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. 城 市 );

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

範 例 題 組 7 依 照 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 為 基 礎

建 立 查 詢 與 刪 除 觀 點 (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 CASCADE;

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

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; 若 沒 有 設 密 碼, 表 示 此 使 用 者 帳 號 不 需 密 碼 即 可 登 入 資 料 庫 系 統 Revoke: 取 消 某 使 用 者 之 權 限 REVOKE <authority> ON <object> FROM <users> 例 : REVOKE Delete ON EMPLOYEE FROM Jacy2; <DCL 的 MySQL 實 作 已 於 實 務 課 上 機 說 明, 請 自 行 參 考 >