第六章 結構化查詢語言 SQL( 一 ) 資料庫系統理論與 實務 - PowerPoint PPT Presentation

slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
第六章 結構化查詢語言 SQL( 一 ) 資料庫系統理論與 實務 PowerPoint Presentation
Download Presentation
第六章 結構化查詢語言 SQL( 一 ) 資料庫系統理論與 實務

play fullscreen
1 / 106
第六章 結構化查詢語言 SQL( 一 ) 資料庫系統理論與 實務
254 Views
Download Presentation
tyrone
Download Presentation

第六章 結構化查詢語言 SQL( 一 ) 資料庫系統理論與 實務

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. 第六章 結構化查詢語言SQL(一)資料庫系統理論與實務

  2. 本章內容 • 6-1簡介 • 6-2資料定義語言(DDL) • 定義「綱要」(Schema) • 定義「資料表」(Tables) • 6-3資料操作語言(DML) • 新增操作(Inert Operation) • 刪除操作(Delete Operation) • 更新操作(Update Operation) • 查詢操作(Select Operation)

  3. 各種系統之基本邏輯單位(1/4) • 檔案系統(File System) • 檔案(File) • 欄位(Fields) • 紀錄(Record) • 關聯式資料模型(Relational Data Model) • 關聯式資料庫管理系統(RDBMS) • 物件導向之類別圖(UML - Class Diagram)

  4. 各種系統之基本邏輯單位(2/4) • 檔案系統(File System) • 關聯式資料模型(Relational Data Model) • 關聯(Relation) • 屬性(Attribute) • 值組(Tuple) • 關聯式資料庫管理系統(RDBMS) • 物件導向之類別圖(UML - Class Diagram)

  5. 各種系統之基本邏輯單位(3/4) • 檔案系統(File System) • 關聯式資料模型(Relational Data Model) • 關聯式資料庫管理系統(RDBMS) • 資料表(Table) • 行(Column) • 列(Row) • 物件導向之類別圖(UML - Class Diagram)

  6. 各種系統之基本邏輯單位(4/4) • 檔案系統(File System) • 關聯式資料模型(Relational Data Model) • 關聯式資料庫管理系統(RDBMS) • 物件導向之類別圖(UML - Class Diagram) • 類別(Class) • 屬性(Attribute) • 物件(Object) /實例(Instance)

  7. 相關名詞比較 表一:相關名詞比較

  8. 資料庫管理系統的相關物件 • 資料庫(Databases) • 『綱要』 (Schema) • 資料表(Tables) • 檢視表(Views) • 預存程序(Stored Procedures) • 函數(Functions) • 觸發器(Triggers) • 定義域(Domains) • 限制(Constraints) • 其他

  9. RDBMS Database Database Schema Schema Schema Schema TablesViewsStored proceduresTriggersFunctionsDomainsConstraints TablesViewsStored proceduresTriggersFunctionsDomainsConstraints TablesViewsStored proceduresTriggersFunctionsDomainsConstraints TablesViewsStored proceduresTriggersFunctionsDomainsConstraints 資料庫管理系統的相關物件 圖6-1 資料庫管理系統的結構

  10. Table 與 View • 『資料表』 (Tables) • 實際儲存資料內容的地方 • 以二維的方形表格來表達 • 『檢視表』 (Views) • 也稱為『虛擬資料表』 (Virtual Tables) • 『檢視表』和『資料表』非常類似 • 檢視表本身並不儲存任何的資料內容 • 查詢是自最下層的資料表,或是經由其他檢視表再自下層的資料表所取得的資料

  11. Join Join views Join Join tables A B C D E View與Table的關係圖 Z 檢視表X,是源自於兩個(或更多)資料表B與C的『合併』(Join)處理 檢視表W,是源自於單一個資料表A 檢視表Y,直接來源是兩個(或更多)檢視表W和X 檢視表Z,它的直接來源是合併一個(或更多)檢視表和兩個(或更多)資料表後 Y W X 圖6-2 View與Table的關係圖

  12. 男業務的訂單資料 Join Views 女業務 男業務 tables 員工 訂單 客戶 View的實際範例 圖6-3 View的範例

  13. 預存程序(Stored Procedures)與觸發器(Triggers)(1/2) • 預存程序(Stored Procedures) • 程序式語言(Procedural Language) • 預先設計好的處理程序 • 經常使用的處理流程且重複使用 • 降低錯誤率的發生 • 執行效率上會較好 • 由使用者或是透過應用程式的呼叫而被動執行 • 觸發器(Triggers)

  14. 預存程序(Stored Procedures)與觸發器(Triggers)(2/2) • 預存程序(Stored Procedures) • 觸發器(Triggers) • 程序式語言(Procedural Language) • 預先設計好的處理程序 • 相依於資料表 • 資料表被異動 (新增、刪除或修改) 時,主動地觸發不同的觸發器來執行其工作 • 有不同的觸發時機

  15. 結構化程式語言簡介(1/2) • Structured Query Language簡稱『SQL』 • 源自於1970年IBM公司的一項研究所延伸 • 由美國國家標準局(the American National Standards Institute,簡稱ANSI)和國際標準組織(International Standards Organization,簡稱ISO)訂定為國際依據之標準

  16. 結構化程式語言簡介(2/2) • 1970年 • 稱為SQL1或SQL-86 • 1992年 • 稱為SQL2或稱為SQL-92 ( ISO/IEC 9075:1992 - Database Language – SQL ) 的標準 • 1999年 • 稱為SQL3或稱為SQL-99 ( ISO/IEC 9075-2:1999 - Database Languages - SQL - Part 2: Foundation (SQL/Foundation)) • 2003年 • 稱為SQL4或稱為SQL-2003 (ISO/IEC 9075-2:2003 - Database Languages - SQL - Part 2: Foundation (SQL/Foundation)).

  17. 結構化程式語言(1/3) • 資料定義語言 • Data Definition Language,簡稱DDL • 建立與維護資料庫內的相關物件 • 包括綱要(Schema)、資料表(Tables)、檢視表(Views)、預存程序(Stored Procedures)、觸發器(Triggers)、函數(Functions)、定義域(Domains)和限制(Constraints)…等等 • 資料操作語言 • 資料控制語言

  18. 結構化程式語言(2/3) • 資料定義語言 • 資料操作語言 • Data Manipulation Language,簡稱DML • 對資料的存取操作(Operations) ,包括對資料的 • 新增 ( Insert ) • 刪除 ( Delete ) • 修改 ( Update ) • 查詢 ( Select ) • 資料控制語言

  19. 結構化程式語言(3/3) • 資料定義語言 • 資料操作語言 • 資料控制語言 • Data Control Language,簡稱DCL • 進行授權和撤銷之安全存取控制的管理和操作

  20. 本章內容 • 6-1簡介 • 6-2資料定義語言(DDL) • 定義「綱要」(Schema) • 定義「資料表」(Tables) • 6-3資料操作語言(DML) • 新增操作(Inert Operation) • 刪除操作(Delete Operation) • 更新操作(Update Operation) • 查詢操作(Select Operation)

  21. 6-2 資料定義語言(DDL) • 『定義』以下物件 • 資料庫(Database) • 綱要(Schema) • 資料表(Table) • 檢視表(View) • 其他不同物件 • 定義的順序 • 父資料表先於子資料表 • 如下頁圖

  22. 建立順序(由上而下) 資料庫ERD之父子關係 圖6-4 資料庫ERD之主從關係

  23. 定義『綱要』(Schema) • 目的在於將不同系統或不同用途的相關物件做一區隔或分類,達到管理上的方便 • 在存取控制上的適當授權,達到資料保密上的安全性,避免未授權的使用者讀取或異動其中的資料

  24. 定義『綱要』之基本語法

  25. 【範例6-1】(1/4) • 為人力資源部門建立一獨立的綱要 • 【說明】 • (語法一) • 僅以最簡單方式建立一名為『HumanResource』的綱要。 • 【語法】 • (語法一) CREATE SCHEMA HumanResource

  26. 未指定schema_name 【範例6-1】(2/4) • 為人力資源部門建立一獨立的綱要 • 【說明】 • (語法二) • 建立一個綱要名稱與使用者 『hrOwner』相同名稱,並授權給予此相同帳號為 『hrOwner』之使用者 。 • 【語法】 • (語法二) CREATE SCHEMA AUTHORIZATION hrOwner

  27. schema_name username 【範例6-1】(3/4) • 為人力資源部門建立一獨立的綱要 • 【說明】 • (語法三) • 建立一個綱要名稱為『HumanResource』,並授權于帳號為『hrOwner』的使用者。 • 【語法】 • (語法三) CREATE SCHEMA HumanResource AUTHORIZATION hrOwner

  28. 定義名為『員工』之資料表 schema_element 定義名為『男員工』之檢視表 【範例6-1】(4/4) • 為人力資源部門建立一獨立的綱要 • 【說明】 • (語法四) • 建立一個綱要名為 『HumanResouce』,並於建立此綱要時,建立一個資料表名為『員工』,與一檢視表名為『男員工』。 • 【語法】 • (語法四) CREATE SCHEMA HumanResource AUTHORIZATION CREATE TABLE 員工 ( 員工編號 int PRIMARY KEY, 性名 varchar(12), 性別 char(1) ) CREATE View 男員工 AS SELECT * FROM 員工 WHERE 性別 = ‘男’

  29. 刪除『綱要』之基本語法

  30. 【範例6-2】(1/2) • 請刪除為人力資源部門所建立的綱要 • 【說明】 • (語法一) • 在綱要內若尚有其他屬性、定義域或元素時,且希望一併刪除時,可以使用CASCADE的選項。 • 【語法】 • (語法一) • DROP SCHEMA HumanResource CASCADE

  31. 【範例6-2】(2/2) • 請刪除為人力資源部門所建立的綱要 • 【說明】 • (語法二) • 在綱要內若沒有任何元素存在,則可以使用RESTRICT的選項。倘若尚有其他屬性、定義域或元素時,使用RESTRICT的選項會阻止DROP命令執行,可避免不小心刪除掉綱要內所有的元素。 • 【語法】 • (語法二) • DROP SCHEMA HumanResource RESTRICT

  32. 定義『資料表』的限制(1/4) • 實體限制 • Entity Constraint • 限制一個資料表內的每一筆紀錄都必須具有唯一性(Uniqueness) • 主要鍵具有兩個特性 • 『鍵值不可重複』 • 『鍵值不可為空值』 • 定義域限制 • 參考完整性限制 • 使用者定義限制

  33. 定義『資料表』的限制(2/4) • 實體限制 • 定義域限制 • Domain Constraint • 資料表中的屬性設定一個限制條件 • 值域範圍(range) • 資料型態 • 參考完整性限制 • 使用者定義限制

  34. 定義『資料表』的限制(3/4) • 實體限制 • 定義域限制 • 參考完整性限制 • Referential Integrity Constraint • 兩個資料表之間關聯性(Relationship)的限制 • 『子資料表』與『父資料表』之間的相依關係 • 使用者定義限制

  35. 定義『資料表』的限制(4/4) • 實體限制 • 定義域限制 • 參考完整性限制 • 使用者定義限制 • User-Defined Constraint • 由使用者依據企業之需求而自訂的其他限制

  36. 『新增操作』可能違反的限制 • 可能違反的限制 • 實體限制 • 定義域限制 • 參考完整性限制 • 使用者定義 • 解決方式 • 拒絕新增

  37. 『刪除操作』可能違反的限制 • 可能違反的限制 • 參考完整性限制 • 解決方式 • 拒絕刪除(Restrict) • 連鎖性刪除(Delete Cascade) • 將參考到被刪除紀錄之外來鍵值設成空值(Set to Null Value)

  38. 連鎖性刪除(Delete Cascade)(1/2) 父資料表 - 『訂單』資料表 子資料表 - 『訂單明細』資料表

  39. 連鎖性刪除(Delete Cascade)(2/2) 94010201 Delete Cascade 父資料表 - 『訂單』資料表 子資料表 - 『訂單明細』資料表

  40. 『更新操作』可能違反的限制 • 可能違反的限制 • 若非更新主要鍵或外來鍵 • 定義域限制 • 使用者定義限制 • 若更新主要鍵或外來鍵 • 實體限制 • 定義域限制 • 參考完整性限制 • 使用者定義 • 解決方式 • 拒絕更新(Restrict) • 連鎖性更新(Update Cascade) • 將參考不到父資料表的外來鍵值設為空值(Set to Null Value)

  41. 連鎖性更新(Update Cascade)(1/2) 父資料表 - 『訂單』資料表 子資料表 - 『訂單明細』資料表

  42. 連鎖性更新(Update Cascade)(2/2) Update Cascade 父資料表 - 『訂單』資料表 子資料表 - 『訂單明細』資料表

  43. 本章內容 • 6-1簡介 • 6-2資料定義語言(DDL) • 定義「綱要」(Schema) • 定義「資料表」(Tables) • 6-3資料操作語言(DML) • 新增操作(Inert Operation) • 刪除操作(Delete Operation) • 更新操作(Update Operation) • 查詢操作(Select Operation)

  44. 定義資料表之基本語法

  45. 建立順序(由上而下) 資料庫ERD之父子關係 圖6-4 資料庫ERD之主從關係

  46. 【範例6-3】 • 【題目】 • 依圖6-4定義出所有之資料表 • 員工 • 客戶 • 供應商 • 產品類別 • 產品資料 • 訂單 • 訂單明細 • 【說明】 • 定義資料表的基本語法在第一欄為資料表之屬性,再定義每個屬性的資料型態,資料型態之後即是該屬性的限制。

  47. 1)建立【員工】資料表 • 【員工】資料表之限制說明 • 在員工資料表中,『員工編號』為此資料表之主要鍵(Primary Key),『姓名』之屬性值不得為空值(Null Value)。 • 【語法】 • (語法一) 將『員工編號』為主要鍵之限制寫在下方的table level區,並將『姓名』不得為空值之限制,直接寫在姓名屬性後方。 • (語法二) 將『員工編號』為主要鍵之限制直接寫在員工編號屬性後方。

  48. 2)建立【客戶】資料表 • 【客戶】資料表之限制說明 • 在客戶資料表中,『客戶編號』為此資料表之主要鍵(Primary Key),『公司名稱』之屬性值不得為空值(Null Value)。 • 【語法】 • (語法一) 將『客戶編號』為主要鍵之限制寫在下方的table level區,並將『公司名稱』不得為空值之限制,直接寫在公司名稱屬性後方。 • (語法二) 將『客戶編號』為主要鍵之限制直接寫在客戶編號屬性後方。