資料庫(kù)系統(tǒng)

1、資料庫(kù)系統(tǒng),1,資料庫(kù)系統(tǒng)Database Systems,Introduction主講人:劉佳灝,資料庫(kù)系統(tǒng),2,相關(guān)名詞,資料庫(kù) (database)集合的特性資料庫(kù)系統(tǒng)資料庫(kù)管理系統(tǒng) (database management systems),資料庫(kù)系統(tǒng),3,資料庫(kù),資料庫(kù)(DB)資料庫(kù)是相關(guān)資料的集合集合的特性集合中的元素沒有順序性集合中的元素不會(huì)重複集合可以是空的,資料庫(kù)系統(tǒng),4,資料庫(kù)管理系統(tǒng),資

2、料庫(kù)管理系統(tǒng) (DBMS)是許多程式的集合，讓使用者得以定義、建構(gòu)，與處理資料庫(kù)定義:資料的型態(tài)、結(jié)構(gòu)與限制建構(gòu):在DBMS的控制下,將資料儲(chǔ)存到媒體處理:查詢、更新,資料庫(kù)系統(tǒng),5,,資料庫(kù)系統(tǒng),使用者,SQL查詢 / 應(yīng)用程式,,資料庫(kù)管理系統(tǒng),,,,,儲(chǔ)存綱要,儲(chǔ)存資料庫(kù),,,資料庫(kù)系統(tǒng),6,資料庫(kù)系統(tǒng)Database Systems,Database System Concepts,資料庫(kù)系統(tǒng),7,簡(jiǎn)介,資料模型(D

3、ata Model) 是提供資料抽象化的主要工具Data Model 是用來描述資料庫(kù)結(jié)構(gòu)的一種概念Data Model的內(nèi)容包含結(jié)構(gòu)的表示方式限制運(yùn)算(操作),資料庫(kù)系統(tǒng),8,資料模型 Data Model,Data Model使用的概念實(shí)體 Entity屬性 Attribute關(guān)係 Relationship,客戶,資料庫(kù)系統(tǒng),9,關(guān)聯(lián)式資料模型概念 Relational Model Concepts,關(guān)聯(lián)式模

4、型將資料庫(kù)表示成一個(gè)關(guān)聯(lián)的集合相關(guān)名詞關(guān)聯(lián)(Relation)值組(Tuple)屬性(Attribute)定義域(Domain),資料庫(kù)系統(tǒng),10,Relation Schemas – 名詞簡(jiǎn)介,PRODUCT,,,,,值組,,屬性集合,,關(guān)聯(lián)表名稱,顏色屬性,,尺寸屬性,,品名屬性,,價(jià)格屬性,,品號(hào)屬性,,,主鍵,,,價(jià)格資料,資料庫(kù)系統(tǒng),11,Relation Schemas – 特性與內(nèi)容,關(guān)聯(lián)表的屬性數(shù)目稱為它的維度

5、一個(gè)關(guān)聯(lián)表中任兩個(gè)屬性名稱不可以重覆「關(guān)聯(lián)表綱要」(Relation Schema) 包含:關(guān)聯(lián)表名稱屬性集宣告所有屬性的相對(duì)資料型態(tài)宣告主鍵的宣告外來鍵 (Foreign Key) 之宣告,資料庫(kù)系統(tǒng),12,DBMS的架構(gòu)與資料獨(dú)立,Three Schema (三綱要架構(gòu))外層(景觀層) External Level:外部綱要概念層 Conceptual Level:慨念綱要內(nèi)層 Internal Level:內(nèi)部

6、綱要資料獨(dú)立邏輯資料獨(dú)立實(shí)體資料獨(dú)立,資料庫(kù)系統(tǒng),13,Three Schema,外層(又稱為景觀層) External Level:外部綱要描述特定使用者所感興趣的資料庫(kù)部分隱藏儲(chǔ)存結(jié)構(gòu)等其他細(xì)節(jié)概念層 Conceptual Level:慨念綱要描述整個(gè)資料庫(kù)的結(jié)構(gòu)隱藏儲(chǔ)存等細(xì)節(jié)著重在實(shí)體、資料型態(tài)、關(guān)係、使用者的操作與限制內(nèi)層Internal Level:內(nèi)部綱要用來描述資料庫(kù)的實(shí)際儲(chǔ)存結(jié)構(gòu)與路徑,資料庫(kù)系統(tǒng),

