03二月20231第7章操縱表中的數據

數據更新

數據查詢

高級檢索技術

小結

習題03二月20232一、數據更新對數據庫的維護更新操作是T-SQL的重要功能，數據更新是為了使數據庫所反映的狀態能及時地與當前最新的狀態一致。更新操作分為插入、修改和刪除。1.插入數據2.修改數據3.刪除數據4.使用企業管理器來管理表格數據03二月202331.插入數據插入是指將一條或若干條記錄插入到表中的操作。在表中插入數據，應該使用INSERT語句。在該語句中，包括了兩個子句，即INSERT子句和VALUES子句。使用INSERT語句插入新記錄INSERT語句用于向表中添加一行新記錄，其基本語法格式為

INSERT[INTO]table_name[(column_list)]VALUES(expression)其中，table_name用來接收數據的表（目標表）的名稱。如果目標表不是當前數據庫中的表，則應當使用“數據庫名.擁有者.表名”的完整格式來描述。03二月202341、給插入記錄的所有字段添加數據向表中插入數據時，允許省略INSERT語句中的列清單。【例】向表中插入一行數據USEpubsINSERTpublishersVALUES('9994','SANCO','WASHINGTON','WA','USA')GO03二月20235如果寫錯了pub_name和city的位置，如：USEpubsINSERTpublishersVALUES('9995','WASHINGTON','SANCO','WA','USA')GO這個語句依然執行成功。服務器不會找到這個錯誤。03二月202362、給插入記錄的部分字段添加數據向表中插入數據時要注意，字符數據和日期數據要使用引號引起來。【例】向表中插入一行數據USEpubsINSERTpublishers(pub_id,pub_name,city,state)VALUES('9993','SANCO','WASHINGTON','WA')GO03二月20237【例】向表中插入部分列數據。

INSERTINTOpublishers(pub_id,pub_name)VALUES(‘9975’,’WORLDPress’)

或INSERTINTOpublishers(pub_name,pub_id)VALUES（‘WORLDPress’，‘9975’)03二月20238使用INSERT語句插入數據時，需要注意以下幾點：(1)對于字符型和日期型數據，插入時要用單引號括起來。如'李明'、'2003/3/28'等。(2)可以插入部分表的數據，也就是可以為每一行的指定部分列插入數據。在插入部分列數據時，應該注意下面兩個問題：①應在INSERT語句中使用字段列表（即指定要插入數據的列名）。②在VALUES子句中，列出與列名對應的數據。且列名的順序與數據的順序應完全對應。(3)對于具有IDENTITY屬性的字段，應當在值列表中跳過。例如，當第三個字段具有IDENTITY屬性時，值列表必須寫成(值1,值2,值4,…)。在默認情況下，不能把數據直接插入一個具有IDENTITY屬性的字段。如果偶然從表中刪除了一行記錄，或在IDENTITY屬性的字段值中存在著跳躍，也可以在該字段中設置一個指定的值。但必須首先用SET語句設置IDENTITY_INSERT選項，然后才能在IDENTITY字段中插入一個指定的值。03二月202392.修改數據修改操作不增減表中的記錄，而是更改現有記錄的字段值。可以使用UPDATE語句修改（更新）表中已經存在的數據。既可以對整個表的某個或某些字段進行修改，也可以根據條件針對特定記錄修改字段的值。在UPDATE語句中，使用WHERE子句指定要修改的行，使用SET子句給出新的數據。新數據既可以是常量，也可以是指定的表達式。命令格式：UPDATEtable_nameSET{column_name={expression|DEFAULT|NULL}|@variable=expression|@variable=column_name=expression}[,...n][FROM{<table_source>}[,...n]][WHERE<search_condition>]03二月202310【例】修改代號為172-32-1176的作者的名字為JOEUSEpubsUPDATEauthorsSETau_lname='JOE'WHEREau_id='172-32-1176'GO修改前修改后03二月202311【例】如果不加上where子句，則會修改表中每一行數據的作者的名字au_lname為JOEUSEpubsUPDATEauthorsSETau_lname='JOE'GO修改前修改后03二月202312【例】修改authors表中指定的一行的每一列的值USEpubsUPDATEauthorsSETau_lname='Black',au_fname='ANN',phone='408496-7223',address='22ClevelandAv.#14',city='Oakland',state='CA',zip='94025',contract='0'WHEREau_id='172-32-1176'GO修改前修改后03二月202313【例】USEpubsUPDATEauthorsSETzip=zip+2GO03二月202314【例】將員工表中的每人工資增加5%UPDATE員工

SET基本工資=基本工資+基本工資*0.05【例】將員工表中總經理工資加10%，其他人工資加5%UPDATE員工

SET基本工資=基本工資+基本工資*0.1WHERE職務='總經理'UPDATE員工

SET基本工資=基本工資+基本工資*0.05WHERE職務<>'總經理'【例】如果員工在銷售表中有銷售記錄，將員工表中該員工的工資增加5%UPDATE員工

SET基本工資=基本工資+基本工資*0.05WHERE員工.編號IN(SELECT編號FROM銷售)03二月202315【例】在discounts表中，為了給每一個訂購數量在100以上的客戶增加百分之十的回扣，修改表中的數據為：

updatediscountssetdiscount=discount（1+0.10）

