關聯式資料庫

關聯式資料庫

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

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

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

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

1 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 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;

Joins • 如果要合併兩個資料表的資料，使用 JOIN … • 範例: • 那一個客戶下了編號 #12351 的訂單 ? • 我們要 Customers 資料表中的客戶名稱，但是需要 Orders 資料表中的訂單編號來做搜尋條件 SELECT FirstName, LastName FROM Customers INNER JOIN Orders ON Customers.CID = Orders.CID WHERE Orders.OID = 12351;

Outer joins • 如果你要不符合條件的記錄，使用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;

巢狀式 joins • 如果要 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;

非查詢式 SQL 命令 • 用來修改資料庫中的資料: • 注意: 字串要用 ‘ 括起來，日期要用 # 括起來 • 如果要刪除客戶，要一併刪除訂單、訂單名細資料 ? 還是只是將該客戶標記成刪除 ? 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;

預儲程序

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

優點與缺點 • 優點: • 執行快速 • 將複雜的 SQL 程式封裝起來 • 提供另外一層的存取控制能力 • 大部份的資料庫提供者都支援 • SQL Server, Oracle, etc. • 缺點: • 每個資料庫提供者的預儲程序語法都不一樣 • 預儲程序不容易轉換成其它格式的程式

關聯式資料庫

關聯式資料庫

Presentation Transcript