数据库技术 课件 项目6　数据的基本操作

数据库技术6.1数据的增删改6.1数据的增删改

数据的增删改是数据库操作的基础。增加数据使用INSERT语句向表中添加新记录；删除数据则用DELETE语句移除表中的记录；修改数据通过UPDATE语句更新表中已存在的记录。这些操作需谨慎执行，以确保数据的完整性和安全性。插入数据6.1.16.1.1插入数据

数据库通过表对象实现对数据的组织和管理，但是对数据实现管理的前提是首先要将数据写入到数据表中。换句话说，创建好数据表后，需要不断的向表中插入新数据来满足用户的需求。这些数据可以是从其他应用程序中得来的，也可以是新数据直接添加到新建表或已经存在的数据表中。MySQL同样提供两种方式插入数据：一种是图形化的插入方法，一种是使用SQL语句实现数据的插入。要有合适的用户权限保持数据格式与表中定义的一致注意

在MySQL中，每个用户账号都被分配了特定的权限，这些权限决定了用户可以

运行哪些操作。当用户尝试向表中插入数据时，MySQL会检查该用户是否具有对该表的插入权限（INSERT）。如果没有足够的权限，操作将被拒绝，并返回一个错误消息。

对于不同的数据类型，插入的数据格式肯定是不一样的，插入数据时，应严格遵守它们的数据格式要求。6.1.1插入数据1.使用视图化方式插入数据

在Navicat中，可以直接通过在表的“打开表”视图模式下通过直接输入数据的方式来插入新数据。这里以项目5中创建的学生信息管理系统中的四张表：classes、students、courses和scores表为例，表的结构如表6.1~表6.4所示。表6.1classes表结构表6.2students表结构表6.3courses表结构表6.4scores表结构6.1.1插入数据假设四张表都有已知数据，数据内容如表6.5-表6.8所示。说明：数据内容均由计算机随机算法生成。表6.7courses表数据表6.5classes表数据表6.6students表数据6.1.1插入数据表6.8scores表数据6.1.1插入数据任务1：向students表中插入新的数据记录，步骤如下：a.启动Navicat管理工具后，选择连接，找到sims数据库所在的数据库连接，双击或右键选择“打开连接”，以建立与数据库的连接。连接成功后，Navicat的左侧导航栏，找到sims数据库。b.在students表上右击，在弹出的快捷菜单中选择“打开表”选项，如图6.1所示。c.在打开的窗口中即可逐行依次录入所需数据，录入完成效果如图6.2所示，插入完成以后关闭窗口即可，数据将自动保存。图6.1打开“打开表”命令图6.2students表数据插入记录完成6.1.1插入数据注意

：如果你在students表中插入的cls_id数据在classes表中不存在，就会出现如图6.3所示提示框。这是因为students表和classes表存在主外键关联。例如我们插入的71010271的班级号，在classes表中并没有这个班级号，就会出现如下提示框.我们只要在classes表中添加“71010271”的班级号的记录,就可以了。图6.3“无法插入记录”对话框6.1.1插入数据任务2：分别向classes表、courses和scores表中插入如表6.6-6.8所示数据。操作步骤参考任务1所示，数据插入完成后的效果如图6.4-图6.6所示。图6.5courses表插入记录结果图6.6scores表插入记录结果图6.4classes表插入记录结果6.1.1插入数据注意：当在scores表中插入记录，出现如图6.7所示提示框时，要注意检查输入的stu_id和crs_id数据是否存在于students表中的stu_id和courses表中的crs_id，因为外键约束会自动检查数据的完整性，如果出现这种提示框则意味着你的输入数据不一致。图6.7scores表“无法插入记录”对话框6.1.1插入数据2.使用SQL命令插入数据（1）INSERTINTO语句在MySQL中，使用INSERTINTO语句来实现对数据的插入操作。使用INSERT语句、可以向表中添加一行或多行记录，如果没有特定列，则认为是整行插入。语法格式如下：INSERT[IGNORE][INTO]表名[(列名,...)]VALUES(值列表{表达式|DEFAULT},..),(...),...SET列名={表达式|DEFAULT},...6.1.1插入数据参数说明：