7、14,Data Independence 資料獨(dú)立,Logical data independence (邏輯資料獨(dú)立)改變 conceptual schema 時(shí),不需改變 external schema (or application)Physical data independence (實(shí)體資料獨(dú)立) 改變 internal schema 時(shí),不需改變 conceptual schema,資料庫(kù)系統(tǒng),15,SQL,DDL

8、 資料定義語言用來定義 concept schema與資料庫(kù)綱要之定義有關(guān)的操作,如資料庫(kù)的建立、表格的建立等命令DML 資料處理語言對(duì)資料庫(kù)資料進(jìn)行擷取、新增、刪除與修改等處理之命令僅對(duì)資料有所影響,與資料庫(kù)綱要定義無關(guān),資料庫(kù)系統(tǒng),16,資料庫(kù)系統(tǒng)Database Systems,Modeling Using the Entity-Relationship Approach,資料庫(kù)系統(tǒng),17,屬性型態(tài),簡(jiǎn)單(Simpl

9、e)複合(Composite)單值(Single-valued)多值(Multi-valued)儲(chǔ)存(Stored)導(dǎo)出(Derived),資料庫(kù)系統(tǒng),18,屬性型態(tài),簡(jiǎn)單(simple) 與 複合(composite),,,,,姓,名,,生日,,地址,,,員工編號(hào),,,電話,,薪資,,,,員工,,,姓名,資料庫(kù)系統(tǒng),19,,地址,,,里別,,隣號(hào),,,,,,郵遞區(qū)號(hào),,路別,,縣市別,,里隣,應(yīng)該是簡(jiǎn)單還是複合屬性?,,

10、資料庫(kù)系統(tǒng),20,屬性型態(tài),單值(single-valued) 與多值(multi-valued)Ex:一個(gè)部門只有一個(gè)部門編號(hào)與部門名稱,某些部門可能存同時(shí)在兩個(gè)地點(diǎn)(例如:一個(gè)部門分別在 臺(tái)中 與 彰化 各有據(jù)點(diǎn)),,部門,,名稱,,部門編號(hào),,地點(diǎn),,,,資料庫(kù)系統(tǒng),21,,屬性型態(tài),儲(chǔ)存(stored) 與 導(dǎo)出(derived)Ex:年紀(jì)可以經(jīng)由生日換算得到,,,,,,姓名,姓,名,,生日,,地址,,,員工編號(hào),,,電話,

11、,薪資,,,,員工,,,年紀(jì),資料庫(kù)系統(tǒng),22,屬性型態(tài) (範(fàn)例二),Ex:一個(gè)客戶的交易次數(shù)可以經(jīng)由訂購(gòu)紀(jì)錄中計(jì)算出來,,,客戶,,編號(hào),,,交易次數(shù),,,姓名,,,電話,,商品,,編號(hào),,,名稱,,,價(jià)格,,日期,,交易量,,,,訂購(gòu),資料庫(kù)系統(tǒng),23,鍵值屬性,一個(gè)具有唯一性的屬性 假設(shè):每一個(gè)客戶的編號(hào)會(huì)唯一每一個(gè)商品的編號(hào)會(huì)唯一,且每一商品的名稱也會(huì)唯一,,,客戶,,編號(hào),,,交易次數(shù),,,姓名,,,電話,,商品,,編號(hào)

12、,,,名稱,,,價(jià)格,,日期,,交易量,,,,訂購(gòu),資料庫(kù)系統(tǒng),24,實(shí)體,關(guān)係,屬性,實(shí)體型態(tài)具有相同屬性集合的元素所形成的集合關(guān)係型態(tài)用來連接某些相關(guān)的實(shí)體型態(tài)屬性可以用來描述實(shí)體或關(guān)係,,,學(xué)生,,學(xué)號(hào),,,姓名,,,性別,,課程,,編號(hào),,,名稱,,,教師,,日期,,成績(jī),,,,選修,資料庫(kù)系統(tǒng),25,關(guān)係,關(guān)係型態(tài)的Degree是參與此關(guān)係的實(shí)體型態(tài)數(shù)目Ex:Works_for的Degree為2,又稱二元關(guān)係,,Re

