Database

目錄 一、Database規劃 二、Database建置 三、Database查詢 法一：ERD->RDM 法二：正規化 DDL 求最簡功能相依 求候選鍵 1NF、2NF、3NF、BCNF 二、Database建置 DDL create、drop、alter DML insert、update、delete 三、Database查詢 DQL select

第 0 章 Database簡介

參考書 書名： 資料庫學習實務 作者： 李春雄

資料庫建構流程 DB規劃 DB建置 DB查詢 [紙上作業階段] [系統建置階段] [SQL查詢階段] 1、畫ERD 2、正規化 建立表格 利用SQL指令至 DB找資料

資料庫的種類 階層式DB 網狀式DB 關聯式DB 物件導向式DB 主流

Database system Database system = DBMS + Database + other (資料庫) 一般user 「Field（欄位）」是由許多個「字元」組成的 「Record（資料記錄）」是由好幾個「欄位」所組成 「Table（資料表）」則是由許多個「資料記錄」所組成的 「DataBase（資料庫）」是由許多個「資料表」所組成的 DBA (資料庫管理師) Note：Database一般包含在DBMS內

Database實體架構 HTTP PHP、 ODBC、 JDBC 展示層 商業邏輯層 資料服務層 發送、接收結果 依請求至DB取得data Brower IE、Chrome Web Server Database Server HTTP PHP、 ODBC、 JDBC Application program Apache、ISS MySQL、 Oracle、SQL Server(微軟) EXE檔 展示層 商業邏輯層 資料服務層 發送、接收結果 依請求至DB取得data 存取data且回傳 e.g. 可透過xampp整合包來安裝

Database系統的優點 避免程式資料相依 完整性 安全性 避免data內嵌於程式中 實體完整性 參考完整性 值域完整性 確保資料的正確性 不會有兩筆重複的記錄 確保一致性 確保type相同 設定使用者帳號權限 驗證使用者帳號、密碼 使用VIEW

Database vs Data warehouse 使用對象 一般user 高階主管 資料特性 未整合 整合過 查詢方式 SQL語言 OLAP、Data mining (多維分析) Example 多少人買過筆電? 哪種人可能買筆電?

第一章 Database的規劃

規劃資料庫的方法 法一：ERD (畫圖) 法二：正規化 (畫表) Step1：繪出ERD(實體關係圖) Step2：ERD -> RDM(關聯資料模型) 法二：正規化 (畫表) Step1：給一個大資料表 Step2：對此資料表做正規化 (將大表切成小表)

資料庫的規劃 – 法一 法一：ERD (畫圖) Step1：繪出ERD(實體關係圖) Step2：ERD -> RDM(關聯資料模型) 法二：正規化 (畫表) Step1：給一個大資料表 Step2：對此資料表做正規化

Char1-1 ERM

Example ERD 轉換 RDM 學生(學號，姓名) 課程(課程，課號，課名) 選課(學號，課號，成績)

ERM(Entity Relationship Model) ERD = ERM的圖形表示法 = Entity(實體) + Relationship(關係) + Attribute(屬性) Entity(實體)： 強實體，ex:員工、專案 弱實體，ex:眷屬 (須依附在強實體上)

ERM(Entity Relationship Model) 參與 員工 專案 弱關係 (皆強實體) 擁有 員工 眷屬 強關係 (有弱實體) 參與 員工 專案 多重關係 管理

ERM(Entity Relationship Model) Attribute(屬性)： 單值屬性，ex: 職業 鍵值屬性 多值屬性，ex: 電話號碼 部份鍵 衍生屬性，ex: 年紀 Note: 弱實體的鍵值 = 部份鍵 +擁有者鍵值 生日 複合屬性 (可分割)，ex: 生日 年 月 日

基數比 1 1 員工 專案 一個員工只可參與一個專案 一個專案只可被一個員工參與 1 N 一個員工可參與多個專案 一個專案只可被一個員工參與 M 參與 N 一個員工可參與多個專案 一個專案可被多個員工參與 員工 專案

參與限制 參與 員工 專案 每個專案都要有人管理 員工 專案 (都要對出去)

Example 1 參與 N 員工 專案 一個員工可管理N個專案 一個專案只可被一個員工管理 且每個專案都須有人管理

Example 汽車保險公司要設計一個Database 客戶資料，含駕照號碼、姓名、生日、地址、電話，其中生日分別記載年、月、日，每個客戶電話不只一個，受保客戶需有車 汽車資料，含汽車牌照號碼、製造車廠、出廠年份 每個客戶擁有受保車子的開始日期 1個客戶可有多台受保車子，但每台車只能隸屬1位客戶 每次事故要記載事故編號、事故地點、日期 每次事故中，是由那位客戶駕駛，是哪台車發生事故，賠償金額 家屬資料，含家屬姓名、身份證號、年齡

Example 設計一個修課系統的Database 學生：含「姓名」、「學號」 課程：含「課號」、「課名」 每個學生可選修多門課 每個課程可被多個學生選修 每個選課會記錄成績

Soultion 法一：關係使用屬性 M N 學生 課程 學號 姓名 成績 課號 課名 法二：全部使用實體 1 N N 1 學生 選課 課程 擁有 N N 擁有 1 學生 選課 課程 學號 姓名 成績 學號 課號 課號 課名

Example 汽車保險公司要設計一個Database 客戶資料，含駕照號碼、姓名、生日、地址、電話，其中生日分別記載年、月、日，每個客戶電話不只一個，受保客戶需有車 汽車資料，含汽車牌照號碼、製造車廠、出廠年份 每個客戶擁有受保車子的開始日期 1個客戶可有多台受保車子，但每台車只能隸屬1位客戶 每次事故要記載事故編號、事故地點、日期 每次事故中，是由那位客戶駕駛，是哪台車發生事故，賠償金額 家屬資料，含家屬姓名、身份證號、年齡

Char1-2 ERD轉RDM

ERD轉RDM RDM(Relation Data Model) = 多個關聯綱要 = 多個Tables綱要

ERD轉RDM ERD -> RDM 學生(學號，姓名) 課程(課程，課號，課名) 選課(學號，課號，成績)

轉換的準則 實體 強實體 弱實體 多值屬性 建立一個新關聯 主鍵 = 候選鍵選其一 All屬性加入此關聯 主鍵 = 部份鍵 + 擁有者主鍵 多值不考慮 (另外建一個新關聯) 衍生不納入 複合取所有 弱實體 主鍵 = 部份鍵 + 擁有者主鍵 外鍵 = 擁有者主鍵 多值屬性 主鍵 = 多值屬性 + 原關聯主鍵

轉換的準則 關係 1:1 1:N M:N 任取一端主鍵(含關係屬性)加入另外一端當外鍵 將1端主鍵(含關係屬性)加至多端做外鍵 若一端完全參與，另一端部份參與 部份參與端主鍵(含關係屬性)加入完全參與端做外鍵 1:N 將1端主鍵(含關係屬性)加至多端做外鍵 M:N 獨立成一個新關聯(含關係屬性) 主鍵 = 兩端主鍵複合

Example 1:1 1 選課 1 學生 課程 學號 姓名 成績 課號 課名 學生( 學號，姓名，課號，成績) 課程( 課號，課名)

Example 1:1且遞迴 1 領導 學生 M 學號 姓名 學生( 學號，姓名，課號，成績，領導的學號) 別名

Example 1:N N 選課 1 學生 課程 學號 姓名 成績 課號 課名 學生( 學號，姓名，課號，成績) 課程( 課號，課名)

Example M:N M N 學生 課程 學號 姓名 成績 課號 課名 學生( 學號，姓名) 課程( 課號，課名) 選課 N 學生 課程 學號 姓名 成績 課號 課名 學生( 學號，姓名) 課程( 課號，課名) 選課( 學號，課號，成績)

Example 1 M 員工代號 部門代號 M 1 員工 部門 部門名稱 姓名 1 1 1 1 位置 M 員工數 M M N 眷屬 專案 領導 1 M 屬於 員工代號 部門代號 M 1 員工 部門 部門名稱 姓名 1 1 管理 1 1 位置 擁有 M 控制 起始日 員工數 M M 工作 N 眷屬 專案 姓名 性別 時數 專案代號 專案名稱

Solution 員工( 員工代號，姓名，部門代號，領導員工代號 ) 眷屬( 員工代號，姓名，性別 ) 部門( 部門代號，部名，員工代號，起始日 ) 專案( 專案代號，專案名稱，部門代號 ) 工作( 員工代號，專案名稱，時數 ) 位置( 部門代號，位置 )

資料庫的規劃– 法二 法一：ERD (畫圖) Step1：繪出ERD(實體關係圖) Step2：ERD -> RDM(關聯資料模型) 法二：正規化 (畫表) Step1：給一個大資料表 找出欄位之間的相依性 化簡成最小相依性 決定主鍵 Step2：對此資料表做正規化 1NF：去除複合屬性、多值屬性 2NF：去除部份相依 3NF：去除遞移相依

Char1-3 功能相依性

功能相依(Function Dependency) Def：給定一個關聯R X -> Y 若R的2個值組具有相同X值，必具相同Y值 e.g. 唸法：X決定Y or Y相依於X e.g. 學號決定姓名 學號 姓名 班級 001 Ken A 002 John B 003 下列FD可能成立： 學號->姓名、學號->班級 {學號，姓名}->班級、{學號、班級}->姓名

功能相依圖 若FD = { AB->D，AB->E，A->C，D->E }，且AB為主鍵 A B C D E 功能相依圖： A B C D E

三種功能相依 完全功能相依 部份功能相依 遞移功能相依 若主鍵由 個屬性所組成，而某個非主鍵屬性相依於主鍵全部屬性 若主鍵由 個屬性所組成，而某個非主鍵屬性相依於主鍵全部屬性 Ex: 令AB為主鍵，若AB->D (稱D完全相依於AB) 部份功能相依 若主鍵由 個屬性所組成，而某個非主鍵屬性相依於主鍵部份屬性 Ex:令AB為主鍵，若AB->C 且 A->C (稱C部份相依於AB) 遞移功能相依 若存在一個非主鍵屬性Z且X->Z，Z->Y，則Y遞移相依於X Ex:若AB->D且D->E (稱E遞移相依於AB)

