SQL系列总结（一）：DDL（数据定义语言）

2023-07-01 15:51:20 浏览数 (2)

环境说明：

数据库：Mysql 5.5

连接软件：Navicat

前言

SQL总结系列目录：

  • SQL系列总结（一）：DDL（数据定义语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（二）：DQL（数据查询语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（三）：DML（数据操纵语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（四）：DCL（数据控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（五）：TCL（事务控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
SQL简介

SQL（Structured Query Language），称为结构化查询语言，是关系数据库的标准语言。其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性控制等一系列功能。

目前没有一个关系数据库系统（RDBMS）能够支持SQL标准的所有概念和特性。大部分数据库系统能支持SQL/92标准的大部分功能以及SQL99、SQL2003中的部分新概念。同时许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改，又可以支持标准以外的一项功能特性。因此，使用具体数据库系统时还需要参考相应的官方文档。

SQL总共由以下几部分组成：

  1. 数据查询语言（DQL: Data Query Language）：其语句也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。
  2. 数据操纵语言（DML：Data Manipulation Language）：用于添加、修改和删除。
  3. 数据控制语言（DCL：Data Control Language）：实现权限控制，确定单个用户和用户组对数据库对象的访问。
  4. 数据定义语言（DDL：Data Definition Language）：在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。
数据字典
定义：

数据字典是关系型数据库内部的一组系统表，他记录数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整约束定义、各类用户对数据库的操作权限、统计信息等。

  • 关系型数据库在执行SQL的数据定义语句时，实际上就是更新数据库字典表中的相应信息。
  • 进行查询优化和查询处理时，数据字典中的信息是其重要依据。

本篇只涉及到DDL，即数据定义语言。如无特别说明，本篇博客中方括号内容表示可选内容。

SQL中的数据定义功能包括模式定义、表定义、视图和索引定义。

0x01.模式

创建模式——CREATE SCHEMA
代码语言：javascript复制
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

  1. 若不指定模式名，则默认为用户名
  2. 用户需要有数据库管理员权限或者获得了管理员授予的CREATE SCHEMA权限才能创建模式

定义模式实际上定义了一个命名空间，用户在创建模式的同时可以在这个模式中创建基本表、视图、定义授权等。即：

代码语言：javascript复制
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
删除模式——DROP SCHEMA
代码语言：javascript复制
DROP SCHEMA <模式名> ;

# CASCADE（级联）和 RESTICT（限制）两者必选其一

0x02.基本表

数据类型

数据类型

含义

CHAR(n)，CHARACTER(n)

长度为n的定长字符串

VARCHAT(n)，CHARACTERVARYING(n)

最大长度为n的变长字符串

CLOB

字符串大对象

BLOB

二进制大对象

INT，INTEGER

长整数（4字节）

SMALLINT

短整数（2字节）

BIGINT

大整数（8字节）

NUMERIC(p,d)

定点数，由p位数字（不包括小数点、符号）组成，小数点后面有d位数字

DECIMAL(p,d)，DEC(p,d)

同NUMERIC

REAL

取决于机器精度的单精度浮点数

DOUBLE PRECISION

取决于机器精度的双精度浮点数

FLOAT(n)

可选精度的浮点数，精度至少为n位数字

BOOLEAN

布尔类型

DATE

日期，包含年、月、日，格式为YYYY-MM-DD

TIME

时间，包含一日的时、分、秒，格式为HH:MM:SS

TIMESTAMP

时间戳

INTERVAL

时间间隔类型

这里要说明的是，不同的数据库产品支持的数据类型并不完全相同，具体使用时还需参考官方文档。

创建表
代码语言：javascript复制
CREATE TABLE <表名> (<列名1> <数据类型> [列级完整性约束条件1],
                    <列名2> <数据类型> [列级完整性约束条件2],
                   ...
                   [<表级完整性约束条件>]);

