大型數(shù)據(jù)庫(kù)應(yīng)用與維護(hù)以SQLServer2012為例上章內(nèi)容回顧4.4

視圖4.4.1視圖概述4.4.2創(chuàng)建視圖4.4.3利用視圖管理數(shù)據(jù)4.4.4管理視圖4.5

索引4.5.1索引概述4.5.2創(chuàng)建索引4.5.3管理索引4.1數(shù)據(jù)庫(kù)的創(chuàng)建與管理4.1.1創(chuàng)建數(shù)據(jù)庫(kù)4.1.2管理數(shù)據(jù)庫(kù)4.2數(shù)據(jù)表的創(chuàng)建與管理4.2.1創(chuàng)建數(shù)據(jù)表4.2.2修改數(shù)據(jù)表4.2.3刪除數(shù)據(jù)表4.3數(shù)據(jù)庫(kù)完整性4.3.1完整性4.3.2約束上章內(nèi)容提問(wèn)數(shù)據(jù)完整性有哪三種類(lèi)型？實(shí)現(xiàn)數(shù)據(jù)完整性的方法是？視圖是什么？有何特點(diǎn)或作用？索引我們介紹了幾種類(lèi)型，分別是？聚集索引和非聚集索引有何特點(diǎn)？

第五章SQLServer數(shù)據(jù)管理第五章SQLServer數(shù)據(jù)管理目的與要求：掌握利用select語(yǔ)句進(jìn)行簡(jiǎn)單的數(shù)據(jù)查詢(xún)，多表的連接查詢(xún)、嵌套查詢(xún)和集合查詢(xún)，并能掌握對(duì)數(shù)據(jù)表的增、刪、改等基本操作。重點(diǎn)與難點(diǎn)：數(shù)據(jù)查詢(xún)數(shù)據(jù)表的增、刪、改等基本操作第10講SQLServer數(shù)據(jù)管理15.1數(shù)據(jù)查詢(xún)5.1.1查詢(xún)語(yǔ)句5.1.2簡(jiǎn)單查詢(xún)5.1.3連接查詢(xún)5.1.4嵌套查詢(xún)5.1.5集合查詢(xún)★5.1數(shù)據(jù)查詢(xún)5.1.1查詢(xún)語(yǔ)句5.1.1

查詢(xún)語(yǔ)句

Select語(yǔ)句的基本語(yǔ)法結(jié)構(gòu)如下：Selectselect_list[intonew_table]Fromtable_source[wheresearch_condition][groupbygroup_by_expression][havingsearch_condition][orderbyorder_expression[asc|desc]]具體內(nèi)容見(jiàn)P119數(shù)據(jù)庫(kù)查詢(xún)是通過(guò)查詢(xún)語(yǔ)句搜索出用戶(hù)所需要的信息，SQLServer對(duì)數(shù)據(jù)的查詢(xún)操作是通過(guò)SELECT查詢(xún)語(yǔ)句來(lái)完成的。5.1.1

查詢(xún)語(yǔ)句參數(shù)說(shuō)明：參數(shù)相關(guān)說(shuō)明SELECT子句指定由查詢(xún)結(jié)果返回的列。Into子句將查詢(xún)結(jié)果存儲(chǔ)到新表或視圖中。FROM子句用于指定數(shù)據(jù)源，即使用的列所在的表或視圖。如果對(duì)象不止一個(gè)，那么它們之間必用逗號(hào)分開(kāi)。WHERE子句指定用于限制返回的行的搜索條件。如果SELECT語(yǔ)句沒(méi)有WHERE子句，DBMS假設(shè)目標(biāo)表中的所有行都滿(mǎn)足搜索條件。GroupBy子句指定用來(lái)放置輸出行的組，并且如果SELECT子句SELECT_list中包含聚合函數(shù)，則計(jì)算每組的匯總值。Having子句指定組或聚合函數(shù)的搜索條件。Having通常與GroupBy子句一起使用。Orderby子句指定結(jié)果集的排序方式。ASC關(guān)鍵字表示升序排列結(jié)果，DESC關(guān)鍵字表示降序排列結(jié)果。如果沒(méi)有指定任何一個(gè)關(guān)鍵字，那么ASC就是默認(rèn)的關(guān)鍵字。如果沒(méi)有OrderBy子句，DBMS將根據(jù)輸入表中的數(shù)據(jù)的存放位置來(lái)顯示數(shù)據(jù)。5.1.2簡(jiǎn)單查詢(xún)5.1.2

簡(jiǎn)單查詢(xún)查詢(xún)指定列查詢(xún)所有列使用運(yùn)算列改變列標(biāo)題顯示是否去重復(fù)元組TOP關(guān)鍵字限制返回行數(shù)where子句模糊查詢(xún)排序查詢(xún)結(jié)果分組與匯總5.1.2

簡(jiǎn)單查詢(xún)1.查詢(xún)指定列【例1】在采購(gòu)管理數(shù)據(jù)庫(kù)中查詢(xún)物料Item中的物料代碼，物料名稱(chēng)。select

物料代碼,物料名稱(chēng)from

物料5.1.2

簡(jiǎn)單查詢(xún)2.查詢(xún)所有列（通配符*：所有字段）【例2】從采購(gòu)管理數(shù)據(jù)庫(kù)（PO）的物料（Item）中查詢(xún)所有記錄。select

*

from

物料5.1.2

簡(jiǎn)單查詢(xún)3.使用運(yùn)算列YEAR()為系統(tǒng)函數(shù)，獲取指定日期的年份。GETDATE()為系統(tǒng)函數(shù)，獲取當(dāng)前日期和時(shí)間【例3】查詢(xún)所有采購(gòu)訂單的訂單編號(hào)，采購(gòu)年份。select

采購(gòu)訂單號(hào),YEAR(單據(jù)日期)from

采購(gòu)訂單頭5.1.2

簡(jiǎn)單查詢(xún)4.改變列標(biāo)題顯示格式：列名AS

指定的列標(biāo)題(新)或者：指定的列標(biāo)題(新)=列名【例4】上例3中，給采購(gòu)年份定義一個(gè)標(biāo)題，名為：'采購(gòu)年份'。select

采購(gòu)訂單號(hào),YEAR(單據(jù)日期)

as

采購(gòu)年份from

采購(gòu)訂單頭select

采購(gòu)訂單號(hào),

采購(gòu)年份=YEAR(單據(jù)日期)

from

采購(gòu)訂單頭5.1.2

簡(jiǎn)單查詢(xún)5.是否去重復(fù)元組All：檢出全部信息（默認(rèn)）Distinct：去掉重復(fù)信息【例5】只查詢(xún)采購(gòu)訂單的訂單日期（去掉重復(fù)）。select

distinct

單據(jù)日期from

采購(gòu)訂單頭

5.1.2

簡(jiǎn)單查詢(xún)6.TOP關(guān)鍵字限制返回行數(shù)格式：TOPn【例6】顯示前5行物料信息。select

top5*from

物料5.1.2

簡(jiǎn)單查詢(xún)6.TOP關(guān)鍵字限制返回行數(shù)格式：TOPn【例7】顯示前10%的物料信息。select

top10percent

*from

物料上機(jī)實(shí)驗(yàn)任務(wù)一實(shí)驗(yàn)5-1SQLServer數(shù)據(jù)管理1第一題：【單表查詢(xún)】1-3題1.查詢(xún)所有客戶(hù)信息。2.對(duì)員工表選擇姓名、部門(mén)，只返回結(jié)果集的前5行。3.查詢(xún)銷(xiāo)售部每個(gè)員工姓名和在本公司的工齡。8分鐘5.1.2

簡(jiǎn)單查詢(xún)where子句模糊查詢(xún)排序查詢(xún)結(jié)果分組與匯總5.1.2

簡(jiǎn)單查詢(xún)7.where子句（1）使用比較表達(dá)式：（2）使用邏輯表達(dá)式：（3）空值（null）的判斷：（4）限定數(shù)據(jù)范圍：（5）限制檢索數(shù)據(jù)范圍（離散的值）：=,>,<,<=,>=,!=,<>and,or,notisnotnull，isnullbetween…and…，notbetween…and…in，notin5.1.2

簡(jiǎn)單查詢(xún)【例8】查詢(xún)所有交貨日期為空的訂單明細(xì)。select

*

from

采購(gòu)訂單分錄where

交貨日期

is

null5.1.2

簡(jiǎn)單查詢(xún)8.模糊查詢(xún)Like子句，與通配符結(jié)合起來(lái)。通配符“%”、“_”、“[]”、“[^]”【例9】查詢(xún)所有公司名稱(chēng)中含有“天津”的供應(yīng)商的信息。select

