數據庫應用技術第三章深入SQLSQL的特點面向集合的操作方式，是關系代數的實現。高度非過程化，只需要提出“What”，不需要指出“How”。上下文無關，運行結果取決于數據。語言風格統(tǒng)一，類自然語言，簡單易學。既是自含式語言，又是嵌入式語言。3.1SQL概述SQL的歷史上世紀70年代早期：IBM公司，為SystemR使用的SEQUEL（StructuredEnglishQueryLanguage）是最早的SQL版本。1979：RelationalSoftwareInc.（現Oracle公司）實現了第一個SQL商業(yè)版本。SQL的標準化從SQL-86(ANSI)到SQL-2008（ANSI和ISO）1996年，NIST開始對SQLDBMS符合SQL標準的程度進行認證。各商業(yè)DBMS逐漸向標準靠攏。現在：SQL是RDBMS的標準語言。SQL的設計初衷是給高級用戶提供一種通用的、易于學習和理解的數據庫操作方式。演變?yōu)榻o程序員使用的數據庫標準接口。SQL的分類查詢(DataQueryLanguage,DQL)SELECT操縱(DataManipulateLanguage,DML)INSERT,DELETE,UPDATE定義(DataDefinitionLanguage,DDL)CREATE,DROP,ALTER控制(DataControlLanguage,DCL)GRANT,REVOKE,COMMIT,ROLLBACKSQL的基本語法SQL語句不區(qū)分大小寫，可以寫在多行，各個單詞之間使用分隔符（空格、回車、制表符）分隔。SQL語句中的關鍵字屬于保留字，不能用于其他地方。數字常量的寫法和通常程序語言一致，字符串使用單引號’包含，字符串中的單引號使用兩個連續(xù)的單引號’’轉義表示，不區(qū)分字符和字符串。SQL的書寫規(guī)范為便于書寫和閱讀，通常采用如下的書寫規(guī)范：關鍵字大寫，其他標識符小寫每個子句單起一行使用制表符縮進標準與可移植性在SQL92標準中定義了四種級別：Entry,Transitional,Intermediate,andFull國際標準與各個廠商之間的不兼容性事實上的不可移植性字符串連接||與+專有的查詢關鍵字數據類型與函數特殊語義可移植性神話3.2簡單的SELECT語句3.2.1基本語句語法 SELECT[DISTINCT|UNIQUE|ALL]*|<查詢項列表

> FROM<表名>;說明查詢項：<字段>|<表達式>[[AS]<別名>]無條件：查詢出表中所有記錄。記錄的返回次序不確定、不穩(wěn)定，依賴于數據庫的實現。*：按某個“特定”順序列舉所有字段。Oracle按照表或者視圖創(chuàng)建時的順序返回。3.2.1基本語句當明確要查詢哪些字段的時候，將字段名稱列在SELECT的后面，這樣語句含義清楚，性能也有所提高。[AS]<別名>：別名起到簡化作用，通常還作為表達式的標題。特別是很多使用SQL的開發(fā)工具直接使用SELECT語句中每個查詢表達式的名稱作為標識符，這種情況下更需要給出別名。表達式與別名之間的AS是一個冗余的關鍵字，可以省略，直接以空白符分隔。3.2.1基本語句DISTINCT|UNIQUE|ALL：DISTINCT與UNIQUE含義相同。在整個查詢內容前加上DISTINCT，返回結果中不重復的內容。不重復針對所有要查詢的表達式集合而言。ALL是缺省值，列出包括重復的所有記錄。另外，DISTINCT是一個比較費時間的操作，使用時需慎重。查詢的內容可以是字段，也可以是由字段、常數通過運算符、函數構成的復雜表達式。這種計算要對表上的所有行都施加運算。例：SELECTsid,upper(sname),salary+

100FROMsalers;E13.2.1基本語句在某些情況下，也可能使用SQL計算純粹常量表達式的值，如檢索服務器當前時間。例：

SELECTsysdate,1+2

FROMsalers;依賴于表中記錄的數目，將得到若干行（也可能是0行）同樣的結果。而這一般不是我們想要得到的結果。E23.2.1基本語句只想得到一行結果在MS-SQL和MySQL中，可以使用不帶FROM子句的SELECT語句； SELECT1+2在Oracle中，不允許沒有FROM子句。系統(tǒng)提供了一個名叫dual的表，保證其中只有一條記錄，不依賴于表的計算可以使用這個表。

