数据库 SQL

关系数据库的标准语言：结构化查询语言 SQL

基本概念

外模式包括若干视图和部分基本表

模式包括若干基本表

内模式包括若干存储文件

基本表：是本身独立存在的表，在关系数据库管理系统中，一个关系对应一个基本表，一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引存放在存储文件中

存储文件的逻辑结构组成了关系数据库的内模式，存储文件的物理结构对最终用户隐藏

视图：是从一个或几个基本表导出的表，本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，数据仍然存放在导出视图的基本表中，视图是虚表

查询表：是一个临时表，只要关闭查询，查询表即刻消失

数据定义

SQL 标准不提供修改模式定义和修改视图定义的操作

SQL 标准不提供索引相关的语句，但为了提高查询效率，商用关系数据库通常提供了索引机制和相关语句

模式

定义模式

create schema <模式名> authorization <用户名>

如果没有指定<模式名>，那么<模式名>隐含为<用户名>

实质：定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象

子句：

  create schema 接受的子句：create table、create view、grant，即可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权

create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

删除模式

drop schema <模式名> <cascade|restrict>

cascade：级联，表示删除模式的同时把该模式中所有的数据库对象全部删除

restrict：限制，如果该模式中定义了下属的数据库对象，则拒绝该删除语句的执行，只有该模式中没有任何下属的对象时才能执行 drop schema 语句

默认 restrict

基本表

定义基本表

创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表

create table <表名> (<列名><数据类型>[列级完整性约束条件]

[,<列名><数据类型>[列级完整性约束条件]]

...

[,<表级完整性约束条件>]);

完整性约束条件：

       建表的同时通常可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中的数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则在即可以定义在表级上又可以定义在列级上

主码：primary key，主码包含多个属性，必须作为表级完整性约束条件进行定义，

primary key(属性1,属性2,...)

取唯一值：unique

非空：not null

外码：作为表级完整性约束条件出现foreign key(参照关系中的属性) references 被参照关系(属性)

列级约束可以转换为表级约束，表级约束不一定可转换为列级约束

数据类型

SQL 标准支持的数据类型：

数据类型	含义
char(n)，character(n)	长度为 n 的定长字符串
varchar(n)，charactervarying	最大长度为 n 的变长字符串
clob	字符串大对象
blob	二进制大对象
int，integer	长整数（4字节）
smallint	短整数（2字节）
bigint	大整数（8字节）
numeric(p,d)	定点数，由 p 位数字（不包括符号、小数点）组成，小数点后面有 d 位数字
decimal(p,d)，dec(p,d)	同上
real	取决于机器精度的单精度浮点数
double precision	取决于机器精度的双精度浮点数
float(n)	可选精度的浮点数，精度至少为 n 位数字
boolean	逻辑布尔量
date	日期，包含年、月、日，格式为 YYYY-MM-DD
time	时间，包含一日的时、分、秒，格式为 HH:MM:SS
timestamp	时间戳类型
interval	时间间隔类型

SQL Server 常见数据类型：

模式与表：

每一个基本表都属于某一个模式，一个模式包含多个基本表

定义基本表时定义其模式的三种方法：

方法一：在表名中显示地给出模式名

create table "模式名".表名(...)

方法二：在创建模式语句中同时创建表

create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

方法三：设置所属的模式，在创建表时表名中不必给出模式名

当用户创建基本表时若没有指定模式，系统根据搜索路径确定该对象所属的模式

搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将会给出错误。

显示当前的搜索路径：

show search_path;

修改基本表

alter table <表名>
[add [column] <新列名> <数据类型> [完整性约束]]//增加新列，column 可省略
[add <列级完整性约束>]//增加列级完整性约束条件
[add <表级完整性约束>]//增加表级完整性约束条件
[drop [column] <列名> [cascade|restrict]]//删除列，指定cascade自动删除引用该列的其他对象；指定restrict若该列被其他对象引用则拒绝删除该列
[drop constraint <完整性约束名> [restrict|cascade]]//删除指定的完整性约束条件，此处，需要给完整性约束条件取名字
[alter column <列名> <数据类型>]//修改列的数据类型

修改列名，一般方法，增加新的列，删除原来一列，一般禁止修改

不论基本表中原来是否已有数据，新增加的列一律为空值，若设定新的列为非空，则无法将新的列添加入表中

add子句：增加新列、新的列级完整性约束条件、新的表级完整性约束条件

drop column子句：删除表中的列，指定cascade自动删除引用该列的其他对象；指定restrict若该列被其他对象引用则拒绝删除该列

drop constraint子句：删除指定的完整性约束条件，需给完整性约束条件取名

alter column子句：修改原有的列定义，包括修改列名、数据类型

删除基本表

drop table <表名> [restrict|cascade]

restrict：有限制条件，欲删除的基本表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或函数……如果存在依赖该表的对象，则此表不能被删除

cascade：没有限制条件，在删除此表的时候，相关的依赖对象，一同被删除

默认 restrict

基本表定义一旦被删除，不仅表中的数据和此表的定义被删除，而且此表上建立的索引、触发器等对象一般也都将被删除。有的关系数据库管理系统还会同时删除此表上建立的视图。若欲删除的基本表被其他表所引用，则这些表也可能被删除。

索引

建立索引是加快查询速度的有效手段，但需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，会增加数据库的负担

常见的索引：顺序文件上的索引、B+树索引、散列索引、位图索引……

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

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

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

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

索引属于内模式的范畴

建立与删除索引由数据库管理员或表的属主，即建立表的人，负责完成

关系数据库管理系统在执行查询时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引

建立索引

create [unique] [cluster] index <索引名> on <表名> (<列名>[<次序>] [,<列名> [<次序>]]...);

<表名>：要建索引地基本表的名字，索引可以建立在该表的一列或多列上，每个<列名>后面可以用<次序>指定索引值的排列次序，可选 asc（升序）或 desc（降序），默认为 asc

unique：表明此索引的每一个索引值只对应唯一的数据记录

cluster：表明要就建立的索引是聚簇索引

修改索引

修改索引名：alter index <旧索引名> rename to <新索引名>

删除

drop index <索引名>

删除索引时，系统会同时从数据字典中删去有关该索引的描述

数据查询

数据查询是数据库的核心操作

select [all|distinct] <目标列表达式> [,<目标列表达式>]...  //all全部；distinct删去重复的
from <表名或视图名> [,<表明或视图名>...]|(<select 语句>) [as] <别名>
[where <条件表达式>]  //条件
[group by <列名1> [having <条件表达式>]]  //分组
[order bu <列名2> [asc|desc]]  //排序

整个 select 语句的含义是，根据 where 子句的条件表达式从 from 子句指定的基本表、视图或派生表中找出满足条件的元组，再按 select 子句中的目标列表达式选出元组中属性值形成结果表

如果有 group by 子句，则将结果按 <列名1> 的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果 group by 子句带 having 短语，则只有满足指定条件的组才予以输出

如果有 order by 子句，则结果表还要按 <列名2> 的值的升序或降序排序

单表查询

选择表中的若干列

选择表中的全部或部分列即关系代数的投影运算

查询指定列

select <目标列表达式>//各个列的先后顺序可以与表中的顺序不一致

from <表名或视图名>

查询全部列

1. 在 select 关键字后面列出所有列名
2. 若列的显示顺序与其在基表中的顺序相同，将 <目标列表达式> 指定为 *

查询经过计算的值

select 子句的 <目标列表达式> 不仅可以是表中的属性列，也可以是表达式、字符串常量、函数……

用户可根据指定别名来改变查询结果的列标题<目标列表达式> 别名

选择表中的若干元组

消除取值重复的列

两个本来并不完全相同的元组在投影到指定的某些列上后，可能会变成相同的行，可以用 distinct 消除它们

select distinct <目标列表达式>

from <表名或视图名>

查询满足条件的元组

查询满足指定条件的元组可以通过 where 子句实现

