[Mysql] 删除数据

为了从一个数据表中删除(去掉)数据，可使用DELETE语句

语法:

DELETE FROM表名 [WHERE 条件] [ORDER BY…] [LIMIT row_count];

DELETE FROM要求指定从中删除数据的表名，WHERE子句过滤要删除的行，ORDER BY子句按照指定的顺序对行进行删除操作，LIMIT子句限制可删除的行数

DELETE不需要列名或通配符。DELETE删除整行而不是删除列，为了删除指定的列，可使用UPDATE语句

导入数据

DROP TABLE IF EXISTS `order_detail`;
CREATE TABLE `order_detail`  (
  `order_id` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `order_time` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `order_detail` VALUES ('o001', '2020-06-15 09:12:33');
INSERT INTO `order_detail` VALUES ('o002', '2020-06-15 13:24:09');
INSERT INTO `order_detail` VALUES ('o003', '2020-06-16 10:44:24');
INSERT INTO `order_detail` VALUES ('o004', '2020-06-17 08:05:52');
INSERT INTO `order_detail` VALUES ('o005', '2020-06-18 18:03:43');
1
2
3
4
5
6
7
8
9
10
11

order_detail表

下面的语句从order_detail表中删除一行:

DELETE FROM order_detail WHERE order_id = 'o004';
1

结果展示:

在这个例子中，只删除order_id = 'o0004’那行的记录，如果省略WHERE子句，它将删除表中每个order记录

DELETE FROM order_detail;
1

结果展示:

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除

DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身(删除的是表的内容而不是表 )

扩展:

如果想从表中删除所有行，不要使用DELETE语句，可使用TRUNCATE TABLE语句，它能够完成与DELETE语句相同的删除操作(删除所有行)，但速度更快，效率更高(TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据)

语法: TRUNCATE TABLE 表名;

TRUNCATE TABLE语句用于清空表，清空后的表中无内容，但表仍然存在

恢复上述order_detail表中的五行数据

order_detail表

下面使用TRUNCATE TABLE语句删除order_detail表中的数据:

TRUNCATE TABLE order_detail;
1

结果展示:

如果表中定义了自增字段，使用TRUNCATE命令删除表中的数据后，再次向表中添加记录时，自增字段的默认值重置为1；使用DELETE FROM命令删除表中的数据后，再次向表中添加记录时，自增字段的值为删除数据时该字段的最大值加1

使用TRUNCATE语句删除表中记录数据

-- 创建test1表并插入数值
CREATE TABLE test1(id INT PRIMARY KEY auto_increment,name CHAR(20));
INSERT INTO test1(name) VALUES('Odin'),('Harry'),('Olck'),('Mark'),('Summer');
-- 检索test1表的全部信息
SELECT * FROM test1;
1
2
3
4
5

结果展示:

-- 使用TRUNCATE删除表中全部记录数据
TRUNCATE test1;
-- 在删除的test1表中重新插入一组数据
INSERT INTO test1(name) VALUES('Rose');
-- 检索test1表的信息
SELECT * FROM test1;
1
2
3
4
5
6

结果展示:

使用DELETE FROM语句删除表中记录数据

-- 创建test2表并插入数值
CREATE TABLE test2(id INT PRIMARY KEY auto_increment,name CHAR(20));
INSERT INTO test2(name) VALUES('Zhang'),('Wang'),('Li'),('Zhao');
-- 检索test2表的全部信息
SELECT * FROM test2;
1
2
3
4
5

结果展示:

-- 使用DELETE FROM删除表中全部记录数据
DELETE FROM test2;
-- 在删除的test2表中重新插入一组数据
INSERT INTO test2(name) VALUES('Hu');
-- 检索test2表的信息
SELECT * FROM test2;
1
2
3
4
5
6

结果展示:

删除数据的指导原则

1.如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除,除非确实打算删除每一行，否则绝对不要使用不带WHERE子句的DELETE语句

2.在对DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确(MySQL没有撤销(undo)按钮,应该非常小心地使用DELETE，否则你会发现自己删除了错误的数据)

扩展练习案例

1.删除记录(一)

导入数据

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60),
(1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70),
(1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80),
(1003, 9002, '2021-09-09 07:01:02', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

exam_record表(试卷作答记录表)

start_time: 试卷开始时间 submit_time: 交卷时间(即结束时间)，如果未完成的话，则为空

**问题:**请删除exam_record表中作答时间小于5分钟整且分数不及格(及格线为60分)的记录

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE,start_time,submit_time) < 5 AND score < 60;

SELECT * FROM exam_record;
1
2
3
4

结果展示:

删除了id为2，4两行记录

2.删除记录(二)

导入数据

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60),
(1002, 9002, '2021-06-02 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9002, '2021-09-09 07:01:02', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

exam_record表(试卷作答记录表)

start_time: 试卷开始时间 submit_time: 交卷时间(即结束时间)，如果未完成的话，则为空

**问题:**请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
OR submit_time IS NULL
ORDER BY start_time
LIMIT 3;

SELECT * FROM exam_record;
1
2
3
4
5
6
7

结果展示:

删除了id为2，3，5三行记录

3.删除记录(三)

导入数据

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58);
1
2
3
4
5
6
7
8
9
10
11
12
13

exam_record表(试卷作答记录表)

**问题:**请删除exam_record表中所有记录，并重置自增主键

TRUNCATE TABLE exam_record;

SELECT * FROM exam_record;
1
2
3

结果展示:

4.删除记录(四)

导入数据

drop table if exists titles_test;
CREATE TABLE titles_test (
   id int(11) not null primary key,
   emp_no  int(11) NOT NULL,
   title  varchar(50) NOT NULL,
   from_date  date NOT NULL,
   to_date  date DEFAULT NULL);

insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

titles_test表

**问题:**删除emp_no重复的记录，只保留最小的id对应的记录

DELETE FROM titles_test
WHERE id NOT IN(SELECT *
                FROM (SELECT MIN(id) 
                      FROM titles_test 
                      GROUP BY emp_no)AS a);
1
2
3
4
5

结果展示:

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