表结构修改操作
在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)