第06章使用SQL查詢-副本

1、第6章 使用SQL進行查詢 SQL的數(shù)據(jù)查詢功能基本格式：SELECT FROM WHERE SQL查詢命令格式SELECT ALL|DISTINCT *|,FROM WHERE GROUP BY , HAVING ORDER BY ASC|DESC , ASC|DESCCOMPUTE SELECT ALL|DISTINCT *|,說明要查詢的數(shù)據(jù)，ALL說明不去掉重復(fù)元組，DISTINCT說明要去掉重復(fù)元組， 一般是表中的列名，如果要查詢表中的所有列可以使用“*”表示 FROM z說明要查詢的數(shù)據(jù)來自哪個（些）表，可以基于單個表或多個表進行查詢；z可以基于基本表、也可以基于視圖，當(dāng)然也可以兩

2、者一起使用。 WHERE 說明查詢條件，即選擇元組的條件，可以用于查詢條件的運算符也非常豐富，下表列出了常用的運算符 GROUP BY , HAVING zGROUP BY短語用于對查詢結(jié)果進行分組，可以利用它進行分組匯總（即對查詢結(jié)果按組進行計算或匯總）； zHAVING短語必須跟隨GROUP BY使用，它用來限定分組必須滿足的條件。ORDER BY ASC|DESC, ASC|DESC z用來對查詢的結(jié)果進行排序。 COMPUTE短語SQL Server支持的短語，可以進行帶明細的匯總。zSQL Server支持的短語，可以進行帶明細的分組匯總。COMPUTE BY短語SQL Server

3、 2005/2008還支持查詢結(jié)果的l并（UNION）l交（INTERSECT）l差（EXCEPT）運算 查詢的分類簡單查詢 連接查詢分組及計算查詢 嵌套查詢 簡單查詢簡單無條件查詢簡單有條件查詢存儲查詢結(jié)果查詢結(jié)果的排序 TOP查詢并、交、差查詢1. 簡單無條件查詢查詢?nèi)柯毠ば畔⒉樵內(nèi)柯毠ば畔ELECT * FROM 基礎(chǔ).職工查詢職工的姓名和工資信息查詢職工的姓名和工資信息SELECT 姓名, 工資 FROM 基礎(chǔ).職工關(guān)系代數(shù)等價表達式：姓名姓名, ,工資工資( (基礎(chǔ).職工) )要去掉結(jié)果表中的重復(fù)行，可用要去掉結(jié)果表中的重復(fù)行，可用DISTINCTDISTINCT實現(xiàn)實現(xiàn)SEL

4、ECT DISTINCT SELECT DISTINCT 班組長班組長 FROMFROM 基礎(chǔ)基礎(chǔ). .職工職工檢索職工表中的所有班組長班組長E2E72. 簡單條件查詢 （1 1）比較大?。┍容^大小 (,=,=,)(,=,=,)查詢工資不少于查詢工資不少于1300元的職工的姓名和工資值。元的職工的姓名和工資值。SELECT 姓名, 工資 FROM 基礎(chǔ).職工WHERE 工資 = 1300 查詢工資在查詢工資在1000至至2000元的職工信息。元的職工信息。 SELECT * FROM 基礎(chǔ).職工WHERE 工資= 1000 AND 工資=2000(2) 使用 NOT BETWEENAND的查詢

5、 查詢查詢2011年年6月簽訂的訂購單信息。月簽訂的訂購單信息。 SELECT * FROM 訂貨.訂購單 WHERE 訂購日期 BETWEEN 2011/06/01 AND 2011/06/30 查詢查詢工資不在工資不在1000至至2000元的職工信息元的職工信息。 SELECT * FROM 基礎(chǔ).職工WHERE 工資 NOT BETWEEN 1000 AND 2000等價的表達式等價的表達式?(3) 字符串匹配查詢字符串匹配查詢 列名列名 NOT LIKE NOT LIKE 匹配串匹配串作用：作用：查找指定列名與匹配串常量匹配的元組。查找指定列名與匹配串常量匹配的元組。匹配串類型：匹配串

