Chap 10 SQL定義、操作與控制指令.

Presentation on theme: "Chap 10 SQL定義、操作與控制指令."— Presentation transcript:

1 Chap 10 SQL定義、操作與控制指令

2 SQL結構化查詢語言 「SQL」（Structured Query Language）的全名是結構化查詢語言，筆者簡稱為SQL語言，SQL語言是「ANSI」（American National Standards Institute）制定的標準資料庫語言，其版本分為： 1989年的ANSI-SQL 89 1992年制定的ANSI-SQL 92，也稱為SQL 2，這是目前關聯式資料庫的標準語言 最新版ANSI-SQL 99稱為SQL 3，適用在物件關聯或導向式資料庫的SQL語言。

3 10-1-1 SQL結構化查詢語言 SQL語言依指令功能，可以分成三類：
資料定義語言DDL（Data Definition Language）：建立資料表、視界和索引等的SQL指令。 資料操作語言DML（Data Manipulation Language）：資料表記錄插入、刪除、更新和查詢指令。 資料控制語言DCL（Data Control Language）：資料庫安全管理的權限設定指令。

4 關聯式資料庫管理系統的查詢最佳化 SQL指令需要轉換成低階機器語言指令來執行查詢，其轉換的步驟即查詢處理模組（Query Processor）子系統的執行順序：

5 10-1-2 關聯式資料庫管理系統的查詢最佳化 SQL查詢的步驟
第二步：在查詢最佳化模組將它轉換成中間格式關聯式代數建立的「查詢樹」（Query Tree），然後執行最佳化處理產生「執行計劃」。 第三步：使用RDBMS提供的選擇、合併、投影等關聯代數運算子的演算法，從執行計劃產生所需的程式碼。 第四步：使用直譯或編譯方式將程式碼轉換成機器語言後，就可以執行SQL指令產生查詢結果。

6 10-1-2 關聯式資料庫管理系統的查詢最佳化 查詢樹（Query Tree）是一棵關聯式代數運算式的樹狀結構，查詢樹的節點規則，如下所示：
關聯表是樹的葉節點（Leaf Nodes）。 關聯式代數運算子是中間節點（Internal Nodes）。

7 查詢最佳化-查詢樹圖例 SELECT Students.name, Classes.room FROM Students, Classes
WHERE Students.sid = Classes.sid and Students.GPA >= 3.0 and Classes.c_no = ‘CS222’

8 查詢最佳化模組 查詢最佳化模組（Query Optimizer）的功能是在最佳化查詢樹，讓查詢樹執行的更有效率。
最佳化的作法有很多種，最簡單的方式是將查詢樹的節點轉換成更有效率且相等功能的節點，這些最佳化規則稱為啟發式查詢最佳化（Heuristic Query Optimization）技術。

9 查詢最佳化技術 啟發式查詢最佳化（Heuristic Query Optimization）：這是規則基礎的最佳化，使用關聯式代數已知的轉換規則產生最有效率的執行計劃，例如：將卡笛生乘積運算和之後的選擇運算以合併運算來取代。 造句法式查詢最佳化（Syntactical Query Optimization）：以查詢樹自行造句找出所有同等功能的可能關聯式代數運算式，其最佳化的依據主要在評估是否有索引可以改進執行效率，以便找出最佳的執行計劃。

10 查詢最佳化技術 成本基礎式查詢最佳化（Cost-Based Query Optimization）：使用RDBMS各種資訊評估使用那一種順序執行關聯式代數運算式擁有最低的成本，以找出最有效率的存取方式，這是SQL Server和目前大多數RDBMS採用的最佳化技術。 語意式查詢最佳化（Semantic Query Optimization）：語意式查詢最佳化需要查詢最佳化模組了解資料庫綱要，可以自行依所知的系統限制來簡化或刪除查詢結果是空集合的部分查詢，而且可以自行評估是否使用索引來加速資料存取。

11 關聯式資料庫管理系統的查詢最佳化 執行計劃（Execution Plan）是從已經最佳化的查詢樹轉換而得的一個執行策略。執行計劃包含如何存取每一個關聯表的資料存取方式。 例如：關聯表使用那一個屬性排序，或是否存在其他屬性的索引。

12 執行計劃演算法 選擇運算：使用線性搜尋（Linear Search）、二元搜尋（Binary Search）或雜湊表搜尋法（Hash Table Search）來找尋值組，在索引部分可以使用叢集索引（Clustering Index）或B-樹演算法。 合併運算：使用巢狀迴圈合併（Nested-loop Join）、單迴圈合併（Single-loop Join）、排序合併（Sort Merge Join）和雜湊合併（Hashing Merge Join）等多種合併演算法。 投影運算：沒有特別的演算法，因為只是刪除一些關聯表的屬性。

