資料庫 設計與 SQL 單元六 曹祖聖 台灣微軟特約資深講師 http://teacher.allok.com.tw jimycao@syset.com MCP, MCP+I, MCSA, MCSE, MCDBA, MCAD, MCSD, MCT / MVP

課程目標及議題 議題 關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序 目前的應用程式大部份都是資料庫相關的應用程式，而這些資料庫通常都是關聯式、使用 Structured Query Language (SQL) 語法來進行存取 … 議題 關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序

議程 關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序

資料庫 資料庫是一個儲存資料的固定位置 一個資料庫中可以包含一個以上的資料表 每一個資料表中有許多記錄 (資料列) DB 資料庫是一個儲存資料的固定位置 一個資料庫中可以包含一個以上的資料表 每一個資料表中有許多記錄 (資料列) 例如: Customers 資料表

術語 欄位 欄位是具有型別的 (currency, string, …) 可以是 null (空值) 主索引鍵 (primary key) (一個或多個值不會重覆的欄位)

範例 以下是一個非常簡單的 Sales 資料庫: Customers, Orders and Products DB 注意一下，現在一個 客戶一次只能訂購一個產品 (我們後續會修正這個問題 …)

關聯式資料庫 什麼是關聯 ? 資料表之間透過主索引鍵進行關聯 … 關聯鍵 (foreign key) (一個關聯到資料表主索引鍵的欄位)

資料庫結構 資料庫結構就是資料庫的設計結果 資料表、欄位、型別、主索引鍵、… 關聯: 一對多、多對一、多對多 1 ¥

正規化 資料庫正規化可以幫助你設計資料庫 一種數學式的資料庫設計方式 讓我們來看一下「第一階正規化」

範例 客戶購買多個產品 第一種設計方式: Items 欄位包含產品代號與數量 (pid, quantity) 的清單 … 這樣並不好 …

第一階正規化 1st 正規化: 在一個欄位中只會包含一個 “簡單” 的資料 處理方式: 將這個欄位變成多個欄位和多筆記錄 …

第一階正規化 之前的設計錯誤 ! 第二種設計: 將訂單明細的部份變成資料表 … 主索引鍵 (組合式)

那麼 … 關聯式資料庫的設計很困難 … 以下請牢記在心: 有很多階的正規劃: 1NF, 2NF, 3NF, … 其它概念: indexes, views, triggers, constraints 以下請牢記在心: 不要有重覆的資料，資料只要儲存一次 … 命名規則很重要: 不要在名稱中加空白，例如使用 PrdoductName 或 prdoduct_name 不要使用 SQL 關鍵字: Count, Date, Day, Year, …

議程 關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序

資料庫管理系統 (DBMS) 資料庫管理系統包含兩項重要的東西: 資料通常儲存在一個或多個檔案裡面 資料 資料庫引擎 資料庫 引擎 資料 要求

一般常見的資料庫管理系統 有兩種常見的資料庫管理系統: 本機 伺服器

Databases 1: Design & SQL 本機資料庫管理系統 Databases 1: Design & SQL Feb-May 2004 本機 DBMS: 資料庫引擎在用戶端 例如: MS Access 資料庫 (.mdb) 資料庫引擎: JET ("Joint Engine Technology") 資料庫引擎 資料 用戶端行程 Note there is a difference between MS Access the product and MS Access databases. In particular, you can programmatically work with MS Access databases WITHOUT owning a copy of MS Access the product. MS Access the product is really just a GUI tool that lets you more easily work with MS Access databases. But JET is available separately for free, and thus you really don't need MS Access the product. ©2004 Joe Hummel, All Rights Reserved

資料庫管理系統伺服器 伺服器 DBMS: 資料庫引擎是另外一個行程 例如: 通常是在另外一部主機上 MS SQL Server, Oracle, DB2, MySQL 資料庫引擎 資料 網路 伺服器行程 用戶端行程