*

from

供應(yīng)商where

供應(yīng)商名稱(chēng)

like

'%天津%'5.1.2

簡(jiǎn)單查詢(xún)8.模糊查詢(xún)Like子句，與通配符結(jié)合起來(lái)。通配符“%”、“_”、“[]”、“[^]”【例10】查詢(xún)供應(yīng)商聯(lián)系人中姓“王”，而且姓名是兩個(gè)字的供應(yīng)商信息。select

*

from

供應(yīng)商where

聯(lián)系人like

‘王_’5.1.2

簡(jiǎn)單查詢(xún)8.模糊查詢(xún)Like子句，與通配符結(jié)合起來(lái)。通配符“%”、“_”、“[]”、“[^]”【例11】查詢(xún)供應(yīng)商聯(lián)系人中含有姓“張”或姓“王”的供應(yīng)商信息。select

*

from

供應(yīng)商where

聯(lián)系人like

'[張王]%'“[]”用于指定范圍（如[a-z]）或集合（如[abcdef]）中的任何單個(gè)字符。5.1.2

簡(jiǎn)單查詢(xún)8.模糊查詢(xún)Like子句，與通配符結(jié)合起來(lái)。通配符“%”、“_”、“[]”、“[^]”【例12】查詢(xún)供應(yīng)商聯(lián)系人中不含有姓“張”或姓“王”的供應(yīng)商信息。select

*

from

供應(yīng)商where

聯(lián)系人like

'[^張王]%'通配符“[^]”用于匹配沒(méi)有在方括號(hào)中列出的字符。上機(jī)實(shí)驗(yàn)任務(wù)二實(shí)驗(yàn)5-1SQLServer數(shù)據(jù)管理1第一題：【單表查詢(xún)】4-7題4.查詢(xún)所有在銷(xiāo)售部或人事部的所有員工的信息。5.查詢(xún)薪水在2000—3000之間的銷(xiāo)售部員工的信息。6.查詢(xún)所有住址中含有“京”的男員工的姓名、電話(huà)、住址。7.查詢(xún)所有不姓“張”，不姓“李”，也不姓“王”的員工信息。15分鐘5.1.2

簡(jiǎn)單查詢(xún)where子句模糊查詢(xún)排序查詢(xún)結(jié)果分組與匯總5.1.2

簡(jiǎn)單查詢(xún)9.排序查詢(xún)結(jié)果

orderby【例13】查詢(xún)所有訂單的訂單編號(hào)和訂貨日期，并按訂貨日期由大到小的順序輸出.使用asc|desc關(guān)鍵字指定按照升序|降序排序select

采購(gòu)訂單號(hào),單據(jù)日期from

采購(gòu)訂單頭order

by

單據(jù)日期

desc5.1.2

簡(jiǎn)單查詢(xún)9.排序查詢(xún)結(jié)果

orderby【例14】查詢(xún)所有訂單的訂單編號(hào)和訂貨日期，并按訂貨日期由大到小的順序輸出；如果日期相同，則按照訂單編號(hào)由小到大排序。使用orderby子句也可以根據(jù)兩列或多列的結(jié)果進(jìn)行排序，并用逗號(hào)分隔開(kāi)不同的排序關(guān)鍵字。select

采購(gòu)訂單號(hào),單據(jù)日期from

采購(gòu)訂單頭order

by

單據(jù)日期

desc,采購(gòu)訂單號(hào)5.1.2

簡(jiǎn)單查詢(xún)10.分組與匯總（1）聚集函數(shù)：求和：SUM;平均：AVG；最大：MAX；最小：MIN；統(tǒng)計(jì)：COUNT。（2）分組匯總：Groupby（3）分組篩選：having1.集函數(shù)的NULL忽略策略？問(wèn)一問(wèn)2.如何識(shí)別分組字段，有什么小竅門(mén)？小竅門(mén)：“每個(gè)”、“按”、“各”一般是提示分組字段。除count(*)外，其它函數(shù)在對(duì)某個(gè)字段計(jì)算時(shí)均忽略該字段的NULL值。3.having與where的區(qū)別？①分組前的條件用Where，分組后的條件用Having②帶集函數(shù)的條件用Having5.1.2