[INTO]是可选的，也可以省略。

表名是必须的，用于指定添加数据的表名。

列名列表，用于指定该数据表需要插入的列，可以是一列或者多列，所有列名放在圆括号（）中，列名中间用逗号分隔。如果指定了列名，那么在目标数据表中所有未被指定的列应该支持空值或默认值属性。表的列名是可选的，如果省略，将默认为插入所有列。

值列表，用于指定向数据表中插入的数据值。这些值也必须放在圆括号（）内，如果指定值为多个时，值之间必须用逗号分隔。如果指定了列名，则这些值必须和指定的列一一对应，如果没有指定列名，则该数据必须与目标表中列顺序一一对应。

表达式，可以是一个常量、变量或一个表达式，也可以是空值NULL，其值的数据类型要与列的数据类型一致。例如，列的数据类型为int，插入的数据是‘aaa’就会出错。当数据为字符型时要用单引号括起。

DEFAULT，指定为该列的默认值。前提是该列原先已经指定了默认值。6.1.1插入数据任务3：向students表中插入一条学生记录“23610210710121，张博，女，汉，2002-1-15，2023-9-1，61021071，云南省宜良县，177*****632，空”。（1）MySQL的命令行客户端代码如下：mysql>INSERTINTOstudents(stu_id,stu_name,stu_gender,stu_nation,stu_birth,stu_entrancedate,cls_id,stu_address,stu_phone,stu_resume)->VALUES('23610210710121','张博','女','汉','2002-1-15','2023-9-1','61021071','云南省宜良县','17*****1234',NULL);运行结果如图6.8所示。图6.8插入记录运行结果6.1.1插入数据（2）Navicat图形化界面操作步骤如下：a.在Navicat窗口工具栏中，单击“新建查询”按钮，打开一个查询编辑器窗口，输入插入students表的代码如下：USEsims;INSERTINTOstudents(stu_id,stu_name,stu_gender,stu_nation,stu_birth,stu_entrancedate,cls_id,stu_address,stu_phone,stu_resume)VALUES('23610210710121','张博','女','汉','2002-1-15','2023-9-1','61021071','云南省宜良县','17*****1234',NULL);b.单击工具栏上的“运行”按钮，在“消息”窗格中出现“Affectedrows:1”的提示，说明记录插入成功，运行结果如图6.9所示。c.在students表上右击，在弹出的快捷菜单中选择“打开表”选项，可以查看表中数据已经插入成功，运行结果如图6.10所示。图6.9Navicat插入记录运行结果图6.10插入记录成功后的students表6.1.1插入数据任务4：向classes表中插入两行记录，分别是（51021171，21计算机应用技术1班）和（51021172，21计算机应用技术2班）。代码如下：INSERTINTOclassesVALUES('51021171','计算机应用技术1班'),('51021172','计算机应用技术2班');注意：值列表与值列表之间用“,”分隔，有几行记录就用几个值列表6.1.1插入数据（2）INSERTSELECT语句INSERT语句中的SELECT子查询可用于将一个或多个其它的表或视图的值添加到现有表中，同时使用SELECT子查询可同时插入多行。INSERTSELECT语句可以完成

运行插入一个数据块的功能，该语句相比起使用单独的INSERT语句来说效率要高得多。使用INSERTSELECT语句时，需要遵守如下规则：

在最外面的查询表中插入所有满足SELECT子句的行；

必须检查插入的新行的表是否在数据库中；

必须保证INSERTINTO的表中的数据类型要与SELECT语句返回的数据类型相同；

INSERTINTO中的列数必须与SELECT语句返回的列数相等；

SELECT语句不能从被插入数据的表中选择行。INSERTSELECT语句的语法如下：INSERT[INTO]<表名>[（列名列表）]SELECT<列名列表>FROM<表名列表>WHERE<条件表达式>6.1.1插入数据参数说明：

INSERTINTO<表名>：指定要将数据插入到的目标表的名称。

