Database講義.docx

Transcription

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

4 四 資 料 庫 系 統 模 型 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 模 型 是 系 統 或 狀 態 之 完 整 抽 象 概 念 依 據 資 料 庫 系 統 的 演 進 和 結 構 的 差 異, 主 要 分 為 檔 案 式 階 層 式 網 路 式 和 關 聯 式 資 料 庫 模 型 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 五 關 聯 式 資 料 模 型 的 資 料 結 構 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 關 聯 式 名 詞 關 聯 表 (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 六 關 連 式 資 料 庫 系 統 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 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 八 資 料 庫 系 統 處 理 架 構 之 演 進 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 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 公 司 發 展 的 資 料 庫 系 統 ( ( 註 :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 十 五 欄 位 屬 性 ( 資 料 型 態 ) 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 建 立 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 型 態 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 此 型 態 用 來 儲 存 日 期 - 時 間 資 料 顯 示 時 內 定 的 顯 示 格 式 為 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 個 位 元 組 的 資 料, 但 實 際 上 必 須 視 硬 碟 大 小 而 定 8. DECIMAL 型 態 頁 次 :18

19 對 於 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) 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 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; (3) JOIN 使 用 別 名 的 技 巧 : 為 了 避 免 冗 長 的 table 名 稱, 我 們 可 以 定 義 table 的 別 名 (Alias), 這 table 的 別 名 是 使 用 較 短 名 頁 次 :21

22 稱 取 代 原 table 名 稱 例 如 : 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 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): 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 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 運 算 子 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 1. 算 術 運 算 子 : 運 算 子 說 明 範 例 + - 單 元 運 算 子, 表 示 一 個 正 或 負 的 運 算 式 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 >= 運 算 子 只 要 有 任 一 個 成 立, 即 為 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 表 示 子 查 詢 之 結 果 ] [NOT] BETWEEN X [ 不 ] 存 在 X 和 Y 區 間 的 判 斷 SELECT * FROM employee WHERE salary BETWEEN AND AND Y EXISTS 子 查 詢 有 獲 得 任 意 數 目 的 資 料 集, 則 傳 回 True SELECT * FROM dept WHERE EXISTS (SELECT * FROM employee WHERE dept.id=employee.deptno) X [NOT] LIKE Y 切 截 查 詢, 在 Y 中 : 符 號 % 可 SELECT * FROM employee WHERE 包 含 任 何 0 個 或 多 個 字 元 ; _ firstname LIKE JA% 可 包 含 任 何 單 一 字 元 IS [NOT ] NULL 測 試 空 值 SELECT * FROM employee WHERE comm IS NULL [ 說 明 ] > ALL(1,2,3) > 3 > 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 查 詢 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 檔 案 結 構 : (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. 合 併 查 詢 (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 4. 條 件 (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) 頁 次 :28

29 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 ( 台 北 市, 台 南 市, 台 中 市 ) 5. 排 序 查 詢 (ORDER BY) DESC 由 大 至 小 排 序 ASC 由 小 至 大 排 序 ( 預 設 ) SELECT ID, NAME, RANK, CITY FROM BOOKSTORE ORDER BY ID DESC SELECT * FROM BOOK ORDER BY AUTHOR ASC, BRN DESC 6. 聚 合 查 詢 五 項 基 本 函 數 : 函 數 名 稱 說 明 COUNT 計 算 指 定 欄 位 之 資 料 集 數 目 SUM 計 算 欄 位 內 容 之 總 合 AVG 計 算 欄 位 內 容 之 平 均 值 MIN 計 算 欄 位 內 容 之 最 小 值 MAX 計 算 欄 位 內 容 之 最 大 值 計 算 學 生 檔 (STUDENT) 的 人 數 計 算 科 目 CO 所 有 修 課 的 人 數, 總 分, 平 均 成 績, 最 高 分, 最 低 分 計 算 學 生 王 五 的 所 有 修 課 數 目, 總 分, 平 均 成 績, 最 高 分, 最 低 分 7. 巢 狀 查 詢 (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 頁 次 :29

30 * 使 用 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) 列 出 薪 水 比 部 門 編 號 開 頭 為 2 的 員 工 平 均 薪 水 還 高 的 其 他 部 門 員 工 資 料 SELECT * FROM EMPLOYEE WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPTNO LIKE '2%') AND DEPTNO NOT LIKE '2%' 頁 次 :30

31 列 出 與 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 8. 群 組 功 能 (GROUP BY) 求 訂 單 檔 中 各 書 被 訂 購 的 次 數 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 分 組 之 不 足 之 處, 例 如 以 姓 名 分 組 求 學 生 中 男 生 女 生 的 人 數 SELECT SEX, COUNT(*) FROM STUDENT GROUP BY SEX 求 各 訂 購 商 訂 購 書 的 總 數, 列 出 時 請 列 出 訂 購 商 名 稱 SELECT ORDERS.VID, BOOKSTORE.NAME, SUM(QUANTITY) FROM ORDERS, BOOKSTORE WHERE ORDERS.VID=BOOKSTORE.ID GROUP BY ORDERS.VID, BOOKSTORE.NAME 頁 次 :31

32 9. 群 組 條 件 (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 分 組 的 依 據 (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) 頁 次 :32

33 (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) 列 出 部 門 平 均 薪 水 超 過 的 員 工 資 料 10. SQL Server 存 取 Unicode 字 碼 雖 然 SQL Server 已 經 支 援 Unicode, 資 料 處 理 的 模 式 亦 是 依 據 字 元 而 非 位 元 組, 不 過 實 際 儲 存 資 料 是 若 非 指 名 使 用 Unicode, 系 統 預 設 仍 是 以 資 料 一 般 的 字 碼 處 理 ( 例 如 台 灣 就 會 以 Big5 字 碼 為 預 設 字 元 ) 參 考 下 列 範 例, 先 建 立 一 個 包 含 兩 個 欄 位 的 表 格, 其 中 NOTE 欄 位 宣 告 為 變 長 字 串, 長 度 8: 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 字 串 或 二 進 位 資 料 會 被 截 斷 陳 述 式 已 經 結 束 表 示 已 經 超 過 允 許 長 度 若 是 以 函 數 LEN( ) 檢 查 資 料 內 容, 系 統 能 夠 很 正 確 地 依 據 字 元 數 目 計 算 出 : SELECT NOTE,LEN(NOTE) FROM TEMP 得 到 下 列 結 果 : 頁 次 :33

34 INSERT INTO TEMP VALUES ('001',' 三 個 字 '); INSERT INTO TEMP VALUES ('002',' 四 個 words'); INSERT INTO TEMP VALUES ('003',' 這 有 五 個 字 '); SQL Server 如 何 存 取 Unicode 字 碼 的 方 法 : 1. 宣 告 的 欄 位 名 稱 必 須 使 用 'n', 如 nchar, nvarchar 2. 存 入 資 料 時 必 須 在 資 料 前 加 上 一 個 N 字 元 ( 必 須 大 寫 ) 例 如 : CREATE TABLE TEMP (sql integer, id nchar(5), name nvarchar(10), title varchar(10)) INSERT INT TEMP VALUES (1, ' 山 东 ',' 包 兆 茏 ',' 经 理 ') INSERT INT TEMP VALUES (2, N' 山 东 ',N' 包 兆 茏 ',N' 经 理 ') INSERT INT TEMP VALUES (3, ' 山 東 ', N' 包 兆 茏 图 书 馆 ',N' 經 理 ') 东 包 包 select * from Temp 的 結 果 如 下 : 山? 包 兆?? 理 2 山 兆 茏? 理 3 山 東 兆 茏 图 书 馆 經 理 可 以 看 出 輸 入 資 料 時 在 前 面 加 一 N 字 元, 就 算 是 該 欄 位 為 非 Unicode 亦 可 ( 如 範 例 中 的 title 欄 位, 並 非 宣 告 為 nvarchar) 縱 使 欄 位 宣 告 為 Unicode( nchar 或 nvarchar), 若 沒 在 字 串 前 加 上 'N' 字 元, 則 一 樣 不 會 存 入 Unicode 頁 次 :34

35 練 習 : ( 請 先 自 行 繪 出 STUDENT, COURSE, SUBJECT, TEACHER 四 個 表 格 的 檔 案 結 構 ) 1. 連 續 題 : (1) 列 出 各 科 修 課 人 數 (2) 列 出 學 生 的 修 課 科 目 數 量 平 均 成 績 (3) 列 出 修 課 人 數 超 過 3 人 的 修 課 科 目 代 碼 (4) 列 出 修 課 人 數 超 過 3 人 的 修 課 科 目 代 碼 學 生 姓 名 及 成 績 (5) 列 出 修 課 人 數 超 過 3 人 的 修 課 科 目 名 稱 學 生 姓 名 及 成 績 (6) 列 出 修 課 人 數 超 過 3 人 的 修 課 科 目 名 稱 學 生 姓 名 及 成 績, 並 依 科 目 名 稱 成 績 學 號 排 序 2. 列 出 學 生 平 均 成 績 低 於 80 分 的 學 生 學 號 姓 名 平 均 成 績 3. 列 出 科 目 平 均 成 績 高 於 75 分 的 科 目 名 稱 授 課 老 師 姓 名 平 均 分 數 4. 列 出 修 課 平 均 成 績 比 平 均 成 績 還 高 的 學 生 學 號 及 其 平 均 成 績 5. 列 出 修 課 男 生 女 生 的 平 均 成 績 6. 求 資 料 庫 分 數 最 高 學 生 的 所 有 科 目 成 績 7. 列 出 老 師 的 開 課 數 量 8. 列 出 老 師 的 姓 名 開 課 科 目 數 量 9. 列 出 老 師 的 姓 名 開 課 科 目 名 稱 各 科 目 修 課 學 生 人 數 修 課 的 平 均 成 績 頁 次 :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 的 巢 狀 元 素, 各 欄 位 內 容 以 屬 性 方 式 表 示 (4) EXPLICIT: 傳 回 應 建 立 之 XML 樹 狀 形 式 (5) XMLData: 指 定 將 查 詢 結 果 產 生 之 XML 內 包 含 其 DTD (6) ELEMENTS: 指 定 將 查 詢 結 果 之 各 欄 位 以 元 素 型 態 傳 回 (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 RIGHT(str_expr, int_expr) LEFT(str_expr, int_expr) 字 串 由 start 處 插 入 從 字 串 的 最 右 邊 往 回 取 int_expr 個 字 元 從 字 串 的 最 左 邊 位 置 取 int_expr 個 字 元 RIGHT( abcdefgh,5) 結 果 為 defgh LEFT( abcdefg,5) 結 果 為 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 本 單 元 介 紹 流 程 : 建 立 檔 案 / 刪 除 檔 案 新 增 / 修 改 / 刪 除 紀 錄 (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 ) 建 立 檔 案 之 前, 先 掌 握 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 檔 案 名 稱 ( 欄 位 名 稱 資 料 型 態 [ 限 制 ], ) 欄 位 名 稱 資 料 型 態 [ 限 制 ], [ 主 鍵 宣 告,] [ 外 來 鍵 宣 告 ] 常 宣 告 的 限 制 (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 2. 檔 案 修 改 ALTER TABLE 檔 名 修 改 類 型 欄 位 [ 範 例 ] alter table department add position varchar(20) // 新 增 一 個 欄 位 的 定 義 alter table department drop column position // 刪 除 一 個 欄 位 的 定 義 alter table department alter column position varchar(30) // 修 改 一 個 欄 位 的 定 義 3. 檔 案 刪 除 DROP TABLE tablename [ 練 習 ] 檔 案 建 立 及 建 立 的 先 後 順 序 (1) 建 立 27 頁 之 相 關 檔 案, 建 立 時 包 含 宣 告 Primary key, Foreign key, 並 考 慮 建 立 所 須 之 索 引 檔 (2) 逐 一 刪 除 檔 案 注 意 Foreign key 所 造 成 的 刪 除 次 序 限 制 頁 次 :43

44 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 語 句 CREATE VIEW 視 界 檔 名 ( 欄 位, 欄 位, ) AS SELECT 欄 位, 欄 位, FROM 檔 案, 檔 案, WHERE 視 界 檔 案 的 資 料 來 源, 且 視 界 檔 案 的 欄 位 必 須 與 SELECT 輸 出 結 果 的 欄 位 數 量 一 致 當 SELECT 輸 出 結 果 的 欄 位 名 稱 沒 有 重 複 且 均 有 名 稱 時, 可 以 省 略 視 界 檔 案 的 欄 位 宣 告, 沿 用 原 先 的 欄 位 名 稱 練 習 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 情 況 之 資 料 異 動 結 果 頁 次 :44

45 (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) 頁 次 :45

46 二 十 一 索 引 (Index) 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 對 關 連 式 資 料 庫,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 頁 次 :46

47 從 左 至 右 的 順 序 表 示 索 引 主 要 到 次 要 的 順 序 2. 刪 除 索 引 檔 DROP INDEX table.index 註 : 索 引 檔 案 資 訊 記 錄 於 系 統 檔 SYSINDEXES 內, 可 以 透 過 預 儲 程 序 SP_HELPINDEX 獲 知 特 定 某 一 個 表 格 的 索 引 檔 頁 次 :47

48 二 十 二 預 儲 程 序 (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 gettitle varchar(50) select * from bib where title=@tit 此 範 例 建 立 一 個 叫 做 gettitle 的 預 儲 程 序, 當 中 包 含 了 一 個 輸 入 參 數 當 gettitle 執 行 時, 它 會 傳 回 一 個 結 果 集 (resultset), 當 中 包 含 了 所 有 在 Bib 檔 案 中,Title 資 料 行 的 值 對 於 輸 入 參 數 的 所 有 紀 錄 預 儲 程 序 比 較 不 一 樣 的 地 方 是 它 們 的 實 際 設 計 預 儲 程 序 事 實 上 是 用 來 建 立 預 儲 程 序 的 Transact-SQL 敘 述 在 其 他 的 程 式 員 中, 程 序 只 是 列 出 所 有 的 動 作, 而 不 會 建 立 任 何 東 西 在 SQL Server 中, 預 儲 程 序 實 際 上 建 立 了 他 們 本 身 2. 刪 除 預 儲 程 序 Drop Proc gettitle 頁 次 :48

49 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 頁 次 :49

50 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' 上 述 這 個 預 儲 程 序 範 例 在 執 行 時, 需 要 兩 個 參 數 : 一 個 是 日 期, 一 個 是 字 串, 所 以 我 們 必 須 在 參 數 內 容 的 前 後 加 上 單 引 號 Trigger 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 以 上 就 是 做 : 當 表 格 有 任 何 資 料 插 入, 則 去 另 一 表 格 對 應 欄 位 去 累 加 頁 次 :50

51 二 十 三 資 料 庫 設 計 概 念 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 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 成 立, 此 種 相 關 性 稱 為 遞 移 相 關 頁 次 :51

52 除 此 之 外, 還 有 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 請 標 示 出 第 一 第 二 第 三 正 規 化 之 結 果 頁 次 :52

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

54 二 十 四 資 料 庫 設 計 分 析 資 料 庫 系 統 授 課 講 義 (Shien-chiang Yu) 資 料 庫 設 計 是 在 某 特 定 的 使 用 者 環 境 及 應 用 中, 進 行 資 料 庫 結 構 的 設 計 工 作, 以 期 能 滿 足 使 用 者 所 有 應 用 過 程 的 資 訊 需 求 (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) 描 述 處 理 資 料 流 程 的 功 能 項 目 通 常 我 們 用 資 料 流 來 表 示 程 式 中 各 個 敘 述 之 間 所 傳 遞 的 訊 息, 資 料 流 程 圖 則 是 將 這 個 傳 遞 的 關 係 以 圖 形 來 表 示, 通 常 它 以 類 似 網 路 的 結 構 來 表 示 一 個 系 統, 頁 次 :54

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

56 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) 圖 示 : (5) 範 例 ( 另 行 補 充 ) 頁 次 :56

57 ( 二 ) 標 準 方 式 : 統 一 塑 模 語 言 (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) 以 便 從 各 種 不 同 的 角 度 將 概 念 透 過 符 號 表 示, 並 將 概 念 間 的 相 互 關 係 藉 由 符 號 的 路 徑 來 描 繪 出 整 個 系 統 頁 次 :57

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

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

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

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

62 頁 次 :62

63 二 十 五 資 料 庫 應 用 ( 一 ) 前 言 前 端 應 用 程 式 與 後 端 資 料 庫 系 統 之 間 的 溝 通 : 1. SQL 指 令 與 資 料 庫 系 統 之 間 的 溝 通 2. 應 用 程 式 與 異 質 資 料 庫 系 統 之 間 的 溝 通 3. 應 用 程 式 與 異 質 資 料 庫 系 統 之 間 的 開 放 式 介 面 頁 次 :63

64 資料庫系統授課講義 (Shien-chiang Yu) SQL 標準的制定組織同時也負責制定開放式資料庫呼叫介面: X/Open SQL/CLI (Call Level Interface) 除了相容於 SQL92 標準之外 也是一個獨立於實作方法之外的 Client/Server 架構 目前主要的資料庫呼叫介面都遵從 X/OpenSQL/CLI 標準 最早在資料庫市場獲得成功 應用的呼叫介面為微軟公司於 1994 年提出的 ODBC(Open DatabaseConnectivity)產品 ODBC 提供應用程式透過 ODBC 函式庫(Library)操作資料庫的各項功能 如建立連線 處理資料集回 傳等 卻不需要處理資料庫系統之間的差異 因為 ODBC 內部的驅動程式管理員會根據 ODBC 設定 呼叫對應的資料庫驅動程式 初期只有微軟自己的資料庫產品使用 ODBC 驅動程式 且也只限於 Windows 作業系統平 台才能使用 但因為 ODBC 遵從 X/Open SQL/CLI 標準 加上微軟的資料庫產品與開發環境與 工具持續增加 其他資料庫廠商亦開始提供相關支援 ODBC 的驅動程式 其實 X/Open SQL/CLI 是依據微軟 ODBC 的提案而制訂出來的標準 因此所有符合 X/Open SQL/CLI 標準的 呼叫介面 都會跟 ODBC 類似 (二) ODBC 設定說明 1. 建立 ODBC 來源參數 2. 開始 設定 控制台 系統管理工具 選擇 資料來源(ODBC) 啟動 ODBC 資料來源管理員 頁次 64

65 資料庫系統授課講義 (Shien-chiang Yu) 3. 於 使用者資料來源名稱 頁籤 按下 新增 鈕 顯示 建立新資料來源 視窗 於該視 窗中選擇 SQLServer 說明 系統資料來源名稱 使用者資料來源名稱 兩者差別之處為 1. 系統資料來源名稱 表示所有的使用者都可以使用這個 DSN Data Source Name 2. 使用者資料來源 只在區域電腦中 並且只有目前登入此作業系統的使用者可以使用 這個 DSN 所以要建在 系統資料來源名稱 或是 使用者資料來源名稱 可依實際需要決定 4. 於接續之 建立新的資料來源至 SQLServer 視窗 逐一輸入相關設定 (1) 名稱 此一 ODBC 連結之名稱 提供程式中指定使用 (2) 伺服器 資料庫系統所在的伺服器電腦名稱或 IP 位址 (3) 識別碼 可選擇使用 NT 帳號登入或資料庫帳號登入 此為程式中連結資料庫時使用 (4) 用戶端設定 指定網路協定之種類 頁次 65

66 資料庫系統授課講義 (Shien-chiang Yu) (5) 預設資料庫 使用此 ODBC 參連接資料庫系統時 預設連結的資料庫名稱 5. 完成設定後 可選擇 測試資料來源 檢測能否正連結至所設定之資料庫 頁次 66

67 資料庫系統授課講義 (Shien-chiang Yu) (三) JDBC 套件介紹 JDBC(Java Database Connectivity)用於 Java 平台上連結資料庫的技術 其包含資料庫驅動程 式管理 資料庫連線 連線程序管理 SQL 指令傳遞 資料庫紀錄回傳 資料庫異動管理 資 料型別轉換等功能 JDBC 3.0 版之後的 API 包含兩個主要的資料庫套件 java.sql, javax.sql java.sql 是 JDBC 的基礎套件 包含有完整連結資料庫的功能 javax.sql 在 JDBC 2.0 版時為 選擇性的套件 但在 JDBC 3.0 版之後則為預設的套件 其主要包含處理伺服器端的連線與資 料物件 常擔任中介介面的角色 1. Java.sql 套件 Java.sql 提供資料庫的類別或介面 可分為連線 資料集 SQL 指令 例外處理 後設資料等 (1) 連線 元件名稱 類型 說明 DriverManager 類別 驅動程式管理員 負責管理 JDBC 驅動程式以建立 連線 Driver 介面 由廠商提供的 JDBC 驅動程式 SQLPermission 類別 用來限制 DriverManager 讀寫 log 的動作 DriverPropertyInfo 類別 介面 Connection 搭配 Driver 介面提供驅動程式連線時所需的設定 管理程式與資料庫系統之間建立的邏輯連線 介面 Savepoint 提供方法已取得 Connection 所設定的儲存點 (2) 資料集 元件名稱 類型 說明 ResultSet 介面 接收資料庫回傳的資料 並提供可以取用其資料的方法 (3) SQL 指令 元件名稱 類型 說明 Statement 介面 執行靜態的 SQL 敘述 PreparedStatement 介面 與 Statement 元件相同 但增加可處理動態 SQL 敘述的功能 CallableStatement 介面 呼叫資料庫預儲程序 (4) 例外處理 元件名稱 類型 說明 SQLException 類別 資料庫發生存錯誤時 會引發此物件 SQLWarning 類別 Connection, Statement 或 ResultSet 物件發生資 料庫存取警告時 會引發此物件 頁次 67

68 資料庫系統授課講義 (Shien-chiang Yu) 類別 DataTruncation JDBC 在進行資料讀寫時 發生資料欄位型態轉 換錯誤 會引發此物件 BatchUpdateException 類別 批次更新的過程發生錯誤 會引發此物件 (5) 後設資料 後設資料(metadata)描述資料庫的綱要 如資料庫支援何種標準 交易進行方式 資料庫定義 與限制條件等 元件名稱 類型 說明 DatabaseMetaData 介面 提供資料庫與 JDBC 驅動程式的 metadata ResultSetMetaData 介面 提供 ResultSet 元件內資料集之欄位定義 ParameterMetaData 介面 提供 PreparedStatement 元件及 CallableStatement 元件內 SQL 敘述之參數的 metadata 2. Javax.sql 套件 Javax.sql 套件依其功能可分為資料源(Data source) 連線池(Connection pool) 分散式交易 (Distributed transaction) 資料列集(Row set)四大類元件 (1) 資料源 元件名稱 類型 說明 DataSource 介面 可註冊於遠端的物件 具備資料來源的設定 負責 傳回連線物件提供用戶端使用 資料源元件顧名思義 就是提供資料來源的元件 在 javax.sql 套件中 可以不需透過 JDBC 驅 動程式管理者建立的資料庫連線取得資料 而可以透過事先設定好的資料源取得資料 因此 Java 資料庫應用程式可以選擇透過 java.sql 套件的連線元件 DriverManager 與資料庫連線也 可以透過 DataSource 資料源元件完成相同的工作 (2) 連線池 元件名稱 類型 ConnectionPoolDataSource 介面 介面 PooledConnection 說明 連線池所用的資料來源 連線池內所產生的連結物件 負責提供用戶 端與資料庫系統之間的連線 ConnectionEvent 類別 用戶端連線中斷時 PooledConnection 返回 給連線池管理的事件 可經由該事件取得 SQL 例外物件 ConnectionEventListener 介面 註冊此監聽介面的物件 可以收到連線異常 (主要發生在連線斷線時)的通知 (3) 分散式交易 元件名稱 類型 說明 頁次 68

69 資料庫系統授課講義 (Shien-chiang Yu) XADataSource 介面 分散式資料源 由實作分散式交易機制的物件提供實體 (instance) 可自不同資料源取得資料 XAConnection 介面 提供連線物件 供用戶端自分散式資料源取得資料 (4) 資料列集 元件名稱 類型 說明 RowSet 介面 資料列集物件 其所提供的資料操作方式與資料集物件 相似 可向資料源進行資料查詢修改等動作 RowSetListener 介面 利用 JavaBean 技術達成的監聽介面 註冊此監聽介面 的物件可以收到資料列集修改的事件 RowSetEvent 類別 當資料列集變動時發出的事件物件 RowSetMetaData 介面 資料列集使用的 metadata 可以提供資料列集中欄位 的個數 資料型態等資訊 RowSetInternal 介面 提供 RowSet 實作 以便使其可以使用 RowSetReader 與 RowSetWriter 的功能 RowSetReader 介面 提供資料列集物件離線讀取的功能 該物件會自行設法 連線與處理斷線 RowSetWriter 介面 提供資料列集物件離線寫入的功能 該物件會自行設法 連線與處理斷線 頁次 69

70 資料庫系統授課講義 (Shien-chiang Yu) (四) 資料庫連線程式撰寫 1. 載入驅動程式 在 Java 程式中 須先將驅動程式的*.jar 檔案路徑宣告在 windows 系統環境變數的 CLASS_PATH 變數內 提供 JVM 執行時可以自動搜尋到所需的類別檔 大多數廠商的驅動程式均會遵循 Java 套件與程式命名的原則 依據如下的命名方式 com.廠商名稱.jdbc.driver 例如 MySQL 的驅動程式名稱為 com.mysql.jdbc.driver 微軟的 SQL Server 驅動程式名稱為 com.microsoft.jdbc.sqlserver.sqlserverdriver 在程式中載入驅動程式的方式為使用 Class 類別的 forname 方法 執行 Class.forName(xxx.xx.xx)回傳的結果是一個類別 其作用是要求JVM 找尋並載入指定的類別 Java 程式以 JDBC 連結 ODBC 的語法範例為 public class Example{ public static void main(string args[]){ try{ Class.forName( sun.jdbc.odbc.jdbcodbcdriver ); catch (ClassNotFoundException e){ 2. 建立資料庫連線 執行 DriverManager 類別的.getConnection( urlstring)方法 當根據 urlstring 正確連線至指定 的資料庫, 則回傳 Connection 物件 若 Connection 物件無法建立, 則拋出 SQLException 例 外 DriverManager.getConnection 方法 getconnection(string url) getconnection(string url, java.util.properties info) getconnection(string url, String user, String password) Java 程式以 JDBC 連結 ODBC 所設定的資料庫範例為 Connection con=drivermanager.getconnection("jdbc:odbc:sql2000"," 帳號","密碼"); 註 帳號與密碼系指該資料庫登入所設定之帳號與密碼 若未設定則保持空值 3. 建立 SQL 敘述物件 SQL 敘述的介面包括三種 執行靜態 SQL 敘述的 Statement 執行動態 SQL 敘述的 PreparedStatement 執行預儲程序的 CallableStatement Java 程式中依據 SQL 敘述的特 性 執行 Connection 物件的 createstatement 方法 preparestatement 方法 preparecall 方 法 即可回傳上述三種介面之物件 (1) Statement 介面 Statement 主要用於執行靜態的 SQL 敘述 也就是在執行 executequery() executeupdate() 頁次 70

71 資料庫系統授課講義 (Shien-chiang Yu) 等方法時 指定內容固定不變的 SQL 語句字串 每一句 SQL 只適用於當時的執行 語法 Statement st = Connection 物件.createStatement(SQL 敘述); 範例 Statement st; try { st = con.createstatement(); catch (SQLException e) { System.out.println (e.getmessage()); ResultSet rs = stmt.executequery ("SELECT * FROM student WHERE ID = "); (2) PreparedStatement 介面 如果有些操作只是 SQL 敘述當中某些參數會有所不同 其餘的 SQL 子句皆相同 則適合使用 PreparedStatement 語法 PreparedStatement pst = Connection 物件.prepareStatement(SQL 敘述); 使用 Connection 的 preparedstatement()方法建立好一個預先編譯 precompile 的 SQL 語句 當中參數會變動的部份 先指定"?"這個佔位字元 例如 PreparedStatement pst = con.preparestatement("insert INTO student VALUES(?,?,?,?,?)"); 再使用 setint() setstring()等方法 將參數指定給每一個欄位 例如 pst.setstring(1, " "); pst.setstring(2, "張三"); pst.setstring(3, "台北縣五股鄉"); pst.setstring(4, "1990/1/5"); //SQLServer 日期是以 String 型態輸入處理 pst.setstring(5, "M"); pst.executeupdate(); //執行 pst.clearparameters(); (3) CallableStatement 介面 語法 CallableStatement cst = Connection 物件.prepareCall("{call 預儲程序名稱(參 數, )"); 範例 CallableStatement cst = con.preparecall("{call find_customer[?,?]"); try { cst.setstring(1, 1); cst.registeroutparameter(2, Types.REAL); cst.execute(); 頁次 71

72 資料庫系統授課講義 (Shien-chiang Yu) float sales = cst.getfloat(2); catch (SQLException e){ System.out.println (e.getmessage()); 4. SQL 操作與執行 Statement 介面中來處理 SQL 敘述操作與執行的方法 依據選取資料(有回應資料集) 處理資 料(無回應資料集)的差異 提供三種方法 (1) executequery(sql 敘述) executequery 方法用於選取資料(Select 指令)的 SQL 查詢敘述 傳回值為 ResultSet 物件 經 由查詢資料庫檔案之資料集均存放於該 ResultSet 物件內 範例 ResultSet rs=st.execute( select * from student ); (2) executeupdate(sql 敘述) executeupdate 方法用於處理資料(無回應資料集) 包括: DML 之 update delete 指令 DDL 之 create alter drop 等指令的 SQL 敘述 傳回值為 int 表示處理筆數 範例 int cnt=st.executeupdate("delete from student"); (3) execute(sql 敘述) execute 方法可以執行任何 SQL 敘述 傳回值為 boolean true 表示執行查詢 可以經由 Statement 取得查詢結果 false 表示執行新增或修改 可以經由 Statement 取得更新筆數 而在執行後 同樣可以用 getresultset() 來取得 ResultSet 使用時 如果需要讓使用者自行 下 SQL 時 可以使用 execute 方法撰寫 範例 boolean rtn=st.execute( select * from student ); ResultSet rs=stmt.getresultset(); 5. 處理回傳結果或 ResultSet ResultSet 物件的方法 (1) 指標方法 每一個 ResultSet 物件 皆有一個指標(cursor)用以指向目前資料的列(row) 最初開始指標 指在第一筆列的前面(beforeFirst) 使用 next()方法來判斷是否還有資料列待處理, 若有則 回傳 true 同時指到下一筆; 若無則回傳 false 定義 說明 boolean relative(int rows) 將指標移動 rows 所指定的數目 正數表示往後移動 負 數表示往前移動 boolean next() 將指標向後移動一筆 如果指標已經移至最後一筆 將 回回傳 false boolean previous() 將指標向前移動一筆 如果指標已經移至最前一筆 將 頁次 72

73 資料庫系統授課講義 (Shien-chiang Yu) 回回傳 false boolean first() 將指標移至第一筆資料 boolean last() 將指標移至最後一筆資料 boolean beforefirst() 將指標移至第一筆資料之前 此時讀取 ResultSet 物件的 資料會是 null boolean afterlast() 將指標移至最後一筆資料之後 此時讀取 ResultSet 物件 的資料會是 null int getrow() 取得目前指標所在的列數 boolean isbeforefirst() 判斷指標是否在第一筆之前 boolean isafterlast() 判斷指標是否在最後一筆之後 boolean isfirst() 判斷指標是否在第一筆 boolean islast() 判斷指標是否在最後一筆 (2) 取欄位內容方法 利用 ResultSet 所提供的 getxxx()方法來取得每一資料列中的欄位值 定義 int getint(int 欄位索引) int getint(string 欄位名稱) String getstring(int 欄位索引) String getstring(string 欄位名稱) 說明 取得指標所指資料列的整數型態的欄位值 取得指標所指資料列的字串型態的欄位值 Date getdate(int 欄位索引) 取得指標所指資料列的日期型態的欄位值 Date getdate(string 欄位名稱) DateTime 型態的資料亦是使用此方法取得 Time gettime(int 欄位索引) Time gettime(string 欄位名稱) 取得指標所指資料列的時間型態的欄位值 範例 ResultSet rs = st.executequery("select * FROM course"); while (rs.next()) { System.out.println("學號" + rs.getstring(1)); System.out.println("姓名" + rs.getstring(2)); System.out.println("成績" + rs.getint(3)); 6. 關閉 JDBC 物件 程式執行完畢 須執行關閉程式中所產生的 JDBC 資料庫相關物件 以釋放占用之連線與 記憶體空間 下列物件皆有 close()方法 Connection Statement ResultSet 頁次 73

74 資料庫系統授課講義 (Shien-chiang Yu) 建立資料庫連結的基本步驟 // Step 1 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Step 2 Connection con = DriverManager.getConnection ("jdbc:odbc:sql2000","",""); // Step 3 Statement st = con.createstatement(); // Step 4 String sql= "SELECT * FROM course"; ResultSet rs = st.executequery(sql); // Step 5 while (rs.next()) { System.out.println("學號" + rs.getstring(1)); System.out.println("姓名" + rs.getstring(2)); System.out.println("成績" + rs.getint(3)); // Step 6 rs.close(); st.close(); con.close(); 網頁互動程式 JSP 練習 練習 1 取得 Student 檔案內容 <html> <head> <%@ page contenttype="text/html;charset=big5" import="java.sql.*" %> </head> <body> <% try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=drivermanager.getconnection("jdbc:odbc:sql2000","",""); Statement st = con.createstatement(); ResultSet rs = st.executequery("select * from student"); %> <center><table border="1"> <tr><th>學號</th><th>姓名</th><th>地址</th><th>生日</th><th>性別</th></tr> <% while (rs.next() ){ out.println("<tr><td>"+rs.getobject("id")+"</td>"); //學號 out.println("<td>"+rs.getobject("name")+"</td>"); //姓名 out.println("<td>"+rs.getobject("address")+"</td>"); //地址 out.println("<td>"+rs.getobject("birth")+"</td>"); //生日 out.println("<td>"+rs.getobject("sex")+"</td></tr>"); //性別 rs.close(); con.close(); catch(exception e){ out.println(e.getmessage() ); %> 頁次 74

75 資料庫系統授課講義 (Shien-chiang Yu) </table></center> </body> </html> 練習 2 列出各學生學號 姓名 修課數目與平均成績 SQL 敘述 方式 1 使用 ResultSet 物件.getObject( 欄位名稱) 的方式取得資料集內容 <% try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=drivermanager.getconnection("jdbc:odbc:sql2000","",""); Statement st = con.createstatement(); String sql= "select student.id,name,count(*)\"cnt\", avg(score)\"avg\" from student,course where student.id=course.id group by student.id,name"; ResultSet rs = st.executequery(sql); %> <center><table border="1"> <tr><th>學號</th><th>姓名</th><th>修課數</th><th>平均分數</th></tr> <% while (rs.next() ){ out.println("<tr><td>"+rs.getobject("id")+"</td>"); //學號 out.println("<td>"+rs.getobject("name")+"</td>"); //姓名 out.println("<td>"+rs.getobject("cnt")+"</td>"); //修課數 out.println("<td>"+rs.getobject("avg")+"</td>"); //平均分數 rs.close(); con.close(); catch(exception e){ out.println(e.getmessage() ); %> </table></center> 方式 2 依據欄位次序 逐一取資料 <% try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=drivermanager.getconnection("jdbc:odbc:sql2000","",""); Statement st = con.createstatement(); String sql= "select student.id,name,count(*), avg(score) from student,course where student.id=course.id group by student.id,name"; ResultSet rs = st.executequery(sql); %> <center><table border="1"> <tr><th>學號</th><th>姓名</th><th>修課數</th><th>平均分數</th></tr> <% while (rs.next() ){ out.println("<tr><td>"+rs.getobject(1)+"</td>"); //學號 out.println("<td>"+rs.getobject(2)+"</td>"); //姓名 out.println("<td>"+rs.getobject(3)+"</td>"); //修課數 out.println("<td>"+rs.getobject(4)+"</td>"); //平均分數 rs.close(); con.close(); catch(exception e){ out.println(e.getmessage() ); %> 頁次 75

76 資料庫系統授課講義 (Shien-chiang Yu) </table></center> 練習 3 將資料依所屬資料型態儲存於變數中 <% String sno=null; //學號 String sname=null; //姓名 int ncnt=0; //修課數 float faverage=0; //平均分數 try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=drivermanager.getconnection("jdbc:odbc:sql2000","",""); Statement st = con.createstatement(); String sql= "select student.id,name,count(*), avg(score) from student,course where student.id=course.id group by student.id,name"; ResultSet rs = st.executequery(sql); %> <center><table border="1"> <tr><th>學號</th><th>姓名</th><th>修課數</th><th>平均分數</th></tr> <% while (rs.next() ){ sno=rs.getstring(1); //學號 sname=rs.getstring(2); //姓名 ncnt=rs.getint(3); //修課數 faverage=rs.getfloat(4); //平均分數 out.println("<tr><td>"+sno+"</td>"); out.println("<td>"+sname+"</td>"); out.println("<td>"+ncnt+"</td>"); out.println("<td>"+faverage+"</td>"); rs.close(); con.close(); catch(exception e){ out.println(e.getmessage() ); %> </table></center> 頁次 76

77 資料庫系統授課講義 (Shien-chiang Yu) (五) 實例練習 簡介使用網頁互動方式 透過資料庫的使用者資料檢驗輸入的帳號與密碼是否整確 並顯示該 使用者的個人資料與權限大小 藉由此範例 說明應用程式與資料庫之關係 1. 系統環境說明 Web 伺服器 Resin 資料庫系統 SQLServer 伺服器端程式執行平台 Java (JSP) 2. 前置作業 資料庫系統啟動 網站伺服器啟動 連結 ODBC 設定 程式開發環境 3. 範例圖示 頁次 77

78 資料庫系統授課講義 (Shien-chiang Yu) (六) 連線池與(JDNI, JavaBean) 1. 連線池(Connection pool) 連線池是容許客戶端共享一組快取的連線物件 這些物件提供對資料庫資源的存取 如先前介 紹之資料庫連線模式 對每個客戶端的請求 都要進行幾次擷取和釋放資料庫連線的作業 其 是由資料庫供應商所倡導的技術 其服務通常是由實作 Java J2EE規格的應用程式伺服器提供 例如 Resin 的 JSP 容器已經實作 JDBC 的資料來源介面 而且支援連線池(Connection Pool) 的功能 直接連接資料庫系統 每個程式實體均會占用一個資料庫通道(session) 透過 連線池 取得資料庫連線 所有程式共用一個資料庫通道 在直接連線的情況下 同時上線的人數越多 資料庫系統的負荷越大 由其有些連線只是執行 一些簡單的 SQL 敘述便結束 而建立與關閉連線對資料庫系統而言 均需要記憶體空間與 CPU 的處理時間 而連線池的架構是透過建立一組持續的資料庫系統連結來服務所有需要使用資料 庫資料的應用程式 若有應用程式需要連結某個資料庫 便可以向連線池提出一個連線的請求 頁次 78

79 資料庫系統授課講義 (Shien-chiang Yu) 連線池會接到 請求 之後 檢查是否有有建立好的連線 有的話則回傳該連線 若無便會像資 料庫廠商所提供的 JDBC 驅動程式產生一個連線 再回傳給應用程式 以供其使用 之後其他 的應用程式便可以透過此連線對資料庫進行運作 因此 在連線池的架構之下 資料庫系統只 會處理一組連結 簡化了每一個程式建立與關閉連線所帶來的負荷 2. JDNI JNDI (Java Naming and Directory Interface)提供 Java 應用程式所需資源的命名服務(Naming Service) 其功能如同 Internet 的 DNS 使用網域名稱即可找到指定的主機資源 JNDI 可以先 定義資源的 JNDI 名稱 在 Java 應用程式只需使用 JNDI 名稱即可取得所需的資源 在 JSP 程式使用 JNDI 與連線池-以 Resin 網站伺服器為例 (1) 定義 JNDI 的資料來源與連線池 在 web.xml 或 resin.conf 檔案是在<web-app-default>或<web-app>標籤下使用<database> 子標籤來定義 JNDI 的資料來源與連線池 <database> <jndi-name>jdbc/mysql</jndi-name> <driver type="org.gjt.mm.mysql.driver"> <url>jdbc:mysql://localhost:3306/test</url> <user></user> <password></password> </driver> <prepared-statement-cache-size>8</prepared-statement-cache-size> <max-connections>20</max-connections> <max-idle-time>30s</max-idle-time> </database> (2) 取得 JNDI 定義的資料庫連結 在 JSP 程式在 JSP 程式取得 JNDI 定義的資料庫連結前 需要匯入一些套件 如下所示 page import="java.sql.*"%> page import="javax.sql.*"%> 此三行可合併成一行 page import="javax.naming.*"%> page import="java.sql.*, javax.sql.*, javax.naming.* " %> JSP 程式使用 InitialContext 物件的 lookup()方法找尋 JNDI 名稱 其搜尋路徑為 java:comp/env 如下所示 Context env=(context) new InitialContext().lookup("java:comp/env"); DataSource ds = (DataSource) env.lookup("jdbc/mysql"); 在找到 JNDI 名稱後 即可使用下列語法取得資料庫連結 Connection dbcon = ds.getconnection(); 頁次 79

80 資料庫系統授課講義 (Shien-chiang Yu) 3. Java Bean 應用 Java Bean 建立連線池的應用 參考 ConnBean.java 與 PoolBean.java 類別程式的內容 (1) ConnBean.java 的屬性 a. Conn 屬性是 Connection 類別的物件 表示對於指定之資料庫的連結 b. inuse 屬性是布林邏輯的變數 使用者開始向連線池要求一個連結時 inuse 為 true 表示 此連線池為是使用中的連結 當使用者請求結束後 歸還該連線池以供其他使用者使用 時 inuse 則為 false (2) ConnBean.java 的方法 a. ConnBean() 建構元 建立一個空白的 ConnBean 物件 b. ConnBean(con) 使用連結物件 con 建立一個 ConnBean 物件 c. getconn() 傳回一個連結物件 conn d. setinuse(inuse) 設定 inuse 是否為使用中的值 e. getinuse() 取得 inuse 的值 f. close() 關閉 conn 物件 (4)測試 ConnBean <html> <head> page import="java.util.*, java.sql.*, mybean.*" contenttype="text/html;charset=big5" %> <jsp:usebean id="connbean" scope="application" class="mybean.connbean"/> </head> <body> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=drivermanager.getconnection("jdbc:odbc:shu"); connbean.setconnection(con); connbean.setinuse(true); out.println("connection:"+connbean.getconnection()); con.close(); %> </body> </html> 練習 列出各學生學號 姓名 修課數目與平均成績 <html> <head> page import="java.util.*, java.sql.*, mybean.*" contenttype="text/html;charset=big5" %> <jsp:usebean id="pool" scope="application" class="mybean.poolbean"/> </head> <body> <% Connection con=null; try{ if (pool.getdriver()==null){ pool.setdriver("sun.jdbc.odbc.jdbcodbcdriver"); 檢查是否已建立連結池 pool.seturl("jdbc:odbc:shu"); pool.setsize(5); //設定連結池大小 物件 pool.initializepool(); con=pool.getconnection(); 頁次 80

81 資料庫系統授課講義 (Shien-chiang Yu) Statement st=con.createstatement(); String sql= "select student.id,name,count(*)\"cnt\", avg(score)\"avg\" from student,course where student.id=course.id group by student.id,name"; ResultSet rs = st.executequery(sql); %> <center><table border="1"> <tr><th>學號</th><th>姓名</th><th>修課數</th><th>平均分數</th></tr> <% while (rs.next() ){ out.println("<tr><td>"+rs.getobject("id")+"</td>"); //學號 out.println("<td>"+rs.getobject("name")+"</td>"); //姓名 out.println("<td>"+rs.getobject("cnt")+"</td>"); //修課數 out.println("<td>"+rs.getobject("avg")+"</td>"); //平均分數 rs.close(); pool.releaseconnection(con); catch(exception e){ out.println(e.getmessage()); %> </table></center> </body> </html> 頁次 81

82 資料庫系統授課講義 (Shien-chiang Yu) 附錄 A ConnBean.java 程式內容 package mybean; import java.io.*; import java.sql.*; public class ConnBean{ private Connection conn = null; private boolean inuse = false; //*建構元 public ConnBean(Connection con){ if (con!=null) conn = con; //* public Connection getconnection(){ return conn; public void setconnection(connection con){ conn = con; public void setinuse(boolean inuse){ this.inuse = inuse; public boolean getinuse(){ return inuse; /* public boolean inuse() { return inuse; */ public void close(){ try{ conn.close(); catch (SQLException e){ System.err.println(e.getMessage()); PoolBean.java 程式內容 package mybean; import java.io.*; import java.sql.*; import java.util.*; public class PoolBean { private String driver = null; private String url = null; private int size = 1; private String username = ""; private String password = ""; private ConnBean connbean=null; private Vector pool = null; public PoolBean() { public void setdriver(string d){ if (d!=null) driver=d; 頁次 82

83 資料庫系統授課講義 (Shien-chiang Yu) public String getdriver(){ return driver; public void seturl(string u){ if (u!=null) url=u; public String geturl(){ return url; public void setsize(int s){ if (s>1) size=s; public int getsize(){ return size; public void setusername(string un){ if (un!=null) username=un; public String getusername(){ return username; public void setpassword(string pw){ if (pw!=null) password=pw; public String getpassword(){ return password; public void setconnbean(connbean cb){ if (cb!=null) connbean=cb; public ConnBean getconnbean() throws Exception{ Connection con = getconnection(); ConnBean cb = new ConnBean(con); cb.setinuse(true); return cb; private Connection createconnection() throws Exception{ Connection con = null; con = DriverManager.getConnection(url,username,password); return con; public synchronized void initializepool() throws Exception{ if (driver==null) throw new Exception("沒有提供驅動程式名稱!"); if (url==null) throw new Exception("沒有提供 URL!"); if (size<1) throw new Exception("連結池的大小 < 1!"); try{ Class.forName(driver); for (int i=0; i<size; i++){ Connection con = createconnection(); if (con!=null){ ConnBean connbean = new ConnBean(con); addconnection(connbean); catch (Exception e){ 頁次 83

84 資料庫系統授課講義 (Shien-chiang Yu) System.err.println(e.getMessage()); throw new Exception(e.getMessage()); private void addconnection(connbean connbean){ if (pool==null) pool=new Vector(size); pool.addelement(connbean); public synchronized void releaseconnection(connection con){ for (int i=0; i<pool.size(); i++){ ConnBean connbean = (ConnBean)pool.elementAt(i); if (connbean.getconnection()==con){ System.err.println("釋放第 " + i + " 個連結"); connbean.setinuse(false); break; public synchronized Connection getconnection() throws Exception{ ConnBean connbean = null; for (int i=0; i<pool.size(); i++){ connbean = (ConnBean)pool.elementAt(i); if (connbean.getinuse()==false){ connbean.setinuse(true); Connection con = connbean.getconnection(); return con; try{ Connection con = createconnection(); connbean = new ConnBean(con); connbean.setinuse(true); pool.addelement(connbean); catch (Exception e){ System.err.println(e.getMessage()); throw new Exception(e.getMessage()); return connbean.getconnection(); public synchronized void emptypool(){ for (int i=0; i<pool.size(); i++){ System.err.println("關閉第 " + i + " JDBC 連結"); ConnBean connbean = (ConnBean)pool.elementAt(i); if (connbean.getinuse()==false) connbean.close(); else{ try{ java.lang.thread.sleep(20000); connbean.close(); catch (InterruptedException e){ System.err.println(e.getMessage()); 頁次 84