13 Instructors與Courses資料表

14 Classes資料表 Classes資料表

15 10-4 資料定義語言 SQL的資料定義語言（DDL）是用來建立關聯式資料庫綱要，也就是在資料庫建立每一個關聯表綱要、視界、索引和設定完整性限制條件。 DDL指令可以分成數種，如下所示： 建立資料表指令：SQL指令建立基底關聯表是使用CREATE TABLE指令，同時還可以設定關聯表的完整性限制條件。 建立視界指令：建立視界（Views）的指令是CREATE VIEW。 建立索引指令：建立資料表索引的DDL指令，也是在本節說明。

16 CREATE TABLE 語法 CREATE TABLE table_name ( column1 datatype [ NOT NULL | NULL ] [ DEFAULT value1 ], column2 datatype [ NOT NULL | NULL ] [ DEFAULT value2 ], column3 datatype [ NOT NULL | NULL ] [ DEFAULT value3 ], ………. columnN datatype [ NOT NULL | NULL ] [ DEFAULT valueN ] [ , PRIMARY KEY (column_list) ] [ , FOREIGN KEY (column_list) REFERENCES table_name (column_list) [ , CHECK (expr) ] )

17 CREATE TABLE-語法說明 NOT NULL | NULL：欄位值是否可以為虛值，如果沒有指明，預設是NULL，可以是空值。 DEFAULT：指定欄位的預設值，如果欄位沒有輸入資料，預設是填入之後的value1~N值。 PRIMARY KEY ()：指定資料表的主鍵，括號內是主鍵的欄位，如果是複合鍵，欄位請使用逗號分隔。 FOREIGN KEY ()/REFERENCES ()：指定資料表的外來鍵，括號為外來鍵的欄位清單，REFERENCE是參考的資料表，括號是參考資料表的主鍵。 CHECK ()：設定欄位的限制條件，在括號內是條件的運算式。

18 10-4-1 CREATE TABLE新增關聯表 建立Students資料表： 在School資料庫建立Students資料表：
sid CHAR(4) NOT NULL , name VARCHAR(12) NOT NULL , tel VARCHAR(15) , birthday DATETIME , GPA FLOAT , PRIMARY KEY (sid) , CHECK (GPA >= 0.0 and GPA <= 4.0) )

19 10-4-1 CREATE TABLE新增關聯表 建立Instructors資料表：
在School資料庫建立Instructors資料表的SQL指令，主鍵是eid欄位，如下所示： CREATE TABLE Instructors ( eid CHAR(4) NOT NULL , name VARCHAR(12) NOT NULL , rank VARCHAR(10) , department VARCHAR(5) , PRIMARY KEY (eid) )

20 10-4-1 CREATE TABLE新增關聯表 建立Courses資料表：
在School資料庫建立Courses資料表的SQL指令，主鍵是c_no欄位，如下所示： CREATE TABLE Courses ( c_no CHAR(5) NOT NULL , title VARCHAR(30) NOT NULL , credits INT DEFAULT 3, PRIMARY KEY (c_no) )

21 10-4-1 CREATE TABLE新增關聯表 建立Classes資料表： CREATE TABLE Classes (
eid CHAR(4) NOT NULL , sid CHAR(4) NOT NULL , c_no CHAR(5) NOT NULL , time DATETIME , room VARCHAR(8) , score FLOAT , PRIMARY KEY (eid, sid, c_no) , FOREIGN KEY (eid) REFERENCES Instructors (eid) , FOREIGN KEY (sid) REFERENCES Students (sid) , FOREIGN KEY (c_no) REFERENCES Courses (c_no) )

22 10-4-2 ALTER TABLE更改關聯表綱要 新增/刪除資料表欄位
ALTER TABLE table_name ADD column_name datatype DROP COLUMN column_name

23 10-4-2 ALTER TABLE更改關聯表綱要 新增資料表欄位範例
在Students資料表新增SSN欄位的身份證字號，資料型態是CHAR(10) ，其SQL指令： ALTER TABLE Students ADD SSN CHAR(10) 刪除資料表欄位範例 在Students資料表刪除欄位SSN，SQL指令： DROP COLUMN SSN

24 10-4-2 ALTER TABLE更改關聯表綱要 新增/刪除資料表的主鍵
ALTER TABLE table_name ADD PRIMARY KEY (column_list) DROP PRIMARY KEY

