第06章使用SQL查詢-副本

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

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

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

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

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

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

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

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

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

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

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

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

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

14、接操作來(lái)完成。關(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元的職工的職工號(hào)、姓名和他們所在的城市。 SELECT 職工號(hào),姓名,城市FROM 基礎(chǔ).職工 JOIN 倉(cāng)儲(chǔ).倉(cāng)庫(kù)ON 職工.倉(cāng)庫(kù)號(hào) = 倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào)WHERE 工資 12502. 多個(gè)表的連接多個(gè)表的連接 查詢接受上海倉(cāng)庫(kù)訂購(gòu)單的北京供應(yīng)商的信息 SE

15、LECT 供應(yīng)商.供應(yīng)商號(hào), 供應(yīng)商名, 地址FROM 訂貨.供應(yīng)商 JOIN 訂貨.訂購(gòu)單 ON 供應(yīng)商號(hào)=供貨方JOIN 基礎(chǔ).職工 ON 職工號(hào)=經(jīng)手人JOIN 倉(cāng)儲(chǔ).倉(cāng)庫(kù) ON 職工.倉(cāng)庫(kù)號(hào)=倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào)WHERE 地址=北京 AND 城市=上海3. 別名和自連接查詢 一個(gè)表通過(guò)一個(gè)表通過(guò)不同的屬性不同的屬性到自身的連接稱作自連接。到自身的連接稱作自連接。相互連接的表物理上為同一張表。相互連接的表物理上為同一張表。必須為兩個(gè)表取別名，使之在邏輯上成為兩個(gè)表。必須為兩個(gè)表取別名，使之在邏輯上成為兩個(gè)表。注意連接條件的寫法注意連接條件的寫法3. 別名和自連接查詢 根據(jù)職工關(guān)系列出上一級(jí)領(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).職工號(hào) = 職員.班組長(zhǎng) 王月 領(lǐng)導(dǎo) 張揚(yáng)張揚(yáng) 領(lǐng)導(dǎo) 陳虻檢索與檢索與“張揚(yáng)張揚(yáng)”工資相同工資相同的職工號(hào)和姓名。的職工號(hào)和姓名。下列語(yǔ)句返回幾條記錄？下列語(yǔ)句返回幾條記錄？SELECT 職工號(hào),姓名,城市FROM 基礎(chǔ).職工 JOIN 倉(cāng)儲(chǔ).倉(cāng)庫(kù)ON 職工.倉(cāng)庫(kù)號(hào) = 倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào)4. 外連接查詢外連接查詢 SELECT FROM LEFT | RIGHT | FULL OUTER JOIN ON WHERE 左連接在結(jié)果表中包含第一個(gè)表中滿足條件的所有記錄

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

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

19、購(gòu)單號(hào)、訂購(gòu)日期、供應(yīng)商名和供應(yīng)商地址。 SELECT 訂購(gòu)單號(hào),訂購(gòu)日期,供應(yīng)商名,地址FROM 訂貨.訂購(gòu)單 LEFT JOIN 訂貨.供應(yīng)商 ON 訂購(gòu)單.供貨方=供應(yīng)商.供應(yīng)商號(hào)結(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右連接舉例右連接舉例查詢訂購(gòu)單及其供應(yīng)商信息，查詢結(jié)果包括訂購(gòu)單號(hào)、訂購(gòu)日期、供應(yīng)商名和供應(yīng)商

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

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

22、 JOIN 基礎(chǔ).職工WHERE 倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào)=職工.倉(cāng)庫(kù)號(hào)分組及匯總查詢 對(duì)查詢結(jié)果進(jìn)行匯總計(jì)算 聚合函數(shù) 匯總l一般匯總查詢 l帶明細(xì)的匯總查詢 l使用GROUP BY的分組匯總查詢 l使用COMPUTE BY的分組匯總查詢 l使用COMPUTE BY和COMPUTE的匯總查詢 語(yǔ)法格式語(yǔ)法格式 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ù)是對(duì)初始的查詢結(jié)果進(jìn)行計(jì)算然后得到最終的查詢結(jié)果 常用聚合函數(shù)lCOUNT（計(jì)數(shù)）lAVG（計(jì)算平均值）lMIN（計(jì)算最小值）lMAX（計(jì)算最大值）lSUM（求和）lCHECKSUM（校驗(yàn)和）lSTDEV（計(jì)算標(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ǔ).職工 一般匯總查詢舉例求北京和上海的倉(cāng)庫(kù)職工的工資總和。 SELECT SUM(工資)FROM 基礎(chǔ).職工 JOIN 倉(cāng)儲(chǔ).倉(cāng)庫(kù)ON 職工.倉(cāng)庫(kù)號(hào)=倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào)WHERE 城市=北京 OR 城市=上

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

26、=倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào)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的分組匯總查詢舉例查詢每個(gè)倉(cāng)庫(kù)的職工人數(shù)和平均工資。 SELECT 倉(cāng)庫(kù)號(hào),COUNT(*),AVG(工資)FROM 基礎(chǔ).職工GROUP BY 倉(cāng)庫(kù)號(hào) 結(jié)果是：WH141337.5WH

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

28、數(shù)和平均工資。 SELECT 倉(cāng)庫(kù)號(hào),COUNT(*),AVG(工資)FROM 基礎(chǔ).職工WHERE 工資1250GROUP BY 倉(cāng)庫(kù)號(hào)HAVING COUNT(*) = 2注意：只有滿足HAVING短語(yǔ)指定條件的組才輸出HAVING短語(yǔ)與WHERE子句的區(qū)別：作用對(duì)象不同lWHERE子句作用于基表或視圖，從中選擇滿足條件的元組。lHAVING短語(yǔ)作用于組，從中選擇滿足條件的組。 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的分組匯總查詢舉例 列出職工全部記錄并計(jì)算各倉(cāng)庫(kù)的平均工資和工資小計(jì)。列出職工全部記錄并計(jì)算各倉(cāng)庫(kù)的平均工資和工資小計(jì)。 SELECT * FROM 基礎(chǔ).職工ORDER BY 倉(cāng)庫(kù)號(hào)COMPUTE AVG(工資),SUM(工資) BY 倉(cāng)庫(kù)號(hào)

30、 使用COMPUTE BY的分組匯總查詢舉例查詢目前訂購(gòu)的器件信息，要求列出器件名稱、訂購(gòu)單號(hào)、訂購(gòu)的數(shù)量和金額，并計(jì)算各種器件的訂購(gòu)條目數(shù)、訂購(gòu)數(shù)量合計(jì)和金額合計(jì)。 SELECT 器件名稱,訂購(gòu)單號(hào),數(shù)量,金額FROM 基礎(chǔ).器件JOIN 訂貨.訂購(gòu)明細(xì)ON 器件.器件號(hào)=訂購(gòu)明細(xì).器件號(hào)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的匯總查詢舉例列出職工全部記錄并計(jì)算各倉(cāng)庫(kù)的平均工資和工資小計(jì)，最后給出全體職工的平均工資和工資總和。 SELECT * FROM 基礎(chǔ).職工ORDER BY 倉(cāng)庫(kù)號(hào)COMPUTE AVG(工資),SUM(工資) BY 倉(cāng)庫(kù)號(hào) COMPUTE AVG(工資),SUM(工資) 注意：SQL規(guī)定，當(dāng)使用聚集函數(shù)時(shí)，列名不能與聚集函數(shù)一起使用（除非他們出現(xiàn)在其他集合中）。l例：查

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

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

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

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

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

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

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

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

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

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

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

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