第5章 表中數(shù)據(jù)的操作

SQLSERVER

應(yīng)用與開發(fā)

計算機工程技術(shù)學(xué)院（軟件學(xué)院）

任淑美二00七年九月（五）本課主要內(nèi)容在企業(yè)管理器中操作表中的數(shù)據(jù)使用SELECT語句查詢數(shù)據(jù)使用INSERT語句插入數(shù)據(jù)使用UPDATE語句更新數(shù)據(jù)使用DELETE語句刪除數(shù)據(jù)本課核心知識點SELECT語句INSERT語句UPDATE語句DELETE語句本課重點難點SELECT語句的基本結(jié)構(gòu)及其各子句的用法使用INSERT、UPDATE、DELETE語句插入、修改和刪除表中數(shù)據(jù)嵌套查詢

本課教學(xué)目的掌握如何在企業(yè)管理器中操作表中的數(shù)據(jù)掌握使用SELECT語句查詢數(shù)據(jù)的方法掌握使用INSERT語句插入數(shù)據(jù)的方法掌握使用UPDATE語句更新數(shù)據(jù)的方法掌握使用DELETE語句刪除數(shù)據(jù)的方法第5章表中數(shù)據(jù)的操作

5.1在企業(yè)管理器中操作表中的數(shù)據(jù)

表中數(shù)據(jù)的操作包括數(shù)據(jù)的查詢、插入、更新和刪除。在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫選項，右擊所要操作的表，選擇“打開表”選項，選擇“查詢”選項，出現(xiàn)查詢數(shù)據(jù)對話框。在對話框中設(shè)置各種查詢條件，還可以直接輸入Transact-SQL中的查詢語句，單擊工具欄中的執(zhí)行按鈕將執(zhí)行指定的查詢，并顯示查詢結(jié)果。打開指定的服務(wù)器和數(shù)據(jù)庫選項，右擊所要操作的表，從彈出的快捷菜單中選擇“打開表”選項，選擇“返回所有行”或“返回首行”選項，出現(xiàn)顯示數(shù)據(jù)對話框。將光標(biāo)定位到最后一條記錄后面，輸入數(shù)據(jù)就可以完成插入操作；將光標(biāo)定位到某條記錄，改變某個數(shù)據(jù)的值就可以完成更新操作；右擊某條記錄，從彈出的快捷菜單中選擇“刪除”選項可以完成記錄的刪除操作。5.2使用SELECT語句查詢數(shù)據(jù)【問題5-1】要求按所發(fā)帖子的總點擊數(shù)由高到低，列出所發(fā)帖子的總點擊數(shù)超過1000的論壇用戶的編號、昵稱和帖子總點擊數(shù)。如何使用SQL語句完成這個查詢?nèi)蝿?wù)？SELECT語句的功能就是從數(shù)據(jù)庫中檢索出符合用戶需求的數(shù)據(jù)。語法格式：SELECTselect_list[INTOnew_table]FROMtable_source

[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]SELECT語句至少要包含SELECT和FROM兩個子句。5.2.1

使用SELECT子句

SELECT子句語法形式如下：SELECT[ALL|DISTINCT][TOPn[PERCENT][WITHTIES]]<select_list><select_list>::=

{

*

|{table_name|view_name|table_alias}.*

|{column_name|expression|IDENTITYCOL|ROWGUIDCOL}

[[AS]column_alias]

|column_alias=expression

}

[,...n]。參數(shù)說明：

