1 / 106

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

第六章 結構化查詢語言 SQL( 一 ) 資料庫系統理論與 實務. 本章內容. 6-1 簡介 6-2 資料定義語言 (DDL) 定義 「 綱要 」 (Schema) 定義 「 資料表 」 (Tables) 6-3 資料操作語言 (DML) 新增操作 (Inert Operation) 刪除操作 (Delete Operation) 更新操作 (Update Operation) 查詢操作 (Select Operation). 各種系統之基本邏輯單位 (1/4). 檔案系統 (File System) 檔案 (File) 欄位 (Fields)

tyrone
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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區，並將『公司名稱』不得為空值之限制，直接寫在公司名稱屬性後方。 • (語法二) 將『客戶編號』為主要鍵之限制直接寫在客戶編號屬性後方。

More Related