第三章（關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL）

本章要點(diǎn)11.了解SQL的發(fā)展以及特點(diǎn)。2.關(guān)系模式的定義是SQL語言的一個(gè)重要功能。重點(diǎn)掌握表的定義。3.視圖是為便于應(yīng)用而提出的邏輯上的關(guān)系。在了解視圖概念的基礎(chǔ)上，學(xué)會定義和查詢視圖。4.使用索引可以獲得對數(shù)據(jù)庫表中特定信息的快速訪問。學(xué)會索引的定義和使用。1本章要點(diǎn)25.用結(jié)構(gòu)化查詢語言SQL對數(shù)據(jù)庫進(jìn)行查詢，是本章的重點(diǎn),也是本書的重要內(nèi)容。6.大量的查詢要求涉及到兩個(gè)或兩個(gè)以上的關(guān)系，一種常用方法是使用連接查詢，所使用的SQL語句和關(guān)系代數(shù)表達(dá)式不僅完全等價(jià)，而且思路也完全一致。對不同的關(guān)系通過公共屬性進(jìn)行并、交、差的運(yùn)算，是多個(gè)關(guān)系進(jìn)行查詢的又一種方法。7.嵌套查詢類似于是對多個(gè)關(guān)系進(jìn)行查詢的有效方法。其中，相關(guān)子查詢，因內(nèi)外層相互關(guān)聯(lián)，而略顯復(fù)雜，應(yīng)加深理解。23.1SQL概述3.2數(shù)據(jù)定義3.3數(shù)據(jù)查詢3.4數(shù)據(jù)更新3.5視圖3.6索引33.1SQL概述SQL語言是當(dāng)前最為成功、應(yīng)用最為廣泛的關(guān)系數(shù)據(jù)庫語言，其主要特點(diǎn)包括：1.SQL語言非常簡潔2.綜合統(tǒng)一3.高度非過程化4.面向集合的操作方式5.以同一種語法結(jié)構(gòu)提供兩種使用方式43.1.2SQL語言功能概述SQL功能可分為四大部分：數(shù)據(jù)定義功能、數(shù)據(jù)控制功能、數(shù)據(jù)查詢功能和數(shù)據(jù)操縱功能。SQL功能命令動詞數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)查詢SELECT數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE53.1.3SQL支持三級模式結(jié)構(gòu)SQL語言支持關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu)，其中視圖對應(yīng)外模式，基本表對應(yīng)模式，存儲文件對應(yīng)內(nèi)模式。6基本表（BASETABLE）是獨(dú)立存在的表，不是由其它的表導(dǎo)出的表。一個(gè)關(guān)系對應(yīng)一個(gè)基本表，一個(gè)或多個(gè)基本表對應(yīng)一個(gè)存儲文件。視圖（VIEW）是從基本表或其他視圖中導(dǎo)出的表，它本身不獨(dú)立存儲在數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中。存儲文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。存儲文件的物理文件結(jié)構(gòu)是任意的。用戶可以用SQL語言對視圖和表進(jìn)行查詢。73.2數(shù)據(jù)定義數(shù)據(jù)定義，也就是用SQL來描述數(shù)據(jù)庫中的數(shù)據(jù)結(jié)構(gòu)，即如何定義關(guān)系的模式。在SQL語句格式中，有下列約定符號的語法規(guī)定需要說明：1.語句格式的約定符號語句格式中，尖括號“<>”中為實(shí)際語義；中括號“[]”中的內(nèi)容為任選項(xiàng)；大括號“{}”或用分隔符“|”中的內(nèi)容為必選項(xiàng)，即必選其中之一項(xiàng)；[,…n]表示前面的項(xiàng)可重復(fù)多次。82.一般語法規(guī)定SQL中的數(shù)據(jù)項(xiàng)（包括列項(xiàng)、表和視圖）分隔符為“，”，其字符串常數(shù)的定界符用單引號“’”表示。3.SQL特殊語法規(guī)定SQL的關(guān)鍵詞一般使用大寫字母表示；SQL語句的結(jié)束符為“；”。SQL一般采用格式化書寫方式。93.2.1屬性的數(shù)據(jù)模型在定義或修改關(guān)系模式的時(shí)候，所有的屬性都必須具有一個(gè)指定的數(shù)據(jù)類型。SQL一般支持如下幾種主要的數(shù)據(jù)類型。1.CHAR（n）表示長度為n個(gè)字符的定長字符串。2.INT或者INTEGER表示一般的整數(shù)。3.FLOAT，或者REAL表示一般的浮點(diǎn)數(shù)。

