這個文章是轉自：http://www.cnblogs.com/f-ck-need-u/p/8912026.html

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_referencesSET 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觸發器。

?

本文原創地址在博客園：http://www.cnblogs.com/f-ck-need-u/p/8912026.html

?

?

轉載于:https://www.cnblogs.com/duhuo/p/4163889.html

總結

以上是生活随笔為你收集整理的(MariaDB/MySQL)之DML(2)：数据更新、删除的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。