Mysql 列修改语句alter/changer/modify

最新推荐文章于 2022-10-26 04:47:17 发布

weixin_30729609

最新推荐文章于 2022-10-26 04:47:17 发布

阅读量146

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/okyoung188/p/6945782.html

版权

column在语句中可以省略
alter column:
设置或删除列的默认值。该操作会直接修改.frm文件而不涉及表数据。所以，这个操作非常快
mySQL> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
| name | varchar(20) | YES  |    | NULL |             |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table example6 alter column name set default 'Gary';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
| name | varchar(20) | YES  |    | Gary |             |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table example6 alter column name drop default;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
| name | varchar(20) | YES  |    | NULL |             |
+--------+-------------+------+-----+---------+----------------+
change column:
列的重命名、列类型的变更以及列位置的移动
mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
| name | varchar(20) | YES  |    | NULL |             |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table example6 change column name NAME varchar(20) not null first;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| NAME | varchar(20) | NO |    | NULL |             |
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table example6 change column NAME name varchar(20) not null default 'Gary' after stu_id;
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
| name | varchar(20) | NO |    | Gary |             |
+--------+-------------+------+-----+---------+----------------+
modify column
除了列的重命名之外，他干的活和CHANGE COLUMN是一样的
mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
| name | varchar(20) | NO |    | Gary |             |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table example6 modify column name varchar(20) not null after stu_id;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra       |
+--------+-------------+------+-----+---------+----------------+
| id    | int(11)    | NO | PRI | NULL | auto_increment |
| stu_id | int(11)    | YES  | UNI | NULL |             |
| name | varchar(20) | NO |    | NULL |             |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
修改表名的方法：
alter table t_old_name rename t_new_name;
rename table t_old_name to t_new_name;

转载于:https://www.cnblogs.com/okyoung188/p/6945782.html