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