13、lationship,,,,,,,,,資料庫(kù)系統(tǒng),26,關(guān)係型態(tài)的限制,Constraints on Relationship Types基數(shù)率與參與基數(shù)率一個(gè)實(shí)體所能參與的關(guān)係案例數(shù)Ex:一個(gè)部門僅會(huì)有一個(gè)主管參與Ex:任何一個(gè)部門一定有一個(gè)主管(去管理它),資料庫(kù)系統(tǒng),27,基數(shù)率(cardinality ratio),1:1的範(fàn)例,員工,,部門,,管理,e1e2e3e4,d1 d2d3,r1

14、r2r3,,,,,,,資料庫(kù)系統(tǒng),28,基數(shù)率,1:N的範(fàn)例,員工,,部門,,屬於,e1e2e3e4,d1 d2d3,r1r2r3r4,,,,,,,,,資料庫(kù)系統(tǒng),29,基數(shù)率,N:M的範(fàn)例,員工,,專案,,參與,e1e2e3e4,P1 p2p3,r1r2r3r4r5r6,,,,,,,,,,,,,資料庫(kù)系統(tǒng),30,參與限制,實(shí)體的存在是否依靠關(guān)係型態(tài)與另一實(shí)體

15、產(chǎn)生關(guān)聯(lián)來決定部份參與 (以單線表示)全部參與 (以雙線表示),員工,,部門,,管理,,部份參與,全部參與,,資料庫(kù)系統(tǒng),31,參與限制 (範(fàn)例二),課程實(shí)體的存在是否依靠開設(shè)關(guān)係型態(tài)與另一教師實(shí)體產(chǎn)生關(guān)聯(lián)來決定課程實(shí)體要存在必須所對(duì)應(yīng)的教師實(shí)體與開設(shè)關(guān)係都存在,該課程才會(huì)存在任何一個(gè)課程一定要有教師去開設(shè)它,教師,,課程,,開設(shè),,部份參與,全部參與,,資料庫(kù)系統(tǒng),32,弱實(shí)體型態(tài)(Weak Entity Type),Weak

16、 Entity Types 本身可以沒有任何的鍵值屬性透過另一實(shí)體型態(tài)的一些屬性所組成的相關(guān)特定實(shí)體(Identifying Owner)來辨認(rèn)辨認(rèn)關(guān)係(Identifying Relationship)Weak Entity在沒有Identifying Owner 的狀況下是無法辨認(rèn)的,所以Weak Entity 總是有一個(gè)完全參與的限制來與它的辨認(rèn)關(guān)係產(chǎn)生關(guān)聯(lián),資料庫(kù)系統(tǒng),33,,,,姓名,生日,性別,關(guān)係,,,,,,Weak

17、 entity,Identifying relationship,員工,,眷屬,,擁有,全部參與,Identifying owner,員工編號(hào),,,,,,,,弱實(shí)體型態(tài)(Weak Entity Type),資料庫(kù)系統(tǒng),34,適當(dāng)名稱,大寫字母表示實(shí)體與關(guān)係型態(tài)屬性名稱字首大寫實(shí)體多使用名詞關(guān)係則採(cǎi)用動(dòng)詞方向：由左到右,由上到下,資料庫(kù)系統(tǒng),35,範(fàn)例:讀者借閱書籍,系統(tǒng)必須紀(jì)錄讀者的身份證號(hào)碼、姓名、生日,與聯(lián)絡(luò)方式,其中

18、聯(lián)絡(luò)方式包含了電話與地址資料系統(tǒng)紀(jì)錄了館藏中每一本書籍的書名與作者, 雖然有些書籍有兩個(gè)以上的作者,但一定都只有一個(gè)唯一書號(hào). 為了掌握每本書的熱門程度,系統(tǒng)必須提供每本書籍的曾經(jīng)被借閱的次數(shù),以提供讀者參考當(dāng)一個(gè)讀者要借閱書籍時(shí),系統(tǒng)必須紀(jì)錄借閱的日期,以便管理. 而每一位讀者至少都有借閱過一次以上書籍的紀(jì)錄,資料庫(kù)系統(tǒng),36,,,範(fàn)例:讀者借閱書籍,讀者,,書籍,,借閱,,身份證號(hào)碼,生日,聯(lián)絡(luò)方式,電話,地址,,,,姓名

19、,,,,書名,書號(hào),作者,借閱次數(shù),,,日期,,作者,資料庫(kù)系統(tǒng),37,資料庫(kù)系統(tǒng)Database Systems,Relational Database Constraints,資料庫(kù)系統(tǒng),38,關(guān)聯(lián)式資料模型的限制,定義域限制鍵值限制實(shí)體完整性限制參考完整性限制,資料庫(kù)系統(tǒng),39,屬性與定義域(Domain),欄位中所允許的數(shù)值(Value)資料稱為定義域(Domain)定義域D是基元(Atomic)數(shù)值資料的集合Dom

