查看并修改表结构

查看表结构 

数据表创建完成后，可以通过查看表结构或者建表语句，来确认表的定义是否正确。 

1、 查看表的基本结构 

查看表结构的关键字为DESCRIBE，语法形式如下： 

DESCRIBE table_name; 

执行DESCRIBE语句，查看goods表结构，效果如下所示。 

mysql> DESCRIBE goods; 

注意：DESCRIBE可以简写为DESC 

2、查看建表语句 

使用SHOW CREATE TABLE语句可以查看表的建表语句，语法形式如下： 

SHOW CREATE TABLE table_name \G 

执行SHOW CREATE TABLE语句，查看goods表的建表语句，效果如下所示。 

mysql> SHOW CREATE TABLE goods \G 

修改表 

MySQL提供了ALTER关键字来修改表结构。 

1、 修改表名 

修改数据表名称的关键字为RENAME，语法形式如下： 

ALTER TABLE old_table_name RENAME new_table_name; 

执行SQL语句，将goods表的名称改为tb_goods。 

mysql> ALTER TABLE goods RENAME tb_goods; 

Query OK, 0 rows affected (0.01 sec) 

mysql> SHOW TABLES; 

+------------------+ 

| Tables_in_galaxy | 

+------------------+ 

| orders           | 

| tb_goods         | 

+------------------+ 

2 rows in set (0.00 sec) 

2、修改字段数据类型 

修改字段数据类型的关键字为MODIFY，语法形式如下： 

ALTER TABLE table_name MODIFY col_name new_data_type; 

执行SQL语句，将tb_goods表中type字段的数据类型修改为CHAR(30)。 

mysql> ALTER TABLE tb_goods MODIFY type CHAR(30); 

Query OK, 0 rows affected (0.02 sec) 

Records: 0  Duplicates: 0  Warnings: 0 

查看tb_goods表结构 

mysql> DESC tb_goods; 

3、 修改字段名 

修改数据表字段名称的关键字为CHANGE，语法形式如下： 

ALTER TABLE table_name CHANGE old_col_name new_col_name data_type; 

执行SQL语句，将tb_goods表中name字段的名称改为g_name。 

mysql> ALTER TABLE tb_goods CHANGE name g_name VARCHAR(30); 

Query OK, 0 rows affected (0.00 sec) 

Records: 0  Duplicates: 0  Warnings: 0 

使用上述语句也可以同时修改数据类型。例如，将g_name字段名称修改为name，数据类型修改为CHAR(30)，效果如下。 

mysql> ALTER TABLE tb_goods CHANGE g_name name CHAR(30); 

Query OK, 0 rows affected (0.02 sec) 

Records: 0  Duplicates: 0  Warnings: 0 

4、 添加字段 

常见添加字段的操作一般分为三种情况：在表的最后一列，在表的第一列或者在指定列之后。 

1）在表的最后一列添加字段 

添加字段的关键字为ADD，语法形式如下： 

ALTER TABLE table_name ADD col_name data_type; 

执行SQL语句，在tb_goods表中添加picture字段。 

mysql> ALTER TABLE tb_goods ADD picture VARCHAR(255); 

Query OK, 0 rows affected (0.01 sec) 

Records: 0  Duplicates: 0  Warnings: 0 

2）在表的第一列添加字段 

ALTER TABLE table_name ADD col_name data_type FIRST; 

执行以下SQL语句，在tb_goods表中第一列添加state字段。 

mysql> ALTER TABLE tb_goods ADD state TINYINT(4) FIRST; 

Query OK, 0 rows affected, 1 warning (0.02 sec) 

Records: 0  Duplicates: 0  Warnings: 1 

3）在表的指定列之后添加字段 

ALTER TABLE table_name ADD col_name1 data_type AFTER col_name2; 

执行SQL语句，在tb_goods表中num字段之后添加intro字段。 

mysql> ALTER TABLE tb_goods ADD intro TEXT AFTER num; 

Query OK, 0 rows affected (0.00 sec) 

