数据库原理及应用（MySQL项目式版）- 课件 项目4 数据操作

项目4数据操作项目目标熟练掌握INSERT、UPDATE和DELETE语句的使用。了解不同的数据完整性约束对数据操作结果的影响。任务4.1插入数据INSERT语句的格式：INSERT[IGNORE][INTO]

表名(字段名称1[,

字段名称2…])VALUES({表达式1|DEFAULT}[,{表达式2|DEFAULT}…]);（1）IGNORE：当插入不符合数据完整性约束的数据时，不执行该语句，而是发出警告。（2）INTO表名：指定待插入数据的表的名称。（3）字段名称：指定需要插入数据的字段名称。（4）VALUES子句：指定各个字段需要插入的具体数据。（5）表达式：可以是常量、变量、计算表达式，其值的数据类型要与字段的数据类型一致。（6）DEFAULT：插入该字段的默认值。4.1插入数据1．插入一条完整的数据【例4-1】向图书表book中插入一条完整的书目数据（100582，大学物理，李大国，博达教育出版社，2020-03-01，43.90，2）。（1）在插入数据时省略字段名称，数据的顺序必须和数据表结构的字段顺序相同。INSERTINTObook

VALUES('100582','大学物理','李大国','博达教育出版社','2020-03-01',43.90,2);mysql>SELECT*FROMbook;+---------+----------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------+--------+----------------+------------+-------+----------+|100582|大学物理|李大国|博达教育出版社|2020-03-01|43.90|

2|+---------+----------+--------+----------------+------------+-------+----------+1rowinset(0.00sec)4.1插入数据1．插入一条完整的数据【例4-1】向图书表book中插入一条完整的书目数据（100582，大学物理，李大国，博达教育出版社，2020-03-01，43.90，2）。（1）在插入数据时省略字段名称，数据的顺序必须和数据表结构的字段顺序相同。INSERTINTObook

VALUES('100582','大学物理','李大国','博达教育出版社','2020-03-01',43.90,2);（2）在插入数据的不省略字段名称INSERTINTObook(book_id,title,author,publisher,pubdate,price,stocknum)VALUES('100582','大学物理','李大国','博达教育出版社','2020-03-01',43.90,2);4.1插入数据2．插入一条数据的部分字段值【例4-2】向图书表book中插入一条书目数据（110001，平面设计基础，职帆启航出版社），仅插入图书编号book_id、书名title和出版社publisher字段的数据。INSERTINTObook(book_id,title,publisher)

VALUES('110001','平面设计基础','职帆启航出版社');mysql>SELECT*FROMbook;+---------+--------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+--------------+--------+----------------+------------+-------+----------+|100582|大学物理|李大国|博达教育出版社|2020-03-01|43.90|2||110001|平面设计基础|NULL

|职帆启航出版社|NULL

|NULL|

1|+---------+--------------+--------+----------------+------------+-------+----------+2rowsinset(0.00sec)stocknum字段插入了默认值1，未指定默认值的字段值插入了NULL。4.1插入数据3．插入多条记录的数据，各条数据之间用英文逗号隔开。【例4-3】向图书表book中插入3条书目数据（100023，计算机科学导论，刘晓婷，博达教育出版社）、（110705，空间数据库原理，宋晓松，职帆启航出版社）和（120087，程序设计基础，赵晓妆，知行联合出版社）。插入数据仅涉图书编号book_id、书名title、作者author和出版社publisher字段。INSERTINTObook(book_id,title,author,publisher)

VALUES('100023','计算机科学导论','刘晓婷','博达教育出版社'),

('110705','空间数据库原理','宋晓松','职帆启航出版社'),

('120087','程序设计基础','赵晓妆','知行联合出版社');mysql>SELECT*FROMbook;+---------+----------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------------+--------+----------------+------------+-------+----------+|100023|计算机科学导论|刘晓婷|博达教育出版社|NULL

|NULL|

1||100582|大学物理

