北京郵電大學(xué)軟件工程研究生的課程-數(shù)據(jù)庫(kù)設(shè)計(jì)開(kāi)發(fā)-0-3

1.3SQL自從20世紀(jì)80年代以來(lái)，SQL就是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言?，F(xiàn)在，SQL語(yǔ)言正從關(guān)系形式（ANSISQL-92標(biāo)準(zhǔn)）轉(zhuǎn)向?qū)ο?關(guān)系形式（ANSISQL-99標(biāo)準(zhǔn)，1999年頒布）。SQL語(yǔ)言是1974年由Boyce和Chamberlin提出。1986年10月美國(guó)國(guó)家標(biāo)準(zhǔn)局(ANSl)批準(zhǔn)了SQL作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn)。同年公布了SQL標(biāo)準(zhǔn)文本(簡(jiǎn)稱(chēng)SQL-86)。1987年6月國(guó)際標(biāo)準(zhǔn)化組織(ISO)也采納了此標(biāo)準(zhǔn)。1989年，美國(guó)國(guó)家標(biāo)準(zhǔn)局（ANSI）采納了新的規(guī)范SQL-89標(biāo)準(zhǔn)，取代SQL-86，同時(shí)SQL-89標(biāo)準(zhǔn)也被國(guó)際標(biāo)準(zhǔn)化組織(ISO)采納。1992年，ANSI/ISO頒布了SQL2版本，標(biāo)準(zhǔn)的名稱(chēng)為SQL-92。SQL-92分稱(chēng)幾個(gè)順序級(jí)別：從代表SQL-89最小擴(kuò)展集的“Entry”到“Intermediate”和“Full”。完成于己于1999年的SQL-99具有更加高級(jí)的特征（包括對(duì)象-關(guān)系特性），亦稱(chēng)SQL3。郭文明2003.06.051.3SQL主要的幾個(gè)數(shù)據(jù)庫(kù)生產(chǎn)廠(chǎng)商并不可能完全遵守SQL-99（以及更老的SQL-92）。我們一般更加關(guān)注在產(chǎn)品中已經(jīng)實(shí)現(xiàn)的SQL-99特征。

1.3.1SQL特點(diǎn)1.3.2數(shù)據(jù)定義1.3.2.1SQL模式的創(chuàng)建和刪除1.3.2.2SQL提供的基本數(shù)據(jù)類(lèi)型1.3.2.3定義、刪除與修改基本表

1.3.2.4建立與刪除索引1.3.2.5視圖的創(chuàng)建和刪除1.3.3數(shù)據(jù)查詢(xún)1.3.3.1簡(jiǎn)單查詢(xún)

1.3.3.2子查詢(xún)1.3.3.3UNION運(yùn)算和FORALL條件1.3.3.4高級(jí)SQL語(yǔ)法1.3.3.5集合函數(shù)1.3.3.6行分組

1.3.4數(shù)據(jù)更新1.3.4.1插入數(shù)據(jù)

1.3.4.2修改數(shù)據(jù)1.3.4.3刪除數(shù)據(jù)郭文明2003.06.051.3.1SQL特點(diǎn)1)綜合統(tǒng)一

2)高度非過(guò)程化

3)面向集合的操作方式

4)以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方式5)語(yǔ)言簡(jiǎn)捷，易學(xué)易用

