1、內容,關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序,資料庫,資料庫是一個儲存資料的固定位置 一個資料庫中可以包含一個以上的資料表 每一個資料表中有許多記錄 (資料列) 例如: Customers 資料表,術語,主索引鍵 (primary key) (一個或多個值不會重覆的欄位),欄位,欄位是具有型別的 (currency, string, ),可以是 null (空值),範例,以下是一個非常簡單的 Sales 資料庫: Customers, Orders and Products,關聯式資料庫,什麼是關聯 ? 資料表之間透過主索引鍵進行關聯 ,關聯鍵 (
2、foreign key) (一個關聯到資料表主索引鍵的欄位),資料庫結構,資料庫結構就是資料庫的設計結果 資料表、欄位、型別、主索引鍵、關聯: 一對多、多對一、多對多,正規化,資料庫正規化可以幫助你設計資料庫 一種數學式的資料庫設計方式 讓我們來看一下第一階正規化,範例,客戶購買多個產品 第一種設計方式: Items 欄位包含產品代號與數量 (pid, quantity) 的清單 這樣並不好 ,第一階正規化,1st 正規化: 在一個欄位中只會包含一個 “簡單” 的資料 處理方式: 將這個欄位變成多個欄位和多筆記錄 ,第一階正規化,之前的設計錯誤 ! 第二種設計: 將訂單明細的部份變成資料表 ,
3、主索引鍵 (組合式),那麼 ,關聯式資料庫的設計很困難 有很多階的正規劃: 1NF, 2NF, 3NF, 其它概念: indexes, views, triggers, constraints以下請牢記在心: 不要有重覆的資料，資料只要儲存一次 命名規則很重要: 不要在名稱中加空白，例如使用 PrdoductName 或 prdoduct_name 不要使用 SQL 關鍵字: Count, Date, Day, Year, ,內容,關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序,資料庫管理系統 (DBMS),資料庫管理系統包含兩項重要的東西: 資料 資料庫
4、引擎資料通常儲存在一個或多個檔案裡面,資料,要求,一般常見的資料庫管理系統,有兩種常見的資料庫管理系統: 本機 伺服器,本機資料庫管理系統,本機 DBMS: 資料庫引擎在用戶端例如: MS Access 資料庫 (.mdb) 資料庫引擎: JET (“Joint Engine Technology“),資料,用戶端行程,資料庫管理系統伺服器,伺服器 DBMS: 資料庫引擎是另外一個行程 通常是在另外一部主機上例如: MS SQL Server, Oracle, DB2, MySQL,資料,用戶端行程,網路,伺服器行程,工具,SQL Server Query Analyzer : 執行 T-SQ
5、L 語法 服務管理員 : 啟動或停止 SQL Server Enterprise Manager : 管理 SQL Server Microsoft Access 建立資料庫、查詢、 使用 JET 引擎，免費 其它: 使用 Visual Studio .NET 中的伺服器總管 WebMatrix 整合開發環境 (http:/ 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序,SQL,Structured Query Language 1970 開始至今 關聯式資料庫程式設計語言 標準型別: integers, reals, strings, currency, 資料結構: 資
6、料表 DML: Data Manipulation Language (select, update, ) DDL: Data Definition Language (create, drop, ) 一種宣告式的語言 你宣告你要什麼 提出要求 資料庫引擎處理你的要求,select範例,找出有欠錢的客戶，並且按照欠錢多寡排序 資料庫引擎會搜尋、排序、然後傳回結果,SELECT LastName, FirstName, AcctBalanceFROM Customers WHERE AcctBalance 0.0ORDER BY AcctBalance DESC;,SQL 中的一切都是資料表,資
7、料表就是 SQL 中最基本的資料結構 你就是要針對資料表做處理 結果看起來就像一個表格,SELECT LastName, FirstName, AcctBalanceFROM Customers WHERE AcctBalance 0.0ORDER BY AcctBalance DESC;,SQL 的 DML,SQL 的 Data Manipulation Language: 使用查詢格式來撰寫 SELECT 語法可以用來從資料庫取得資料 其它語法: INSERT, UPDATE, DELETE不分大小寫 ,範例 #1,搜尋所有客戶資料，不做其它處理 ,SELECT * FROM Custom
8、ers;,範例 #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 範例: 那一個客戶
9、下了編號 #12351 的訂單 ?我們要 Customers 資料表中的客戶名稱，但是需要 Orders 資料表中的訂單編號來做搜尋條件,SELECT FirstName, LastNameFROM Customers INNER JOIN Orders ON Customers.CID = Orders.CID WHERE Orders.OID = 12351;,Outer joins,如果你要不符合條件的記錄，使用outer join 範例: 列出所有客戶資料，以及他們的訂單數目 (如果有的話) inner join : 只會傳回有下訂單的客戶 outer join : 傳回所有客戶 (不
10、管有沒有下訂單),SELECT FirstName, LastName, COUNT(OID) AS NumberOfOrdersFROM CustomersLEFT OUTER JOIN Orders ON Customers.CID = Orders.CIDGROUP BY LastName, FirstNameORDER BY LastName ASC, FirstName ASC;,巢狀式 joins,如果要 join 多個表格時會用到 範例: 某一個客戶訂了那一個產品 需要什麼欄位，就 join 那個欄位所在的表格 ,SELECT ProductName FROM Products
11、WHERE Products.PID IN( SELECT DISTINCT PID FROM OrderItems INNER JOIN ( SELECT OID FROM OrdersINNER JOIN Customers ON Orders.CID = Customers.CIDWHERE FirstName = Jim AND LastName = Bag ) AS TEMP On OrderItems.OID = TEMP.OID )ORDER BY ProductName ASC;,非查詢式 SQL 命令,用來修改資料庫中的資料: 注意: 字串要用 括起來，日期要用 # 括起來如
12、果要刪除客戶，要一併刪除訂單、訂單名細資料 ? 還是只是將該客戶標記成刪除 ?,INSERT INTO Customers(CID, FirstName, LastName, CreditLimit, AcctBalance, DateOfEntry)Values(667, Jia, Zhang, 1000.0, 0.0, #01-March-2004#);,UPDATE CustomersSET CreditLimit = 40000000000.0, AcctBalance = 0.0WHERE LastName = Gates AND FirstName = Bill;,DELETE F
13、ROM Customers WHERE CID = 666;,內容,關聯式資料庫設計 資料庫管理系統 (DBMS) 結構化查詢語言 (SQL) 預儲程序,預儲程序,預儲程序是事先儲存在資料庫中的 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. 缺點: 每個資料庫提供者的預儲程序語法都不一樣 預儲程序不容易轉換成其它格式的程式,
