对Mysql数据表本身进行操作
创建实验环境
mysql> create database test_db; Query OK, 1 row affected (0.00 sec) mysql> use test_db; Database changed mysql> create table test_table(id int(10),name varchar(20),age int); Query OK, 0 rows affected (0.04 sec)
(1).查看表结构
查看表结构有四种方法，如果查找的不是当前数据库里的表，一定要使用[数据库名].[表名]的格式使用。最常用的一般是desc [表名]。
mysql> desc test_table; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> explain test_table; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> show columns from test_table; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> show fields from test_table; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
(2).修改表名
alter table [旧的表名] rename [新的表名];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> alter table test_table rename table_newname; Query OK, 0 rows affected (0.04 sec) mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | table_newname | +-------------------+ 1 row in set (0.00 sec)
(3).只修改表的字段类型
alter tabel [表名] modify [字段名] [修改后的字段类型];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
修改已有数据的表的字段类型，请谨慎。
mysql> desc table_newname; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table table_newname modify name char(22); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | char(22) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
(4).修改表的字段名和字段类型
alter table [表名] change [旧的字段名] [新的字段名] [新的字段类型];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
修改已有数据的表的字段类型，请谨慎。如果不想修改字段类型，请保持类型的一致。
mysql> desc table_newname; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | char(22) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table table_newname change name newname char(30); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | newname | char(30) | YES | | NULL | | | age | int(11) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
(5).添加字段
alter table [表名] add [字段名] [字段类型];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> desc table_newname; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | newname | char(30) | YES | | NULL | | | age | int(11) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table table_newname add job char(40); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc table_newname; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | newname | char(30) | YES | | NULL | | | age | int(11) | YES | | NULL | | | job | char(40) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
(6).在表的指定位置添加字段
字段添加到第一位：alter table [表名] add [字段名] [字段类型] first;
字段添加到某一位的后面：alter table [表名] add [字段名] [字段类型] after [字段名];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> desc table_newname;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table table_newname add sex enum('M','W') first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table table_newname add address varchar(40) after newname;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc table_newname;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| sex | enum('M','W') | YES | | NULL | |
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
(7).删除表的指定字段
alter table [表名] drop [字段名];
如果不在当前数据库，需要使用[数据库名].[表名]代替单一的[表名]。
mysql> desc table_newname;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| sex | enum('M','W') | YES | | NULL | |
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table table_newname drop sex;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc table_newname;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| newname | char(30) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| job | char(40) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
如果还有其他的操作，以后再加
