数据库系统¶

第一章数据库概览¶

数据库发展三阶段¶

人工管理阶段：不能长期保存，不具有独立性，不共享

文件系统阶段：长期保存，独立性差，共享性差，冗余度大，

数据库系统阶段：特点：1.数据结构化 2.共享性高，冗余度低，易于扩充 3.数据独立性高 4.数据统一管理与控制

数据库系统组成¶

数据库

数据库管理系统（DBMS）

数据库的应用程序

数据库系统的人员：管理员（DBA） ...

三级模式与二级映像¶

三级模式：外模式，模式，内模式

模式：也称逻辑模式，一个数据库只有一个模式，是数据库全体数据的逻辑结构和特征的描述

外模式：也称子模式或用户模式，是模式的子集，一个数据库可以有多个外模式

内模式：也称存储模式，只有一个内模式，是数据物理结构和存储方式的描述

二级映像：

外模式/模式映像：模式改变时，对映像做出相应的改变可以使外模式保持不变，保证数据和程序的逻辑独立性

模式/内模式映像：内模式改变时，对映像做出相应的改变可以使模式保持不变，保证数据的物理独立性

数据库管理系统¶

功能：数据定义，数据操纵，数据库的事务管理和运行管理，数据库的创建和维护

数据模型¶

分类：概念数据模型，逻辑数据模型，物理数据模型

概念数据模型：ER图

ER模型三要素：实体，属性，联系

逻辑数据模型（一般都直接叫数据模型）：层次模型，网状模型，关系模型，面向对象模型

数据模型三要素：数据结构，数据操作，数据完整性约束

层次模型是一棵树，关系模型是一张二维表

第二章关系数据库模型¶

关系的性质¶

列是同质的，即数据类型相同

不同的列可以出其同一个域，但属性名要不同

列的顺序可以任意交换

任意两个元组不可以完全相同

元组顺序可以任意

分量必须取原子值，即要求每个分量都是不可再分的数据项

关系数据结构¶

一个关系就是一个规范化的二维表，笛卡尔积的有意义的有限子集

一个关系由关系名，关系模式，关系实例组成

元组，属性，码，域，分量，关系模式

候选码：唯一性，最小性

超码：可以唯一的确定一行，候选码是超码的子集

主码：如果由多个候选码，选一个

主属性：包含在候选码中的各属性

外码

关系数据语言的分类¶

关系代数，关系演算，域演算，SQL

关系操作 & 关系代数¶

传统的集合运算：笛卡尔积，并，交，差

专门的关系运算：选择，投影，连接（等值，自然连接，外连接，左外，右外），除法

数据完整性¶

实体完整性

参照完整性

用户自定义完整性

列级完整性约束只能涉及一个属性，表级可以涉及多个属性

第三章 SQL语言¶

SQL概述¶

特点：综合统一，高度非过程化，面向集合的操作方式，以同一种语法结构提供两种使用方式，语言简洁易学易用

数据定义（DDL），数据操纵（DML），数据控制（DCL）

巴科斯范式（BNF）¶

<> 必选项

[ ] 可以出现一次或不出现

{ } 可以多次出现或不出现

| 多选一

数据定义¶

数据库¶

create database <数据库名>
use <数据库名>
alter database <数据库名> 
drop database <数据库名>

基本表¶

定义

create table <表名> (
    <列名><数据类型>[<列级完整性约束>]
    {,<列名><数据类型>[<列级完整性约束>]}
    [,<表级完整性约束>]
)

列级约束：

not null, default, unique, check, primary key, foreign key

foreign key: foreign key (<列名>) references <外表名>(<外表列名>)

修改

alter table <表名>
alter column <列名> <新数据类型> [null | not null]
| add <列名> <数据类型> [约束]
| drop column <列名>
| add [constraint<约束名>] <约束定义>
| drop constraint <约束名>

删除

drop table <基本表名>

索引¶

分类：聚簇索引和非聚簇索引

默认非聚簇，聚簇索引只可以建立一个

聚簇索引：索引项顺序和物理顺序一致

定义

