数据库系统概论复习笔记

Posted on | In , | |

Abstract: 数据库系统概论期末复习笔记 ~

Part1 数据库概述

关系模型的构成：关系数据结构、关系操作集合、关系完整性约束。

1.1 数据库发展

1.数据库发展过程：

数据库发展过程

2.三个时期的比较：

三个时期的比较

1.2 基本概念

1.数据模型

三个要求：

  • 真实模拟现实社会
  • 容易理解
  • 便于在计算机上实现

两类：

  • 概念模型：= 信息模型；面向用户，按用户的观点来对数据和信息建模，主要用于数据库设计
  • 逻辑模型/物理模型：对数据最底层的抽象，描述数据在系统内部的表示方式和存取方式，面向计算机系统；包括层次模型，网状模型，关系模型，面向对象模型，对象关系模型

三要素：

  • 数据结构
  • 数据操作
  • 数据的完整性约束条件

常用数据模型：

  • 层次
  • 网状
  • 关系
  • 面向对象
  • 对象关系
  • 半结构化

2.四个基本概念

数据Data

数据库Database：长期储存在计算机内、有组织的、可共享的大量数据的集合。

人员数据视图:

人员数据视图

数据库结构图：

数据库结构图

基本特征：

  • 数据按一定数据模型组织、描述和储存
  • 可为各种用户共享
  • 冗余度较小
  • 数据独立性较高
  • 易扩展

数据库管理系统DBMS：

  • 位于用户与操作系统之间的一层数据管理软件.是基础软件,是一个大型复杂的软件系统
  • 用途：科学地组织和存储数据、高效地获取和维护数据

  • 数据控制功能：

    • 数据的安全性（Security）保护:保护数据,以防止不合法的使用造成的数据的泄密和破坏.
    • 数据的完整性（Integrity）检查:将数据控制在有效范围内,或保证数据之间满足一定关系
    • 并发（Concurrency）控制:对多用户的并发操作加以控制和协调,防止相互干扰而得到错误的结果
    • 数据库恢复（Recovery）:将数据库从错误状态恢复到某一已知的正确状态

  • 主要功能：数据定义，数据操纵（增删查改），数据控制（数据的完整性、安全性定义与检查数据库的并发控制与故障恢复），数据服务（拷贝、转储、重组、性能检测、分析……）

数据库系统DBS：

  • 在计算机系统中引入数据库后的系统构成
  • 组成：数据库，数据库管理系统，应用系统，数据库管理员
  • 特点：数据结构化；数据的共享性高，冗余度低,易扩充；数据独立性高；数据由DBMS统一管理和控制

3.数据独立性（两个独立性）

  • 物理独立性：指用户的应用程序与存储在磁盘上的数据库中数据是相互独立的.当数据的物理存储改变了,应用程序不用改变
  • 逻辑独立性：指用户的应用程序与数据库的逻辑结构是相互独立的.数据的逻辑结构改变了,用户程序也可以不变
  • 物理独立性与逻辑独立性保证了数据独立性也是由DBMS的二级映像功能来保证的

4.信息世界基本概念

实体：客观存在并可相互区别的事物。可以是具体的人、事、物或抽象的概念。

属性：实体所具有的某一特性称为属性.一个实体可以由若干个属性来刻画。

码：唯一标识实体的属性集。

域(Domain) 属性的取值范围称为该属性的域。

实体型(Entity Type) ：用实体名及其属性名集合来抽象和刻画同类实体称为实体型。

实体集(Entity Set) 同一类型实体的集合称为实体集

联系(Relationship)现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系。

  • 实体内部的联系通常是指组成实体的各属性之间的联系
  • 实体之间的联系通常是指不同实体集之间的联系

A.两个实体型：

  • 一对一联系（1：1）：如果对于实体集A中的每一个实体,实体集B中至多有一个（也可以没有）实体与之联系,反之亦然,则称实体集A与实体集B具有一对一联系,记为1:1。
  • 一对多联系（1：n）：一个班级中有若干名学生,每个学生只在一个班级中学习
  • 多对多联系（m：n）：课程与学生之间的联系：一门课程同时有若干个学生选修,一个学生可以同时选修多门课程

联系名

B.两个以上实体型：

2+

5.数据库管理员的具体职责（DBA）

  • 决定数据库中的信息内容和结构
  • 决定数据库的存储结构和存取策略
  • 定义数据的安全性要求和完整性约束条件

  • 监控数据库的使用和运行

    • 周期性转储数据库

      • 数据文件
      • 日志文件

    • 系统故障恢复

    • 介质故障恢复
    • 监视审计文件
    • 数据库的改进和重组：性能监控和调优定期对数据库进行重组织,以提高系统的性能需求增加和改变时,数据库须需要重构造

1.3 关系数据库理论

1. 关系模式（五元组）

  • 格式：R（U，D，DOM，F）
  • R：关系
  • U：属性
  • D：域
  • DOM：属性到域的映射
  • 属性组U上的一组数据依赖F(如函数依赖(Functional Dependency,FD),多值依赖(Multivalued Dependency,MVD))