和關(guān)系代數(shù)相比，就查詢(xún)能力而言，SQL并沒(méi)有根本的改進(jìn)，在關(guān)系代數(shù)查詢(xún)方面的經(jīng)驗(yàn)可以成為用SQL來(lái)實(shí)現(xiàn)查詢(xún)的良好借鑒。在構(gòu)造查詢(xún)時(shí)SQL的select語(yǔ)句比關(guān)系代數(shù)要靈活.郭文明2003.06.051.3.2數(shù)據(jù)定義SQL的數(shù)據(jù)定義功能包括對(duì)模式(Schema)、表(關(guān)系，Table)、視圖(View)和索引(Index)的創(chuàng)建、刪除和修改操作。如下表所示。操作對(duì)象操作方式創(chuàng)建刪除修改模式CREATESCHEMADROPSCHEMA表CREATETABLEDROPTABLEALTERTABLE視圖CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX郭文明2003.06.051.3.2.1SQL模式的創(chuàng)建和刪除在SQL-99中，模式是表、索引及其他數(shù)據(jù)庫(kù)對(duì)象的集合。模式名通常是一個(gè)用戶(hù)名。在CoreSQL-99和當(dāng)前大多數(shù)產(chǎn)品中，當(dāng)用戶(hù)的數(shù)據(jù)庫(kù)帳戶(hù)建立時(shí)，其模式在用戶(hù)名之后給出，他們不能再建立其他模式。SQL-99的擴(kuò)展特性允許用戶(hù)建立附加模式，一個(gè)SQL模式由模式名和模式擁有者的用戶(hù)名或賬號(hào)來(lái)確定。SQL模式的創(chuàng)建可用CREATE語(yǔ)句實(shí)現(xiàn)，其句法如下：

CREATESCHEMA<模式名>AUTHORIZATION<用戶(hù)名>

目前只有DB2UDB允許用戶(hù)建立附加模式。其它產(chǎn)品中模式用用戶(hù)名替代。

郭文明2003.06.051.3.2.1SQL模式的創(chuàng)建和刪除在SQL中還有一個(gè)“目錄”概念。目錄是SQL環(huán)境中所有模式的集合。包含數(shù)據(jù)庫(kù)中定義的對(duì)象的信息的表，由系統(tǒng)維護(hù)。ORACLE叫數(shù)據(jù)字典，DB2UDB叫目錄表，INFORMIX叫系統(tǒng)目錄。目錄表在建立數(shù)據(jù)庫(kù)時(shí)建立，用戶(hù)不能更新，但DBA可以用select獲取這些信息。當(dāng)一個(gè)SQL模式及其所屬的基本表、視圖等元素都不需要時(shí)，可以用DROP語(yǔ)句撤消這個(gè)SQL模式。DROP語(yǔ)句的句法如下：

DROPSCHEMA<模式名>[CASCADE|RESTRICT]郭文明2003.06.051.3.2.2SQL提供的基本數(shù)據(jù)類(lèi)型SQL-99ORACLEINFORMIXDB2UDB說(shuō)明Cchar(n)char(n)n<=4000char(n)n<=32767char(n)n<=254定長(zhǎng)字符型chararray[n+1]varchar(n)varchar(n)varchar2(n)varchar(n)varchar(n)變長(zhǎng)字符型chararray[n+1]numeric(p,d)decimal(p,d)numeric(p,d)decimal(p,d)number(p,d)numeric(p,d)decimal(p,d)numeric(p,d)decimal(p,d)定點(diǎn)數(shù)，由p位數(shù)字(不包括符號(hào)、小數(shù)點(diǎn))小數(shù)點(diǎn)后面有d位數(shù)字無(wú)smallintsmallintsmallintsmallint短整數(shù)shortintintegerintegerintegerinteger長(zhǎng)整數(shù)int,longintrealrealrealreal浮點(diǎn)數(shù)floatdoubleprecision,float,float(n)doubleprecision,number,floatfloat(n)doubleprecision,floatdoubleprecision,double,float,float(n)取決于機(jī)器精度的雙精度浮點(diǎn)數(shù)至少為n位精度double郭文明2003.06.051.3.2.3定義、刪除與修改基本表建立數(shù)據(jù)庫(kù)最重要的一步就是定義一些基本表。SQL語(yǔ)言使用CREATETABLE語(yǔ)句定義基本表，一般格式如下：

