《數(shù)據(jù)庫原理及應用》課件第4章

第4章關系數(shù)據(jù)庫標準語言SQL4.1SQL語言概述4.2網(wǎng)上書店數(shù)據(jù)庫 4.3數(shù)據(jù)定義功能4.4SQL數(shù)據(jù)更新功能4.5SQL數(shù)據(jù)查詢功能4.6SQL數(shù)據(jù)控制功能4.7視圖4.8嵌入式SQL本章小結習題4

本章主要內容

由于SQL語言的標準化，所以大多數(shù)關系型數(shù)據(jù)庫系統(tǒng)都支持SQL語言。SQL語言已經(jīng)發(fā)展成為多種平臺進行交互操作的底層會話語言，成為數(shù)據(jù)庫領域中一個主流語言。這一章將詳細介紹SQL的核心部分：數(shù)據(jù)定義、數(shù)據(jù)更新、數(shù)據(jù)查詢、數(shù)據(jù)控制、視圖和嵌入式SQL等。

本章學習目標

—

熟練掌握SQL語言的數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)更新功能。

—

掌握SQL語言的數(shù)據(jù)控制功能。

—

掌握SQL語言的視圖操作。

—

了解嵌入式SQL的應用及使用方法。

4.1SQL語言概述

SQL是介于關系代數(shù)與關系演算之間的一種結構化查詢語言，其功能不僅僅是查詢。SQL是一個功能強大，通用的、簡單易學的數(shù)據(jù)庫語言。

4.1.1SQL語言功能特征

1.綜合統(tǒng)一

SQL的綜合統(tǒng)一表現(xiàn)為DDL、DML、DCL的統(tǒng)一。SQL語言將數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML、數(shù)據(jù)控制語言DCL功能集于一體，語言風格統(tǒng)一，可以獨立完成數(shù)據(jù)庫生命周期中的全部活動。

2.高度非過程化

SQL語言進行數(shù)據(jù)操作時，只需要提出“做什么”，而不需說明“怎么做”，因此無需了解存取路徑及路徑的選擇，并且SQL語言的操作過程也是由系統(tǒng)自動完成的，這樣減輕了用戶的負擔，有利于提高數(shù)據(jù)的獨立性。

3.面向集合的操作方式

SQL語言采用集合的操作方式，不僅操作的對象、操作的結果可以是集合，而且一次插入、刪除、更新操作的對象也可以是集合，即SQL語言既可以接受集合作為輸入，也可以返回集合作為輸出。

4.一種語法，兩種使用方式

SQL語言既是自含式語言，又是嵌入式語言。作為自含式語言，SQL可以獨立地用于聯(lián)機交互操作，用戶可以在鍵盤上直接輸入命令對數(shù)據(jù)庫進行操作；作為嵌入式語言，SQL語言可以嵌入到高級語言的程序中去，如C、Java等。在兩種不同的使用方式下，SQL的語法結構基本一致，為應用程序的研發(fā)帶來了很大的靈活性和方便性。

5.語言簡潔，易學易用

SQL語言功能極為強大，但語言結構簡捷，設計構思非常巧妙。在SQL語言中所有的核心功能只需要9個動詞，如表4.1所示，而且語句接近英語語句，方便學習，容易使用。

4.1.2SQL語言基本概念

SQL語言支持數(shù)據(jù)庫三級模式結構，如圖4.1所示，其中外模式(E)對應于視圖(View)和部分基本表；模式(C)對應于基本表，是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結構和特征的描述，是所有用戶的公共數(shù)據(jù)視圖；內模式(I)對應于存儲文件。

圖4.1SQL支持關系數(shù)據(jù)庫三級模式結構

基本表是本身獨立存在的表，在SQL中一個關系就對應一個基本表。一個(或多個)基本表對應一個存儲文件，一個表可以帶若干索引，索引也存放在存儲文件中。存儲文件的邏輯結構組成了關系數(shù)據(jù)庫的內模式，物理結構是任意的，對用戶透明。

視圖是從一個或幾個基本表中導出的表。數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應的數(shù)據(jù)，這些數(shù)據(jù)仍然存放在導出視圖的基本表中，因此視圖是一個虛表。在概念上視圖與基本表等同，用戶可以在視圖上再定義視圖。

4.2網(wǎng)上書店數(shù)據(jù)庫

用網(wǎng)上書店數(shù)據(jù)庫作為一個例子來講解SQL的數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制語句的具體應用。為此，定義網(wǎng)上書店數(shù)據(jù)庫，它包括5個表，具體的定義見4.3.1節(jié)中例4.1和例4.2，關系的主碼加下畫線表示。

用戶表：USER1(ID，NAME，PASSWORD，ADDRESS，POSTCODE，EMAIL，HOMEPHONE，CELLPHONE，OFFICEPHONE)；

圖書類型表：SORTKIND(ID，NAME)；

圖書表：PRODUCT(ID，NAME，DESCRIPTION，PRICE，IMG，ZUOZHE，SORTKIND_ID)；

訂單表：USER1_ORDER(ID，STATUS，COST，DATE，USER1_ID)；

訂單條目表：ORDER_ITERM(ID，AMOUNT，PRODUCT_ID，ORDER_ID)。

4.3數(shù)據(jù)定義功能

SQL數(shù)據(jù)定義功能主要有數(shù)據(jù)庫模式的定義、基本表的定義、視圖的定義以及索引的定義四部分。特別要注意的是這里所說的“定義”不僅僅是對象的創(chuàng)建(CREATE)，還包括刪除(DROP)和修改(ALTER)，共三部分內容。

4.3.1基本表的操作

在關系數(shù)據(jù)庫中，關系是數(shù)據(jù)庫的基本組成單位，關系又稱為表。建立數(shù)據(jù)庫的重要一步就是建立基本表，本節(jié)主要討論基本表的定義。

1.基本表的創(chuàng)建

在SQL語言中，使用CREATETABLE語句創(chuàng)建基本表，其一般格式如下：

CREATETABLE<基本表名>(<列名><數(shù)據(jù)類型>[列級完整性約束條件]

[,<列名><數(shù)據(jù)類型>[列級完整性約束條件]]…

[,<表級完整性約束條件>])

<基本表名>是所要定義的表名，它可以有一個或多個屬性(列)。

建表的同時還可以定義該表的完整性約束條件。當用戶操作基本表時，DBMS自動檢查該操作是否違反了預先定義的完整性約束條件。

說明：在SQL語句格式中，有下列約定符號和相應的語法規(guī)定。

(1)語法格式的約定符號。

·＜＞：其中的內容為必選項，表示不能為空的實際語義。

·［］：其中的內容為任選項。

·｛｝或｜：必選其中的一項。

(2)語法規(guī)定。

一般語法規(guī)定：

·SQL中數(shù)據(jù)項(列項、表和視圖)的分隔符為“,”。

·字符串常數(shù)的定界符用單引號“‘”表示。

·SQL語句的結束符為“;”。

·SQL采用格式化書寫方式。

·SQL語句中的所有符號均為英文半角狀態(tài)下符號。

【例4.1】建立一個“用戶”表USER1，它由用戶編號ID、姓名NAME、密碼PASSWORD、地址ADDRESS、郵編POSTCODE、電子郵箱EMAIL、宅電HOMEPHONE、移動電話CELLPHONE、辦公室電話OFFICEPHONE

9個屬性組成。其中用戶編號為主鍵，密碼和姓名不許為空。

系統(tǒng)執(zhí)行上面的CREATETABLE語句后，就在數(shù)據(jù)庫中建立一個新的空的“用戶”表USER1，并將有關表的定義及有關的約束條件存放在數(shù)據(jù)字典中。

