第5章數(shù)據(jù)查詢操作主編：劉志成本章學習導航本章學習導航

本章學習要點（1）查詢的基本語法。（2）簡單查詢的形式和實現(xiàn)。（3）內(nèi)連接查詢的形式和實現(xiàn)。（4）外連接查詢的形式和實現(xiàn)。（5）子查詢的實現(xiàn)和應用。（6）聯(lián)合查詢及其應用。

建議課時：6課時5.1查詢的基本語法查詢基本語法PL/SQL的數(shù)據(jù)查詢是一種從數(shù)據(jù)庫中檢索符合搜索條件的記錄生成數(shù)據(jù)記錄集合，并把它們存入數(shù)據(jù)記錄集對象中的操作。

5.2簡單查詢選擇列1．選擇所有列2．選擇指定列3．計算列4．使用別名【例1-1】查詢商品的所有信息。

SELECT*FROMSCOTT.Goods;【例1-2】網(wǎng)站管理人員在了解商品信息時只需要了解所有商品的商品號、商品名稱和商品單價。SELECTg_ID,g_Name,g_PriceFROMSCOTT.Goods【例1-3】在Goods表中存儲有商品數(shù)量和商品單價，現(xiàn)在需要了解所有商品的商品號、商品名稱和商品總額。SELECTg_ID,g_Name,g_Price*g_NumberFROMGoods5.2簡單查詢選擇列【例1-4】要求了解所有商品的商品號、商品名稱和總價值，但希望分別以漢字標題商品號、商品名稱和總價值表示g_ID、g_Name和g_Price*g_Number。SELECTg_ID商品號,g_Name商品名稱,g_Price*g_Number總價值FROMSCOTT.Goods【例1-5】顯示所有會員的編號、姓名、性別和年齡SELECTc_ID編號,c_NAME姓名,c_GENDER性別,TO_CHAR(SYSDATE,‘YYYY’)-TO_CHAR(c_BIRTH,‘YYYY’)AS年齡FROMSCOTT.CUSTOMERS;5.2簡單查詢選擇行-條件查詢【例2-1】需要了解所有商品中的“熱點”商品的所有信息。SELECT*FROMSCOTT.GoodsWHEREg_Status='熱點‘【例2-2】需要了解商品類別為“01”，商品單價在2500元以上的商品信息，要求以漢字標題顯示商品號、商品名稱、商品類別號和價格。SELECTg_ID商品號,g_Name商品名稱,t_ID類別號,g_Price價格FROMGoodsWHEREt_ID='01'ANDg_Price>2500【例2-3】需要了解所有年齡在20～25歲之間的會員的名稱和年齡（用Nl表示，不是基本表中的字段，是計算出來的列）。SELECTc_Name,TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(c_BIRTH,'YYYY')NLFROMCustomersWHERETO_CHAR(SYSDATE,'YYYY')-TO_CHAR(c_BIRTH,'YYYY')BETWEEN20AND255.2簡單查詢選擇行-條件查詢-使用IN【例2-5】需要了解來自“湖南株洲”和“湖南長沙”兩地會員的詳細信息。SELECTc_ID,c_Name,c_AddressFROMCustomersWHERESUBSTR(c_Address,1,4)IN('湖南株洲','湖南長沙')【例2-6】需要了解家庭地址不是“湖南株洲”和“湖南長沙”的商品的詳細信息。SELECTc_ID,c_Name,c_AddressFROMCustomersWHERESUBSTR(c_Address,1,4)NOTIN('湖南株洲','湖南長沙')5.2簡單查詢選擇行-條件查詢-使用通配符【例2-7】需要了解所有商品中以“三星”兩個字開頭的商品的詳細信息。SELECT*FROMGoodsWHEREg_NameLIKE'三星%‘【例2-8】需要了解姓“黃”且名字中只有兩個漢字的會員的會員名、真實姓名、電話和電子郵箱。SELECTc_Name,c_TrueName,c_Phone,c_E-mailFROMCustomersWHEREc_TrueNameLIKE'黃_'5.2簡單查詢選擇行-條件查詢-空值判斷對于那些允許空值的列，可以使用ISNULL或ISNOTNULL來判斷其值是否為空。對于使用ISNULL的查詢表達式，如果返回值為邏輯真，則說明當前數(shù)據(jù)記錄對應列的值為空，否則為非空。ISNOTNULL的含義與ISNULL恰好相反。