4.DATE和TIMEDATE表示日期；TIME，表示時(shí)間。

5.BIT（n）表示長度為n的位串。103.2.2定義表定義一個(gè)表的SQL語句最簡單的格式如下：CREATETABLE<表名>

（<列名1><數(shù)據(jù)類型>，<列名2><數(shù)據(jù)類型>，……<列名n><數(shù)據(jù)類型>）；其中，CREATETABLE是關(guān)鍵字，表示我們將要定義一個(gè)新的關(guān)系模式；表名就是我們要定義表的名字（即關(guān)系名）；圓括號里的列名1，列名2，…，列名n是關(guān)系的屬性名，每個(gè)屬性名后面的數(shù)據(jù)類型就代表該屬性對應(yīng)的分量的數(shù)據(jù)類型。系統(tǒng)執(zhí)行了這條建表的語句后，就會在數(shù)據(jù)庫中新建一個(gè)表R（A1，A2，…，An）。11在實(shí)際應(yīng)用當(dāng)中，我們通常對某些屬性或者對所建的表作一定的約束，例如規(guī)定其不能為空或者設(shè)定其默認(rèn)值等等。如果有約束進(jìn)行如下格式的建表。CREATETABLE<表名>(<列名1><數(shù)據(jù)類型>[列級完整性約束條件][,<列名2><數(shù)據(jù)類型>[列級完整性約束條件]]...[,表級完整性約束條件]);[<其它參數(shù)>]）12建立倉庫表CREATETABLE倉庫

(倉庫號CHAR(5)PRIMARYKEY,

城市CHAR(10),

面積INTCHECK(面積>0))建立職工表CREATETABLE職工

(倉庫號CHAR(5)FOREIGNKEYREFERENCES倉庫,

職工號CHAR(5)PRIMARYKEY,

工資INTCHECK(工資>=1000AND工資<=5000)DEFAULT1200)）133.2.3修改表修改表的命令是ALTERTABLE，該命令格式如下：ALTERTABLE<表名>ADD<列名><數(shù)據(jù)類型>[<列級完整性約束>]DROP<完整性約束名>DROPCOLUMN<列名>ALTERCOLUMN<列名><數(shù)據(jù)類型>[<列級完整性約束>]相應(yīng)的功能分別是⑴增加新的屬性（字段）⑵刪除完整性約束⑶刪除屬性⑷修改屬性的定義14往訂購單關(guān)系中增加一個(gè)新屬性“完成日期”ALTERTABLE訂購單

ADD完成日期DATETIMENULL刪除訂購單關(guān)系中的“完成日期”屬性：ALTERTABLE訂購單DROPCOLUMN完成日期153.2.4刪除基本表刪除表的命令格式：DROPTABLE例：DROPTABLE訂購單DROPTABLE直接從磁盤上刪除<表名>所對應(yīng)的數(shù)據(jù)庫文件。163.3數(shù)據(jù)查詢SQL的核心是查詢。SQL的查詢命令也稱作SELECT命令，它的基本形式由SELECT-FROM-WHERE查詢塊組成，多個(gè)查詢塊可以嵌套執(zhí)行。SQL查詢命令格式：SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,目標(biāo)列表達(dá)式]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];17含義：根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或視圖中找出滿足條件的元組，再按SELECT子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。如果有GROUP子句，則將結(jié)果按<列名1>的值進(jìn)行分組，該屬性列值相等的元組為一個(gè)組，每個(gè)組產(chǎn)生結(jié)果表中的一條記錄。通常會在每組中作用集函數(shù)。如果GROUP子句帶HAVING短語，則只有滿足指定條件的組才可以輸出。如果有ORDER子句，則結(jié)果表還要按<列名2>的值的升序或降序排序。183.3.1簡單查詢從幾個(gè)最簡單的查詢開始，這些查詢給予單個(gè)表，可以有簡單的查詢條件。這樣的查詢由Select和From短語構(gòu)成無條件查詢，或由SELECT、FROM和WHERE短語構(gòu)成條件查詢。例：從職工關(guān)系中檢索所有工資值。SELECT工資FROM職工SELECTDISTINCT工資FROM職工DISTINCT短語的作用是去掉查詢結(jié)果中的重復(fù)值.19SELECT*FROM倉庫其中“*”是通配符，表示所有屬性，即字段，這里的命令等同于：SELECT倉庫號,城市,面積FROM倉庫SELECTDISTINCT倉庫號FROM職工WHERE工資>1210這里Where短語指定了查詢條件，查詢條件可以是任意復(fù)雜的邏輯表達(dá)式。20SELECT*FROM職工WHERE工資BETWEEN1220AND1240表達(dá)式“工資BETWEEN1220AND1240”等價(jià)于(工資>=1220)AND(工資<=1240)顯然使用BETWEEN…AND…表達(dá)條件更清晰、更簡潔。SELECT*FROM供應(yīng)商WHERE供應(yīng)商名LIKE'%公司‘LIKE用于查找指定列中與匹配串常量匹配的元組。