功能相依的問題 更新異常 新增異常 刪除異常 更新資料時，須同步更新，否則有一致性問題 新增資料時，主鍵不可為空 刪除資料時，會把過多的資訊刪除

更新、新增、刪除異常 學生課程收費表 學號 課號 學分費 S001 C001 3000 S002 C002 4000 S003 S004 5000 刪除異常 無法刪除S002學生 (會刪除過多的資訊) 更新異常 無法僅更新此record的學分費 (需同步更新) 新增異常 無法僅新增一個課程 (需連同學號新增) 僅有一個大資料表，會存在更新、新增、刪除異常 null C004 3000

求最簡功能相依 Step1：右邊最簡 右邊不能有複合屬性 Step2：左邊最簡 左邊複合屬性去除多餘屬性 1、若AB->C且A->C 刪除AB->C 2、若BC->D且C->B 新增C->D且刪除BC->D Step3：FD最簡 利用遞移性刪除多餘FD

Example F = { A->BC，A->C，AB->C，C->B，BC->D } 求F最簡功能相依? (sol) step1：右邊最簡 A->B，A->C ，AB->C，C->B，BC->D step2：左邊最簡 A->B ，A->C ， C->B，C->D step3：FD最簡 A->C ， C->B，C->D

求候選鍵 Step1：最小性 1、將all屬性列出 2、對照右邊有的屬性從A中刪除 Step2：唯一性 求包=all屬性

Example 若關聯R具有下列相依關係 B->A，C->A，C->B 列出所有候選鍵? (sol) step1：最小性 列出all屬性 => {A，B，C}，刪除右邊 => C step2：唯一性 求包：{C}⁺ ≡ { C，A，B } ≡ R ∴ C為候選鍵

Char1-4 正規化

正規化 Def：將一個大的關聯綱要，分割成多個結構良好的 小關聯綱要之過程 目的：1、降低資料重覆 2、避免更新、新增、刪除異常 1NF 單一資料表 多個獨立相關的小資料表

1NF(First Normal Form) Def：屬性值必須是定義域內1個不可分割單元值 作法： 去除複合屬性 去除多值屬性 分解成多個簡單屬性 去除多值屬性 法一：獨立成新關聯 法二：存成多筆記錄 複合 多值 學號 姓名 生日 選修 S001 ken 88.08.08 國文 英文 S002 joe 99.09.09 學號 姓名 年 月 日 選修 S001 ken 88 08 國文 英文 S002 joe 99 09 1NF

2NF(Second Normal Form) Def：滿足1NF且每個非主鍵屬性完全功能相依於主鍵 作法： 去除部份相依 部份相依屬性獨立成新關聯

2NF(Second Normal Form) 1、找出相依性 學號 -> 姓名，年，月，日 {學號，選修} -> 姓名、年，月，日 2、決定主鍵 {學號，選修} 3、畫出相依圖 學號 姓名 年 月 日 選修 S001 ken 88 08 國文 英文 S002 joe 99 09 去除部份相依 學號 選修 S001 國文 英文 S002 學號 姓名 年 月 日 S001 ken 88 08 S002 joe 99 09

3NF(Third Normal Form) Def：滿足2NF且每個非主鍵屬性沒有遞移相依於主鍵 作法： 去除遞移相依 遞移相依屬性獨立成新關聯 找法：看是否存在與主鍵無關的相依性 ∵不存在遞移相依 ∴2NF即為最後結果 ∴得R1(學號，選修) R2(學號，姓名，年，月，日) 3NF

BCNF Def：符合3NF且所有決定因子皆為超級鍵 BCNF R1(學號，選修) ∵所有決定因子皆為超級鍵 R2(學號，姓名，年，月，日)

Example 若有一個關聯綱要如下： R(學號，課程，教室，容納入數，成績) 對R做正規化至3NF

Solution R 一、1NF： 去除複合屬性、多值屬性 ∵R不存在複合屬性、多值屬性 ∴R已符合1NF 二、2NF： 去除部份相依 1、找出相依性 ∵R(學號，課程，教室，容納人數，成績) ∴{學號，課程}->成績 課程->教室，容納人數 教室->容納人數 2、找出主鍵 (1) 最小性：學號，課程，教室，容納人數，成績 (2) 唯一性：{學號，課程}⁺ ≡ { 學號，課程，成績，教室，容納人數 } ≡ R ∴ {學號，課程}為主鍵 3、畫出功能相依圖 & 去除部份相依 ∵課程->教室，容納人數 ∴R1(課程，教室，容納人數) R2(學號，課程，成績) R 學號 課程 教室 容納人數 成績

Solution R1 R2 R3 R4 三、3NF： 去除遞移相依 ∵教室->容納人數 ∴切割R1可得 課程 教室 容納人數 四、BCNF：所有決定因子皆為超級鍵 ∵R2、R3、R4的決定因子皆為超級鍵 ∴亦符合BCNF R1 課程 教室 容納人數 切割 R2 學號 課程 成績 R3 教室 容納人數 課程 教室 R4 ∴R經過1~3NF、BCNF之後會被切割成R2、R3、R4

