資料庫管理 Database Managent Ex.1-2 課本範例練習 系級：物理四 學號：49814201 姓名：吳嘉峰 授課老師：楊維邦 教授 日期：2013/03/25

目錄 P. 2-45範例操作 P. 2-12範例操作 範例表格建立 P. 2-12表格建立 P. 2-43範例操作 CASE 1：一步到位 比較不同操作順序之速度 P. 2-12範例操作 合併表格

範例表格建立 P. 2-12表格建立

建立 Banking Database 資料庫 指令格式： CREATE DATABASE 資料庫名稱

建立 P. 2-12 表格 1. branch 2. Customer 3. Depositor 4. Borrower 5. Account 6. loan CREATE TABLE branch ( branch_name char((16), branch_city char(16) PRIMARY KEY(branch_name) ) 指令格式： CREATE TABLE 表格名稱 ( 欄位名稱1 資料型別(資料長度), 欄位名稱2 資料型別(資料長度) PRIMARY KEY(欄位名稱) ) 設為PRIMARY KEY 表示該欄位不得為空值 且為唯一

建立 P. 2-12 表格

輸入資料 新增多筆資料 指令格式： INSERT INTO `表格名稱` (`欄位1`, `欄位2`) VALUES (資料1, 資料2), (資料3, 資料4) 在表格branch中新增資料

檢視各表格中的資料

P. 2-43範例操作 CASE 1：一步到位

P. 2-43範例操作 – CASE 1 Find the name of all customers who have a loan at the Perryridge branch 指令語法： SELECT `欄位1`, `欄位2` FROM `資料表名稱` WHERE 敘述式

P. 2-43範例操作 – CASE 1 Customer_name Adams Hayes

P. 2-43範例操作 CASE 2：按部就班

P. 2-43範例操作 – CASE 2 First Step borroewr × loan SELECT * FROM `borrower`, `loan` ; #顯示borrower × loan的結果(Cartesian-Product)

First Step Result 8×7＝56 tuples

P. 2-43範例操作 – CASE 2 Second Step σborrow.loan_number = loan.loan_number(borroewr × loan) SELECT * FROM `borrower`, `loan` WHERE borrow.loan_number = loan.loan_number; #顯示borrower × loan之後，loan_number相同的結果

Second Step Result customer_name loan_number branch_name amount Adams Perryridge 1300 Hayes L_15 1500 Jackson L_14 Downtown Jones L_17 1000 Smith L_11 Round Hill 900 L_23 Redwood 2000 Williams

P. 2-43範例操作 – CASE 2 Third Step σbranch_name =“perryridge” (σborrow.loan_number = loan.loan_number(borroewr × loan)) SELECT * FROM `borrower`, `loan` WHERE borrow.loan_number = loan.loan_number && branch_name = “Perrtridge”; #顯示borrower × loan之後，loan_number相同且branch_name = “Perryridge”的結果

Third Step Result customer_name loan_number branch_name amount Adams Perryridge 1300 Hayes L_15 1500

P. 2-43範例操作 – CASE 2 Final Step Πcustomer_name(σbranch_name =“perryridge” (σborrow.loan_number = loan.loan_number(borroewr × loan))) SELECT `customer_name` FROM `borrower`, `loan` WHERE borrow.loan_number = loan.loan_number && branch_name = “Perrtridge”; #在borrower × loan中尋找loan_number相同且branch_name = “Perryridge”的結果，最後顯示出customer_name欄位

Final Result customer_name Adams Hayes

P. 2-45範例操作 比較不同操作順序之 速度

電腦太快？！ 查詢僅花費 0.0006 秒！

填入測試用亂數資料 Rand(); #隨機產生一個0~1之間的亂數 在表格loan中增加1000筆亂數資料

填入測試用亂數資料 在表格borrower中增加1000筆亂數資料

子查詢造成的欄位名稱衝突 子查詢： SELECT * FORM ( SELECT * FROM `表格1`)NewName #1060 – Duplicate column name “loan_number” 子查詢： SELECT * FORM ( SELECT * FROM `表格1`)NewName

What is better? Query 1 Average Access time = ? Πcustomer_name(σbranch_name = “Perryridge”(σborrower.loan_nimber = loan.loan_number(borrower×loan))) Query 2 Average Access time =? Πcustomer_name(σborrower.loan_nimber = loan.loan_number((σbranch_name = “Perryridge”(loan) )× borrower)) Ratio = ?

Query 1(先相乘表格再查詢) Step 1 查詢花費 0.1265 秒

Query 1(先相乘表格再查詢) Step 2 查詢花費 0.1073 秒

Query 1(先相乘表格再查詢) Final Step 查詢花費 0.1086 秒 SELECT `customer_name` FROM ( SELECT * FROM `borrower`, `loan` WHERE borrower.borrower_loan_number = loan.loan_number )step1 WHERE branch_name = "Perryridge";

Query 1 Average Access Time (0.1086 + 0.1066+0.1064) / 3 ＝0.1072 (sec) 0.1086 + 0.1066+0.1064 0.1086 + 0.1066 0.1086

Query 2(先查詢再相乘表格) Step 1 查詢花費 0.0011 秒

Query 2(先查詢再相乘表格) Step 2 查詢花費 0.0014 秒

Query 2(先查詢再相乘表格) Step 3 查詢花費 0.0033 秒 SELECT * FROM ( SELECT * FROM `loan` WHERE branch_name = “Perryridge” )step1, `borrower` WHERE step1.loan_number = borrower.loan_number;

Query 2(先查詢再相乘表格) Final Step 查詢花費 0.0038 秒 SELECT `customer_name` FROM ( SELECT * FROM `loan` WHERE branch_name = “Perryridge” )step1, `borrower` WHERE step1.loan_number = borrower.borrower_loan_number;

Query 2 Average Access Time (0.0038 + 0.0024+0.0055) / 3 ＝0.0039 (sec) 0.0038 + 0.0024+0.0055 0.0038 + 0.0024 0.0038

Answer: Query 2 is better! Query 1 Average Access time = 0.1072 (sec) Πcustomer_name(σbranch_name = “Perryridge”(σborrower.loan_nimber = loan.loan_number(borrower×loan))) Query 2 Average Access time =0.0039 (sec) Πcustomer_name(σborrower.loan_nimber = loan.loan_number((σbranch_name = “Perryridge”(loan) )× borrower)) Ratio = 0.1072/0.0039 ≒ 27.5 ! 勝

P. 2-12範例操作 合併表格

合併結果

使用語法：LEFT JOIN ON

使用語法：LEFT JOIN ON

The End. Thank you!