第4章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言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本章小結(jié)習(xí)題4

本章主要內(nèi)容

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

本章學(xué)習(xí)目標(biāo)

—

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

—

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

—

掌握SQL語言的視圖操作。

—

了解嵌入式SQL的應(yīng)用及使用方法。

4.1SQL語言概述

SQL是介于關(guān)系代數(shù)與關(guān)系演算之間的一種結(jié)構(gòu)化查詢語言，其功能不僅僅是查詢。SQL是一個(gè)功能強(qiáng)大，通用的、簡(jiǎn)單易學(xué)的數(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功能集于一體，語言風(fēng)格統(tǒng)一，可以獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動(dòng)。

2.高度非過程化

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

3.面向集合的操作方式

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

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

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

5.語言簡(jiǎn)潔，易學(xué)易用

SQL語言功能極為強(qiáng)大，但語言結(jié)構(gòu)簡(jiǎn)捷，設(shè)計(jì)構(gòu)思非常巧妙。在SQL語言中所有的核心功能只需要9個(gè)動(dòng)詞，如表4.1所示，而且語句接近英語語句，方便學(xué)習(xí)，容易使用。

4.1.2SQL語言基本概念

SQL語言支持?jǐn)?shù)據(jù)庫三級(jí)模式結(jié)構(gòu)，如圖4.1所示，其中外模式(E)對(duì)應(yīng)于視圖(View)和部分基本表；模式(C)對(duì)應(yīng)于基本表，是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述，是所有用戶的公共數(shù)據(jù)視圖；內(nèi)模式(I)對(duì)應(yīng)于存儲(chǔ)文件。

圖4.1SQL支持關(guān)系數(shù)據(jù)庫三級(jí)模式結(jié)構(gòu)

基本表是本身獨(dú)立存在的表，在SQL中一個(gè)關(guān)系就對(duì)應(yīng)一個(gè)基本表。一個(gè)(或多個(gè))基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件，一個(gè)表可以帶若干索引，索引也存放在存儲(chǔ)文件中。存儲(chǔ)文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式，物理結(jié)構(gòu)是任意的，對(duì)用戶透明。

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

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

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

用戶表：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ù)庫模式的定義、基本表的定義、視圖的定義以及索引的定義四部分。特別要注意的是這里所說的“定義”不僅僅是對(duì)象的創(chuàng)建(CREATE)，還包括刪除(DROP)和修改(ALTER)，共三部分內(nèi)容。

4.3.1基本表的操作

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

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

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

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

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

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

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

建表的同時(shí)還可以定義該表的完整性約束條件。當(dāng)用戶操作基本表時(shí)，DBMS自動(dòng)檢查該操作是否違反了預(yù)先定義的完整性約束條件。

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

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

·＜＞：其中的內(nèi)容為必選項(xiàng)，表示不能為空的實(shí)際語義。

·［］：其中的內(nèi)容為任選項(xiàng)。

·｛｝或｜：必選其中的一項(xiàng)。

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

一般語法規(guī)定：

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

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

·SQL語句的結(jié)束符為“;”。

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

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

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

9個(gè)屬性組成。其中用戶編號(hào)為主鍵，密碼和姓名不許為空。

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

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

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

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

2.完整性約束

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

列級(jí)完整性約束：針對(duì)關(guān)系屬性值設(shè)置的限定條件，只能應(yīng)用在一列上。

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

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

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

②按照語法在相應(yīng)的列名及其類型后單獨(dú)列出：

CONSTRAINT<約束名>PRIMARYKEY；

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

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

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

注意：關(guān)系模型的實(shí)體完整性在CREATETABLE中用PRIMARYKEY定義。若單屬性構(gòu)成的主鍵可以有兩種說明方法：定義為列級(jí)約束條件或定義為表級(jí)約束條件。若對(duì)于多個(gè)屬性構(gòu)成的主鍵只有一種說明方法：定義為表級(jí)約束條件。

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

①在列級(jí)定義主鍵，定義語句如下：

②在表級(jí)定義主鍵，定義語句如下：

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

圖4.2插入記錄時(shí)對(duì)全表的掃描

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

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

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

REFERENCES<表名>(<列名>)

②

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

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

③可在相應(yīng)列名及其類型后面單獨(dú)列出并指定約束名，其語法格式為

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

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

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

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

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

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

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

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

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

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

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

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

列級(jí)UNIQUE約束：

