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