[（列名列表）]（可选）：在括号中列出目标表中要插入数据的列名。如果省略此部分，则默认插入到目标表的所有列中，但要求SELECT语句返回的列数与目标表的列数完全匹配，并且顺序也要一致。

注意：为了清晰和避免错误，建议明确指定列名列表。

SELECT<列名列表>：指定要选择哪些列的数据。

FROM<表名列表>：指定SELECT语句的数据来源，即一个或多个表。

WHERE<条件表达式>（可选）：用于指定过滤条件，以确定哪些行应该被选中并插入到目标表中，如果省略此部分，则SELECT语句会返回所有行。6.1.1插入数据任务5：将classes表中的所有数据行插入到数据库中已经存在的数据表classes_copy1中。其中classes_copy1表的结构与classes表结构一样。代码如下：INSERTINTOclasses_copy1SELECT*FROMclasses;

更新数据6.1.26.1.2更新数据

1.使用视图化方式更新数据

如果关系表中的数据不正确或需要更新原始数据时，那么要更新这些错误的数据或更新原始的数据，使用对象资源管理器更新记录和添加记录的操作相似。

在Navicat窗口中选择并右击表，然后在快捷菜单中选择“打开表”选项，打开需要更新数据的表，更新相应记录。6.1.2更新数据2.使用SQL语句更新数据（1）UPDATE语句在MySQL数据库中要想修改数据库记录，就需要用UPDATE语句，UPDATE语句就是为了改变数据库中的现存数据而存在的。这条语句虽然有一些复杂的选项，但确实是最容易学习的语句之一。这是因为在大多数情况下，这条语句的高级部分很少使用。在用户看来，UPDATE语句只是用来改变指定行中的数据。但实际的内部情况是MySQL从表中删除旧的数据行并插入新行。UPDATE语句语法格式如下：UPDATE<表名>SET<列名>=<更新值>[WHERE<更新条件>]

参数说明：

表名，指需要修改的表名。

SET后面可以紧随多个数据列的更新值，不限一个，使用逗号分隔。

WHERE子句是可选的，用来限制更新数据的条件。如果不限制，则整个表的所有数据行将被更新。注意：使用UPDATE语句，可以更新一行数据，也可以更新多行数据。6.1.1插入数据任务6：使用UPDATE语句修改记录。将courses表中的“计算机应用基础”课程名称修改为“信息技术”，并且课时调整为56。代码如下：UPDATEcoursesSETcrs_name='信息技术',crs_hours=56WHEREcrs_id='230103';（2）根据表中数据更新行UPDATE语句可以非常灵活的设置SET语句后的子句，从而能灵活的解决各种数据更新问题。任务7：将scores表中学号为“23510102710101”同学的成绩增加5分,同时运行查询语句查看该同学更新后的分数。代码如下：：UPDATEscoresSETsc_grade=sc_grade+5WHEREstu_id='23510102710101';删除数据6.1.36.1.3删除数据

当数据库的添加工作完成后，随着使用和对表中数据的修改，有些数据开始被淘汰，这些多余无用的数据既占用数据库的空间，又会影响表的修改和查询的速度，这时我们需要及时将它们删除。6.1.3删除数据1.使用视图化方式删除数据任务8：删除班级classes表中“计算机应用技术2班”记录。操作步骤如下：a.启动Navicat，找到需要删除记录的classes表。b.在classes表上右击，在弹出的快捷菜单中选择“打开表”选项，打开classes表。c.选择要删除的记录行，点右键，在快捷菜单中选择“删除记录”选项，如图6.11所示。d.在弹出删除确认对话框中单击“删除一条记录”按钮，即可成功删除记录，如图6.12所示。注意：MySQL中支持多行删除数据，可以选择多行数据后同时点右键，选择“删除”命令。在确认删除时，有时也会出现如下图6.13所示提示框，出现这种情况时，是因为classes表和students的外键约束原因，如果删除classes中的部分数据会影响students表中的数据信息，因此在删除数据的时候，需要考虑到数据的完整性。图6.13“删除检查”提示框图6.12“确认删除”命令图6.11“删除”选项6.1.3删除数据2.使用SQL语句删除数据（1）DELETE语句在MySQL中使用DELETE语句来实现数据的删除操作，DELETE语句只包含两个子句，其中一个子句是强制性的。DELETE语句的语法格式如下：：DELETEFROM<表名>[WHERE<删除条件>]

