第5章 表中数据的操作

SQLSERVER

应用与开发

计算机工程技术学院（软件学院）

任淑美二00七年九月（五）本课主要内容在企业管理器中操作表中的数据使用SELECT语句查询数据使用INSERT语句插入数据使用UPDATE语句更新数据使用DELETE语句删除数据本课核心知识点SELECT语句INSERT语句UPDATE语句DELETE语句本课重点难点SELECT语句的基本结构及其各子句的用法使用INSERT、UPDATE、DELETE语句插入、修改和删除表中数据嵌套查询

本课教学目的掌握如何在企业管理器中操作表中的数据掌握使用SELECT语句查询数据的方法掌握使用INSERT语句插入数据的方法掌握使用UPDATE语句更新数据的方法掌握使用DELETE语句删除数据的方法第5章表中数据的操作

5.1在企业管理器中操作表中的数据

表中数据的操作包括数据的查询、插入、更新和删除。在企业管理器中，打开指定的服务器和数据库选项，右击所要操作的表，选择“打开表”选项，选择“查询”选项，出现查询数据对话框。在对话框中设置各种查询条件，还可以直接输入Transact-SQL中的查询语句，单击工具栏中的执行按钮将执行指定的查询，并显示查询结果。打开指定的服务器和数据库选项，右击所要操作的表，从弹出的快捷菜单中选择“打开表”选项，选择“返回所有行”或“返回首行”选项，出现显示数据对话框。将光标定位到最后一条记录后面，输入数据就可以完成插入操作；将光标定位到某条记录，改变某个数据的值就可以完成更新操作；右击某条记录，从弹出的快捷菜单中选择“删除”选项可以完成记录的删除操作。5.2使用SELECT语句查询数据【问题5-1】要求按所发帖子的总点击数由高到低，列出所发帖子的总点击数超过1000的论坛用户的编号、昵称和帖子总点击数。如何使用SQL语句完成这个查询任务？SELECT语句的功能就是从数据库中检索出符合用户需求的数据。语法格式：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]。参数说明：

ALL：指定在结果集中可以显示重复行。ALL是默认设置。DISTINCT：指定在结果集中只能显示唯一行。空值被认为相等。TOPn[PERCENT]：指定输出查询结果集中的前n行。n是介于0和4294967295之间的整数。如果还指定了PERCENT，则只输出结果集中的前百分之n行。当指定时带PERCENT时，n必须是介于0和100之间的整数。如果查询包含ORDERBY子句，将输出由ORDERBY子句排序后的前n行（或前百分之n行）。WITHTIES：指定返回结果集中最后的n行或n%行。<select_list>：指定查询结果集中的列。选择列表是以逗号分隔的一系列表达式。*：表示所有列table_name|view_name|table_alias.*：将*的作用域限制为指定的表或视图。column_name：指定要返回的列名。expression：是列名、常量、函数以及运算符连接的表达式。。在expression中可以使用行聚合函数（又称统计函数），SQLServer中常用的聚合函数如表。说明：DISTINCT表示在计算时去掉列中的重复值。如果不指定DISTINCT或指定ALL（默认），则计算所有指定的值。IDENTITYCOL：返回标识列。ROWGUIDCOL：返回行全局唯一标识列。column_alias：指定列的别名。。【例5-1】假设Section表中的数据如表所示。（1）查询表中的所有记录。SELECT*FROMSection（2）查询所有版块的名称（SName），点击率（SClickCount）和帖子数量（STopicCount）。SELECTSname,SClickCount,STopicCountFROMSection查询结果如下：SName

SClickCount

STopicCount

【逍遥体苑】501【相约同行】1001【游记攻略】1200（所影响的行数为3行）（3）查询所有版块的SName（别名为版块名称），SMasterID（别名为版主编号）和SClickCount（别名为点击率）。SELECT版块名称=Sname,SMasterIDAS版主编号,SClickCount

点击率FROMSection说明：在上例中使用了更改列标题（定义别名）的3种方法：列别名=列名列名AS列别名列名列别名注意：列别名的使用范围：列别名只在定义的语句中有效。（4）查询前2条记录。SELECTTOP2*FROMSection（5）查询所有版块的帖子数量（STopicCount），去掉重复值。SELECTDISTINCTSTopicCountFROMSection（6）统计所有版块的帖子总数。SELECTSUM(STopicCount)FROMSection5.2.2

使用INTO子句

INTO子句用于创建新表并将查询结果插入新表中。语法格式：[INTOnew_table]其中的参数new_table用于指定所要生成的新表的名称。新创建表的列由select_list指定。【例5-2】创建一个只有版块编号、版块名称和点击率的新表New_Section_，其列定义和表中数据与Section表相同。SELECTSID,Sname,SClickCountINTONew_Section

FROMSection运行结果如下：（所影响的行数为3行）执行select*fromNew_Section，返回结果为：SIDSname

SClickCount

1【逍遥体苑】502【相约同行】1003【游记攻略】120（所影响的行数为3行）。5.2.3

使用FROM子句

FROM子句用于指定要查询的表。语法形式如下：FROM{<table_source>}[,...n]...……<table_source>：指定查询所用的表、视图、派生表或联接表。……【例5-3】假设Users表中的数据如下图所示。（1）查询所有版块的版块编号、版块名称、版主的编号、姓名以及电子邮箱。USEbbsDBGOSELECTSID,SName,UID,UName,UEmail

FROMSection,UsersWHEREUsers.UID=Section.SmasterIDGO（2）使用内联接INNERJOIN完成（1）的功能。SELECTSID,SName,UID,UName,UEmail

FROMSectionINNERJOINUsersONUID=SmasterID。5.2.4

使用WHERE子句

WHERE子句是条件子句，用于限定查询的内容。语法格式：WHERE<search_condition><search_condition>::={[NOT]<predicate>|(<search_condition>)}[{AND|OR}[NOT]{<predicate>|(<search_condition>)}]}[,...n]参数说明：search_condition：查询的条件表达式1.比较表达式使用比较表达式的一般形式为：expressionoperatorexpressionexpression：可以是列名、常量、函数、变量、标量子查询，或者是由运算符或子查询连接的列名、常量和函数的任意组合。还可以包含CASE函数。operator:

比较运算符=（等于）>（大于）>=（对于等于）<（小于）<=（小于等于）<>（不等于）!>（不大于）!<（不小于）!=（不等于）【例5-4】查询表Section中帖子数量不小于10的版块。SELECT*FROMSectionWHERESTopicCount>=102．逻辑表达式在Transact-SQL中可以使用的逻辑运算符有3个：NOT（逻辑反）、AND（逻辑与）、OR（逻辑或）逻辑运算符的优先顺序是NOT、AND、OR。在比较表达式和逻辑表达式中有3种可能的取值：TRUE、FALSE或UNKNOWN。【例5-5】查询表Section中点击率不低于100，并且帖子数量不为0的版块。SELECT*FROMSectionWHERESClickCount>=100ANDSTopicCount<>03．BETWEEN关键字使用BETWEEN关键字可以限定查寻范围，其语法形式如下：test_expression[NOT]BETWEENbegin_expressionANDend_expression

参数说明：test_expression：被测试的表达式。begin_expression

：指定取值范围的上限。end_expression：指定取值范围的下限。【例5-6】查询表Section中点击率在50和100之间的版块。SELECT*FROMSectionWHERESClickCountBETWEEN50AND1004．IN关键字使用IN关键字可以测试给定的值是否与子查询或列表中的值相匹配。语法格式：test_expression[NOT]IN(subquery|expression[,...n])参数说明：test_expression：任何有效的SQLServer表达式。subquery：包含某列结果集的子查询。expression[,...n]：一个表达式列表，用来测试是否匹配。【例5-7】从Users表中查询生日不在3月、5月和7月的论坛用户信息。SELECT*FROMUsersWHEREMONTH(UBirthday)NOTIN(3,5,7)5．LIKE关键字LIKE关键字用于将所给字符串与指定的的模式匹配。语法格式：match_expression[NOT]LIKEpattern[ESCAPEescape_character]参数说明：match_expression：任何字符串数据类型的有效SQLServer表达式。Pattern：指定match_expression

