四、表字段的操作

1、字段的增加

alter table 表名 add 字段名 字段类型;

-- 默认在表末尾增加
alter table 表名 add 字段名  字段类型;
-- 添加在第一个字段，好像没有第二个字段的说法
alter table 表名 add 字段名  字段类型 first;
-- 添加到某一个字段之后
alter table 表名 add 字段名  字段类型 after 字段名（被指定）;
-- ===================================================================== --
-- ===================================================================== --
举例：
-- 在最后添加语文成绩
mysql> alter table student add Chinese float(4,1);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| day     | date        | YES  |     | NULL    |       |
| Chinese | float(4,1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
-- ===================================================================== --
-- ===================================================================== --
-- 在第一列添加班级 
mysql> alter table student add class int first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 继续往后添加数学，方便对照之后的英语效果
mysql> alter table student add Math float(4,1);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| class   | int         | YES  |     | NULL    |       |
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| day     | date        | YES  |     | NULL    |       |
| Chinese | float(4,1)  | YES  |     | NULL    |       |
| Math    | float(4,1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
-- ===================================================================== --
-- ===================================================================== --
-- 在语文后添加英语
mysql> alter table student add English float(4,1) after Chinese;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| class   | int         | YES  |     | NULL    |       |
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| day     | date        | YES  |     | NULL    |       |
| Chinese | float(4,1)  | YES  |     | NULL    |       |
| English | float(4,1)  | YES  |     | NULL    |       |
| Math    | float(4,1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

2、字段长度的修改

alter table 表名 modify column 字段名 数据类型(长度);

alter table 表名 modify column 字段名 数据类型(长度);
 
举例：
mysql> alter table student modify column class varchar(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
-- ===================================================================== --
-- 反正班级列我也没写不怕破坏数据
mysql> select * from student;
+-------+------+-----------+------+------------+---------+---------+------+
| class | id   | name      | sex  | day        | Chinese | English | Math |
+-------+------+-----------+------+------------+---------+---------+------+
| NULL  |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL | NULL |
| NULL  |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL | NULL |
| NULL  |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL | NULL |
| NULL  |    2 | 曹操      | 男   | 0155-01-01 |    NULL |    NULL | NULL |
| NULL  |    2 | 达摩      | 男   | 2015-01-01 |    NULL |    NULL | NULL |
+-------+------+-----------+------+------------+---------+---------+------+
-- =================================================================================== --
mysql> insert into student values ('九零一班',1514,'蔷薇','女','2018-1-1',150.0,150.0,150);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from student;
+--------------+------+-----------+------+------------+---------+---------+-------+
| class        | id   | name      | sex  | day        | Chinese | English | Math  |
+--------------+------+-----------+------+------------+---------+---------+-------+
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 曹操      | 男   | 0155-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 达摩      | 男   | 2015-01-01 |    NULL |    NULL |  NULL |
| 九零一班     | 1514 | 蔷薇      | 女   | 2018-01-01 |   150.0 |   150.0 | 150.0 |
+--------------+------+-----------+------+------------+---------+---------+-------+
-- =================================================================================== --
mysql> alter table student modify column sex varchar(1);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
 
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| class   | varchar(5)  | YES  |     | NULL    |       |
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| sex     | varchar(1)  | YES  |     | NULL    |       |
| day     | date        | YES  |     | NULL    |       |
| Chinese | float(4,1)  | YES  |     | NULL    |       |
| English | float(4,1)  | YES  |     | NULL    |       |
| Math    | float(4,1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
 
mysql> select * from student;
+--------------+------+-----------+------+------------+---------+---------+-------+
| class        | id   | name      | sex  | day        | Chinese | English | Math  |
+--------------+------+-----------+------+------------+---------+---------+-------+
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 曹操      | 男   | 0155-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 达摩      | 男   | 2015-01-01 |    NULL |    NULL |  NULL |
| 九零一班     | 1514 | 蔷薇      | 女   | 2018-01-01 |   150.0 |   150.0 | 150.0 |
+--------------+------+-----------+------+------------+---------+---------+-------+
-- =================================================================================== --
mysql> alter table student modify column class varchar(2);
ERROR 1265 (01000): Data truncated for column 'class' at row 6
 
mysql> select * from student;
+--------------+------+-----------+------+------------+---------+---------+-------+
| class        | id   | name      | sex  | day        | Chinese | English | Math  |
+--------------+------+-----------+------+------------+---------+---------+-------+
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 曹操      | 男   | 0155-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 达摩      | 男   | 2015-01-01 |    NULL |    NULL |  NULL |
| 九零一班     | 1514 | 蔷薇      | 女   | 2018-01-01 |   150.0 |   150.0 | 150.0 |
+--------------+------+-----------+------+------------+---------+---------+-------+
 
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| class   | varchar(5)  | YES  |     | NULL    |       |
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| sex     | varchar(1)  | YES  |     | NULL    |       |
| day     | date        | YES  |     | NULL    |       |
| Chinese | float(4,1)  | YES  |     | NULL    |       |
| English | float(4,1)  | YES  |     | NULL    |       |
| Math    | float(4,1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

注意：修改长度不能小于原有的长度 ， 否则原有数据会被破会 ， 不可修复。

不过好像会报错：ERROR 1265 (01000): Data truncated for column 'class' at row 6

3、字段数据类型的修改

alter table 表名 modify 字段名 数据类型(长度);

alter table 表名 modify 字段名 数据类型(长度);
 
举例：
mysql> alter table student modify sex enum('男','女')
    -> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
 
mysql> desc student;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| class   | varchar(5)        | YES  |     | NULL    |       |
| id      | int               | YES  |     | NULL    |       |
| name    | varchar(10)       | YES  |     | NULL    |       |
| sex     | enum('男','女')   | YES  |     | NULL    |       |
| day     | date              | YES  |     | NULL    |       |
| Chinese | float(4,1)        | YES  |     | NULL    |       |
| English | float(4,1)        | YES  |     | NULL    |       |
| Math    | float(4,1)        | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+
 
mysql> select * from student;
+--------------+------+-----------+------+------------+---------+---------+-------+
| class        | id   | name      | sex  | day        | Chinese | English | Math  |
+--------------+------+-----------+------+------------+---------+---------+-------+
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 曹操      | 男   | 0155-01-01 |    NULL |    NULL |  NULL |
| NULL         |    2 | 达摩      | 男   | 2015-01-01 |    NULL |    NULL |  NULL |
| 九零一班     | 1514 | 蔷薇      | 女   | 2018-01-01 |   150.0 |   150.0 | 150.0 |
+--------------+------+-----------+------+------------+---------+---------+-------+
 

4、字段名修改

alter table 表名 change 旧的字段名 新的字段名 新的数据类型;

alter table 表名 change 旧的字段名  新的字段名 新的数据类型;
 
举例：
mysql> alter table student change id xuehao int(3);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> desc student;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| class   | varchar(5)        | YES  |     | NULL    |       |
| xuehao  | int               | YES  |     | NULL    |       |
| name    | varchar(10)       | YES  |     | NULL    |       |
| sex     | enum('男','女')   | YES  |     | NULL    |       |
| day     | date              | YES  |     | NULL    |       |
| Chinese | float(4,1)        | YES  |     | NULL    |       |
| English | float(4,1)        | YES  |     | NULL    |       |
| Math    | float(4,1)        | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+
 
mysql> select * from student;
+--------------+--------+-----------+------+------------+---------+---------+-------+
| class        | xuehao | name      | sex  | day        | Chinese | English | Math  |
+--------------+--------+-----------+------+------------+---------+---------+-------+
| NULL         |      1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |      1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |      1 | 安琪拉    | 女   | 2015-01-01 |    NULL |    NULL |  NULL |
| NULL         |      2 | 曹操      | 男   | 0155-01-01 |    NULL |    NULL |  NULL |
| NULL         |      2 | 达摩      | 男   | 2015-01-01 |    NULL |    NULL |  NULL |
| 九零一班     |   1514 | 蔷薇      | 女   | 2018-01-01 |   150.0 |   150.0 | 150.0 |
+--------------+--------+-----------+------+------------+---------+---------+-------+

5、字段的删除

alter table 表名 drop column 字段名;

alter table 表名 drop column 字段名;
 
举例：
mysql> alter table student drop column day;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc student;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| class   | varchar(5)        | YES  |     | NULL    |       |
| xuehao  | int               | YES  |     | NULL    |       |
| name    | varchar(10)       | YES  |     | NULL    |       |
| sex     | enum('男','女')   | YES  |     | NULL    |       |
| Chinese | float(4,1)        | YES  |     | NULL    |       |
| English | float(4,1)        | YES  |     | NULL    |       |
| Math    | float(4,1)        | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+
 
mysql> select * from student;
+--------------+--------+-----------+------+---------+---------+-------+
| class        | xuehao | name      | sex  | Chinese | English | Math  |
+--------------+--------+-----------+------+---------+---------+-------+
| NULL         |      1 | 安琪拉    | 女   |    NULL |    NULL |  NULL |
| NULL         |      1 | 安琪拉    | 女   |    NULL |    NULL |  NULL |
| NULL         |      1 | 安琪拉    | 女   |    NULL |    NULL |  NULL |
| NULL         |      2 | 曹操      | 男   |    NULL |    NULL |  NULL |
| NULL         |      2 | 达摩      | 男   |    NULL |    NULL |  NULL |
| 九零一班     |   1514 | 蔷薇      | 女   |   150.0 |   150.0 | 150.0 |
+--------------+--------+-----------+------+---------+---------+-------+

6、修改表名

alter table 表名 rename 新的表名;

alter table 表名 rename 新的表名;
 
举例：确实不分大小写，都大小写混用了，都不报错
mysql> alter tABLE STUDENT RENAME B1;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from student;
ERROR 1146 (42S02): Table 'b1.student' doesn't exist
 
mysql> desc B1;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| class   | varchar(5)        | YES  |     | NULL    |       |
| xuehao  | int               | YES  |     | NULL    |       |
| name    | varchar(10)       | YES  |     | NULL    |       |
| sex     | enum('男','女')   | YES  |     | NULL    |       |
| Chinese | float(4,1)        | YES  |     | NULL    |       |
| English | float(4,1)        | YES  |     | NULL    |       |
| Math    | float(4,1)        | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+
 
mysql> select * from B1;
+--------------+--------+-----------+------+---------+---------+-------+
| class        | xuehao | name      | sex  | Chinese | English | Math  |
+--------------+--------+-----------+------+---------+---------+-------+
| NULL         |      1 | 安琪拉    | 女   |    NULL |    NULL |  NULL |
| NULL         |      1 | 安琪拉    | 女   |    NULL |    NULL |  NULL |
| NULL         |      1 | 安琪拉    | 女   |    NULL |    NULL |  NULL |
| NULL         |      2 | 曹操      | 男   |    NULL |    NULL |  NULL |
| NULL         |      2 | 达摩      | 男   |    NULL |    NULL |  NULL |
| 九零一班     |   1514 | 蔷薇      | 女   |   150.0 |   150.0 | 150.0 |
+--------------+--------+-----------+------+---------+---------+-------+
 

7、清空表数据

delete from 表名;

delete from 表名;
 
举例: 表已为空，但是字段信息还在
mysql> delete from b1
    -> ;
Query OK, 6 rows affected (0.00 sec)
 
mysql> select * from B1;
Empty set (0.00 sec)
 
mysql> desc B1;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| class   | varchar(5)        | YES  |     | NULL    |       |
| xuehao  | int               | YES  |     | NULL    |       |
| name    | varchar(10)       | YES  |     | NULL    |       |
| sex     | enum('男','女')   | YES  |     | NULL    |       |
| Chinese | float(4,1)        | YES  |     | NULL    |       |
| English | float(4,1)        | YES  |     | NULL    |       |
| Math    | float(4,1)        | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+

8、删除表

drop table 表名;

drop table 表名; -- 可以连删，逗号分割就行
 
举例：
mysql> drop table b1;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| b1                 |
| fhy1               |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
 
mysql> select database();
+------------+
| database() |
+------------+
| b1         |
+------------+
 
mysql> show tables;
Empty set (0.00 sec)
 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
-- 比较另外一个数据库，
-- 发现，数据库b1中的 表b1确实删除了。(改名前叫student，早知道不叫b1了，和数据库重名了，看着不方便)
mysql> use fhy1;
Database changed
 
mysql> select database();
+------------+
| database() |
+------------+
| fhy1       |
+------------+
 
mysql> show tables;
+----------------+
| Tables_in_fhy1 |
+----------------+
| employee       |
| sanguo         |
| t1             |
+----------------+