ALL：指定在結(jié)果集中可以顯示重復(fù)行。ALL是默認(rèn)設(shè)置。DISTINCT：指定在結(jié)果集中只能顯示唯一行?？罩当徽J(rèn)為相等。TOPn[PERCENT]：指定輸出查詢結(jié)果集中的前n行。n是介于0和4294967295之間的整數(shù)。如果還指定了PERCENT，則只輸出結(jié)果集中的前百分之n行。當(dāng)指定時帶PERCENT時，n必須是介于0和100之間的整數(shù)。如果查詢包含ORDERBY子句，將輸出由ORDERBY子句排序后的前n行（或前百分之n行）。WITHTIES：指定返回結(jié)果集中最后的n行或n%行。<select_list>：指定查詢結(jié)果集中的列。選擇列表是以逗號分隔的一系列表達式。*：表示所有列table_name|view_name|table_alias.*：將*的作用域限制為指定的表或視圖。column_name：指定要返回的列名。expression：是列名、常量、函數(shù)以及運算符連接的表達式。。在expression中可以使用行聚合函數(shù)（又稱統(tǒng)計函數(shù)），SQLServer中常用的聚合函數(shù)如表。說明：DISTINCT表示在計算時去掉列中的重復(fù)值。如果不指定DISTINCT或指定ALL（默認(rèn)），則計算所有指定的值。IDENTITYCOL：返回標(biāo)識列。ROWGUIDCOL：返回行全局唯一標(biāo)識列。column_alias：指定列的別名。?！纠?-1】假設(shè)Section表中的數(shù)據(jù)如表所示。（1）查詢表中的所有記錄。SELECT*FROMSection（2）查詢所有版塊的名稱（SName），點擊率（SClickCount）和帖子數(shù)量（STopicCount）。SELECTSname,SClickCount,STopicCountFROMSection查詢結(jié)果如下：SName

SClickCount

STopicCount

【逍遙體苑】501【相約同行】1001【游記攻略】1200（所影響的行數(shù)為3行）（3）查詢所有版塊的SName（別名為版塊名稱），SMasterID（別名為版主編號）和SClickCount（別名為點擊率）。SELECT版塊名稱=Sname,SMasterIDAS版主編號,SClickCount

點擊率FROMSection說明：在上例中使用了更改列標(biāo)題（定義別名）的3種方法：列別名=列名列名AS列別名列名列別名注意：列別名的使用范圍：列別名只在定義的語句中有效。（4）查詢前2條記錄。SELECTTOP2*FROMSection（5）查詢所有版塊的帖子數(shù)量（STopicCount），去掉重復(fù)值。SELECTDISTINCTSTopicCountFROMSection（6）統(tǒng)計所有版塊的帖子總數(shù)。SELECTSUM(STopicCount)FROMSection5.2.2

使用INTO子句

INTO子句用于創(chuàng)建新表并將查詢結(jié)果插入新表中。語法格式：[INTOnew_table]其中的參數(shù)new_table用于指定所要生成的新表的名稱。新創(chuàng)建表的列由select_list指定?！纠?-2】創(chuàng)建一個只有版塊編號、版塊名稱和點擊率的新表New_Section_，其列定義和表中數(shù)據(jù)與Section表相同。SELECTSID,Sname,SClickCountINTONew_Section

FROMSection運行結(jié)果如下：（所影響的行數(shù)為3行）執(zhí)行select*fromNew_Section，返回結(jié)果為：SIDSname

SClickCount

1【逍遙體苑】502【相約同行】1003【游記攻略】120（所影響的行數(shù)為3行）。5.2.3

使用FROM子句

FROM子句用于指定要查詢的表。語法形式如下：FROM{<table_source>}[,...n]...……<table_source>：指定查詢所用的表、視圖、派生表或聯(lián)接表。……【例5-3】假設(shè)Users表中的數(shù)據(jù)如下圖所示。（1）查詢所有版塊的版塊編號、版塊名稱、版主的編號、姓名以及電子郵箱。USEbbsDBGOSELECTSID,SName,UID,UName,UEmail

FROMSection,UsersWHEREUsers.UID=Section.SmasterIDGO（2）使用內(nèi)聯(lián)接INNERJOIN完成（1）的功能。SELECTSID,SName,UID,UName,UEmail

FROMSectionINNERJOINUsersONUID=SmasterID。5.2.4

使用WHERE子句

WHERE子句是條件子句，用于限定查詢的內(nèi)容。語法格式：WHERE<search_condition><search_condition>::={[NOT]<predicate>|(<search_condition>)}[{AND|OR}[NOT]{<predicate>|(<search_condition>)}]}[,...n]參數(shù)說明：search_condition：查詢的條件表達式1.比較表達式使用比較表達式的一般形式為：expressionoperatorexpressionexpression：可以是列名、常量、函數(shù)、變量、標(biāo)量子查詢，或者是由運算符或子查詢連接的列名、常量和函數(shù)的任意組合。還可以包含CASE函數(shù)。operator:

比較運算符=（等于）>（大于）>=（對于等于）<（小于）<=（小于等于）<>（不等于）!>（不大于）!<（不小于）!=（不等于）【例5-4】查詢表Section中帖子數(shù)量不小于10的版塊。SELECT*FROMSectionWHERESTopicCount>=102．邏輯表達式在Transact-SQL中可以使用的邏輯運算符有3個：NOT（邏輯反）、AND（邏輯與）、OR（邏輯或）邏輯運算符的優(yōu)先順序是NOT、AND、OR。在比較表達式和邏輯表達式中有3種可能的取值：TRUE、FALSE或UNKNOWN?！纠?-5】查詢表Section中點擊率不低于100，并且帖子數(shù)量不為0的版塊。SELECT*FROMSectionWHERESClickCount>=100ANDSTopicCount<>03．BETWEEN關(guān)鍵字使用BETWEEN關(guān)鍵字可以限定查尋范圍，其語法形式如下：test_expression[NOT]BETWEENbegin_expressionANDend_expression

參數(shù)說明：test_expression：被測試的表達式。begin_expression

：指定取值范圍的上限。end_expression：指定取值范圍的下限?！纠?-6】查詢表Section中點擊率在50和100之間的版塊。SELECT*FROMSectionWHERESClickCountBETWEEN50AND1004．IN關(guān)鍵字使用IN關(guān)鍵字可以測試給定的值是否與子查詢或列表中的值相匹配。語法格式：test_expression[NOT]IN(subquery|expression[,...n])參數(shù)說明：test_expression：任何有效的SQLServer表達式。subquery：包含某列結(jié)果集的子查詢。expression[,...n]：一個表達式列表，用來測試是否匹配?！纠?-7】從Users表中查詢生日不在3月、5月和7月的論壇用戶信息。SELECT*FROMUsersWHEREMONTH(UBirthday)NOTIN(3,5,7)5．LIKE關(guān)鍵字LIKE關(guān)鍵字用于將所給字符串與指定的的模式匹配。語法格式：match_expression[NOT]LIKEpattern[ESCAPEescape_character]參數(shù)說明：match_expression：任何字符串?dāng)?shù)據(jù)類型的有效SQLServer表達式。Pattern：指定match_expression

中的搜索模式，可以包含下列有效SQLServer通配符：%：可匹配任意類型和長度的字符串。_（下劃線）：可匹配任何單個字符。[]：指定范圍或集合中的任何單個字符。[^]：不屬于指定范圍或集合的任何單個字符escape_character：允許在字符串中搜索通配符，而不是將其作為通配符使用?！纠?-8】在Pubs數(shù)據(jù)庫的authors表中查找所有區(qū)號為415的電話號碼。USEpubsGOSELECTphoneFROMauthorsWHEREphoneLIKE'415%'ORDERbyau_lnameGO【例5-9】在Pubs數(shù)據(jù)庫的authors表中查找名字為Cheryl或Sheryl的作者。USEpubsGOSELECTau_lname,au_fname,phoneFROMauthorsWHEREau_fnameLIKE'[CS]heryl'ORDERBYau_lnameASC,au_fnameASCGO查詢結(jié)果如圖5所示?！纠?-10】在Pubs數(shù)據(jù)庫的authors表中查找姓為Carson、Carsen、Karson

或Karsen

的作者。USEpubsGOSELECTau_lname,au_fname,phoneFROMauthorsWHEREau_lnameLIKE'[CK]ars[eo]n'ORDERBYau_lnameASC,au_fnameASCGO查詢結(jié)果如圖所示。6．NULL關(guān)鍵字在WHERE子句中不能使用比較運算符對空值進行判斷，只能使用ISNULL來確定一個給定的表達式是否為NULL。其語法形式如下：expressionIS[NOT]NULL參數(shù)expression是任何有效的SQLServer表達式。【例5-11】在Pubs數(shù)據(jù)庫的authors表中查找所有預(yù)付款少于$5,000或者預(yù)付款未知（或為NULL）的書，返回它們的書號及預(yù)付款。USEpubsGOSELECTtitle_id,advanceFROMtitlesWHEREadvance<$5000ORadvanceISNULLGO查詢結(jié)果如下：5.2.5