WHERElowqty>=10003二月2023163.刪除數據當數據庫表中的數據不需要的時候或無意義，可以刪除。一般情況下，使用DELETE語句刪除數據。DELETE語句可以從一個表中刪除一行或多行數據。【格式一】

DELETE[FROM]{

table_name|view_name

}[FROM{<table_source>}][WHERE<search_condition>]WHERE子句給出刪除數據所必須滿足的條件，省略WHERE子句時將刪除所有數據，但保留表的結構，此時表成為沒有記錄的空表。03二月202317【例】刪除所有來自CA州的作家USEpubsDELETEFROMauthorsWHEREstate='CA'GO【例】刪除表中所有數據，剩下的只有表格的定義USEpubsDELETEFROMauthorsGO03二月202318在DELETE語句中，如果使用了WHERE語句，那么就從指定的表中刪除滿足WHERE子句條件的數據行。【例】刪除sales表中3年前的銷售記錄

DELETEsales

WHERE

DATEDIFF(YEAR,ord_date,GETDATE())>=3

如果在DELETE語句中，沒有指定WHERE語句，那么就將表中所有的記錄全部刪除。即DELETEsales語句將刪除sales表中的全部記錄。【例如】刪除沒有員工的部門記錄

DELETEFROM部門

WHERE部門號NOTIN(SELECT部門號FROM員工)03二月202319DATEDIFF函數的用法【示例】確定在pubs數據庫中標題發布日期和當前日期間的天數。USEpubsGOSELECTDATEDIFF(day,pubdate,getdate())ASno_of_daysFROMtitlesGO03二月202320【格式二】TRUNCATETABLE語句用于清空表中所有數據：

TRUNCATETABLEtable_name從效果看，TRUNCATETABLEtable_name與不帶WHERE子句的DELETE語句相同，都可以刪除表中全部記錄。二者的相同點是都不刪除表的結構、索引、約束、規則和默認，如果希望刪除表的定義，必須使用DROPTABLE語句。TRUNCATETABLE語句與DELETE語句的區別是，使用TRUNCATETABLE語句不記錄日志；而DELETE語句每刪除一條記錄，都要記入日志。因此TRUNCATETABLE語句的執行速度更快，占用更少的系統資源和事務日志資源。

【例如】刪除所有sales記錄

TRUNCATETABLEsales因為TRUNCATETABLE操作是不進行日志記錄的，所以建議在TRUNCATETABLE語句之前用先對數據庫作備份。03二月2023214.使用企業管理器來管理表格數據選定指定的表格，右鍵“打開表”：“返回所有行”：打開表后，返回表中的所有數據；“返回首行”：打開表后，返回表中指定的前n行數據“查詢”：在彈出窗口上利用圖形化方式或直接書寫SQL語句進行相應的查詢。03二月202322二、數據查詢查詢是數據庫的主要操作之一，也是SQL語句最主要的功能。查詢數據使用SELECT語句。數據查詢是從數據庫存儲的數據中根據用戶的需求獲得所數據。數據查詢技術就是提取數據的各種可行的方法。SELECT語句的作用是讓數據庫服務器根據客戶端的要求搜尋出用戶所需要的信息資料，并按用戶規定的格式進行整理后返回給客戶端。SELECT語句1.單表簡單查詢2.使用文字串3.改變列標題4.數據運算5.條件查詢03二月202323其中：SELECT子句：用于指定選擇的列或行及其限定；INTO子句：用于將查詢結果集存儲到一個新的數據庫表中；FROM子句：用于指出所查詢的表名以及各表之間的邏輯關系；WHERE子句：用于指定對記錄的過濾條件；GROUPBY子句：用于對查詢到的記錄進行分組；HAVING子句：用于指定分組統計條件，要與GROUPBY子句一起使用；ORDERBY子句：用于對查詢到的記錄進行排序處理。在這些子句中，只有SELECT子句和FROM子句是必選項，其他子句均為可選項。【格式】該命令中，只有SELECT<查詢輸出項>和FROM<數據源>子句是必選項。SELECT<select_list>/*指定所檢索的列名*/[INTO<new_table>]/*指定所要生成新表的名稱*/FROM<table_cource>/*指定要檢索的表名*/[WHERE<search_condition>]/*指定要檢索的行*/[GROUPBY<group_by_expression>]/*分組檢索子句*/[HAVING<search_condition>]/*指定分組子句的條件*/[ORDERBY<order_expression>[ASC|DESC]]/*排序子句，升序|降序*/03二月2023241.單表簡單查詢【例】從pubs數據庫的publishers表中檢索所有的出版商代號、出版商的名字以及國籍USEpubsSELECTpub_id,pub_name,countryFROMpublishersGO（1）SELECT語句的常規使用方式

SELECT列名1[,列名2,…列名n]FROM表名03二月202325（2）用“*”表示表中所有的列語法格式如下：

