ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【MySql】mysql 表的常规管理

【MySql】mysql 表的常规管理

原创 Linux操作系统 作者:杨奇龙 时间:2012-02-17 07:10:54 0 删除 编辑
记录一些简单的表的管理知识,方便使用!
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)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-716632/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7530164