记录一些简单的表的管理知识,方便使用！
mysql> desc yql9;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| val | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1 添加字段 ALTER TABLE tabname ADD field_name field_type;
mysql> alter table yql9 add new_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_id);
ERROR 1067 (42000): Invalid default value for 'new_id'
mysql> alter table yql9 add new_id int(5) unsigned not null auto_increment ,add primary key (new_id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table yql9 add gmt_created timestamp;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yql9;
+-------------+-----------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| val | char(5) | YES | | NULL | |
| new_id | int(5) unsigned | NO | PRI | NULL | auto_increment |
| gmt_created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.01 sec)
2 删除字段 alter table tabname drop field_name;
mysql> alter table yql9 drop column new_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yql9;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| val | char(5) | YES | | NULL | |
| gmt_created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
mysql> alter table yangql9 drop v;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yangql9;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | 0 | |
| gmt_created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)
3 修改字段的数据类型 alter table tabname change old_field_name new_field_name field_type;
mysql> alter table yql9 change val v integer;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yql9;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| v | int(11) | YES | | NULL | |
| gmt_created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
mysql> alter table yql9 change v v tinyint not null default 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yql9;
+-------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| v | tinyint(4) | NO | | 0 | |
| gmt_created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
4 重命名表
mysql> alter table yql9 rename yangql9;
Query OK, 0 rows affected (0.01 sec)
5 添加索引 alter table tabname add index /create index idxname on tabname(column_name);
mysql> alter table yangql9 add index id_idx (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yangql9;
+-------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | MUL | NULL | |
| v | tinyint(4) | NO | | 0 | |
| gmt_created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
6 添加主键
mysql> alter table yangql9 add primary key(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yangql9;
+-------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | 0 | |
| v | tinyint(4) | NO | | 0 | |
| gmt_created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)