SELECTsysdate,1+2 FROMdual;E33.2.2篩選-WHEREWHERE通過邏輯表達式篩選出滿足某些條件的記錄。基本比較運算符=、>、<、>=、<=、<>其他相當于比較運算符的結構BETWEEN..AND..、IN、LIKE、NULL的運算邏輯運算符NOT、AND、ORBETWEEN..AND..檢查表達式的值是否在某個區(qū)間內例：列出工資在4000和5000之間的銷售員的編號和工資。E4BETWEEN..AND..檢查表達式的值是否在某個區(qū)間內例：列出工資在4000和5000之間的銷售員的編號和工資。

SELECTsid,salary

FROMsalers WHEREsalaryBETWEEN4000AND5000;閉區(qū)間，等于兩端值的記錄也會被查出來。一般要求區(qū)間前面的值小于后面的值，否則將查不出結果。E4IN檢查表達式的值是否包含在某個值的集合內例：查詢出名字叫Tom、Jack或者Mary的銷售員的編號和工資。E5IN檢查表達式的值是否包含在某個值的集合內例：查詢出名字叫Tom、Jack或者Mary的銷售員的編號和工資。SELECTsid,salaryFROMsalersWHEREsname=‘Tom’ORsname=‘Jack’ORsname=‘Mary’;

SELECTsid,salary

FROMsalers WHEREsnameIN(‘Tom’,‘Jack’,‘Mary’);E5LIKE對字符串進行通配查找%：匹配0個或任意多個字符_：匹配一個字符例：查詢出所有名字以S開頭的銷售員的編號、名字和工資信息。例如，Smith、Sa和S等。E6LIKE對字符串進行通配查找%：匹配0個或任意多個字符_：匹配一個字符例：查詢出所有名字以S開頭的銷售員的編號、名字和工資信息。例如，Smith、Sa和S等。

SELECTsid,sname,salary FROMsalers WHEREsnameLIKE‘S%’;E6LIKE例：查詢出來所有名字中含有第一個是A，第三個是M，中間可以是任意字符的子串的顧客信息。E7LIKE例：查詢出來所有名字中含有第一個是A，第三個是M，中間可以是任意字符的子串的顧客信息。

SELECT* FROMcustomers WHEREcnameLIKE‘%A_M%’;E7LIKE若待查詢表達式中含有%或者_，需要進行轉義描述。例：查詢名字以%開頭，以_結尾的銷售員編號、姓名和工資。E8LIKE若待查詢表達式中含有%或者_，需要進行轉義描述。例：查詢名字以%開頭，以_結尾的銷售員編號、姓名和工資。

SELECTsid,sname,salary

FROMsalers WHEREsnameLIKE‘\%%\_’ESCAPE‘\’;ESCAPE關鍵字指明使用的轉義字符（上例為\），轉義字符后面的直接字符不按照通配符對待。E8SELECT*FROMsalers WHEREsalary>1000ORsalary<=1000;是否能查出所有記錄？E9ISNULL/ISNOTNULL空值NULL用來表示未知的(Unknown)，不適用的(Inapplicable)。不能直接和空值進行比較，需要使用ISNULL和ISNOTNULL。SELECT*FROMsalers WHEREsalary>1000ORsalary<=1000;

等價于：例：查詢工資非空的銷售員信息。

SELECT*FROMsalers WHEREsalaryISNOTNULL;E9E10NVL函數

E11NVL函數例：欲發(fā)放獎金，為薪水的10%，如果薪水為空，按照1000計算。列出每位銷售員應發(fā)放的獎金。

SELECTsid,nvl(salary,1000)*0.1

FROMsalers;E12邏輯運算可使用邏輯運算符AND、OR和NOT將多個關系表達式連接在一起，構成復雜的邏輯表達式。優(yōu)先級從高到低為NOT、AND和OR。3.2.3聚集函數與分組統(tǒng)計聚集函數：多條記錄統(tǒng)計出一個結果。COUNT、MAX、MIN、AVG和SUM等聚集函數和NULL的關系COUNT(*)：對符合條件的所有記錄計數，不考慮是否為空。COUNT(字段)：該字段為空的行不計數在內。MAX、MIN：NULL不參與比較。AVG、SUM：NULL不參與運算。沒有GROUPBY子句，帶聚集函數的查詢一定只返回一行數據，但是可能是空值。如果表中無數據，或者無滿足條件數據，或者所有符合條件數據都是空，COUNT返回0，其他函數則返回空值。聚集函數里面可以使用DISTINCT，只對不重復的字段進行統(tǒng)計。也可以使用ALL，為缺省值。SUM、AVG只用于數值型。MAX、MIN可用于數值、字符和日期型。例：SELECTcount(*)…返回記錄數SELECTcount(字段)…字段為空的記錄不參與計數SELECTcount(DISTINCT字段)…重復字段不參與計數SELECTmax(sname),avg(salary),sum(salary),avg(salary)*count(salary)FROMsalersE13GROUPBY分組子句按照GROUPBY條件對記錄進行分組，然后進行統(tǒng)計。每一個分組得出一個結果。可以使用多個條件。使用GROUPBY子句時對查詢字段（不包括條件字段）使用要求在SELECT子句中直接出現的字段（包括非聚集函數內的）必須是在GROUPBY中列出的字段，否則，必須放在聚集函數之內。如使用了聚集函數，未使用GROUPBY子句，則所有字段均不能直接出現在SELECT子句中。HAVING分組篩選子句對分組后的每個組按照條件進行篩選只能使用聚集函數（對分組后數據的統(tǒng)計值進行篩選）和分組字段（通常無意義）只能在GROUPBY后面出現例：當前有多少訂單？E14例：當前有多少訂單？ SELECTcount(*) FROMorders;E14例：當前有多少訂單？ SELECTcount(*) FROMorders;例：當前l(fā)ocations內有多少個郵編？不重復數有多少？E14E15例：當前有多少訂單？ SELECTcount(*) FROMorders;例：當前l(fā)ocations內有多少個郵編？不重復數有多少？

