(MariaDB/MySQL)之DML(2)：數據更新、刪除

来源:https://www.cnblogs.com/f-ck-need-u/archive/2018/04/23/8912026.html
-Advertisement-
Play Games

本文目錄：1.update語句2.delete語句　2.1 單表刪除　2.2 多表刪除3.truncate table 1.update語句 update用於修改表中記錄。 先簡單介紹下各子句和關鍵字相關的功能，後文將詳細解釋它們。 low_priority只對使用表級鎖的存儲引擎有效(如MyISA ...


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

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Nginx-場景實踐篇
    Nginx 場景實踐篇 一、Nginx作為靜態資源Web服務 1、動態資源和靜態資源 客戶端請求的頁面如果是靜態網頁，那麼伺服器會直接把靜態網頁的內容響應給客戶端。如果客戶端請求的是動態網頁，伺服器需要先把動態網頁換成靜態網頁，然後再把轉換後的靜態網頁響應給客戶端 靜態資源的幾種類型 瀏覽器渲染：H ...
  • Linux 密鑰登錄
    1.生成密鑰。 ssh-keygen命令用來生成密鑰對，基本的選項： -t 指定的密鑰類型(rsa,rsa1,dsa,ecdsa)。 -p 指定密語。 -f 指定生成密鑰文件的命名。 -c 添加註釋。 2.將公鑰部署到Linux上和下載私鑰到本地。 下載密鑰可以使用sz命令或scp命令或者filez ...
  • Linux下的磁碟分區
    一、硬碟介面類型 硬碟的介面主要有IDE、SATA、SCSI 、SAS和光纖通道等五種類型。其中IDE和SATA介面硬碟多用於家用產品中，也有部分應用於伺服器，SATA是一種新生的硬碟介面類型，已經取代了大部分IDE介面應用。SCSI 、SAS主要應用於伺服器上，普通家用設備一般不支持SCSI和SA ...
  • Linux 用戶篇——用戶管理命令之id、whoami、su、chage
    一、淺談id、whoami、su、chage 本篇是續寫上一篇<Linux 用戶篇——用戶管理命令之useradd、passwd、userdel、usermod>。 (1)id命令 命令格式：id username(用戶名) 命令解釋：查看用戶的UID(用戶ID)、GID(組ID)。 (2)whoa ...
  • ubuntu14.04安裝Shadowsocks
    我的系統是unbuntu14.04，我先是按照官方教程的安裝，後來也百度了一點別人的教程，算是一個雜燴。 註意，為什麼要使用privoxy？ 因為如果不使用的話，就是全局代理，使用全局代理會使所有的連接通過shadowsocks伺服器中轉，一般不建議使用全局代理。另外，gnome桌面的代理設置無法正 ...
  • Linux（七）開機，重啟和用戶登錄註銷
    7.1 關機&重啟命令 基本介紹： shutdown -h now 立刻進行關機 shutdown -h 1 1分鐘後關機 shutdown -r now 現在重啟電腦 halt 關機，作用和上面一樣 reboot 重啟 sync 把記憶體的數據同步到磁碟 註意細節： 不管是重啟系統還是關閉系統，首 ...
  • Linux（四）使用Xshell5遠程登錄
    Xshell5和Xftp5的安裝包 鏈接：https://pan.baidu.com/s/1q3-ch75TW3lvC3KX25klNQ 密碼：m31n 說明： 公司開發的時候，具體情況是這樣的： 1、linux伺服器是開發小組共用的； 2、正式上線的項目是運行在公網的； 3、因此程式員需要遠程登錄 ...
  • Windows結構化異常處理淺析
    結構化異常處理（**structured exception handling**，下文簡稱:**SEH**），是作為一種系統機制引入到操作系統中的，本身與語言無關。在我們自己的程式中使用**SEH**可以讓我們集中精力開發關鍵功能，而把程式中所可能出現的異常進行統一的處理，使程式顯得更加簡潔且增加... ...
一周排行
    -Advertisement-
    Play Games
  • 如何基於surging跨網關跨語言進行緩存降級
    基於.NET Framework 4.8 開發的深度學習模型部署測試平臺，提供了YOLO框架的主流系列模型，包括YOLOv8~v9，以及其系列下的Det、Seg、Pose、Obb、Cls等應用場景，同時支持圖像與視頻檢測。模型部署引擎使用的是OpenVINO™、TensorRT、ONNX runti... ...
  • 十年沉澱，重啟開發之路
    十年沉澱，重啟開發之路 十年前，我沉浸在開發的海洋中，每日與代碼為伍，與演算法共舞。那時的我，滿懷激情，對技術的追求近乎狂熱。然而，隨著歲月的流逝，生活的忙碌逐漸占據了我的大部分時間，讓我無暇顧及技術的沉澱與積累。 十年間，我經歷了職業生涯的起伏和變遷。從初出茅廬的菜鳥到逐漸嶄露頭角的開發者，我見證了 ...
  • C#.NET體系圖文概述—2024最全總結
    C# 是一種簡單、現代、面向對象和類型安全的編程語言。.NET 是由 Microsoft 創建的開發平臺，平臺包含了語言規範、工具、運行，支持開發各種應用，如Web、移動、桌面等。.NET框架有多個實現，如.NET Framework、.NET Core（及後續的.NET 5+版本），以及社區版本M... ...
  • 班級擂臺（光榮）榜 - 極簡教育小工具
    前言 本文介紹瞭如何使用三菱提供的MX Component插件實現對三菱PLC軟元件數據的讀寫，記錄了使用電腦模擬，模擬PLC，直至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1. PLC開發編程環境GX Works2，GX Works2下載鏈接 https:// ...
  • web server apache tomcat11-31-websocket
    前言 整理這個官方翻譯的系列，原因是網上大部分的 tomcat 版本比較舊，此版本為 v11 最新的版本。 開源項目 從零手寫實現 tomcat minicat 別稱【嗅虎】心有猛虎，輕嗅薔薇。 系列文章 web server apache tomcat11-01-官方文檔入門介紹 web serv ...
  • day31-jQuery
    1、jQuery介紹 jQuery是什麼 jQuery是一個快速、簡潔的JavaScript框架，是繼Prototype之後又一個優秀的JavaScript代碼庫（或JavaScript框架）。jQuery設計的宗旨是“write Less，Do More”，即倡導寫更少的代碼，做更多的事情。它封裝 ...
  • aardio爬蟲) 實戰篇：逆向有道翻譯web介面
    前言 之前的文章把js引擎(aardio封裝庫) 微軟開源的js引擎(ChakraCore))寫好了，這篇文章整點js代碼來測一下bug。測試網站：https://fanyi.youdao.com/index.html#/ 逆向思路 逆向思路可以看有道翻譯js逆向（MD5加密，AES加密）附完整源碼 ...
  • 面試官：這就是你理解的Java多線程基礎？
    引言 現代的操作系統（Windows，Linux，Mac OS）等都可以同時打開多個軟體（任務），這些軟體在我們的感知上是同時運行的，例如我們可以一邊瀏覽網頁，一邊聽音樂。而CPU執行代碼同一時間只能執行一條，但即使我們的電腦是單核CPU也可以同時運行多個任務，如下圖所示，這是因為我們的 CPU 的 ...
  • Python文本統計與分析從基礎到進階
    掌握使用Python進行文本英文統計的基本方法，並瞭解如何進一步優化和擴展這些方法，以應對更複雜的文本分析任務。 ...
  • Redis多數據源，看這篇就夠了
    背景 Redis多數據源常見的場景： 分區數據處理：當數據量增長時，單個Redis實例可能無法處理所有的數據。通過使用多個Redis數據源，可以將數據分區存儲在不同的實例中，使得數據處理更加高效。 多租戶應用程式：對於多租戶應用程式，每個租戶可以擁有自己的Redis數據源，以確保數據隔離和安全性。 ...
所有分類