数据的查询和修改ppt课件

2005年9月第1页,1,第5章数据的查询和修改,教学内容：数据的查询数据的修改,2005年9月第2页,2,5.1.1显示表中的所有数据,SELECT*FROM表名提示：你可以用星号(*)来指定所有列。例5-1：显示数据库ToyUniverse的表Toys中所有的数据。USEToyUniverse-使用ToyUniverse数据库，后面没有此句都代表该数据库SELECT*FROMToys,2005年9月第3页,3,5.1.2显示一张表上指定列的所有数据,SELECT列名,列名FROM表名提示：列名也可以是经过计算的值。包括几个列的组合。例5-2：现在需要一张包含所有接受者（Recipient）的姓名、城市、电话号码的报表。SELECTvFirstName,vLastName,cCity,cPhoneFROMRecipient,2005年9月第4页,4,5.1.3显示指定的、带用户友好的列标题的列,方法1:SELECT列标题=列名,列名FROM表名方法2:SELECT列名列标题,列名FROM表名方法3:SELECT列名AS列标题,列名FROM表名例5-3：现在需要一张包含所有购物者（Shopper）的姓名、城市、电话号码的报表。SELECT姓名=vFirstName+vLastName,城市=cCity,电话=cPhoneFROMShopper或者：SELECTvFirstName+vLastName姓名,cCity城市,cPhone电话FROMRecipient,2005年9月第5页,5,5.1.4用条件来筛选表中指定的行,1按指定的条件检索并显示数据的SELECT子句的语法是：SELECT选择列表FROM表名WHERE条件当使用比较运算符时，考虑以下几点：表达式中可以包含常数、列名、函数、和通过算术运算符连接的嵌套查询。确保在所有的char、varchar、text、datetime和smalldatetime类型的数据周围添加单引号。虽然也可以用双引号，但为了和ANSI标准兼容，最好用单引号。,2005年9月第6页,6,例5-4：,现在需要一张家住在NewYork的购物者的姓名、城市、电话号码的报表。SELECT姓名=vFirstName+vLastName,城市=cCity,电话=cPhoneFROMShopperWHEREcCity=NewYork,2005年9月第7页,7,2根据多重条件检索,语法是：SELECT选择列表FROM表名WHERENOT条件AND|ORNOT条件当在一句语句中使用多个逻辑运算符时，被处理的顺序是NOT在先、然后是AND、最后是OR。括号可以用来改变处理顺序，也使得表达式的可读性更强例5-5：显示价格范围在$15到$20之间的所有玩具的列表。SELECTcToyId,vToyName,mToyRate,siToyQohFROMToysWHEREmToyRate15ANDmToyRate50,2005年9月第15页,15,5.1.7用TOP限制结果集,有时一个结果集中的数据过多，如果一次全部传到客户端显示，会浪费网络资源，有时候只要检索排好序的顶部几条记录即可。语法为：SELECTTOPnPERCENT列名,列名FROM表名WHERE搜索条件ORDERBY列名,列名这里，n是一个数字。若使用PERCENT关键字，则返回总行数的百分之n（行）。TOP子句限制了结果集中返回的行数。如果在包含TOP的SELECT语句中使用了ORDERBY子句，排序后返回。,2005年9月第16页,16,例5-13：,根据1998年的售出数量显示头5个PickoftheMonth玩具的玩具代码SELECTTOP5cToyId,iTotalSoldFROMPickOfMonthWHEREiYear=1998ORDERBYiTotalSolddesc,2005年9月第17页,17,5.1.8汇总数据,1用COMPUTE和COMPUTEBY汇总数据COMPUTEBY子句可以用同一SELECT语句既查看明细行，又查看汇总行。语法是：SELECT列名,列名.FROM表名ORDERBY列名,列名.COMPUTE集合函数(列名)BY列名,列名注意：COMPUTEBY只能用于已经排序的列。SELECT列表中的列名必须是排过序的并且用在COMPUTEBY子句中。未在COMPUTEBY子句中提及的列不能成为SELECT列表的一部分。在COMPUTE或COMPUTEBY子句中，不能包含ntext、text或image数据类型。提供COMPUTE和COMPUTEBY是为了向后兼容。,2005年9月第18页,18,求和总计示例,USEpubsSELECTtype,price,advanceFROMtitlesORDERBYtypeCOMPUTESUM(price),SUM(advance),USEpubsSELECTtype,price,advanceFROMtitlesORDERBYtypeCOMPUTESUM(price),SUM(advance)BYtype,2005年9月第19页,19,例5-14：,显示一张包含所有订货的订货代码、玩具代码和所有订货的玩具价格的报表。该报表应该既显示每次订货的总计又显示所有订货的总计。SELECTcOrderNo,cToyId,mToyCostFROMOrderDetailOrderbycOrderNoComputeSUM(mToyCost)bycOrderNoComputeSUM(mToyCost),2005年9月第20页,20,COMPUTE和GROUPBY之间的区别,GROUPBY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。COMPUTE生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子聚合，或SELECT语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在COMPUTE子句中指定，而不是在选择列表中。,2005年9月第21页,21,2用CUBE汇总数据,CUBE运算符生成的结果集是多维数据集。多维数据集是一个结果集，其中包含了各维度的所有可能组合的交叉表格。CUBE运算符在SELECT语句的GROUPBY子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUPBY应指定维度列和关键字WITHCUBE。结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。,2005年9月第22页,22,用CUBE汇总数据示例,将包含Item和Color的所有可能组合的Quantity小计SELECTItem,Color,SUM(Quantity)ASQtySumFROMInventoryGROUPBYItem,ColorWITHCUBE,2005年9月第23页,23,3用ROLLUP汇总数据,在生成包含小计和合计的报表时，ROLLUP运算符很有用。ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。CUBE和ROLLUP之间的区别在于：CUBE生成的结果集显示了所选列中值的所有组合的聚合。ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。,2005年9月第24页,24,用ROLLUP汇总数据示例,SELECTItem,Color,SUM(Quantity)ASQtySumFROMInventoryGROUPBYItem,ColorWITHROLLUP,2005年9月第25页,25,ROLLUP和COMPUTEBY比较,ROLLUP操作的结果集具有类似于COMPUTEBY所返回结果集的功能；然而，ROLLUP具有下列优点：ROLLUP返回单个结果集；COMPUTEBY返回多个结果集，而多个结果集会增加应用程序代码的复杂性。ROLLUP可以在服务器游标中使用；COMPUTEBY不可以。有时，查询优化器为ROLLUP生成的执行计划比为COMPUTEBY生成的更为高效。,2005年9月第26页,26,5.1.9字符串函数,2005年9月第27页,27,5.1.9字符串函数,2005年9月第28页,28,例5-15：,显示玩具名、说明、所有玩具的价格。但是，只显示说明的前40个字母。SELECTvToyName,Description=Substring(vToyDescription,1,20),mToyRateFROMtoys,2005年9月第29页,29,5.1.10日期函数,2005年9月第30页,30,日期元素,2005年9月第31页,31,例5-16：,按以下格式显示所有运货的报表：,提示:运送天数（DaysinTransit）=实际交付日期（Actualdeliverydate）运货日期（Shipmentdate）SELECTOrderNumber=cOrderNo,ShipmentDate=dShipmentDate,ActualDeliveryDate=dActualDeliveryDate,DayinTransit=DATEDIFF(dy,dShipmentDate,dActualDeliveryDate)FROMshipment,2005年9月第32页,32,5.1.11数学函数,2005年9月第33页,33,5.1.11数学函数,ROUND(数值表达式,长度)这里，数值表达式是需要进行四舍五入的表达式。长度是表达式四舍五入的精度。如果长度是正数，则表达式四舍五入到小数点的右边。如果长度是负数，则表达式四舍五入到小数点的左边。,2005年9月第34页,34,5.1.12模糊查询,2005年9月第35页,35,5.1.12模糊查询,2005年9月第36页,36,例5-17：,显示所有名字以S开头的购物者。SELECTcShopperId,cPassword,vFirstName,vLastNameFROMShopperWHEREvFirstNamelikeS%,2005年9月第37页,37,5.1.13内联接,用单句SELECT语句显示多张表中的数据。为了这个目的，这些表中必须有一个对等的公共列。这称为简单联接或内联接。内联接是用比较运算符比较要联接列的值的联接。语法：SELECT列名,列名,列名FROM表名INNERJOIN表名ON表名.引用列名连接操作符表名.引用列名注意：如果SELECT列表中的列名被*所取代，则所有表中的所有列都将在相关行中显示。,2005年9月第38页,38,5.1.13内联接,例5-18：显示所有玩具的名称及其所属的类别名称。SELECTvToyName,cCategoryFROMToysJOINCategoryONToys.cCategoryId=Category.cCategoryID例5-19：显示所有玩具的名称、商标名称和类别名称。SELECTvToyName,cBrandName,cCategoryFROMToysASaINNERJOINCategoryASbONa.cCategoryId=b.cCategoryIdINNERJOINToyBrandAScONa.cBrandId=c.cBrandId,2005年9月第39页,39,5.1.14外联接,显示一张表的全部记录和另一张表的部分记录。这种类型的联接称为外联接。左向外联接右向外联接完整外部联接。语法：SELECT列名,列名,列名FROM表名LEFTRIGHT|FULLOUTERJOIN表名ON表名.引用列名连接操作符表名.引用列名这里，连接操作符可以是=、=、。注意：OUTERJOIN只可能发生在两个表之间,2005年9月第40页,40,例5-20：,显示所有玩具的名称和购物车代码。如果玩具不在购物车上，则应显示NULL。SELECTvToyName,cCartIdFROMToysLEFTOUTERJOINShoppingCartONToys.cToyId=ShoppingCart.cToyId,2005年9月第41页,41,5.1.15合并查询结果集,将不同查询的输出结果合并成单一的结果集。这时就可以使用UNION操作符，要将两张表中的数据合并为单一的输出。语法：SELECT列名,列名FROM表名UNIONALLSELECT列名,列名FROM表名注意：结果集的列标题是第一个SELECT语句的列标题。后续的SELECT语句中的所有列必须具有同第一个SELECT语句中的列相似的数据类型，而且列数也必须相似。缺省情况下，UNION子句将移去重复行。如果使用了ALL，这些重复行也将显示。,2005年9月第42页,42,例5-21：,显示购物者和接收者的名字、姓、地址和城市。SELECTvFirstName,vLastName,vAddress,cCityFROMShopperUNIONSELECTvFirstName,vLastName,vAddress,cCityFROMRecipient,2005年9月第43页,43,5.1.16子查询,在一个SELECT语句的WHERE子句或HAVING子句中嵌套另一个SELECT语句的查询称为嵌套查询，又称子查询。子查询是SQL语句的扩展，其语句形式如下。语法：SELECT，.FROMWHERE表达式（SELECT,.FROM）,2005年9月第44页,44,1使用比较运算符连接子查询,子查询可由一个比较运算符（=、=、,!或15,2005年9月第55页,55,3INSERTSELECT语句,从一个表向另一个已经存在的表添加数据。语法：INSERTINTO表名1SELECT列名)FROM表名2WHERE条件这里：表名1指定了将要插入数据的表的名字。列名指定了你需要从现有表复制到新表的列的名字。表名2指定了从中复制数据的表。条件指定了插入的行要满足的条件。例5-27：将表OldItems中的所有属性、所有行插入表Items。INSERTINTOOldToysSELECT*FROMToys,2005年9月第56页,56,5.2.2数据的更新,SQLServer提供了UPDATE语句来进行修改数据。一行中的一列是更新的最小单元。语法：UPDATE表名SET列名=值,列名=值FROM表名WHERE条件这里，表名指定了你要修改的表的名字。列名指定了在特定表中你所要修改的列。值指定了你要赋给表列的值。表达式、列名、变量名等都是合法值。也可以使用DEFAULT和NULL关键字。FROM表名指定了在UPDATE语句中使用的表。当表名中仅包含常数、变量和算术表达式时，不需要使用该项。,2005年9月第57页,57,5.2.2数据的更新,提示：同一时刻只能对一张表进行更新。如果一次更新违背了完整性约束，则所有的更新都将被回滚，也就是说，表没有发生任何变化。使用UPDATE更新数据时，会将被更新的原数据存放到事务处理日志中。如果所更新的表特别大，则有可能在命令尚未执行完时，就将事务处理日志填满了。这时SQLServer会生成错误信息，并将更新过的数据返回原样。解决此问题有两种办法：一种是加大事务处理日志的存储空间，但这似乎不大合算；另一种是分解更新语句的操作过程，并及时清理事务处理日志。例如，将更新命令分解为两个命令，在其间插入BACKUPLOG命令将事务处理日志清除。还可以基于子查询实现数据更新。,2005年9月第58页,58,例5-27：,使由NewMoonBooks出版的所有书籍的价格加倍。该查询更新titles表；其子查询引用publishers表。UPDATEtitlesSETprice=price*2WHEREpub_idIN(SELECTpub_idFROMpublishersWHEREpub_name=NewMoonBooks),2005年9月第59页,59,5.2.3数据的删除,语法：DELETEFROM表名FROM表WHERE条件这里，表名是你要从中删除行的表的名字。表是设置删除条件所需的表的名字。条件是指定了要删除的行应符合的条件。,2005年9月第60页,60,TRUNCATETABLE命令,要删除表中的所有数据，那么使用TRUNCATETABLE命令比用DELETE命令快得多。因为DELETE命令除了删除数据外，还会对所删除的数据在事务处理日志中作记录，以防止删除失败时可以使用事务处理日志来恢复数据；而TRUNCATETABLE则只做删除与表有关的所有数据页的操作。但是TRUNCATETABLE命令不能用于被别的表的外关键字依赖的表。语法如下：TRUNCATETABLEtable_name注意：由于TRUNCATETABLE命令不会对事务处理日志进行数据删除记录操作，因此不能激活触发器。,2005年9月第61页,61,例5-28：,删除商业书籍的所有销售记录：USEPubsDELETEsalesWHEREtitle_idIN(SELECTtitle_idFROMtitlesWHEREtype=business),2005年9月第62页,