跳至主要內容

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]
);
上次编辑于:
贡献者: 棋.