SELECT*FROM表名服務器會按用戶創建表格時聲明列的順序來顯示所有的列。【例】檢索表中的全部信息，即全部列和全部行。USEpubsUSEpubsSELECT*或SELECT*FROMjobsFROMtitleauthorGOGO03二月202326（3）使用SELECT語句進行無數據源檢索所謂無數源檢索就是使用SELECT語句來檢索不在表中的數據。（a）使用SELECT語句查看常量【例】SELECT'sqlserver6.5'SELECT'sqlserver7.0'GO（b）使用SELECT語句查看全局變量。【例】查詢本地SQLServer服務器的版本信息SELECT@@version【例】查詢本地SQLServer服務器使用的語言SELECT@@language03二月202327（4）使用TOP關鍵字SQLServer提供了TOP關鍵字，讓用戶指定返回前面一定數量的數據。使用TOP關鍵字查詢可以大大減少查詢花費的時間。語法如下：

SELECT[TOPn|TOPnPERCENT]列名1[,列名2,…列名n]FROM表名其中；

TOPn表示返回最前面的n行，n表示返回的行數。

TOPnPERCENT表示返回的前面的n%行，n取值范圍0.001到99.99。【例】從northwind數據庫的customers表中返回前面10行數據。SELECTTOP10*FROMcustomers【例】從northwind數據庫中的customers表中返回前10%的數據。SELECTTOP10percent*FROMcustomers03二月202328【例】按從小到大輸出單筆銷售額前五名銷售數據的日期、商品號、數量、金額SELECTTOP5日期,商品號,數量,金額FROM銷售ORDERBY金額DESCGO03二月202329（5）使用DISTINCT關鍵字使用DISTINCT關鍵字就能夠從返回的結果數據集合中刪除重復的行，使返回的結果更簡潔。在使用DISTINCT關鍵字后，如果表中有多個為NULL的數據，服務器會把這些數據視為相等。【例】從pubs數據庫的publishers表中搜索出所有國家的名字USEpubsSELECTcountryFROMpublishersGO03二月202330【例】從pubs數據庫的publishers表中搜索出所有國家的名字USEpubsSELECTDISTINCTcountryFROMpublishersGO03二月202331【例】從pubs數據庫的publishers（出版商）表中檢索所有的出版商的名字USEpubsSELECTDISTINCTpub_nameFROMpublishersGO03二月202332【區別】03二月202333【區別】03二月202334【演示】再添加orderby語句03二月202335（6）使用計算列在進行數據查詢時，經常需要對查詢到的數據進行再次計算處理。T-SQL允許直接在SELECT語句中使用計算列。計算列并不存在于表格所存儲的數據中，它是通過對某些列的數據進行演算得來的結果。【例】將每本書的銷售價格降低30%。USEpubsSELECTtitle_id,type,price,price-price*0.3FROMtitlesGO03二月202336【例】使用字符串連接符（+）來連接作者的姓和名，以及作者所居住的州和城市的名稱。USEpubsSELECTau_lname+'.'+au_fname,city+','+state

FROMauthorsGO03二月2023372.使用文字串為了增加SELECT語句檢索結果的可讀性，可以通過在SELECT關鍵字后面增加文字串，文字串使用單引號引起來。【例】使用文字串USEpubsSELECT'Thephonenumberof',au_lname,'is',phoneFROMauthorsGO03二月2023383.改變列標題在默認情況下，在數據檢索結果中所顯示出來的列標題就是在創建表時使用的列名。但是，顯示的列標題也是可以改變的。T-SQL提供了在SELECT語句中操作列名的方法。用戶可以根據實際需要對查詢數據的列標題進行修改，或者為沒有標題的列加上臨時的標題。對列名進行操作有3種方式：在列表達式后面給出列名；用“=”來連接列表達式；用AS關鍵字來連接列表達式和指定的列名。03二月202339【例】改變列標題USEpubsSELECTtitle_id'圖書代號',price'原價',price-price*0.3'現價'FROMtitlesSELECT'圖書代號'=title_id,'原價'=price,'現價'=price-price*0.3FROMtitlesSELECTtitle_idas'圖書代號',priceas'原價',price-price*0.3as'現價'FROMtitlesGO執行這三句的返回結果是相同的。①采用符合ANSI規則的標準方法，即在列表達式后面給出列名；②使用SQL支持的“=”來連接列表達式，其形式是：新標題=列名；③使用AS關鍵字來連接列表達式和指定的列名，其形式是：列名AS新標題。由于AS關鍵字是可以省略的，因此改變列標題也可以寫成：列名新標題。03二月2023404.數據運算數據運算就是指對檢索的數據進行各種運算，也就是說，可以在SELECT關鍵字后面列出的列項中使用各種運算符和函數。這些運算符和函數包括算術運算符、數學函數、字符串函數、日期和時間函數、系統函數等。算術運算符可以用在各種數字列上，這些列的數據類型是int、smallint、tinyint、float、real、money、smallmoney。這些算術運算符包括+、-、*、/和%。【例】使用+運算符，為每一本書增加2元USEpubsSELECTbookTitle=title,bookType=type,oldPrice=price,newPrice=price+2FROMtitlesGO03二月202341數學函數返回通常需要運算的數據的數值。【例】在檢索語句中使用數學函數SELECTPI=PI()SELECTSIN=SIN(PI()/2.0)SELECTCOS=COS(PI()/4.0)SELECTTAN=TAN(PI()/4.0)SELECTEXP=EXP(100)03二月2023425.條件查詢在SELECT語句中，WHERE子句指定要檢索的數據行。在WHERE子句中，可以使用的搜索條件包括比較運算符、范圍、列表、字符串匹配、合并以及取反等。使用WHERE子句的目的是從表格的數據集中篩選出符合條件的行。WHERE子句的語法允許在列名稱和列值之間使用比較運算符。不同的比較運算符可以用來檢索不同的數據行。使用語法：

SELECTcolum_name[,...n]FROMtable_nameWHERE<search_condition>其中：<search_condition>定義查詢條件，篩選返回的數據行。

03二月202343⑴

使用算術表達式

使用算術表達式作為搜索條件的一般表達形式是：

表達式

算術操作符

表達式

（表達式為：常量、變量和列表達式的任意有效組合。）WHERE子句中允許使用的算術操作符03二月202344【例】從employee表中檢索出在1993年12月31日之前已經雇傭的雇員USEpubsSELECT*FROMemployeeWHEREhire_date<'1993-12-31'GO03二月202345【例】查詢pubs庫的titles表中，價格打了8折后仍大于12美元的書號、種類以及原價。USEpubsSELECTtitle_idAS書號,typeAS種類,priceAS原價FROMtitlesWHEREprice-price*0.2>12GO03二月202346⑵

使用邏輯表達式在WHERE子句中可以使用邏輯運算符把若干個搜索條件合并起來，組成復雜的復合條件。在T-SQL里的邏輯表達式共有3個，分別是：

NOT、AND、OR。在T-SQL中邏輯表達式共有3種可能的結果值，分別是TRUE，FALSE和UNKOWN。UNKOWN是由值為NULL的數據參與邏輯運算得出的結果。

【例】查詢所有在美國加利福尼亞州的出版社USEpubsSELECTpub_idAS出版社代號,pub_nameAS出版社名稱,cityAS城市,stateAS州,countryAS國家FROMpublishersWHEREcountry='usa‘ANDstate='ca'GO03二月202347【例】通過使用一個復合檢索條件，檢索姓是White或名的首字母是A的作者信息USEpubsSELECTau_lname,au_fnameFROMauthorsWHEREau_lname=‘White‘ORau_fnameLIKE‘A%‘【例】查詢部門號為‘50’的女職工信息SELECT*

FROM員工WHERE部門號='50'AND性別='女'03二月202348⑶

使用BETWEEN關鍵字：范圍檢查使用BETWEEN關鍵字可以更方便地限制查詢數據的范圍。語法格式為：

表達式[NOT]BETWEEN表達式1AND表達式2字段值位于給定范圍的記錄使條件為真。范圍包含兩個端點。【例】查詢價格在15和20美元之間的書的書號、種類和價格。USEpubsSELECTtitle_idAS書號,typeAS種類,priceAS原價FROMtitlesWHEREpriceBETWEEN$15AND$20GO03二月202349【例】查詢價格在15和20美元之間的書的書號、種類和價格WHEREpriceBETWEEN$15AND$20GO相當于：WHEREprice>=$15ANDprice<=$20【例】查詢價格低于15和高于20美元的書的書號、種類和價格WHEREpriceNOTBETWEEN$15AND$20GO相當于：WHEREprice<$15ORprice>$2003二月20235003二月202351【例】在銷售表中查詢2005年5月銷售的商品號和數量、金額SELECT日期,商品號,數量,金額FROM銷售WHERE日期BETWEEN'2005/05/01'AND'2005/05/31'03二月202352⑷

使用IN關鍵字：集合運算符語法格式為：表達式

[NOT]IN(表達式1,表達式2[,…表達式n])

【例】在員工表中查詢職務為'總經理','經理','副經理'的員工信息SELECT*FROM員工WHERE職務IN('總經理','經理','副經理')03二月202353【例】查詢所有居住在KS、CA、MI或IN州的作家

USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstateIN('CA','KS','MI','IN')

GO如果不使用IN關鍵字，這些語句可以使用下面的語句代替：

USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstate='CA'ORstate='KS'ORstate='MI'ORstate='IN'GO03二月202354【例】查詢所有不在KS、CA、MI或IN州居住的作家

USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstateNOTIN('CA','KS','MI','IN')

GO如果不使用NOTIN關鍵字，這些語句可以使用下面的語句代替：

USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstate<>'CA'ORstate<>'KS'ORstate<>'MI'ORstate<>'IN'GO03二月202355

⑸

LIKE子句：匹配檢查LIKE關鍵字用于檢索與特定字符串匹配的數據，字符型字段使用。LIKE關鍵字后面跟一個列值的一部分而不是一個完整的列值。語法格式為：

表達式

[NOT]LIKE條件條件通常與通配符配合使用。所有通配符都只有在LIKE子句中才有意義，否則通配符會被當作普通字符處理。字段在內容符合給定通配格式的記錄使條件為真。NOT選項是求反。03二月202356LIKE關鍵字與通配符的使用①

LIKE子句在大多數情況下會與通配符配合使用。SQLServer提供了以下4種通配符供用戶靈活實現復雜的查詢條件。通配符含義%（百分號）表示從0~n個任意字符。_（下劃線）表示單個的任意字符。[]（封閉方括號）表示方括號里列出的任意一個字符。[^]任意一個沒有在方括號里列出的字符。【例】使用通配符在員工表中查詢職務有'經理'字樣以外的員工信息SELECT*FROM員工WHERE職務NOTLIKE'%經理%'03二月202357【例】查詢所有名字以D打頭的作家USEpubsSELECTau_lname+‘.’+au_fnameFROMauthorsWHEREau_fnameLIKE'D%'【例】使用6個下劃線來匹配任何姓是6個字母的作家USEpubsSELECT*FROMauthorsWHEREau_fnameLIKE‘______’03二月202358【例】查詢USEpubsSELECTau_lname,au_fname,phone,au_idFROMauthorsWHEREau_idLIKE'72_-%'03二月202359【例】在“學生信息”表中，查詢借書證號第一位數字不是3～5的學生。

SELECT*FROM學生信息

WHERE借書證號LIKE'[^3-5]%'【例】更精確的查詢USEpubsSELECTau_lname,au_fname,phone,au_idFROMauthorsWHEREau_idLIKE'72[2345]-%'03二月202360【例】精確查詢03二月202361【例】在“學生信息”表中，查詢姓趙、姓李、姓王和姓張的學生。

SELECT*FROM學生信息

WHERE姓名LIKE'[趙李王張]%'【例】在“學生信息”表中，查詢除了姓趙、姓李、姓王和姓張以外的學生。

SELECT*FROM學生信息

WHERE姓名NOTLIKE'[趙李王張]%'【例】在“圖書信息”表中，查詢計算機類的圖書名稱中有“程序設計”幾個字的信息。要求輸出字段為：圖書名稱，圖書類別，出版日期。

SELECT圖書名稱，圖書類別，出版日期

FROM圖書信息

WHERE圖書類別='計算機'AND圖書名稱LIKE'%程序設計%'03二月202362【示例】帶有匹配符的字符串必須使用引號引起來

LIKE’BR%’LIKE’Br%’LIKE’%een’LIKE’%en%’LIKE’_en’LIKE’[CK]%’LIKE’[S-V]ing’LIKE’M[^c]%’03二月202363②

LIKE關鍵字與轉義字符的使用ESCAPE用來規定轉義字符。若要查找作為字符而不是通配符的%、_等，在查詢時使用ESCAPE子句定義轉義符也可以將通配符作為普通字符匹配查詢。凡是ESCAPE子句中出現的字符都成為轉義字符，它在字符串中不再表示真實的字符，只起標記的作用。凡是在轉義字符后面出現的第一個表示通配符的字符都不再被視為通配符，而被當作普通字符處理。所有的字符都可以被當作轉義字符處理。03二月202364【例如】為了查詢某個字段中包含“%”的記錄，應當用以下WHERE子句：

WHERE具體的字段名LIKE'%!%%'ESCAPE'!'第一、三個%為通配符，中間的%為普通字符，由轉義字符“！”引出。為了查詢某個字段中“TW_”開頭的記錄，應當用以下WHERE子句：

WHERE具體的字段名LIKE'TW!_%'ESCAPE'!'03二月202365【例】假設有一個表X，列col的值如下：'[xyz]‘、'%xyz‘、'x_yz‘、'xyzw'，若在X中查找以%開頭的字符串

SELECTcolFROMXWHEREcollike't%%'

ESCAPE't'【例】設一數據庫包含名為“摘要”的字段，該字段包含文本"30%"。若要搜索在字段中任何位置包含字符串"30%"的行，語句為：WHERE摘要LIKE'%30\%%‘ESCAPE'\’03二月202366高級檢索技術1.統計查詢2.分組數據3.子查詢4.排序技術5.多表連接查詢6.聯合技術03二月2023671.統計查詢統計函數，例如AVG、SUM、COUNT、MAX、MIN、STDEV和VAR，返回整個表或幾個列或一個列的匯總數據。這些函數必須用在SELECT子句中，其返回的結果在查詢結果集中作為新列出現。函數表達式可以是下列幾種形式的任意組合：列名常量由算術運算符連接起來的函數03二月202368【例】使用統計函數USEpubsSELECT'maxPrice'=MAX(price),/*MAX函數求表達式的最大值*/'minPrice'=MIN(price),/*MIN函數求表達式的最小值*/'averagePrice'=AVG(price),/*AVG函數求數據表達式的平均值*/'stdevPrice'=STDEV(price),/*STDEV函數計算表達式的標準差*/'varPrice'=VAR(price),/*VAR函數計算表達式的方差*/'totalPrice'=SUM(price),/*SUM求函數表達式的總計*/'countOfRows'=COUNT(price)/*COUNT函數是求表中數據的數量*/FROMtitlesGO03二月202369【例】查詢titles中所有書的平均價格USEpubsSELECTAVG(price)FROMtitlesGO【例】查詢各種商業圖書的平均價格USEpubsSELECTAVG(price)'avg_price'FROMtitlesWHEREtype='business'GO03二月202370【例】統計查詢USEpubsSELECTAVG(price),MAX(price),MIN(price)FROMtitlesGO03二月202371【例】為輸出列取名的統計查詢USEpubsSELECTAVG(price)AS價格平均值,MAX(price)AS價格最大值,MIN(price)AS價格最小值FROMtitlesGO03二月202372【例】在輸出列上進行運算的查詢USEpubsSELECTSUM(price)AS價格總額,AVG(price)AS價格平均值,SUM(price+price*0.1)AS預期價格總額,AVG(price+price*0.1)AS預期價格平均值FROMtitlesGO03二月202373

在T-SQL中，允許與統計函數如count()，sum()和avg()一起使用DISTINCT關鍵字來處理列或表達式中不同的值。【例】查詢倉儲的貨物種類。USEpubsSELECTCOUNT(DISTINCTstor_id)FROMsalesGO

03二月202374若此句不用DISTINCT關鍵字，則返回結果為21。03二月2023752.分組數據一般情況下，可以根據表中的某一列進行分組，并且使用統計函數，對每一個組只能產生一個單個值。在大多數情況下使用統計函數，返回的是所有行數據的統計結果。統計函數只能產生一個單一的匯總數據。如果需要按某一字段數據的值進行分類，在分類的基礎上再進行統計計算，就需要使用GROUPBY子句了。數據分組是指通過GROUPBY子句按一定的條件對查詢到的結果進行分組，再對每一組數據統計計算。03二月2023761．GROUPBY子句GROUPBY子句的語法格式如下：

GROUPBYgroup_by_expression[,...n]group_by_expression為分組表達式，是執行分組時所依據的一個表達式，通常是一個字段名。text、ntext、image以及bit數據類型的字段不能用在分組表達式中。注：對select子句后面每一列數據除了出現在統計函數中的列以外，都必須在groupby子句中應用。

groupby子句中不支持對列設置別名，也不支持任何使用了統計函數的集合列。03二月202377【例】按書的種類分類，求出3種類型書籍的價格總和、平均價格以及各類書籍的數量。USEpubsSELECTtype,sum(price)'sum_price',avg(price)'avg_price',count(*)FROMtitlesWHEREtypein('business','mod_cook','trad_cook')GROUPBYtypeGO03二月202378執行過程：03二月20237903二月202380【例】在“圖書信息”表中，求出“計算機”、“電子”和“英語”3種類別的圖書的價格總和以及平均價格。SELECT圖書類別,SUM(定價)AS總價值,AVG(定價)AS平均價格FROM圖書信息WHERE圖書類別IN('計算機','電子','英語')GROUPBY圖書類別【例】查詢“圖書信息”表中各類圖書的數量。

SELECT圖書類別，COUNT(*)AS數量

FROM圖書信息

GROUPBY圖書類別03二月202381也可以根據多列進行分組。這時統計函數按照這些列的惟一組合來進行統計計算。【例】按書的種類和出版商代號分類，返回一個平均價格和總價格SELECTtype,pub_id,avg(price)'avg_price',sum(price)'sum_price'FROMtitlesWHEREtypein('business','trad_cook','mod_cook')GROUPBYtype,pub_idGO03二月202382步驟1、查看分組情況步驟2、查看所需的分組情況步驟3、根據多列進行分組03二月2023832．HAVING子句若要輸出滿足一定條件的分組，則需要使用having關鍵字。即當完成數據結果的查詢和統計后，可以使用HAVING子句來對查詢和統計的結果進行進一步的篩選。HAVING子句的語法格式如下：

HAVINGserach_condition注：WHERE與HAVING的主要區別是各自的作用對象不同。

WHERE是從基表或視圖中檢索滿足條件的記錄。

HAVING

是從所有的組中，檢索滿足條件的組。03二月202384【例】查詢所有價格超過10美元的書的種類和平均價格SELECTtype,avg(price)'avg_price‘FROMtitlesWHEREprice>$10GROUPBYtypeGOSELECTtype,avg(price)'avg_price‘FROMtitlesWHEREprice>10GROUPBYtypeHAVINGavg(price)>$18GO03二月202385【例】檢索銷售量超過25本書的圖書的圖書代號和銷售量SELECTtitle_id,'soldTotal‘=SUM(qty)FROMsalesGROUPBYtitle_idHAVINGSUM(qty)>25GO用COMPUTE和COMPUTEBY匯總數據COMPUTEBY子句得以用同一SELECT語句既查看明細行，又查看匯總行。可以計算子組的匯總值，也可以計算整個結果集的匯總值。COMPUTE子句需要下列信息：可選的BY關鍵字，該關鍵字可按對一列計算指定的行聚合。行聚合函數名稱；例如，SUM、AVG、MIN、MAX或COUNT。要對其執行行聚合函數的列。說明：在COMPUTE或COMPUTEBY子句中，不能包含ntext、text

或image