CREATETABLE<表名>(<列名><數(shù)據(jù)類(lèi)型>[列級(jí)完整性約束條件][，<列名><數(shù)據(jù)類(lèi)型>[列級(jí)完整性約束條件]]…[，<表級(jí)完整性約束條件>])；例:CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY,SnameCHAR(8)NOTNULL,SageSMALLINTCHECK(SageBETWEEN17AND22),SsexCHAR(2)CHECK(SsexIN(’男’,’女’)),SdeptCHAR(20)DEFAULT(‘軟件學(xué)院’));創(chuàng)建學(xué)生表:Sno為主鍵(非空唯一),Sname非空,Sage在17到20之間取值,Ssex只能取‘男’或‘女’,Sdept默認(rèn)值為‘軟件學(xué)院’.郭文明2003.06.051.3.2.3定義、刪除與修改基本表實(shí)際使用時(shí)要有用戶(hù)ID和密碼,進(jìn)入交互式環(huán)境，才能完成數(shù)據(jù)庫(kù)操作。修改基本表

ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類(lèi)型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類(lèi)型>];其中<表名>是要修改的基本表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件，MODIPY子句用于修改原有的列定義，包括修改列名和數(shù)據(jù)類(lèi)型。郭文明2003.06.051.3.2.3定義、刪除與修改基本表刪除基本表

DROPTABLE<表名>基本表一旦刪除，表中的數(shù)據(jù)、此表上建立的索引和視圖都將自動(dòng)被刪除掉。因此執(zhí)行刪除基本表的操作一定要格外小心。

注意：有的系統(tǒng)，如Oracle，刪除基本表后建立在此表上的視圖定義仍然保留在數(shù)據(jù)字典中。但是，當(dāng)用戶(hù)引用時(shí)就報(bào)錯(cuò)。郭文明2003.06.051.3.2.4建立與刪除索引索引的功能表現(xiàn)在以下3方面。(1)使用索引可以明顯地加快數(shù)據(jù)查詢(xún)的速度(2)使用索引可保證數(shù)據(jù)的唯一性(3)使用索引可以加快連接速度建立索引的原則

(1)索引的建立和維護(hù)由DBA和DBMS完成(2)大表應(yīng)當(dāng)建索引，小表則不必建索引(3)對(duì)于一個(gè)基本表，不要建立過(guò)多的索引(4)根據(jù)查詢(xún)要求建索引郭文明2003.06.051.3.2.4建立與刪除索引CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<asc|desc>[，<列名>[<asc|desc>]])；

如果數(shù)據(jù)增加刪改頻繁，系統(tǒng)會(huì)花費(fèi)許多時(shí)間來(lái)維護(hù)索引。這時(shí)，可以刪除一些不必要的索引。DROPINDEX<索引名>；

郭文明2003.06.051.3.2.5視圖的創(chuàng)建和刪除視圖是關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)提供給用戶(hù)以多種角度觀(guān)察數(shù)據(jù)庫(kù)中數(shù)據(jù)的重要機(jī)制。視圖一經(jīng)定義，就可以和基本表一樣被查詢(xún)、被刪除，我們也可以在一個(gè)視圖之上再定義新的視圖，但對(duì)視圖的更新(增加、刪除、修改)操作則有一定的限制。

CREATVIEW<視圖名>[(列名>[,<列名>]...)]AS<子查詢(xún)>[WITHCHECKOPTION]；其中子查詢(xún)可以是任意復(fù)雜的SELECT語(yǔ)句，但通常不允許含有ORDERBY子句和DISTINCT短語(yǔ)。WITHCHECKOPTION表示對(duì)視圖進(jìn)行UPDATE，INSERT和DELETE操作時(shí)要保證更新、插入或刪除的行滿(mǎn)足視圖定義中的謂詞條件(即子查詢(xún)中的條件表達(dá)式)。

郭文明2003.06.051.3.2.5視圖的創(chuàng)建和刪除DROPVIEW<視圖名>；

視圖刪除后視圖的定義將從數(shù)據(jù)字典中刪除。但是由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，不過(guò)該視圖已失效。用戶(hù)使用時(shí)會(huì)出錯(cuò)，要用DROPVIEW語(yǔ)句將它們一一刪除。DBMS執(zhí)行CREATEVIEW語(yǔ)句的結(jié)果只把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT。在關(guān)系數(shù)據(jù)庫(kù)中，并不是所有的視圖都是可更新的,因?yàn)橛行┮晥D的更新不能的有意義的轉(zhuǎn)換成對(duì)基本表的更新。行列子集視圖是可更新的。各個(gè)DBMS對(duì)視圖的更新有自己的規(guī)定。郭文明2003.06.051.3.3數(shù)據(jù)查詢(xún)數(shù)據(jù)查詢(xún)是數(shù)據(jù)庫(kù)的核心操作。SQL語(yǔ)言的數(shù)據(jù)查詢(xún)只有一條SELECT語(yǔ)句:

SELECT[ALL|DISTINCT]{*|<目標(biāo)列表達(dá)式[[as]別名]>[，<目標(biāo)列表達(dá)式[[as]別名]>]...}

FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名>[，<列名>][HAVING<條件表達(dá)式>]]][ORDERBY<列名>[ASC|DESC][，<列名>[ASC|DESC]]…];郭文明2003.06.051.3.3數(shù)據(jù)查詢(xún)Select語(yǔ)句的執(zhí)行過(guò)程可以理解為：首先，對(duì)FROM子句中的所有表做關(guān)系乘積接著，刪除不滿(mǎn)足WHERE子句的行根據(jù)GROUPBY子句對(duì)剩余的行進(jìn)行分組然后刪除不滿(mǎn)足HAVING子句的組求出SELECT子句選擇列表的表達(dá)式的值若有關(guān)鍵詞DISTINCT存在，則刪除重復(fù)的行Select中的標(biāo)識(shí)符：一般的SQL標(biāo)識(shí)符是大小寫(xiě)無(wú)關(guān)的。實(shí)際上SQL在解釋以前會(huì)把它們轉(zhuǎn)化為大寫(xiě)形式。一個(gè)標(biāo)識(shí)符必須以一個(gè)字母打頭，EntrySQL-92和CoreSQL-99將一個(gè)標(biāo)識(shí)符字節(jié)數(shù)限制在18個(gè)以?xún)?nèi)。郭文明2003.06.051.3.3數(shù)據(jù)查詢(xún)表達(dá)式可以是數(shù)值表達(dá)式、字符串表達(dá)式和日期表達(dá)式等。

數(shù)值表達(dá)式由常數(shù)、表屬性、算術(shù)運(yùn)算符、算術(shù)函數(shù)所組成。字符串表達(dá)式由常數(shù)、表屬性、字符串運(yùn)算符、字符串函數(shù)所組成。日期表達(dá)式由常數(shù)、表屬性、日期運(yùn)算符、日期函數(shù)所組成WHERE中使用謂詞來(lái)表示條件。一般情況謂詞運(yùn)算結(jié)果為T(mén)RUE或FALSE,但如果遇到空值時(shí),可能為UNKNOWN。SQL查詢(xún)的一個(gè)爭(zhēng)議點(diǎn)即：對(duì)于同一個(gè)查詢(xún)會(huì)存在眾多不同的構(gòu)造方法。郭文明2003.06.051.3.3ORACLE、INFORMIX、DB2UDB

中的一些數(shù)學(xué)函數(shù)名稱(chēng)描述結(jié)果abs(n)mod(n,b)sqrt(n)

n的絕對(duì)值，n為數(shù)值型n被b除后得到的余數(shù)。n,b為整數(shù)n的平方根，n為整數(shù)或浮點(diǎn)數(shù)

另外，三角函數(shù)、指數(shù)函數(shù)、對(duì)數(shù)函數(shù)、冪函數(shù)和round(n)數(shù)值類(lèi)型整數(shù)浮點(diǎn)郭文明2003.06.051.3.3一些標(biāo)準(zhǔn)的和特定產(chǎn)品的串處理函數(shù)SQL-99中的描述ORACLEDB2UDBINFORMIX返回串長(zhǎng)度(整數(shù)個(gè)字符)CHAR_LENGTH(str)返回子串,從m個(gè)開(kāi)始取n個(gè)SUBSTRING（str）FORMmFOR(n)返回去掉左或右空格后得到的包含空格的串TRIM（[[LEADING|TRAILING|BOTH][SET]FROM]str）返回子串str2在str1中位置，如果指定n,則從n開(kāi)始POSITION（str1INstr2）字母小寫(xiě)LOWER（str）字母大寫(xiě)UPPER（str）length(str)substr(str,m[,n])trim([[leading|trailing|both][set]from]str),ltrim(str[,set]),rtrim(str[,set])instr(str1,str2[,n])lower(str)upper(str)length(str)substr(str,m[,n])ltrim(str);rtrim(str)posstr(str1,str2[,n])lcase(str)ucase(str)length(str),char_length(str)substr(str,m[,n])，substring(strfrommforn)trim([[leading|trailing|both][set]from]str);lower(str)upper(str)郭文明2003.06.051.3.3SQL中的標(biāo)準(zhǔn)謂詞謂詞

形式

例子比較謂詞BETWEEN謂詞量化謂詞IN謂詞

EXISTS謂詞ISNULL謂詞LIKE謂詞expr1θ{expr2|(subquery)}expr1[NOT]BETWEENexpr2andexpr3exprθ[SOME|ANY|ALL](subquery)expr[NOT]IN(subquery)

[NOT]EXISTS(subquery)colnameIS[NOT]NULLcloname[NOT]LIKEval[ESCAPEval]p.price>(subquery)c.discntbetween10.0and12.0c.discnt>=all(subquery)pidin(selectpidfromorders)exist(select*…)c.discntisnullcnamelike‘A%’郭文明2003.06.051.3.3.1簡(jiǎn)單查詢(xún)例：檢索定貨記錄中所有pid值：selectpidfromorders--結(jié)果中有重復(fù)的pidselectdistinctpidfromorders--結(jié)果中pid唯一select缺省為all,distinct沒(méi)有出現(xiàn)時(shí)允許重復(fù)行，缺省情況不遵守行唯一性規(guī)則。例：在orders表上生成每筆業(yè)務(wù)的利潤(rùn)profit(收入減去60%的成本、顧客的折扣以及代理商的酬金):selectordno,x.cid,x.aid,x.pid,.40*(x.qty*p.price)-.01*(c.discnt+a.percent)*(x.qty*p.price)asprofitfromordersasx,customerasc,agentsasa,productsaspwherec.cid=x.cidanda.aid=x.aidandp.pid=x.pid;乘積×

投影π

選擇σ

郭文明2003.06.051.3.3.1簡(jiǎn)單查詢(xún)注：1）SQL-99規(guī)定FROM子句中執(zhí)行連接運(yùn)算，但大多數(shù)產(chǎn)品通過(guò)笛卡爾積運(yùn)算并且在WHERE子句中包含表示參與連接的列值相等的條件來(lái)模擬連接運(yùn)算，具體實(shí)現(xiàn)方法(執(zhí)行計(jì)劃，查詢(xún)優(yōu)化)各不相同。2）FROM中AS被省略，SQL仍能識(shí)別表別名。ORACLE和INFORMIX使用別名或表別名，DB2UDB使用相關(guān)名。

3）列表達(dá)式的列名可以通過(guò)AS指明，ORACLE中稱(chēng)列別名，INFORMIX中稱(chēng)顯示標(biāo)簽，DB2UDB中簡(jiǎn)單稱(chēng)為列名。如果沒(méi)有AS，ORACLE中將完整表達(dá)式文本作為列名。郭文明2003.06.051.3.3.2子查詢(xún)每個(gè)Select查詢(xún)都會(huì)生成一張表，但我們不能像關(guān)系代數(shù)表達(dá)式那樣任意將一個(gè)Select語(yǔ)句嵌入另一個(gè)Select語(yǔ)句。這是SQL與關(guān)系代數(shù)的一個(gè)很重要的不同點(diǎn)。例如：from子句中不能出現(xiàn)select(SQL-99標(biāo)準(zhǔn)已去掉該限制，但數(shù)據(jù)庫(kù)產(chǎn)品中并未完全實(shí)現(xiàn))，where子句中顯然可以出現(xiàn)select。出現(xiàn)在另一個(gè)select語(yǔ)句之內(nèi)的select語(yǔ)句形式稱(chēng)為子查詢(xún)。一個(gè)子查詢(xún)能以許多種方式出現(xiàn)在另一個(gè)select語(yǔ)句的WHERE子句條件中。郭文明2003.06.051.3.3.2子查詢(xún)1）IN謂詞(NOTIN)例:求通過(guò)住在北京或上海的代理商訂貨的顧客的姓名和折扣.selectcname,discntfromcustomerswherecidin(selectcidfromorderswhereaidin(selectaidfromagentswherecityin(‘北京’,’上海’)))；例:求由住在北京的顧客和住在北京的代理商組成的所有訂貨ordno.selectordnofromorderswhere(cid,aid)in(selectcid,aidfromcustomersc,agentsawherec.city=‘北京’anda.city=‘北京’);以上兩例為不相關(guān)查詢(xún):內(nèi)層子查詢(xún)獨(dú)立于外層的select.例:找出訂購(gòu)了產(chǎn)品p05的顧客的名字.selectdistinctcnamefromcustomerswhere‘p05’in(selectpidfromorderswherecid=customers.cid)本例為相關(guān)查詢(xún):子查詢(xún)使用外層select語(yǔ)句提供的數(shù)據(jù).SQL-99允許,有些系統(tǒng)不允許.郭文明2003.06.051.3.3.2子查詢(xún)2)量化比較謂詞:exprθ[SOME|ANY|ALL](subquery)

θ為比較運(yùn)算符<,<=,=,<>,>,>=。SOME和ANY含義相同，SOME是最新版本的推崇形式。例：找出傭金百分率最小的代理商aid。selectaidfromagentswherepercent<=all(selectpercentfromagents);例：求出滿(mǎn)足以下條件的顧客cid:該顧客的discnt小于任一住在北京的顧客的discnt.錯(cuò)誤：selectcidfromcustomerwherediscnt<any(selectdiscntfromcustomerswherecity=‘北京’);正確:selectcidfromcustomerwherediscnt<all(selectdiscntfromcustomerswherecity=‘北京’);注意any不是任意郭文明2003.06.051.3.3.2子查詢(xún)3)EXISTS謂詞:EXISTS(Subquery)為真當(dāng)且僅當(dāng)子查詢(xún)返回一個(gè)非空集合;NOTEXISTS(Subquery)為真當(dāng)且僅當(dāng)返回集合為空.

例:求出既訂購(gòu)了產(chǎn)品p01有訂購(gòu)了產(chǎn)品p07的顧客cid.關(guān)系代數(shù):πcid(σpid=’p01’(O))∩πcid(σpid=’p07’(O))selectdistinctcidfromordersxwherepid=‘p01’andexists(select*fromorderswherecid=x.cidandpid=‘p07’);或selectdistinctx.cidfromordersx,ordersywherex.pid=‘p01’andx.cid=y.cidandy.cid=‘p07’;EXISTS的查詢(xún)一般能找到等價(jià)的其他查詢(xún)形式。使用和不使用exists交運(yùn)算郭文明2003.06.051.3.3.2子查詢(xún)NOTEXISTS確實(shí)為我們帶來(lái)了一些新的功能。notexists能被用來(lái)實(shí)現(xiàn)關(guān)系代數(shù)的MINUS運(yùn)算。如果R和S是兩個(gè)兼容表(屬性相同A1…An),R-S用SQL計(jì)算:selectA1…AnfromRwherenotexists(select*fromSwhereS.A1=R.A1and………andS.An=R.An);

例:找出沒(méi)有通過(guò)代理商a03訂貨的顧客cid.關(guān)系代數(shù)：πcid(O)—πcid(σaid=’a03’(O))

selectdistinctcidfromordersxwherenotexists(select*fromorderswherecid=x.cidandaid=‘a(chǎn)03’);差運(yùn)算郭文明2003.06.051.3.3.3UNION運(yùn)算和FORALL條件為了提供關(guān)系代數(shù)的∪運(yùn)算，SQL使用UNION：subqueryUNION[ALL]subquery例：包含了顧客所在的或代理商所在或兩者皆在的城市名單。

selectcityfromcustomersunionselectcityfromagents;或selectcityfromcustomersunionallselectcityfromagents;行不重復(fù)行重復(fù)∪運(yùn)算郭文明2003.06.051.3.3.3UNION運(yùn)算和FORALL條件SQL中沒(méi)有等價(jià)的÷運(yùn)算。如果面臨的查詢(xún)“要求被檢索的對(duì)象集合必須符合‘所有’這類(lèi)關(guān)鍵詞的條件”(FORALL)時(shí)，關(guān)系代數(shù)要用到除運(yùn)算。SQL中可以：1.表述要檢索的候選對(duì)象的一個(gè)反例(至少一個(gè)對(duì)象不符合條件)，.并建立select語(yǔ)句(選出所有反例)；2.建立表示這類(lèi)反例不存在的條件(notexists)；3.建立最終select。例：求通過(guò)住在北京的所有代理商訂了貨的顧客cid.1.反例：住在北京但沒(méi)有為所求顧客c.cid訂貨的代理商：

