MySQL数据库教程（第2版）-课件 第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：插入该字段的默认值。1．插入一条完整的数据【例4-1】向院系表department中插入一条完整的院系数据(101,外国语学院,李大国)。（1）在插入数据时省略字段名称

INSERTINTOdepartment

VALUES('101','外国语学院','李大国');（2）在插入数据时不省略字段名称INSERTINTOdepartment(deptno,deptname,director)VALUES('101','外国语学院','李大国');mysql>INSERTINTOdepartment->VALUES('101','外国语学院','李大国');QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+--------+------------+----------+|deptno|deptname|director|+--------+------------+----------+|101|外国语学院|李大国

|+--------+------------+----------+1rowinset(0.01sec)2．插入一条数据的部分字段值【例4-2】向院系表department中插入一条院系数据，仅插入院系代码deptno字段的值102。INSERTINTOdepartment(deptno)VALUES('102');deptname默认值为NULLdirector默认值为郑明mysql>INSERTINTOdepartment(deptno)->VALUES('102');QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+---------+------------+----------+|deptno|deptname|director|+---------+------------+----------+|101|外国语学院|李大国

||102|NULL|郑明|+---------+------------+----------+2rowsinset(0.00sec)3．插入多条数据【例4-3】向院系表department中插入3条院系数据(103,机械工程学院,王莱)、(104,电气与自动化学院,马逖)和(105,经济与管理学院,周海明)。

INSERTINTOdepartment

VALUES('103','机械工程学院','王莱'),

('104','电气与自动化学院','马逖'),

('105','经济与管理学院','周海明');mysql>SELECT*FROMdepartment;+---------+------------------+----------+|deptno|deptname|director|+---------+------------------+----------+|101|外国语学院

|李大国

||102|NULL|郑明||103|机械工程学院|王莱

||104|电气与自动化学院|马逖||105|经济与管理学院

|周海明|+---------+------------------+----------+5rowsinset(0.00sec)4．插入查询结果中的数据语句格式：INSERT[IGNORE][INTO]表名1SELECT*FROM表名2;【例4-4】新建数据表newdepart，其结构与department表完全相同，然后将department表中的所有数据插入newdepart表。新建数据表newdepartCREATETABLEnewdepartLIKEdepartment;插入查询结果中的数据INSERTINTOnewdepartSELECT*FROMdepartment;

mysql>SELECT*FROMnewdepart;+---------+------------------+----------+|deptno|deptname|director|+---------+------------------+----------+|101|外国语学院|李大国||102|NULL|郑明||103|机械工程学院|王莱||104|电气与自动化学院|马逖||105|经济与管理学院|周海明|+---------+------------------+----------+5rowsinset(0.00sec)5．插入并替换已存在的数据REPLACE语句的语法格式与INSERT语句基本相同，但在插入的数据不满足主键约束时，REPLACE语句可以在插入数据之前将与新数据冲突的旧数据删除，使新数据能够正常插入。5．插入并替换已存在的数据【例4-5】向数据表newdepart中插入两条院系数据(101,外国语学院,李大国)和(106,信息工程学院,姜尚)，其中第1条数据与数据表中已有的数据完全相同。REPLACEINTOnewdepartVALUES('101','外国语学院','李大国'),

('106','信息工程学院','姜尚');

如果用INSERT语句插入这两条数据，则执行结果将出错。

mysql>REPLACEINTOnewdepart->VALUES('101','外国语学院','李大国'),->('106','信息工程学院','姜尚');QueryOK,3rowsaffected(0.01sec)Records:2Duplicates:1Warnings:0mysql>INSERTINTOnewdepart->VALUES('101','外国语学院','李大国'),->('106','信息工程学院','姜尚');ERROR1062(23000):Duplicateentry'101'forkey'newdepart.PRIMARY'4.2修改数据UPDATE语句格式：UPDATE表名SET字段名称1=值1[,字段名称2=值2…][WHERE条件表达式];（1）SET子句：用于指定要修改的字段名称及其值。（2）WHERE子句：用于限定要修改的数据，只有满足条件的数据才会被修改。如果省略WHERE子句，则默认修改所有的数据。1．修改指定条件的数据【例4-6】将数据表newdepart中院系代码deptno字段值为102的院系名称deptname字段值修改为“新能源学院”、负责人director字段值修改为“张国庆”。UPDATEnewdepartSETdeptname='新能源学院',director='张国庆'WHEREdeptno='102';mysql>SELECT*FROMnewdepart;+---------+------------------+----------+|deptno|deptname|director|+---------+------------------+----------+|101|外国语学院|李大国||102|新能源学院|张国庆||103|机械工程学院|王莱||104|电气与自动化学院|马逖||105|经济与管理学院|周海明||106|信息工程学院|姜尚|+---------+------------------+----------+6rowsinset(0.00sec)+---------+------------------+----------+|deptno|deptname|director|+---------+------------------+----------+|102|NULL|郑明|2．修改全部数据【例4-7】将数据表newdepart中负责人director字段值均修改为“院长+负责人”的形式。例如，新能源学院的director字段值应修改为“院长张国庆”。

