資料庫管理 Database Managent Ex.1-2 課本範例練習

Presentation on theme: "資料庫管理 Database Managent Ex.1-2 課本範例練習"— Presentation transcript:

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

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

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

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

5 建立 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 表示該欄位不得為空值 且為唯一

6 建立 P 表格

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

8 檢視各表格中的資料

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

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

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

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

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

14 First Step Result 8×7＝56 tuples

15 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相同的結果

16 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

17 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”的結果

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

19 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欄位

20 Final Result customer_name Adams Hayes

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

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

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

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

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

26 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 = ?

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

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

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

30 Query 1 Average Access Time
( ) / 3 ＝ (sec) 0.1086

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

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

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

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

35 Query 2 Average Access Time
( ) / 3 ＝ (sec) 0.0038

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

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

38 合併結果

39 使用語法：LEFT JOIN ON

40 使用語法：LEFT JOIN ON

41 The End. Thank you!