使用GROUPBY子句

GROUPBY子句用來對查詢結(jié)果分組。語法格式：[GROUPBY[ALL]group_by_expression[,...n][WITH{CUBE|ROLLUP}]]參數(shù)說明：ALL：包含所有組和結(jié)果集。group_by_expression：是對其執(zhí)行分組的表達式。CUBE：指定在結(jié)果集內(nèi)不僅包含由GROUPBY提供的正常行，還包含匯總行。ROLLUP：指定在結(jié)果集內(nèi)不僅包含由GROUPBY提供的正常行，還包含匯總行。按層次結(jié)構(gòu)順序，從組內(nèi)的最低級別到最高級別匯總組。注意：指定GROUPBY時，選擇列表中任一非聚合表達式內(nèi)的所有列都應(yīng)包含在GROUPBY列表中?！纠?-12】統(tǒng)計論壇庫中Users表中的男、女用戶人數(shù)。USEbbsDBGOSELECTUSex,COUNT(*)人數(shù)FROMUsersGROUPBYUSexGO查詢結(jié)果如圖5.2.6

使用HAVING子句

HAVING子句指定組或聚合的搜索條件。HAVING通常與GROUPBY子句一起使用。如果不使用GROUPBY子句，HAVING的行為與WHERE子句一樣。語法格式：[HAVING<search_condition>]參數(shù)<search_condition>指定組或聚合應(yīng)滿足的搜索條件?！纠?-13】在Pubs數(shù)據(jù)庫的titles表中查詢截止到目前的銷售額超過$40,000的出版商。USEpubsGOSELECTpub_id,total=SUM(ytd_sales)FROMtitlesGROUPBYpub_idHAVINGSUM(ytd_sales)>40000GO查詢結(jié)果如圖。5.2.7

使用ORDERBY子句

ORDERBY子句用于根據(jù)一個列或者多個列來排序查詢結(jié)果。語法形式如下：[ORDERBY{order_by_expression[ASC|DESC]}

[,...n]]參數(shù)說明：order_by_expression：指定要排序的列。ASC：指定按遞增順序的值進行排序。DESC：指定按遞減順序的值進行排序。說明：空值被視為最低的可能值。【例5-14】將發(fā)帖表Topic中的記錄按點擊率由低到高排序。USEbbsDBGOSELECT*FROMTopicORDERBYTClickCount

GO5.2.8

使用COMPUTE子句