定義基本表時，要指明各屬性列的數(shù)據(jù)類型及長度，不同的數(shù)據(jù)庫系統(tǒng)支持的數(shù)據(jù)類型不完全相同，表4.2列舉了主要數(shù)據(jù)類型。

【例4.2】創(chuàng)建“圖書類型”表SORTKIND，它由類型號ID和類型名NAME組成，其中ID為主鍵。創(chuàng)建“圖書”表PRODUCT，它由圖書號ID、圖書名NAME、描述DESCRIPTION、單價PRICE、圖片IMG、作者ZUOZHE、圖書類型號SORTKIND_ID

7個屬性組成。其中圖書號為主鍵不能為空，SORTKIND_ID為外鍵。創(chuàng)建“訂單”表USER1_ORDER，它由訂單號ID、狀態(tài)STATUS、單價COST、日期DATE、用戶號USER1_ID組成，其中訂單號為主鍵，用戶號為外鍵。創(chuàng)建“訂單條目”表ORDER_ITERM，它由條目號ID、數(shù)量AMOUNT、圖書號PRODUCT_ID、訂單號ORDER_ID組成，其中條目號為主鍵，圖書號和訂單號是外鍵。

2.完整性約束

完整性約束的用途是限制輸入到基本表中的值的范圍，SQL的完整性約束可以分為列級完整性約束和表級完整性約束兩種。

列級完整性約束：針對關系屬性值設置的限定條件，只能應用在一列上。

表級完整性約束：涉及關系中多個屬性的限制條件，可以應用在一個基本表中的多個屬性列上。當需要在一個基本表中的多個列上建立約束條件時，只能建立表級約束。當創(chuàng)建完整性約束之后，它作為基本表定義的一部分，被存入數(shù)據(jù)字典中。

(1)實體完整性約束(PRIMARYKEY約束)。實體完整性約束也稱為PRIMARYKEY約束，即主鍵約束。它能保證主鍵的唯一性和非空性。一個基本表的主鍵由若干屬性列組成，可能只含有一列，也可能有幾列。實體完整性約束可以在列級或表級上進行定義，但不可以在兩個級別上同時定義。在創(chuàng)建基本表時，PRIMARYKEY約束定義主鍵的方法如下：

①?PRIMARYKEY約束直接寫在列名及其類型之后。

②按照語法在相應的列名及其類型后單獨列出：

CONSTRAINT<約束名>PRIMARYKEY；

其中，<約束名>是PRIMARYKEY約束的名字。

③在CREATETABLE語句列出基本表的所有的列定義之后，再附加一個PRIMARYKEY約束說明：

PRIMARYKEY(<列名1>［,<列名2>,…,N］)；

注意：關系模型的實體完整性在CREATETABLE中用PRIMARYKEY定義。若單屬性構成的主鍵可以有兩種說明方法：定義為列級約束條件或定義為表級約束條件。若對于多個屬性構成的主鍵只有一種說明方法：定義為表級約束條件。

【例4.3】將USER1_ORDER表中的ID屬性定義為主鍵。

①在列級定義主鍵，定義語句如下：

②在表級定義主鍵，定義語句如下：

插入或對主鍵列進行更新操作時，RDBMS按照實體完整性規(guī)則自動進行檢查，檢查內容包括：檢查主鍵值是否唯一，如果不唯一則拒絕插入或修改；檢查主鍵的各個屬性是否為空，只要有一個為空就拒絕插入或修改。檢查記錄中主鍵值是否唯一的方法是進行全表掃描，如圖4.2所示。

圖4.2插入記錄時對全表的掃描

(2)參照完整性約束(FOREIGNKEY約束)。參照完整性約束也稱為FOREIGNKEY約束或外鍵約束，用于定義參照完整性，即用來維護兩個基本表之間的一致性關系。外鍵的建立主要是通過將一個基本表中的主鍵所在的列包含在另一個表中，而這些列就是另一個基本表的外鍵。

定義外鍵有三種方式，分別是：

①如果外鍵只有一個屬性列，可以在它的列名和類型后面直接用FOREIGNKEY說明它參照哪個表哪列，其語法格式為

REFERENCES<表名>(<列名>)

②

可在屬性列表后面增加一個或幾個外鍵說明，其語法格式為

FOREIGNKEY(<列名>)REFERENCES<表名>(<列名>)

③可在相應列名及其類型后面單獨列出并指定約束名，其語法格式為

CONSTRAINT<約束名>FOREIGNKEY(<列名>)REFERENCES<表名>(<列名>)

【例4.4】定義ORDER_ITERM中的參照完整性。

參照完整性違約處理方法有三種：拒絕(NOACTION)執(zhí)行，它是默認策略；級聯(lián)(CASCADE)操作；設置為空值(SET-NULL)。對于參照完整性，除了應該定義外鍵，還應定義外鍵列是否允許空值。

(3)用戶定義的完整性。用戶定義的完整性就是針對某一具體應用的數(shù)據(jù)必須滿足的語義要求。用戶定義的完整性由RDBMS提供，而不必由應用程序承擔屬性上的約束條件定義。

關系模型的自定義完整性定義在CREATETABLE語句中，包括列值非空(NOTNULL)、列值唯一(UNIQUE)、檢查列值是否滿足一個布爾表達式(CHECK)。

①不允許取空值(NOTNULL約束)。

【例4.5】在定義SORTKIND表時，說明NAME屬性不允許取空值。

②列值唯一(UNIQUE約束)。UNIQUE約束是唯一性約束，主要用來確保不受PRIMARYKEY約束的列上的數(shù)據(jù)的唯一性。PRIMARYKEY約束與UNIQUE約束的區(qū)別主要表現(xiàn)在以下幾方面：

·UNIQUE約束，主要用在非主鍵的一列或多列上要求數(shù)據(jù)唯一的情況。

·UNIQUE約束，允許該列上存在NULL值，在主鍵決不允許出現(xiàn)這種情況。

·在一個基本表上可以設置多個UNIQUE約束，但只能有一個主鍵約束。

UNIQUE約束也可以在列級或表級上設置。如果要設置多個列的UNIQUE約束，則必須設置表級約束。在SQL語句中，創(chuàng)建基本表時，定義UNIQUE約束的方法如下：

列級UNIQUE約束：

CONSTRAINT<約束名>UNIQUE

其中，<約束名>是UNIQUE約束的名字。

表級UNIQUE約束：

CONSTRAINT<約束名>UNIQUE(<列名1>［,<列名2>,…,N］)

【例4.6】建立SORTKIND，要求類型名稱NAME列取值唯一，類型編號ID列為主鍵。

CREATETABLESORTKIND

(

IDVARCHAR(16)PRIMARYKEY,/*列級完整性約束條件，ID為主鍵*/

NAMEVARCHAR(32)CONSTRAINTUN_NAME

UNIQUE

);

③?CHECK約束。

【例4.7】在ORDER_ITERM表中，AMOUNT小于100。

【例4.8】在高校管理系統(tǒng)中創(chuàng)建一學生表，當學生的性別是男時，其名字不能以MS.開頭。

3.基本表的修改

隨著環(huán)境和需求的變化，有時需要修改已建好的基本表。表的修改包括結構的修改和約束條件的修改。在SQL語言中，使用ALTERTABLE語句修改基本表，其一般格式如下：

ALTERTABLE<基本表名>

[ADD<新列名><數(shù)據(jù)類型>|[完整性約束]]

[DROP<完整性約束名>]

[ALTERCOLUMN<列名><數(shù)據(jù)類型>];

說明：

①<基本表名>是要修改的基本表的名字。

②ADD子句用于在基本表中增加新列和新完整性約束條件。新增加的列不能定義為“NOTNULL”，因為不論基本表中是否有數(shù)據(jù)，新增加的列一律為空值(NULL)。

③DROP子句用于刪除完整性約束條件。

④ALTER子句用于修改原有的列定義，包括列名和列的數(shù)據(jù)類型及長度。

【例4.9】向USER1表中增加“性別”一列，其數(shù)據(jù)類型為字符型。

ALTERTABLEUSER1ADDSEXCHAR(2);

不論基本表中原來是否有數(shù)據(jù)，新增加的列一律為空值。

【例4.10】將HOMEPHONE數(shù)據(jù)類型改為整型。

ALTERTABLEUSER1ALTERCOLUMNHOMEPHONEINT;

【例4.11】刪除用戶姓名必須取唯一值的約束。

ALTERTABLEUSER1DROPUNIQUE(NAME);

4.基本表的刪除

當基本表不再需要時，可以用DROPTABLE刪除表?；颈硪坏┍粍h除，其中的所有數(shù)據(jù)也會隨之丟失。

在SQL語言中，使用DROPTABLE刪除基本表，其一般格式如下：

DROPTABLE<基本表名>［RESTRICT|CASCADE］;

RESTRICT：表明刪除表是有限制的。欲刪除的基本表不能被其他表的約束所引用，如果存在依賴該表的對象，則此表不能被刪除。

CASCADE：表明刪除該表沒有限制。在刪除基本表的同時，相關的依賴對象一起刪除。

說明：

①<基本表名>是要刪除的基本表的名字。

②基本表被刪除后，依附于此表建立的索引和視圖都將被自動刪除掉，并且無法恢復，此時系統(tǒng)釋放其所占的存儲空間。因此執(zhí)行刪除基本表的操作一定要格外小心。

③只有基本表的擁有者才可以使用此語句。

④不能使用DROP刪除系統(tǒng)表。

【例4.12】刪除基本表PRODUCT。

DROPTABLEPRODUCT;

注意：不同的數(shù)據(jù)庫產(chǎn)品在遵循SQL標準的基礎上具體實現(xiàn)細節(jié)和處理策略上會與標準有差異。

表4.3就SQL-99標準對DROPTABLE的規(guī)定，對比分析KingbaseES、Oracle9I、MSSQLServer2000這三種數(shù)據(jù)庫產(chǎn)品對DROPTABLE的不同處理策略。

表中的R表示RESTRICT，即DROPTABLE<基本表名>RESTRICT；C表示CASCADE，即DROPTABLE<基本表名>CASCADE。其中Oracle9I沒有RESTRICT選項；MSSQLServer2000沒有RESTRICT和CASCADE選項。

從比較中可以知道：

(1)對于索引，刪除基本表后，這3種RDBMS都自動刪除該表上已經(jīng)建立的所有索引。

(2)對于視圖，Oracle9I與MSSQLServer2014都是刪除基本表后，還保留表上的視圖定義，但視圖已經(jīng)失效。KingbaseES分兩種情況：若刪除時帶RESTRIC選項，則不可以刪除表；若帶CASCADE選項刪除，則可以刪除表，也可以同時刪除視圖。KingbaseES的這種策略符合SQL-99標準。

(3)對于存儲過程和函數(shù)，刪除基本表后，這3種數(shù)據(jù)庫產(chǎn)品都不自動刪除建立在此表基礎上的存儲過程和函數(shù)，但這些存儲過程和函數(shù)卻已失效。

同樣對于其他的SQL語句，不同的數(shù)據(jù)庫產(chǎn)品在處理策略上會與標準SQL有所差異。因此，如果發(fā)現(xiàn)本書中的個別例子在某種數(shù)據(jù)庫產(chǎn)品中無法通過，請讀者參考相關數(shù)據(jù)庫產(chǎn)品手冊。

4.3.2索引操作

在使用數(shù)據(jù)庫系統(tǒng)時，用戶所看到和操作的數(shù)據(jù)就好像是在簡單的二維表中的，而實際上，對于數(shù)據(jù)在磁盤上是如何存儲的，用戶并不清楚。但是，數(shù)據(jù)的物理存儲結構，卻是決定數(shù)據(jù)庫性能的主要因素。索引是最常見的改善數(shù)據(jù)庫性能的技術。

索引就是加快檢索表中數(shù)據(jù)的方法。數(shù)據(jù)庫的索引類似于書籍的索引。在書籍中，索引允許用戶不必翻閱整本書就能迅速地找到所需要的信息。在數(shù)據(jù)庫中，索引也允許數(shù)據(jù)庫程序迅速地找到表中的數(shù)據(jù)，而不必掃描整個數(shù)據(jù)庫。

1.索引的特點

簡單地說，一個索引就是一個指向表中數(shù)據(jù)的指針。例如，若讀者想找出一本書中討論某主題的所有頁碼，首先需要去查閱按字母順序列出的包含所有主題的索引，然后再去閱讀某些特定的頁。在數(shù)據(jù)庫中，索引具有同樣的作用。索引查詢指向基本表中數(shù)據(jù)的確切物理地址。實際上，查詢都被定向于數(shù)據(jù)庫中數(shù)據(jù)在數(shù)據(jù)文件中的地址，但對查詢者來說，它是在參閱一張表。

索引是在SQL基本表中列上建立的一種數(shù)據(jù)庫對象，也可稱其為索引文件，它和建立于其上的基本表是分開存儲的。建立索引的主要目的是提高數(shù)據(jù)檢索性能。索引可以被創(chuàng)建或撤銷，這對數(shù)據(jù)毫無影響。但是，一旦索引被撤銷，數(shù)據(jù)查詢的速度可能會變慢。索引要占用物理空間，且通常比基本表本身占用的空間還要大。

當建立索引以后，它便記錄了表中被索引列的每一個取值。當在表中加入新的數(shù)據(jù)時，索引中也增加相應的數(shù)據(jù)項。當對數(shù)據(jù)庫中的基本表建立了索引并且進行數(shù)據(jù)查詢時，首先在相應的索引中查找。如果數(shù)據(jù)被找到，則返回該數(shù)據(jù)在基本表中的確切位置。

對于一個基本表，可以根據(jù)應用環(huán)境的需要創(chuàng)建若干索引以提供多種存取途徑。通常，索引的創(chuàng)建和撤銷由DBA或表的擁有者負責。用戶不能也不必要在存取數(shù)據(jù)時選擇索引，索引的選擇由系統(tǒng)自動進行。

2.索引的用途

索引的用途表現(xiàn)在三個方面：

(1)由于基本表中的列比較多(有的可達幾百列)，元組也比較多(大的數(shù)據(jù)庫中的元組可達數(shù)萬個)，因此數(shù)據(jù)文件會很大。在進行數(shù)據(jù)查詢時，如果不使用索引，則需要將數(shù)據(jù)文件分塊，逐個讀到內存中，再進行查找比較操作。而使用索引后，系統(tǒng)會先將索引文件讀入內存，根據(jù)索引項找到元組的地址，然后再根據(jù)地址將元組數(shù)據(jù)讀入內存。

(2)保證數(shù)據(jù)的唯一性。索引的定義中包括定義數(shù)據(jù)唯一性的內容。當定義了數(shù)據(jù)唯一性的功能后，再對相關的索引項進行數(shù)據(jù)輸入或數(shù)據(jù)更新時，系統(tǒng)要進行檢查，以確保其數(shù)據(jù)的唯一性成立。