select*fromagentswherea.city=‘北京’andnotexists(select*fromordersxwherex.cid=c.cidandx.aid=a.aid)2.反例不存在：notexists(反例)3.最終：selectc.cidfromcustomerswherenotexists(select*fromagentswherea.city=‘北京’andnotexists(select*fromordersxwherex.cid=c.cidandx.aid=a.aid));÷運(yùn)算郭文明2003.06.051.3.3.3UNION運(yùn)算和FORALL條件例：找出訂購(gòu)了所有被顧客c006訂購(gòu)的商品的顧客的cid.反例：被c006訂購(gòu)但沒(méi)有被c.cid訂購(gòu)的商品：selectp.pidfromproductspwherep.pidin(selectpidfromordersxwherex.cid=‘c006’)andnotexists(select*fromordersywherey.pid=p.pidandy.cid=c.cid)

反例不存在：notexists(selectp.pidfromproductspwherep.pidin(selectpidfromordersxwherex.cid=‘c006’)andnotexists(select*fromordersywherey.pid=p.pidandy.cid=c.cid))

最終：selectcidfromcustomerswherenotexists(selectp.pidfromproductspwherep.pidin(selectpidfromordersxwherex.cid=‘c006’)andnotexists(select*fromordersywherey.pid=p.pidandy.cid=c.cid));÷運(yùn)算被c006訂購(gòu)沒(méi)有被c.cid訂購(gòu)郭文明2003.06.051.3.3.4高級(jí)SQL語(yǔ)法以下介紹的高級(jí)SQL運(yùn)算符不是EntrySQL-92的部分,但幾乎都屬于SQL-99,目前產(chǎn)品中不一定支持，但可能出現(xiàn)在未來(lái)的數(shù)據(jù)庫(kù)產(chǎn)品中。1)INTERSECT(∩)和EXCEPT(—)運(yùn)算符

Subquery[UNION[ALL]|INTERSECT[ALL]|EXCEPT[ALL]Subquery]注：CoreSQL-99只有EXCEPT而沒(méi)有EXCEPTALL。兩個(gè)子查詢(xún)從左到右的列類(lèi)型是兼容的,即可以并、交、差。如類(lèi)型char(5)和類(lèi)型char(10)的列運(yùn)算,結(jié)果為char(10).ORACLE提供UNION、UNIONALL、INTERSECT、MINUS(EXCEPT)。但不支持INTERSECTALL或MINUSALL。DB2UDB都支持。ALL考慮重復(fù)行及數(shù)目郭文明2003.06.051.3.3.4高級(jí)SQL語(yǔ)法例:(QUNIONALLQUNIONALLQ)INTERSECTALL(QUNIONALLQ)結(jié)果是(QUNIONALLQ),包含兩個(gè)重復(fù)行。(QUNIONALLQUNIONALLQ)INTERSECT(QUNIONALLQ)結(jié)果是(Q),沒(méi)有包含重復(fù)行。(QUNIONALLQUNIONALLQ)EXCEPTALL(QUNIONALLQ)結(jié)果是(Q),沒(méi)有包含重復(fù)行。郭文明2003.06.051.3.3.4高級(jí)SQL語(yǔ)法2)連接形式

通用形式：FROMtablename[[AS]corr_name[,……]……]

SQL-99：FROM后面還可以是：一般形式：

tablename[[AS]corr_name[(colname[,colname……])]]子查詢(xún)：

(subquery)[AS]corr_name[(colname[,colname……])]