20、ain中的數(shù)值是不可分割個(gè)所以稱為基元Dom(Sex)={男生,女生} 則”男生” 與 ”女生” 皆為sex屬性的的合理基元值Ex:age:大於0的整數(shù)ID_numbers:一個(gè)英文字母加上九位數(shù)字的集合年級(jí):介於1~4之間的數(shù)值,資料庫(kù)系統(tǒng),40,定義域限制,定義域限制指定了一屬性A的值必須在該屬性值的定義域Dom(A)裡的一個(gè)基元值定義方式列舉範(fàn)圍格式資料型態(tài),資料庫(kù)系統(tǒng),41,鍵值限制,關(guān)聯(lián)可視為一個(gè)集合,所含

21、值組必須相異t1[SK] t2[SK]SK:超鍵 (superkey)每個(gè)關(guān)聯(lián)至少存在一個(gè)SK,≠,資料庫(kù)系統(tǒng),42,包含SK的屬性集合亦為SK,,Ex: Sid是一SK,所以包含Sid的屬性集合皆為SK{Name,Sid} 、{Sid,Tel_num} 、{Sid,Name,Age}鍵值屬性可以用來確認(rèn)關(guān)聯(lián)中的唯一的值組鍵值是由關(guān)聯(lián)綱要中屬性的意義來決定,,資料庫(kù)系統(tǒng),43,主鍵(Primary Key)的產(chǎn)生,鍵

22、值是一個(gè)最小的SuperKey若有一個(gè)以上的鍵值,則每一個(gè)鍵值都稱為是一個(gè)候選鍵(Candidate Key)必須從Candidate Key中選擇一個(gè)作為主鍵(Primary Key)主鍵的特性唯一性最小性,,資料庫(kù)系統(tǒng),44,關(guān)聯(lián)式資料模型的限制,定義域限制鍵值限制實(shí)體完整性限制參考完整性限制,,與關(guān)聯(lián)中的主鍵(PK)有關(guān),資料庫(kù)系統(tǒng),45,鍵值限制與實(shí)體完整性,鍵值限制 (Key Constraint)主鍵值不

23、能重複實(shí)體完整性(Entity Integrity Constraint)主鍵值不能有空值,資料庫(kù)系統(tǒng),46,參考完整性,若一個(gè)關(guān)聯(lián)滿足定義域限制、鍵值限制、與實(shí)體完整性限制,我們只也能相信個(gè)別的關(guān)聯(lián)本身是合理的,但並無法保證關(guān)聯(lián)與關(guān)聯(lián)之間的資料狀態(tài)是一致的參考完整性限制是指定在兩個(gè)關(guān)聯(lián)之間,用以維持兩個(gè)關(guān)聯(lián)值組的一致性,資料庫(kù)系統(tǒng),47,外鍵與參考,R1的外鍵FK (Foreign Key)FK的屬性與另一關(guān)聯(lián)綱要R2的Pr

24、imary Key有相同的定義域 (FK 參考R2)R1的值組t1中的FK可以和R2的某一值組t2 的PK相符,或是空值t1[FK] = t2[PK],,資料庫(kù)系統(tǒng),48,參考完整性,FK的屬性值必須在所參考之關(guān)聯(lián)的PK屬性中被找到,或者為Null關(guān)聯(lián)式資料庫(kù)之參考完整性限制,藉由FK與PK間的參考關(guān)係,以達(dá)到關(guān)聯(lián)表之間的一致性,資料庫(kù)系統(tǒng),49,EMPLOYEE.Dno 與 DEPARTMENT.Dnum 之間的一致性,,

25、,,,,EMPLOYEE,DEPARTMENT,資料庫(kù)系統(tǒng),50,若一員工不屬任何部門時(shí), EMPLOYEE.Dno 可以是空值,,,,,,EMPLOYEE,DEPARTMENT,資料庫(kù)系統(tǒng),51,限制的實(shí)作,使用DDL指定限制,create table orders (no int not null, id int not null, quantity int,

26、 odate datetime default getdate(), /*以系統(tǒng)日期及時(shí)間為odate的初值*/ primary key (no,id,odate), /*宣告複合欄位(no,id,odate)為主鍵*/ foreign key (no) references bookstores, foreign key (id) references boo

27、ks, check(quantity>0 and quantity<5001)) /*設(shè)定quantity值的限制條件*/,資料庫(kù)系統(tǒng),52,資料庫(kù)系統(tǒng)Database Systems,Functional Dependencies and Normalization,資料庫(kù)系統(tǒng),53,重要觀念,良好的語意減少tuple的重複值減少tuple的空值不允許假值組(Spurious Tuples)的存在,