(3)加快表連接的速度。在進行基本表的連接操作時，系統(tǒng)需要對被連接的基本表的連接字段進行查詢，其工作量是非常巨大的。如果在被連接的基本表的連接字段上創(chuàng)建索引，則可以大大提高連接操作的速度。因此，許多系統(tǒng)要求連接文件必須有相應的索引才能執(zhí)行連接操作。

3.創(chuàng)建索引的原則

為了提高數(shù)據(jù)查詢的速度，在創(chuàng)建索引時，應遵循三個原則：

(1)索引的創(chuàng)建和維護由DBA和DBMS完成。索引由DBA或表的擁有者負責創(chuàng)建和撤銷，其他用戶不能隨意創(chuàng)建和撤銷索引。索引由系統(tǒng)自動選擇和維護，即不需要用戶指定使用索引，也不需要用戶打開索引或對索引執(zhí)行重索引操作，這些工作都由DBMS自動完成。

(2)是否創(chuàng)建索引取決于表的數(shù)據(jù)量大小和對查詢的要求。基本表中記錄的數(shù)據(jù)量越大，記錄越長，越有必要創(chuàng)建索引，創(chuàng)建索引后加快查詢速度的效果會比較明顯。相反，對于記錄比較少的基本表，創(chuàng)建索引的意義則不大。另外，索引要根據(jù)數(shù)據(jù)查詢或處理的要求而創(chuàng)建。即對那些查詢頻度高、實時性要求高的數(shù)據(jù)一定要創(chuàng)建索引，否則不必考慮創(chuàng)建索引的問題。

(3)對于一個基本表，不要建立過多的索引。索引文件要占用文件目錄和存儲空間，索引過多會使系統(tǒng)負擔加重。索引需要自身維護，當基本表的數(shù)據(jù)增加、刪除或修改時，索引文件要隨之變化，以保持與基本表一致。顯然，索引過多會影響數(shù)據(jù)增、刪、改的速度。

盡管使用索引可以強化數(shù)據(jù)庫的性能，但也有需要避免使用索引的時候，如下面所示的八種情況：

①包含太多重復值的列。

②查詢中很少被引用的列。

③值特別長的列。

④查詢返回率很高的列。

⑤具有很多NULL值的列。

⑥需要經(jīng)常插、刪、改的列。

⑦記錄較少的基本表。

⑧需要頻繁地進行大量數(shù)據(jù)更新的基本表。

4.索引的類型及選擇

在數(shù)據(jù)庫中，對一張表可以創(chuàng)建不同類型的索引，而這些索引都具有相同的作用，即加快數(shù)據(jù)查詢速度以提高數(shù)據(jù)庫的性能。索引的一般類型有三種：

(1)單列索引。單列索引是對基本表的某一單獨的列進行的索引，是最簡單和最常用的索引類型，它是在表的某一列的基礎上建立的。

(2)唯一索引。唯一索引不允許在表中插入任何相同的取值。使用唯一索引不但能提高性能，還可以維護數(shù)據(jù)的完整性。

(3)復合索引。復合索引是針對表中兩個或兩個以上的列建立的索引。由于被索引列的順序對數(shù)據(jù)查詢速度具有顯著的影響，因此創(chuàng)建復合索引時，應當考慮索引的性能。為了優(yōu)化性能，通常將最強限定值放在第一位。但是，那些始終被指定的列更應當放在第一位。

5.建立索引

在SQL語言中，使用CREATEINDEX語句建立索引，其一般格式如下：

CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名1>[<次序1>][,<列名2>[<次序2>]]…);

其中，<表名>是建立索引所依附的基本表，索引可以建立在此表的一列或多列上，各列之間用逗號分隔。每個列名都可以用ASC(升序)/DESC(降序)指定次序，缺省值為ASC。

UNIQUE表明此索引中若有重復記錄，其只保留對應的一條數(shù)據(jù)記錄。

【例4.13】在USER1表的NAME(姓名)列上建立一個聚簇索引。

CREATECLUSTERINDEXUIDXONUSER1(NAME);

用戶一般可以在最常查詢的列上創(chuàng)建聚簇索引以提高查詢速度。聚簇索引一旦建立，再要更新索引列時，會導致表中記錄的物理順序的變更，代價太大，而且一個基本表只能建一個聚簇索引，因此對于經(jīng)常更新的列不宜創(chuàng)建聚簇索引。

使用索引的原則：不應該在一個表上建立太多的索引(一般不超過兩到三個)。索引能改善查詢效果，但也耗費了磁盤空間，降低了更新操作的性能，因為系統(tǒng)必須花時間來維護這些索引。除了為數(shù)據(jù)的完整性而建立的唯一索引外，建議在表較大時再建立普通索引。通常，表中的數(shù)據(jù)越多，索引的優(yōu)越性才越明顯。

【例4.14】為網(wǎng)上書店數(shù)據(jù)庫中的PRODUCT，SORTKIND，USER1_ORDER，ORDER_ITERM四個表上建立索引，其中在PRODUCT，SORTKIND表的ID列上建升序唯一索引，在USER1_ORDER表的USER1_ID列上建降序唯一索引，ORDER_ITERM表按ORDER_ID降序和PRODUCT_ID升序建唯一索引。

6.刪除索引

索引一旦建立，由系統(tǒng)使用和維護，當不需要時，可刪除索引。在SQL語言中使用DROPINDEX語句刪除索引，一般格式如下：

DROPINDEX<索引名>;

如果數(shù)據(jù)增刪頻繁，系統(tǒng)會花費許多時間來維護索引，從而會降低了查詢效率，故可以刪除一些不必要的索引。刪除索引時，系統(tǒng)會同時刪除數(shù)據(jù)字典中有關該索引的定義。

在RDBMS中，索引一般采用B+樹、HASH索引來實現(xiàn)。B+樹索引具有動態(tài)平衡的優(yōu)點。HASH索引具有查找速度快的特點。索引是關系數(shù)據(jù)庫的內部實現(xiàn)技術，屬于內模式的范疇。

用戶使用CREATEINDEX語句定義索引時，可以定義唯一索引、非唯一索引、聚簇索引。至于某個索引是采用B+樹，還是HASH索引則由具體的RDBMS決定。

【例4.15】刪除PRODUCT表中的PIDX索引。

DROPINDEXPIDX;

7.索引的優(yōu)點

創(chuàng)建索引有以下5個優(yōu)點：

(1)創(chuàng)建唯一性索引，保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

(2)大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。

(3)加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。

(4)在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著地減少查詢中分組和排序的時間。

(5)通過使用索引，可以在查詢的過程中使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

8.索引的缺點

創(chuàng)建索引有以下3個缺點：

(1)創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。

(2)索引需要占物理空間。除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。

(3)當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要進行動態(tài)的維護，這就降低了數(shù)據(jù)的維護速度。

4.3.3SQLServer中數(shù)據(jù)定義的實現(xiàn)

說明：在SQL中語句需要以“;”結束，而在SQLServer中“;”是語句間的分隔符，并非是語句的組成部分，當只有一條語句時，“;”可以省略。

【例4.16】創(chuàng)建名為BOOKSTORES的數(shù)據(jù)庫。

CREATEDATABASEBOOKSTORES;

【例4.17】建立一個學生表STUDENT，它由學生號ID、姓名NAME、密碼PASSWORD、地址ADDRESS、郵編POSTCODE、電子郵箱EMAIL、宅電HOMEPHONE