CONSTRAINT<約束名>UNIQUE

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

表級(jí)UNIQUE約束：

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

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

CREATETABLESORTKIND

(

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

NAMEVARCHAR(32)CONSTRAINTUN_NAME

UNIQUE

);

③?CHECK約束。

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

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

3.基本表的修改

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

ALTERTABLE<基本表名>

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

[DROP<完整性約束名>]

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

說明：

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

②ADD子句用于在基本表中增加新列和新完整性約束條件。新增加的列不能定義為“NOTNULL”，因?yàn)椴徽摶颈碇惺欠裼袛?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.基本表的刪除

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

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

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

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

CASCADE：表明刪除該表沒有限制。在刪除基本表的同時(shí)，相關(guān)的依賴對(duì)象一起刪除。

說明：

①<基本表名>是要?jiǎng)h除的基本表的名字。

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

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

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

【例4.12】刪除基本表PRODUCT。

DROPTABLEPRODUCT;

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

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

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

從比較中可以知道：

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

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

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

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

4.3.2索引操作

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

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

1.索引的特點(diǎn)

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

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

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

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

2.索引的用途

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

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

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

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

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

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

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

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

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

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

①包含太多重復(fù)值的列。

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

③值特別長的列。

④查詢返回率很高的列。

⑤具有很多NULL值的列。

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

⑦記錄較少的基本表。

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

4.索引的類型及選擇

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

(1)單列索引。單列索引是對(duì)基本表的某一單獨(dú)的列進(jìn)行的索引，是最簡(jiǎn)單和最常用的索引類型，它是在表的某一列的基礎(chǔ)上建立的。

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

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

5.建立索引

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

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

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

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

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

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

CREATECLUSTERINDEXUIDXONUSER1(NAME);

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

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

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

6.刪除索引

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

DROPINDEX<索引名>;

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

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

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

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

DROPINDEXPIDX;

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

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

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

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

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

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

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

8.索引的缺點(diǎn)

創(chuàng)建索引有以下3個(gè)缺點(diǎn)：

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

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

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

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

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

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

CREATEDATABASEBOOKSTORES;

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

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

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

CREATECLUSTERINDEXIDXONSTUDENT(ID);

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

DROPINDEXSIDX;

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

4.4.1插入操作

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

1.插入單條元組

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

INSERT

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

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

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

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

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

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

‘01’，‘計(jì)算機(jī)’)。

INSERT

INTOSORTKIND

VALUES(‘01’,‘計(jì)算機(jī)’);

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

INSERT

INTOPRODUCT

(ID,NAME,PRICE,ZUOZHE,SORTKIND_ID)

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

29.5,

‘施伯樂’,

‘01’);

新插入的記錄在DESCRIPTION、IMG列上自動(dòng)賦空值。

2.插入多個(gè)元組

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

INSERT

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

<子查詢>;

說明：

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

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

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

INSERT

INTOUSER1_ORDER(ID)

SELECTID

FROMPRODUCT

WHERESORTKIND_ID='01';

4.4.2修改操作

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

UPDATE<表名>

SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]...

[WHERE<條件>];

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

1.修改某一個(gè)元組的值

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

UPDATEPRODUCT

SETPRICE=30

WHEREID?=?‘9787040123104’;

2.修改多個(gè)元組的值

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

UPDATEPRODUCT

SETPRICE=PRICE+1;

3.帶子查詢的修改語句

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

4.4.3刪除操作

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

DELETE

FROM<表名>

[WHERE<條件>]；

1.刪除某一個(gè)元組的值

【例4.26】將PRODUCT表中ID號(hào)為“9787040123104”的書刪除。

DELETE

FROMPRODUCT

WHEREID=‘9787040123104’;

2.刪除多個(gè)元組的值

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

DELETE

FROMPRODUCT;

3.帶子查詢的刪除語句

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

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

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

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

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

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

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

4.5.1單表查詢

單表查詢指只涉及一個(gè)關(guān)系的查詢。

1.選擇關(guān)系中的若干列

選擇表中的所有列或部分列，即為投影運(yùn)算。

1)查詢?nèi)苛?/p>

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

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

SELECT*

FROMUSER1;

等價(jià)于

SELECTID,NAME,PASSWORD,ADDRESS,POSTCODE，

EMAIL,HOME_PHONE,CELL_PHONE,OFFICE_PHONE

FROMUSER1;

2)查詢指定列

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

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

SELECT關(guān)鍵字后面的<目標(biāo)列表達(dá)式>既可是表中的屬性列，也可以是表達(dá)式。

2.選擇表中的若干元組

1)消除重復(fù)行

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

【例4.43】查詢出訂購了圖書的用戶編號(hào)。

SELECTUSER1_ID

FROMUSER1_ORDER;

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

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

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

(2)確定范圍。

BETWEEN…AND…可以用來查詢?cè)谥付ǚ秶鷥?nèi)的元組，其指定的是閉區(qū)間，BETWEEN后為下限，AND后為上限。NOTBETWEEN…AND…用來查詢不在指定范圍內(nèi)的元組。

(3)字符匹配。

用LIKE謂詞進(jìn)行字符匹配。其一般格式如下：

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

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

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

(4)確定集合。

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

(5)復(fù)合條件查詢。

邏輯運(yùn)算符AND和OR可用來連接多條件查詢，條件運(yùn)算順序?yàn)閺淖蟮接遥褹ND優(yōu)先級(jí)高于OR，但用戶可以用括號(hào)改變優(yōu)先級(jí)。

(6)涉及空值的查詢。

3)對(duì)查詢結(jié)果進(jìn)行排序

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

4)函數(shù)查詢

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

5)對(duì)查詢結(jié)果進(jìn)行分組

GROUPBY子句將查詢結(jié)果按照某一列或某幾列進(jìn)行分組，值相等的為一組。

4.5.2連接查詢

1.普通連接

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

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

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

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

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

SELECTUSER1.*,USER1_ORDER.*

FROMUSER1,USER1_ORDER

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

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

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

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

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

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

SELECTUSER1.NAME,USER1_ORDER.ID,COST

FROMUSER1,USER1_ORDER

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

COST屬性前面沒有加表名前綴，是因?yàn)橹挥蠻SER1_ORDER表中有COST屬性，不會(huì)引起混淆。

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

注意：連接查詢方式只用一個(gè)查詢塊，并且必須在WHERE子句中給出連接謂詞。當(dāng)目標(biāo)列中涉及的屬性在不同表中時(shí)，只能使用連接查詢方式進(jìn)行查詢。

2.外連接

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

3.復(fù)合條件連接

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

4.5.3嵌套查詢

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

1.帶有IN謂詞的子查詢

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

【例4.72】查詢出計(jì)算機(jī)類的圖書編號(hào)及圖書名稱。

先分步完成子查詢，然后再構(gòu)造嵌套查詢。

①先確定計(jì)算機(jī)類圖書的編號(hào)。

SELECTID

FROMSORTKIND

WHERENAME='計(jì)算機(jī)';

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

2.帶有比較運(yùn)算符的子查詢

當(dāng)子查詢結(jié)果返回的是一個(gè)單值時(shí)，父查詢和子查詢之間可以用比較運(yùn)算符>，>=，<，<=，=，!=，<>等進(jìn)行連接。

例如，在例4.72中，由于計(jì)算機(jī)類的圖書類型編號(hào)只有一個(gè)，也就是說內(nèi)查詢結(jié)果只返回一個(gè)值，因此可以用“=”代替IN，其SQL語句如下：

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

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

SELECTAVG(COST)

FROMUSER1_ORDERY

WHEREY.USER1_ID='001';

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

SELECTUSER1_ID,ID

FROMUSER1_ORDERX

WHERECOST>=24；

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

④外層查詢?nèi)〕鱿乱粋€(gè)元組重復(fù)做上述①至③步驟，直到外層的USER1_ORDER元組全部處理完畢，得到查詢的全部結(jié)果。

求解相關(guān)子查詢不像求解不相關(guān)子查詢那樣，一次將子查詢求解出來，然后求解父查詢。內(nèi)查詢由于與外查詢有關(guān)，因此必須反復(fù)求值。

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

單獨(dú)使用比較運(yùn)算符時(shí)，要求子查詢返回的結(jié)果必須為單值。若子查詢返回的是一個(gè)集合，就要使用帶有ANY和ALL謂詞的比較運(yùn)算符，其語義組合如表4.6所示。

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

4.5.4集合查詢

因?yàn)镾ELECT語句的查詢結(jié)果是元組的集合，所以多個(gè)SELECT語句的結(jié)果可以進(jìn)行集合操作。集合操作的種類主要有并操作UNION、交操作INTERSECT、差操作EXCEPT。參加集合操作的各查詢結(jié)果的列數(shù)必須相同，對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同。

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

