数据操纵语句大全.ppt

1,第四章数据操纵语句,4.1在SQLServerManagementStudio中查看记录4.2INSERT语句4.3UPDATE语句4.4DELETE语句4.5简单查询4.6数据统计查询,2,4.1在SQLServerManagementStudio中查看记录,4.1.1打开表4.1.2快速定位记录4.1.3返回前几条记录,3,4.1.1打开表,例：在SQLserverManagementStudio中打开“Northwind”数据库中的“products”表。1.启动【SQLserverManagementStudio】，在【对象资源管理器】中展开树形目录，定位到【products】表。2.右击【products】表，在弹出的快捷菜单中选择【打开表】选项。3.此时已经将【products】表打开了，右边对话框显示的是【products】表里的记录内容，该表格对话框名为【结果窗格】。,4,4.1.2快速定位记录,在【结果窗格】的最下方，可以快速定位到上一条、下一条记录，也可以直接跳到第一条、最后一条记录。如果要想跳到某一条记录的话，在文本框内输入数字，再按回车键。,5,4.1.3返回前几条记录,1.在打开表之后，调出【属性】对话框。调出方法为：单击菜单栏中的【视图】|【属性窗口】选项。在【属性】对话框里，展开【TOP规范】选项，在【（最前面）】下拉列表框里选择【是】选项，在【percent】下拉列表框里选择【否】选项，在【表达式】文本框里输入数字“10”。2.选择【结果窗格】，然后再单击【运行SQL】按钮。在【结果窗格】里只显示前十条记录。,6,第四章数据操纵语句,4.1在SQLServerManagementStudio中查看记录4.2INSERT语句4.3UPDATE语句4.4DELETE语句4.5简单查询4.6数据统计查询,7,4.2INSERT语句,4.2.1在SSMS中插入记录4.2.2用INSERT语句记录,8,4.2.1在SSMS中插入记录,注意事项：1.标识列、计算列的字段不能输入字段内容。2.不能为NULL值的字段必须输入字段内容。3.输入字段内容的数据类型要和字段定义的数据类型一致。4.如果字段上有约束，一定要符合约束要求。5.如果要让字段输入默认值，则不用在字段内输入任何数据，在保存记录时，SQLserver会自动填入默认值，但前提是该字段设置有默认值。,9,4.2.2用INSERT语句记录,语法格式如下：INSERTINTOtable_name(column_list)VALUES(expression,.n),10,4.2.2用INSERT语句记录,语法注释：INTO：一个可选的关键字，可以将它用在INSERT和目标表之间。table_name：将要接收数据的表或table变量的名称。(column_list)：要在其中插入数据的一列或多列的列表。必须用圆括号将column_list括起来，并且用逗号进行分隔。VALUES：引入要插入的数据值的列表。对于column_list（如果已指定）中或者表中的每个列，都必须有一个数据值。必须用圆括号将值列表括起来。如果VALUES列表中的值与表中列的顺序不相同，或者未包含表中所有列的值，那么必须使用column_list明确地指定存储每个传入值的列。,11,4.2.2用INSERT语句记录,1.最简单的INSERT语句示范案例1在“Northwind”数据库的“OrderDetails”表里插入一条记录，其中“OrderID”字段的值为10248；“ProductID”字段的值为1；“UnitPrice”字段的值为10；“Quantity”字段的值为2；“Discount”字段的值为0.8。程序清单如下：INSERTorderdetails(orderid,productid,unitprice,quantity,discount)VALUES(10248,1,10,2,0.8),12,4.2.2用INSERT语句记录,2.省略清单的INSERT语句示范案例2在上例中省略字段清单，程序清单如下：INSERTorderdetailsVALUES(10249,2,10,2,0.9),13,4.2.2用INSERT语句记录,3.标识列、允许为空字段的处理示范案例3在“Northwind”数据库的“Categories”表里插入一条记录，其中“CategoryName”字段的值为“图书”；“Description”字段的值为“所有类型的图书”。程序清单如下：INSERTINTOcategories(categoryname,description)VALUES(图书,所有类型的图书),14,4.2.2用INSERT语句记录,4.有默认值字段的处理示范案例4在“学生”数据库中创建“教师”表（性别为有默认值列）。在该表中录入数据。程序清单如下：INSERTINTOteachers(teach_id,teach_name,tsex,tdegree)VALUES(rj001,李波,DEFAULT,本科),15,4.2.2用INSERT语句记录,5.省略VALUES清单的INSERT语句在T-SQL中，有一种简单的插入多行的方法。这种方法是使用SELECT语句查询出的结果代替VALUES子句。这种方法的语法结构如下：INSERTINTOtable_name(column_name1,column_name2,column_namen)SELECTcolumn_name,nFROMtable_nameWHEREsearch_conditions,16,4.2.2用INSERT语句记录,示范案例5在“Northwind”数据库中，从“Employees”表里查出“EmployeeID”、“LastName”、“FirstName”、“PostalCode”、“City”、“Address”、“HomePhone”7个字段，组成一个“雇员通信录”表。,17,4.2.2用INSERT语句记录,程序清单如下：CREATETABLE雇员通信录(雇员IDintprimarykey,姓氏nvarchar(20)notnull,名字nvarchar(10)notnull,邮政编码nvarchar(10)null,城市nvarchar(15)null,地址nvarchar(60)null,家庭电话nvarchar(24)null)GOINSERTINTO雇员通信录SELECTemployeeid,lastname,firstname,postalcode,city,address,homephoneFROMemployeesGO,18,第四章数据操纵语句,4.1在SQLServerManagementStudio中查看记录4.2INSERT语句4.3UPDATE语句4.4DELETE语句4.5简单查询4.6数据统计查询,19,4.3UPDATE语句,4.3.1在SSMS中更新记录4.3.2用UPDATE语句更新记录,20,4.3.1在SSMS中更新记录,注意事项：1.在修改char和nchar这类长度固定的字段时，要注意其后的空格。2.在可以为空值NULL的字段中，如果要设为NULL，可以按Ctrl+0键。3.如果将记录字段内容修改过后，又想回到修改前的值，可用将光标聚焦到该字段，然后按Esc键。4.如果想放弃整条记录的修改，可以连按两次Esc键。,21,4.3.2用UPDATE语句更新记录,语法格式如下：UPDATEtable_nameSETcolumn_name=expression|DEFAULT|NULL,.nFROM,.nWHERE:=table_nameAStable_aliasWITH(,.n),22,4.3.2用UPDATE语句更新记录,语法注释：table_name：需要更新的表的名称。SET：指定要更新的列或变量名称的列表。column_name：含有要更改数据的列的名称。expression|DEFAULT|NULL：列值表达式。：修改数据来源表。,23,4.3.2用UPDATE语句更新记录,示范案例6使用T-SQL语句，将“Northwind”数据库“Employees”表中“EmployeeID”字段值为1的记录的“LastName”字段值修改为“bb”。程序清单如下：UPDATEemployeesSETlastname=bbWHEREemployeeid=1,24,4.3.2用UPDATE语句更新记录,示范案例7用计算值来更新记录，修改“Northwind”数据库“Products”表中“ProductID”字段值为1的记录的“UnitPrice”字段的值，使其值在当前的基础上减少5。程序清单如下：UPDATEproductsSETunitprice=unitprice-5WHEREproductid=1,25,第四章数据操纵语句,4.1在SQLServerManagementStudio中查看记录4.2INSERT语句4.3UPDATE语句4.4DELETE语句4.5简单查询4.6数据统计查询,26,4.4DELETE语句,4.4.1在SSMS中删除记录4.4.2用DELETE语句删除记录4.4.3用Truncatetable语句删除记录,27,4.4.1在SSMS中删除记录,注意事项：1.记录删除之后不能再撤消删除。2.一次可以删除多条记录。3.在选择记录后，按Delete键也可以进行删除操作。4.如果要删除的记录是其他表的外键字段，删除操作可能会影响外键表。,28,4.4.2用DELETE语句删除记录,语法格式如下：DELETEtable_nameFROM,.nWHERE:=table_nameAStable_alias,.n),29,4.4.2用DELETE语句删除记录,语法注释：table_name：是要从其中删除行的表的名称。FROM：指定附加的FROM子句。table_nameAStable_alias：是为删除操作提供标准的表名。WHERE：指定用于限制删除行数的条件。如果没有提供WHERE子句，则DELETE删除表中的所有行。：指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。,30,4.4.2用DELETE语句删除记录,示范案例8将“Northwind”数据库“OrderDetails”表中“OrderID”字段值为10251的记录删除。程序清单如下：DELETEorderdetailsWHEREorderid=10251,31,4.4.2用DELETE语句删除记录,示范案例9在“Northwind”数据库中删除雇员“Nancy”的所有订单。在该例中删除的数据引用的是雇员表中的数据。程序清单如下：select*fromorderswhereemployeeid=1DELETEordersFROMemployeesWHEREemployees.employeeid=orders.employeeidANDemployees.firstname=Nancy,32,4.4.3用TRUNCATETABLE语句删除记录,基本语法格式:TRUNCATETABLEtable_name功能:删除表中的所有记录语法注释：TRUNCATETABLE：为关键字table_name：为要删除所用记录的表名。,33,4.4.3用TRUNCATETABLE语句删除记录,TRUNCATE与DELETE比较：比DELETE快,不记录日志的操作,它将释放表的数据和索引所占据的所有空间及所有为全部索引分配的页,删除的数据是不可恢复的。,34,4.4.3用TRUNCATETABLE语句删除记录,示范案例10删除学生信息管理数据库student中教师表teachers中的所有记录。程序清单如下：DELETEteachers或TRUNCATETABLEteachers,35,第四章数据操纵语句,4.1在SQLServerManagementStudio中查看记录4.2INSERT语句4.3UPDATE语句4.4DELETE语句4.5简单查询4.6数据统计查询,36,4.5简单查询,4.5.1选择表中的若干列4.5.2选择表中的若干记录4.5.3对查询的结果排序,37,4.5简单查询,基本语法格式：SELECTDISTINCT|TOPnPERCENTWITHTIESselect_listFROMtable_listWHEREsearch_conditionsORDERBYorder_listASC|DESC,38,4.5简单查询,注释说明：SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等。FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。WHERE子句用来限定查询的范围和条件。ORDERBY用于指定排序方式。DISTINCT用于消除结果集中的重复值。TOPnPERCENT用于显示前n条或n%条的记录信息.WITHTIES用于在结果集中包含附加记录,在使用ORDERBY子句时,当出现两个或多个记录和最后一条记录的值相等时,这些附加记录也将出现在结果集中。,39,4.5.1选择表中的若干列,1.查询表中的所有列示范案例11查询“Northwind”数据库中Categories表中的所有记录。程序清单如下：USEnorthwindGOSELECT*FROMcategoriesGO,40,4.5.1选择表中的若干列,2.查询表中部分列示范案例12查询“Northwind”数据库中“Categories”表中的“categoryname”和“description”字段。程序清单如下：USEnorthwindGOSELECTcategoryname，DescriptionFROMcategoriesGO,41,4.5.1选择表中的若干列,3.为结果集内的列指定别名格式：表达式AS别名由于计算列不是数据表中的列，所以用到了“AS”关键字来指定列的别名。即使要查询的列是数据表中真实存在的列，也可以用“AS”来指定别名。关键字AS可以省略。,42,4.5.1选择表中的若干列,示范案例13查询“Northwind”数据库中“OrderDetails”表中每个订单的总价。程序清单如下：USEnorthwindGOSELECTorderid,productid,unitprice,quantity,discount,unitprice*(1-discount)*quantityASzongjiaFROMorderdetailsGO,43,4.5.2选择表中的若干记录,1.查看不重复记录DISTINCT例如，在“Northwind”数据库中要查看货物在哪些城市里有过销售记录。在订购表“Orders”中有一个字段是“ShipCity”，通过查询这个字段，就可以了解货物曾经销往过哪些城市。如果程序代码如下：SELECTshipcityFROMorders或SELECTALLshipcityFROMorders,44,4.5.2选择表中的若干记录,如果要显示不重复的记录的话，可以使用如下代码：SELECTDISTINCTshipcityFROMordersDistinct关键字可以同时指定多个字段，例如同时指定两个字段的话，那么查询的结果是两个字段同时不重复的记录，如以下代码：SELECTDISTINCTshipname,shipcityFROMorders,45,4.5.2选择表中的若干记录,示范案例14在“northwind”数据库中“suppliers”表中检索所有行，但每个国家“Country”只显示一次。程序清单如下：USEnorthwindGOSELECTDISTINCTcountryFROMsuppliersGO,46,4.5.2选择表中的若干记录,2.查看满足条件记录WHERE如果只希望得到表中满足特定条件的一些记录，用户可以在查询语句中使用WHERE子句。,47,4.5.2选择表中的若干记录,常用的查询条件：,48,（1）比较大小示范案例15查询“Northwind”数据库中“Products”表中库存量unitinstock不为零的产品。程序清单如下：USEnorthwindGOSELECT*FROMproductsWHEREunitsinstock0GO,4.5.2选择表中的若干记录,49,（2）确定范围示范案例16在“Northwind”数据库中“products”表中检索“unitprice”在$10.00到$20.00之间的所有产品的产品名称和单价。程序清单如下：SELECT*FROMproductsWHEREunitpriceBETWEEN10AND20GO,4.5.2选择表中的若干记录,50,（3）确定集合IN示范案例17在“northwind”数据库中“suppliers”表中检索坐落在日本或意大利的公司列表。程序清单如下：SELECT*FROMsuppliersWHEREcountryIN(Japan,Italy),4.5.2选择表中的若干记录,51,4.5.2选择表中的若干记录,（4）模糊查询通配符：语法格式：notlikeESCAPE%：代表任意长度的字符串（长度可为0）的字符串。_:代表任意单个字符。：表示和方括号中列出的任意一个字符相匹配。:表示不在方括号中列出的任意一个字符。%_%：特殊字符的处理用【】匹配。,52,4.5.2选择表中的若干记录,字符匹配示范案例18在“northwind”数据库“customers”表中检索公司名称中包含字符串“restaurant”公司名称。程序清单如下：SELECT*FROMcustomersWHEREcompanynameLIKE%restaurant%,53,4.5.2选择表中的若干记录,转义字符的使用：示范案例19在“northwind”数据库的类别“categories”表查询类别名称categoryName中包含下划线_的所有的类别记录信息。程序清单如下：SELECT*FROMcategoriesWHEREcategorynameLIKE%_%,54,4.5.2选择表中的若干记录,（5）涉及空值（非空值）查询列表达式isnotnull示范案例20在“northwind”数据库“Customers”表中查看区域为空值的顾客信息。程序清单如下：SELECT*FROMcustomersWHEREregionISNULL,55,4.5.2选择表中的若干记录,（6）多重条件查询示范案例21在“northwind”数据库“products”表中检索产品名称“productname”以字母T开头或产品标识为46,并且单价大于$16.00。程序清单如下：SELECT*FROMproductsWHERE(productnameLIKEt%ORproductid=46)ANDunitprice16,56,4.5.2选择表中的若干记录,3.限制返回行数TOPTOPnPERCENTWITHTIES示范案例22在“Northwind”数据库“orderdetails”订单明细表中查看订购数量最多的五条订单。程序清单如下：SELECTTOP5*FROMorderdetailsORDERBYquantityDESC,57,4.5.3对查询的结果排序,用户可以使用ORDERBY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认为升序。如果不使用ORDERBY子句，则结果集按照记录在表中的顺序排列。基本语法格式：ORDERBY列名，列名DESC|ASC,58,4.5.3对查询的结果排序,示范案例23在“Northwind”数据库“products”产品表中查看所有产品记录，并以产品名排序。程序清单如下：SELECT*FROMproductsORDERBYproductname,59,4.5.3对查询的结果排序,当按多列排序时，先按前面的列排序，如果值相同再按后面的列排序。示范案例24在“Northwind”数据库“products”产品表中查看所有产品记录，并以供应商编号和产品名排序。程序清单如下：SELECT*FROMproductsORDERBYsupplierid,productname,60,第四章数据操纵语句,4.1在SQLServerManagementStudio中查看记录4.2INSERT语句4.3UPDATE语句4.4DELETE语句4.5简单查询4.6数据统计查询,61,4.6数据统计查询,4.6.1使用聚合函数4.6.2对结果进行分组4.6.3用COMPUTE子句来归类,62,4.6.1使用聚合函数,SQLServer提供了许多聚合函数，主要有：,63,4.6.1使用聚合函数,字段的数据类型决定了可以用在该字段上的聚合函数类型。如：SUM和AVG只能用在数据类型代表数字的字段上。,64,4.6.1使用聚合函数,示范案例25在“Northwind”数据库中查询“employees”表中雇员的数目。程序清单如下：SELECTcount(*)AS雇员数目FROMemployees,65,4.6.1使用聚合函数,实验并思考：1）如果本例中将count(*)更改为count(employeeid),其显示的个数为多少？2）如果改为count(city)呢？结果又如何呢？,66,4.6.1使用聚合函数,对包含空值的字段使用聚合函数时，应注意SQLServer的聚合函数（COUNT(*)除外）将忽略字段中的空值。COUNT（*）将计算所有的行，即使每个字段都含有空值。,67,4.6.2对结果进行分组,GROUPBY子句将查询结果集按某一列或多列值分组，分组列的值相等的为一组，并对每一组进行统计计算。对查询结果集分组的目的是为了细化聚合函数的作用对象。GROUPBY子句的语法格式为：GROUPBYALL列名，列名WITHCUBE|ROLLUPHAVING筛选条件表达式,68,4.6.2对结果进行分组,语法注释：ALL：用于指定包含所有组和结果集。BY列名：是按列名指定的字段进行分组。使用WITHCUBE会对GROUPBY所列出的所有分组字段进行汇总运算。使用WITHROLLUP会对GROUPBY所列出的第一个分组字段进行汇总运算。HAVING筛选条件表达式：表示对生成的组筛选后再对满足条件的组进行统计。,69,4.6.2对结果进行分组,70,4.6.2对结果进行分组,示范案例26统计“northwind”数据库“orders”表中每个城市的订单总数。程序清单如下：SELECTshipcity,count(orderid)AS订单总数FROMordersGROUPBYshipcity,71,4.6.2对结果进行分组,使用GROUPBY子句时，应注意SQLServer将为每一组计算一个汇总值，并把汇总值保存在一个字段中。对于指定的一组，SQLServer只生成一条记录，不返回详细信息。SQLServer只对满足WHERE子句的记录进行分组和汇总。不要对可能包含空值的字段使用GROUPBY子句，因为空值也将被当作一组，而不管记录是否满足WHERE子句的条件。,72,USEnorthwindSELECTproductid,orderid,quantityFROMorderdetailsGO,4.6.2对结果进行分组,73,4.6.2对结果进行分组,示范案例27统计“northwind”数据库订购“orders”表中订单总数超过20的城市，以及这些城市的订单总数和总运费。程序清单如下：SELECTshipcity,count(orderid)订单总数,sum(freight)运货费总数FROMordersGROUPBYshipcityHAVINGcount(orderid)20,74,USEnorthwindSELECTproductid,orderid,quantityFROMorderdetailsGO,4.6.2对结果进行分组,75,4.6.2对结果进行分组,示范案例28在“northwind”数据库订购“orders”表中按年份统计每个城市的订单总数。程序清单如下：SELECTshipcity,count(orderid)订单总数,year(orderdate)AS订购年份FROMordersGROUPBYshipcity,year(orderdate)ORDERBYshipcity,year(orderdate),76,4.6.2对结果进行分组,描述,77,4.6.2对结果进行分组,示范案例29统计“Northwind”数据库“orders”表中每个城市的订单总数，并进行汇总。程序清单如下：SELECTshipcity,count(orderid)AS订单总数FROMordersGROUPBYshipcityWITHCUBE,78,4.6.2对结果进行分组,联合使用GROUPBY子句和CUBE操作符，能生成基于GROUPBY子句指定的所有字段的可能组合使用GROUPBY子句和CUBE操作符时，应注意如果在GROUPBY子句中有n个字段或表达式，SQLServer将在结果集中返回2n种可能的组合。结果集中含有NULL的记录代表该记录由CUBE操作符生成。,79,4.6.2对结果进行分组,80,4.6.2对结果进行分组,示范案例30在“Northwind”数据库“orders”表中按年份统计每个城市的订单总数，并按城市进行汇总。程序清单如下：SELECTshipcity,year(orderdate)AS订购年份,count(orderid)AS订单总数FROMordersGROUPBYshipcity,year(orderdate)WITHROLLUP,81,4.6.2对结果进行分组,联合使用GROUPBY子句和ROLLUP操作符，计算组中的汇总值使用GROUPBY子句和ROLLUP操作符时，应注意SQLServer处理GROUPBY中字段列表的顺序是从右到左，然后对每个组使用聚合函数。SQLServer将在结果集中增加一行，这行将显示总和或平均值之类的汇总值，新增的行以NULL标识。,82,4.6.3用COMPUTE子句来归类,COMPUTE子句对查询结果集中的所有记录进行汇总统计，并显示所有参加汇总记录的详细信息。当与by一起使用时，COMPUTE子句在结果集内生成控制中断和小计。可在同一查询内指定COMPUTEBY和COMPUTE。使用语法格式为：COMPUTE聚合函数（列名）BY列名,83,4.6.3用COMPUTE子句来归类,注释说明：聚合函数（列名）:例如SUM（），AVG（），COUNT