數據類型。COMPUTE生成的結果集COMPUTE所生成的匯總值在查詢結果中顯示為分離的結果集。包括COMPUTE子句的查詢的結果類似于控制中斷報表，即匯總值由指定的組（或稱中斷）控制的報表。可以為各組生成匯總值，也可以對同一組計算多個聚合函數。當COMPUTE帶有可選的BY子句時，符合SELECT條件的每個組都有兩個結果集：每個組的第一個結果集是明細行集，其中包含該組的選擇列表信息。每個組的第二個結果集有一行，其中包含該組的COMPUTE子句中所指定的聚合函數的小計。當COMPUTE不帶可選的BY子句時，SELECT語句有兩個結果集：每個組的第一個結果集是包含選擇列表信息的所有明細行。第二個結果集有一行，其中包含COMPUTE子句中所指定的聚合函數的合計COMPUTE用法示例1、下列SELECT語句使用簡單COMPUTE子句生成titles表中price及advance的求和總計：USEpubsSELECTtype,price,advanceFROMtitlesORDERBYtypeCOMPUTESUM(price),SUM(advance)2、下列查詢在COMPUTE子句中加入可選的BY關鍵字，以生成每個組的小計：USEpubsSELECTtype,price,advanceFROMtitlesORDERBYtypeCOMPUTESUM(price),SUM(advance)BYtype此SELECT語句的結果用12個結果集返回，六個組中的每個組都有兩個結果集。每個組的第一個結果集是一個行集，其中包含選擇列表中所請求的信息。每個組的第二個結果集包含COMPUTE子句中兩個SUM函數的小計。比較COMPUTE和GROUPBYCOMPUTE和GROUPBY之間的區別匯總如下：GROUPBY生成單個結果集。每個組都有一個只包含分組依據列和顯示該組子聚合的聚合函數的行。選擇列表只能包含分組依據列和聚合函數。COMPUTE生成多個結果集。一類結果集包含每個組的明細行，其中包含選擇列表中的表達式。另一類結果集包含組的子聚合，或SELECT語句的總聚合。選擇列表可包含除分組依據列或聚合函數之外的其它表達式。聚合函數在COMPUTE子句中指定，而不是在選擇列表中。（1）下列查詢使用GROUPBY和聚合函數該查詢將返回一個結果集，其中每個組有一行，該行中包含該組的聚合小計：USEpubsSELECTtype,SUM(price),SUM(advance)FROMtitlesGROUPBYtype（2）下列查詢在COMPUTE子句中加入可選的BY關鍵字，以生成每個組的小計：USEpubsSELECTtype,price,advanceFROMtitlesORDERBYtypeCOMPUTESUM(price),SUM(advance)BYtype03二月2023933.子查詢一條SELECT命令的查詢結果是一個表，如果將某個查詢的結果作為另外一個查詢的WHERE條件的一部分，就形成了查詢嵌套。外層的SELCT語句稱為外部查詢，內層的SELECT語句稱為子查詢。SELECT語句可以嵌套在其他許多語句中，例如SELECT、INSERT、UPDATE或DELETE等。嵌套查詢要求服務器在處理最終查詢工作之前先生成一個結果，然后根據當前的查詢結果再進一步繼續下面的查詢工作。當一個查詢依賴于另外一個查詢結果時，那么可以使用子查詢。在某些查詢中，查詢語句比較復雜不容易理解，因此為了把這些復雜的查詢語句分解成簡單的查詢語句，可以使用查詢語句。03二月202394子查詢有兩種類型：一種是只返回一個單值的子查詢，這時它可以用在一個單值可以使用的地方；返回的單個值被外部查詢的比較操作(如，=、!=、<、<=、>、>=)使用，該值可以是子查詢中使用集合函數得到的值。另外一種是返回一列值的子查詢，這時它只能用在WHERE子句中。子查詢必須使用括號括起來。返回的這個值列表被外部查詢的in、notin、any或all比較操作使用。03二月202395子查詢的使用應該滿足一定的限制條件：在比較運算符的后面的子查詢只能包含一個表達式或列名（除了使用EXISTS和IN關鍵字）。如果外查詢的WHERE字句包含一個列名，那么該列名必須與之查詢中的列兼容。ntext，text和image數據類型不允許用在子查詢的列中。DISTINCT關鍵字不能用在包含GROUPBY子句的子查詢中。在T-SQL中，還提供了以下集中與子查詢有關的運算方式：（1）<字段><比較運算符>ALL<子查詢>（2）<字段><比較運算符>ANY|SOME(<子查詢>)（3）<字段>[NOT]IN(<子查詢>)（4）[NOT]EXISTS(<子查詢>)03二月202396【例】查詢來自CA州的作家的全部作品和作家的代號USE

pubsSELECTtitle_id,au_idFROMtitleauthorWHEREau_idIN

(SELECTau_idFROMauthorsWHEREstate='CA')ORDERBYtitle_idGO嵌套子查詢的執行過程為：首先執行子查詢，子查詢得到的結果集不被顯示出來，而是傳給外部查詢，作為外部查詢的條件使用，然后執行外部查詢，并顯示查詢結果。03二月202397在嵌套查詢中使用EXISTS關鍵字【例】查詢來自CA州的作家的全部作品和作家的代號USEpubsSELECTtitle_id,au_idFROMtitleauthorWHEREEXISTS

(SELECT*FROMauthorsWHEREauthors.au_id=titleauthor.au_idANDstate='CA')ORDERBYtitle_idGO03二月202398在嵌套查詢中使用NOTEXISTS關鍵字【例】查詢非來自CA州的作家的全部作品和作家的代號USEpubsSELECTtitle_id,au_idFROMtitleauthorWHERENOTEXISTS

(SELECT*FROMauthorsWHEREauthors.au_id=titleauthor.au_idANDstate='CA')ORDERBYtitle_idGO03二月202399【例】查詢所有出版了書的作者的信息

USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREau_idIN

(SELECTau_idFROMtitleauthor)GO在嵌套查詢中使用IN關鍵字03二月2023100在嵌套查詢中使用NOTIN關鍵字【例】查詢非來自CA州的作家的全部作品和作家的代號USEpubsSELECTtitle_id,au_idFROMtitleauthorWHEREau_id

