1、 結構化查詢語言SQL2009/11/13 檔案系統檔案系統(File System)檔案檔案(File)欄位欄位(Fields)紀錄紀錄(Record)關聯式資料模型(Relational Data Model)關聯式資料庫管理系統(RDBMS)物件導向之類別圖(UML-Class Diagram)2/107各種系統之基本邏輯單位(1/4)續續下頁 檔案系統(File System)關聯式資料模型關聯式資料模型(Relational Data Model)關聯關聯(Relation)屬性屬性(Attribute)值組值組(Tuple)關聯式資料庫管理系統(RDBMS)物件導向之類別圖(UML
2、-Class Diagram)3/107各種系統之基本邏輯單位(2/4)續續下頁 檔案系統(File System)關聯式資料模型(Relational Data Model)關聯式資料庫管理系統關聯式資料庫管理系統(RDBMS)資料表資料表(Table)行行(Column)列列(Row)物件導向之類別圖(UML-Class Diagram)4/107各種系統之基本邏輯單位(3/4)續續下頁 檔案系統(File System)關聯式資料模型(Relational Data Model)關聯式資料庫管理系統(RDBMS)物件導向之類別圖物件導向之類別圖(UML-Class Diagram)類別類
3、別(Class)屬性屬性(Attribute)物件物件(Object)/實例實例(Instance)5/107各種系統之基本邏輯單位(4/4)檔案系統(File System)關聯式資料模型(Relational Data Model)關聯式資料庫管理系統(RDBMS)物件導向之類別圖(UML-Class Diagram)檔案(File)關聯(Relation)資料表(Table)類別(Class)欄位(Fields)屬性(Attribute)行(Column)屬性(Attribute)紀錄(Record)值組(Tuple)列(Row)物件(Object)/實例(Instance)6/107相
4、關名詞比較表一：相關名詞比較 Structured Query Language 簡稱SQL，唸成Sequel 源自於1970年IBM公司的一項研究所延伸 由美國國家標準局(the American National Standards Institute，簡稱ANSI)和國際標準組織(International Standards Organization，簡稱ISO)訂定為國際依據之標準7/107結構化程式語言簡介(1/2)1970年 稱為SQL1或SQL-86 1992年 稱為SQL2或稱為SQL-92(ISO/IEC 9075:1992-Database Language SQL)的標
5、準 2019年 稱為SQL3或稱為SQL-99(ISO/IEC 9075-2:2019-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結構化程式語言簡介(2/2)資料定義語言資料定義語言 Data Definition Language，簡稱，簡稱DDL 建立與維護資料庫內的相關物件建立與維護資料庫內的相關物件 包括綱要
6、包括綱要(Schema)、資料表、資料表(Tables)、檢視表、檢視表(Views)、預存程序、預存程序(Stored Procedures)、觸發器、觸發器(Triggers)、函數、函數(Functions)、定義域、定義域(Domains)和限制和限制(Constraints)等等等等 資料操作語言 資料控制語言9/107結構化程式語言(1/3)續續下頁 資料定義語言 資料操作語言資料操作語言 Data Manipulation Language，簡稱，簡稱DML 對資料的存取操作對資料的存取操作(Operations)，包括對資料的，包括對資料的 新增新增(Insert)刪除刪除(D
7、elete)修改修改(Update)查詢查詢(Select)資料控制語言10/107結構化程式語言(2/3)續續下頁 資料定義語言 資料操作語言 資料控制語言資料控制語言 Data Control Language，簡稱，簡稱DCL 進行授權和撤銷之安全存取控制的管理和操作進行授權和撤銷之安全存取控制的管理和操作 11/107結構化程式語言(3/3)目的在於將不同系統或不同用途的相關物件做一區隔或分類，達到管理上的方便在存取控制上的適當授權，達到資料保密上的安全性，避免未授權的使用者讀取或異動其中的資料12/107定義綱要(Schema)13/107定義綱要之基本語法為人力資源部門建立一獨立的
8、綱要【說明】(語法一)僅以最簡單方式建立一名為HumanResource的綱要。【語法】(語法一)14/107【範例6-1】(1/4)CREATE SCHEMA HumanResource 為人力資源部門建立一獨立的綱要【說明】(語法二)建立一個綱要名稱與使用者 hrOwner相同名稱，並授權給予此相同帳號為 hrOwner之使用者。【語法】(語法二)15/107【範例6-1】(2/4)CREATE SCHEMA AUTHORIZATION hrOwner未指定schema_name為人力資源部門建立一獨立的綱要【說明】(語法三)建立一個綱要名稱為HumanResource，並授權于帳號為hr
9、Owner的使用者。【語法】(語法三)16/107【範例6-1】(3/4)CREATE SCHEMA HumanResource AUTHORIZATION hrOwner schema_nameusername為人力資源部門建立一獨立的綱要【說明】(語法四)建立一個綱要名為 HumanResouce，並於建立此綱要時，建立一個資料表名為員工，與一檢視表名為男員工。【語法】(語法四)17/107【範例6-1】(4/4)CREATE SCHEMA HumanResource AUTHORIZATION CREATE TABLE 員工(員工編號 int PRIMARY KEY,性名 varchar
10、(12),性別 char(1)CREATE View 男員工ASSELECT*FROM 員工 WHERE 性別=男schema_element定義名為員工之資料表定義名為男員工之檢視表18/107刪除綱要之基本語法請刪除為人力資源部門所建立的綱要【說明】(語法一)在綱要內若尚有其他屬性、定義域或元素時，且希望一併刪除時，可以使用CASCADE的選項。【語法】(語法一)DROP SCHEMA HumanResource CASCADE19/107【範例6-2】(1/2)請刪除為人力資源部門所建立的綱要【說明】(語法二)在綱要內若沒有任何元素存在，則可以使用RESTRICT的選項。倘若尚有其他屬性
11、、定義域或元素時，使用RESTRICT的選項會阻止DROP命令執行，可避免不小心刪除掉綱要內所有的元素。【語法】(語法二)DROP SCHEMA HumanResource RESTRICT 20/107【範例6-2】(2/2)可能違反的限制 實體限制 定義域限制 參考完整性限制 使用者定義 解決方式 拒絕新增21/107新增操作可能違反的限制 可能違反的限制 參考完整性限制 解決方式 拒絕刪除(Restrict)連鎖性刪除(Delete Cascade)將參考到被刪除紀錄之外來鍵值設成空值(Set to Null Value)22/107刪除操作可能違反的限制23/107連鎖性刪除(Dele
12、te Cascade)(1/2)父資料表-訂單資料表子資料表-訂單明細資料表續續下頁24/107連鎖性刪除(Delete Cascade)(2/2)94010201Delete Cascade父資料表-訂單資料表子資料表-訂單明細資料表 可能違反的限制 若非更新主要鍵或外來鍵 定義域限制 使用者定義限制 若更新主要鍵或外來鍵 實體限制 定義域限制 參考完整性限制 使用者定義 解決方式 拒絕更新(Restrict)連鎖性更新(Update Cascade)將參考不到父資料表的外來鍵值設為空值(Set to Null Value)25/107更新操作可能違反的限制 26/107連鎖性更新(Upda
13、te Cascade)(1/2)父資料表-訂單資料表子資料表-訂單明細資料表續續下頁27/107連鎖性更新(Update Cascade)(2/2)Update Cascade父資料表-訂單資料表子資料表-訂單明細資料表28/107定義資料表之基本語法29/107資料庫ERD之父子關係圖6-4 資料庫ERD之主從關係建立順序建立順序(由上而下由上而下)【題目】依圖6-4定義出所有之資料表員工客戶供應商產品類別產品資料訂單訂單明細【說明】1)定義資料表的基本語法在第一欄為資料表之屬性，再定義每個屬性的資料型態，資料型態之後即是該屬性的限制。30/107【範例6-3】【員工】資料表之限制說明 在員
14、工資料表中，員工編號為此資料表之主要鍵(Primary Key)，姓名之屬性值不得為空值(Null Value)。【語法】(語法一)將員工編號為主要鍵之限制寫在下方的table level區，並將姓名不得為空值之限制，直接寫在姓名屬性後方。(語法二)將員工編號為主要鍵之限制直接寫在員工編號屬性後方。31/1071)建立【員工】資料表續續下頁32/107【客戶】資料表之限制說明 在客戶資料表中，客戶編號為此資料表之主要鍵(Primary Key)，公司名稱之屬性值不得為空值(Null Value)。【語法】(語法一)將客戶編號為主要鍵之限制寫在下方的table level區，並將公司名稱不得為空
15、值之限制，直接寫在公司名稱屬性後方。(語法二)將客戶編號為主要鍵之限制直接寫在客戶編號屬性後方。33/1072)建立【客戶】資料表續續下頁34/107【供應商】資料表之限制說明 在供應商資料表中，除了供應商編號為此資料表之主要鍵(Primary Key)之外，此資料表中的地址和電話屬性，不得不得同時同時為空值(Null Value)，也就是至少要有一個屬性有值。【語法】(語法一)將供應商編號為主要鍵之限制寫在下方的table level區，並將地址和電話屬性，不得同時不得同時為空值(Null Value)之限制，寫於table level區。(語法二)將供應商編號為主要鍵之限制直接寫在供應商編
16、號屬性後面，但將地址和電話屬性，不得同時不得同時為空值(Null Value)之限制，仍要寫於table level區，不得寫於個別的屬性後方。倘若將此限制寫於個別屬性後方，其語意將成為地址和電話兩個屬性皆不可皆不可為空值(Null Value)。35/1073)建立【供應商】資料表續續下頁36/107【產品類別】資料表之限制說明 在產品類別資料表中，類別編號為此資料表之主要鍵(Primary Key)，類別名稱之屬性值不得為空值(Null Value)。【語法】(語法一)將類別編號為主要鍵之限制寫在下方的table level區，並將類別名稱不得為空值之限制，直接寫在類別名稱屬性後方。(語法
17、二)將類別編號為主要鍵之限制直接寫在類別編號屬性後方。37/1074)建立【產品類別】資料表續續下頁38/107【產品資料】資料表之限制說明 在產品資料的資料表中，產品編號為此資料表之主要鍵(Primary Key)，類別編號為此資料表中一個外來鍵，參考產品類別資料表中的主要鍵類別編號；供應商編號為此資料表中另一個外來鍵，參考供應商資料表中的主要鍵供應商編號；並且庫存量和安全存量必須設預設值 0。【語法】(語法一)將產品編號為主要鍵及類別編號和供應商編號為兩個外來鍵之限制寫在下方的table level區，並將庫存量和安全存量設預設值為0之限制，直接寫在兩個屬性後方。(語法二)將產品編號為主要
18、鍵及類別編號和供應商編號為兩個外來鍵之限制直接寫在個別屬性後方。39/1075)建立【產品資料】資料表續續下頁40/107【訂單】資料表之限制說明 在訂單資料表中，訂單編號為此資料表之主要鍵(Primary Key)，員工編號為此資料表中一個外來鍵，參考員工資料表中的主要鍵員工編號；客戶編號為此資料表中另一個外來鍵，是參考客戶資料表中的主要鍵客戶編號。【語法】(語法一)將訂單編號為主要鍵及員工編號和客戶編號為兩個外來鍵之限制寫在下方的table level區。(語法二)將訂單編號為主要鍵及員工編號和客戶編號為兩個外來鍵之限制直接寫在個別屬性後方。41/1076)建立【訂單】資料表續續下頁42/
19、107【訂單明細】資料表之限制說明 訂單編號+產品編號兩個屬性結合成為此資料表之主要鍵(Primary Key)。單一個產品編號屬性又剛好為此資料表中一個外來鍵，參考訂單資料表中的主要鍵訂單編號，除了要達到參考完整性限制外，若有違反此限制時，採用連鎖刪除(DELETE CASCADE)和連鎖更新(UPDATE CASCADE)；產品編號為此資料表中另一個外來鍵，參考產品資料資料表中的主要鍵產品編號。除此之外，對於數量屬性也將限制必須大於0。43/1077)建立【訂單明細】資料表續續下頁【語法】(語法一)將所有的限制全部寫在下方的table level區。但是在外來鍵的限制方面，本資料表共有兩個
20、外來鍵，一為訂單編號參考訂單資料表的訂單編號，且要求在違反限制時，將使用連鎖刪除和連鎖更新，所以在其參考語法後方要再加上選項ON DELETE CASCADE ON UPDATE CASCADE。另一個外來鍵為產品編號參考產品資料資料表的產品編號，由於沒有特別限制在違反時的處理方式，預設沒有任何選項時，則為拒絕刪除和拒絕更新。最後為一個數量的限制必須大於0。(語法二)由於此資料表的主要鍵是由兩個屬性訂單編號和產品編號組合而成，所以並不可分別寫在屬性後方，否則在語意上將解釋為訂單編號為主要鍵，產品編號也是主要鍵，也就是此資料表有兩個主要鍵，這是不被允許的，所以只要多於一個屬性所組成的鍵，就必須要置於table level區，不可分別寫於屬性後方。其他限制都屬於單一個屬性的限制，所以皆可置於該屬性後面做限制。44/107建立【訂單明細】資料表續續下頁45/10746/107刪除資料表之基本語法 刪除順序與建立順序相反47/107
