MySQL數(shù)據(jù)操作-DML語(yǔ)句的使用

更新時(shí)間：2020年11月12日 09:50:06 作者：翁智華

這篇文章主要介紹了MySQL數(shù)據(jù)操作-DML語(yǔ)句的使用，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下

說(shuō)明

DML(Data Manipulation Language)數(shù)據(jù)操作語(yǔ)言，是指對(duì)數(shù)據(jù)庫(kù)進(jìn)行增刪改的操作指令，主要有INSERT、UPDATE、DELETE三種，代表插入、更新與刪除，這是學(xué)習(xí)MySQL必要掌握的基本知識(shí)。

方語(yǔ)法中 [] 中內(nèi)容可以省略。

INSERT操作

逐行插入

語(yǔ)法格式如下：

 insert into t_name[(column_name1,columnname_2,...)] values (val1,val2);
 或者
 insert into t_name set column_name1 = val1,column_name2 = val2;

1、字段名稱(chēng)和值需要保證數(shù)量一直，類(lèi)型一直，位置一一對(duì)應(yīng)，否則可能導(dǎo)致異常。

2、not null的字段需要保證有插入的值，否則會(huì)報(bào)非空的異常信息。允許null的字段如果不想輸入數(shù)據(jù)，字段和值都不出現(xiàn)，或者value用null代替。

3、數(shù)值類(lèi)型，值不需要用單引號(hào)括起來(lái)，其他的如字符型或日期類(lèi)型，值需要用單引號(hào)括起來(lái)；

4、如果表名后面的column_name 省略不寫(xiě)，則代表覆蓋該表的所有字段。值的順序和表中字段順序須保持一致。

5、上述第二種語(yǔ)法的寫(xiě)法更繁瑣，現(xiàn)在比較少使用。

測(cè)試一下：

mysql> desc `user1`;
+---------+--------------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra     |
+---------+--------------+------+-----+---------+----------------+
| id   | bigint(20)  | NO  | PRI | NULL  | auto_increment |
| name  | varchar(20) | NO  |   | NULL  |        |
| age   | int(11)   | NO  |   | 0    |        |
| address | varchar(255) | YES |   | NULL  |        |
+---------+--------------+------+-----+---------+----------------+
4 rows in set

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou');
Query OK, 1 row affected

mysql> insert into `user1`(age,address) values(20,'fuzhou');
1364 - Field 'name' doesn't have a default value

mysql> insert into `user1` values('sol',21,'xiamen');
1136 - Column count doesn't match value count at row 1

mysql> insert into `user1` values(null,'sol',21,'xiamen');
Query OK, 1 row affected

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name | age | address |
+----+-------+-----+---------+
| 3 | brand | 20 | fuzhou |
| 4 | sol  | 21 | xiamen |
+----+-------+-----+---------+
2 rows in set

批量插入

語(yǔ)法格式如下：

 insert into t_name [(column_name1,column_name2)] values (val1_1,val1_2),(val2_1,val2_2)...);
 或者
 insert into t_name [(column_name1,column_name2)] select o_name1,o_name2 from o_t_name [where condition];

1、上述第一個(gè)語(yǔ)法，values 后面的值個(gè)數(shù)需要同等配對(duì) column的數(shù)量，可以設(shè)置多個(gè)，逗號(hào)隔開(kāi)，提高數(shù)據(jù)插入效率。

2、第二個(gè)語(yǔ)法，select查詢(xún)的字段和插入數(shù)據(jù)的字段數(shù)量、順序、類(lèi)型需要一致。 insert的字段可以省略，代表插入t_name表所有字段。條件可選。

測(cè)試一下：

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou'),('sol',21,'xiamen');
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name | age | address |
+----+-------+-----+---------+
| 5 | brand | 20 | fuzhou |
| 6 | sol  | 21 | xiamen |
+----+-------+-----+---------+
2 rows in set

mysql> desc `user2`;
+---------+--------------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra     |
+---------+--------------+------+-----+---------+----------------+
| id   | bigint(20)  | NO  | PRI | NULL  | auto_increment |
| name  | varchar(20) | NO  |   | NULL  |        |
| age   | int(11)   | NO  |   | 0    |        |
| address | varchar(255) | YES |   | NULL  |        |
| sex   | int(11)   | NO  |   | 1    |        |
+---------+--------------+------+-----+---------+----------------+
5 rows in set

mysql> insert into `user2` (name,age,address,sex) select name,age,address,null from `user1`;
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name | age | address | sex |
+----+-------+-----+---------+------+
| 7 | brand | 20 | fuzhou | 1  |
| 8 | sol  | 21 | xiamen | 1  |
+----+-------+-----+---------+------+
2 rows in set

UPDATE操作

數(shù)據(jù)更新

語(yǔ)法格式如下：

update t_name [[as] alias] set [ alias.]column_name1 = val1,[alias.]column_name2 = val2 [where condition];

1、alias 是別名的意思，別名越簡(jiǎn)單識(shí)別性越強(qiáng)越好，容易辨認(rèn)，方便操作，沒(méi)有別名情況下，表名就是別名

2、as alias 中as也是可選的，where 條件也是可選的，所以用戶(hù)可以選擇需要的，符合特定條件的部分?jǐn)?shù)據(jù)進(jìn)行更新。

