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

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

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

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

1.3.3.2子查詢1.3.3.3UNION運算和FORALL條件1.3.3.4高級SQL語法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特點1)綜合統(tǒng)一

2)高度非過程化

3)面向集合的操作方式

4)以同一種語法結構提供兩種使用方式5)語言簡捷，易學易用

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

CREATESCHEMA<模式名>AUTHORIZATION<用戶名>

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

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

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

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

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

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

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

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

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

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

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

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

視圖刪除后視圖的定義將從數(shù)據(jù)字典中刪除。但是由該視圖導出的其他視圖定義仍在數(shù)據(jù)字典中，不過該視圖已失效。用戶使用時會出錯，要用DROPVIEW語句將它們一一刪除。DBMS執(zhí)行CREATEVIEW語句的結果只把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT。在關系數(shù)據(jù)庫中，并不是所有的視圖都是可更新的,因為有些視圖的更新不能的有意義的轉換成對基本表的更新。行列子集視圖是可更新的。各個DBMS對視圖的更新有自己的規(guī)定。郭文明2003.06.051.3.3數(shù)據(jù)查詢數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作。SQL語言的數(shù)據(jù)查詢只有一條SELECT語句:

SELECT[ALL|DISTINCT]{*|<目標列表達式[[as]別名]>[，<目標列表達式[[as]別名]>]...}

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

數(shù)值表達式由常數(shù)、表屬性、算術運算符、算術函數(shù)所組成。字符串表達式由常數(shù)、表屬性、字符串運算符、字符串函數(shù)所組成。日期表達式由常數(shù)、表屬性、日期運算符、日期函數(shù)所組成WHERE中使用謂詞來表示條件。一般情況謂詞運算結果為TRUE或FALSE,但如果遇到空值時,可能為UNKNOWN。SQL查詢的一個爭議點即：對于同一個查詢會存在眾多不同的構造方法。郭文明2003.06.051.3.3ORACLE、INFORMIX、DB2UDB

中的一些數(shù)學函數(shù)名稱描述結果abs(n)mod(n,b)sqrt(n)

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

另外，三角函數(shù)、指數(shù)函數(shù)、對數(shù)函數(shù)、冪函數(shù)和round(n)數(shù)值類型整數(shù)浮點郭文明2003.06.051.3.3一些標準的和特定產(chǎn)品的串處理函數(shù)SQL-99中的描述ORACLEDB2UDBINFORMIX返回串長度(整數(shù)個字符)CHAR_LENGTH(str)返回子串,從m個開始取n個SUBSTRING（str）FORMmFOR(n)返回去掉左或右空格后得到的包含空格的串TRIM（[[LEADING|TRAILING|BOTH][SET]FROM]str）返回子串str2在str1中位置，如果指定n,則從n開始POSITION（str1INstr2）字母小寫LOWER（str）字母大寫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中的標準謂詞謂詞

形式

例子比較謂詞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簡單查詢例：檢索定貨記錄中所有pid值：selectpidfromorders--結果中有重復的pidselectdistinctpidfromorders--結果中pid唯一select缺省為all,distinct沒有出現(xiàn)時允許重復行，缺省情況不遵守行唯一性規(guī)則。例：在orders表上生成每筆業(yè)務的利潤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簡單查詢注：1）SQL-99規(guī)定FROM子句中執(zhí)行連接運算，但大多數(shù)產(chǎn)品通過笛卡爾積運算并且在WHERE子句中包含表示參與連接的列值相等的條件來模擬連接運算，具體實現(xiàn)方法(執(zhí)行計劃，查詢優(yōu)化)各不相同。2）FROM中AS被省略，SQL仍能識別表別名。ORACLE和INFORMIX使用別名或表別名，DB2UDB使用相關名。

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

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