2. 相关概念

A.函数依赖：

  • R(U)是属性集U上的关系模式.X,Y是U的子集.若R(U)的任意一个可能的关系r,r中不可能存在两个元组在X上的属性住相等,而在Y上的属性值不等,则X函数确定Y或Y依赖于X,记做X→Y,若X→Y,Y→X,记做X←→Y(非主属性中某属性值唯一)
  • 非平凡函数依赖:X→Y,但X不包含Y,则称X→Y非平凡函数依赖(例子 (Sno,Cno)→Grade )
  • 平凡函数依赖:X→Y,且X包含Y则称X→Y平凡函数依赖(例子 (Sno,Cno)→Cno )
  • 完全函数依赖:X→Y且对X的任何一个真子集X’,都有X’→Y不成立,则称Y对X完全函数依赖(例子 (Sno,Cno)→Grade )
  • 部分函数依赖:X→Y,但Y不完全依赖于X,则称Y对X部分函数依赖(例子 (Sno,Cno)→Sdept [由Sno便可推出Sdept] )
  • 传递函数依赖:在R(U)中,如果X→Y,(X不包含Y),Y→X不成立,Y→Z,Z不属于Y,则称Z对X传递函数依赖,记做X→Z(例子 Sno→Sdept, Sdept→Mname成立,所以Sno→Mname)

B.多值依赖：

定义:

  • 形式1:设R(U)是属性集U上的一个关系模式.X,Y,Z是U的子集,并且Z=U-X-Y,感谢模式R(U)中多值依赖X→→Y成立,当且仅当对R(U),的任一关系r,给定的一对(x,z)值,有一组Y的值,这组值仅仅决定于x值而与z值无关
  • 形式2:在R(U)的任一关系r中,如果存在元组t,s使得t[X]=s[X],那么必然存在元组w,v属于r,(w,v可以与s,t相同),使得w[X]=v[X]=t[X],而w[Z]=v[Z]=t[Z],v[Y]=s[Y],v[Z]=tZ则Y多值依赖与X记做X→→Y,这里X,Y是U的子集,Z=U-X-Y

平凡多值依赖: 若X→→Y,而Z=φ,即Z为空,则称X→→Y为平凡多值依赖

多值依赖性质:

  • 多值依赖具有对称性.即若X→→Y,则X→→Z其中Z=U-X-Y
  • 多值依赖具有传递性,即若X→→Y,X→→Z,则X→→Z - Y,X→→Y - Z
  • 函数依赖可以看做多值依赖的特殊情况,即若X→Y则X→→Y.这是因为当X→Y时,对X的每一个值x,Y有一个确定的值y与之对应,所以X→→Y
  • 若X→→Y,X→→Z,则X→→YZ
  • 若X→→Y,X→→Z,则X→→Y∩Z

多值依赖与函数依赖的区别:

  • 多值依赖的有效性与属性集的范围有关
  • 若X→→Y在U上成立则在W(U包含W, W包含XY)上一定成立,反之则不然,即X→→Y在W(U包含W)上成立,在U上并不一定成立,这是因为多只依赖的定义中不仅涉及属性组X和Y,而且涉及U中的其余属性Z
  • 一般得在R(U)上若有X→→Y在W(U包含W)上成立,则称X→→Y为R(U)的嵌入型多值依赖
  • 但是在关系模式R(U)中函数依赖X→Y的有效性仅决定于X,Y这两个属性集的值.只要在R(U)的任何一个关系r中,元组在X和Y上的值满足函数依赖的定义,则函数依赖X→Y在任何属性集W(U包含W, W包含XY)上成立
  • 若函数依赖X→Y在R(U)上成立,则对任何Y的子集Y’具有X→Y’成立,而多值依赖X→→Y若在R(U)上成立,却不能保证对于任何一个Y’ 即Y的子集的X→→Y’都成立

码：

  • 候选码，主码：设K为R中的属性组合,若K完全依赖于U则K为R的候选码(Candidate key),若候选码多于一个,则选定一个为主码(Primary key)。【唯一标识实体的属性或属性集为候选码，可以有多个；主码只能有一个；主码一定是候选码，候选码不一定是主码】
  • 主属性，非主属性，全码：包含在任何一个候选码中的属性,称为主属性(Primary attribute).不包含在任何码中的属性称为非主属性(Nonprime attribute)或非码属性(Non-key attribute).最简单的情况,单个属性是码.最极端的情况,整个属性组都是码,称为全码(All-key)

3. 模式存在问题

  • 数据库冗余太大
  • 更新异常
  • 插入异常
  • 删除异常

4.范式（规范化）

第一范式1NF：每一个分类必须是一个不可分的数据项,则属于第一范式规范。

2NF：若R属于1NF, 且存在非主属性完全函数依赖于码。

3NF：R属于2NF，且非主属性既不部分依赖于码，也不传递依赖于码。