比较大小

用于进行比较的运算符一般包括 =、>、<、>=、<=、!=、<>（不等于）、!>（不大于）、!<（不小于）

确定范围

between……and…… 和 not between……and…… 可以用来查找属性值在或不在指定范围内的元组，其中 between 后是范围的下限（即低值），and 后是范围的上限（即高值）

确定集合

in 可以用来查找属性值属于指定集合的元组

not in 用于查找属性值不属于指定集合的元组

字符匹配

like 用来进行字符串的匹配

[not] like '<匹配串>' [escape '<换码字符>']

查找指定的属性列值与 <匹配串> 相匹配的元组

<匹配串> 可以是一个完整的字符串，也可以含有通配符 % 和 _

• %：代表任意长度（长度可以为0）的字符串
• _：代表任意单个字符

如果 like 后面的匹配串中不含通配符，则可以用 = 运算符取代 like 谓词，用 != 或 <> 运算符取代 not like 谓词

数据库字符集为 ASCII 时一个汉字需要两个 _；当字符集为 GBK 时只需要一个 _

如果用户要查询的字符串本身含有通配符 % 或 _ ，此时需要 escape '<换码字符>' 短语对通配符进行转义，使得紧跟在换码字符后面紧跟的字符不再是通配符而是普通的子字符

涉及空值的查询

is null 空值

is not null 非空值

is 不能用等号 = 代替

多重条件查询

and 和 or 可用来连接多个查询条件，and 优先级高于 or，但用户可用括号改变优先级

order by 子句

用 order by 子句对查询结果按照一个或多个属性列的升序 asc 或降序 desc 排列，默认值为升序

对于空值，排序时显示的次序由具体系统实现决定

聚集函数

count(*)  //统计元组个数
count([distinct|all] <列名>)  //统计一列中值的个数
sum([distinct|all] <列名>)   //计算一列值的总和（此列必须是数值型）
avg([distinct|all] <列名>)   //计算一列值的平均值（此列必须是数值型）
max([distinct|all] <列名>)   //求一列值中的最大值
min([distinct|all] <列名>)   //求一列值中的最小值

若指定 distinct 短语，则表示在计算时要取消指定列中的重复值

若不指定 distinct 短语或指定 all 短语（all 为默认值），则表示不取消重复值

当聚集函数遇到空值时，除 count(*) 外，都跳过空值只处理非空值。count(*) 是对元组进行计数，某个元组的一个或部分列取空值不影响 count 的统计结果

where 子句中不能用聚集函数作为条件表达式，聚集函数只能用于 select 子句和 group by 中的 having 子句

group by 子句

group by 子句将查询结果按某一列或多列的值分组，值相等的为一组

对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一组，即每一组都有一个函数值

where 子句与 having 短语的区别在于作用对象不同。where 子句作用于基本表或视图，从中选择满足条件的元组。having 短语作用于组，从中选择满足条件的组

连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询

连接查询是关系数据库中最主要的查询

连接查询包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询、复合条件连接查询

等值与非等值连接查询

连接查询的 where 子句中用来连接两个表的条件称为连接条件或连接谓词

