mysql 数据表的批改_MySQL

mysql 数据表的修改

添加单列?

? ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

mysql> show columns from user;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(20) | YES  |     | NULL    |                || pid      | smallint(6) | YES  | MUL | NULL    |                |+----------+-------------+------+-----+---------+----------------+3 rows in setmysql> describe user;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(20) | YES  |     | NULL    |                || pid      | smallint(6) | YES  | MUL | NULL    |                |+----------+-------------+------+-----+---------+----------------+3 rows in set

?添加一列

mysql> 	alter table user add userno varchar(20) not null;Database changedmysql> describe user;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(20) | YES  |     | NULL    |                || pid      | smallint(6) | YES  | MUL | NULL    |                || userno   | varchar(20) | NO   |     |         |                |+----------+-------------+------+-----+---------+----------------+4 rows in set

? age 到所有列前面

mysql> alter table user add age varchar(2) first;Database changedRecords: 1  Duplicates: 0  Warnings: 0mysql> describe user;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| age      | varchar(2)  | YES  |     | NULL    |                || id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(20) | YES  |     | NULL    |                || pid      | smallint(6) | YES  | MUL | NULL    |                || userno   | varchar(20) | NO   |     |         |                |+----------+-------------+------+-----+---------+----------------+5 rows in set

? sex 到username后面

mysql> alter table user add column sex char(1) after username;Database changedRecords: 1  Duplicates: 0  Warnings: 0mysql> describe user;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| age      | varchar(2)  | YES  |     | NULL    |                || id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(20) | YES  |     | NULL    |                || sex      | char(1)     | YES  |     | NULL    |                || pid      | smallint(6) | YES  | MUL | NULL    |                || userno   | varchar(20) | NO   |     |         |                |+----------+-------------+------+-----+---------+----------------+6 rows in set

?? 添加多列缺点就是只能放在最后面，不能排在某字段的前面或后面

mysql> alter table user add column(a1 varchar(1),a2 varchar(2));Database changedRecords: 1  Duplicates: 0  Warnings: 0mysql> describe user;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| age      | varchar(2)  | YES  |     | NULL    |                || id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(20) | YES  |     | NULL    |                || sex      | char(1)     | YES  |     | NULL    |                || pid      | smallint(6) | YES  | MUL | NULL    |                || userno   | varchar(20) | NO   |     |         |                || a1       | varchar(1)  | YES  |     | NULL    |                || a2       | varchar(2)  | YES  |     | NULL    |                |+----------+-------------+------+-----+---------+----------------+

?删除一列

mysql> alter table user drop a1;Database changedRecords: 1  Duplicates: 0  Warnings: 0

?删除多列用逗号隔开

mysql> alter table user drop sex,drop age;Database changed

修改列

mysql> alter table user modify id int after pid;Database changedRecords: 0  Duplicates: 0  Warnings: 0mysql> show columns from user;+----------+---------------------+------+-----+---------+-------+| Field    | Type                | Null | Key | Default | Extra |+----------+---------------------+------+-----+---------+-------+| username | varchar(20)         | YES  |     | NULL    |       || pid      | smallint(6)         | YES  |     | NULL    |       || id       | int(11)             | YES  |     | NULL    |       || age      | tinyint(3) unsigned | NO   |     |         |       |+----------+---------------------+------+-----+---------+-------+4 rows in set

??修改表名用rename?

? 法一?

mysql> alter table user rename deploy;Database changedmysql> show tables;+--------------+| Tables_in_t1 |+--------------+| deploy       || dept         || pet          |+--------------+3 rows in set

?用 rename to

mysql> rename table deploy to user;Database changedmysql> show tables;+--------------+| Tables_in_t1 |+--------------+| dept         || pet          || user         |+--------------+3 rows in set

添加主键约束

mysql> create table user(    -> id smallInt,    -> username varchar(20));Database changedmysql> alter table user add constraint pk_user_id primary key(id);Database changedRecords: 0  Duplicates: 0  Warnings: 0mysql> show create  table user;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE `user` (  `id` smallint(6) NOT NULL default '0',  `username` varchar(20) default NULL,  PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+

? 添加唯一约束

mysql> alter table user add unique(username);Database changedRecords: 0  Duplicates: 0  Warnings: 0mysql> describe user;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | smallint(6) | NO   | PRI | 0       |       || username | varchar(20) | YES  | UNI | NULL    |       |+----------+-------------+------+-----+---------+-------+2 rows in set

?添加外键约束

mysql> alter table user add foreign key(pid) references dept(id);Database changedRecords: 0  Duplicates: 0  Warnings: 0mysql> show columns from user;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | smallint(6) | NO   | PRI | 0       |       || username | varchar(20) | YES  | UNI | NULL    |       || pid      | smallint(6) | YES  | MUL | NULL    |       |+----------+-------------+------+-----+---------+-------+3 rows in set

?添加默认约束

mysql> alter table user alter age set default 10;Database changed

?删除默认约束

mysql> alter table user alter age drop default;Database changed

?删除主键约束

mysql> alter table user drop primary key;Database changedRecords: 0  Duplicates: 0  Warnings: 0

?删除唯一约束

mysql> show index from user;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| user  |          0 | username |            1 | username    | A         |           0 | NULL     | NULL   | YES  | BTREE      |         || user  |          1 | pid      |            1 | pid         | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+2 rows in setmysql> alter table user drop index username;Database changed

?删除外键约束

mysql> show create table user;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                                                             |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE `user` (  `id` smallint(6) NOT NULL default '0',  `username` varchar(20) default NULL,  `pid` smallint(6) default NULL,  `age` tinyint(3) unsigned NOT NULL,  KEY `pid` (`pid`),  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `dept` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in setmysql> alter table user drop foreign key user_ibfk_1;Database changedRecords: 0  Duplicates: 0  Warnings: 0

?生成外键的时候会有索引，所以这里要把索引也删除掉

mysql> show create table user;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                     |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE `user` (  `id` smallint(6) NOT NULL default '0',  `username` varchar(20) default NULL,  `pid` smallint(6) default NULL,  `age` tinyint(3) unsigned NOT NULL,  KEY `pid` (`pid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in setmysql> alter table user drop index pid;Database changedRecords: 0  Duplicates: 0  Warnings: 0mysql> show columns from user;+----------+---------------------+------+-----+---------+-------+| Field    | Type                | Null | Key | Default | Extra |+----------+---------------------+------+-----+---------+-------+| id       | smallint(6)         | NO   |     | 0       |       || username | varchar(20)         | YES  |     | NULL    |       || pid      | smallint(6)         | YES  |     | NULL    |       || age      | tinyint(3) unsigned | NO   |     |         |       |+----------+---------------------+------+-----+---------+-------+4 rows in setmysql> show create table user;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE `user` (  `id` smallint(6) NOT NULL default '0',  `username` varchar(20) default NULL,  `pid` smallint(6) default NULL,  `age` tinyint(3) unsigned NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set