项目4 数据插入、删除、修改和查询(冲突时的文件备份2013-08-18 16-09-07)

数据插入、删除、修改和查询项目4技能目标及知识目标√熟悉数据增加、删除、修改的SQL语法；√掌握数据增加、删除、的方法√掌握简单查询，条件查询、连接查询、嵌套查询的方法；√掌握聚合函数的用法；√掌握分组筛选，计算汇总的方法；2项目导引创建数据库和表的目的在于存储数据，系统开发过程中，更多的时候是对表的数据进行管理，包括数据的插入、修改（更新）、删除、查询。通常这些操作都是通过SQL语句来完成的，所以本项目需要重点掌握应用SQL语句来进行数据的管理。数据查询是数据库的重要技术，是数据库的主要应用目的；熟练掌握各种查询为数据库应用系统的开发奠定基础；因此，本项目用三个任务详细学习各种查询。3任务1插入、修改、删除表数据一、任务背景表数据操作。DML、DDL、DCL的概念。4任务1插入、修改、删除表数据二、任务需求1.使用INSERT语句向Product表添加两条记录；2.使用省略INSERT子句列表方法向Product表添加两条数据；3.建立Product备用表格Producr1，然后使用INSERT…SELECT语句的方法插入数据；4.使用SELECT…INTO语句完成新建“飞毛腿商品”表格的同时插入数据；5.将Product表格中所有Catcode为“A01”的商品的Price降低5元；6.使用DELETE语句删除Product表格中价格小于100的商品；7.使用TRUNCATETABLE语句删除Product1表格中的剩余数据。5任务1插入、修改、删除表数据三、任务分析此任务的需求主要是进行表数据的管理，主要包括表数据的插入、修改和删除。通常，表和视图数据管理可使用SQLServer2008管理平台和Transact-SQL语言两种方法来完成。使用SQLserver2008管理平台管理表数据的方法是：启动SQLServer2008管理平台，在“对象资源管理器”中逐级展开其中的树形目录，在GoodsDB数据库下右击要数据管理的具体表格（如Product表、Account表），从弹出的快捷菜单中选择“打开表（O）”命令，而后即可进行表数据的添加、更新与删除。后文中将会具体介绍使用Transact-SQL语言进行数据库管理的操作。6任务1插入、修改、删除表数据四、知识要点（一）数据插入Transact-SQL语言中主要使用INSERT语句向表或视图中插入新的数据行。用于查询的SELECT语句也可用于向表中插入数据。表数据的插入的语法格式如下：INSERT[INTO]{table_name|view_name}{(column_name[,...n]){VALUES({DEFAULT|NULL|expression}[,...n])|derived_table}}7任务1插入、修改、删除表数据四、知识要点（一）数据插入命令说明：（1）INSERT[INTO]：指定要向表中插入数据，INTO可以省略。（2）{table_name|view_name}：表示要插入数据的表或视图的名称。（3）(column_name[,...n])：表示要插入数据的表或视图的列名清单。（4）VALUES：该关键字指定要插入数据的列表清单。（5）{DEFAULT|NULL|expression}[,...n]：该数据列表清单中包括默认值、空值和表达式的数量、次序和数据类型必须与列清单中指定列的定义相匹配。如果在VALUES清单中按表中定义的列的顺序提供每列的值，则可以省略列清单。（6）derived_table：这是一个导入表中数据的SELECT子句。通常INSERT命令一次只能在表中插入一行数据，但可以采用SELECT子句替代VALUES子句，将一张表中的多行数据导入到要插入数据的表中。8任务1插入、修改、删除表数据四、知识要点（二）数据更新Transact-SQL语言中的UPDATE语句用于更新修改表中的数据，该语句可以用于一次修改表中一行或多行数据，其语法格式如下：UPDATEtable_name SET{column_name={expression|DEFAULT|NULL}|@variable=expression|@variable=column_name=expression}[,...n][FROM{<table_source>}[,...n]][WHERE<search_condition>]9任务1插入、修改、删除表数据四、知识要点命令说明：（1）UPDATEtable_name：指定需要更新的表的名称为table_name所表示的名称。（2）SET：该子句表示对指定的列或变量名称赋予新值。（3）column_name={expression|DEFAULT|NULL}：将变量、字符、表达式的值，或默认值，或空值替换column_name所指定列的现有值。不能修改标识列数据。（4）@variable=expression：指定将变量、字符、表达式的值赋予一个已经声明的局部变量。（局部变量见项目5“T-SQL语言编程”任务）（5）@variable=column_name=expression：指定将变量、字符、表达式的值同时赋予一列和一个变量。（6）FROM{<table_source>：表示要依据一个表（可以是本表或其他表）中的数据进行更新操作。（7）WHERE<search_condition>：指定修改数据的条件，如果省略此选项，则修改每一行中的该列数据。有WHERE子句时，仅修改符合WHERE条件的行。（8）在一个UPDATE中，可以一次修改多列的数据，只要在SET后面写入多个列名及其表达式，每个用逗号隔开。（9）UPDATE不能修改具有IDENTITY属性的列值。10任务1插入、修改、删除表数据四、知识要点（三）数据删除Transact-SQL语言中删除表中数据的方法有两种，在指定的表或视图中删除满足给定条件的数据可以使用DELETE语句；如果要清除表中全部数据，则还可以使用TRUNCATETABLE语句。DELETE语句的语法如下：DELETE[FROM]{table_name|view_name}[FROM{<table_source>}][WHERE<search_condition>]命令说明：（1）DELETE语句中的语法项目含义与UPDATE语句相同。（2）WHERE子句给出删除数据所必须满足的条件，省略WHERE子句时将删除所有数据。11任务1插入、修改、删除表数据四、知识要点（3）删除数据TRUNCATETABLE语句的语法格式如下：TRUNCATETABLE[{database_name.[schema_name].|schema_name.}]table_name[;]其中，table_name表示要删除数据的表的名称。TRUNCATETABLE语句的语法说明如下：（1）TRUNCATETABLE语句可删除指定表中的所有数据行，表结构及其所有索引继续保留，为该表所定义的约束、规则、默认值和触发器仍然有效。（2）与DELETE语句相比，TRUNCATETABLE语句删除速度更快。因为DELETE语句在每删除一行时都要把删除操作记录到日志中，而TRUNCATETABLE语句则是通过释放表数据页面的方法来删除表中数据，它只在释放页面后做一次事务日志。（3）使用TRUNCATETABLE语句删除数据后，这些行时不可恢复的，而DELETE操作则可回滚，能够恢复原来数据。（4）TRUNCATETABLE语句不能操作日志，它不能激活触发器，所有TRUNCATETABLE语句不能删除一个被其他表通过FOREIGNKEY约束所参照的表。

12任务1插入、修改、删除表数据五、任务实施1.基本格式插入实例要求：使用INSERT语句向Product表添加两条记录。程序代码如下：insertintoproduct(ProCode,CatCode,ProName,Price,StockNum,Description)Values('A0103','A01','金士顿16G',200,100,'略')Insertintoproduct(ProCode,CatCode,ProName,Price,StockNum,Description)Values('A0208','A02','品胜充电器',154,100,'略')13任务1插入、修改、删除表数据五、任务实施2.省略INSERT子句列表要求：使用省略INSERT子句列表方法插入两条数据。从表数据的插入的语法格式中可以看出，INSERTINTO子句后可以不带列名，但若不带指定列的列表，输入值的顺序必须与表或者视图中的列顺序一致，数据类型、数据精度和小数位数必须与列的列表中对应列一致。程序代码如下，结果如图4.1.4所示：InsertintoproductValues('A0104','A01','乐风数据线',24,56,'略')Insertintoproduct(ProCode,CatCode,ProName,Price,StockNum,Description)Values('A0308','A03','乐风手机音响',98,200,'略')14任务1插入、修改、删除表数据五、任务实施3.使用INSERT…SELECT语句要求：先建立Product备用表格Producr1，然后使用INSERT…SELECT语句的方法插入数。据INSERT...SELECT语句利用SELECT子句的结果集与INSERT语句结合使用，可以将结果集数据插入到制定的表中，该方法可以将一条或多条数据插入表中，也可用于将一个或多个其他表或者视图的值添加到表中。（1）新建Product备用表格Product1CREATETABLEProduct1(ProCodechar(5)NOTNULL,

CatCodechar(3)NOTNULL,

ProNamevarchar(30)NOTNULL, Pricedecimal(10,2)NULL,

StockNum

intNULL, Descriptionvarchar(200)NULL,CONSTRAINTPK_Product1PRIMARYKEY(procode))ON[PRIMARY]15任务1插入、修改、删除表数据五、任务实施3.使用INSERT…SELECT语句要求：先建立Product备用表格Producr1，然后使用INSERT…SELECT语句的方法插入数。据INSERT...SELECT语句利用SELECT子句的结果集与INSERT语句结合使用，可以将结果集数据插入到制定的表中，该方法可以将一条或多条数据插入表中，也可用于将一个或多个其他表或者视图的值添加到表中。（2）在建立备用表格的基础上利用INSERT…SELECT语句插入数据Insertintoproduct1(ProCode,CatCode,ProName,Price,StockNum,Description)SelectProCode,CatCode,ProName,Price,StockNum,DescriptionFromproductwhereprice<10016任务1插入、修改、删除表数据五、任务实施4.使用SELECT…INTO语句要求：使用SELECT…INTO语句完成新建“飞毛腿商品”表格的同时插入数据。使用SELECT…INTO语句可以将SELECT查询的结果集数据插入到指定的新表中。程序代码如下，结果如下图4.1.7所示。SelectProCode,CatCode,ProName,StockNum,DescriptionInto飞毛腿商品

FromproductWhereProNamelike’*飞毛腿*’17任务1插入、修改、删除表数据五、任务实施5.使用UPDATE…SET语句更新数据要求：将Product表格中所有Catcode为“A01”的商品的Price降低5元。程序代码如下：UpdateproductSetprice=price-5WhereCatcode='A01'18任务1插入、修改、删除表数据五、任务实施6.使用DELETE语句删除数据要求：使用DELETE语句删除Product表格中价格小于100的商品。程序代码如下：deletefromproductwhereprice<10019任务1插入、修改、删除表数据五、任务实施7.使用TRUNCATETABLE语句删除数据要求：使用TRUNCATETABLE语句删除Product1表格中的剩余数据。程序代码如下：

TruncatetableProduct120任务1插入、修改、删除表数据六、课堂互动1.向数据表中添加数据的方法有哪几种？2.数据更新操作能不能同时修改一条数据的多个值？21任务2简单查询一、任务背景数据查询能为我们做些什么？在数据库管理工作中，数据查询是最基本最常态化的工作。22任务2简单查询二、任务需求1.查询Product表中的所有记录；2.查询Product表中CatCode，去掉重复值；3.查询Product表格中的ProCode、CatCode和ProName列，返回前5条记录；4.使用列的别名，查询Product表格中ProCode（别名为产品编号）、CatCode（别名为类别编号）和ProName（别名为产品名称）；5.查询Product表格中库存各种商品及价值总金额；6.查询Product表格中Price大于100的产品ProName和ProCode；7.查询Account中Birthday在1990年以后（不包含1990年）的记录；8.查询Product表格中所有“飞毛腿”品牌产品的记录；9.查询Account表格中nick姓“王”或“李”的人员的记录；10.查询Account表格中Birthday在1989年的记录的AccCode、Nick、Email、Address；11.查询Product表格CatCode为A03、A06、B02的产品记录；12.查询Product表格中Description为空的记录；13.将Product表格中的CatCode为A02的产品数据按照Price由高到低的顺序输出。23任务2简单查询三、任务分析本任务的主要作用是进行数据库查询，数据库查询速度的提高是数据库技术发展的重要标志之一，在数据库发展过程中，数据查询曾经是一个非常困难的事情，直到使用了Transact-SQL语言之后，数据库的查询才变得相对简单。在数据库应用中，数据查询是通过SELECT语句来完成的，SELECT语法提供了强大的操作能力，对于数据库开发人员来说，随时可以通过查询来完成。。24任务2简单查询四、知识要点SELECT语句结构SELECT语句能够从数据库中查询出符合用户需求的数据，并将结果以表格的形式返回，是Transact-SQL语言中使用最频繁的语句之一。它功能能强大，所以也有较多的子句，包含主要子句的基本语法格式如下：SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_list[INTOnew_table]FROMtable_source

[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]][COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(列名1)}[,…n][BY列名1[,…n]]25任务2简单查询四、知识要点命令说明：（1）ALL|DISTINCT：DISTINCT关键字用于禁止在查询结果数据集中显示重复的行。ALL关键字允许在查询结果数据集中显示查询到的全部行。默认值为ALL关键字。（2）TOPn[PERCENT]：TOPn用于在查询结果数据集中显示查询到的前n行数据（n为自然数）；TOPnPERCENT用于在查询结果数据集中显示查询到的前百分之n行的数据。（3）select_list：查询所涉及到的列清单。（4）INTOnew_table：将查询结果集保存到新表中。（5）FROMtable_source：查询所涉及到的源表，即从中查询数据的表。（6）WHEREsearch_condition：查询条件。（7）GROUPBYgroup_by_expression：查询的分组汇总表达式。（8）HAVINGsearch_condition：分组汇总结果的筛选条件。（9）ORDERBYorder_expression[ASC|DESC]：查询结果集的排序准则。ASC表示查询结果升序排列，DESC表示降序排列。（10）[COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(列名1)}[,…n][BY列名1[,…n]]：用于对列进行聚合函数计算并生成汇总值，汇总的结果以附加行的形式出现。26任务2简单查询四、知识要点WHERE子句WHERE子句是对表中的行进行选择查询，即通过在SELECT语句中使用WHERE子句可以从数据表中过滤出符合WHERE子句指定选择条件的记录，从而实现行的查询。WHERE子句必须紧跟在FROM子句之后，其基本格式为：WHERE<search_condition>27任务2简单查询四、知识要点说明：search_condition为查询条件，查询条件是一个逻辑表达式，其中可以包含的运算符如下表4-2-1所示。28任务2简单查询五、任务实施1.使用通配符“*”，返回所有列值要求：查询Product表中的所有记录。Select*FromProduct29任务2简单查询五、任务实施2.使用DISRINCT关键字消除重复记录要求：查询Product表中CatCode，去掉重复值。程序如和执行如图4.2.2所示。SelectdistinctcatcodeFromProduct30任务2简单查询五、任务实施3.使用TOPn指定返回查询结果的前n行记录要求：查询Product表格中的ProCode、CatCode和ProName列，返回前5条记录。程序及执行如图4.2.3所示。Selecttop5ProCode,CatCode,ProNameFromProduct另外，top关键字还可以有另外的一种用法，返回前百分之n行，用法为topnpercent，比如要返回结果的前50%，程序如下：Selecttop50percentProCode,CatCode,ProNameFromProduct31任务2简单查询五、任务实施4.使用列别名改变查询结果中的列名要求：使用列的别名，查询Product表格中ProCode（别名为产品编号）、CatCode（别名为类别编号）和ProName（别名为产品名称）。SelectProCodeas产品编号,类别编号=CatCode,ProName

产品名称FromProduct32任务2简单查询五、任务实施5.使用列表达式要求：查询Product表格中库存各种商品及价值总金额。在SELECT子句中可以使用算术运算符对数字型数据列进行加（+）、减（-）、乘（×）、除（÷）和取模（%）运算，构造列表达式，获取经过计算的查询结果。SelectProName

产品名称,Price*StockNumas总金额FromProduct33任务2简单查询五、任务实施6.比较表达式作为查询条件要求：查询Product表格中Price大于100的产品ProName和ProCode。使用比较表达式作为查询条件的一般格式是：expression比较运算符expressionselectProName,procodefromProductwherePrice>10034任务2简单查询五、任务实施7.逻辑表达式作为查询条件要求：查询Account中Birthday在1990年以后（不包含1990年）的记录。使用逻辑表达式作为查询条件的一般格式是：Expressionandexpression或expressionorexpression或notexpression程序及执行结果如图4.2.7所示。select*fromAccountwhereBirthday>'1990-12-31'35任务2简单查询五、任务实施8.模糊查询要求：查询Product表格中所有“飞毛腿”品牌产品的记录。模糊查询使用到谓词LIKE，用于指出一个字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。语法的格式如下：String_expression[NOT]LIKEstring_expressionselect*fromProductwhereProNamelike'%飞毛腿%'36任务2简单查询五、任务实施8.模糊查询通配符列表及用法示例如下表4-2-237任务2简单查询五、任务实施9.模糊查询——单个字符匹配要求：查询Account表格中nick姓“王”或“李”的人员的记录。程序和执行结果如图4.2.9所示。select*fromAccountwhereNicklike'[王李]_'38任务2简单查询五、任务实施10.范围比较要求：查询Account表格中Birthday在1989年的记录的AccCode、Nick、Email、Address。用于范围比较的关键字有两个：BETWEEN和IN。使用BETWEEN关键字可以方便地限制查询数据的范围；使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个值匹配时，即返回TRUE,否则返回FALSE。BETWEEN关键字的语法格式：Expression[NOT] BETWEENexpression1ANDexpression2selectAccCode,Nick,Email,AddressfromAccountwhereBirthdaybetween'1989-1-1'and'1989-12-31'39任务2简单查询五、任务实施11.范围比较——In运算符要求：查询Product表格CatCode为A03、A06、B02的产品记录。程序和执行结果如图4.2.11所示。select*fromProductwhereCatCodein('A03','A06','B02')go40任务2简单查询五、任务实施12.空值比较要求：查询Product表格中Description为空的记录。当需要判定一个表达式的值是否为空值时，使用ISNULL关键字，格式为：ExpressionIS[NOT]NULL说明：当不使用NOT时，若表达式expression为空值，返回TURE，否则返回FALSE；当使用NOT时，结果刚好相反。程序及执行结果如图4.2.12所示。select*fromProductwhereDescriptionisnull41任务2简单查询五、任务实施13.ORDERBY子句排序要求：将Product表格中的CatCode为A02的产品数据按照Price由高到低的顺序输出。在数据库应用中，经常要对查询的结果进行排序输出，比如将商品按照价格的由高到低排序输出。在SELECT语句中