[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>

[<表名1>.] <列名1> between [<表名2>.] <列名2> and [<表名2>.] <列名3>//连接谓词

等值连接：连接运算符为 =

非等值连接：连接运算符不是 =

自然连接：在等值连接中把目标列中重复的属性列去掉

连接谓词中的列名称为连接字段，连接条件中的各连接字段类型必须是可比的，但名字不必相同

一条 SQL 语句可以同时完成选择和连接查询，此时 where 子句是由连接谓词和选择谓词组成的复合条件，连接谓词在前，选择谓词在后

实现方法

• 嵌套循环连接算法：m*n
• 排序合并法：先排序，然后使用双指针，m+n
• 索引连接：使用索引进行查询

自身连接

一个表与其自己进行连接，称为表的自身连接，需要给表取别名以示区别

外连接

在通常的连接操作中，只有满足连接条件的元组才能作为结果输出

若想要将所有元组输出，则想要使用外连接

左外连接

select <目标列表达式>

from <表名1> left outer join <表名2> on <表名1>.<列名1>=<表名2>.<列名2>

左外连接=内连接+左边表中失配的元组

右外连接

select <目标列表达式>

from <表名1> right outer join <表名2> on <表名1>.<列名1>=<表名2>.<列名2>

右外连接=内连接+右边表中失配的元组

全外连接

select *

from <表名1> full outer join <表名2> on <表名1>.<列名1>=<表名2>.<列名2>

会保留表1和表2的全部数据，全外连接=内连接+左边表中失配的元组+右边表中失配的元组

多表查询

两个以上的表进行连接，使用 and 进行连接

嵌套查询

查询块：select - from - where

嵌套查询的出现的位置：where 子句或 having 短语中

允许多层查询，即一个子查询中可以嵌套其他子查询，但是子查询的 select 语句中不能使用 order by 子句， order by 子句只能对最终查询结果进行排序

分类：相关子查询与不相关子查询

不相关子查询：子查询的查询条件不依赖于父查询

相关子查询：子查询的查询条件依赖于父查询

带有 in 谓词的子查询—不相关

子查询的结果是集合时，使用 in

带有比较运算符的子查询—可相关可不相关

知道内层查询的结果是返回单个值

带有 any (some) 或 all 谓词的子查询

带有 exists 谓词的子查询

exists 代表存在量词 ∃ ，带有 exists 谓词的子查询不返回任何数据，只产生逻辑真值 ”true“ 或逻辑假值 ”false“

可以利用 exists 判断 x∈S、S⊆R、S=R、S∩R 非空等是否成立

使用存在量词 exists 后，若内层查询结果非空，则外层的 where 子句返回真值，否则返回假值

使用存在量词 not exists 后，若内层查询结果为空，则外层的 where 子句返回真值，否则返回假值

集合查询

select 语句的查询结果是元组的集合，所以多个 select 语句的结果可进行集合操作。集合操作主要包括并操作 union、交操作 intersect(in)、差操作 except(not in)

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

数据更新

数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据

插入数据

SQL 的数据插入语句 insert 通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组

插入元组

insert

into <表名> [(<属性列1>[,<属性列2>]...)]

values (<常量1>[,<常量2>]...);

将新元组插入指定表中，其中新元组的属性列1的值为常量1，属性列2的值为常量2，...

into 子句中没有出现的属性列（属性的顺序可以与 create table 中的顺序不一样），新元组在这些列上将取空值 。在表定义时说明了 not null 的属性列不能取空值，否则会出错

若 into 子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值

value 子句对新元组的各属性赋值，字符串常数要用单引号括起来

插入子查询结果

子查询不仅可以嵌套在 select 语句中用以构造父查询的条件，也可以嵌套在 insert 语句中用以生成要插入的批量数据

insert

into <表名> [(<属性列1>[,<属性列2>]...)]

子查询;

修改数据

update <表名>

set <列名>=<表达式>[,<列名>=<表达式>]...

[where <条件>];

修改指定表中满足 where 子句条件的元组，若省略 where 子句，则表示要修改表中的所有元组

修改某一个元组的值

修改多个元组的值

带子查询的修改语句

子查询可以嵌套在 update 语句中，构造修改的条件

删除数据

delete

from <表名>

[where <条件>];

delete 语句的功能是从指定表中删除满足 where 子句条件的所有元组

省略 where 子句表示删除表中全部元组，但表的定义仍在字典中，说明 delete 子句删除的是表中的数据，而非关于表的定义

drop 删除定义，delete 删除数据

删除某一个元组的值

删除多个元组的值

带子查询的删除语句

子查询可以嵌套在 delete 语句中，用以构造执行删除操作的条件

空值的处理

取空值的情况：

• 该属性应该有一个值，但目前不知道其具体值
• 该属性不应该有值
• 由于某种原因不便于填写

空值是一个很特殊的值，含有不确定性

空值的产生

向表中插入新的元组时，会产生空值

外连接也会产生空值

空值的运算关系会产生空值

空值的判断

判断一个属性的值是否为空值，用 is null 或 is not null 表示【不可用运算符】

空值的约束条件

属性定义（或者域定义）中有 not null 约束条件的不能取空值，码属性不能取空值

空值的算术运算、比较运算和逻辑运算

空值与另一个值（包括另一个空值）的算术运算的结果为空值

空值与另一个值（包括另一个空值）的比较运算的结果为 unknown

有了 unknown 后，传统的逻辑运算中的二值（true、false）逻辑就拓展成了三值逻辑

视图

定义：视图是从一个或几个基本表（或视图）导出的表

视图与基本表不同，是一个虚表

数据库中只存放视图的定义，而不存放视图对应的数据

基本表中的数据发生变化，从视图中查询出的数据随之改变

定义视图

建立视图

create view <视图名> [(<列名>[,<列名>]...)]

as <子查询>//可以是任意的 select 语句

[with check option];//表示对视图进行 update、insert 和 delete 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件

组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。若省略了视图的各个属性列名，则隐含该视图由子查询中 select 子句目标列中的诸字段组成

需明确指定组成视图的所有列名的情况：

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

关系数据库管理系统执行 create view 语句的结果只是把视图的定义存入数据字典，并不执行其中的 select 语句。在对视图查询时，才按视图的定义从基本表中将数据查出。

行列子集视图：视图从单个基本表导出，并且只是去掉了基本表的某些行和某些列，但保留了主码

分组视图：用带有聚集函数和 group by 子句的查询定义的视图

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上

虚拟列：派生属性

带虚拟列的视图也称为带表达式的视图

删除视图

drop view <视图名> [cascade]

视图删除后，视图的定义将从数据字典中删除

若该视图上导出了其他视图，则使用 cascade 级联删除语句把该视图和由其导出的所有视图一起删除

基本表删除后，由改基本表导出的所有视图均无法使用，但是视图的定义没有从字典中清除。删除这些视图定义需要显式地使用 drop view 语句

查询视图

视图定义之后，用户可以对视图进行查询

视图消解：关系数据库管理系统执行对视图地查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询

where 子句中不能用聚集函数作为条件表达式

目前多数关系数据库系统对行列子集视图的查询均能进行正确的转换，但对非行列子集视图的查询不一定可以做转换，故此类查询应该直接对基本表进行

定义视图并查询视图与基于派生表的查询是有区别的。视图一旦定义，其定义将永久保存在数据字典中，之后的所有查询都可以直接引用该视图。而派生表只是在语句执行时临时定义，语句执行后该定义即被删除

更新视图

定义：更新视图是指通过视图插入、删除和修改数据

由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新

关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新

一般行列子集视图是可更新的

DB2 对视图更新的规定：

1. 若视图是由两个以上基本表导出的，则此视图不允许更新
2. 若视图的字段来自字段表达式或常数，则不允许对此视图执行 insert 和 update 操作，但允许执行 delete 操作
3. 若视图的字段来自聚集函数，则此视图不允许更新
4. 若视图定义中含有 group by 子句，则此视图不允许更新
5. 若视图定义中含有 distinct 短语，则此视图不允许更新
6. 若视图定义中有嵌套查询，并且内层查询的 from 子句中涉及的表也是导出的该视图的基本表，则此视图不允许更新
7. 一个不允许更新的视图上定义的视图也不允许更新

不可更新的视图与不允许更新的视图是两个不同的概念，前者指理论上已证明其是不可更新的视图，后者指实际系统中不支持其更新，但本身有可能是可更新的视图

视图的作用

1. 视图能够简化用户的操作
2. 视图使用户能以多种角度看待同一数据
3. 视图对重构数据库提供了一定程度的逻辑独立性【尽管数据库的逻辑结构改变，但应用程序不必修改，因为新建立的视图定义为用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据】
4. 视图能够对机密数据提供安全保护
5. 适当利用视图可以更清晰地表达查询