本文目录:
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
来源: https://www.cnblogs.com/f-ck-need-u/p/8912026.html