(MariaDB/MySQL)之DML(2)：数据更新、删除
本文目录：
1.update语句
2.delete语句
2.1 单表删除
2.2 多表删除
3.truncate table
1.update语句
update用于修改表中记录。
# 单表更新语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
[PARTITION (partition_list)]
SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
# 多表更新语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
[WHERE where_condition]
先简单介绍下各子句和关键字相关的功能，后文将详细解释它们。
- low_priority只对使用表级锁的存储引擎有效(如MyISAM和Aria)，它设置delete语句的优先级低于读操作，使update延迟到没有任何进程访问表的时候才会执行。见：(MariaDB/MySQL)MyISAM存储引擎读、写操作的优先级。
- ignore是在更新某行出错的时候忽略错误，继续更新其他行。
- where子句筛选出要更新的行。如果不给定where子句，则update会更新整张表中的所有行。
- order by子句表示先对筛选出来的数据排序，排序后按顺序更新这些行。在更新某些行的时候，使用order by能解决一些错误。
- limit子句表示更新一定数量的行。
例如：
# 单表更新 UPDATE table_name SET column1 = value1, column2 = value2 WHERE id=100;
按排序更新指定行数。
update book set bookcount=2 where bookname in ('ss') order by bookid limit 10;
多表更新。注意，下面的语句会更新两张表中的数据。
UPDATE BOOK,BOOK2 SET BOOK.bookcount=2 ,BOOK2.bookcount=3 WHERE BOOK.bookid=1 AND BOOK2.bookid=1;
基于其他表来更新某表数据。注意，下面的语句只更新一张表中的数据。
update t,t1 set t1.name='newname' where t1.id=t2.id; update t set name='newname' where t.id=(select max(id) from t1);
注意，SQL Server支持下面的update from语法，但是MySQL/MariaDB不支持。
-- 使用多表联接为软件测试低于65分的学生减5分 UPDATE TScore SET mark = mark - 5 FROM TScore a JOIN TSubject b ON a.subJectID = b.subJectID WHERE b.subJectName = '软件测试' AND mark < 65
下面是关于update需要注意的几种特殊情况。
(1).更新时有键值重复时，可以考虑使用order by子句。
例如，下面的表：id为主键，不允许重复。
create or replace table t(id int primary key,sex char(3),name char(20));
insert into t values(1,'nan','longshuai1'),
(2,'nan','longshuai2'),
(3,'nv','xiaofang1'),
(4,'nv','xiaofang2'),
(5,'nv','xiaofang3'),
(6,'nv','xiaofang4'),
(7,'nv','tun\'er'),
(8,'nan','longshuai3');
下面的语句将更新失败，因为如果更新成功，主键id将重复。
update t set id=id+1 where id>5; ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
但使用order by之后，将能正常更新，因为会先排序，然后按降序结果集进行更新。
update t set id=id+1 where id>5 order by id desc; select * from t where id>5; +----+------+------------+ | id | sex | name | +----+------+------------+ | 7 | nv | xiaofang4 | | 8 | nv | tun'er | | 9 | nan | longshuai3 | +----+------+------------+
(2).一定要注意update中set赋值语句的同时性。
多个赋值语句是从左到右评估的，除非sql_mode指定了SIMULTANEOUS_ASSIGNMENT模式(从MariaDB 10.3.5开始支持该模式)，这种情况下UPDATE语句是同时评估所有赋值语句的。（注：标准SQL的update赋值语句就是同时性的）
例如，给定如下表：
CREATE OR REPLACE TABLE tx (c1 INT, c2 INT); INSERT INTO tx VALUES (10,10);
下面的update能正确执行，更新后c2字段的值和c1的值相同。
UPDATE tx SET c1=c1+1,c2=c1; SELECT * FROM tx; +------+------+ | c1 | c2 | +------+------+ | 11 | 11 | +------+------+
设置sql_mode模式SIMULTANEOUS_ASSIGNMENT，再执行相同的更新语句。
/* 由于同时评估各赋值语句，所以更新后c1的值会加1，c2的值等于更新前的c1 */ SET @@sql_mode=CONCAT(@@sql_mode,',SIMULTANEOUS_ASSIGNMENT'); UPDATE tx SET c1=c1+1,c2=c1; SELECT * FROM tx; +------+------+ | c1 | c2 | +------+------+ | 12 | 11 | +------+------+
(3).更新源和目标相同的数据。
在MariaDB 10.3.2之前，执行下面的update语句会失败。
update t set id='10' where id=(select max(t.id) from t); ERROR 1093 (HY000): Table 't' is specified twice, both as a target for 'UPDATE' and as a separate source for data
但是从MariaDB 10.3.2开始，允许执行这样的update语句。
2.delete语句
delete用于删除表中记录。可以删除单表数据，也可以删除多表数据。
先看语法：
# 单表删除语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name [PARTITION (partition_list)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
[RETURNING select_expr
[, select_expr ...]]
# 多表语法：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
# 或：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
先简单介绍下各子句和关键字相关的功能，后文将详细解释它们。
- from子句指定要删除的哪张表中的数据，如果是多表语法，则可能只是提供引用功能，不一定会删除其中的数据。
- low_priority只对使用表级锁的存储引擎有效(如MyISAM和Aria)，它设置delete语句的优先级低于读操作，使delete延迟到没有任何进程访问表的时候才会执行。见：(MariaDB/MySQL)MyISAM存储引擎读、写操作的优先级。
- quick是通知存储引擎将删除操作合并起来，存储引擎收到这个通知后，删除多行的操作会合并成一个批，当批的大小达到一定程度之后才一次性删除，一定程度上能提升删除数据的效率。对InnoDB/XtraDB可能无效，但对MyISAM和Aria是有效的。
- ignore是在删除某行出错的时候忽略错误，继续删除其他行。
- where子句筛选出要删除的行。如果不给定where子句，则delete会删除整张表中的所有行。
- order by子句表示先对筛选出来的数据排序，排序后按顺序删除这些行。
- limit子句表示删除一定数量的行。
- returning子句用于返回所删除的行相关的数据。这是一个MariaDB非常人性化的功能，不仅可以让我们知道删除了哪些行，某些时候还能借此恢复误删除的行。MySQL不支持该功能。
- using子句用于多表删除语法。
MySQL/MariaDB中delete语句中必须使用from子句。单表删除时，表名必须放在from子句中，而多表删除语法中，多表是可以放在from子句之前的。习惯了SQL Server的人一开始可能会因此而不习惯，出于方便的原因，SQL Server中的delete往往会不写from子句。
2.1 单表删除
给定如下表，并插入一些数据。
create or replace table t(id int primary key,sex char(3),name char(20));
insert into t values(1,'nan','longshuai1'),
(2,'nan','longshuai2'),
(3,'nv','xiaofang1'),
(4,'nv','xiaofang2'),
(5,'nv','xiaofang3'),
(6,'nv','xiaofang4'),
(7,'nv','tun\'er'),
(8,'nan','longshuai3');
删除sex='nv'且id>6的记录。
delete from t where id>6 and sex='nv';
对于delete语句而言，order by子句主要结合limit子句使用。
delete from t order by id limit 2;
如果使用returning子句，可以自定义删除行的时候返回哪些数据。注意，MariaDB 10.3.1之前下面的语句会失败。见下文。
delete from t where id=(select max(id) from t) returning concat("delete id: ",id) as maxid;
+--------------+
| maxid |
+--------------+
| delete id: 8 |
+--------------+
或者返回删除行的所有字段的值：
delete from t returning *; +----+------+-----------+ | id | sex | name | +----+------+-----------+ | 3 | nv | xiaofang1 | | 4 | nv | xiaofang2 | | 5 | nv | xiaofang3 | | 6 | nv | xiaofang4 | +----+------+-----------+
注意，下面的delete语句中，删除的是同源同目标数据。在MariaDB 10.3.1之前，delete语句无法删除这样的记录。报错信息如下：
delete from t where id=(select max(id) from t); ERROR 1093 (HY000): Table 't' is specified twice, both as a target for 'DELETE' and as a separate source for data
但从MariaDB 10.3.1之后，允许删除这样的记录。
2.2 多表删除
两种语法，一种语法是将表引用放在from子句之前，另一种语法是使用using子句。它们其实是等价的。
如果下面的语法不明白，请将delete tbl_name这部分替换成select column_list来考虑。delete的执行过程和select是一样的，只不过是筛选数据后，一个是对筛选的结果集进一步select，一个是delete筛选出来的结果集。
下面的语句会删除t和t1两张表中满足id相等的记录。注意，是两张表中的内容都删除。
delete t,t1 from t join t1 on t.id=t1.id; # 等价于 delete from t,t1 using t join t1 on t.id=t1.id;
如果只是要删除一张表中的内容，但需要引用多张表，则可以参考下面的语句。该语句只会删除t表的内容，不会删除t1表的内容。
# delete tbl_name1 from tbl_name1 join tbl_name2 .... delete t from t join t1 on t.id=t1.id;
例如，删除表t中有的记录，但t1表中没有的记录。
delete t from t left join t1 on t.id=t1.id where t1.id is NULL;
如果使用了别名，那么和select一样，在delete列表引用表名的时候，需要使用别名。
# 正确的语法 DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; # 错误的语法 DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2 WHERE a1.id=a2.id; DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2 WHERE a1.id=a2.id;
3.truncate table
truncate table用于清空一张表。truncate table等价于drop table + re-create table两个操作，因此它是DDL语句而非DML语句，也因此它需要表的drop权限，且速度比delete表中所有速度要快的多的多，特别是表比较大的时候。
在re-create表的时候，它根据".frm"文件中的表结构来重建表，因此索引等属性都会保留下来。但auto_increment最近的值会重置，因为该表被删除，它的auto_increment值全被清空。
如果表上有其他锁的存在，则truncate table会失败。
如果表上有外键引用，则truncate table会失败。
如果表上有触发器，则truncate table不会触发任何触发器。因为MariaDB/MySQL不支持DDL触发器。
回到Linux系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
转载请注明出处：http://www.cnblogs.com/f-ck-need-u/p/8912026.html
注：若您觉得这篇文章还不错请点击右下角推荐，您的支持能激发作者更大的写作热情，非常感谢！