測(cè)試一下：

mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name | age | address | sex |
+----+-------+-----+---------+------+
| 7 | brand | 20 | fuzhou | NULL |
| 8 | sol  | 21 | xiamen | NULL |
+----+-------+-----+---------+------+
2 rows in set

mysql> update `user2` as u2 set u2.name = 'hero',u2.age=23,u2.sex=1 where id=7;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex |
+----+------+-----+---------+------+
| 7 | hero | 23 | fuzhou |  1 |
| 8 | sol | 21 | xiamen | NULL |
+----+------+-----+---------+------+
2 rows in set

還有一種方式是同時(shí)更新多個(gè)表，使用不同的別名以及一些條件去限制，不過(guò)不建議這么做，操作易錯(cuò)，并且不好維護(hù)。

DELETE操作

delete方式刪除

語(yǔ)法格式如下：

delete [alias] from t_name [[as] alias] [where condition];

1、跟上面一樣，alias代表別名，沒(méi)有別名情況下，表名就是別名

2、如果表設(shè)置了別名，則delete后面必須跟上別名，否則數(shù)據(jù)庫(kù)會(huì)報(bào)異常。

測(cè)試一下：

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex |
+----+------+-----+---------+------+
| 7 | hero | 23 | fuzhou |  1 |
| 8 | sol | 21 | xiamen | NULL |
+----+------+-----+---------+------+
2 rows in set

mysql> delete from `user2` as alias where sex=1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as alias where sex=1' at line 1

mysql> delete alias from `user2` as alias where sex=1;
Query OK, 1 row affected

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex |
+----+------+-----+---------+------+
| 8 | sol | 21 | xiamen | NULL |
+----+------+-----+---------+------+
1 row in set

3、如果刪除表中所有的數(shù)據(jù)，則后面不帶上where條件即可，不過(guò)要謹(jǐn)慎使用喲。

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 8 | sol  | 21 | xiamen  |  0 |
| 10 | brand | 21 | fuzhou  |  1 |
| 11 | helen | 20 | quanzhou |  0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> delete from `user2`;
Query OK, 3 rows affected

mysql> select * from `user2`;
Empty set

truncate方式刪除

語(yǔ)法格式如下：

truncate t_name;

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 12 | brand | 21 | fuzhou  |  1 |
| 13 | helen | 20 | quanzhou |  0 |
| 14 | sol  | 21 | xiamen  |  0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> truncate `user2`;
Query OK, 0 rows affected

mysql> select * from `user2`;
Empty set

看起來(lái)跟delete很像，但是重新插入數(shù)據(jù)會(huì)發(fā)現(xiàn)，他的自增主鍵會(huì)重新從1開(kāi)始，但是delete的是直接在原來(lái)的所以自增值之后往上加。看下面id字段。

mysql> insert into `user2` (name,age,address,sex) values('brand',21,'fuzhou',1),('helen',20,'quanzhou',0),('sol',21,'xiamen',0);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou  |  1 |
| 2 | helen | 20 | quanzhou |  0 |
| 3 | sol  | 21 | xiamen  |  0 |
+----+-------+-----+----------+-----+
3 rows in set

那 truncate 和 delete有什么區(qū)別呢？我們來(lái)梳理下。

truncate和delete的比較

1、truncate 指的是清空表的數(shù)據(jù)、釋放表的空間，但不刪除表的架構(gòu)定義(表結(jié)構(gòu))。因?yàn)椴话琖here條件，所以不是刪除具體行，而是將整個(gè)表清空了。

2、而delete 語(yǔ)句是刪除表中的數(shù)據(jù)行，可以在后面帶上條件控制刪除的維度、范圍，它每次從表中刪除一行，會(huì)同時(shí)將該行的刪除操作作為事務(wù)保存在日志中，用于進(jìn)行可能的回滾操作。

3、truncate 和 delete 一樣的地方是：只是刪除數(shù)據(jù)，涉及到的表結(jié)構(gòu)及其列、約束、索引等均不會(huì)變。

4、如果被外鍵 foreign key 約束，不能使用truncate ，只能使用不帶where子句的delete語(yǔ)句。

5、truncate 操作會(huì)記錄在日志中，delete操作會(huì)放到 rollback segement 中，執(zhí)行時(shí)要等事務(wù)被commit才會(huì)生效；所以delete 會(huì)觸發(fā)刪除觸發(fā)器（如果有的話(huà)），truncate 不會(huì)。

6、如果像上面我們測(cè)試的那樣，包含自增字段，truncate方式清空之后，自增列的值會(huì)被初始化從1開(kāi)始。

delete方式要分情況判斷（如果數(shù)據(jù)全部delete，數(shù)據(jù)庫(kù)未被重啟，則按照之前max+1；數(shù)據(jù)庫(kù)重啟了，則一樣會(huì)重新開(kāi)始計(jì)算自增列的初始值）。

7、還有drop，drop語(yǔ)句會(huì)刪除表包括結(jié)構(gòu)、數(shù)據(jù)、依賴(lài)該表的約束（constrain），觸發(fā)器（trigger）索引（index）等。

以上就是MySQL數(shù)據(jù)操作-DML語(yǔ)句的使用的詳細(xì)內(nèi)容，更多關(guān)于MySQL dml語(yǔ)句的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: