MySQL 表的增删改查操作

表结构修改操作

在book表里添加一个字段；

格式：alter table 表名 add 字段名称字段类型;

mysql> alter table book add count int;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

　　查看表结构

mysql> desc book;  #简写
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| datel | date        | YES  |     | NULL    |       |
| price | double(5,2) | YES  |     | NULL    |       |
| count | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> describe book;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| datel | date        | YES  |     | NULL    |       |
| price | double(5,2) | YES  |     | NULL    |       |
| count | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

　　删除某的字段操作

mysql> alter table book drop count;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> describe book;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| datel | date        | YES  |     | NULL    |       |
| price | double(5,2) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

　　修改字段操作

mysql> alter table book modify price int;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> describe book;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| datel | date        | YES  |     | NULL    |       |
| price | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

　　插入操作

格式：insert into 表名(想插入的字段名称.....) values(想插入字段的值)；

insert into 表名 values(表中所有字段的值)

mysql> insert into book(num) values(1);
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from book;
+------+------+-------+-------+
| num  | name | datel | price |
+------+------+-------+-------+
|    1 | NULL | NULL  |  NULL |
+------+------+-------+-------+
1 row in set (0.00 sec)

　　插入两个字段操作

mysql> insert into book(num,name) values(2,'chenxi');
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from book;
+------+--------+-------+-------+
| num  | name   | datel | price |
+------+--------+-------+-------+
|    1 | NULL   | NULL  |  NULL |
|    2 | chenxi | NULL  |  NULL |
+------+--------+-------+-------+
2 rows in set (0.00 sec)

当你要对这个表的所有字段插入数据。可以不写前面的字段名称。但是values必须要对应表里面的所有字段名称。

会默认对照你数据表的格式进行一一对应的插入。

mysql> insert into book values(3,'cv','2020.4.4','34');
Query OK, 1 row affected (0.39 sec)
mysql> select * from book;
+------+--------+------------+-------+
| num  | name   | datel      | price |
+------+--------+------------+-------+
|    1 | NULL   | NULL       |  NULL |
|    2 | chenxi | NULL       |  NULL |
|    3 | cv     | 2020-04-04 |    34 |
+------+--------+------------+-------+
3 rows in set (0.00 sec)

　删除修改操作　

清空表

1 2	`mysql> DELETE FROM tf;` `Query OK, 0 rows affected (0.00 sec)`

　　删除num值为1的这条记录

mysql> delete from book where num=1;
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from book;
+------+--------+------------+-------+
| num  | name   | datel      | price |
+------+--------+------------+-------+
|    2 | chenxi | NULL       |  NULL |
|    3 | cv     | 2020-04-04 |    34 |
+------+--------+------------+-------+
2 rows in set (0.00 sec)

修改表中记录

语法：

update 表名 set 字段名=新的字段值,......

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 旧值

修改price字段所有值；

mysql> update book set price = 90;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
 
mysql> select * from book;
+------+--------+------------+-------+
| num  | name   | datel      | price |
+------+--------+------------+-------+
|    2 | chenxi | NULL       |    90 |
|    3 | cv     | 2020-04-04 |    90 |
+------+--------+------------+-------+
2 rows in set (0.00 sec)

修改where num = 3的price为9

mysql> update book set price = 99 where num = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from book;
+------+--------+------------+-------+
| num  | name   | datel      | price |
+------+--------+------------+-------+
|    2 | chenxi | NULL       |    90 |
|    3 | cv     | 2020-04-04 |    99 |
+------+--------+------------+-------+
2 rows in set (0.00 sec)

修改一条记录多个字段。（只需要,号隔开）

mysql> update book set name = 'linux', num = '1' where num = 3;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from book;
+------+--------+------------+-------+
| num  | name   | datel      | price |
+------+--------+------------+-------+
|    2 | chenxi | NULL       |    90 |
|    1 | linux  | 2020-04-04 |    99 |
+------+--------+------------+-------+
2 rows in set (0.00 sec)