Mysql 修改
来源:互联网 发布:淘宝店铺名怎么改不了 编辑:程序博客网 时间:2024/05/07 22:20
1.修改表名:
alter table table_oldname rename [to] table_newname;
mysql> alter table example0 rename user;
Query OK, 0 rows affected (0.00 sec)
2.修改字段数据类型:
alter table table_name modify attribute data_type;
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id | int(11) | NO | PRI | NULL | |
| stu_name | varchar(20) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table user modify stu_name varchar(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id | int(11) | NO | PRI | NULL | |
| stu_name | varchar(30) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
3.修改字段名:
alter table table_name chane old_attribute new_attribute new_data_type;
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id | int(11) | NO | PRI | NULL | |
| stu_name | varchar(30) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table user change stu_name name varchar(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
增加字段
alter table table_name add attribute_1 data_thype [constraint] [first|after attribute_2]
在不指名位置的情况下,默认字段增加在最后一列:
mysql> alter table user add age int(4) not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
| age | int(4) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
在表的第一列增加字段:
mysql> alter table user add num int(8) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num | int(8) | YES | | NULL | |
| stu_id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
| age | int(4) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
在表的指定位置后面增加字段:
mysql> alter table user add address varchar(30) not null after stu_id;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num | int(8) | YES | | NULL | |
| stu_id | int(11) | NO | PRI | NULL | |
| address | varchar(30) | NO | | NULL | |
| name | varchar(30) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
| age | int(4) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
删除字段
alter table table_name drop attribute;
mysql> alter table user drop address;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num | int(8) | YES | | NULL | |
| stu_id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
| age | int(4) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段的排列位置
alter table table_name modify attribute_1 data_type first|after attribute_2
修改字段到第一个位置:
mysql> alter table user modify name varchar(30) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| num | int(8) | YES | | NULL | |
| stu_id | int(11) | NO | PRI | NULL | |
| stu_sex | tinyint(1) | YES | | NULL | |
| age | int(4) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段到指定位置:
mysql> alter table user modify sex boolean after name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| num | int(8) | YES | | NULL | |
| stu_id | int(11) | NO | PRI | NULL | |
| age | int(4) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
更改表的存储引擎
alter table table_name engine=engine_name;
- Mysql 修改
- 【Mysql】修改mysql密码
- 修改MYSQL的密码
- 修改mysql认证方式
- mysql 密码修改
- 修改MySQL的字符集
- mysql 修改密码方法
- mysql 修改root密码
- 修改mysql root密码
- 如何修改mysql用户密码
- mysql 修改密码
- MySql修改用户密码命令
- 修改mysql登陆密码
- MySQL的编码修改
- 修改mysql字符集
- MySQL修改密码方法
- MySQL修改密码方法
- 修改mysql编码
- C语言的指针、数据、结构体关系总结
- Android Activity生命周期管理
- zoj 2548 Prerequisites?
- 我的python学习之路--如何写python模块
- 经典单片机课程设计题目大全【大学教授珍藏资料】
- Mysql 修改
- session 共享的实现方式(PHP)
- 现实迷途 第三章 一家四口
- IE10最新修改:贯彻Metro风格
- 在EXCEL中把“日期”换算成“星期”的方法
- C#键值对容器
- 【转载】SVN常见问题及相关原因,供各位查阅
- UI设计参考
- Android Notifications通知