本文目錄：
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

註：若您覺得這篇文章還不錯請點擊右下角推薦，您的支持能激發作者更大的寫作熱情，非常感謝！