顯式連接：table1[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOINtable2ONcondition

顯然SQL-99中允許子查詢(xún)出現(xiàn)在FROM子句中。以上三種形式在產(chǎn)品中并沒(méi)有完全實(shí)現(xiàn)。使用時(shí)可以試用或幫助。可以是視圖名ORACLE不能有AS允許為表的列重新命名允許是子查詢(xún),此時(shí)別名必須內(nèi)連,左連,右連,外連連接條件郭文明2003.06.051.3.3.4高級(jí)SQL語(yǔ)法例：檢索至少訂購(gòu)了一件價(jià)格低于0.5的商品的顧客姓名。

selectdistinctcnamefrom(ordersojoinproductspono.pid=p.pid)joincustomerscono.cid=c.cidwherep.price<0.5;注:ORACLE僅提供左連和右連,而且語(yǔ)法與標(biāo)準(zhǔn)SQL也不同:SELECT…FROMT1,T2WHERE[T1.c1[(+)]=T2.c2|T1.c1=T2.c2[(+)]]ANDconditionT2保留所有行與T1連不上的用NULL郭文明2003.06.051.3.3.5SQL中的集合函數(shù)SQL中稱(chēng)集合函數(shù)(setfunction),ORACLE稱(chēng)組函數(shù)(groupfunction),DB2UDB稱(chēng)列函數(shù)(columnfunction),INFORMIX稱(chēng)聚集函數(shù)(aggregatefunction).集合函數(shù)語(yǔ)法:SET_FUNCTION([ALL|DISTINCT]col)|COUNT(*)注:ALL時(shí)包括重復(fù)行，DISTINCT不包括重復(fù)行。WHERE子句比較操作中不能使用集合函數(shù).如discnt<max(…)集合函數(shù)忽略了所有空值.集合函數(shù)不允許嵌套使用.如AVG(selectMAX(dollars)……)名稱(chēng)參數(shù)類(lèi)型結(jié)果類(lèi)型描述COUNT任意(*)數(shù)值出現(xiàn)次數(shù)SUM數(shù)值數(shù)值參數(shù)和AVG數(shù)值數(shù)值參數(shù)均值MAX字符,數(shù)值字符,數(shù)值最大值MIN字符,數(shù)值字符,數(shù)值最小值郭文明2003.06.051.3.3.6SQL中行的分組SQL報(bào)表功能:根據(jù)某些列值的共性把一個(gè)表所包含的全部行分成若干個(gè)子集,然后對(duì)每個(gè)子集執(zhí)行集合函數(shù).

例:打印每個(gè)代理商為顧客c002和c003訂購(gòu)產(chǎn)品及產(chǎn)品總數(shù)量.selecta.aid,aname,p.pid,pname,sum(qty)fromordersx,productsp,agentsawherex.pid=p.pidandx.aid=a.aidandx.cidin(‘c002’,’c003’)

groupbya.aid,a.aname,p.pid,p.pname;GROUPBY對(duì)象的列上的空值會(huì)被分在同一組里.郭文明2003.06.051.3.3.6SQL中行的分組如果要去掉分組后的某些行,不能用where,只能用HAVING子句.

例:求被至少兩個(gè)顧客訂購(gòu)的產(chǎn)品pid.selectpidfromordersgroupbypidhavingcount(distinctcid)>=2;如果沒(méi)有GROUPBY只有HAVING,則整個(gè)結(jié)果為一組.基本SQLselect語(yǔ)句的通用形式不允許集合函數(shù)的嵌套.但可以有變通的GROUPBY形式.例:求所有代理商的最大銷(xiāo)售額的平均值.郭文明2003.06.051.3.3.6SQL中行的分組例:求所有代理商的最大銷(xiāo)售額的平均值.錯(cuò):selectavg(selectmax(dollars)fromordersgroupbyaid);對(duì):SQL高級(jí)形式,ORACLE中可行:

selectavg(t.x)from(selectaid,max(dollars)asxfromordersgroupbyaid)t;常用作法:creatviewtas(selectaid,max(dollars)asxfromordersgroupbyaid);selectavg(t.x)fromt;郭文明2003.06.051.3.4SQL數(shù)據(jù)更新SQL中數(shù)據(jù)更新包括插入數(shù)據(jù)(Insert)、修改數(shù)據(jù)(Update)和刪除數(shù)據(jù)(Delete)三條語(yǔ)句.

1.3.4.1插入數(shù)據(jù)

INSERTINT