修改数据表
来源：互联网 发布：淘宝2016年销售总额 编辑：程序博客网 时间：2024/05/20 15:12
表的操作：列的增删 ,约束的增删。
列的增删
- 添加单列
-ALTER TABLE tal_name ADD[COLUMN] col_name column_definition [FIRST | AFTER col_name] - 添加多列：
-ALTER TABLE tal_name ADD[COLUMN] （ col_name column_definition，…) - 删除列：
-ALTER TABLE tb1_name DROP[COLUMN] col_name; //删除一列
-ALTER TABLE tb1_name DROP[COLUMN] col_name， DROP[COLUMN] col_name； //删除多列
也可以一行命令添加删除 要用逗号分隔。如上
约束的增删， constraint
- 添加主键约束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)
删除主键约束：ALTER TABLE tbl_name DROP PRIMARY KEY
删除唯一约束： ALTER TABLE tbl_name DROP{INDEX|KEY}index_name
添加外键约束：ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces (id);
删除外键约束：ALTER TABLE tbl_name DROP FOREUGN KEY fk_symbob
添加/删除默认约束:ALTER TABLE tbl_name ALTER [COLUMN] col_name{SEL DEFAULT iteral | DROP DEFAULT}
修改列
MODIFY ,CHANGE
修改列定义
如果字段类型 位置上要修改 可以修改列定义
1. ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
2. 修改列名称
- ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
修改数据表名
- ALTER TABLE tbl_name RENAME [TO| AS] new_tbl_name
- RENAME TABLE tbl_name TO new_tbl_name
增加列
mysql> SHOW COLUMNS FROM user1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | |+----------+----------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)无指定添加位置
——添加的列在所有列的后面
mysql> ALTER TABLE user1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;Query OK, 0 rows affected (0.51 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FROM user1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | tinyint(3) unsigned | NO | | 10 | |+----------+----------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)mysql>指定添加位
mysql> ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;Query OK, 0 rows affected (0.42 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FROM user1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || password | varchar(32) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | tinyint(3) unsigned | NO | | 10 | |+----------+----------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql>删除列
//删除单列 mysql> ALTER TABLE user1 DROP password;Query OK, 0 rows affected (0.49 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FROM user1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | tinyint(3) unsigned | NO | | 10 | |+----------+----------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)//删除多列mysql> ALTER TABLE user1 DROP username，DROP age;添加主键约束
先创建一个表
mysql> CREATE TABLE user2( -> username VARCHAR(10) NOT NULL, -> pid SMALLINT UNSIGNED -> );Query OK, 0 rows affected (0.22 sec)mysql> SHOW CREATE TABLE user2;+-------+------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+------------------------------------------------------------------------------------------------------------------------------------------+| user2 | CREATE TABLE `user2` ( `username` varchar(10) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)添加约束
mysql> ALTER TABLE user2 ADD id SMALLINT UNSIGNED;Query OK, 0 rows affected (0.29 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FROM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | | NULL | || id | smallint(5) unsigned | YES | | NULL | |+----------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)//添加主键 并给主键命名mysql> ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY(id);Query OK, 0 rows affected (0.30 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FROM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | | NULL | || id | smallint(5) unsigned | NO | PRI | NULL | |+----------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)//添加约束mysql> ALTER TABLE user2 ADD UNIQUE (username);Query OK, 0 rows affected (0.16 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FROM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | UNI | NULL | || pid | smallint(5) unsigned | YES | | NULL | || id | smallint(5) unsigned | NO | PRI | NULL | |+----------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)添加外键约束
mysql> SHOW COLUMNS FROM provinces;+-------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || pname | varchar(20) | NO | | NULL | |+-------+----------------------+------+-----+---------+----------------+2 rows in set (0.01 sec)mysql> ALTER TABLE user2 ADD FOREIGN KEY(pid) REFERENCES provinces(id);Query OK, 0 rows affected (0.69 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW CREATE TABLE user2;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user2 | CREATE TABLE `user2` ( `username` varchar(10) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `pid` (`pid`), CONSTRAINT `user2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name{SEL DEFAULT iteral | DROP DEFAULT}
mysql> ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL;Query OK, 0 rows affected (0.36 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FORM user2;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORM user2' at line 1mysql> SHOW COLUMNS FroM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | UNI | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | PRI | NULL | || age | tinyint(3) unsigned | NO | | NULL | |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)//增加默认值mysql> ALTER TABLE user2 ALTER age SET DEFAULT 15;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FroM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | UNI | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | PRI | NULL | || age | tinyint(3) unsigned | NO | | 15 | |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)//删除默认值mysql> ALTER TABLE user2 ALTER age DROP DEFAULT;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FroM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | UNI | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | PRI | NULL | || age | tinyint(3) unsigned | NO | | NULL | |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)删除唯一约束
ALTER TABLE tbl_name DROP{INDEX|KEY}index_name
mysql> SHOW COLUMNS FroM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | PRI | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | | NULL | || age | tinyint(3) unsigned | NO | | NULL | |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)//查看约束的名字mysql> SHOW INDEXES FROM user2\G;*************************** 1. row *************************** Table: user2 Non_unique: 0 Key_name: username Seq_in_index: 1 Column_name: username Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment:Index_comment:*************************** 2. row *************************** Table: user2 Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment:Index_comment:2 rows in set (0.00 sec)ERROR:No query specifiedmysql> ALTER TABLE user2 DROP index username;Query OK, 0 rows affected (0.43 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW COLUMNS FroM user2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | | NULL | || age | tinyint(3) unsigned | NO | | NULL | |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql>删除外键约束
ALTER TABLE tbl_name DROP FOREIGNKEY fk_symbob
mysql> SHOW CREATE TABLE user2;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user2 | CREATE TABLE `user2` ( `username` varchar(10) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `id` smallint(5) unsigned NOT NULL, `age` tinyint(3) unsigned NOT NULL, KEY `pid` (`pid`), CONSTRAINT `user2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW CREATE TABLE user2;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user2 | CREATE TABLE `user2` ( `username` varchar(10) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `id` smallint(5) unsigned NOT NULL, `age` tinyint(3) unsigned NOT NULL, KEY `pid` (`pid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
mysql> SHOW CREATE TABLE user2;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user2 | CREATE TABLE `user2` ( `username` varchar(10) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `id` smallint(5) unsigned NOT NULL, `age` tinyint(3) unsigned NOT NULL, KEY `pid` (`pid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)//移动位置mysql> ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;Query OK, 0 rows affected (0.34 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW CREATE TABLE user2;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user2 | CREATE TABLE `user2` ( `id` smallint(5) unsigned NOT NULL, `username` varchar(10) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `age` tinyint(3) unsigned NOT NULL, KEY `pid` (`pid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)//修改类型 由大类型改为小类型可能会造成数据的丢失mysql> ALTER TABLE user2 MODIFY id TINYINT UNSIGNED NOT NULL FIRST;Query OK, 0 rows affected (0.45 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW CREATE TABLE user2;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user2 | CREATE TABLE `user2` ( `id` tinyint(3) unsigned NOT NULL, `username` varchar(10) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `age` tinyint(3) unsigned NOT NULL, KEY `pid` (`pid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)修改数据表名
mysql> ALTER TABLE user2 RENAME user3;Query OK, 0 rows affected (0.08 sec)mysql> SHOW TABLES -> ;+--------------+| Tables_in_ti |+--------------+| provinces || tab1 || tab2 || tab3 || tab4 || tab5 || tab6 || user1 || user3 || users |+--------------+10 rows in set (0.00 sec)mysql> RENAME TABLE user3 to user2;Query OK, 0 rows affected (0.12 sec)mysql> SHOW TABLES;+--------------+| Tables_in_ti |+--------------+| provinces || tab1 || tab2 || tab3 || tab4 || tab5 || tab6 || user1 || user2 || users |+--------------+10 rows in set (0.00 sec) 0 0
- 修改数据表
- 修改数据表
- 修改数据表
- 修改数据表
- 修改数据表
- 创建数据表和修改数据表
- 修改数据表名
- sql2008无法修改数据表
- 创建修改删除数据表
- 修改mysql数据表
- 15、MySQL-修改数据表
- oracle 修改数据表字段
- 约束及修改数据表
- MySQL修改数据表
- MySql修改数据表
- 数据表的修改
- MySQL修改数据表
- MySQL 修改数据表
- MySQL之——Replication的容量、故障排错以及多线程方式传输二进制日志
- 使用RecyclerView时item过多滑动报错
- requirejs和seajs那些蛋疼事儿
- android开发如何定制状态栏的颜色，同时支持4.4(KIKAT,api19)和19以上系统。
- linux下redis安装以及处理一些常见问题
- 修改数据表
- Python 包管理工具解惑
- 谈到电影，我们收获了什么
- PHP.3-DIV+CSS标准网页布局准备工作（上）
- STM32学习笔记（1）如何移植FreeRTOS
- 自定义侧滑菜单SlidingMenu
- MySQL添加远程访问权限
- Android——View的工作原理（三）
- Html5利用AppCache和LocalStorage实现缓存h5页面数据
