数据库系统07-编辑维护表格数据.ppt

第 7 讲 编辑维护表格数据,一、添加数据 1 概论 2 insert 命令的各种形式 3 插入一行数据 4 插入多行数据 二、更新数据 三、删除数据,上机: 实验 3 数据操纵,1 P118 试一试 P121 试一试 P122 试一试 P123 试一试 P124 试一试 P125 试一试 P129 试一试 P156 试一试 P162 试一试,一、添加数据 1 概论,事物是不断变化的，生活也在不断地向前发 展. 因为数据库的目的是准确地反映现实世界,所 以其中的数据也必须随着现实世界的变化而变化. 对于 SQL 程序员来说，这就意味着要使用数 据操纵语言(DML)的命令 INSERT、DELETE、 UPDATE. 但这些操作并不是只能对单行数据进 行的，你应该从集合的角度来考虑问题. 使用SQL修改数据的过程利用了SQL Server 全部数据检索能力-强大的检索 select、连接、 全文检索、子查询和视图.,有几种不同的工具都可以用来向 SQL Server 发送数据修改命令. 但我们的重点并不 在于这些向 SQL Server 发送指定命令的工具, 而是在于 insert、update 和 delete 命令的应 用策略和使用方法. SQL Server 提供了两种主要的工具来提 交 SQL 命令：查询分析器和企业管理器的查 询设计器. 尽管查询分析器不能以可视化的方式来表 示连接和列，但它却具有与 T-SQL 命令有关 的丰富特性. 而查询设计器两种方式都可以用.,SQL 提供了4 种形式的 insert 命令以及 select/into 命令作为插入数据的主要方法. 其 中最简单的形式只是插入一行数据，而最复杂 的形式则能够使用一个复杂的语句创建一个数 据集合，并利用这个数据集合来创建一个表. 每一种的 insert 命令形式都适用于一种特 定的任务，这些任务通常与要插入的数据的来 源有关.,2 insert 命令的各种形式,insert 命令的各种形式,使用 insert/values 方法只接收一组数 据，所以它只能一次插入一行数据. INSERT INTO ownertable (column,) VALUES (values,) 创建insert语句的关键在于保证以正确 的顺序排列插入列，并确保要插入值的数据 类型与它们所对应的列的数据类型是一致的.,3 插入一行数据,当把数据值插入到新的行中时，每个值都对 应一个插入列. 插入列之间的顺序是任意的-这 个顺序与表中列的顺序无关，只要在 SQL insert 命令中插入列的顺序与相应的插入值之间的排列 顺序保持一致就可以了. INSERT INTO dbo.Guide(lname,fname,G_id) VALUES (smith,Dan,m0123) INSERT INTO dbo.Guide(fname, lname,G_id) VALUES (jeff,Davis,m0253) INSERT INTO dbo.Guide(fname, lname) VALUES (Tammie,Commer),在 insert 命令中不一定要列出表中的每个 列，但只要列出了某个列, 那么就必须在 insert 命令中为它提供相应的插入值. 如果为 G_id 列指定了默认值，就会为它插 入默认值而不是空值了. 当一个列没有默认值，但却具有 Not Null 约束时，如果在 insert 命令中没有为它提供值, 那么该 insert 语句就不能正常执行.,注意：标识列是不能够接受插入的数据值 的. 所以在忽略插入列的列表时，在插入值的 列表中也不要为标识列提供值. 插入的数据值也可以是表达式计算的结果 值. 当需要对数据类型进行转换，或者需要变 更、计算、串联数据时，就需要使用这种方法. 当已知要插入的数据时（这些数据通常是 从用户界面中以变量的形式发送的），使用命 令 insert/values 是最佳的方法. 如果数据已 经存在于数据库中，则最有效、最灵活的插入 数据的方法就是 insert/select.,使用 insert/select 语句，可以将一个查 询结果集合中的数据移动到表中. 这种方法的 真正能力在于：可以使用 select 命令从几乎 所有的数据源中获取数据，并对其进行灵活地 加工处理以满足当前的需要. 因为 select 语句可以返回大量的行，所以 这种方法也就可以一次插入大量的行了. 语法格式如下： INSERT INTO ownertable SELECT columns FROM data_sources WHERE conditions 同样要注意列之间的对应以及数据类型.,例：建立一个新表. SELECT * INTO myDB.dbo.myauthors FROM pubs.dbo.authors WHERE state=ls 演示 INSERT myauthors(au_id,au_lname, au_fname,phone,state,contract) SELECT au_id,au_lname,au_fname, phone, state, contract FROM pubs.dbo.authors WHERE state=CA,使用 insert/select 语句的关键在于选择 正确的结果集. 所以，最好在执行 insert 命 令之前，首先单独执行一下 select 语句，以 确认结果集是正确的，即：谋定而后动，反 复求证以确保万无一失. 其它插入数据的方法还有： INSERT ownertable(column,) EXEC ProcdureName 以及使用列的默认值: INSERT owner.table DEFAULT VALUES,SQL 的 update 命令具有令人不可思议的 强大功能，这绝非夸大其词. 在其它语言的代 码中，需要用数十行代码，使用许多嵌套循环 才能完成的工作，现在只需使用一个 update 命令就可以完成了. 更酷的是，SQL 并不是一种真正的命令语 言，而是一种描述性的语言. 你只需要使用 SQL 代码向查询优化器描述你要做什么，查询 优化器就会找到一种基于代码的、优化的查询 执行计划来完成这项任务.,二、更新数据 概述, update 命令的语法和例题 SQL 的 update 命令非常简单，也很容易 使用. 它既可以更新表中某一行中的某一列， 也可以更新表中的所有行和所有列. update 命令的语法如下： UPDATE dbo.table SET column=value or expression or column column=value FROM date_sources WHERE conditions ,SET关键字用于将表中任意列中的数据修 改为新的值. 为列指定新值时，可以使用静态 的字符串常量、变量、表达式甚至是子句中所 引用的数据源中的另一个列. 对于任何 update 语句来说，where 子句 都是非常重要的. 如果不使用它，就会对整个 表进行更新. 如果使用了 where 子句, 那么只 有满足 where 子句条件的行才会被修改. 请一定要反复地检查 where 子句, 确保万 无一失.,例： 将 pub_id=9901 的出版者名称改为 Joes Press. UPDATE publishers SET pub_name=Joes Press WHERE pub_id=9901 例： 汇总每本书在1995年的销售量. UPDATE titles SET ytd_sales=(SELECT SUM(qty) FROM sales WHERE sales.title_id=titles.title_id AND ord_date BETWEEN 01/01/1995 AND 12/31/1995) 演示, 进行全程搜索和替换 数据库开发人员经常需要清理数据库中那些有 问题的数据. 幸运的是，SQL 提供了一个 replace() 函数，可以将它与 update 命令配合使用来进行全 程搜索和替换. 下面的例子是在 titles 中将 royalty 列中的所有10 都替换为15. USE PUBS UPDATE titles SET royalty=replace(royslty, 10,15), 概述 delete 命令过于简单了，以至于使用它时 很容易会出问题. 使用它的基本形式会从表中 删除所有的行, 并且因为 delete 命令删除的是 整个行，所以使用它时也不需要指定任何列的 名字. 在 delete 命令的语法中，虽然 where 子 句是可选的，但是，在使用时，却必须对它加 以重点的关注.,三、删除数据, delete 命令的语法 下面是 delete 命令的简明语法： DELETE FROM ownertable FROM data_sources WHERE conditions 注意：在上面的语法中，除了 delete 关键字和 表名以外，所有其它部分都是可以省略的. 如： DELETE FROM OBX.dbo.Product 它删除 Product 表中所有的数据- 删除时不会 问你任何问题，也不会给你第二次选择的机会.,SQL Server本身没有提供“撤销”（undo）命 令. 一旦提交了事务，就再也无法撤销它了. 这就 是为什么 where 子句对于删除操作如此重要的原 因所在. 到目前为止，在绝大多数情况下使用 delete 都是为了删除一条记录. 在这种情况下，通常会使 用主键作为条件来选定要删除的行. USE OBX DELETE FROM dbo.Product WHERE productID=DB8D8D60-76F4-45C3, 删除时引用多个表 前面所讲的 update 命令可以使用 from子句将 要更新的表和其它的表连接起来, 从而在选择行的 时候具有更大的灵活性. delete 命令也使用了完全 相同的技术. 使用这项技术需要第二个 from 关键 字. 但要注意，与使用 update 命令的 from 子句 一样, delete 命令的第二个 from 子句也不是 ANSI 的标准语法. 如果可移植性对于你的项目非常重要 的话，应该使用子查询来引用其它的表.,下面给出一个删除数据时引用多个表的例子. DELETE Product FROM dbo.Product JOIN ProductCategory ON Product.ProductCategoryID =ProductCategory.ProductCategoryID WHERE ProductCategoryName=Video, 级联删除 参照完整性要求， 从表中的行不能引用主表 中不存在的行. 这意味着如果在某个从表中有记录 行引用了主表中要删除的行, 那么删除主表中行的 操作就会失败. 任何违背参照完整性的删除操作都会被禁止. 要解决这个问题, 首先必须删除从表中那些引用了 要删除的主表记录的行，然后再删除主表中的行. 这称为级联删除., 用 truncate 语句清除表 在 SQL Server 中提供了一种快速删除表中所 有行的非记录方法. 它比不用 where 子句的 delete 语句要快. 该语句语法如下： TRUNCATE TABLE table_name 例题可参见教科书.,例题程序1：显示含有本年度截止到现在的当前销售额的行，然后按type以递减顺序计算书籍的平均价格和预付款总额.,USE pubs SELECT CAST(title AS char(20) AS title, type, price, advance FROM titles WHERE ytd_sales IS NOT NULL ORDER BY type DESC COMPUTE AVG(price), SUM(advance) BY type COMPUTE SUM(price), SUM(advance),例题程序2：先删除表T1，然后又创建一个带有四个列的表T1.最后利用INSERT 语句插入一些数据行，这些行只有部分列包含值.,IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = T1) DROP TABLE T1 GO CREATE TABLE T1 ( column_1 int identity, column_2 varchar(30) CONSTRAINT default_name DEFAULT (column default),column_3 int NULL, column_4 varchar(40) ) INSERT INTO T1 (column_4) VALUES (Explicit value) INSERT INTO T1 (column_2,column_4) VALUES (Explicit value, Explicit value) INSERT INTO T1 (column_2,column_3,column_4) VALUES (Explicit value,-44,Explicit value) SELECT * FROM T1,例题程序3：一个简单的修改语句.,UPDATE publishers SET city = Atlanta, state = GA,例题程序4：一个带有WHERE条件的修改语句.,UPDATE authors SET state = PC, city = Bay City WHERE state = CA AND city = Oakland,例题程序5：通过 UPDATE 语句使用来自另一个表的信息，其修改表 titles 中的 ytd_sales 列，以反映表 sales 中的最新销售记录.,UPDATE titles SET ytd_sales = titles.ytd_sales + sales.qty FROM titles, sales WHERE titles.title_id = sales.title_id AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales),