結(jié)構(gòu)化查詢語言SQL課件

1、 結(jié)構(gòu)化查詢語言SQL2009/11/13檔案系統(tǒng)(File System)檔案(File)欄位(Fields)紀(jì)錄(Record)關(guān)聯(lián)式資料模型(Relational Data Model)關(guān)聯(lián)式資料庫管理系統(tǒng)(RDBMS)物件導(dǎo)向之類別圖(UML - Class Diagram)2 /107各種系統(tǒng)之基本邏輯單位(1/4)續(xù)下頁檔案系統(tǒng)(File System)關(guān)聯(lián)式資料模型(Relational Data Model)關(guān)聯(lián)(Relation)屬性(Attribute)值組(Tuple)關(guān)聯(lián)式資料庫管理系統(tǒng)(RDBMS)物件導(dǎo)向之類別圖(UML - Class Diagram)3 /107

2、各種系統(tǒng)之基本邏輯單位(2/4)續(xù)下頁檔案系統(tǒng)(File System)關(guān)聯(lián)式資料模型(Relational Data Model)關(guān)聯(lián)式資料庫管理系統(tǒng)(RDBMS)資料表(Table)行(Column)列(Row)物件導(dǎo)向之類別圖(UML - Class Diagram)4 /107各種系統(tǒng)之基本邏輯單位(3/4)續(xù)下頁檔案系統(tǒng)(File System)關(guān)聯(lián)式資料模型(Relational Data Model)關(guān)聯(lián)式資料庫管理系統(tǒng)(RDBMS)物件導(dǎo)向之類別圖(UML - Class Diagram)類別(Class)屬性(Attribute)物件(Object) /實(shí)例(Instance

3、)5 /107各種系統(tǒng)之基本邏輯單位(4/4)檔案系統(tǒng)(File System)關(guān)聯(lián)式資料模型(Relational Data Model)關(guān)聯(lián)式資料庫管理系統(tǒng)(RDBMS)物件導(dǎo)向之類別圖(UML - Class Diagram)檔案(File)關(guān)聯(lián)(Relation)資料表(Table)類別(Class)欄位(Fields)屬性(Attribute)行(Column)屬性(Attribute)紀(jì)錄(Record)值組(Tuple)列(Row)物件(Object) / 實(shí)例(Instance)6 /107相關(guān)名詞比較表一：相關(guān)名詞比較Structured Query Language簡稱SQ

4、L，唸成Sequel源自於1970年IBM公司的一項(xiàng)研究所延伸由美國國家標(biāo)準(zhǔn)局(the American National Standards Institute，簡稱ANSI)和國際標(biāo)準(zhǔn)組織(International Standards Organization，簡稱ISO)訂定為國際依據(jù)之標(biāo)準(zhǔn)7 /107結(jié)構(gòu)化程式語言簡介(1/2) 1970年稱為SQL1或SQL-86 1992年稱為SQL2或稱為SQL-92 ( ISO/IEC 9075:1992 - Database Language SQL ) 的標(biāo)準(zhǔn) 2019年稱為SQL3或稱為SQL-99 ( ISO/IEC 9075-2:2

5、019 - Database Languages - SQL - Part 2: Foundation (SQL/Foundation) 2019年稱為SQL4或稱為SQL-2019 (ISO/IEC 9075-2:2019 - Database Languages - SQL - Part 2: Foundation (SQL/Foundation). 8 /107結(jié)構(gòu)化程式語言簡介(2/2) 資料定義語言Data Definition Language，簡稱DDL建立與維護(hù)資料庫內(nèi)的相關(guān)物件包括綱要(Schema)、資料表(Tables)、檢視表(Views)、預(yù)存程序(Stored Pr

6、ocedures)、觸發(fā)器(Triggers)、函數(shù)(Functions)、定義域(Domains)和限制(Constraints)等等 資料操作語言資料控制語言9 /107結(jié)構(gòu)化程式語言(1/3)續(xù)下頁資料定義語言資料操作語言Data Manipulation Language，簡稱DML對資料的存取操作(Operations) ，包括對資料的新增 ( Insert )刪除 ( Delete )修改 ( Update )查詢 ( Select ) 資料控制語言10 /107結(jié)構(gòu)化程式語言(2/3)續(xù)下頁資料定義語言資料操作語言資料控制語言Data Control Language，簡稱DCL

7、進(jìn)行授權(quán)和撤銷之安全存取控制的管理和操作 11 /107結(jié)構(gòu)化程式語言(3/3)目的在於將不同系統(tǒng)或不同用途的相關(guān)物件做一區(qū)隔或分類，達(dá)到管理上的方便在存取控制上的適當(dāng)授權(quán)，達(dá)到資料保密上的安全性，避免未授權(quán)的使用者讀取或異動(dòng)其中的資料12 /107定義綱要(Schema)13 /107定義綱要之基本語法為人力資源部門建立一獨(dú)立的綱要 【說明】(語法一) 僅以最簡單方式建立一名為HumanResource的綱要?！菊Z法】(語法一)14 /107【範(fàn)例6-1】(1/4)CREATE SCHEMA HumanResource 為人力資源部門建立一獨(dú)立的綱要 【說明】(語法二) 建立一個(gè)綱要名稱與使

8、用者 hrOwner相同名稱，並授權(quán)給予此相同帳號為 hrOwner之使用者 ?！菊Z法】(語法二)15 /107【範(fàn)例6-1】(2/4)CREATE SCHEMA AUTHORIZATION hrOwner未指定schema_name為人力資源部門建立一獨(dú)立的綱要 【說明】(語法三) 建立一個(gè)綱要名稱為HumanResource，並授權(quán)于帳號為hrOwner的使用者?！菊Z法】(語法三)16 /107【範(fàn)例6-1】(3/4)CREATE SCHEMA HumanResource AUTHORIZATION hrOwner schema_nameusername為人力資源部門建立一獨(dú)立的綱要 【說

9、明】(語法四) 建立一個(gè)綱要名為 HumanResouce，並於建立此綱要時(shí)，建立一個(gè)資料表名為員工，與一檢視表名為男員工?！菊Z法】(語法四)17 /107【範(fàn)例6-1】(4/4)CREATE SCHEMA HumanResource AUTHORIZATION CREATE TABLE 員工(員工編號 int PRIMARY KEY,性名 varchar(12),性別 char(1)CREATE View 男員工ASSELECT * FROM 員工 WHERE 性別 = 男schema_element定義名為員工之資料表定義名為男員工之檢視表18 /107刪除綱要之基本語法請刪除為人力資源部

10、門所建立的綱要【說明】(語法一) 在綱要內(nèi)若尚有其他屬性、定義域或元素時(shí)，且希望一併刪除時(shí)，可以使用CASCADE的選項(xiàng)?！菊Z法】(語法一)DROP SCHEMA HumanResource CASCADE19 /107【範(fàn)例6-2】(1/2)請刪除為人力資源部門所建立的綱要【說明】(語法二) 在綱要內(nèi)若沒有任何元素存在，則可以使用RESTRICT的選項(xiàng)。倘若尚有其他屬性、定義域或元素時(shí)，使用RESTRICT的選項(xiàng)會阻止DROP命令執(zhí)行，可避免不小心刪除掉綱要內(nèi)所有的元素?！菊Z法】(語法二)DROP SCHEMA HumanResource RESTRICT 20 /107【範(fàn)例6-2】(2/

11、2)可能違反的限制實(shí)體限制定義域限制參考完整性限制使用者定義解決方式拒絕新增21 /107新增操作可能違反的限制 可能違反的限制參考完整性限制解決方式拒絕刪除(Restrict)連鎖性刪除(Delete Cascade) 將參考到被刪除紀(jì)錄之外來鍵值設(shè)成空值(Set to Null Value) 22 /107刪除操作可能違反的限制23 /107連鎖性刪除(Delete Cascade)(1/2)父資料表 - 訂單資料表子資料表 - 訂單明細(xì)資料表續(xù)下頁24 /107連鎖性刪除(Delete Cascade)(2/2)94010201Delete Cascade父資料表 - 訂單資料表子資料表

12、 - 訂單明細(xì)資料表可能違反的限制若非更新主要鍵或外來鍵定義域限制使用者定義限制 若更新主要鍵或外來鍵實(shí)體限制定義域限制參考完整性限制使用者定義解決方式拒絕更新(Restrict) 連鎖性更新(Update Cascade) 將參考不到父資料表的外來鍵值設(shè)為空值(Set to Null Value) 25 /107更新操作可能違反的限制 26 /107連鎖性更新(Update Cascade)(1/2)父資料表 - 訂單資料表子資料表 - 訂單明細(xì)資料表續(xù)下頁27 /107連鎖性更新(Update Cascade)(2/2)Update Cascade父資料表 - 訂單資料表子資料表 - 訂單

13、明細(xì)資料表28 /107定義資料表之基本語法29 /107資料庫ERD之父子關(guān)係圖6-4 資料庫ERD之主從關(guān)係建立順序(由上而下)【題目】依圖6-4定義出所有之資料表員工客戶供應(yīng)商產(chǎn)品類別產(chǎn)品資料訂單訂單明細(xì)【說明】定義資料表的基本語法在第一欄為資料表之屬性，再定義每個(gè)屬性的資料型態(tài)，資料型態(tài)之後即是該屬性的限制。30 /107【範(fàn)例6-3】【員工】資料表之限制說明在員工資料表中，員工編號為此資料表之主要鍵(Primary Key)，姓名之屬性值不得為空值(Null Value)。【語法】(語法一) 將員工編號為主要鍵之限制寫在下方的table level區(qū)，並將姓名不得為空值之限制，直接寫

14、在姓名屬性後方。(語法二) 將員工編號為主要鍵之限制直接寫在員工編號屬性後方。31 /1071)建立【員工】資料表續(xù)下頁32 /107【客戶】資料表之限制說明在客戶資料表中，客戶編號為此資料表之主要鍵(Primary Key)，公司名稱之屬性值不得為空值(Null Value)。【語法】(語法一) 將客戶編號為主要鍵之限制寫在下方的table level區(qū)，並將公司名稱不得為空值之限制，直接寫在公司名稱屬性後方。(語法二) 將客戶編號為主要鍵之限制直接寫在客戶編號屬性後方。33 /1072)建立【客戶】資料表續(xù)下頁34 /107【供應(yīng)商】資料表之限制說明在供應(yīng)商資料表中，除了供應(yīng)商編號為此資料

15、表之主要鍵(Primary Key)之外，此資料表中的地址和電話屬性，不得同時(shí)為空值(Null Value)，也就是至少要有一個(gè)屬性有值?！菊Z法】(語法一) 將供應(yīng)商編號為主要鍵之限制寫在下方的table level區(qū)，並將地址和電話屬性，不得同時(shí)為空值(Null Value)之限制，寫於table level區(qū)。(語法二) 將供應(yīng)商編號為主要鍵之限制直接寫在供應(yīng)商編號屬性後面，但將地址和電話屬性，不得同時(shí)為空值(Null Value)之限制，仍要寫於table level區(qū)，不得寫於個(gè)別的屬性後方。倘若將此限制寫於個(gè)別屬性後方，其語意將成為地址和電話兩個(gè)屬性皆不可為空值(Null Value

16、)。 35 /1073)建立【供應(yīng)商】資料表續(xù)下頁36 /107【產(chǎn)品類別】資料表之限制說明在產(chǎn)品類別資料表中，類別編號為此資料表之主要鍵(Primary Key)，類別名稱之屬性值不得為空值(Null Value)?！菊Z法】(語法一) 將類別編號為主要鍵之限制寫在下方的table level區(qū)，並將類別名稱不得為空值之限制，直接寫在類別名稱屬性後方。(語法二) 將類別編號為主要鍵之限制直接寫在類別編號屬性後方。37 /1074)建立【產(chǎn)品類別】資料表續(xù)下頁38 /107【產(chǎn)品資料】資料表之限制說明在產(chǎn)品資料的資料表中，產(chǎn)品編號為此資料表之主要鍵(Primary Key)，類別編號為此資料表中