工具 SQL Server Microsoft Access 其它: Query Analyzer : 執行 T-SQL 語法 Enterprise Manager : 管理 SQL Server Microsoft Access 建立資料庫、查詢、… 使用 JET 引擎，免費 其它: 使用 Visual Studio .NET 中的伺服器總管 WebMatrix 整合開發環境 (http://www.asp.net/)

資料庫管理工具

議程 關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序

SQL Structured Query Language 1970 開始至今 關聯式資料庫程式設計語言 一種宣告式的語言 標準型別: integers, reals, strings, currency, … 資料結構: 資料表 DML: Data Manipulation Language (select, update, …) DDL: Data Definition Language (create, drop, …) 一種宣告式的語言 你宣告你要什麼 – 提出要求 資料庫引擎處理你的要求

範例 找出有欠錢的客戶，並且按照欠錢多寡排序 … SELECT LastName, FirstName, AcctBalance 資料庫引擎會搜尋、排序、然後傳回結果 SELECT LastName, FirstName, AcctBalance FROM Customers WHERE AcctBalance > 0.0 ORDER BY AcctBalance DESC;

SQL 中的一切都是資料表 資料表就是 SQL 中最基本的資料結構 你就是要針對資料表做處理 結果看起來就像一個表格 SELECT LastName, FirstName, AcctBalance FROM Customers WHERE AcctBalance > 0.0 ORDER BY AcctBalance DESC;

SQL … SELECT LastName, FirstName, AcctBalance FROM Customers WHERE AcctBalance > 0.0 ORDER BY AcctBalance DESC; SQL …

SQL 的 DML SQL 的 Data Manipulation Language: 使用查詢格式來撰寫 SELECT 語法可以用來從資料庫取得資料 其它語法: INSERT, UPDATE, DELETE 不分大小寫 …

範例 #1 搜尋所有客戶資料，不做其它處理 … SELECT * FROM Customers;

範例 #2 根據 LastName 和 FirstName 來排序 … SELECT * FROM Customers ORDER BY LastName ASC, FirstName ASC;

計算型的欄位 SQL 可以進行計算 例如: 計算客戶數目、平均欠款、欠款最大金額 SELECT COUNT(*) AS CustomerCount, AVG(AcctBalance) AS AvgAcctBalance, MAX(AcctBalance) AS MaxAcctBalance FROM Customers;

Databases 1: Design & SQL Joins Databases 1: Design & SQL Feb-May 2004 如果要合併兩個資料表的資料，使用 JOIN … 範例: 那一個客戶下了編號 #12351 的訂單 ? 我們要 Customers 資料表中的客戶名稱，但是需要 Orders 資料表中的訂單編號來做搜尋條件 SELECT FirstName, LastName FROM Customers INNER JOIN Orders ON Customers.CID = Orders.CID WHERE Orders.OID = 12351; An "inner join" means only the records that match the join condition are considered for selection; customers without matching orders and orders without matching customers are not considered. ©2004 Joe Hummel, All Rights Reserved

Databases 1: Design & SQL Outer joins Databases 1: Design & SQL Feb-May 2004 如果你要不符合條件的記錄，使用outer join 範例: 列出所有客戶資料，以及他們的訂單數目 (如果有的話) inner join : 只會傳回有下訂單的客戶 outer join : 傳回所有客戶 (不管有沒有下訂單) SELECT FirstName, LastName, COUNT(OID) AS NumberOfOrders FROM Customers LEFT OUTER JOIN Orders ON Customers.CID = Orders.CID GROUP BY LastName, FirstName ORDER BY LastName ASC, FirstName ASC; GROUP BY groups records so we can perform computation A "left" outer join means keep the unmatched records from the left (first) table, while a "right" outer join means keep the unmatched records from the right (second) table. ©2004 Joe Hummel, All Rights Reserved

Databases 1: Design & SQL 巢狀式 joins Databases 1: Design & SQL Feb-May 2004 如果要 join 多個表格時會用到 … 範例: 某一個客戶訂了那一個產品 需要什麼欄位，就 join 那個欄位所在的表格 … SELECT ProductName FROM Products WHERE Products.PID IN ( SELECT DISTINCT PID FROM OrderItems INNER JOIN SELECT OID FROM Orders INNER JOIN Customers ON Orders.CID = Customers.CID WHERE FirstName = 'Jim' AND LastName = 'Bag' ) AS TEMP On OrderItems.OID = TEMP.OID ) ORDER BY ProductName ASC; Wow, fun ehh? :-) But if you study it from the innermost query and work your way outward, it makes sense: first use customer name to get order ids, then use order ids to get product ids, then use products to match with Products table and retrieve the product names. Note that string values require a special delimiter in SQL, in this case '. Dates require the # character as their delimiter. Note the Distinct keyword in the nested query. This eliminates duplicate product ids, so that when we join with the Products table we get each product name only once in the final result. ©2004 Joe Hummel, All Rights Reserved

Databases 1: Design & SQL Feb-May 2004 用來修改資料庫中的資料: 注意: 字串要用 ‘ 括起來，日期要用 # 括起來 如果要刪除客戶，要一併刪除訂單、訂單名細資料 ? 還是只是將該客戶標記成刪除 ? INSERT INTO Customers(CID, FirstName, LastName, CreditLimit, AcctBalance, DateOfEntry) Values(667, 'Jia', 'Zhang', 1000.0, 0.0, #01-March-2004#); UPDATE Customers SET CreditLimit = 40000000000.0, AcctBalance = 0.0 WHERE LastName = 'Gates' AND FirstName = 'Bill'; DELETE FROM Customers WHERE CID = 666; Databases should maintain "referential integrity". This means that a table's foreign key should always be valid, i.e. the references between tables are valid. If we delete a customer from the Customers table, then we may end up with records in the Orders table for which there is no customer record. So either we delete these child records when we delete master customer record, or we don't delete customer record at all — perhaps we just mark the customer's record as closed, but don't delete it. Probably makes more sense… ©2004 Joe Hummel, All Rights Reserved

議程 關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序

預儲程序 預儲程序是事先儲存在資料庫中的 SQL 程式 可以接受參數 可以回傳資料表 預先編譯、最佳化過 – 幾乎是十倍快 ! DB /***** 輸入訂單編號，傳回下這筆訂單的客戶資料 … *****/ CREATE PROCEDURE sprocOrdersOIDToCustomer @OID bigint AS SELECT * FROM Customers INNER JOIN Orders ON Customers.CID = Orders.CID WHERE Orders.OID = @OID

優點與缺點 優點: 缺點: 執行快速 將複雜的 SQL 程式封裝起來 提供另外一層的存取控制能力 大部份的資料庫提供者都支援 /***** 輸入訂單編號，傳回下這筆訂單的客戶資料 … *****/ CREATE PROCEDURE sprocOrdersOIDToCustomer @OID bigint AS SELECT * FROM Customers INNER JOIN Orders ON Customers.CID = Orders.CID WHERE Orders.OID = @OID 優點: 執行快速 將複雜的 SQL 程式封裝起來 提供另外一層的存取控制能力 大部份的資料庫提供者都支援 SQL Server, Oracle, etc. 缺點: 每個資料庫提供者的預儲程序語法都不一樣 預儲程序不容易轉換成其它格式的程式

參考資訊 投影片與範例程式碼: 參考書籍: http://teacher.allok.com.tw 文魁 – Visual Basic .NET 學習範本 文魁 – Visual Basic .NET 程式設計經典

Databases 1: Design & SQL Feb-May 2004 ©2004 Joe Hummel, All Rights Reserved