create [unique][clustered|notclustered] index <索引名>
on <基本表名>(<列名>[asc|desc] [{,(<列名>[asc|desc]}...])

删除

drop index <表名>.<索引名>

数据查询¶

select [all|distinct] <目标列表达式> [,<目标列表达式>]...
from <表名或视图名> [,<表名或视图名>]...
[where <条件表达式>]
[group by <列名1>]
[having <条件表达式>]   -- having 用于指定分组过滤条件
[order by <列名2>[asc|desc]]

更改结果列标题¶

select 客户编号 as cno
    from CustomerInfo

select 'cno' = 客户编号   # = 列名必须写右边
    from CustomerInfo

替换查询结果中的数据¶

select <列名> =
    case 
        when 条件1 then 表达式1
        ...
        else 表达式
    end
    from <表名>
    where <条件表达式>

去除重复行¶

select unique <列名>
    from <表名>

模糊查询¶

% 表示任意长度的字符串

_ 表示任意一个字符

select *
    from <表名>
    where <列名> like '江苏%'

如果查询的条件中包含通配符需要转义：escape\

where 微信号 like 'wxid\_%' escape'\'

空值比较¶

<表达式> is [not] null

对结果集排序¶

[order by <列名>[asc|desc]] [,<列名>[asc|desc]]...] # 默认 asc 升序

对结果集分类¶

group by <表达式>
# eg
select 商品类别, count(商品编号) as '种数'
    from GoodsInfo
    group by 商品类别

having筛选¶

having 语句用在 group by 子句后来进行筛选，只输出符合条件的

[having <条件>]
# eg
select 商品类别, count(商品编号) as '种数'
    from GoodsInfo
    group by 商品类别
    having count(*) > 1

聚合函数¶

聚合函数 不允许 嵌套

sum | avg ()
max | min ()
count ()
# eg
select count(客户编号) as '客户数'
    from OrderList

连接查询¶

连接谓词

from <表1> [表1别名], <表2> [表2别名][, <表3> [表3别名]...]

以join关键字连接

# 内连接 默认内连接 inner join (inner可以不写)
select ...   # 2个表
    from <表1> join <表2> on <条件>
    where ...

select ...   # 3个表
    from <表1> join <表2> join <表3> 
        on <条件1>
        on <条件2>
    where ... 

# 外连接 outer join
# 左外:left outer join 右外:right outer join 完全外:full outer join

嵌套查询¶

子查询不能包含 order by，即 order by 只能对最终查询排序

带 in 的子查询¶

where 所在省市 in
(子查询)

带比较运算符的子查询¶

where 数量 >
(子查询)   # 这里的子查询要求结果只有一个

带 ALL / ANY 谓词的子查询¶

用以解除比较运算符要求子查询的结果集元素只能有一个的限制

where 数量 > all
(子查询) 

# 等效于
where 数量 >
(带 MAX 的子查询) # 当然这里子查询只能查询一列

带 EXISTS 谓词的子查询¶

[not] exists (子查询)

集合查询¶

select 语句的查询结果是结果集，所以可以进行集合运算

并 UNION 交 INTERSECT 差 EXCEPT

这里的结果集列数必须相同，对应列的数据类型也必须相同

select ...
union
select ...

select ...
intersect
select ...

数据更新（插入&修改&删除）¶

数据插入¶

# 插入元组
insert into <表名> [(<列1> [,<列2>...])]
values (<常量1> [,<常量2>...])

# 插入子查询
insert into <表名> [(<列1> [,<列2>...])]
    <子查询>

数据修改¶

update <表名> [[as]<别名>]
    set <列名> = <常量> [, <列名> = <常量> ...]
    [where <条件表达式>]

数据删除¶

delete [from] <表名>
    [where <条件表达式>]

视图¶

虚表，可以用以保护数据安全和满足不同用户的需求

创建视图¶

create view <视图名> [(<列名>[,<列名>])] 
as
<select 查询语句>

修改视图¶

alter view <视图名> [(<列名>[,<列名>])] 
as
<select 查询语句>

删除视图¶

drop view <视图名>

视图查询¶

和基本表一致

视图更新¶

防止出现范围外的数据可以在定义视图时加上 with check option

插入数据，修改数据，删除数据都和基本表一致

但有很多限制，如果更新成功了会映射到基本表

第四章数据库设计¶

广义：数据库及其应用系统的设计，即设计整个的数据库应用系统

狭义：数据库本身的设计，即设计数据库的各级模式并建立数据库

数据库设计的 6 个阶段：

需求分析阶段：数据流图，数据字典

概念结构设计阶段：概念数据模型 ER 图

逻辑结构设计阶段

物理结构设计阶段

数据库实施阶段

数据库运行与维护阶段

概念结构设计¶

自顶向下，自底向上，ER设计方法，EER设计方法

由局部视图到全局概念结构：一次集成，多次集成

集成包括：合并，消除冗余

合并的三种类型的冲突：命名冲突，属性冲突，概念冲突

逻辑结构设计¶

步骤：

