關聯式資料庫

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

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

範例 以下是一個非常簡單的 Sales 資料庫: Customers, Orders and Products DB

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

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

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

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

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

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

結構化查詢語言 (SQL)

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

select範例 找出有欠錢的客戶，並且按照欠錢多寡排序 … 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 的 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

預儲程序

預儲程序 預儲程序是事先儲存在資料庫中的 SQL 程式 可以接受參數 可以回傳資料表 預先編譯、最佳化過 – 幾乎是十倍快 ! DB /***** 輸入訂單編號，傳回下這筆訂單的客戶資料 … *****/ 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. 缺點: 每個資料庫提供者的預儲程序語法都不一樣 預儲程序不容易轉換成其它格式的程式