HTML網頁基礎語言

Size: px

Start display at page:

Download "HTML網頁基礎語言"

牙边
4 years ago
Views:

1 第 8 章 SQL 定義操作與控制指令 8-1 SQL 語言的基礎 8-2 資料庫的實體資料模型 8-3 資料定義語言 8-4 資料操作語言 8-5 資料控制語言 1

2 8-1-1 SQL 結構化查詢語言 - 說明 SQL(Structured Query Language) 全名是結構化查詢語言 ANSI(American National Standards Institute) 制定的標準資料庫語言版本分為 1989 年的 ANSI-SQL 年制定的 ANSI-SQL 92 稱為 SQL 2, 關聯式資料庫的標準語言 1999 年制定的 ANSI-SQL 99 稱為 SQL 3, 適用在物件關聯或導向式資料庫 2

3 8-1-1 SQL 結構化查詢語言 - 種類 SQL 依指令功能, 分成三類資料定義語言 DDL(Data Definition Language) 建立資料表視界和索引等的 SQL 指令資料操作語言 DML(Data Manipulation Language) 資料表記錄的新增刪除更新和查詢指令資料控制語言 DCL(Data Control Language) 資料庫安全管理的權限設定指令 3

4 8-1-2 查詢最佳化 - 執行順序圖例 SQL 指令需要轉換成低階機器語言指令來執行查詢轉換的步驟即查詢處理模組 (Query Processor) 子系統的執行順序 4

5 8-1-2 查詢最佳化 -SQL 查詢的步驟第一步剖析 : 用查詢剖析器, 檢查 SQL 指令的語法是否正確, 且驗證屬性和關聯表名稱是否存在第二步最佳化 : 用查詢最佳化模組, 將 SQL 指令轉換成中間格式, 即關聯代數建立的查詢樹 (Query Tree), 然後執行最佳化處理, 產生執行計劃 (Execution Plan) 第三步產生程式碼 : 使用 RDBMS 提供的選擇合併投影等關聯代數運算子的演算法, 從執行計劃產生所需的程式碼第四步執行程式碼 : 用直譯或編譯方式將程式碼轉換成機器語言後, 就可以執行 SQL 指令產生查詢結果

6 8-2 實體資料模型 - 實體關聯圖 score name 6

7 8-2 實體資料模型 - 關聯表綱要 7

8 8-2 資料庫的真實資料 8

9 8-2 實體資料模型 -Students 資料表使用 SQL 指令新增所需的資料表, 建立 School 學校資料庫 Students 資料表欄位名稱資料類型長度欄位說明 sid CHAR 4 學生的學號, 主鍵 name VARCHAR2 12 姓名 tel VARCHAR2 15 電話 birthday DATE N/A 生日 GPA NUMBER 2 成績 9

10 8-2 實體資料模型 -Instructors 資料表 Instructors 資料表欄位名稱資料類型長度欄位說明 eid CHAR 4 講師的員工編號, 主鍵 name VARCHAR2 12 講師姓名 rank VARCHAR2 10 講師的等級 department VARCHAR2 5 所屬科系 10

11 8-2 實體資料模型 -Courses 資料表 Courses 資料表欄位名稱資料類型長度欄位說明 c_no CHAR 5 課程編號, 主鍵 title VARCHAR2 30 課程名稱 credits NUMBER 1 學分數 11

12 8-2 實體資料模型 -Classes 資料表 Classes 資料表欄位名稱資料類型長度欄位說明 eid CHAR 4 講師的員工編號, 參考 Instructors 資料表的 eid 欄位 sid CHAR 4 學生學號, 參考 Students 資料表的 sid 欄位 c_no CHAR 5 課程編號, 參考 Courses 資料表的 c_no 欄位 time CHAR 8 上課時間 room VARCHAR2 8 教室編號 score NUMBER 3 這門課程的成績 12

13 8-3-1 資料定義語言資料定義語言 (DDL) 建立關聯式資料庫綱要建立關聯表綱要視界索引和設定完整性限制條件建立關聯表指令 :CREATE TABLE 指令建立視界指令 :CREATE VIEW 指令建立索引指令 :CREATE INDEX 指令 13

14 8-3-2 CREATE TABLE 語法 CREATE TABLE table_name ( column1 datatype [ NOT NULL NULL ] [ DEFAULT value1 ] [CHECK (expr1)], column2 datatype [ NOT NULL NULL ] [ DEFAULT value2 ] [CHECK (expr2)], column3 datatype [ NOT NULL NULL ] [ DEFAULT value3 ] [CHECK (expr3)],. columnn datatype [ NOT NULL NULL ] [ DEFAULT valuen ] [CHECK (exprn)] [, CONSTRAINT constraint_name1 PRIMARY KEY (column_list) ] [, CONSTRAINT constraint_name2 FOREIGN KEY (column_list) REFERENCES table_name (column_list)] ); [X Y ]: [ ] 表示可有可無的選項, 為選項分隔符號 expr1~n: 表示一個以上的條件式 column_list: 表示欄位名稱序列 14

