DDL-表操作-修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束];
DDL-表操作-修改.
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];
DDL-表操作-修改
修改表名
ALTER TABLE 表名 RENAMETO 新表名;
DDL-表操作-删除.
删除表
DROP TABLE[IF EXISTS]表名;
删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
mysql> use itcast;
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| tbuser |
| tmp |
+------------------+
mysql> desc tmp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydata | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
//为emp表增加一个新的字段”昵称”为nickname,类型为varchar(20)
mysql> alter table tmp add nickname varchar(20);
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydata | date | YES | | NULL | |
| nickname | varchar(20) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
//将emp表的nickname字段修改为username,类型为varchar(30)
alter table tmp change nickname username varchar(30) comment '用户名';
mysql> desc tmp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydata | date | YES | | NULL | |
| username | varchar(30) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
//将emp表的字段username删除
mysql> alter table tmp drop username;
mysql> desc tmp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydata | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
//将emp表的表名修改为 employee
mysql> alter table tmp rename to employee;
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employee |
| tbuser |
+------------------+
//删除表tbuser
mysql> drop table if exists tbuser;
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employee |
+------------------+