6、類型：匹配串可以是字符串常量，也可以匹配串可以是字符串常量，也可以含有通配符。含有通配符。 通配符種類：通配符種類： （百分號）：匹配（百分號）：匹配0 0個或多個字符。個或多個字符。 （下劃線）：匹配一個字符。（下劃線）：匹配一個字符。 ：匹配括號中的字符：匹配括號中的字符 ：不匹配括號中的字符：不匹配括號中的字符(3) 字符串匹配查詢字符串匹配查詢 從器件關(guān)系中查找在規(guī)格字段值中任意位置包含字從器件關(guān)系中查找在規(guī)格字段值中任意位置包含字符串符串“DDR”的所有記錄。的所有記錄。 SELECT * FROM 基礎(chǔ).器件 WHERE 規(guī)格 LIKE %DDR% 查找訂購單號為查找訂購單號為OR

7、開頭，最后開頭，最后1位為位為0的訂購單記的訂購單記錄錄SELECT * FROM 訂貨.訂購單 WHERE 訂購單號 LIKE OR_0（3）字符串匹配查詢）字符串匹配查詢查找訂購單號前查找訂購單號前3位是位是OR7、最后、最后1位為位為09的所有訂的所有訂購單記錄。購單記錄。 SELECT * FROM 訂貨.訂購單 WHERE 訂購單號 LIKE OR70-9 查找訂購單號前查找訂購單號前3位是位是OR7、最后、最后1位不是位不是6或或8的所的所有訂購單記錄。有訂購單記錄。 SELECT * FROM 訂貨.訂購單 WHERE 訂購單號 LIKE OR768（4 4）空值查詢）空值查詢

8、空值是未確定的值或其值尚不知道空值是未確定的值或其值尚不知道例如，學(xué)生選課，在開學(xué)初學(xué)生只有選課記錄，例如，學(xué)生選課，在開學(xué)初學(xué)生只有選課記錄，沒有修課成績，這時成績成績一項的值就是空值。沒有修課成績，這時成績成績一項的值就是空值。不能用不能用= =或或，只能用，只能用IS NULLIS NULL或或IS NOT NULLIS NOT NULL（4 4）空值查詢）空值查詢 查詢沒有確定供貨方的訂購單信息（供貨方字查詢沒有確定供貨方的訂購單信息（供貨方字段為空值的記錄）段為空值的記錄）SELECT * FROM 訂貨.訂購單 WHERE 供貨方 IS NULL查詢已經(jīng)確定了供貨方的訂購單信息查詢

9、已經(jīng)確定了供貨方的訂購單信息 SELECT * FROM 訂貨.訂購單 WHERE 供貨方 IS NOT NULL(5) 使用使用IN表達式的查詢表達式的查詢 test_expression NOT IN (expression ,.n)查詢器件名稱為“內(nèi)存”或“鼠標(biāo)”的器件信息。 SELECT * FROM 基礎(chǔ).器件 WHERE 器件名稱 IN (內(nèi)存, 鼠標(biāo))以前怎么表示?3. 存儲查詢結(jié)果 使用INTO短語可以將查詢結(jié)果存儲到指定的新表中。查詢職工E3經(jīng)手的訂購單的訂購單號、供貨方和訂購日期信息，并將結(jié)果存儲到“訂貨”模式下、表名為E3的表中。 SELECT 訂購單號, 供貨方, 訂購

10、日期 INTO 訂貨.E3 FROM 訂貨.訂購單WHERE 經(jīng)手人=E34. 查詢結(jié)果的排序查詢結(jié)果的排序 ORDER BY order_expression ASC | DESC 按單價升序列出所有器件信息。按單價升序列出所有器件信息。 SELECT * FROM 基礎(chǔ).器件 ORDER BY 單價下列查詢語句的含義是什么？ SELECT * FROM 訂貨.訂購明細 ORDER BY 訂購單號, 金額 DESC5. TOP短語的作用 TOP (expression) PERCENT WITH TIES 從器件表中查詢單價最高的3條器件信息。 SELECT TOP (3) * FROM 基

11、礎(chǔ).器件 ORDER BY 單價 DESC5. TOP短語的作用 從訂購明細表中查詢訂購數(shù)量最多的從訂購明細表中查詢訂購數(shù)量最多的7條訂購信息，條訂購信息，如果有與第如果有與第7條記錄的數(shù)量并列的記錄也一起列出。條記錄的數(shù)量并列的記錄也一起列出。 SELECT TOP (7) WITH TIES * FROM 訂貨.訂購明細 ORDER BY 數(shù)量 DESC從訂購明細表中查詢金額在前從訂購明細表中查詢金額在前15%的訂購記錄信息，的訂購記錄信息，如果隨后有金額并列的記錄也一起列出。如果隨后有金額并列的記錄也一起列出。 SELECT TOP (15) PERCENT WITH TIES * FR