UPDATEnewdepartSETdirector=CONCAT('院长',director);CONCAT函数的功能是将多个字符串连接成一个字符串。mysql>SELECT*FROMnewdepart;+--------+------------------+------------+|deptno|deptname|director|+--------+------------------+------------+|101|外国语学院

|院长李大国||102|新能源学院

|院长张国庆||103|机械工程学院|院长王莱

||104|电气与自动化学院|院长马逖

||105|经济与管理学院

|院长周海明||106|信息工程学院

|院长姜尚|+--------+------------------+------------+6rowsinset(0.00sec)4.3删除数据DELETE语句格式：DELETEFROM表名[WHERE条件表达式];1．删除满足指定条件的数据DELETE语句格式：DELETEFROM表名[WHERE条件表达式];【例4-8】将数据表newdepart中院系代码deptno字段值为101的数据删除。

DELETEFROMnewdepartWHEREdeptno='101';mysql>SELECT*FROMnewdepart;+--------+------------------+------------+|deptno|deptname|director|+--------+------------------+------------+|102|新能源学院

|院长张国庆||103|机械工程学院|院长王莱

||104|电气与自动化学院|院长马逖

||105|经济与管理学院

|院长周海明||106|信息工程学院

|院长姜尚|+--------+------------------+------------+5rowsinset(0.00sec)2．删除全部数据【例4-9】删除数据表newdepart中的所有院系数据。使用不带WHERE子句的DELETE语句删除全部数据，DELETE语句格式：DELETEFROM表名;

DELETEFROMnewdepart;使用TRUNCATE语句删除全部数据，TRUNCATE语句格式：TRUNCATE[TABLE]表名;TRUNCATEnewdepart;mysql>SELECT*FROMnewdepart;Emptyset(0.00sec)4.4课堂案例：学生成绩管理数据库的数据操作根据第3章课堂案例中对学生成绩管理数据库的数据定义，学生成绩管理数据库一共有6张数据表，本节的主要任务是完成对这6张表的数据操作。1．向各张表中插入数据对于创建数据表时指定了外键约束、建立了参照完整性的子表，必须先向父表中插入数据，然后才能向子表中插入数据。在插入数据时，不满足外键约束的数据无法插入。例如，学生表student和院系表department之间存在外键约束，department表是父表，student表是子表，外键约束要求student表中的院系代码deptno字段的值要么为空值，要么是department表中已经存在的主键deptno字段的值。因此，必须先向department表中插入数据，然后才能向student表中插入数据。1．向各张表中插入数据（1）向department表中插入院系数据。如果前面已经插入过数据，则进行替换。REPLACEINTOdepartment

VALUES('101','外国语学院','李大国'),

('102','新能源学院','张国庆'),

('103','机械工程学院','王莱'),

('104','电气与自动化学院','马逖'),

('105','经济与管理学院','周海明'),

('106','信息工程学院','姜尚');

1．向各张表中插入数据（2）向student表中插入学生数据（以前3条数据为例）INSERTINTOstudentVALUES('20241010103','宋洪博','男','2006-05-15','英语2401','101',598,'三好学生，一等奖学金'),('20241010105','刘向志','男','2005-10-08','英语2401','101',525,NULL),('20241010230','李媛媛','女','2006-09-02','英语2402','101',596,NULL);

1．向各张表中插入数据（3）向course表中插入课程数据（以前3条数据为例）INSERTINTOcourseVALUES('10101400','大学英语',4.0),('10200700','风力发电技术',3.0),('10200716','太阳能发电技术',3.0);1．向各张表中插入数据（4）向teacher表中插入教师数据（以前3条数据为例）INSERTINTOteacherVALUES('10100391','杨丽','女','副教授','101'),('10112583','周家罗','男','讲师','101'),('10223769','林达','女','讲师','102');1．向各张表中插入数据（5）向score表中插入选修成绩数据（以前3条数据为例）选修记录编号scid字段是自增字段（默认从1开始自增），可以不插入值。INSERTINTOscore(stuno,crsno,grade,schoolyear,semester)VALUES('20241010103','10600200',85,'2024-2025','1'),('20241010103','10600053',93,'2024-2025','2'),('20241010103','10500131',88,'2024-2025','1');