4.5.5SELECT語句的一般格式

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

1.目標(biāo)列表達(dá)式格式

目標(biāo)列表達(dá)式格式有以下4種：

(1)?*。

(2)

<表名>.*。

(3)

COUNT([DISTINCT|ALL]*)。

(4)

[<表名>.]<屬性列名表達(dá)式>[,[<表名>.]<屬性列名表達(dá)式>]…。

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

)?組成的運(yùn)算公式。

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

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

3.條件表達(dá)式格式

條件表達(dá)式有以下7種格式：

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

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

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

4.6.1授權(quán)操作

GRANT語句的一般格式：

GRANT<權(quán)限>[,<權(quán)限>]...

[ON<對(duì)象類型><對(duì)象名>]

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

[WITHGRANTOPTION];

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

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

對(duì)不同類型的操作對(duì)象有不同的操作權(quán)限，常見的操作權(quán)限如表4.8所示。

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

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

GRANTCREATETABLE

TOU8;

4.6.2收回權(quán)限操作

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

REVOKE<權(quán)限>[,<權(quán)限>]...

[ON<對(duì)象類型><對(duì)象名>]

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

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

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

→U5→U6→U7

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

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

4.7視圖

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

4.7.1視圖定義

1.創(chuàng)建視圖

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

CREATEVIEW

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

AS<子查詢>

[WITHCHECKOPTION];

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

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

①某個(gè)目標(biāo)列是聚集函數(shù)或列表達(dá)式。

②目標(biāo)列為*。

③多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段。

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

(1)行列子集視圖。

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

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

由于定義視圖時(shí)加上了WITHCHECKOPTION子句，以后對(duì)該視圖進(jìn)行更新時(shí)DBMS將自動(dòng)加上SORTKIND_ID='01'條件。

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

【例4.106】建立計(jì)算機(jī)類的圖書視圖。

(4)基于視圖的視圖。

【例4.107】建立計(jì)算機(jī)類且圖書價(jià)格大于20元的視圖。

(5)帶表達(dá)式的視圖。

【例4.108】定義一個(gè)反映圖書9折價(jià)格的視圖。

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

(6)分組視圖。

【例4.109】將圖書的類型編號(hào)及它的平均價(jià)格定義為一個(gè)視圖。

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

2.刪除視圖

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

DROPVIEW<視圖名>;

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

【例4.111】刪除視圖IS_PRODUCT。

DROPVIEWIS_PRODUCT;

4.7.2視圖查詢

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

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

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

視圖消解法的局限：有些情況下，視圖消解法不能生成正確查詢，因?yàn)椴捎靡晥D消解法的DBMS會(huì)限制這類查詢。

4.7.3視圖更新

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

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

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

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

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

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

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

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

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

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

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

4.7.4SQLServer中的視圖操作

4.8嵌入式SQL

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

4.8.1嵌入式SQL的概述

SQL語言提供了兩種不同的使用方式：交互式、嵌入式。對(duì)嵌入式SQL，RDBMS一般采用預(yù)編譯方法處理，即由RDBMS的預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描，識(shí)別出SQL語句，把它們轉(zhuǎn)換成主語言調(diào)用語句，以使主語言編譯程序能識(shí)別它，最后由主語言的編譯程序?qū)⒄麄€(gè)源程序編譯成目標(biāo)碼。為了區(qū)分SQL語句與主語言語句，需要在所有SQL語句前加前綴EXECSQL，其結(jié)束標(biāo)志隨主語言的不同而不同。

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

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

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

EXECSQL<SQL語句>;

例：EXECSQLDROPTABLEPRODUCT;

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

EXECSQL<SQL語句>END-EXEC

例：EXECSQLDROPTABLEPRODUCTEND-EXEC

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

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

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

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

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

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

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

1.

SQL通信區(qū)

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

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

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

2.主變量

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

一個(gè)主變量可以附帶一個(gè)指示變量(IndicatorVariable)。指示變量是一個(gè)整型變量，用來“指示”所指主變量的值或條件。輸入主變量可以利用指示變量賦空值。輸出主變量可以利用指示變量檢測(cè)出是否空值，或值是否被截?cái)唷?/p>

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

3.游標(biāo)(Cursor)

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

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

4.8.3不使用游標(biāo)的SQL語句

不使用游標(biāo)的語句有：

·