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

1、SQL Server2008数据库项目案例教程数据插入、删除、修改和查询项目4Page 2技能目标及知识目标熟悉数据增加、删除、修改的SQL语法；掌握数据增加、删除、的方法掌握简单查询，条件查询、连接查询、嵌套查询的方法；掌握聚合函数的用法；掌握分组筛选，计算汇总的方法；Page 3项目导引n 创建数据库和表的目的在于存储数据，系统开发过程中，更多的时候是对表的数据进行管理，包括数据的插入、修改（更新）、删除、查询。通常这些操作都是通过SQL语句来完成的，所以本项目需要重点掌握应用SQL语句来进行数据的管理。n 数据查询是数据库的重要技术，是数据库的主要应用目的；熟练掌握各种查询为数据库应用系

2、统的开发奠定基础；因此，本项目用三个任务详细学习各种查询。 Page 4任务1 插入、修改、删除表数据一、任务背景一、任务背景n 表数据操作。n DML、DDL、DCL的概念。Page 5任务1 插入、修改、删除表数据二、任务需求二、任务需求1. 使用INSERT语句向Product表添加两条记录；2. 使用省略INSERT子句列表方法向Product表添加两条数据；3. 建立Product备用表格Producr1，然后使用INSERTSELECT语句的方法插入数据；4. 使用SELECTINTO语句完成新建“飞毛腿商品”表格的同时插入数据；5. 将Product表格中所有Catcode为“A

3、01”的商品的Price降低5元；6. 使用DELETE语句删除Product表格中价格小于100的商品；7. 使用TRUNCATE TABLE语句删除Product1表格中的剩余数据。Page 6任务1 插入、修改、删除表数据三、任务分析三、任务分析n 此任务的需求主要是进行表数据的管理，主要包括表数据的插入、修改和删除。通常，表和视图数据管理可使用SQL Server2008管理平台和Transact-SQL语言两种方法来完成。n 使用SQL server2008管理平台管理表数据的方法是：启动SQL Server2008管理平台，在“对象资源管理器”中逐级展开其中的树形目录，在Goods

4、DB数据库下右击要数据管理的具体表格（如Product表、Account表），从弹出的快捷菜单中选择“打开表（O）”命令，而后即可进行表数据的添加、更新与删除。n 后文中将会具体介绍使用Transact-SQL语言进行数据库管理的操作。 Page 7任务1 插入、修改、删除表数据四、知识要点四、知识要点（一）数据插入（一）数据插入Transact-SQL语言中主要使用INSERT语句向表或视图中插入新的数据行。用于查询的SELECT语句也可用于向表中插入数据。表数据的插入的语法格式如下：INSERT INTO table_name | view_name (column_name ,.n )

5、VALUES( DEFAULT | NULL | expression ,.n )| derived_table Page 8任务1 插入、修改、删除表数据四、知识要点四、知识要点（一）数据插入（一）数据插入命令说明：（1）INSERT INTO ：指定要向表中插入数据，INTO可以省略。（2）table_name | view_name：表示要插入数据的表或视图的名称。（3）(column_name ,.n)：表示要插入数据的表或视图的列名清单。（4）VALUES ：该关键字指定要插入数据的列表清单。（5） DEFAULT | NULL | expression ,.n：该数据列表清单中包括

6、默认值、空值和表达式的数量、次序和数据类型必须与列清单中指定列的定义相匹配。如果在VALUES清单中按表中定义的列的顺序提供每列的值，则可以省略列清单。（6）derived_table：这是一个导入表中数据的SELECT子句。通常INSERT命令一次只能在表中插入一行数据，但可以采用SELECT子句替代VALUES子句，将一张表中的多行数据导入到要插入数据的表中。 Page 9任务1 插入、修改、删除表数据四、知识要点四、知识要点（二）数据更新（二）数据更新Transact-SQL语言中的UPDATE语句用于更新修改表中的数据，该语句可以用于一次修改表中一行或多行数据，其语法格式如下：UPDA

7、TE table_nameSET column_name = expression | DEFAULT | NULL | variable = expression | variable = column_name = expression ,.n FROM ,.n WHERE Page 10任务1 插入、修改、删除表数据四、知识要点四、知识要点命令说明：（1）UPDATE table_name：指定需要更新的表的名称为table_name所表示的名称。（2）SET：该子句表示对指定的列或变量名称赋予新值。（3）column_name = expression | DEFAULT | NULL

8、：将变量、字符、表达式的值，或默认值，或空值替换column_name所指定列的现有值。不能修改标识列数据。（4）variable = expression：指定将变量、字符、表达式的值赋予一个已经声明的局部变量。（局部变量见项目5“T-SQL语言编程”任务）（5）variable = column_name = expression：指定将变量、字符、表达式的值同时赋予一列和一个变量。（6）FROM ：表示要依据一个表（可以是本表或其他表）中的数据进行更新操作。（7）WHERE ：指定修改数据的条件，如果省略此选项，则修改每一行中的该列数据。有WHERE子句时，仅修改符合WHERE条件的行。

9、（8）在一个UPDATE中，可以一次修改多列的数据，只要在SET后面写入多个列名及其表达式，每个用逗号隔开。（9）UPDATE不能修改具有IDENTITY属性的列值。 Page 11任务1 插入、修改、删除表数据四、知识要点四、知识要点（三）数据删除（三）数据删除Transact-SQL语言中删除表中数据的方法有两种，在指定的表或视图中删除满足给定条件的数据可以使用DELETE语句；如果要清除表中全部数据，则还可以使用TRUNCATE TABLE语句。DELETE语句的语法如下：DELETE FROM table_name | view_name FROM WHERE 命令说明：（1）DELE

10、TE语句中的语法项目含义与UPDATE语句相同。（2）WHERE子句给出删除数据所必须满足的条件，省略WHERE子句时将删除所有数据。 Page 12任务1 插入、修改、删除表数据四、知识要点四、知识要点（3）删除数据）删除数据TRUNCATE TABLE语句的语法格式如下：TRUNCATE TABLE database_name.schema_name. | schema_name.table_name ;其中，table_name表示要删除数据的表的名称。 TRUNCATE TABLE语句的语法说明如下：（1）TRUNCATE TABLE语句可删除指定表中的所有数据行，表结构及其所有索引继

11、续保留，为该表所定义的约束、规则、默认值和触发器仍然有效。（2）与DELETE语句相比，TRUNCATE TABLE语句删除速度更快。因为DELETE语句在每删除一行时都要把删除操作记录到日志中，而TRUNCATE TABLE语句则是通过释放表数据页面的方法来删除表中数据，它只在释放页面后做一次事务日志。（3）使用TRUNCATE TABLE语句删除数据后，这些行时不可恢复的，而DELETE操作则可回滚，能够恢复原来数据。（4）TRUNCATE TABLE语句不能操作日志，它不能激活触发器，所有TRUNCATE TABLE语句不能删除一个被其他表通过FOREIGN KEY约束所参照的表。 Pa

12、ge 13任务1 插入、修改、删除表数据五、任务实施五、任务实施1.基本格式插入实例基本格式插入实例要求：使用INSERT语句向Product表添加两条记录。程序代码如下：insert into product (ProCode,CatCode,ProName,Price,StockNum,Description)Values (A0103,A01,金士顿16G,200,100,略)Insert into product (ProCode,CatCode,ProName,Price,StockNum,Description)Values (A0208,A02,品胜充电器,154,100,略)P

13、age 14任务1 插入、修改、删除表数据五、任务实施五、任务实施2.省略省略INSERT子句列表子句列表 要求：使用省略INSERT子句列表方法插入两条数据。从表数据的插入的语法格式中可以看出，INSERT INTO子句后可以不带列名，但若不带指定列的列表，输入值的顺序必须与表或者视图中的列顺序一致，数据类型、数据精度和小数位数必须与列的列表中对应列一致。程序代码如下，结果如图4.1.4所示：Insert into product Values (A0104,A01,乐风数据线,24,56,略)Insert into product (ProCode,CatCode,ProName,Pric

14、e,StockNum,Description)Values (A0308,A03,乐风手机音响,98,200,略) Page 15任务1 插入、修改、删除表数据五、任务实施五、任务实施3.使用使用INSERTSELECT语句语句 要求：先建立Product备用表格Producr1，然后使用INSERTSELECT语句的方法插入数。据INSERT.SELECT语句利用SELECT子句的结果集与INSERT语句结合使用，可以将结果集数据插入到制定的表中，该方法可以将一条或多条数据插入表中，也可用于将一个或多个其他表或者视图的值添加到表中。 （1）新建Product备用表格Product1CREAT

