首页 > 代码库 > MySQL 基本操作2

MySQL 基本操作2

MySQL的基本操作————增 删

1.向表中增加数据  insert into 表名 (字段1,字段2……) values (值1,值2……);
mysql> select * from linlin;
Empty set (0.00 sec)

mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| score | float       | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| id    | int(20)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into linlin (score, name, id) values (99, A, 1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from linlin;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|    99 | A    |    1 |
+-------+------+------+
1 row in set (0.00 sec)

也可以一次增加多条数据  insert into 表名 (字段1,字段2……) values (值1,值2……),(值1,值2……),……;
mysql> insert into linlin (score, name, id) values (98, B, 2), (97, C, 3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from linlin;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|    99 | A    |    1 |
|    98 | B    |    2 |
|    97 | C    |    3 |
+-------+------+------+
3 rows in set (0.00 sec)

当插入语句中(字段1,字段2……)省略时,系统会按照表中字段的排列顺序插入
insert into 表名 values (值1,值2……);
mysql> insert into linlin values (96, D, 4);
Query OK, 1 row affected (0.02 sec)

mysql> select * from linlin;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|    99 | A    |    1 |
|    98 | B    |    2 |
|    97 | C    |    3 |
|    96 | D    |    4 |
+-------+------+------+
4 rows in set (0.00 sec)

insert语句的另一种写法:
insert into 表名 set 字段1 = 值1,字段2 = 值2,……;
mysql> insert into linlin set score = 95, name = E, id = 5;
Query OK, 1 row affected (0.02 sec)

mysql> select * from linlin;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|    99 | A    |    1 |
|    98 | B    |    2 |
|    97 | C    |    3 |
|    96 | D    |    4 |
|    95 | E    |    5 |
+-------+------+------+
5 rows in set (0.00 sec)


2.对表中已存在的数据进行修改
update 表名 set 字段1 = 值1,字段2 = 值2,…… where 条件;
mysql> select * from linlin where id = 1;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|   100 | A    |    1 |
+-------+------+------+
1 row in set (0.00 sec)

mysql> update linlin set score = 99 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from linlin where id = 1;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|    99 | A    |    1 |
+-------+------+------+
1 row in set (0.00 sec)

如果需要更新全部数据时,则不需要where条件

3. 删除表中的记录
delete from 表名 where 表达式;
mysql> select * from linlin;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|    99 | A    |    1 |
|    98 | B    |    2 |
|    97 | C    |    3 |
|    96 | D    |    4 |
|    95 | E    |    5 |
+-------+------+------+
5 rows in set (0.00 sec)

mysql> delete from linlin where id = 5;
Query OK, 1 row affected (0.01 sec)

mysql> select * from linlin;
+-------+------+------+
| score | name | id   |
+-------+------+------+
|    99 | A    |    1 |
|    98 | B    |    2 |
|    97 | C    |    3 |
|    96 | D    |    4 |
+-------+------+------+
4 rows in set (0.00 sec)

如果需要删除全部数据,则不必加上where条件

另外,还可以使用 truncate 表名 删除全部数据

truncate 表名 删除全部数据 与 delete from 表名 删除全部数据的异同
它们都可以删除全部数据,但对于自动增加字段的值,truncate 表名 删除
全部数据之后再向表中添加数据时,自动增加字段的默认值时从1开始,而
对于 delete from 表名 删除全部数据,自动增加字段的默认值是从未删除
时该字段的值加1开始。

 

MySQL 基本操作2