簡(jiǎn)單查詢(xún)10.分組與匯總（1）聚合函數(shù)【例15】查詢(xún)最早的采購(gòu)訂單和最晚的采購(gòu)訂單的日期。select

max(單據(jù)日期)

as

最晚日期,min(單據(jù)日期)

as

最早日期from

采購(gòu)訂單頭5.1.2

簡(jiǎn)單查詢(xún)10.分組與匯總（1）聚合函數(shù)【例16】查詢(xún)最早的采購(gòu)訂單的詳細(xì)信息。select

*

from

采購(gòu)訂單頭where

單據(jù)日期=(select

min(單據(jù)日期)

from

采購(gòu)訂單頭)5.1.2

簡(jiǎn)單查詢(xún)10.分組與匯總（2）分組匯總：GROUPBY子句分組統(tǒng)計(jì)查詢(xún)【例17】統(tǒng)計(jì)每張訂單的最高訂貨數(shù)量、最低訂貨數(shù)量、平均訂貨數(shù)量和總數(shù)量。select

采購(gòu)訂單號(hào),max(訂貨數(shù)量)

as

最高訂貨數(shù)量,min(訂貨數(shù)量)

as

最低訂貨數(shù)量,avg(訂貨數(shù)量)

as

平均訂貨數(shù)量,sum(訂貨數(shù)量)

as

總訂貨數(shù)量from

采購(gòu)訂單分錄group

by

采購(gòu)訂單號(hào)5.1.2

簡(jiǎn)單查詢(xún)10.分組與匯總（2）分組匯總：GROUPBY子句分組統(tǒng)計(jì)查詢(xún)【例18】統(tǒng)計(jì)每張訂單的最高訂貨數(shù)量、最低訂貨數(shù)量、平均訂貨數(shù)量和總數(shù)量，交貨日期早于2014年的不參與統(tǒng)計(jì)。select

采購(gòu)訂單號(hào),max(訂貨數(shù)量)

as

最高訂貨數(shù)量,min(訂貨數(shù)量)

as

最低訂貨數(shù)量,avg(訂貨數(shù)量)

as

平均訂貨數(shù)量,sum(訂貨數(shù)量)

as

總訂貨數(shù)量from

采購(gòu)訂單分錄where

year(交貨日期)>2014group

by

采購(gòu)訂單號(hào)5.1.2

簡(jiǎn)單查詢(xún)10.分組與匯總（3）分組篩選：HAVING子句限定查詢(xún)【例19】統(tǒng)計(jì)每張訂單的最高訂貨數(shù)量、最低訂貨數(shù)量、平均訂貨數(shù)量和總數(shù)量，并輸出平均數(shù)量大于50的信息。select

采購(gòu)訂單號(hào),max(訂貨數(shù)量)

as

最高訂貨數(shù)量,min(訂貨數(shù)量)

as

最低訂貨數(shù)量,avg(訂貨數(shù)量)

as

平均訂貨數(shù)量,sum(訂貨數(shù)量)

as

總訂貨數(shù)量from

采購(gòu)訂單分錄group

by

采購(gòu)訂單號(hào)havingavg(訂貨數(shù)量)>505.1.2

簡(jiǎn)單查詢(xún)10.分組與匯總（3）分組篩選：HAVING子句限定查詢(xún)WHERE和HAVING的區(qū)別：作用的對(duì)象不同，WHERE子句作用于數(shù)據(jù)源，從中選擇滿(mǎn)足條件的元組；HAVING短語(yǔ)作用于結(jié)果集的分組中選擇滿(mǎn)足條件的組，HAVING短語(yǔ)必須與GROUPBY子句合用，不能單獨(dú)使用。能否寫(xiě)成：SELECT采購(gòu)訂單號(hào),MAX(訂貨數(shù)量),MIN(訂貨數(shù)量),AVG(訂貨數(shù)量),SUM(訂貨數(shù)量)FROM采購(gòu)訂單分錄

GroupBy采購(gòu)訂單號(hào)WHEREAVG(訂貨數(shù)量)>50×實(shí)驗(yàn)5-1SQLServer數(shù)據(jù)管理1第一題：【單表查詢(xún)】8-9題8.統(tǒng)計(jì)各部門(mén)薪水在4000以上的員工的人數(shù)。9.統(tǒng)計(jì)平均薪水大于5000的部門(mén)及平均薪水，并按平均薪水從高到低排序。上機(jī)實(shí)驗(yàn)任務(wù)三10分鐘1中場(chǎng)小結(jié)查詢(xún)指定列查詢(xún)所有列使用運(yùn)算列改變列標(biāo)題顯示是否去重復(fù)元組TOP關(guān)鍵字限制返回行數(shù)where子句模糊查詢(xún)排序查詢(xún)結(jié)果分組與匯總5.1.3連接查詢(xún)5.1.3

連接查詢(xún)多表連接實(shí)際上就是實(shí)現(xiàn)如何使用一個(gè)表中的數(shù)據(jù)來(lái)選擇另一個(gè)表中的行。而連接條件則主要通過(guò)以下方法定義兩個(gè)表在查詢(xún)中的關(guān)聯(lián)方式：（1）指定每個(gè)表中要用于連接的列。典型的連接條件在一個(gè)表中的指定外鍵，在另一個(gè)表中指定與其關(guān)聯(lián)的鍵。（2）指定比較各列的值時(shí)要使用的比較運(yùn)算符（=、<>等）。5.1.3

連接查詢(xún)表的連接的實(shí)現(xiàn)可以通過(guò)兩種方法：利用SELECT語(yǔ)句的WHERE子句；在FROM子句中使用Join(InnerJoin、CrossJoin、rightOuterJoin、LeftOuterJoin、FullOuterJoin等)關(guān)鍵字。5.1.3

連接查詢(xún)以下三種連接方式：內(nèi)連接外連接左外連接右外連接全外連接3.交叉連接連接類(lèi)型關(guān)鍵字交叉連接CROSSJOIN內(nèi)連接（自連接）INNERJOIN外連接左外連接LEFTOUTERJOIN右外連接RIGHTOUTERJOIN全外連接FULLOUTERJOIN5.1.3

連接查詢(xún)一、內(nèi)連接格式1：from表名1innerjoin表名2on

連接表達(dá)式格式2：from表名1，表名2where

連接表達(dá)式建議使用方式5.1.3

連接查詢(xún)【例1】查詢(xún)所有訂單的物料代碼、物料名稱(chēng)和訂貨數(shù)量。select

物料.物料代碼,物料名稱(chēng),訂貨數(shù)量from

采購(gòu)訂單分錄inner

join

物料on

采購(gòu)訂單分錄.物料代碼=物料.物料代碼select

物料.物料代碼,物料名稱(chēng),訂貨數(shù)量from

采購(gòu)訂單分錄,物料where

采購(gòu)訂單分錄.物料代碼=物料.物料代碼5.1.3

連接查詢(xún)【例2】從訂單分錄表中查詢(xún)單筆交易金額大于200的供應(yīng)商信息。select

distinct

B.*from

采購(gòu)訂單頭

as

Ainner

join

供應(yīng)商

as

B

on

A.供應(yīng)商代碼=B.供應(yīng)商代碼inner

join

采購(gòu)訂單分錄

as

C

on

A.采購(gòu)訂單號(hào)

=

C.采購(gòu)訂單號(hào)

and

C.金額>200實(shí)驗(yàn)5-1SQLServer數(shù)據(jù)管理1第二題：【連接查詢(xún)】1-2題1.查詢(xún)各銷(xiāo)售訂單的信息，包括客戶(hù)號(hào)、客戶(hù)名稱(chēng)、訂單編號(hào)、訂單金額、訂貨日期。2.查詢(xún)銷(xiāo)售硬盤(pán)的客戶(hù)名稱(chēng)。上機(jī)實(shí)驗(yàn)任務(wù)四10分鐘5.1.3

連接查詢(xún)二、外連接左外連接右外連接全外連接5.1.3

連接查詢(xún)1.左外連接格式：from表名1leftouterjoin表名2on

連接表達(dá)式如果：表1沒(méi)形成連接的元組，表2列為NULL【例3】查詢(xún)所有供應(yīng)商的訂單信息。select供應(yīng)商.供應(yīng)商名稱(chēng),采購(gòu)訂單頭.*from

供應(yīng)商left

outer

join

采購(gòu)訂單頭on

采購(gòu)訂單頭.供應(yīng)商代碼

=

供應(yīng)商.供應(yīng)商代碼5.1.3

連接查詢(xún)2.右外連接格式：from表名1rightouter