附：常用到的与表有关的约束条件：

  • NOT NULL：非空约束
  • UNIQUE：唯一约束
  • PRIMARY KEY：主键约束
  • FROEIGN KEY：外键约束
  • CHECK：校验约束

查看当前数据库有多少表：

代码语言：javascript复制
# 选中某一个数据库
USE <数据库名>;
# 查看该数据库的所有表
SHOW TABLES;
删除表
代码语言：javascript复制
DROP TABLE <表名> [RESTRICT|CASCADE];

RESTRICT与CASCADE的区别： RESTRICT指限制删除，表示该表的删除是有限制条件的：即该表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束），不能存在依赖于该表的对象，比如视图、触发器、存储过程或者函数等。只有当这些限制条件不存在时，才能允许删除。 CASCADE指级联删除，加上此参数之后则该表的删除没有限制条件。在删除基本表的同时，相关的对象，例如视图等，都将被一起删除。 如果不指定删除类型时，默认是RESTRICT。

修改表

这里的修改针对的是基本表的结构（如添加删除列、或者修改数据类型），并不是基本表的数据。对于基本表数据的修改属于DML的范围，本篇博客只涉及到DDL。

添加新列

代码语言：javascript复制
ALTER TABLE <表名> ADD [COLUMN] <新列名> <数据类型> [完整性约束];

# 给已存在的列添加列级完整性约束
ALTER TABLE <表名> ADD [列级完整性约束条件];

添加新的表级约束条件

代码语言：javascript复制
ALTER TABLE <表名> ADD <表级完整性约束条件>;

删除指定列

代码语言：javascript复制
ALTER TABLE <表名> DROP [COLUMN] <列名> [CASCADE|RESTRICT];

删除指定的完整性约束条件

代码语言：javascript复制
ALTER TABLE <表名> DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE];

修改表中已存在的列

代码语言：javascript复制
ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型>;

0x03.索引

建立索引的目的：加快查询速度

缺点：索引虽然能够加速数据库查询，但需要占用一定的存储空间，并且当基本表更新时，索引也需要进行相应的维护。这些都会增加数据库的负担，因此要根据实际应用的需要有选择地创建索引。

索引类型

目前SQL标准中没有涉及索引，但商用关系数据库系统一般都会支持索引机制，且不同数据库支持的索引类型不尽相同。

顺序文件上的索引：针对按指定属性值升序和降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。

B 树索引：将索引属性组织成 B 树的形式，B 树的叶节点为属性值和相应的元组指针。B 树索引具有动态平衡的优点。

散列(hash)索引：建立若干个桶，将索引属性按照其散列函数映射到相应桶中，桶中存放索引属性和相应的元组指针。散列 索引具有查找速度快的特点。

位图索引：用位向量记录索引属性中可能出翔的值，每个位向量对应一个可能值。

建立索引
代码语言：javascript复制
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名1>[<次序1>],···);

  1. <表名>是要建索引的基本表的名字
  2. 索引可以建立在该表的一列或者多列上，各列名之间用逗号分隔没每个列名后妈可以用<次序>指定索引值的排列次序，可选ASC(升序)或者DESC(降序)。默认ASC。
  3. UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
  4. CLUSTER此索引是聚簇索引。
修改索引名称
代码语言：javascript复制
ALTER INDEX <旧索引名> RENAME TO <新索引名>;

要修改索引本身的话，建议删除再重建。

删除索引
代码语言：javascript复制
DROP INDEX <索引名>
  • 索引一经建立就由系统使用和维护，无需用户干预。
  • 删除索引是由于数据库频繁进行增、删、改，系统便会花费许多时间来维护索引，从而降低查询效率，这是便可以删除一些不必要的索引。
  • 索引删除后，数据字典上关于索引的描述也会被删除。

0x04.视图

定义

视图是从一个或几个基本表（或者视图）导出的表。

视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。

特征

数据库只存放视图的定义，而不存放视图中对应表的数据（否则数据库中便存放了很多相同的数据），这些数据仍存放在原来的基本表中。

归根到底，视图与“图”无关，其实质上还是表。只不过由于不存放数据，只存放定义，因此称其为“虚表”。

几个概念
  • 行列子集视图：建立在基本表之上，只是去掉了基本表的某些行和列，但保留了主键的这类视图。
  • 分组视图：带有聚集函数和GROUP BY子句的查询的视图。
  • 带表达式的视图：简单来说就是视图中存在基本表中不实际存在的列，即虚拟列。这些列是由基本表中的数据列经过各种计算派生出来的。
创建视图
代码语言：javascript复制
CREATE VIEW <视图名> (<列名>,<列名>,<列名> ...) AS <子查询> [WITH CHECK OPTION];
  • <子查询>是针对基本表的SELECT语句，即从建立视图的基本表中选取部分数据，而不是全部数据
  • [WITH CHECK OPTION]是一个条件表达式，有这个条件表达式时，对视图进行UPDATE、INSERT和DELETE时如果要操作的行不满足这里的条件，则不允许进行
  • 视图不仅可以建立在单个基本表上，也可以建立在多个基本表上
  • 数据库执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只有在进行视图的查询时，才会执行SELECT语句。
  • 组成视图的属性列名全部指定或者全部省略，没有第三种选择。

以下三种情况必须要指明视图的列名：

  1. 某个目标列并不是单纯的属性名，而是聚集函数或者列表达式
  2. 多表连接时选出了几个同名列作为视图的字段
  3. 需要在视图中为某个列启用新的更适合的名字
查询视图

视图其本质上还是表，因此可以对其进行查询。查询视图与查询表的语句基本相同。详见DQL。

在视图查询的过程中，会经过视图消解，将对视图的查询转换为对基本表的查询。

视图消解：关系型数据库执行视图的查询操作时，首先进行有效性检查，即确定查询中涉及到的表、视图等是否都存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称之为视图消解。

局限：目前多数关系数据库对行列子集视图都能正确地转换。但对非行列子集视图的查询就不一定能做转换了，因此这类查询应该直接对基本表进行。

非行列子集视图：图中的部分列由其他表的列经过运算得出。

视图查询与基于派生表的查询的区别：

  • 视图一旦定义，其定义将永久保存在数据字典中，之后的所有查询都可以直接饮用该视图。
  • 而派生表知识在语句执行时临时定义，语句执行还定义即被删除。
更新视图

视图的更新包括INSERT、DELETE、UPDATE，其操作语句与表的操作语句基本相同。此处不再详述。详见DML。

类似于视图的查询，对视图的更新同样是通过视图消解，转换为对基本表的更新操作。

目前各个关系数据库一般只允许对行列子集视图进行更新，而且不同的数据库对视图的更新还有更进一步的规定。由于各数据库系统实现方法上的差异，这些的规定也不尽相同。

删除视图
代码语言：javascript复制
DROP VIEW <视图名> [CASCADE];
  • 视图删除实质上是将视图的定义从数据字典中删除。
  • CASCADE是可选的
  • 若要删除的视图还导出了其他视图，那么加上CASCADE参数之后将会把该视图导出的视图一块删除。

总结

SQL可以分为数据定义(DDL)、数据查询(DQL)、数据更新(DML)、数据控制(DCL)四大部分。

综上，DDL中的基本操作可以用表格简单总结一下：

操作对象

创建

删除

修改

模式

CREATE SCHEMA

DROP SCHEMA

​

表

CREATE TABLE

DROP TABLE

ALTER TABLE

视图

CREATE VIEW

DROP VIEW

​

索引

CREATE INDEX

DROP INDEX

ALTER INDEX

主要参考资料：《数据库系统概论（第5版）》 王珊 萨师煊 编著

数据库 sql ddl 数据 索引

0 人点赞