25 10-4-2 ALTER TABLE更改關聯表綱要 新增主鍵範例
使用ALTER TABLE指令新增Employees的主鍵SSN，其SQL指令如下所示： ALTER TABLE Employees ADD PRIMARY KEY (SSN)

26 10-4-2 ALTER TABLE更改關聯表綱要 新增/刪除資料表的外來鍵
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_list) REFERENCES table_name (column_list) DROP CONSTRAINT constraint_name

27 10-4-2 ALTER TABLE更改關聯表綱要 新增外來鍵範例
以上一節的Employees資料表為例，使用ALTER TABLE指令新增Employees資料表的外來鍵eid欄位，它是參考到Instructors的eid欄位，其SQL指令如下所示： ALTER TABLE Employees ADD CONSTRAINT constraint_eid FOREIGN KEY (eid) REFERENCES Instructors (eid)

28 10-4-2 ALTER TABLE更改關聯表綱要 刪除外來鍵範例
在Employees資料表刪除外來鍵，也就是刪除名為constraint_eid的限制條件，其SQL指令如下所示： ALTER TABLE Employees DROP CONSTRAINT constraint_eid

29 10-4-2 ALTER TABLE更改關聯表綱要 新增/刪除欄位的限制條件
ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT value FOR column_name | CHECK (expr) DROP CONSTRAINT constraint_name

30 10-4-2 ALTER TABLE更改關聯表綱要 新增欄位預設值範例
在Employees資料表新增name欄位預設值，其SQL指令如下所示： ALTER TABLE Employees ADD CONSTRAINT default_name DEFAULT ‘陳會安’ FOR name

31 10-4-2 ALTER TABLE更改關聯表綱要 新增欄位的限制條件範例
在Students學生資料表新增生日birthday欄位限制條件，其SQL指令如下所示： ALTER TABLE Students ADD CONSTRAINT check_birthday CHECK (birthday >= ' ' and birthday <= ' ')

32 DROP TABLE刪除整個關聯表 對於資料庫已經存在的基底關聯表，可以使用DROP TABLE 指令刪除指定的資料表，刪除的範圍包含資料表索引、記錄和視界，其基本語法如下所示： DROP TABLE table_name 上述語法是從資料庫刪除table_name的資料表。 刪除Employees資料表的SQL指令： DROP TABLE Employees

33 10-4-4 TRUNCAT TABLE刪除關聯表內容
TRUNCATE TABLE table_name 刪除Classes資料表內容，其SQL指令如下所示： TRUNCATE TABLE Classes

34 CREATE INDEX建立索引 以單層有序索引（Single Level Ordered Index）來說，使用主鍵排序建立的索引稱為「主索引」（Primary Index）。非主鍵排序的索引稱為叢集索引（Clustering Index），其他非排序欄位稱為「次索引」（Secondary Index），也稱為非叢集索引（Non-clustering Index）。 一個基底關聯表只能擁有一個主索引或叢集索引來決定記錄的排序方式，如果還有其他索引一定是次索引，在一個基底關聯表可以擁有多個次索引。

35 10-4-5 CREATE INDEX建立索引 DDL的CREATE INDEX指令可以建立基底關聯表的索引，其基本語法如下所示：
CREATE [ UNIQUE ] INDEX index_name ON table_name (column_name[(length)],... ) [ ASC|DESC ] 上述語法可以建立SQL Server資料表的非叢集索引，UNIQUE是指索引值為唯一，表示不能有2筆記錄擁有相同索引值，index_name是索引名稱，column_name欄位如果不只一個，請使用逗號分隔。length欄位是長度，可以使用部分欄位值來建立索引資料，ASC是由小到大排序，DESC是由大到小。

36 10-4-5 CREATE INDEX建立索 建立資料表的索引範例
在Students學生資料表新增欄位name索引資料，其SQL指令如下所示： CREATE INDEX index_students ON Students(name)

37 10-4-6 DROP INDEX刪除索引 DDL的DROP INDEX指令可以刪除基底關聯表的索引資料，其基本語法如下所示：
DROP INDEX table_name.index_name 上述table_name是資料表名稱，index_name是索引名稱。 刪除資料表的索引： 在Students學生資料表刪除索引資料的SQL指令，如下所示： DROP INDEX Students.index_students

38 10-5 資料操作語言 SQL的資料操作語言（DML）可以新增、刪除和更新資料表的記錄，換句話說，DDL是建立資料庫綱要，DML是建立資料庫副本（Instance）。 SQL語言的資料表操作指令一共有3個，如下表所示： INSERT：在資料表插入一筆新記錄 UPDATE：更新資料表的記錄，這些記錄是已經存在的記錄 DELETE：刪除資料表的記錄

39 10-5-1 INSERT新增記錄-語法 SQL語言的INSERT指令可以新增一筆記錄到資料表，INSERT指令的基本語法，如下所示：
INSERT INTO table (column1,column2,…..) VALUES ('value1', 'value2 ', …) 上述SQL指令的table為新增記錄的資料表名稱，column1~n是資料表內的欄位名稱，不需全部欄位，不過需要包含所有非虛值欄位，value1~n是對應的欄位值。

40 10-5-1 INSERT新增記錄-注意事項 INSERT指令的注意事項，如下所示： 不論是欄位或值的清單，都需要使用逗號分隔。
在INSERT指令VALUES的值中，數值不用單引號包圍，字元與日期/時間需要單引號括起。 INSERT指令的欄位名稱清單，並不需要和資料表定義的欄位數目或順序相同，只需列出需要值的欄位，不過括號內的欄位名稱順序要和VALUES值的順序相同。

41 10-5-1 INSERT新增記錄-範例 新增記錄： 在Students新增一筆學生記錄的SQL指令，如下所示：
INSERT INTO Students VALUES ('S001','陳會安', ' ','1967/09/03',3.7) 上述SQL指令可以新增一筆記錄。

42 10-5-2 UPDATE更新記錄-語法 SQL語言的UPDATE指令可以將資料表符合條件的記錄，更新指定欄位的內容：
UPDATE table SET column1 = ‘value1’ WHERE conditions table是資料表，SET子句column1是資料表的欄位名稱，不用全部只需更新欄位，value1是更新欄位值。 更新的欄位不只一個時使用逗號分隔： UPDATE table SET column1 = 'value1' , column2 = 'value2' WHERE conditions

43 10-5-2 UPDATE更新記錄 UPDATE指令的注意事項，如下所示： 更新記錄：
WHERE子句是UPDATE指令的必要元素，如果沒有此條件，資料表所有記錄欄位都會被更新。 更新欄位值如果為數值不用單引號包圍，字元與日期/時間需要使用單引號包圍。 更新記錄： 在資料表Students更改學號S001生日和GPA： UPDATE Students SET birthday=' ', GPA=3.0 WHERE sid = 'S001'

44 DELETE刪除記錄-語法 SQL語言的DELETE指令可以將資料表符合條件的記錄刪除掉，DELETE指令的基本語法，如下所示： DELETE FROM table WHERE conditions table是資料表，WHERE子句conditions為刪除記錄的條件，口語來說是：「將符合conditions條件的記錄刪除掉」。

45 10-5-3 DELETE刪除記錄 DELETE指令的注意事項，如下所示： 刪除記錄： 在資料表Students刪除學號S001記錄：
WHERE子句是DELETE指令的必要元素，如果沒有此條件，資料表內的所有記錄都會被刪除掉。 WHERE條件可以使用=、<>、>、<=和>=等比較運算子。 WHERE條件如果不只一個條件，可以使用邏輯運算子and或or連結。 刪除記錄： 在資料表Students刪除學號S001記錄： DELETE FROM Students WHERE sid = 'S001'

46 10-6 資料控制語言-說明 SQL語言的資料控制語言（Data Control Language，DCL）可以定義資料庫安全管理所需的規則，授予或撤回使用者的相關權限，讓使用者： 只能執行授權的操作和處理擁有權限的資料。 限制無法執行或取得沒有授權的操作或資料。

47 10-6 資料控制語言-GRANT指令 GRANT指令可以授予資料庫使用者的權限，其基本語法如下所示：
GRANT { ALL [ PRIVILEGES ] | privileges [ (column [ ,columnn]) ] [,privileges…] } ON table_name TO user_name [,user_name ...] 上述語法可以在ON子句指定授予哪一個table_name資料表的權限，在privileges權限的括號中可以指定擁有哪些欄位的權限。

48 10-6 資料控制語言-REVOKE指令 REVOKE指令是對應GRANT指令，可以徹回某位使用者所授予的權限，其基本語法如下所示：
REVOKE { ALL [ PRIVILEGES ] | privileges [ (column [ ,columnn]) ] [,privileges…] } ON table_name FROM user_name [,user_name ...] 上述語法可以在ON子句指定徹回哪一個table_name資料表的權限，在privileges權限的括號中可以指定撤回哪些欄位的權限。