BCNF：属于3NF，且多有属性都不部分依赖或传递依赖于码，所有决定属性集都包含码。

4NF：所有非平凡的多值依赖都是函数依赖。

5NF：连接依赖均由候选码所蕴含。

范式包含关系

范式规范化过程：

范式规范化过程

1.4 关系模型

1.关系模型

从用户角度看，关系模型中数据的逻辑结构时一张二维表，由行和列组成。

2.相关概念

  • 元组Tuple：表中的一行即为一个元组（包含不同属性集的值）
  • 关系Relation：对应一张表；要求关系的每个分量都是不可分的数据项，不允许表中表
  • 属性Attribute：表中的一列为一个属性
  • 主码key：表中的某个属性组，可唯一确定一个元组
  • 域domain：属性的取值范围
  • 分量：元组中的一个属性值
  • 关系模式：对关系的描述；关系名（属性1，属性2，…）

3.完整性约束

  • 作用：【约束】保证数据库中数据的正确性和相容性

  • 包括：

    • 域完整性约束：保证数据库字段取值的合理性；包括检查（CHECK）、默认值（DEFAULT）、不为空（NOT NULL）、外键（FOREIGN KEY）等约束。
    • 实体完整性(Entity integrity): 指关系的主关键字[主码]不能重复也不能取“空值“
    • 参照完整性: 定义建立关系之间联系的主关键字与外部关键字引用的约束条件
    • 用户定义的完整性(user defined integrity)：实体完整性和参照完整性适用于任何关系型数据库系统，它主要是针对关系的主关键字和外部关键字取值必须有效而做出的约束；用户定义的完整性是根据应用环境的要求和实际的需要，对某一具体应用所涉及的数据提出约束性条件，这一约束机制一般不应由应用程序提供，而应有由关系模型提供定义并检验，用户定义完整性主要包括字段有效性约束和记录有效性。

4.关系模型的优缺点

优点：

  • 建立在严格的数学概念的基础上
  • 概念单一: 实体和各类联系都用关系来表示；对数据的检索结果也是关系
  • 关系模型的存取路径对用户透明: 具有更高的数据独立性,更好的安全保密性；简化了程序员的工作和数据库开发建立的工作

缺点：

  • 存取路径对用户透明导致查询效率往往不如非关系数据模型
  • 为提高性能,必须对用户的查询请求进行优化增加了开发DBMS的难度

5.系统结构解释

从数据库管理系统角度看数据库系统通常采用三级模式结构,是数据库系统内部的系统结构。

从数据库最终用户角度看(数据库系统外部的体系结构),数据库系统的结构分为:

  • 单用户结构主从式结构
  • 分布式结构
  • 客户／服务器
  • 浏览器／应用服务器／数据库服务器多层结构等

6.实例instance

  • 模式的一个具体值
  • 反映数据库某一时刻的状态
  • 同一个模式可以有很多实例
  • 实例随数据库中的数据的更新而变动

7.模式Schema（又称：逻辑模式）

解释：

  • 模式：数据库中全体数据的逻辑结构和特征的描述
  • 反映：数据的逻辑结构及其联系
  • 所有用户的公共数据视图，综合所有用户的需求

说明：一个数据库只有一个模式

地位：数据库系统模式结构的中间层

特点：

  • 与数据的物理存储细节和硬件环境无关
  • 与具体的应用程序、开发工具个高级程序设计语言无关

定义：

  • 数据的逻辑结构（数据项的名称、类型、取值范围等）
  • 数据之间的联系
  • 数据有关的安全性、完整性要求

8.外模式(External Schema)

说明：

  • 数据库用户（包括应用程序员和终端用户）使用的局部数据的逻辑结构和特征的描述
  • 数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

地位：介于模式与内模式之间

模式与外模式的关系：一对多

  • 外模式是模式的子集
  • 一个数据库可以有多个外模式，反映不同用户的应用需求、看待数据的方式、对数据保密的要求
  • 对模式中同一数据，在外模式中的结构、类型、长度、保密级别都可以不同

外模式与应用的关系：一对多

  • 同一外模式也可以为某一用户的多个应用系统所使用
  • 但一个应用程序只能使用一个外模式

用途:

  • 保证数据库安全性的一个有力措施
  • 每个用户只能看见和访问所对应的外模式中的数据

9.内模式（internal schema，又称存储模式）

说明：

  • 是数据物理结构和存储方式的描述
  • 一个数据库只有一个内模式

  • 是数据在数据库内部的表示方式：

    • 记录的存储方式：顺序存储，hash存储等
    • 索引的组织方式
    • 数据是否压缩存储
    • 数据是否加密
    • 数据存储记录结果的规定

10.三级模式结构（数据库系统的系统结构）

组成：内模式，模式，外模式

三级模式结构

同一个模式可以有任意多个外模式

每一个外模式,数据库系统都有一个外模式／模式映象,定义外模式与模式之间的对应关系

11.二级映象

作用：二级映像在DBMS内部实现这三个抽象层次的联系和转换。

