數(shù)據(jù)庫課程組PPT課件

1、信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院數(shù)據(jù)庫課程組信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院第05講 查詢數(shù)據(jù)表信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院學(xué)習(xí)目標(biāo)知識目標(biāo)知識目標(biāo): :知道數(shù)據(jù)查詢語句的基本結(jié)構(gòu)了解常用數(shù)據(jù)查詢子句及作用熟悉常用常見數(shù)據(jù)查詢類型能力目標(biāo)能力目標(biāo): :能使用查詢語句進行簡單查詢能使用查詢語句進行多表查詢會使用嵌套查詢會合并查詢結(jié)果信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院主要內(nèi)容查詢語句基本格式條件查詢查詢排序分組查詢多表查詢嵌套查詢合并查詢信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院任務(wù)5.1 單表數(shù)據(jù)查詢查詢數(shù)據(jù)表中數(shù)據(jù)的語句基本命令格式如下：子任務(wù)5.1.1 查詢語句基本格式SELECT 列名或表達式列表

2、INTO 新表FROM 表名WHERE 查詢條件ORDER BY 排序條件GROUP BY 分組條件HAVING 分組后篩選條件信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院子任務(wù)5.1.2 查詢數(shù)據(jù)列和行【例5-1】查詢S（學(xué)生表）中前5位同學(xué)信息。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-2】查詢S（學(xué)生表）中SNO(學(xué)號)和SNAME(姓名)兩列數(shù)據(jù)。【練一練】查詢Product（產(chǎn)品表）中ProID(產(chǎn)品編號)和ProName(產(chǎn)品名稱)兩列數(shù)據(jù)。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-3】將SC（選課表）中SNO(學(xué)號)和CNO(課程號)兩列數(shù)據(jù)合并成一列輸出。【練一練】查詢Product（產(chǎn)品表）

3、中ProID(產(chǎn)品編號)和ProName(產(chǎn)品名稱)兩列數(shù)據(jù)。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院函數(shù)用法函數(shù)用途SUM(ALL|DISTINCT 表達式)返回列或表達式的和。AVG(ALL|DISTINCT 表達式)返回列或表達式的平均值。MAX(表達式)返回列或表達式的最大值。MIN(表達式)返回列或表達式的最小值。COUNT(ALL|DISTINCT 表達式或*) 返回所有的項數(shù)。除了算術(shù)運算符外，還有聚合函數(shù)也可以用于表達式，具體用法如表5-1所示。表5-1 常用聚合函數(shù)【例5-4】統(tǒng)計C（課程表）中所有課程的總學(xué)時?！揪氁痪殹拷y(tǒng)計Product（產(chǎn)品表）中所有商品的庫存總和。信息工程學(xué)院

4、常州機電職業(yè)技術(shù)學(xué)院【例5-5】統(tǒng)計C（課程表）中課程學(xué)時的最大值、最小值和平均值?！揪氁痪殹拷y(tǒng)計Product（產(chǎn)品表）中最大的商品庫存。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-6】統(tǒng)計C（課程表）中開設(shè)的所有課程門數(shù)。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-7】統(tǒng)計C（課程表）中有多少種不同的學(xué)時?！揪氁痪殹拷y(tǒng)計Product（產(chǎn)品表）中有多少種商品。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院子任務(wù)5.1.3 精確條件查詢要查詢符合條件的數(shù)據(jù)行需要用WHERE子句。WHERE子句后寫查詢條件。SQL SERVER中常用的比較運算符和邏輯運算符如表5-2和5-3所示。運算符含義示例=等于WHERE S

5、CORE=100 查詢成績列的值等于100的行數(shù)據(jù)或!=不等于WHERE SCORE100 查詢成績列的值不等于100的行數(shù)據(jù)大于WHERE SCORE90 查詢成績列的值大于90的行數(shù)據(jù)小于WHERE SCORE=大于等于WHERE SCORE=90 查詢成績列的值大于或等于90的行數(shù)據(jù)=小于等于WHERE SCORE90 查詢成績列的值不大于90的行數(shù)據(jù)，即查詢成績小于等于90的行數(shù)據(jù)AND如果兩個布爾表達式都為TRUE，則返回TRUEWHERE SCORE=60 AND SCORE=70查詢成績列的值大于60且小于70的行數(shù)據(jù)OR如果兩個布爾表達式中的一個為TRUE，則返回TRUEWHE

6、RE SNAME=李勇 OR SNAME=劉晨查詢姓名列的值為李勇或者劉晨的行數(shù)據(jù)BETWEENAND如果操作數(shù)在某個范圍之內(nèi)，則返回TRUE，包含邊界值WHERE SCORE BETWEEN 60 AND 70效果與第二行示例相同IN如果操作數(shù)等于表達式列表中的一個，則返回TRUE。WHERE SNAME IN(李勇, 劉晨)效果與第三行示例相同信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-8】查詢C（課程表）中教師編號為T1的教師所開設(shè)的課程?！揪氁痪殹坎樵僑ale（銷售表）中產(chǎn)品編號為001的商品銷售記錄。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-9】查詢SC（選課表）中成績字段為空值的數(shù)據(jù)。信

7、息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-10】查詢C（課程表）中學(xué)時數(shù)在60到80之間的課程。（包括60和80）。此題有兩種方法可以完成?！揪氁痪殹坎樵働roduct（產(chǎn)品表）中庫存數(shù)量大于5且小于10的的商品信息。（注意此題不包含邊界值，所以不能用BETWEENAND）信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-11】查詢C（課程表）中數(shù)據(jù)庫和數(shù)據(jù)結(jié)構(gòu)這兩門課程的學(xué)時數(shù)。此題有兩種方法可以完成?！揪氁痪殹坎樵働roduct（產(chǎn)品表）中洗衣機和空調(diào)的庫存數(shù)量。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-12】改寫【例5-11】查詢C（課程表）中除了數(shù)據(jù)庫和數(shù)據(jù)結(jié)構(gòu)這兩門課程以外其它課程的學(xué)時數(shù)。此題有兩

8、種方法可以完成?！揪氁痪殹坎樵僑ale（銷售表）中除編號001和002這兩種商品外其它商品的銷售數(shù)量。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-13】查詢C（課程表）中教師T3講授的信息系統(tǒng)這門課程的學(xué)時數(shù)。并將該查詢結(jié)果保存到新表C_T3中。【練一練】查詢Sale（銷售表）中編號001的商品在2017-02-04這天的銷售數(shù)量。并將該查詢結(jié)果保存到新表Sale_001中。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院子任務(wù)5.1.4 模糊條件查詢當(dāng)查詢條件不明確時，我們需要使用LIKE關(guān)鍵字進行模糊查詢，模糊查詢一般用于字符對象的查詢。LIKE后跟帶通配符的比較條件，如果條件中未通配符將進行精確比較查詢。常

9、用的通配符如表5-4所示。表5-4 常用通配符通配符含義示例%匹配多個任意字符LIKE %a% 匹配包含字符a的字符串_（下劃線）僅匹配一個任意字符LIKE a_ 匹配以字符a開頭，之后是任意一個字符的字符串 匹配括號中的任意一個字符LIKE abc% 匹配以a、b、c中任意一個字符開頭的字符串匹配不在括號中的其它字符LIKE abc% 匹配除了a、b、c之外其它字符開頭的字符串信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-14】查詢S（學(xué)生表）中姓張的同學(xué)信息。【練一練】查詢Product（產(chǎn)品表）中產(chǎn)品名稱中含有“冰”字的產(chǎn)品信息。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-15】查詢S（學(xué)生表）中

10、名字有兩個字且以“敏”字結(jié)尾的同學(xué)信息?！揪氁痪殹坎樵働roduct（產(chǎn)品表）中產(chǎn)品名稱以“機”字結(jié)尾的產(chǎn)品信息。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-16】查詢S（學(xué)生表）中姓張和姓劉的同學(xué)信息?！揪氁痪殹坎樵働roduct（產(chǎn)品表）中產(chǎn)品名稱以“冰”和“洗”開頭的產(chǎn)品信息。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-17】查詢S（學(xué)生表）中除姓張和姓劉的同學(xué)以外其它同學(xué)信息。此題有兩種方法可以完成?！揪氁痪殹坎樵働roduct（產(chǎn)品表）中產(chǎn)品名稱不以“冰”或“洗”開頭的產(chǎn)品信息。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院子任務(wù)5.1.5 查詢結(jié)果排序如果要對查詢結(jié)果排序，則要用ORDER BY子句。關(guān)

11、鍵字ASC表示升序，DESC表示降序，默認為升序?！纠?-18】查詢S（學(xué)生表）中同學(xué)信息，結(jié)果按出生日期升序排列?！揪氁痪殹坎樵僑ale（銷售表）中產(chǎn)品銷售信息，結(jié)果以銷售日期升序排列。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-19】查詢SC（選課表）中成績信息，結(jié)果先按課程編號升序排，若課程編號相同再按成績降序排?！揪氁痪殹坎樵僑ale（銷售表）中產(chǎn)品銷售信息，結(jié)果先按銷售日期升序排列，若銷售日期相同，再按銷售數(shù)量降序排。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院子任務(wù)5.1.6 分組查詢統(tǒng)計 分組查詢類似于分類匯總，即按某一特征對數(shù)據(jù)進行分類，而后匯總統(tǒng)計相關(guān)數(shù)據(jù)。例如，按性別分類統(tǒng)計人數(shù)，按課程分

12、類統(tǒng)計每門課程的平均分等。分組查詢用GROUP BY子句加上聚合函數(shù)來進行查詢?！纠?-20】統(tǒng)計SC（選課表）中每門課程的平均成績，結(jié)果按平均成績升序排列。【練一練】統(tǒng)計Sale（銷售表）中每種產(chǎn)品的銷售數(shù)量，結(jié)果以銷售數(shù)量升序排列。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-21】統(tǒng)計SC（選課表）中每位同學(xué)的選課門數(shù)，并合計總門數(shù)?！揪氁痪殹拷y(tǒng)計Sale（銷售表）中每種產(chǎn)品的銷售記錄數(shù)，并合計總記錄數(shù)。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-22】統(tǒng)計SC（選課表）中每位同學(xué)的選課門數(shù)，并篩選出選課門數(shù)超過5門的學(xué)生?！揪氁痪殹拷y(tǒng)計Sale（銷售表）中每種產(chǎn)品的銷售數(shù)量，并篩選出銷售數(shù)量大于

13、3的記錄。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-23】統(tǒng)計S（學(xué)生表）中計算機系和信息系的人數(shù)。此題有兩種方法?？紤]一下哪種方法查詢效率更高?！揪氁痪殹糠謩e統(tǒng)計Sale（銷售表）中2017-02-04和2017-06-03這兩天的產(chǎn)品銷售數(shù)量。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院任務(wù)5.2 多表數(shù)據(jù)查詢?nèi)绻覀冃枰瑫r查看學(xué)生的基本信息及其選課信息就需要用到兩張表S（學(xué)生表）和SC（選課表）的數(shù)據(jù)，這時就需要進行多表查詢。多表查詢需要將表進行連接。如果多表查詢時未加表連接條件則會出現(xiàn)如圖所示結(jié)果。該連接稱為交叉連接，交叉連接的結(jié)果稱為笛卡兒積。笛卡兒積結(jié)果中列數(shù)是兩張表列數(shù)之和，行數(shù)是兩張表行數(shù)

14、之積。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院笛卡兒積的結(jié)果對于我們來講是沒有意義的，我們需要對表加上連接條件。表連接分為內(nèi)連接和外連接兩種方式。命令也有SQL Server和ANSI標(biāo)準(zhǔn)語法兩種寫法，其中ANSI標(biāo)準(zhǔn)語法是通用的。SQL Server語法格式：SELECT 列名或表達式，F(xiàn)ROM 表1，表2，WHERE 連接條件1 AND 連接條件2 ANSI語法格式：SELECT 列名或表達式，F(xiàn)ROM 表1 JOIN 表2ON 連接條件1JOIN 表3ON連接條件2信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院1.內(nèi)連接內(nèi)連接包括有等值連接、自然連接、不等連接、自連接等連接方式。1）等值連接：連接條件為兩個字