1．向各张表中插入数据（5）向score表中插入选修成绩数据（以前3条数据为例）如果要手动为自增字段插入值，则要注意该值不能违反主键约束。例如，插入第35条数据（假定只插入了前3条数据）。若手动插入的值大于当前自增值，则后续自增值将从手动插入的值开始自增，即在不插入scid字段值的情况下，下一条数据的scid值将是36。默认情况下，给自增字段插入0或NULL时，系统会自动插入下一个自增值。例如，插入第36条数据。INSERTINTOscoreVALUES(35,'20241060111','10101400',85,'2024-2025','1');INSERTINTOscoreVALUES(0,'20241060111','10600140',66,'2024-2025','2');mysql>SELECT*FROMscore;+------+-------------+----------+-------+------------+----------+|scid|stuno|crsno|grade|schoolyear|semester|+------+-------------+----------+-------+------------+----------+|1|20241010103|10600200|85|2024-2025|1||2|20241010103|10600053|93|2024-2025|2||3|20241010103|10500131|88|2024-2025|1||35|20241060111|10101400|85|2024-2025|1||36|20241060111|10600140|66|2024-2025|2|+------+-------------+----------+-------+------------+----------+5rowsinset(0.00sec1．向各张表中插入数据（6）向teaching表中插入讲授安排数据（以前3条数据为例）INSERTINTOteachingVALUES('电气2401','10100391','10101400'),('财务2401','10100391','10101400'),('计算2401','10112583','10101400');2．修改表中的数据（1）修改未指定外键约束的字段值不受任何限制，可以直接修改。（2）只要设置了外键约束，对父表的主键字段值和子表的外键字段值的修改就一定会受到参照完整性的限制。例如，对于父表department和子表student之间的外键约束，student表中的deptno字段值要么修改为空值，要么修改为department表中已经存在的deptno字段值。修改department表中的deptno字段值时，根据ONUPDATE子句的选项有不同的效果。①RESTRICT：如果student表中存在该院系的学生，则不允许修改。②CASCADE：允许修改，同时自动修改student表该院系学生的deptno字段值为新的院系代码。③SETNULL：允许修改，同时自动修改student表该院系学生的deptno字段值为NULL。④NOACTION：不采取动作，其作用和RESTRICT一样。⑤SETDEFAULT：允许修改，同时自动修改student表该院系学生的deptno字段值为默认值。2．修改表中的数据根据在第3章课堂案例中对学生成绩管理数据库的数据定义，在指定student表和department表之间的外键约束时，设置ONUPDATE选项为CASCADE。（1）假设有学生转专业，需要将学号为20241030109的学生的班级修改为“风电2401”、院系代码修改为102。由于班级既不是主键也不是外键，且102是父表department中已经存在的主键字段值，因此修改成功。mysql>UPDATEstudentSETclassno='风电2401',deptno='102'->WHEREstuno='20241030109';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:02．修改表中的数据根据在第3章课堂案例中对学生成绩管理数据库的数据定义，在指定student表和department表之间的外键约束时，设置ONUPDATE选项为CASCADE。（2）假设学校进行院系调整，需要将新能源学院的院系代码由原来的102调整为111。。由于子表student的外键约束设置ONUPDATE子句的选项为CASCADE，所以在student表中该院系所有学生的deptno字段值会同时自动修改为111。mysql>UPDATEdepartmentSETdeptno='111'WHEREdeptno='102';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0mysql>SELECT*FROMstudentWHEREdeptno='111';+-------------+---------+--------+------------+----------+--------+------------+----------------+|stuno|stuname|gender|birthdate|classno|deptno|enterscore|awards|+-------------+---------+--------+------------+----------+--------+------------+----------------+|20241020110|王琦|男|2006-01-23|风电2401|111|500|优秀学生干部||20241030109|张虎|男|2006-07-18|风电2401|111|550|数学建模一等奖|+-------------+---------+--------+------------+----------+--------+------------+----------------+2rowsinset(0.00sec)3．删除表中的数据（1）删除子表中的数据时不受影响。（2）删除父表中的数据时一定会受到参照完整性的限制。例如，对于student表