15、E TABLE Product1 (ProCode char(5) NOT NULL,CatCode char(3) NOT NULL,ProName varchar(30) NOT NULL,Price decimal(10, 2) NULL,StockNum int NULL,Description varchar(200) NULL, CONSTRAINT PK_Product1 PRIMARY KEY(procode) ON PRIMARY Page 16任务1 插入、修改、删除表数据五、任务实施五、任务实施3.使用使用INSERTSELECT语句语句 要求：先建立Product备用表

16、格Producr1，然后使用INSERTSELECT语句的方法插入数。据INSERT.SELECT语句利用SELECT子句的结果集与INSERT语句结合使用，可以将结果集数据插入到制定的表中，该方法可以将一条或多条数据插入表中，也可用于将一个或多个其他表或者视图的值添加到表中。 （2）在建立备用表格的基础上利用INSERTSELECT语句插入数据Insert into product1(ProCode,CatCode,ProName,Price,StockNum,Description) Select ProCode,CatCode,ProName,Price,StockNum,Descrip

17、tion From product where price100Page 17任务1 插入、修改、删除表数据五、任务实施五、任务实施4.使用使用SELECTINTO语句语句要求：使用SELECTINTO语句完成新建“飞毛腿商品”表格的同时插入数据。使用SELECTINTO语句可以将SELECT查询的结果集数据插入到指定的新表中。程序代码如下，结果如下图4.1.7所示。Select ProCode,CatCode,ProName,StockNum,Description Into 飞毛腿商品 From product Where ProName like *飞毛腿* Page 18任务1 插入、

18、修改、删除表数据五、任务实施五、任务实施5. 使用使用UPDATESET语句更新数据语句更新数据要求：将Product表格中所有Catcode为“A01”的商品的Price降低5元。程序代码如下：Update product Set price=price-5 Where Catcode=A01 Page 19任务1 插入、修改、删除表数据五、任务实施五、任务实施6.使用使用DELETE语句删除数据语句删除数据要求：使用DELETE语句删除Product表格中价格小于100的商品。程序代码如下：delete from productwhere price100 Page 20任务1 插入、修改

19、、删除表数据五、任务实施五、任务实施7.使用使用TRUNCATE TABLE语句删除数据语句删除数据要求：使用TRUNCATE TABLE语句删除Product1表格中的剩余数据。程序代码如下： Truncate table Product1 Page 21任务1 插入、修改、删除表数据六、课堂互动六、课堂互动n 1.向数据表中添加数据的方法有哪几种？n 2.数据更新操作能不能同时修改一条数据的多个值？Page 22任务2 简单查询一、任务背景一、任务背景n 数据查询能为我们做些什么？n 在数据库管理工作中，数据查询是最基本最常态化的工作 。Page 23任务2 简单查询二、任务需求二、任务需

20、求n 1. 查询Product表中的所有记录；n 2. 查询Product表中CatCode，去掉重复值；n 3. 查询Product表格中的ProCode、CatCode和ProName列，返回前5条记录；n 4. 使用列的别名，查询Product表格中ProCode（别名为产品编号）、CatCode（别名为类别编号）和ProName（别名为产品名称）；n 5. 查询Product表格中库存各种商品及价值总金额；n 6. 查询Product表格中Price大于100的产品ProName和ProCode；n 7. 查询Account中Birthday在1990年以后（不包含1990年）的记录；

21、n 8. 查询Product表格中所有“飞毛腿”品牌产品的记录；n 9. 查询Account表格中nick姓“王”或“李”的人员的记录；n 10. 查询Account表格中Birthday在1989年的记录的AccCode、Nick、Email、Address；n 11. 查询Product表格CatCode为A03 、A06、B02的产品记录；n 12. 查询Product表格中Description为空的记录；n 13. 将Product表格中的CatCode为A02的产品数据按照Price由高到低的顺序输出。 Page 24任务2 简单查询三、任务分析三、任务分析本任务的主要作用是进行数

22、据库查询，数据库查询速度的提高是数据库技术发展的重要标志之一，在数据库发展过程中，数据查询曾经是一个非常困难的事情，直到使用了Transact-SQL语言之后，数据库的查询才变得相对简单。在数据库应用中，数据查询是通过SELECT 语句来完成的，SELECT语法提供了强大的操作能力，对于数据库开发人员来说，随时可以通过查询来完成。 Page 25任务2 简单查询四、知识要点四、知识要点SELECT语句结构语句结构SELECT语句能够从数据库中查询出符合用户需求的数据，并将结果以表格的形式返回，是Transact-SQL语言中使用最频繁的语句之一。它功能能强大，所以也有较多的子句，包含主要子句的

23、基本语法格式如下：SELECT ALL | DISTINCT TOP n PERCENT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC COMPUTE AVG|COUNT|MAX|MIN|SUM( 列名1),n BY列名1 ,n Page 26任务2 简单查询四、知识要点四、知识要点命令说明：（1）ALL | DISTINCT：

24、DISTINCT关键字用于禁止在查询结果数据集中显示重复的行。ALL关键字允许在查询结果数据集中显示查询到的全部行。默认值为ALL关键字。（2）TOP n PERCENT ：TOP n用于在查询结果数据集中显示查询到的前n行数据（n为自然数）；TOP n PERCENT用于在查询结果数据集中显示查询到的前百分之n行的数据。（3）select_list：查询所涉及到的列清单。（4）INTO new_table：将查询结果集保存到新表中。（5）FROM table_source：查询所涉及到的源表，即从中查询数据的表。（6）WHERE search_condition：查询条件。（7）GROUP

25、BY group_by_expression：查询的分组汇总表达式。（8）HAVING search_condition：分组汇总结果的筛选条件。（9）ORDER BY order_expression ASC | DESC ：查询结果集的排序准则。ASC表示查询结果升序排列，DESC表示降序排列。（10） COMPUTE AVG|COUNT|MAX|MIN|SUM( 列名1),n BY列名1 ,n ：用于对列进行聚合函数计算并生成汇总值，汇总的结果以附加行的形式出现。 Page 27任务2 简单查询四、知识要点四、知识要点WHERE子句子句WHERE子句是对表中的行进行选择查询，即通过在SE

26、LECT语句中使用WHERE子句可以从数据表中过滤出符合WHERE子句指定选择条件的记录，从而实现行的查询。WHERE子句必须紧跟在FROM子句之后，其基本格式为：WHERE Page 28任务2 简单查询四、知识要点四、知识要点说明：search_condition为查询条件，查询条件是一个逻辑表达式，其中可以包含的运算符如下表4-2-1所示。 Page 29任务2 简单查询五、任务实施五、任务实施1.使用通配符使用通配符“*”，返回所有列值，返回所有列值要求：查询Product表中的所有记录。Select *From Product Page 30任务2 简单查询五、任务实施五、任务实施2

27、.使用使用DISRINCT关键字消除重复记录关键字消除重复记录要求：查询Product表中CatCode，去掉重复值。程序如和执行如图4.2.2所示。Select distinct catcodeFrom Product Page 31任务2 简单查询五、任务实施五、任务实施3.使用使用TOP n指定返回查询结果的前指定返回查询结果的前n行记录行记录要求：查询Product表格中的ProCode、CatCode和ProName列，返回前5条记录。程序及执行如图4.2.3所示。Select top 5 ProCode,CatCode,ProNameFrom Product 另外，top关键字还可

28、以有另外的一种用法，返回前百分之n行，用法为top n percent，比如要返回结果的前50%，程序如下：Select top 50 percent ProCode,CatCode,ProNameFrom Product Page 32任务2 简单查询五、任务实施五、任务实施4.使用列别名改变查询结果中的列名使用列别名改变查询结果中的列名要求：使用列的别名，查询Product表格中ProCode（别名为产品编号）、CatCode（别名为类别编号）和ProName（别名为产品名称）。Select ProCode as 产品编号,类别编号=CatCode,ProName 产品名称From Pro

29、duct Page 33任务2 简单查询五、任务实施五、任务实施5.使用列表达式使用列表达式要求：查询Product表格中库存各种商品及价值总金额。在SELECT子句中可以使用算术运算符对数字型数据列进行加（+）、减（-）、乘（）、除（）和取模（%）运算，构造列表达式，获取经过计算的查询结果。Select ProName 产品名称,Price*StockNum as 总金额From Product Page 34任务2 简单查询五、任务实施五、任务实施6.比较表达式作为查询条件比较表达式作为查询条件要求：查询Product表格中Price大于100的产品ProName和ProCode。使用比较

30、表达式作为查询条件的一般格式是：expression 比较运算符 expressionselect ProName,procodefrom Productwhere Price100 Page 35任务2 简单查询五、任务实施五、任务实施7.逻辑表达式作为查询条件逻辑表达式作为查询条件要求：查询Account中Birthday在1990年以后（不包含1990年）的记录。使用逻辑表达式作为查询条件的一般格式是：Expression and expression 或expression or expression或not expression程序及执行结果如图4.2.7所示。select *fro

31、m Accountwhere Birthday1990-12-31Page 36任务2 简单查询五、任务实施五、任务实施8.模糊查询模糊查询要求：查询Product表格中所有“飞毛腿”品牌产品的记录。模糊查询使用到谓词LIKE，用于指出一个字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。语法的格式如下：String_expression NOT LIKE string_ expression select *from Productwhere ProName like %飞毛腿% Page 37任务2 简单查询五、任务实施五、任务实施8.模糊查询模糊查询通配符列表及用法示例如下

32、表4-2-2 Page 38任务2 简单查询五、任务实施五、任务实施9.模糊查询模糊查询单个字符匹配单个字符匹配要求：查询Account表格中nick姓“王”或“李”的人员的记录。程序和执行结果如图4.2.9所示。select *from Accountwhere Nick like 王李_ Page 39任务2 简单查询五、任务实施五、任务实施10.范围比较范围比较要求：查询Account表格中Birthday在1989年的记录的AccCode、Nick、Email、Address。用于范围比较的关键字有两个：BETWEEN和IN。使用BETWEEN关键字可以方便地限制查询数据的范围；使用I

33、N关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个值匹配时，即返回TRUE,否则返回FALSE。BETWEEN关键字的语法格式：Expression NOT BETWEEN expression1 AND expression2 select AccCode,Nick,Email,Addressfrom Accountwhere Birthday between 1989-1-1 and 1989-12-31Page 40任务2 简单查询五、任务实施五、任务实施11.范围比较范围比较In运算符运算符要求：查询要求：查询Product表格表格CatCode为为A03 、A06、

34、B02的产品记录。的产品记录。程序和执行结果如图程序和执行结果如图4.2.11所示。所示。select *from Productwhere CatCode in (A03,A06,B02)goPage 41任务2 简单查询五、任务实施五、任务实施12.空值比较空值比较要求：查询Product表格中Description为空的记录。当需要判定一个表达式的值是否为空值时，使用IS NULL 关键字，格式为：Expression IS NOT NULL说明：当不使用NOT时，若表达式expression为空值，返回TURE，否则返回FALSE；当使用NOT时，结果刚好相反。程序及执行结果如图4.2.12所示。select *from Productwhere Description is null Page 42任务2 简单查询五、任务实施五、任务实施13.ORDER BY 子句排序子句排序要求：将Product表格中的CatCode为A02的产品数据按照Price由高到低的顺序输出。在数据库应用中，经常要对查询的结果进行排序输出，比如将商品按照价格的由高到低排序输出。在