Char1-5 關聯式代數

關聯式代數(Relational Algebra)

第二章 Database的建置

資料的六個層級 位元(bit) = 0 or 1 位元組(byte) = bits 欄位(field) = bytes Size 小 大 位元(bit) = 0 or 1 位元組(byte) = bits 欄位(field) = bytes 記錄(record) = fields 檔案(file) = table = records 資料庫(database) = files = tables 由多個欄位組成 由多個tables組成

Example 學生資料表共5筆記錄

名詞介紹 SQL 關聯模型 Table (資料表) Relation (關聯) Record (記錄) Tuple (值組) Field (欄位) Attribute (屬性) Header Degree=3 (3個欄位) [資料表] Field 1 Field 2 Field 3 Record Body Cardinality=3 (3個記錄)

Example Record

關聯式Database Def：由一些正規化的表格所組成， 並以外鍵定義表格間的關聯 課程資料 課號 課名 C1 國文 C2 數學 學生資料 學號 姓名 S1 Dar S2 Ken 學生選修課程 學號 課程 S1 C1 C2 S2 外鍵 誰是外鍵???

關聯式Database 關聯鍵 外鍵(Foreign key) 超級鍵(super key) 可識別值組唯一性的屬性集合 Ex: {課號, 課名}、{課號} 候選鍵(candidate key) = 最小超級鍵 為主鍵的候選鍵，最小且唯一 Ex: {課號} 主鍵(Primary key) 從候選鍵裡面找一個當主鍵，其它為次要鍵(代替鍵) 外鍵(Foreign key) 一個關聯中屬性的集合，此集合在其它關聯中份演主鍵的角色 course 課號 課名 C1 國文 C2 數學 C3 course 課號 課名 C1 國文 C2 數學 scourse 學號 課號 S1 C1 C2 ∴scourse.課號為外鍵 外鍵

完整性限制 關聯式DB須滿足完整性限制 實體完整性限制 參考完整性限制 值域完整性限制 每個值組具唯一性，即關聯須具主鍵，主鍵不可為空、不可重複 參考完整性限制 同一行的資料欄位值一定存在於其它的關聯中的資料欄位值，即外鍵適情況可為空(null)，若不空則父關聯主鍵需有對應值 值域完整性限制 關聯的同一行中data type要相同 三個含義 1、unknown 2、missing 3、N/A

完整性限制 course 課號 課名 C1 國文 C2 數學 scourse 學號 課程 S1 C1 C2 S2 實體完整性限制 值域完整性限制 外鍵 參考完整性限制 Note：scourse - 課程為外鍵

DBMS (Database Management System) Def：一種通用型軟體，用於定義、建構、 處理、分享、保護、長期維護Database e.g. MySQL、Oracle、SQL Server SQL語法 DDL：建立DB ex：CREATE、DROP、ALTER DML：存取DB ex：INSERT、SELECT、UPDATE、DELETE DCL：控制DB存取權限 ex：GRANT、REVOKE、DENY

Char2-1 MySQL安裝

Database實體架構 PHP HTTP 展示層 商業邏輯層 資料服務層 發送、接收結果 依請求至DB取得data 存取data且回傳 Brower PHP HTTP IE、Chrome Web Server Database Server Application program Apache、ISS MySQL、 Oracle、SQL Server(微軟) EXE檔 展示層 商業邏輯層 資料服務層 發送、接收結果 依請求至DB取得data 存取data且回傳 e.g. 可透過xampp整合包來安裝

XAMPP XAMPP 1、MySQL 2、Oracle 3、SQL Server DBMS MySql Database Apache PHP

XAMPP軟體下載 https://www.apachefriends.org/zh_tw/index.html 下載後點擊安裝

開啟XAMPP control Panel 將Apache、MySQL啟動

使用瀏灠器進入phpMyAdmin [法一] 在瀏灠器網址處輸入：127.0.0.1 2、進入phpMyAdmin [法二] http://127.0.0.1/phpmyadmin

phpMyAdmin介面 操作 系統資訊 資料庫

設定phpMyAdmin帳密 留一個root帳號、一個新增帳號

設定config.inc.php檔 目的：改成須帳號密碼才可登入 路徑：C:\xampp\phpMyAdmin\config.inc.php 設定下列參數

設定httpd.conf檔 目的：改成不限區網才可存取 路徑： C:\xampp\apache\conf\extra\httpd-xampp.conf 新增下列語句： <Directory "C:/xampp/phpMyAdmin"> AllowOverride AuthConfig #Require local Allow from all Require all granted ErrorDocument 403 /error/XAMPP_FORBIDDEN.html.var </Directory>

重新登入 Step1：先登出並關掉瀏灠器 Step2：輸入你電腦的固定IP ( ex: 140.128.x.x or 120.110.x.x ) Step3：確認是否會出現帳密彈跳視窗 登出

練習 利用MySQL介面操作方式建立一個資料庫具下列兩個tables， 並設定主鍵 Course SCourse Student

Step1 - 新增一個資料庫 1、 2、設定DB名稱 3、

Step2 - 建立資料表 2 3 設定資料表schema 1、點選剛剛所建立的DB並新增資料表 2、給定資料表名稱 3、設定欄位 1 (1) 名稱 (2) 型態 (3) 長度 (4) 索引(設定主鍵)

Step3 - 查看資料表的schema 2、 主鍵 1、選擇資料表

Step4 - 新增值組 1、 2、

Step5 - 查看值組 2、 1、選擇資料表 值組 需設定主鍵才會出現

SQL指令查詢 找出學生姓名為john的修課資訊 SELECT * FROM student,scourse,course WHERE student.sid=scourse.sid and scourse.cid=course.cid and student.sname='john'; 執行結果：

主鍵的目的 目的： 確保資料的實體完整性(Entity Integrity)。 避免有兩個相同的record(tuple) course 課號 課名 C1 國文 C2

外鍵的目的 目的： 確保資料的參考完整性(Referential Integrity)。 外鍵適情況可為空，若不空則父關聯主鍵須有對應值 (解決一致性問題) course 課號 課名 C1 國文 C2 數學 scourse 學號 課號 S1 C1 C2 1、不能刪：RESTRICT 2、一起刪：CASCADE 外鍵 ∵若刪除此值組會導致「scourse」的C1沒有對應值

練習 利用MySQL介面操作方式建立一個資料庫具下列三個tables， 並設定主鍵、外鍵 外鍵

Step1 – 建立表格 建立course table、student table並且給予主鍵 course student

Step2 -建立表格 建立scourse table並給予主鍵、外鍵， 其中主鍵為複合主鍵， cid為外鍵參考course.cid sid為外鍵參考student.sid scourse 主鍵黃色 外鍵灰色

外鍵設定方式 先將scourse table的cid、sid分別設成索引 2 會出現灰key 1 3、點擊「索引」

再對scourse table的cid、sid設定參考對象 1 4、設定cid參考course.cid 設定sid參考student.sid 3、設成CASCADE 或 RESTRICT 2 CASCADE ： 同步刪除或修改 RESTRICT ： 禁止刪除或修改

複合主鍵設定方式 將scourse table的cid、sid設為複合主鍵 1 選擇table 2 先選cid當主鍵 6 4 5

cid、sid為主鍵、外鍵

Step3 – 新增值組 新增值組至course、student、scourse tables中 sourse scourse

Step4 – 刪除測試 用下列SQL指令測試表格是否會同步刪除 1、刪除course表格中的cid=‘c001’的值組 2、檢查scourse表格是否cid=‘c001’的值組也會同步被刪除 scourse course 同步刪除

Step5 – 更新測試 用下列SQL指令測試表格是否會同步刪除 1、更新course表格中的cid=‘c001’的值組，改成cid=‘c006’ 2、檢查course、scourse表格是否cid=‘c001’的值組也會同步更新成cid=‘c006’ scourse course 檢查是否也會更正成’c006’ 更新 將’c001’改成‘c006’

Char2-2 SQL語法

SQL的語法 DDL(資料定義語言) Create、Drop、Alter DML(資料操作語言) Insert、Update、Delete 、 SELECT DCL(資料控制語言) Grant、Revoke、Alter password 定義、操作、控制

DDL (Data Definition Language) 新增資料表：CREATE TABLE CREATE TABLE 資料表名稱( 欄位名稱1 欄位型態, 欄位名稱2 欄位型態, PRIMARY KEY(欄位名稱), FOREIGN KEY(欄位名稱) REFERENCES 資料表名稱(欄位名稱) ON DELETE RESTRICT ON UPDATE CASCADE);

DML (Data Manipulation Language) 插入值組：INSERT INTO INSERT INTO 資料表名稱 VALUES (‘欄位1資料’，‘欄位2資料’，…..)， …. ;

練習 1、利用MySQL指令方式，建立出下列Course表格，各欄位屬性如下： cid: int(20) cname、crdeit、teacher: varchar(20) 其中cid為主鍵 2、利用MySQL指令方式，插入值組至Course表格 (值組如下所示) Course

MySQL Query Browser下載 下載免安裝版

連線到你的MySQL

3、執行SQL指令 2、撰寫 SQL指令 4、查詢結果 1、點選 資料庫

練習 利用MySQL指令方式建立一個資料庫具下列三個tables， 並設定主鍵、外鍵

Step1 - 建立表格 建立student表格 建立course表格 建立scourse表格 設成同步更新或刪除

Step2 - 插入值組 插入值組至student表格 插入值組至course表格 插入值組至scourse表格

Step3 – 查看表格 course 2、執行 3、表格內容 1、點兩下 stduent

scourse

Step4 – 刪除測試 用下列SQL指令測試表格是否會同步刪除 1、刪除course表格中的cid=‘c001’的值組 2、檢查scourse表格是否cid=‘c001’的值組也會同步被刪除 scourse course 同步刪除

Step5 – 更新測試 用下列SQL指令測試表格是否會同步刪除 1、更新course表格中的cid=‘c001’的值組，改成cid=‘c006’ 2、檢查course、scourse表格是否cid=‘c001’的值組也會同步更新成cid=‘c006’ scourse course 檢查是否也會更正成’c006’ 更新 將’c001’改成‘c006’

練習 利用MySQL指令建立出下列三個表格，並插入一筆值組，規格如下： 1、各欄位資料型態除了credit、phone為int，其它皆為varchar 2、各欄位資料長度皆不可大於20 3、cid、sid為外鍵，分別參考course-cid、student-sid， 並且皆須設為同步刪除、同步更新 [測試] 刪除course表格值組，則scourse表格值組也須同步被刪除 [測試] 更新course表格值組，則scourse表格值組也須同步被更新

解答

第三章 Database的查詢

SQL Fiddle SQL Fiddle 一個線上學習與測試多種資料庫 SQL 語法的網站 http://sqlfiddle.com/ 目前有支援下列資料庫： 1、MySQL 2、Oracle 11g R2 3、PostgreSQL SQLite (WebSQL) 4、SQLite 5、MS SQL Server

SQL Fiddle

SQL Fiddle 1、 3、 2、 4、

SQL Fiddle

Char 3-1 SQL基礎查詢

DQL (Data Query Language) 語法 執行順序 SELECT 屬性 or 聚合函數 5 FROM 表格 1 WHERE 篩選條件 2 GROUP BY 分群屬性 3 HAVING 群組篩選 4 ORDER BY 屬性 6 一定要出現 e.g. 6 1 2 3 4 5 SQL指令最後要以分號做結尾

印出表格內的所有值組 SQL指令： *代表所有欄位的意思

篩選條件 常用於WHERE，可搭配AND、OR、NOT使用 常見運算子 = < > != <= >= !> !< 其它運算子 IN BETWEEN LIKE e.g. IN(存在於) e.g. BETWEEN(介於) e.g. LIKE(包含) 常用在子查詢 查詢姓名是j開頭

e.g. 查詢姓名有a e.g. 查詢姓名第二個字母是a

練習 查詢1：列出學生之中「男生」且「年紀小於等於20歲」的「所有欄位」 建立一個student資料表如下 Data type：age為INT、其它為varchar 查詢1：列出學生之中「男生」且「年紀小於等於20歲」的「所有欄位」 查詢2：列出學生之中年紀介於等於18歲~20歲且性別為女生的「姓名」

解答 查詢1：列出學生之中「男生」且「年紀小於等於20歲」的所有資訊

解答 查詢2：列出學生之中年紀介於等於18歲~20歲且性別為女生的姓名

練習 查詢1：查詢學生之中名字的開頭不是j，並且為女生，列出「所有欄位」 建立一個student資料表如下，其中 Data type：age為INT、其它為varchar 查詢1：查詢學生之中名字的開頭不是j，並且為女生，列出「所有欄位」 查詢2：查詢學生之中名字的第二個字母是’a’，並且為學號大於’s004’， 列出「學號」、「姓名」 查詢3：查詢學生之中名字的最後的字母是’n’，並且年紀介於18~20之間， 列出「所有欄位」

解答 查詢1：查詢學生之中名字的開頭不是j，並且為女生，列出「所有欄位」 查詢2：查詢學生之中名字的第二個字母是’a’，並且為學號大於’s004’， 列出「學號」、「姓名」 查詢3：查詢學生之中名字的最後的字母是’n’，並且年紀介於18~20之間， 列出「所有欄位」

聚合函數(Aggregate function) 聚合函數：內建的統計函數共有5種 1. COUNT 2. SUM 3. AVG 4. MIN 5. MAX ：計算值組的數量 ：計算數值的總和 ：計算數值的平均值 ：取得最小值 ：取得最大值 Note: 聚合函數常搭配分群使用

MAX e.g. 列出學生中最大的年紀 e.g. 列出學生中姓名、最大的年紀 MySQL：結果錯誤 Oracle： 編譯錯誤 SELECT中只能單獨用聚合函數 WHERE中不可直接用聚合函數 (除非用子查詢)

MAX e.g. 分別列出男生和女生年紀最大的學號、姓名、年紀、性別

練習 若有一個「student」表格如下 1、將男學生年紀由大到小排列，列出「姓名」、「性別」、「年紀」 2、查詢學生中年紀最大者，列出「姓名」、「性別」、「年紀」 3、查詢姓名j開頭且年紀最大者，列出「所有資訊」

解答 1、將男學生年紀由大到小排列，列出「姓名」、「性別」、「年紀」 2、查詢學生中年紀最大者，列出「姓名」、「性別」、「年紀」 3、查詢姓名j開頭且年紀最大者，列出「所有資訊」

COUNT e.g. 查詢學生之中「男生」且「年紀小於等於20歲」的人數 e.g. 查詢學生之中姓名的第二個字元為a的所有學生之平均年紀、總和年紀

AVG、SUM e.g. 查詢男生的平均年紀，列出「性別」、「平均年紀」 e.g. 查詢男生、女生的平均年紀 ，「列出性別」、「平均年紀」 20 e.g. 查詢男生、女生的平均年紀 ，「列出性別」、「平均年紀」 20 18.5 e.g. 查詢學生的姓名中存在e字元的所有學生年紀總和 「列出年紀」

練習 若有一個「student」表格如下 1、列出學生之中「男生」且「年紀小於等於20歲」的人數 2、列出學生之中姓名的第二個字元為a的所有學生之平均年紀、總和年紀 3、列出男生、女生的平均年紀 4、查詢學生的姓名中存在e字元的所有學生年紀總和 (列出年紀)

分群(group) Def：對表格中的值組做分組 語法： 使用GROUP BY，並且須搭配聚合函數使用 對學號做分群 分成3群 scourse(選課) 語法： 使用GROUP BY，並且須搭配聚合函數使用 ( Ex: COUNT、AVG、SUM )

分群(group) EX: 每個學生修了多少課 分成5群 依sid分群 Scourse (選課)

分群(group) EX: 每個課有多少學生修 依cid分群 Scourse (選課)

題目 e.g. 分別列出男生和女生的年紀最大之「性別」、「年紀」 e.g. 列出住在台中的學生之「學號」、「姓名」、「地區」 若人數小於等於1人則不列出

解答 e.g. 分別列出男生和女生的年紀最大之「性別」、「年紀」 e.g. 列出住在台中的學生之「學號」、「姓名」、「地區」 student e.g. 分別列出男生和女生的年紀最大之「性別」、「年紀」 e.g. 列出住在台中的學生之「學號」、「姓名」、「地區」 e.g. 列出學生住在各地區的人數之「地區」、「人數」， 若人數小於等於1人則不列出

自然合併(NATURAL JOIN) Def：自動合拼相同欄位名稱 EX: (MySQL 由左至右做合併) student 學號 姓名 S1 Ken S2 Mia scourse 學號 課號 S1 C1 C2 S2 合併後的虛擬表格 學號 姓名 課號 S1 Ken C1 C2 S2 Mia

練習 有三個表格如下，其中SCourse表格的學號、課號為外鍵 1、查詢所有有修課的同學，列出「學號」、「姓名」、「課號」、「課名」 2、查詢所有修課同學的修課數，列出「學號」、「姓名」、「修課數」 3、查詢有修JAVA此課程之「修課人數」，並將「修課人數」別名「student_count」， 列出「課程名稱」、「 student_count 」 SCourse Course Student

解答 1、 2、 3、

等位合併 Def：若存在兩個表格T1、T2， T1的外鍵參考T2的主鍵，且欄位名稱不相同 EX： 用等位合併 T2 課號 課名 C1 國文 數學 T1 學號 課程 S1 C1 C2 S2 外鍵

等位合併 EX：列出兩個表格合併後的所有值組 course 課號 課名 C1 國文 C2 數學 scourse 學號 課程 S1 C1 C2 合併的欄位名稱不同，所以用等位合併

EXISTS Def: 用於檢查子查詢是否至少會返回一筆資料 student take_course sid sname S001 John S002 Mia S003 Jessica S004 Alpha S005 Dar S006 Tiffany S007 Eason sid cid score S001 C001 90 C002 100 S002 85 C003 S003 55 70 80 C004 C005 99 S004 S005 88 S006 77 S007 34 例如： student(sid,sname) take_course(sid,cid,score) 找出沒被當過的學生

Example – 課程資料庫 有5個資料表如下： student teacher course take_course offer sid sname S001 John S002 Mia S003 Jessica S004 Alpha S005 Dar S006 Tiffany S007 Eason tid tname T001 Jeff T002 Hugo cid cname credit C001 English 1 C002 Math 3 C003 C++ 2 C004 Java C005 Logic sid cid score S001 C001 90 C002 100 S002 85 C003 S003 55 70 80 C004 C005 99 S004 S005 88 S006 77 S007 34 tid tname T001 Jeff T002 Hugo

[查詢：修過某位老師剛好兩門課，列出學生姓名、老師姓名]

[查詢：從沒被當過(所有課程score>=60)，列出學生姓名]

[查詢：列出每個學生的姓名、總學分數、平均成績]

Char 3-2 SQL進階查詢

Oracle語法 Oracle 不支援ON UPDATE功能

集合運算 聯集：「UNION」 A集合 B集合 A UNION B 差集：「MINUS」 A集合 B集合 A MINUS B

Example course1 course2 將兩個表格相減

練習 一、利用Oracle指令方式建立一個資料庫具下列三個tables， 並設定主鍵、外鍵 course student csourse

解答 利用Oracle指令方式建立student、course、scourse三個表格 寫在這 Oracle複合主鍵的寫法 sc_pk為複合主鍵的新名稱，可隨便命名 Oracle複合主鍵的寫法 Oraclec外鍵的寫法

Note: 所有的指令皆需寫在一個區塊並一起送出，否則DB會被刪除 插入值組至三個表格 一個一個插入不然會有error INSERT INOT在SQL Fiddle只能一個一個插入 Note: 所有的指令皆需寫在一個區塊並一起送出，否則DB會被刪除

