数据表简单操作

查看表基本结构：

mysql> desc emp1;+----------+-------------+------+-----+-------- +-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| eid      | varchar(30) | NO   | PRI |         |       || username | varchar(15) | NO   |     | NULL    |       || uid      | varchar(20) | YES  |     | NULL    |       || salary   | float       | YES  |     | NULL    |       || birthday | datetime    | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+5 rows in set (0.29 sec)

Field :字段名

Type:字段类型

Null:表示该列是否可以存储Null值

Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分;MUL表示在列中某个给定值允许出现多次。

Default:表示该列是否有默认值，如果有的话值为多少。

Extra:表示可以获取的与给定列有关的附加信息。例如AUTO_INCREMENT等。

查看表详细结构：

mysql> show create TABLE emp1\G;*************************** 1. row ***************************   Table: emp1Create Table: CREATE TABLE `emp1` ( `eid` varchar(30) NOT NULL DEFAULT '', `username` varchar(15) NOT NULL, `uid` varchar(20) DEFAULT NULL, `salary` float DEFAULT NULL, `birthday` datetime DEFAULT NULL, PRIMARY KEY (`eid`)) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.03 sec)ERROR:No query specified

修改表名：

mysql> alter table emp1 rename emp;Query OK, 0 rows affected (0.12 sec)

修改表字段数据类型

mysql> alter table emp modify  salary double;Query OK, 3 rows affected (0.34 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| eid      | varchar(30) | NO   | PRI |         |       || username | varchar(15) | NO   |     | NULL    |       || uid      | varchar(20) | YES  |     | NULL    |       || salary   | double      | YES  |     | NULL    |       || birthday | datetime    | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)

修改字段名

mysql> alter table emp change eid id varchar(20);Query OK, 3 rows affected (0.14 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | varchar(20) | NO   | PRI |         |       || username | varchar(15) | NO   |     | NULL    |       || uid      | varchar(20) | YES  |     | NULL    |       || salary   | double      | YES  |     | NULL    |       || birthday | datetime    | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+5 rows in set (0.02 sec)

添加字段：

mysql> alter table emp add sex set('男','女') not null;Query OK, 3 rows affected (0.16 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> alter table emp add s set('男','女') not null  after sex;Query OK, 3 rows affected (0.15 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+----------------+------+-----+---------+-------+| Field    | Type           | Null | Key | Default | Extra |+----------+----------------+------+-----+---------+-------+| id       | varchar(20)    | NO   | PRI |          || username | varchar(15)    | NO   |     | NULL    |       || uid      | varchar(20)    | YES  |     | NULL    |       || salary   | double         | YES  |     | NULL    |       || birthday | datetime       | YES  |     | NULL    |       || sex      | set('男','女') | NO   |     | NULL    |       || s        | set('男','女') | NO   |     | NULL    |       |+----------+----------------+------+-----+---------+-------+7 rows in set (0.02 sec)

删除字段：

mysql> alter table emp drop uid,sex,s;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'sex,s' at line 1mysql> alter table emp drop uid;Query OK, 3 rows affected (0.18 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> alter table emp drop sex;Query OK, 3 rows affected (0.18 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> alter table emp drop s;Query OK, 3 rows affected (0.15 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | varchar(20) | NO   | PRI |         |       || username | varchar(15) | NO   |     | NULL    |       || salary   | double      | YES  |     | NULL    |       || birthday | datetime    | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.02 sec)

修改字段排列位置：

mysql> alter table emp modify id varchar(20) after username;Query OK, 3 rows affected (0.20 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| username | varchar(15) | NO   |     | NULL    |       || id       | varchar(20) | NO   | PRI |         |       || salary   | double      | YES  |     | NULL    |       || birthday | datetime    | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)

修改表的存储引擎:

mysql> alter table emp engine=InnoDB;Query OK, 3 rows affected (0.82 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> show create table  emp\G;*************************** 1. row ***************************   Table: empCreate Table: CREATE TABLE `emp` (  `username` varchar(15) NOT NULL,  `id` varchar(20) NOT NULL DEFAULT '',  `salary` double DEFAULT NULL,  `birthday` datetime DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.06 sec)ERROR:No query specified

查看表默认的存储引擎：

mysql> show variables like "storage_engine";+----------------+--------+| Variable_name  | Value  |+----------------+--------+| storage_engine | InnoDB |+----------------+--------+1 row in set (0.13 sec)