資料庫系統 Database Systems

Presentation on theme: "資料庫系統 Database Systems"— Presentation transcript:

1 資料庫系統 Database Systems
Introduction 主講人:劉佳灝 資料庫系統

2 相關名詞 資料庫 (database) 集合的特性 資料庫系統 資料庫管理系統 (database management systems)

3 資料庫 資料庫(DB) 資料庫是相關資料的集合 集合的特性 集合中的元素沒有順序性 集合中的元素不會重複 集合可以是空的 資料庫系統

4 資料庫管理系統 資料庫管理系統 (DBMS)是許多程式的集合，讓使用者得以定義、建構，與處理資料庫 定義:資料的型態、結構與限制
處理:查詢、更新 資料庫系統

5 使用者 SQL查詢 / 應用程式 資料庫系統 資料庫管理系統 儲存 綱要 儲存 資料庫 資料庫系統

6 資料庫系統 Database Systems
Database System Concepts 資料庫系統

7 簡介 資料模型(Data Model) 是提供資料抽象化的主要工具 Data Model 是用來描述資料庫結構的一種概念
結構的表示方式 限制 運算(操作) 資料庫系統

8 資料模型 Data Model Data Model使用的概念 實體 Entity 屬性 Attribute 關係 Relationship
商品 商品編號 商品名稱 尺寸 顏色 價格 客戶編號 客戶姓名 電話 購買 客戶 時間 訂購量 資料庫系統

9 關聯式資料模型概念 Relational Model Concepts
關聯式模型將資料庫表示成一個關聯的集合 相關名詞 關聯(Relation) 值組(Tuple) 屬性(Attribute) 定義域(Domain) 資料庫系統

10 Relation Schemas – 名詞簡介
主鍵 關聯表名稱 PRODUCT Pid Pname Size Color Price 11 襯衫 L 藍 100 22 帽子 M 200 13 夾克 S 紅 300 34 牛仔褲 黑 400 屬性集合 值組 價格資料 品號屬性 品名屬性 尺寸屬性 顏色屬性 價格屬性 資料庫系統

11 Relation Schemas – 特性與內容
關聯表的屬性數目稱為它的維度 一個關聯表中任兩個屬性名稱不可以重覆 「關聯表綱要」(Relation Schema) 包含: 關聯表名稱 屬性集宣告 所有屬性的相對資料型態宣告 主鍵的宣告 外來鍵 (Foreign Key) 之宣告 資料庫系統

12 DBMS的架構與資料獨立 Three Schema (三綱要架構) 資料獨立 外層(景觀層) External Level:外部綱要
概念層 Conceptual Level:慨念綱要 內層 Internal Level:內部綱要 資料獨立 邏輯資料獨立 實體資料獨立 資料庫系統

13 Three Schema 外層(又稱為景觀層) External Level:外部綱要 描述特定使用者所感興趣的資料庫部分
隱藏儲存結構等其他細節 概念層 Conceptual Level:慨念綱要 描述整個資料庫的結構 隱藏儲存等細節 著重在實體、資料型態、關係、使用者的操作與限制 內層Internal Level:內部綱要 用來描述資料庫的實際儲存結構與路徑 資料庫系統

14 Data Independence 資料獨立
Logical data independence (邏輯資料獨立) 改變 conceptual schema 時,不需改變 external schema (or application) Physical data independence (實體資料獨立) 改變 internal schema 時,不需改變 conceptual schema 資料庫系統

15 SQL DDL 資料定義語言 DML 資料處理語言 用來定義 concept schema
與資料庫綱要之定義有關的操作,如資料庫的建立、表格的建立等命令 DML 資料處理語言 對資料庫資料進行擷取、新增、刪除與修改等處理之命令 僅對資料有所影響,與資料庫綱要定義無關 資料庫系統

16 資料庫系統 Database Systems
Modeling Using the Entity-Relationship Approach 資料庫系統

17 屬性型態 簡單(Simple) 複合(Composite) 單值(Single-valued) 多值(Multi-valued)
儲存(Stored) 導出(Derived) 資料庫系統