|李大国|博达教育出版社|2020-03-01|43.90|

2||110001|平面设计基础|NULL

|职帆启航出版社|NULL|NULL|

1||110705|空间数据库原理|宋晓松|职帆启航出版社|NULL

|NULL|

1||120087|程序设计基础

|赵晓妆|知行联合出版社|NULL

|NULL|

1|+---------+----------------+--------+----------------+------------+-------+----------+5rowsinset(0.00sec)4.1插入数据4.插入查询结果中的数据INSERT[IGNORE][INTO]表名1[(字段名称1[,字段名称2,…])]SELECT*FROM表名2;【例4-4】新建数据表newbook，其结构与图书表book完全相同，然后将book表中的所有数据插入newbook表中。CREATETABLEnewbookLIKEbook;INSERTINTOnewbookSELECT*FROMbook;

mysql>SELECT*FROMnewbook;+---------+----------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------------+--------+----------------+------------+-------+----------+|100023|计算机科学导论|刘晓婷|博达教育出版社|NULL

|NULL|

1||100582|大学物理

|李大国|博达教育出版社|2020-03-01|43.90|

2||110001|平面设计基础|NULL

|职帆启航出版社|NULL|NULL|

1||110705|空间数据库原理|宋晓松|职帆启航出版社|NULL

|NULL|

1||120087|程序设计基础

|赵晓妆|知行联合出版社|NULL

|NULL|

1|+---------+----------------+--------+----------------+------------+-------+----------+5rowsinset(0.00sec)4.1插入数据5．插入并替换已存在的数据REPLACE语句的语法格式与INSERT语句基本相同。但在插入的数据不满足主键约束时，REPLACE语句可以在插入数据之前将与新数据冲突的旧数据删除，使新数据能够正常插入。4.1插入数据【例4-5】向数据表newbook中插入两条完整的书目数据（100023，计算机科学导论，刘晓婷，博达教育出版社，2019-08-01，49.80，2）和（120034，学术英语写作，王丽，知行联合出版社，2017-11-01，46.90，2），其中有一条数据与数据表中已有的数据完全相同。REPLACEINTOnewbook

VALUES('100023','计算机科学导论','刘晓婷','博达教育出版社','2019-08-01',49.80,2),

('120034','学术英语写作','王丽','知行联合出版社','2017-11-01',46.90,2);

如果用INSERT语句插入这两条数据，则执行结果将出错。INSERTINTOnewbookVALUES('100023','计算机科学导论','刘晓婷','博达教育出版社','2019-08-01',49.80,2),('120034','学术英语写作','王丽','知行联合出版社','2017-11-01',46.90,2);ERROR1062(23000):Duplicateentry'100023'forkey'newbook.PRIMARY'任务4.2修改数据UPDATE语句格式：UPDATE表名SET字段名称1=值1[,字段名称2=值2…][WHERE条件表达式];1．修改指定条件的数据【例4-6】将数据表newbook中图书编号book_id为110001的记录的作者author修改为李明明。UPDATEnewbookSETauthor='李明明'

WHEREbook_id='110001';任务4.2修改数据【例4-7】将数据表newbook中图书编号book_id为110001的记录的出版日期pubdate修改为2018-07-01，定价price修改为52.00。UPDATEnewbookSETpubdate='2018-07-01',price=52.00WHEREbook_id='110001';mysql>SELECT*FROMnewbook;+---------+----------------+--------+----------------+------------+-------+----------+|book_id|title

|author|publisher

|pubdate

|price|stocknum|+---------+----------------+--------+----------------+------------+-------+----------+|100023|计算机科学导论|刘晓婷|博达教育出版社|NULL

|NULL|

1||100582|大学物理

|李大国|博达教育出版社|2020-03-01|43.90|

2||110001|平面设计基础|李明明|职帆启航出版社|2018-07-01|52.00|

1||110705|空间数据库原理|宋晓松|职帆启航出版社|NULL

