250 likes | 355 Views
關聯式資料庫. DB. 資料庫. 資料庫是一個儲存資料的固定位置 一個資料庫中可以包含一個以上的資料表 每一個資料表中有許多記錄 ( 資料列 ) 例如 : Customers 資料表. 術語. 欄位. 欄位是具有型別的 (currency, string, …). 可以是 null ( 空值 ). 主索引鍵 (primary key) ( 一個或多個值不會重覆的欄位 ). DB. 範例. 以下是一個非常簡單的 Sales 資料庫 : Customers, Orders and Products. 關聯式資料庫. 什麼是關聯 ?
E N D
DB 資料庫 • 資料庫是一個儲存資料的固定位置 • 一個資料庫中可以包含一個以上的資料表 • 每一個資料表中有許多記錄 (資料列) • 例如: Customers 資料表
術語 欄位 欄位是具有型別的 (currency, string, …) 可以是 null (空值) 主索引鍵 (primary key)(一個或多個值不會重覆的欄位)
DB 範例 • 以下是一個非常簡單的Sales資料庫: • Customers, Orders and Products
關聯式資料庫 • 什麼是關聯 ? • 資料表之間透過主索引鍵進行關聯 … 關聯鍵 (foreign key)(一個關聯到資料表主索引鍵的欄位)
1 1 ¥ ¥ 資料庫結構 • 資料庫結構就是資料庫的設計結果 • 資料表、欄位、型別、主索引鍵、… • 關聯: 一對多、多對一、多對多
正規化 • 資料庫正規化可以幫助你設計資料庫 • 一種數學式的資料庫設計方式 • 讓我們來看一下「第一階正規化」
範例 • 客戶購買多個產品 • 第一種設計方式: • Items欄位包含產品代號與數量 (pid, quantity) 的清單 … • 這樣並不好 …
第一階正規化 • 1st正規化: • 在一個欄位中只會包含一個 “簡單” 的資料 • 處理方式: 將這個欄位變成多個欄位和多筆記錄 …
第一階正規化 • 之前的設計錯誤 ! • 第二種設計:將訂單明細的部份變成資料表 … 主索引鍵(組合式)
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. • 缺點: • 每個資料庫提供者的預儲程序語法都不一樣 • 預儲程序不容易轉換成其它格式的程式