MySQL学习总结（五）DML语言之插入语句/修改语句/修改单表/修改多表/删除语句/单表的删除/多表的删除（级联删除）/DELETE和TRUNCATE的区别

DML(Data Manipulation Language)：数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性

一、插入语句

（一）方式一

格式：

INSERT INTO 表名(列名1,列名2...) VALUES(值1,值2...);

特点：

1. 插入的值的类型要与列的类型一致或兼容
2. 不可以为null的列必须插入值
3. 列的顺序可以和表中不一致
4. 列数和值的个数必须一致
5. 可以省略列名，缺省默认是所有列，此时列的顺序和表中列的顺序一致

假设有以下表：

案例1：插入的值的类型要与列的类型一致或兼容

INSERT INTO student(id,stuname,gender,birthday,telephone)
VALUES(1,'小明','男','1999-01-01','13888888888');

案例2：不可以为null的列必须插入值，那可以为null的列如何插入值？

#方式一：直接插入null
INSERT INTO student(id,stuname,gender,birthday,telephone)
VALUES(2,'小红','女',NULL,NULL);
#方式二：省略可以为null的列和值
INSERT INTO student(id,stuname)
VALUES(3,'小刚');

案例3：列的顺序可以和表中不一致

INSERT INTO student(birthday,telephone,id,stuname,gender)
VALUES('1997-04-04','13777777777',4,'小王','男');

案例4：列数和值的个数必须一致

INSERT INTO student(id,stuname,telephone)
VALUES(5,'小黑','1300000000');

案例5：可以省略列名，默认所有列，此时列的顺序和表中列的顺序一致

INSERT INTO student
VALUES(6,'小白','女',NULL,NULL);

（二）方式二

格式：

INSERT INTO 表名
SET 列名=值,列名=值,...;

案例：

INSERT INTO student
SET id=7,stuname='小敏',gender='女',birthday='1998-09-09',telephone=NULL;

（三）两种方式的比较

1.方式一支持插入多行数据，方式二不支持

案例：

INSERT INTO student
VALUES(8,'小天','男','1998-08-08',NULL),
(9,'小铭','男','1997-06-06',NULL),
(10,'小何','男','1999-07-07','158888888888');

2.方式一支持子查询，方式二不支持

案例1：

INSERT INTO student(id,stuname,gender)
SELECT 11,'小光','男';

案例2：将员工表中编号为100的员工信息插入到学生表中

INSERT INTO student(id,stuname,telephone)
SELECT employee_id,last_name,phone_number
FROM employees 
WHERE employee_id=100;

案例3：当然，方式一的子查询也可以实现插入多行数据

INSERT INTO student
SELECT 8,'小天','男','1998-08-08',NULL UNION
SELECT 9,'小铭','男','1997-06-06',NULL UNION
SELECT 10,'小何','男','1999-07-07','158888888888';

二、修改语句

（一）修改单表的记录

格式：

UPDATE 表名
SET 列=新值,列=新值,...
WHERE 筛选条件;

案例1：修改student表中名字包含’红’的学生的电话为13899888899

UPDATE student
SET telephone='13899888899'
WHERE stuname LIKE '%红%';

案例2：修改student表中id号为3的名称为小飞，生日为1995-05-05

UPDATE student
SET stuname='小飞',birthday='1995-05-05'
WHERE id=3;

（二）修改多表的记录

格式：

• sql92语法：

UPDATE 表1 别名,表2 别名
SET 列=值,列=值,...
WHERE 连接条件
AND 筛选条件;

• sql99语法：

UPDATE 表1 别名
连接类型 JOIN 表2 别名
ON 连接条件
SET 列=值,列=值,...
WHERE 筛选条件；

假设有这样两张表：

案例1：修改小明的女朋友的年龄为20

UPDATE girl g
INNER JOIN boy b
ON g.boyfriend_id=b.id
SET g.age=20
WHERE b.boyname='小明';

案例2：修改没有男朋友的女生的男朋友编号为1号

UPDATE girl g
LEFT JOIN boy b
ON g.boyfriend_id=b.id
SET g.boyfriend_id=1
WHERE b.id IS NULL;

三、删除语句

（一）方式一：DELETE

1.单表的删除

格式：

DELETE FROM 表名 
【WHERE 筛选条件
LIMIT 条目数】;

案例：删除手机号以8结尾的学生信息

DELETE FROM student
WHERE telephone LIKE '%8';

2.多表的删除（级联删除）

格式：

• sql92语法：

DELETE 表1的别名,表2的别名
FROM 表1 别名,表2 别名
WHERE 连接条件
AND 筛选条件;

• sql99语法：

DELETE 表1的别名,表2的别名
FROM 表1 别名
连接类型 JOIN 表2 别名
ON 连接条件
WHERE 筛选条件;

案例1：删除小黑的女朋友的信息

DELETE g
FROM girl g
INNER JOIN boy b
ON g.boyfriend_id=b.id
WHERE b.boyname='小黑';

案例2：删除小飞和他女朋友的信息

DELETE g,b
FROM girl g
INNER JOIN boy b
ON g.boyfriend_id=b.id
WHERE b.boyname='小飞';

（二）方式二：TRUNCATE

格式：

TRUNCATE TABLE 表名;

案例：

TRUNCATE TABLE employees;

它不能加WHERE筛选条件
用于完全清空表数据 , 但表结构、索引、约束等不变

（三）DELETE和TRUNCATE的区别

1. DELETE可以加WHERE条件，TRUNCATE不能加
2. TRUNCATE删除，效率较高一点
3. DELETE删除有返回值，TRUNCATE删除没有返回值（不会告诉你几行受到影响）
4. DELETE删除可以回滚，TRUNCATE删除不能回滚（后面讲）
5. 假如要删除的表中有自增长列（后面讲），如果用DELETE删除后，再插入数据，自增长列的值从断点开始；而如果用TRUNCATE删除，再插入数据，自增长列的值从1开始。

案例：

1.使用DELETE命令删除grade表中所有列数据（即不带WHERE筛选条件的DELETE命令），其中gradeId属性为自增

DELETE FROM grade;

删除后，使用INSERT命令添加数据

INSERT INTO grade(gradeName) VALUES('研一');

执行结果：添加的该条数据的gradeId不是1，而是5

2.使用TRUNCATE命令删除grade数据表

TRUNCATE TABLE grade;

删除后，使用INSERT命令添加数据

INSERT INTO grade(gradeName) VALUES('研一');

执行结果：添加的该条数据的gradeId是1