|NULL|

1||120034|学术英语写作

|王丽|知行联合出版社|2017-11-01|46.90|

2||120087|程序设计基础|赵晓妆|知行联合出版社|NULL|NULL|1|+---------+----------------+--------+----------------+------------+-------+----------+6rowsinset(0.00sec)任务4.2修改数据2．修改全部数据【例4-8】将数据表newbook中作者author字段的值均修改为“第一作者+姓名”的形式。例如，图书编号book_id为100023的记录的author应修改为“第一作者刘晓婷”。UPDATEnewbookSETauthor=CONCAT("第一作者",author);mysql>SELECT*FROMnewbook;+---------+--------------+--------------+--------------+------------+-------+----------+|book_id|title

|author

|publisher

|pubdate

|price|stocknum|+---------+--------------+--------------+--------------+------------+-------+----------+|100023|计算机科学导论|第一作者刘晓婷|博达教育出版社|2019-08-01|49.80|

2||100582|大学物理

|第一作者李大国|博达教育出版社|2020-03-01|43.90|

2||110001|平面设计基础

|第一作者李明明|职帆启航出版社|2018-07-01|52.00|

1||110705|空间数据库原理|第一作者宋晓松|职帆启航出版社|NULL

|NULL|

1||120034|学术英语写作

|第一作者王丽|知行联合出版社|2017-11-01|46.90|

2||120087|程序设计基础

|第一作者赵晓妆|知行联合出版社|NULL

|NULL|1|+---------+--------------+--------------+--------------+------------+-------+----------+6rowsinset(0.00sec)任务4.3删除数据DELETE语句格式：DELETEFROM表名[WHERE条件表达式];1．删除满足指定条件的数据【例4-9】将数据表newbook中图书编号book_id为110001的数据删除。DELETEFROMnewbookWHEREbook_id='110001';2．删除全部数据【例4-10】使用DELETE语句删除数据表newbook中的所有图书数据。DELETEFROMnewbook;【例4-11】使用TRUNCATE语句删除数据表newbook中的所有图书数据。TRUNCATEnewbook;mysql>SELECT*FROMnewbook;Emptyset(0.00sec)项目案例：图书馆借还书管理数据库的数据操作1．向各张表中插入数据2．修改表中的数据3．删除表中的数据1．向各张表中插入数据对于创建数据表时指定了外键约束、建立了参照完整性的子表，必须先插入父表中的数据，然后才能插入子表中的数据。在插入数据时，不满足外键约束的数据无法插入。例如，图书表book和馆藏表stock之间存在外键约束，book表是父表、stock表是子表，外键约束要求stock表中的图书编号book_id字段值要么为空值，要么是book表中已经存在的主键字段值。因此，必须先插入book表中的数据，然后才能插入stock表中的数据。1．向各张表中插入数据（1）向book表中插入书目数据。如果前面已经插入过数据，则进行替换。REPLACEINTObookVALUES('100023','计算机科学导论','刘晓婷','博达教育出版社','2019-08-01',49.80,2),('100582','大学物理','李大国','博达教育出版社','2020-03-01',43.90,2),('110001','平面设计基础','李明明','职帆启航出版社','2018-07-01',52.00,2),('110301','导游情景英语','刘广志','职帆启航出版社','2020-01-01',45.00,1),('110705','空间数据库原理','宋晓松','职帆启航出版社','2022-04-01',65.00,1);1．向各张表中插入数据（2）向stock表中插入馆藏图书数据。INSERTINTOstockVALUES('10024017','100582','二层B-8-2',0),('10024018','100582','二层B-8-2',0),('10100601','100023','一层J-6-1',2),('10100602','100023','一层J-6-1',1),('11030001','110001','一层C-18-4',0);1．向各张表中插入数据（3）向读者类型表readertype中插入读者类型数据。INSERTINTOreadertypeVALUES(1,'教师',8,90),(2,'学生',5,60);1．向各张表中插入数据（4）向读者表reader中插入读者数据。INSERTINTOreaderVALUES('1101003091',1,'杨丽','女','外国语学院'),('1105010561',1,'赵晓丽','女','经济与管理学院'),('1105071058',1,'李俊','男','经济与管理学院'),('2101240103',2,'宋洪博','男','外国语学院'),('2104241129',2,'侯明斌','男','新能源学院');1．向各张表中插入数据（5）向读者表reader中插入读者数据。INSERTINTOborrow(reader_id,stock_id,borrowtime,returntime)VALUES('1101003091','12040712','2023-05-2008:23:10','2023-06-2016:21:53'),('1105010561','11030002','2023-09-2009:01:22','2023-10-3014:39:32'),('1105010561','12026030','2024-03-0208:45:21','2024-03-2015:05:46'),('1101003091','10100602','2024-03-0215:32:27','2024-03-2214:25:10'),('1101003091','11030002','2024-03-2014:50:43','2024-05-1616:30:24');borrow_id字段为自增字段（从1开始自增），不需要插入值。1．向各张表中插入数据（5）向读者表reader中插入读者数据。INSERTINTOborrowVALUES(27,'2107240106','10100602','2025-03-2108:39:08',NULL);手动为自增字段插入值，要注意该值不能违反主键约束，后续自增值将从手动插入的值开始自增。给自增字段插入0或NULL时，系统会自动插入下一个自增值，而不是插入0或NULL。INSERTINTOborrowVALUES(0,'2107240106','12026030','2025-03-2108:40:31',NULL);2．修改表中的数据

修改未指定外键约束的字段值时，不受任何限制，可以直接修改。只要指定了外键约束，对父表的主键字段值和子表的外键字段值的修改就一定会受到参照完整性的限制。例如，父表readertype和子表reader之间的外键约束，不同的选项有不同的效果。①RESTRICT：如果reader表中存在该类型的读者，则不允许修改，否则允许修改。②CASCADE：允许修改，并且同时自动修改reader表中该类型的所有读者的type_id字段的值为新的类型编号。③SETNULL：允许修改，并且同时自动修改reader表中该类型的所有读者的type_id字段的值为NULL（在外键字段没有指定为NOTNULL的条件下）。④NOACTION：不采取动作，其作用和RESTRICT一样。⑤SETDEFAULT：允许修改，并且同时自动修改reader表中该类型的所有读者的type_id字段的值为默认值。2．修改表中的数据（1）由于学生转专业，因此需要将reader_id为2107240101的读者的所属院系dept修改为人工智能学院。UPDATEreaderSETdept='人工智能学院'WHEREreader_id='2107240101';由于dept字段既不是主键也不是外键，因此可以直接修改。2．修改表中的数据（2）由于图书馆进行读者类型调整，因此需要将学生的类型编号由原来的2调整为3。UPDATEreadertypeSETtype_id=3WHEREtype_id=2;由于父表readertype和子表reader的外键约束设置了ONUPDATE子句的选项为CASCADE，所以在reader表中该类型的所有读者的type_id也会自动修改为3。mysql>SELECT*FROMreaderWHEREtype_id=3;+------------+---------+--------+--------+--------------+|reader_id

|type_id|name

|gender|dept

|+------------+---------+--------+--------+--------------+|2101240103|

3|宋洪博|男

|外国语学院

||2104241129|

3|侯明斌|男

|新能源学院

||2106240104|

3|王刚

|男

|人工智能学院||2106240206|

3|王晓红|女

|人工智能学院||2107240101|

3|李淑子|女

|人工智能学院||2107240106|

3|刘丽华|女

|数理学院

|+------------+---------+--------+--------+--------------+6rowsinset(0.00sec)3．删除表中的数据删除子表中的数据时不受影响。删除父表中的数据时一定会受到参照完整性的限制。例如，父表book与子表stock之间的外键约束，子表stock中数据的删除不受影响（暂时忽略stock表和borr