第四章　SQL 介紹

第 四 章 SQL 介 紹 黃 仁 鵬 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 1

4-1 資 料 庫 共 通 的 語 言 -SQL 像 大 多 數 資 料 庫 相 同,ORACLE 只 會 說 一 種 共 通 的 語 言, 而 這 種 語 言 就 是 SQL (Structural Query Language, 結 構 化 查 詢 語 言 ) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 2

4-2 SQL 的 歷 史 在 70 年 代,E.F.Codd 首 先 提 出 關 聯 式 資 料 模 型 (Relational Data Model) 而 後 IBM 公 司 在 System R 關 聯 式 資 料 庫 管 理 系 統 中, 研 發 出 最 早 的 SQL 語 言 叫 做 SEQUEL (Structured English Query Language) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 3

4.3 SQL 的 基 本 語 言 資 料 定 義 語 言 (Data Definition Language,DDL) 資 料 操 縱 語 言 (Data Manipulation Language,DML) 資 料 控 制 語 言 (Data Control Language, DCL) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 4

資 料 定 義 語 言 (Data Definition Language,DDL) 用 來 定 義 資 料 庫 的 綱 要 (Schema), 如 關 聯 表 名 稱, 關 聯 表 內 部 的 欄 位 屬 性 資 料 型 態 定 義 或 更 改 整 合 限 制 條 件 (Integrity Constraint), 給 予 (Grant) 或 取 消 (Revoke) 權 限 (Privilege) 或 角 色 (Role) 等 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 5

資 料 操 縱 語 言 (Data Manipulation Language,DML) 主 要 提 供 使 用 者 資 料 庫 中 資 料 操 作 的 指 令, 如 新 增 (Insert) 刪 除 (Delete) 修 改 (Update) 與 查 詢 (Select) 等 運 算 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 6

資 料 控 制 語 言 (Data Control Language,DCL) 用 來 控 制 資 料 庫 系 統 內 部 的 異 動 交 易 (Transaction) 處 理 與 系 統 效 能 等 指 令 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 7

然 而 ORACLE 除 了 以 上 三 種 SQL 基 本 語 言 外,ORACLE 又 把 資 料 控 制 語 言 (Data Control Language) 更 細 分 成 : 異 動 交 易 處 理 指 令 (Transaction Control Command) 連 線 會 談 期 間 控 制 指 令 (Session Control Command) 系 統 控 制 指 令 (System Control Command) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 8

異 動 交 易 處 理 指 令 (Transaction Control Command): 用 來 控 制 對 資 料 作 出 的 改 變, 也 就 是 處 理 由 DML 指 令 在 異 動 交 易 過 程 (Transaction) 所 做 的 修 改, 如 委 付 確 認 (Commit) 回 復 (Rollback) 以 及 設 定 儲 存 點 (Savepoint) 等 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 9

連 線 會 談 期 間 控 制 指 令 (Session Control Command): 用 來 動 態 地 控 制 使 用 者 連 線 期 間 的 特 性 (Property), 如 改 變 連 線 會 談 期 間 的 內 定 語 言 關 閉 資 料 庫 鏈 結 (Database Link) 等 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 10

系 統 控 制 指 令 (System Control Command) 用 來 動 態 的 管 理 一 個 ORACLE Instance ( 資 料 庫 實 例 ) 的 特 性 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 11

4-3-1 資 料 定 義 語 言 (Data Definition Language, DDL) 資 料 定 義 語 言 (Data Definition Language,DDL) 的 主 要 功 用 為 : 1. 新 增 修 改 刪 除 物 件 (Object) 2. 給 予 (Grant) 撤 回 (Revoke) 刪 除 (Drop) 權 限 (Privilege) 與 角 色 (Role) 3. 建 立 系 統 稽 核 (Audit) 功 能 4. 在 資 料 字 典 (Data Dictionary) 中 加 入 註 解 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 12

4-3-2 資 料 操 縱 語 言 (Data Manipulation Language) 用 來 處 理 與 查 詢 綱 要 物 件 (Schema Object) 的 資 料 這 些 DML 指 令 並 不 會 自 動 地 做 內 隱 式 委 付 確 認 (Implicitly Commit) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 13

4-3-3 異 動 交 易 處 理 指 令 (Transaction Control Command) 異 動 交 易 處 理 指 令 (Transaction Control Command) 的 主 要 功 用 為 : 處 理 DML 指 令 在 異 動 交 易 過 程 (Transaction) 中 所 做 的 修 改 這 些 指 令 並 不 會 自 動 地 做 內 隱 式 委 付 確 認 (Implicitly Commit) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 14

4-3-4 連 線 會 談 期 控 制 指 令 (Session Control Command) 連 線 會 談 期 間 控 制 指 令 (Session Control Command) 的 主 要 功 用 為 : 它 動 態 地 控 制 使 用 者 連 線 會 談 期 間 的 特 性 這 些 指 令 並 不 會 自 動 地 做 內 隱 式 委 付 確 認 (Implicitly Commit) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 15

4-3-5 系 統 控 制 指 令 (System Control Command) 系 統 控 制 指 令 (System Control Command) 的 主 要 功 用 為 : 它 動 態 地 管 理 ORACLE 實 例 (ORACLE Instance) 的 特 性, 這 些 指 令 不 會 自 動 地 做 內 隱 式 委 付 確 認 (Implicitly Commit) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 16

4-3-6 內 嵌 式 SQL 指 令 (Embedded SQL Command) 內 嵌 式 SQL 指 令 的 主 要 功 用 為 : 它 可 將 資 料 定 義 語 言 (Data Definition Language,DDL) 資 料 操 縱 語 言 (Data Manipulation Language, DML) 異 動 交 易 處 理 指 令 (Transaction Control Command) 等 指 令 放 入 程 序 化 語 言 的 程 式 中 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 17

4-4 資 料 型 態 ANSI-SQL 主 要 提 供 三 種 主 要 的 資 料 型 態 : 字 元 數 字 浮 點 數 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 18

4-5 資 料 型 態 轉 換 在 ORACLE 中 能 夠 將 某 種 資 料 型 態 轉 換 成 另 一 種 資 料 型 態 資 料 型 態 的 轉 換 可 以 透 過 ORACLE 系 統 自 動 轉 換, 或 是 透 過 資 料 型 態 轉 換 函 數 來 完 成 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 19

4-6 基 本 表 格 (BASED TABLE) 一 個 基 本 表 格 (Based Table) 實 質 上 由 兩 部 分 組 成 : 表 格 名 和 一 組 欄 位 名 構 成 的 分 類 資 訊 實 際 存 放 的 資 料 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 20

4-6-1 表 格 名 欄 位 名 的 命 名 規 則 在 使 用 CREATE TABLE 命 令 建 立 表 格 時, 定 義 表 格 名 和 欄 位 名 的 規 則 如 下 : 1. 表 格 名 欄 位 名 必 須 以 字 母 開 頭, 大 小 寫 均 可, 其 後 的 字 元 可 以 由 字 母 數 字 組 成, 也 可 以 包 括 $ # 和 底 線 _ 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 21

2. 同 一 個 使 用 者 的 基 本 表 格 名 必 須 唯 一, 而 且 不 能 與 ORACLE 的 保 留 字 相 同 ; 同 一 基 本 表 格 中 的 欄 位 名 也 須 唯 一, 不 允 許 在 同 一 基 本 表 格 中 兩 個 欄 位 名 相 同 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 22

3. 表 格 名 與 欄 位 名 是 不 區 分 大 小 寫 的 例 如 :FIRSTNAME,FirstName 和 Firstname 均 認 為 是 相 同 的 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 23

4. 如 果 表 格 名 欄 位 名 用 雙 引 號 括 起 來, 則 可 以 不 滿 足 上 述 規 則 如 果 使 用 了 雙 引 號, 則 要 區 分 字 母 的 大 小 寫, 如 FIRSTNAME 與 Firstname 就 不 再 認 為 是 相 同 的 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 24

4-6-2 建 立 基 本 表 與 輸 入 資 料 建 立 基 本 表 命 令 - CREATE TABLE CREATE TABLE < 表 格 名 > < 欄 位 名 1>< 型 態 > [NULL NOT NULL][, < 欄 位 名 2>< 型 態 > [NULL NOT NULL]] 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 25

插 入 資 料 -INSERT INTO INSERT INTO < 表 格 名 >(< 欄 位 名 1>,< 欄 位 名 2>, ) VALUES(< 值 1>,< 值 2>, ); 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 26

關 聯 表 格 內 部 的 整 合 限 制 (Integrity Constraints) 可 分 為 三 種 : 個 体 整 合 限 制 (Entity Integrity Constraints) 值 域 整 合 限 制 (Domain Integrity Constraints) 參 考 整 合 限 制 (Referential Integrity Constraints 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 27

個 体 整 合 限 制 (Entity Integrity Constraints) 它 規 範 關 聯 表 內 部 的 整 合 限 制 條 件, 例 如 : 主 鍵 (Primary Key) 的 值 不 可 為 虛 值 (Null) 欄 位 的 值 是 否 唯 一 (Unique) 欄 位 的 值 是 否 可 為 虛 值 (Null) PRIMARY KEY (< 主 鍵 欄 位 名 >) [NOT] NULL UNIQUE 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 28

值 域 整 合 限 制 (Domain Integrity Constraints) 它 規 範 關 聯 表 內 部 屬 性 值 必 須 在 某 一 限 制 範 圍 之 內, 例 如 : 欄 位 的 型 態 (Type) 與 大 小 (Size) 輸 入 值 的 檢 查 (Check) CHECK DEFAULT 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 29

參 考 整 合 限 制 (Referential Integrity Constraints) 它 規 範 關 聯 表 格 與 關 聯 表 格 之 間 的 整 合 限 制 條 件, 例 如 : 表 格 中 的 外 來 鍵 (Foreign Key) 設 定 FOREIGN KEY 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 30

4-6-3 更 新 資 料 更 新 資 料 -UPDATE SET UPDATE < 表 格 名 > SET < 欄 位 名 1>=< 新 值 1>, WHERE < 被 更 新 記 錄 的 條 件 > < 欄 位 名 2>=< 新 值 2>,... < 欄 位 名 n>=< 新 值 n> 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 31

4-6-4 刪 除 資 料 刪 除 資 料 -DELETE FROM DELETE FROM < 表 格 名 > WHERE < 條 件 > 刪 除 指 令 不 能 只 刪 除 部 分 欄 位 中 的 資 料, 因 此 不 需 要 指 明 欄 位 名 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 32

4-6-5 修 改 基 本 資 料 表 格 (Based Table) 結 構 修 改 基 本 資 料 表 格 結 構 命 令, 主 要 可 分 為 : 1. 新 增 欄 位 定 義 2. 修 改 欄 位 定 義 3. 廢 除 欄 位 定 義 4. 新 增 整 合 限 制 5. 修 改 整 合 限 制 6. 廢 除 整 合 限 制 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 33

新 增 欄 位 定 義 -ALTER TABLE ADD ALTER TABLE < 表 格 名 > ADD (< 欄 位 名 1>< 資 料 型 態 > [,< 欄 位 名 2>< 資 料 型 態 > ]); 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 34

修 改 欄 位 的 定 義 -ALTER TABLE MODIFY ALTER TABLE< 表 格 名 > MODIFY (< 欄 位 名 1> [< 資 料 型 態 >] [NULL NOT NULL] [,< 欄 位 名 2>[< 資 料 型 態 >[NULL NOT NULL]] ]); 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 35

廢 除 欄 位 定 義 --ALTER TABLE DROP COLUMN ALTER TABLE < 表 格 名 > DROP COLUMN < 欄 位 名 > 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 36

新 增 整 合 限 制 (Integrity Constraint)- ALTER TABLE ADD CONSTRAINT ALTER TABLE < 表 格 名 > ADD (CONSTRAINT < 整 合 限 制 名 1>< 整 合 限 制 條 件 1> [, CONSTRAINT < 整 合 限 制 名 2>< 整 合 限 制 條 件 2> ]); 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 37

修 改 整 合 限 制 修 改 整 合 限 制 又 可 分 成 : 實 施 整 合 限 制 (ENABLE) ALTER TABLE < 表 格 > ENABLE CONSTRAINT 暫 時 取 消 整 合 限 制 (DISABLE) ALTER TABLE < 表 格 > DISABLE CONSTRAINT 廢 除 整 合 限 制 ALTER TABLE < 表 格 > DROP CONSTRAINT 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 38

4-6-6 廢 除 基 本 表 格 廢 除 基 本 表 格 命 令 -DROP TABLE 當 要 廢 除 的 基 本 表 格 中 含 有 參 考 整 合 限 制 時, 也 就 是 被 別 的 表 格 參 考 到 表 格 中 的 候 選 鍵 時, 必 須 加 入 CASCADE CONSTRAINTS 才 能 廢 除 表 格 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 39

4-6-7 建 立 表 格 的 建 議 步 驟 1. 廢 除 表 格, 以 免 原 表 格 已 存 在 而 產 生 錯 誤 2. 建 立 表 格 3. 建 立 個 体 整 合 限 制 與 值 域 整 合 限 制 4. 建 立 參 考 整 合 限 制 5. 插 入 資 料 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 40

4-6-8 建 立 表 格 時 加 入 整 合 限 制 此 時 建 立 表 格 的 順 序 就 很 重 要 被 關 聯 的 表 格 要 先 建 立, 例 如 :parent 表 格 要 比 student 表 格 先 建 立 ; parent 表 格 的 資 料 要 比 student 表 格 資 料 先 輸 入 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 41

4-6-9 簡 易 查 詢 簡 易 查 詢 -SELECT SELECT < 查 詢 內 容 > FROM < 表 格 名 > WHERE < 條 件 > ORDER BY < 排 序 內 容 > 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 42

4-7-1 虛 擬 欄 位 虛 擬 欄 位 (Pseudo Column) 不 是 任 何 表 格 的 一 個 真 實 欄 位, 然 而, 當 其 出 現 於 一 個 SELECT 指 令 的 選 擇 列 表 中 時, 它 將 提 供 一 個 具 有 特 殊 或 特 別 意 義 的 值 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 43

4-7-2 虛 擬 表 格 虛 擬 表 格 (DUAL) 是 ORACLE 提 供 的 最 小 的 工 作 表, 它 僅 包 含 一 行 一 列 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 44

4-8 資 料 型 態 轉 換 函 數 HEXTORAW(CHAR) RAWTOCHAR(RAW) TO_CHAR (DATE, 特 定 日 期 格 式 ) TO_CHAR (NUMBER, 特 定 數 值 格 式 ) TO_DATE(CHAR, 特 定 日 期 格 式 ) TO_NUMBER(CHAR, 特 定 數 值 格 式 ) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 45

特 定 的 日 期 格 式 參 數 舉 例 說 明 : TO_DATE( 31-3 月 -1964, DD-MONTH-YYYY ) TO_CHAR(HIREDATE, DD/MM/YYYY ) TO_CHAR(HIREDATE, AD DD-MON-YYYY ); 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 46

4-9 條 件 運 算 1. 用 來 比 較 的 : 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 47

2. 在 ( 或 不 在 ) 某 連 續 範 圍 之 內 (NOT) BETWEEN AND 3. 在 ( 或 不 在 ) 某 值 域 範 圍 (NOT) IN ( 值 域 範 圍 ) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 48

4. 與 某 一 綱 要 相 匹 配 ( 或 不 匹 配 ) (NOT) LIKE ( 綱 要 ) 在 ORACLE 系 統 中 匹 配 綱 要 是 用 % ( 百 分 比 符 號 ) 表 示 任 何 字 元 ; 用 _ ( 底 線 符 號 ) 表 示 任 一 字 元 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 49

5. 列 表 或 子 查 詢 結 果 中 的 任 一 (ANY) 或 全 部 (ALL) 值 ANY 任 一 ALL 全 部 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 50

6. 辨 別 子 查 詢 結 果 有 無 傳 回 值 (NOT) EXISTS 7. 虛 值 (NULL) 判 斷 條 件 IS(NOT)NULL 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 51

4-10 SQL 中 常 用 的 函 數 SQL 函 數 是 用 來 處 理 一 個 資 料 項, 並 傳 回 結 果 的 運 算 函 數 可 以 接 受 零 個 或 多 個 參 數, 利 用 不 同 參 數 的 函 數 完 成 不 同 的 運 算, 並 產 生 一 個 或 多 個 結 果 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 52

函 數 種 類 1. 數 值 函 數 : 數 值 函 數 接 受 數 值 輸 入, 傳 回 數 值 2. 字 元 函 數 : 字 元 函 數 接 受 字 元 輸 入, 可 傳 回 字 元 或 數 值 3. 日 期 函 數 日 期 函 數 操 作 DATE 資 料 型 態 的 值, 除 MONTHS_BETWEEN 外, 其 他 所 有 的 日 期 函 數 會 傳 回 一 個 DATE 型 態 的 值 4. 虛 值 函 數 NVL 5. 群 組 函 數 (Aggregation functions) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 53

數 值 函 數 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 54

字 元 函 數 函 數 說 明 CHR(n) 傳 回 字 元, 其 ASCII 碼 等 於 n CONCAT(s1, s2) 傳 回 s1 字 串 連 接 s2 字 串 的 新 字 串 INITCAP(s) 將 字 串 s 的 第 一 個 字 母 為 大 寫, 其 它 的 為 小 寫 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 55

字 元 函 數 (cont.) LENGTH(s) 傳 回 字 串 的 長 度 LOWER(s) 將 所 有 字 母 為 小 寫 LPAD(s1, n[, s2]) 用 s2 字 元 填 入 s1 字 串 中 的 左 邊, 使 其 總 長 度 為 n s2 的 預 設 值 為 單 個 空 格 ( ) 如 果 s1 字 串 長 度 大 於 n, 函 數 則 傳 回 s1 字 串 的 部 分 n 為 傳 回 值 的 總 長 度 LTRIM(s [,set]) 從 s 字 串 的 左 邊 開 始 移 去 set 字 串 中 的 字 元, 直 至 第 一 個 不 是 set 中 的 字 元 為 止 set 的 預 設 值 為 單 個 空 格 ( ) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 56

字 元 函 數 (cont.) REPLACE(s, 搜 尋 字 串 串 ]) [, 取 代 字 將 用 取 代 字 串 代 替 每 一 個 搜 尋 字 串 的 值, 如 果 取 代 字 串 不 寫, 則 所 有 搜 尋 字 串 的 值 被 刪 去 RPAD(s1, n[,s2]) 用 s2 填 入 字 串 的 右 邊, 使 其 總 長 度 為 n s2 字 串 的 預 設 值 為 單 個 空 格 ( ) 如 果 s1 比 n 長, 則 傳 回 s1 的 部 份 n 為 傳 回 值 的 總 長 度 RTRIM(s [,set]) 從 右 邊 開 始 移 去 set 字 串 中 的 字 元, 直 至 最 後 一 個 不 set 中 的 字 元 為 止 set 的 預 設 值 為 單 個 空 格 ( ) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 57

字 元 函 數 (cont.) SUBSTR ( s, m [,n]) 求 s 字 串 的 子 字 串, 從 m 處 的 字 元 開 始, 取 n 個 字 元 長 TRANSLATE (s, froms, tos) 將 s 字 串 中 的 froms 字 串 中 每 一 個 字 元 值 利 用 tos 字 串 中 相 應 字 元 替 換 不 在 froms 字 串 中 的 s 的 字 元 則 不 進 行 轉 換 UPPER(s) 傳 回 s, 其 中 所 有 字 母 為 大 寫, 傳 回 值 與 引 數 s 有 相 同 資 料 型 態 DECODE(ex, s1, r1 [, s2, r2, ], d) 依 照 ex 的 值 傳 回 不 同 的 值, 若 ex 的 值 為 s1 則 傳 回 值 r1,[ 若 ex 的 值 為 s2 則 傳 回 值 r2,] 否 則 傳 回 值 d 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 58

日 期 函 數 函 數 說 明 ADD_MONTHS(d,n) 傳 回 加 n 個 月 的 d 日 期, 引 數 n 為 整 數 如 果 d 為 該 月 中 最 後 一 天 ( 例 如 :31 日 ), 而 且 結 果 月 份 的 總 天 數 較 少 ( 例 如 : 二 月 的 28 日 ), 則 結 果 的 日 期 為 結 果 月 份 的 最 後 一 日 ( 例 如 :28 日 ), 否 則 結 果 日 期 的 天 數 與 d 中 的 原 日 期 一 致 LAST_DAY(d) 傳 回 包 含 d 日 期 的 月 份 最 後 一 天 之 日 期 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 59

日 期 函 數 (Cont.) MONTHS_BETWEEN(d1,d2) 傳 回 日 期 d1 和 d2 之 間 的 月 數 如 果 d1 比 d2 晚, 結 果 為 正 ; 如 果 d1 比 d2 早, 則 為 負 ; 如 果 d1 與 d2 為 同 一 日 期, 則 結 果 為 0 NEXT_DAY(d, 星 期 幾 ) 傳 回 比 日 期 d 晚 的 第 一 個 星 期 幾 ROUND(d[,fmt]) 傳 回 依 照 指 定 的 格 式 (fmt) 的 單 位 將 d 四 捨 五 入 到 最 近 日 期 TRUNC(d[,fmt]) 傳 回 依 照 指 定 的 格 式 (fmt) 的 單 位 將 d 無 條 件 捨 去 到 最 近 的 日 期 SYSDATE 傳 回 當 前 的 日 期 和 時 間 注 意 :CHECK 約 束 條 件 中 不 能 使 用 該 函 數 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 60

虛 值 函 數 NVL 虛 值 不 是 零, 也 非 空 白, 用 來 記 錄 目 前 資 料 值 未 知 的 情 況 虛 值 本 身 不 能 做 任 何 比 較 與 運 算, 因 為 結 果 都 是 虛 值 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 61

以 虛 值 的 特 性 與 意 義 可 大 致 分 為 以 下 三 種 : 1. 可 適 用 的 虛 值 (Applicable Null Values) 2. 不 可 適 用 的 虛 值 (Inapplicable Null Values) 3. 完 全 不 知 道 的 虛 值 (Totally Unknown) 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 62

可 適 用 的 虛 值 (Applicable Null Values) 若 是 某 特 定 欄 位 的 值 確 實 存 在, 但 是 我 們 並 不 知 道 其 值 為 何, 所 以 暫 時 存 放 一 個 虛 值, 等 到 日 後 得 知 確 實 數 值 後 再 填 入 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 63

不 可 適 用 的 虛 值 (Inapplicable Null Values) 若 是 某 特 定 欄 位 的 值 根 本 就 不 存 在, 則 我 們 就 可 放 入 一 個 虛 值, 表 示 此 欄 位 的 值 並 不 存 在 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 64

完 全 不 知 道 的 虛 值 (Totally Unknown) 若 是 某 特 定 欄 位 的 值, 我 們 完 全 不 知 道 這 個 值 是 否 存 在 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 65

群 組 函 數 (Aggregation functions) SQL 可 利 用 GROUP BY 子 句 將 查 詢 的 結 果 分 組, 並 對 每 個 群 組 利 用 前 面 介 紹 的 群 組 函 數 傳 回 單 列 群 組 資 訊 查 詢 的 資 料 依 照 GROUP BY 子 句 所 指 定 的 運 算 式 的 值 分 組, 將 相 同 值 的 資 料 組 成 一 組 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 66

4-11 SELECT 指 令 小 結 SELECT < 欄 位 名 1>,< 欄 位 名 2>,,< 欄 位 名 n> FROM < 表 格 名 1>,< 表 格 名 2>,,< 表 格 名 n> [WHERE 條 件 ] GROUP BY < 欄 位 名 1>,< 欄 位 名 2>,,< 欄 位 名 n> [HAVING 分 組 篩 選 條 件 ] [ORDER BY < 欄 位 名 1>,< 欄 位 名 2>,,< 欄 位 名 n>] 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 67

4-12-1 視 觀 表 格 (View) 的 概 念 可 以 由 一 個 基 本 表 格 中 選 取 的 某 些 資 料 列 和 欄 位 組 成, 也 可 以 由 幾 個 表 格 中 滿 足 一 定 條 件 的 資 料 組 成 視 觀 表 格 如 同 是 基 本 表 格 (Base Table) 窗 口, 它 反 映 了 一 個 或 多 個 基 本 表 格 的 局 部 資 料, 但 視 觀 表 格 本 身 並 無 實 際 存 放 資 料, 資 料 都 來 自 於 基 本 表 格, 視 觀 表 格 僅 是 對 應 基 本 表 格 的 部 分 資 料, 因 此 視 觀 表 格 只 是 一 個 虛 擬 表 格 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 68

使 用 視 觀 表 格 的 主 要 原 因 視 觀 表 格 可 以 提 供 安 全 : 視 觀 表 格 是 建 立 在 一 個 或 數 個 表 格 上, 使 用 者 可 以 操 作 視 觀 表 格 而 不 會 直 接 存 取 表 格 中 較 機 密 的 資 料 欄 位 視 觀 表 格 可 以 隱 藏 複 雜 的 資 料 查 詢 : 跨 多 表 格 的 關 聯 查 詢 或 是 較 複 雜 的 查 詢 都 可 以 利 用 視 觀 表 格 來 代 替, 如 此 可 以 簡 化 查 詢 操 作 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 69

4-12-2 建 立 視 觀 表 格 的 指 令 建 立 視 觀 表 格 (View)-CREATE VIEWCREATE VIEW < 視 觀 表 格 名 > [< 視 觀 表 格 欄 位 名 串 列 >] AS SELECT 指 令 [WITH CHECK OPTION]; 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 70

4-12-3 使 用 視 觀 表 格 (View) 的 指 令 視 觀 表 格 的 使 用 如 同 基 本 表 格 一 樣, 使 用 者 可 以 查 詢 其 中 的 資 料, 在 某 些 情 況 下 可 以 對 視 觀 表 格 進 行 修 改 刪 除 或 插 入 資 料 操 作, 其 操 作 方 式 與 一 般 基 本 表 格 相 同 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 71

4-12-4 廢 除 視 觀 表 格 廢 除 視 觀 表 格 -DROP VIEW DROP VIEW < 視 觀 表 名 >; 雖 然 視 觀 表 格 (View) 的 廢 除 不 會 影 響 基 本 表 格 的 使 用, 但 會 導 致 該 視 觀 表 格 相 關 的 所 有 授 權 被 自 動 取 消, 該 視 觀 表 格 相 關 的 所 有 視 觀 表 格 和 同 義 字 變 成 無 效 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 72

4-13 基 本 表 格 視 觀 表 格 的 拷 貝 在 使 用 資 料 庫 時, 經 常 需 要 對 原 始 的 資 料 資 訊 進 行 備 份, 備 份 資 料 可 以 用 來 做 刪 除 前 的 歸 檔 資 料, 或 用 來 做 臨 時 性 修 改, 或 是 提 供 給 其 他 人 使 用 我 們 可 以 在 資 料 庫 內 拷 貝 基 本 表 格 或 視 觀 表 格 拷 貝 一 個 基 本 表 格, 一 般 包 括 該 表 格 的 欄 位 定 義 和 所 有 的 資 料 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 73

拷 貝 表 格 CREATE TABLE AS SELECT CREATE TABLE < 表 格 名 >[(< 欄 位 名 > [NOT NULL] )] AS SELECT 命 令 ; 其 中 :< 表 格 名 > 為 新 表 格 的 名 字 ;[ ( < 欄 位 名 > [NOT NULL] ) ] 為 可 選 項, 如 果 不 選 這 項, 則 備 份 表 格 的 各 欄 位 名 及 資 料 型 態 是 由 SELECT 敘 述 的 查 詢 結 果 決 定 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 74

4-14 建 立 與 使 用 序 列 (SEQUENCE) 在 ORACLE 系 統 中 並 沒 有 提 供 任 何 自 動 編 號 的 欄 位, 所 以 ORACLE 就 利 用 序 列 (SEQUENCES), 來 達 成 相 同 的 功 能 它 的 功 能 類 似 一 個 計 數 器 (Counter) 序 列 是 產 生 連 續 數 字 最 有 效 率 的 方 法 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 75

建 立 序 列 方 法 CREATE SEQUENCE < 序 列 名 > 當 你 建 立 了 序 列 之 後, 一 般 預 設 的 情 況 下 ORACLE 系 統 會 在 記 憶 體 之 中 產 生 20 組 號 碼, 以 供 你 使 用 每 一 個 號 碼 只 能 產 生 一 次 就 換 到 下 一 個 號 碼, 你 可 以 使 用 sequence_name.currval 來 得 到 目 前 值 與 利 用 sequence_name.nextval 得 到 下 一 個 可 用 之 值 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 76

更 改 序 列 -- ALTER SEQUENCE ALTER SEQUENCE < 序 列 名 > <New_paramenters>; 其 中 <New_parameters> 為 新 的 選 項 參 數 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 77

廢 除 序 列 -- DROP SEQUENCE DROP SEQUENCE < 序 列 名 > 此 外 我 們 可 以 利 用 查 詢 ORACLE 系 統 中 資 料 字 典 的 視 觀 表 格 user_sequences 得 知 使 用 者 目 前 建 立 了 那 些 序 列 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 78

4-15-1 索 引 (Index) 的 概 念 索 引 (Index) 就 好 比 書 籍 中 的 目 錄, 當 你 要 找 尋 某 章 節 時, 可 迅 速 地 找 到, 節 省 找 尋 的 時 間 然 而 在 一 般 查 詢 中,ORACLE 要 逐 列 查 詢 該 表 格, 檢 查 是 否 為 所 需 的 資 料, 直 到 找 到 所 有 符 合 WHERE 子 句 中 的 條 件 的 資 料 列 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 79

4-15-2 建 立 索 引 建 立 索 引 -CREATE INDEX ON CREATE [UNIQUE] INDEX < 索 引 名 > ON < 表 格 名 > ( < 欄 位 名 > [ASC/DESC] [, < 欄 位 名 >[ASC/DESC] ] ); 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 80

建 立 索 引 的 原 則 (1) 較 大 的 基 本 表 格 應 該 建 立 索 引 表 格 越 大, 索 引 越 能 更 好 地 改 善 回 應 時 間, 索 引 也 越 有 效,ORACLE 系 統 越 有 機 會 找 到 最 短 路 徑 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 81

建 立 索 引 的 原 則 (cont.) (2) 對 一 個 基 本 表 格 可 以 建 立 多 個 索 引, 但 建 議 最 好 不 要 超 過 三 個 一 個 表 格 可 以 有 多 個 索 引, 索 引 越 多, 查 詢 速 度 越 快 但 是 建 立 太 多 的 索 引 是 不 合 適 的 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 82

建 立 索 引 的 原 則 (cont.) (3) 一 般 在 主 鍵 欄 位 上 建 立 索 引 當 一 個 主 鍵 為 複 合 欄 位 主 鍵 時, 那 麼 最 好 是 把 資 料 種 類 最 多 的 欄 位 放 在 CREATE INDEX 命 令 列 的 首 位 如 果 各 欄 位 資 料 種 類 相 近, 則 將 最 經 常 用 到 的 欄 位 放 在 前 面 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 83

建 立 索 引 的 原 則 (cont.) (4) 建 立 索 引 要 注 意 先 後 順 序 通 常 在 表 格 中 輸 入 資 料 後 才 建 立 索 引 如 果 先 建 索 引 後 輸 入 資 料, 則 每 插 入 一 列 都 要 對 索 引 進 行 一 次 更 新, 這 很 浪 費 時 間 但 如 果 希 望 保 證 輸 入 資 料 的 唯 一 性, 那 麼 只 能 以 犧 牲 性 能 為 代 價, 在 輸 入 資 料 前 先 建 立 好 唯 一 索 引 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 84

建 立 索 引 的 原 則 (cont.) (5) 建 立 索 引 要 注 意 95/5 法 則 在 某 些 情 況 下 使 用 索 引 會 拖 慢 速 度 你 可 利 用 95/5 法 則 評 估 是 否 使 用 索 引 當 查 詢 回 覆 結 果 小 於 表 格 全 部 資 料 的 5% 時, 使 用 索 引 是 最 佳 的 查 詢 方 式 如 果 查 詢 回 覆 結 果 超 過 表 格 全 部 資 料 的 95% 時, 不 使 用 索 引 查 詢 的 方 式 通 常 會 有 比 較 好 的 效 能 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 85

索 引 的 內 部 結 構 ORACLE 系 統 採 用 的 索 引 內 部 結 構 是 B + 樹, 從 而 保 證 對 表 格 中 任 何 一 列 的 存 取 所 需 要 的 時 間 都 差 不 多 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 86

4-15-3 索 引 的 類 型 依 建 立 方 式 分 類 : 單 鍵 索 引 多 鍵 索 引 依 索 引 欄 位 值 是 否 可 重 覆 分 類 : 唯 一 索 引 非 唯 一 索 引 依 儲 存 方 式 分 類 : 壓 縮 索 引 非 壓 縮 索 引 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 87

單 鍵 索 引 和 多 鍵 索 引 單 鍵 索 引 是 指 在 表 格 中 利 用 某 一 欄 位 所 建 立 的 索 引 多 鍵 索 引 是 指 在 表 格 中 利 用 多 於 一 個 欄 位 所 建 立 的 索 引 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 88

唯 一 索 引 和 非 唯 一 索 引 唯 一 索 引 和 非 唯 一 索 引 是 根 據 在 建 立 索 引 時 是 否 指 明 UNIQUE 來 確 定 的 唯 一 索 引 要 求 被 索 引 的 欄 位 值 不 能 重 覆 非 唯 一 索 引 則 允 許 索 引 欄 位 值 可 以 重 覆 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 89

壓 縮 索 引 和 非 壓 縮 索 引 建 立 索 引 時, 若 索 引 按 壓 縮 方 式 儲 存, 則 為 壓 縮 索 引, 否 則 為 非 壓 縮 索 引 壓 縮 索 引 的 優 點 是 減 少 了 儲 存 需 要, 因 而 減 少 了 I/O 的 次 數, 但 是 為 了 完 成 壓 縮 以 及 在 檢 索 時, 對 壓 縮 檔 做 解 碼 需 要 耗 費 一 定 的 時 間 使 用 非 壓 縮 索 引, 由 於 在 尋 找 時 無 需 進 行 解 碼, 因 而 加 快 了 執 行 速 度 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 90

4-15-4 廢 除 索 引 廢 除 索 引 -DROP INDEX DROP INDEX [< 使 用 者 名 稱 >. ]< 索 引 名 >; 當 使 用 者 不 再 需 要 使 用 索 引 時, 可 以 使 用 DROP INDEX 指 令 廢 除 索 引 其 中 :< 索 引 名 > 指 出 要 廢 除 的 索 引 名 字 [< 使 用 者 名 稱 >] 的 選 項, 表 示 該 索 引 的 擁 有 者 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 91

4-16 同 義 字 (Synonym) ORACLE 系 統 中 提 供 了 一 個 很 方 便 的 物 件 - Synonym ( 同 義 字 ) 同 義 字 可 分 為 Public ( 公 用 ) 與 Private ( 專 用 ) 兩 種 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 92

同 義 字 的 主 要 目 的 隱 藏 原 資 料 庫 物 件 的 原 擁 有 者 的 資 訊 為 某 物 件 提 供 一 個 較 簡 單 或 是 方 便 的 名 字 隱 藏 某 資 料 庫 物 件 的 實 際 位 置 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 93

4-16-1 定 義 同 義 字 定 義 同 義 字 -CREATE SYNONYM FOR 建 立 同 義 字 的 命 令 如 下 : CREATE [PUBLIC] SYNONYM < 同 義 字 > FOR [< 使 用 者 名 稱 >.] < 資 料 庫 物 件 >; 其 中 :[PUBLIC] 為 選 項, 如 果 選 擇 該 項, 則 表 示 建 立 的 這 個 同 義 字 可 以 被 所 有 使 用 者 使 用, 也 就 是 一 個 公 用 的 (public) 同 義 字, 但 只 有 DBA ( 資 料 庫 管 理 員 ) 能 夠 選 擇 [PUBLIC] 項 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 94

4-16-2 廢 除 同 義 字 廢 除 同 義 字 -DROP SYNONYM DROP [PUBLIC] SYNONYM < 同 義 字 >; 只 有 DBA 才 能 選 擇 [PUBLIC] 項 來 刪 除 公 用 同 義 字 同 義 字 的 廢 除 不 會 影 響 其 原 本 表 格 視 觀 表 格 (View) 使 用 權 限 改 變 一 個 同 義 字 的 方 法 是 先 廢 除 該 同 義 字, 再 重 建 該 同 義 字 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 95

4-17 資 料 庫 鏈 結 (Database Link) 資 料 庫 鏈 結 允 許 使 用 者 處 理 遠 端 資 料 庫 的 資 料, 可 使 用 已 經 設 定 好 的 資 料 庫 鏈 結 而 不 需 要 知 道 遠 端 資 料 庫 的 實 際 位 置 使 用 資 料 庫 鏈 結 時, 系 統 會 透 過 網 路 啟 動 一 個 連 線 (session), 以 便 執 行 你 的 SQL 指 令 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 96

4-17-1 建 立 資 料 庫 鏈 結 建 立 資 料 庫 鏈 結 -CREATE DATABASE LINK CREATE [PUBLIC] DATABASE LINK < 資 料 庫 鏈 結 名 > CONNECT TO < 使 用 者 > IDENTIFIED BY < 密 碼 > USING < 主 機 字 串 > 其 中 :< 使 用 者 > 為 連 線 遠 端 主 機 中 的 合 法 使 用 者 ;< 主 機 字 串 > 為 利 用 NET8 Easy Config 設 定 好 的 主 機 字 串 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 97

4-17-2 廢 除 資 料 庫 鏈 結 廢 除 資 料 庫 鏈 結 -DROP DATABASE LINK DROP DATABASE LINK < 資 料 庫 鏈 結 名 > 2009/3/26 資 料 庫 系 統 理 論 與 實 務 - 使 用 Oracle 98