天天看點

MySQL 增删改

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開始。