PowerPoint 簡報

Size: px

Start display at page:

Download "PowerPoint 簡報"

兹松
5 years ago
Views:

1 資料庫管理 Database Management 建立銀行資料庫及查詢範例系級 : 物理四學號 : 姓名 : 吳嘉峰授課老師 : 楊維邦教授

2 主題說明利用 phpmyadmin 在 MySQL 中建立簡單的銀行資料庫依照範例情境, 練習對銀行資料庫作查詢

3 目錄 Primary Key Foreign Key Schema Diagram 建立 Banking Database 建立 Table 輸入資料手動解題實做練習心得 SQL 操作演列

4 Banking Database

5 Example: Banking Database 1. branch 分公司 2. customer 客戶 ( 存款戶, 貸款戶 ) 3. depositor 存款戶 4. borrower 貸款戶 5. account 存款帳 6. loan 貸款帳資料來源 :Database System Concepts, Silberschatz etc (Fifth Ed.)

6 Primary Key & Foregin Key Primary Key: 該欄位不得為空值且為唯一 Foregin Key: 該欄位的值需參考其他表格中的欄位, 當被參考表格中不存在該值時便不可輸入該筆資料, 被參考的資料也不可任意的更新 (UPDATE) 或刪除 (DELETE)

7 Schema Diagram Primary Key and foreign key can be depicted by schema diagram Schema Diagram for the Banking Enterprise 箭號方向 :Primary Key Foreign Key 資料來源 :Database System Concepts, Silberschatz etc (Fifth Ed.)

8 建立 Database 及 Table 並輸入資料

9 建立 Banking Database 資料庫新增一個名為 CREATE DATABASE Banking_Database Banking_Database; 的資料庫指令格式 : CREATE DATABASE 資料庫名稱

建立及輸入 Table 內資料 1. branch 2. customer 3. account 4. loan 5. depositor 6.

資料長度 ), PRIMARY KEY( 欄位名稱 ), ); 設為 PRIMARY KEY 表示該欄位不得為空值且為唯一 ( 選用 )

: INSERT INTO ` 表格名稱 ` (` 欄位 1`, ` 欄位 2`) VALUES ( 資料 1, 資料 2), ( 資料 3,

10 建立及輸入 Table 內資料 1. branch 2. customer 3. account 4. loan 5. depositor 6. borrower 指令格式 : CREATE TABLE 表格名稱 ( 欄位名稱 1 資料型別 ( 資料長度 ), 欄位名稱 2 資料型別 ( 資料長度 ), PRIMARY KEY( 欄位名稱 ), ); 設為 PRIMARY KEY 表示該欄位不得為空值且為唯一 ( 選用 ) FOREIGN KEY( 本 table 欄位名稱 ) REFERENCES 參照 table 名稱 ( 參照欄位 ) 新增多筆資料指令格式 : INSERT INTO ` 表格名稱 ` (` 欄位 1`, ` 欄位 2`) VALUES ( 資料 1, 資料 2), ( 資料 3, 資料 4); 設為 FOREIGN KEY 表示該欄位的值需參考其他表格中的欄位, 當被參考表格中不存在該值時便不可輸入該筆資料 ( 選用 )

11 back 1. branch ( 分公司 ) branch-name branch-city assets Brighton Brooklyn Downtown CREATE TABLE Brooklyn branch INSERT INTO `branch` Mianus ( (`branch_name`, Horseneck `branch_city`, `assets`) VALUES ('Brighton', 'Brooklyn', North Town ), branch_name Rye char(16), ('Downtown', 'Brooklyn', Perryridge branch_city ), Horseneck char(16), ('Mianus', 'Horseneck', ), assets int(10), Pownal Bennington ('North Town', 'Rye', ), PRIMARY KEY(branch_name) ('Perryridge', 'Horseneck', Redwood ); ), Palo Alto ('Pownal', 'Bennington', Round Hill ), Horseneck ('Redwood', 'Palo Alto', ), ('Round Hill', 'Horseneck', );

12 back 2. customer ( 客戶 : 存款戶貸款戶 ) INSERT INTO customer-name `customer` (`customer_name`, customer-street `customer_street`, customer-city `customer_city`) AdamsVALUES Spring Pittsfield ('Adams', 'Spring', 'Pittsfield' ), Brooks Senator Brooklyn ('Brooks', 'Senator', CREATE 'Brooklyn'), TABLE customer Curry North Rye ('Curry', 'North', ( 'Rye'), ('Glenn', 'Sand Glenn Hill', 'Woodside'), customer_name Walnut char(16), Stamford ('Green', 'Walnut', Hayes 'Stamford'), customer_street Main char(16), Harrison ('Hayes', 'Main', Johnson 'Harrison'), customer_city Alma char(16), Palo Alto ('Johnson', Jones 'Alma', 'Palo PRIMARY Alto'), MainKEY(customer_name) Harrison ('Jones', 'MAin', ); 'Harrison'), Lindsay Park Pittsfield ('Lindsay', 'Park', 'Pittsfield'), ('Smith', 'North', Smith 'Rye'), North Rye ('Turner', 'Putnam', Turner 'Stamford'), Putuam Stamford ('Williams', Williams 'Nassau', 'Princeton'), Nassau Princeton ('Jackson', NULL, NULL);# 特別注意!

13 back 3. account ( 存款帳 ) account-number branch-name balance CREATE TABLE account A-101 Downtown 500 ( A-102 Perryridge 400 INSERT account_number INTO `account` A-201 (`account_number`, char(16), Brighton `branch_name`, 900 `balance`) VALUESbranch_name char(16), A-215 Mianus 700 ('A_101', balance 'Downtown', int(10), 500), PRIMARY A-217 KEY (account_number), Brighton 750 ('A_102', 'Perryridge', 400), ('A_201', FOREIGN 'Brighton', A-222 KEY 800), (branch_name) Redwood REFERENCES700 branch(branch_name) ); ('A_215', 'Mianus', A ), Round Hill 350 ('A_217', 'Brighton', 750), ('A_222', 'Redwood', 700), ('A_305', 'Round Hill', 350);

14 back 4. loan ( 貸款帳 ) loan-number branch-name amount L-11 Round Hill 900 INSERT INTO `loan` (`loan_number`, `branch_name`, `amount`) CREATE VALUES TABLE loan L-14 Downtown 1500 ( ('L_11', 'Round L-15 Hill', 900), Perryridge 1500 loan_number char(16), ('L_14', 'Downtown', branch_name L ), char(16), Perryridge 1300 ('L_15', 'Perryridge', 1500), amount L-17 int(10), Downtown 1000 ('L_16', 'Perryridge', 1300), PRIMARY L-23 KEY (loan_number), ('L_17', Redwood 2000 FOREIGN 'Downtown', KEY 1000), (branch_name) REFERENCES branch(branch_name) ); ('L_23', 'Redwood', L ), Mianus 500 ('L_93', 'Mianus', 500);

15 back 5. depositor ( 存款戶 ) customer-name account-number Hayes A-102 INSERT INTO `depositor` (`customer_name`, `account_number`) VALUES Johnson A-101 CREATE TABLE depositor (('Hayes', 'A_102'), Johnson A-201 ('Johnson', customer_name 'A_101'), Jones char(16), A-217 ('Johnson', account_number 'A_201'), char(16), Lindsay A-222 ('Jones', 'A_217'), ('Lindsay', 'A_222'), Smith A-215 ); ('Smith', 'A_215'), Turner A-305 ('Turner', 'A_305'); FOREIGN KEY (customer_name) REFERENCES customer(customer_name), FOREIGN KEY (account_number) REFERENCES account(account_number)

16 back 6. borrower ( 貸款戶 ) customer-name loan-number Adams L-36 INSERT INTO `borrower` (`customer_name`, `loan_number`) VALUES ('Adams', 'L_16'), Curry L-93 CREATE TABLE borrower Hayes L-15 (('Curry', 'L_93'), ('Hayes', customer_name 'L_15'), Jackson char(16), L-14 ('Jackson', loan_number 'L_14'), Jones char(16), L-17 ('Jones', 'L_17'), FOREIGN KEY Smith (loan_number) REFERENCES L-11 ('Smith', 'L_11'), loan(loan_number) ); ('Smith', 'L_23'), Smith L-23 ('Williams', 'L_17'); Williams L-17 FOREIGN KEY (customer_name) REFERENCES customer(customer_name),

17 檢視各表格中的資料

18 範例說明及演練

19 查詢範例說明 1. 找出住在 North 且有貸款的客戶 2. 查詢這些客戶的貸款金額 3. 確認貸款分公司及所在地 4. 是否有存款? 5. 存款分公司所在地? 6. 住在 North 同時有存款及貸款的客戶之帳號及金額

20 手動解題 1. 找出住在確認貸款分公司及所在地查詢這些客戶的貸款金額 5. 存款分公司所在地 North 4. 是否有存款且有貸款的客戶名稱?? 1. branch 分公司 2. customer 客戶 ( 存款戶, 貸款戶 ) 3. depositor 存款戶 4. borrower 貸款戶 5. account 存款帳 6. loan 貸款帳

21 SQL 解題 1: 住 North 且有貸款的客戶

22 SQL 解題 2: 貸款金額

23 SQL 解題 3: 貸款分公司及所在地

24 SQL 解題 4: 是否有存款?

25 SQL 解題 5: 存款分公司所在地?

26 SQL 解題 6: 住在 North 同時有存款及貸款的客戶之帳目資料

27 實做練習心得

28 心得感想這次為了建立好銀行資料庫, 必須要把它的 schema diagram 弄清楚, 了解各個 key 的作用, 參考時需注意欄位型別及長度若是不參考各 key 之間的關係, 銀行資料庫依然可以順利建立完成, 但是這種銀行資料庫可能會在更新刪除後出現錯誤或資料一致性問題資料輸入必須謹慎, 需注意是否有打字錯誤真實的資料庫系統中, 我們比較不可能使用手動查詢的方式來解決問題, 因為真實系統中的資料數量遠高於這次的範例解決同一問題的 SQL query, 通常不只一種

29 Thank you!

(Microsoft Word - 11\244T\246\342\277\337\260l\302\334.doc)

$(Microsoft Word - 11\244T\246\342\277\337\260l\302\334.doc)$ 赤川次郎作品集 11 三色貓追蹤序曲那是一隻凶猛的野狗加上饑餓, 正在虎視眈眈地注視孩子嘴裏啃著的香腸, 準備突然猛撲上去孩子還不滿三歲, 不曉得若想躲開那隻身體比自己大的惡犬一擊, 最好是把香腸扔掉, 但他反而緊握在手開