查看并修改表结构

查看表结构

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

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;