18 屬性型態 簡單(simple) 與 複合(composite) 姓 名 地址 員工編號 姓名 電話 生日 員工 薪資 資料庫系統

19 應該是簡單還是複合屬性? 地址 縣市別 里隣 路別 郵遞區號 里別 隣號 資料庫系統

20 屬性型態 單值(single-valued) 與多值(multi-valued)
Ex:一個部門只有一個部門編號與部門名稱,某些部門可能存同時在兩個地點(例如:一個部門分別在 台中 與 彰化 各有據點) 名稱 地點 部門編號 部門 資料庫系統

21 屬性型態 儲存(stored) 與 導出(derived) Ex:年紀可以經由生日換算得到 姓 名 地址 員工編號 姓名 電話 生日 員工
薪資 年紀 資料庫系統

22 屬性型態 (範例二) Ex:一個客戶的交易次數可以經由訂購紀錄中計算出來 姓名 名稱 編號 編號 客戶 訂購 商品 價格 電話 日期 交易量
資料庫系統

23 鍵值屬性 一個具有唯一性的屬性 假設: 每一個客戶的編號會唯一 每一個商品的編號會唯一,且每一商品的名稱也會唯一 姓名 名稱 編號 編號
訂購 商品 價格 電話 日期 交易量 交易次數 資料庫系統

24 實體,關係,屬性 實體型態 關係型態 屬性可以用來描述實體或關係 具有相同屬性集合的元素所形成的集合 用來連接某些相關的實體型態 編號 學號
學生 名稱 選修 課程 姓名 教師 日期 成績 性別 資料庫系統

25 關係 關係型態的Degree是參與此關係的實體型態數目 Ex:Works_for的Degree為2,又稱二元關係 Relationship
employee Works for Department 資料庫系統

26 關係型態的限制 Constraints on Relationship Types 基數率 參與 基數率與參與 一個實體所能參與的關係案例數
Ex:一個部門僅會有一個主管 參與 Ex:任何一個部門一定有一個主管(去管理它) 資料庫系統

27 基數率(cardinality ratio)
1:1的範例 員工 部門 管理 e1 e2 e3 e4 r1 r2 r3 d1 d2 d3 資料庫系統

28 基數率 1:N的範例 員工 部門 屬於 e1 e2 e3 e4 r1 r2 r3 r4 d1 d2 d3 資料庫系統

29 基數率 N:M的範例 員工 專案 參與 e1 e2 e3 e4 r1 r2 r3 r4 r5 r6 P1 p2 p3 資料庫系統

30 參與限制 實體的存在是否依靠關係型態與另一實體產生關聯來決定 部份參與 (以單線表示) 全部參與 (以雙線表示) 員工 部門 管理 部份參與
資料庫系統

31 參與限制 (範例二) 課程實體的存在是否依靠開設關係型態與另一教師實體產生關聯來決定
課程實體要存在必須所對應的教師實體與開設關係都存在,該課程才會存在 任何一個課程一定要有教師去開設它 教師 部份參與 全部參與 課程 開設 資料庫系統

32 弱實體型態(Weak Entity Type)
Weak Entity Types 本身可以沒有任何的鍵值屬性 透過另一實體型態的一些屬性所組成的相關特定實體(Identifying Owner)來辨認 辨認關係(Identifying Relationship) Weak Entity在沒有Identifying Owner 的狀況下是無法辨認的,所以Weak Entity 總是有一個完全參與的限制來與它的辨認關係產生關聯 資料庫系統

33 弱實體型態(Weak Entity Type)
員工編號 員工 Identifying relationship 姓名 Weak entity Identifying owner 全部參與 生日 眷屬 擁有 性別 關係 資料庫系統

34 適當名稱 大寫字母表示實體與關係型態 屬性名稱字首大寫 實體多使用名詞 關係則採用動詞 方向：由左到右,由上到下 資料庫系統

35 範例:讀者借閱書籍 系統必須紀錄讀者的身份證號碼、姓名、生日,與聯絡方式,其中聯絡方式包含了電話與地址資料
系統紀錄了館藏中每一本書籍的書名與作者, 雖然有些書籍有兩個以上的作者,但一定都只有一個唯一書號. 為了掌握每本書的熱門程度,系統必須提供每本書籍的曾經被借閱的次數,以提供讀者參考 當一個讀者要借閱書籍時,系統必須紀錄借閱的日期,以便管理. 而每一位讀者至少都有借閱過一次以上書籍的紀錄 資料庫系統

36 範例:讀者借閱書籍 身份證號碼 書號 書名 姓名 讀者 書籍 借閱 生日 作者 作者 日期 聯絡方式 借閱次數 電話 地址 資料庫系統

37 資料庫系統 Database Systems
Relational Database Constraints 資料庫系統

38 關聯式資料模型的限制 定義域限制 鍵值限制 實體完整性限制 參考完整性限制 資料庫系統

39 屬性與定義域(Domain) 欄位中所允許的數值(Value)資料稱為定義域(Domain) 定義域D是基元(Atomic)數值資料的集合
Dom(Sex)={男生,女生} 則”男生” 與 ”女生” 皆為sex屬性的的合理基元值 Ex: age:大於0的整數 ID_numbers:一個英文字母加上九位數字的集合 年級:介於1~4之間的數值 資料庫系統

40 定義域限制 定義域限制指定了一屬性A的值必須在該屬性值的定義域Dom(A)裡的一個基元值 定義方式 列舉 範圍 格式 資料型態 資料庫系統

41 鍵值限制 ≠ 關聯可視為一個集合,所含值組必須相異 t1[SK] t2[SK] SK:超鍵 (superkey) 每個關聯至少存在一個SK
資料庫系統

42 包含SK的屬性集合亦為SK 張大森 s0023 047111111 男生 22 陳小東 s0052 047112235 女生 25 林中正
Name Sid Tel_num Sex Age 張大森 s0023 男生 22 陳小東 s0052 女生 25 林中正 s0043 23 Ex: Sid是一SK,所以包含Sid的屬性集合皆為SK {Name,Sid} 、{Sid,Tel_num} 、{Sid,Name,Age} 鍵值屬性可以用來確認關聯中的唯一的值組 鍵值是由關聯綱要中屬性的意義來決定 資料庫系統

43 主鍵(Primary Key)的產生 鍵值是一個最小的SuperKey
若有一個以上的鍵值,則每一個鍵值都稱為是一個候選鍵(Candidate Key) 必須從Candidate Key中選擇一個作為主鍵(Primary Key) 主鍵的特性 唯一性 最小性 資料庫系統

44 關聯式資料模型的限制 定義域限制 鍵值限制 實體完整性限制 參考完整性限制 與關聯中的主鍵(PK) 有關 資料庫系統

45 鍵值限制與實體完整性 鍵值限制 (Key Constraint) 實體完整性(Entity Integrity Constraint)
主鍵值不能重複 實體完整性(Entity Integrity Constraint) 主鍵值不能有空值 資料庫系統

46 參考完整性 若一個關聯滿足定義域限制、鍵值限制、與實體完整性限制,我們只也能相信個別的關聯本身是合理的,但並無法保證關聯與關聯之間的資料狀態是一致的 參考完整性限制是指定在兩個關聯之間,用以維持兩個關聯值組的一致性 資料庫系統

47 外鍵與參考 R1的外鍵FK (Foreign Key)
FK的屬性與另一關聯綱要R2的Primary Key有相同的定義域 (FK 參考R2) R1的值組t1中的FK可以和R2的某一值組t2 的PK相符,或是空值 t1[FK] = t2[PK] 資料庫系統

48 參考完整性 FK的屬性值必須在所參考之關聯的PK屬性中被找到,或者為Null
資料庫系統

49 EMPLOYEE.Dno 與 DEPARTMENT.Dnum 之間的一致性
Eid Name Age Sex Dno e1 張三 28 男生 1 e2 李四 33 女生 e3 王五 24 3 DEPARTMENT Dnum Dname Location 1 行政 台北 3 研發 新竹 資料庫系統

50 若一員工不屬任何部門時, EMPLOYEE.Dno 可以是空值
Eid Name Age Sex Dno e1 張三 28 男生 1 e2 李四 33 女生 e3 王五 24 3 e4 陳七 31 DEPARTMENT Dnum Dname Location 1 行政 台北 3 研發 新竹 資料庫系統

51 限制的實作 使用DDL指定限制 (no int not null, id int not null, quantity int,
create table orders (no int not null, id int not null, quantity int, odate datetime default getdate(), /*以系統日期及時間為odate的初值*/ primary key (no,id,odate), /*宣告複合欄位(no,id,odate)為主鍵*/ foreign key (no) references bookstores, foreign key (id) references books, check(quantity>0 and quantity<5001)) /*設定quantity值的限制條件*/ 資料庫系統

52 資料庫系統 Database Systems
Functional Dependencies and Normalization 資料庫系統

53 重要觀念 良好的語意 減少tuple的重複值 減少tuple的空值 不允許假值組(Spurious Tuples)的存在 資料庫系統

54 語意設計上的優劣 不良的設計 設計易於解釋的Schema 一個實體僅描述單一意義,勿結合多個於一關聯 EMP_DEPT ENAME EID
SEX TEL DNUM DNAME DMGR 資料庫系統

55 tuple的重複值問題 EMP_DEPT ENAME EID SEX TEL DNUM DNAME DMGR 陳花依 E1 女生
1 行政 林大城 E2 男生 陳富強 E3 2 研發 張珠珠 E4 莊勝為 E5 3 生產 資料庫系統

56 減少tuple的空值 EID ENAME ADDRESS TEL PNO PNAME 陳花依 林大城 陳富強 E1 TAIPEI
123456 1 PROJ_1 E2 林大城 2 E3 陳富強 HOUSTON 654321 3 PROJ_3 資料庫系統

57 不允許假值組的存在 適當地分割關聯可以有效減少空間的浪費,並藉由正規化與關聯式運算(處理)來維持資料的完整性並滿足各項操作
假值組的問題,係因不良的關聯表格切割所導致 關聯表的分割必須考量FK與PK 資料庫系統

58 我們可以由 分割成, 但無法由 回覆成原來的 陳花依 陳花依 林大城 林大城 陳富強 陳富強 張珠珠 張珠珠 E1 1 32 Proj1
EID PNO HOUR ENAME PNAME PLOC E1 1 32 陳花依 Proj1 TP 2 7 Proj2 CH E2 3 40 林大城 Proj3 E3 20 陳富強 E4 張珠珠 KS ENAME PLOC 陳花依 TP CH 林大城 陳富強 張珠珠 KS EID PNO HOUR PNAME PLOC E1 1 32 Proj1 TP 2 7 Proj2 CH E2 3 40 Proj3 E3 20 E4 KS 我們可以由 分割成, 但無法由 回覆成原來的 資料庫系統

59 Functional Dependencies
功能相依 是資料庫的兩個屬性集合的限制 關聯表分割的重要參考依據 表示法(XY)與意義 Relation R 中的任意兩個tuples t1與t2,若t1[X]=t2[X],則使得t1[Y]=t2[Y] Tuple中attribute Y的值是依靠X來決定 稱為X到Y的功能相依,或Y功能相依於X X可以決定Y 資料庫系統

60 XY XY iff r(R)的X值相同,則Y值必定相同 Key points 功能相依是一屬性語意的特性
若一關聯不存在超過一個以上的X值,則表示X是一個Candidate Key 若 XY 並不表示 YX 成立 功能相依是一屬性語意的特性 功能相依無法從特定的關聯中推論得到 資料庫系統

61 功能相依性 與 (非)主要屬性 EMP_PROJ EID PNO HOUR ENAME PNAME PLOC fd1 fd2 fd3 在Relation R 中,若一Attribute(s) 為任意R 的成員(每一Tuple都有該屬性),此Attribute(s)稱為主要屬性,否則則稱為非主要屬性 EID與PNO為EMP_PROJ的主要屬性,其餘屬非主要屬性 資料庫系統

62 正規化 第一正規化型式 (1NF) by E.F. Codd 第二正規化型式 (2NF) by E.F. Codd
廣義 3NF─BCNF (Boyce/Codd Normal Form) 第四正規化型式 (4NF) by R. Fagin 第五正規化型式 (5NF) by R. Fagin 又稱為 Projection/Join Normal Form (PJ/NF) 資料庫系統

63 1NF 1NF：所有屬性定義域僅含基元值(Atomic) (不能有複合屬性/多值的存在) 不符合1NF DEPARTMENT DNUM
DNAME DMGR DLOCS 1 行政 E1 {TP, CH, TC} 2 研發 E3 {TP} 3 生產 E5 {KS} DLOCS的定義域包含基元值,但某些值組包含這些值組的集合 DLOCS屬性並非基元值 資料庫系統

64 non-1NF分割 (Case1) 將違反1NF的屬性從原來的關聯中刪除,並與主鍵放到一個個別的關聯 DNUM DNAME DMGR
DEPARTMENT DNUM DNAME DMGR DLOCS DNUM DLOC DNUM DLOC 1 TP CH TC 2 3 KS DEPT_LOCATIONS 資料庫系統

65 1NF EMP_PORJ 1NF不允許有複合屬性的存在,這些屬性本身是屬於多值 巢狀關聯:一個值組有一個關聯存在
PNO為巢狀關聯PROJS的部份主鍵 EID ENAME PROJS PNO HOURS EID ENAME PNO HOURS E1 陳花依 1 32 2 7 E2 林大城 3 40 E3 陳富強 20 12 E4 張珠珠 14 10 E5 莊勝為 30 31 資料庫系統

66 non-1NF分割 (Case2) EID ENAME PNO HOURS EMP_PORJ EID ENAME EID PNO HOURS
主鍵與部份主鍵 形成新關聯的主鍵 資料庫系統

67 2NF 2NF是以完全功能相依為基礎 完全功能相依 若XY為完全功能相依,則從X中移除任一屬性A,將使得此功能相依性不再成立
EMP_PROJ EID PNO HOUR ENAME PNAME PLOC fd1 fd2 fd3 {EID,PNO}  HOUR (完全相依) EID  ENAME (去掉EID後不滿足相依性) PNO  {PNAME,PLOC} (去掉PNO後不滿足相依性) {EID,PNO}  ENAME (不滿足完全相依性,部份相依) 資料庫系統

68 2NF 2NF 若關聯R中的每一個非主要屬性是完全功能相依於R的Primary Key則稱Relation R屬於2NF non-2NF的處理 Non-2NF的Relation可以進一部被分解成多個2NF的Relations 所產生的新關聯將只有非主要屬性與在主鍵內其完全功能相依的部份 資料庫系統

69 2NF EMP_PROJ EID PNO HOUR ENAME PNAME PLOC EID ENAME EID PNO HOUR PNO
fd1 fd2 fd3 2NF EID ENAME EID PNO HOUR PNO PNAME PLOC fd2 fd1 fd3 資料庫系統

70 3NF 概念基礎 3NF是以遞移相依性為基礎 遞移相依性 Z不屬於任何鍵值的子集合,且XZ,ZY成立 EMP_DEPT ENAME
EID SEX TEL DNUM DNAME DMGR DNUM不屬於任何鍵值的子集合,且 EIDDNUM, DNUM{DNAME,DMGR}成立 存在遞移相依 … (不符合3NF) 資料庫系統

71 3NF 定義 Relation R 是3NF:Relation R 是2NF且沒有非主要屬性是遞移相依於主鍵
Non-3NF的Relation可以進一部被分解成多個3NF的Relations 所產生的新關聯將只有非主要屬性及與其完全功能相依的部分 資料庫系統

72 3NF 員工 部門 EMP_DEPT ENAME EID SEX TEL DNUM DNAME DMGR ENAME EID SEX TEL
資料庫系統

73 Boyce-Codd正規式 (BCNF) BCNF比3NF更嚴格,BCNF成立3NF一定成立,但3NF不一定屬於BCNF Example
在Relation R中每一個功能相依XA都滿足X是R的Super Key Example CNAME={M, L} ASIZE=0.5、0.6、0.7、0.8、0.9、1.0則CNAME=M ASIZE=1.1、1.2、…、2.0則CNAME=L 則存在一個FD: ASIZE  CNAME 假設有數千筆Tuples 資料庫系統

74 3NF  BCNF LOTS依舊符合3NF,但其實CNAME只有 M與L兩種值 P_ID CNAME LOT ASIZE P_ID LOT
減少到十幾筆 BCNF P_ID LOT ASIZE ASIZE CNAME 資料庫系統

75 資料庫系統 Database Systems
SQL 語法 資料庫系統

76 資料庫範例 CUSTOMER ORDERS PRODUCT CID int NAME char RANK AGE 1 ALEX 15 2
BOB 12 3 CATHY 25 4 DAVIE 33 5 ELLA 24 CID int PID ODATE datetime QUAN 1 2005/07/01 50 2 2005/07/02 30 3 2005/07/03 20 4 2005/08/04 5 2005/08/05 40 6 2005/08/01 2005/09/02 60 2005/09/01 2005/11/03 2005/11/04 2005/12/11 10 2005/12/21 PID int PNAME char COLOR PRICE 1 襯衫 紅色 100 2 長褲 藍色 200 3 300 4 400 5 水藍色 500 6 綠色 600 7 淺綠色 700 8 深藍色 800 資料庫系統

77 資料庫的建立與刪除 Create Database Drop Database 語法: Create database 資料庫名稱
範例: Create database saledb Drop Database 語法: Drop database 資料庫名稱 範例: Drop database saledb 資料庫系統

78 關聯表的建立與刪除 建立客戶(CUSTOMER)關聯表 建立商品(PRODUCT)關聯表 Create table customer
( cid int not null, name varchar(50), rank int, age int, primary key (cid) ) 建立商品(PRODUCT)關聯表 Create table product ( pid int not null, pname varchar(50), color varchar(10), price int, primary key (pid) ) 資料庫系統

79 關聯表的建立與刪除 建立交易(ORDERS)關聯表 刪除關聯表 drop table 關聯表名稱 Create table orders
( cid int not null, pid int not null, odate datetime default getdate(), quan int, primary key (cid,pid,odate), foreign key (cid) references customer, foreign key (pid) references product ) 刪除關聯表 drop table 關聯表名稱 資料庫系統

80 SQL-DML 資料的新增 資料擷取(查詢) 資料修改 資料刪除 資料庫系統

81 INSERT 指令 INSERT INTO 關聯表 (欄位1,欄位2,欄位3) VALUES (值1,值2,值3) Ex1: insert into customer (cid,name,rank,age) VALUES ( 1,'ALEX',1,15) Ex2: INSERT INTO product (pid,pname,price) VALUES ( 1,'襯衫',100) Ex3: INSERT INTO orders (cid,pid,quan) VALUES ( 1,1,50) 資料庫系統

82 資料擷取(查詢) select [all] [distinct] [top <n>|<n percent>]
<欄位>|<運算式> [as <自訂欄位標題>] from <關聯表> [where <搜尋條件>|<合併條件>] [group by <用以分組的欄位> [having <分組條件>] ] [order by <欄位>|<運算式>] 資料庫系統

83 SQL QUERY 基本組成 CUSTOMER SELECT <attribute list>
FROM <table list> WHERE <condition> Ex1:擷取年紀大於20歲的客戶姓名與年紀 SELECT NAME,AGE FROM CUSTOMER WHERE AGE>20 CID int NAME char RANK AGE 1 ALEX 15 2 BOB 12 3 CATHY 25 4 DAVIE 33 5 ELLA 24 NAME AGE CATHY 25 DAVIE 33 ELLA 24 資料庫系統

84 SQL QUERY PRODUCT Ex2:擷取所有商品編號與品名屬性 SELECT PID,PNAME FROM PRODUCT
int PNAME char COLOR PRICE 1 襯衫 紅色 100 2 長褲 藍色 200 3 300 4 400 5 水藍色 500 6 綠色 600 7 淺綠色 700 8 深藍色 800 Ex2:擷取所有商品編號與品名屬性 SELECT PID,PNAME FROM PRODUCT Ex3:擷取所有價格低於500元且顏色屬於紅色的商品項目資訊 SELECT * FROM PRODUCT WHERE PRICE<500 AND COLOR='紅色' PID PNAME COLOR PRICE 1 襯衫 紅色 100 3 長褲 300 4 400 資料庫系統

85 SQL QUERY Ex4:擷取所有交易量大於40的交易,列出這些交易的客戶姓名,商品編號與交易量 SELECT NAME,PID,QUAN
FROM CUSTOMER,ORDERS WHERE CUSTOMER.CID=ORDERS.CID AND QUAN>40 FROM CUSTOMER C,ORDERS O WHERE C.CID=O.CID AND QUAN>40 Ex5:擷取所有屬於CATHY客戶的交易,列出這些交易的商品名稱,價格,與交易量 SELECT PNAME,PRICE,QUAN FROM CUSTOMER C,ORDERS O,PRODUCT P WHERE C.CID=O.CID AND O.PID=P.PID AND NAME='CATHY' 資料庫系統

86 DISTINCT 與 IN 的用法 Ex6:擷取客戶等級的類型 SELECT DISTINCT RANK FROM CUSTOMER
SELECT DISTINCT PID FROM ORDERS Ex8:擷取屬於1,3,或5號客戶的交易紀錄,列出客戶編號與商品編號 SELECT CID,PID FROM ORDERS WHERE CID=1 OR CID=3 OR CID=5 WHERE CID IN (1,3,5) 資料庫系統

87 IN與巢狀查詢的應用 Ex9:擷取所有交易量大於40的交易,列出這些交易的商品名稱,顏色與價格
SELECT PNAME,COLOR,PRICE FROM PRODUCT WHERE PID IN ( SELECT DISTINCT PID FROM ORDERS WHERE QUAN>40 ) 資料庫系統

88 聚合函數 (Aggregates) 聚合函數: Count, Sum, Avg, Max, Min 計算完後的結果只有一個答案
Ex10:擷取所有商品中最大,最小,與平均之價格 SELECT MAX(PRICE),MIN(PRICE),AVG(PRICE) FROM PRODUCT Ex11:擷取所有屬於ALEX客戶的交易之次數與總交易量 SELECT COUNT(*),SUM(QUAN) FROM CUSTOMER C, ORDERS O WHERE C.CID=O.CID AND NAME='ALEX' Ex12:屬於1號商品的交易共有幾筆,總交易量又是多少? SELECT COUNT(*) AS OTIMES, SUM(QUAN) AS OTOTAL FROM ORDERS WHERE PID=1 資料庫系統

89 GROUP BY 與 HAVING GROUP BY具有群組化特性,而Having可用來篩選符合條件的Group, 正如 Where 子句是用來篩選出符條件的值組 Ex13:擷取各項商品的交易次數與總交易量 SELECT COUNT(*),SUM(QUAN) FROM ORDERS GROUP BY PID Ex14:擷取各項商品的交易次數與總交易量,列出總交易量高於55的商品交易次數與總交易量 HAVING SUM(QUAN) > 55 資料庫系統

90 範例 Ex15:擷取年紀低於18歲客戶的交易紀錄,求取各項商品的的總交易量,列出其中總交易量高於35的商品編號與總交易量
SELECT PID,SUM(QUAN) FROM CUSTOMER C,ORDERS O WHERE C.CID=O.CID AND AGE>18 GROUP BY PID HAVING SUM(QUAN)>35 資料庫系統

91 Like的敘述 % : 以百分比符號表示零個或多個字元 : 以底線符號表示任一個字元 僅適用於字元型態的屬性
% : 以百分比符號表示零個或多個字元 : 以底線符號表示任一個字元 僅適用於字元型態的屬性 Ex16:擷取商品顏色屬性中,含有 '藍' 的商品名稱與價格 SELECT PNAME,PRICE FROM PRODUCT WHERE COLOR LIKE '%藍%' Ex17:擷取姓名中第二個字母為 'L' 的客戶編號與姓名 SELECT CID,NAME FROM CUSTOMER WHERE NAME LIKE '_L%' 資料庫系統

92 排序 ORDER BY 一般語法組成 排列方式 SELECT * FROM TABLE WHERE CONDITION
ORDER BY ATTRIBUTE <ASC|DESC> 排列方式 ASC (由小至大) DESC (由大至小) 資料庫系統

93 排序 ORDER BY Ex18:擷取所有價格低於500的商品名稱與價格,並依價格由低至高排列輸出 SELECT PNAME,PRICE
FROM PRODUCT WHERE PRICE <500 ORDER BY PRICE ASC Ex19:擷取所有交易量低於55的交易紀錄,列出客戶編號,商品編號與交易量,並依交易量由高至低排列,若交易量相同時,則依客戶編號由小至大排列 SELECT CID,PID,QUAN FROM ORDERS WHERE QUAN<55 ORDER BY QUAN DESC,CID ASC 資料庫系統

94 UPDATE 指令 CUSTOMER Ex:將1號客戶的年紀修改為20歲 UPDATE CUSTOMER SET AGE=20
CID int NAME char RANK AGE 1 ALEX 15 (20) 2 BOB (XYZ) 12 (18) 3 CATHY 25 4 DAVIE 33 5 ELLA 24 UPDATE 指令 UPDATE 關聯表 SET 欄位1=值1,欄位2=值2 WHERE 條件 Ex:將1號客戶的年紀修改為20歲 UPDATE CUSTOMER SET AGE=20 WHERE CID=1 Ex:將2號客戶的姓名修改為XYZ並將年紀改為18歲 SET NAME='XYZ', AGE=18 WHERE CID=2 資料庫系統

95 UPDATE CUSTOMER PRODUCT Ex:將年紀小於20的客戶等級都修改成1 UPDATE CUSTOMER
CID int NAME char RANK AGE 1 ALEX 1 (1) 15 2 BOB 2 (1) 12 3 CATHY 25 4 DAVIE 33 5 ELLA 24 UPDATE Ex:將年紀小於20的客戶等級都修改成1 UPDATE CUSTOMER SET RANK=1 WHERE AGE<20 Ex:將所有價格高於500元的商品,其價格降低20元 UPDATE PRODUCT SET PRICE=PRICE-20 WHERE PRICE>500 PRODUCT PID int PNAME char COLOR PRICE 1 襯衫 紅色 100 2 長褲 藍色 200 3 300 4 400 5 水藍色 500 6 綠色 600 (580) 7 淺綠色 700 (680) 8 深藍色 800 (780) 資料庫系統

96 UPDATE CUSTOMER ORDERS Ex:所有購買過6號商品的客戶等級修改為5 UPDATE CUSTOMER
CID int NAME char RANK AGE 1 ALEX 15 2 BOB 2 (5) 12 3 CATHY 25 4 DAVIE 33 5 ELLA 1 (5) 24 CID int PID ODATE datetime QUAN 1 2005/07/01 50 2 2005/07/02 30 3 2005/07/03 20 4 2005/08/04 5 2005/08/05 40 6 2005/08/01 2005/09/02 60 2005/09/01 2005/11/03 2005/11/04 2005/12/11 10 2005/12/21 Ex:所有購買過6號商品的客戶等級修改為5 UPDATE CUSTOMER SET RANK=5 WHERE CID IN ( SELECT CID FROM ORDERS WHERE PID=6 ) 資料庫系統

97 DELETE 指令 DELETE FROM 關聯表 WHERE 刪除條件 CUSTOMER Ex1: Ex2:
DELETE FROM CUSTOMER WHERE NAME='ELLA' Ex2: DELETE FROM CUSTOMER WHERE CID=5 CUSTOMER CID int NAME char RANK AGE 1 ALEX 15 2 BOB 12 3 CATHY 25 4 DAVIE 33 5 ELLA 24 資料庫系統

98 DELETE Ex3:刪除所屬於BOB客戶的所有交易紀錄 Ex4: Ex5: DELETE FROM ORDERS
WHERE CID IN ( SELECT CID FROM CUSTOMER WHERE NAME='BOB' ) Ex4: Ex5: DELETE FROM CUSTOMER 資料庫系統