練習 查詢1： 修過jeff老師所有課的同學之「學號」「姓名」 查詢2： 所有授課課程皆為3學分的老師之「老師姓名」

解答 查詢1： 修過jeff老師所有課的同學之「學號」「姓名」 查詢2：所有授課課程皆為3學分的老師之「老師姓名」

練習 一、 插入下列值組 二、 查詢1： 曾租過all紅色船的「船員姓名」 查詢2： 同一天中租超過一艘船(不含一艘)的「船員姓名」 建立一個水手租船的資料庫，此資料庫的schema如下： Sailor( sid, sname) Boat( bid, bname, color ) Reserve( sid, bid, day ) 一、 插入下列值組 二、 查詢1： 曾租過all紅色船的「船員姓名」 查詢2： 同一天中租超過一艘船(不含一艘)的「船員姓名」

解答 一、建立表格且插入值組

二、 查詢1： 曾租過all紅色船的「船員姓名」 查詢2： 同一天中租超過一艘船(不含一艘)的「船員姓名」

Char 3-3 遞迴查詢

Example 有一個表格如下： 查詢Ken的經理，其經理編號、經理姓名? 員工編號 員工姓名 經理編號 E001 Jeff E002 Bird E003 Ken E004 Verson 查詢Ken的經理，其經理編號、經理姓名? e1 e2 員工編號 員工姓名 經理編號 E001 Jeff E002 Bird E003 Ken E004 Verson 員工編號 員工姓名 經理編號 E001 Jeff E002 Bird E003 Ken E004 Verson 員工編號 員工姓名 經理編號 經理姓名 E001 Jeff E002 Bird E003 Ken E004 Verson 合併

練習 一、建立出下列顧客表格 (sid表示介紹人編號) 二、1、查詢由mia介紹的顧客，其顧客編號、姓名 2、查詢沒有介紹人的顧客，其顧客編號、姓名

解答 一、建立出下列顧客表格 (sid表示介紹人編號) 二、1、查詢由mia介紹的顧客，其顧客編號、姓名 2、查詢沒有介紹人的顧客，其顧客編號、姓名

Char 3-4 VIEW

兩層式綱要 應用程式內嵌SQL 網頁內嵌SQL 欄位名稱須一致 外部層 缺點： 當資料表綱要的欄位名稱修改， 內部層 則all外部程式都需修改 內部層

三層式綱要 應用程式內嵌SQL 網頁內嵌SQL 外部層 概念層 內部層 欄位名稱不須一致 需自己寫mapping程式 (寫view) DBMS會自動mapping (資料的儲存方式、索引方式) 欄位與欄位之間需建立mapping 內部層

ANSI SPARC三層式綱要 User1 User2 User3 (View層) 外部層 外部層 外部層 個別使用者觀點 邏輯資料獨立(難) 概念層 全部使用者觀點 實體資料獨立(易) 內部儲存方式觀點 內部層 目的：實現資料獨立、資訊隱藏 (1) 邏輯資料獨立：修改概念綱要時，不用修改到上層的外部綱要 (難，∵需自己寫轉換程式) (2) 實體資料獨立：修改內部綱要時，不用修改到上層的概念綱要、外部綱要 (易，∵DBMS自動會轉換)

VIEW(視界) Def：由其它表格所衍生的虛擬表格 語法：如同C語言的Macro Ex：建立一個view 可查詢每個學生的平均成績

練習 有三個表格如下：Scourse表格的學號、課號為外鍵 1、建立一個VIEW， 此VIEW可查詢同學之「學號」「修課數」 2、列出有修課同學之「學號」「姓名」「修課數」 3、對於學分>1且修課人數>1的課， 列出「課號」「課名」「成績>=60分的人數」 Scourse(修課) Course(課程) Student(學生)

解答 1、 2、[寫法一] 不使用子查詢 [寫法二] 使用子查詢 3、

成果驗收 1、是否可登入「 http://127.0.0.1/phpmyadmin 」 (10%) 1、是否可登入「 http://127.0.0.1/phpmyadmin 」 (10%) 2、是否可登入「 http://實體IP/phpmyadmin 」 (10%) 如： http://120.110.16.xx/phpmyadmin 3、登入時是否會出現須輸入帳號密碼的彈跳視窗， (20%) 並且輸入正確的帳號密碼後可以順利登入

4、建立一個「選課資料庫」，內含五個資料表如下，名稱加底線為主鍵： 查詢：列出5個表格合併後的所欄位(如下) (50%) teacher course offer student take_course 查詢：列出每個學生的姓名、總學分數、平均成績(如下) (10%) 「總學分數」別名成sum_credit，「平均成績」別名成avg_score

solution 查詢：列出5個表格合併後的所欄位 查詢：列出每個學生的姓名、總學分數、平均成績(如下) (20%) select sid,sname,cid,cname,score,tid,tname,credit from offer natural join teacher natural join course natural join take_course natural join student; 查詢：列出每個學生的姓名、總學分數、平均成績(如下) (20%) select sname,sum_credit,avg_score from (select sid,sum(credit) as sum_credit,avg(score) as avg_score from student natural join take_course natural join course group by sid) as T1 natural join student;