将概念模型转化为一般的关系，网状，层次模型

对数据模型进行优化

设计用户外模式

二元关系：1对1，1对多，多对多

第五章关系规范化理论¶

数据冗余和操作异常¶

数据冗余：同一数据在一个或多个数据文件中重复储存

数据冗余会导致的操作异常：插入异常，删除异常，修改异常

函数依赖¶

分类：

平凡函数依赖和非平凡函数依赖

部分函数依赖与完全函数依赖

传递函数依赖

多值依赖，平凡多值依赖

范式¶

第一范式：每个关系模式必定属于1NF

第二范式：没有部分函数依赖，属于2NF

第三范式：不存在传递函数依赖，属于3NF

BC范式：每个决定因素都含有候选码

第四范式：所有的非平凡多值依赖的决定因素都含有码

数据依赖公理系统¶

逻辑蕴含¶

设又满足函数依赖集 F 的关系模式 $R<U, F>$，对于 R 的任一关系 r，若一个关系中函数依赖 $X\rightarrow Y$ 都成立，则称逻辑蕴含$X\rightarrow Y$ ，记为 $F\Rightarrow X\rightarrow Y$

Armstrong 公理系统¶

用途：求给定关系模式的码，从一组函数依赖求得蕴含的函数依赖

对于关系模式 $R<U,F>$ ，有一下定律

自反律：若 $Y\subseteq X \subseteq U$，则 $F\Rightarrow X\rightarrow Y$

增广律：若 $F\Rightarrow X\rightarrow Y$，且 $Z\subseteq U$，则 $F\Rightarrow ZX\rightarrow ZY$

传递律：若 $F\Rightarrow X\rightarrow Y$ 及 $F\Rightarrow Y\rightarrow Z$，则 $F\Rightarrow X\rightarrow Z$

推理可得以下规则

合并规则：若 $X\rightarrow Y,X\rightarrow Z$，则 $X\rightarrow YZ$

伪传递规则：若 $X\rightarrow Y,WY\rightarrow Z$，则有 $WX\rightarrow Z$

分解规则：若 $X\rightarrow Y$，且 $Z\subseteq Y$，则有 $X\rightarrow Z$

函数依赖集的闭包¶

$X^+_F$ 称为属性集 $X$ 关于函数依赖集 $F$ 的闭包

求$X^+_F$ 的方法：在函数依赖 $F$ 找左侧是 X 的子集的依赖并不断扩大 $X$ 这个集合，直到不能扩大为止

最小依赖集¶

函数依赖集等价：如果 $G^+=F^+$，则称函数依赖集 $G$ 和 $F$ 等价 / 互为覆盖

最小依赖集：如果函数依赖集 $F$ 满足

$F $ 中任一函数依赖的右部仅有单一属性

$F$ 中不存在这样的函数依赖 $X\rightarrow A$，使得 $F$ 与 $F-\{X\rightarrow A\}$ 等价

$F$ 中不存在这样的函数依赖 $X\rightarrow A$，$X$ 有真子集 $Z$ 使得 $F$ 与 $F-\{X\rightarrow A\}\cup \{Z\rightarrow A\}$ 等价

则称 $F$ 是最小依赖集或最小覆盖，记为 $F_{min}$，即要求函数依赖集是最小的

求函数依赖集 $F$ 的 $F_{min}$ 的方法

先将函数依赖右侧右侧是多属性的分解开多个函数依赖

然后逐一检查每一个函数依赖，检查这个函数依赖是否可去

最后逐一检查每一个函数依赖的左侧中的每一个属性，检查单个属性是否可去

其他考点：计算码的算法

模式分解¶

无损连接性¶

掌握判断模式分解无损连接性的算法，略

函数依赖保持¶

略

第六章数据库应用开发¶

T-SQL¶

变量¶

declare @< 局部变量名> <数据类型> [, @< 局部变量名> <数据类型> ...]  # 声明
set @<局部变量名> = <表达式>                                     # 赋值
select @<局部变量名> = <表达式> [, @<局部变量名> = <表达式>]        # select 可以一次赋值多个变量
set @<局部变量名> = (select 查询)                               # 查询结果赋值
print 字符串 | 局部变量 | 全局变量 | 函数                          # 输出

流程控制语句¶

begin...end

begin   # 一个 begin...end 是一个语句块
    SQL语句1
    SQL语句2
    ...
end

if...else

if <条件表达式>
    { 语句1 | 语句块1 }   # 里面有 begin...end
[else
    { 语句2 | 语句块2 }]

while