参数说明：

DELETEFROM是必选项，要求指定其中删除行的表的名称；

WHERE子句是可选项，类似于UPDATE和INSERT中的WHERE子语句，要求指定搜索条件。如果在DELETE语句中没有出现WHERE子语句，则意味着删除表中所有行记录。通过在DELETE语句中使用WHERE子句，可以删除表中的单行，多行及所有行数据。如果DELETE语句中没有WHERE子句的限制，表中的所有记录都将被删除。

注意：DELETE语句不能删除记录的某个字段的值，DELETE语句只能对整条记录进行删除。使用DELETE语句只能删除表中的记录，不能删除表本身。删除表本身的命6.1.3删除数据任务9：在scores表中，要删除学号为23510102710101学生的课程号为230203课程的成绩记录.代码如下：DELETEFROMscoresWHEREstu_id='23510102710101'ANDcrs_id='230203';DELETE和DROP的区别：DELETE是删除记录，即使表中所有的记录被删除但表仍然存在；而DROP是删除表，删除表的同时表中的记录自然也不存在。使用DELETE语句删除数据，系统每次删除一行表中的记录，且从表中删除记录行之前，在事务日志中记录相关的删除操作和删除行中的值，在删除失败时，可以用日志来恢复数据。6.1.3删除数据（2）TRUNCATETABLE语句使用TRUNCATETABLE语句将删除指定表中的所有数据，因此也称为清除表数据语句。TRUNCATETABLE语句不记录日志，只记录整个数据页的释放过程，而DELETE语句对每一行的数据修改都记录日志，所以使用TRUNCATETABLE语句进行删除工作比没有指定条件的DELETE语句

运行效率更高。TRUNCATETABLE立即释放表中数据所占的全部空间，同时也释放了分配给索引的全部空间。TRUNCATETABLE语句语法格式如下：TRUNCATETABLE[数据库名.[架构名].]表名6.1.3删除数据与DELETE语句相比，TRUNCATETABLE语句具有以下一些优点：

所用的事务日志空间比较少。DELETE语句每删除一行，都在事务日志中为删除的行记录一个项，TRUNCATETABLE通过释放用于存储表数据的数据页来删除数据，并且在事务日志中只记录页释放。

使用的锁通常较少。当使用行锁

运行DELETE语句时，将会锁定表中各行以便删除，TRUNCATETABLE始终锁定表和页，而不是锁定各行。

运行TRUNCATETABLE后表中将不留下任何页。

运行DELETE语句后，表中将留下空页。在以下一种情况下不能使用TRUNCATETABLE语句来替代无WHERE子句的DELETE语句：

在需要保留标识的情况不能使用TRUNCATETABLE，因为TRUNCATETABLE语句会重新设置种子值。

在需要使用触发器的情况下不能使用TRUNCATETABLE，因为TRUNCATETABLE不会触发触发器。

对于参与了索引视图的表不能使用TRUNCATETABLE语句，注意是索引视图而不是普通视图。使用TRUNCATETABLE语句需要足够的权限才能

运行，首先

运行者必须是表的所有者，具有DBA权限或者表的ALTER权限，对于基表

运行TRUNCATETABLE需要有表的排他访问权限。6.1.3删除数据任务10：删除表classes_copy1表中的所有数据。代码如下：TRUNCATETABLEclasses_copy1;谢谢数据库技术6.2事务管理6.2事务管理

事务管理在数据库系统中扮演着至关重要的角色。它确保了一组数据库操作要么全部成功执行，要么在遇到错误时全部撤销，从而维护了数据的一致性和完整性。事务管理通过锁定机制、并发控制和隔离级别等手段，有效防止了数据冲突和不一致的情况发生。在实际应用中，事务管理对于金融、电商等需要高数据一致性和可靠性的领域尤为重要。通过事务管理，企业可以确保数据的准确性、可靠性和安全性，从而为用户提供更加稳定和高效的服务。事务的概念与特性6.2.16.2.1事务的概念与特性