中的搜索模式，可以包含下列有效SQLServer通配符：%：可匹配任意类型和长度的字符串。_（下划线）：可匹配任何单个字符。[]：指定范围或集合中的任何单个字符。[^]：不属于指定范围或集合的任何单个字符escape_character：允许在字符串中搜索通配符，而不是将其作为通配符使用。【例5-8】在Pubs数据库的authors表中查找所有区号为415的电话号码。USEpubsGOSELECTphoneFROMauthorsWHEREphoneLIKE'415%'ORDERbyau_lnameGO【例5-9】在Pubs数据库的authors表中查找名字为Cheryl或Sheryl的作者。USEpubsGOSELECTau_lname,au_fname,phoneFROMauthorsWHEREau_fnameLIKE'[CS]heryl'ORDERBYau_lnameASC,au_fnameASCGO查询结果如图5所示。【例5-10】在Pubs数据库的authors表中查找姓为Carson、Carsen、Karson

或Karsen

的作者。USEpubsGOSELECTau_lname,au_fname,phoneFROMauthorsWHEREau_lnameLIKE'[CK]ars[eo]n'ORDERBYau_lnameASC,au_fnameASCGO查询结果如图所示。6．NULL关键字在WHERE子句中不能使用比较运算符对空值进行判断，只能使用ISNULL来确定一个给定的表达式是否为NULL。其语法形式如下：expressionIS[NOT]NULL参数expression是任何有效的SQLServer表达式。【例5-11】在Pubs数据库的authors表中查找所有预付款少于$5,000或者预付款未知（或为NULL）的书，返回它们的书号及预付款。USEpubsGOSELECTtitle_id,advanceFROMtitlesWHEREadvance<$5000ORadvanceISNULLGO查询结果如下：5.2.5

使用GROUPBY子句

GROUPBY子句用来对查询结果分组。语法格式：[GROUPBY[ALL]group_by_expression[,...n][WITH{CUBE|ROLLUP}]]参数说明：ALL：包含所有组和结果集。group_by_expression：是对其执行分组的表达式。CUBE：指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。ROLLUP：指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。注意：指定GROUPBY时，选择列表中任一非聚合表达式内的所有列都应包含在GROUPBY列表中。【例5-12】统计论坛库中Users表中的男、女用户人数。USEbbsDBGOSELECTUSex,COUNT(*)人数FROMUsersGROUPBYUSexGO查询结果如图5.2.6

使用HAVING子句

HAVING子句指定组或聚合的搜索条件。HAVING通常与GROUPBY子句一起使用。如果不使用GROUPBY子句，HAVING的行为与WHERE子句一样。语法格式：[HAVING<search_condition>]参数<search_condition>指定组或聚合应满足的搜索条件。【例5-13】在Pubs数据库的titles表中查询截止到目前的销售额超过$40,000的出版商。USEpubsGOSELECTpub_id,total=SUM(ytd_sales)FROMtitlesGROUPBYpub_idHAVINGSUM(ytd_sales)>40000GO查询结果如图。5.2.7

使用ORDERBY子句

ORDERBY子句用于根据一个列或者多个列来排序查询结果。语法形式如下：[ORDERBY{order_by_expression[ASC|DESC]}

[,...n]]参数说明：order_by_expression：指定要排序的列。ASC：指定按递增顺序的值进行排序。DESC：指定按递减顺序的值进行排序。说明：空值被视为最低的可能值。【例5-14】将发帖表Topic中的记录按点击率由低到高排序。USEbbsDBGOSELECT*FROMTopicORDERBYTClickCount

GO5.2.8

使用COMPUTE子句