SELECTcount(postcode),count(DISTINCTpostcode) FROMlocations;E14E15例：在locations中有多少個不重復的城市？E16例：在locations中有多少個不重復的城市？ SELECTcount(DISTINCTcity) FROMlocations;E16例：在locations中有多少個不重復的城市？ SELECTcount(DISTINCTcity) FROMlocations;例：列出每位顧客的編號、購買次數和總購買額。E16E17例：在locations中有多少個不重復的城市？ SELECTcount(DISTINCTcity) FROMlocations;例：列出每位顧客的編號、購買次數和總購買額。 SELECTcid,count(dollars),sum(dollars) FROMorders GROUPBYcid;E16E17例：在一周之前，有多少個顧客購買過產品？E18例：在一周之前，有多少個顧客購買過產品？ SELECTcount(DISTINCTcid) FROMorders WHEREbuy_date<sysdate–7;E18例：在一周之前，有多少個顧客購買過產品？ SELECTcount(DISTINCTcid) FROMorders WHEREbuy_date<sysdate–7;例：列出最后一次商品出入庫的時間。E18E19例：在一周之前，有多少個顧客購買過產品？ SELECTcount(DISTINCTcid) FROMorders WHEREbuy_date<sysdate–7;例：列出最后一次商品出入庫的時間。SELECTmax(date_happen)FROMstock_in_out;E18E19例：列出共有多少種產品已經被銷售過。E20例：列出共有多少種產品已經被銷售過。SELECTcount(DISTINCTpid)FROMorders;E20例：列出共有多少種產品已經被銷售過。SELECTcount(DISTINCTpid)FROMorders;例：根據出入庫記錄，計算庫存數。E20E21例：列出共有多少種產品已經被銷售過。SELECTcount(DISTINCTpid)FROMorders;例：根據出入庫記錄，計算庫存數。SELECTsum(qty_in_out)FROMstock_in_out;E20E21例：列出每位顧客的編號、姓名和總購買額和購買次數。E22例：列出每位顧客的編號、姓名和總購買額和購買次數。

SELECTc.cid,ame,sum(o.dollars),count(o.dollars) FROMorderso,customersc WHEREo.cid(+)=c.cid GROUPBYc.cid,ame;cname雖然冗余，但必須出現在GROUPBY后面，除非使用其他方法。E22例：對銷售量不小于1000的訂單進行統(tǒng)計，計算每種產品的平均銷售額。E23例：對銷售量不小于1000的訂單進行統(tǒng)計，計算每種產品的平均銷售額。

SELECTpid,avg(dollars) FROMorders WHEREqty>=1000 GROUPBYpid;

先執(zhí)行WHERE，后執(zhí)行GROUPBY。E23例：找出平均銷售量大于500的商品，列出商品編號和總銷售額。E24例：找出平均銷售量大于500的商品，列出商品編號和總銷售額。 SELECTpid,sum(dollars) FROMorders GROUPBYpid HAVINGavg(qty)>500;

分組之后計算HAVING條件。E24例：找出至少購買過兩筆超過1000元的顧客，列出顧客編號和總購買額。E25例：找出至少購買過兩筆超過1000元訂單的顧客，列出顧客編號和總購買額。

SELECTcid,sum(dollars) FROMorders WHEREdollars>1000 GROUPBYcid HAVINGcount(*)>1;E25例：按商品分類后，總銷售額的最高值是多少？E26例：按商品分類后，總銷售額的最高值是多少？

SELECTmax(sum(dollars)) FROMorders GROUPBYpid;E26小結返回數據無GROUPBY子句，帶聚集函數的查詢一定只返回一行數據，但是可能是空值。如果表中無數據，或者無滿足條件數據，或者所有符合條件數據都是空，COUNT返回0，其他函數則返回空值。帶有聚集函數的SQL對查詢表達式的要求未使用GROUPBY，但出現了一個聚集函數，其他表達式也必須是聚集函數。使用GROUPBY，查詢表達式必須是使用聚集函數的表達式分組字段直接參與的表達式其他字段直接出現，為語法錯誤HAVING子句的要求同查詢表達式3.2.4排序ORDERBY<排序準則>{,<排序準則>} <排序準則>：<字段>

|<表達式>

|<別名>

|<序號>[ASC|DESC][NULLSFIRST|NULLSLAST]ASC|DESC：升/降序，升序為缺省值。<字段>：不一定在SELECT中列出。<表達式>：有些DBMS不支持。<序號>：從1開始排。在多個SELECT進行集合運算時，適合使用序號方式。NULLSFIRST|NULLSLAST：NULL通常被排在一起，ASC時缺省為LAST，DESC時缺省為FIRST。例：按工資降序列出銷售員的所有信息，工資為空的銷售員信息放在最后。E27例：按工資降序列出銷售員的所有信息，工資為空的銷售員信息放在最后。

SELECT* FROMsalers ORDERBYsalaryDESCNULLSLAST;E27SELECT語句執(zhí)行過程如何理解SELECT語句的執(zhí)行過程？⑤SELECT ①FROM ②[WHERE] ③[GROUPBY ④[HAVING]] ⑥[ORDERBY];3.3復雜的SELECT語句開發(fā)數據庫軟件時如果可能，盡量用一條SQL語句完成工作。如果無法用一條SQL語句完成，就通過PL/SQL實現（不過，盡可能少用PL/SQL）。如果在PL/SQL中也無法做到（因為它缺少一些特性，如列出目錄中的文件），可以試試使用Java存儲過程來實現。Oracle9i及以后版本中，需要這樣做的可能性極小。如果用Java辦不到，那就在C外部過程中實現。如果速度要求很高，或者要使用采用C編寫的一個第三方API，就常常使用這種做法。如果在C外部例程中還無法實現，就該好好想想有沒有必要做這個工作了。3.3.1連接查詢連接查詢：Join，對多表數據的笛卡爾積進行查詢。連接條件：笛卡爾積操作后，根據多個表之間相關聯的某些字段間的判斷條件進行篩選。根據連接條件的不同，可將連接分為：等值連接(Equaljoin)不等值連接(Non-equaljoin)自連接(Selfjoin)外連接(Outerjoin)例：找出所有顧客曾購買過的商品名稱和購買數量。分析：

購買—orders

商品名稱—products

購買數量—orders

Selectnamesandquantitiesofproductsboughtbyallcustomers.E28例：找出顧客們曾購買過的商品名稱和購買數量。

SELECTproducts.pname,orders.qty FROMproducts,orders WHEREproducts.pid=orders.pid;

或簡寫為：

SELECTp.pname,o.qty FROMproductsp,orderso WHEREp.pid=o.pid;E28例：找出住在Dallas或Duluth的顧客購買過商品的編號。分析：

購買—orders

顧客居住地—customersSelectidsofproductsandtheirbuyersliveinDallasorDuluth.SelectidsofproductswhichisorderedbythecustomersliveinDallasorDuluth.E29例：找出住在Dallas或Duluth的顧客購買過商品的編號。 SELECTo.pid FROMorderso,customersc WHEREo.cid=c.cidAND( c.city=‘Dallas’ORc.city=‘Duluth’);E29例：列出每個銷售員及其上級銷售員的姓名。

Selectthenamesofsalersandthenamesoftheirmanagers.比較直觀的想法：Selects1.snameas‘銷售員’,s2.snameas‘上級’Fromsalerss1,salerss2Wheres1.manager=s2.sid結果？E30例：列出每個銷售員及其上級銷售員的姓名。

SELECTs1.snameas‘銷售員’,s2.snameas‘上級’

FROMsalerss1,salerss2

WHEREs2.sid(+)=s1.manager;自連接：同一個表不同名，重用，并未真正復制數據，必須使用表的別名，邏輯復制。外連接：對于右表上的記錄，若與左表中任何記錄都未成功匹配，在結果中保留該記錄，對應的左表各自段置為NULL。左連接和右連接是等價的，Oracle中只使用左連接。E30例：成對地列出在同一城市居住的顧客的編號。E31例：成對地列出在同一城市居住的顧客的編號。

SELECTc1.cid,c2.cid FROMcustomersc1,customersc2 WHEREc1.city=c2.city;重復的信息只保留一份，如(a,b)和(b,a)；同一顧客自身的配對信息也不列出(a,a)。

WHEREc1.city=c2.cityANDc1.cid<c2.cid;E31例：列出所有工資高于Brown的銷售員的姓名。

SELECTs2.sname FROMsalerss1,salerss2 WHEREs1.sname=‘Brown’AND s2.salary>s1.salary;問題：有多名叫作Brown的銷售員？E32例：列出曾經購買過商品的顧客的姓名和所購買的商品的名稱。分析：購買—orders

商品名稱—products

顧客姓名—customers

E33例：列出曾經購買過商品的顧客的姓名和所購買的商品的名稱。

SELECTame,p.pname FROMcustomersc,productsp,orderso WHEREo.cid=c.cidANDo.pid=p.pid;一般來說，N個表連接時需要N-1個連接條件。E33例：列出曾購買過單價超過1元的商品的顧客的姓名。分析：購買—orders

商品單價—products

顧客姓名—customersSelectnamesofcustomersandtheyboughtproductswhichcostmorethan1yuan.E34例：列出曾購買過單價超過1元的商品的顧客的姓名。

SELECTame FROMorderso,customersc,productsp WHEREo.cid=c.cidANDo.pid=p.pidAND p.price>1;E34例：找出與其經理在同一城市的銷售員的姓名。分析：銷售員姓名—salers

銷售員的經理—salers（manager）

銷售員城市—locations

經理城市—locations（manager）Selectnamesofsalersandtheyworkinthesamecitieswiththeirmanagers.E35例：找出與其經理在同一城市的銷售員的姓名。

SELECTs1.sname FROMsalerss1,salerss2,

locationss1_loc,locationss2_loc WHEREs1.manager=s2.sid

ANDs1.lid=s1_loc.lid

ANDs2.lid=s2_loc.lid

ANDs1_loc.city=s2_loc.city;E353.3.2子查詢出現在另外一個SQL語句中的查詢。里面出現的SQL語句也被稱為子查詢(subquery)或內查詢(innerquery)。外面的查詢被稱為外查詢(outerquery)。位置一般在WHERE子句中使用，子查詢內不可有ORDERBY子句。按照不同的擴展語法，也可能在SELECT、HAVING和FROM子句中使用。在WHERE子句使用子查詢，標準的語法有：1、<字段表達式>比較運算符<子查詢>2、<字段表達式>比較運算符量詞<子查詢>3、<字段表達式>[NOT]IN<子查詢>4、[NOT]EXISTS<子查詢>1、比較運算符如果子查詢返回一個數據，則可以直接作為數值參與普通比較運算，此時，要求子查詢出現在比較運算符的右端。例：查詢與編號為S002的銷售員工資相同的其他人的所有信息。SelecttheinformationofsalerswhohasthesamesalarywithS002.SelecttheinformationofsalersandthosesalershavethesamesalarywithS002.E36例：查詢與編號為S002的銷售員工資相同的其他人的所有信息。SELECT*FROMsalersWHEREsalary=( SELECTsalary FROMsalers WHEREsid=‘S002’);SELECTs2.*FROMsalerss1,salerss2WHEREs1.sid=‘S002’ANDs1.salary=s2.salary;E36例：查詢比Jack工資高的銷售員信息。SelecttheinformationofsalerswhohavehighersalarythenJack.Selecttheinformationofsalerswhohavehighersalarythanonesalerandthissaler’snameisJack.E37例：查詢比Jack工資高的銷售員信息。

SELECT* FROMsalers WHEREsalary>( SELECTsalary FROMsalers WHEREsname=‘Jack’

);需要保證只有一個叫Jack的人！SELECTs2.*FROMsalerss1,salerss2WHEREs1.sname=‘Jack’ANDs2.salary>s1.salary;E37例：查詢工資高于平均工資的銷售員信息。Listtheinformationofsalerswhosesalaryishigherthantheaverage.E38例：查詢工資高于平均工資的銷售員信息。

SELECT* FROMsalers WHEREsalary>( SELECTAVG(salary) FROMsalers

);E38例：查詢單筆銷售額第二高的銷售額。E39例：查詢單筆銷售額第二高的銷售額。第一高：

SELECTMAX(dollars)FROMorders;第二高：小于第一高的最高SELECTMAX(dollars) FROMorders WHEREdollars<( SELECTMAX(dollars) FROMorders

);E39直接使用子查詢時，若返回一個結果，作為數值處理；若無返回數據，作為NULL值處理；若返回多條數據，產生運行時錯誤（非語法錯誤）。用戶需要在邏輯上和數據上保證返回0條或者1條結果，例如使用主鍵或唯一鍵檢索使用聚集函數，但不使用GROUPBY其他邏輯上保證2、量詞如果子查詢返回多行結果，需要使用量詞和關系運算符。ALL：子查詢所返回的所有記錄的值均滿足條件，相當于謂詞邏輯中的全稱量詞。SOME：子查詢所返回的記錄中存在一些記錄的值滿足條件，相當于存在量詞。ANY：與SOME相同，但由于ANY在英語中有歧義（有任意的意思），所以一般不使用。例：找出比工作在編號為L001的地點的所有銷售員工資都低的銷售員信息。找出比（工作在編號為L001的地點的所有銷售員工資）都低的銷售員信息。SELECTsalaryFROMsalersWHERElid=‘L001’;E40例：找出比工作在編號為L001的地點的所有銷售員工資都低的銷售員信息。

SELECT* FROMsalers WHEREsalary<ALL( SELECTsalary FROMsalers WHERElid=‘L001’

);E40例：列出曾經從工作在編號為L001的地點的銷售員處購買過貨物的所有顧客的編號。ListidsofcustomerswhoboughtproductsfromsalersworkingatL001.ListidsofcustomersandthesecustomersboughtsomethingfromsalersandthesesalersworkatL001.分析：

銷售員工作地點編號—salers

購買

—orders

顧客編號—ordersE41例：列出曾經從工作在編號為L001的地點的銷售員處購買過貨物的所有顧客的編號。列出曾經從（工作在編號為L001的地點的銷售員）處購買過貨物的所有顧客的編號。SELECTsidFROMsalersWHERElid=‘L001’;E41例：列出曾經從工作在編號為L001的地點的銷售員處購買過貨物的所有顧客的編號。SELECTcidFROMordersWHEREsid=SOME( SELECTsid FROMsalers WHERElid=‘L001’);SELECTo.cidFROMorderso,salerssWHEREo.sid=s.sidANDs.lid=‘L001’;E41例：查找薪水最高的銷售員，列出其編號和姓名。Listidandnameofsalerswhohasthehighestsalary.E42例：查找薪水最高的銷售員，列出其編號和姓名。SELECTsid,snameFROMsalersWHEREsalary=(

SELECTmax(salary)FROMsalers);或者WHEREsalary>=ALL(

SELECTsalaryFROMsalers);E423、IN除量詞外，還可以使用IN進行比較。IN等價于=SOMENOTIN等價于

<>ALL例：列出所有從未購買過商品的顧客的編號和姓名。Listidandnameofcustomerwhohaveneverbought.Listidandnameofcustomerwhoisnotinthelistofbuyers.E43例：列出所有從未購買過商品的顧客的編號和姓名。SELECTcid,cnameFROMcustomersWHEREcidNOTIN( SELECTcid FROMorders);或者WHEREcid<>ALL(SELECTcid FROMorders);不需要使用DISTINCTE43例：列出薪水最高銷售員的編號和姓名。E44例：列出薪水最高銷售員的編號和姓名。SELECTsid,snameFROMsalersWHEREsalary=( SELECTmax(salary) FROMsalers);E44例：列出工作地點和工資都與Jack相同的銷售員信息。E45例：列出工作地點和工資都與Jack相同的銷售員信息。SELECT*FROMsalersWHERElidIN(

SELECTlidFROMsalersWHEREsname=‘Jack’)

ANDsalaryIN(

SELECTsalaryFROMsalersWHEREsname

=‘Jack’);

問題：如果表中多個叫Jack的銷售員，會出現與（Jack1的lid，Jack2的salary）進行匹配的情況！E45例：列出工作地點和工資都與Jack相同的銷售員信息。可以用lid和salary字段構造一個字符串SELECT*FROMsalersWHERElid||‘@’||salaryIN(SELECTlid||‘@’||salary

FROMsalers

WHEREsname=‘Jack’);E45在Oracle中可以寫為：SELECT*FROMsalersWHERE(lid,salary)IN( SELECTlid,salary FROMsalers WHEREsname=‘Jack’);E45例：列出居住在Duluth的顧客曾在工作于L001地點的銷售員處購買商品的訂單號。ListidoforderswhichisorderedbycustomerswholiveatDuluthandhaveboughtproductfromthesalerswhoworkatL001.列出（（居住在Duluth的顧客）曾在（工作于L001地點的銷售員））處購買商品的訂單號。1.列出（）（）商品的訂單號

SELECToidFROMordersWHEREcid?(2)ANDsid?(3)2.居住Duluth的顧客

SELECTcidFROMcustomersWHEREcity=‘Duluth’3.工作地點為L001的銷售員

SELECTsidFROMsalersWHERElid=‘L001’E46例：列出居住在Duluth的顧客曾在工作于L001地點的銷售員處購買商品的訂單號。SELECToidFROMordersWHEREcidIN( SELECTcidFROMcustomersWHEREcity=‘Duluth’)ANDsidIN( SELECTsidFROMsalersWHERElid=‘L001’);E46SELECToidFROMordersWHERE(cid,sid)IN( SELECTcid,sid FROMcustomersc,salerss

WHEREc.city=‘Duluth’ANDs.lid=‘L001’);在Oracle中可以寫為：E464、EXISTS使用EXISTS和NOTEXISTS判斷子查詢是否返回行（不考慮具體數據）。在這種情況下，通常將外部的數據引入到子查詢內部作為條件。例：列出工作地點和工資都與Jack相同的銷售員信息。另外一種寫法：SELECT*FROMsalers

sWHEREEXISTS(

SELECT*

FROMsalers

WHEREsname=‘Jack’

ANDlid=s.lidANDsalary=s.salary);E45例：列出沒有通過S005銷售員購買過商品的顧客的姓名。Step1：列出（？）顧客的姓名。SELECTFROMcustomerscWHERE?Step2：通過S005銷售員購買商品

該顧客有從S005銷售員處買的商品

SELECT*

FROMorders

WHEREsid=‘S005’andc.cid=cidE47例：列出沒有通過S005銷售員購買過商品的顧客的姓名。SELECTameFROMcustomerscWHERENOTEXISTS( SELECT* FROMorderso WHEREsid=‘S005’ANDo.cid=c.cid);只關心是否有返回結果，不關心結果是什么。問題：該語句是否返回了在任何銷售員處都未買過商品的顧客信息？E47濾掉從未購買過商品的顧客信息問題變?yōu)椋涸涃徺I過商品的顧客中，沒有從S005銷售員購買過商品的顧客姓名。

SELECTame FROMordersoc,customerscWHERENOTEXISTS( SELECT* FROMorderso WHEREsid=‘S005’ANDo.cid=c.cid)

ANDoc.cid=c.cid;E47例：列出在所有銷售員處都購買過商品的顧客的編號。在每個銷售員s那里都購買o過商品的顧客c這樣的銷售員ss不存在（顧客c沒有在銷售員ss處購買o過商品）顧客c沒有在銷售員ss處購買o過商品

SELECT*

FROMorders

WHEREsid=ssANDcid=c;

該結果集合應該為空。E48使用NOTEXIST進行For-All查詢。對于顧客cid，不存在他沒訂過貨的銷售員。SELECTDISTINCTcid

FROMcustomerscWHERENOTEXISTS(

SELECT*

FROMsalerss

WHERENOT

EXISTS(

SELECT*FROMorderso

WHEREo.sid=s.sidANDo.cid=c.cid

));E48量詞轉換比較運算符其他寫法=SOMEIN=ALL無意義<>SOME無意義<>ALLNOTIN>SOME>(SELECTMIN()…)<SOME<

(SELECTMAX()…)>ALL>(SELECTMAX()…)<ALL<

(SELECTMIN()…)IN可以轉換為EXISTS子查詢的使用方式邏輯表達式運算符INNOTIN,eopxSOME,eopxALLEXISTSNOTEXISTS一般來說在子查詢中不使用DISTINCT和ORDERBY。3.3.3集合運算★可以對兩條SELECT語句的結果集合進行集合運算。只要求兩條SELECT語句查詢表達式的數目和對應數據類型一致。集合運算的種類并集運算：取兩個查詢結果的并集。使用UNION關鍵字。例：SELECTcityFROMlocationsUNIONSELECTcityFROMcustomers;合并時，如果有多條完全相同的記錄，只保留一個。要保留重復的記錄，需要使用

UNIONALL。E49集合運算的種類交集運算：取兩個查詢結果的交集，使用INTERSECT關鍵字。差集運算：取兩個查詢結果的差集，使用MINUS關鍵字。可以對集合運算的總體結果進行排序，在語句的最后面寫ORDERBY。這種情況下使用序號作標識是比較方便的。3.3.4其他高級SELECT語句★樹狀查詢例：列出以編號為’S001’的銷售員為根的人員樹狀關系。 SELECT*

FROMsalers

STARTWITHsid='S001'

CONNECTBYPRIORsid=manager;靈活的子查詢E50SQL實際執(zhí)行時需要進行語法分析，產生執(zhí)行計劃，對執(zhí)行方式進行優(yōu)化。一般說來，盡量使用連接而不是子查詢，特別是子查詢內部使用外查詢字段值的情況；盡量不把子查詢作為表使用；沒有必要不要使用DISTINCT、GROUPBY和ORDERBY；3.4TOP-N問題在實踐中經常遇到的典型問題表ranks(id,score)按score升/降序，查詢出屬于某個范圍的記錄。M1：排在最前面的N條記錄(基本的TOP-N問題)。當score出現重復/并列時，仍然準確地取出N條記錄。M2：排在最前面的N條記錄。與M1的區(qū)別是，返回所有與第N條記錄score相等的記錄，因此可能返回多于N條記錄。M3：排在最前面N個不同score值對應的所有記錄。M4：（廣義的TOP-N問題）最前面的第N1至N2條記錄。一般不考慮并列，只考慮記錄數目。例如，網站數據的分頁瀏覽。1、專用方案-MySQLMySQL支持最充分，提供了LIMIT選項。例：M1問題的MySql解。

SELECT*FROMranksORDERBYscore

LIMIT3;例：M4問題的MySql解。

SELECT*FROMranksORDERBYscore

LIMIT2,3;1、專用方案–MS-SQL提供了TOPN選項。例：M1問題的MS-SQL解。

SELECTTOP3

*FROMranksORDERBYscore;例：M2問題的MS-SQL解。

SELECTTOP3WITHTIES*FROMranksORDERBYscore;例：M4問題的MS-SQL解。 SELECTTOP3*FROMranksWHEREidNOTIN( SELECTTOP2idFROMranksORDERBYscore) ORDERBYscore;沒有直接專用于解決M3問題的方法。1、專用方案-OracleOracle對此類問題的支持比較弱。ROWNUM，其使用語法如同一個字段一樣，但并不是Oracle中提供了一個偽列（pseudo-column）表中字段，因此稱之為偽列。ROWNUM表示當前返回的記錄是整個結果集合的第幾個（從1開始計數）。例：M1問題在Oracle中一個錯誤的解。

SELECT*FROMranks

WHEREROWNUM<=3 ORDERBYscore;很遺憾，上面的例子并不能得到期望的結果。

因為ROWNUM這個偽列是作為一個WHERE條件出現的，所以要先于ORDERBY計算。所以上例的結果是選出開始的3條記錄(次序不確定)，然后進行排序。E4-1在FROM中使用子查詢例：M1問題的Oracle解。

SELECT*

FROM(

SELECT*FROMranksORDERBYscore

) WHEREROWNUM<=3;在Oracle中，很難用ROWNUM來解決剩下的問題。E4-22、通用方案處于前N位：比其score小的記錄數小于N。例：M2問題。

SELECT*

FROMranks

r1

WHERE3>(

SELECTCOUNT(*)

FROMranks

r2

WHEREr2.score<r1.score

) ORDERBYscore;與第3個記錄等值的結果都能被查出idscoreCOUNT(*)1102213214215346347468469581058E4-32、通用方案例：M3問題。 SELECT* FROMranksr1 WHERE3>( SELECTCOUNT(DISTINCTscore) FROMranksr2 WHEREr2.score<r1.score

) ORDERBYscore;idscoreCOUNT(DISTINCT*)1102213214215326327438439541054E4-42、通用方案例：什么結果？

SELECT*

FROMranksr1

WHERE3>=(

SELECTCOUNT(*)

FROMranksr2

WHEREr2.score<=r1.score

) ORDERBYscore;

idscoreCOUNT(*)111224324424536636748848951010510E4-5例：解決M2問題的近似方法。SELECT*FROMranksWHEREidIN

(

SELECTr1.idFROMranksr1,ranksr2WHEREr1.score

>=

r2.scoreGROUPBYr1.idHAVINGCOUNT(*)<=3)ORDERBYscore;idscoreCOUNT(*)111224324424536636748848951010510E4-6例：M4問題。SELECT*FROMranksr1WHERE8>(

SELECTCOUNT(*)FROMranksr2

WHEREr2.score<r1.score) AND3<=( SELECTCOUNT(*)FROMranksr2 WHEREr2.score<r1.score)ORDERBYscore;idscoreCOUNT(*)1102213214215346347468469581058E4-7M1問題中的并列，不考慮其他因素，只使用SQL語句是不能解決的。例：

SELECT*FROMranksr1

WHERE3>(

SELECTCOUNT(*)FROMranksr2

WHEREr2.score<r1.scoreOR(

r2.score=r1.scoreANDr2.id<r1.id)) ORDERBYscore,id;用id強制排序！idscoreCOUNT(*)/id110221/1321/2421/3534/4634/5746/6846/7958/81058/