17、一個(gè)外來鍵，參考產(chǎn)品類別資料表中的主要鍵類別編號；供應(yīng)商編號為此資料表中另一個(gè)外來鍵，參考供應(yīng)商資料表中的主要鍵供應(yīng)商編號；並且?guī)齑媪亢桶踩媪勘仨氃O(shè)預(yù)設(shè)值 0?！菊Z法】(語法一) 將產(chǎn)品編號為主要鍵及類別編號和供應(yīng)商編號為兩個(gè)外來鍵之限制寫在下方的table level區(qū)，並將庫存量和安全存量設(shè)預(yù)設(shè)值為0之限制，直接寫在兩個(gè)屬性後方。(語法二) 將產(chǎn)品編號為主要鍵及類別編號和供應(yīng)商編號為兩個(gè)外來鍵之限制直接寫在個(gè)別屬性後方。 39 /1075)建立【產(chǎn)品資料】資料表續(xù)下頁40 /107【訂單】資料表之限制說明在訂單資料表中，訂單編號為此資料表之主要鍵(Primary Key)，員工編號為此資

18、料表中一個(gè)外來鍵，參考員工資料表中的主要鍵員工編號；客戶編號為此資料表中另一個(gè)外來鍵，是參考客戶資料表中的主要鍵客戶編號?！菊Z法】(語法一) 將訂單編號為主要鍵及員工編號和客戶編號為兩個(gè)外來鍵之限制寫在下方的table level區(qū)。(語法二) 將訂單編號為主要鍵及員工編號和客戶編號為兩個(gè)外來鍵之限制直接寫在個(gè)別屬性後方。41 /1076)建立【訂單】資料表續(xù)下頁42 /107【訂單明細(xì)】資料表之限制說明訂單編號+產(chǎn)品編號兩個(gè)屬性結(jié)合成為此資料表之主要鍵(Primary Key)。單一個(gè)產(chǎn)品編號屬性又剛好為此資料表中一個(gè)外來鍵，參考訂單資料表中的主要鍵訂單編號，除了要達(dá)到參考完整性限制外，若有違反此限制時(shí)，採用