12、OM 訂貨.訂購明細 ORDER BY 金額 DESC6. 集合運算 SQL Server2005/2008支持集合的l并（UNION）l交（INTERSECT）l差（EXCEPT）運算。（1）集合并運算）集合并運算 查詢工資大于1500和工資小于1250的職工的倉庫號、職工號、姓名和工資信息（用集合并運算完成），結(jié)果按工資升序排序。語句SELECT 倉庫號, 職工號, 姓名, 工資 FROM 基礎(chǔ).職工 WHERE 工資1500UNIONSELECT 倉庫號, 職工號, 姓名, 工資 FROM 基礎(chǔ).職工 WHERE 工資1250ORDER BY 工資注意：ORDER BY短語是對最終結(jié)果進

13、行排序，該短語不能用在中間結(jié)果上。（2）集合交運算）集合交運算 查詢WH2倉庫有經(jīng)手2011年6月訂購單的職工號信息（用集合交運算完成）。語句SELECT 職工號 FROM 基礎(chǔ).職工 WHERE 倉庫號=WH2 INTERSECTSELECT 經(jīng)手人 FROM 訂貨.訂購單 WHERE 訂購日期 BETWEEN 2011/06/01 AND 2011/06/30(3) 集合差運算集合差運算 查詢目前沒有經(jīng)手訂購單的職工號（用集合差運算完成）。SELECT 職工號 FROM 基礎(chǔ).職工EXCEPTSELECT 經(jīng)手人 FROM 訂貨.訂購單連接查詢 當(dāng)查詢的結(jié)果出自多個表時，需要通過表之間的連

14、接操作來完成。關(guān)系代數(shù)的連接?連接查詢 SELECT FROM INNER|LEFT|RIGHT|FULL OUTER JOIN ON INNER|LEFT|RIGHT|FULL OUTER JOIN ON , nWHERE SELECT FROM JOIN ON JOIN ON , nWHERE 1. 一般連接一般連接 查詢工資多于1250元的職工的職工號、姓名和他們所在的城市。 SELECT 職工號,姓名,城市FROM 基礎(chǔ).職工 JOIN 倉儲.倉庫ON 職工.倉庫號 = 倉庫.倉庫號WHERE 工資 12502. 多個表的連接多個表的連接 查詢接受上海倉庫訂購單的北京供應(yīng)商的信息 SE

15、LECT 供應(yīng)商.供應(yīng)商號, 供應(yīng)商名, 地址FROM 訂貨.供應(yīng)商 JOIN 訂貨.訂購單 ON 供應(yīng)商號=供貨方JOIN 基礎(chǔ).職工 ON 職工號=經(jīng)手人JOIN 倉儲.倉庫 ON 職工.倉庫號=倉庫.倉庫號WHERE 地址=北京 AND 城市=上海3. 別名和自連接查詢 一個表通過一個表通過不同的屬性不同的屬性到自身的連接稱作自連接。到自身的連接稱作自連接。相互連接的表物理上為同一張表。相互連接的表物理上為同一張表。必須為兩個表取別名，使之在邏輯上成為兩個表。必須為兩個表取別名，使之在邏輯上成為兩個表。注意連接條件的寫法注意連接條件的寫法3. 別名和自連接查詢 根據(jù)職工關(guān)系列出上一級領(lǐng)導(dǎo)

16、及其職員(被其領(lǐng)導(dǎo))的清單SELECT 領(lǐng)導(dǎo).姓名, 領(lǐng)導(dǎo),職員.姓名FROM 基礎(chǔ).職工 領(lǐng)導(dǎo) JOIN 基礎(chǔ).職工 職員ON 領(lǐng)導(dǎo).職工號 = 職員.班組長 王月 領(lǐng)導(dǎo) 張揚張揚 領(lǐng)導(dǎo) 陳虻檢索與檢索與“張揚張揚”工資相同工資相同的職工號和姓名。的職工號和姓名。下列語句返回幾條記錄？下列語句返回幾條記錄？SELECT 職工號,姓名,城市FROM 基礎(chǔ).職工 JOIN 倉儲.倉庫ON 職工.倉庫號 = 倉庫.倉庫號4. 外連接查詢外連接查詢 SELECT FROM LEFT | RIGHT | FULL OUTER JOIN ON WHERE 左連接在結(jié)果表中包含第一個表中滿足條件的所有記錄