21匹配串中可包含如下四種通配符：_(下劃線)：匹配任意一個(gè)字符。％（百分號）：匹配0個(gè)或多個(gè)字符。[]:匹配[]中的任意一個(gè)字符。如[abcd]表示匹配a、b、c和d中的任何一個(gè)。[^]:不匹配[]中的任意一個(gè)字符。如[abcd]表示不匹配a、b、c和d。223.3.2排序SQL語句具有將查詢結(jié)果按用戶指定的列進(jìn)行排序的功能，而且查詢的結(jié)果既可以按一列進(jìn)行排序，也可以按多列進(jìn)行排序。查詢結(jié)果可以從小到大（升序）排列，也可以從大到?。ń敌颍┡帕小Ｅ判蜃泳涞母袷綖椋篛RDERBY列名[ASC|DESC][,…n]SELECT*FROM職工ORDERBY工資SELECT*FROM職工ORDERBY倉庫號,工資

233.3.3連接查詢?nèi)粢粋€(gè)查詢涉及兩個(gè)或兩個(gè)以上的表，稱之為連接查詢，連接查詢時(shí)關(guān)系數(shù)據(jù)庫中最主要的查詢。連接條件的一般格式為：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

24DBMS執(zhí)行連接操作的過程是：首先取表1中的第1個(gè)元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第1個(gè)元組與該元組拼接起來，形成結(jié)果表中的一個(gè)元組。表2全部查找完畢后，再取表1中的第2個(gè)元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第2個(gè)元組與該元組拼接起來，形成結(jié)果表中的另一個(gè)元組。重復(fù)這個(gè)過程，直到表1中的全部元組都處理完畢為止。251.一般連接找出工資多于1230元的職工號和他們所在的城市。SELECT職工號,城市FROM職工,倉庫WHERE(工資>1230)AND(職工.倉庫號=倉庫.倉庫號)找出工作在面積大于400的倉庫的職工號以及這些職工工作所在的城市。SELECT職工號,城市FROM倉庫,職工WHERE(面積>400)AND(職工.倉庫號=倉庫.倉庫號)262.別名

在連接操作中，經(jīng)常需要使用關(guān)系名作前綴，有時(shí)這樣顯得很麻煩。因此，SQL允許在FROM短語中為關(guān)系名定義別名，格式為：<關(guān)系名>[AS]<別名>給出有北京倉庫訂購單的北京供應(yīng)商的名稱。就可以使用別名，如下是使用了別名同樣的連接語句：SELECT供應(yīng)商名FROM供應(yīng)商ASS,訂購單ASP,職工ASE,倉庫ASWWHERE地址=‘北京'AND城市=‘北京'ANDS.供應(yīng)商號=P.供應(yīng)商號ANDP.職工號=E.職工號ANDE.倉庫號=W.倉庫號273.內(nèi)連接在新的SQL標(biāo)準(zhǔn)中內(nèi)連接（INNER）運(yùn)算的一般格式是：SELECT<屬性或表達(dá)式列表>FROM<表名>[INNER]JOIN<表名>ON<連接條件>[WHERE<限定條件>]內(nèi)連接就是傳統(tǒng)的連接操作，其中INNER可以省略，這里用ON短語指定連接條件，用WHERE短語指定其它限定條件。284.外連接在內(nèi)連接中，結(jié)果集中保留了符合連接條件的元組，而排除了兩個(gè)表中沒有對應(yīng)的或匹配的元組情況。如果要求查詢結(jié)果中保留非匹配的元組，就要執(zhí)行外部連接操作。SQL的外連接分左外部和右外部連接兩種，左外部連接操作是在結(jié)果集中保留連接表達(dá)式左表中的非匹配記錄；右外部連接查詢操作是在結(jié)果集中保留連接表達(dá)式右表中的非匹配記錄。外連接中不匹配的分量用NULL表示。29外連接（OUTER）運(yùn)算的一般格式：SELECT<屬性或表達(dá)式列表>FROM<表名>LEFT|RIGHT[OUTER]JOIN<表名>ON<連接條件>[WHERE<限定條件>]SELECT倉庫.倉庫號,城市,面積,職工號,工資FROM倉庫LEFTJOIN職工ON倉庫.倉庫號=職工.倉庫號303.3.4嵌套查詢