7個屬性組成。其中ID為主鍵，姓名不能為空且值唯一，密碼也不許為空。

【例4.18】在學生表的ID(姓名)列上建立一個聚簇索引。

CREATECLUSTERINDEXIDXONSTUDENT(ID);

【例4.19】刪除SORTKIND表中的SIDX索引。

DROPINDEXSIDX;

4.4SQL數(shù)據(jù)更新功能

4.4.1插入操作

SQL語言中，數(shù)據(jù)插入語句INSERT通常有兩種形式，一種是一次插入一條元組，另一種是一次插入一個子查詢結果，即一次插入多條元組。

1.插入單條元組

在SQL語言中，插入單條元組的語句格式如下：

INSERT

INTO<表名>[(<屬性列1>[,<屬性列2>]…)

VALUES(<常量1>[,<常量2>]…);

INSERT語句的功能是將指定的元組插入到指定的關系中，其中屬性列的順序要與常量值的順序一一對應，常量1的值賦給屬性列1，常量2的值賦給屬性列2，依次賦值。

若在INTO子句中沒有出現(xiàn)的屬性列，則新值在這些列上取空值。必須注意，在表定義中不許為空的列不能取空值，否則會出錯。

有時可以省略屬性列表，但常量的列表順序要求必須與指定關系的實際屬性列順序一致，且新插入的記錄必須在每個屬性列上均有值。

【例4.20】插入一條圖書類型記錄(

‘01’，‘計算機’)。

INSERT

INTOSORTKIND

VALUES(‘01’,‘計算機’);

【例4.21】將一個新書記錄(‘9787040123104’，‘數(shù)據(jù)庫系統(tǒng)教程’，29.5，‘施伯樂’，‘01’)插入到PRODUCT表中。

INSERT

INTOPRODUCT

(ID,NAME,PRICE,ZUOZHE,SORTKIND_ID)

VALUES(‘9787040123104’,‘數(shù)據(jù)庫系統(tǒng)教程’,

29.5,

‘施伯樂’,

‘01’);

新插入的記錄在DESCRIPTION、IMG列上自動賦空值。

2.插入多個元組

在SQL語言中，子查詢結果可以一次性插入到指定的關系中。插入子查詢結果的語句格式如下：

INSERT

INTO<基本表名>[(<屬性列1>[,<屬性列2>…)]

<子查詢>;

說明：

①SQL先處理<子查詢>，得到查詢結果，再將結果插入到<基本表名>所指的基本表中。

②<子查詢>結果集合中的列數(shù)、列序和數(shù)據(jù)類型必須與<基本表名>所指基本表中相應的各項匹配或兼容。

【例4.22】在PRODUCT表中查詢出所有01類圖書，將其圖書編號插入到USER1_ORDER訂單表中。

INSERT

INTOUSER1_ORDER(ID)

SELECTID

FROMPRODUCT

WHERESORTKIND_ID='01';

4.4.2修改操作

當數(shù)據(jù)庫中的數(shù)據(jù)發(fā)生變化時，需要對關系進行修改。在SQL語言中，修改操作的一般格式為

UPDATE<表名>

SET<列名>=<表達式>[，<列名>=<表達式>]...

[WHERE<條件>];

UPDATE語句的功能是修改指定關系中滿足WHERE子句條件的元組，其中SET子句給出指定列的修改方式及修改后取值。若省略WHERE子句，則說明要修改關系中的所有元組。在WHERE子句中可以嵌套子查詢。

1.修改某一個元組的值

【例4.23】將PRODUCT表中ID號為“9787040123104”的書的PRICE改為30元。

UPDATEPRODUCT

SETPRICE=30

WHEREID?=?‘9787040123104’;

2.修改多個元組的值

【例4.24】將PRODUCT表中所有圖書的PRICE加1元。

UPDATEPRODUCT

SETPRICE=PRICE+1;

3.帶子查詢的修改語句

【例4.25】將PRODUCT表中所有計算機類的圖書的PRICE提高5%。

4.4.3刪除操作

當數(shù)據(jù)庫中的數(shù)據(jù)不再需要時，應將這些不需要的數(shù)據(jù)從關系中刪除。在SQL語言中，刪除語句的一般格式為

DELETE

FROM<表名>

[WHERE<條件>]；

1.刪除某一個元組的值

【例4.26】將PRODUCT表中ID號為“9787040123104”的書刪除。

DELETE

FROMPRODUCT

WHEREID=‘9787040123104’;

2.刪除多個元組的值

【例4.27】將PRODUCT表中所有圖書刪除。

DELETE

FROMPRODUCT;

3.帶子查詢的刪除語句

【例4.28】將PRODUCT表中所有計算機類的圖書刪除。

4.4.4SQLServer中更新操作的實現(xiàn)

RDBMS在執(zhí)行更新語句時會檢查所做的更新操作是否破壞表上已定義的完整性規(guī)則，即實體完整性、參照完整性、用戶定義完整性(NOTNULL約束、UNIQUE約束、值域約束)。若破壞了表上定義的完整性約束規(guī)則，則更新失敗。

4.5SQL數(shù)據(jù)查詢功能

數(shù)據(jù)庫查詢是數(shù)據(jù)庫的核心操作。在SQL語言中，用SELECT語句進行查詢。該語句具有靈活的使用方式和豐富的功能，其一般格式如下：

此語句含義為根據(jù)WHERE條件從FROM子句指定的表中選出滿足條件的元組，然后按SELECT子句后面指定的屬性列提取出指定的列。若有GROUPBY子句，再根據(jù)GROUPBY子句指出的<列名1>分組，屬性列值相等的為一組；若GROUPBY子句中有HAVING子句，則只有滿足HAVING條件的組才被輸出。若有ORDERBY子句，則將結果按<列名2>指定的順序排序。ASC為升序，DESC為降序，缺省時為ASC。

在SQL語言中，SELECT既可以實現(xiàn)單表的簡單查詢，又可以實現(xiàn)多表的嵌套查詢和連接查詢。

4.5.1單表查詢

單表查詢指只涉及一個關系的查詢。

1.選擇關系中的若干列

選擇表中的所有列或部分列，即為投影運算。

1)查詢全部列

選出表中的全部列有兩種方法。一種是在SELECT關鍵字后面列出所有的列名，并以“,”分割，指定的列順序可以不與表中順序一致；另一種是在SELECT關鍵字后面指定“*”，此時輸出列的順序必與原表順序一致。

【例4.38】查詢出全體用戶的詳細信息。

SELECT*

FROMUSER1;

等價于

SELECTID,NAME,PASSWORD,ADDRESS,POSTCODE，

EMAIL,HOME_PHONE,CELL_PHONE,OFFICE_PHONE

FROMUSER1;

2)查詢指定列

在多數(shù)情況下，用戶只對一部分列信息感興趣，此時就可以在SELECT子句后面指定要查詢的屬性列名。

3)查詢經(jīng)過計算的值

SELECT關鍵字后面的<目標列表達式>既可是表中的屬性列，也可以是表達式。

2.選擇表中的若干元組

1)消除重復行

兩個并不相同的元組，投影到某些列后會出現(xiàn)相同的幾個元組，此時一般就需要消除重復元組。

【例4.43】查詢出訂購了圖書的用戶編號。

SELECTUSER1_ID

FROMUSER1_ORDER;

由于同一個用戶可能訂購多種圖書，所以上例中得到的USER1_ID可能會有重復值。如果要去掉重復值，則必須用DISTINCT關鍵字。若沒有DISTINCT關鍵字，則為ALL，即不消除重復值。要特別注意的是，DISTINCT修飾的是其后面的所有列。

