数据库和表

数据库、表和字段是数据库中最常用的对象，本文将介绍对象的命名，以及它们的创建、删除和修改，并学习如何通过information_schema数据库查询对象信息。

对象命名

MySQL中数据库和表的存储基于文件系统，如每个数据库分别保存在同名文件夹中，数据表分别保存为独立的文件。Windows操作系统中，文件夹和文件的名称并不区分字母大小写，但在Linux等操作系统中，文件夹和文件的命名则区分字母大小写。工作中，MySQL数据库和表的命名需要有一些约定，如本合集文章约定，对于自定义的数据库、表、字段、视图等MySQL对象的命名采用小字英文字母、数字和下画线的组合，如cdb_demo数据库、t1表、user_main表等。

对于已存在的对象（如数据库、表、字段、视图等），如果名称里可能包含特殊字符，则可以在引用对象时使用一对反单引号（`），如`cdb_demo`.`t1`。如下面的两段代码都可以查询cdb_demo数据库t1表中的所有记录。

MySQL

use `cdb_demo`;
select * from `t1`;

或

MySQL

select * from `cdb_demo`.`t1`;

下面的代码将只显示t1表中recid、f1、f2字段的数据。

MySQL

use `cdb_demo`;
select `recid`,`f1`,`f2` from `t1`;

数据库

查看已存在的数据库时，可以使用show语句，如下面的代码可以显示所有数据库名称。

MySQL

show databases;

需要查找特定名称的数据库时，可以使用like或where关键字指定条件。如下面的代码会查找名称以cdb开始的数据库。

MySQL

show databases like 'cdb%';

在我们的测试环境中，本例会显示cdb_demo。代码中，like关键字后的模式使用了'cdb%'，其中cdb表示查询的内容，而%符号表示0或多个字符，即匹配了名称以cdb开始的数据库。

创建数据库使用create database语句，基本格式如下：

MySQL

create database if not exists <数据库名称> default charset=utf8mb4;

这里，if not exists为可选，表示当指定名称的数据库不存在时执行创建操作，省略时，如果数据库已存在则会出错。default charset选项也为可选，用于指定数据库的默认字符集，示例中使用了utf8mb4字符集。

删除数据库使用语句“drop database <数据库名称>”。请注意，数据库删除后，其中的数据会全部丢失，请谨慎操作！！！

表

查看某个数据库中已存在的表时可以使用show tables语句，如下面的代码就可以查看cdb_demo数据库中有哪些表。

MySQL

use cdb_demo;
show tables;

查看表的结构定义可以使用describe语句，如下面的代码就可以查看t1表的定义。

MySQL

use cdb_demo;
describe t1;

本文示例执行结果如下图的所示。

查看t1表定义

查询结果中显示了t1表中的字段定义，包括的信息有：

Field，字段名。
Type，数据类型。
Null，是否允许为空值。
Key，字段定义的键，其中，PRI为主键（Primary Key），UNI为唯一键（Unique）。
Default，默认值。
Extra，其它设置，如auto_increment定义为自增长字段。

创建表时使用create table语句，基本语法如下。

MySQL

create table if not exists <表名> (字段定义)
engine = innodb
default charset = utf8mb4;

代码中，if not exists表示在数据表不存在时创建它，省略时，如果创建的表已存在则会出错。engine参数指定表的类型，MySQL数据表可以使用多种类型，本合集主要使用innodb。default charset参数指定表的默认字符集，本合集示例将统一使用utf8mb4字符集。

字段定义中，多个字段定义使用逗号分隔，每个字段定义中常用的要素包括：

字段名，本合集统一使用小写英文字符、数字和下画线命名。

数据类型，上篇文章介绍了一些常用的数据类型，可使用相关关键字定义。

是否允许为空，默认允许字段数据为空，如果不允许字段数据为空，可添加not null。

默认值，使用"default <值>"格式指定字段数据的默认值，添加记录时没有指定字段的数据时，字段数据就会使用默认值。请注意，如允许字段为空，又没有指定默认值，则默认值为空（null）。

主键（PK，Primary Key）。设置某个字段为主键时在字段定义中添加primary key。请注意，主键是表的属性，可以使用独立的语句设置，如“prmary key (f1,f2)”就是指定f1和f2字段数据的组合为主键，即在表的数据中，每条记录中f1和f2数据的组合不能重复。下面的代码演示了多字段主键的设置。

MySQL

use cdb_demo;

create table tt2(
f1 varchar(30),
f2 int,
f3 varchar(30),
primary key (f1,f2)
) engine = innodb default charset=utf8mb4;

唯一键（Unique）。当字段不是主键，而字段数据又不能重复时，可以在字段定义时使用unique关键字设置唯一键。唯一键同样可以使用单独的语句设置，并指定多个字段数据的组合唯一，如unique(f1,f3)就是指定f1和f3字段数据的组合不能重复。

数据验证。字段定义时可以使用check函数指定数据的范围，如“sex int not null check(sex in(0,1,2)) default 0”指定sex字段为int类型，不能为空，其值只能是0、1、2中的一个，且默认值为0。在学习更多的查询条件后可以设置更加丰富的数据验证规则。

下面的代码创建了保存用户信息的user_main表。

MySQL

use cdb_demo;

create table user_main(
recid bigint not null auto_increment primary key,
username varchar(50) not null unique,
userpwd char(64) not null,
ustate int not null default 1,
sex int not null check(sex in(0,1,2)) default 0,
fullname varchar(30),
phone varchar(30),
email varchar(50),
region varchar(30)
)engine=innodb default charset=utf8mb4;

insert into user_main(username,userpwd,ustate,fullname)
values('admin','8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92',0,'管理员');

前面的示例创建了t1表，其中的数据字段命名使用了f开始，然后是数字和字母序号的规则，而user_main表中使用了含义更加清晰的字段名。工作中，具体使用哪一种命名方式可以根据需要和实际情况而定，如果表中的字段不多，则可以使用含义明确的字段名；如果表中的字段比较多，而含义也比较复杂，而可以按字母和数字序号命名，但此时应有一个字段名字典，方便在使用数据时快速参考，应用开发中，还可以通过编程很方便地处理字段字典，在后续的编程相关合集中会有具体的应用介绍。

复制表结构

需要创建与已存在的表结构相同的新表时，同样可以使用create table语句，其语法如下：

MySQL

create table <新表名> like <旧表名>

如下面的代码创建了t1_bak表，其结构与t1表相同。

MySQL

use cdb_demo;

create table t1_bak like t1;

通过语句“describe cdb_demo.t1_bak;”可以查看t1_bak表的定义。

修改表名和表的移动

修改表的名称时可以使用rename table语句，其语法如下：

MySQL

rename table <旧表名> to <新表名>

如下面的代码可将t1_bak表修改为t1_old。

MySQL

use cdb_demo;

rename table t1_bak to t1_old;

使用rename table语句还可以将表移动到其它数据库中，如下面的代码：

MySQL

create database cdb_bak default charset=utf8mb4;

rename table cdb_demo.t1_old to cdb_bak.t1_bak;

代码首先创建了cdb_bak数据库，然后将cdb_demo数据库中的t1_old表移动到cdb_bak数据库，并命名为t1_bak。

添加字段

创建表后，还可以添加新的字段。修改表的定义时使用alter table语句，而添加字段则需要使用add column子句，其中column关键字可以省略。应用语法如下。

MySQL

alter table add column <字段定义>

其中，<字段定义>与创建表时的字段定义方法相同，如下面的代码就是在t1表中添加f7字段。

MySQL

use cdb_demo;

alter table t1 add column f7 tinyint not null default 0 check(f7 in (0,1,2));

代码中添加的f7字段定义为tinyint类型，不允许为空，默认值为0，并且数据只能是0、1或2。

删除字段

删除字段时使用alter table语句和drop column子句，使用语法如下。

MySQL

alter table drop column <字段名>

请注意，删除字段后，字段数据会一并删除，操作需谨慎！！！

修改字段

修改字段定义时使用alter table语句和change column子句，语法如下：

MySQL

alter table change column <旧字段名> <新字段定义>

请注意，一般情况下，修改字段类型时，新的类型处理范围应大于原有类型；如下面的代码会将t1表中新创建的f7字段修改为int类型。

MySQL

use cdb_demo;

alter table t1 change column f7 f7 int not null default 0 check(f7 in (0,1,2));

information_schema数据库

information_schema数据库包含了MySQL数据库中的各种信息，下面介绍数据库、表和字段信息的查询。

首先，可以通过SCHEMATA表查询数据库信息，其中的SCHEMA_NAME字段保存了数据库名称，判断数据库是否存在时可以使用如下代码。

MySQL

use information_schema;

select SCHEMA_NAME from SCHEMATA where SCHEMA_NAME='cdb_demo';

代码会查询cdb_demo数据库是否存在，执行结果返回“cdb_demo”，如果查询的数据库不存在则不会返回任何数据。如果需要返回固定的值，如数据库存在就返回1，不存在返回0，可以参考如下代码。

MySQL

use information_schema;

set @result = (select 1 where 
exists (select SCHEMA_NAME from SCHEMATA where SCHEMA_NAME='cdb_demo'));

select if(@result=1,1,0);

代码中使用了@result变量和if()函数，本例查询的同样是cdb_demo数据库，执行结果会显示1，大家可以修改数据库名称来观察执行结果。

表信息保存在TABLES表中，基本信息可以关注如下几个字段：

TABLE_SCHEMA，所在数据库。
TABLE_NAME，表名。
ENGINE，引擎类型。

查询cdb_demo数据库中所有表的信息可以使用如下代码。

MySQL

use information_schema;

select * from TABLES 
where TABLE_SCHEMA='cdb_demo';

字段信息保存在COLUMNS表中，基本信息可以关注如下几个字段：

TABLE_SCHEMA，所在数据库。
TABLE_NAME，表名。
COLUMN_NAME，字段名。
COLUMN_DEFAULT，默认值。
IS_NULLABLE，是否允许为。
DATA_TYPE，数据类型。
COLUMN_TYPE，字段类型完整定义。

查询cdb_demo数据库中t1表的字段信息可以使用如下代码。

MySQL

use information_schema;

select * from COLUMNS 
where TABLE_SCHEMA='cdb_demo' and TABLE_NAME='t1';

后续文章中会学习更多的查询条件和查询方法，实际应用中可以根据需要灵活查询数据库、表、字段等数据库对象信息。