while <逻辑表达式>
    { 语句1 | 语句块1 } # 里面有 begin...end

break / continue / return 略

函数¶

内置函数¶

略

用户自定义函数¶

标量函数

create function [<所有者>.]<函数名>
([ { @<参数名>[as]<参数类型>[=<默认值>]} [,....] ])
returns <返回参数类型>
[as]
begin
    <函数体>
    return <返回值表达式>
end

标量函数的调用

select @<变量名> = dbo.<函数名>(实参1,...,实参n)  # 方式1 select
exec @<变量名> = dbo.<函数名> 实参1,...,实参n     # 方式2 exec

内嵌表值函数

create function [<所有者>.]<函数名>
([ { @<参数名>[as]<参数类型>[=<默认值>]} [,....] ])
returns table
[as]
begin
    return (<select>语句)
end

内嵌表值函数的调用

# 只能通过 select
select* from <函数名>(参数表)

多语句表值函数

create function [<所有者>.]<函数名>
([ { @<参数名>[as]<参数类型>[=<默认值>]} [,....] ])
returns @reuturn_variable table <表的定义>
[as]
begin
    <函数体>
    return 
end

多语句表值函数

# 只能通过 select
select* from <函数名>(参数表)

删除函数

drop function {[<所有者>.]<函数名>} [,...n]

游标¶

游标类型¶

静态游标只读
动态游标能够反映对结果集中所做的更改
只进游标只支持从头到尾提取数据
键集驱动游标可以修改基本表中非关键字列的值，但不可以插入数据

声明游标¶

