第7章操纵表中的数据课件

操纵表中的数据操纵表中的数据 14七月20241第第7 7章章 操纵表中的数据操纵表中的数据 q 数据更新数据更新q 数据查询数据查询q 高级检索技术高级检索技术 q 小结小结q 习题习题操纵表中的数据操纵表中的数据 14七月20242一、数据更新一、数据更新对数据库的维护更新操作是T-SQL的重要功能，数据更新是为了使数据库所反映的状态能及时地与当前最新的状态一致。更新操作分为插入、修改和删除。1.插入数据2.修改数据3.删除数据4.使用企业管理器来管理表格数据操纵表中的数据操纵表中的数据 14七月202431.插入数据插入数据插入是指将一条或若干条记录插入到表中的操作。在表中插入数据，应该使用INSERT语句。在该语句中，包括了两个子句，即INSERT子句和VALUES子句。使用使用 INSERT 语句插入新记录语句插入新记录INSERT语句用于向表中添加一行新记录，其基本语法格式为INSERT INTO table_name(column_list)VALUES (expression)其中，table_name用来接收数据的表（目标表）的名称。如果目标表不是当前数据库中的表，则应当使用“数据库名数据库名.拥有者拥有者.表名表名”的完整格式来描述。操纵表中的数据操纵表中的数据 14七月202441、给插入记录的所有字段添加数据、给插入记录的所有字段添加数据向表中插入数据时，允许省略INSERT语句中的列清单。【例】向表中插入一行数据USEpubsINSERTpublishersVALUES(9994,SANCO,WASHINGTON,WA,USA)GO操纵表中的数据操纵表中的数据 14七月20245如果写错了pub_name和city的位置，如：USEpubsINSERTpublishersVALUES(9995,WASHINGTON,SANCO,WA,USA)GO这个语句依然执行成功。服务器不会找到这个错误。操纵表中的数据操纵表中的数据 14七月202462、给插入记录的部分字段添加数据、给插入记录的部分字段添加数据向表中插入数据时要注意，字符数据和日期数据要使用引号引起来。【例】向表中插入一行数据USEpubsINSERTpublishers(pub_id,pub_name,city,state)VALUES(9993,SANCO,WASHINGTON,WA)GO操纵表中的数据操纵表中的数据 14七月20247【例】向表中插入部分列数据。INSERTINTOpublishers(pub_id,pub_name)VALUES(9975,WORLDPress)或INSERTINTOpublishers(pub_name,pub_id)VALUES（WORLDPress，9975)操纵表中的数据操纵表中的数据 14七月20248使用INSERT语句插入数据时，需要注意以下几点：(1)对于字符型和日期型数据，插入时要用单引号括起来。如李明、2003/3/28等。(2)可以插入部分表的数据，也就是可以为每一行的指定部分列插入数据。在插入部分列数据时，应该注意下面两个问题：应在INSERT语句中使用字段列表（即指定要插入数据的列名）。在VALUES子句中，列出与列名对应的数据。且列名的顺序与数据的顺序应完全对应。(3)对于具有IDENTITY属性的字段，应当在值列表中跳过。例如，当第三个字段具有IDENTITY属性时，值列表必须写成(值1,值2,值4,)。在默认情况下，不能把数据直接插入一个具有IDENTITY属性的字段。如果偶然从表中删除了一行记录，或在IDENTITY属性的字段值中存在着跳跃，也可以在该字段中设置一个指定的值。但必须首先用SET语句设置IDENTITY_INSERT选项，然后才能在IDENTITY字段中插入一个指定的值。操纵表中的数据操纵表中的数据 14七月202492.修改数据修改数据修改操作不增减表中的记录，而是更改现有记录的字段值。可以使用UPDATE语句修改（更新）表中已经存在的数据。既可以对整个表的某个或某些字段进行修改，也可以根据条件针对特定记录修改字段的值。在UPDATE语句中，使用WHERE子句指定要修改的行，使用SET子句给出新的数据。新数据既可以是常量，也可以是指定的表达式。命令格式：UPDATE table_name SET column_name=expression|DEFAULT|NULL|variable=expression|variable=column_name=expression ,.n FROM ,.n WHERE 操纵表中的数据操纵表中的数据 14七月202410【例】修改代号为172-32-1176的作者的名字为JOEUSEpubsUPDATEauthorsSETau_lname=JOEWHEREau_id=172-32-1176GO修改前修改后操纵表中的数据操纵表中的数据 14七月202411【例】如果不加上where子句，则会修改表中每一行数据的作者的名字au_lname为JOEUSEpubsUPDATEauthorsSETau_lname=JOEGO修改前修改后操纵表中的数据操纵表中的数据 14七月202412【例】修改authors表中指定的一行的每一列的值USEpubsUPDATEauthorsSETau_lname=Black,au_fname=ANN,phone=408496-7223,address=22ClevelandAv.#14,city=Oakland,state=CA,zip=94025,contract=0WHEREau_id=172-32-1176GO修改前修改后操纵表中的数据操纵表中的数据 14七月202413【例】USEpubsUPDATEauthorsSETzip=zip+2GO操纵表中的数据操纵表中的数据 14七月202414【例】将员工表中的每人工资增加5%UPDATE员工SET基本工资=基本工资+基本工资*0.05【例】将员工表中总经理工资加10%，其他人工资加5%UPDATE员工SET基本工资=基本工资+基本工资*0.1WHERE职务=总经理UPDATE员工SET基本工资=基本工资+基本工资*0.05WHERE职务总经理【例】如果员工在销售表中有销售记录，将员工表中该员工的工资增加5%UPDATE员工SET基本工资=基本工资+基本工资*0.05WHERE员工.编号IN(SELECT编号FROM销售)操纵表中的数据操纵表中的数据 14七月202415【例】在discounts表中，为了给每一个订购数量在100以上的客户增加百分之十的回扣，修改表中的数据为：updatediscountssetdiscount=discount（1+0.10）WHERElowqty=100操纵表中的数据操纵表中的数据 14七月2024163.删除数据删除数据当数据库表中的数据不需要的时候或无意义，可以删除。一般情况下，使用DELETE语句删除数据。DELETE语句可以从一个表中删除一行或多行数据。【格式一】DELETE FROM table_name|view_name FROM WHERE WHERE子句给出删除数据所必须满足的条件，省略WHERE子句时将删除所有数据，但保留表的结构，此时表成为没有记录的空表。操纵表中的数据操纵表中的数据 14七月202417【例】删除所有来自CA州的作家USEpubsDELETEFROMauthorsWHEREstate=CAGO【例】删除表中所有数据，剩下的只有表格的定义USEpubsDELETEFROMauthorsGO操纵表中的数据操纵表中的数据 14七月202418在DELETE语句中，如果使用了WHERE语句，那么就从指定的表中删除满足WHERE子句条件的数据行。【例】删除sales表中3年前的销售记录DELETEsalesWHEREDATEDIFF(YEAR,ord_date,GETDATE()=3如果在DELETE语句中，没有指定WHERE语句，那么就将表中所有的记录全部删除。即DELETEsales语句将删除sales表中的全部记录。【例如】删除没有员工的部门记录DELETEFROM部门WHERE部门号NOTIN(SELECT部门号FROM员工)操纵表中的数据操纵表中的数据 14七月202419DATEDIFF函数的用法【示例】确定在pubs数据库中标题发布日期和当前日期间的天数。USEpubsGOSELECTDATEDIFF(day,pubdate,getdate()ASno_of_daysFROMtitlesGO操纵表中的数据操纵表中的数据 14七月202420【格式二格式二】TRUNCATETABLE语句用于清空表中所有数据：TRUNCATE TABLE table_name从效果看，TRUNCATETABLEtable_name与不带WHERE子句的DELETE语句相同，都可以删除表中全部记录。二者的相同点是都不删除表的结构、索引、约束、规则和默认，如果希望删除表的定义，必须使用DROPTABLE语句。TRUNCATETABLE语句与DELETE语句的区别是，使用TRUNCATETABLE语句不记录日志；而DELETE语句每删除一条记录，都要记入日志。因此TRUNCATETABLE语句的执行速度更快，占用更少的系统资源和事务日志资源。【例如】删除所有sales记录TRUNCATETABLEsales因为TRUNCATETABLE操作是不进行日志记录的，所以建议在TRUNCATETABLE语句之前用先对数据库作备份。操纵表中的数据操纵表中的数据 14七月2024214.使用企业管理器来管理表格数据使用企业管理器来管理表格数据选定指定的表格，右键“打开表”：“返回所有行”：打开表后，返回表中的所有数据；“返回首行”：打开表后，返回表中指定的前n行数据“查询”：在弹出窗口上利用图形化方式或直接书写SQL语句进行相应的查询。操纵表中的数据操纵表中的数据 14七月202422二、数据查询二、数据查询 查询是数据库的主要操作之一，也是SQL语句最主要的功能。查询数据使用SELECT语句。数据查询是从数据库存储的数据中根据用户的需求获得所数据。数据查询技术就是提取数据的各种可行的方法。SELECT语句的作用是让数据库服务器根据客户端的要求搜寻出用户所需要的信息资料，并按用户规定的格式进行整理后返回给客户端。SELECT语句语句1.单表简单查询单表简单查询2.使用文字串使用文字串3.改变列标题改变列标题4.数据运算数据运算5.条件查询条件查询操纵表中的数据操纵表中的数据 14七月202423其中：SELECT子句：用于指定选择的列或行及其限定；INTO子句：用于将查询结果集存储到一个新的数据库表中；FROM子句：用于指出所查询的表名以及各表之间的逻辑关系；WHERE子句：用于指定对记录的过滤条件；GROUPBY子句：用于对查询到的记录进行分组；HAVING子句：用于指定分组统计条件，要与GROUPBY子句一起使用；ORDERBY子句：用于对查询到的记录进行排序处理。在这些子句中，只有SELECT子句和FROM子句是必选项，其他子句均为可选项。【格式格式】该命令中，只有SELECT和FROM子句是必选项。SELECT /*指定所检索的列名*/INTO /*指定所要生成新表的名称*/FROM /*指定要检索的表名*/WHERE /*指定要检索的行*/GROUP BY /*分组检索子句*/HAVING /*指定分组子句的条件*/ORDER BYASC|DESC /*排序子句，升序|降序*/操纵表中的数据操纵表中的数据 14七月2024241.单表简单查询单表简单查询【例】从pubs数据库的publishers表中检索所有的出版商代号、出版商的名字以及国籍USEpubsSELECTpub_id,pub_name,countryFROMpublishersGO（1）SELECT语句的常规使用方式 SELECT 列名列名1,列名列名2,列名列名n FROM 表名表名操纵表中的数据操纵表中的数据 14七月202425（2）用）用“*”表示表中所有的列表示表中所有的列语法格式如下：SELECT*FROM表名服务器会按用户创建表格时声明列的顺序来显示所有的列。【例】检索表中的全部信息，即全部列和全部行。USEpubsUSEpubsSELECT*或SELECT*FROMjobsFROMtitleauthorGOGO操纵表中的数据操纵表中的数据 14七月202426（3）使用）使用SELECT语句进行无数据源检索语句进行无数据源检索所谓无数源检索就是使用SELECT语句来检索不在表中的数据。（a）使用SELECT语句查看常量【例】SELECTsqlserver6.5SELECTsqlserver7.0GO（b）使用SELECT语句查看全局变量。【例】查询本地SQLServer服务器的版本信息SELECTversion【例】查询本地SQLServer服务器使用的语言SELECTlanguage操纵表中的数据操纵表中的数据 14七月202427（4）使用）使用TOP关键字关键字SQLServer提供了TOP关键字，让用户指定返回前面一定数量的数据。使用TOP关键字查询可以大大减少查询花费的时间。语法如下：SELECTTOP n|TOP n PERCENT列名1,列名2,列名nFROM表名其中；TOPn表示返回最前面的n行，n表示返回的行数。TOPnPERCENT表示返回的前面的n%行，n取值范围0.001到99.99。【例】从northwind数据库的customers表中返回前面10行数据。SELECTTOP10*FROMcustomers【例】从northwind数据库中的customers表中返回前10%的数据。SELECTTOP10percent*FROMcustomers操纵表中的数据操纵表中的数据 14七月202428【例】按从小到大输出单笔销售额前五名销售数据的日期、商品号、数量、金额SELECTTOP5日期,商品号,数量,金额FROM销售ORDERBY金额DESCGO操纵表中的数据操纵表中的数据 14七月202429（5）使用）使用DISTINCT关键字关键字使用DISTINCT关键字就能够从返回的结果数据集合中删除重复的行，使返回的结果更简洁。在使用DISTINCT关键字后，如果表中有多个为NULL的数据，服务器会把这些数据视为相等。【例】从pubs数据库的publishers表中搜索出所有国家的名字USEpubsSELECTcountryFROMpublishersGO操纵表中的数据操纵表中的数据 14七月202430【例】从pubs数据库的publishers表中搜索出所有国家的名字USEpubsSELECTDISTINCT countryFROMpublishersGO操纵表中的数据操纵表中的数据 14七月202431【例】从pubs数据库的publishers（出版商）表中检索所有的出版商的名字USEpubsSELECT DISTINCTpub_nameFROMpublishersGO操纵表中的数据操纵表中的数据 14七月202432【区别】操纵表中的数据操纵表中的数据 14七月202433【区别】操纵表中的数据操纵表中的数据 14七月202434【演示】再添加orderby语句操纵表中的数据操纵表中的数据 14七月202435（6）使用计算列）使用计算列在进行数据查询时，经常需要对查询到的数据进行再次计算处理。T-SQL允许直接在SELECT语句中使用计算列。计算列并不存在于表格所存储的数据中，它是通过对某些列的数据进行演算得来的结果。【例】将每本书的销售价格降低30%。USEpubsSELECTtitle_id,type,price,price-price*0.3FROMtitlesGO操纵表中的数据操纵表中的数据 14七月202436【例】使用字符串连接符（+）来连接作者的姓和名，以及作者所居住的州和城市的名称。USEpubsSELECTau_lname+.+au_fname,city+,+stateFROMauthorsGO操纵表中的数据操纵表中的数据 14七月2024372.使用文字串使用文字串为了增加SELECT语句检索结果的可读性，可以通过在SELECT关键字后面增加文字串，文字串使用单引号引起来。【例】使用文字串USEpubsSELECTThephonenumberof,au_lname,is,phoneFROMauthorsGO操纵表中的数据操纵表中的数据 14七月2024383.改变列标题改变列标题在默认情况下，在数据检索结果中所显示出来的列标题就是在创建表时使用的列名。但是，显示的列标题也是可以改变的。T-SQL提供了在SELECT语句中操作列名的方法。用户可以根据实际需要对查询数据的列标题进行修改，或者为没有标题的列加上临时的标题。对列名进行操作有3种方式：在列表达式后面给出列名；用“=”来连接列表达式；用AS关键字来连接列表达式和指定的列名。操纵表中的数据操纵表中的数据 14七月202439【例】改变列标题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关键字是可以省略的，因此改变列标题也可以写成：列名新标题。操纵表中的数据操纵表中的数据 14七月2024404.数据运算数据运算数据运算就是指对检索的数据进行各种运算，也就是说，可以在SELECT关键字后面列出的列项中使用各种运算符和函数。这些运算符和函数包括算术运算符、数学函数、字符串函数、日期和时间函数、系统函数等。算术运算符可以用在各种数字列上，这些列的数据类型是int、smallint、tinyint、float、real、money、smallmoney。这些算术运算符包括+、-、*、/和%。【例】使用+运算符，为每一本书增加2元USEpubsSELECTbookTitle=title,bookType=type,oldPrice=price,newPrice=price+2FROMtitlesGO操纵表中的数据操纵表中的数据 14七月202441数学函数返回通常需要运算的数据的数值。【例】在检索语句中使用数学函数SELECTPI=PI()SELECTSIN=SIN(PI()/2.0)SELECTCOS=COS(PI()/4.0)SELECTTAN=TAN(PI()/4.0)SELECTEXP=EXP(100)操纵表中的数据操纵表中的数据 14七月2024425.条件查询条件查询在SELECT语句中，WHERE子句指定要检索的数据行。在WHERE子句中，可以使用的搜索条件包括比较运算符、范围、列表、字符串匹配、合并以及取反等。使用WHERE子句的目的是从表格的数据集中筛选出符合条件的行。WHERE子句的语法允许在列名称和列值之间使用比较运算符。不同的比较运算符可以用来检索不同的数据行。使用语法：SELECTcolum_name,.nFROMtable_nameWHERE其中：定义查询条件，筛选返回的数据行。操纵表中的数据操纵表中的数据 14七月202443 使用算术表达式使用算术表达式 使用算术表达式作为搜索条件的一般表达形式是：表达式表达式 算术操作符算术操作符 表达式表达式（表达式为：常量、变量和列表达式的任意有效组合。）WHERE子句中允许使用的算术操作符操纵表中的数据操纵表中的数据 14七月202444【例】从employee表中检索出在1993年12月31日之前已经雇佣的雇员USEpubsSELECT*FROMemployeeWHEREhire_date12GO操纵表中的数据操纵表中的数据 14七月202446 使用逻辑表达式使用逻辑表达式在WHERE子句中可以使用逻辑运算符把若干个搜索条件合并起来，组成复杂的复合条件。在T-SQL里的逻辑表达式共有3个，分别是：NOT、AND、OR。在T-SQL中逻辑表达式共有3种可能的结果值，分别是TRUE，FALSE和UNKOWN。UNKOWN是由值为NULL的数据参与逻辑运算得出的结果。【例】查询所有在美国加利福尼亚州的出版社USEpubsSELECTpub_idAS出版社代号,pub_nameAS出版社名称,cityAS城市,stateAS州,countryAS国家FROMpublishersWHEREcountry=usaANDstate=caGO操纵表中的数据操纵表中的数据 14七月202447【例】通过使用一个复合检索条件，检索姓是White或名的首字母是A的作者信息USEpubsSELECTau_lname,au_fnameFROMauthorsWHEREau_lname=WhiteORau_fnameLIKEA%【例】查询部门号为50的女职工信息SELECT*FROM员工WHERE部门号=50AND性别=女操纵表中的数据操纵表中的数据 14七月202448 使用使用BETWEEN关关键字：范字：范围检查使用BETWEEN关键字可以更方便地限制查询数据的范围。语法格式为：表达式表达式NOT BETWEEN 表达式表达式1 AND 表达式表达式2字段值位于给定范围的记录使条件为真。范围包含两个端点。【例】查询价格在15和20美元之间的书的书号、种类和价格。USEpubsSELECTtitle_idAS书号,typeAS种类,priceAS原价FROMtitlesWHEREpriceBETWEEN$15AND$20GO操纵表中的数据操纵表中的数据 14七月202449【例】查询价格在15和20美元之间的书的书号、种类和价格WHEREpriceBETWEEN$15AND$20GO相当于：WHEREprice=$15ANDprice=$20【例】查询价格低于15和高于20美元的书的书号、种类和价格WHEREpriceNOT BETWEEN$15AND$20GO相当于：WHEREprice$20操纵表中的数据操纵表中的数据 14七月202450操纵表中的数据操纵表中的数据 14七月202451【例】在销售表中查询2005年5月销售的商品号和数量、金额SELECT日期,商品号,数量,金额FROM销售WHERE日期BETWEEN2005/05/01AND2005/05/31操纵表中的数据操纵表中的数据 14七月202452 使用使用IN关键字：集合运算符关键字：集合运算符语法格式为：表达式表达式 NOT IN (表达式表达式1,表达式表达式2,表达式表达式n)【例】在员工表中查询职务为总经理,经理,副经理的员工信息SELECT*FROM员工WHERE职务IN(总经理,经理,副经理)操纵表中的数据操纵表中的数据 14七月202453【例】查询所有居住在居住在KS、CA、MI或IN州的作家USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstateIN(CA,KS,MI,IN)GO如果不使用IN关键字，这些语句可以使用下面的语句代替：USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstate=CAORstate=KSORstate=MIOR state=INGO操纵表中的数据操纵表中的数据 14七月202454【例】查询所有不在不在KS、CA、MI或IN州居住的作家USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstateNOT IN(CA,KS,MI,IN)GO如果不使用NOTIN关键字，这些语句可以使用下面的语句代替：USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREstateCAORstateKSORstateMIOR stateINGO操纵表中的数据操纵表中的数据 14七月202455 LIKELIKE子句：匹配检查子句：匹配检查LIKE关键字用于检索与特定字符串匹配的数据，字符型字段使用。LIKE关键字后面跟一个列值的一部分而不是一个完整的列值。语法格式为：表达式表达式 NOT LIKE NOT LIKE 条件条件 条件通常与通配符配合使用。所有通配符都只有在LIKE子句中才有意义，否则通配符会被当作普通字符处理。字段在内容符合给定通配格式的记录使条件为真。NOT选项是求反。操纵表中的数据操纵表中的数据 14七月202456LIKE关键字与通配符的使用LIKE子句在大多数情况下会与通配符配合使用。SQLServer提供了以下4种通配符供用户灵活实现复杂的查询条件。通配符含义%（百分号）表示从0n个任意字符。_（下划线）表示单个的任意字符。（封闭方括号）表示方括号里列出的任意一个字符。任意一个没有在方括号里列出的字符。【例】使用通配符在员工表中查询职务有经理字样以外的员工信息SELECT*FROM员工WHERE职务NOTLIKE%经理%操纵表中的数据操纵表中的数据 14七月202457【例】查询所有名字以D打头的作家USEpubsSELECTau_lname+.+au_fnameFROMauthorsWHEREau_fnameLIKED%【例】使用6个下划线来匹配任何姓是6个字母的作家USEpubsSELECT*FROMauthorsWHEREau_fnameLIKE_操纵表中的数据操纵表中的数据 14七月202458【例】查询USEpubsSELECTau_lname,au_fname,phone,au_idFROMauthorsWHEREau_idLIKE72_-%操纵表中的数据操纵表中的数据 14七月202459【例】在“学生信息”表中，查询借书证号第一位数字不是35的学生。SELECT*FROM学生信息WHERE借书证号LIKE3-5%【例】更精确的查询USEpubsSELECTau_lname,au_fname,phone,au_idFROMauthorsWHEREau_idLIKE722345-%操纵表中的数据操纵表中的数据 14七月202460【例】精确查询操纵表中的数据操纵表中的数据 14七月202461【例】在“学生信息”表中，查询姓赵、姓李、姓王和姓张的学生。SELECT*FROM学生信息WHERE姓名LIKE赵李王张%【例】在“学生信息”表中，查询除了姓赵、姓李、姓王和姓张以外的学生。SELECT*FROM学生信息WHERE姓名NOT LIKE赵李王张%【例】在“图书信息”表中，查询计算机类的图书名称中有“程序设计”几个字的信息。要求输出字段为：图书名称，图书类别，出版日期。SELECT图书名称，图书类别，出版日期FROM图书信息WHERE图书类别=计算机AND图书名称LIKE%程序设计%操纵表中的数据操纵表中的数据 14七月202462【示例】带有匹配符的字符串必须使用引号引起来LIKEBR%LIKEBr%LIKE%eenLIKE%en%LIKE_enLIKECK%LIKES-VingLIKEMc%操纵表中的数据操纵表中的数据 14七月202463LIKE关键字与转义字符的使用ESCAPE用来规定转义字符。若要查找作为字符而不是通配符的%、_等，在查询时使用ESCAPE子句定义转义符也可以将通配符作为普通字符匹配查询。凡是ESCAPE子句中出现的字符都成为转义字符，它在字符串中不再表示真实的字符，只起标记的作用。凡是在转义字符后面出现的第一个表示通配符的字符都不再被视为通配符，而被当作普通字符处理。所有的字符都可以被当作转义字符处理。操纵表中的数据操纵表中的数据 14七月202464【例如】为了查询某个字段中包含“%”的记录，应当用以下WHERE子句：WHERE 具体的字段名具体的字段名 LIKE%!%ESCAPE!第一、三个%为通配符，中间的%为普通字符，由转义字符“！”引出。为了查询某个字段中“TW_”开头的记录，应当用以下WHERE子句：WHERE 具体的字段名具体的字段名 LIKE TW!_%ESCAPE!操纵表中的数据操纵表中的数据 14七月202465【例】假设有一个表X，列col的值如下：xyz、%xyz、x_yz、xyzw，若在X中查找以%开头的字符串SELECTcolFROMXWHEREcolliket%ESCAPEt【例】设一数据库包含名为“摘要”的字段，该字段包含文本30%。若要搜索在字段中任何位置包含字符串30%的行，语句为：WHERE摘要LIKE%30%ESCAPE 操纵表中的数据操纵表中的数据 14七月202466高级检索技术高级检索技术 1.统计查询2.分组数据3.子查询4.排序技术5.多表连接查询6.联合技术操纵表中的数据操纵表中的数据 14七月2024671.统计查询统计查询统计函数，例如AVG、SUM、COUNT、MAX、MIN、STDEV和VAR，返回整个表或几个列或一个列的汇总数据。这些函数必须用在SELECT子句中，其返回的结果在查询结果集中作为新列出现。函数表达式可以是下列几种形式的任意组合：l列名l常量l由算术运算符连接起来的函数操纵表中的数据操纵表中的数据 14七月202468【例】使用统计函数USEpubsSELECTmaxPrice=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函数是求表中数据的数量*/FROMtitlesGO操纵表中的数据操纵表中的数据 14七月202469【例】查询titles中所有书的平均价格USEpubsSELECTAVG(price)FROMtitlesGO【例】查询各种商业图书的平均价格USEpubsSELECTAVG(price)avg_priceFROMtitlesWHEREtype=businessGO操纵表中的数据操纵表中的数据 14七月202470【例】统计查询USEpubsSELECTAVG(price),MAX(price),MIN(price)FROMtitlesGO操纵表中的数据操纵表中的数据 14七月202471【例】为输出列取名的统计查询USEpubsSELECTAVG(price)AS价格平均值,MAX(price)AS价格最大值,MIN(price)AS价格最小值FROMtitlesGO操纵表中的数据操纵表中的数据 14七月202472【例】在输出列上进行运算的查询USEpubsSELECTSUM(price)AS价格总额,AVG(price)AS价格平均值,SUM(price+price*0.1)AS预期价格总额,AVG(price+price*0.1)AS预期价格平均值FROMtitlesGO操纵表中的数据操纵表中的数据 14七月202473在T-SQL中，允许与统计函数如count()，sum()和avg()一起使用DISTINCT关键字来处理列或表达式中不同的值。【例】查询仓储的货物种类。USEpubsSELECTCOUNT(DISTINCTstor_id)FROMsalesGO操纵表中的数据操纵表中的数据 14七月202474若此句不用DISTINCT关键字，则返回结果为21。操纵表中的数据操纵表中的数据 14七月2024752.分组数据分组数据一般情况下，可以根据表中的某一列进行分组，并且使用统计函数，对每一个组只能产生一个单个值。在大多数情况下使用统计函数，返回的是所有行数据的统计结果。统计函数只能产生一个单一的汇总数据。如果需要按某一字段数据的值进行分类，在分类的基础上再进行统计计算，就需要使用GROUPBY子句了。数据分组是指通过GROUPBY子句按一定的条件对查询到的结果进行分组，再对每一组数据统计计算。操纵表中的数据操纵表中的数据 14七月2024761GROUPBY子句GROUPBY子句的语法格式如下：GROUP BY group_by_expression,.n group_by_expression为分组表达式，是执行分组时所依据的一个表达式，通常是一个字段名。text、ntext、image以及bit数据类型的字段不能用在分组表达式中。注：对注：对select子句后面每一列数据除了出现在统计函数中子句后面每一列数据除了出现在统计函数中的列以外，都必须在的列以外，都必须在group by子句中应用。子句中应用。group by子句中不支持对列设置别名，也不支持任何使用子句中不支持对列设置别名，也不支持任何使用了统计函数的集合列。了统计函数的集合列。操纵表中的数据操纵表中的数据 14七月202477【例】按书的种类分类，求出3种类型书籍的价格总和、平均价格以及各类书籍的数量。USEpubsSELECTtype,sum(price)sum_price,avg(price)avg_price,count(*)FROMtitlesWHEREtypein(business,mod_cook,trad_cook)GROUP BY typeGO操纵表中的数据操纵表中的数据 14七月202478执行过程：操纵表中的数据操纵表中的数据 14七月202479操纵表中的数据操纵表中的数据 14七月202480【例】在“图书信息”表中，求出“计算机”、“电子”和“英语”3种类别的图书的价格总和以及平均价格。SELECT图书类别,SUM(定价)AS总价值,AVG(定价)AS平均价格FROM图书信息WHERE图书类别IN(计算机,电子,英语)GROUP BY 图书类别图书类别【例】查询“图书信息”表中各类图书的数量。SELECT图书类别，COUNT(*)AS数量FROM图书信息GROUP BY 图书类别图书类别操纵表中的数据操纵表中的数据 14七月202481也可以根据多列根据多列进行分行分组。这时统计函数按照这些列的惟一组合来进行统计计算。【例】按书的种类和出版商代号分类，返回一个平均价格和总价格SELECTtype,pub_id,avg(price)avg_price,sum(price)sum_priceFROMtitlesWHEREtypein(business,trad_cook,mod_cook)GROUP BY type,pub_idGO操纵表中的数据操纵表中的数据 14七月202482步骤步骤1、查看分组情况、查看分组情况步骤步骤2、查看所需的分组情况、查看所需的分组情况步骤步骤3、根据多列进行分组、根据多列进行分组操纵表中的数据操纵表中的数据 14七月2024832HAVING子句若要输出满足一定条件的分组，则需要使用having关键字。即当完成数据结果的查询和统计后，可以使用HAVING子句来对查询和统计的结果进行进一步的筛选。HAVING子句的语法格式如下：HAVINGserach_condition注：注：WHERE与与HAVING的主要区别是各自的作用对象不同。的主要区别是各自的作用对象不同。WHERE 是从基表或视图中检索满足条件的记录。是从基表或视图中检索满足条件的记录。HAVING 是从所有的组中，检索满足条件的组。是从所有的组中，检索满足条件的组。操纵表中的数据操纵表中的数据 14七月202484【例】查询所有价格超过10美元的书的种类和平均价格SELECTtype,avg(price)avg_priceFROMtitlesWHERE price$10GROUPBYtypeGOSELECTtype,avg(price)avg_priceFROMtitlesWHERE price10GROUPBYtypeHAVING avg(price)$18GO操纵表中的数据操纵表中的数据 14七月202485【例】检索销售量超过25本书的图书的图书代号和销售量SELECTtitle_id,soldTotal=SUM(qty)FROMsalesGROUPBYtitle_idHAVINGSUM(qty)25GO操纵表中的数据操纵表中的数据 用用 COMPUTE 和和 COMPUTE BY 汇总数据汇总数据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)BYtype操纵表中的数据操纵表中的数据 14七月2024933.子查询子查询一条SELECT命令的查询结果是一个表，如果将某个查询的结果作为另外一个查询的WHERE条件的一部分，就形成了查询嵌套查询嵌套。外层的SELCT语句称为外部外部查询，内层的SELECT语句称为子查询子查询。SELECT语句可以嵌套在其他许多语句中，例如SELECT、INSERT、UPDATE或DELETE等。嵌套查询要求服务器在处理最终查询工作之前先生成一个结果，然后根据当前的查询结果再进一步继续下面的查询工作。当一个查询依赖于另外一个查询结果时，那么可以使用子查询。在某些查询中，查询语句比较复杂不容易理解，因此为了把这些复杂的查询语句分解成简单的查询语句，可以使用查询语句。操纵表中的数据操纵表中的数据 14七月202494子查询有两种类型：一种是只返回一个单值的子查询，这时它可以用在一个单值可以使用的地方；返回的单个值被外部查询的比较操作(如，=、!=、=)使用，该值可以是子查询中使用集合函数得到的值。另外一种是返回一列值的子查询，这时它只能用在WHERE子句中。子查询必须使用括号括起来。返回的这个值列表被外部查询的in、notin、any或all比较操作使用。操纵表中的数据操纵表中的数据 14七月202495子查询的使用应该满足一定的限制条件：l在比较运算符的后面的子查询只能包含一个表达式或列名（除了使用EXISTS和IN关键字）。l如果外查询的WHERE字句包含一个列名，那么该列名必须与之查询中的列兼容。lntext，text和image数据类型不允许用在子查询的列中。lDISTINCT关键字不能用在包含GROUPBY子句的子查询中。在T-SQL中，还提供了以下集中与子查询有关的运算方式：（1）ALL（2）ANY|SOME()（3）NOT IN()（4）NOT EXISTS()操纵表中的数据操纵表中的数据 14七月202496【例】查询来自CA州的作家的全部作品和作家的代号USEpubsSELECTtitle_id,au_idFROMtitleauthorWHEREau_idIN(SELECT au_id FROM authors WHERE state=CA)ORDERBYtitle_idGO嵌套子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。操纵表中的数据操纵表中的数据 14七月202497在嵌套查询中使用EXISTS关键字【例】查询来自CA州的作家的全部作品和作家的代号USEpubsSELECTtitle_id,au_idFROMtitleauthorWHEREEXISTS(SELECT*FROM authors WHERE authors.au_id=titleauthor.au_id AND state=CA)ORDERBYtitle_idGO操纵表中的数据操纵表中的数据 14七月202498在嵌套查询中使用NOTEXISTS关键字【例】查询非来自CA州的作家的全部作品和作家的代号USEpubsSELECTtitle_id,au_idFROMtitleauthorWHERENOT EXISTS(SELECT*FROM authors WHERE authors.au_id=titleauthor.au_id AND state=CA)ORDERBYtitle_idGO操纵表中的数据操纵表中的数据 14七月202499【例】查询所有出版了书的作者的信息USEpubsSELECTau_id,au_lname,au_fnameFROMauthorsWHEREau_idIN(SELECT au_id FROM titleauthor)GO在嵌套查询中使用IN关键字操纵表中的数据操纵表中的数据 14七月2024100在嵌套查询中使用NOTIN关键字【例】查询非来自CA州的作家的全部作品和作家的代号USEpubsSELECTtitle_id,au_idFROMtitleauthorWHEREau_idNOT IN(SELECT au_id FROM authors WHERE state=CA)ORDERBYtitle_idGOIN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOTIN表示不属于。操纵表中的数据操纵表中的数据 14七月2024101将查询结果保存到表中将查询结果保存到表中带有INTO子句的SELECT语句（必须在目的数据库内具有createtable权限），允许用户创建一个新表并且把数据插入到新表中。这种方法不同于前面讲述的那些方法。在前面的那些方法有一个共同的特点，就是在数据插入之前表已经存在。但是，使用SELECTINTO语句是在插入数据的过程中建立新表。使用SELECTINTO语句完成建立一个新表新表并向其中插入多条记录。带有INTO子句的SELECT语句的语法形式为：SELECT SELECT_list INTO new_table_name FROM table_list WHERE search_conditions其中，new_table_name表名就是想要创建的新表的名称。新表中包含新表中包含的字段由的字段由SELECT子句字段列表的内容来决定，新表中包含的记录则由子句字段列表的内容来决定，新表中包含的记录则由WHERE子句指定的条件来决定。子句指定的条件来决定。操纵表中的数据操纵表中的数据 14七月2024102【例】使用SELECTINTO语句插入数据SELECTtitle=SUBSTRING(title,1,40),Monthly=ytd_sales/12INTOphonytableFROMtitles操纵表中的数据操纵表中的数据 14七月2024103在select语句中，使用into子句可以将查询的结果存放到一个新建的数据表，也可保存到tempdb库的临时表中。*如果要将查询结果存放到tempdb临时表，则在临时表名前要加“#”号。【例】select*into#李姓表from学生信息表where姓名like李%-刷新当前库的用户表，看看有无【#李姓表】-刷新【tempdb】库的用户表，看看有无【#李姓表】select*from#李姓表-加在当前库可查询其临时表droptable#李姓表-在当前库可删除tempdb的临时表操纵表中的数据操纵表中的数据 14七月20241044.排序技术排序技术排序技术就是使用ORDERBY子句排列查询结果的顺序。ORDERBY的结果依据SQLServer系统安装时配置的排列规则排序。ORDERBY的语法形式如下：SELECT column_name,column_name,FROM table_nameORDER BY column_name ASC|DESC,其中：ABS表示升序，这是默认值；DESC是降序。*多表达式在多表达式在order byorder by子句中的顺序决定排序依据的优先顺序。子句中的顺序决定排序依据的优先顺序。*不能按不能按ntext、text或或image类型的列排序类型的列排序 操纵表中的数据操纵表中的数据 14七月2024105区别：查询pubs数据库中的表stores中商店的名字USEpubsUSEpubsSELE