17、；如果左連接在結(jié)果表中包含第一個表中滿足條件的所有記錄；如果是在連接條件上匹配的元組，則第二個表返回相應(yīng)值，否則第是在連接條件上匹配的元組，則第二個表返回相應(yīng)值，否則第二個表返回空值。二個表返回空值。 右連接在結(jié)果表中包含第二個表中滿足條件的所有記錄；如果右連接在結(jié)果表中包含第二個表中滿足條件的所有記錄；如果是在連接條件上匹配的元組，則第一個表返回相應(yīng)值，否則第是在連接條件上匹配的元組，則第一個表返回相應(yīng)值，否則第一個表返回空值。一個表返回空值。 全連接在結(jié)果表中包含兩個表中滿足條件的所有記錄；如果是全連接在結(jié)果表中包含兩個表中滿足條件的所有記錄；如果是在連接條件上匹配的元組，則另一個表返回相

18、應(yīng)值，否則另一在連接條件上匹配的元組，則另一個表返回相應(yīng)值，否則另一個表返回空值。個表返回空值。一般連接舉例一般連接舉例查詢訂購單及其供應(yīng)商信息，查詢結(jié)果包括訂購單號、訂購日期、供應(yīng)商名和供應(yīng)商地址。 SELECT 訂購單號,訂購日期,供應(yīng)商名,地址FROM 訂貨.訂購單 JOIN 訂貨.供應(yīng)商ON 訂購單.供貨方=供應(yīng)商.供應(yīng)商號結(jié)果是：OR67 2011/06/23 愛華電子廠 北京OR73 2011/07/28 華通電子公司 北京OR76 2011/05/25 華通電子公司 北京OR79 2011/06/13 華通電子公司 北京左連接舉例左連接舉例查詢訂購單及其供應(yīng)商信息，查詢結(jié)果包括訂

19、購單號、訂購日期、供應(yīng)商名和供應(yīng)商地址。 SELECT 訂購單號,訂購日期,供應(yīng)商名,地址FROM 訂貨.訂購單 LEFT JOIN 訂貨.供應(yīng)商 ON 訂購單.供貨方=供應(yīng)商.供應(yīng)商號結(jié)果是：OR67 2011/06/23 愛華電子廠 北京OR73 2011/07/28 華通電子公司 北京OR76 2011/05/25 華通電子公司 北京OR77 NULL NULL NULLOR79 2011/06/13 華通電子公司 北京OR80 NULL NULL NULLOR90 NULL NULL NULL右連接舉例右連接舉例查詢訂購單及其供應(yīng)商信息，查詢結(jié)果包括訂購單號、訂購日期、供應(yīng)商名和供應(yīng)商

20、地址。 SELECT 訂購單號,訂購日期,供應(yīng)商名,地址FROM 訂貨.訂購單 RIGHT JOIN 訂貨.供應(yīng)商 ON 訂購單.供貨方=供應(yīng)商.供應(yīng)商號結(jié)果是：OR67 2011/06/23 愛華電子廠 北京OR73 2011/07/28 華通電子公司 北京OR76 2011/05/25 華通電子公司 北京NULL NULL 振華電子廠 西安NULL NULL 世紀金夢公司 鄭州全連接舉例查詢供應(yīng)商及其接受的訂購單信息，查詢結(jié)果包括供應(yīng)商號、供應(yīng)商名、訂購單號和訂購日期字段，結(jié)果按供應(yīng)商名排序。 SELECT 訂購單號,訂購日期,供應(yīng)商號,供應(yīng)商名FROM 訂貨.供應(yīng)商 FULL JOIN

21、訂貨.訂購單ON 供應(yīng)商.供應(yīng)商號= 訂購單.供貨方 ORDER BY 供應(yīng)商名結(jié)果是：OR77 NULL NULL NULLOR67 2011/06/23 S7 愛華電子廠 OR73 2011/07/28 S4 華通電子公司OR76 2011/05/25 S4 華通電子公司 S6 世紀金夢公司 S3 振華電子廠 廣義笛卡爾積 SELECT FROM CROSS JOIN WHERE 得到倉庫關(guān)系和職工關(guān)系的廣義笛卡爾積的運算結(jié)果。SELECT * FROM 倉儲.倉庫 CROSS JOIN 基礎(chǔ).職工 對倉庫關(guān)系和職工關(guān)系進行傳統(tǒng)的等值連接。SELECT * FROM 倉儲.倉庫 CROSS

