结构化查询语言SQL

結構化查詢語言SQL2009/11/13•檔案系統(FileSystem)–檔案(File)•欄位(Fields)•紀錄(Record)•關聯式資料模型(RelationalDataModel)•關聯式資料庫管理系統(RDBMS)•物件導向之類別圖(UML-ClassDiagram)2/107各種系統之基本邏輯單位(1/4)續下頁•檔案系統(FileSystem)•關聯式資料模型(RelationalDataModel)–關聯(Relation)•屬性(Attribute)•值組(Tuple)•關聯式資料庫管理系統(RDBMS)•物件導向之類別圖(UML-ClassDiagram)3/107各種系統之基本邏輯單位(2/4)續下頁•檔案系統(FileSystem)•關聯式資料模型(RelationalDataModel)•關聯式資料庫管理系統(RDBMS)–資料表(Table)•行(Column)•列(Row)•物件導向之類別圖(UML-ClassDiagram)4/107各種系統之基本邏輯單位(3/4)續下頁•檔案系統(FileSystem)•關聯式資料模型(RelationalDataModel)•關聯式資料庫管理系統(RDBMS)•物件導向之類別圖(UML-ClassDiagram)–類別(Class)•屬性(Attribute)•物件(Object)/實例(Instance)5/107各種系統之基本邏輯單位(4/4)檔案系統(FileSystem)關聯式資料模型(RelationalDataModel)關聯式資料庫管理系統(RDBMS)物件導向之類別圖(UML-ClassDiagram)檔案(File)關聯(Relation)資料表(Table)類別(Class)欄位(Fields)屬性(Attribute)行(Column)屬性(Attribute)紀錄(Record)值組(Tuple)列(Row)物件(Object)/實例(Instance)6/107相關名詞比較表一：相關名詞比較•StructuredQueryLanguage•簡稱『SQL』，唸成Sequel•源自於1970年IBM公司的一項研究所延伸•由美國國家標準局(theAmericanNationalStandardsInstitute，簡稱ANSI)和國際標準組織(InternationalStandardsOrganization，簡稱ISO)訂定為國際依據之標準7/107結構化程式語言簡介(1/2)•1970年–稱為SQL1或SQL-86•1992年–稱為SQL2或稱為SQL-92(ISO/IEC9075:1992-DatabaseLanguage–SQL)的標準•1999年–稱為SQL3或稱為SQL-99(ISO/IEC9075-2:1999-DatabaseLanguages-SQL-Part2:Foundation(SQL/Foundation))•2003年–稱為SQL4或稱為SQL-2003(ISO/IEC9075-2:2003-DatabaseLanguages-SQL-Part2:Foundation(SQL/Foundation)).8/107結構化程式語言簡介(2/2)•資料定義語言–DataDefinitionLanguage，簡稱DDL–建立與維護資料庫內的相關物件–包括綱要(Schema)、資料表(Tables)、檢視表(Views)、預存程序(StoredProcedures)、觸發器(Triggers)、函數(Functions)、定義域(Domains)和限制(Constraints)…等等•資料操作語言•資料控制語言9/107結構化程式語言(1/3)續下頁•資料定義語言•資料操作語言–DataManipulationLanguage，簡稱DML–對資料的存取操作(Operations)，包括對資料的•新增(Insert)•刪除(Delete)•修改(Update)•查詢(Select)•資料控制語言10/107結構化程式語言(2/3)續下頁•資料定義語言•資料操作語言•資料控制語言–DataControlLanguage，簡稱DCL–進行授權和撤銷之安全存取控制的管理和操作11/107結構化程式語言(3/3)•目的在於將不同系統或不同用途的相關物件做一區隔或分類，達到管理上的方便•在存取控制上的適當授權，達到資料保密上的安全性，避免未授權的使用者讀取或異動其中的資料12/107定義『綱要』(Schema)13/107定義『綱要』之基本語法•為人力資源部門建立一獨立的綱要•【說明】–(語法一)–僅以最簡單方式建立一名為『HumanResource』的綱要。•【語法】–(語法一)14/107【範例6-1】(1/4)CREATESCHEMAHumanResource•為人力資源部門建立一獨立的綱要•【說明】–(語法二)–建立一個綱要名稱與使用者『hrOwner』相同名稱，並授權給予此相同帳號為『hrOwner』之使用者。•【語法】–(語法二)15/107【範例6-1】(2/4)CREATESCHEMAAUTHORIZATIONhrOwner未指定schema_name•為人力資源部門建立一獨立的綱要•【說明】–(語法三)–建立一個綱要名稱為『HumanResource』，並授權于帳號為『hrOwner』的使用者。•【語法】–(語法三)16/107【範例6-1】(3/4)CREATESCHEMAHumanResourceAUTHORIZATIONhrOwnerschema_nameusername•為人力資源部門建立一獨立的綱要•【說明】–(語法四)–建立一個綱要名為『HumanResouce』，並於建立此綱要時，建立一個資料表名為『員工』，與一檢視表名為『男員工』。•【語法】–(語法四)17/107【範例6-1】(4/4)CREATESCHEMAHumanResourceAUTHORIZATIONCREATETABLE員工(員工編號intPRIMARYKEY,性名varchar(12),性別char(1))CREATEView男員工ASSELECT*FROM員工WHERE性別=‘男’schema_element定義名為『員工』之資料表定義名為『男員工』之檢視表18/107刪除『綱要』之基本語法•請刪除為人力資源部門所建立的綱要•【說明】–(語法一)–在綱要內若尚有其他屬性、定義域或元素時，且希望一併刪除時，可以使用CASCADE的選項。•【語法】–(語法一)–DROPSCHEMAHumanResourceCASCADE19/107【範例6-2】(1/2)•請刪除為人力資源部門所建立的綱要•【說明】–(語法二)–在綱要內若沒有任何元素存在，則可以使用RESTRICT的選項。倘若尚有其他屬性、定義域或元素時，使用RESTRICT的選項會阻止DROP命令執行，可避免不小心刪除掉綱要內所有的元素。•【語法】–(語法二)–DROPSCHEMAHumanResourceRESTRICT20/107【範例6-2】(2/2)•可能違反的限制–實體限制–定義域限制–參考完整性限制–使用者定義•解決方式–拒絕新增21/107『新增操作』可能違反的限制•可能違反的限制–參考完整性限制•解決方式–拒絕刪除(Restrict)–連鎖性刪除(DeleteCascade)–將參考到被刪除紀錄之外來鍵值設成空值(SettoNullValue)22/107『刪除操作』可能違反的限制23/107連鎖性刪除(DeleteCascade)(1/2)父資料表-『訂單』資料表子資料表-『訂單明細』資料表續下頁24/107連鎖性刪除(DeleteCascade)(2/2)94010201DeleteCascade父資料表-『訂單』資料表子資料表-『訂單明細』資料表•可能違反的限制–若非更新主要鍵或外來鍵•定義域限制•使用者定義限制–若更新主要鍵或外來鍵•實體限制•定義域限制•參考完整性限制•使用者定義•解決方式–拒絕更新(Restrict)–連鎖性更新(UpdateCascade)–將參考不到父資料表的外來鍵值設為空值(SettoNullValue)25/107『更新操作』可能違反的限制26/107連鎖性更新(UpdateCascade)(1/2)父資料表-『訂單』資料表子資料表-『訂單明細』資料表續下頁27/107連鎖性更新(UpdateCascade)(2/2)UpdateCascade父資料表-『訂單』資料表子資料表-『訂單明細』資料表28/107定義資料表之基本語法29/107資料庫ERD之父子關係圖6-4資料庫ERD之主從關係建立順序(由上而下)•【題目】–依圖6-4定義出所有之資料表1)員工2)客戶3)供應商4)產品類別5)產品資料6)訂單7)訂單明細•【說明】–定義資料表的基本語法在第一欄為資料表之屬性，再定義每個屬性的資料型態，資料型態之後即是該屬性的限制。30/107【範例6-3】•【員工】資料表之限制說明–在員工資料表中，『員工編號』為此資料表之主要鍵(PrimaryKey)，『姓名』之屬性值不得為空值(NullValue)。•【語法】–(語法一)將『員工編號』為主要鍵之限制寫在下方的tablelevel區，並將『姓名』不得為空值之限制，直接寫在姓名屬性後方。–(語法二)將『員工編號』為主要鍵之限制直接寫在員工編號屬性後方。31/1071)建立【員工】資料表續下頁32/107•【客戶】資料表之限制說明–在客戶資料表中，『客戶編號』為此資料表之主要鍵(PrimaryKey)，『公司名稱』之屬性值不得為空值(NullValue)。•【語法】–(語法一)將『客戶編號』為主要鍵之限制寫在下方的tablelevel區，並將『公司名稱』不得為空值之限制，直接寫在公司名稱屬性後方。–(語法二)將『客戶編號』為主要鍵之限制直接寫在客戶編號屬性後方。33/1072)建立【客戶】資料表續下頁34/107•【供應商】資料表之限制說明–在供應商資料表中，除了『供應商編號』為此資料表之主要鍵(PrimaryKey)之外，此資料表中的『地址』和『電話』屬性，不得同時為空值(NullValue)，也就是至少要有一個屬性有值。•【語法】–(語法一)將『供應商編號』為主要鍵之限制寫在下方的tablelevel區，並將『地址』和『電話』屬性，不得同時為空值(NullValue)之限制，寫於tablelevel區。–(語法二)將『供應商編號』為主要鍵之限制直接寫在供應商編號屬性後面，但將『地址』和『電話』屬性，不得同時為空值(NullValue)之限制，仍要寫於tablelevel區，不得寫於個別的屬性後方。倘若將此限制寫於個別屬性後方，其語意將成為『地址』和『電話』兩個屬性皆不可為空值(NullValue)。35/1073)建立【供應商】資料表續下頁36/107•【產品類別】資料表之限制說明–在產品類別資料表中，『類別編號』為此資料表之主要鍵(PrimaryKey)，『類別名稱』之屬性值不得為空值(NullValue)。•【語法】–(語法一)將『類別編號』為主要鍵之限制寫在下方的tablelevel區，並將『類別名稱』不得為空值之限制，直接寫在類別名稱屬性後方。–(語法二)將『類別編號』為主要鍵之限制直接寫在類別編號屬性後方。37/1074)建立【產品類別】資料表續下頁38/107•【產品資料】資料表之限制說明–在產品資料的資料表中，『產品編號』為此資料表之主要鍵(PrimaryKey)，『類別編號』為此資料表中一個外來鍵，參考『產品類別』資料表中的主要鍵『類別編號』；『供應商編號』為此資料表中另一個外來鍵，參考『供應商』資料表中的主要鍵『供應商編號』；並且庫存量和安全存量必須設預設值0。•【語法】–(語法一)將『產品編號』為主要鍵及『類別編號』和『供應商編號』為兩個外來鍵之限制寫在下方的tablelevel區，並將『庫存量』和『安全存量』設預設值為0之限制，直接寫在