MySQL数据库教程课件

第4章数据操作学习目标熟练掌握INSERT、UPDATE和DELETE语句。了解不同的数据完整性约束对数据操作结果的影响。4.1插入数据INSERT语句：

INSERT[IGNORE][INTO]

表名(字段名称1[,

字段名称2…])VALUES({表达式1|DEFAULT}[,{表达式2|DEFAULT}…]);（1）IGNORE：当插入不符合数据完整性约束的数据时，不执行该语句，当作一条警告处理。（2）字段名称：省略时表示要插入全部字段的数据，否则必须指定要插入数据的字段名称。（3）VALUES子句：指定各个字段要插入的具体数据。数据的顺序与字段名称的顺序一致。（4）表达式：可以是常量、变量或者一个表达式，也可以是空值。字符串型或日期和时间型数据常量必须用英文单引号或双引号引起来。（5）DEFAULT：插入该字段的默认值4.1插入数据1．插入一条记录的全部数据【例4-1】向院系表department中插入一条完整的院系数据（101，外国语学院，李大国）。（1）对所有插入数据省略字段名称INSERTINTOdepartmentVALUES("101","外国语学院","李大国");（2）对所有插入数据不省略字段名称INSERTINTOdepartment(deptno,deptname,director)VALUES("101","外国语学院","李大国");mysql>INSERTINTOdepartment->VALUES("101","外国语学院","李大国");QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+--------+------------+----------+|deptno|deptname|director|+--------+------------+----------+|101|外国语学院|李大国

|+--------+------------+----------+4.1插入数据2．插入一条记录的部分数据【例4-2】向院系表department中插入一条院系数据（102，可再生能源学院），仅插入院系代码deptno和院系名称deptname字段的数据。INSERTINTOdepartment(deptno,deptname)VALUES("102","可再生能源学院");director默认值为院长mysql>INSERTINTOdepartment(deptno,deptname)->VALUES("102","可再生能源学院");QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+--------+----------------+----------+|deptno|deptname|director|+--------+----------------+----------+|101|外国语学院|李大国

||102|可再生能源学院|院长

|+--------+----------------+----------+4.1插入数据2．插入一条记录的部分数据【例4-3】向院系表department中插入一条院系数据（103，王莱），仅插入院系代码deptno和负责人director字段的数据。

INSERTINTOdepartment(deptno,director)VALUES("103","王莱");deptname默认值为NULLmysql>INSERTINTOdepartment(deptno,director)->VALUES("103","王莱");QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMdepartment;+--------+----------------+----------+|deptno|deptname|director|+--------+----------------+----------+|101|外国语学院|李大国

||102|可再生能源学院|院长||103|NULL|王莱

|+--------+----------------+----------+4.1插入数据3．插入多条记录的数据，各条数据之间用英文逗号隔开。【例4-4】向院系表department中插入3条院系数据（104，电气与电子工程学院，马逖）、（105，经济与管理学院，周海明）和（106，控制与计算机工程学院，姜尚）。INSERTINTOdepartmentVALUES("104","电气与电子工程学院","马逖"),("105","经济与管理学院","周海明"),("106","控制与计算机工程学院","姜尚");mysql>INSERTINTOdepartment->VALUES("104","电气与电子工程学院","马逖"),->("105","经济与管理学院","周海明"),->("106","控制与计算机工程学院","姜尚");QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMdepartment;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外国语学院

|李大国

||102|可再生能源学院|院长||103|NULL|王莱||104|电气与电子工程学院

|马逖||105|经济与管理学院|周海明||106|控制与计算机工程学院|姜尚|+--------+----------------------+----------+4.1插入数据4.插入查询结果中的数据INSERT[IGNORE][INTO]表名1(字段名称1[,字段名称2,…])SELECT(字段名称1[,字段名称2,…])FROM表名2;【例4-5】新建数据表department1，其结构与院系表department完全相同，然后将department表中的所有数据插入department1表中。CREATETABLEdepartment1LIKEdepartment;INSERTINTOdepartment1SELECT*FROMdepartment;

mysql>SELECT*FROMdepartment1;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外国语学院

|李大国

||102|可再生能源学院|院长||103|NULL|王莱||104|电气与电子工程学院

|马逖||105|经济与管理学院|周海明||106|控制与计算机工程学院|姜尚|+--------+----------------------+----------+4.1插入数据5．插入并替换已存在的数据REPLACE语句的语法格式与INSERT语句基本相同。但在插入的数据不满足主键约束时，REPLACE语句可以在插入数据之前将与新数据冲突的旧数据删除，使新数据能够正常插入。4.1插入数据5．插入并替换已存在的数据【例4-6】向department1表中插入两条院系数据（101，外国语学院，李大国）和（107，数理学院，董蔚来），其中有一条数据与数据库中已有的数据完全相同。REPLACEINTOdepartment1VALUES("101","外国语学院","李大国"),("107","数理学院","董蔚来");

如果用INSERT语句插入这两条数据，则执行结果将出错。INSERTINTOdepartment1VALUES("101","外国语学院","李大国"),("107","数理学院","董蔚来");ERROR1062(23000):Duplicateentry'101'forkey'department.PRIMARY'mysql>REPLACEINTOdepartment1->VALUES("101","外国语学院","李大国"),->("107","数理学院","董蔚来");QueryOK,3rowsaffected(0.01sec)Records:2Duplicates:1Warnings:04.2修改数据UPDATE语句：UPDATE表名SET字段名称1=值1[,字段名称2=值2…][WHERE条件];（1）SET子句：用于指定要修改的字段名称及其值。（2）WHERE子句：用于限定要修改数据的记录，只有满足条件的记录才会被修改。如果省略WHERE子句，则默认修改所有的记录。4.2修改数据1．修改指定记录的数据【例4-7】将department1表中院系代码deptno为103的院系名称deptname修改为“能源动力与机械工程学院”。UPDATEdepartment1SETdeptname="能源动力与机械工程学院"WHEREdeptno="103";mysql>SELECT*FROMdepartment1;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外国语学院

|李大国

||102|可再生能源学院|院长||103|NULL|王莱||104|电气与电子工程学院

|马逖||105|经济与管理学院|周海明||106|控制与计算机工程学院|姜尚||107|数理学院

|董蔚来|+--------+----------------------+----------+4.2修改数据1．修改指定记录的数据【例4-8】将department1表中院系代码deptno为102的负责人director修改为“张国庆”。UPDATEdepartment1SETdirector="张国庆"WHEREdeptno="102";mysql>SELECT*FROMdepartment1;+--------+----------------------+----------+|deptno|deptname|director|+--------+----------------------+----------+|101|外国语学院

|李大国

||102|可再生能源学院|院长||103|能源动力与机械工程学院|王莱||104|电气与电子工程学院

|马逖||105|经济与管理学院|周海明||106|控制与计算机工程学院

|姜尚||107|数理学院

|董蔚来|+--------+----------------------+----------+4.2修改数据2．修改全部记录的数据修改全部记录的数据时，不需要使用WHERE子句。【例4-9】将department1表中所有院系的负责人director均修改为“院长+姓名”的形式。UPDATEdepartment1SETdirector=CONCAT("院长",director);CONCAT函数的功能是将多个字符串连接成一个字符串。mysql>SELECT*FROMdepartment1;+--------+------------------------+------------+|deptno|deptname|director|+--------+------------------------+------------+|101|外国语学院

|院长李大国||102|可再生能源学院

|院长张国庆||103|能源动力与机械工程学院|院长王莱||104|电气与电子工程学院|院长马逖||105|经济与管理学院

|院长周海明||106|控制与计算机工程学院

|院长姜尚

||107|数理学院

|院长董蔚来|+--------+------------------------+------------+4.3删除数据DELETE语句：DELETEFROM表名[WHERE条件];1．删除满足指定条件的数据【例4-10】将department1表中院系代码deptno为101的数据删除。

DELETEFROMdepartment1WHEREdeptno="101";4.3删除数据DELETE语句：DELETEFROM表名[WHERE条件];2．删除全部数据【例4-11】使用DELETE语句删除department1表中的所有院系数据。DELETEFROMdepartment1;mysql>SELECT*FROMdepartment1;Emptyset(0.00sec)4.3删除数据2．删除全部数据TRUNCATE语句：TRUNCATE[TABLE]表名;（1）使用不带WHERE子句的DELETE语句，会删除数据表中的所有数据，但仍然会在数据库中保留数据表的定义；（2）使用TRUNCATE语句，会删除原来的数据表并重新创建数据表，执行速度比DELETE语句快。【例4-12】使用TRUNCATE语句删除department1表中的所有院系数据。TRUNCATEdepartment1;4.4课堂案例：学生成绩管理数据库的数据操作1．插入各张表中的数据对于创建数据表时指定了外键约束、建立了参照完整性的子表，必须先插入父表中的数据，然后才能插入子表中的数据。例如，学生表student和院系表department之间存在外键约束，department表是父表、student表是子表，外键约束要求student表中的院系代码deptno字段值要么为空值，要么是department表中已经存在的主键值。因此，必须先插入department表中的数据，然后才能插入student表中的数据。4.4课堂案例：学生成绩管理数据库的数据操作1．插入各张表中的数据（1）向院系表department中插入院系数据。如果前面已经插入过数据，则进行覆盖。插入前5条数据。REPLACEINTOdepartmentVALUES("101","外国语学院","李大国"),("102","可再生能源学院","张国庆"),("103","能源动力与机械工程学院","王莱"),("104","电气与电子工程学院","马逖"),("105","经济与管理学院","周海明");4.4课堂案例：学生成绩管理数据库的数据操作1．插入各张表中的数据（2）向学生表student中插入学生数据。插入前5条数据，如果奖惩情况无数据，则awards字段可以插入空值（NULL）或者空字符串（""）。INSERTINTOstudentVALUES("120211010103","宋洪博","男","2003-05-15","党员","英语2101","101",698,"三好学生，一等奖学金"),

("120211010105","刘向志","男","2002-10-08","团员","英语2101","101",625,NULL),

("120211010230","李媛媛","女","2003-09-02","团员","英语2102","101",596,""),

("120211030110","王琦","男","2003-01-23","团员","机械2101","103",600,"优秀学生干部，二等奖学金"),

("120211030409","张虎","男","2003-07-18","群众","机械2104","103",650,"北京市数学建模一等奖");4.4课堂案例：学生成绩管理数据库的数据操作1．插入各张表中的数据（3）向课程表course中插入课程数据。插入前5条数据。INSERTINTOcourseVALUES("10101400","学术英语",64),("10101410","通用英语",48),("10300710","现代控制理论",40),("10400350","模拟电子技术基础",56),("10500131","证券投资学",32);4.4课堂案例：学生成绩管理数据库的数据操作1．插入各张表中的数据（4）向教师表teacher中插入教师数据。插入前5条数据。INSERTINTOteacherVALUES("10100391","杨丽","女","副教授","101"),("10112583","周家罗","男","教授","101"),("10309242","宋江科","男","教授","103"),("10423769","林达","女","教授","104"),("10501561","赵晓丽","女","副教授","105");4.4课堂案例：学生成绩管理数据库的数据操作1．插入各张表中的数据（5）向选修成绩表score中插入选修成绩数据。插入前5条数据INSERTINTOscoreVALUES("120211010103","10101400",85,"2021-2022","2"),("120211010103","10500131",93,"2021-2022","1"),("120211010103","10600611",88,"2021-2022","1"),("120211010103","10700140",70,"2021-2022","1"),("120211010105","10101400",68,"2021-2022","2");4.4课堂案例：学生成绩管理数据库的数据操作1．插入各张表中的数据（6）向讲授安排表teaching中插入讲授安排数据。插入前5条数据。INSERTINTOteachingVALUES("英语2101","10100391","10101400"),("财务2101","10100391","10101400"),("英语2102","10112583","10101400"),("财务2101","10112583","10101410"),("电气2111","10309242","10300710");4.4课堂案例：学生成绩管理数据库的数据操作2．修改表中的数据（1）修改未指定外键约束的字段值不受任何限制，可以直接修改。（2）只要设置了外键约束，对父表的主键字段值和子表的外键字段值的修改就一定会受到参照完整性的限制。例如，对于学生表student和院系表department之间的外键约束，修改子表中的deptno外键字段值时，要么为空值，要么是父表中已经存在的主键值；修改父表中的deptno主键字段值时，不同的ONUPDATE选项有不同的效果。①RESTRICT：如果student表中存在该院系的学生，则不允许修改。②CASCADE：允许修改，同时自动修改student表中相关学生的deptno字段的值为新的院系代码。③SETNULL：允许修改，同时自动修改student表中相关学生的deptno字段的值为NULL。④NOACTION：不采取动作，其作用和RESTRICT一样。⑤SETDEFAULT：允许修改，同时自动修改student表中相关学生的deptno字段的值为默认值。4.4课堂案例：学生成绩管理数据库的数据操作2．修改表中的数据（1）由于学生转专业，因此需要将学号sno为120211080101的班级classno修改为“风电2101”，院系不变。UPDATEstudentSETclassno="风电2101"WHEREsno="120211080101";由于班级classno字段既不是主键也不是外键，因此可以直接修改。4.4课堂案例