Records: 0  Duplicates: 0  Warnings: 0 

5、 删除字段 

删除数据表字段的关键字为DROP，语法形式如下： 

ALTER TABLE table_name DROP col_name; 

执行以下SQL语句，将tb_goods表中的picture字段删除。 

mysql> ALTER TABLE tb_goods DROP picture; 

Query OK, 0 rows affected (0.01 sec) 

Records: 0  Duplicates: 0  Warnings: 0 

6、 修改字段顺序 

修改字段顺序的关键字为MODIFY，语法形式如下： 

ALTER TABLE table_name MODIFY col_name data_type FIRST | AFTER col_name2; 

执行以下SQL语句，将tb_goods表的state字段位置修改为id字段之后。 

mysql> ALTER TABLE tb_goods MODIFY state TINYINT(4) AFTER id; 

Query OK, 0 rows affected, 1 warning (0.02 sec) 

Records: 0  Duplicates: 0  Warnings: 1 

7、修改存储引擎 

用户可以在创建表时设置存储引擎，也可以在表创建完成之后修改表的存储引擎，语法形式如下： 

ALTER TABLE table_name ENGINE=e_name; 

在修改存储引擎之前，往往需要首先查看表当前的存储引擎，语法形式如下： 

SHOW CREATE TABLE table_name \G 

mysql> SHOW CREATE TABLE tb_goods \G 

*************************** 1. row *************************** 

       Table: tb_goods 

Create Table: CREATE TABLE `tb_goods` ( 

  `id` int NOT NULL AUTO_INCREMENT, 

  `state` tinyint DEFAULT NULL, 

  `type` char(30) DEFAULT NULL, 

  `name` char(30) DEFAULT NULL, 

  `price` decimal(7,2) unsigned DEFAULT NULL, 

  `num` int DEFAULT '0', 

  `intro` text, 

  `add_time` datetime DEFAULT NULL, 

  PRIMARY KEY (`id`), 

  UNIQUE KEY `name` (`name`) 

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

1 row in set (0.00 sec) 

首先执行SQL语句，查看category表的存储引擎，结果如下： 

ALTER TABLE category ENGINE=InnoDB; 

mysql> ALTER TABLE category ENGINE=InnoDB; 

Query OK, 0 rows affected (0.01 sec) 

Records: 0  Duplicates: 0  Warnings: 0 

删除表 

删除数据表会将表的定义和表中的数据全部删除，因此，用户最好反复确认后再执行此操作。 

1、 删除没有被关联的表 

使用DROP关键字可以一次性删除一个或多个没有被其他表关联的表，语法形式如下： 

DROP TABLE [IF EXISTS] table_name1,table_name2,……,table_namen; 

提示：如果删除的数据表不存在，系统会提示错误信息并中断执行，加上“IF EXISTS”参数后，系统会在执行删除命令之前判断表是否存在，如果表不存在，命令仍可以顺利执行，但系统会提示警告。 

执行SQL语句，删除category表和不存在的tb_good表，并查看数据库中的所有表。 

mysql> DROP TABLE IF EXISTS category; 

2、 删除被其他表关联的主表 

如果数据表之间存在外键关联，那么直接删除主表，系统会提示错误信息，这种情况下，可以先删除与它关联的从表，再删除主表。但有时需要保留从表中的数据，此时需解除主表和从表之间的关联，即删除从表中的外键约束。 

删除外键的语法形式如下： 

ALTER TABLE table_name DROP FOREIGN KEY key_name; 

之前创建的tb_goods表和order表存在外键关联，如果直接删除tb_goods表，系统会提示错误，如下所示。 

mysql> DROP TABLE tb_goods; 

ERROR 3730 (HY000): Cannot drop table 'tb_goods' referenced by a foreign key constraint 'goo_ord' on table 'orders'. 

可以执行SQL语句先删除orders表中的外键，然后删除tb_goods表，如下所示。 

ALTER TABLE orders DROP FOREIGN KEY goo_ord; 

DROP TABLE tb_goods; 

DROP TABLE tb_orders;