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

第7讲编辑维护表格数据,一、添加数据1概论2insert命令的各种形式3插入一行数据4插入多行数据二、更新数据三、删除数据,上机:实验3数据操纵,1P118试一试P121试一试P122试一试P123试一试P124试一试P125试一试P129试一试P156试一试P162试一试,一、添加数据1概论,事物是不断变化的，生活也在不断地向前发展.因为数据库的目的是准确地反映现实世界,所以其中的数据也必须随着现实世界的变化而变化.对于SQL程序员来说，这就意味着要使用数据操纵语言(DML)的命令INSERT、DELETE、UPDATE.但这些操作并不是只能对单行数据进行的，你应该从集合的角度来考虑问题.使用SQL修改数据的过程利用了SQLServer全部数据检索能力-强大的检索select、连接、全文检索、子查询和视图.,有几种不同的工具都可以用来向SQLServer发送数据修改命令.但我们的重点并不在于这些向SQLServer发送指定命令的工具,而是在于insert、update和delete命令的应用策略和使用方法.SQLServer提供了两种主要的工具来提交SQL命令：查询分析器和企业管理器的查询设计器.尽管查询分析器不能以可视化的方式来表示连接和列，但它却具有与T-SQL命令有关的丰富特性.而查询设计器两种方式都可以用.,SQL提供了4种形式的insert命令以及select/into命令作为插入数据的主要方法.其中最简单的形式只是插入一行数据，而最复杂的形式则能够使用一个复杂的语句创建一个数据集合，并利用这个数据集合来创建一个表.每一种的insert命令形式都适用于一种特定的任务，这些任务通常与要插入的数据的来源有关.,2insert命令的各种形式,insert命令的各种形式,使用insert/values方法只接收一组数据，所以它只能一次插入一行数据.INSERTINTOowner.table(column,)VALUES(values,)创建insert语句的关键在于保证以正确的顺序排列插入列，并确保要插入值的数据类型与它们所对应的列的数据类型是一致的.,3插入一行数据,当把数据值插入到新的行中时，每个值都对应一个插入列.插入列之间的顺序是任意的-这个顺序与表中列的顺序无关，只要在SQLinsert命令中插入列的顺序与相应的插入值之间的排列顺序保持一致就可以了.INSERTINTOdbo.Guide(lname,fname,G_id)VALUES(smith,Dan,m0123)INSERTINTOdbo.Guide(fname,lname,G_id)VALUES(jeff,Davis,m0253)INSERTINTOdbo.Guide(fname,lname)VALUES(Tammie,Commer),在insert命令中不一定要列出表中的每个列，但只要列出了某个列,那么就必须在insert命令中为它提供相应的插入值.如果为G_id列指定了默认值，就会为它插入默认值而不是空值了.当一个列没有默认值，但却具有NotNull约束时，如果在insert命令中没有为它提供值,那么该insert语句就不能正常执行.,注意：标识列是不能够接受插入的数据值的.所以在忽略插入列的列表时，在插入值的列表中也不要为标识列提供值.插入的数据值也可以是表达式计算的结果值.当需要对数据类型进行转换，或者需要变更、计算、串联数据时，就需要使用这种方法.当已知要插入的数据时（这些数据通常是从用户界面中以变量的形式发送的），使用命令insert/values是最佳的方法.如果数据已经存在于数据库中，则最有效、最灵活的插入数据的方法就是insert/select.,使用insert/select语句，可以将一个查询结果集合中的数据移动到表中.这种方法的真正能力在于：可以使用select命令从几乎所有的数据源中获取数据，并对其进行灵活地加工处理以满足当前的需要.因为select语句可以返回大量的行，所以这种方法也就可以一次插入大量的行了.语法格式如下：INSERTINTOowner.tableSELECTcolumnsFROMdata_sourcesWHEREconditions同样要注意列之间的对应以及数据类型.,例：建立一个新表.SELECT*INTOmyDB.dbo.myauthorsFROMpubs.dbo.authorsWHEREstate=ls演示INSERTmyauthors(au_id,au_lname,au_fname,phone,state,contract)SELECTau_id,au_lname,au_fname,phone,state,contractFROMpubs.dbo.authorsWHEREstate=CA,使用insert/select语句的关键在于选择正确的结果集.所以，最好在执行insert命令之前，首先单独执行一下select语句，以确认结果集是正确的，即：谋定而后动，反复求证以确保万无一失.其它插入数据的方法还有：INSERTowner.table(column,)EXECProcdureName以及使用列的默认值:INSERTowner.tableDEFAULTVALUES,SQL的update命令具有令人不可思议的强大功能，这绝非夸大其词.在其它语言的代码中，需要用数十行代码，使用许多嵌套循环才能完成的工作，现在只需使用一个update命令就可以完成了.更酷的是，SQL并不是一种真正的命令语言，而是一种描述性的语言.你只需要使用SQL代码向查询优化器描述你要做什么，查询优化器就会找到一种基于代码的、优化的查询执行计划来完成这项任务.,二、更新数据概述,update命令的语法和例题SQL的update命令非常简单，也很容易使用.它既可以更新表中某一行中的某一列，也可以更新表中的所有行和所有列.update命令的语法如下：UPDATEdbo.tableSETcolumn=valueorexpressionorcolumncolumn=valueFROMdate_sourcesWHEREconditions,SET关键字用于将表中任意列中的数据修改为新的值.为列指定新值时，可以使用静态的字符串常量、变量、表达式甚至是子句中所引用的数据源中的另一个列.对于任何update语句来说，where子句都是非常重要的.如果不使用它，就会对整个表进行更新.如果使用了where子句,那么只有满足where子句条件的行才会被修改.请一定要反复地检查where子句,确保万无一失.,例：将pub_id=9901的出版者名称改为JoesPress.UPDATEpublishersSETpub_name=JoesPressWHEREpub_id=9901例：汇总每本书在1995年的销售量.UPDATEtitlesSETytd_sales=(SELECTSUM(qty)FROMsalesWHEREsales.title_id=titles.title_idANDord_dateBETWEEN01/01/1995AND12/31/1995)演示,进行全程搜索和替换数据库开发人员经常需要清理数据库中那些有问题的数据.幸运的是，SQL提供了一个replace()函数，可以将它与update命令配合使用来进行全程搜索和替换.下面的例子是在titles中将royalty列中的所有10都替换为15.USEPUBSUPDATEtitlesSETroyalty=replace(royslty,10,15),概述delete命令过于简单了，以至于使用它时很容易会出问题.使用它的基本形式会从表中删除所有的行,并且因为delete命令删除的是整个行，所以使用它时也不需要指定任何列的名字.在delete命令的语法中，虽然where子句是可选的，但是，在使用时，却必须对它加以重点的关注.,三、删除数据,delete命令的语法下面是delete命令的简明语法：DELETEFROMowner.tableFROMdata_sourcesWHEREconditions注意：在上面的语法中，除了delete关键字和表名以外，所有其它部分都是可以省略的.如：DELETEFROMOBX.dbo.Product它删除Product表中所有的数据-删除时不会问你任何问题，也不会给你第二次选择的机会.,SQLServer本身没有提供“撤销”（undo）命令.一旦提交了事务，就再也无法撤销它了.这就是为什么where子句对于删除操作如此重要的原因所在.到目前为止，在绝大多数情况下使用delete都是为了删除一条记录.在这种情况下，通常会使用主键作为条件来选定要删除的行.USEOBXDELETEFROMdbo.ProductWHEREproductID=DB8D8D60-76F4-45C3,删除时引用多个表前面所讲的update命令可以使用from子句将要更新的表和其它的表连接起来,从而在选择行的时候具有更大的灵活性.delete命令也使用了完全相同的技术.使用这项技术需要第二个from关键字.但要注意，与使用update命令的from子句一样,delete命令的第二个from子句也不是ANSI的标准语法.如果可移植性对于你的项目非常重要的话，应该使用子查询来引用其它的表.,下面给出一个删除数据时引用多个表的例子.DELETEProductFROMdbo.ProductJOINProductCategoryONProduct.ProductCategoryID=ProductCategory.ProductCategoryIDWHEREProductCategoryName=Video,级联删除参照完整性要求，从表中的行不能引用主表中不存在的行.这意味着如果在某个从表中有记录行引用了主表中要删除的行,那么删除主表中行的操作就会失败.任何违背参照完整性的删除操作都会被禁止.要解决这个问题,首先必须删除从表中那些引用了要删除的主表记录的行，然后再删除主表中的行.这称为级联删除.,用truncate语句清除表在SQLServer中提供了一种快速删除表中所有行的非记录方法.它比不用where子句的delete语句要快.该语句语法如下：TRUNCATETABLEtable_name例题可参见教科书.,例题程序1：显示含有本年度截止到现在的当前销售额的行，然后按type以递减顺序计算书籍的平均价格和预付款总额.,USEpubsSELECTCAST(titleASchar(20)AStitle,type,price,advanceFROMtitlesWHEREytd_salesISNOTNULLORDERBYtypeDESCCOMPUTEAVG(price),SUM(advance)BYtypeCOMPUTESUM(price),SUM(advance),例题程序2：先删除表T1，然后又创建一个带有四个列的表T1.最后利用INSERT语句插入一些数据行，这些行只有部分列包含值.,IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME=T1)DROPTABLET1GOCREATETABLET1(column_1intidentity,column_2varchar(30)CONSTRAINTdefault_nameDEFAULT(columndefault),column_3intNULL,column_4varchar(40)INSERTINTOT1(column_4)VALUES(Explicitvalue)INSERTINTOT1(column_2,column_4)VALUES(Explicitvalue,Explicitvalue)INSERTINTOT1(column_2,column_3,column_4)VALUES(Explicitvalue,-44,Explicitvalue)SELECT*FROMT1,例题程序3：一个简单的修改语句.,UPDATEpublishersSETcity=Atlanta,state=GA,例题程序4：一个带有WHERE条件的修改语句.,UPDATEauthorsSETstate=PC