28、資料庫(kù)系統(tǒng),54,語意設(shè)計(jì)上的優(yōu)劣,設(shè)計(jì)易於解釋的Schema一個(gè)實(shí)體僅描述單一意義,勿結(jié)合多個(gè)於一關(guān)聯(lián)不良的設(shè)計(jì),,,EMP_DEPT,,資料庫(kù)系統(tǒng),55,tuple的重複值問題,,,,EMP_DEPT,資料庫(kù)系統(tǒng),56,減少tuple的空值,資料庫(kù)系統(tǒng),57,不允許假值組的存在,適當(dāng)?shù)胤指铌P(guān)聯(lián)可以有效減少空間的浪費(fèi),並藉由正規(guī)化與關(guān)聯(lián)式運(yùn)算(處理)來維持資料的完整性並滿足各項(xiàng)操作假值組的問題,係因不良的關(guān)聯(lián)表格切割所導(dǎo)致關(guān)

29、聯(lián)表的分割必須考量FK與PK,資料庫(kù)系統(tǒng),58,,,我們可以由 分割成,但無法由回覆成原來的,,,,,,,,,,,,,,資料庫(kù)系統(tǒng),59,Functional Dependencies,功能相依是資料庫(kù)的兩個(gè)屬性集合的限制關(guān)聯(lián)表分割的重要參考依據(jù)表示法(X?Y)與意義Relation R 中的任意兩個(gè)tuples t1與t2,若t1[X]=t2[X],則使得t1[Y]=t2[Y]Tuple中attribute Y

30、的值是依靠X來決定稱為X到Y(jié)的功能相依,或Y功能相依於XX可以決定Y,資料庫(kù)系統(tǒng),60,X?Y,X?Y iff r(R)的X值相同,則Y值必定相同Key points若一關(guān)聯(lián)不存在超過一個(gè)以上的X值,則表示X是一個(gè)Candidate Key若 X?Y 並不表示 Y?X 成立功能相依是一屬性語意的特性功能相依無法從特定的關(guān)聯(lián)中推論得到,資料庫(kù)系統(tǒng),61,功能相依性 與 (非)主要屬性,在Relation R 中,若一A

31、ttribute(s) 為任意R 的成員(每一Tuple都有該屬性),此Attribute(s)稱為主要屬性,否則則稱為非主要屬性EID與PNO為EMP_PROJ的主要屬性,其餘屬非主要屬性,,,,,,,,,,,,,fd1,fd2,fd3,EMP_PROJ,資料庫(kù)系統(tǒng),62,正規(guī)化,第一正規(guī)化型式 (1NF) by E.F. Codd第二正規(guī)化型式 (2NF) by E.F. Codd第三正規(guī)化型式 (3NF) by E.F. C

32、odd廣義 3NF─BCNF (Boyce/Codd Normal Form) 第四正規(guī)化型式 (4NF) by R. Fagin第五正規(guī)化型式 (5NF) by R. Fagin又稱為 Projection/Join Normal Form (PJ/NF),資料庫(kù)系統(tǒng),63,1NF,1NF：所有屬性定義域僅含基元值(Atomic) (不能有複合屬性/多值的存在),,,不符合1NF,DLOCS的定義域包含基元值,但某

33、些值組包含這些值組的集合DLOCS屬性並非基元值,,DEPARTMENT,,資料庫(kù)系統(tǒng),64,non-1NF分割 (Case1),將違反1NF的屬性從原來的關(guān)聯(lián)中刪除,並與主鍵放到一個(gè)個(gè)別的關(guān)聯(lián),,DEPARTMENT,DEPT_LOCATIONS,,,,,,,,資料庫(kù)系統(tǒng),65,1NF,1NF不允許有複合屬性的存在,這些屬性本身是屬於多值巢狀關(guān)聯(lián):一個(gè)值組有一個(gè)關(guān)聯(lián)存在PNO為巢狀關(guān)聯(lián)PROJS的部份主鍵,,,EMP_POR

34、J,,,資料庫(kù)系統(tǒng),66,non-1NF分割,,主鍵與部份主鍵形成新關(guān)聯(lián)的主鍵,,,EMP_PORJ,,1NF,(Case2),資料庫(kù)系統(tǒng),67,,2NF,2NF是以完全功能相依為基礎(chǔ)完全功能相依若X?Y為完全功能相依,則從X中移除任一屬性A,將使得此功能相依性不再成立,{EID,PNO} ? HOUR (完全相依)EID ? ENAME (去掉EID後不滿足相依性)PNO ? {PNAME,PLOC} (去掉PNO後

35、不滿足相依性){EID,PNO} ? ENAME (不滿足完全相依性,部份相依),,,,,,,,,,,,,fd1,fd2,fd3,EMP_PROJ,資料庫(kù)系統(tǒng),68,2NF,2NF若關(guān)聯(lián)R中的每一個(gè)非主要屬性是完全功能相依於R的Primary Key則稱Relation R屬於2NFnon-2NF的處理Non-2NF的Relation可以進(jìn)一部被分解成多個(gè)2NF的Relations所產(chǎn)生的新關(guān)聯(lián)將只有非主要屬性與在主鍵內(nèi)

36、其完全功能相依的部份,資料庫(kù)系統(tǒng),69,2NF,,,,,,,,,,,,,fd1,fd2,fd3,EMP_PROJ,,,,,,,,fd1,,,,fd2,,,,fd3,,,2NF,資料庫(kù)系統(tǒng),70,3NF 概念基礎(chǔ),3NF是以遞移相依性為基礎(chǔ)遞移相依性Z不屬於任何鍵值的子集合,且X?Z,Z?Y成立,DNUM不屬於任何鍵值的子集合,且 EID?DNUM, DNUM?{DNAME,DMGR}成立 存在遞移相依 … (不符合3NF),,,

37、,,,,,,,,,EMP_DEPT,資料庫(kù)系統(tǒng),71,3NF 定義,Relation R 是3NF:Relation R 是2NF且沒有非主要屬性是遞移相依於主鍵Non-3NF的Relation可以進(jìn)一部被分解成多個(gè)3NF的Relations所產(chǎn)生的新關(guān)聯(lián)將只有非主要屬性及與其完全功能相依的部分,資料庫(kù)系統(tǒng),72,3NF,員工,部門,,,,,,,,,,,,,,,,,,,,,,,,EMP_DEPT,,3NF,資料庫(kù)系統(tǒng),73,Boyc

38、e-Codd正規(guī)式 (BCNF),BCNF比3NF更嚴(yán)格,BCNF成立3NF一定成立,但3NF不一定屬於BCNFBCNF的定義在Relation R中每一個(gè)功能相依X?A都滿足X是R的Super KeyExampleCNAME={M, L}ASIZE=0.5、0.6、0.7、0.8、0.9、1.0則CNAME=MASIZE=1.1、1.2、…、2.0則CNAME=L則存在一個(gè)FD: ASIZE ? CNAME假設(shè)有數(shù)千

39、筆Tuples,資料庫(kù)系統(tǒng),74,3NF ? BCNF,LOTS依舊符合3NF,但其實(shí)CNAME只有 M與L兩種值,,,,LOTS,,,,,,,,,,,,,,,BCNF,資料庫(kù)系統(tǒng),75,資料庫(kù)系統(tǒng)Database Systems,SQL 語法,資料庫(kù)系統(tǒng),76,,CUSTOMER,ORDERS,PRODUCT,,,,,,,資料庫(kù)範(fàn)例,資料庫(kù)系統(tǒng),77,資料庫(kù)的建立與刪除,Create Database語法: Create data

40、base 資料庫(kù)名稱範(fàn)例: Create database saledbDrop Database語法: Drop database 資料庫(kù)名稱範(fàn)例: Drop database saledb,資料庫(kù)系統(tǒng),78,關(guān)聯(lián)表的建立與刪除,建立客戶(CUSTOMER)關(guān)聯(lián)表Create table customer( cid int not null, name varchar(50), rank int,

41、 age int, primary key (cid) ),建立商品(PRODUCT)關(guān)聯(lián)表Create table product( pid int not null, pname varchar(50), color varchar(10), price int, primary key (pid)),資料庫(kù)系統(tǒng),79,關(guān)聯(lián)表的建立與刪除,建立交易(ORDERS)關(guān)聯(lián)表Create ta

42、ble 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),刪除關(guān)聯(lián)表

43、 drop table 關(guān)聯(lián)表名稱,資料庫(kù)系統(tǒng),80,SQL-DML,資料的新增資料擷取(查詢)資料修改資料刪除,資料庫(kù)系統(tǒng),81,INSERT,指令 INSERT INTO 關(guān)聯(lián)表 (欄位1,欄位2,欄位3) VALUES (值1,值2,值3),Ex1:insert into customer (cid,name,rank,age)VALUES ( 1,'ALEX',1,15),Ex2:

44、INSERT INTO product (pid,pname,price)VALUES ( 1,'襯衫',100),Ex3:INSERT INTO orders (cid,pid,quan)VALUES ( 1,1,50),資料庫(kù)系統(tǒng),82,資料擷取(查詢),select [all] [distinct] [top |] | [as ] from

45、 [where |] [group by [having ] ] [order by |],資料庫(kù)系統(tǒng),83,SQL QUERY,基本組成SELECT FROM WHERE Ex1:擷取年紀(jì)大於20歲的客戶姓名與年紀(jì) SELECT NAME,AGEFROM CUSTOMERWHERE AGE>20,CUSTOMER,,資料庫(kù)系統(tǒng),84,SQL QUERY,E

46、x2:擷取所有商品編號(hào)與品名屬性 SELECT PID,PNAMEFROM PRODUCTEx3:擷取所有價(jià)格低於500元且顏色屬於紅色的商品項(xiàng)目資訊 SELECT *FROM PRODUCTWHERE PRICE<500 AND COLOR='紅色',,PRODUCT,資料庫(kù)系統(tǒng),85,SQL QUERY,Ex4:擷取所有交易量大於40的

47、交易,列出這些交易的客戶姓名,商品編號(hào)與交易量 SELECT NAME,PID,QUANFROM CUSTOMER,ORDERSWHERE CUSTOMER.CID=ORDERS.CID AND QUAN>40 SELECT NAME,PID,QUANFROM CUSTOMER C,ORDERS OWHERE C.CID=O.CID AND QUAN>40

48、Ex5:擷取所有屬於CATHY客戶的交易,列出這些交易的商品名稱,價(jià)格,與交易量 SELECT PNAME,PRICE,QUANFROM CUSTOMER C,ORDERS O,PRODUCT PWHERE C.CID=O.CID AND O.PID=P.PID AND NAME='CATHY',資料庫(kù)系統(tǒng),86,DISTINCT 與 IN 的用法,Ex6:擷取客戶等級(jí)的類型 S

49、ELECT DISTINCT RANKFROM CUSTOMEREx7:擷取曾經(jīng)銷售過的書籍編號(hào) SELECT DISTINCT PIDFROM ORDERSEx8:擷取屬於1,3,或5號(hào)客戶的交易紀(jì)錄,列出客戶編號(hào)與商品編號(hào)SELECT CID,PIDFROM ORDERSWHERE CID=1 OR CID=3 OR CID=5SELECT CID,PID

50、FROM ORDERSWHERE CID IN (1,3,5),資料庫(kù)系統(tǒng),87,IN與巢狀查詢的應(yīng)用,Ex9:擷取所有交易量大於40的交易,列出這些交易的商品名稱,顏色與價(jià)格SELECT PNAME,COLOR,PRICEFROM PRODUCTWHERE PID IN ( SELECT DISTINCT PIDFROM ORDERSWHERE QUAN&g

51、t;40 ),資料庫(kù)系統(tǒng),88,聚合函數(shù) (Aggregates),聚合函數(shù): Count, Sum, Avg, Max, Min計(jì)算完後的結(jié)果只有一個(gè)答案Ex10:擷取所有商品中最大,最小,與平均之價(jià)格SELECT MAX(PRICE),MIN(PRICE),AVG(PRICE)FROM PRODUCTEx11:擷取所有屬於ALEX客戶的交易之次數(shù)與總交易量SELECT COUNT(*),SUM(QUAN

52、)FROM CUSTOMER C, ORDERS OWHERE C.CID=O.CID AND NAME='ALEX'Ex12:屬於1號(hào)商品的交易共有幾筆,總交易量又是多少? SELECT COUNT(*) AS OTIMES, SUM(QUAN) AS OTOTALFROM ORDERS WHERE PID=1,資料庫(kù)系統(tǒng),89,GROUP BY 與 HAVING,GR

53、OUP BY具有群組化特性,而Having可用來篩選符合條件的Group, 正如 Where 子句是用來篩選出符條件的值組Ex13:擷取各項(xiàng)商品的交易次數(shù)與總交易量SELECT COUNT(*),SUM(QUAN)FROM ORDERSGROUP BY PIDEx14:擷取各項(xiàng)商品的交易次數(shù)與總交易量,列出總交易量高於55的商品交易次數(shù)與總交易量SELECT COUNT(*),SUM(QUAN

54、)FROM ORDERSGROUP BY PIDHAVING SUM(QUAN) > 55,資料庫(kù)系統(tǒng),90,範(fàn)例,Ex15:擷取年紀(jì)低於18歲客戶的交易紀(jì)錄,求取各項(xiàng)商品的的總交易量,列出其中總交易量高於35的商品編號(hào)與總交易量SELECT PID,SUM(QUAN)FROM CUSTOMER C,ORDERS OWHERE C.CID=O.CID AND AGE>1

55、8GROUP BY PIDHAVING SUM(QUAN)>35,資料庫(kù)系統(tǒng),91,Like的敘述,% : 以百分比符號(hào)表示零個(gè)或多個(gè)字元 : 以底線符號(hào)表示任一個(gè)字元僅適用於字元型態(tài)的屬性Ex16:擷取商品顏色屬性中,含有 '藍(lán)' 的商品名稱與價(jià)格SELECT PNAME,PRICEFROM PRODUCTWHERE COLOR LIKE '%

56、藍(lán)%'Ex17:擷取姓名中第二個(gè)字母為 'L' 的客戶編號(hào)與姓名SELECT CID,NAMEFROM CUSTOMERWHERE NAME LIKE '_L%',資料庫(kù)系統(tǒng),92,排序 ORDER BY,一般語法組成SELECT *FROM TABLEWHERE CONDITIONORDER BY ATTRIBUTE 排列方式ASC

57、 (由小至大)DESC (由大至小),資料庫(kù)系統(tǒng),93,排序 ORDER BY,Ex18:擷取所有價(jià)格低於500的商品名稱與價(jià)格,並依價(jià)格由低至高排列輸出 SELECT PNAME,PRICEFROM PRODUCTWHERE PRICE <500ORDER BY PRICE ASCEx19:擷取所有交易量低於55的交易紀(jì)錄,列出客戶編號(hào),商品編號(hào)與交易量,並依交易量由高至低排列,若交

58、易量相同時(shí),則依客戶編號(hào)由小至大排列 SELECT CID,PID,QUANFROM ORDERSWHERE QUAN<55ORDER BY QUAN DESC,CID ASC,資料庫(kù)系統(tǒng),94,UPDATE,指令 UPDATE 關(guān)聯(lián)表 SET 欄位1=值1,欄位2=值2 WHERE 條件Ex:將1號(hào)客戶的年紀(jì)修改為20歲UPDATE CUSTOMERSET AGE

59、=20WHERE CID=1Ex:將2號(hào)客戶的姓名修改為XYZ並將年紀(jì)改為18歲UPDATE CUSTOMERSET NAME='XYZ', AGE=18WHERE CID=2,CUSTOMER,資料庫(kù)系統(tǒng),95,UPDATE,Ex:將年紀(jì)小於20的客戶等級(jí)都修改成1UPDATE CUSTOMERSET RANK=1 WHERE AGE500,CUSTOMER,

60、PRODUCT,資料庫(kù)系統(tǒng),96,UPDATE,Ex:所有購(gòu)買過6號(hào)商品的客戶等級(jí)修改為5UPDATE CUSTOMERSET RANK=5WHERE CID IN ( SELECT CID FROM ORDERS WHERE PID=6 ),CUSTOMER,ORDERS,,,,,,,資料庫(kù)系統(tǒng),97,DELETE,指令 DELETE FROM 關(guān)聯(lián)表

61、 WHERE 刪除條件Ex1:DELETE FROM CUSTOMERWHERE NAME='ELLA'Ex2:DELETE FROM CUSTOMERWHERE CID=5,CUSTOMER,,資料庫(kù)系統(tǒng),98,DELETE,Ex3:刪除所屬於BOB客戶的所有交易紀(jì)錄DELETE FROM ORDERSWHERE CID IN ( SELECT CID