【例2-10】查詢暫時沒有商品圖片的商品信息SELECT*FROMGoodsWHEREg_ImageISNULL5.2簡單查詢選擇行-使用DISTINCT【例2-11】需要了解在WebShop網(wǎng)站進行了購物并下了訂單的會員編號。SELECTg_IDFROMOrderDetailsSELECTDISTINCTg_IDFROMOrderDetails

5.2簡單查詢選擇行-使用ROWNUM【例2-12】查詢商品表中前5條商品的詳細信息。

SELECT * FROM SCOTT.GOODS WHERE ROWNUM<=5;在eBuy電子商城中，“新品推薦”功能中的商品就可以通過這種方式進行選擇

5.2簡單查詢選擇行-查詢結果排序在輸出查詢數(shù)據(jù)記錄集時，默認的數(shù)據(jù)記錄顯示順序是按這些數(shù)據(jù)記錄在原表中的邏輯排列順序。如果需要定制查詢數(shù)據(jù)記錄集的輸出順序，則可以使用ORDERBY子句來實現(xiàn)。ORDERBY子句能夠按照指定的一個或多個列（表達式）的升序或降序來重新排列查詢數(shù)據(jù)記錄集的輸出順序。

【例3-1】需要了解商品類別號為“01”的商品的商品號、商品名稱和商品單價，并要求根據(jù)商品的價格進行降序（價格由高到低）排列。SELECTg_ID,g_Name,g_PriceFROMSCOTT.GoodsWHEREt_ID='01'ORDERBYg_PriceDESC

5.2簡單查詢選擇行-查詢結果排序【例3-2】在“【例3-1】”中，如果商品的價格相同，要求根據(jù)商品名稱進行升序排列。SELECTg_ID,g_Name,g_PriceFROMSCOTT.GoodsWHEREt_ID='01'ORDERBYg_PriceDESC,g_NameASCSELECTg_ID,g_Name,g_PriceFROMSCOTT.GoodsWHEREt_ID='01'ORDERBY3DESC,2ASC;

5.2簡單查詢選擇行-查詢結果分組有時候需要對查詢數(shù)據(jù)記錄集按列或表達式進行分組，以利于分析數(shù)據(jù)，此時可以通過使用GROUPBY子句來實現(xiàn)。如果需要在分組的基礎上進行組的過濾，則可以結合GROUPBY子句再使用HAVING子句來實現(xiàn)。對于需要對查詢數(shù)據(jù)集進行匯總以生成統(tǒng)計報表的情況，則需要使用COMPUTE子句。

5.2簡單查詢選擇行-查詢結果分組-使用聚合函數(shù)【例4-1】查詢所有商品的最高價、最低價、平均價和所有庫存量之和。SELECTMAX(g_Price)最高價,MIN(g_Price)最低價,AVG(g_Price)平均價,SUM(g_Number)總庫存FROMSCOTT.Goods;5.2簡單查詢選擇行-查詢結果分組-使用GROUP【例4-2】需要了解每一類別的商品總數(shù)。SELECTt_ID類別號,COUNT(t_ID)商品數(shù)FROMSCOTT.GoodsGROUPBYt_ID;執(zhí)行該PL/SQL查詢語句，將先按商品類別編號t_ID進行分組，相同商品類別編號的數(shù)據(jù)記錄位于同一個組內(nèi)，然后通過使用聚合函數(shù)進行統(tǒng)計，計算出每一組內(nèi)具有的商品編號數(shù)目。

5.2簡單查詢選擇行-查詢結果分組-使用GROUP【例4-3】查詢商品單價超過2000的商品類別編號和庫存量。對于該例，可以考慮分兩步執(zhí)行：第一步，使用WHERE條件過濾掉價格在2000元以下的所有商品記錄，被過濾掉的數(shù)據(jù)記錄不再參與后面的操作；第二步，按商品編號對過濾后的數(shù)據(jù)記錄集進行分組，得到的分組數(shù)據(jù)記錄集即為最終所需要的結果。SELECTt_ID,SUM(g_Number)庫存量FROM SCOTT.GoodsWHERE g_PRICE>=2000GROUPBYt_ID;5.2簡單查詢選擇行-查詢結果分組-使用GROUP如果需要使查詢數(shù)據(jù)記錄集生成數(shù)據(jù)統(tǒng)計，以及橫向小計統(tǒng)計，則需要在GROUPBY子句中使用ROLLUP關鍵字?！纠?-4】顯示每個商品類別的商品庫存量及商品總庫存量。SELECTg_ID,t_ID,SUM(g_Number)庫存量FROM SCOTT.GoodsGROUPBYROLLUP(t_ID,g_ID);5.2簡單查詢選擇行-查詢結果分組-使用GROUP如果需要對查詢數(shù)據(jù)記錄集生成數(shù)據(jù)統(tǒng)計、橫向小計及縱向小計結果，則可以在GROUPBY子句中使用CUBE關鍵字

。

【例4-5】顯示商品總庫存量、每個種類商品的商品總量和每一商品的數(shù)量。SELECTg_ID,t_ID,SUM(g_Number)庫存量FROM SCOTT.GoodsGROUPBYCUBE(g_ID,t_ID);5.2簡單查詢選擇行-查詢結果分組-使用GROUP在對查詢數(shù)據(jù)記錄集進行分組的基礎上，再對每組數(shù)據(jù)記錄集進行過濾時，不能使用WHERE子句來進行過濾，而應該使用HAVING子句進行過濾，因為WHERE子句在分組之前執(zhí)行過濾，而HAVING則在分組之后執(zhí)行過濾。

【例4-6】需要了解訂單總額大于5000的訂單信息，并按升序排列。SELECTo_ID訂單編號,sum(d_Price*d_Number)總金額FROMSCOTT.OrderDetailsGROUPBYo_IDHAVINGsum(d_Price*d_Number)>5000ORDERBYsum(d_Price*d_Number)5.3連接查詢內(nèi)連接查詢實際上，數(shù)據(jù)庫實例中的各個表之間可能存在某些內(nèi)在關聯(lián)，通過這些關聯(lián)，可以為應用程序提供一些涉及多個表的復雜信息，如主表和外表之間就存在主鍵和外鍵的關聯(lián)。PL/SQL為這種多個表之間存在關聯(lián)的查詢提供了檢索數(shù)據(jù)的方法，稱為連接查詢。

內(nèi)連接是使用比較運算符作為連接條件的連接方式。內(nèi)連接作為一種典型的默認連接方式，關鍵字INNER默認提供。使用內(nèi)連接方式時，只有那些滿足連接條件的數(shù)據(jù)記錄被顯示，不滿足連接條件的數(shù)據(jù)記錄將不被顯示。根據(jù)連接條件中的關系運算符是否使用“=”，內(nèi)連接可以分為等值連接和非等值連接。若用于連接的兩個表或視圖來源于同一個表或視圖，這樣的內(nèi)連接也被稱之為自連接。5.3連接查詢等值連接【例5-1】需要了解每個商品的商品號、商品名稱和商品類別名稱?！痉治觥可唐坊拘畔⒋娣旁贕oods表中，商品分類信息存放在Types表，所以本查詢實際上同時涉及Goods與Types兩個表中的數(shù)據(jù)。這兩個表之間的聯(lián)系是通過兩個表都具有的屬性t_ID實現(xiàn)的。要查詢商品及其類別名稱，就必須將這兩個表中商品號相同的記錄連接起來，這是一個等值連接。SELECTGoods.g_ID,Goods.t_ID,Types.t_Name,Goods.g_NameFROMSCOTT.GoodsJOINSCOTT.TypesONSCOTT.Goods.t_ID=SCOTT.Types.t_ID;5.3連接查詢等值連接【例5-2】需要了解所有訂單中訂購的商品信息（商品名稱、購買價格和購買數(shù)量）和訂單日期。【分析】在“訂單表”中存放了訂單號和訂單產(chǎn)生日期等信息，而該訂單所購買的商品的信息（商品號、購買價格和購買數(shù)量）存放在“訂單詳情”表中，商品的名稱存放在“商品表”中，因此，訂單表需要和訂單詳情表通過訂單號進行連接以獲得訂單中所購商品的商品號等信息，而訂單詳情表需要和商品表進行連接以通過商品號獲得商品名稱信息。主要涉及三個表的查詢。SELECTOrders.o_ID,o_Date,g_Name,d_Price,d_NumberFROMSCOTT.OrdersJOINSCOTT.OrderDetailsONOrders.o_ID=OrderDetails.o_IDJOINGoodsONOrderDetails.g_ID=Goods.g_ID;5.3連接查詢自身連接連接操作一般在兩個表之間進行，也可以在一個表與其自身之間進行連接，這樣的連接操作稱為自連接。為了分別表示一個表與其自身，需要引入表的別名?！纠?-3】需要了解不低于“三星SGH-C178”價格的商品號、商品名稱和商品單價，查詢后的結果要求按商品單價升序排列。SELECTG2.g_ID商品號,G2.g_Name商品名稱,G2.g_Price價格FROMSCOTT.GoodsG1JOINSCOTT.GoodsG2ONG1.g_Name='三星SGH-C178'ANDG1.g_Price<=G2.g_PriceORDERByG2.g_Price;5.3連接查詢外連接查詢-左外連接查詢數(shù)據(jù)記錄集包含來自一個表的所有數(shù)據(jù)記錄和另一個表中的匹配數(shù)據(jù)記錄的連接稱為左外連接。對于左外連接，第一個表中的所有數(shù)據(jù)記錄將被顯示，第二個表（匹配表）如果找不到相匹配的數(shù)據(jù)記錄，相應的列將顯示為空值（NULL），否則顯示匹配數(shù)據(jù)記錄?！纠?-4】需要了解所有商品類別及其對應商品信息，如果該商品類別沒有對應商品也需要顯示其類別信息?！痉治觥繉ypes表和Goods表進行左外連接，Types為左表，Goods表為右表。完成語句如下所示。SELECTTypes.t_ID,t_Name,g_ID,g_Name,g_Price,g_NumberFROMSCOTT.TypesLEFTOUTERJOINSCOTT.GoodsonTypes.t_ID=Goods.t_ID;5.3連接查詢外連接查詢-右外連接查詢數(shù)據(jù)記錄集包含來自第二個表的所有數(shù)據(jù)記錄和第一個表中的匹配數(shù)據(jù)記錄的連接稱為右外連接。對于右外連接，第二個表中的所有數(shù)據(jù)記錄將被顯示，第一個表（匹配表）如果找不到相匹配的數(shù)據(jù)記錄，相應的列將顯示為空值（NULL），否則顯示匹配數(shù)據(jù)記錄?！纠?-5】需要了解所有商品的信息（即使是不存在對應的商品類別信息，實際上這種情況是不存在的）?！痉治觥繉ypes表和Goods表進行右外連接，Goods為左表，Types表為右表。完成語句如下所示。SELECTTypes.t_ID,t_Name,g_ID,g_Name,g_Price,g_NumberFROMSCOTT.TypesRIGHTOUTERJOINSCOTT.GoodsonTypes.t_ID=Goods.t_ID;5.3連接查詢外連接查詢-完全外部連接查詢數(shù)據(jù)記錄集的兩個連接表中所有行的連接操作稱為完全外部連接。對于完全外部連接，兩個連接表無論是否匹配，它們的數(shù)據(jù)記錄都將被顯示。【例5-6】需要了解所有商品的基本信息和類別信息?！痉治觥吭赥ypes表和Goods表之間的建立完整外部連接。完成語句如下所示。SELECTTypes.t_ID,t_Name,g_ID,g_Name,g_Price,g_NumberFROMSCOTT.TypesFULLOUTERJOINSCOTT.GoodsonTypes.t_ID=Goods.t_ID;