15、段值相等，可以是不同字段值相等。【例5-24】查詢S（學(xué)生表）中學(xué)生的基本信息及SC（選課表）中該生相關(guān)選課信息。我們分別用兩種語法實現(xiàn)該查詢。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院2）自然連接：自然連接時特殊的等值連接，它要求連接條件必須是公共字段相等，且結(jié)果集中沒有重復(fù)列。而等值連接結(jié)果集中可以有重復(fù)列。【例5-25】用自然連接方式查詢S（學(xué)生表）中學(xué)生“李勇”的基本信息及SC（選課表）中該生的選課信息。只需在【例5-23】基礎(chǔ)上去掉重復(fù)字段SNO即可?！揪氁痪殹坎樵働roduct(產(chǎn)品表)中“電視機”的產(chǎn)品信息以及Sale（銷售表）中該產(chǎn)品的銷售信息 。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-

16、26】查詢成績在90分以上的學(xué)生選課信息，包括學(xué)生的學(xué)號、姓名、選修課程號、課程名及選課成績。【練一練】查詢每個部門的商品銷售情況，包括部門編號，部門名稱，產(chǎn)品編號，產(chǎn)品名稱，銷售數(shù)量，銷售員工號等。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院3）比較連接：連接條件使用比較運算符。即連接條件不用等號?！纠?-27】為每位教師查詢一下非自己講授的課程?！揪氁痪殹繛槊课宦殕T查詢不是自己銷售的商品信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院4）自連接：把一張表模擬成兩張表，自己和自己進行連接查詢?！纠?-28】查詢所有年齡比劉晨小的學(xué)生姓名、性別、出生日期和劉晨的出生日期?！揪氁痪殹坎樵儍r格比“空調(diào)”高的商品及空調(diào)的價格。

17、信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院2.外連接外連接包括左外連接（LEFT JOIN）、右外連接（RIGHT JOIN）和全外連接（FULL JOIN）三種。內(nèi)連接主要查詢兩張表中匹配的記錄，外連接可以查詢兩張表中所有匹配和非匹配的記錄。外連接只有ANSI標(biāo)準(zhǔn)語法格式。1）左外連接（LEFT JOIN）：查詢JOIN關(guān)鍵字左邊表中所有的記錄及右邊表中匹配的記錄，如果右邊表沒有匹配記錄，則相應(yīng)字段顯示為空值。2）右外連接（RIGHT JOIN）：查詢JOIN關(guān)鍵字右邊表中所有的記錄及左邊表中匹配的記錄，如果左邊表沒有匹配記錄，則相應(yīng)字段顯示為空值。3)全外連接（FULL JOIN）：顯示兩張表中所有

18、匹配和非匹配記錄。非匹配記錄的相應(yīng)字段為空值。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-29】分別用左外連接、右外連接和全外連接的方式連接S（學(xué)生表）和SC（選課表）。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【練一練】在Depart（部門表）中添加一個部門“04，管理部”，分別用左外連接、右外連接和全外連接連接查詢Depart（部門表）和Product(產(chǎn)品表)，查看每個部門銷售產(chǎn)品的信息，并對比查詢結(jié)果。練習(xí)完成后請刪除新增部門記錄。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院任務(wù)5.3 嵌套查詢把內(nèi)部查詢語句的結(jié)果作為比較值放在外部查詢語句的WHERE或HAVING子句中，這種查

19、詢方式叫嵌套查詢。內(nèi)部的查詢語句稱為子查詢?！纠?-30】查詢T（教師表）中職稱與“李力”相同的教師信息。【練一練】查詢和“王麗”部門相同的其他職員信息。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-31】查詢SC（選課表）中選修了教師 “T1”所講授課程的學(xué)生學(xué)號及其所選課程號。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-32】用EXISTS改寫上例5-31。 也可以用NOT EXISTS表示取反。如圖5-46所示，查詢除教師“T1”外，選修其他老師所授課程的學(xué)生?！揪氁痪殹坑脙煞N方法查詢“家電部”所售的產(chǎn)品信息。信息工程學(xué)院常州機電職業(yè)技術(shù)學(xué)院【例5-33】查詢SC（選課表）中成績高于所有課程平均分的選課信息?！揪氁痪殹坎樵働roduct（商品表 ）中銷售價格比所有商品平均價格低的商品信息。信息工程學(xué)院常州機