15 8-3-2 CREATE TABLE- 語法說明 NOT NULL NULL 欄位值是否可以為空值, 預設是 NULL 空值 DEFAULT 指定欄位的預設值, 如果欄位沒有輸入資料 CHECK ( ) 設定欄位的限制條件, 括號內是條件的運算式 PRIMARY KEY ( ) 指定資料表的主鍵, 括號內是主鍵的欄位 ( 集 ) 如果是複合式主鍵, 欄位名稱之間請使用逗號分隔 FOREIGN KEY ( )/REFERENCES table_name( ) FOREIGN KEY 指定資料表的外來鍵, 括號內為外來鍵的欄位 table_name 是被參考的關聯表, 括號內是被參考關聯表的主鍵欄位 15

16 8-3-2 建立 Students 資料表 SQL 指令 CREATE TABLE Students ( sid CHAR(4) NOT NULL, name VARCHAR2(12) NOT NULL, birthday DATE, GPA NUMBER(2,1) CHECK(GPA>=0.0 and GPA<=4.0), tel VARCHAR2(15), CONSTRAINT stu_sid_pk PRIMARY KEY (sid) ); 16

17 8-3-2 建立 Instructors 資料表 SQL 指令 CREATE TABLE Instructors ( eid CHAR(4) NOT NULL, name VARCHAR2(12) NOT NULL, rank VARCHAR2(10), department VARCHAR2(5), CONSTRAINT ins_eid_pk PRIMARY KEY (eid) ); 17

18 8-3-2 建立 Courses 資料表 SQL 指令 CREATE TABLE Courses ( c_no CHAR(5) NOT NULL, title VARCHAR2(30) NOT NULL, credits NUMBER(1) DEFAULT 3, CONSTRAINT cou_c_no_pk PRIMARY KEY (c_no) ); 18

19 8-3-2 建立 Classes 資料表 Classes 資料表 SQL 指令 CREATE TABLE Classes ( eid CHAR(4) NOT NULL, sid CHAR(4) NOT NULL, c_no CHAR(5) NOT NULL, time CHAR(8), room VARCHAR2(8), score NUMBER(3,0), CONSTRAINT cla_eid_sid_c_no_pk PRIMARY KEY (eid, sid, c_no), CONSTRAINT cla_eid_fk1 FOREIGN KEY (eid) REFERENCES Instructors (eid), CONSTRAINT cla_sid_fk2 FOREIGN KEY (sid) REFERENCES Students (sid), CONSTRAINT cla_c_no_fk3 FOREIGN KEY (c_no) REFERENCES Courses (c_no) ); 19

20 8-3-3 ALTER TABLE 更改關聯表綱要 - 語法新增 / 刪除資料表欄位 ALTER TABLE table_name ADD (column_name datatype, ) MODIFY (column_name datatype, ) DROP (column1, ) 20

21 8-3-3 ALTER TABLE- 範例在 Students 新增 SSN 欄位的身份證字號資料型態是 CHAR(10) SQL 指令 ALTER TABLE Students ADD (SSN CHAR(10)); 在 Students 刪除欄位 SSN SQL 指令 ALTER TABLE Students DROP (SSN); 21

22 8-3-3 異動主鍵 - 語法新增主鍵宣告 ALTER TABLE table_name 與課本內容有差距 ADD (CONSTRAINT constraint_name PRIMARY KEY (column_list)) 刪除主鍵宣告 ALTER TABLE table_name DROP (CONSTRAINT constraint_name) 22

23 8-3-3 異動外來鍵 - 語法新增外來鍵宣告 ALTER TABLE table_name ADD (CONSTRAINT constraint_name FOREIGN KEY (column_list) REFERENCES table_name (column_list)) 刪除外來鍵宣告 ALTER TABLE table_name DROP (CONSTRAINT constraint_name) 23

24 8-3-3 異動限制條件 - 語法新增欄位的限制條件宣告 ALTER TABLE table_name ADD (CONSTRAINT constraint_name CHECK (expr)) 刪除欄位的限制條件宣告 ALTER TABLE table_name DROP (CONSTRAINT constraint_name) 24

25 8-3-3 異動限制條件 - 範例在 Students 新增 GPA 欄位限制條件 SQL 指令 ALTER TABLE Students ADD (CONSTRAINT check_gpa CHECK (GPA >= 0 and GPA <= 4.0)); 25

26 8-3-4 DROP TABLE- 刪除整個關聯表刪除的範圍包含索引和記錄語法如下 DROP TABLE table_name 範例 : 刪除 Employees 關聯表 DROP TABLE Employees 26

27 8-3-5 TRUNCATE TABLE- 刪除關聯表記錄刪除關聯表的記錄, 但是保留關聯表綱要基本語法 TRUNCATE TABLE table_name 範例 : 刪除 Classes 記錄 TRUNCATE TABLE Classes 27

28 8-3-6 索引單層有序索引 (Single Level Ordered Index) 主鍵排序建立的索引稱為主索引 (Primary Index) 非主鍵排序建立的索引稱為叢集索引 (Clustering Index) 非排序欄位建立的索引稱為次索引 (Secondary Index) 一個基底關聯表只有一個主索引或叢集索引來決定記錄的排序方式如果還有其他索引一定是次索引, 可以擁有多個 28

29 8-3-6 CREATE INDEX 建立索引 CREATE INDEX 建立基底關聯表的索引, 基本語法 CREATE INDEX index_name ON table_name (column_name) [ASC DESC] index_name 是索引名稱 column_name 是表格 table_name 的索引欄位 ASC 是由小到大排序,DESC 是由大到小範例 : CREATE INDEX index_students ON Students(name) 29

30 8-3-7 DROP INDEX 刪除索引 DROP INDEX 刪除基底關聯表的索引, 基本語法 : DROP INDEX index_name index_name 是索引名稱範例 :DROP INDEX index_students 30

31 補充 : CREATE VIEW 建立景觀 (view) CREATE VIEW 建立景觀, 基本語法 CREATE VIEW view_name AS select att1, att2, FROM table_name WHERE view_name 是景觀名稱 att1, att2, 是表格 table_name 的欄位名稱範例 : CREATE VIEW view_students AS select name, GPA FROM Students; 31

32 補充 : DROP VIEW 建立景觀 (view) DROP VIEW 刪除景觀, 基本語法 : DROP VIEW view_name view_name 是景觀名稱範例 :DROP VIEW view_students 32

33 8-4 資料操作語言資料操作語言 (DML) 新增刪除更新和查詢關聯表的記錄有 4 個操作指令 INSERT: 插入關聯表新的記錄 UPDATE: 更新關聯表的記錄 DELETE: 刪除關聯表的記錄 SELECT: 查詢關聯表的記錄做完之後, 務必下 commit 指令以保存更動過的資料如果要反悔更動過資料的動作, 則下 rollback 指令 33

34 INSERT 指令 INSERT 新增記錄新增一筆記錄到關聯表, 基本語法 : INSERT INTO table_name [(column1,column2,..)] VALUES ('value1', 'value2', ) table_name 為新增記錄的關聯表名稱 column1~n 是欄位名稱, 不需全部欄位, 不過需要包含所有宣告成非空值欄位,value1~n 是對應的欄位值字元與日期資料需要單引號括起, 數值不用欄位名稱清單, 並不需要和資料表定義的欄位數目或順序相同, 只需列出需要值的欄位, 不過括號內的欄位名稱順序要和 VALUES 值的順序相同範例 :INSERT INTO Students VALUES ('S001',' 陳會安 ', ' ','1967/09/03',3.7) 34

35 8-4-2 UPDATE 更新記錄 UPDATE 指令關聯表符合條件的記錄, 更新指定欄位內容, 基本語法 UPDATE table_name SET column_name = value, WHERE conditions table_name 是關聯表名稱 column_name 是關聯表的欄位名稱字元與日期需要單引號括起, 數值不用若沒有 WHERE 條件子句, 所有記錄欄位都會被更新 WHERE 條件可以使用 = <> > < <= 和 >= 等運算子 WHERE 條件如果不只一個, 可以使用邏輯運算子 and 或 or 或 not 連結範例 : UPDATE Students SET GPA=3.0 WHERE sid = 'S001' 35

36 8-4-3 DELETE 刪除記錄 DELETE 指令將關聯表符合條件的記錄刪除掉, 基本語法 DELETE FROM table_name WHERE conditions table_name 是關聯表 conditions 為刪除記錄的條件如果沒有 WHERE 子句條件, 所有記錄都會被刪除掉範例 :DELETE FROM Students WHERE sid = 'S001' 36

37 8-5 資料控制語言 - 說明資料控制語言 (DCL) 定義資料庫安全管理所需的規則授予或撤回使用者的相關權限讓使用者只能執行授權的操作和處理擁有權限的資料 37

38 8-5 資料控制語言 -GRANT 指令 GRANT 指令授予資料庫使用者權限, 基本語法 GRANT { ALL [ PRIVILEGES ] privileges [ (column [,columnn])] [,privileges ] } ON table_name TO user_name [,user_name...] ON 子句指定授予哪一個 table_name 關聯表的權限 privileges 權限的括號中可以指定擁有哪些欄位的權限 38

39 8-5 資料控制語言 -REVOKE 指令 REVOKE 指令徹回某位使用者所授予的權限, 基本語法 REVOKE { ALL [ PRIVILEGES ] privileges [ (column [,columnn]) ] [,privileges ] } ON table_name FROM user_name [,user_name...] ON 子句指定徹回哪一個 table_name 關聯表的權限 privileges 權限的括號中可以指定徹回哪些欄位的權限 39

ASP.NET 2.0網頁設計範例教本

ASP.NET 2.0網頁設計範例教本第 8 章 SQL 結構化查詢語言 8-1 SQL 結構化查詢語言的基礎 8-2 資料庫的實體資料模型 8-3 資料定義語言 8-4 資料操作語言 8-5 資料控制語言 8-6 產生 SQL Server 指令碼精靈 8-1 SQL 語言的基礎 8-1-1 SQL 結構化查詢語言 8-1-2 關聯式資料庫管理系統的查詢最佳化 8-1-1 SQL 結構化查詢語言 - 說明 SQL (Structured