首页 > > 网络编程 > Mysql >

(MariaDB/MySQL)之DML(2)：数据更新、删除

2018-06-18 01:21:25来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

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触发器。

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：mysql之数据备份与恢复

下一篇：MySQL数据库主从同步延迟分析及解决方案

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签