例:求出既訂購了產(chǎn)品p01有訂購了產(chǎn)品p07的顧客cid.關系代數(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的查詢一般能找到等價的其他查詢形式。使用和不使用exists交運算郭文明2003.06.051.3.3.2子查詢NOTEXISTS確實為我們帶來了一些新的功能。notexists能被用來實現(xiàn)關系代數(shù)的MINUS運算。如果R和S是兩個兼容表(屬性相同A1…An),R-S用SQL計算:selectA1…AnfromRwherenotexists(select*fromSwhereS.A1=R.A1and………andS.An=R.An);

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

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

selectcityfromcustomersunionselectcityfromagents;或selectcityfromcustomersunionallselectcityfromagents;行不重復行重復∪運算郭文明2003.06.051.3.3.3UNION運算和FORALL條件SQL中沒有等價的÷運算。如果面臨的查詢“要求被檢索的對象集合必須符合‘所有’這類關鍵詞的條件”(FORALL)時，關系代數(shù)要用到除運算。SQL中可以：1.表述要檢索的候選對象的一個反例(至少一個對象不符合條件)，.并建立select語句(選出所有反例)；2.建立表示這類反例不存在的條件(notexists)；3.建立最終select。例：求通過住在北京的所有代理商訂了貨的顧客cid.1.反例：住在北京但沒有為所求顧客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));÷運算郭文明2003.06.051.3.3.3UNION運算和FORALL條件例：找出訂購了所有被顧客c006訂購的商品的顧客的cid.反例：被c006訂購但沒有被c.cid訂購的商品：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));÷運算被c006訂購沒有被c.cid訂購郭文明2003.06.051.3.3.4高級SQL語法以下介紹的高級SQL運算符不是EntrySQL-92的部分,但幾乎都屬于SQL-99,目前產(chǎn)品中不一定支持，但可能出現(xiàn)在未來的數(shù)據(jù)庫產(chǎn)品中。1)INTERSECT(∩)和EXCEPT(—)運算符

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

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

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

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

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

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

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

selectdistinctcnamefrom(ordersojoinproductspono.pid=p.pid)joincustomerscono.cid=c.cidwherep.price<0.5;注:ORACLE僅提供左連和右連,而且語法與標準SQL也不同:SELECT…FROMT1,T2WHERE[T1.c1[(+)]=T2.c2|T1.c1=T2.c2[(+)]]ANDconditionT2保留所有行與T1連不上的用NULL郭文明2003.06.051.3.3.5SQL中的集合函數(shù)SQL中稱集合函數(shù)(setfunction),ORACLE稱組函數(shù)(groupfunction),DB2UDB稱列函數(shù)(columnfunction),INFORMIX稱聚集函數(shù)(aggregatefunction).集合函數(shù)語法:SET_FUNCTION([ALL|DISTINCT]col)|COUNT(*)注:ALL時包括重復行，DISTINCT不包括重復行。WHERE子句比較操作中不能使用集合函數(shù).如discnt<max(…)集合函數(shù)忽略了所有空值.集合函數(shù)不允許嵌套使用.如AVG(selectMAX(dollars)……)名稱參數(shù)類型結果類型描述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報表功能:根據(jù)某些列值的共性把一個表所包含的全部行分成若干個子集,然后對每個子集執(zhí)行集合函數(shù).

例:打印每個代理商為顧客c002和c003訂購產(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對象的列上的空值會被分在同一組里.郭文明2003.06.051.3.3.6SQL中行的分組如果要去掉分組后的某些行,不能用where,只能用HAVING子句.

例:求被至少兩個顧客訂購的產(chǎn)品pid.selectpidfromordersgroupbypidhavingcount(distinctcid)>=2;如果沒有GROUPBY只有HAVING,則整個結果為一組.基本SQLselect語句的通用形式不允許集合函數(shù)的嵌套.但可以有變通的GROUPBY形式.例:求所有代理商的最大銷售額的平均值.郭文明2003.06.051.3.3.6SQL中行的分組例:求所有代理商的最大銷售額的平均值.錯:selectavg(selectmax(dollars)fromordersgroupbyaid);對:SQL高級形式,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)三條語句.

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

INSERTINT