MySQL 表的增删查改

一、插入数据

1. INSERT ... VALUES ...

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

针对特定字段添加数据：

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

添加多行数据：

# 插入多行数据，在 values 后面表示，每个（）里面的数据代表一行。
mysql> insert into user
    -> (id,name)
    -> values (4,'zhao'),(5,'qian');


mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
+----+------+----------+---------+------+

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

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

2. INSERT ... SET ...

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

e.g.

mysql> insert into user
    -> set
    -> id = 6,
    -> name='sun';
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
+----+------+----------+---------+------+

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

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

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

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

二、修改数据

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

可选项：

• WHERE 限定修改范围，不指定where，则修改所有数据！
• ORDER BY 排序
• LIMIT 限定修改行数

e.g.

# 先查询一下
mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | NULL | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
| 10 | NULL | NULL     |    NULL |   87 |
+----+------+----------+---------+------+

# 修改数据，将id为7的name字段改为‘Zhou’
mysql> update user set name='Zhou' where id=7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
| 10 | NULL | NULL     |    NULL |   87 |
+----+------+----------+---------+------+

三、删除数据

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

删除一条数据：

mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
| 10 | NULL | NULL     |    NULL |   87 |
+----+------+----------+---------+------+

mysql> delete from user where id = 10;


mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
+----+------+----------+---------+------+

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

mysql> select * from grade;
+------+--------+
| gid  | record |
+------+--------+
|    1 |     90 |
|    2 |     80 |
|    3 |     70 |
|    4 |     87 |
+------+--------+


mysql> delete from grade;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from grade;Empty set (0.00 sec)