msyql修改表
创建表
create table if not exists `category`
(
`category_id` int unsigned primary key not null,
`category_name` char(10) unique
)engine=innodb charset=utf8;
修改表可以用如下语句
alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
alter table <表名> modify <字段名> <新数据类型>;
mysql> desc `category`;
+---------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_name | char(20) | YES | UNI | NULL | |
+---------------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table `category` change `category_name` `category_name1` char(30);
Query OK, 1 row affected (1.34 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc `category`;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_name1 | char(30) | YES | UNI | NULL | |
+----------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table `category` modify `category_name1` char(10);
Query OK, 1 row affected (1.21 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc `category`;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_name1 | char(10) | YES | UNI | NULL | |
+----------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
添加字段可以用如下语句
alter table <表名> add <新字段名> <新数据类型> [约束条件] [first|after已存在的字段名];
如下
mysql> desc `category`;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_name1 | char(10) | YES | UNI | NULL | |
+----------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table category add `category_description` char(20) after `category_id`;
Query OK, 0 rows affected (0.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_description | char(20) | YES | | NULL | |
| category_name1 | char(10) | YES | UNI | NULL | |
+----------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除字段可以用如下语句
alter table <表名> drop <字段名>;
如下
mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_description | char(20) | YES | | NULL | |
| category_name1 | char(10) | YES | UNI | NULL | |
+----------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table `category` drop `category_name1`;
Query OK, 0 rows affected (0.69 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_description | char(20) | YES | | NULL | |
+----------------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
修改字段排列位置可以用如下语句
alter table <表名> modify <字段1> <字段类型> first|after <字段2>;
如下
mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id | int(10) unsigned | NO | PRI | NULL | |
| category_description | char(20) | YES | | NULL | |
+----------------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table `category` modify `category_description` char(20) first;
Query OK, 0 rows affected (0.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_description | char(20) | YES | | NULL | |
| category_id | int(10) unsigned | NO | PRI | NULL | |
+----------------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
修改表的存储引擎
alter table <表名> engine=<新的存储引擎>;
如下
mysql> show create table `category`;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| category | CREATE TABLE `category` (
`category_description` char(20) DEFAULT NULL,
`category_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table `category` engine=myisam;
Query OK, 1 row affected (0.49 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table `category`;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| category | CREATE TABLE `category` (
`category_description` char(20) DEFAULT NULL,
`category_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除表的外键
alter table <表名> drop foreign key <外键名>;
如下
mysql> show create table `book`\G;
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` (
`book_id` int(10) unsigned NOT NULL,
`book_name` char(10) DEFAULT NULL,
`category_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`book_id`),
KEY `fk_1` (`category_id`),
CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table `book` drop foreign key `fk_1`;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table `book`\G;
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` (
`book_id` int(10) unsigned NOT NULL,
`book_name` char(10) DEFAULT NULL,
`category_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`book_id`),
KEY `fk_1` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified