存儲在系統中的數據是數據庫管理系統(DBMS)的核心，數據庫被設計用來管理數據的存儲、訪問和維護數據的完整性。MySQL中提供豐富的數據管理語句，包括插入數據的INSERT、更新數據的UPDATE以及刪除數據的DELETE語句。

7.1、插入數據

為表的所有字段插入數據

使用基本的INSERT語句插入數據，要求指定表名稱和插入到新紀錄中的值，其基本語法為：INSERT?INTO?tbl_name?(column_list)?VALUES?(value_list)

在插入數據前，首先創建一張表：mysql>?CREATE?TABLE?person

->?(

->?id?????INT?UNSIGNED?NOT?NULL?AUTO_INCREMENT,

->?name???CHAR(40)?NOT?NULL?DEFAULT?'',

->?age????INT?NOT?NULL?DEFAULT?0,

->?info???CHAR(50)?NULL,

->?PRIMARY?KEY?(id)

->?);

Query?OK,?0?rows?affected?(0.03?sec)

在person表中，插入一條新記錄，id值為1，name值為Green，age值為21，info值為Lawyermysql>??INSERT?INTO?person?(id?,name,?age?,?info)

->???????VALUES?(1,'Green',?21,?'Lawyer');

Query?OK,?1?row?affected?(0.02?sec)

mysql>?SELECT?*?FROM?person;

+----+-------+-----+--------+

|?id?|?name??|?age?|?info???|

+----+-------+-----+--------+

|??1?|?Green?|??21?|?Lawyer?|

+----+-------+-----+--------+

1?row?in?set?(0.00?sec)

在person表中，插入一條新記錄，id值為2，name值為Suse，age值為22，info值為dancermysql>??INSERT?INTO?person?(age?,name,?id?,?info)

->???????VALUES?(22,?'Suse',?2,?'dancer');

Query?OK,?1?row?affected?(0.02?sec)

mysql>?SELECT?*?FROM?person;

+----+-------+-----+--------+

|?id?|?name??|?age?|?info???|

+----+-------+-----+--------+

|??1?|?Green?|??21?|?Lawyer?|

|??2?|?Suse??|??22?|?dancer?|

+----+-------+-----+--------+

2?rows?in?set?(0.00?sec)

為表的指定字段插入數據

在person表中，插入一條新記錄，name值為Willam，age值為20，info值為sports manmysql>??INSERT?INTO?person?(name,?age,info)

->??????VALUES('Willam',?20,?'sports?man');

Query?OK,?1?row?affected?(0.02?sec)

mysql>?SELECT?*?FROM?person;

+----+--------+-----+------------+

|?id?|?name???|?age?|?info???????|

+----+--------+-----+------------+

|??1?|?Green??|??21?|?Lawyer?????|

|??2?|?Suse???|??22?|?dancer?????|

|??3?|?Willam?|??20?|?sports?man?|

+----+--------+-----+------------+

3?rows?in?set?(0.00?sec)

在person表中，插入一條新記錄，name值為laura，age值為25mysql>??INSERT?INTO?person?(name,?age?)?VALUES?('Laura',?25);

Query?OK,?1?row?affected?(0.01?sec)

mysql>?SELECT?*?FROM?person;

+----+--------+-----+------------+

|?id?|?name???|?age?|?info???????|

+----+--------+-----+------------+

|??1?|?Green??|??21?|?Lawyer?????|

|??2?|?Suse???|??22?|?dancer?????|

|??3?|?Willam?|??20?|?sports?man?|

|??4?|?Laura??|??25?|?NULL???????|

+----+--------+-----+------------+

4?rows?in?set?(0.00?sec)

可以發現id字段在插入數據后，沒有賦值時自動增加，在這里id字段為表的主鍵，不能為空，紫銅會自動為字段插入自增的序列值。

同時插入多條記錄

INSERT語句可以同時相數據表中插入多條記錄，插入時指定多個值列表，每個值列表之間用逗號分隔開，基本語法為：INSERT?INTO?tbl_name?(column_list)

VALUES?(value_list1),?(value_list2),(value_list3);

在person表中，在name、age和info字段指定插入值，同時插入3條新記錄mysql>??INSERT?INTO?person(name,?age,?info)

->??????VALUES?('Evans',27,?'secretary'),

->??????('Dale',22,?'cook'),

->??????('Edison',28,?'singer');

Query?OK,?3?rows?affected?(0.02?sec)

Records:?3??Duplicates:?0??Warnings:?0

mysql>?SELECT?*?FROM?person;

+----+--------+-----+------------+

|?id?|?name???|?age?|?info???????|

+----+--------+-----+------------+

|??1?|?Green??|??21?|?Lawyer?????|

|??2?|?Suse???|??22?|?dancer?????|

|??3?|?Willam?|??20?|?sports?man?|

|??4?|?Laura??|??25?|?NULL???????|

|??5?|?Evans??|??27?|?secretary??|

|??6?|?Dale???|??22?|?cook???????|

|??7?|?Edison?|??28?|?singer?????|

+----+--------+-----+------------+

7?rows?in?set?(0.00?sec)

在person表中，不指定插入列表，同時插入2條新記錄mysql>??INSERT?INTO?person

->??????VALUES?(9,'Harry',21,?'magician'),

->??????(NULL,'Harriet',19,?'pianist');

Query?OK,?2?rows?affected?(0.02?sec)

Records:?2??Duplicates:?0??Warnings:?0

mysql>?SELECT?*?FROM?person;

+----+---------+-----+------------+

|?id?|?name????|?age?|?info???????|

+----+---------+-----+------------+

|??1?|?Green???|??21?|?Lawyer?????|

|??2?|?Suse????|??22?|?dancer?????|

|??3?|?Willam??|??20?|?sports?man?|

|??4?|?Laura???|??25?|?NULL???????|

|??5?|?Evans???|??27?|?secretary??|

|??6?|?Dale????|??22?|?cook???????|

|??7?|?Edison??|??28?|?singer?????|

|??9?|?Harry???|??21?|?magician???|

|?10?|?Harriet?|??19?|?pianist????|

+----+---------+-----+------------+

9?rows?in?set?(0.00?sec)

將查詢結果插入數據

INSERT語句用來給數據表插入記錄時，指定插入記錄的列值。INSERT還可以將SELECT語句查詢的結果插入到列表中，其基本語法為：INSERT?INTO?tbl_name1?(column_list1)

SELECT?(column_list2)?FROM?table_name2?WHERE?(condition)

從person_old表中查詢所有的記錄，并將其插入到person表中首先，創建一個名為person_old的數據表，其表結構與person結構相同

mysql>?CREATE?TABLE?person_old

->?(

->?id?????INT?UNSIGNED?NOT?NULL?AUTO_INCREMENT,

->?name???CHAR(40)?NOT?NULL?DEFAULT?'',

->?age????INT?NOT?NULL?DEFAULT?0,

->?info???CHAR(50)?NULL,

->?PRIMARY?KEY?(id)

->?);

Query?OK,?0?rows?affected?(0.11?sec)

向person_old表中添加兩條記錄

mysql>??INSERT?INTO?person_old

->??????VALUES?(11,'Harry',20,?'student'),?(12,'Beckham',31,?'police');

Query?OK,?2?rows?affected?(0.20?sec)

Records:?2??Duplicates:?0??Warnings:?0

mysql>?SELECT?*?FROM?person_old;

+----+---------+-----+---------+

|?id?|?name????|?age?|?info????|

+----+---------+-----+---------+

|?11?|?Harry???|??20?|?student?|

|?12?|?Beckham?|??31?|?police??|

+----+---------+-----+---------+

2?rows?in?set?(0.00?sec)

插入數據到person表中

mysql>?INSERT?INTO?person(id,?name,?age,?info)

->??????SELECT?id,?name,?age,?info?FROM?person_old;

Query?OK,?2?rows?affected?(0.01?sec)

Records:?2??Duplicates:?0??Warnings:?0

mysql>?SELECT?*?FROM?person;

+----+---------+-----+------------+

|?id?|?name????|?age?|?info???????|

+----+---------+-----+------------+

|??1?|?Green???|??21?|?Lawyer?????|

|??2?|?Suse????|??22?|?dancer?????|

|??3?|?Willam??|??20?|?sports?man?|

|??4?|?Laura???|??25?|?NULL???????|

|??5?|?Evans???|??27?|?secretary??|

|??6?|?Dale????|??22?|?cook???????|

|??7?|?Edison??|??28?|?singer?????|

|??9?|?Harry???|??21?|?magician???|

|?10?|?Harriet?|??19?|?pianist????|

|?11?|?Harry???|??20?|?student????|

|?12?|?Beckham?|??31?|?police?????|

+----+---------+-----+------------+

11?rows?in?set?(0.00?sec)

7.2、更新數據

表中有數據之后，可以對數據進行更新，其基本語法為：UPDATE?table_name

SET?col_name1=value1,col_name2=value2,...,

WHERE?where_condition

在person表中，更新id值為11的記錄，將age字段值改為15，將name字段值改為LiMingmysql>?UPDATE?person?SET?age?=?15,?name='LiMing'?WHERE?id?=?11;