22、 JOIN 基礎(chǔ).職工WHERE 倉庫.倉庫號=職工.倉庫號分組及匯總查詢 對查詢結(jié)果進行匯總計算 聚合函數(shù) 匯總l一般匯總查詢 l帶明細的匯總查詢 l使用GROUP BY的分組匯總查詢 l使用COMPUTE BY的分組匯總查詢 l使用COMPUTE BY和COMPUTE的匯總查詢 語法格式語法格式 SELECT ( expression ) ,.n FROM table_source WHERE search_condition SELECT Group_id, ( expression ) ,.n FROM table_source WHERE search_condition GROUP

23、 BY Group_id HAVING SELECT . FROM table_source WHERE search_condition ORDER BY Group_idCOMPUTE ( expression ) ,.n BY Group_idCOMPUTE ( expression ) ,.n 聚合函數(shù) 聚合函數(shù)是對初始的查詢結(jié)果進行計算然后得到最終的查詢結(jié)果 常用聚合函數(shù)lCOUNT（計數(shù)）lAVG（計算平均值）lMIN（計算最小值）lMAX（計算最大值）lSUM（求和）lCHECKSUM（校驗和）lSTDEV（計算標(biāo)準(zhǔn)差） l1. 一般匯總查詢 SELECT ( expressio

24、n ) ,.n FROM table_source WHERE search_condition 一般匯總查詢舉例找出供應(yīng)商所在地的數(shù)目。 SELECT COUNT (DISTINCT 地址)FROM 訂貨.供應(yīng)商 求支付的工資總數(shù)和職工人數(shù)，以及所有職工的平均工資、最高工資和最低工資。 SELECT SUM(工資),COUNT(*),AVG(工資),MAX(工資), MIN(工資)FROM 基礎(chǔ).職工 一般匯總查詢舉例求北京和上海的倉庫職工的工資總和。 SELECT SUM(工資)FROM 基礎(chǔ).職工 JOIN 倉儲.倉庫ON 職工.倉庫號=倉庫.倉庫號WHERE 城市=北京 OR 城市=上

25、海2. 帶明細的匯總查詢 SELECT select_list FROM table_source WHERE search_condition COMPUTE ( expression ) ,.n 帶明細的匯總查詢舉例 列出供應(yīng)商的地址，并計算出供應(yīng)商所在地的數(shù)目。 SELECT DISTINCT 地址FROM 訂貨.供應(yīng)商 COMPUTE COUNT(地址)結(jié)果是：西安北京鄭州Cnt3帶明細的匯總查詢舉例 列出北京和上海倉庫的職工姓名、工資、所在城市信息，并計算他們的職工人數(shù)、工資總和以及平均工資。 SELECT 姓名,工資,城市FROM 基礎(chǔ).職工 JOIN 倉儲.倉庫ON 職工.倉庫號

26、=倉庫.倉庫號WHERE 城市=北京 OR 城市=上海COMPUTE COUNT(姓名),SUM(工資),AVG(工資)3. 使用GROUP BY的分組匯總查詢 SELECT group_id, (expression) ,.n FROM table_source WHERE search_condition GROUP BY group_id HAVING search_condition使用GROUP BY的分組匯總查詢舉例查詢每個倉庫的職工人數(shù)和平均工資。 SELECT 倉庫號,COUNT(*),AVG(工資)FROM 基礎(chǔ).職工GROUP BY 倉庫號 結(jié)果是：WH141337.5WH

27、231333.33WH321345WH411270使用GROUP BY的分組匯總查詢舉例查詢每個倉庫工資相同的職工人數(shù)，結(jié)果按倉庫號排序。 SELECT 倉庫號,工資,COUNT(*)FROM 基礎(chǔ).職工GROUP BY 倉庫號,工資ORDER BY 倉庫號使用GROUP BY+HAVING的分組匯總查詢舉例求至少有4個職工的每個倉庫的職工人數(shù)和平均工資。SELECT 倉庫號,COUNT(*),AVG(工資)FROM 基礎(chǔ).職工GROUP BY 倉庫號HAVING COUNT(*) = 4使用GROUP BY+HAVING的分組匯總查詢舉例查詢至少有2個職工的工資大于1250的每個倉庫的職工人

