mysql基础教程 -- 插入，更新与删除

实验环境

mysql版本：5.7.22
操作系统：ubuntu-16.04-x64

插入

命令格式：

INSERT INTO table_name(column_list)  VALUES (value_list)
# table_name 指要插入的表名
# column_list  待插入数据的列名
# value_list 插入的数据

本文使用样例表person,创建语句如下：

CREATE TABLE `person` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
 `name` char(40) NOT NULL DEFAULT '', 
 `age` int(11) NOT NULL DEFAULT 0,  
 `info` char(50) DEFAULT NULL, 
 PRIMARY KEY (`id`));

1.插入所有字段

插入所有字段的数据时，可以在column_list中指定或者不指定。

INSERT INTO `person`(id, name , age, info)VALUES (1, "lisi", 25, "info1");

查询结果：

select * from person;
+----+------+-----+-------+
| id | name | age | info  |
+----+------+-----+-------+
|  1 | lisi |  25 | info1 |
+----+------+-----+-------+
1 row in set (0.00 sec)

2.插入部分字段

INSERT INTO `person`(id, name, info) VALUES (2, "wangwu", "info2");

查询结果：

select * from person;
+----+--------+-----+-------+
| id | name   | age | info  |
+----+--------+-----+-------+
|  1 | lisi   |  25 | info1 |
|  2 | wangwu |   0 | info2 |
+----+--------+-----+-------+
2 rows in set (0.00 sec)

3.插入多条记录

INSERT INTO `person` (id, name , age, info)  VALUES  (3, "tim", 47, "info3"), (4, "bob", 30, "info4");

查询结果：

select * from person;
+----+--------+-----+-------+
| id | name   | age | info  |
+----+--------+-----+-------+
|  1 | lisi   |  25 | info1 |
|  2 | wangwu |   0 | info2 |
|  3 | tim    |  47 | info3 |
|  4 | bob    |  30 | info4 |
+----+--------+-----+-------+
4 rows in set (0.00 sec)

4.将查询结果插入结果
命令格式：

INSERT INTO table_name (column_list1) 
SELECT (column_list2) FROM table_name2 [WHERE (condition)]

table_name为待插入数据的表
column_list1指定要插入的列
table_name2指定插入数据从哪个表中查询出来
column_list2指定数据来源表的查询列
WHERE (condition)为可选参数

例：
创建一个名为person_new的数据的表

CREATE TABLE `person_new` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
 `name` char(40) NOT NULL DEFAULT '', 
 `age` int(11) NOT NULL DEFAULT 0,  
 `info` char(50) DEFAULT NULL, 
 PRIMARY KEY (`id`));

将person的查询结果插入person_new表中

mysql> insert into `person_new` (id, name, age, info) select id, name, age, info from `person`;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from person_new;
+----+--------+-----+-------+
| id | name   | age | info  |
+----+--------+-----+-------+
|  2 | wangwu |  25 | info2 |
|  3 | tim    |  25 | info3 |
|  4 | bob    |  25 | info4 |
+----+--------+-----+-------+
3 rows in set (0.00 sec)

更新

UPDATE table_name SET column_name1 = value1, column_name2 = value2, …. [WHERE (condition)];
table_name指要执行更新操作的表
column_name指字段名，value表示字段的新值。
如果不指定WHERE参数，将对表中的所有行执行更新操作。
例：

mysql> select * from person;
+----+--------+-----+-------+
| id | name   | age | info  |
+----+--------+-----+-------+
|  2 | wangwu |  25 | info2 |
|  3 | tim    |  25 | info3 |
|  4 | bob    |  25 | info4 |
+----+--------+-----+-------+
3 rows in set (0.00 sec)

mysql> update `person` set age = 10 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from person;
+----+--------+-----+-------+
| id | name   | age | info  |
+----+--------+-----+-------+
|  2 | wangwu |  25 | info2 |
|  3 | tim    |  25 | info3 |
|  4 | bob    |  25 | info4 |
+----+--------+-----+-------+
3 rows in set (0.00 sec)

删除

DELETE FROM table_name [WHERE (condition)]
table_name指要执行删除操作的表，[WHERE(condition)]为可选参数，如果不指定将删除表中所有的内容。
例：

mysql> select * from person;
+----+--------+-----+-------+
| id | name   | age | info  |
+----+--------+-----+-------+
|  1 | lisi   |  25 | info1 |
|  2 | wangwu |   0 | info2 |
|  3 | tim    |  47 | info3 |
|  4 | bob    |  30 | info4 |
+----+--------+-----+-------+
4 rows in set (0.00 sec)


mysql> delete from person where id = 1;
Query OK, 1 row affected (0.03 sec)

mysql> select * from person;
+----+--------+-----+-------+
| id | name   | age | info  |
+----+--------+-----+-------+
|  2 | wangwu |   0 | info2 |
|  3 | tim    |  47 | info3 |
|  4 | bob    |  30 | info4 |
+----+--------+-----+-------+
3 rows in set (0.00 sec)