在SQL語言中，一個(gè)SELECT┅FROM┅WHERE語句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。在書寫嵌套查詢語句時(shí)，總是從上層查詢塊（也稱外層查詢塊或父查詢）向下層查詢（也稱子查詢）塊書寫，而在處理時(shí)則是由下層向上層處理，即下層查詢結(jié)果集用于建立上層查詢塊的查詢條件。311.使用IN操作符的嵌套查詢帶有IN謂詞的子查詢是指外層查詢與子查詢之間用IN進(jìn)行連接.哪些城市至少有一個(gè)倉庫的職工的工資為1250元？SELECT城市FROM倉庫WHERE倉庫號IN(SELECT倉庫號

FROM職工

WHERE工資=1250）

32找出和職工E4掙同樣工資的所有職工。SELECT職工號FROM職工WHERE工資IN(SELECT工資

FROM職工

WHERE職工號=‘E4’)

332.使用量詞的嵌套查詢在嵌套查詢中可以使用ANY、SOME、ALL等量詞，它們的形式是：<表達(dá)式><比較運(yùn)算符>[ANY|ALL|SOME](子查詢)檢索有職工的工資大于或等于WH1倉庫中任何一名職工的工資的倉庫號。SELECTDISTINCT倉庫號FROM職工WHERE工資>=ANY(SELECT工資FROM職工

WHERE倉庫號=‘WH1')34檢索有職工的工資大于或等于WH1倉庫中所有職工的工資的倉庫號。SELECTDISTINCT倉庫號FROM職工WHERE工資>=ALL(SELECT工資FROM職工WHERE倉庫號=‘WH1')353.使用EXISTS的嵌套查詢在嵌套查詢中還可以使用[NOT]EXISTS，具體形式是：[NOT]EXISTS(子查詢)檢索那些倉庫中還沒有職工的倉庫的信息。SELECT*FROM倉庫WHERENOTEXISTS(SELECT*FROM職工

WHERE倉庫號=倉庫.倉庫號)36檢索那些倉庫中至少已經(jīng)有一個(gè)職工的倉庫的信息。SELECT*FROM倉庫WHEREEXISTS(SELECT*FROM職工

WHERE倉庫號=倉庫.倉庫號)373.3.5分組及計(jì)算查詢SQL語言是完備的，也就是說，只要數(shù)據(jù)是按關(guān)系方式存入數(shù)據(jù)庫的，就能構(gòu)造合適的SQL命令把它檢索出來。事實(shí)上，SQL不僅具有一般的檢索能力，而且還有計(jì)算方式的檢索，比如檢索職工的平均工資。檢索某個(gè)倉庫中職工的最高工資值等。

找出供應(yīng)商所在地的數(shù)目

SELECTCOUNT(DISTINCT地址)FROM供應(yīng)商

38求在“上?！眰}庫工作的職工的最高工資值。SELECTMAX(工資)FROM職工WHERE倉庫號IN(SELECT倉庫號FROM倉庫

WHERE城市=‘上?！?上面幾個(gè)例子是對整個(gè)關(guān)系的運(yùn)算，而利用GROUPBY子句進(jìn)行分組計(jì)算查詢使用得更加廣泛。GROUPBY短語格式如下：GROUPBY<列名>[,<列名>…][HAVING<謂詞>]

39求每個(gè)倉庫的職工的平均工資SELECT倉庫號,AVG(工資)FROM職工GROUPBY倉庫號在HAVING子句中可以使用計(jì)算函數(shù)，但在WHERE子句中則不能使用計(jì)算函數(shù)。HAVING子句通常與GROUPBY子句一起使用。例：求至少有兩個(gè)職工的每個(gè)倉庫的平均工資。SELECT倉庫號,COUNT(*),AVG(工資)FROM職工GROUPBY倉庫號HAVINGCOUNT(*)>=2

403.4數(shù)據(jù)更新

數(shù)據(jù)更新是指數(shù)據(jù)的增、刪、改操作，SQL的數(shù)據(jù)更新語句包括INSERT（插入）、UPDATE（修改）和DELETE（刪除）三種。SQL的數(shù)據(jù)插入語句有兩種使用形式：一種是使用常量，一次插入一個(gè)元組；另一種是插入子查詢的結(jié)果，一次插入多個(gè)元組。插入一個(gè)元組格式為：

INSERTINTO<表名>[(<列名1>[,<列名2>…])]VALUES(<表達(dá)式>[,<表達(dá)式>……])

41插入一個(gè)查詢結(jié)果格式為：INSERTINTO<表名>[(<列名>[,<列名>…])]<SELECT查詢>插入一個(gè)完整的元組INSERTINTO訂購單

VALUES(‘E7',‘S4',‘OR76',‘05-25-2002')插入一個(gè)不完整的元組

INSERTINTO訂購單(職工號,訂購單號)VALUES(‘E7',‘OR76')

423.4.2數(shù)據(jù)修改SQL的數(shù)據(jù)修改語句是UPDATE。其格式為：UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>…][[FROM<表名>]WHERE<邏輯表達(dá)式>]

43給WH1倉庫的職工提高10%的工資UPDATE職工SET工資=工資*1.10WHERE倉庫號=‘WH1‘給所有職工增加10%的工資UPDATE職工SET工資=工資*1.10443.4.3數(shù)據(jù)刪除SQL的刪除語句是DELETE。其格式為：DELETEFROM<表名>[[FROM<表名>]WHERE<邏輯表達(dá)式>]刪除倉庫關(guān)系中倉庫號值是WH2的元組DELETEFROM倉庫WHERE倉庫號='WH2‘刪除所在城市是上海的倉庫的所有職工元組DELETEFROM職工FROM倉庫WHERE倉庫.倉庫號=職工.倉庫號AND城市=‘上海'453.5視圖

視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨(dú)立存儲在數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個(gè)虛表。用戶可以用SQL語言對視圖和基本表進(jìn)行查詢。在用戶眼中，視圖和基本表都是關(guān)系，而存儲文件對用戶是透明的。463.5.1定義視圖SQL語言用CREATEVIEW來定義視圖，其命令格式如下：CREATEVIEW<視圖名>AS<SELECT-查詢塊>其功能為定義視圖名和視圖結(jié)構(gòu)，將后面的子查詢得到的元組作為視圖的內(nèi)容。例：CREATEVIEWV_bjASSELECT倉庫號，面積FROM倉庫WHERE城市=‘北京'例：CREATEVIEWE_WASSELECT職工號，倉庫號FROM職工473.5.2查詢視圖由于視圖中并不包含任何數(shù)據(jù)，當(dāng)我們把視圖當(dāng)作一個(gè)虛擬的表進(jìn)行查詢時(shí)，實(shí)際上是從定義該視圖的基本表中得到適當(dāng)?shù)脑M，所以查詢能夠得到回答。DBMS執(zhí)行對視圖的查詢時(shí)，首先進(jìn)行有效性檢查，檢查查詢涉及的表、視圖等是否在數(shù)據(jù)庫中存在，如果存在，則從數(shù)據(jù)字典中取出查詢涉及的視圖的定義，把定義中的子查詢和用戶對視圖的查詢結(jié)合起來，轉(zhuǎn)換成對基本表的查詢，然后再執(zhí)行這個(gè)經(jīng)過修正的查詢。將對視圖的查詢轉(zhuǎn)換為對基本表的查詢的過