28、數(shù)和平均工資。 SELECT 倉庫號,COUNT(*),AVG(工資)FROM 基礎(chǔ).職工WHERE 工資1250GROUP BY 倉庫號HAVING COUNT(*) = 2注意：只有滿足HAVING短語指定條件的組才輸出HAVING短語與WHERE子句的區(qū)別：作用對象不同lWHERE子句作用于基表或視圖，從中選擇滿足條件的元組。lHAVING短語作用于組，從中選擇滿足條件的組。 4. 使用使用COMPUTE BY的分組匯總查詢的分組匯總查詢 SELECT select_list FROM table_source WHERE search_condition ORDER BY order_

29、expression ASC | DESC COMPUTE ( expression ) ,.n BY expressionzORDER BY 的必要性If ORDER BY子句是：ORDER BY a,b,c則COMPUTEBY子句可以是如下三種形式：COMPUTEBY a,b,cCOMPUTEBY a,bCOMPUTEBY a使用COMPUTE BY的分組匯總查詢舉例 列出職工全部記錄并計算各倉庫的平均工資和工資小計。列出職工全部記錄并計算各倉庫的平均工資和工資小計。 SELECT * FROM 基礎(chǔ).職工ORDER BY 倉庫號COMPUTE AVG(工資),SUM(工資) BY 倉庫號

30、 使用COMPUTE BY的分組匯總查詢舉例查詢目前訂購的器件信息，要求列出器件名稱、訂購單號、訂購的數(shù)量和金額，并計算各種器件的訂購條目數(shù)、訂購數(shù)量合計和金額合計。 SELECT 器件名稱,訂購單號,數(shù)量,金額FROM 基礎(chǔ).器件JOIN 訂貨.訂購明細ON 器件.器件號=訂購明細.器件號ORDER BY 器件名稱COMPUTE COUNT(器件名稱),SUM(數(shù)量),SUM(金額) BY 器件名稱使用COMPUTE BY和COMPUTE的匯總查詢 SELECT select_list FROM table_source WHERE search_condition ORDER BY ord

31、er_expression ASC | DESC COMPUTE ( expression ) ,.n BY expressionCOMPUTE ( expression ) ,.n 使用COMPUTE BY和COMPUTE的匯總查詢舉例列出職工全部記錄并計算各倉庫的平均工資和工資小計，最后給出全體職工的平均工資和工資總和。 SELECT * FROM 基礎(chǔ).職工ORDER BY 倉庫號COMPUTE AVG(工資),SUM(工資) BY 倉庫號 COMPUTE AVG(工資),SUM(工資) 注意：SQL規(guī)定，當(dāng)使用聚集函數(shù)時，列名不能與聚集函數(shù)一起使用（除非他們出現(xiàn)在其他集合中）。l例：查

32、詢工資最高的職工姓名和工資，如下寫法是錯誤的： SELECT 姓名, MAX(工資) FROM 基礎(chǔ).職工聚集函數(shù)不能出現(xiàn)在WHERE子句中 l例：查詢工資最高的職工姓名，如下寫法是錯誤的： SELECT 姓名 FROM 基礎(chǔ).職工 WHERE 工資= MAX(工資) 嵌套查詢 普通嵌套查詢 使用量詞的嵌套查詢 內(nèi)、外層互相關(guān)嵌套查詢 使用EXISTS的嵌套查詢 1. 普通嵌套查詢 SELECT FROM WHERE AND IN(SELECT FROM WHERE )普通嵌套查詢舉例查詢哪些城市至少有一個倉庫的職工的工資為1250元？ SELECT 城市 FROM 倉儲.倉庫 WHERE 倉