COMPUTE子句用于生成统计结果，放在查询结果的最后。语法格式：[COMPUTE

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

|VAR|VARP|SUM}

(expression)}[,...n]

[BYexpression[,...n]]。参数说明：AVG|COUNT|MAX|MIN|STDEV|STDEVP|VAR|VARP|SUM：指定要执行的聚合函数。expression：指定需要执行计算的列名。BYexpression：在结果集内生成控制中断和分类汇总。在使用COMPUTE子句时，应遵守以下原则：在行聚合函数中不能使用DISTINCT关键字。在SELECTINTO语句中不能使COMPUTE子句，任何由COMPUTE生成的计算结果都不出现在用SELECTINTO语句创建的新表内。COMPUTEBY中的表达式必须出现在SELECT选择列表中，并且必须将其指定为与选择列表中的某个表达式完全一样。如果使用COMPUTEBY，则必须也使用ORDERBY子句。表达式必须与在ORDERBY后列出的子句相同或是其子集，并且必须按相同的序列。【例5-15】查询Section表中所有版块的点击率及平均点击率。USEbbsDBGOSELECTSID,SName,SClickCount

FROMSectionCOMPUTEAVG(SClickCount)GO查询结果如图5.2.9

使用联合查询

联合查询是指将两个或两个以上的SELECT语句通过UNION运算符连接起来的查询，联合查询可以将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中所有查询的全部行。使用UNION组合两个查询结果集的两个基本规则是：

所有查询中的列数和列的顺序必须相同。数据类型必须兼容。语法格式：{<queryspecification>|(<queryexpression>)}UNION[ALL]<queryspecification|(<queryexpression>)[UNION[ALL]<queryspecification|(<queryexpression>)[...n]]参数说明：<query_specification>|(<query_expression>)：参与查询的SELECT语句。ALL：在结果中包含所有的行，包括重复行。如果没有指定，则删除重复行。。【例5-16】查询发帖表Topic和跟帖表Reply中所有帖子的发帖人编号和主题。USEbbsDBGOSELECTTUIDAS发帖人编号,TTopicAS主题FROMTopicUNIONSELECTRUID,RTopicFROMReplyGO查询结果如图。5.2.10

使用嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中的查询称为嵌套查询。嵌套查询中上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。SQL语言允许多层嵌套。嵌套查询主要用于复杂的查询中。在某些嵌套查询中WHERE之后还可以使用ANY和ALL两个关键字。【例5-17】查询年龄最小的论坛用户的用户编号、昵称、出生日期和电子邮箱。USEbbsDBGOSELECTUID,UName,UBirthday,UEmail

FROMUsersWHEREUBirthday>=ALL(SELECTUBirthdayFROMUsers)查询结果如图【例5-18】查询除年龄最小的论坛用户外的所有用户的编号、昵称、出生日期和电子邮箱。USEbbsDBGOSELECTUID,UName,UBirthday,UEmail

FROMUsersWHEREUBirthday>ANY(SELECTUBirthdayFROMUsers)查询结果如图

【例5-19】SELECT语句综合应用：关于【问题5-1】的解答。SELECTUID,UＮame,SUM(TClickCount)as

TClickCount_TotalFROMUsers,TopicWHEREUsers.UID=Topic.TUIDGROUPBYUID,UName

HAVINGSUM(TClickCount)>1000ORDERBYTClickCount_TotalDESC查询结果如图5.3使用INSERT语句插入数据【问题5-2】在Pubs数据库中，从编号为1389的出版社新进一本business类书，书号为BU9876,书名为CreatingWebPages,价格为29.99元，如何使用SQL语句将此书加入Pubs数据库的图书表Titles中？可以使用INSERT语句向表或视图中添加一行或多行数据。语法格式：INSERT[INTO]{table_name|view_name}

