SQL 数据定义语言DDL

数据定义语言(Data Definition Language)用于定义数据库中各种对象的结构,例如表、视图、索引等。常见的 DDL 语句包括创建(CREATE)、修改(ALTER)和删除(DROP)。虽然各种对象的具体语法细节不同,但都遵循相同的模式;例如,创建索引可以使用 CREATE INDEX 语句;

DDL是DBMS的核心组件,也是SQL的重要组成部分,DDL的正确性和稳定性是整个SQL运行的重要基础。

面对同一个需求,不同的开发人员创建出来的数据库和数据表可能千差万别,那么在设计数据库的时候,究竟什么是好的原则?我们在创建数据表的时候需要注意什么?

DDL的基础语法及设计工具

DDL的英文全称是Data Definition Language,中文是数据定义语言。它定义了数据库的结构和数据表的结构。

在DDL中,我们常用的功能是增删改,分别对应的命令是CREATE、DROP和ALTER。需要注意的是,在执行DDL的时候,不需要COMMIT,就可以完成执行任务。

1.对数据库进行定义

CREATE DATABASE nba; // 创建一个名为nba的数据库
DROP DATABASE nba; // 删除一个名为nba的数据库

2.对数据表进行定义

创建表结构的语法是这样的:

CREATE TABLE [table_name](字段名 数据类型,......)

SQL 创建表结构

比如我们想创建一个球员表,表名为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表明整个字段不能是空值,是一种数据约束。AUTO_INCREMENT代表主键自动增长。

实际上,我们通常很少自己写DDL语句,可以使用一些可视化工具来创建和操作数据库和数据表。在这里我推荐使用Navicat,它是一个数据库管理和设计工具,跨平台,支持很多种数据库管理软件,比如MySQL、Oracle、MariaDB等。

假如还是针对player这张表,我们想设计以下的字段:

SQL DDL介绍
其中player_id是数据表player的主键,且自动增长,也就是player_id会从1开始,然后每次加1。player_id、team_id、player_name 这三个字段均不为空,height字段可以为空。

按照上面的设计需求,我们可以使用Navicat软件进行设计,如下所示:

SQL DDL介绍
然后,我们还可以对player_name字段进行索引,索引类型为Unique。使用Navicat设置如下:

SQL DDL介绍
这样一张player表就通过可视化工具设计好了。我们可以把这张表导出来,可以看看这张表对应的SQL语句是怎样的。方法是在Navicat左侧用右键选中player这张表,然后选择“转储SQL文件”→“仅结构”,这样就可以看到导出的SQL文件了,代码如下:

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;

你能看到整个SQL文件中的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方法进行索引。我会在后面介绍BTREEHASH索引方式的区别。

整个数据表的存储规则采用InnoDB。之前我们简单介绍过InnoDB,它是MySQL5.5版本之后默认的存储引擎。同时,我们将字符编码设置为utf8,排序规则为utf8_general_ci,行格式为Dynamic,就可以定义数据表的最后约定了:

ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

你能看出可视化工具还是非常方便的,它能直接帮我们将数据库的结构定义转化成SQL语言,方便数据库和数据表结构的导出和导入。不过在使用可视化工具前,你首先需要了解对于DDL的基础语法,至少能清晰地看出来不同字段的定义规则、索引方法,以及主键和外键的定义。

SQL 修改表结构

在创建表结构之后,我们还可以对表结构进行修改,虽然直接使用Navicat可以保证重新导出的数据表就是最新的,但你也有必要了解,如何使用DDL命令来完成表结构的修改。

1.添加字段,比如我在数据表中添加一个age字段,类型为int(11)

ALTER TABLE player ADD (age int(11));

2.修改字段名,将age字段改成player_age

ALTER TABLE player RENAME COLUMN age to player_age

3.修改字段的数据类型,将player_age的数据类型设置为float(3,1)

ALTER TABLE player MODIFY (player_age float(3,1));

4.删除字段, 删除刚才添加的player_age字段

ALTER TABLE player DROP COLUMN player_age;
赞(2)
未经允许不得转载:极客笔记 » SQL 数据定义语言DDL
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址