修改表名
语句:alter table 旧表名 rename to 新表名;to为可选参数,有无都不想影响结构。
例子:
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1 |
| tb_dept2 |
| tb_dept3 |
| tb_emp0 |
| tb_emp1 |
| tb_emp2 |
| tb_emp5 |
| tb_emp6 |
| tb_emp7 |
| tb_emp8 |
+-------------------+
10 rows in set (0.01 sec)
mysql> alter table tb_dept3 rename tb_deptment3;
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1 |
| tb_dept2 |
| tb_deptment3 |
| tb_emp0 |
| tb_emp1 |
| tb_emp2 |
| tb_emp5 |
| tb_emp6 |
| tb_emp7 |
| tb_emp8 |
+-------------------+
10 rows in set (0.00 sec)
修改字段的数据类型
语句:alter table 表名 modify 字段名 数据类型
例子:
mysql> desc tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table tb_dept1 modify name varchar(30);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改字段名
语法:alter table 表名 change 旧字段名 新字段名 新数据类型;
这个即可以更改字段名,也可以更改数据类型。
例子:
mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| location | varchar(45) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table tb_dept1 change location loc varchar(50);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加字段
语法:alter table 表名 add 新字段名 数据类型 约束条件 frist|after 已存在字段名。
添加无完整性约束条件的字段
例子:
mysql> alter table tb_dept1 add managerrId int(10);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe tb_dept1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerrId | int(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
添加有完整性约束条件的字段
例子:
mysql> describe tb_dept1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerrId | int(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table tb_dept1 add column1 varchar(12) not null;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings