前排提示:
本篇博客篇幅较长,建议结合目录进行阅读!
目录
环境说明:
数据库: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总共由以下几部分组成:
- 数据查询语言(DQL: Data Query Language):其语句也称为“数据检索语句”,用以从表中获得数据,确定数据怎样在应用程序给出。
- 数据操纵语言(DML:Data Manipulation Language):用于添加、修改和删除。
- 数据控制语言(DCL:Data Control Language):实现权限控制,确定单个用户和用户组对数据库对象的访问。
- 数据定义语言(DDL:Data Definition Language):在数据库中创建新表或修改、删除表(CREATE TABLE 或 DROP TABLE);为表加入索引等。
数据字典
定义:
数据字典是关系型数据库内部的一组系统表,他记录数据库中所有的定义信息,包括关系模式定义、视图定义、索引定义、完整约束定义、各类用户对数据库的操作权限、统计信息等。
- 关系型数据库在执行SQL的数据定义语句时,实际上就是更新数据库字典表中的相应信息。
- 进行查询优化和查询处理时,数据字典中的信息是其重要依据。
本篇只涉及到DDL,即数据定义语言。如无特别说明,本篇博客中方括号内容表示可选内容。
SQL中的数据定义功能包括模式定义、表定义、视图和索引定义。
0x01.模式
创建模式——CREATE SCHEMA
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
- 若不指定模式名,则默认为用户名
- 用户需要有数据库管理员权限或者获得了管理员授予的CREATE SCHEMA权限才能创建模式
定义模式实际上定义了一个命名空间,用户在创建模式的同时可以在这个模式中创建基本表、视图、定义授权等。即:
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
删除模式——DROP SCHEMA
DROP SCHEMA <模式名> <CASCADE|RESRICT>;
# 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 | 时间间隔类型 |
这里要说明的是,不同的数据库产品支持的数据类型并不完全相同,具体使用时还需参考官方文档。
创建表
CREATE TABLE <表名> (<列名1> <数据类型> [列级完整性约束条件1],
<列名2> <数据类型> [列级完整性约束条件2],
...
[<表级完整性约束条件>]);
附:常用到的与表有关的约束条件:
- NOT NULL:非空约束
- UNIQUE:唯一约束
- PRIMARY KEY:主键约束
- FROEIGN KEY:外键约束
- CHECK:校验约束
查看当前数据库有多少表:
# 选中某一个数据库
USE <数据库名>;
# 查看该数据库的所有表
SHOW TABLES;
删除表
DROP TABLE <表名> [RESTRICT|CASCADE];
RESTRICT
与CASCADE
的区别:
RESTRICT
指限制删除,表示该表的删除是有限制条件的:即该表不能被其他表的约束所引用(如CHECK
,FOREIGN KEY
等约束),不能存在依赖于该表的对象,比如视图、触发器、存储过程或者函数等。只有当这些限制条件不存在时,才能允许删除。
CASCADE
指级联删除,加上此参数之后则该表的删除没有限制条件。在删除基本表的同时,相关的对象,例如视图等,都将被一起删除。如果不指定删除类型时,默认是
RESTRICT
。
修改表
这里的修改针对的是基本表的结构(如添加删除列、或者修改数据类型),并不是基本表的数据。对于基本表数据的修改属于DML的范围,本篇博客只涉及到DDL。
-
添加新列
ALTER TABLE <表名> ADD [COLUMN] <新列名> <数据类型> [完整性约束]; # 给已存在的列添加列级完整性约束 ALTER TABLE <表名> ADD [列级完整性约束条件];
-
添加新的表级约束条件
ALTER TABLE <表名> ADD <表级完整性约束条件>;
-
删除指定列
ALTER TABLE <表名> DROP [COLUMN] <列名> [CASCADE|RESTRICT];
-
删除指定的完整性约束条件
ALTER TABLE <表名> DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE];
-
修改表中已存在的列
ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型>;
0x03.索引
建立索引的目的:加快查询速度
缺点:索引虽然能够加速数据库查询,但需要占用一定的存储空间,并且当基本表更新时,索引也需要进行相应的维护。这些都会增加数据库的负担,因此要根据实际应用的需要有选择地创建索引。
索引类型
目前SQL标准中没有涉及索引,但商用关系数据库系统一般都会支持索引机制,且不同数据库支持的索引类型不尽相同。
顺序文件上的索引:针对按指定属性值升序和降序存储的关系,在该属性上建立一个顺序索引文件,索引文件由属性值和相应的元组指针组成。
B+树索引:将索引属性组织成 B+树的形式,B+树的叶节点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。
散列(hash)索引:建立若干个桶,将索引属性按照其散列函数映射到相应桶中,桶中存放索引属性和相应的元组指针。散列 索引具有查找速度快的特点。
位图索引:用位向量记录索引属性中可能出翔的值,每个位向量对应一个可能值。
建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名1>[<次序1>],···);
<表名>
是要建索引的基本表的名字- 索引可以建立在该表的一列或者多列上,各列名之间用逗号分隔没每个列名后妈可以用
<次序>
指定索引值的排列次序,可选ASC(升序)
或者DESC(降序)
。默认ASC
。UNIQUE
表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER
此索引是聚簇索引。
修改索引名称
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
要修改索引本身的话,建议删除再重建。
删除索引
DROP INDEX <索引名>
- 索引一经建立就由系统使用和维护,无需用户干预。
- 删除索引是由于数据库频繁进行增、删、改,系统便会花费许多时间来维护索引,从而降低查询效率,这是便可以删除一些不必要的索引。
- 索引删除后,数据字典上关于索引的描述也会被删除。
0x04.视图
定义
视图是从一个或几个基本表(或者视图)导出的表。
视图一经定义,就可以和基本表一样被查询、被删除。也可以在一个视图上再定义新的视图,但对视图的更新(增、删、改)操作则有一定的限制。
特征
数据库只存放视图的定义,而不存放视图中对应表的数据(否则数据库中便存放了很多相同的数据),这些数据仍存放在原来的基本表中。
归根到底,视图与“图”无关,其实质上还是表。只不过由于不存放数据,只存放定义,因此称其为“虚表”。
几个概念
- 行列子集视图:建立在基本表之上,只是去掉了基本表的某些行和列,但保留了主键的这类视图。
- 分组视图:带有聚集函数和
GROUP BY子句
的查询的视图。 - 带表达式的视图:简单来说就是视图中存在基本表中不实际存在的列,即虚拟列。这些列是由基本表中的数据列经过各种计算派生出来的。
创建视图
CREATE VIEW <视图名> (<列名>,<列名>,<列名> ...) AS <子查询> [WITH CHECK OPTION];
-
<子查询>
是针对基本表的SELECT语句,即从建立视图的基本表中选取部分数据,而不是全部数据 -
[WITH CHECK OPTION]
是一个条件表达式,有这个条件表达式时,对视图进行UPDATE、INSERT和DELETE时如果要操作的行不满足这里的条件,则不允许进行 -
视图不仅可以建立在单个基本表上,也可以建立在多个基本表上
-
数据库执行
CREATE VIEW语句
的结果只是把视图的定义存入数据字典,并不执行其中的SELECT语句
。只有在进行视图的查询时,才会执行SELECT语句
。 -
组成视图的属性列名全部指定或者全部省略,没有第三种选择。
以下三种情况必须要指明视图的列名:
- 某个目标列并不是单纯的属性名,而是聚集函数或者列表达式
- 多表连接时选出了几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更适合的名字
查询视图
视图其本质上还是表,因此可以对其进行查询。查询视图与查询表的语句基本相同。详见[DQL]()。
在视图查询的过程中,会经过视图消解,将对视图的查询转换为对基本表的查询。
视图消解:关系型数据库执行视图的查询操作时,首先进行有效性检查,即确定查询中涉及到的表、视图等是否都存在。如果存在,则从数据字典中取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询。这一转换过程称之为视图消解。
局限:目前多数关系数据库对行列子集视图都能正确地转换。但对非行列子集视图的查询就不一定能做转换了,因此这类查询应该直接对基本表进行。
非行列子集视图:图中的部分列由其他表的列经过运算得出。
视图查询与基于派生表的查询的区别:
- 视图一旦定义,其定义将永久保存在数据字典中,之后的所有查询都可以直接饮用该视图。
- 而派生表知识在语句执行时临时定义,语句执行还定义即被删除。
更新视图
视图的更新包括INSERT、DELETE、UPDATE,其操作语句与表的操作语句基本相同。此处不再详述。详见[DML]()。
类似于视图的查询,对视图的更新同样是通过视图消解,转换为对基本表的更新操作。
目前各个关系数据库一般只允许对行列子集视图进行更新,而且不同的数据库对视图的更新还有更进一步的规定。由于各数据库系统实现方法上的差异,这些的规定也不尽相同。
删除视图
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版)》 王珊 萨师煊 编著
评论