03二月20231第7章操縱表中的數(shù)據(jù)

數(shù)據(jù)更新

數(shù)據(jù)查詢

高級檢索技術(shù)

小結(jié)

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

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

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

或INSERTINTOpublishers(pub_name,pub_id)VALUES（‘WORLDPress’，‘9975’)03二月20238使用INSERT語句插入數(shù)據(jù)時，需要注意以下幾點：(1)對于字符型和日期型數(shù)據(jù)，插入時要用單引號括起來。如'李明'、'2003/3/28'等。(2)可以插入部分表的數(shù)據(jù)，也就是可以為每一行的指定部分列插入數(shù)據(jù)。在插入部分列數(shù)據(jù)時，應(yīng)該注意下面兩個問題：①應(yīng)在INSERT語句中使用字段列表（即指定要插入數(shù)據(jù)的列名）。②在VALUES子句中，列出與列名對應(yīng)的數(shù)據(jù)。且列名的順序與數(shù)據(jù)的順序應(yīng)完全對應(yīng)。(3)對于具有IDENTITY屬性的字段，應(yīng)當(dāng)在值列表中跳過。例如，當(dāng)?shù)谌齻€字段具有IDENTITY屬性時，值列表必須寫成(值1,值2,值4,…)。在默認情況下，不能把數(shù)據(jù)直接插入一個具有IDENTITY屬性的字段。如果偶然從表中刪除了一行記錄，或在IDENTITY屬性的字段值中存在著跳躍，也可以在該字段中設(shè)置一個指定的值。但必須首先用SET語句設(shè)置IDENTITY_INSERT選項，然后才能在IDENTITY字段中插入一個指定的值。03二月202392.修改數(shù)據(jù)修改操作不增減表中的記錄，而是更改現(xiàn)有記錄的字段值。可以使用UPDATE語句修改（更新）表中已經(jīng)存在的數(shù)據(jù)。既可以對整個表的某個或某些字段進行修改，也可以根據(jù)條件針對特定記錄修改字段的值。在UPDATE語句中，使用WHERE子句指定要修改的行，使用SET子句給出新的數(shù)據(jù)。新數(shù)據(jù)既可以是常量，也可以是指定的表達式。命令格式：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子句，則會修改表中每一行數(shù)據(jù)的作者的名字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【例】將員工表中總經(jīng)理工資加10%，其他人工資加5%UPDATE員工

SET基本工資=基本工資+基本工資*0.1WHERE職務(wù)='總經(jīng)理'UPDATE員工

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

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

updatediscountssetdiscount=discount（1+0.10）

WHERElowqty>=10003二月2023163.刪除數(shù)據(jù)當(dāng)數(shù)據(jù)庫表中的數(shù)據(jù)不需要的時候或無意義，可以刪除。一般情況下，使用DELETE語句刪除數(shù)據(jù)。DELETE語句可以從一個表中刪除一行或多行數(shù)據(jù)?！靖袷揭弧?/p>

DELETE[FROM]{

table_name|view_name

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

DELETEsales

WHERE

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

如果在DELETE語句中，沒有指定WHERE語句，那么就將表中所有的記錄全部刪除。即DELETEsales語句將刪除sales表中的全部記錄?！纠纭縿h除沒有員工的部門記錄

DELETEFROM部門

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

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

【例如】刪除所有sales記錄

TRUNCATETABLEsales因為TRUNCATETABLE操作是不進行日志記錄的，所以建議在TRUNCATETABLE語句之前用先對數(shù)據(jù)庫作備份。03二月2023214.使用企業(yè)管理器來管理表格數(shù)據(jù)選定指定的表格，右鍵“打開表”：“返回所有行”：打開表后，返回表中的所有數(shù)據(jù)；“返回首行”：打開表后，返回表中指定的前n行數(shù)據(jù)“查詢”：在彈出窗口上利用圖形化方式或直接書寫SQL語句進行相應(yīng)的查詢。03二月202322二、數(shù)據(jù)查詢查詢是數(shù)據(jù)庫的主要操作之一，也是SQL語句最主要的功能。查詢數(shù)據(jù)使用SELECT語句。數(shù)據(jù)查詢是從數(shù)據(jù)庫存儲的數(shù)據(jù)中根據(jù)用戶的需求獲得所數(shù)據(jù)。數(shù)據(jù)查詢技術(shù)就是提取數(shù)據(jù)的各種可行的方法。SELECT語句的作用是讓數(shù)據(jù)庫服務(wù)器根據(jù)客戶端的要求搜尋出用戶所需要的信息資料，并按用戶規(guī)定的格式進行整理后返回給客戶端。SELECT語句1.單表簡單查詢2.使用文字串3.改變列標(biāo)題4.數(shù)據(jù)運算5.條件查詢03二月202323其中：SELECT子句：用于指定選擇的列或行及其限定；INTO子句：用于將查詢結(jié)果集存儲到一個新的數(shù)據(jù)庫表中；FROM子句：用于指出所查詢的表名以及各表之間的邏輯關(guān)系；WHERE子句：用于指定對記錄的過濾條件；GROUPBY子句：用于對查詢到的記錄進行分組；HAVING子句：用于指定分組統(tǒng)計條件，要與GROUPBY子句一起使用；ORDERBY子句：用于對查詢到的記錄進行排序處理。在這些子句中，只有SELECT子句和FROM子句是必選項，其他子句均為可選項?！靖袷健吭撁钪?，只有SELECT<查詢輸出項>和FROM<數(shù)據(jù)源>子句是必選項。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數(shù)據(jù)庫的publishers表中檢索所有的出版商代號、出版商的名字以及國籍USEpubsSELECTpub_id,pub_name,countryFROMpublishersGO（1）SELECT語句的常規(guī)使用方式

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

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

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

TOPn表示返回最前面的n行，n表示返回的行數(shù)。

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

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

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

03二月202343⑴

使用算術(shù)表達式

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

表達式

算術(shù)操作符

表達式

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

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

NOT、AND、OR。在T-SQL中邏輯表達式共有3種可能的結(jié)果值，分別是TRUE，F(xiàn)ALSE和UNKOWN。UNKOWN是由值為NULL的數(shù)據(jù)參與邏輯運算得出的結(jié)果。

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

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

使用BETWEEN關(guān)鍵字：范圍檢查使用BETWEEN關(guān)鍵字可以更方便地限制查詢數(shù)據(jù)的范圍。語法格式為：

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

使用IN關(guān)鍵字：集合運算符語法格式為：表達式

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

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

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

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

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關(guān)鍵字，這些語句可以使用下面的語句代替：

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

⑸

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

表達式

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

LIKE子句在大多數(shù)情況下會與通配符配合使用。SQLServer提供了以下4種通配符供用戶靈活實現(xiàn)復(fù)雜的查詢條件。通配符含義%（百分號）表示從0~n個任意字符。_（下劃線）表示單個的任意字符。[]（封閉方括號）表示方括號里列出的任意一個字符。[^]任意一個沒有在方括號里列出的字符?！纠渴褂猛ㄅ浞趩T工表中查詢職務(wù)有'經(jīng)理'字樣以外的員工信息SELECT*FROM員工WHERE職務(wù)NOTLIKE'%經(jīng)理%'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【例】在“學(xué)生信息”表中，查詢借書證號第一位數(shù)字不是3～5的學(xué)生。

SELECT*FROM學(xué)生信息

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

SELECT*FROM學(xué)生信息

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

SELECT*FROM學(xué)生信息

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

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

FROM圖書信息

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

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

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

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

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

SELECTcolFROMXWHEREcollike't%%'

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

在T-SQL中，允許與統(tǒng)計函數(shù)如count()，sum()和avg()一起使用DISTINCT關(guān)鍵字來處理列或表達式中不同的值?！纠坎樵儌}儲的貨物種類。USEpubsSELECTCOUNT(DISTINCTstor_id)FROMsalesGO

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

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

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

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

FROM圖書信息

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

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

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匯總數(shù)據(jù)COMPUTEBY子句得以用同一SELECT語句既查看明細行，又查看匯總行。可以計算子組的匯總值，也可以計算整個結(jié)果集的匯總值。COMPUTE子句需要下列信息：可選的BY關(guān)鍵字，該關(guān)鍵字可按對一列計算指定的行聚合。行聚合函數(shù)名稱；例如，SUM、AVG、MIN、MAX或COUNT。要對其執(zhí)行行聚合函數(shù)的列。說明：在COMPUTE或COMPUTEBY子句中，不能包含ntext、text

或image

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

(SELECT*FROMauthorsWHEREauthors.au_id=titleauthor.au_idANDstate='CA')ORDERBYtitle_idGO03二月202398在嵌套查詢中使用NOTEXISTS關(guān)鍵字【例】查詢非來自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關(guān)鍵字03二月2023100在嵌套查詢中使用NOTIN關(guān)鍵字【例】查詢非來自CA州的作家的全部作品和作家的代號USEpubsSELECTtitle_id,au_idFROMtitleauthorWHEREau_id

NOTIN

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

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

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

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

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

--加＃在當(dāng)前庫可查詢其臨時表

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

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

USEpubsSELECTstor_name,cityFROMstores

ORDERBY1DESCGO03二月2023107也可以根據(jù)兩列或多列的結(jié)果進行排序，只要用逗號分隔開不同的排序關(guān)鍵字就可以了?！纠坎樵僼itles中各類書的書號、價格、年銷售量和銷售利潤，并用年銷售量和書價進行升序排列。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條記錄定價值相同的后續(xù)記錄。所使用的查詢語句為:SELECTTOP4WITHTIES

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

FROM圖書信息

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