MySQL数据库技术与应用第5章-编辑数据

第5章编辑数据本章学习目标：掌握插入单行记录和多行记录的方法掌握更新记录的方法，包括根据查询子句更新记录的方法掌握删除记录的方法，包括根据查询子句删除记录的方法以及清空表的方法

工作场景导入学校教务处工作人员在工作中经常会遇到更新教学管理数据库中数据的情况。例如，有以下更新需求：新生入学时，需要大批量插入学生的选课信息；学生的选课信息录入出错时需要进行更改；修改各专业培养方案时，会添加一批新课程、修改一批课程的信息，或者删除一批课程。引导问题：如何插入单个记录或多条记录？如何更新记录？如何删除记录？5.1插入数据对教学管理数据库中的表录入数据、修改和删除数据库可以通过SSMS管理器进行操作。5.1.1插入单行数据使用INSERT语句可向指定表中插入数据。INSERT语法的基本结构如下：INSERT[INTO]<table_name>(column_name1,column_name2…,column_namen)VALUES(values1,values2,…,valuesn)其中，INTO关键字是可选的，可以忽略，teble_name指的是表的名称，column_name1,column_name2…,column_namen指的是表中定义的列名称，这些列必须在表中已经定义，VALUES子句中的值values1,values2,…,valuesn指的是要插入的记录在各列中的取值。INSERT语句中的列名必须与VALUES子句中的值一一对应，且数据类型一致。1.最简单的INSERT语句【例5-1】向教学管理数据库中的课程表中增加一门新课程，课程名称为“SQLServer2014”，其先修课为离散数学（课程号为1008），学分为3分。代码如下：INSERTINTO课程(课程号，课程名，先修课程号，学分)VALUES('1007','SQLServer2014','1002',3)2.省略清单的INSERT语句【例5-2】使用省略清单的INSERT语句向教学管理数据库中的课程表中增加一条新记录，课程名称为“算法设计与分析”，课程号为“1017”，先修课为C语言（课程号为1003），学分为4分。代码如下：INSERTINTO课程VALUES('1006','C语言','1005',3)3.使用DEFAULTVALUES子句上例中向课程表插入新课信息时，如果表中先修课程号与学分这两列允许插入空值或默认值，则插入记录的代码也可以写成如下形式：INSERTINTO课程VALUES('1017','C语言',DEFAULT,NULL)如果表中所有列都允许为空，或者定义有默认值或者其它可自动获取的数据类型，则可以使用INSERT...DEFAULTVALUES语句向表中插入一行仅用默认值的记录，此行记录唯一的参数是表名，列名和数据值都没有。假设课程表中所有列都允许为空或者有默认值，则可以使用如下语句向课程表中插入一条仅含有默认值的记录。INSERTINTO课程DEFAULTVALUES5.1.2插入多行数据利用INSERT…VALUES语句也可以向数据库的表格中插入多行记录，其语法结构如下：INSERT[INTO]<table_name>(column_name1,column_name2…,column_namen)VALUES(val11,val12,…,val1n),(val21,val22,…,val2n),…,(valn1,valn2,…,valnn)其中，需要在VALUES后面输入各条记录的值。上述方法虽然能够达到插入多行数据的目的，但是需要录入大量的数据值，效率过低。下面介绍两种较为高效的多行数据插入方法。5.1.2插入多行数据利用SELECT插入查询结果集在T-SQL语言中，最常用且最简单的插入多行数据的方法是利用INSERT…SELECT语句。它使用SELECT语句查询出的结果代替VALUES子句，将结果集作为多行记录插入到表中。INSERT…SELECT语句借助SELECT语句的灵活性，可以从任何地方抽取任意多行数据，并对数据进行转，从而作为返回结果集插入数据库表中。其语法结构如下：INSERT[INTO]table_name[(column_list)]SELECTcolumn_listFROMtable_nameWHEREsearch_conditions其中，search_conditions指的是查询条件，INSERT表和SELECT表的结果集的列数、列序、数据类型必须一致。【例5-3】创建一个学分表，然后把每位学生选修的课程所获得的学分输入到学分表中。T-SQL代码如下：--创建学分表CREATETABLE学分表(学号char(10)notnull,

姓名varchar(10)notnull,

选修课程门数

tinyint,

学分

tinyint)--插入数据INSERT学分表SELECT学生.学号,姓名,COUNT(选课.课程号),SUM(学分)FROM学生,选课,课程WHERE学生.学号=选课.学号AND选课.课程号=课程.课程号GROUPBY学生.学号,姓名5.1.2插入多行数据5.1.2插入多行数据2.大批量插入数据BULKINSERT语句是批量加载数据的一种方式，它按照用户指定的格式把大量数据插入到数据库的表中。BULKINSERT语句经常与FIELDTERMINATOR子句和ROWTERMINATOR子句一起使用，前者用于指定字段之间的分隔符，后者用于指定行之间的分隔符。【例5-4】使用大批量插入数据记录的方法将txt文件中的数据插入到指定的表中。T-SQL代码如下：--创建表

bulk_tableCREATETABLEbulk_table(c1char(4),c2int)--插入数据BULKINSERTbulk_tableFROM'F:\temp\bulk_test.txt'WITH(FIELDTERMINATOR=';',ROWTERMINATOR='\n')GO5.2修改数据在数据输入过程中，可能会出现输入错误，或是因为时间变化而需要更新数据，这都需要修改数据。修改表中的数据可以使用查询分析器中的网格界面进行修改。本节主要介绍使用T-SQL的UPDATE语句实现数据的修改，UPDATE语句的语法格式如下：UPDATEtable_nameSETcolumn_1=expression_1,column_2=expression_2,…,column_n=expression_n[WHEREsearch_conditions]SET子句后面既可以跟具体的值，也可以是一个表达式。UPDATE语句只更新WHERE子句筛选的行，如果不带WHERE子句，则表中的所有行都将被更新。WHERE子句的条件也可以是一个子查询。5.2.1修改单行数据

【例5-5】将SQLServer2014课程的学分改为4分。T-SQL代码如下：UPDATE课程SET学分=6WHERE课程名='数据结构'执行结果如图5-4所示。如果上例中没有WHERE子句，则表示把所有课程的学分都更新为6分。UPDATE语句还可以同时修改一个表中的多个值。例如：UPDATE课程SET课程名='SQLServer2014数据库设计',学分=2WHERE课程名='SQLServer2014'5.2.2修改多行数据【例5-6】将选课表中所有选修“线性代数”课的学生的成绩减3分。T-SQL代码如下：UPDATE选课SET成绩=成绩-3WHERE课程号IN(SELECT课程号FROM课程WHERE课程名='线性代数')5.3删除数据随着系统的运行，表中可能会产生一些无用的数据，这些数据不仅占用空间，而且还影响查询的速度，所以应该及时地删除。删除数据可以使用DELETE语句和TRUNCATETABLE语句。5.3.1使用DELETE语句删除数据从表中删除数据，最常用的是DELETE语句。DELETE语句的语法格式如下：DELETEFROMtable_name[WHEREsearch_conditions]如果省略了WHEREsearch_conditions子句，就表示删除数据表中的全部数据；如果加上了WHEREsearch_conditions子句就可以根据筛选条件删除表中的指定数据。【例5-7】删除学生表中的所有记录。T-SQL代码如下：DELETEFROM学生本例中没有使用WHERE语句，将删除选课表中的所有记录，只剩下表的定义。用户可以通过“资源管理器”查看。【例5-8】删除课程表中没有学分的记录。T-SQL代码如下：DELETE课程WHERE学分ISNULL【例5-9】删除选课表中姓名为“李华”、选修课程为“1003”的选课信息。T-SQL代码如下：DELETE选课WHERE选课.课程号='1003'AND学号=(SELECT学号FROM学生WHERE姓名='李华')用户在操作数据库时，要谨慎使用DELETE语句，因为执行该语句后，数据会从数据库中永久的被删除。5.4.1使用DELETE语句删除数据5.3.2使用TRUNCATETABLE语句清空表使用TRUNCATETABLE语句删除所有记录的语法格式如下：TRUNCATETABLEtable_name其中，TRUNCATETABLE为关键字，table_name为要删除记录的表名。使用TRUNCATETABLE语句比DELETE语句要快，因为它是逐页删除表中的内容，而DELETE则是逐行删除内容。TRUNCATETABLE是不记录日志的操作，它将释放表的数据和索引所占据的所有空间以及所有为全部索引分配的页，删除的数据是不可恢复的。而DELETE语句则不同，它在删除每一行记录时都要把删除操作记录在日志中。删除操作记录在日志中，可以通过事务回滚来恢复删除的数据。用TRUNCATETABLE和DELETE语句都可以删除所有的记录，但是表结构还在，而DROPTABLE不但删除表中的数据，而且还删除表的结构并释放空间。【例5-10】使用TRUNCATETABLE语句清空课程表。T-SQL代码如下：TRUNCATETABLE课程5.5多数据编辑EditTableData特性使可以将插入、更新和删除语句组合在一起。它仅仅是将所有修改生成到一个动态的脚本文件中。当你运行该脚本文件时，它会按照你在模型中给各修改安排的相同顺序来执行所有修改。当修改超出了一个事务的范围时，会存在在执行所有修改之前暴露部分修改的风险。例如，假设在你