NOTIN

(SELECTau_idFROMauthorsWHEREstate='CA')ORDERBYtitle_idGOIN表示屬于，即外部查詢中用于判斷的表達式的值與子查詢返回的值列表中的一個值相等；NOTIN表示不屬于。03二月2023101將查詢結果保存到表中帶有INTO子句的SELECT語句（必須在目的數據庫內具有createtable權限），允許用戶創建一個新表并且把數據插入到新表中。這種方法不同于前面講述的那些方法。在前面的那些方法有一個共同的特點，就是在數據插入之前表已經存在。但是，使用SELECTINTO語句是在插入數據的過程中建立新表。使用SELECTINTO語句完成建立一個新表并向其中插入多條記錄。帶有INTO子句的SELECT語句的語法形式為：

SELECTSELECT_listINTOnew_table_nameFROMtable_listWHEREsearch_conditions其中，new_table_name表名就是想要創建的新表的名稱。新表中包含的字段由SELECT子句字段列表的內容來決定，新表中包含的記錄則由WHERE子句指定的條件來決定。03二月2023102【例】使用SELECTINTO語句插入數據

SELECTtitle=SUBSTRING(title,1,40),Monthly=ytd_sales/12INTOphonytableFROMtitles03二月2023103

在select語句中，使用into子句可以將查詢的結果存放到一個新建的數據表，也可保存到[tempdb]庫的臨時表中。*如果要將查詢結果存放到[tempdb]臨時表，則在臨時表名前要加“#”號。【例】select*into#李姓表from學生信息表

where姓名like'李%'--刷新當前庫的用戶表，看看有無【#李姓表】--刷新【tempdb】庫的用戶表，看看有無【#李姓表】select*from#李姓表

--加＃在當前庫可查詢其臨時表

droptable#李姓表--在當前庫可刪除[tempdb]的臨時表03二月20231044.排序技術排序技術就是使用ORDERBY子句排列查詢結果的順序。ORDERBY的結果依據SQLServer系統安裝時配置的排列規則排序。ORDERBY的語法形式如下：SELECTcolumn_name,column_name,…FROMtable_nameORDERBYcolumn_name[ASC|DESC],…其中：ABS表示升序，這是默認值；DESC是降序。*多表達式在orderby子句中的順序決定排序依據的優先順序。*不能按ntext、text或image類型的列排序03二月2023105區別：查詢pubs數據庫中的表stores中商店的名字

USEpubsUSEpubsSELECTstor_name和SELECTstor_nameFROMstoresFROMstoresGOORDERBYstor_nameDESCGO每次查詢的結果隨機按商店名降序排列03二月2023106【例】查詢pubs數據庫中的表stores中商店的名字，并按使用列所處的位置來進行排序列商店的排序情況。

USEpubsSELECTstor_name,cityFROMstores

ORDERBY1DESCGO03二月2023107也可以根據兩列或多列的結果進行排序，只要用逗號分隔開不同的排序關鍵字就可以了。【例】查詢titles中各類書的書號、價格、年銷售量和銷售利潤，并用年銷售量和書價進行升序排列。USEpubsSELECTtitle_id,price,ytd_sales,'profit'=price*ytd_salesFROMtitlesORDERBYytd_sales,priceGO03二月2023108【例】以ytd_sales升序，price降序進行排列注意：在一個查詢語句中，既可以有升序排列，也可以有降序排列。但是對于同一個列來說，只能是升序排列或降序排列。03二月2023109【例】查詢titles中各類書的銷售利潤和書號，并按照各種書的銷售利潤降序排列。

USEpubsSELECTtitle_id,'profit'=price*ytd_salesFROMtitles

ORDERBY'profit'DESCGO03二月2023110TOP或TOP…WITHTIES子句與ORDERBY子句通過在SELECT語句中使用TOP子句，可以查詢表最前面的若干條記錄。如果有兩條或多條記錄中排序字段的值相同，則只顯示其中一條記錄；如果需要將排序字段值相等的那些記錄一并顯示出來，則在SELECT語句中TOP后面添加WITHTIES即可。WITHTIES必須與TOP一起使用，而且只能與ORDERBY子句一起使用。

【例】從“圖書信息”表中查詢定價最低的前4條記錄。若第4條記錄后面有多個與第4條定價值相同的記錄，如何將其顯示出來？

(1)顯示前4條記錄的查詢語句如下：

SELECTTOP4

圖書名稱,出版社名稱,作者,定價

FROM圖書信息

ORDERBY定價

(2)顯示前4個定價最低的記錄，包括與第4條記錄定價值相同的后續記錄。所使用的查詢語句為:SELECTTOP4WITHTIES

圖書名稱,出版社名稱,作者,定價

FROM圖書信息

ORDERBY定價03二月2023111如果有兩條或多條記錄中排序字段的值相同，則只顯示其中一條記錄將排序字段值相等的那些記錄一并顯示出來所有記錄03二月2023112用戶也可以根據未曾出現在SELECT列表中的值進行排序。【例】查詢titles中各類書的銷售利潤和書號，并按照各種書的價格降序排列。USEpubsSELECTtitle_id,'profit'