5.3連接查詢外連接查詢-交叉連接查詢交叉連接是使用CROSS關鍵字進行的連接，它的輸出為笛卡兒積，即第一個表的每一條數(shù)據(jù)記錄與第二個表的每一條數(shù)據(jù)記錄進行連接。笛卡兒積的結果通常很大，其數(shù)據(jù)記錄數(shù)目等于兩個表的數(shù)據(jù)記錄數(shù)目之積，數(shù)據(jù)記錄的列數(shù)等于兩個表的列數(shù)之和。對商品信息表和商品類別表進行交叉連接。其完成語句有下面兩種。[語句一]SELECT*FROMSCOTT.TypesCROSSJOINSCOTT.Goods或[語句二]SELECTTypes.*,Goods.*FROMSCOTT.Types,SCOTT.Goods5.4子查詢IN子查詢將一個查詢語句嵌套在另一個查詢語句中的查詢稱為嵌套查詢或子查詢。被嵌入在其他查詢語句中的查詢語句稱為子查詢語句，子查詢語句的載體查詢語句稱為父查詢語句。子查詢語句一般嵌入在另一個查詢語句的WHERE子句或HAVING子句中，另外，子查詢語句也可以嵌入在一個數(shù)據(jù)記錄更新語句的WHERE子句中。

對列表操作的子查詢通過IN關鍵字實現(xiàn)父查詢和子查詢之間的連接，判斷指定列的值是否出現(xiàn)在子查詢的查詢數(shù)據(jù)記錄集中。使用IN的子查詢語句返回的查詢數(shù)據(jù)記錄集一般由單列多行值組成，這也是子查詢中最常用的一種形式。對于使用IN的子查詢的連接條件，其語法格式為：