{[（column_list

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

|derived_table}}参数说明：[INTO]：一个可选的关键字，使用这个关键字可以使语句的意义清晰。table_name：将要接收数据的表或table变量的名称。view_name：将要接收数据的视图名称。column_list：要在其中插入数据的一列或多列的列表，VALUES：引入要插入的数据值的列表。DEFAULT：强制使用列定义的默认值填充。NULL：强制使用NULL值填充。expression：常量、变量或表达式。表达式不能包含SELECT或EXECUTE语句。derived_table：任何有效的SELECT语句，它返回将插入到表中的数据行。

【例5-20】将一行添加到Pubs数据库的titles表中，以指定书名、类型、出版商及价格的值：USEPubsGOINSERTINTOtitles(title_id,title,type,pub_id,price)VALUES('BU9876','CreatingWebPages','business','1389','29.99')。5.4使用UPDATE语句更新数据

【问题5-3】如何使用SQL语句将Pubs数据库的titles表中书号为‘BU9876’的书名修改为“网页制作”？可以使用UPDATE语句修改表中特定记录或字段的数据。语法格式：UPDATE{table_name|view_name}[FROM{<table_source>}[,...n]SETcolumn_name={expression|DEFAULT|NULL}[,...n][WHEREsearch_condition>]。参数说明：table_name：需要更新的表的名称。view_name：要更新的视图的名称。SET：指定要更新的列或变量名称的列表。column_name：要更改数据的列的名称。expression：变量、字面值、表达式或加上括弧的返回单个值的subSELECT

语句。expression返回的值将替换column_name

或@variable中的现有值。DEFAULT：使用列定义的默认值替换列中的现有值。NULL：使用NULL值更改列中的现有值。WHERE：指定条件来限定所更新的行。若无WHERE子句，将会修改表中的每行数据。<search_condition>：指定要更新的行需满足的条件。【例5-21】修改Pubs数据库的titles表中书号为'BU9876'的书名为“网页制作”。USEPubsGOUPDATEtitelsSETtitle=’网页制作’WHEREtitle_id=’BU9876’5.5使用DELETE语句删除数据DELETE语句用于删除表中记录。5.5.1使用DELETE语句语法格式：DELETE[FROM]{table_name|view_name}[WHERE<search_condition>]参数说明：FROM：一个可选的关键字，使用这个关键字可以使语句的意义清晰。table_name：要从其中删除行的表的名称。view_name：要从其中删除行的视图的名称。WHERE：指定条件来限定所删除的行。若无WHERE子句，将删除表中所有行。<search_condition>：指定要删除的行需满足的条件。【例5-22】删除pubs数据库titles表中书号为'BU9876'的记录。USEpubsGODELETEFROMtitlesWHEREtitle_id=’BU9876’【例5-23】删除pubs数据库authors表中au_lname

是McBadden

的所有行。USEpubsGODELETEFROMauthorsWHEREau_lname='McBadden'5.5.2使用TRUNCATE语句清除表中的所有数据、只留下表的定义，可以使用TRUNCATE语句。与DELETE语句相比，TRUNCATE通常速度快，因为TRUNCATE是不记录日志的操作。语法格式：TRUNCATETABLEname

参数name指定要删除全部行的表的名称。【例5-24】清空跟帖表Reply中的数据。

TRUNCATETABLEReply5.6综合实例【实例说明】对在4.7中创建的COLLEGE数据库，完成以下对表中数据的操作：（1）查询“06182”班总分在300分以上的学生的学号，姓名和总成绩，结果按总成绩由高到低排序。（设学号字段S_number的前5位为班号）。（2）根据Student表产生一个名为Student_1的新表，其内容仅包括党员同学。（3）向Student_1表插入单条记录，情况如表所示。（4）批量插入记录：将表Student表中所有女同学加入到Student_1中。（5）将Score表中分数低于60分的在原来的基础上加10分（6）将Student_2表中的所有政治面目为群众的同学删除。。【实现技术分析】（1）使用SELECT语句完成查询操作；（2）使用INSERT语句完成向表中添加新记录的插入操作；（3）使用UPDATE语句完成表中数据的修改操