declare <游标名> cursor              
[local|global]                          -- 游标作用域
[forword_only|scroll]                   -- 游标移动方向
[static|keyset|dynamic|fast_forward]    -- 游标类型
[read_only|scroll_locks|optimistic]     -- 访问属性
[type_warning]                          -- 类型转换警告
for <select语句>
[for update [of<列名>[,...n]]            -- 可修改的列

打开游标¶

open { {[global]<游标名>} | @<游标变量名> }
-- eg
select '游标 KH_cur 数据行数' = @@CURSOR_ROWS

读取游标¶

fetch
[ [next|prior|first|last|absolute{n|@nvar}|relative{n|@nvar}]
    from]
{ {[global]<游标名>} | @<游标变量名> }
[into @variable_name[,...n]]

关闭游标¶

close { {[global]<游标名>}|@<游标变量名> }

释放游标¶

deallocate { {[global]<游标名>}|@<游标变量名>}

游标变量¶

略

游标函数¶

CURSOR_STATUS( 'loacl'|'global'|'variable', '游标名'|'游标变量名' )
-- eg
set @statusVar = CURSOR_STATUS('local','CUR')

存储过程¶

存储过程编译和优化后存放在数据库服务器上，执行一次后存放在高缓中

优点：

提高系统性能

代码复用，执行效率高

存储过程的定义¶

create proc[edure]<存储过程名>
[{@<参数><数据类型>}
[=default][output]]
[,...n1]
as <SQL语句> [...n2]

存储过程的执行¶

[exec[ute]] <存储过程名>
[[@<参数名>=] {<值>|@<变量>[output]|[default]} [,...n]]
-- deaflut 表示用默认值

删除同名存储过程¶

if exists (select name from sysobjects
          where name = 'eg' and type = 'P')
          drop procedure eg
-- 删除函数等同理

修改存储过程¶

alter proc[edure]<存储过程名>
[{@<参数><数据类型>}
[=default][output]]
[,...n1]
as <SQL语句> [...n2]

删除存储过程¶

drop proc[edure] {<存储过程名>}[,...n]

触发器¶

触发器的分类¶

触发类型：insert / update / delete
触发方式：after / instead of

创建触发器¶

create trigger <触发器名> on { <基本表>|<视图> }
-- 指定触发器名及操作对象
{for|after|instead of} {[delete][,][insert][,][update]}
-- 定义触发器的类型，默认是 after
as
[if update(<列名>)[{and|or}update(<列名>)] [,...n] ]
<SQL语句> [,...n]               -- 可包含一条或多条SQL语句

触发器的禁止与启用¶

alter table {enable|disable} <触发器名>

修改触发器¶

alter trigger <触发器名> on { <基本表>|<视图> }
-- 指定触发器名及操作对象
{for|after|instead of} {[delete][,][insert][,][update]}
-- 定义触发器的类型，默认是 after
as
[if update(<列名>)[{and|or}update(<列名>)] [,...n] ]
<SQL语句> [,...n]               -- 可包含一条或多条SQL语句

删除触发器¶

drop trigger {<触发器名>} [,...n]

数据库访问接口¶

开放数据库连接(ODBC)¶

组成：ODBC数据库应用程序，驱动程序管理器，驱动程序，数据源

5阶段：配置数据源，连接数据源，初始化应用程序，SQL处理，处理结束

优点：以统一的方式处理所有数据库

JDBC¶

组成：Java应用程序，JDBC驱动程序管理器，JDBC驱动程序，数据库

ADO.NET¶

数据库应用系统体系结构¶

体系结构：单用户模式，主从式多用户模式，C/S模式，B/S模式

第七章数据库保护¶

对数据库的破坏¶

非法用户
非法数据
各种故障
多用户的并发访问

数据库安全¶

SQLServer —— 用户权限操作 - 徐林俊

数据库的安全控制

用户识别与鉴别

-- 创建登录名
create login <登录名> with password = '<密码>', default_database = <关联的数据库>
-- 创建用户名
create user <用户名> for login <登录名> # 登录名与用户名关联

访问控制

-- 授权
grant <权限> [,<权限>...] # all privileges
    on <对象类型> <对象名> [, <对象类型> <对象名>...]
    to <用户> [, <用户>...]
    [with grant option] # 允许转授
-- 收回权限
revoke <权限> [,<权限>...]
    on <对象类型> <对象名> [, <对象类型> <对象名>...]
    from <用户> [, <用户>...] [cascade] # cascade 表示级联回收

视图机制
安全审计机制

SQL Server的安全机制

身份验证模式
登录和用户
权限管理
角色管理

数据库完整性¶

准确性，有效性，相容性

数据库完整性控制¶

实体完整性
参照完整性
用户自定义完整性

SQL Server 的完整性机制¶

约束 Constraint¶

not null
primary key
check
foreign key
default
unique

check 约束

约束字段所允许的范围

[constraint <约束名>] check(<条件>)

unique 约束

[constraint <约束名>] unique

规则 Rule¶

创建规则

create rule <规则名> as <条件表达式>

绑定规则

[exec[ute]] sp_bindrule [@rulename=] '<规则名>' ,[@objanme] '<绑定对象名>'

解绑规则

[exec[ute]] sp_unbindrule [@objanme] '<绑定对象名>'

删除规则
```
drop rule <规则名> [,...]
```

默认 Default¶

略

并发控制¶

事务：是用户定义的一个数据库操作序列，要么全做，要么都不做，是一个不可分割的工作单位，是DBMS的基本单位

事务的 ACID 性质¶

原子性
一致性
隔离性
持久性

事务的活动过程¶

4 个状态

事务开始
事务读 / 写
事务提交 (COMMIT)
事务回滚 (ROLLBACK)

事务的并发执行¶

丢失更新
读 “脏” 数据
不可重复读

并发调度的可串行性¶

事务的并发调度是正确的当且仅当其结果与串行调度执行的结果相同，可串行化是并发控制的正确性的准则

封锁¶

封锁类型：

排他锁：写锁或 X 锁，自己可读可写，其他事务无法对数据进行加锁或操作
共享锁：读锁或 S 锁，自己只可读，其他事务只能加 S 锁

封锁协议：

一级封锁协议：对数据进行写操作时必须加X锁，事务结束释放，解决‘丢失更新’
二级封锁协议：一级封锁协议再加上在读数据前加上 S 锁，读完即释放，进一步解决‘读脏数据’
三级封锁协议：一级封锁协议再加上在读数据前加上 S 锁，事务结束释放，进一步解决‘不可重复度’
两段锁协议：所有事务必须分两个阶段对数据加锁和解锁，即同意上锁，然后再统一解锁

活锁 & 死锁：

活锁：在封锁过程中某个事务永远处于等待的状态而得不到封锁机会

解决方法：先来先服务

死锁：若干事务都处于等待状态，相互等待对方释放锁

解决方法：预防法：顺序申请法，一次申请法

解除法：定时法，死锁检测法

数据库恢复¶

故障种类¶

系统故障
事务故障
介质故障
计算机病毒
误操作
自然灾害
盗窃

数据库恢复技术¶

把数据库从错误状态恢复到某个正确的状态

数据库恢复机制的两个方面：一是建立冗余数据，二是系统出现故障后利用冗余数据将数据库恢复到某个正常的状态

备份：数据转存，登录日志文件

参考¶

数据库基础教程（第三版）顾韵华电子工业出版社

数据库系统¶

第一章 数据库概览¶

数据库发展三阶段¶