Query?OK,?1?row?affected?(0.02?sec)

Rows?matched:?1??Changed:?1??Warnings:?0

mysql>?SELECT?*?FROM?person?WHERE?id=11;

+----+--------+-----+---------+

|?id?|?name???|?age?|?info????|

+----+--------+-----+---------+

|?11?|?LiMing?|??15?|?student?|

+----+--------+-----+---------+

1?row?in?set?(0.00?sec)

在person表中，更新age值為19~22的記錄，將info字段值都改為studentmysql>?UPDATE?person?SET?info='student'??WHERE?age??BETWEEN?19?AND?22;

Query?OK,?6?rows?affected?(0.02?sec)

Rows?matched:?6??Changed:?6??Warnings:?0

mysql>?SELECT?*?FROM?person?WHERE?age?BETWEEN?19?AND?22;

+----+---------+-----+---------+

|?id?|?name????|?age?|?info????|

+----+---------+-----+---------+

|??1?|?Green???|??21?|?student?|

|??2?|?Suse????|??22?|?student?|

|??3?|?Willam??|??20?|?student?|

|??6?|?Dale????|??22?|?student?|

|??9?|?Harry???|??21?|?student?|

|?10?|?Harriet?|??19?|?student?|

+----+---------+-----+---------+

6?rows?in?set?(0.00?sec)

7.3、刪除數據

從數據表中刪除數據使用DELETE語句，其基本語法為：DELETE?FROMN?table_name?[WHERE?]

在person表中，刪除id等于11的記錄執行刪除操作前，使用SELECT語句查看當前id=11的記錄

mysql>??SELECT?*?FROM?person?WHERE?id=11;

+----+--------+-----+---------+

|?id?|?name???|?age?|?info????|

+----+--------+-----+---------+

|?11?|?LiMing?|??15?|?student?|

+----+--------+-----+---------+

1?row?in?set?(0.00?sec)

使用DELETE語句刪除該記錄

mysql>??DELETE?FROM?person?WHERE?id?=?11;

Query?OK,?1?row?affected?(0.02?sec)

語句執行完畢，查看執行結果：

mysql>??SELECT?*?FROM?person?WHERE?id=11;

Empty?set?(0.00?sec)

在person表中，使用DELETE語句同時刪除多條記錄，刪除age字段在19-22的記錄執行刪除操作前，使用SELECT語句查看當前的數據

mysql>??SELECT?*?FROM?person?WHERE?age?BETWEEN?19?AND?22;

+----+---------+-----+---------+

|?id?|?name????|?age?|?info????|

+----+---------+-----+---------+

|??1?|?Green???|??21?|?student?|

|??2?|?Suse????|??22?|?student?|

|??3?|?Willam??|??20?|?student?|

|??6?|?Dale????|??22?|?student?|

|??9?|?Harry???|??21?|?student?|

|?10?|?Harriet?|??19?|?student?|

+----+---------+-----+---------+

6?rows?in?set?(0.00?sec)

DELETE刪除這些記錄

mysql>??DELETE?FROM?person?WHERE?age?BETWEEN?19?AND?22;

Query?OK,?6?rows?affected?(0.01?sec)

查看執行結果

mysql>??SELECT?*?FROM?person?WHERE?age?BETWEEN?19?AND?22;

Empty?set?(0.00?sec)

刪除person表中所有記錄，SQL語句如下執行刪除操作前，使用SELECT語句查看當前的數據：

mysql>?SELECT?*?FROM?person;

+----+---------+-----+-----------+

|?id?|?name????|?age?|?info??????|

+----+---------+-----+-----------+

|??4?|?Laura???|??25?|?NULL??????|

|??5?|?Evans???|??27?|?secretary?|

|??7?|?Edison??|??28?|?singer????|

|?12?|?Beckham?|??31?|?police????|

+----+---------+-----+-----------+

4?rows?in?set?(0.00?sec)

執行DELETE語句刪除這4條記錄

mysql>??DELETE?FROM?person;

Query?OK,?4?rows?affected?(0.01?sec)

查看執行結果：

mysql>??SELECT?*?FROM?person;

Empty?set?(0.00?sec)

如果想刪除表中的所有記錄，還可以使用TRUNCATE TABLE語句，TRUNCATE將直接刪除原來的表并重新創建一個表，其語法結構為TRUNCATE TABLE table_name。TRUNCATE直接刪除表而不是刪除記錄，因此執行速度比DELETE快。

總結

以上是生活随笔為你收集整理的mysql更新写入数据_七、MySQL插入、更新与删除数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。