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

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

目錄 檢視銀行資料庫 建立銀行資料庫 查詢範例練習 手動解題 SQL操作演列 心得感想 實做練習心得 Primary Key Foreign Key Schema Diagram 建立銀行資料庫 建立Banking Database 建立Table 輸入資料 查詢範例練習 手動解題 SQL操作演列 心得感想 實做練習心得

檢視銀行資料庫 Banking Database

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

Primary Key ＆ Foregin Key Foregin Key：該欄位的值需參考 其他表格中的欄位，當被參考表 格中不存在該值時便不可輸入該 筆資料，被參考的資料也不可任 意的更新(UPDATE)或刪除 (DELETE)。

Schema Diagram for the Banking Enterprise 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. 2006 (Fifth Ed.)

建立Database及Table並輸入資料 建立銀行資料庫 建立Database及Table並輸入資料

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

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

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

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

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

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

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

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

檢視各表格中的資料

查詢範例練習 範例說明及演練

查詢範例說明 找出住在North且有貸款的客戶。 查詢這些客戶的貸款金額。 確認貸款分公司及所在地。 是否有存款？ 存款分公司所在地？

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

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

SQL解題2：貸款金額

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

SQL解題4：是否有存款？

SQL解題5：存款分公司所在地？

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

心得感想 實做練習心得

心得感想 這次為了建立好銀行資料庫，必須要把它的 schema diagram弄清楚，了解各個key的 作用，參考時需注意欄位型別及長度。 資料輸入必須謹慎，需注意是否有打字錯誤。 真實的資料庫系統中，我們比較不可能使用 手動查詢的方式來解決問題，因為真實系統 中的資料數量遠高於這次的範例。 解決同一問題的SQL query，通常不只一種。

The End. Thank you!