COMPUTE子句用于生成統(tǒng)計結(jié)果，放在查詢結(jié)果的最后。語法格式：[COMPUTE

{{AVG|COUNT|MAX|MIN|STDEV|STDEVP

|VAR|VARP|SUM}

(expression)}[,...n]

[BYexpression[,...n]]。參數(shù)說明：AVG|COUNT|MAX|MIN|STDEV|STDEVP|VAR|VARP|SUM：指定要執(zhí)行的聚合函數(shù)。expression：指定需要執(zhí)行計算的列名。BYexpression：在結(jié)果集內(nèi)生成控制中斷和分類匯總。在使用COMPUTE子句時，應(yīng)遵守以下原則：在行聚合函數(shù)中不能使用DISTINCT關(guān)鍵字。在SELECTINTO語句中不能使COMPUTE子句，任何由COMPUTE生成的計算結(jié)果都不出現(xiàn)在用SELECTINTO語句創(chuàng)建的新表內(nèi)。COMPUTEBY中的表達式必須出現(xiàn)在SELECT選擇列表中，并且必須將其指定為與選擇列表中的某個表達式完全一樣。如果使用COMPUTEBY，則必須也使用ORDERBY子句。表達式必須與在ORDERBY后列出的子句相同或是其子集，并且必須按相同的序列?！纠?-15】查詢Section表中所有版塊的點擊率及平均點擊率。USEbbsDBGOSELECTSID,SName,SClickCount

FROMSectionCOMPUTEAVG(SClickCount)GO查詢結(jié)果如圖5.2.9

使用聯(lián)合查詢

聯(lián)合查詢是指將兩個或兩個以上的SELECT語句通過UNION運算符連接起來的查詢，聯(lián)合查詢可以將兩個或更多查詢的結(jié)果組合為單個結(jié)果集，該結(jié)果集包含聯(lián)合查詢中所有查詢的全部行。使用UNION組合兩個查詢結(jié)果集的兩個基本規(guī)則是：

所有查詢中的列數(shù)和列的順序必須相同。數(shù)據(jù)類型必須兼容。語法格式：{<queryspecification>|(<queryexpression>)}UNION[ALL]<queryspecification|(<queryexpression>)[UNION[ALL]<queryspecification|(<queryexpression>)[...n]]參數(shù)說明：<query_specification>|(<query_expression>)：參與查詢的SELECT語句。ALL：在結(jié)果中包含所有的行，包括重復(fù)行。如果沒有指定，則刪除重復(fù)行。?！纠?-16】查詢發(fā)帖表Topic和跟帖表Reply中所有帖子的發(fā)帖人編號和主題。USEbbsDBGOSELECTTUIDAS發(fā)帖人編號,TTopicAS主題FROMTopicUNIONSELECTRUID,RTopicFROMReplyGO查詢結(jié)果如圖。5.2.10

使用嵌套查詢

在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING子句的條件中的查詢稱為嵌套查詢。嵌套查詢中上層的查詢塊稱為外層查詢或父查詢，下層查詢塊稱為內(nèi)層查詢或子查詢。SQL語言允許多層嵌套。嵌套查詢主要用于復(fù)雜的查詢中。在某些嵌套查詢中WHERE之后還可以使用ANY和ALL兩個關(guān)鍵字?！纠?-17】查詢年齡最小的論壇用戶的用戶編號、昵稱、出生日期和電子郵箱。USEbbsDBGOSELECTUID,UName,UBirthday,UEmail

FROMUsersWHEREUBirthday>=ALL(SELECTUBirthdayFROMUsers)查詢結(jié)果如圖【例5-18】查詢除年齡最小的論壇用戶外的所有用戶的編號、昵稱、出生日期和電子郵箱。USEbbsDBGOSELECTUID,UName,UBirthday,UEmail

FROMUsersWHEREUBirthday>ANY(SELECTUBirthdayFROMUsers)查詢結(jié)果如圖

【例5-19】SELECT語句綜合應(yīng)用：關(guān)于【問題5-1】的解答。SELECTUID,UＮame,SUM(TClickCount)as

TClickCount_TotalFROMUsers,TopicWHEREUsers.UID=Topic.TUIDGROUPBYUID,UName

HAVINGSUM(TClickCount)>1000ORDERBYTClickCount_TotalDESC查詢結(jié)果如圖5.3使用INSERT語句插入數(shù)據(jù)【問題5-2】在Pubs數(shù)據(jù)庫中，從編號為1389的出版社新進一本business類書，書號為BU9876,書名為CreatingWebPages,價格為29.99元，如何使用SQL語句將此書加入Pubs數(shù)據(jù)庫的圖書表Titles中？可以使用INSERT語句向表或視圖中添加一行或多行數(shù)據(jù)。語法格式：INSERT[INTO]{table_name|view_name}

{[（column_list

）]{VALUES（{DEFAULT|NULL|expression}[,...n]）

|derived_table}}參數(shù)說明：[INTO]：一個可選的關(guān)鍵字，使用這個關(guān)鍵字可以使語句的意義清晰。table_name：將要接收數(shù)據(jù)的表或table變量的名稱。view_name：將要接收數(shù)據(jù)的視圖名稱。column_list：要在其中插入數(shù)據(jù)的一列或多列的列表，VALUES：引入要插入的數(shù)據(jù)值的列表。DEFAULT：強制使用列定義的默認(rèn)值填充。NULL：強制使用NULL值填充。expression：常量、變量或表達式。表達式不能包含SELECT或EXECUTE語句。derived_table：任何有效的SELECT語句，它返回將插入到表中的數(shù)據(jù)行。

【例5-20】將一行添加到Pubs數(shù)據(jù)庫的titles表中，以指定書名、類型、出版商及價格的值：USEPubsGOINSERTINTOtitles(title_id,title,type,pub_id,price)VALUES('BU9876','CreatingWebPages','business','1389','29.99')。5.4使用UPDATE語句更新數(shù)據(jù)

【問題5-3】如何使用SQL語句將Pubs數(shù)據(jù)庫的titles表中書號為‘BU9876’的書名修改為“網(wǎng)頁制作”？可以使用UPDATE語句修改表中特定記錄或字段的數(shù)據(jù)。語法格式：UPDATE{table_name|view_name}[FROM{<table_source>}[,...n]SETcolumn_name={expression|DEFAULT|NULL}[,...n][WHEREsearch_condition>]。參數(shù)說明：table_name：需要更新的表的名稱。view_name：要更新的視圖的名稱。SET：指定要更新的列或變量名稱的列表。column_name：要更改數(shù)據(jù)的列的名稱。expression：變量、字面值、表達式或加上括弧的返回單個值的subSELECT

語句。expression返回的值將替換column_name

或@variable中的現(xiàn)有值。DEFAULT：使用列定義的默認(rèn)值替換列中的現(xiàn)有值。NULL：使用NULL值更改列中的現(xiàn)有值。WHERE：指定條件來限定所更新的行。若無WHERE子句，將會修改表中的每行數(shù)據(jù)。<search_condition>：指定要更新的行需滿足的條件?！纠?-21】修改Pubs數(shù)據(jù)庫的titles表中書號為'BU9876'的書名為“網(wǎng)頁制作”。USEPubsGOUPDATEtitelsSETtitle=’網(wǎng)頁制作’WHEREtitle_id=’BU9876’5.5使用DELETE語句刪除數(shù)據(jù)DELETE語句用于刪除表中記錄。5.5.1使用DELETE語句語法格式：DELETE[FROM]{table_name|view_name}[WHERE<search_condition>]參數(shù)說明：FROM：一個可選的關(guān)鍵字，使用這個關(guān)鍵字可以使語句的意義清晰。table_name：要從其中刪除行的表的名稱。view_name：要從其中刪除行的視圖的名稱。WHERE：指定條件來限定所刪除的行。若無WHERE子句，將刪除表中所有行。<search_condition>：指定要刪除的行需滿足的條件?！纠?-22】刪除pubs數(shù)據(jù)庫titles表中書號為'BU9876'的記錄。USEpubsGODELETEFROMtitlesWHEREtitle_id=’BU9876’【例5-23】刪除pubs數(shù)據(jù)庫authors表中au_lname

是McBadden

的所有行。USEpubsGODELETEFROMauthorsWHEREau_lname='McBadden'5.5.2使用TRUNCATE語句清除表中的所有數(shù)據(jù)、只留下表的定義，可以使用TRUNCATE語句。與DELETE語句相比，TRUNCATE通常速度快，因為TRUNCATE是不記錄日志的操作。語法格式：TRUNCATETABLEname

參數(shù)name指定要刪除全部行的表的名稱?！纠?-24】清空跟帖表Reply中的數(shù)據(jù)。

TRUNCATETABLEReply5.6綜合實例【實例說明】對在4.7中創(chuàng)建的COLLEGE數(shù)據(jù)庫，完成以下對表中數(shù)據(jù)的操作：（1）查詢“06182”班總分在300分以上的學(xué)生的學(xué)號，姓名和總成績，結(jié)果按總成績由高到低排序。（設(shè)學(xué)號字段S_number的前5位為班號）。（2）根據(jù)Student表產(chǎn)生一個名為Student_1的新表，其內(nèi)容僅包括黨員同學(xué)。（3）向Student_1表插入單條記錄，情況如表所示。（4）批量插入記錄：將表Student表中所有女同學(xué)加入到Student_1中。（5）將Score表中分?jǐn)?shù)低于60分的在原來的基礎(chǔ)上加10分（6）將Student_2表中的所有政治面目為群眾的同學(xué)刪除。。【實現(xiàn)技術(shù)分析】（1）使用SELECT語句完成查詢操作；（2）使用INSERT語句完成向表中添加新記錄的插入操作；（3）使用UPDATE語句完成表中數(shù)據(jù)的修改操