在MySQL中，事务（Transaction）是指一组数据库操作（如插入、更新、删除等），这些操作被视为一个单一的工作单元。事务必须同时成功或失败，以确保数据库的一致性。事务通常遵循ACID属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

这四个特性共同确保了事务的可靠性和可恢复性，通过将多个操作组合在一个事务中，可以确保数据的完整性，并且在并发情况下，通过隔离性避免了由多个事务同时访问和修改相同数据而引起的问题。2.2.1配置参数介绍事务是一个原子操作，被视为不可分割的最小工作单元。它要么完全执行并且对数据库产生了影响，要么完全不执行。如果事务中的任何一部分操作失败，整个事务将回滚到初始状态，数据库不会受到部分执行操作的影响。01事务开始之前和结束之后，数据库必须保持一致性状态。这意味着事务在执行期间对数据进行的任何更改必须满足事先定义的一组规则（约束条件、触发器等）。如果在事务执行期间违反了一致性约束，事务将回滚并丢弃所有修改。02事务的隔离性确保同时运行的多个事务相互之间是隔离的，每个事务都认为它是在独立运行的。这意味着一个事务的操作和结果对其他事务是不可见的，直到事务提交。通过隔离级别（如读未提交、读已提交、可重复读、串行化）来控制事务之间的隔离程度。03一旦事务被提交，其所做的修改将永久保存在数据库中，并且对于任何后续事务都是可见的。即使在系统故障或崩溃发生时，已提交的事务的修改也会持久保留。04原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持久性（Durability）

事务的隔离级别与并发控制6.2.26.2.2事务的隔离级别与并发控制MySQL事务的隔离级别由低到高分为：读未提交、读已提交、可重复读、串行化。并发控制则通过锁机制和MVCC实现，确保数据一致性和完整性，同时提高系统并发性能。6.2.2事务的隔离级别与并发控制01读未提交（ReadUncommitted）

最低级别的隔离。

事务可以读取其他事务尚未提交的未经确认的数据。

容易导致脏读、不可重复读和幻读的问题。

在学生信息管理系统中，如果事务T1正在修改某个学生的成绩，而事务T2在T1提交之前读取了该成绩，那么T2可能会读取到脏数据（即T1尚未提交的新成绩）。03可重复读（REPEATABLEREAD）

在整个事务过程中，多次读取同样的数据会得到相同的结果。

可以解决不可重复读的问题，但可能出现幻读。即在同一事务中两次查询同一表时，结果可能不同，因为其他事务插入或删除了数据。MySQL的默认隔离级别。

在学生信息管理系统中，如果事务T1在读取某个班级的学生名单时，事务T2在该班级中插入了一个新学生并提交，那么T1在后续读取时不会看到这个新插入的学生（因为T1的隔离级别是可重复读，它会保持对原始数据的视图不变）。02读已提交（READCOMMITTED）

事务只能读取其他事务已经提交的数据。

可以避免脏读问题，可能导致不可重复读问题，即在同一事务中两次读取同一数据时，结果可能不同。

在学生信息管理系统中，如果事务T1已经提交了某个学生的成绩更新，那么事务T2在读取该成绩时，将看到T1提交后的新成绩。04串行化（SERIALIZABLE）

最高级别的隔离。

事务会按照次序依次执行，避免了并发带来的问题。

但会导致数据库并发性能大幅下降。

在学生信息管理系统中，如果采用串行化隔离级别，那么每个事务都会按顺序执行，从而完全避免了脏读、不可重复读和幻读的问题。但这也意味着系统的并发性能会受到严重影响，因为事务需要等待其他事务完成后才能执行。1.事务的隔离级别6.2.2事务的隔离级别与并发控制

并发控制的主要目标是确保数据的一致性和完整性，同时提高系统的并发性能。在学生信息管理系统中，多个用户或事务可能同时尝试访问或修改学生的信息，如查询成绩、更新学生信息或增加新学生等。如果没有有效的并发控制机制，这些并发操作可能会