WHERE表達式[NOT]IN（子查詢）5.4子查詢IN子查詢【例6-1】需要了解和“摩托羅拉W380”為同類商品的商品號、商品名稱和類別號?！痉治觥恳樵兣c“摩托羅拉W380”的同類商品，首先要知道“摩托羅拉W380”的商品類別，再根據(jù)該類別獲取同類商品的相關信息。（1）確定“摩托羅拉W380”所屬類別名。SELECTt_IDFROMSCOTT.GoodsWHEREg_Name='摩托羅拉W380‘（2）查找類別號為‘01’的商品信息。SELECTg_ID,g_Name,t_IDFROMSCOTT.GoodsWHEREt_ID='01';

使用子查詢的語句如下。SELECTg_ID,g_Name,t_IDFROMSCOTT.GoodsWHEREt_IDIN(SELECTt_IDFROMGoodsWHEREg_Name='摩托羅拉W380');5.4子查詢IN子查詢【例6-2】需要了解購買了“紅雙喜牌兵乓球拍”的訂單號、訂單時間和訂單總金額。SELECTo_ID,o_Date,o_SumFROMSCOTT.OrdersWHEREo_IDIN(SELECTo_IDFROMOrderDetailsWHEREg_IDIN

(SELECTg_IDFROMGoodsWHEREg_Name='紅雙喜牌兵乓球拍'));【例6-3】需要了解購買了商品號為“060001”的會員e_ID（會員號）、e_Name（會員名稱）和e_Address（籍貫）。SELECTc_ID,c_Name,c_AddressFROMSCOTT.CustomersWHEREc_IDIN

(SELECTc_IDFROMOrdersJOINOrderDetailsONOrders.o_ID=OrderDetails.o_IDWHEREg_ID='060001');5.4子查詢比較運算符子查詢子查詢也可以使用比較運算符引入。此時，子查詢結果為一個單行單列的值，并可以在父查詢中通過比較運算符（“>”、“>=”、“<”、“<=”、“=”、“!=”或“<>”）連接子查詢，如果子查詢返回不止一個值，整個查詢語句將會產(chǎn)生錯誤

。

【例6-4】需要了解購買了“紅雙喜牌兵乓球拍”的訂單號、訂單時間和訂單總金額。（使用“=”完成“）SELECTo_ID,o_Date,o_SumFROMOrdersWHEREo_IDIN

(SELECTo_IDFROMOrderDetailsWHEREg_ID=

(SELECTg_IDFROMGoodsWHEREg_Name='紅雙喜牌兵乓球拍'));5.4子查詢ANY或ALL子查詢【例6-5】需要了解比籍貫為“湖南長沙”任一會員年齡小的會員信息，查詢結果按降序排列。【分析】比任一會員的年齡小，即比最小的還要小。反過來，如果是大于ALL，則要比最大的還要大，完成語句如下所示。SELECTc_ID,c_Name,TO_CHAR(SYSDATE)-TO_CHAR(c_Birth)Age,c_AddressFROMCustomersWHERESUBSTR(c_Address,1,4)<>'湖南長沙'ANDc_Birth>ALL(SELECTc_BirthFROMCustomersWHERESUBSTR(c_Address,1,4)='湖南長沙')ORDERBYAgeDESC5.4子查詢EXIST子查詢【例6-6】針對Employees表中的每一名員工，在Orders表中查找處理過訂單并且送貨模式為“郵寄”的所有訂單信息?！痉治觥康谝徊揭檎姨幚磉^訂單的員工編號，第二步再根據(jù)員工處理訂單的送貨模式顯示訂單詳細信息。SELECT*FROMOrdersWHEREo_Sendmode='郵寄'ANDEXISTS(SELECTe_IDFROMEmployeesASEmpWHEREEmp.e_ID=Orders.e_ID)5.4子查詢INSERT子查詢INSERTINTO語句不但可以將一條數(shù)據(jù)記錄插入到表中，也可以通過使用子查詢的形式將一個數(shù)據(jù)記錄集插入到表中，實現(xiàn)批量插入。

【例6-7】求每一類商品的平均價格，并將結果保存到數(shù)據(jù)庫中。（1）在數(shù)據(jù)庫中建立一個有兩個屬性列的新表，其中一列存放類別名，另一列存放相應類別的商品平均價格。CREATETABLEAvgGoods(t_IDCHAR(2),a_avgFLOAT)；其中t_ID代表商品類別號，a_avg代表平均價格。（2）對數(shù)據(jù)庫的商品表按商品號分組求平均價格，再把商品號和平均價格存入新表中。INSERTINTOAvgGoods(t_ID,a_avg)SELECTt_ID,AVG(g_Price)FROMGoodsGROUPBYt_ID;（3）查看表