mysql更新写入数据_七、MySQL插入、更新与删除数据

最新推荐文章于 2022-03-24 18:18:43 发布

weixin_39928844

最新推荐文章于 2022-03-24 18:18:43 发布

阅读量224

点赞数

文章标签： mysql更新写入数据

本文链接：https://blog.csdn.net/weixin_39928844/article/details/113458792

版权

本文详细介绍了如何使用MySQL进行数据管理，包括插入数据的基本语法，如INSERT INTO语句，同时插入多条记录，以及指定字段插入。还展示了如何更新数据，如UPDATE语句的使用，以及根据条件批量更新。最后，讨论了删除数据的操作，如DELETE语句的运用，删除单条记录和批量删除。此外，提到了TRUNCATE TABLE语句用于快速清空表内容。

摘要由CSDN通过智能技术生成

存储在系统中的数据是数据库管理系统(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快。

weixin_39928844

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql更新写入数据_七、MySQL插入、更新与删除数据

存储在系统中的数据是数据库管理系统(DBMS)的核心，数据库被设计用来管理数据的存储、访问和维护数据的完整性。MySQL中提供丰富的数据管理语句，包括插入数据的INSERT、更新数据的UPDATE以及删除数据的DELETE语句。7.1、插入数据为表的所有字段插入数据使用基本的INSERT语句插入数据，要求指定表名称和插入到新纪录中的值，其基本语法为：INSERTINTOtbl_name(co...
复制链接

扫一扫