mysql+表+无法+增删改_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)

删除修改操作

清空表

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)