33、庫號 IN (SELECT 倉庫號 FROM 基礎(chǔ).職工 WHERE 工資 = 1250) zJOIN?SELECT 城市 FROM 倉儲.倉庫 JOIN基礎(chǔ).職工ON 倉庫.倉庫號=職工.倉庫號WHERE 工資 = 1250 查詢效率? 普通嵌套查詢舉例查詢所有職工的工資都多于1210元的倉庫的信息。 SELECT * FROM 倉儲.倉庫 WHERE 倉庫號 NOT IN (SELECT 倉庫號 FROM 基礎(chǔ).職工WHERE 工資 = 1210 ) JOIN?普通嵌套查詢舉例找出和職工E4掙同樣工資的所有職工。 SELECT 職工號 FROM 基礎(chǔ).職工 WHERE 工資 = (SELE

34、CT 工資 FROM 基礎(chǔ).職工 WHERE 職工號 = E4)Join?普通嵌套查詢舉例找出哪些城市的倉庫向北京的供應(yīng)商發(fā)出了訂購單。SELECT 城市 FROM 倉儲.倉庫 WHERE 倉庫號 IN (SELECT 倉庫號 FROM 基礎(chǔ).職工 WHERE 職工號 IN (SELECT 職工號 FROM 訂貨.訂購單 WHERE 供貨方 IN (SELECT 供應(yīng)商號 FROM 訂貨.供應(yīng)商 WHERE 地址=北京) Join?2. 使用量詞的嵌套查詢 ANY|ALL|SOME (子查詢)使用量詞的嵌套查詢舉例查詢有職工的工資大于或等于WH1倉庫中任何一名職工的工資的倉庫號。 SELECT

35、 DISTINCT 倉庫號 FROM 基礎(chǔ).職工 WHERE 工資 = ANY(SELECT 工資 FROM 基礎(chǔ).職工 WHERE 倉庫號= WH1)等價?使用量詞的嵌套查詢舉例查詢有職工的工資大于或等于“WH1”倉庫中所有職工的工資的倉庫號。 SELECT DISTINCT 倉庫號 FROM 基礎(chǔ).職工 WHERE 工資 = ALL(SELECT 工資 FROM 基礎(chǔ).職工 WHERE 倉庫號= WH1)等價?3. 內(nèi)、外層互相關(guān)嵌套查詢 列出每個職工經(jīng)手的具有最高總金額的訂購單信息。列出每個職工經(jīng)手的具有最高總金額的訂購單信息。 SELECT outa.訂購單號,outa.經(jīng)手人,out

36、a.供貨方,outa.訂購日期,outa.金額FROM 訂貨.訂購單 outaWHERE outa.金額 = (SELECT MAX(innera.金額)FROM 訂貨.訂購單 inneraWHERE innera.經(jīng)手人 = outa.經(jīng)手人)列出每個職工經(jīng)手的? 是否可以用Group by?4. 使用EXISTS的嵌套查詢 NOT EXISTS (子查詢)查詢目前沒有經(jīng)手訂購單的職工信息。查詢目前沒有經(jīng)手訂購單的職工信息。 等價?查詢那些目前至少已經(jīng)經(jīng)手了查詢那些目前至少已經(jīng)經(jīng)手了1張訂購單的職工信息。張訂購單的職工信息。 等價?4. 使用EXISTS的嵌套查詢 NOT EXISTS (子

37、查詢)查詢目前沒有經(jīng)手訂購單的職工信息。查詢目前沒有經(jīng)手訂購單的職工信息。 SELECT * FROM 基礎(chǔ).職工 WHERE NOT EXISTS(SELECT * FROM 訂貨.訂購單 WHERE 經(jīng)手人 = 職工.職工號)等價?查詢那些目前至少已經(jīng)經(jīng)手了查詢那些目前至少已經(jīng)經(jīng)手了1張訂購單的職工信息。張訂購單的職工信息。 SELECT * FROM 基礎(chǔ).職工 WHERE EXISTS(SELECT * FROM 訂貨.訂購單 WHERE 經(jīng)手人 = 職工.職工號)等價?需要查詢支持的數(shù)據(jù)操作 查詢支持的插入操作 查詢支持的更新操作 查詢支持的刪除操作 1. 查詢支持的插入操作 INS

38、ERT INTO 新在“重慶”設(shè)立一個倉庫號為WH5、面積為600的倉庫，并計劃在該倉庫存放全部器件，因此先在庫存表中插入倉庫號WH5和所有器件號的組合，數(shù)量暫時為空值NULL。INSERT INTO 倉儲.庫存SELECT WH5,器件號,NULLFROM 基礎(chǔ).器件注意INSERT INTOSELECT 和 SELECTINTO的不同 2. 查詢支持的更新操作 UPDATE schema_name.table_nameSET col_name=exp|DEFAULT|NULL ,.n WHERE 這里的可以是基于本表定義的表達式，還可以是基于其他表的查詢，即在中可以嵌套查詢，并且通常是內(nèi)外

39、層互相關(guān)的嵌套，即外層為內(nèi)層查詢提供值，而內(nèi)層的查詢結(jié)果為外層的UPDATE語句所用。2. 查詢支持的更新操作根據(jù)訂購明細表中的單價和數(shù)量計算并更新訂購單表的金額字段值。 2. 查詢支持的更新操作根據(jù)訂購明細表中的單價和數(shù)量計算并更新訂購單表的金額字段值。 UPDATE 訂貨.訂購單 SET 金額=(SELECT SUM(單價*數(shù)量) FROM 訂貨.訂購明細WHERE 訂購單號=訂購單.訂購單號)3. 查詢支持的刪除操作 DELETE FROM schema_name.table_nameWHERE 同樣，這里的可以是基于本表定義的表達式，還可以是基于其他表的查詢，即在中可以嵌套查詢，可以是

40、內(nèi)外層互相關(guān)的嵌套、也可以是僅外層依賴于內(nèi)層的嵌套。3. 查詢支持的刪除操作刪除目前沒有任何訂購單的供應(yīng)商記錄。 DELETE FROM 訂貨.供應(yīng)商WHERE 供應(yīng)商號 NOT IN(SELECT 供貨方 FROM 訂貨.訂購單 WHERE 供貨方 IS NOT NULL)視圖及其操作倉庫號 城市 面積倉庫號 職工號 工資倉庫號 城市 職工號 工資倉庫號 面積基本表視圖定義視圖的命令 視圖是根據(jù)對表的查詢定義的，其命令格式如下：CREATE VIEW .(,) AS WITH CHECK OPTION行列子集視圖 從單個基本表選取某些行和某些列、并且包含基本表中的關(guān)鍵字所定義的視圖稱作行列子

41、集視圖。 行列子集視圖不僅可以用于查詢，原則上也可以進行各種更新操作。 視圖是虛擬表，所以對視圖的所有操作實際上都要轉(zhuǎn)換成對基本表的操作。行列子集視圖定義視圖emp_v1，使之只包含職工基本表的職工號、倉庫號和姓名字段。 CREATE VIEW emp_v1 ASSELECT 職工號,倉庫號,姓名 FROM基礎(chǔ).職工SELECT * FROM emp_v1INSERT INTO emp_v1 VALUES(E13,WH1,郭天華)?WITH CHECK OPTION的作用 對通過視圖操作的數(shù)據(jù)是否滿足定義視圖時的條件做檢查。 定義視圖wh_v1，使之只包含城市在北京的倉庫號和面積信息。 CRE

42、ATE VIEW wh_v1 ASSELECT 倉庫號,面積 FROM 倉儲.倉庫 WHERE 城市 = 北京行列子集視圖 ?INSERT INTO wh_v1 VALUES(WH9,777)?WITH CHECK OPTION的作用定義視圖wh_v2，使之只包含城市在北京的倉庫號和面積信息，定義視圖時使用WITH CHECK OPTION選項。 CREATE VIEW wh_v2 ASSELECT 倉庫號,面積 FROM 倉儲.倉庫 WHERE 城市 = 北京 WITH CHECK OPTIONINSERT INTO wh_v2 VALUES(WH10,777)?基于多個表的視圖 定義視圖w

43、h_emp_v1，使之包含倉庫號、城市、職工號和職工姓名信息。 CREATE VIEW wh_emp_v1 ASSELECT 倉庫.倉庫號, 城市, 職工號, 姓名 FROM 基礎(chǔ).職工 JOIN 倉儲.倉庫 ON 職工.倉庫號 = 倉庫.倉庫號 對用戶將有關(guān)系wh_emp_v1(倉庫號,城市,職工號,姓名) wh_emp_v1不是行列子集視圖，這樣的視圖能夠進行插入、修改和刪除操作嗎？ 不是行列子集視圖能進行插、改和刪操作嗎?INSERT INTO wh_emp_v1 VALUES(WH11,杭州,E13,海燕) ?INSERT INTO wh_emp_v1(倉庫號,城市) VALUES(WH11,杭州) ?INSERT INTO wh_emp_v1(倉庫號,職工號,姓名) VALUES(WH11,E13,海燕) ?CREATE VIEW wh_emp_v1 ASSELECT 倉庫.倉庫號, 城市, 職工號, 姓名 FROM 基礎(chǔ).職工 JOIN 倉儲.倉庫 ON 職工.倉庫號 = 倉庫.倉庫號 包含虛列的視圖 根據(jù)職工表創(chuàng)建包含職工號、姓名、月工資和年工資4列信息的視圖。 CREATE VIEW v_sal(職工號,姓名,月工資,年工資) ASSELECT 職工號,姓名,工資,工資*12 FROM 基礎(chǔ).職工計算列只可查詢、不可操作 INSERT I