概述
DDL 的英文全称是 Data Definition Language,中文是数据定义语言。它定义了数据库的结构和数据表的结构。
在 DDL 中,我们常用的功能是增删改,分别对应的命令是 CREATE、DROP 和 ALTER。需要注意的是,在执行 DDL 的时候,不需要 COMMIT,就可以完成执行任务。
数据库管理
在数据库中,创建和删除数据库的指令如下:
CREATE DATABASE nba; // 创建一个名为nba的数据库
DROP DATABASE nba; // 删除一个名为nba的数据库
数据表管理
创建数据表(基于DDL)
创建数据表的语法结构如下:
CREATE TABLE [table_name](字段名 数据类型,......);
比如我们想创建一个球员表,表名为 player,里面有两个字段,一个是 player_id,它是 int 类型,另一个 player_name 字段是varchar(255)类型。这两个字段都不为空,且 player_id 是递增的。
CREATE TABLE player (
player_id int(11) NOT NULL AUTO_INCREMENT,
player_name varchar(255) NOT NULL
);
在上述命令中,我们需要注意以下几点:
- 语句最后以分号(;)作为结束符;
- 最后一个字段的定义结束后没有逗号;
- 数据类型中 int(11) 代表整数类型,显示长度为 11 位,括号中的参数 11 代表的是最大有效显示长度,与类型包含的数值范围大小无关;
- varchar(255)代表的是最大长度为 255 的可变字符串类型;
- NOT NULL表明整个字段不能是空值,是一种数据约束;
-
创建数据表(基于DataGrip)
实际上,我们通常很少自己写 DDL 语句,可以使用一些可视化工具来创建和操作数据库和数据表。
我们以 DataGrip 为例进行演示,它是一个数据库管理和设计工具,跨平台,支持很多种数据库管理软件,比如 MySQL、Oracle、MariaDB 等。
假如还是针对 player 这张表,我们想设计以下的字段:
其中: player_id 是数据表 player 的主键,且自动增长;
- player_id、team_id、player_name 这三个字段均不为空,height 字段可以为空。
此时,我们按照上述的设计需求,可以使用 DataGrip 配置如下:
这样一来,我们只需要在上述表格中配置相关的列、键和索引时,将会自动在预览部分生成对应的 DDL 语句。
此时,我们也可以直接在预览部门编辑 DDL 语句进行修改,然后点击确定即可,例如,我们可以扩展 DDL 语句如下:
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player` (
`player_id` int(11) NOT NULL AUTO_INCREMENT,
`team_id` int(11) NOT NULL,
`player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`height` float(3, 2) NULL DEFAULT 0.00,
PRIMARY KEY (`player_id`) USING BTREE,
UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
可以看到,在这个 DDL 处理中:
- 首先先删除 player 表(如果数据库中存在该表的话)
- 然后再创建 player 表,里面的数据表和字段都使用了反引号,这是为了避免它们的名称与 MySQL 保留字段相同,对数据表和字段名称都加上了反引号。
- player_name 字段的字符编码是 utf8,排序规则是utf8_general_ci,代表对大小写不敏感,如果设置为utf8_bin,代表对大小写敏感。
- 我们将 player_id 设置为了主键,因此在 DDL 中使用PRIMARY KEY进行规定,同时索引方法采用 BTREE。
- 我们对 player_name 字段进行索引,在设置字段索引时,我们可以设置为UNIQUE INDEX(唯一索引),也可以设置为其他索引方式,比如NORMAL INDEX(普通索引),这里我们采用UNIQUE INDEX。唯一索引和普通索引的区别在于它对字段进行了唯一性的约束。
- 在索引方式上,你可以选择BTREE或者HASH,这里采用了BTREE方法进行索引。
- 整个数据表的存储规则采用 InnoDB。之前我们简单介绍过 InnoDB,它是 MySQL5.5 版本之后默认的存储引擎。同时,我们将字符编码设置为 utf8,排序规则为utf8_general_ci,行格式为Dynamic。
当完成了数据表的创建后,我们可以在 DataGrip 中快捷导出数据表的创建语句:
其中,我们可以将 SQL 生成器中的生成方式调整为『RDBMS 服务器提供的定义』。
你能看出可视化工具还是非常方便的,它能直接帮我们将数据库的结构定义转化成 SQL 语言,方便数据库和数据表结构的导出和导入。
修改数据表结构
在创建表结构之后,我们还可以对表结构进行修改,虽然直接使用 Navicat 可以保证重新导出的数据表就是最新的,但也有必要了解,如何使用 DDL 命令来完成表结构的修改。
添加字段
在数据表中添加一个 age 字段,类型为int(11):
ALTER TABLE player ADD (age int(11));
修改字段名称
修改字段名,将 age 字段改成player_age:
ALTER TABLE player RENAME COLUMN age to player_age;
修改字段类型
将player_age的数据类型设置为float(3,1):
ALTER TABLE player MODIFY (player_age float(3,1));
删除字段
删除刚才添加的player_age字段:
ALTER TABLE player DROP COLUMN player_age;
删除数据表
删除数据表的操作非常简单,只需要执行如下 DROP TABLE语句即可:
DROP TABLE player;
数据表约束
当我们创建数据表的时候,还会对字段进行约束,约束的目的在于保证 RDBMS 里面数据的准确性和一致性。
接下来,我们来了解一下关于数据表的相关约束:
- 主键约束:主键起的作用是唯一标识一条记录,不能重复,不能为空,即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段,也可以由多个字段复合组成。
- 外键约束:外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的,也可以为空。比如 player_id 在 player 表中是主键,如果你想设置一个球员比分表即 player_score,就可以在 player_score 中设置 player_id 为外键,关联到 player 表中。
- 唯一性约束:唯一性约束表明了字段在表中的数值是唯一的,除了主键之外,还可以对其他字段进行唯一性约束。唯一性约束和普通索引(NORMAL INDEX)之间是有区别的。唯一性约束相当于创建了一个约束和普通索引,目的是保证字段的正确性,而普通索引只是提升数据检索的速度,并不对字段的唯一性进行约束。
- NOT NULL 约束:即表明该字段不应为空,必须有取值。
- DEFAULT:表明了字段的默认值。如果在插入数据的时候,这个字段没有取值,就设置为默认值。
CHECK 约束:用来检查特定字段取值范围的有效性,CHECK 约束的结果不能为 FALSE,比如我们可以对身高 height 的数值进行 CHECK 约束,必须≥0,且<3,即CHECK(height>=0 AND height<3)。
数据表设计原则
在进行数据库设计的时候,我们通常需要考虑一些通用性的原则,主要原则如下:
数据表的个数越少越好:RDBMS 的核心在于对实体和联系的定义,也就是 E-R 图(Entity Relationship Diagram),数据表越少,证明实体和联系设计得越简洁,既方便理解又方便操作。
- 数据表中的字段个数越少越好:字段个数越多,数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立,而不是某个字段的取值可以由其他字段计算出来。
- 数据表中联合主键的字段个数越少越好:联合主键中的字段越多,占用的索引空间越大,不仅会加大理解难度,还会增加运行时间和索引空间,因此联合主键的字段个数越少越好。
- 使用主键越多越好。
- 大规模数据量的情况下不要使用外键:超大型的数据应用场景,大量的插入,更新和删除在外键的约束下会降低性能,同时数据库在水平拆分和分库的情况下,数据库端也做不到执行外键约束。