说明：映象定义通常包含在各自外模式的描述中

外模式/模式映像：

  • 作用：保证数据的逻辑独立性

  • 说明：

    • 外模式描述的是数据的局部逻辑结构
    • 模式描述的是数据的全局逻辑结构
    • 当模式改变时,数据库管理员修改有关的外模式／模式映象,使外模式保持不变，应用程序是依据数据的外模式编写的,从而应用程序不必修改,保证了数据与程序的逻辑独立性,简称数据的逻辑独立性

模式／内模式映像:

  • 作用:
    保证数据的物理独立性
  • 说明: 当数据库的存储结构改变了（例如选用了另一种存储结构）,数据库管理员修改模式／内模式映象,使模式保持不变，应用程序不受影响.保证了数据与程序的物理独立性,简称数据的物理独立性

12 触发器

触发器又叫做事件-条件-动作（event-condition-action）规则。当特定的系统事件发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，通常是一段SQL存储过程。

触发器类型

  • 行级触发器（FOR EACH ROW）
  • 语句级触发器（FOR EACH STATEMENT）

激活触发器

  • 触发器的执行，是由触发事件激活的，并由数据库服务器自动执行
    一个数据表上可能定义了多个触发器，遵循如下的执行顺序:

（1） 执行该表上的BEFORE触发器;
（2） 激活触发器的SQL语句;
（3） 执行该表上的AFTER触发器。

1.5 SQL概述

1.SQL的特点

综合统一：

  • 集数据定义语言（DDL）,数据操纵语言（DML）,数据控制语言（DCL）功能于一体

  • 可以独立完成数据库生命周期中的全部活动：

    • 定义关系模式,插入数据,建立数据库；
    • 对数据库中的数据进行查询和更新；
    • 数据库重构和维护
    • 数据库安全性、完整性控制等

  • 用户数据库投入运行后,可根据需要随时逐步修改模式,不影响数据的运行

  • 数据操作符统一

高度非过程化：

  • 非关系数据模型的数据操纵语言“面向过程”,必须指定存取路径
  • SQL只要提出“做什么”,无须了解存取路径
  • 存取路径的选择以及SQL的操作过程由系统自动完成

面向集合的操作方式：

  • 非关系数据模型采用面向记录的操作方式,操作对象是一条记录
  • SQL采用集合操作方式: 操作对象、查找结果可以是元组的集合；一次插入、删除、更新操作的对象可以是元组的集合

以同一种语法结构提供多种使用方式：

  • SQL是独立的语言:能够独立地用于联机交互的使用方式
  • SQL又是嵌入式语言:SQL能够嵌入到高级语言（例如C,C++,Java）程序中,供程序员设计程序时使用

语言简洁，易学易用：SQL功能极强,完成核心功能只用了9个动词

2.SQL的数据定义功能

SQL的数据定义语句

3.三个基本概念

1.基本表：

  • 本身独立存在的表
    SQL中一个关系就对应一个基本表
    一个(或多个)基本表对应一个存储文件
    一个表可以带若干索引

2.存储文件:

  • 逻辑结构组成了关系数据库的内模式
  • 物理结构是任意的,对用户透明

3.视图:

  • 从一个或几个基本表导出的表
  • 数据库中只存放视图的定义而不存放视图对应的数据
  • 视图是一个虚表
  • 用户可以在视图上再定义视图

4.SQL支持关系数据库三级模式结构

SQL支持关系数据库三级模式结构

5.标识符

定义：由用户定义的可识别的字符序列；标识某个实体的一个符号

规则:

  • 第一个字符必须是字母或下划线（_）或@或#；
  • 后续字符可以是：字母、数字、_、#、$、@等；

注意: 不能使用SQL中的关键字和运算符,不允许嵌入空格或其他特殊字符

Part2 数据库设计

2.1 总体设计过程

1.数据库设计步骤

数据库设计步骤

数据库设计步骤2

2.设计描述

设计描述

数据库设计3

3.数据库设计的特点

数据库设计的特点

2.2 需求分析

1.分析和表达用户需求

首先把任何一个系统都抽象为：

抽象

分解处理功能和数据:

  • 分解处理功能: 将处理功能的具体内容分解为若干子功能
  • 分解数据: 处理功能逐步分解同时,逐级分解所用数据,形成若干层次的数据流图

  • 表达方法:

    • 处理逻辑：用判定表或判定树来描述
    • 数据：用数据字典来描述

  • 将分析结果再次提交给用户,征得用户的认可

2.任务

通过调查,收集与分析数据,获得用户对数据要求:

  • 信息要求: 指用户需要从数据库中获得信息的内容与性质,再由信息要求导出数据要求
  • 处理要求: 指用户要完成什么处理功能,对初一响应时间有什么要求,处理方式是批处理还是联机处理
  • 安全性与完整性要求

3.需求分析过程

需求分析过程

4.数据流图

符号说明：

符号说明

例子：

示例

数据字典：

  • 与数据流图的区别

    • 数据流图 — 表达了数据和处理的关系
    • 数据字典 — 则是系统中各类数据描述的集合

2.3 概念结构设计

1.四类方法

信息世界-概念结构

1.自顶向下: 即首先定义全局概念结构的框架,然后逐步细化

自顶向下

2.自底向上：即首先定义个局部应用的概念结构,然后将他们集合起来,得到全局概念

自底向上

3.逐步扩展：首先定义最重要的核心概念结构,然后向外扩充,以滚球的方法逐步生成其他概念结构,直至总体概念结构

逐步扩展

4.混合策略：即将自顶向下和自底向上相结合,用自顶向下策略设计一个全局概念结构框架,以它为骨架集成由底向上策略中设计的个局部概念结构

混合策略

3.三种抽象

1.分类classification：

  • 定义某一类概念作为现实世界中一组对象的类型
  • 抽象了对象值和型之间的“is member of”的语义

2.聚集aggregation：

  • 定义某一类型的组成成分
  • 抽象了对象内部类型和成分之间“is part of”的语义
  • 复杂的聚集,某一类型的成分仍是一个聚集

聚集-图例

3.E-R图：

任务:

  • 将各局部应用涉及的数据分别从数据字典中抽取出来
  • 参照数据流图,标定各局部应用中的实体、实体的属性、标识实体的码
  • 确定实体之间的联系及其类型（1:1,1:n,m:n）

两条准则:

  • 属性不能再具有需要描述的性质.即属性必须是不可分的数据项,不能再由另一些属性组成
  • 属性不能与其他实体具有联系.联系只发生在实体之间

2.4 逻辑结构设计

1.E-R图与关系模型转换

  • 转换内容: 将实体、实体的属性和实体之间的联系转换为关系模式
  • 转换原则: 一个实体转换为一个关系模式；实体的属性即为关系的属性；实体的码即为关系的码

2.E-R图实体型间的联系有以下不同情况

  • 一个1:1联系可以转换为一个独立的关系模式,也可以与任意一端对应的关系模式合并: 转换为一个独立的关系模式；与某一端实体对应的关系模式合并
  • 一个1:n联系可以转换为一个独立的关系模式,也可以与n端对应的关系模式合并: 转换为一个独立的关系模式；与n端对应的关系模式合并
  • 一个m:n联系转换为一个关系模式：三个或三个以上实体间的一个多元联系转换为一个关系模式

  • 具有相同码的关系模式可合并:

    • 目的：减少系统中的关系个数
    • 合并方法： 将其中一个关系模式的全部属性加入到另一个关系模式中,然后去掉其中的同义属性（可能同名也可能不同名）,并适当调整属性的次序

3.优化数据模型方法

  • 确定数据依赖
  • 对于各个关系模式之间的数据依赖进行极小化处理,消除冗余的联系.
  • 确定各关系模式分别属于第几范式.
  • 分析对于应用环境这些模式是否合适,确定是否要对它们进行合并或分解.
  • 对关系模式进行必要的分解或合并

4.设计用户子模式

  • 使用更符合用户习惯的别名
  • 针对不同级别的用户定义不同的外模式,以满足系统对安全性的要求.
  • 简化用户对系统的使用

5.任务

  • 将概念结构转化为具体的数据模型
  • 逻辑结构设计的步骤
  • 将概念结构转化为一般的关系、网状、层次模型
  • 将转化来的关系、网状、层次模型向特定DBMS支持下的数据模型转换
  • 对数据模型进行优化
  • 设计用户子模式

6.逻辑结构设计时3个步骤

逻辑结构设计时3个步骤

2.5 数据库物理设计

1.步骤

  • 确定数据库的物理结构,在关系数据库中主要指存取方法和存储结构
  • 对物理结构进行评价,评价的重点是时间和空间效率

2.索引存取

  • 选择索引存取方法的一般规则:

    • 如果一个(一组)属性经常在查询条件中出现,则考虑在这个(这组)属性上建立索引(组合索引)
    • 如果一个属性经常作为最大值和最小值等聚集函数的参数,则考虑在这个属性上建立索引
    • 如果一个(或一组)属性经常在连接操作的连接条件中出现,则考虑在这个(或这组)属性上建立索引

  • 关系上定义的索引数过多会带来较多的额外开销:

    • 维护索引的开销
    • 查找索引的开销

3.聚簇

用途:

  • 大大提高按聚簇码进行查询的效率
  • 节省存储空间

局限性:

  • 聚簇只能提高某些特定应用的性能
  • 建立与维护聚簇的开销相当大

适用范围:

  • 既适用于单个关系独立聚簇,也适用于多个关系组合聚簇
  • 当通过聚簇码进行访问或连接是该关系的主要应用,与聚簇码无关的其他访问很少或者是次要的时,可以使用聚簇

2.6 数据库实施

2.7 数据库运行和维护

Part3 数据查询、数据更新、触发器

3.1 数据查询

0.select

1.SELECT column_name,column_name
FROM table_name; 选取特定列

2.SELECT * FROM table_name; 选取所有列

3.distinct关键词：返回唯一不同值（筛掉重复值，只列出不同的值）

SELECT DISTINCT column_name,column_name
FROM table_name;

1.聚集函数

COUNT(列个数或元组个数)

SUM(列值总和)

AVG(列平均值)　　　　　　　+ ([DISTINCT|ALL] <列名>)

MAX(列中最大值)

MIN(列中最小值)

VARIANCE(列的标准方差)

STDDEV(列的标准差)

2.where语句

where

where <属性列名> [not] between A and B

where <属性列名>[not] in … (SELECT语句)/(<值1>)[,<值2>]……..)

where <属性列名> [NOT]LIKE <匹配串>

where <属性列名> IS [NOT] NULL

[NOT] EXISTS (SELECT语句)

<条件表达式> AND / OR <条件表达式> [ AND / OR <条件表达式>]……..

3.ORDER BY 子句:

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

说明:

  • 升序：ASC;(缺省值)
  • 降序：DESC;

  • 当排序列含空值时:

    • ASC：排序列为空值的元组最后显示
    • DESC：排序列为空值的元组最先显示

4.GROUP BY 子句

细化聚集函数的作用对象

5.(not) exists

带有 EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值 “true” 或逻辑假值 “false”

eg.查询没有选修1号课程的学生姓名.

SELECT Sname
FROM Student
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno=’1’);

6.连接查询

同时涉及多表的查询。

格式:

  • [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
  • [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

等值连接： =

eg.查询每个学生及其选修课程情况：

select student., SC.
from student, SC
where student.sno = SC.sno

自然连接

说明:在等值连接中把目标列中重复的属性列去掉

自身连接

说明:一个表与其自己进行连接

要求:需要给表起别名以示区别,由于所有属性名都是同名属性,因此必须使用别名前缀

例子:查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

复合条件连接

说明:WHERE子 句中含多个连接条件

例子:查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno/ 连接谓词/
AND SC.Cno=’2’AND SC.Grade > 90;/ 其他限定条件 /

嵌套查询

概述:

  • 一个 SELECT-FROM-WHERE 语句称为一个查询块,将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询
  • 上层查询快称为外层查询或父查询,下层查询块称为内层查询或子查询

1.不相关子查询

要求: 子查询的查询条件不依赖于父查询

实现: 由里向外逐层处理.即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件

eg.查询选修了2号课程的所有学生姓名

select sname
from student
where sno in (select sno
from SC
where cno = ‘2’);

2.相关子查询:

要求:子查询的查询条件依赖于父查询

实现: 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表,然后再取外层表的下一个元组,重复这一过程,直至外层表全部检查完为止

例子:找出每个学生超过他选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC x
WHERE Grade >=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);

3.2 数据更新

数据插入

1.插入元组

insert into <表名> [(<属性列1>[,<属性列2 >]…)]
VALUES (<常量1> [,<常量2>] … )

eg.将一个新学生元组（学号：200215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中

insert into student (‘xxxx’,’xxxx’,’xxx’,18)

2.插入子查询结果:

INSERT INTO <表名> [(<属性列1>[,<属性列2 >]…)]
子查询

eg.

insert into Dept_age(Sdept, Avg_age)
select Sdept, AVG(Sage)
from Student
group by Sdept;

数据更改

UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>];

eg.将学生 200215121 的年龄改为22岁(修改某一个元组的值)

UPDATE Student
SET Sage=22
WHERE Sno=’ 200215121 ‘;

将所有学生的年龄增加1岁(修改多个元组的值)

UPDATE Student
SET Sage= Sage+1;

将计算机科学系全体学生的成绩置零(修改多个元组的值)

UPDATE SC
SET Grade=0
WHERE ‘CS’= (SELETE Sdept
FROM Student
WHERE Student.Sno = SC.Sno);

数据删除

DELETE
FROM <表名>
[WHERE <条件>];

eg.删除学号为 200215128 的学生记录(删除某一个元组的值)

DELETE
FROM Student
WHERE Sno=’200215128’;

删除所有的学生选课记录(删除多个元组的值)

DELETE
FROM SC;

删除计算机科学系所有学生的选课记录(带子查询的删除语句)

DELETE
FROM SC
WHERE ‘CS’= (SELECT Sdept
FROM Student
WHERE Student.Sno=SC.Sno);

3.3基本表，表完整性

基本表

1.定义基本表

create table <表名>
（<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ] …
[,<表级完整性约束条件> ] ）;

约束类型：

Primary key　　定义主键,保证主键列无重复值　　　　　　实体完整性
Unique　　　　 保证该列无重复值　　　　　　　　　 　　实体完整性
Foreign key　　 定义外键,保证数据表间数据的一致性　　　参照完整性
Check 　　　　 定义表中某些列的数据范围　　　　　　　 自定义完整性
Default　　　　 为列的数据提供默认值　　　　　　　　　 自定义完整性

数据基本类型:

数据类型　　　　　 含义　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
CHAR(n)　　　　　 长度为n的定长字符串
VARCHAR(n)　　　 最大长度为n的变长字符串,实际存储有效长度
INT　　　　　　　　长整数（也可以写作 INTEGER）
SMALLINT　　　　 短整数
NUMERIC(p,d)　 定点数,由p位数字（不包括符号、小数点）组成,小数后面有d位数字
REAL 　　　　　　 取决于机器精度的浮点数
Double Precision　 取决于机器精度的双精度浮点数
FLOAT(n)　　　　 浮点数,精度至少为n位数字
DATE 　　　　　　 日期,包含年、月、日,格式为 YYYY-MM-DD
TIME 　　　　　　 时间,包含一日的时、分、秒,格式为 HH:MM:SS

eg.建立一个学生选课表

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
/ 主码由两个属性构成,必须作为表级完整性进行定义/
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/ 表级完整性约束条件,Sno是外码,被参照表是Student /
FOREIGN KEY (Cno) REFERENCES Course(Cno)
/ 表级完整性约束条件, Cno是外码,被参照表是Course/
);

2.修改基本表

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ ALTER COLUMN<列名> <数据类型> ]
[ADD [COLUMN<约束名> ] <约束定义> ]
;

eg.向Student表增加“入学时间”列,其数据类型为日期型 .

ALTER TABLE Student
ADD S_entrance DATE

3.删除基本表

DROP TABLE <表名>［RESTRICT| CASCADE］;(缺省情况是 RESTRICT)

eg.删除Student表

DROP TABLE Student CASCADE ;

实体完整性

定义为列级约束条件:

CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2) ,
Sage SMALLINT,
Sdept CHAR(20));

定义为表级约束条件:

CREATE TABLE Student(
Sno CHAR(9),
Sname CHAR(20) NOT NULL,
Ssex CHAR(2) ,
Sage SMALLINT,
Sdept CHAR(20),
PRIMARY KEY (Sno));

参照完整性

在 CREATE TABLE 中用 FOREIGN KEY 短语定义哪些列为外码用REFERENCES短语指明这些外码参照哪些表的主码

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno, Cno), /在表级定义实体完整性/
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/在表级定义参照完整性/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
/在表级定义参照完整性/
);

3.4 视图

特点:

  • 是从一个或几个基本表（或视图）导出的表
  • 只存放视图的定义,不存放视图对应的数据
  • 基表中的数据发生变化,从视图中查询出的数据也随之改变
  • 一个窗口,透过它可以看到数据库中自己感兴趣的数据及其变化

特殊用途: 定义基本表是,为了减少数据库中的沉余数据,表中只存放基本数据,由基本数据经过各种计算派生出的数据一般是不存储的,但由于视图中数据并不是实际存储,所以定义视图时可以根据应用的需要,设置一些派生属性列.这些派生属性由于在基本表中并不实际存在也称它们为虚拟列.带虚拟列的视图也称为带表达式的视图

作用:

  • 视图能够简化用户的操作
  • 视图使用户能以多种角度看待同一数据
  • 视图对重构数据库提供了一定程度的逻辑独立性
  • 视图能够对机密数据提供安全保护
  • 适当的利用视图可以更清晰的表达查询

基于视图的操作:

  • 查询
  • 删除
  • 受限更新
  • 定义基于该视图的新视图

建立视图

create view
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];

eg.建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= ‘IS’
WITH CHECK OPTION;

基于视图的视图 (建立信息系选修了1号课程且成绩在90分以上的学生的视图)

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;

删除视图

DROP VIEW <视图名>[CASCADE];

该语句从数据字典中删除指定的视图定义,如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除

删除基表时,由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除
视图查询:

视图查询

更新视图

限制:

  • 一些视图是不可更新的,因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新
  • 允许对行列子集视图进行更新

  • 对其他类型视图的更新不同系统有不同限制

    • 若视图是由两个以上基本表导出的,则此视图不允许更新
    • 若视图的字段来自字段表达式或常数,则不允许对此视图执行INSERT和UPDATE操作,但是允许执行DELETE操作,
    • 若视图的字段来自聚集函数,则此视图不允许更新
    • 若视图定义中含有GROUP BY子句,则此视图不允许更新
    • 若视图定义中含有DISTINCT,则此视图不允许更新

Part4 数据库安全性

1.概述

数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄漏、更改或破坏。

数据库的不安全因素

  • 非授权用户对数据库的恶意存取和破坏
  • 数据库中重要或敏感的数据被泄露
  • 安全环境的脆弱性

2.数据库安全性措施

安全模型

数据库管理系统安全性控制模型

数据库安全性控制的常用方法

  • 用户标识和鉴定
  • 存取控制
  • 视图
  • 审计
  • 密码存储

3.用户身份鉴别

用户标识：用户名，用户标识号

鉴别方法：

  • 静态口令鉴别
  • 动态口令
  • 生物特征
  • 智能卡

4.存取控制

存取控制机制的组成：

  • 定义用户权限
  • 合法权限检查

常用存取控制方法：

  • 自主存取控制：通过SQL的GRANT 和 REVOKE语句实现
  • 强制存取控制

自主存取控制

  1. GRANT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
GRANT <权限>[,<权限>]... 
ON <对象类型> <对象名>,<对象类型> <对象名>,...
TO <用户>[,<用户>]...
[WITH GRANT OPTION];

GRANT SELECT   /*把查询Student表的权限授给用户U1*/
ON TABLE Student
TO U1;
GRANT ALL PRIVILEGES   /*把对Student表和Course表的全部操作权限授予所有用户*/
ON TABLE Student,Course
TO PUBLIC;
GRANT UPDATE(Sno),SELECT  /*把查询Student表和修改学生学号的权限授给用户U4*/
ON TABLE Student
TO U4;

2.REVOKE

1
2
3
REVOKE <权限>[,<权限>]... 
ON <对象类型> <对象名>,<对象类型> <对象名>,...
FROM <用户>[,<用户>]...;

强制存取控制

自主：可能存在数据的无意泄露

强制：全部实体分为主体和客体两大类。

DAC+MAC安全性检查：

DAC+MAC安全性检查

先进行自主存取控制检查，通过自主存取控制检查的数据对象再由系统进行强制存取控制检查，只有通过强制存取控制检查的数据对象方可存取。

5.视图机制

把要保密的数据对无权存取这些数据的用户隐藏起来，对数据提供一定程度的安全保护

间接实现了支持存取谓词的用户权限定义

6.审计

什么是审计

  • 审计日志（Audit Log）
  • 将用户对数据库的所有操作记录在上面
  • DBA利用审计日志，找出非法存取数据的人、时间和内容
  • C2以上安全级别的DBMS必须具有审计功能

AUDIT语句和NOAUDIT语句

  • AUDIT语句：设置审计功能
  • NOAUDIT语句：取消审计功能
1
2
3
4
AUDIT ALTER,UPDATE  /*对修改SC表结构或修改SC表数据的操作进行审计*/
ON SC;
NOAUDIT ALTER,UPDATE
ON SC;

审计一般可以分为用户级审计和系统级审计

  • 用户级审计

    • 任何用户可设置的审计
    • 主要是用户针对自己创建的数据库表和视图进行审计

  • 系统级审计

    • 只能由数据库管理员设置
    • 监测成功或失败的登录要求、监测授权和收回操作以及其他数据库级权限下的操作

7.数据加密

防止数据库中数据在存储和传输中失密的有效手段

根据一定的算法将原始数据—明文（Plain text）变换为不可直接识别的格式­—密文（Cipher text）

加密方法：

  • 存储加密
  • 传输加密

8.其他安全性保护

推理控制

隐蔽信道

数据隐私保护

Part5 数据库恢复技术

5.1 事务的基本概念

事务是一系列的数据库操作，是数据库应用程序的基本逻辑单元。事务处理技术主要包括数据库恢复技术和并发控制技术。

事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。

事务和程序比较

  • 两个概念
  • 在关系数据库中，一个事务可以是一条SQL语句、一组SQL语句或整个程序。
  • 一个程序通常包含多个事务

显式定义：

  • commit提交：提交事务的所有操作，将事务中所有对数据库的更新写回到磁盘上的物理数据库中去
  • ROLLBACK表示回滚，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

事务的ACID特性：

  • 原子性（Atomicity）
    事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做。
  • 一致性（Consistency）
    事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
  • 隔离性（Isolation）
    一个事务的执行不能被其他事务干扰。
  • 持续性（Durability ）
    一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

5.2 数据库恢复概述

1.故障

故障：

  • 系统故障：软硬件故障
  • 人为故障：操作失误，恶意破坏

故障种类：

  • 事务内部故障：解决——撤销事务

  • 系统故障：不破坏数据库；内存缓冲区的信息全部丢失；解决：

    • 事务未提交：UNDO所有未完成事务（强行撤销）
    • 事务已提交：REDO所有已提交事务（重做）

  • 介质故障：外存故障；解决——装入故障前某时刻的数据副本/重做所有成功事务

  • 病毒

2.恢复的实现技术

恢复操作的基本原理：冗余

  • 利用存储在系统其它地方的冗余数据来重建数据库中已被破坏或不正确的那部分数据

建立冗余数据最常用的技术是数据转储和登记日志文件。

数据转储

转储是指DBA将整个数据库复制到磁带或另一个磁盘上保存起来的过程，备用的数据称为后备副本或后援副本。

静态转储：在系统中无运行事务时进行的转储操作

动态转储：转储操作与用户事务并发进行

登记日志文件

日志文件格式：

  • 以记录为单位的日志
  • 以数据块为单位的日志

Reference

kzangv-SQL笔记
SQL学习笔记之基础操作
Lewis’s blog-数据库系统概论

Thanks!