2)查詢出滿足條件的元組

用WHERE子句指定查詢中需要滿足的條件，WHERE子句常用的查詢條件如表4.4所示。

(2)確定范圍。

BETWEEN…AND…可以用來查詢在指定范圍內的元組，其指定的是閉區(qū)間，BETWEEN后為下限，AND后為上限。NOTBETWEEN…AND…用來查詢不在指定范圍內的元組。

(3)字符匹配。

用LIKE謂詞進行字符匹配。其一般格式如下：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]

上述語句的功能是查詢指定屬性列值與<匹配串>相匹配的元組。<匹配串>可以是一個不含通配符的完整字符串(當<匹配串>為不含通配符的完整字符串時，LIKE可用“=”號代替，NOTLIKE可用“!=”代替)，也可以含有通配符“%”和“_”。

其中，“%(百分號)”代表出現(xiàn)在指定位置的任意長度(長度可以為0)的字符串；“_(下劃線)代”表出現(xiàn)在指定位置的任意單個字符。

(4)確定集合。

用IN謂詞可以查找屬性值在指定的集合中的元組。

(5)復合條件查詢。

邏輯運算符AND和OR可用來連接多條件查詢，條件運算順序為從左到右，且AND優(yōu)先級高于OR，但用戶可以用括號改變優(yōu)先級。

(6)涉及空值的查詢。

3)對查詢結果進行排序

在SQL語言中，SELECT查詢可以用ORDERBY子句對查詢結果進行排序，可以根據(jù)一個屬性排序，也可以按照多個屬性排序。

4)函數(shù)查詢

SQL語言中為方便用戶使用，提供了許多聚集函數(shù)，常用的SQL聚集函數(shù)如表4.5所示。

5)對查詢結果進行分組

GROUPBY子句將查詢結果按照某一列或某幾列進行分組，值相等的為一組。

4.5.2連接查詢

1.普通連接

普通連接操作只輸出滿足連接條件的元組。連接查詢中用來連接兩個表的條件稱為連接條件或連接謂詞，連接謂詞中的列名稱為連接字段，其一般格式為

