玩轉Mysql系列 – 第5天：DML操作匯總，確定你都會？
- 2019 年 10 月 4 日
- 筆記
對mysql感興趣的，加一下我的微信itsoku，拉你入群交流技術。
這是Mysql系列第5篇。
環境：mysql5.7.25，cmd命令中進行演示。
DML(Data Manipulation Language)數據操作語言，以INSERT、UPDATE、DELETE三種指令為核心，分別代表插入、更新與刪除，是必須要掌握的指令，DML和SQL中的select熟稱CRUD（增刪改查）。
文中涉及到的語法用[]包含的內容屬於可選項，下面做詳細說明。
插入操作
插入單行2種方式
方式1
insert into 表名[(欄位,欄位)] values (值,值);
說明： 值和欄位需要一一對應 如果是字元型或日期類型，值需要用單引號引起來；如果是數值類型，不需要用單引號 欄位和值的個數必須一致，位置對應 欄位如果不能為空，則必須插入值 可以為空的欄位可以不用插入值，但需要注意：欄位和值都不寫；或欄位寫上，值用null代替 表名後面的欄位可以省略不寫，此時表示所有欄位，順序和表中欄位順序一致。
方式2
insert into 表名 set 欄位 = 值,欄位 = 值;
方式2不常見，建議使用方式1
批量插入2種方式
方式1
insert into 表名 [(欄位,欄位)] values (值,值),(值,值),(值,值);
方式2
insert into 表 [(欄位,欄位)] 數據來源select語句;
說明： 數據來源select語句可以有很多種寫法，需要注意：select返回的結果和插入數據的欄位數量、順序、類型需要一致。 關於select的寫法後面文章會詳細介紹。
如：
-- 刪除test1 drop table if exists test1; -- 創建test1 create table test1(a int,b int); -- 刪除test2 drop table if exists test2; -- 創建test2 create table test2(c1 int,c2 int,c3 int); -- 向test2中插入數據 insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402); -- 向test1中插入數據 insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2; -- 向test1插入數據，數據來源於test2表 insert into test1 (a,b) select c2,c3 from test2 where c1>=200; select * from test1; mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 2 | 2 | | 201 | 202 | | 301 | 302 | | 401 | 402 | mysql> select * from test2; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 100 | 101 | 102 | | 200 | 201 | 202 | | 300 | 301 | 302 | | 400 | 401 | 402 | +------+------+------+ 4 rows in set (0.00 sec)
數據更新
單表更新
語法：
update 表名 [[as] 別名] set [別名.]欄位 = 值,[別名.]欄位 = 值 [where條件];
有些表名可能名稱比較長，為了方便操作，可以給這個表名起個簡單的別名，更方便操作一些。 如果無別名的時候，表名就是別名。
示例：
mysql> update test1 t set t.a = 2; Query OK, 4 rows affected (0.00 sec) Rows matched: 6 Changed: 4 Warnings: 0 mysql> update test1 as t set t.a = 3; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> update test1 set a = 1,b=2; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0
多表更新
可以同時更新多個表中的數據
語法：
update 表1 [[as] 別名1],表名2 [[as] 別名2] set [別名.]欄位 = 值,[別名.]欄位 = 值 [where條件]
示例：
-- 無別名方式 update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10; -- 無別名方式 update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10 where test1.a = test2.c1; -- 別名方式更新 update test1 t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1; -- 別名的方式更新多個表的多個欄位 update test1 as t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;
使用建議
建議採用單表方式更新，方便維護。
刪除數據操作
使用delete刪除
delete單表刪除
delete [別名] from 表名 [[as] 別名] [where條件];
注意： 如果無別名的時候，表名就是別名 如果有別名，delete後面必須寫別名 如果沒有別名，delete後面的別名可以省略不寫。
示例
-- 刪除test1表所有記錄 delete from test1; -- 刪除test1表所有記錄 delete test1 from test1; -- 有別名的方式，刪除test1表所有記錄 delete t1 from test1 t1; -- 有別名的方式刪除滿足條件的記錄 delete t1 from test1 t1 where t1.a>100;
上面的4種寫法，大家可以認真看一下。
多表刪除
可以同時刪除多個表中的記錄，語法如下：
delete [別名1],[別名2] from 表1 [[as] 別名1],表2 [[as] 別名2] [where條件];
說明： 別名可以省略不寫，但是需要在delete後面跟上表名，多個表名之間用逗號隔開。
示例1
delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;
刪除test1表中的記錄，條件是這些記錄的欄位a在test.c2中存在的記錄
看一下運行效果：
-- 刪除test1 drop table if exists test1; -- 創建test1 create table test1(a int,b int); -- 刪除test2 drop table if exists test2; -- 創建test2 create table test2(c1 int,c2 int,c3 int); -- 向test2中插入數據 insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402); -- 向test1中插入數據 insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2; -- 向test1插入數據，數據來源於test2表 insert into test1 (a,b) select c2,c3 from test2 where c1>=200; mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 2 | 2 | | 201 | 202 | | 301 | 302 | | 401 | 402 | mysql> select * from test2; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 100 | 101 | 102 | | 200 | 201 | 202 | | 300 | 301 | 302 | | 400 | 401 | 402 | +------+------+------+ 4 rows in set (0.00 sec) mysql> delete t1 from test1 t1,test2 t2 where t1.a=t2.c2; Query OK, 3 rows affected (0.00 sec) mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 2 | 2 | +------+------+ 3 rows in set (0.00 sec)
從上面的輸出中可以看到test1表中3條記錄被刪除了。
示例2
delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2;
同時對2個表進行刪除，條件是test.a=test.c2的記錄
看一下運行效果：
-- 刪除test1 drop table if exists test1; -- 創建test1 create table test1(a int,b int); -- 刪除test2 drop table if exists test2; -- 創建test2 create table test2(c1 int,c2 int,c3 int); -- 向test2中插入數據 insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402); -- 向test1中插入數據 insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2; -- 向test1插入數據，數據來源於test2表 insert into test1 (a,b) select c2,c3 from test2 where c1>=200; mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 2 | 2 | | 201 | 202 | | 301 | 302 | | 401 | 402 | +------+------+ 6 rows in set (0.00 sec) mysql> select * from test2; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 100 | 101 | 102 | | 200 | 201 | 202 | | 300 | 301 | 302 | | 400 | 401 | 402 | +------+------+------+ 4 rows in set (0.00 sec) mysql> delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2; Query OK, 6 rows affected (0.00 sec) mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 2 | 2 | +------+------+ 3 rows in set (0.00 sec) mysql> select * from test2; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 100 | 101 | 102 | +------+------+------+ 1 row in set (0.00 sec)
從輸出中可以看出test1和test2總計6條記錄被刪除了。
平時我們用的比較多的方式是delete from 表名這種語法，上面我們介紹了再delete後面跟上表名的用法，大家可以在回顧一下，加深記憶。
使用truncate刪除
語法
truncate 表名;
drop，truncate，delete區別
- drop (刪除表)：刪除內容和定義，釋放空間，簡單來說就是把整個表去掉，以後要新增數據是不可能的，除非新增一個表。 drop語句將刪除表的結構被依賴的約束（constrain），觸發器（trigger）索引（index），依賴於該表的存儲過程/函數將被保留，但其狀態會變為：invalid。 如果要刪除表定義及其數據，請使用 drop table 語句。
- truncate (清空表中的數據)：刪除內容、釋放空間但不刪除定義(保留表的數據結構)，與drop不同的是，只是清空表數據而已。 注意：truncate不能刪除具體行數據，要刪就要把整個表清空了。
- delete (刪除表中的數據)：delete 語句用於刪除表中的行。delete語句執行刪除的過程是每次從表中刪除一行，並且同時將該行的刪除操作作為事務記錄在日誌中保存，以便進行進行回滾操作。 truncate與不帶where的delete ：只刪除數據，而不刪除表的結構（定義） truncate table 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。 對於由foreign key約束引用的表，不能使用truncate table ，而應使用不帶where子句的delete語句。由於truncate table 記錄在日誌中，所以它不能激活觸發器。 delete語句是資料庫操作語言(dml)，這個操作會放到 rollback segement 中，事務提交之後才生效；如果有相應的 trigger，執行的時候將被觸發。 truncate、drop 是資料庫定義語言(ddl)，操作立即生效，原數據不放到 rollback segment 中，不能回滾，操作不觸發 trigger。 如果有自增列，truncate方式刪除之後，自增列的值會被初始化，delete方式要分情況（如果資料庫被重啟了，自增列值也會被初始化，資料庫未被重啟，則不變）
- 如果要刪除表定義及其數據，請使用 drop table 語句
- 安全性：小心使用 drop 和 truncate，尤其沒有備份的時候，否則哭都來不及
- 刪除速度，一般來說: drop> truncate > delete
mysql系列大概有20多篇，喜歡的請關注一下，歡迎大家加我微信itsoku或者留言交流mysql相關技術!
