MySQL数据表操作

棋大约 7 分钟约 1957 字

拿Windows系统的文件系统进行类比的话，如果说数据库是文件系统中的文件夹，那么数据表就是文件夹中的文件。在MySQL中，数据库里可以存放多张数据表。在对数据表进行操作之前，必须使用USE语句选择数据库，才可以在指定的数据库中对数据表进行操作，如创建数据表，修改表结构，以及重命名、复制或者删除数据表等，否则将无法对数据表进行操作。

创建数据表

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

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名 [{create_difinition,...}][table_options][select_statement];

其中，TEMPORARY代表若使用此关键字，那么将创建一个临时表。

create_definition是表的列属性部分，MySQL要求在创建表时至少包含一列。

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

select_statement是SELECT语句描述部分，用于快速创建表。

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

col_name type [NOT NULL|NULL] [DEFUALT default_value] [AUTO_INCREAMENT] [PRIMARY_KEY] [reference_definition]

其中，col_name代表字段名。

type代表字段类型。

NOT NULL|NULL代表该列是否允许是空值，系统一般允许是空值，所以若不想允许为空值必须使用NOT NULL。

DEFAULT default_value代表默认值。

AUTO_INCREMENT代表是否自动编号，每个表只能有一个该列，且必须被索引。

PRIMARY_KEY表示是否为主键，一个表只能有一个主键。若表中没有主键，而某些应用程序需要主键，MySQL将返回第一个没有任何NULL列的UNIQUE键作为主键。

reference_definition代表为字段添加注释。

创建数据表的简洁形式

CREATE TABLE table_name (列名1 属性, 列名2 属性);

创建第一个数据表

create table tb_first (id int primary key not null auto_increment,name varchar(4));

第一行表示使用db_admin数据库，第二行表示创建数据表，名字叫做tb_first（tb是table）的缩写，其中id是第一个字段名，int是该字段的数据类型，即整型，primary key表示该字段为主键，not null表示该字段不能为NULL值，auto_increment代表该字段可以自动增长，后面的name为第二个字段名，类型为变长字符串，最长为4个字符。

查看表结构

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

SHOW COLUMNS

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

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

在上面我们创建了数据表tb_first数据表，里面有有两个字段：id和name，下面我们尝试读取这几个字段：

show columns from tb_first;

DESCRIBES

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

DESCRIBE 数据表名;

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

DESCRIBE 数据表名 列名;

使用DESC查看数据表结构

DESC tb_first;

修改表结构

修改表结构指增加或者删除字段、修改字段名或字段类型以及修改表名等，可以使用ALTER TABLE语句实现，语法如下：

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

其中，[IGNORE]是可选项，表示如果出现重复的行，则只执行一行，其他的行将被删除。

alter_spec子句表示定义要修改的内容，基本语法如下：

ADD [COLUMN] create_difinition [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] col_name {SET DEFAULT literal|DROP DEFAULT}  -- 修改字段默认值
| CHANGE [COLUMN] old_col_name create_definition  -- 修改字段名/类型
| MODIFY [COLUMN] create_definition  -- 修改子句定义字段
| DROP [COLUMN] col_name  -- 删除某一字段
| DROP PRIMARY KEY  -- 删除主键
| DROP INDEX index_name  -- 删除某一索引
| RENAME [AS] new_tbl_name  -- 更改表名
| COMMENT 'comment'  -- 给表添加注释

其中，create_definition用于定义列的数据类型和属性，与CREATE TABLE语句中的语法相同。

[FIRST|AFTER column_name] 用于指定位于哪个字段的前面还是后面，使用FIRST关键字时表示位于指定字段的前面；使用AFTER关键字时表示位于哪个字段的后面；column_name表示字段名。

[index_name] 是可选项，用于指定索引名。

{index_col_name}用于指定索引列名。

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

old_col_name表示指定要修改的字段名。

new_tbl_name表示指定新的表名。

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

添加新字段和修改字段定义

在MySQL的ALTER TABLE中，可使用ADD [COLUMN] create_definition [FIRST|AFTERT column_name]子句来添加新字段；使用MODIFY [COLUMN] create_definition子句修改已定义的字段的定义，下面通过实例演示如何为一个已有表添加新字段，并修改已有字段的定义。

添加新字段并修改已有字段的定义

首先查看已有的字段以及它们的描述。

DESC tb_first;

下面我们新增加一个字段叫做create_time，意为创建时间，并且将name的类型改为varchar(10)。

alter table tb_first add column (create_time datetime default now() not null),modify column name varchar(10);

最后再查看表的描述。

DESC tb_first;

可以看到name的字段类型已经改为长度为10的变长字符串，且新增添了一个字段create_time。

修改表的名称

使用ALTER语句将tb_first名字修改为table_first。

alter table tb_first rename as table_first;

重命名表

在MySQL中，可以使用RENAME TABLE语句将表重命名，语法如下。

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

如将table_first命名为tb_first。

rename table table_first to tb_first;

复制表

创建表的CREATE TABLE语句还有另外一种语法结构：在一个已经存在的数据表基础上创建该表的备份，也就是复制表，语法如下。

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

现在创建第二个表，名字叫做tb_second，源数据表就是tb_first。

create table if not exists tb_second like tb_first;

删除表

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

DROP TABLE [IF EXISTS] 数据表名;

如我们删除数据表tb_second。

drop table if exists tb_second;

在删除数据表的时候要特别小心，删除数据表之后表中的数据都会被一并删除。

一些操作的语法，可直接套用

为字段添加约束

如为表添加外键约束（外键约束请见《数据完整性约束》一章）。

alter table tb_name add 
(contraint `fri_key_name`  --外键名
    foreign key (col_name) --参照表的字段名
reference tb_name  --被参照表名
    (col_name)  --被参照表的字段名
    [on delete reference_option]
    [on update reference_option]
);

MySQL数据表操作

# 创建数据表

# 创建数据表的简洁形式

# 创建第一个数据表

# 查看表结构

# SHOW COLUMNS

# DESCRIBES

# 使用DESC查看数据表结构

# 修改表结构

# 添加新字段和修改字段定义

# 添加新字段并修改已有字段的定义

# 修改表的名称

# 重命名表

# 复制表

# 删除表

# 一些操作的语法，可直接套用

# 为字段添加约束