一、插入数据

1. INSERT ... VALUES ...

1INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
2VALUES (值1) [… , (值n) ];

针对特定字段添加数据：

1mysql> insert into user
2    -> (id,name,password,balance,debt)
3    -> values(2,'song','1234',3000,100);
4Query OK, 1 row affected (0.01 sec)

添加多行数据：

1# 插入多行数据，在 values 后面表示，每个（）里面的数据代表一行。mysql> insert into user
2    -> (id,name)
3    -> values (4,'zhao'),(5,'qian');
4
5
6mysql> select * from user;
7+----+------+----------+---------+------+
8| id | name | password | balance | debt |
9+----+------+----------+---------+------+
10|  1 | Wang | 1234     |   15000 |    0 |
11|  2 | song | 1234     |    3000 |  100 |
12|  3 | li   | 1234     |    2000 |    0 |
13|  4 | zhao | NULL     |    NULL | NULL |
14|  5 | qian | NULL     |    NULL | NULL |
15+----+------+----------+---------+------+

针对所有字段添加数据，可以省略字段部分：

1mysql> insert into user
2    -> values(3,'li','1234',2000,0);

2. INSERT ... SET ...

1INSERT INTO <表名>
2SET <字段1> = <值1>,
3    <字段2> = <值2>,　　 ...;

e.g.

1mysql> insert into user
2    -> set
3    -> id = 6,
4    -> name='sun';
5Query OK, 1 row affected (0.01 sec)
6
7mysql> select * from user;
8+----+------+----------+---------+------+
9| id | name | password | balance | debt |
10+----+------+----------+---------+------+
11|  1 | Wang | 1234     |   15000 |    0 |
12|  2 | song | 1234     |    3000 |  100 |
13|  3 | li   | 1234     |    2000 |    0 |
14|  4 | zhao | NULL     |    NULL | NULL |
15|  5 | qian | NULL     |    NULL | NULL |
16|  6 | sun  | NULL     |    NULL | NULL |
17+----+------+----------+---------+------+

3. INSERT ... SELECT ... FROM...

将一个表中的数据复制到另一个表中：

将grade表中的record字段的数据，复制到user表中的debt字段中。

1mysql> insert into user
2    -> (debt)
3    -> select record from grade;
4Query OK, 4 rows affected (0.01 sec)

二、修改数据

1UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
2[ORDER BY 子句] [LIMIT 子句]可选项：WHERE     限定修改范围，不指定where，则修改所有数据！ORDER BY  排序LIMIT     限定修改行数

e.g.

1# 先查询一下mysql> select * from user;
2+----+------+----------+---------+------+
3| id | name | password | balance | debt |
4+----+------+----------+---------+------+
5|  1 | Wang | 1234     |   15000 |    0 |
6|  2 | song | 1234     |    3000 |  100 |
7|  3 | li   | 1234     |    2000 |    0 |
8|  4 | zhao | NULL     |    NULL | NULL |
9|  5 | qian | NULL     |    NULL | NULL |
10|  6 | sun  | NULL     |    NULL | NULL |
11|  7 | NULL | NULL     |    NULL |   90 |
12|  8 | NULL | NULL     |    NULL |   80 |
13|  9 | NULL | NULL     |    NULL |   70 |
14| 10 | NULL | NULL     |    NULL |   87 |
15+----+------+----------+---------+------+
16
17# 修改数据，将id为7的name字段改为‘Zhou’
18mysql> update user set name='Zhou' where id=7;
19Query OK, 1 row affected (0.01 sec)
20Rows matched: 1  Changed: 1  Warnings: 0
21
22mysql> select * from user;
23+----+------+----------+---------+------+
24| id | name | password | balance | debt |
25+----+------+----------+---------+------+
26|  1 | Wang | 1234     |   15000 |    0 |
27|  2 | song | 1234     |    3000 |  100 |
28|  3 | li   | 1234     |    2000 |    0 |
29|  4 | zhao | NULL     |    NULL | NULL |
30|  5 | qian | NULL     |    NULL | NULL |
31|  6 | sun  | NULL     |    NULL | NULL |
32|  7 | Zhou | NULL     |    NULL |   90 |
33|  8 | NULL | NULL     |    NULL |   80 |
34|  9 | NULL | NULL     |    NULL |   70 |
35| 10 | NULL | NULL     |    NULL |   87 |
36+----+------+----------+---------+------+

三、删除数据

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

删除一条数据：

1mysql> select * from user;
2+----+------+----------+---------+------+
3| id | name | password | balance | debt |
4+----+------+----------+---------+------+
5|  1 | Wang | 1234     |   15000 |    0 |
6|  2 | song | 1234     |    3000 |  100 |
7|  3 | li   | 1234     |    2000 |    0 |
8|  4 | zhao | NULL     |    NULL | NULL |
9|  5 | qian | NULL     |    NULL | NULL |
10|  6 | sun  | NULL     |    NULL | NULL |
11|  7 | Zhou | NULL     |    NULL |   90 |
12|  8 | NULL | NULL     |    NULL |   80 |
13|  9 | NULL | NULL     |    NULL |   70 |
14| 10 | NULL | NULL     |    NULL |   87 |
15+----+------+----------+---------+------+
16
17mysql> delete from user where id = 10;
18
19
20mysql> select * from user;
21+----+------+----------+---------+------+
22| id | name | password | balance | debt |
23+----+------+----------+---------+------+
24|  1 | Wang | 1234     |   15000 |    0 |
25|  2 | song | 1234     |    3000 |  100 |
26|  3 | li   | 1234     |    2000 |    0 |
27|  4 | zhao | NULL     |    NULL | NULL |
28|  5 | qian | NULL     |    NULL | NULL |
29|  6 | sun  | NULL     |    NULL | NULL |
30|  7 | Zhou | NULL     |    NULL |   90 |
31|  8 | NULL | NULL     |    NULL |   80 |
32|  9 | NULL | NULL     |    NULL |   70 |
33+----+------+----------+---------+------+

删除所有数据：delete from <表名>;

1mysql> select * from grade;
2+------+--------+
3| gid  | record |
4+------+--------+
5|    1 |     90 |
6|    2 |     80 |
7|    3 |     70 |
8|    4 |     87 |
9+------+--------+
10
11
12mysql> delete from grade;
13Query OK, 4 rows affected (0.01 sec)
14
15mysql> select * from grade;Empty set (0.00 sec)