Database講義.docx_101

Transcription

1 一 資 料 庫 系 統 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 在 一 個 資 料 庫 系 統 (Database System) 中 可 分 為 資 料 庫 (Database) 與 資 料 庫 管 理 系 統 (Database Management System, DBMS) 兩 個 部 份 資 料 庫 簡 單 的 說 就 是 一 群 經 過 電 腦 整 合 後 的 資 料, 儲 存 在 一 個 或 多 個 檔 案 中, 而 管 理 這 個 資 料 庫 的 軟 體 就 稱 之 為 資 料 庫 管 理 系 統 名 詞 說 明 : 1. 資 料 (Data): 資 料 是 資 料 庫 中 儲 存 的 基 本 物 件 資 料 的 種 類 很 多, 包 括 文 字 圖 形 聲 音 影 像 等 都 是 資 料 2. 資 料 庫 : 一 群 整 合 性 的 資 料 記 錄 集 合 3. 資 料 庫 管 理 系 統 (DBMS) 是 由 一 組 電 腦 程 式 所 組 成, 用 來 定 義 管 理 和 處 理 資 料 庫 內 儲 存 的 資 料 資 料 庫 是 以 嚴 謹 的 結 構 將 零 散 的 資 料 組 合 而 成 為 有 用 的 資 料, 藉 由 資 料 庫 管 理 系 統 用 來 管 理 這 些 資 料 一 個 資 料 庫 系 統 的 組 成 有 四 大 部 分 : 1. 使 用 者 使 用 者 乃 是 資 料 庫 系 統 的 主 要 服 務 對 象, 依 其 使 用 資 料 庫 的 方 式 目 的 與 時 機 來 區 分, 可 以 將 使 用 者 分 為 下 列 三 種 : (1) 直 接 使 用 者 : 對 資 料 庫 管 理 系 統 下 達 命 令 的 使 用 者 (2) 應 用 程 式 : 透 過 程 式 介 面 的 呼 叫, 對 資 料 庫 管 理 系 統 下 達 命 令 的 應 用 軟 體 程 式 (3) 資 料 庫 管 理 師 (Database Administrator, 簡 稱 DBA): 透 過 資 料 庫 管 理 系 統 所 提 供 的 命 令, 扮 演 資 料 庫 管 理 系 統 與 上 述 兩 種 使 用 者 之 間 的 中 介 角 色 負 責 排 解 資 料 庫 管 理 系 統 在 使 用 上 的 疑 難 調 整 系 統 效 能 保 護 資 料 避 免 破 壞 等 等 2. 資 料 資 料 乃 是 資 料 庫 中 的 主 體, 在 資 料 庫 系 統 中 的 資 料 基 本 可 以 分 為 運 算 資 料 (Operational Data) 與 異 動 資 料 (Transaction Log) 運 算 資 料 就 是 使 用 者 所 要 面 對 的 處 理 對 象, 也 就 是 資 料 庫 中 所 存 放 的 資 料 ; 而 異 動 資 料 則 是 資 料 庫 管 理 系 統 為 了 對 資 料 庫 做 有 效 和 正 確 的 管 理, 依 照 使 用 者 所 下 達 的 命 令, 而 自 動 產 生 的 記 錄 資 料 頁 次 :1

2 3. 硬 體 在 資 料 庫 系 統 上 的 硬 體 設 備 包 括 電 腦 主 機 磁 碟 機 光 碟 機 ( 櫃 ) 備 份 裝 置 等 4. 軟 體 一 個 資 料 庫 系 統 所 包 含 的 軟 體 包 括 : (1) 資 料 庫 管 理 系 統 : 使 用 者 和 資 料 庫 之 間 的 介 面 (2) 應 用 程 式 : 即 是 之 前 所 提 透 過 各 種 程 式 介 面 使 用 資 料 庫 的 應 用 程 式, 也 是 資 料 庫 的 使 用 者 之 一 二 資 料 庫 管 理 系 統 的 基 本 功 能 1. 能 有 組 織 地 將 資 料 儲 存 起 來, 並 具 備 快 速 的 資 料 存 取 技 巧 2. 能 有 效 地 管 理 資 料 庫 的 綱 要 資 料 庫 綱 要 (Schema) 指 的 是 使 用 者 對 資 料 庫 的 定 義, 這 些 定 義 是 一 些 用 來 描 述 資 料 的 資 料 (Metadata), 包 含 資 料 的 欄 位 名 稱 資 料 型 態 與 合 法 使 用 者 等 等 3. 要 提 供 一 套 高 階 查 詢 語 言 (High-level Query Language) 供 使 用 者 使 用 4. 異 動 的 管 理 (Transaction Management): 由 於 系 統 中 可 能 有 多 個 使 用 者 同 時 對 同 一 個 資 料 庫 下 達 命 令, 要 求 資 料 庫 管 理 系 統 完 成 工 作 而 使 用 者 對 於 資 料 庫 的 一 個 完 整 動 作 稱 為 一 個 異 動 一 個 異 動 可 能 包 含 許 多 的 運 算 動 作, 所 以 資 料 庫 管 理 系 統 必 須 有 效 地 做 異 動 的 管 理, 以 防 止 同 時 執 行 的 異 動 因 交 錯 執 行 而 發 生 不 可 挽 救 的 錯 誤 而 管 理 這 些 異 動 時, 最 重 要 的 參 考 資 料 便 是 異 動 記 錄 5. 對 資 料 的 安 全 管 制 (Security Control) 一 般 作 法 包 括 : (1) 建 立 使 用 者 通 行 密 碼 (2) 針 對 資 料 的 新 增 (Insert) 刪 除 (Delete) 查 詢 (Select) 修 改 (Update) 等 權 利 分 別 訂 定 使 用 權 (3) 使 用 View( 視 界, 或 稱 概 觀 ) 來 隱 密 部 份 資 料 不 給 使 用 者 或 查 詢 6. 備 份 與 確 保 資 料 正 確 性 的 工 具 7. 提 供 使 用 者 獨 立 的 特 性 8. 提 供 DBA 用 來 管 理 系 統 與 資 料 庫 的 工 具 如 監 控 系 統 效 能 調 整 系 統 效 能 等 工 具 頁 次 :2

3 三 資 料 庫 的 資 料 結 構 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 資 料 庫 是 由 資 料 所 組 成, 而 這 些 資 料 可 分 為 位 元 (bit) 字 元 (character) 欄 位 field) 記 錄 (record) 檔 案 (file) 與 資 料 庫 (database) 等 幾 個 層 次 : 位 元 字 元 欄 位 記 錄 檔 案 在 電 腦 中 所 有 的 資 料 都 是 由 0 與 1 所 構 成, 然 後 由 8 個 bit 組 成 一 個 byte 構 成 字 元 的 單 位 一 個 或 數 個 字 元 可 以 構 成 一 個 欄 位 存 放 資 料, 而 一 個 或 數 個 欄 位 又 可 以 組 成 一 筆 記 錄 例 如 一 本 書 的 記 錄 包 括 書 號 書 名 作 者 與 價 格 等 欄 位 : 有 4 個 欄 位 書 號 書 名 作 者 價 格 由 欄 位 組 合 成 記 錄 每 個 欄 位 都 會 有 定 義 使 用 的 byte 數, 以 提 供 儲 存 資 料 的 空 間 例 如 一 些 書 目 資 料 的 記 錄 : F001 資 料 庫 系 統 應 用 張 三 200 F002 資 料 庫 設 計 實 務 李 四 450 兩 筆 記 錄 許 多 的 記 錄 會 存 放 在 檔 案 中, 將 這 些 檔 案 組 合 在 一 起 就 構 成 了 資 料 庫 基 本 上 在 資 料 庫 中 所 存 放 的 是 經 過 整 合 後 的 資 料, 可 避 免 資 料 的 重 複 而 且 便 於 修 改 及 管 理 頁 次 :3

4 四 資 料 庫 系 統 模 型 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 模 型 是 系 統 或 狀 態 之 完 整 抽 象 概 念 依 據 資 料 庫 系 統 的 演 進 和 結 構 的 差 異, 主 要 分 為 檔 案 式 階 層 式 網 路 式 和 關 聯 式 資 料 庫 模 型 1. 階 層 式 (Hierarchical Model)- 其 資 料 結 構 採 用 樹 狀 架 構 依 據 資 料 的 不 同 類 別, 將 資 料 分 門 別 類, 儲 存 在 不 同 階 層 之 下 階 層 式 資 料 庫 的 優 點 是 結 構 類 似 於 金 字 塔, 不 同 層 次 間 資 料 關 連 性 直 接 且 簡 單 ; 缺 點 則 是 因 為 資 料 以 縱 向 發 展, 橫 向 關 聯 難 以 建 立, 資 料 容 易 重 複 出 現, 造 成 管 理 不 便 付 款 方 式 Cash Visa Check MasterCard 客 戶 Gerardo Strom Edward Islas Lynda Tam Gerardo Strom Gerardo Strom Edward Islas Lynda Tam 商 品 Fan belt Motor Oil Brake light Fan belt Brake light Muffler Socket wrench set Muffler Motor Oil 2. 網 路 式 (Network Model)- 將 每 一 資 料 視 為 一 節 點 (node), 而 節 點 與 節 點 間 可 以 建 立 關 聯, 相 互 連 接 而 取 得 資 料 優 點 是 避 免 了 資 料 的 重 複 性 ; 缺 點 是 關 聯 性 較 複 雜, 尤 其 是 資 料 庫 變 得 越 來 越 大 的 時 候, 關 聯 性 的 維 護 會 變 得 非 常 麻 煩 付 款 方 式 Cash Visa Check MasterCard 客 戶 Gerardo Strom Edward Islas Lynda Tam 商 品 Socket wrench set Fan belt Motor Oil Muffler Brake light 3. 關 連 式 (Relational Model) 關 聯 是 資 料 庫 是 以 二 維 陣 列 來 儲 存 資 料, 依 照 行 與 列 的 關 係 形 成 的 記 錄 的 集 合 稱 之 為 資 料 表 格 (Table) 關 聯 是 資 料 庫 最 大 的 特 點 在 於 將 每 個 具 有 相 同 屬 性 頁 次 :4

5 的 資 料 獨 立 地 儲 存 在 一 個 表 格 中 對 任 何 一 個 表 格 而 言, 使 用 者 可 以 新 增 刪 除 修 改 資 料 表 中 的 任 何 資 料 它 解 決 了 階 層 式 資 料 庫 的 橫 向 關 聯 不 足 的 缺 點, 也 避 免 了 網 狀 式 資 料 庫 過 於 複 雜 的 問 題, 所 以 目 前 大 部 分 的 資 料 庫 都 是 採 用 關 聯 式 資 料 庫 系 統 的 模 型 頁 次 :5

6 五 關 聯 式 資 料 模 型 的 資 料 結 構 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 關 聯 式 名 詞 關 聯 表 (Relation) 屬 性 (Attribute) 值 組 (Tuple) 實 體 (Entity) 資 料 庫 名 詞 表 格 (Table) 欄 位 (Field) 欄 (Column) 記 錄 (Record) 列 (Row) 概 念 模 型 的 基 本 概 念 包 括 : 1. 實 體 (Entity) 客 觀 存 在 並 可 相 區 隔 的 事 務 稱 之 為 實 體 實 體 可 以 是 個 具 體 的 人 事 物, 也 可 以 是 抽 象 的 概 念 或 關 係, 例 如 一 位 職 員 一 位 學 生 一 個 部 門 一 門 課 學 生 的 一 次 選 課 部 門 的 一 次 訂 貨 老 師 與 系 所 的 工 作 關 聯 等 2. 屬 性 (Attribute) 實 體 所 具 有 的 某 一 個 特 性 稱 之 為 屬 性 一 個 實 體 可 以 包 含 若 干 個 屬 性, 例 如 學 生 實 體 可 以 由 學 號 姓 名 性 別 生 日 系 所 入 學 時 間 等 屬 性 組 成 3. 鍵 (Key) 唯 一 標 示 實 體 的 屬 性 集 稱 之 為 鍵 例 如 學 號 是 學 生 實 體 的 鍵, 學 號 可 以 代 表 特 定 的 某 一 位 學 生 4. 值 域 (Domain) 屬 性 取 值 的 範 圍 稱 為 該 屬 性 的 值 域 例 如 學 號 屬 性 的 值 域 為 10 位 數 的 正 整 數, 中 文 姓 名 屬 性 的 值 域 為 5 位 數 以 內 的 字 串 等, 成 績 屬 性 的 值 域 為 介 於 0 至 100 的 正 整 數 5. 關 係 (Relationship) 在 現 實 世 界, 事 物 內 部 間 或 事 物 與 事 物 之 間 常 存 在 有 關 係, 也 就 是 彼 此 間 的 關 聯 性, 這 些 關 係 再 資 訊 世 界 中 反 應 為 實 體 內 部 的 關 係 或 實 體 與 實 體 間 的 關 係 實 體 內 部 的 關 係 通 常 是 指 組 成 實 體 之 各 屬 性 的 關 係 ; 實 體 之 間 的 關 係 則 是 指 不 圖 實 體 之 間 的 關 聯 性 而 實 體 與 實 體 之 間 的 關 聯 性 可 以 分 為 下 列 三 種 : (1) 一 對 一 (1:1) 如 果 對 於 實 體 集 A 中 的 每 一 個 實 體, 在 實 體 集 B 中 至 多 有 一 個 實 體 與 之 相 關, 反 之 亦 然, 則 稱 實 體 集 A 與 實 體 集 B 具 有 一 對 一 關 係 例 如 一 個 班 級 只 有 一 位 導 師, 一 個 班 級 只 有 一 位 班 長, 則 導 師 與 班 級 之 關 係 具 有 一 對 一 關 係, 班 長 與 班 級 之 關 係 一 具 有 一 對 一 關 係 (2) 一 對 多 (1:n) 如 果 對 於 實 體 集 A 中 的 每 一 個 實 體, 在 實 體 集 B 中 至 有 n (n 0) 個 實 體 與 之 相 關, 反 之, 對 於 實 體 集 B 中 的 每 一 實 體, 在 實 體 集 A 中 最 多 只 有 一 個 實 體 與 之 相 關, 則 稱 實 體 集 A 頁 次 :6

7 與 實 體 集 B 具 有 一 對 多 關 係 例 如 一 個 班 級 有 多 位 學 生, 而 每 一 位 學 生 則 都 屬 於 某 一 班 級, 則 班 級 與 學 生 之 間 具 有 一 對 多 關 係 (3) 多 對 多 (m:n) 如 果 對 於 實 體 集 A 中 的 每 一 個 實 體, 在 實 體 集 B 中 至 有 n (n 0) 個 實 體 與 之 相 關, 反 之, 對 於 實 體 集 B 中 的 每 一 實 體, 在 實 體 集 A 中 亦 有 m (m 0) 個 實 體 與 之 相 關, 則 稱 實 體 集 A 與 實 體 集 B 具 有 多 對 多 關 係 一 門 課 程 有 多 位 同 學 選 修, 而 一 位 同 學 可 以 同 時 選 修 多 門 課 程, 則 課 程 與 學 生 之 間 具 有 多 對 多 關 係 在 關 聯 式 資 料 模 型 中, 資 料 結 構 是 以 表 格 式 的 方 式 來 組 織 資 料, 稱 之 為 關 聯 表 (Relation) 一 個 關 聯 表 有 一 個 關 聯 表 名 稱 (Relation name) 另 外, 關 聯 表 中 會 含 有 一 個 以 上 的 欄 位, 我 們 稱 這 些 欄 位 為 屬 性, 其 中 必 須 要 指 定 某 個 屬 性 子 集 (Attribute Subset) 為 主 鍵 (Primary key), 使 得 每 一 筆 記 錄 均 可 利 用 其 主 鍵 值 和 其 他 記 錄 來 區 別 之 將 一 個 關 聯 表 的 所 有 屬 性 集 合 起 來 稱 之 為 該 關 聯 表 的 屬 性 集 (Attribute set) 其 中 每 一 個 屬 性 都 會 具 有 屬 性 名 稱 (Attribute name) 與 資 料 類 型 (Data type), 每 個 資 料 型 態 都 會 有 其 所 屬 的 值 域 (Domain), 以 定 義 其 合 法 的 資 料 值 主 鍵 值 域 值 域 屬 性 名 稱 值 組 關 聯 表 屬 性 值 一 個 關 聯 表 的 屬 性 數 目 稱 為 該 關 聯 表 的 維 度 (Degree), 也 就 是 該 關 聯 表 之 屬 性 集 的 大 小 由 於 屬 性 集 是 一 個 集 合, 所 以 按 照 集 合 的 定 義, 我 們 可 以 知 道 :1. 在 一 個 關 聯 表 中 的 屬 性 名 稱 不 可 以 有 重 複 的 情 況 發 生 有 了 關 聯 表 綱 要 (Schema) 後, 我 們 便 可 將 資 料 存 入 關 聯 表 中, 存 入 的 單 位 是 一 筆 一 筆 的 記 錄 (Records), 而 一 筆 記 錄 是 由 許 多 屬 性 值 所 組 成 的 集 合, 在 關 聯 式 資 料 模 型 裡 稱 之 為 值 組 (Tuple) 所 以 一 個 關 聯 表 事 實 上 就 是 一 個 由 許 多 值 組 所 組 成 的 集 合, 而 關 聯 表 中 所 含 值 組 的 數 目 稱 之 為 該 關 聯 表 的 數 集 (Cardinality) 頁 次 :7

8 關 聯 表 的 特 性 : 2. 不 含 重 複 的 值 組 3. 值 組 之 間 是 沒 有 順 序 的 4. 屬 性 之 間 是 沒 有 順 序 的 5. 所 有 屬 性 值 都 是 單 元 值 (Atomic value), 不 可 以 是 一 個 集 合 頁 次 :8

9 六 關 連 式 資 料 庫 系 統 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 1. 表 格 : 關 連 式 資 料 庫 系 統 最 基 本 的 觀 念, 便 是 表 格 (Table 或 稱 Relation,file) 它 以 表 格 作 為 儲 存 單 位, 所 有 儲 存 在 資 料 庫 中 的 資 料, 都 以 表 格 形 式 處 理 一 個 表 格 是 儲 存 資 料 最 自 然 的 方 式, 表 格 由 行 (column 或 稱 field,attribute) 與 列 (row 或 稱 tuple,record) 所 組 成 每 一 行 儲 存 相 同 性 質 的 資 料, 每 一 列 則 包 含 許 多 不 同 性 質 的 資 料 項 目 每 一 列 又 稱 為 一 個 記 錄 (record), 每 一 行 可 視 為 記 錄 內 的 一 個 欄 位 (field) 2. 紀 錄 (Record) 欄 位 (Field) 和 內 容 值 (Value): 每 一 個 表 格 都 是 由 記 錄 和 欄 位 所 組 成 一 筆 記 錄 代 表 一 個 表 格 內 某 一 個 實 體 的 所 有 資 訊 ; 而 一 個 欄 位 則 是 儲 存 在 一 個 表 格 內 的 一 段 個 別 資 訊 3. 列 (Row) 和 行 (Column): 表 格 內 所 有 的 記 錄 和 欄 位 都 參 照 到 列 和 行, 如 上 圖 所 示, 關 聯 式 資 料 庫 的 表 格 通 常 都 是 以 格 狀 方 式 呈 現, 橫 向 的 列 代 表 表 格 的 記 錄, 而 縱 向 的 行 則 是 代 表 欄 位 而 行 與 列 交 叉 的 欄 位 則 稱 為 資 料 項 目 (Cell), 例 如 要 取 得 第 五 筆 記 錄 的 分 機 號 碼, 就 是 取 得 第 五 列 與 PhoneExt 行 相 交 的 欄 位 值, 也 就 是 該 筆 記 錄 之 PhoneExt 欄 位 的 資 料 項 目 內 容 4. 虛 值 (Null value): 對 每 一 筆 記 錄 而 言, 其 每 一 個 欄 位 均 應 存 有 一 實 際 的 資 料, 但 是 當 該 欄 位 沒 有 資 料 存 在 時 會 發 生 什 麼 情 形? 例 如 某 一 客 戶 表 格 專 用 來 存 放 客 戶 相 關 的 資 料, 其 中 有 一 欄 位 是 用 來 存 放 每 一 個 客 戶 的 傳 真 號 碼 但 是 如 果 該 客 戶 並 沒 有 或 不 知 道 傳 真 號 碼 時, 並 須 有 一 個 方 式 來 表 明 這 種 情 況 因 為 沒 有 資 料 和 空 資 料 是 不 一 樣 的, 例 如 一 個 字 串 (String) 內 容 是 空 的, 仍 表 示 有 此 一 字 串, 只 是 其 內 容 長 度 為 零 ; 而 沒 有 字 串 則 表 示 完 全 不 存 在 此 一 字 串 虛 值 也 就 是 代 表 完 全 不 存 在 的 意 思, 在 SQL Server 中 當 一 個 欄 位 的 內 容 是 Null 時, 就 表 示 該 欄 位 的 內 容 完 全 不 存 在, 如 果 您 常 式 對 一 個 有 Null 的 欄 位 做 計 算, 其 結 果 一 定 是 Null 頁 次 :9

10 5. 欄 位 屬 性 (Field properity): 表 格 中 每 一 個 欄 位 的 型 態 並 非 都 一 樣, 例 如 電 話 號 碼 和 生 日 欄 位 的 格 式 一 定 不 同, 因 此 資 料 庫 系 統 必 須 能 處 理 各 種 不 同 的 資 料 型 態, 在 SQL Server 裡 便 是 透 過 欄 位 屬 性 來 設 定 各 欄 位 處 理 的 資 料 型 態 種 類 如 下 圖, 顯 示 某 一 個 表 格 的 綱 要 (Schema) 資 訊, 顯 示 此 一 表 格 各 欄 位 所 能 儲 存 的 資 料 型 態 頁 次 :10

11 6. 視 界 (View): 雖 然 在 關 聯 式 資 料 庫 中 資 料 均 是 儲 存 在 表 格 內, 但 是 通 常 表 格 並 不 是 以 使 用 者 習 慣 的 次 序 來 呈 現, 例 如 資 料 庫 中 有 客 戶 檔 職 員 檔 訂 單 檔 和 訂 單 細 節 檔 等 等, 除 非 每 次 要 看 所 有 的 客 戶 資 料 或 是 訂 單 資 料, 否 則 類 似 下 列 其 情 形, 使 用 者 並 不 容 易 直 接 由 表 格 獲 知 所 需 的 資 訊 : (1) 要 查 看 各 個 訂 單 的 總 價 (2) 查 看 客 戶 資 料 要 依 據 各 客 戶 所 屬 國 家 分 別 來 檢 視 (3) 只 想 查 看 當 月 生 日 的 員 工 資 料 為 了 達 成 這 些 特 殊 的 需 求, 資 料 庫 提 供 了 一 個 工 具 稱 之 為 視 界 或 稱 概 觀 View 是 一 個 虛 擬 表 格, 是 從 實 際 的 表 格 中 透 過 查 詢 語 言 的 執 行 而 呈 現 出 來 的 虛 擬 表 格, 實 際 上 並 非 實 值 存 在 的 表 格 View 的 定 義 是 以 SELECT 敘 述 為 基 礎, 為 特 定 資 料 的 集 合 藉 由 View 來 存 取 資 料, 可 以 簡 化 查 詢 步 驟, 並 可 做 某 種 程 度 的 讀 取 權 限 控 制 頁 次 :11

12 七 鍵 值 類 型 1. 候 選 鍵 (Candidate Key): 主 鍵 是 一 個 唯 一 的 識 別 值 (Unique Identifier), 它 是 由 屬 性 集 的 子 集 所 構 成 其 實 在 一 個 關 聯 表 中 符 合 此 條 件 的 屬 性 子 集 可 能 會 有 好 幾 個, 這 些 屬 性 子 集 我 們 稱 之 為 候 選 鍵 而 主 鍵 便 是 由 一 堆 候 選 鍵 中 所 選 出 來 的 要 成 為 候 選 鍵 的 屬 性 子 集 必 須 要 具 備 下 列 兩 個 條 件 : (1) 唯 一 識 別 性 : 在 一 個 關 聯 表 中 絕 不 會 有 兩 個 值 組 的 屬 性 子 集 具 有 相 同 的 值 也 就 是 說 關 聯 表 中 各 值 組 的 屬 性 子 集 必 須 能 夠 唯 一 識 別 該 值 組 (2) 非 多 餘 性 (nonredundancy): 組 成 鍵 值 的 屬 性 必 須 全 部 存 在 才 能 達 成 唯 一 識 別 的 特 性 ( 最 小 性 : 一 個 屬 性 子 集 是 滿 足 唯 一 性 的 最 小 屬 性 子 集 合 ) 如 果 我 們 有 多 個 候 選 鍵, 在 選 擇 主 鍵 時 可 以 參 考 下 列 四 個 原 則 : (1) 選 擇 永 遠 不 會 變 更 其 值 的 屬 性 (2) 確 保 不 會 是 虛 值 的 屬 性 (3) 不 要 用 人 工 才 能 解 讀 的 編 號 鍵 值 例 如 假 設 某 公 司 旗 下 有 多 個 生 產 廠, 產 品 編 號 ML10045 中 的 ML 代 表 某 一 生 產 廠 代 碼, 但 該 生 產 廠 代 碼 必 須 透 過 另 一 代 碼 檔 轉 換 成 說 明 才 可 知 該 工 廠 名 稱, 若 將 來 該 代 碼 檔 修 改 或 資 料 異 動, 則 可 能 造 成 產 品 編 號 解 譯 (parsing) 錯 誤 (4) 儘 量 以 單 一 的 屬 性 來 代 替 整 筆 值 組 如 果 都 沒 有 適 合 的 欄 位 集, 可 以 考 慮 新 增 一 欄 位, 儲 存 唯 一 性 的 流 水 號 2. 替 代 鍵 (Alternate Key) 由 數 個 候 選 鍵 中 選 擇 其 中 一 個 作 為 主 鍵 時, 則 其 他 剩 下 來 的 候 選 鍵 便 稱 為 替 代 鍵 3. 主 鍵 (Primary Key) 主 鍵 是 由 一 組 欄 位 所 組 成, 用 來 區 別 表 格 中 的 每 一 筆 記 錄 凡 是 主 鍵 的 欄 位 就 不 能 是 虛 值 (NULL), 因 此 凡 是 宣 告 為 Primary Key 的 欄 位, 系 統 會 自 動 為 其 建 立 NOT NULL 的 限 制 ; 並 自 動 建 立 唯 一 索 引 (Unique Index) 宣 告 為 LONG 和 LONG RAW 資 料 型 態 的 欄 位 不 能 做 為 主 鍵, 4. 外 來 鍵 (Foreign Key) 資 料 相 互 之 間 一 致 性 限 制 功 能, 可 以 確 保 資 料 庫 資 料 的 一 致 資 料 相 互 間 一 致 性 限 制, 是 透 過 頁 次 :12

13 主 鍵 與 外 來 鍵 的 結 合 來 達 成 關 聯 式 表 格 間 的 關 係 必 須 藉 由 外 來 鍵 來 建 立, 因 為 對 某 一 表 格 的 外 來 鍵 而 言, 其 詳 細 的 資 料 是 儲 存 在 另 外 一 個 表 格 之 中, 因 此 稱 為 外 來 鍵 (Foreign key) 書 目 資 料 表 作 者 資 料 表 (Primary key) (Foreign key) (Foreign key) * 書 目 編 號 題 名 作 者 編 號 分 館 代 碼 : * 作 者 代 碼 作 者 姓 名 稱 謂 : (Primary key) 館 別 資 料 表 * 分 館 代 碼 分 館 名 稱 地 址 : (Primary key) 書 籍 編 號 與 作 者 編 號 欄 位 分 別 是 書 目 資 料 表 與 作 者 資 料 表 的 Primary key 為 了 建 立 兩 個 表 格 之 間 的 關 聯 性, 則 在 書 目 資 料 表 需 要 有 一 Foreign Key 對 應 到 作 者 資 料 表 的 Primary Key, 這 種 機 制 稱 之 為 參 考 完 整 性 (Referential Integrity) 外 來 鍵 的 值 必 須 來 自 於 其 所 參 考 到 的 表 格 當 自 行 輸 入 不 是 NULL 的 值, 並 且 該 值 不 存 在 其 所 參 考 表 格 的 記 錄 中 時, 系 統 會 拒 絕 該 資 料 的 輸 入, 如 此 可 避 免 打 斷 兩 個 表 格 之 間 的 關 聯 性 頁 次 :13

14 八 資 料 庫 系 統 處 理 架 構 之 演 進 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 1. 第 一 階 段 : 集 中 式 處 理 架 構 2. 第 二 階 段 : 主 / 從 式 架 構 3. 第 三 階 段 : 三 層 式 處 理 架 構 4. 第 四 階 段 : 同 質 性 分 散 式 處 理 架 構 5. 第 五 階 段 : 異 質 性 分 散 式 處 理 架 構 6.. 第 六 階 段 : 行 動 計 算 處 理 九 關 聯 式 資 料 庫 廠 商 1.Oracle 由 ORACLE 公 司 所 發 展, 在 台 灣 稱 之 為 美 商 甲 骨 文 公 司 ( 2.Informix-OnLine Informix SE(Standard Engine) 由 Informix Software 公 司 所 發 展, 在 台 灣 稱 為 英 孚 美 公 司 ( 現 已 由 IBM 所 併 購 3.Sybase SQL Server 由 Sybase 公 司 所 發 展, 在 台 灣 稱 為 賽 貝 斯 公 司 此 產 品 目 前 改 名 為 Sybase Adaptive Server ( 4.Ingres 最 早 由 加 州 柏 克 萊 校 區 的 M. Stonebraker 教 授 所 發 展, 而 後 成 為 資 料 庫 產 品, 現 在 是 Computer Associates(CA, 在 台 灣 稱 為 組 合 國 際 ) 的 一 個 部 門 ( 5.Microsoft SQL Server 由 Microsoft 公 司 所 發 展, 最 早 是 和 Sybase 合 作 發 展, 目 前 則 是 各 自 獨 立 發 展 ( 6.DB2 為 IBM 公 司 所 發 展 ( 7.Progress 由 Progress Software Corp. 發 展 ( 8.Gupta SQL Base 由 Gupta Corp. 發 展, 現 已 改 名 為 Centura SQL Base ( 9.DBMaker 是 由 國 內 凌 群 電 腦 公 司 所 發 展 ( 除 了 商 業 版 本 之 外, 國 際 間 亦 有 許 多 開 放 原 始 碼 的 非 商 業 使 用 免 費 資 料 庫 系 統, 如 : 1. PostgreSQL 最 初 由 加 州 大 學 伯 克 萊 分 校 計 算 機 科 學 系 開 發, 倡 導 了 很 多 物 件 導 向 的 觀 念 ( 2. MySQL TCX 公 司 發 展 的 資 料 庫 系 統 ( 後 被 sun( 昇 陽 ) 公 司 併 購 之 後 sun 被 Oracle 公 司 併 購, 現 MySQL 歸 屬 於 Oracle 公 司 ( 註 :MySQL 的 官 方 發 音 是 My Ess Que Ell, 不 是 MY-SEQUEL ) 頁 次 :14

15 十 什 麼 是 SQL 1.SQL(Structured Query Language) 是 在 關 連 式 資 料 庫 中, 定 義 和 處 理 資 料 的 標 準 語 言, 也 是 一 個 在 商 業 間, 應 用 最 廣 泛 的 資 料 庫 語 言 並 非 要 SQL 才 能 處 理 關 連 式 資 料 庫, 而 是 SQL 是 大 多 數 關 連 式 資 料 庫 的 標 準 介 面 2. 關 連 式 資 料 庫 必 須 藉 由 SQL 的 功 能, 支 援 執 行 資 料 的 定 義 (Definition) 處 理 (Manipulation) 和 控 制 (Control) 3.SQL 提 供 程 式 和 使 用 者 用 於 存 取 資 料 庫 系 統 內 資 料 的 標 準 命 令 集 十 一 SQL 的 起 源 1.SQL 最 早 起 始 於 1970 年, 由 IBM 在 San Jose, California 研 究 實 驗 室 的 E. F. Codd 發 表 將 資 料 組 成 表 格 的 資 料 關 連 模 型 的 查 詢 代 數 與 應 用 原 則 (Codd's Relational Algebra) 年, 同 一 實 驗 室 中 的 D. D. Chamberlin 和 R. F. Boyce 對 Codd's Relational Algebra 制 定 了 一 套 規 範 語 言 -SEQUEL(Structured English QUEry Language) 3. 兩 年 後,D. D. Chamberlin 將 發 展 的 新 版 本 SEQUEL/2 建 立 在 IBM 的 資 料 庫 管 理 系 統 System R 上 1980 年 時, 改 名 為 SQL 自 此, 隨 著 關 連 式 資 料 庫 管 理 系 統 的 發 展,SQL 廣 泛 的 被 應 用 在 各 種 資 料 庫 管 理 系 統 上 年 IBM 發 表 SQL/DS 後, 關 聯 式 資 料 庫 領 域 可 以 說 是 百 家 爭 鳴, 首 先 是 Relational Software 公 司 ( 後 來 更 名 為 Oracle) 發 表 第 一 個 關 聯 式 資 料 庫 管 理 系 統 (RDBMS), 並 結 合 SQL 成 為 第 一 代 上 市 發 行 RDBMS 的 主 流 十 二 SQL 標 準 1. 為 了 達 到 資 料 庫 定 義 與 應 用 模 組, 在 各 種 不 同 的 關 連 式 資 料 庫 管 理 系 統 間 的 可 攜 性, 以 及 提 供 關 連 式 資 料 庫 管 理 系 統, 在 發 展 上 的 共 通 準 則 由 ANSI 的 X3H2 小 組 負 責 訂 定 了 SQL 標 準 (ANSI SQL), 後 來 也 被 ISO 納 入 為 國 際 上 所 認 同 的 標 準 2. 目 前 最 新 SQL 標 準 為 SQL99 (ANSI X ) 3. 大 部 分 關 連 式 資 料 庫 管 理 系 統 遵 循 ANSI SQL 89 頁 次 :15

16 十 三 SQL 功 能 SQL 指 令 雖 稱 為 查 詢 語 言, 卻 涵 蓋 了 下 列 資 料 庫 語 言 基 本 的 功 能 : 1. 建 立 資 料 庫 及 其 表 格 欄 位 2. 增 加 刪 除 修 改 資 料 庫 的 資 料 內 容 3. 能 執 行 各 種 查 詢 (query) 資 料 的 動 作, 並 組 合 一 組 資 料 成 為 有 意 義 的 資 訊 十 四 SQL 指 令 集 SQL 指 令 包 含 許 多 處 理 資 料 庫 的 命 令, 這 些 指 令 集 依 功 能 區 分 為 三 部 份 : 1. 資 料 定 義 語 言 (Data Definition Language, 簡 稱 DDL): 用 定 義 資 料 庫 的 綱 要, 如 表 格 名 稱 所 含 有 的 欄 位 與 資 料 型 態 整 合 限 制 條 件 等 等 2. 資 料 處 理 語 言 (Data Manipulation Language, 簡 稱 DML): 用 來 處 理 資 料 庫 中 的 資 料, 包 括 資 料 的 新 增 (INSERT) 刪 除 (DELETE) 修 改 (UPDATE) 與 選 擇 (SELECT) 等 運 算 3. 交 易 控 制 語 言 (Transaction Control Language, 簡 稱 TCL 或 DCL): 用 來 處 理 資 料 庫 的 使 用 權 限 與 安 全 管 制 功 能 可 以 控 制 使 用 者 是 否 可 以 合 法 使 用 資 料 庫 是 否 可 以 合 法 對 資 料 庫 的 表 格 執 行 新 增 刪 除 新 增 維 護 或 選 擇 等 動 作 是 否 可 以 對 資 料 庫 做 備 份 等 等 的 管 制 有 些 人 將 SQL 戲 稱 為 Scarcely Qualifying as a Language ( 還 不 夠 格 稱 為 程 式 語 言 ) 這 麼 說 其 實 也 有 道 理,SQL 實 際 上 並 不 是 程 式 語 言, 而 可 以 視 為 一 種 資 料 庫 程 式 設 計 函 式 庫 就 像 一 般 我 們 都 稱 呼 SQL 的 指 令 為 查 詢 指 令, 但 這 也 並 不 表 示 SQL 中 所 有 指 令 的 內 容 都 在 對 資 料 庫 做 查 詢 的 動 作 頁 次 :16

17 十 五 欄 位 屬 性 ( 資 料 型 態 ) 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 建 立 SQL 的 表 格 時, 必 須 宣 告 各 行 欄 位 的 資 料 型 態 SQL Server 基 本 的 欄 位 資 料 型 態 包 括 : 資 料 型 態 說 明 BINARY 儲 存 二 進 位 資 料 DATE&TIME 儲 存 日 期 時 間 資 料 FLOAT 和 REAL 儲 存 浮 點 資 料 MONEY 儲 存 幣 值 資 料 TIMESTAMP 表 格 易 動 標 籤 IMAGE 儲 存 圖 形 資 料 CHAR 儲 存 字 串 資 料 DECIMAL 儲 存 定 數 字 資 料 INT 儲 存 整 數 資 料 BIT 儲 存 邏 輯 資 料 (0/1) TEXT 儲 存 大 量 文 字 資 料 1. BINARY 型 態 (1) binary[(n)] 此 型 態 欄 位 用 來 儲 存 binary 資 料 n 是 宣 告 的 長 度, 以 位 元 組 為 單 位,n 必 須 介 於 1 到 8000 之 間 (2) varbinary[(n)] verbinary 型 態 同 binary, 但 具 備 變 動 長 度 的 特 性 2. CHAR 型 態 (1) char[(n)] char 型 態 的 欄 位 用 來 儲 存 字 元 資 料, 最 多 可 儲 存 8000 個 字 元 只 要 一 經 宣 告, 不 管 輸 入 的 資 料 長 度 為 何, 將 固 定 佔 用 n 個 位 元 組 的 儲 存 空 間 如 果 輸 入 字 串 長 度 小 於 n 時, 若 該 欄 位 不 允 許 null 值, 則 系 統 會 將 不 足 部 分 補 空 白 ; 若 該 欄 位 允 許 null 值, 則 不 足 部 分 不 補 空 白 若 輸 入 資 料 超 過 宣 告 的 長 度, 則 其 超 出 部 分 會 被 截 掉 (2) varchar[(n)] varchar 型 態 同 char 型 態, 不 過 它 是 具 有 變 動 長 度 的 特 性 n 是 此 型 態 儲 存 資 料 的 最 大 允 許 長 度,n 的 範 圍 可 以 為 1 到 8000 此 型 態 資 料 實 際 佔 用 的 儲 存 空 間 視 其 輸 入 資 料 長 度 而 定, 但 是 輸 入 資 料 中 的 後 置 空 白 (trailing blanks) 部 分 將 不 會 被 存 入, 所 以 也 不 列 入 佔 用 空 間 記 數 註 :SQL Server 的 欄 位 若 需 要 儲 存 為 unicode 字 碼, 必 須 宣 告 為 : nvarchar 或 nchar 型 態, 其 中 n 表 示 national 之 意 頁 次 :17

18 3. DATE&TIME 型 態 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 此 型 態 用 來 儲 存 日 期 - 時 間 資 料 顯 示 時 內 定 的 顯 示 格 式 為 MM DD yyyy hh:mm AM( 或 PM) (1) Datetime 此 型 別 的 欄 位 使 用 兩 個 4 bytes 的 整 數 儲 存 資 料, 其 中 4 個 位 元 組 存 放 從 西 元 1900 年 1 月 1 日 到 該 日 之 前 或 之 後 所 經 過 的 天 數, 另 外 4 個 位 元 組 則 儲 存 從 零 時 起 至 該 時 間 所 經 過 的 微 秒 數 (milliseconds) 若 輸 入 資 料 省 略 時 間 部 分, 系 統 將 以 12:00:00:000AM 作 為 時 間 內 定 值 (2) Smalldatetime 同 datetime 型 態, 但 儲 存 的 資 料 較 不 精 確, 此 型 態 只 佔 用 4 個 位 元 組 的 儲 存 空 間 其 中 兩 個 位 元 組 用 來 儲 存 日 期, 另 兩 個 位 元 組 用 來 儲 存 時 間, 故 此 型 別 允 許 儲 存 的 日 期 範 圍 為 西 元 1990 年 1 月 1 日 至 西 元 2079 年 6 月 6 日, 精 確 度 到 分 鐘 4. FLOAT 和 REAL 型 態 float 和 real 型 態 的 欄 位 是 用 來 儲 存 無 法 以 有 限 位 數 表 示 的 數 值 資 料, 例 如 無 窮 小 數 (1) float[(n)] n 可 從 1 到 53, 表 示 此 型 態 的 二 進 位 精 確 度 其 值 的 範 圍 為 -1.79E+308 到 1.79E+308 (2) real[(n)] 和 float 型 態 相 同 n 可 為 1 到 7, 因 此 最 多 可 以 有 7 位 精 確 度, 其 值 範 圍 為 -3.40E+38 到 3.40E MONEY 型 態 money 型 態 的 資 料 是 一 具 有 4 位 小 數 的 decimal 型 態 數 值 輸 入 時 必 須 在 數 值 前 加 入 幣 別 符 號, 如 果 是 負 值 則 幣 別 符 號 後 面 加 - 符 號, 不 需 要 每 三 個 字 加 逗 號, 但 在 列 印 時 會 自 動 幫 你 加 印 逗 號 此 型 態 的 資 料 最 多 只 能 有 四 位 小 數 位 數, 如 果 需 要 更 多 小 數 位 數, 可 改 使 用 decimal 型 態 6. TIMESTAMP 型 態 雖 然 命 名 為 timestamp, 但 此 一 型 態 完 全 和 日 期 時 間 無 任 何 關 聯 它 相 當 於 binary(8) 或 varbinary(8) 型 別 但 是 它 有 一 重 要 的 特 性, 就 是 在 含 有 此 一 型 態 欄 位 的 row 每 次 被 UPDATE 或 INSERT 時, 此 欄 位 值 即 會 被 異 動, 而 且 在 同 一 資 料 庫 內 該 欄 位 值 是 唯 一 的 每 一 資 料 表 格 內 僅 能 有 一 個 timestamp 型 態 的 欄 位 而 且 必 須 以 timestamp 命 名 7. IMAGE 型 態 image 型 態 欄 位 用 來 儲 存 大 量 的 二 位 元 資 料 (binary data), 通 常 用 來 儲 存 圖 形 檔 此 一 型 態 的 欄 位 理 論 上 最 大 可 儲 存 2,147,483,647 個 位 元 組 的 資 料, 但 實 際 上 必 須 視 硬 碟 大 小 而 定 頁 次 :18

19 8. DECIMAL 型 態 對 於 SQLServer 而 言,decimal 和 numeric 視 為 相 同 的 型 態, 用 來 儲 存 具 有 小 數 點 而 且 數 值 確 定 的 數 值 不 像 float 和 real 是 用 來 儲 存 近 似 值 宣 告 的 方 式 為 : decimal[(p[,s])] numeric[(p[,s])] 對 SQL Server 而 言,decimal 和 numeric 視 為 相 同 的 資 料 型 態, 它 們 可 用 來 儲 存 具 有 小 數 點 的 數 值 不 像 float 和 real 是 用 來 儲 存 近 視 值,decimal 和 numeric 試 用 來 儲 存 確 定 的 值 s 表 示 小 數 位 數,p 表 示 精 確 度 (precision) 例 如 decimal(10,3) 表 示 共 有 七 位 整 數 三 位 小 數, 此 欄 位 精 確 度 為 十 位 若 未 指 定 時, 系 統 預 設 為 18 位 精 確 度, 內 定 小 數 位 數 為 0 9. INT 型 態 int 整 數 型 態 可 儲 存 2,147,483,648 到 2,147,483,647 間 的 整 數 smallint 型 態 可 儲 存 到 間 的 整 數 tinyint 型 態 可 儲 存 間 的 整 數 10. BIT 型 態 bit 型 態 的 欄 位 佔 用 一 個 位 元 組 的 空 間, 其 值 為 0 或 1 或 null 如 果 輸 入 異 於 0 或 1 的 值, 都 會 被 視 為 TEXT 型 態 text 型 態 欄 位 用 來 儲 存 任 何 文 字 資 料, 通 常 用 來 儲 存 一 些 memo 文 件 等 資 料 理 論 上 此 一 型 態 的 欄 位 最 大 可 儲 存 2,147,483,647 個 位 元 組 的 資 料, 但 實 際 上 必 須 視 硬 碟 大 小 而 定 頁 次 :19

20 十 六 SQL 命 令 1. 資 料 定 義 語 言 (DDL) ALTER CREATE DROP 更 改 建 立 刪 除 資 料 庫 的 表 格 (Table) 是 由 列 (row) 和 行 (column) 所 組 成 的 二 為 矩 陣, 可 以 使 用 CREATE TABLE 來 產 生 Table 一 旦 Table 產 生 後, 就 可 以 開 始 填 入 資 料 產 生 新 的 表 格 如 果 覺 得 有 不 妥 之 處, 想 改 變 Table 結 構 時, 可 使 用 ALTER TABLE 指 令 當 Table 沒 有 任 何 利 用 價 值 時, 可 使 用 DROP TABLE 將 它 從 資 料 庫 中 完 全 刪 除 掉 DDL 指 令 練 習 : (1) 建 立 表 格 CREATE TABLE ORD (ORDER_NUMBER INTEGER NOT NULL, CLIENT_NAME VARCHAR(10), SALES VARCHAR(10), ORDER_DATE DATETIME) (2) 增 加 新 的 欄 位 ALTER TABLE ORD ADD DESCRIPTION VARCHAR(20) DEFAULT 'NONE' ALTER TABLE ORD ADD TEMP VARCHAR(20) (3) 改 變 欄 位 的 型 態 ( 相 同 資 料 型 態, 但 長 度 增 加 ) ALTER TABLE ORD ALTER COLUMN CLIENT_NAME VARCHAR(30) (4) 刪 除 欄 位 ALTER TABLE ORD DROP COLUMN TEMP (5) 建 立 一 個 視 界 (VIEW) CREATE VIEW CODE_CITY AS SELECT CODE, DESCRIPTION FROM SYS_CODE WHERE CODE_TYPE= CITY ; (6) 刪 除 一 個 表 格 DROP TABLE ORD; (7) 刪 除 一 個 視 界 DROP VIEW CODE_CITY; 頁 次 :20

21 2. 資 料 處 理 語 言 (DML) 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) DELETE INSERT SELECT UPDATE 刪 除 新 增 選 擇 修 改 (1) 查 詢 功 能 簡 介 : SELECT 欄 位 項 目 FROM table 名 稱 [WHERE 查 詢 條 件 ] [GROUP BY 分 類 欄 位 項 目 ] [HAVING 查 詢 條 件 ] [ORDER BY 排 序 欄 位 項 目 ] 例 : 列 出 表 格 CUSTOMER 所 有 內 容 SELECT * FROM CUSTOMER; 例 : 列 出 表 格 CUSTOMER 中, 地 址 在 台 北 市 的 客 戶 姓 名 SELECT NAME FROM CUSTOMER WHERE CITY= 台 北 市 ; (2) WHERE 運 算 子 : WHERE 子 句 設 定 查 詢 的 條 件, 取 出 特 定 的 資 料 GROUP BY 子 句 是 從 WHERE 所 選 出 的 資 料 從 新 組 合, 這 些 組 合 是 根 據 GROUP BY 子 句 所 指 定 的 欄 位,HAVING 是 隸 屬 在 GROUP BY 子 句 內, 作 用 與 WHERE 類 似, 但 它 是 過 濾 由 GROUP BY 中 所 選 出 的 資 料 當 結 合 兩 個 表 格 作 為 複 合 查 詢 時 ( 稱 為 JOIN), 且 有 共 同 的 KEY 時,WHERE 條 件 必 須 加 以 限 定 各 表 格 的 KEY 需 相 等, 否 則 將 有 相 乘 的 結 果 產 生 CUSTOMER CUSTOMER_ID NAME CITY... INVOICE INVOICE_ID CUSTOMER_ID SALE_DATE... SELECT NAME,INVOICE_ID,SALE_DATE FROM CUSTOMER,INVOICE WHERE CUSTOMER.CUSTOMER_ID= INVOICE.CUSTOMER_ID; 頁 次 :21

22 (3) JOIN 使 用 別 名 的 技 巧 : 為 了 避 免 冗 長 的 table 名 稱, 我 們 可 以 定 義 table 的 別 名 (Alias), 這 table 的 別 名 是 使 用 較 短 名 稱 取 代 原 table 名 稱 例 如 : SELECT BRN, B.NAME, AUTHOR, P.* FROM BOOK B, PUBLISHER P WHERE B.PUBLISHER=P.PID 這 例 子 中 使 用 B 代 替 BOOK,P 代 替 PUBLISHER, 這 些 別 名 只 在 該 查 詢 命 令 內 有 效 (4) 虛 值 Null 如 果 一 列 的 某 行 缺 少 值, 就 說 該 行 是 空 值 (Null), 或 者 說 包 含 一 個 空 值 虛 值 可 出 現 在 任 何 型 態 的 行 上 要 測 試 一 個 虛 值, 只 能 使 用 比 較 操 作 IS NULL 和 IS NOT NULL 例 :SELECT * FROM CUSTOMER WHERE TELPHONE IS NULL; DML 指 令 練 習 (1) 修 改 功 能 簡 介 : UPDATE table 名 稱 SET ( 欄 位 名 稱, 欄 位 名 稱...)=( 子 查 詢 ) [WHERE 查 詢 條 件 ] UPDATE table 名 稱 SET 欄 位 1= 值 1, 欄 位 2= 值 2... [WHERE 查 詢 條 件 ] 例 : 某 銀 行 剛 和 VISA 發 卡 公 司 簽 下 合 同, 同 意 提 供 給 該 VISA 公 司 的 客 戶 10% 的 貸 款, 你 可 使 用 下 列 命 令 來 更 新 : UPDATE TRANSMASTER SET NET_AMOUNT=NET_AMOUNT*0.9 WHERE CUSTID = (SELECT CUSTID FROM CUSTOMER WHERE COMPANY= SA ); (2) 新 增 功 能 簡 介 : INSERT INTO table 名 稱 [( 欄 位 名 稱, 欄 位 名 稱...)] VALUES ( 欄 位 內 容, 欄 位 內 容...) (3) 刪 除 功 能 簡 介 : DELETE FROM table 名 稱 [WHERE 查 詢 條 件 ] 頁 次 :22

23 3. 交 易 控 制 語 言 (TCL): 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) COMMIT ROLLBACK 確 認 交 易 放 棄 交 易 Transaction 是 指 每 次 所 交 付 執 行 的 一 連 串 的 動 作, 而 這 些 動 作 形 成 一 個 工 作 單 位, 且 每 次 的 transaction 必 須 是 完 全 執 行, 或 完 全 不 執 行, 而 不 允 許 只 執 行 部 分 本 質 上 每 一 個 SQL 指 令 敘 述 即 是 一 個 transaction 例 如 : Update course set score=score*1.5 此 指 令 敘 述, 會 修 改 course 表 格 中 所 有 資 料, 要 就 全 部 做 完, 要 就 全 不 作, 而 不 會 只 做 一 半 萬 一 做 到 一 半, 系 統 當 機 時 怎 麼 辦? 當 資 料 庫 系 統 重 新 啟 動 時, 假 若 資 料 庫 未 損 毀, 則 系 統 會 執 行 一 個 復 原 (recovery) 的 動 作, 回 復 先 前 的 狀 態 ( 註 : 此 復 原 動 作 需 視 不 同 資 料 庫 系 統 而 有 不 同 動 作 模 式 ) TCL 指 令 包 括 :COMMIT, ROLLBACK, 藉 由 BEGIN TRANSACTION 啟 動 交 易 的 控 制 (1)COMMIT 結 束 當 前 交 易 (Transaction), 使 當 前 交 易 所 執 行 的 全 部 修 改 永 久 化, 同 時 刪 除 交 易 所 設 定 的 全 部 保 留 點 (Savepoint), 釋 放 該 交 易 執 行 中 所 建 立 的 鎖 (Lock) (2)ROLLBACK 結 束 交 易, 撤 銷 當 前 交 易 中 的 全 部 改 變, 刪 除 該 交 易 中 所 設 定 的 全 部 保 留 點, 釋 放 該 交 易 執 行 中 所 建 立 的 鎖 語 法 : BEGIN TRANSACTION name COMMIT TRANSACTION name ROLLBACK TRANSACTION SAVE TRANSACTION name ( 可 Rollback 至 此 name 所 異 動 的 部 分 ) 範 例 1 COMMIT 下 列 範 例 包 含 兩 個 指 令 敘 述, 一 個 是 修 改 學 生 姓 名, 一 個 是 新 增 該 生 修 習 科 目 : begin transaction 頁 次 :23

24 update student set name=' 張 叄 ' where id=' ' insert into course (id,subject,score) values (' ','LM',79) commit transaction 由 於 兩 個 指 令 敘 述 包 含 在 begin transaction 和 commit transaction 之 中, 資 料 庫 系 統 會 保 證 這 兩 個 指 令 一 定 會 完 全 做 完, 而 不 會 只 做 一 辦, 以 避 免 導 致 學 生 資 料 與 選 課 資 料 不 一 致 情 形 範 例 2 ROLLBACK 若 將 上 述 範 例 的 commit 更 換 成 rollback: begin transaction update student set name=' 張 三 ' where id=' ' delete from course where id= and subject= LM rollback transaction 當 系 統 遇 到 rollback 指 令 時, 便 會 復 原 所 有 的 資 料 變 動, 回 到 begin transaction 前 的 狀 態 範 例 3 儲 存 點 置 : 交 易 控 制 包 含 儲 存 點 儲 存 點 可 以 使 資 料 在 復 原 時, 復 原 至 先 前 特 定 的 SQL 敘 述 執 行 位 begin tran save tran item1 update student set name=' 張 參 豐 ' where id=' ' insert into course (id,subject,score) values (' ','LM',79) save tran item2 insert student values (' ',' 孫 九 ',' 台 北 縣 新 店 市 ','05/26/1982','M') insert into course values (' ','LM','80') insert into course values (' ','CO','75') save tran item3 update course set score='85' where id=' ' and subject='co' delete course where id=' ' -- rollback tran item3 -- rollback tran item2 -- rollback tran item1 commit tran 頁 次 :24

25 十 七 SQL 運 算 子 1. 算 術 運 算 子 : 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 運 算 子 說 明 範 例 + - 單 元 運 算 子, 表 示 一 個 正 或 負 的 運 算 式 SELECT * FROM employee WHERE salary<0 * / 二 元 運 算 子, 為 乘 除 運 算 SELECT salary, salary*1.05, (salary*1.05)+comm from employee + - 二 元 運 算 子, 為 加 減 運 算 SELECT salary + comm FROM % 餘 數 employee WHERE GETDATE() hiredate > 字 元 運 算 子 : 運 算 子 說 明 範 例 + 連 接 運 算 子 SELECT 姓 名 : +lastname FROM employee 3. 比 較 運 算 子 : 運 算 子 說 明 範 例 = 相 等 判 斷 SELECT * FROM employee WHERE salary = 35000!= <> 不 等 判 斷 SELECT * FROM employee WHERE > < >= <=!>!< 大 於 判 斷 小 於 判 斷 大 於 等 於 判 斷 小 於 等 於 判 斷 不 大 於 不 小 於 salary!= SELECT * FROM employee WHERE salary > SELECT * FROM employee WHERE salary >= IN 存 在 任 何 成 員 判 斷 SELECT * FROM employee WHERE job IN (670,671) NOT IN 不 存 在 任 何 成 員 判 斷 SELECT * FROM employee WHERE ANY ( 只 能 用 於 子 查 詢 ) salary NOT IN (SELECT salary FROM employee WHERE deptno=30) 對 一 值 與 一 個 表 格 中, 每 一 值 或 查 SELECT * FROM employee WHERE 詢 傳 回 的 每 一 值 做 比 較 在 該 運 算 salary=any (SELECT salary FROM 子 之 前 必 須 有 =,!=,<,>,<=, employee WHERE deptno=30) >= 運 算 子 頁 次 :25

26 SOME ( 只 能 用 於 子 查 詢 ) ALL [NOT] BETWEEN X AND Y EXISTS X [NOT] LIKE Y IS [NOT ] NULL [ 說 明 ] 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 只 要 有 任 一 個 成 立, 即 為 True 例 如 >ANY(1,2,3) 表 示 比 較 對 象 只 要 大 於 1, 2, 3 任 一 數, 也 就 表 示 只 需 大 於 1 即 成 立 [ 其 中 1,2,3 表 示 子 查 詢 之 結 果 ] 只 要 有 任 一 些 成 立, 即 為 True 表 示 方 法 類 似 ANY, 比 較 對 象 對 SELECT * FROM employee WHERE 表 格 中 所 有 值 都 成 立, 才 為 True salary > all (select (salary+comm) from 例 如 比 較 對 象 >ALL(1,2,3) 表 employee where comm >3500) 示 比 較 對 象 必 須 大 於 1, 2, and 3, 也 就 表 示 需 大 於 3 [ 其 中 1,2,3 表 示 子 查 詢 之 結 果 ] [ 不 ] 存 在 X 和 Y 區 間 的 判 斷 SELECT * FROM employee WHERE 子 查 詢 有 獲 得 任 意 數 目 的 資 料 集, 則 傳 回 true > ALL(1,2,3) Ł > 3 true 則 執 行 select 之 結 果 false 則 不 執 行 select 之 結 果 salary BETWEEN AND SELECT * FROM dept WHERE EXISTS (SELECT * FROM employee WHERE dept.id=employee.deptno) 切 截 查 詢, 在 Y 中 : 符 號 % 可 SELECT * FROM employee WHERE 包 含 任 何 0 個 或 多 個 字 元 ; _ firstname LIKE JA% 可 包 含 任 何 單 一 字 元 測 試 空 值 SELECT * FROM employee WHERE comm IS NULL > ANY(1,2,3) Ł > 1 ( 假 設 上 例 中 的 1,2,3 為 子 查 詢 結 果, 因 為 ALL, ANY 必 須 用 於 子 查 詢 結 果 判 斷, 而 IN 則 可 用 於 表 列 或 子 查 詢 結 果 之 判 斷 基 本 上 IN 敘 述 可 等 於 =ANY 敘 述 ) SELECT * FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPT WHERE DEPT.ID=EMPLOYEE.DEPTNO AND LOCATION LIKE 一 樓 % ) 相 等 於 JOIN 查 詢 : SELECT EMPLOYEE.* FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DEPT=DEPT.ID AND LOCATION LIKE 一 樓 % 頁 次 :26

27 十 八 SELECT 查 詢 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 檔 案 結 構 : (1) 學 生 修 課 資 訊 (2) 商 品 銷 售 資 訊 (3) 圖 書 採 購 資 訊 * 表 示 主 鍵 (Primary key) FK 表 示 外 來 鍵 (Foreign key) 表 示 外 來 鍵 指 向 資 料 的 來 源 頁 次 :27

28 1. 一 般 查 詢 SELECT NAME, AUTHOR, PRICE, PUBLISHER FROM BOOK SELECT * FROM BOOK SELECT DISTINCT PRICE FROM BOOK 2. 算 數 查 詢 SELECT NAME, PRICE, PRICE*0.8 FROM BOOK 3. 條 件 (WHERE) 子 句 查 詢 SELECT BRN, NAME, AUTHOR, PRICE FROM BOOK WHERE BRN > 105 SELECT BRN, NAME, AUTHOR, PRICE FROM BOOK WHERE BRN IN (101,102,103,104) SELECT BRN, NAME, AUTHOR, PRICE FROM BOOK WHERE BRN BETWEEN 101 AND 104 SELECT * FROM BOOKSTORE WHERE CITY LIKE 台 % SELECT ID, NAME, RANK, CITY FROM BOOKSTORE WHERE NOT ( RANK >=20 AND CITY LIKE 台 % ) SELECT ID, NAME, RANK FROM BOOKSTORE WHERE RANK NOT IN (20,30) SELECT ID, NAME, CITY FROM BOOKSTORE WHERE NOT (CITY IN ( 台 北 市, 台 南 市, 台 中 市 )) SELECT ID, NAME, RANK, CITY FROM BOOKSTORE WHERE RANK IN (20,30) AND CITY IN ( 台 北 市, 台 南 市, 台 中 市 ) 練 習 : 試 列 出 男 性 (M) 學 生 地 址 在 台 北 市 的 修 課 細 節 頁 次 :28

29 4. 合 併 查 詢 (JOIN) SELECT BOOK.NAME, ORDERS.* FROM BOOK, ORDERS WHERE BOOK.BRN=ORDERS.BRN SELECT B.NAME, S.NAME, QUANTITY, QUANTITY*PRICE FROM BOOK B, BOOKSTORE S, ORDERS O WHERE O.BRN=B.BRN AND O.VID=S.ID 5. 別 名 (Alias) 別 名 分 為 欄 位 別 名 ( 標 籤,Label) 與 表 格 別 名 欄 位 別 名 用 於 改 變 資 料 呈 現 時 的 欄 位 標 籤 內 容 ; 表 格 別 名 則 是 用 於 簡 化 SQL 內 表 格 名 稱 的 長 度 欄 位 別 名 宣 告 方 式 : select 欄 位 欄 位 別 名, 表 格 別 名 宣 告 方 式 :select 欄 位,. from 表 格 表 格 別 名, [ 注 意 ] 欄 位 別 名 表 格 別 名 有 效 範 圍 只 在 同 一 SQL 敘 述 內 有 效 ; 使 用 表 格 別 名, 則 該 SQL 敘 述 內 所 有 該 表 格 名 稱 均 要 使 用 該 別 名, 不 可 再 用 原 表 格 名 稱 6. 排 序 查 詢 (ORDER BY) DESC 由 大 至 小 排 序 ASC 由 小 至 大 排 序 ( 預 設 ) SELECT ID, NAME, RANK, CITY FROM BOOKSTORE ORDER BY ID DESC SELECT * FROM BOOK ORDER BY AUTHOR ASC, BRN DESC 7. 聚 合 查 詢 五 項 基 本 函 數 : 函 數 名 稱 說 明 COUNT 計 算 指 定 欄 位 之 資 料 集 數 目 SUM 計 算 欄 位 內 容 之 總 合 AVG 計 算 欄 位 內 容 之 平 均 值 MIN 計 算 欄 位 內 容 之 最 小 值 MAX 計 算 欄 位 內 容 之 最 大 值 計 算 學 生 檔 (STUDENT) 的 人 數 計 算 科 目 CO 所 有 修 課 的 人 數, 總 分, 平 均 成 績, 最 高 分, 最 低 分 計 算 學 生 王 五 的 所 有 修 課 數 目, 總 分, 平 均 成 績, 最 高 分, 最 低 分 頁 次 :29

30 8. 巢 狀 查 詢 (1) 子 查 詢 只 有 一 個 值 * 使 用 BOOKSTORE 檔 : SELECT NAME,RANK FROM BOOKSTORE WHERE RANK < (SELECT AVG(RANK) FROM BOOKSTORE) SELECT NAME,RANK FROM BOOKSTORE WHERE RANK = (SELECT MAX(RANK) FROM BOOKSTORE)-10 * 使 用 EMPLOYEE 檔 : 列 出 和 ( 姓 )Green 同 部 門 的 員 工 資 料 SELECT * FROM EMPLOYEE WHERE DEPTNO = (SELECT DEPTNO FROM EMPLOYEE WHERE LASTNAME='Green') 列 出 比 ( 姓 )Green 薪 水 少 的 員 工 資 料 SELECT * FROM EMPLOYEE WHERE SALARY < (SELECT SALARY FROM EMPLOYEE WHERE LASTNAME='Green') (2) 子 查 詢 傳 回 一 個 集 合 SELECT NAME FROM BOOKSTORE WHERE ID IN (SELECT VID FROM ORDERS WHERE BRN='101') SELECT NAME FROM BOOKSTORE, ORDERS WHERE BOOKSTORE.ID=ORDERS.VID AND BRN='101' SELECT NAME FROM BOOKSTORE WHERE EXISTS (SELECT * FROM ORDERS WHERE ORDERS.VID=BOOKSTORE.ID AND BRN='101') 找 出 沒 人 訂 購 書 的 書 局 SELECT * FROM BOOKSTORE WHERE ID NOT IN (SELECT VID FROM ORDERS) 找 出 沒 人 訂 購 的 書 SELECT * FROM BOOK WHERE BRN NOT IN (SELECT BRN FROM ORDERS) 等 於 SELECT * FROM BOOK WHERE NOT EXISTS (SELECT * FROM ORDERS WHERE BOOK.BRN=ORDERS.BRN) 頁 次 :30

31 列 出 薪 水 比 部 門 編 號 開 頭 為 2 的 員 工 平 均 薪 水 還 高 的 其 他 部 門 員 工 資 料 SELECT * FROM EMPLOYEE WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPTNO LIKE '2%') AND DEPTNO NOT LIKE '2%' 列 出 與 Green 相 同 部 門 或 薪 水 比 他 高 的 員 工 姓 名 部 門 名 稱 和 工 資 SELECT LastName+' '+FirstName, Description, Salary FROM EMPLOYEE, DEPT WHERE DEPT.ID=EMPLOYEE.DEPTNO AND (DEPTNO=(SELECT DEPTNO FROM EMPLOYEE WHERE LASTNAME='Green') OR SALARY > (SELECT SALARY FROM EMPLOYEE WHERE LASTNAME='Green')) [ 注 意 ] 括 號 時 機, 不 需 特 別 排 除 Green 9. 群 組 功 能 (GROUP BY) 執 行 注 意 原 則 1. 需 出 現 在 select, having, order by 之 後 的 一 般 性 欄 位 ( 非 聚 合 函 數 結 果 ), 必 須 一 定 要 在 group by 有 出 現 group by 的 欄 位, 才 能 使 用 於 select, having,order by 之 處 ; select sex,id,count(*) from student group by sex-- 錯 誤 2. 有 使 用 在 gorup by 分 組 的 欄 位, 可 以 不 一 定 要 出 現 於 select 之 處 ; select count(*) from student group by sex -- 可 以 不 列 出 sex 欄 位 3. 有 多 個 群 組 的 資 料 條 件, 使 用 在 group by 時, 不 需 分 先 後 次 序 ; ----ex. 列 出 依 據 課 目 性 別 分 別 計 算 學 生 人 數 與 平 均 分 數 select subject,sex,count(*),avg(score) from student,course where student.id=course.id group by sex, subject --sex, subject 先 後 次 序 更 換 不 會 影 響 結 果 4. 如 果 group by 的 欄 位 不 包 含 主 鍵, 需 考 量 是 否 有 同 名 的 疑 慮 若 有, 則 須 加 上 主 鍵 ----ex. 請 依 姓 名 分 別 列 出 各 學 生 的 平 均 成 績 與 修 課 數 update student set name=' 張 三 ' where id= 將 資 料 更 動 一 下 以 便 測 試 select name,count(*),avg(score) from student,course where student.id=course.id group by name -- 可 能 資 料 會 有 錯 誤 select name,count(*),avg(score) from student,course where student.id=course.id 頁 次 :31

32 group by name,student.id-- 須 加 上 主 鍵, 否 則 同 名 的 人 會 被 合 併 計 算 select * from course order by id 5. group by 表 示 系 統 掃 描 整 個 檔 案 之 後 才 能 群 聚 指 定 的 資 料, 因 此 可 以 在 select where order by 之 後 直 接 使 用 聚 合 函 數 ; select id,count(*) from course -- 錯 誤! 聚 合 函 數 不 可 與 一 般 欄 位 並 列 select id,count(*) from course group by id order by avg(score) --Correct! 6. having 與 where 均 是 用 於 條 件 判 斷 where 的 條 件 是 執 行 於 群 組 之 前 的 判 斷 ;having 的 條 件 則 是 執 行 於 群 組 後 的 判 斷 --ex. 求 住 在 台 北 地 區 男 女 生 人 數 select sex,count(*) from student where address like ' 台 北 %' group by sex --ex. 列 出 男 生 (sex='m') 中 修 課 數 超 過 3 門 的 學 生 姓 名 與 修 課 數 平 均 成 績 select name,count(*),avg(score) from student, course where student.id=course.id and sex='m' group by student.id, name having count(*)>3 求 訂 單 檔 中 各 書 被 訂 購 的 次 數 SELECT BRN, COUNT(BRN) FROM ORDERS GROUP BY BRN 求 各 書 被 訂 購 的 數 量 SELECT BRN, SUM(QUANTITY) FROM ORDERS GROUP BY BRN 列 出 訂 單 中 各 訂 購 商 購 買 各 種 書 本 的 數 量 SELECT VID,BRN,SUM(QUANTITY) FROM ORDERS GROUP BY VID,BRN ORDER BY VID,BRN 求 各 學 生 的 平 均 成 績 SELECT AVG(SCORE) FROM COURSE GROUP BY ID [ 注 意 ] 以 非 PK 分 組 之 不 足 之 處, 例 如 以 姓 名 分 組 頁 次 :32

33 求 學 生 中 男 生 女 生 的 人 數 SELECT SEX, COUNT(*) FROM STUDENT GROUP BY SEX 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 求 各 訂 購 商 訂 購 書 的 總 數, 列 出 時 請 列 出 訂 購 商 名 稱 SELECT ORDERS.VID, BOOKSTORE.NAME, SUM(QUANTITY) FROM ORDERS, BOOKSTORE WHERE ORDERS.VID=BOOKSTORE.ID GROUP BY ORDERS.VID, BOOKSTORE.NAME 10. 群 組 條 件 (HAVING) HAVING 之 前 一 定 要 有 GROUP BY 敘 述, 用 來 過 濾 掉 GROUP BY 之 條 件 (1) 列 出 訂 單 檔 中 各 商 品 購 買 總 數 超 過 20 的 訂 單 編 號 與 購 買 總 數 ( 依 訂 單 分 組 ) SELECT ID, SUM(QUANTITY) FROM ORDERS GROUP BY ID HAVING SUM(QUANTITY) >20 (2) 請 列 出 同 一 個 書 目 資 料 下 所 有 館 藏 總 和 被 借 超 過 20 次 的 書 目 編 號 書 名 與 作 者 SELECT BIB.BRN,TITLE FROM HOLDING,BIB WHERE HOLDING.BRN=BIB.BRN GROUP BY BIB.BRN, TITLE HAVING SUM(COUNT)>20 (3) [ 依 員 工 各 別 統 計 ] 列 出 本 月 ( 判 斷 年 度 月 份 ) 份 銷 售 單 價 總 計 超 過 的 員 工 姓 名 與 其 銷 售 金 額 總 計 ( 數 量 * 單 價 ) SELECT E.NAME, SUM(PRICE*COUNT) FROM SALES S,EMPLOYEE E WHERE S.ENO=E.ENO AND DATEPART(YY,SDATE)=DATEPART(YY,GETDATE()) AND DATEPART(MM,SDATE)=DATEPART(MM,GETDATE()) GROUP BY E.ENO, E.NAME HAVING SUM(PRICE) >0 [ 注 意 ]: 因 員 工 有 可 能 同 名 同 姓, 因 此 不 可 以 只 用 員 工 姓 名 作 GROUP BY 分 組 的 依 據 頁 次 :33

34 (4) 請 依 據 各 部 門 分 別 統 計 各 商 品 銷 售 數 量 超 過 100 份 部 門 名 稱 與 商 品 名 稱 [ 資 料 庫 無 此 檔 案 ] SELECT D.ID, P.NAME, SUM(COUNT) FROM SALES S, EMPLOYEE E, DEPT D, PRODUCT P WHERE S.ENO=E.ENO AND D.ID=E.DEPT AND S.PID=P.ID GROUP BY D.ID, P.ID, P.NAME HAVING SUM(COUNT) > 1000 (5) 列 出 修 課 人 數 最 多 的 老 師 姓 名 修 課 人 數 與 平 均 成 績 SELECT SUBJECT, J.DESCRIPTION,COUNT(*), AVG(SCORE) FROM COURSE S,SUBJECT J WHERE S.SUBJECT=J.ID GROUP BY SUBJECT,J.DESCRIPTION HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM COURSE GROUP BY SUBJECT) (6) 列 出 修 課 平 均 成 績 最 高 的 科 目 名 稱 與 老 師 姓 名 SELECT SUBJECT, J.DESCRIPTION, T.DESCRIPTION, AVG(SCORE) FROM COURSE S,SUBJECT J,TEACHER T WHERE S.SUBJECT=J.ID AND J.TEACHER=T.ID GROUP BY SUBJECT,J.DESCRIPTION, T.DESCRIPTION HAVING AVG(SCORE) >= ALL(SELECT AVG(SCORE) FROM COURSE GROUP BY SUBJECT) [ 練 習 ] (1) 列 出 家 住 在 台 北 的 男 生, 且 平 均 成 績 高 於 80 分 的 姓 名 地 址 平 均 成 績 與 修 課 數 (2) 列 出 部 門 人 數 超 過 4 人 以 上 的 部 門 編 號 人 數 與 最 高 最 低 的 薪 資 (3) 列 出 部 門 平 均 薪 水 超 過 的 員 工 資 料 群 組 之 後, 資 料 庫 系 統 便 以 組 為 單 位, 可 以 列 出 各 組 的 基 本 資 料, 但 無 法 列 出 各 組 內 容 的 細 節 如 果 要 列 出 群 組 後 各 組 的 細 節, 則 該 群 組 的 敘 述 要 以 子 查 詢 方 式 處 理 頁 次 :34

35 單 元 練 習 : ( 請 先 自 行 繪 出 STUDENT, COURSE, SUBJECT, TEACHER 四 個 表 格 的 檔 案 結 構 ) 1. 連 續 題 : (1) 列 出 各 科 修 課 人 數 (2) 列 出 學 生 的 修 課 科 目 數 量 平 均 成 績 (3) 列 出 修 課 人 數 超 過 8 人 的 修 課 科 目 代 碼 (4) 列 出 修 課 人 數 超 過 8 人 的 修 課 科 目 代 碼 學 生 姓 名 及 成 績 (5) 列 出 修 課 人 數 超 過 8 人 的 修 課 科 目 名 稱 學 生 姓 名 及 成 績 (6) 列 出 修 課 人 數 超 過 8 人 的 修 課 科 目 名 稱 學 生 姓 名 及 成 績, 並 依 科 目 名 稱 成 績 學 號 排 序 2. 列 出 學 生 平 均 成 績 低 於 80 分 的 學 生 學 號 姓 名 平 均 成 績 3. 列 出 科 目 平 均 成 績 高 於 75 分 的 科 目 名 稱 授 課 老 師 姓 名 平 均 分 數 4. 列 出 修 課 平 均 成 績 比 平 均 成 績 還 高 的 學 生 學 號 及 其 平 均 成 績 5. 列 出 修 課 男 生 女 生 的 平 均 成 績 6. 求 資 料 庫 分 數 最 高 學 生 的 所 有 科 目 成 績 7. 列 出 老 師 的 開 課 數 量 8. 列 出 有 開 課 成 功 的 老 師 姓 名 與 開 課 數 量 ( 與 第 7 題 比 較 ) 9. 列 出 老 師 的 姓 名 開 課 科 目 數 量 10. 列 出 老 師 的 姓 名 開 課 科 目 名 稱 各 科 目 修 課 學 生 人 數 修 課 的 平 均 成 績 頁 次 :35

36 9.XML SQL Server2000 提 供 在 SELECT 敘 述 執 行 並 輸 出 成 XML 的 功 能, 而 產 生 的 方 式 只 須 在 SELECT 敘 述 最 後 加 上 相 關 的 指 令 宣 告 即 可 指 令 宣 告 : FOR XML {RAW AUTO EXPLICIT [, XMLData] [, ELEMENTS] [, BINARY base64]} 關 鍵 字 說 明 : (1) FOR XML: 指 定 將 查 詢 結 果 輸 出 成 XML (2) RAW: 指 定 將 查 詢 結 果 每 一 列 資 料 以 通 用 的 <row> 元 素 表 示, 各 欄 位 內 容 以 屬 性 方 式 表 示 (3) AUTO: 指 定 將 多 種 表 格 查 詢 結 果 轉 換 成 一 個 XML 的 巢 狀 元 素, 各 欄 位 內 容 以 屬 性 方 式 表 示 ( 不 支 援 group by) (4) EXPLICIT: 傳 回 應 建 立 之 XML 樹 狀 形 式 (5) XMLData: 指 定 將 查 詢 結 果 產 生 之 XML 內 包 含 該 表 格 Schema 之 DTD (6) ELEMENTS: 指 定 將 查 詢 結 果 之 各 欄 位 以 元 素 型 態 傳 回 ( 須 配 合 AUTO) (7) BINARY base64: 指 定 將 傳 回 之 二 進 位 資 料 以 標 準 的 base64 編 碼 頁 次 :36

37 十 九 SQL 函 數 常 用 的 SQL Server 資 料 庫 函 數 : 名 稱 說 明 匯 總 函 數 : 計 算 表 格 內 某 一 欄 位 的 加 總 值 平 均 值 最 大 值 最 小 值 AVG() COUNT() MAX() MIN() SUM() GETDATE() DAY(date) MONTH(date) YEAR(date) DATEPART(datepart, date) select AVG(unit_price) from pur_item where prod_id= P001 select COUNT(*) from employee select prod_id,count(*) from sale_item group by prod_id having count(*) > 3 select * fom employee where salary = (select MAX(salary) from employee) select * fom employee where salary = (select MIN(salary) from employee) select order_date,sum(tot_amt),avg(tot_amt),count(*) from sales group by order_date order by order_date 日 期 函 數 取 得 系 統 日 期 select DAY( 05/21/2002 ) select MONTH( 05/21/2002 ) select MONTH( 05/21/2002 ) 語 法 : DATEPART( datepart 參 數, date) YEAR(YY) QUARTER(QQ) 1-4 MONTH(MM) 1-12 DAYOFYEAR(DY) DAY(DD) 1-31 WEEK(WK) 1-53 WEEKDAY(DW) 1-7( 星 期 日 - 星 期 六 ) HOUR( 不 能 用 HR) 0-23 MINUTE(MI) 0-59 SECOND(SS) 0-59 MILLISECOND(MS) 統 計 每 月 訂 單 總 額 與 訂 單 筆 數 : select DATEPART(MONTH, order_date),sum(tot_amt),count(*) from sales group by 頁 次 :37

38 DATEDIFF(datepart, startdate, enddate) DATEADD(datepart,number,date) DATENAME(datepart,date) DATEPART(MONTH,order_date) order by DATEPART(MONTH,order_date) 計 算 startdate 至 enddate 距 離 多 少 個 datepart 單 位 日 期 加 上 指 定 的 日 期 值 例 : DATEADD(DAY,10, 1996/12/24 ) 1997 年 1 月 3 日 DATEADD(MONTH,2, 1996/12/24 ) 1997 年 2 月 24 日 DATEADD(WEEK,3, 1996/12/24 ) 1997 年 1 月 14 日 顯 示 日 期 個 別 組 成 部 分 的 名 稱 例 : LEN(str) ASCII(str) CHAR(int) LOWER(str) UPPER(str) LTRIM(str) RTRIM(str) STR(float,[len,[decimal]]) DATENAME(YEAR, 1996/09/24 ) 1996 DATENAME(QUARTER, 1996/09/24 ) 3 DATENAME(WEEK, 1996/09/24 ) 39 DATENAME(WEEKDAY, 1996/09/24 ) Tuesday DATENAME(DAYOFYEAR, 1996/09/24 ) 268 字 串 函 數 求 字 串 內 容 的 長 度 求 字 串 最 左 字 元 的 ASCII 值 將 ASCII 值 轉 為 ASCII 碼 將 字 串 全 部 轉 為 小 寫 將 字 串 全 部 轉 為 大 寫 去 除 字 串 前 置 空 白 去 除 字 串 後 方 空 白 將 數 字 轉 為 字 串,len 為 字 串 總 長 度,decimal 為 小 數 位 數 STUFF(string1,start,len,string2) 將 string1 字 串 由 start 位 置 處 刪 除 len 個 字 元, 並 將 string2 字 串 由 start 處 插 入 RIGHT(str_expr, int_expr) LEFT(str_expr, int_expr) 從 字 串 的 最 右 邊 往 回 取 int_expr 個 字 元 從 字 串 的 最 左 邊 位 置 取 int_expr 個 字 元 RIGHT( abcdefgh,5) LEFT( abcdefg,5) 結 果 為 defgh 結 果 為 abcde SUBSTRING(str_expr,start,len) 由 字 串 左 邊 算 起 第 start 位 置 取 長 度 為 len 的 字 串 REPLACE(str_expr, str1,str2) 將 字 串 str_expr 的 內 容 為 str1 取 代 成 str2 例 :select name, replace(name,' 台 ',' 臺 ') from book 頁 次 :38

39 系 統 函 數 ISNULL(expr, val) 將 expr 中 為 NULL 值 者 以 val 值 取 代 SELECT SUM(SALARY+COMM), SUM(SALARY+ISNULL(COMM,0)) FROM EMPLOYEE 頁 次 :39

40 本 單 元 介 紹 流 程 : 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 建 立 檔 案 / 刪 除 檔 案 新 增 / 修 改 / 刪 除 紀 錄 (DDL) 異 動 管 理 (TCL) 建 立 視 界 / 索 引 二 十 資 料 建 立 1. 檔 案 建 立 CREATE TABLE [ database_name.[ owner ]. owner. ] table_name ( { < column_definition > column_name AS computed_column_expression < table_constraint > ::= [ CONSTRAINT constraint_name ] } ) [ { PRIMARY KEY UNIQUE } [,...n ] [ ON { filegroup DEFAULT } ] [ TEXTIMAGE_ON { filegroup DEFAULT } ] < column_definition > ::= { column_name data_type } [ COLLATE < collation_name > ] [ [ DEFAULT constant_expression ] [ IDENTITY [ ( seed, increment ) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL] [ < column_constraint > ] [...n ] < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL NOT NULL ] [ { PRIMARY KEY UNIQUE } [ CLUSTERED NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor ] [ON {filegroup DEFAULT} ] ] ] [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE NO ACTION } ] [ ON UPDATE { CASCADE NO ACTION } ] [ NOT FOR REPLICATION ] ] CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } < table_constraint > ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY UNIQUE } [ CLUSTERED NONCLUSTERED ] { ( column [ ASC DESC ] [,...n ] ) } [ WITH FILLFACTOR = fillfactor ] 頁 次 :40

41 [ ON { filegroup DEFAULT } ] ] FOREIGN KEY [ ( column [,...n ] ) ] REFERENCES ref_table [ ( ref_column [,...n ] ) ] [ ON DELETE { CASCADE NO ACTION } ] [ ON UPDATE { CASCADE NO ACTION } ] [ NOT FOR REPLICATION ] CHECK [ NOT FOR REPLICATION ] ( search_conditions ) } 2. DML 資 料 維 護 的 語 法 : 新 增 資 料 :INSERT INTO 檔 案 ( 欄 位, 欄 位, ) VALUES ( 資 料, 資 料, ) 修 改 資 料 :UPDATE 檔 案 SET 欄 位 = 資 料, 欄 位 = 資 料, WHERE 條 件 刪 除 資 料 :DELETE FROM 檔 案 WHERE 條 件 DML DDL TCL/DCL INSERT 新 增 CREATE ROLLBACK DELETE 刪 除 DROP COMMIT UPDATE 修 改 ALTER SELECT 選 擇 無 檔 案 建 立 的 基 本 語 法 : CREATE TABLE 檔 案 名 稱 ( 欄 位 名 稱 資 料 型 態 [ 限 制 ], ) 欄 位 名 稱 資 料 型 態 [ 限 制 ], [ 主 鍵 宣 告,] [ 外 來 鍵 宣 告 ] 3. 常 使 用 的 限 制 (Constraint) 宣 告 : (1) 宣 告 主 鍵 ( 當 該 檔 案 的 主 鍵 只 有 一 個 欄 位 時 ):PRIMARY KEY (2) 預 設 值 :DEFAULT 值 (3) 檢 查 ( 當 資 料 輸 入 時 驗 證 的 語 法 ):CHECK ( 條 件 ) (4) 不 允 許 虛 值 :NOT NULL 頁 次 :41

42 若 檔 案 的 主 鍵 包 含 不 只 一 個 欄 位, 則 主 鍵 必 須 單 獨 宣 告 ( 若 主 鍵 僅 一 個 欄 位, 亦 可 使 用 此 種 宣 告 方 式 ): PRIMARY KEY ( 欄 位, 欄 位, ), 若 該 檔 案 的 某 些 欄 位 為 外 來 鍵, 則 外 來 鍵 的 宣 告 為 : FOREIGN KEY ( 欄 位, 欄 位, ) REFERENCES 主 檔 檔 名 ( 欄 位, 欄 位, ) [ ON DELETE CASCADE ON UPDATE CASCADE] 例 : CREATE TABLE STUDENT ( ID VARCHAR(10) PRIMARY KEY, NAME VARCHAR(10) NOT NULL, AGE INT CHECK (AGE >10 AND AGE <60), BIRTH DATETIME DEFAULT GETDATE()); INSERT INTO STUDENT (ID,NAME,AGE) VALUES ('A001',' 張 三 ',20) INSERT INTO STUDENT (ID,NAME,BIRTH) VALUES ('A002',' 李 四 ','2/12/1981') INSERT INTO STUDENT (ID,NAME,AGE,BIRTH) VALUES ('A003',' 王 五 ',21,'4/8/1980') INSERT INTO STUDENT VALUES ('A004',' 錢 六 ',22,'7/1/1979') CREATE TABLE STUDENT_SCORE (ID VARCHAR(10), SUBJECT VARCHAR(10), SCORE INT CHECK (SCORE > 0 AND SCORE< 100), PRIMARY KEY (ID,SUBJECT), FOREIGN KEY (ID) REFERENCES STUDENT(ID) ON DELETE CASCADE); INSERT INTO STUDENT_SCORE VALUES ('A001','ENG',80); INSERT INTO STUDENT_SCORE VALUES ('A001','CHI',92); [ 新 增 資 料 之 錯 誤 練 習 ] (1) 遺 漏 欄 位 (2) 欄 位 與 資 料 型 態 不 符 (3) 輸 入 資 料 超 過 資 料 型 態 範 圍 (4) 超 過 CHECK 值 (5) 重 覆 KEY 值 (6) 輸 入 不 存 在 的 欄 位 (7) 輸 入 不 存 在 的 FOREIGN KEY 值 頁 次 :42

43 [ 比 較 ] (1) 能 否 執 行 INSERT INTO COURSE VALUES ('A005','ENG',70); (2) (2) DELETE FROM STUDENT WHERE ID= A001 之 後,COURSE 是 否 仍 存 在 A001 的 成 績 資 料? (3) 能 否 執 行 DROP TABLE STUDENT 4. 檔 案 修 改 ALTER TABLE 檔 名 修 改 類 型 欄 位 [ 範 例 ] alter table department add position varchar(20) // 新 增 一 個 欄 位 的 定 義 alter table department drop column position // 刪 除 一 個 欄 位 的 定 義 alter table department alter column position varchar(30) // 修 改 一 個 欄 位 的 定 義 5. 檔 案 刪 除 DROP TABLE tablename [ 練 習 ] 檔 案 建 立 及 建 立 的 先 後 順 序 (1) 建 立 27 頁 之 相 關 檔 案, 建 立 時 包 含 宣 告 Primary key, Foreign key, 並 考 慮 建 立 所 須 之 索 引 檔 (2) 逐 一 刪 除 檔 案 注 意 Foreign key 所 造 成 的 刪 除 次 序 限 制 6. SQL Server 存 取 使 用 Unicode 字 碼 雖 然 SQL Server 已 經 支 援 Unicode, 資 料 處 理 的 模 式 亦 是 依 據 字 元 而 非 位 元 組, 不 過 實 際 儲 存 資 料 是 若 非 指 名 使 用 Unicode, 系 統 預 設 仍 是 以 資 料 一 般 的 字 碼 處 理 ( 例 如 台 灣 就 會 以 Big5 字 碼 為 預 設 字 元 ) 參 考 下 列 範 例, 先 建 立 一 個 包 含 兩 個 欄 位 的 表 格, 其 中 NOTE 欄 位 宣 告 為 變 長 字 串, 長 度 9: CREATE TABLE TEMP (ID CHAR(3) PRIMARY KEY, NOTE VARCHAR(9) ); 接 著, 請 試 著 輸 入 下 列 資 料, 並 予 以 執 行 : INSERT INTO TEMP VALUES ('001',' 三 個 字 '); INSERT INTO TEMP VALUES ('002',' 四 個 words'); INSERT INTO TEMP VALUES ('003',' 這 有 五 個 字 '); 依 據 宣 告,NOTE 應 該 可 以 存 放 9 個 字 元 的 資 料, 但 實 際 輸 入 第 三 筆 資 料 時, 卻 出 現 下 列 訊 息 : 伺 服 器 : 訊 息 8152, 層 級 16, 狀 態 9, 行 1 頁 次 :43

44 字 串 或 二 進 位 資 料 會 被 截 斷 陳 述 式 已 經 結 束 表 示 已 經 超 過 允 許 長 度 若 是 以 函 數 LEN( ) 檢 查 資 料 內 容, 系 統 能 夠 很 正 確 地 依 據 字 元 數 目 計 算 出 : SELECT NOTE,LEN(NOTE) FROM TEMP 得 到 下 列 結 果 : SQL Server 如 何 存 取 Unicode 字 碼 的 方 法 : 1. 宣 告 的 欄 位 名 稱 必 須 使 用 'n', 如 nchar, nvarchar 2. 存 入 資 料 時 必 須 在 資 料 前 加 上 一 個 N 字 元 ( 必 須 大 寫 ) 例 如 : CREATE TABLE TEMP (sql integer, id nchar(5), name nvarchar(10), title varchar(10)) INSERT INTO TEMP VALUES (1, ' 山 东 ',' 包 兆 茏 ',' 经 理 ') INSERT INTO TEMP VALUES (2, N' 山 东 ',N' 包 兆 茏 ',N' 经 理 ') INSERT INTO TEMP VALUES (3, ' 山 東 ', N' 包 兆 茏 图 书 馆 ',N' 經 理 ') select * from Temp 的 結 果 如 下 : 山? 包 兆?? 理 2 山 东 包 兆 茏? 理 3 山 東 包 兆 茏 图 书 馆 經 理 可 以 看 出 輸 入 資 料 時 在 前 面 加 一 N 字 元, 就 算 是 該 欄 位 為 非 Unicode 亦 可 ( 如 範 例 中 的 title 欄 位, 並 非 宣 告 為 nvarchar) 縱 使 欄 位 宣 告 為 Unicode( nchar 或 nvarchar), 若 沒 在 字 串 前 加 上 'N' 字 元, 則 一 樣 不 會 存 入 Unicode 7. SQL Server 如 何 於 欄 位 自 動 編 流 水 號 (identify) 方 法 : 使 用 IDENTITY ( 起 始 值, 遞 增 ) ex. CREATE TABLE new_std ( id int IDENTITY(1,1), name varchar (20)); 之 後 可 不 需 指 定 id 欄 位 的 值, 則 系 統 會 補 上 流 水 號 若 欲 取 得 最 近 產 生 的 識 別 值 編 號, 可 使 用 : 頁 次 :44

45 SELECT IDENT_CURRENT(' 表 格 名 稱 '); 插 入 資 料 時 如 何 自 訂 識 別 值 的 值? 1. 開 啟 identity_insert set identity_insert 表 格 名 稱 on ; 2. 開 始 輸 入 資 料 3. 關 閉 identity_insert set identity_insert 表 格 名 稱 off ; 頁 次 :45

46 4. 視 界 (View) 建 立 VIEW 可 以 視 為 另 一 種 形 式 的 表 格 和 表 格 一 樣,VIEW 也 是 由 數 個 欄 位 定 義 所 組 成, 只 不 過 VIEW 的 欄 位 是 來 自 其 他 表 格 內 的 欄 位, 它 並 無 自 己 定 義 的 的 欄 位 使 用 者 可 以 對 VIEW 執 行 SELECT, INSERT, UPDATE 和 DELETE 的 動 作, 利 用 GRANT 指 令 將 視 界 的 使 用 權 開 放 給 特 定 的 使 用 者 等 等 CREATE VIEW [ < database_name >.[ owner.] view_name [ ( column [,...n ] ) ] [ WITH < view_attribute > [,...n ] ] AS select_statement [ WITH CHECK OPTION ] CREATE VIEW 指 令 中 的 SELECT 有 以 下 的 限 制 : (1) 不 能 使 用 order by, compute 或 compute by 語 句 (2) 不 能 使 用 union 語 句 (3) 不 能 使 用 into 語 句 視 界 檔 案 的 資 料 來 源, 且 視 界 檔 案 的 欄 位 必 須 與 SELECT 輸 出 結 果 的 欄 位 數 量 一 致 當 SELECT 輸 出 結 果 的 欄 位 名 稱 沒 有 重 複 且 均 有 名 稱 時, 可 以 省 略 視 界 檔 案 的 欄 位 宣 CREATE VIEW 視 界 檔 名 ( 欄 位, 欄 位, ) 告, 沿 用 原 先 的 欄 位 名 稱 AS SELECT 欄 位, 欄 位, FROM 檔 案, 檔 案, WHERE 練 習 1: STUDENT, COURSE, SUBJECT, TEACHER 表 格 之 間 有 建 立 foreign key 之 關 係 BOOK, ORDERS, BOOKSTORE, PUBLISHER 表 格 之 間 沒 有 建 立 foreign key 之 關 係 ( 參 見 p.27 之 檔 案 結 構 ) [ 比 較 ] (1) 建 立 單 純 的 VIEW 建 立 下 列 查 詢 的 VIEW 檔 : (1.1) 應 用 Orders 表 格 建 立 包 含 所 有 出 貨 日 期 距 訂 貨 日 期 超 過 100 天 資 料 的 VIEW (1.2) 應 用 Student, Course 表 格 建 立 包 含 所 有 學 生 姓 名 與 其 平 均 成 績 的 VIEW (2) 建 立 VIEW 的 VIEW (3) 透 過 join 所 結 合 的 VIEW 能 否 執 行 insert, update, delete 指 令 (3.1) 有 foreign key 情 況 之 資 料 異 動 結 果 頁 次 :46

47 (3.2) 沒 有 foreign key 情 況 之 資 料 異 動 結 果 (4) 透 過 aggregation 所 結 合 的 VIEW 能 否 執 行 insert, update, delete 指 令 5. 刪 除 視 界 DROP VIEW viewname 練 習 2: (1) 使 用 INFORMATION_SCHEMA.VIEWS 列 出 此 一 資 料 庫 有 哪 一 些 VIEW 表 格 (2) 使 用 INFORMATION_SCHEMA.TABLES 列 出 此 一 資 料 庫 有 哪 一 些 VIEW 表 格 (TABLE_TYPE 欄 位 內 容 :BASE TABLE/VIEW) 頁 次 :47

48 二 十 一 索 引 (Index) 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 對 關 連 式 資 料 庫,INDEX 是 相 當 重 要 的, 系 統 透 過 INDEX 直 接 指 向 我 們 所 需 的 資 料 其 存 在 資 料 庫 內 的 主 要 目 的 是 : 改 善 資 料 的 存 取 速 度 若 設 定 為 唯 一 性 的 索 引, 則 可 強 制 資 料 在 單 一 檔 案 內 的 唯 一 性 (1) 沒 有 INDEX, 資 料 庫 管 理 系 統 必 須 為 找 尋 一 筆 資 料, 而 掃 描 整 個 Table (2) 有 INDEX, 資 料 庫 透 過 管 理 系 統 所 管 理 的 索 引 表, 計 算 出 資 料 儲 存 在 Table 中 的 位 置 後, 直 接 讀 取 出 (3) 系 統 必 須 花 費 時 間 去 更 新 維 護 任 何 INDEX, 所 以 在 產 生 INDEX 之 前 必 須 確 定 需 要 任 何 對 Table 新 增 修 改 和 刪 除 動 作 均 會 觸 發 系 統 更 新 所 屬 的 INDEX, 因 此 越 多 的 INDEX 會 造 成 系 統 效 率 降 低 1. 建 立 新 索 引 檔 CREATE [ UNIQUE ] [ CLUSTERED NONCLUSTERED ] INDEX index_name ON { table view } ( column [ ASC DESC ] [,...n ] ) 索 引 檔 建 立 之 觀 念 : (1) 一 個 檔 案 (Table) 可 以 有 0~n 個 索 引 檔 (2) 索 引 檔 一 定 屬 於 某 一 個 Table (3) 索 引 檔 種 類 包 含 一 般 索 引 與 唯 一 性 索 引 (Unique) 兩 種, 唯 一 性 即 表 示 建 立 該 索 引 之 欄 位 內 容 不 可 重 複 (4) 宣 告 之 主 鍵 (Primary key), 系 統 會 自 動 依 據 主 鍵 欄 位 宣 告 一 個 唯 一 性 索 引 檔 系 統 管 理 者 (DBA) 可 依 實 際 所 需, 手 動 建 立 索 引 檔, 建 立 後 DBMS 會 依 檔 案 內 容 的 異 動 自 行 維 護 索 引 檔, 不 需 人 工 維 護 刪 除 檔 案 時,DBMS 系 會 自 動 刪 除 該 檔 案 所 屬 的 所 有 索 引 檔 (Table) 一 般 索 引 檔 宣 告 語 法 : CREATE INDEX 索 引 檔 名 ON 檔 案 名 稱 ( 欄 位 名 稱, 欄 位 名 稱, ) 唯 一 性 索 引 檔 宣 告 語 法 : CREATE UNIQUE INDEX 索 引 檔 名 ON 檔 案 名 稱 ( 欄 位 名 稱, 欄 位 名 稱, ) *UNIQUE 對 資 料 表 或 檢 視 表 建 立 一 個 唯 一 的 索 引 ( 亦 即 任 何 兩 個 資 料 列 都 不 許 可 有 相 同 索 引 值 的 索 引 ) 建 立 UNIQUE 索 引 時, 會 將 多 個 NULL 值 視 為 重 複 * 排 序 不 是 遞 增 asc 就 是 遞 減 desc, 若 未 註 明, 則 預 設 為 內 定 的 asc column_name 頁 次 :48

49 從 左 至 右 的 順 序 表 示 索 引 主 要 到 次 要 的 順 序 2. 刪 除 索 引 檔 DROP INDEX table.index 註 : 索 引 檔 案 資 訊 記 錄 於 系 統 檔 SYSINDEXES 內, 可 以 透 過 預 儲 程 序 SP_HELPINDEX 獲 知 特 定 某 一 個 表 格 的 索 引 檔, 或 使 用 SP_STATISTICS 列 出 特 定 某 一 個 表 格 索 引 檔 的 較 多 細 節 SP_HELPINDEX student 列 出 student 索 引 檔 的 index 明 細 SP_STATISTICS student 列 出 student 索 引 檔 的 index 明 細 ( 索 引 的 統 計 資 料 ) 頁 次 :49

50 二 十 二 預 儲 程 序 (Stored Procedure) 預 儲 程 序 是 儲 存 在 DBMS 內 的 資 料 庫 物 件, 在 SQL Server 的 預 儲 程 序 中 包 含 了 一 組 Transaction-SQL 敘 述 式 以 便 在 往 後 重 複 使 用 它 們 就 和 其 他 程 式 語 言 的 副 程 式 (subroutine) 及 函 數 (function) 擁 有 相 同 的 作 用 邏 輯 上 來 說, 預 儲 程 序 包 含 了 : (1) 一 個 檔 頭 (Header), 當 中 定 義 了 預 儲 程 序 的 名 稱 輸 入 和 輸 出 參 數, 以 及 一 些 其 他 的 處 理 選 項 你 可 以 把 它 想 像 成 一 個 API(Application Programming Interace) 或 預 儲 程 序 的 宣 告 (2) 一 個 主 體 (Body), 當 中 包 含 了 當 預 儲 程 序 被 呼 叫 時, 所 應 該 執 行 的 Transaction-SQL 敘 述 式 預 儲 程 序 的 檔 頭 和 主 體 是 由 AS 這 個 關 鍵 字 來 區 隔 預 儲 程 序 的 檔 頭 包 含 了 參 數 的 清 單, 而 每 個 參 數 之 間 則 使 用 逗 號 "," 加 以 區 隔 每 個 參 數 的 定 義 都 包 含 了 一 個 識 別 項 和 資 料 型 別 參 數 的 識 別 項 必 須 字 元 做 為 開 頭 1. 建 立 預 儲 程 序 語 法 : CREATE PROC[EDURE] procedure_name [{@parameter data_type} [ = default] [OUTPUT] ] [, n] AS sql-statement [ n] 範 例 : create proc varchar(50) as select * from bib where title=@tit 此 範 例 建 立 一 個 叫 做 gettitle 的 預 儲 程 序, 當 中 包 含 了 一 個 輸 入 參 數 當 gettitle 執 行 時, 它 會 傳 回 一 個 結 果 集 (resultset), 當 中 包 含 了 所 有 在 Bib 檔 案 中,Title 資 料 行 的 值 對 於 輸 入 參 數 的 所 有 紀 錄 預 儲 程 序 比 較 不 一 樣 的 地 方 是 它 們 的 實 際 設 計 預 儲 程 序 事 實 上 是 用 來 建 立 預 儲 程 序 的 Transact-SQL 敘 述 在 其 他 的 程 式 員 中, 程 序 只 是 列 出 所 有 的 動 作, 而 不 會 建 立 任 何 東 西 在 SQL Server 中, 預 儲 程 序 實 際 上 建 立 了 他 們 本 身 2. 刪 除 預 儲 程 序 Drop Proc gettitle 頁 次 :50

51 3. 修 改 預 儲 程 序 修 改 預 儲 程 序 的 一 種 方 式 是 重 新 建 立, 另 一 種 方 式 則 是 使 用 ALTER PROCEDURE 敘 述 來 修 改 預 儲 程 序 (1) 使 用 刪 除 在 新 增 方 式 : Drop proc gettitle go create proc int as select * from bib where price 如 果 不 確 定 一 個 預 儲 程 序 是 否 存 在 時, 可 以 先 撰 寫 一 段 程 式 來 加 以 檢 查 如 果 不 這 麼 做 的 話, 當 在 刪 除 一 個 並 不 存 在 的 預 儲 程 序 時,SQL Server 將 會 產 生 一 個 錯 誤 訊 息 if exists (select * from sysobjects where id=object_id('gettitle') and OBJECTPROPERTY(id,'isProcedure')=1) Drop proc gettitle go create proc int as select * from bib where price (1) 使 用 修 改 方 式 : Alter proc int as select * from bib where price 頁 次 :51

52 4. 呼 叫 預 儲 程 序 預 儲 程 序 的 主 要 目 的 之 一 就 是 以 有 用 的 格 式, 而 從 SQL Server 的 資 料 庫 中 傳 回 資 訊 Execute gettitle 400 Execute( 可 省 略 為 exec) 關 鍵 字 後 面 出 現 的 就 是 預 儲 程 序 的 名 稱 由 於 上 述 這 個 預 儲 程 序 只 需 要 一 個 參 數, 我 們 必 須 把 它 加 入 在 預 儲 程 序 之 後 create proc Varchar(20) AS select * from ProductOrder where OrderDate and ShipCountry exec getorder '1997/8/1', 'USA' 上 述 這 個 預 儲 程 序 範 例 在 執 行 時, 需 要 兩 個 參 數 : 一 個 是 日 期, 一 個 是 字 串, 所 以 我 們 必 須 在 參 數 內 容 的 前 後 加 上 單 引 號 註 : 預 儲 程 序 名 稱 儲 存 在 sysobjects 系 統 資 料 表 中, 而 CREATE PROCEDURE 陳 述 式 的 文 字 則 儲 存 於 syscomments 中 可 以 透 過 系 統 內 建 的 預 儲 程 序 sp_stored_procedures 獲 知 此 一 資 料 庫 已 宣 告 的 預 儲 程 序 清 單 SP_STORED_PROCEDURES 頁 次 :52

53 二 十 三 觸 發 程 序 (Trigger) 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) CREATE TRIGGER trigger_name ON tablename FOR [DELETE, INSERT, UPDATE] AS SET NOCOUNT ON VARCHAR(10) = (SELECT seq_id FROM Inserted) UPDATE TableName2 set counter=counter+1 where seq_id 以 上 就 是 做 : 當 表 格 有 任 何 資 料 插 入, 則 去 另 一 表 格 對 應 欄 位 去 累 加 頁 次 :53

54 二 十 四 資 料 庫 設 計 概 念 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 1. 正 規 化 法 (Normalization) 一 般 在 設 計 資 料 庫 時, 正 規 化 (Normalize) 是 必 經 過 程, 它 可 以 儘 可 能 減 少 資 料 重 覆 問 題 一 旦 消 除 了 大 部 分 的 資 料 重 覆 問 題, 卻 衍 生 出 另 一 個 問 題 : 即 資 料 查 詢 速 度 變 慢! 通 常 正 規 化, 我 們 會 將 資 料 表 由 一 個 細 分 成 數 個 表 格, 若 要 找 出 其 中 一 筆 資 料, 很 可 能 需 要 join 相 關 表 格, 而 join 動 作, 將 直 接 影 響 系 統 效 率, 造 成 查 詢 速 度 變 慢 以 一 步 步 正 規 化 組 織 之 檔 案 資 料 以 設 計 資 料 庫 之 方 法, 將 資 料 庫 的 Table 分 解 為 較 小 的 Table 直 到 每 個 Table 的 每 個 欄 位 都 依 賴 該 Table 的 主 鍵 目 的 : 清 除 不 一 致 性 (Consistency) /* 指 資 料 之 異 動 */ (1) 第 一 正 規 化 (First Normal Form): 一 筆 記 錄 沒 有 重 覆 的 資 料 項 第 一 正 規 化 的 表 格 必 須 合 以 下 條 件 : 1. 必 須 為 row-column 的 二 維 式 table 2. table 的 每 一 筆 資 料 (row) 只 描 述 一 件 事 情 3. 每 一 欄 位 只 含 有 單 一 事 物 的 特 性 ( 欄 位 的 唯 一 性 ) 4. 每 一 筆 row 的 欄 位 內 只 允 許 存 放 單 一 值 5. 每 個 欄 位 名 稱 必 須 是 獨 一 無 二 的 6. 沒 有 任 何 兩 筆 資 料 是 相 同 的 7. row 或 欄 位 的 先 後 順 予 是 無 關 緊 要 的 結 論 : 降 低 重 複 性 (redundancy) /* 指 資 料 之 儲 存 */ (2) 第 二 正 規 化 (Second Normal Form): 已 1NF, 且 記 錄 中 每 筆 資 料 可 由 主 鍵 單 一 辨 視, 但 不 能 由 部 份 主 鍵 來 辨 識 結 論 : 消 除 功 能 相 依 (Functional Dependency) 所 謂 功 能 相 依 是 指 表 格 和 表 格 之 間 的 相 互 關 係, 若 某 個 表 格 中 有 兩 個 欄 位 A 及 B, 當 A 欄 位 值 可 推 導 出 B 欄 位 值, 稱 功 能 相 依 性 即 若 一 關 連 R, 其 屬 性 Y 功 能 相 關 於 屬 性 X, 記 作 R.X R.Y; 若 且 唯 若 R 中 有 二 個 X 值 相 同 時, 其 Y 值 亦 相 同 (3) 第 三 正 規 化 (Third Normal Form): 已 2NF, 且 所 有 和 主 鍵 無 關 之 資 料 項 彼 此 間 獨 立 結 論 : 消 除 遞 移 相 依 (Transitive Dependency) 並 連 結 主 檔 所 謂 遞 移 相 依 是 指 在 一 個 表 格 中, 如 果 某 一 欄 位 值 可 決 定 其 他 欄 位 值, 但 這 些 欄 位 中 又 存 在 某 一 欄 位 可 以 決 定 剩 餘 欄 位 值, 稱 遞 移 相 依 性 若 有 上 述 情 況 存 在, 在 刪 除 資 料 時, 可 能 會 造 成 其 他 資 料 損 毀 若 R.A R.B 且 R.B R.C, 則 R.A R.C 成 立, 此 種 相 關 性 稱 為 遞 移 相 依 頁 次 :54

55 除 此 之 外, 還 有 Boyce-Codd 及 第 四 第 五 種 正 規 化 格 式, 但 實 務 上 不 常 發 生 Boyce-Codd 正 規 化 : 將 多 個 候 選 鍵 中 挑 出 一 個 決 定 因 子 作 為 主 鍵 4NF : 去 除 多 值 相 依 性 5NF : 克 服 合 併 相 依 性 [ 練 習 ] 1. 假 設, 欲 建 立 商 品 銷 售 資 料 庫, 於 需 求 分 析 後 客 戶 購 買 細 目 應 包 含 之 資 料 範 例 摘 錄 如 下 表 所 示 : 客 戶 姓 名 地 址 電 話 客 戶 類 型 說 商 品 商 品 名 商 品 實 際 購 買 購 買 日 期 時 間 編 號 類 型 明 代 碼 稱 定 價 售 價 數 量 A001 張 三 台 北 市 文 N 普 級 11 雞 精 May 2003 山 區 XX 路 維 他 命 Jul 2003 A002 李 四 台 北 市 士 N 普 級 11 雞 精 Jul 2003 林 區 XX 路 12 魚 肝 油 Oct 維 他 命 Oct 雞 精 Nov 2003 A003 王 五 台 北 縣 XX G 金 級 12 魚 肝 油 Jun 200 路 A004 錢 六 新 莊 市 XX V 白 金 級 12 魚 肝 油 Jul 2003 路 維 他 命 Sep 2003 A005 趙 七 台 北 市 景 美 區 XX 街 G 金 級 11 雞 精 Jan 奶 粉 Dec 2003 請 標 示 出 第 一 第 二 第 三 正 規 化 之 結 果 頁 次 :55

56 2. 欲 建 立 圖 書 館 流 通 作 業, 於 使 用 者 需 求 分 析 後 借 閱 資 料 應 包 含 之 資 料 範 例 如 下 表 所 示, 設 計 下 列 訂 單 之 資 料 庫 檔 案 : ( 可 分 別 假 設 還 書 後 是 否 清 除 借 閱 紀 錄 ) 讀 者 讀 者 讀 者 系 所 系 所 說 明 讀 者 類 型 借 閱 資 料 書 目 書 名 作 者 借 閱 日 期 應 還 日 期 編 號 姓 名 系 所 代 碼 ( 全 稱 ) 類 型 說 明 編 號 編 號 ( 登 錄 號 ) A001 張 三 資 傳 IC A002 李 四 會 計 AC 資 訊 傳 播 學 系 會 計 及 國 際 貿 易 學 系 C 大 學 職 場 導 向 張 三 21 May Jul 2002 生 資 料 庫 理 論 李 四 21 May Jul 2002 T 老 師 職 場 導 向 張 三 18 Jul Sep 程 式 設 計 王 五 20 Jul Sep XML 與 錢 六 20 Jul Sep 2002 JAVA 密 碼 學 趙 七 20 Jul Sep 2002 A003 王 五 資 管 IM 資 訊 管 理 U 研 究 程 式 設 計 王 五 05 Jun Jul 2002 學 系 生 A004 錢 六 資 管 IM 資 訊 管 理 學 系 T 老 師 程 式 設 計 王 五 10 Jun Aug 密 碼 學 趙 七 10 Jun Aug 一 美 容 公 司, 希 望 規 劃 一 個 客 戶 資 料 管 理 的 系 統 包 括 記 錄 客 戶 的 姓 名 生 日 地 址 與 性 別 以 及 客 戶 的 子 女 姓 名 生 日 與 性 別 希 望 能 藉 以 管 理 客 戶 在 公 司 的 編 號 消 費 總 金 額 何 時 到 店 內 做 過 何 種 保 養, 以 及 該 次 保 養 的 花 費 與 美 容 師 名 字 請 規 劃 出 此 一 系 統 的 資 料 庫 檔 案 與 欄 位, 並 予 以 正 規 化 後, 畫 出 其 實 體 關 係 圖 ( 假 設 每 次 保 養 只 由 一 位 美 容 師 負 責 ) 頁 次 :56

57 二 十 五 資 料 庫 設 計 分 析 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) 資 料 庫 設 計 是 在 某 特 定 的 使 用 者 環 境 及 應 用 中, 進 行 資 料 庫 結 構 的 設 計 工 作, 以 期 能 滿 足 使 用 者 所 有 應 用 過 程 的 資 訊 需 求 (Batini, et al, 1986) 一 般 而 言, 資 料 庫 設 計 可 分 為 四 個 階 段 : 1. 資 料 需 求 的 規 格 涉 及 到 不 同 使 用 者 及 群 體 有 關 資 訊 需 求 的 確 認 2. 觀 念 設 計 (Conceptual design) 對 使 用 者 及 應 用 系 統 的 資 訊 觀 點 進 行 模 型 的 建 立, 亦 即 建 立 實 體 - 關 係 模 型 (Entity-Relation model, 簡 稱 ER model) 或 UML 的 類 別 關 係, 同 時 亦 包 括 資 料 如 何 處 理 如 何 使 用 的 設 計 3. 邏 輯 設 計 (Logical design) 在 邏 輯 設 計 階 段 中, 係 將 觀 念 架 構 轉 換 成 所 選 定 的 DBMS 之 邏 輯 資 料 模 式 邏 輯 設 計 的 結 果 可 產 生 邏 輯 架 構 (schema), 早 期 可 能 是 關 聯 式 網 狀 式 或 階 層 式 的 邏 輯 架 構 中 的 一 種, 不 過 現 今 主 要 均 是 關 聯 式 資 料 庫 4. 實 體 設 計 (Physical design) 將 邏 輯 資 料 模 式 轉 換 成 某 特 定 硬 體 及 所 選 用 的 DBMS 所 適 用 的 形 式 實 體 設 計 係 決 定 資 料 儲 存 的 結 構 及 檢 索 的 路 徑 實 體 設 計 的 結 果 可 產 生 實 體 架 構, 包 括 儲 存 的 表 示 (representation) 檢 索 路 徑 及 績 效 最 佳 化 的 參 數 值 ( 一 ) 傳 統 方 式 : 1. 系 統 需 求 說 明 : 說 明 軟 硬 體 設 備 與 使 用 環 境 之 需 求 2. 系 統 架 構 圖 : 以 全 觀 (overall) 的 方 式 描 述 系 統 整 體 3. 環 境 圖 : 環 境 圖 是 定 義 與 系 統 有 關 的 外 部 實 體 及 系 統 與 這 些 外 部 實 體 的 介 面 4. 資 料 流 程 圖 (Data Flow Diagram, DFD): 資 料 流 程 圖 是 結 構 化 資 訊 系 統 分 析 的 主 要 工 具, 將 一 組 處 理 或 程 序 的 邏 輯 資 料 流 程 記 錄 成 文 件, 包 括 資 料 的 外 部 來 源 和 目 的 地 轉 換 資 料 的 活 動, 以 及 保 留 資 料 的 儲 存 區 或 集 合 它 表 達 出 使 用 者 的 功 能 處 理 需 求, 以 及 功 能 處 理 項 目 間 資 料 的 流 出 與 流 入 系 統 資 料 的 邏 輯 轉 換 功 能 因 此, 從 資 料 流 程 圖 中, 可 以 了 解 各 項 外 部 實 體 的 資 料 流 通 介 面, 並 且 知 道 需 要 那 些 資 料 儲 存 處 可 用 來 儲 存 資 料, 以 支 援 處 理 過 程 所 需 的 資 料 或 所 產 生 的 資 料 資 料 流 程 圖 (Data Flow Diagram,DFD) 主 要 有 二 種 目 的 : (1) 顯 示 資 料 在 系 統 中 的 流 向,(2) 描 述 處 理 資 料 流 程 的 功 能 項 目 通 常 我 們 用 資 料 流 來 表 示 程 式 中 各 個 敘 述 之 間 所 傳 遞 的 訊 息, 資 料 流 程 圖 則 是 將 這 個 傳 遞 的 關 係 以 圖 形 來 表 示, 通 常 它 以 類 似 網 路 的 結 構 來 表 示 一 個 系 統, 頁 次 :57

58 DFD 包 含 了 四 個 項 目 : (1) 資 料 流 : 以 箭 頭 來 表 示 (2) 處 理 單 元 : 以 圓 圈 來 表 示 (3) 檔 案 : 以 直 線 三 邊 方 框 來 表 示 資 料 庫 系 統 授 課 講 義 ( 世 新 資 傳 系 Seljuk) (4) 外 部 實 體 : 資 料 來 源 和 去 處, 以 矩 形 來 表 示 EN_TOPIC 選 擇 檢 索 項 輸 入 查 詢 內 容 依 據 EN_INDEX 判 斷 需 查 詢 的 索 引 點 AUTHORITY EN_INDEX 判 斷 是 否 符 合 AUTHORITY 檔 使 用 Widecard 不 處 理 關 鍵 字 查 詢 Y ( 完 全 符 合 查 詢 ) N ( 關 鍵 詞 查 詢 ) 顯 示 結 果 [ 條 列 式 ] 選 擇 顯 示 格 式 PARAM_SYNONYM EN_FORMAT EN_DISPLAY 取 同 義 詞 查 詢 METAKWD EN_LIST 反 正 規 檔 ( 加 快 顯 示 速 度 ) 有 找 到 資 料 沒 找 到 資 料 顯 示 結 果 [ 內 容 ] METAKWD_MRN 顯 示 無 符 合 的 資 料 選 擇 確 認 顯 示 結 果 傳 回 MRN 供 呼 叫 之 程 式 處 理 5. 資 料 字 典 : 使 用 在 資 料 庫 上 描 述 資 料 庫 結 構 與 表 格 欄 位 的 名 稱 內 容 與 格 式 等 資 料 的 定 義 系 統 名 稱 : 線 上 考 試 與 查 詢 系 統 日 期 :2001 Feb 02 檔 案 名 稱 : 使 用 者 基 本 資 料 檔 檔 案 組 織 : 索 引 循 序 檔 資 料 元 素 名 稱 中 文 英 文 帳 號 Sid 文 字 7 密 碼 PW 文 字 8 姓 名 Sname 文 字 10 性 別 Sex 文 字 2 主 鍵 值 : 帳 號 項 次 :1/6 型 態 長 度 小 數 點 備 註 頁 次 :58

59 E_mail 文 字 30 電 話 Tel 數 字 14 住 址 Address 文 字 60 年 級 Grade 文 字 2 班 級 Class 文 字 2 6 實 體 關 係 圖 (Entity-relationship diagram, ERD) 描 述 資 料 物 件 之 間 的 關 係 在 ERD 中 使 用 data object description 描 述 各 資 料 物 件 的 屬 性 ERD- 使 用 圖 形 方 式 標 示 資 料 物 件 之 間 的 關 係 (1) 資 料 物 件 軟 體 必 須 了 解 的 任 何 綜 合 資 訊 (2) 屬 性 資 料 物 件 的 特 性, 通 常 具 有 三 種 特 性 (a) 資 料 物 件 實 體 的 名 稱 實 體 的 描 述 與 其 他 表 格 中 實 體 的 關 聯 性 (b) 必 須 使 用 一 個 或 一 個 以 上 的 屬 性 用 來 標 示 此 一 資 料 物 件 的 實 體 (c) 關 係 資 料 物 件 之 間 的 結 合 關 係 (3) 資 料 物 間 之 間 使 用 的 兩 個 標 示 (Martin/Odell) (a)cardinality 數 集 是 列 舉 出 一 個 物 件 與 另 一 物 件 間 相 關 的 最 大 數 量 1 對 1(1:1) 1 對 多 (1:N) 多 對 多 (M:N) (b)modality 必 備 關 係, 當 物 件 之 間 並 無 關 係 存 在 或 關 係 並 非 強 制 性 時, 其 關 係 的 modality 為 零 (zero) Cardinality Modality 1..N 0..N (4) 圖 示 : 頁 次 :59

60 (5) 範 例 ( 二 ) 標 準 方 式 : 統 一 塑 模 語 言 (Unified Modeling Language, UML) UML 是 由 三 位 世 界 級 物 件 技 術 大 師,James Rumbaugh Grady Booch 和 提 出 OOSE (Object-Oriented Software Engineering) 方 法 的 Ivar Jacobson 所 其 同 研 究 開 發 出 來 的 物 件 導 向 分 析 與 設 計 的 標 準 語 言 過 去 由 於 軟 體 業 界 沒 有 一 個 共 通 的 物 件 導 向 分 析 與 設 計 的 標 準, 因 此 不 同 的 開 發 者 及 使 用 者 間, 要 進 行 溝 通 是 一 件 很 困 難 的 事 情 UML, 就 是 為 了 要 把 軟 體 開 發 初 期 所 進 行 的 物 件 導 向 分 析 與 設 計, 用 一 套 大 家 皆 遵 循 的 標 準 化 語 言 來 開 立 規 格, 並 且 利 用 圖 示 法 (Notation) 表 達 來 建 立 及 保 存 一 個 完 善 的 文 件 紀 錄 因 此, 將 開 發 軟 體 系 統 初 期 所 必 須 進 行 的 物 件 分 析 與 設 計, 用 一 套 標 準 化 的 使 用 UML 來 建 立 規 格, 並 且 利 用 圖 示 法 表 達 來 建 立 架 構 和 做 文 件 紀 錄, 運 用 以 元 件 為 基 礎 的 物 件 導 向 技 術 來 開 發 軟 體 UML 總 共 從 五 種 觀 點 定 義 了 九 種 不 同 的 圖 形, 分 別 是 使 用 者 觀 點 的 使 用 者 案 例 圖 (Use Case Diagram); 結 構 觀 點 的 類 別 圖 (Class Diagram) 物 件 圖 (Object Diagram); 行 為 觀 點 的 循 序 圖 (Sequence Diagram) 合 作 圖 (Collaboration Diagram) 狀 態 圖 (State Diagram) 活 動 圖 (Activity Diagram); 實 作 觀 點 的 元 件 圖 (Component Diagram) 以 及 環 境 觀 點 的 部 署 圖 (Deployment Diagram) 以 便 從 各 種 不 同 的 角 度 將 概 念 透 過 符 號 表 示, 並 將 概 念 間 的 相 互 關 係 藉 由 符 號 的 路 徑 來 描 繪 出 整 個 系 統 頁 次 :60

61 以 類 別 圖 (Class diagram) 表 示 學 校 學 生 老 師 系 所 課 程 之 關 係 圖 如 下 : 在 軟 體 系 統 裡 面, 透 過 程 式 語 言 可 以 將 各 種 非 軟 體 的 概 念 性 事 務 與 程 式 設 計 作 對 照, 像 客 戶 交 易 或 是 對 話 等 1. 類 別 2. 關 係 (Relationship) (1) 相 依 關 係 (dependency) 頁 次 :61

62 Dependency 是 一 種 使 用 關 係, 它 代 表 某 一 事 物 (event 類 別 ) 規 格 的 改 變 有 可 能 會 影 響 到 另 一 個 使 用 該 事 物 的 事 物 (Window 類 別 ) 規 格 但 反 過 來 則 不 見 的 成 立 當 需 要 表 現 某 一 個 事 物 使 用 另 一 個 事 物 時, 就 可 以 利 用 dependency 來 表 現 (2) 一 般 化 關 係 (generalization) generalization 是 一 般 性 事 物 ( 父 類 別 ) 和 其 特 殊 性 事 物 ( 子 類 別 ) 之 間 的 關 係 每 一 個 類 別 均 可 以 具 有 零 個 一 個 或 多 個 父 類 別 只 要 是 沒 有 父 類 別 但 具 有 一 個 或 多 個 子 類 別 的 類 別 就 稱 之 為 根 類 別 (root class) 或 基 底 類 別 (base class); 而 沒 有 子 類 別 的 類 別 稱 為 葉 類 別 (leaf class) 而 只 有 單 一 父 類 別 的 類 別, 稱 此 兩 類 別 之 關 係 為 單 一 繼 承 (single inheritance); 而 有 多 個 父 類 別 的 繼 承 則 稱 為 多 重 繼 承 (multiple inheritance) (3) 結 合 關 係 (association) Association 是 一 種 結 構 關 係, 它 可 以 訂 定 某 一 種 事 物 的 物 件 是 如 何 與 另 一 種 事 物 的 物 件 之 間 互 相 連 接 的 名 稱 (name) 每 一 種 結 合 關 係 都 可 以 具 有 名 稱, 透 過 此 一 名 稱 可 以 描 述 這 種 結 合 的 本 質, 亦 可 使 此 一 結 合 關 係 比 較 清 楚 角 色 (role) 頁 次 :62

63 當 某 一 個 類 別 參 與 結 合 關 係 時, 它 就 會 在 此 結 合 關 係 理 扮 演 某 種 角 色 角 色 所 代 表 的 其 實 就 是 在 結 合 關 係 一 端 的 類 別 對 另 一 端 類 別 所 呈 現 出 來 的 一 面 如 圖 Person 和 Company 在 此 一 結 合 關 係 裡, 分 別 扮 演 了 員 工 (employee) 和 雇 主 (employeeloyer) 的 角 色 多 重 性 / 數 集 (multiplicity) 結 合 關 係 代 表 了 物 件 之 間 的 結 構 關 係, 有 時 我 們 進 行 塑 模 時 必 須 要 描 述 出 有 幾 個 物 件 慧 根 每 一 個 實 例 相 結 合, 其 中 有 幾 個 物 件 就 是 結 合 關 係 裡 的 multiplicity, 這 種 多 重 性 可 以 使 用 單 一 值 或 某 一 區 間 值 來 表 示 當 在 association 某 一 端 指 定 該 物 件 的 multiplicity 時, 同 樣 地 也 必 須 指 定 另 一 端 會 有 幾 個 物 件 與 之 對 應 指 定 的 關 係 可 以 是 唯 一 (1) 零 到 一 (0..1) 零 到 多 (0..*) 一 到 多 (1..*), 甚 至 固 定 一 個 數, 例 如 3 聚 合 關 係 (aggregation) 一 般 的 結 合 關 係 是 由 兩 個 類 別 所 組 成, 且 兩 者 之 間 在 概 念 上 是 屬 於 同 一 層 級 的, 不 會 有 哪 一 個 類 別 比 另 一 個 重 要 但 有 時 會 有 需 要 塑 造 整 體 和 部 分 的 關 係, 在 這 種 關 係 裡 面 會 有 一 個 類 別 是 較 大 的 事 物 ( 整 體 ), 這 個 事 物 是 由 較 小 的 事 物 ( 部 分 ) 所 構 成 的, 而 這 種 關 係 稱 之 為 aggregation, 所 代 表 的 是 一 種 包 含 關 係 (has-a relationship) 頁 次 :63

64 [ 練 習 ] 1. 設 計 下 列 之 資 料 庫 檔 案 : 因 應 SARS, 政 府 規 定 機 場 需 建 立 各 航 班 旅 客 資 料, 建 立 內 容 如 下 : [ 其 中 國 別 地 點 需 自 行 另 建 立 代 碼 檔 ] 航 班 到 港 旅 客 資 訊 紀 錄 表 航 班 資 料 航 空 公 航 空 公 公 司 國 別 航 班 編 號 起 始 地 點 到 達 地 點 飛 機 機 型 機 型 名 稱 最 大 載 客 量 抵 達 日 期 司 代 碼 司 名 稱 CN 中 華 航 twn CN520 MACAU TAIPE B747 波 音 廣 體 /5/10 空 747 乘 客 資 料 # 護 照 號 碼 姓 名 國 別 性 別 出 生 年 月 日 1. M Brown twn M 1955/07/03 2. M Mary twn F 1960/04/21 3. M John jpn M 1970/02/28 [ 假 設 每 航 班 每 日 到 港 最 多 一 次 ] 請 : 1. 分 別 標 示 出 第 一 第 二 第 三 正 規 化 之 結 果 2. 依 據 UML 之 類 別 圖 繪 出 其 檔 案 結 構 3. 以 SQL 宣 告 檔 案 (tables) 之 建 立, 並 宣 告 所 需 建 立 之 主 鍵 外 來 鍵 與 索 引 檔 (index tables) 4. 請 將 範 例 之 資 料 加 入 你 所 建 立 的 檔 案 內 ( 每 一 個 table 各 輸 入 一 筆 記 錄 即 可 ) 5. 依 範 例 輸 出 之 內 容 建 立 一 個 View 檔 6. 刪 除 2003/5/1~2003/5/30 所 有 航 班 的 乘 客 資 料 ( 注 意 有 無 on delete cascade, 不 要 多 刪, 也 不 要 少 刪 ) 頁 次 :64

65 頁 次 :65