第7章操纵表中的数据

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'