MySQL数据表的操作

在对MySQL数据表进行操作之前，必须首先使用USE语句选择数据库，才可以在指定的数据库中对数据表进行操作，如创建数据表、修改表结构、数据表更名或删除数据表等。

1、创建数据表

创建数据表使用CREATE TABLE语句。语法如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名
[(create_definition,…)][table_options][COMMENT = '表的注释']

CREATE TABLE语句的参数说明：

关键字	说明
TEMPORARY	如果使用该关键字，表示创建一个临时表
IF NOT EXISTS	该关键字用于避免表存在时MySQL报告错误
create_definition	这是表的列属性部分。MySQL要求咋创建表时，表要至少包含一列
table_options	表的一些特性参数，其中大多数选项涉及的是表数据如何存储及存储在何处，如ENGINE选项用于定义表的存储引擎。多数情况下，用户不必指定表选项
COMMENT = '表的注释'	为表添加注释

下面介绍列属性create_definition部分，每一列定义的具体格式如下。

col_name type [NOT NULL | NULL][DEFAULT default_value][AUTO_INCREMENT]
[PRIMARY KEY][COMMENT '字段注释']

属性create_definition的参数说明：

参数	说明
col_name	字段名
type	字段类型
NOT NULL | NULL	指出该列是否允许为空值，系统一般默认允许为空值。
DEFAULT default_value	表示默认值
AUTO_INCREMENT	表示是否是自动编号，每个表只能有一个AUTO_INCREMENT列，并且必须被引用
PRIMARY KEY	表示是否为主键。一个表只能有一个PRIMARY KEY。如表中没有一个PRIMARY KEY，而某些应用程序需要PRIMARY KEY，MySQL将返回第一个没有任何NULL列的UNIQUE键，作为PRIMARY KEY
COMMENT '字段注释'	为字段添加注释

以上是创建一个数据表的一些基础知识，它看起来十分复杂，但在实际的应用中使用最基本的格式创建数据表即可，具体格式如下。

CREATE TABLE 数据表名(列名1 属性,列名2 属性...);

示例：使用CREATE TABLE语句创建一个名为tb_admin的数据表。

CREATE TABLE IF NOT EXISTS tb_admin
(
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '编号（自增）',
	user VARCHAR(30) NOT NULL COMMENT '账号',
	password VARCHAR(30) NOT NULL COMMENT '密码',
	create_time DATETIME COMMENT '创建时间'
) COMMENT = '管理员信息表';

2、查看表结构

对于一个创建成功的数据表，可以使用SHOW COLUMNS语句或DESCRIBE语句查看指定数据表的表结构。

2.1 使用SHOW COLUMNS语句

在MySQL中，使用SHOW COLUMNS语句可以查看表结构，SHOW COLUMNS语句的基本语法格式如下：

SHOW  [FULL] COLUMNS  FROM 数据表名 [FROM 数据库名];

或者：

SHOW  [FULL] COLUMNS  FROM 数据表名.数据库名;

示例：使用SHOW COLUMNS语句查看数据表tb_admin的表结构。

SHOW FULL COLUMNS FROM tb_admin;

2.2 使用DESCRIBE语句

在MySQL中，还可以使用DESCRIBE语句查看数据表结构。DESCRIBE语句的基本语法格式如下：

DESCRIBE 数据表名;

其中，DESCRIBE可以简写成DESC。在查看表结构时，也可以只列出某一列的信息。其语法格式如下：

DESCRIBE 数据表名 列名;

示例：使用DESCRIBE语句查看数据表tb_admin的表结构。

-- 查看tb_admin表结构
DESCRIBE tb_admin;

-- 查看user字段信息
DESCRIBE tb_admin user;

3、修改表结构

修改表结构使用 ALTER TABLE语句。修改表结构指增加或者删除字段、修改字段名称或者字段义类型、设置取消主键外键、设置取消索引以及修改表的注释等，语法如下：

ALTER [IGNORE] TABLE 数据表名 alter_spec[,alter_spec]... table_options

参数说明如下：

（1）[IGNORE]：可选项，表示如果出现重复关键的行，则只执行一次，其它重复的行被删除。

（2）数据表名：用于指定要修改的数据表的名称。

（3）alter_spec子句：用于定义要修改的内容。

（4）table_options：用于指定表的一些特性参数，其中大多数选项涉及的是表数据如何存储及存储在何处，如ENGINE选项用于定义表的存储引擎。多数情况下，用户不必指定表选项。

alter_spec子句用于定义要修改的内容，其语法格式如下：

-- 添加新字段
ADD [COLUMN] create_definition [FIRST | AFTER column_name]

-- 添加索引名称
ADD INDEX [index_name](index_col_name,...)

-- 添加主键名称
ADD PRIMARY KEY(index_col_name,...)

-- 添加唯一索引
ADD UNIQUE [index_name](index_col_name,...)

-- 修改字段默认值
ALTER [COLUMN] column_name {SET DEFAULT literal | DROP DEFAULT}

-- 修改字段名/类型
CHANGE [COLUMN] old_col_name create_definition

-- 修改子句定义字段
MODIFY [COLUMN] create_definition

-- 删除字段名称
DROP [COLUMN] column_name

-- 删除主键名称
DROP PRIMARY KEY

-- 删除索引名称
DROP INDEX index_name

-- 更改表名
RENAME [AS] new_tbl_name

上面的语法中，各参数说明如下：

（1）create_definition：用于定义列的数据类型和属性。

（2）[FIRST|AFTER column_name]：用于指定位于哪个字段的前面或者后面，当使用FIRST关键字时（后面不能加字段名），表示位于该表的第一位；使用AFTER 关键字时，表示位于指定字段的后面，其中的column_name表示字段名。

（3）[index_name]：可选项，用于指定索引名。

（4）(index_col_name,...)：用于指定索引列名。

（5）{SET DEFAULT literal | DROP DEFAULT}子句：为字段设置或者删除默认值。其中literal参数为要设置的默认值。

（6）old_col_name：用于指定要修改的字段名称。

（7）new_tbl_name：用于指定新的表名。

说明：ALTER TABLE语句允许指定多个动作，其动作间使用逗号分隔，每个动作表示对表的一个修改。

3.1 新增字段

在MySQL的ALTER TABLE语句中，可以通过使用ADD [COLUMN] create_definition [FIRST | AFTER column_name ]子句来添加新字段。

示例：添加一个新的email字段，该字段在password字段的后面。

ALTER TABLE tb_admin ADD COLUMN email VARCHAR(50) NOT NULL COMMENT '电子邮箱' AFTER password;

3.2 修改字段定义

使用MODIFY [COLUMN] create_definition子句可以修改已定义字段的定义。

示例：修改email字段的类型，将原先的VARCHAR(50)改成VARCHAR(100)。

ALTER TABLE tb_admin MODIFY COLUMN email VARCHAR(100) NOT NULL COMMENT '电子邮箱';

注意：修改时要把原来的其他属性也加上，否则其他属性会被清除了。如上面的示例如果写成：ALTER TABLE tb_admin MODIFY COLUMN email VARCHAR(100); 那么原先设置的非空和注释也被覆盖没了。

3.3 修改字段名

在MySQL的ALTER TABLE语句中，使用CHANGE [COLUMN] old_col_name create_definition子句可以修改字段名或者字段类型。

示例：修改user字段名修改为user_name。

ALTER TABLE tb_admin CHANGE user user_name VARCHAR(30) NOT NULL COMMENT '账号';

3.4 删除字段

在MySQL的ALTER TABLE中，使用DROP [COLUMN] col_name子句可以删除指定字段。

示例：删除email字段。

ALTER TABLE tb_admin DROP COLUMN email;

3.5 修改表名

在MySQL的ALTER TABLE中，使用RENAME [AS] new_tbl_name子句可以修改表名。

示例：修改表名，将td_admin表名修改为td_admin2。

ALTER TABLE tb_admin RENAME AS tb_admin2;

4、重命名表

在MySQL中，重命名数据表可以使用RENAME TABLE语句来实现，语法格式如下：

RENAME TABLE 数据表名1 To 数据表名2

示例：重命名表，将td_admin2表名修改为td_user。

RENAME TABLE tb_admin2 TO tb_user;

5、复制表

创建表的CREATE TABLE命令还有另外一种语法结构，在一张已经存在的数据表的基础上创建一份该表的拷贝，也就是复制表。

这种用法的语法格式如下：

CREATE TABLE [IF NOT EXISTS] 数据表名
	{LIKE 源数据表名 | (LIKE 源数据表名)}

示例：创建一份数据表tb_user的备份tb_userNew（仅表结构）。

CREATE TABLE tb_userNew
LIKE tb_user;

说明：使用该语法复制数据表时，将创建一个与源数据表相同结构的新表，该数据表的列名、数据类型和索引都将被复制，但是表的内容是不会被复制的。因此，新创建的表是一张空表。如果想要同时复制表中的内容，可以通过使用AS(查询表达式)子句来实现。

示例：创建一份数据表tb_user的备份tb_userNew2（表结构+表数据）。

CREATE TABLE tb_userNew2
AS SELECT * FROM tb_user;

6、删除表

删除数据表的操作很简单，同删除数据库的操作类似，使用DROP TABLE语句即可实现，语法格式如下： 

DROP TABLE [IF EXISTS] 数据表名;

示例：删除tb_user表。

DROP TABLE IF EXISTS tb_user;