說明
DML(Data Manipulation Language)資料操作語言,是指對資料庫進行增删改的操作指令,主要有INSERT、UPDATE、DELETE三種,代表插入、更新與删除,這是學習MySQL必要掌握的基本知識。
與之前的章節一緻,下方文法中 [] 中内容可以省略。
INSERT操作
逐行插入
文法格式如下:
1 insert into t_name[(column_name1,columnname_2,...)] values (val1,val2);
2 或者
3 insert into t_name set column_name1 = val1,column_name2 = val2;
1、字段名稱和值需要保證數量一直,類型一直,位置一 一對應,否則可能導緻異常。
2、not null的字段需要保證有插入的值,否則會報非空的異常資訊。允許null的字段如果不想輸入資料,字段和值都不出現,或者value用null代替。
3、數值類型,值不需要用單引号括起來,其他的如字元型或日期類型,值需要用單引号括起來;
4、如果表名後面的column_name 省略不寫,則代表覆寫該表的所有字段。值的順序和表中字段順序須保持一緻。
5、上述第二種文法的寫法更繁瑣,現在比較少使用。
測試一下:
1 mysql> desc `user1`;
2 +---------+--------------+------+-----+---------+----------------+
3 | Field | Type | Null | Key | Default | Extra |
4 +---------+--------------+------+-----+---------+----------------+
5 | id | bigint(20) | NO | PRI | NULL | auto_increment |
6 | name | varchar(20) | NO | | NULL | |
7 | age | int(11) | NO | | 0 | |
8 | address | varchar(255) | YES | | NULL | |
9 +---------+--------------+------+-----+---------+----------------+
10 4 rows in set
11
12 mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou');
13 Query OK, 1 row affected
14
15 mysql> insert into `user1`(age,address) values(20,'fuzhou');
16 1364 - Field 'name' doesn't have a default value
17
18 mysql> insert into `user1` values('sol',21,'xiamen');
19 1136 - Column count doesn't match value count at row 1
20
21 mysql> insert into `user1` values(null,'sol',21,'xiamen');
22 Query OK, 1 row affected
23
24 mysql> select * from `user1`;
25 +----+-------+-----+---------+
26 | id | name | age | address |
27 +----+-------+-----+---------+
28 | 3 | brand | 20 | fuzhou |
29 | 4 | sol | 21 | xiamen |
30 +----+-------+-----+---------+
31 2 rows in set
批量插入
文法格式如下:
1 insert into t_name [(column_name1,column_name2)] values (val1_1,val1_2),(val2_1,val2_2)...);
2 或者
3 insert into t_name [(column_name1,column_name2)] select o_name1,o_name2 from o_t_name [where condition];
1、上述第一個文法,values 後面的值個數需要同等配對 column的數量,可以設定多個,逗号隔開,提高資料插入效率。
2、第二個文法,select查詢的字段和插入資料的字段數量、順序、類型需要一緻。 insert的字段可以省略,代表插入t_name表所有字段。條件可選。
測試一下:
1 mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou'),('sol',21,'xiamen');
2 Query OK, 2 rows affected
3 Records: 2 Duplicates: 0 Warnings: 0
4
5 mysql> select * from `user1`;
6 +----+-------+-----+---------+
7 | id | name | age | address |
8 +----+-------+-----+---------+
9 | 5 | brand | 20 | fuzhou |
10 | 6 | sol | 21 | xiamen |
11 +----+-------+-----+---------+
12 2 rows in set
1 mysql> desc `user2`;
2 +---------+--------------+------+-----+---------+----------------+
3 | Field | Type | Null | Key | Default | Extra |
4 +---------+--------------+------+-----+---------+----------------+
5 | id | bigint(20) | NO | PRI | NULL | auto_increment |
6 | name | varchar(20) | NO | | NULL | |
7 | age | int(11) | NO | | 0 | |
8 | address | varchar(255) | YES | | NULL | |
9 | sex | int(11) | NO | | 1 | |
10 +---------+--------------+------+-----+---------+----------------+
11 5 rows in set
12
13 mysql> insert into `user2` (name,age,address,sex) select name,age,address,null from `user1`;
14 Query OK, 2 rows affected
15 Records: 2 Duplicates: 0 Warnings: 0
16
17 mysql> select * from `user2`;
18 +----+-------+-----+---------+------+
19 | id | name | age | address | sex |
20 +----+-------+-----+---------+------+
21 | 7 | brand | 20 | fuzhou | 1 |
22 | 8 | sol | 21 | xiamen | 1 |
23 +----+-------+-----+---------+------+
24 2 rows in set
UPDATE操作
資料更新
文法格式如下:
1 update t_name [[as] alias] set [ alias.]column_name1 = val1,[alias.]column_name2 = val2 [where condition];
1、alias 是别名的意思,别名越簡單識别性越強越好,容易辨認,友善操作,沒有别名情況下,表名就是别名
2、as alias 中as也是可選的,where 條件也是可選的,是以使用者可以選擇需要的,符合特定條件的部分資料進行更新。
測試一下:
1 mysql> select * from `user2`;
2 +----+-------+-----+---------+------+
3 | id | name | age | address | sex |
4 +----+-------+-----+---------+------+
5 | 7 | brand | 20 | fuzhou | NULL |
6 | 8 | sol | 21 | xiamen | NULL |
7 +----+-------+-----+---------+------+
8 2 rows in set
9
10 mysql> update `user2` as u2 set u2.name = 'hero',u2.age=23,u2.sex=1 where id=7;
11 Query OK, 1 row affected
12 Rows matched: 1 Changed: 1 Warnings: 0
13
14 mysql> select * from `user2`;
15 +----+------+-----+---------+------+
16 | id | name | age | address | sex |
17 +----+------+-----+---------+------+
18 | 7 | hero | 23 | fuzhou | 1 |
19 | 8 | sol | 21 | xiamen | NULL |
20 +----+------+-----+---------+------+
21 2 rows in set
還有一種方式是同時更新多個表,使用不同的别名以及一些條件去限制,不過不建議這麼做,操作易錯,并且不好維護。
DELETE操作
delete方式删除
文法格式如下:
1 delete [alias] from t_name [[as] alias] [where condition];
1、跟上面一樣,alias代表别名,沒有别名情況下,表名就是别名
2、如果表設定了别名,則delete後面必須跟上别名,否則資料庫會報異常。
測試一下:
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、如果删除表中所有的資料,則後面不帶上where條件即可,不過要謹慎使用喲。
1 mysql> select * from `user2`;
2 +----+-------+-----+----------+-----+
3 | id | name | age | address | sex |
4 +----+-------+-----+----------+-----+
5 | 8 | sol | 21 | xiamen | 0 |
6 | 10 | brand | 21 | fuzhou | 1 |
7 | 11 | helen | 20 | quanzhou | 0 |
8 +----+-------+-----+----------+-----+
9 3 rows in set
10
11 mysql> delete from `user2`;
12 Query OK, 3 rows affected
13
14 mysql> select * from `user2`;
15 Empty set
truncate方式删除
文法格式如下:
1 truncate t_name;
1 mysql> select * from `user2`;
2 +----+-------+-----+----------+-----+
3 | id | name | age | address | sex |
4 +----+-------+-----+----------+-----+
5 | 12 | brand | 21 | fuzhou | 1 |
6 | 13 | helen | 20 | quanzhou | 0 |
7 | 14 | sol | 21 | xiamen | 0 |
8 +----+-------+-----+----------+-----+
9 3 rows in set
10
11 mysql> truncate `user2`;
12 Query OK, 0 rows affected
13
14 mysql> select * from `user2`;
15 Empty set
看起來跟delete很像,但是重新插入資料會發現,他的自增主鍵會重新從1開始,但是delete的是直接在原來的是以自增值之後往上加。看下面id字段。
1 mysql> insert into `user2` (name,age,address,sex) values('brand',21,'fuzhou',1),('helen',20,'quanzhou',0),('sol',21,'xiamen',0);
2 Query OK, 3 rows affected
3 Records: 3 Duplicates: 0 Warnings: 0
4
5 mysql> select * from `user2`;
6 +----+-------+-----+----------+-----+
7 | id | name | age | address | sex |
8 +----+-------+-----+----------+-----+
9 | 1 | brand | 21 | fuzhou | 1 |
10 | 2 | helen | 20 | quanzhou | 0 |
11 | 3 | sol | 21 | xiamen | 0 |
12 +----+-------+-----+----------+-----+
13 3 rows in set
那 truncate 和 delete有什麼差別呢?我們來梳理下。
truncate和delete的比較
1、truncate 指的是清空表的資料、釋放表的空間,但不删除表的架構定義(表結構)。因為不包含Where條件,是以不是删除具體行,而是将整個表清空了。
2、而delete 語句是删除表中的資料行,可以在後面帶上條件控制删除的次元、範圍,它每次從表中删除一行,會同時将該行的删除操作作為事務儲存在日志中,用于進行可能的復原操作。
3、truncate 和 delete 一樣的地方是:隻是删除資料,涉及到的表結構及其列、限制、索引等均不會變。
4、如果被外鍵 foreign key 限制,不能使用truncate ,隻能使用不帶where子句的delete語句。
5、truncate 操作會記錄在日志中,delete操作會放到 rollback segement 中,執行時要等事務被commit才會生效;是以delete 會觸發删除觸發器(如果有的話),truncate 不會。
6、如果像上面我們測試的那樣,包含自增字段,truncate方式清空之後,自增列的值會被初始化從1開始。
delete方式要分情況判斷(如果資料全部delete,資料庫未被重新開機,則按照之前max+1;資料庫重新開機了,則一樣會重新開始計算自增列的初始值)。
7、前面章節我們還學過drop,drop語句會删除表包括 結構、資料、依賴該表的限制(constrain),觸發器(trigger)索引(index)等。
為幫助開發者們提升面試技能、有機會入職BATJ等大廠公司,特别制作了這個專輯——這一次整體放出。
大緻内容包括了: Java 集合、JVM、多線程、并發程式設計、設計模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大廠面試題等、等技術棧!
歡迎大家關注公衆号【Java爛豬皮】,回複【666】,擷取以上最新Java後端架構VIP學習資料以及視訊學習教程,然後一起學習,一文在手,面試我有。
每一個專欄都是大家非常關心,和非常有價值的話題,如果我的文章對你有所幫助,還請幫忙點贊、好評、轉發一下,你的支援會激勵我輸出更高品質的文章,非常感謝!