首先我们创建一个测试的用户表

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `user2`(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '123123123@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);

1、插入数据

1、不指定具体的字段名

1
INSERT [INTO] tbl_name VALUES|VALUE(值...)

向表中插入一条数据，并查看：

1
2
3
4
5
6
7
8
9
10
mysql> INSERT INTO user2 VALUE(1,"sing","root","sing@163.com",18);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user2;
+----+----------+----------+--------------+------+
| id | username | password | email        | age  |
+----+----------+----------+--------------+------+
|  1 | sing     | root     | sing@163.com |   18 |
+----+----------+----------+--------------+------+
1 row in set (0.00 sec)

2、列出指定字段

1
INSERT [INTO] tbl_name(字段名称1,...) VALUES|VALUE(值1,...)

插入两天记录，且只给用户名和密码有值，其余的将会使用默认值，并查看：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> INSERT user2(username,password) VALUES('A','aaa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT user2(username,password) VALUES('B','bbb');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user2;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | sing     | root     | sing@163.com     |   18 |
|  2 | A        | aaa      | 123123123@qq.com |   18 |
|  3 | B        | bbb      | 123123123@qq.com |   18 |
+----+----------+----------+------------------+------+
3 rows in set (0.01 sec)

3、同时插入多条记录

1
INSERT [INTO] tbl_name[(字段名称...)] VALUES(值...),(值...)...

我们直接查出三条数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> INSERT user2 VALUES(4,'C','ccc','ccc@qq.com',23),
    -> (5,'D','ddd','ddd@qq.com',13),
    -> (6,'E','eee','eee@qq.com',63);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM user2;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | sing     | root     | sing@163.com     |   18 |
|  2 | A        | aaa      | 123123123@qq.com |   18 |
|  3 | B        | bbb      | 123123123@qq.com |   18 |
|  4 | C        | ccc      | ccc@qq.com       |   23 |
|  5 | D        | ddd      | ddd@qq.com       |   13 |
|  6 | E        | eee      | eee@qq.com       |   63 |
+----+----------+----------+------------------+------+
6 rows in set (0.00 sec)

4、通过SET形式插入记录

1
INSERT [INTO] tbl_name SET 字段名称=值,...

测试一下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> INSERT user2 SET id=7,username='F',password='fff';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user2;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | sing     | root     | sing@163.com     |   18 |
|  2 | A        | aaa      | 123123123@qq.com |   18 |
|  3 | B        | bbb      | 123123123@qq.com |   18 |
|  4 | C        | ccc      | ccc@qq.com       |   23 |
|  5 | D        | ddd      | ddd@qq.com       |   13 |
|  6 | E        | eee      | eee@qq.com       |   63 |
|  7 | F        | fff      | 123123123@qq.com |   18 |
+----+----------+----------+------------------+------+
7 rows in set (0.00 sec)

5、将查询结果插入到表中

1
INSERT [INTO] tbl_name[(字段名称,...)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

我们新创建一个表user3,查询user2中的id和username插入到user3表中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> CREATE TABLE IF NOT EXISTS user3(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT user3 SELECT id,username FROM user2;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM user3;
+----+----------+
| id | username |
+----+----------+
|  2 | A        |
|  3 | B        |
|  4 | C        |
|  5 | D        |
|  6 | E        |
|  7 | F        |
|  1 | sing     |
+----+----------+
7 rows in set (0.00 sec)

我们可以看到我们的user2表中有多个字段，如果我们用上面的方法直接将查询结果插入到user3,会报错数据不匹配，我们可以将字段列出来操作：

1
2
3
4
5
mysql> INSERT user3 SELECT id,username FROM user2;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT user3(id,username) SELECT id,username FROM user2;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

2、更新数据

1
UPDATE tbl_name SET 字段名称=值,... [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

如果不加WHERE条件时，会把数据库中所以信息都更新，例如将user2中所有字段都改为33岁：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> UPDATE user2 SET age=33;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from user2;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | sing     | root     | sing@163.com     |   33 |
|  2 | A        | aaa      | 123123123@qq.com |   33 |
|  3 | B        | bbb      | 123123123@qq.com |   33 |
|  4 | C        | ccc      | ccc@qq.com       |   33 |
|  5 | D        | ddd      | ddd@qq.com       |   33 |
|  6 | E        | eee      | eee@qq.com       |   33 |
|  7 | F        | fff      | 123123123@qq.com |   33 |
+----+----------+----------+------------------+------+
7 rows in set (0.00 sec)

更新id为1的密码、年龄：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> UPDATE user2 SET password='sing',age=23 WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user2;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | sing     | sing     | sing@163.com     |   23 |
|  2 | A        | aaa      | 123123123@qq.com |   33 |
|  3 | B        | bbb      | 123123123@qq.com |   33 |
|  4 | C        | ccc      | ccc@qq.com       |   33 |
|  5 | D        | ddd      | ddd@qq.com       |   33 |
|  6 | E        | eee      | eee@qq.com       |   33 |
|  7 | F        | fff      | 123123123@qq.com |   33 |
+----+----------+----------+------------------+------+
7 rows in set (0.00 sec)

3、删除数据

1
DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

同样的如果不加条件的时候会删除所有的数据，测试删除user2中id为7的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> DELETE FROM user2 WHERE id=7;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user2;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | sing     | sing     | sing@163.com     |   23 |
|  2 | A        | aaa      | 123123123@qq.com |   33 |
|  3 | B        | bbb      | 123123123@qq.com |   33 |
|  4 | C        | ccc      | ccc@qq.com       |   33 |
|  5 | D        | ddd      | ddd@qq.com       |   33 |
|  6 | E        | eee      | eee@qq.com       |   33 |
+----+----------+----------+------------------+------+
6 rows in set (0.00 sec)

彻底清空数据表：

1
TRUNCATE [TABLE] tbl_name

这个就不测试啦~