[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>

[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

連接條件中的各連接字段類型必須是可比的，但名字不必是相同的。連接條件要在WHERE子句中。

【例4.67】查詢出每個用戶及其訂購圖書的信息。

SELECTUSER1.*,USER1_ORDER.*

FROMUSER1,USER1_ORDER

WHEREUSER1.ID=USER1_ORDER.USER1_ID;/*將兩表中同一用戶的信息連接起來*/

在連接查詢中，為了避免混淆，要在屬性名前面加上表名前綴。如果屬性名在參加連接的表中是唯一的，則可以省略表名前綴。

若沒有指定兩表的連接條件，則兩表做廣義笛卡爾積，即兩表元組交叉乘積，其連接結果會產(chǎn)生一些沒有意義的元組，所以這種運算實際上很少用。

若連接條件中的連接運算符是等號(=)，則該連接是等值連接，其中會有相同的重復屬性列。如果去掉重復的屬性列，則是自然連接。

一般情況下，并不需要將兩個表中的所有屬性列均顯示出來，只是將用戶需要的屬性列在SELECT子句中列出來即可。在指定輸出的屬性列中，如果有兩個表中都存在的屬性，則需要在屬性名前面加上表名前綴，否則不需要加表名前綴。

【例4.68】查詢出訂購圖書的用戶姓名、訂單編號和訂單金額。

SELECTUSER1.NAME,USER1_ORDER.ID,COST

FROMUSER1,USER1_ORDER

WHEREUSER1.ID=USER1_ORDER.USER1_ID;/*將兩個表中同一用戶的信息連接起來*/

COST屬性前面沒有加表名前綴，是因為只有USER1_ORDER表中有COST屬性，不會引起混淆。

連接不僅可以在兩個不同的表中進行，也可以是一個表與其自身進行連接，稱為自身連接，這種連接在實際查詢中經(jīng)常會用到。

注意：連接查詢方式只用一個查詢塊，并且必須在WHERE子句中給出連接謂詞。當目標列中涉及的屬性在不同表中時，只能使用連接查詢方式進行查詢。

2.外連接

通常情況下，連接操作只會將滿足條件的元組作為結果輸出，例如USER1表和USER1_ORDER表做普通連接時只會輸出滿足條件的元組，沒有訂購圖書的用戶就不會顯示出來。但有時我們想要以USER1表為主體列出每個用戶的基本情況及其訂購圖書的情況(若某個用戶沒有訂購圖書，只輸出其用戶基本信息，其訂購圖書的信息為空即可)，這時就需要應用外連接(OUTERJOIN)。

3.復合條件連接

在上面的例子中，WHERE條件中只有一個條件，但多數(shù)時候WHERE子句中會有多個條件，這就稱為復合條件連接。

4.5.3嵌套查詢

在SQL語言中，一個SELECT—FROM—WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢(NestedQuery)。

1.帶有IN謂詞的子查詢

在嵌套查詢中，子查詢的結果往往是一個集合，所以謂詞IN是嵌套查詢中最常用的謂詞。

【例4.72】查詢出計算機類的圖書編號及圖書名稱。

先分步完成子查詢，然后再構造嵌套查詢。

①先確定計算機類圖書的編號。

SELECTID

FROMSORTKIND

WHERENAME='計算機';

【例4.73】查詢出用戶姓名為“李平”的用戶訂購的訂單編號及訂單金額。

2.帶有比較運算符的子查詢

當子查詢結果返回的是一個單值時，父查詢和子查詢之間可以用比較運算符>，>=，<，<=，=，!=，<>等進行連接。

例如，在例4.72中，由于計算機類的圖書類型編號只有一個，也就是說內查詢結果只返回一個值，因此可以用“=”代替IN，其SQL語句如下：

這個語句的一個可能的執(zhí)行過程是：

①從外層查詢中取出USER1_ORDER的一個元組X，將元組X的USER1_ID值(001)傳送給內層查詢。

SELECTAVG(COST)

FROMUSER1_ORDERY

WHEREY.USER1_ID='001';

②執(zhí)行內層查詢，得到值24，用該值代替內層查詢，得到外層查詢。

SELECTUSER1_ID,ID

FROMUSER1_ORDERX

WHERECOST>=24；

③執(zhí)行這個查詢，得到一組結果。

④外層查詢取出下一個元組重復做上述①至③步驟，直到外層的USER1_ORDER元組全部處理完畢，得到查詢的全部結果。

求解相關子查詢不像求解不相關子查詢那樣，一次將子查詢求解出來，然后求解父查詢。內查詢由于與外查詢有關，因此必須反復求值。

3.帶有ANY和ALL謂詞的子查詢

單獨使用比較運算符時，要求子查詢返回的結果必須為單值。若子查詢返回的是一個集合，就要使用帶有ANY和ALL謂詞的比較運算符，其語義組合如表4.6所示。

實際上用聚集函數(shù)實現(xiàn)子查詢通常比直接使用ANY或ALL查詢效率要高，ANY、ALL謂詞與聚集函數(shù)、IN謂詞的等價轉換關系如表4.7所示。

4.5.4集合查詢

因為SELECT語句的查詢結果是元組的集合，所以多個SELECT語句的結果可以進行集合操作。集合操作的種類主要有并操作UNION、交操作INTERSECT、差操作EXCEPT。參加集合操作的各查詢結果的列數(shù)必須相同，對應項的數(shù)據(jù)類型也必須相同。

【例4.75】查詢出01類的圖書或價格在30元以上的圖書編號及其圖書名稱。

4.5.5SELECT語句的一般格式

SELECT語句是SQL的核心語句。從上面的例子可以看到其語句成分豐富多樣，下面我們總結一下它們的一般格式。

1.目標列表達式格式

目標列表達式格式有以下4種：

(1)?*。

(2)

<表名>.*。

(3)

COUNT([DISTINCT|ALL]*)。

(4)

[<表名>.]<屬性列名表達式>[,[<表名>.]<屬性列名表達式>]…。

其中，<屬性列名表達式>是由屬性列、作用于屬性列的聚集函數(shù)和常量的任意算術運算符(+，-，*，/

)?組成的運算公式。

2.聚集函數(shù)的一般格式

聚集函數(shù)的一般格式如下：

3.條件表達式格式

條件表達式有以下7種格式：

4.5.6SQLServer中數(shù)據(jù)查詢的實現(xiàn)

4.6SQL數(shù)據(jù)控制功能

數(shù)據(jù)控制亦稱為數(shù)據(jù)保護，包括數(shù)據(jù)的安全性控制、完整性控制、并發(fā)控制和恢復。SQL語言提供了數(shù)據(jù)控制功能，能夠在一定程度上保證數(shù)據(jù)庫中數(shù)據(jù)的安全性、完整性，并提供了一定的并發(fā)控制及恢復能力。

4.6.1授權操作

GRANT語句的一般格式：

GRANT<權限>[,<權限>]...

[ON<對象類型><對象名>]

TO<用戶>[,<用戶>]...

[WITHGRANTOPTION];

GRANT語句的語義是將指定操作對象的指定操作權限授予指定的用戶。發(fā)出該GRANT語句的可以是DBA，也可以是該數(shù)據(jù)庫對象的創(chuàng)建者(基本表的屬主)，還可以是已經(jīng)擁有該權限的用戶。該授權的用戶可以是一個或多個具體用戶，也可以是PUBLIC用戶，即全體用戶。

如果指定了WITHGRANTOPTION子句，則獲得某種權限的用戶還可以把這種權限再授予別的用戶，但不許循環(huán)授權，即被授權者不能再把權限授回給授權者或其祖先，如圖4.3所示。

對不同類型的操作對象有不同的操作權限，常見的操作權限如表4.8所示。

圖4.3不允許循環(huán)授權

【例4.95】DBA把在網(wǎng)上書店數(shù)據(jù)庫中建立表的權限授予用戶U8。

GRANTCREATETABLE

TOU8;

4.6.2收回權限操作

用戶被授予的權限可由DBA或其他授權者用REVOKE語句收回。REVOKE語句的一般格式為

REVOKE<權限>[,<權限>]...

[ON<對象類型><對象名>]

FROM<用戶>[,<用戶>]...;

將用戶U5的INSERT權限收回的時候，必須級聯(lián)(CASCADE)收回，即系統(tǒng)只收回直接或間接從U5處獲得的權限，否則系統(tǒng)將拒絕(RESTRICT)執(zhí)行此命令。

系統(tǒng)將收回直接或間接從U5處獲得的對USER1_ORDER表的INSERT權限，過程如下：

→U5→U6→U7

收回U5、U6、U7獲得的對USER1_ORDER表的INSERT權限的過程是：←U5←U6←U7。由上面的例子可見，SQL提供了非常靈活的授權機制。DBA擁有對數(shù)據(jù)庫中所有對象的所有權限，并且可以根據(jù)需要將權限授予不同用戶。

4.6.3SQLServer中數(shù)據(jù)控制的實現(xiàn)

4.7視圖

視圖是從一個或幾個基本表(或視圖)導出的表，它與基本表不同，是一個虛表。數(shù)據(jù)庫中只存放視圖的定義，而不存放視圖對應的數(shù)據(jù)，這些數(shù)據(jù)仍然存放在原來的基本表中。所以，若基本表數(shù)據(jù)發(fā)生變化，則視圖中的數(shù)據(jù)也會隨之發(fā)生變化。從這個意義上講，視圖是數(shù)據(jù)庫的一個窗口，透過視圖可以看見數(shù)據(jù)庫中自己感興趣的數(shù)據(jù)及其變化情況。

4.7.1視圖定義

1.創(chuàng)建視圖

在SQL語言中，用CREATEVIEW命令建立視圖，其一般語句格式為

CREATEVIEW

<視圖名>[(<列名>[,<列名>]…)]

AS<子查詢>

[WITHCHECKOPTION];

其中，子查詢可以是任意復雜的SELECT語句，但通常不能含有ORDERBY子句和DISTINCT短語；WITHCHECKOPTION透過視圖進行增刪改操作時，不得破壞視圖定義中的謂詞條件(即子查詢中的條件表達式)。

組成視圖的屬性列名或全部省略或全部指定。如果省略了視圖的各個屬性列名，則隱含指明該視圖由子查詢中SELECT目標列中的諸字段組成。但下面四種情況必須明確指定視圖的所有列名：

①某個目標列是聚集函數(shù)或列表達式。

②目標列為*。

③多表連接時選出了幾個同名列作為視圖的字段。

④需要在視圖中為某個列啟用新的更合適的名字。

(1)行列子集視圖。

若一個視圖由一個基本表導出，并且只是去掉了基本表中的若干行和若干列，但保留了碼，我們稱這類視圖為行列子集視圖。

【例4.105】建立01類圖書的視圖，并要求透過該視圖進行的更新操作只涉及01類圖書。

由于定義視圖時加上了WITHCHECKOPTION子句，以后對該視圖進行更新時DBMS將自動加上SORTKIND_ID='01'條件。

(3)基于多個基表的視圖。

【例4.106】建立計算機類的圖書視圖。

(4)基于視圖的視圖。

【例4.107】建立計算機類且圖書價格大于20元的視圖。

(5)帶表達式的視圖。

【例4.108】定義一個反映圖書9折價格的視圖。

設置派生屬性列(也稱為虛擬列SALE_PRICE)時，帶表達式的視圖必須明確定義組成視圖的屬性列名。

(6)分組視圖。

【例4.109】將圖書的類型編號及它的平均價格定義為一個視圖。

【例4.110】建立01類圖書的視圖。

2.刪除視圖

在SQL語言中，用DROPVIEW語句刪除視圖，其一般語句格式為

DROPVIEW<視圖名>;

該語句從數(shù)據(jù)字典中刪除指定的視圖定義，由該視圖導出的其他視圖定義仍在數(shù)據(jù)字典中，但已不能使用，必須顯式刪除。

【例4.111】刪除視圖IS_PRODUCT。

DROPVIEWIS_PRODUCT;

4.7.2視圖查詢

視圖定義之后，用戶可以像查詢基本表一樣查詢視圖。DBMS實現(xiàn)視圖查詢的方法一般有兩種：實體化視圖(ViewMaterialization)、視圖消解法(ViewResolution)。

實體化視圖(ViewMaterialization)，首先進行有效性檢查，檢查所查詢的視圖是否存在。如果存在，則取出并執(zhí)行視圖定義，將視圖臨時實體化，生成臨時表，進而將查詢視圖轉換為查詢臨時表，查詢完畢刪除被實體化的視圖(臨時表)。

視圖消解法(ViewResolution)，首先進行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義，把視圖定義中的子查詢與用戶的查詢結合起來，轉換成等價的對基本表的查詢，最后執(zhí)行修正后的查詢。

視圖消解法的局限：有些情況下，視圖消解法不能生成正確查詢，因為采用視圖消解法的DBMS會限制這類查詢。

4.7.3視圖更新

從用戶角度來看，更新視圖與更新基本表相同。更新視圖是指通過視圖來插入(INSERT)、刪除(DELETE)和修改(UPDATE)數(shù)據(jù)。

由于視圖是不實際存儲數(shù)據(jù)的表，所以對視圖的更新，最終要轉換為對基本表的更新。像查詢視圖那樣，對視圖的更新操作也可以通過視圖消解法轉換為對基本表的更新操作。

為了防止用戶通過視圖對不屬于視圖范圍的基本表數(shù)據(jù)進行操作，可以在定義視圖時加上WITHCHECKOPTION子句。這樣在視圖上更新視圖時，RDBMS就會檢查視圖定義中的條件，若不滿足條件，則拒絕執(zhí)行該操作。

實際系統(tǒng)允許對行列子集視圖進行更新，對其他類型視圖的更新，不同系統(tǒng)有不同限制。DB2對視圖更新的限制有：

(1)若視圖是由兩個以上基本表導出的，則此視圖不允許更新。

(2)若視圖的字段來自字段表達式或常數(shù)，則不允許對此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。

(3)若視圖的字段來自聚集函數(shù)，則此視圖不允許更新。

(4)若視圖定義中含有GROUPBY子句，則此視圖不允許更新。

(5)若視圖定義中含有DISTINCT短語，則此視圖不允許更新。

(6)若視圖定義中有嵌套查詢，并且內層查詢的FROM子句中涉及的表也是導出該視圖的基本表，則此視圖不允許更新。

(7)一個不允許更新的視圖上定義的視圖也不允許更新。

4.7.4SQLServer中的視圖操作

4.8嵌入式SQL

SQL語言的特點之一就是在兩種使用方式下，SQL語言的語法結構基本上是一致的。當然細節(jié)上會有許多差異，在程序設計的環(huán)境下，SQL語句要做一些必要的擴充。

4.8.1嵌入式SQL的概述

SQL語言提供了兩種不同的使用方式：交互式、嵌入式。對嵌入式SQL，RDBMS一般采用預編譯方法處理，即由RDBMS的預處理程序對源程序進行掃描，識別出SQL語句，把它們轉換成主語言調用語句，以使主語言編譯程序能識別它，最后由主語言的編譯程序將整個源程序編譯成目標碼。為了區(qū)分SQL語句與主語言語句，需要在所有SQL語句前加前綴EXECSQL，其結束標志隨主語言的不同而不同。

嵌入了SQL的應用程序的執(zhí)行過程如圖4.4所示。

圖4.4嵌入了SQL的應用程序的執(zhí)行過程

以C為主語言的嵌入式SQL語句的一般形式為

EXECSQL<SQL語句>;

例：EXECSQLDROPTABLEPRODUCT;

以COBOL作為主語言的嵌入式SQL語句的一般形式為

EXECSQL<SQL語句>END-EXEC

例：EXECSQLDROPTABLEPRODUCTEND-EXEC

注意：嵌入式SQL語句根據(jù)作用的不同，可分為可執(zhí)行語句和說明性語句。允許出現(xiàn)可執(zhí)行的高級語言語句的地方，都可以寫可執(zhí)行SQL語句；允許出現(xiàn)說明語句的地方，都可以寫說明性SQL語句。

4.8.2嵌入式SQL語句與主語言之間的通信

將SQL語言嵌入高級語言中進行混合編程時，SQL語言中描述性的面向集合的語句負責操縱數(shù)據(jù)庫，高級語言中過程性的面向記錄的語句負責控制程序流程。這時，程序中會含有兩種不同計算模型的語句，它們之間應該如何通信呢？

數(shù)據(jù)庫工作單元和源程序工作單元之間的通信主要包括以下三個方面：

(1)向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息，使主語言能夠據(jù)此控制程序流程，主要用SQL通信區(qū)(SQLCommunicationArea，SQLCA)實現(xiàn)。

(2)主語言向SQL語句提供參數(shù)，主要由主變量(HostVariable)實現(xiàn)。

(3)將SQL語句查詢數(shù)據(jù)庫的結果交主語言進一步處理，主要由主變量和游標(Cursor)實現(xiàn)。

1.

SQL通信區(qū)

SQL語句執(zhí)行后，DBMS反饋給應用程序若干信息，這些信息主要包括描述系統(tǒng)當前工作狀態(tài)和描述運行環(huán)境兩方面內容。再將這些信息送到SQL通信區(qū)(SQLCA)中，應用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來的執(zhí)行語句。

SQLCA的內容既與所執(zhí)行的SQL語句有關，又與該SQL語句的執(zhí)行情況有關。例如在執(zhí)行刪除語句DELETE后，不同的執(zhí)行情況，SQLCA中會有不同的信息：若違反數(shù)據(jù)保護規(guī)則，則操作拒絕；若沒有滿足條件的行，則一行也不會被刪除；若成功刪除，則顯示刪除的行數(shù)；若無條件刪除，則顯示警告信息；由于各種原因，執(zhí)行出錯等。

SQLCA是一個數(shù)據(jù)結構，在使用SQLCA之前，應用EXECSQLINCLUDESQLCA加以定義。SQLCA中有一個存放每次執(zhí)行SQL語句后返回代碼的變量SQLCODE。如果SQLCODE等于預定義的常量SUCCESS，則表示SQL語句執(zhí)行成功，否則表示出錯。應用程序每執(zhí)行完一條SQL語句之后都應該測試一下SQLCODE的值，以了解該SQL語句的執(zhí)行情況并做相應處理。

2.主變量

在SQL語句中，使用的主語言程序變量簡稱為主變量(HostVariable)，嵌入式SQL語句中可以用主變量來輸入或輸出數(shù)據(jù)。根據(jù)作用不同，主變量分為兩種類型：輸入主變量，由應用程序對其賦值，SQL語句引用；輸出主變量，由SQL語句賦值或設置狀態(tài)信息，返回給應用程序。有時，一個主變量有可能既是輸入主變量又是輸出主變量。

一個主變量可以附帶一個指示變量(IndicatorVariable)。指示變量是一個整型變量，用來“指示”所指主變量的值或條件。輸入主變量可以利用指示變量賦空值。輸出主變量可以利用指示變量檢測出是否空值，或值是否被截斷。

對主變量說明之后，它便可以在SQL語句中任何一個能夠使用表達式的地方出現(xiàn)。為了與數(shù)據(jù)庫對象名(表名、視圖名、列名等)區(qū)別，SQL語句中的主變量名前要加冒號“:”作為標志。指示變量前也必須加冒號標志，且要緊跟在所指主變量之后。在SQL語句之外(主語言語句中)使用主變量和指示變量可以直接引用，不必加冒號。

3.游標(Cursor)

SQL語言與主語言具有不同的數(shù)據(jù)處理方式。SQL語言是面向集合的，一條SQL語句原則上可以產(chǎn)生或處理多條記錄，而主語言是面向記錄的，一組主變量一次只能存放一條記錄，故僅使用主變量并不能完全滿足SQL語句向應用程序輸出數(shù)據(jù)的要求。因此，嵌入式SQL引入了游標的概念，用來協(xié)調這兩種不同的處理方式。

游標是系統(tǒng)為用戶開設的一個數(shù)據(jù)緩沖區(qū)，用來存放SQL語句的執(zhí)行結果。每個游標區(qū)都有一個名字，用戶可以用SQL語句逐一從游標中獲取記錄，并賦給主變量，再交由主語言進一步處理。

4.8.3不使用游標的SQL語句

不使用游標的語句有：

·