期末复习

1 数据管理技术概述

1 数据库相关概念

数据库系统 (Database System)
  • 基于数据库建立的一种信息系统
  • DBS = DBMS + DB + 应用程序 + 用户（数据库管理员、应用系统开发人员、终端用户）
    Pasted image 20250228104354.png
数据库管理系统 (DataBase Management System)
  • 管理数据库的一种软件系统
  • 主要功能包括：
    • 数据定义功能
    • 数据组织、存储、管理功能
    • 数据操纵功能
    • 数据库事务管理和运行管理
    • 数据库的建立与维护…
      Pasted image 20250228104531.png

3 数据库结构

3.1 数据库模式结构

三级模式结构

Pasted image 20250228112203.png

外模式--用户层
  • 外模式（也称子模式或用户模式）
    • 数据库用户使用的局部数据的逻辑结构和特征的描述
    • 外模式是基于数据视图实现。
    • 外模式通常是模式的子集
    • 一个数据库可以有多个外模式。
    • 外模式与应用的关系：一对多
      Pasted image 20250228112527.png
模式（概念模式）--逻辑层
  • 模式（也称概念模式）
    • 数据库中全体数据的逻辑结构和特征的描述
    • 一个数据库只有一个模式
      Pasted image 20250228112517.png
内模式--物理层
  • 内模式（也称存储模式）
    • 是数据物理结构和存储方式的描述
    • 一个数据库只有一个内模式
      Pasted image 20250228112537.png
二级映像
  • 三级模式是对数据的三个抽象级别
  • 二级映象在DBMS内部实现这三个抽象层次的联系和转换
    Pasted image 20250228113535.png
外模式/ 模式映象
  • 实现手段：数据视图（View） （在后面章节中会介绍）
  • 作用：保证数据的逻辑独立性（模式改变时应用程序不必修改）
模式 / 内模式映象
  • 作用：保证数据的物理独立性（当数据库的存储结构改变时应用程序不受影响）

3.2 数据库功能结构

数据库管理系统运行着很多例程来完成数据管理功能
查询处理器、存储管理器、安全管理器等

3.3 数据库体系结构

数据库的组件构成和相互之间的交互作用

  • 集中式：客户端不运行程序，数据管理和业务处理都由中央服务器负责
  • 客户-服务器式 (Client-Serve, CS)：客户端运行应用程序，数据库部署于中心服务器，通常采用单体数据库结构
  • 分布式：当数据规模巨大，单体数据库无法处理时，可以使用并行、分布式架构扩展容量和处理能力，提升可用性
  • 并行式
  • 云数据库

2 数据模型

2.1 什么是数据模型

  • 数据模型分类
    • 概念数据模型
    • 逻辑数据模型
    • 物理数据模型
      Pasted image 20250228120228.png
概念数据模型
  • 是独立于计算机系统的数据模型，是对现实世界的第一层抽象
  • 概念模型是按用户的观点对数据建模，强调其语义表达能力，是用户和数据库设计人员之间进行交流的语言和工具
逻辑数据模型
  • 直接面向数据库的逻辑结构,是对现实世界的第二层抽象。它直接与DBMS有关,有严格的形式化定义,以便在计算机系统中实现。
  • 它通常有一组严格定义的无二义性语法和语义的DB语言，人们可以用这种语言来定义、操纵DB中的数据。
  • 该类数据模型有层次模型、网状模型、关系模型等。
  • 逻辑数据模型是区分不同类型数据库的依据，并在很大程度上决定了数据库的性能和应用范围。
  • 通常所说的数据模型即指逻辑数据模型
物理数据模型
  • 反映了数据在存储介质上的组成结构，并描述了访问机制
  • 如何表达记录结构、记录顺序和访问路径等信息

2.2 概念模型

ER数据模型：最常用的一种概念数据模型
  • 两种常用抽象手段
    • 分类（Classification）
      • 定义某一类概念作为现实世界中一组对象的类型
      • 在特定的上下文里，这些对象具有某些共同的特性和行为
      • 大学里的学生，奥运会的赛事，公交公司的司机…
    • 聚集（Aggregation）
      • 定义某一类型的组成成分
      • 根据上下文管理需求，给出指定类型的特征集合
      • 学生：学号、姓名、年龄等特征组成
三个基本元素
实体 (Entity)
  • 客观存在并可以相互区分的客观事物或抽象事件称为实体
    • 可以触及的客观对象仓库、器件、职工……等是实体
    • 客观存在的抽象事件订货、演出、足球赛……等也是实体
  • 实体集
属性 (Attribute)
  • 域（Domain）：属性的取值范围。所有可能的取值的集合。
  • 属性的类型（Type）
    • 简单属性：不可再分的属性
    • 复合属性：如：街道＝家庭地址＋门牌号码。
    • 单值属性：每一个特定的实体在该属性上的取值唯一，如学生的学号、年龄等
    • 多值属性：某特定实体在该属性上有多于一个的取值，如某人的电话号码
  • 键（key）
    • 实体集中能唯一标识实体的属性或属性组称为实体集的键
    • 两个以上属性组成的键称为复合键(compound key)
    • 实体集中的每一个实体，在键属性上的取值各不相同
联系 (Relation)
  • 联系由一系列的实体间对应关系所组成
  • 联系的键必然是复合键，其键由来自于各相关实体集的键属性组成
  • 也可以用属性来刻画联系的特性
ER图——用图形化的方法来表示ER数据模式
  • 用矩形框表示实体，把实体名写在框内
  • 实体的属性用椭圆框表示，框内写上属性名，并用连线连到相应实体。
  • 实体之间的联系用菱形框表示，框内写上联系名，并用连线与有关的实体相连。
    Pasted image 20250609200637.png
联系的元

参与联系的实体的个数称为联系的元
Pasted image 20250307103118.png

一元联系
  • 有时二元联系关联的两个实体集实际上属于同一个实体集的两个副本，可以将这二个实体集合并，形成一元联系。
    Pasted image 20250307105456.png
  • 一元联系中的1:1联系
    运动员根据其得分来排定名次。在名次排列中，排在他前面只有一个人，排在他后面也只有一个人
    Pasted image 20250307110507.png
  • 一元联系中的1:n联系
    职工之间的上下级关系中，一个领导可以管理多个职工，每个职工只隶属于一个领导
    Pasted image 20250307110521.png
  • 一元联系中的m:n联系
    工厂的零件之间存在着组合关系，一种零件由多种子零件组成，而一种零件也可以是其他零件的子零件
    Pasted image 20250307110555.png
多元联系

Pasted image 20250307111934.png

联系中的角色 (Roles)
  • 在联系中关联的每个实体集应是相互区别的。在一元联系中，需要用角色来区分同一实体集的不同副本
  • 下图中的标签“manager” 和 “worker”被称为角色，它们说明了 employee 实体是如何通过 works_for 联系相互关联的
    Pasted image 20250307111100.png
联系的基数比约束(Mapping Cardinality Constraints)
  • 表示一个实体可以通过联系关联到的另一个实体集中实体的数量。
  • 一对一联系 (1:1)
    • 实体集A中的一个实体至多与实体集B中的一个实体相对应，反之亦然，则称实体集A与实体集B为一对一的联系。记作1:1。
      Pasted image 20250307103207.png
  • 一对多联系 (1:n)
    • 实体集A中的一个实体可以与实体集B中的多个实体相对应，反之，实体集B中的一个实体至多与实体集A中的一个实体相对应
      Pasted image 20250307103324.png
  • 多对多联系 (m:n)
    • 实体集A中的一个实体可以与实体集B中的多个实体相对应，反之亦然
      Pasted image 20250307103457.png
约束 (Constraints)

  • 常见约束：非空约束；唯一值约束；主键约束； 外键约束；Check约束等等

  • 键约束（Key Constraints）

    • 超键（super key） 是实体集中的一个或一组属性，用它们可以唯一的确定每一个实体
    • 候选键（candidate key）是某个超键的最小集
    • 若存在多个候选键，则可以任选一个作为该实体集的主键（primary key）。主键会被数据库登记和使用。
      例：学生（学号，身份证号，年龄，性别，班级）
      超键：（学号，身份证号，年龄），（学号，性别，班级）…
      候选键：（学号），（身份证号）
      主键：（学号）
建模准则
  • KISS准则 (keep it simple and stupid)
  • 避免冗余
  • 能抽象为属性的，就不要抽象为实体
    • 只有在符合下面两个情况之一的时候才需要把事物抽象为实体
      1. 事物有至少一个非键属性（不属于候选键的属性）学生（姓名） VS 书（书名，页数，出版年代）
      2. 事物处于“一对多”或“多对多”联系中“多”那一端

2.3 逻辑模型

基本概念
  • 逻辑数据模型是对现实世界的第二层抽象。负责将概念数据模式映射为数据库的逻辑结构。
  • 逻辑数据模型的三要素：数据结构、数据操作和数据约束
逻辑模型的发展

文件模型、层次模型、网状模型、关系模型、XML模型、面向对象模型

2.3.1 关系模型概述

ER模型 vs. 关系模型
  • 都用于数据建模
  • ER模型有很多概念
    • 实体、关系、属性等
    • 不适合在计算机上实现(只有数据结构，没有定义数据操作)
  • 关系模型
    • 只有一个概念：关系（relation）
    • 适合在计算机上实现，能进行高效的数据操作
关系数据库
  • 关系数据库系统是支持关系模型的数据库系统

2.3.2 关系数据结构（结构）

  • 关系模型就是用二维表格结构来表示实体及实体之间联系的模型
  • 关系的框架（表头）称为关系模式
  • 关系框架和符合该框架的关系值称为关系实例
    Pasted image 20250314100856.png
关系模型
  • 层次模型和网状模型：实体之间的联系是通过指针来实现。
  • 关系模型：用二维表来表示实体与实体之间的联系，这就是关系模型的本质所在。
关系的定义
  • 在关系模型中，数据是以二维表的形式存在的，这个二维表就叫做关系。
域（Domain）
  • 域中所包含的值的个数称为域的基数（用m表示）。
  • 域表示属性的取值范围。例如：
  • 域值无排列次序，如D2={男，女}={女，男}
笛卡尔积
  • 笛卡尔积每一个元素（d1，d2，d3，…，dn）叫做一个n元组，简称元组。
  • 元素中的每一个di叫做一个分量，来自相应的域（di∈Di）
  • 元组不是di的集合，元组的分量（di）是按序排列的
  • 笛卡尔积的基数M（即元素（d1,d2,……dn）的个数）为所有域的基数的累乘之积，即
  • 笛卡尔积的二维表表示
    Pasted image 20250314103111.png
关系（Relation）的定义
  • 笛卡尔积D1×D2×…×Dn的任一子集称为定义在域D1，D2，…Dn上的n元关系（Relation），可用R（D1，D2 ……Dn）表示
  • R为关系名，n称为关系的元(目或度)（Degree）。
    当n=1时，称为单元关系。
    当n=2时，称为二元关系。
    …
    当n=n时，称为n元关系。
关系的二维表表示

同样可以把关系看成一个二维表。其中，

  • 表的框架由域Di（i=1，2，……n）构成；
  • 表的任意一行对应一个元组；
  • 表的每一列来自同一域；
  • 域可以相同，为了加以区别，每列起一个名字，称为属性，n目关系有n个属性，属性的名字唯一，属性的取值范围Di（i=1，2，…，n）称为值域
  • 具有相同关系框架的关系成为同类关系，如：
    Pasted image 20250314104653.png
关系的性质

关系中每一分量必须是不可分的数据项。不可“表中有表”。满足此条件的关系称为规范化关系，否则称为非规范化关系。
Pasted image 20250314105754.png

ER模型转关系模型
  • 实体类型的转换：将每个实体类型转换成一个关系模式，实体的属性即为关系模式的属性，实体标识符即为关系模式的键。
  • 对于二元联系的处理
    • 把m:n联系转化为关系
    • 对于1：n联系和1：1联系的处理
      • 可以像m：n联系一样，直接转换为一个关系，但更好的是……
      1. 对于 1：n的处理：合并到n端
      2. 对于1：1的处理：合并到常用实体对应的实体关系中。
  • 对于三元联系的处理
    • 1:1:1 联系与其中一个实体集合并
    • 1:1:N 联系与N端实体集合合并
    • 1:M:N 联系转换为独立的关系模式，主键为1端实体集的主键
    • M:N:P 联系转换为独立的关系模式，主键为三个实体集的主键的组合

2.3.3 关系的完整性（约束）

  • 关系模型中的完整性约束：
    1. 域完整性：值域 NULL
    2. 实体完整性：主键不为空，取值唯一
    3. 参照完整性：外键（NULL or 等于被参照关系的主键）、参照关系、被参照关系
    4. 用户定义的完整性

2.3.4 关系代数（操作）

1 关系运算

  • 按表达查询的方法不同，关系运算可分为关系代数和关系演算两大类。

2 关系代数（Relational Algebra）

  • 关系代数是对关系的查询语言：以对关系的运算表达查询
  • 关系代数运算的三个要素
    • 运算对象：关系
    • 运算结果：关系
    • 运算符：四类
四类关系运算符

Pasted image 20250315210714.png

  • 另一个概念：基本运算
    • 集合并、集合差、广义笛卡儿积、选择、投影
    • 由基本运算可以推导出其它所有运算
广义笛卡尔积 (Extended Cartesian Product)
  • 笛卡尔积：集合运算 vs. 广义笛卡尔积：关系运算
  • 的度为R与S的度之和， 的元组个数为R和S的元组个数的乘积。
    Pasted image 20250315211751.png
专门的关系运算
  • 选择 、投影 、连接 和除
  • 其中选择、投影是基本运算符
选择 (selection)
  • 选择运算表示为：
    Pasted image 20250324161506.png
投影 (projection)
  • 投影运算定义为：
    Pasted image 20250324161541.png
连接 (join)

Theta链接

  • 定义
    从两个关系的广义笛卡儿积中选取给定属性间满足 操作的元组。
    为算术比较符，当 为等号时称为等值连接；θ为“<”时，称为小于连接；θ为“>”时，称为大于连接等等。
    此类连接也被统称为Theta连接(连接)
    Pasted image 20250324161609.png

自然连接

  • 定义:从两个关系的广义笛卡儿积中选取在相同属性列（名一致&定义域一致）上取值相等的元组，并去掉重复的属性列。
  • 自然连接是特殊的等值连接
    • 等值连接：从两个关系(R、S)的笛卡尔积中选取属性(A、B)值相等的元组
    • 自然连接：在等值连接中取消重复属性
      Pasted image 20250324161705.png

外链接

  • 为避免自然连接时因失配而发生的信息丢失，可以假定往参与连接的一方表中附加一个取值全为空值的行，它和参与连接的另一方表中的任何一个未匹配上的元组都能匹配，称之为外连接。
    外连接 = 自然连接 + 失配的元组。
    外连接的形式：左外连接、右外连接、全外连接 。
    R Pasted image 20250324162550.png S 左外连接 = 自然连接 + 左侧表中失配的元组。
    R Pasted image 20250324162634.png S 右外连接 = 自然连接 + 右侧表中失配的元组。
    R Pasted image 20250324162651.png S 全外连接 = 自然连接 + 两侧表中失配的元组
    Pasted image 20250609220000.png
除 (divide)
  • 除运算定义：设有关系R (X ,Y )和S (Y )，其中X 、Y 可以是单个属性或属性集，则：

Pasted image 20250324162735.png

3 SQL语言

组成部分
  • 查询语言 DQL (Query Language)
  • 定义语言 DDL (Data Definition Language)
  • 操纵语言 DML (Data Manipulation Language)
  • 控制语言 DCL (Data Control Language)

1 查询语言 DQL

基本结构
SELECT
FROM
WHERE
GROUP BY
ORDER BY
  • SELECT子句：指定要显示的属性列
  • FROM子句：指定查询对象(基本表或视图)
  • WHERE子句：指定查询条件
  • GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
  • HAVING短语：筛选出只有满足指定条件的组
  • ORDER BY子句：对查询结果表按指定列值的升序或降序排序

1.1 单表查询

  • 查询仅涉及一个表，是一种最简单的查询操作
选择表中的若干列

[例1] 查询全体学生的学号与姓名。
SELECT Sno，Sname
FROM Student；

[例3] 查询全体学生的详细记录。
SELECT Sno，Sname，Ssex，Sage，Sdept
FROM Student；
或
SELECT *
FROM Student；

查询经过计算的值

SELECT子句的<目标列表达式>为表达式

  • 算术表达式
  • 字符串常量
  • 函数
  • 列别名
  • 等

[例4] 查全体学生的姓名及其出生年份。
SELECT Sname，2022-Sage
FROM Student；
输出结果：
Pasted image 20250323221008.png

[例5] 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECT Sname , 2020-Sage AS 'Year of Birth: ' , LOWER(Sdept)
FROM Student；

使用列别名改变查询结果的列标题

SELECT Sname AS NAME，'Year of Birth: ’ AS BIRTH，2022-Sage AS BIRTHDAY，LOWER(Sdept) AS DEPARTMENT
FROM Student；
Pasted image 20250323221144.png

选择表中的若干元组
保留重复行

  • 用ALL关键字
    SELECT ALL Sno FROM SC;

  • ALL为默认关键字，可以省略，等价于：
    SELECT Sno FROM SC;

消除取值重复的行

  • 在SELECT子句中使用DISTINCT短语消除重复行
    SELECT DISTINCT Sno FROM SC;

  • 注意 DISTINCT短语的作用范围是所有目标列
    例：查询选修课程的各种成绩
    错误的写法
    SELECT DISTINCT Cno，DISTINCT Grade FROM SC;
    正确的写法
    SELECT DISTINCT Cno，Grade FROM SC;

用where子句查询满足条件的元祖

Pasted image 20250323221612.png
比较大小

  • 在WHERE子句的比较条件中使用比较运算符 = ，>，<，>=，<=，!= 或 <>，!>，!<，逻辑运算符NOT + 比较运算符
    [例6] 查询所有年龄在20岁以下的学生姓名及其年龄。
    SELECT Sname，Sage
    FROM Student
    WHERE Sage < 20；

确定范围

  • 使用谓词 BETWEEN … AND … NOT BETWEEN … AND …
    [例7] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
    SELECT Sname，Sdept，Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 23；

确定集合

  • 使用谓词 IN <值表>, NOT IN <值表>
    <值表>：用逗号分隔的一组取值

    [例9] 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
    SELECT Sname，Ssex
    FROM Student
    WHERE Sdept NOT IN ( 'IS'，'MA'，'CS' );

字符串匹配

  • WHERE 子句中可以对字符串进行模版匹配，形如：
    WHERE <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>
    其中<pattern>是匹配模板，即：固定字符串或含通配符的字符串

    • 通配符：
      % (百分号) 代表任意长度（长度可以为0）的字符串
      _ (下横线) 代表任意单个字符
      [例10] 查询学号为95001的学生的详细情况。
      SELECT FROM Student
      WHERE Sno LIKE '95001'；
      等价于：
      SELECT       FROM Student
      WHERE Sno = '95001'；

    [例11] 查询所有姓刘学生的姓名、学号和性别。
    SELECT Sname，Sno，Ssex
    FROM Student
    WHERE Sname LIKE ‘刘%’；

    [例12] 查询姓"欧阳"且全名为三个汉字的学生的姓名。
    SELECT Sname
    FROM Student
    WHERE Sname LIKE '欧阳__'；

    [例13] 查询名字中第2个字为"阳"字的学生的姓名和学号
    SELECT Sname，Sno
    FROM Student
    WHERE Sname LIKE '__阳%'；

    [例14] 查询所有不姓刘的学生姓名。
    SELECT Sname，Sno，Ssex
    FROM Student
    WHERE Sname NOT LIKE '刘%'；

  • 当用户要查询的字符串本身就含有 % 或 时，要使用ESCAPE '<换码字符>' 短语对通配符进行转义。(! Mysql不能用\作为转义符)
    [例15] 查询DB_Design课程的课程号和学分。
    SELECT Cno，Ccredit
    FROM Course
    WHERE Cname LIKE 'DB\_Design'
    ESCAPE ''
    [例16] 查询以“DB    ”开头，且倒数第3个字符为 i 的课程的详细情况。
    SELECT
    FROM Course
    WHERE Cname LIKE ‘DB    _%i_ _' ESCAPE '*'；

  • 当进行固定字符串匹配时用可以‘=’代替‘like’

  • 对于包含单引号的字符串，在条件表达式中用双引号代替单引号
    [例16] 查询课程名为‘数据库’的课程的详细情况。
    SELECT *
    FROM Course
    WHERE Cname = ’’’数据库’’’ ；

多重条件查询

  • 用逻辑运算符AND，OR和 NOT来联结多个查询条件
    [例17] 查询计算机系年龄在20岁以下的学生姓名。
    SELECT Sname
    FROM Student
    WHERE Sdept= 'CS' AND Sage<20；

    [例18] 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。
    SELECT Sname，Ssex
    FROM Student
    WHERE Sdept IN ( 'IS'，'MA'，'CS' )
    可改写为：
    SELECT Sname，Ssex
    FROM Student
    WHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS '；

    [例19]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
    SELECT Sname，Sdept，Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 23；
    可改写为：
    SELECT Sname，Sdept，Sage
    FROM Student
    WHERE Sage>=20 AND Sage<=23；

涉及空值的查询

  • 测试是否空值需用谓词 IS NULL 或 IS NOT NULL

  • “IS NULL” 不能用 “= NULL” 代替
    [例20] 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
    SELECT Sno，Cno FROM SC
    WHERE Grade IS NULL；

    [例21] 查所有有成绩的学生学号和课程号。
    SELECT Sno，Cno
    FROM SC
    WHERE Grade IS NOT NULL；

对查询结果进行排序

  • 使用ORDER BY子句

    • 升序：ASC；降序：DESC；缺省值为升序

  • 当排序列含空值时

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

  • Order子句只能出现在select语句的最后部分

    [例22] 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
    SELECT Sno，Grade
    FROM SC
    WHERE Cno= ' 3 '
    ORDER BY Grade DESC；

    [例23] 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
    SELECT *
    FROM Student
    ORDER BY Sdept，Sage DESC；

1.2 聚集和分组

聚集-集函数

只有select和having能使用集函数

在Select子句中可以使用集函数，对指定的列进行聚合计算
COUNT SUM AVG MAX MIN

  • DISTINCT短语：在计算时要取消指定列中的重复值

  • ALL短语：不取消重复值

  • ALL为缺省值

    [例24] 查询学生总人数。
    SELECT COUNT(*)
    FROM Student；

    [例25] 查询选修了课程的学生人数。
    SELECT COUNT(DISTINCT Sno)
    FROM SC；
    注：用DISTINCT以避免重复计算学生人数

    [例26] 计算1号课程的学生平均成绩。
    SELECT AVG(Grade)
    FROM SC
    WHERE Cno= ' 1 '；

    [例27] 查询选修1号课程的学生最高分数。
    SELECT MAX(Grade)
    FROM SC
    WHER Cno= ' 1 '；

  • 如果列中没有非空值，则集函数结果会返回空值

分组
group by
  • 使用GROUP BY子句分组
    • 未对查询结果分组时，集函数将作用于整个查询结果
    • 对查询结果分组后，集函数将分别作用于每个组
  • GROUP BY子句的作用对象是查询的中间结果表
  • 分组方法：按指定的一列或多列值分组，值相等的为一组
  • 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数
    Pasted image 20250323224730.png
    Pasted image 20250323224751.png
    [例28] 求各个课程号及相应的选课人数。
    SELECT Cno，COUNT(Sno)
    FROM SC
    GROUP BY Cno；
    Pasted image 20250323224840.png
    Pasted image 20250323224855.png
having

  • HAVING短语与WHERE子句的区别：作用对象不同

    • WHERE子句作用于基表或视图，从中选择满足条件的元组
    • HAVING短语作用于组，从中选择满足条件的组。

  • HAVING子句中出现的列只能是在group by子句或集函数中出现的列

    [例30] 查询选修了3门以上课程的学生学号。
    SELECT Sno
    FROM SC
    GROUP BY Sno
    HAVING COUNT(*) >3；

    [例31] 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数
    SELECT Sno, COUNT(*)
    FROM SC
    WHERE Grade>=90
    GROUP BY Sno
    HAVING COUNT(*)>=3;

    Pasted image 20250323225254.png

1.3 多表查询

  • 在From子句中涉及

  • 和自己连接：自身连接

  • 和其他表连接

    • 不带连接谓词：广义笛卡尔积
    • 带连接谓词
      • 不满足连接条件的元组一并输出：外链接
      • 只输出满足连接条件的元祖：内连接
        • 非单一条件：复合条件连接
        • 单一条件
          • 等值：等值连接
          • 不等值：非等值连接
广义笛卡尔积
  • 是不带链接谓词的连接
  • 一般格式是 SELECT <属性或表达式列表> FORM <表名> CROSS JOIN <表名>
  • 例
    SELECT Student.*, SC.* FROM Student, SC
等值连接
  • 语法：[<表名1>.]<列名1> = [<表名2>.]<列名2>
    [例32] 查询每个学生及其选修课程的情况。
    SELECT Student.sno，sname, cno
    FROM Student，SC
    WHERE Student.Sno = SC.Sno；
自然连接

等值连接的一种特殊情况，把目标列中重复的属性列去掉

非等值连接查询

连接运算符不是=号的连接

内连接

传统的连接操作被称为内连接（INNER JOIN），其一般格式是：
SELECT <属性或表达式列表>
FROM <表名> [INNER] JOIN <表名>
ON <连接条件>
[WHERE <限定条件>]

  • 上式中INNER可以省略，这里用ON短语指定连接条件，用WHERE短语指定其它限定条件。
自身连接查询
  • 一个表与其自己进行连接
  • 需要给表的两个副本起别名以示区别
  • 由于所有属性名都是同名属性，因此必须使用别名前缀

[例33] 查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno，SECOND.Cpno
FROM Course AS FIRST，Course AS SECOND
WHERE FIRST.Cpno = SECOND.Cno；
Pasted image 20250330181925.png

外连接查询

普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

  • 语法
    SELECT <属性或表达式列表>
    FROM <表名> [LEFT | RIGHT | FULL] [OUTER] JOIN <表名>
    ON <连接条件>
    [WHERE <限定条件>]

  • Mysql不支持全外连接
    解决方法：Left join Union Right join

[例34] 查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作
SELECT Student.Sno，Sname，Cno，Grade
FROM Student Left Join SC
on Student.Sno =SC.Sno；

复合条件连接查询

WHERE子句中含多个连接条件时，称为复合条件连接
[例34] 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名
SELECT Student.Sno, student.Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND / 连接谓词/
SC.Cno= ' 2 ' AND /* 其他限定条件 */
SC.Grade > 90；/* 其他限定条件 */

1.4 子查询

  • 一个SELECT-FROM-WHERE语句称为一个查询块
  • 子查询一定要跟在比较符之后（p26）
    错误的例子：
    SELECT Sno，Sname，Sdept
    FROM Student
    WHERE ( SELECT Sdept
    FROM Student
    WHERE Sname= ‘ 刘晨 ’ )
    = Sdept；
    正确的例子：
    SELECT Sno, Sname, Sdept
    FROM Student
    WHERE Sdept = ( SELECT Sdept -- 子查询跟在比较符 '=' 之后
    FROM Student
    WHERE Sname = ' 刘晨 ' ); -- 比较符之后是子查询
子查询分类

  • 可以嵌套在另一个查询块的WHERE子句或HAVAING短语的条件中
    SELECT Sname FROM Student WHERE Sno IN 外层查询/父查询
    （SELECT Sno FROM SC WHERE Cno= ' 2 '）；内层查询/子查询

  • 可以插入到FROM子句中，作为临时表使用

    • 临时表需起个别名来引用
      SELECT SIS.Sno, Sname, Cno
      FROM SC, (SELECT Sno, Sname from Student WHERE Sdept= ‘ IS ‘) as SIS
      WHERE SC.Sno=SIS.Sno

  • 可以插入到SELECT子句中

    • 确保返回结果为单列单行
    • 没有太大意义，多数数据库不支持

  • 相关子查询

    • 子查询的查询条件依赖于父查询
    • 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；
    • 然后再取外层表的下一个元组；
    • 重复这一过程，直至外层表全部检查完为止。
      SELECT Sname FROM Student S
      WHERE EXISTS (
      SELECT 1 FROM SC
      JOIN Course ON SC.Cno = Course.Cno
      WHERE SC.Sno = S.Sno -- 引用外层当前行的Sno
      AND Cname = 'DB'
      );

  • 不相关子查询

    • 由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件
      上面那个例子（插入From子句）是不相关子查询
      SELECT Sname FROM Student
      WHERE Sno IN (
      SELECT Sno FROM SC
      JOIN Course ON SC.Cno = Course.Cno
      WHERE Cname = 'DB' -- 独立查询，不引用外层
      );
引出子查询的谓词
IN

[例35] 查询与“刘晨”在同一个系学习的学生。
此查询可以分步来完成

  1. 确定“刘晨”所在系名（是‘IS’系）
    SELECT Sdept FROM Student
    WHERE Sname= ' 刘晨 ‘；
  2. 查找所有在IS系学习的学生。
    SELECT Sno，Sname，Sdept
    FROM Student
    WHERE Sdept= ' IS '；

[例36] 查询与“刘晨”在同一个系学习的学生。
也可以直接构造嵌套查询：将第一步查询嵌入到第二步查询的条件中
SELECT Sno，Sname，Sdept FROM Student
WHERE Sdept IN
(SELECT Sdept FROM Student
WHERE Sname= ‘ 刘晨 ’)；
此查询为不相关子查询

[例37]. 查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno，Sname ③ 最后在Student关系中取出Sno和Sname
FROM Student
WHERE Sno IN
(SELECT Sno ② 然后在SC关系中找出选修了3号课程的学生学号
FROM SC
WHERE Cno IN
(SELECT Cno ① 首先在Course关系中找出“信息系统”的课程号，结果为3号
FROM Course
WHERE Cname= ‘信息系统’));
或
SELECT Sno，Sname
FROM Student，SC，Course
WHERE Student.Sno = SC.Sno AND
SC.Cno = Course.Cno AND
Course.Cname=‘信息系统’；

比较运算符

当能确切知道内层查询返回单值时，可用比较运算符 (>, <, =, >=, <=, != (或 <>))。

假设一个学生只可能在一个系学习，并且必须属于一个系，则在
[例36]中可以用 = 代替IN
SELECT Sno，Sname，Sdept FROM Student
WHERE Sdept =
SELECT Sdept FROM Student
WHERE Sname= ' 刘晨 '；

ANY或ALL
  • ANY：任意一个值
  • ALL：所有值
  • 与比较运算符配合使用
    Pasted image 20250330204626.png

[例38] 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄
SELECT Sname，Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <> ' IS ' ;
/ 注意这是父查询块中的条件 /

  • ANY和ALL谓词有时可以用集函数实现
  • 用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数
    Pasted image 20250328103141.png

[例39] 用集函数实现 [例38] 的查询
SELECT Sname，Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <> ' IS ’;

[例40] 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。
方法一：用ALL谓词
SELECT Sname，Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <> ' IS ’;

方法二：用集函数
SELECT Sname，Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <>' IS ’;

EXISTS
  1. EXISTS谓词
    • 存在量词
    • 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
    • 若内层查询结果非空，则返回真值
    • 若内层查询结果为空，则返回假值
    • 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
  2. NOT EXISTS谓词

Pasted image 20250330205637.png
Pasted image 20250330205652.png

  • 用于相关子查询
    [例41] 查询所有选修了1号课程的学生姓名。
    SELECT Sname
    FROM Student
    WHERE EXISTS
    (SELECT *
    FROM SC /*相关子查询*/
    WHERE Sno=Student.Sno AND
    Cno= ' 1 ')；

  • 用于不相关子查询
    select from a where exists (select from b)

  • 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换

    • 用EXISTS/NOT EXISTS实现全称量词，
      [例42] 查询选修了全部课程的学生姓名。
      SELECT Sname FROM Student
      WHERE NOT EXISTS
      (SELECT FROM Course
      WHERE NOT EXISTS
      (SELECT       FROM SC
      WHERE Sno= Student.Sno
      AND Cno= Course.Cno));
    • 用EXISTS/NOT EXISTS实现蕴含逻辑，
      [例43] 查询至少选修了学生95002选修的全部课程的学生号码

      • 用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。

      • 形式化表示：
        用P表示谓词 “学生95002选修了课程y”
        用q表示谓词 “学生x选修了课程y”
        则上述查询为:

        变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。
        Pasted image 20250610143435.png

  • 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
    [例36] 中的IN查询可以用带EXISTS谓词的子查询替换：
    SELECT Sno，Sname，Sdept
    FROM Student S1
    WHERE EXISTS
    (SELECT *
    FROM Student S2
    WHERE S2.Sdept = S1.Sdept AND
    S2.Sname = ' 刘晨 '）；

1.5 集合查询

  • 并操作 ( subquery ) UNION ( subquery )
  • 交操作 ( subquery ) INTERSECT ( subquery )
  • 差操作 ( subquery ) EXCEPT ( subquery )

[例44] 查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT
FROM Student
WHERE Sage<=19；

[例45] 查询计算机科学系选修95001号课程的学生的学号。
SELECT Sno
FROM Student
WHERE Sdept= 'CS'
Intersect
SELECT Sno
FROM SC
WHERE Cno=‘95001’；

[例46] 查询计算机科学系没有选修95001号课程的学生的学号。
SELECT Sno
FROM Student
WHERE Sdept= 'CS'
Except
SELECT Sno
FROM SC
WHERE Cno=‘95001’；

2 定义语言 DDL

  • 数据库模式（schema）主要由数据库中关系（relation）的声明构成，还包括视图（view）、索引（index）、触发器（trigger）等对象
  • 对数据库模式的定义通过SQL语言中的数据定义语言（Data Definition Language）部分来完成
  • DDL语言完成数据库对象的创建、删除和修改
    Pasted image 20250328110606.png

2.1 定义基本表

语法

CREATE TABLE <表名>(
	<列名> <数据类型>[ <列级完整性约束条件> ]
	[，<列名> <数据类型>[ <列级完整性约束条件>] ] …
	[，<表级完整性约束条件> ]
);
常见的数据类型
  • INT or INTEGER
  • REAL or FLOAT
  • CHAR(n ) = 定长字符串
  • VARCHAR(n ) = 变长字符串，n是字符串最大长度
  • DATE, TIME, and DATETIME
常用完整性约束
  • 主键约束： PRIMARY KEY
  • 唯一性约束：UNIQUE
  • 非空值约束：NOT NULL
  • 参照完整性约束： REFERENCES

例

[例1] 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE，
Sname CHAR(20) UNIQUE，
Ssex CHAR(1) ，
Sage INT，
Sdept VARCHAR(15))；

[例2] 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主键。
CREATE TABLE SC(
Sno CHAR(5) ,
Cno CHAR(3) ,
Grade int,
Primary key (Sno, Cno));

2.2 删除基本表

  • 语法：DROP TABLE <表名>;

[例3] 删除Student表
DROP TABLE Student ;

2.3 修改基本表

语法

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；

  • <表名>：要修改的基本表
  • ADD子句：增加新列和新的完整性约束条件
    • 不论基本表中原来是否已有数据，新增加的列一律为空值。
  • DROP子句：删除指定的列或完整性约束条件
  • MODIFY子句：用于修改列名和数据类型

例

[例4] 向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD Scome DATE；

[例5] 删除属性列
ALTER TABLE Student Drop Sage；

[例6] 将年龄的数据类型改为半字长整数。
ALTER TABLE Student MODIFY Sage SMALLINT；

[例7] 删除学生姓名必须取唯一值的约束。
ALTER TABLE Student DROP UNIQUE(Sname)；

3 操纵语言 DML

  • 数据更新语句不返回关系表，而是会改变数据库的内容
  • 三种数据更新类型
    • Insert 插入一条或多条元组
    • Update 更新现有一条或多条元组中的值
    • Delete 删除一条或多条元组

3.1 插入数据

插入单条元组

语法
INSERT
	INTO <表名> [(<属性列1>[，<属性列2 >…)]
	VALUES (<常量1> [，<常量2>] … )
  • INTO子句
    • 指定要插入数据的表名及属性列
    • 属性列的顺序可与表定义中的顺序不一致
    • 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
    • 指定部分属性列：插入的元组在其余属性列上取空值
  • VALUES子句
    • 提供的值必须与INTO子句匹配：个数、类型
例

[例7] 将一个新学生记录
（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。
INSERT INTO Student
VALUES ('95020'， '陈冬' ， '男' ，'IS'，18)；

[例8] 插入一条选课记录( '95020'，'1 ')。
INSERT INTO SC(Sno，Cno)
VALUES (' 95020 '，' 1 ')；
新插入的记录在Grade列上取空值

插入子查询结果

语法
INSERT
INTO <表名> [(<属性列1> [，<属性列2>… )]
子查询;
  • 功能：将子查询结果插入指定表中
  • INTO子句(与插入单条元组类似)
    • 指定要插入数据的表名及属性列
    • 属性列的顺序可与表定义中的顺序不一致
    • 没有指定属性列：表示要插入的是一条完整的元组
    • 指定部分属性列：插入的元组在其余属性列上取空值
  • 子查询
    SELECT子句目标列必须与INTO子句匹配
    • 值的个数
    • 值的类型
例

[例9] 对每一个系，求学生的平均年龄，并把结果存入数据库。

第一步：建表
CREATE TABLE Deptage
(Sdept CHAR(15), Avgage SMALLINT)
第二步：插入数据
INSERT INTO Deptage(Sdept，Avgage)
SELECT Sdept，AVG(Sage)
FROM Student
GROUP BY Sdept

3.2 修改数据

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

  • SET子句

    • 指定修改方式
      • 要修改的列
      • 修改后取值

  • WHERE子句

    • 指定要修改的元组
    • 缺省表示要修改表中的所有元组

  • 功能
    修改指定表中满足WHERE子句条件的元组

  • 三种修改方式

    • 修改某一个元组的值
    • 修改多个元组的值
    • 带子查询的修改语句

修改某一个元组的值

[例10] 将学生95001的年龄改为22岁。

UPDATE Student
SET Sage=22
WHERE Sno=' 95001 '；

修改多个元组的值

[例11] 将所有学生的年龄增加1岁。

UPDATE Student
SET Sage = Sage+1；

带子查询的修改语句

[例12] 将计算机科学系全体学生的成绩置零。

UPDATE SC
SET Grade=0
WHERE 'CS'=
(SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno)；

3.3 删除数据

  • 语法格式
DELETE FROM <表名> [WHERE <条件>];

  • 功能
    删除指定表中满足WHERE子句条件的元组

  • WHERE子句
    指定要删除的元组
    缺省表示要修改表中的所有元组

  • 三种删除方式

    • 删除某一个元组的值
      [例13] 删除学号为95019的学生记录。
      DELETE
      FROM Student
      WHERE Sno='95019'；
    • 删除多个元组的值
      [例14] 删除2号课程的所有选课记录。
      DELETE
      FROM SC
      WHERE Cno='2'；
      [例15] 删除所有的学生选课记录。
      DELETE
      FROM SC；
    • 带子查询的删除语句
      [例16] 删除计算机科学系所有学生的选课记录。
      DELETE
      FROM SC
      WHERE 'CS'=
      (SELETE Sdept
      FROM Student
      WHERE Student.Sno=SC.Sno)；

4 控制语言 DCL

  • 授予或回收访问数据库的某种特权
  • 控制数据库操纵事务发生的时间及效果
  • 对数据库实行监视等

5 视图

  • 视图是一种虚表（virtual table），是从一个或几个基本表（或视图）导出的表
    • 视图其实是在数据字典中存储的一条Select 语句
    • DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句
    • 在对视图查询时，按视图的定义从基本表中将数据查出
    • 对于某些视图（可更新视图），可以对视图执行数据更新操作，数据库会根据视图定义去更新对应的基本表数据

5.1 视图定义

在定义视图时，组成视图的属性列名需全部省略或全部指定

  • 当如下情形时，全部省略：
    视图属性由子查询中SELECT目标列中的诸字段组成
  • 当如下情形时，需明确指定视图的所有列名:
    • 某个目标列是集函数或列表达式
    • 多表连接时选出了几个同名列作为视图的字段
    • 需要在视图中为某个列启用新的更合适的名字
语法

CREATE VIEW
<视图名> [(<列名> [，<列名>]…)]
AS <子查询>
[WITH CHECK OPTION]；

例
建立新视图

[例17] 建立信息系学生的视图
CREATE VIEW IS_Student
AS
SELECT Sno，Sname，Sage
FROM Student
WHERE Sdept= 'IS'；

[例18] 建立信息系选修了1号课程的学生视图。
CREATE VIEW IS_S1(Sno，Sname，Grade)
AS
SELECT Student.Sno，Sname，Grade
FROM Student，SC
WHERE Sdept= 'IS' AND
Student.Sno=SC.Sno AND
SC.Cno= '1'；

基于视图建立新视图

[例19] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno，Sname，Grade
FROM IS_S1
WHERE Grade>=90；

带表达式的视图

[例20] 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno，Sname，Sbirth)
AS
SELECT Sno，Sname，2000-Sage
FROM Student
设置一些派生属性列, 也称为虚拟列--Sbirth
带表达式的视图必须明确定义组成视图的各个属性列名

带分组的视图

[例21] 将学生的学号及他的平均成绩定义为一个视图
假设SC表中“成绩”列Grade为数字型
CREAT VIEW S_G(Sno，Gavg)
AS
SELECT Sno，AVG(Grade)
FROM SC
GROUP BY Sno；

不建议以 SELECT * 方式创建视图，因这样创建的视图可扩展性差

[例22]将Student表中所有女生记录定义为一个视图
CREATE VIEW
F_Student1(stdnum，name，sex，age，dept)
AS SELECT *
FROM Student
WHERE Ssex='女'；
缺点：当修改基表Student的结构后，Student表与F_Student1视图的映象关系会被破坏，导致该视图不能正确工作。

[例23]将Student表中所有女生记录定义为一个视图
CREATE VIEW
F_Student2 (stdnum，name，sex，age，dept)
AS SELECT Sno，Sname，Ssex，Sage，Sdept
FROM Student
WHERE Ssex='女'；
为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。

5.2 视图使用

  • 视图查询：对视图的查询与对基本表的查询完全相同
  • 视图更新
    • 从用户角度，对视图的更新与对基本表的更新方法上也完全相同
    • 但是存在一些不可更新视图，因为对这些视图的更新不能唯一地/有意义地转换成对相应基本表的更新
      Pasted image 20250610160147.png

With Check Option

透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）
[例25] 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT *
FROM Student
WHERE Sdept= 'IS’
WITH CHECK OPTION

没有 WITH CHECK OPTION 时，如果插入(95008, '韩萍', 20, 'CS'），不会报错

5.3 视图的作用

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

4 数据库编程

数据库应用程序的两种形式

  • 数据库内部应用程序
  • 数据库外部应用程序
    Pasted image 20250411095602.png

1 数据库内部编程

数据库内部程序的常见形式：

  • 存储过程
  • 函数
  • 触发器

1.1 存储过程

  • 存储过程
    • 由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。
    • 通常将多次重复执行的代码段编写成一个过程（procedure orfunction)，保存在数据库中
  • 存储过程的优点
    • 是SQL和模块化编程的结合，能够完成复杂业务功能
    • 在创建的时候进行预编译，可以提高SQL执行效率
    • 位于数据库服务器上，调用的时候无需通过网络传输大量数据
    • 可以做为一种安全机制来加以充分利用。例如参数化的存储过程可以防止SQL注入式的攻击
使用
创建
CREATE PROCEDURE procedure_name (
	[ IN | OUT | INOUT ] param_name type [ ,... ]
)
[ BEGIN ]
	sql_statement
[ END ]
  • procedure_name：数据库服务器合法的对象标识
  • param_name：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数也可以定义输入参数、输出参数或输入/输出参数。默认为输入参数。
  • sql_statement：是一个过程化SQL块。包括声明部分和可执行语句部分
删除
DROP PROCEDURE prodedure_name
调用
CALL procedure_name (param_name type [ ,... ])
  • 数据库服务器支持在过程体中调用其他存储过程
语法
变量
流程控制
常用数据库函数与命令
嵌入式SQL
# 下面代码能将stuid=001的学生的cno=55的课程的score++
declare v_score int;
select score into v_score from sc where cno=55 and stuid=001; # into v_score
Set v_score=v_score+1;
Update sc set score=v_score where cno=55 and stuid=001; # score=v_score
动态SQL
  • 根据用户输入参数和/或数据库状态，动态确定程序中的SQL语句内容
  • Prepare：组装SQL语句
  • Exceute：动态执行SQL语句
-- 创建一个名为 count_field 的存储过程
-- 参数说明：
--   FIELDNAME: 输入参数，指定要查询的字段名（字符串类型）
--   FIELDVALUE: 输入参数，指定要匹配的字段值（整数类型）
CREATE PROCEDURE count_field(IN FIELDNAME VARCHAR(255), IN FIELDVALUE INT)
BEGIN
    -- 动态构建SQL语句（注意：实际使用需验证字段名防止SQL注入）
    -- 使用CONCAT拼接字符串，生成格式如：'SELECT COUNT(*) FROM stu WHERE age = ?'
    -- 问号(?)是参数占位符，后续会用实际值替换
    SET @sql = CONCAT('SELECT COUNT(*) FROM stu WHERE ', FIELDNAME, ' = ?');
    
    -- 准备预处理语句
    -- 将拼接好的SQL字符串编译为可执行的预处理对象
    PREPARE stmt FROM @sql;
    
    -- 设置参数值
    -- 将输入参数FIELDVALUE存入用户变量@fieldvalue
    SET @fieldvalue = FIELDVALUE;
    
    -- 执行预处理语句
    -- 使用USING子句将@fieldvalue的值绑定到SQL语句中的问号占位符
    -- 实际执行类似：SELECT COUNT(*) FROM stu WHERE age = 20
    EXECUTE stmt USING @fieldvalue;
    
    -- 释放预处理语句资源
    -- 清理已编译的语句对象，释放内存
    DEALLOCATE PREPARE stmt;
END;
游标编程
  • 应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对SELECT查询结果集的一种扩展
  • 游标可以对查询语句返回的行结果集中的每一行进行操作。
    主要功能包括：
    • 定位到结果集中的指定行
    • 从结果集的当前位置检索一行或多行
    • 可对结果集中当前位置的行进行数据修改
    • 可以显示其它用户对结果集中的数据库数据进行的数据更改
      Pasted image 20250411160521.png
CREATE PROCEDURE stu_count(c_age int)
BEGIN
	DECLARE p_age int; # 声明变量
	DECLARE p_c int; # 声明游标结束判断变量，默认值为0;
	DECLARE fetchSeqOk boolean DEFAULT 0;
	DECLARE my_cursor CURSOR for select age FROM t_user; -- 定义游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = 1;
	-- 游标执行结束时将会设置fetchSeqOk 变量为1
	-- 在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误
	SET p_c=0;
	
	OPEN my_cursor;  # 打开游标
		WHILE fetchSeqOk=0 DO. -- 判断是不是到了最后一条数据
			fetch my_cursor into p_age; -- 游标改变位置指向下一行，取下一行数据
			IF p_age<c_age THEN
				SET p_c=p_c+1;
			END IF;
		END WHILE;
		Select p_c as concat(’小于’, c_age, ’岁的总人数’); -- 输出结果
	CLOSE my_cursor; -- 关闭游标，释放内存
END

1.2 自定义函数

需求：
输入：字符串
输出：第一个字母大写，后面的字母小写

实现：

CREATE FUNCTION capitalize(input_string VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
	DECLARE output_string VARCHAR(255);
	SET output_string = 
		CONCAT(UPPER(LEFT(input_string,1)),LOWER(SUBSTRING(input_string, 2)));
	RETURN output_string;
END

使用：
select capitalize(’samPLeSTring’);
select capitalize(stu_name) from stu;

2 数据库应用系统开发

2.1 数据库访问与Python MySQL Client

2.1.1 数据库访问

  • 各种高级编程语言都提供访问和操作数据库的类库
  • 使用类库进行数据库开发的基本过程：
    Pasted image 20250411160845.png

2.1.2 Python MySQL Client

  • PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb。
  • PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
  • PyMySQL安装： pip install PyMySQL
事务机制——保证数据的一致性
  • 事务机制可以确保数据一致性。事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
    • 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
    • 一致性（consistency）。事务必须使数据库从一个一致的状态转换到另一个一致的状态。
    • 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
    • 持久性（durability）。指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

2.2 对象关系映射--ORM (Object-Relation Mapping)

2.2.1 简介

阻抗失配：由于关系模型的建模能力有限，面向对象的方法无法完全贯彻到关系数据库中。此外，应用程序需要嵌入SQL语言才能操纵数据库。反映了对象模型与关系模型之间的不匹配问题。

  • OR映射
    • 用户开发和维护一个中间件层，负责将对象数据映射到关系数据库的表中
    • 系统中其它模块可以通过OR映射层以操作对象的方法操作关系表中的数据
    • OR映射对前端开发人员屏蔽了数据库底层细节，使得他们可以专注于业务流程的实现，极大提高了应用系统开发的生产率
      Pasted image 20250411202623.png
  • 实现方案
    • Active Record
    • Data Mapper

2.2.2 Active record

  • Active record：一个对象既包含数据又包含行为。这些数据大部分是持久性的，需要存储在数据库中。Active Record使用最明显的方法，将数据访问逻辑放在域对象中。这样，所有人都知道如何在数据库中读取和写入数据。
  • 将对象和数据库表看作是一一对应的关系，一个对象对应一个数据库表中的一行数据，对象和行数据之间的映射关系由ORM框架自动维护。通过对象的属性来操作数据表，例如修改对象的属性后，直接通过ORM框架更新到数据库表中。
  • 优点：简单，容易理解
  • 缺点：耦合度高，性能较差

2.2.3 Data Mapper

  • Data Mapper：将对象和数据库表看作是两个独立的概念，对象类与数据库表之间没有必然的联系，需要手动定义对象属性和数据表的字段之间的映射关系。对象的读写操作不直接与数据库交互，而是通过数据访问对象（Data Access Object，DAO）来实现。
  • 优点：
    • 提供更好的灵活性，选择需要的对象属性进行存储
    • 使得应用层和数据层相对独立，隐藏了彼此的细节
    • 性能较好
  • 缺点：
    • 复杂，需要一定的学习成本，部署较为困难

2.2.4 总结

  • 优点
    • 提高开发效率
    • 数据库平台透明
    • 数据库结构自动维护
    • 代码可读性高
  • 缺点
    • 需要一定的学习成本
    • 性能问题
    • 不适用于复杂场景

5 存储和索引

1 存储

1.2 数据的存储结构

数据库逻辑存储结构

Pasted image 20250411112119.png

  • 块
    • 相当于磁盘页面
    • 块是数据库中最小的分配单元。一次I/O将读写一整个块。
  • 区
  • 段
    • 一个段只存储一类数据对象。例如有表段、索引段、回滚段等
    • 通常数据库中每个表对应于一个段
  • 表空间
    • 逻辑上表空间可由0个或多个段组成
    • 物理上表空间由一个或多个数据文件组成
    • 一个段不能跨越一个表空间,但可跨越表空间内的文件
    • 设置表空间的作用是将数据对象的逻辑结构和物理结构统一起来
  • 数据库
    • 包含了一个或多个表空间(如用户表空间、临时表空间、系统表空间等）
    • 组成数据库的表空间和数据文件是一对多关系。
      Pasted image 20250411112504.png
数据的存储结构
  • 文件组织结构
    数据文件中页面之间的组织方式
  • 常见文件结构类型
    • 堆组织表
    • 索引组织表
    • 聚簇表
      • 索引聚簇表
      • 散列聚簇表

2 索引

索引
  • 用于提高主表的查询速度
多级索引

数据记录数量庞大的情况下，单级索引效率太低

  • 时间复杂度：
    Pasted image 20250411113904.png
B+树索引 - 多级索引pro
  • 数据库系统中使用最广泛的多级索引
特点
  • 将索引键组织成一棵平衡树， 即从树根到树叶的所有路径一样长
  • 数据（指向基本表记录存储位置的指针）存储在叶结点
  • 最底层的叶节点包含每个索引键和指向被索引行的指针（行id）
  • 叶节点之间有通道可供平行查询
  • 每一个叶节点都和磁盘页面大小一致
  • 查询的时间复杂度： (m为分叉数，即B+树的阶）
B+树索引的更新
  • 设法在适当的叶结点中为新键找到空闲空间，如果有的话，就把键放在那里
  • 如果在适当的叶结点中没有空间，就把该叶结点分裂成两个，并且把其中的键分到这两个新结点中，使每个新节点有一半或刚好超过一半的键。
  • 某一层的结点分裂在其上一层看来，相当于是要在这一较高的层次上插入一个 新的键－指针对。因此，我们可以在这一较高层次上逆规地使用这个插入
    策略；如果有空间，则插入；如果没有，则分裂这个父结点且继续向树的高层推进。
  • 例外的情况是，如果试图插入键到根结点中并且根结点没有空间，那么我们就分裂根结点成两个结点，且在更上一层创建一个新的结点。这个新的根结点有两个刚分裂成的结点作为它的子结点。
    Pasted image 20250411114703.png
    Pasted image 20250411114712.png
    Pasted image 20250411114724.png
B+树索引重复键的处理

用一个链表存储重复的键值对应的记录行的RID值
Pasted image 20250411114937.png

B+树索引的效率
  • 一般B+树保持在3层，这意味着只需3次磁盘I/O即可获得数据的物理存储地址
  • 若将B+树的根节点和中间节点存入缓存（这是完全可以的），则只需1次磁盘I/O就能读取数据
何时使用B+树索引
  • 大部分情况下B+树索引都能工作得很好
  • 当要查询的记录数占记录总数的百分比非常大的时候，不用索引将比用索引更快
散列索引（Hash Index）
  • B+树索引需要3次左右磁盘IO才能查到数据记录
  • 散列索引只需一次磁盘IO就可以查到数据记录
  • 基本思想是：根据给定索引值，用一种算法将记录分散存储到多个“桶”中（一般一个桶就是一个数据块，块中内容用一次磁盘操作就可以读取到内存中）。当要查找记录时，用相同算法算出该记录所在的桶，读取整个桶的数据到内存中，然后在桶中顺序查找要找的纪录
    Pasted image 20250411115324.png
散列索引溢出块
  • 如果桶的数量足够多，则每个桶通常占用一个磁盘页面（块）
  • 如果记录数很多，则会出现一个块中容纳不下新记录的情况，这时可以增加一个溢出块到桶的链上
    Pasted image 20250411115400.png
散列索引特点
  • 散列索引是CPU密集型的，B+树索引是I/O密集型的（I/O次数多于散列索引）
  • 散列索引在进行等值查找时速度很快
  • 散列索引无法用于范围查找
  • 不适合在重复值很多的列上建立哈希索引
  • 哈希索引重构代价很大，不适合在更新频繁的表中建立哈希索引
聚簇索引（Cluster Index）
  • 大多数关系表以堆组织表的形式存放
  • 建立聚簇索引后，数据在物理文件中的存放位置不再是无序的，而是根据索引中键值的逻辑顺序决定了表中相应行的物理顺序，即形成索引组织表
    Pasted image 20250411115650.png
  • 聚簇索引特点
    • 物理顺序只有一个，因此一张表只能有一个聚簇索引
    • 在聚簇索引列上的查询速度比B+树索引快
    • 数据在物理上按顺序排在数据页上，重复值也排在一起，因而在使用包含范围检查(between、<、<=、>、>=)或使用group by或order by的查询时，可以大大提高查询速度
    • DML频繁的表中不要建立聚簇索引，因为会带来大量索引数据维护的开销
    • MySQL在表的主键上建立聚簇索引
联合索引

  • 若要加速如下的查询，该怎么建立索引？
    Select * from tb_a where a>1 and b=1

  • 通过在（a，b）字段上建立联合索引，可以获得比单独建立a字段索引和b字段索引更快的查找速度

  • 最左前缀原则：只有在查询条件中使用了联合索引的最左前缀（左边字段） 时，该联合索引才回生效
    上例中若执行下述查询语句则联合索引不生效
    Select * from tb_a where b=1

  • 查看如下SQL语句：
    Select * from Emp whereage / 2>20

  • 即使在age字段上建立了索引，但索引对上述SQL语句不起作用

  • 解决办法：改写表达式，使表达式左边不包含计算式
    Select from Emp where age > 20 2

  • 另一个例子：
    Select * from Emp where to_char(birth_day,‘YYYY-MM-DD’) = ‘2022-11-10’，怎么改写？
    需要用到函数索引：将to_char(birth_day,‘YYYY-MM-DD‘) 定义为索引

索引的选择

针对不同的数据情况选择合适的索引类型，考虑因素如：

  • 重复值占比

  • 列值是否被频繁更新

  • 是否范围查询或分组查询

  • 索引按照字段特性还可分为：

    • 主键索引
      • 建立在主键字段上的索引
      • 一张表最多只有一个主键索引
      • 索引列的值不允许有空值
    • 唯一索引
      • 建立在 UNIQUE 字段上的索引
      • 一张表可以有多个唯一索引
      • 索引列的值必须唯一，但是允许有空值

6 查询优化

sql语句执行过程
Pasted image 20250411121130.png

查询优化的一般准则
  • 选择运算应尽可能先做
    目的：减小中间关系
  • 在执行连接操作前对关系适当进行预处理
    按连接属性排序
    在连接属性上建立索引
  • 投影运算和选择运算同时做
    目的：避免重复扫描关系
  • 将投影运算与其前面或后面的双目运算结合
    目的：减少扫描关系的遍数
  • 提取公共子表达式
查询优化的一般过程
  1. 将查询转换成某种内部表示，通常是语法树
  2. 根据一定的等价变换规则把语法树转换成标准（优化）形式 （代数优化）
  3. 选择低层的操作算法（物理优化）
    • 对于语法树中的每一个操作计算各种执行算法的执行代价
    • 选择代价小的执行算法
  4. 生成查询计划(查询执行方案)
例

Pasted image 20250411211156.png
Pasted image 20250411211205.png
Pasted image 20250411211215.png
Pasted image 20250411211518.png

7 完整性约束

7.1 完整性约束

完整性控制机制
  • 完整性约束条件定义机制：定义约束什么
  • 完整性检查机制：检查是否违背约束
  • 违约反应：如果违背约束要怎么做
完整性约束条件

  • 完整性约束条件作用的对象

    • 列：对属性的取值类型、范围、精度等的约束条件
    • 元组：对元组中各个属性列间的联系的约束
    • 关系：对若干元组间、关系集合上以及关系之间的联系的约束

  • 对象的两种状态：静态（状态）、动态（变迁）

  • 分类：3*2

  • 静态列级约束：对列的取值域的说明

  • 静态元组约束：规定元组的各个列之间的约束关系

    • 如有订货关系（ID，商品，订货量，发货量），要求每条记录中：发货量<=订货量
    • 用 Check 约束实现

  • 静态关系约束：关系的各个元组之间或若干关系之间存在的各种联系或约束

    • 分类：
      • 实体完整性约束
      • 参照完整性约束
      • 函数依赖约束
      • 统计约束
    • 需用触发器实现
    • 例
      工资表（职工ID，，工资，是否部门经理），要求：
      职工平均工资的2倍<=部门经理的工资<=职工平均工资的5倍

  • 动态列级约束：修改列定义或列值时应满足的约束条件

    • 修改列定义时的约束
      例：将原来允许空值的列改为不允许空值时:
      该列目前已存在空值，则拒绝这种修改（数据库自动实现）
    • 修改列值时的约束
      修改列值时新旧值之间要满足的约束条件（使用触发器实现）
      例：职工工资调整 >= 原来工资
      年龄只能增长

  • 动态元组约束：修改元组值时各个字段之间要满足的约束条件
    例: 职工工资调整不得低于其原来工资 + 工龄*1.5
    用触发器实现

  • 动态关系约束：对关系变化前后状态的限制条件
    例：事务一致性、原子性等约束条件
    具体内容在事务处理部分讲解

完整性约束另一种分类方法
  • 最重要的几类完整性约束条件：
    • 域完整性
    • 实体完整性约束
    • 参照完整性约束
      可以方便地在数据库中设置
  • 其它的完整性约束条件：
    • 用户自定义完整性约束
      往往需编程实现（触发器或Check等）

7.2 完整性控制

  • 完整性控制机制：指完整性约束的定义、检查和违约反应规则

  • 完整性规则五元组表示：(D，O，A，C，P)

    • D（Data） 约束作用的数据对象；
    • O（Operation） 触发完整性检查的数据库操作
      当用户发出什么操作请求时需要检查该完整性规则
      是立即检查还是延迟检查；
    • A（Assertion） 数据对象必须满足的断言或语义约束，是规则的主体；
    • C（Condition） 选择A作用的数据对象值的谓词（insert/modify/...）
    • P（Procedure） 违反完整性规则时触发的过程。

    例：在“学号不能为空”的约束中
    D 约束作用的对象为Sno属性
    O 插入或修改Student 元组时
    A Sno不能为空
    C 无（A可作用于所有记录的Sno属性）
    P 拒绝执行该操作

7.3 违约反应

  • 违反参照完整性的操作的违约反应：
    • （通常）拒绝执行
    • 接受这个操作，同时执行一些附加的操作，以保证数据库的状态正确
参照完整性违约反应
  • 外键是否可以取空值：依赖应用环境语义而定
  • 在被参照关系中删除元组时，若参照关系有若干元组的外键值与被删除的被参照关系的主键值相同，此时的违约反应可以是：
    删掉21系
    • 级联删除（CASCADES）：一起也都给删了
      21系的所有人都被删了
    • 受限删除（RESTRICTED）：删一部分
    • 置空值删除（NULLIFIES）：相关的值置空
      21系所有人的系号置为空值
  • 在参照关系中插入元组时，若被参照关系不存在相应的元组，则违约反应可以是：
    插入一个60系的同学（buaa目前没有60系）
    • 受限插入
    • 递归插入
      建60系
  • 修改被参照关系中的主键，若主键在参照关系中已经被引用，则违约反应可以是：
    • 级联修改
    • 受限修改
    • 置空值修改

7.4 用户自定义约束的实现

7.4.1 Check约束

  • CHECK 约束通过限制输入到列中的值来强制域的完整性。
  • 常用于实现静态列级约束和静态元组约束。
  • 可以通过任何基于逻辑运算符返回结果 TRUE 或 FALSE 的逻辑（布尔）表达式来创建 CHECK 约束。
    表达式可包含列名、比较运算符（>、<、 =等）、逻辑运算符(and、or等)、条件谓词（in、like等），可使用pattern（如_、%通配符，[]选择符等）

[例1] 欲将 salary 列的取值范围限制在 $15,000 至 $100,000 之间，则Check约束为：
salary >= 15000 AND salary <= 100000
检查Email字段的格式：
email like ‘%_@_%._%’
学号是一个五位字符串，前二位为字符，后三位为数字，则可定义CHECK约束如下：
SID like ‘__[0,9][0,9][0,9]’

7.4.2 触发器

  • 用于实现复杂逻辑的用户自定义约束的工具

  • 是一种特殊的存储过程

    • 与表（或视图、数据库等）紧密相连，不能脱离宿主存在
    • 由数据库自动调用执行，用户不能调用
    • 没有参数和返回值

  • 是一种Event-Condition-Action规则的实现

CREATE [ OR REPLACE ] TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF]
trigger_event ON table_reference
[FOR EACH ROW [ WHEN trigger_condition ] ]
trigger_body;
触发器

  • 事件 INSERT

    • 表/视图级事件：INSERT / DELETE / UPDATE
    • 数据库级事件：CREATE / ALTER / DROP TABLE /PROCEDURE / VIEW…

  • 触发的时机 AFTER

    • AFTER /BEFORE /INSTEAD OF
    • Instead Of 触发器会替换掉用户提交的操作语句。常用于不可更新视图的更新。
      想给所有课的平均分都+1分，但是加不到同学成绩上，只能加到平均分上。可以用Instead of来代替给所有学生的所有课+1分

  • 触发/执行粒度 FOR EACH ROW

    • 语句级触发器 - 缺省 - 每条用户语句触发一次
    • 行级触发器 - FOR EACH ROW - 对于表中每条受影响记录，触发一次触发器

  • Referencing引用 REFERENCING NEW ROW AS NewTuple

    • 名为“Inserted”的表，包含新插入的行
    • 名为“Deleted” 的表，包含删除的行
    • 在触发器业务逻辑中可以通过引用来处理新值、旧值
    • OpenGauss、MySQL等数据库中可以直接使用关键字New、Old来引用新、旧值：New.Attribute,Old.Attribute

  • 动作 INSERT INTO Fruits(Fname) VALUES(NewTuple.Fname)

    • 实现自定义约束的业务逻辑
    • 通常用于进行关联数据更新，不能返回查询结果
    • 若需多于一条语句，则可用程序块实现：Begin…End
    • 拒绝更新的违约动作：引发系统错误，提示数据库拒绝更新，可用RaiseError（SQLServer）、Signal（MySQL）、Raise（OpenGauss）函数
      Pasted image 20250418105648.png

  • Instead of 触发器

    • 将用户提交的语句替换成触发器代码
    • 在视图上创建的 Instead of 触发器可以实现不可更新视图的更新
Instead of 触发器 普通触发器 (Before/After)
作用对象 仅视图 表或视图
原始操作是否执行 ❌ 完全替代 ✔️ 原始操作仍会执行
触发时机 在操作前替代 Before：操作前执行逻辑
After：操作后执行逻辑
级别 只能是行级 支持行级或语

8 数据库安全

8.1 安全认证

  • 安全认证:确认试图登录数据库的用户是否被授权访问数据库的过程
  • 认证方式
    • 数据库认证
    • 外部认证：
      • 由操作系统或网络服务执行身份验证
      • 通过中间层服务器来验证用户的身份
      • 其它认证方式
数据库认证
  • 使用存储在数据库中的信息对连接到数据库的用户进行验证
  • 最常用的方法：密码认证
  • 缺点：
    • 密码容易被盗、伪造和滥用
    • 难以应对复杂的网络攻击
    • 频繁的认证操作影响数据库性能
外部认证
  • 强身份认证：进行双因素(如密码+短信)或多因素身份验证
  • 代理认证：让中央设施对网络的所有成员进行身份验证

8.2 访问控制

  • 访问控制：按照用户的身份和权限，控制用户对数据库中数据访问

  • 权限：允许或拒绝数据库用户提出的数据访问请求

  • 访问控制关注的问题

    • 阻止访问：无权限时，主体不能访问客体
    • 确定访问权限：确定主体是否有权对客体进行访问
    • 授予访问权限：授予主体访问客体的权限
    • 撤销访问权限：删除主体对客体的访问权限

  • 定义存取权限：在数据库系统中，为了保证用户只能访问他有权存取的数据，必须预先对每个用户定义存取权限。

  • 检查存取权限：对于通过鉴定获得上机权的用户（即合法用户），系统根据他的存取权限定义对他的各种操作请求进行控制，确保他只执行合法操作。

  • 存取权限由两个要素组成

    • 数据对象
    • 操作类型
权限分类
  • 系统权限
    • 启动、关闭数据库
    • 转储、恢复数据库
    • 创建、删除数据库等等
  • 数据对象权限
    • 对基本表，视图，存储过程，函数等数据库对象的操作权限
      增加、删除、修改等
  • 列级权限：包括列的添加、修改、删除等
  • 行级权限：包括行的插入、修改、删除等
  • 连接级权限：包括连接控制等
权限控制

  • SQL DCL（Data Control Language)中的权限控制语句

    • 权限授予：Grant
    • 权限收回：Revoke
      例：

  • 对指定用户授予、收回表级查询权限

    • Grant select on TableA on UserA
    • Revoke select on TableA from UserA

  • 限定列级权限

    • Grant select (a, b) on TableA on UserA

  • 如何创建新用户？
    授予基本的登录数据库和查看数据的权利

    • Create user username IDENTIFIED BY ‘password’
    • Grant Usage on dbname.* to username@‘%’

  • 谁才能授予、收回权限？

    • 数据对象的创建者
    • DBA
    • 拥有传播权限的用户
      • With Grant Option
      • Grant select on TableA on UserA with grant option
      • 此时UserA可以向其他用户授予TableA的Select权限
角色

角色是命名的权限集合，使用角色可以方便的进行授权管理

  • 角色分类

    • 服务器角色：系统内建，不可自建
    • 数据库角色：可自建
    • Public角色：代表所有用户都具有的权限集合

  • 角色可以从属于别的角色，获得别的角色定义的权限（即角色可以继承）

  • 创建角色：Create Role roleA;
    当前用户需具备创建角色的权限

  • 向角色逐个授予权限
    Grant insert, delete on tableA to roleA; Grant execute on procedureA to roleA

  • 将角色赋予指定用户

    • Grant roleA to UserA
    • 此时UserA就继承了roleA的权限
    • 也可以对用户UserA赋予roleA之外的权限

9 并发控制

9.1 并发控制

数据库的一致性（Consistency）
  • 数据的一致性：在任何时刻用户面对的数据库都是符合现实世界的语义逻辑的。
  • 数据库的并发控制是以事务为基本单位进行的，通过对事务所操作的数据施加封锁来实现一致性
    • 这种数据一致性约束属于动态关系约束

9.2 事物

  • 事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位，是并发控制的基本单位，是遇到各类数据库错误（包括软硬件故障）后进行数据恢复的处理单位
  • 事务和程序是两个概念
    • 一个事务可以是一条SQL语句或一组连续的SQL语句
    • 一个对数据库进行操作的应用程序可以包含多个事务，而这些事务是串行的
SQL中事务的定义
  • 事务的开始与结束可以由用户显式控制。如果用户没有显式地定义事务，则由DBMS按照缺省规定自动划分事务（通常一条DML语句为一个事务）
  • 用户自定义的事务以SQL语句 Begin transaction 开始，以Commit 或 Rollback 结束。
    • COMMIT 表示事务的提交，将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，此时事务正常结束；
    • ROLLBACK 表示事务的回滚，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的更新操作全部撤销，再回滚到事务开始时的状态
事务的ACID特征
  • 为了保护数据的完整性，一般要求事务具有ACID四个特征
  • 原子性（ Atomicity ）：十五中的操作要么全做，要么全不做
  • 隔离性（ Isolation ）：并发执行的各个事物之间互不干扰
  • 一致性（ Consistency）:数据库在事务执行前是一致的，执行过程中可能暂时出现不一致，而当事务执行完后，数据库仍会处于一致性状态
  • 持久性（ Durability ）:一个事务一旦提交（commit）之后，它对数据库的影响必须是永久的。系统发生失效不能改变事务的持久性。
事务的执行状态
  • 活动状态（Active） – 是事务的初始状态；事务在执行的时候处于这种状态
  • 部分提交状态（Partially committed） – 对数据库进行了部分更新，但事务并未最后结束的状态
  • 失败状态（Failed) – 由于物理或逻辑错误导致事务语句无法进行下去的状态
  • 中止状态（Aborted） – 事务回滚所有操作，数据库恢复到事务开始之前的状态
  • 提交状态（Committed） – 事务正确执行完成的状态
    Pasted image 20250418120119.png
数据库怎么保证事务特性的实现？
  • 数据库的恢复机制保证了原子性和持久性的实现
  • 一种简单的恢复机制： shadow-database影子数据库技术
    Pasted image 20250418120950.png
    • 假定同时只有一个事务是活动的（不存在隔离性问题）
    • 对数据库的更新都在影子数据库上进行。当事务更新都完成后，数据库指针db-pointer指向影子数据库，将之变成当前数据库。若事务出现错误，则删除影子数据库
  • 恢复机制不能解决事务并发执行情况下的数据一致性、隔离性问题
实物的并发控制机制
  • 对事务的并行运行顺序进行合理安排，达成事务的隔离性和一致性
  • 主要手段：
    • 调度
    • 锁
    • 协议
调度 (Schedule)
  • 调度是并发运行的事务中各条指令的执行序列
    • 调度应对各事务所有指令的执行时间顺序做出安排
    • 调度应保留同一事务中各指令原有的执行顺序
  • 将所有事务串行起来的调度策略一定是正确的调度策略。
    • 以不同的顺序串行执行事务也有可能会产生不同的结果，但由于不会将数据库置于不一致状态，所以都可以认为是正确的。
保证并发调度正确性的方法：
  • 封锁方法（Locking）：事务锁定使用的资源以防止其它事务访问 /修改
  • 时标方法（Time-Stamping ）：为每一个事务分配一个全局唯一的时间戳
  • 乐观方法：假定大多数数据库操作是不冲突的
  • 多版本并发控制：维护数据对象的多个版本
封锁方法
死锁

Pasted image 20250425114200.png

  • 解决死锁的方法
    1. 预防死锁：破坏产生死锁的条件
      一次封锁法：
      • 要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行
      • 一次封锁法存在的问题：将以后要用到的全部数据加锁，势必扩大了封锁的范围，从而降低了系统的并发度
    2. 死锁的诊断与解除
      • 由DBMS的并发控制子系统定期检测系统中是否存在死锁
      • 一旦检测到死锁，就要设法解除
    3. 等待图法检测死锁
      • 用事务等待图动态反映所有事务的等待情况
        • 事务等待图是一个有向图G=(T，U)
        • T为结点的集合，每个结点表示正运行的事务
        • U为边的集合，每条边表示事务等待的情况
        • 若T1等待T2，则T1，T2之间划一条有向边，从T1指向T2
      • 系统进行周期性检查，如果发现图中存在回路，则表示出现了死锁，为此选择一个事务回滚，打破死锁
        Pasted image 20250425114540.png

10 备份与恢复

分类

逻辑备份恢复

  • 利用SQL从数据库中提取数据，并将其存入文件中。这些数据可重新导入原来的数据库，也可以导入到其他数据库中
  • 通常使用DBMS提供的导出/导入工具包实现（如MySQLDump）
  • 备份效率低，影响数据库运行性能，数据量大时不适用
  • 常用于小型数据库迁移和数据维护
    物理备份恢复
  • 基于数据文件（和日志文件）的备份恢复
  • 备份效率高，对数据库运行性能影响小,是保证数据库安全性的重要手段
  • 备份策略复杂
  • 本章主要讨论物理备份恢复的方法
备份与恢复
  • DBMS提供恢复子系统：保证故障发生后，能把数据库中的数据从错误状态恢复到某种逻辑一致的状态
  • 恢复技术是系统安全性的重要保证，保证事务的原子性和持久性
环境设定

Pasted image 20250509101015.png

  • 事务原语

    • INPUT(X): 把数据项 X 从磁盘读到内存缓冲区
    • OUTPUT(X): 将数据项 X 写到磁盘里记录下来
    • READ(X,t): 把数据项 X 拷贝到内存和事务的本地变量 t 中
    • WRITE(X,t): 把事务本地变量 t 拷贝回数据项 X（内存中）
      粗略认为：
      READ WRITE: 事物处理
      INPUT OUTPUT: 磁盘和内存之间

  • 缓冲区内容和磁盘内容不一定一致

  • 缓冲区处理策略

    • Force： 内存中的数据最晚在commit的时候写入磁盘
    • No Force： 内存中的数据可以一直保留，在commit之后过一段时间再写入磁盘（系统崩溃时数据可能还没写入磁盘，没能持久化）
    • No Steal： 不允许在commit之前把内存中数据写入磁盘
    • Steal：允许在事务commit之前把内存中的数据写入磁盘（系统崩溃时可能有未提交的数据被持久化了）

  • 为提升数据读写性能，当前数据库常用No Force+Steal 策略

1 故障的种类

1.1 事务故障

  • 事务故障：某个事务在运行过程中由于种种原因未运行至正常终止点就夭折了
  • 事务故障的常见原因
    • 输入数据有误
    • 运算溢出
    • 违反了某些完整性限制
    • 某些应用程序出错
    • 并行事务发生死锁
  • 只影响故障事务（程序）本身
  • 事务故障的恢复
    • 发生事务故障时，夭折的事务可能已把对数据库的部分修改写回磁盘
    • 事务故障的恢复
      撤消事务（UNDO）

1.2 系统故障

  • 系统故障
    • 整个系统的正常运行突然被破坏
    • 所有正在运行的事务都非正常终止
    • 内存中数据库缓冲区的信息全部丢失
    • 外部存储设备上的数据未受影响
  • 系统故障的原因
    • 操作系统或DBMS代码错误
    • 操作员操作失误
    • 特定类型的硬件错误（如CPU故障）
    • 突然停电
  • 影响到当前正在运行的事务或部分已经运行完结的事务
  • 系统故障的恢复
    • 清除尚未完成的事务对数据库的所有修改
      系统重新启动时，恢复程序要强行撤消（UNDO）所有未完成事务
    • 将缓冲区中已完成事务提交的结果写入数据库
      系统重新启动时，恢复程序需要重做（REDO）所有已提交的事务
    • 实现上，需要日志文件的帮助

1.3 介质故障

  • 硬件故障使存储在外存中的数据部分丢失或全部丢失
  • 介质故障比前两类故障的可能性小得多，但破坏性大得多
  • 硬件故障
    • 磁盘损坏
    • 磁头碰撞
    • 操作系统的某种潜在错误
    • 瞬时强磁场干扰
  • 影响到内存中的数据和磁盘上的数据
  • 介质故障的恢复
    • 装入数据库发生介质故障前某个时刻的数据副本
    • 重做自此时始的所有成功事务，将这些事务已提交的结果重新记入数据库
    • 实现上，需要数据转储副本（即备份数据）和日志文件的帮助

2 恢复的实现技术

2.1 数据转储

  • 数据转储：指DBA将整个数据库复制到磁带或另一个磁盘上保存起来的过程。
    这些备用的数据文本称为后备副本，或备份。
  • 数据转储方法
    • 静态转储与动态转储
    • 全量转储与增量转储

2.2 登记日志文件

  • 日志文件是用来记录事务对数据库的更新操作的文件
  • 顺序写入，写入效率高（数据文件是随机写入，效率低）
  • 事务操作先写入日志文件，写成功后，再写入数据缓冲区，最后再择机写入数据文件
    Pasted image 20250509105606.png

3 恢复的策略

3.5 ARIES算法

Algorithms for Recovery and Isolation ExploitingSemantics（基于语义的恢复与隔离算法），多数数据库采用的数据恢复策略

  1. Write-Ahead Logging—WAL策略
    • 出于性能上的考虑，数据的修改都是在内存中进行，并将这些修改操作记录到日志（redo log和undo log）中，然后异步将内存中的数据写入到磁盘；日志也并非立刻写入至磁盘，而是先写入到 log buffer，再按照相应的参数配置进行磁盘的写入操作（立即或定期）
    • 数据文件是随机写，性能差；日志文件是顺序写，效率高
    • 事务提交的信息只要写入日志文件即可保证持久性: no force
  2. 系统故障时，先进行分析，利用Checkpoint及日志中的信息找出所有活跃事务列表
  3. 随后通过Redo log恢复数据
    • 对于列表中所有事务，重做事务操作，保证事务持久性
    • 未提交事务也重做，保证事务所修改数据没有逻辑不一致性（例如故障时事务删了表里的一条数据，但还没更新索引）
    • 把已重做的已经提交的事务从待处理事务列表中删除
  4. 最后通过Undo log回滚数据
    • 对于事务列表中剩下的事务（未提交事务）进行回滚操作，保证事务原子性

11 关系数据理论

什么是规范化理论

数据库规范化理论是设计关系型数据库表结构的核心方法论，旨在通过消除数据冗余、依赖关系和异常，提升数据一致性和存储效率。其核心思想是将复杂表逐步分解为更小、结构更简单的表，满足不同范式（Normal Form）的要求。

规范化理论的主要内容
  1. 函数依赖
  2. 范式（Normal Form）
  3. 模式分解
    其中，函数依赖起着核心的作用，是模式分解和模式设计的基础，范式是模式分解的标准。

一个好的关系模式应该具备以下四个条件：

  1. 尽可能少的数据冗余。
  2. 没有插入异常。
  3. 没有删除异常。
  4. 没有更新异常。
  • 如何按照一定的规范设计关系模式，将结构复杂的关系分解成结构简单的关系，从而把不好的关系数据库模式转变为好的关系数据库模式，这就是关系的规范化。

1 函数依赖

函数依赖的定义
  • 关系模式中的各属性之间相互依赖、相互制约的联系称为数据依赖。
  • 数据依赖一般分为函数依赖、多值依赖和连接依赖。
  • 其中，函数依赖是最重要的数据依赖。

函数依赖（Functional Dependency）是关系模式中属性之间的一种逻辑依赖关系。

  • 例如在上一节介绍的关系模式SCD中，SNO与SN、AGE、DEPT之间都有一种依赖关系。
  • 由于一个SNO只对应一个学生，而一个学生只能属于一个系，所以当SNO的值确定之后，SN，AGE，DEPT的值也随之被唯一的确定了
  • 这类似于变量之间的单值函数关系。设单值函数Y=F(X)，自变量X的值可以决定一个唯一的函数值Y。
  • 在这里，我们说SNO决定函数（SN，AGE，DEPT），或者说 （SN，AGE，DEPT）函数依赖于SNO。

函数依赖的形式化定义
设关系模式R(U，F)，U是属性全集，F是U上的函数依赖集，X和Y是U的子集，如果对于R(U)的任意一个可能的关系r，对于X的每一个具体值，Y都有唯一的具体值与之对应，则称X决定函数Y，或Y函数依赖于X，记作X→Y。我们称X为决定因素，Y为依赖因素。当Y不函数依赖于X时，记作：⇸。当且时，则记作：。

有关函数依赖的几点说明
  1. 函数依赖是语义范畴的概念：我们只能根据语义来确定一个函数依赖，而不能按照其形式化定义来证明一个函数依赖是否成立。
  2. 函数依赖关系的存在与时间无关：当关系中的元组增加、删除或更新后都不能破坏这种函数依赖。
  3. 函数依赖可以保证关系分解的无损连接性
    • 设R（X，Y，Z），X，Y，Z为不相交的属性集合，如果X→Y或X→Z，则有R(X，Y，Z) = R1[X，Y] ⋈ R2[X，Z]
    • 其中，R1[X，Y]表示关系R在属性（X，Y）上的投影
    • 即R等于其投影在X上的自然连接，这样便保证了关系R分解后不会丢失原有的信息，称作关系分解的无损连接性。
    • 基于上述原则的分解方法称为投影分解

2 范式

  • 规范化的基本思想是消除关系模式中的数据冗余，消除数据依赖中的不合适的部分，解决数据插入、删除时发生的异常现象。
  • 我们把关系数据库的规范化过程中为不同程度的规范化要求设立的不同标准称为范式（Normal Form）。
  • 由于规范化的程度不同，就产生了不同的范式。
    • 满足最基本规范化要求的关系模式叫第一范式，
    • 在第一范式中进一步满足一些要求为第二范式，
    • 以此类推就产生了第三范式等概念。
  • 一个低一级范式的关系模式，通过模式分解转化为若干个高一级范式的关系模式的集合，这种分解过程叫作关系模式的规范化（Normalization）
非规范化设计
  • 非规范化设计：在特殊条件和要求下，适当地降低甚至抛弃关系模式的范式，不再要求一个表只描述一个实体或者实体间的一种联系。其主要目的在于提高数据库的运行效率
  • 一般认为，在下列情况下可以考虑进行非规范化处理：
    • 大量频繁的查询过程所涉及的表都需要进行连接；
    • 主要的应用程序在执行时要将表连接起来进行查询；
    • 对数据的计算需要临时表或进行复杂的查询。
  • 非规范化处理的主要技术包括增加冗余或派生列，对表进行合并、分割或增加重复表等。
  • 管理方式较常采用的是用触发器来实现。
增加冗余列

增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作，但它需要更多的存储空间，同时增加表维护的工作量。

例如，如果经常检索一门课的任课教师姓名，则需要做class和teacher表的连接查询：
select classname,teachername from class,teacher where class.teacherNo=teacher.teacherNo
这样的话就可以在class表中增加一列 teacher_name，就不需要连接操作了。

增加派生列

增加派生列指增加的列来自其它表中的数据，由它们计算生成。它的作用是在查询时减少连接操作，避免使用集函数。派生列除于冗余列同样的缺点之外，还需消耗计算资源在数据变化时对派生列的值进行及时维护。

重新组表

重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能，但需要更多的磁盘空间，同时也损失了数据在概念上的独立性

例如，用户经常需要同时查看课程号，课程名称，任课教师号，任课教师姓名，则可把表class(classno,classname,teacherno) 和表teacher(teacherno,teachername) 合并成一个表class_Teacher(classno,classname,teacherno,teachername)。

表分割

对表做分割可以提高性能，特别是在进行大数据量管理时，常采用“分库分表”策略
表分割有两种方式：

  • 水平分割
  • 垂直分割

水平分割

  • 根据一列或多列数据的值把数据行放到两个独立的表中。
  • 分割方法包括范围分区、散列分区、列表分区等
  • 水平分割通常在下面的情况下使用：
    • 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
    • 表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
  • 需要把数据存放到多个介质上。

垂直分割

  • 把主键和一些列放到一个表，然后把主键和另外的列放到另一个表中。
  • 如果一个表中某些列常用，而另外一些列不常用，就可以采用垂直分割加快查询速度。其缺点是需要管理冗余列，查询所有数据需要join操作。
特点
  • 非规范化设计的主要优点
    • 减少了查询操作所需的连接
    • 减少了外部键和索引的数量
    • 可以预先进行统计计算，提高了查询时的响应速度
  • 非规范化存在的主要问题
    • 增加了数据冗余
    • 影响数据库的完整性
    • 降低了数据更新的速度
    • 增加了存储表所占用的物理空间

3 模式分解

模式的分解
  • 定义：关系模式 的一个分解：，，，，，且不存在 ，则称 为 在 上的投影
  • 函数依赖集合 的一个覆盖 叫作 在属性 上的投影
数据依赖的公理系统
  • 逻辑蕴含
    对于满足一组函数依赖 的关系模式 ，，其任何一个关系 ，若函数依赖 都成立, 则称 逻辑蕴含
  • Armstrong公理系统
    • 一套推理规则，是模式分解算法的理论基础
    • 用途
      • 从一组函数依赖求得蕴含的函数依赖
      • 求给定关系模式的候选键

对关系模式R <U，F >，有以下的推理规则：

  • A1自反律（Reflexivity）：若，则 为 所蕴含。

  • A2增广律（Augmentation）：若为所蕴含，且，则为 所蕴含。

  • A3传递律（Transitivity）：若及为 所蕴含，则为 所蕴含。
    注意：由自反律所得到的函数依赖均是平凡的函数依赖

  • 根据A1，A2，A3这三条推理规则可以得到下面三条推理规则：

    • 合并规则：由，有。
    • 伪传递规则：由，有。
    • 分解规则：由及 ，有 。

  • 根据合并规则和分解规则，可得引理： 成立的充分必要条件是 成立

函数依赖闭包
  • 在关系模式 中为 所逻辑蕴含的函数依赖的全体叫作 的闭包，记为 。
    蕴含==导出
  • 定义
    设为属性集上的一组函数依赖，，则 能由根据公理导出
    称为属性集 关于函数依赖集 的闭包
  • 引理
    设为属性集上的一组函数依赖，，能由 根据Armstrong公理导出的充分必要条件是
  • 用途
    将判定 是否能由 根据Armstrong公理导出的问题，转化为求出 ，判定是否为的子集的问题

例
Pasted image 20250523110754.png
Pasted image 20250523110809.png

Pasted image 20250523110940.png

  • 建立公理系统体系目的：从已知的 f 推导出未知的f
  • 有效性：由F出发根据Armstrong公理推导出来的每一个函数依赖一定在F+中
    Armstrong公理系统是正确的
  • 完备性：F+中的每一个函数依赖，必定可以由F出发根据Armstrong公理推导出来
    Armstrong公理是完备的
    函数依赖集等价
  • 如果G+=F+，就说函数依赖集 F覆盖G（F是G的覆盖，或G是F的覆盖），或F与G等价。
  • F+ = G+ 的充分必要条件是
    和
    要判定，只须逐一对 F 中的函数依赖X→Y，考察 Y是否属于就行了。
最小依赖集
  • 每一个函数依赖集F 均等价于一个极小函数依赖集Fm。此Fm称为F 的最小依赖集
  • 如果函数依赖集 F 满足下列条件，则称 F 为一个极小函数依赖集。亦称为最小依赖集或最小覆盖。
    • F中任一函数依赖的右部仅含有一个属性。
    • F中不存在这样的函数依赖X→A，使得F 与 F-{X→A}等价。
    • F中不存在这样的函数依赖X→A， X有真子集Z 使得F-{X→A}∪{Z→A}与F等价。
      Pasted image 20250523111550.png
无损连接性

关系模式的一个分解，

  • 若与、、…、自然连接的结果相等，则称关系模式的这个分解 具有无损连接性（Lossless join）
  • 具有无损连接性的分解保证不丢失信息
  • 无损连接性不一定能解决插入异常、删除异常、修改复杂、数据冗余等问题
函数依赖保持性

设关系模式，被分解为若干个关系模式，其中，且不存在，Fi为F在Ui上的投影。如果F 所蕴含的任意一个函数依赖一定也由 所蕴含，则称关系模式R的分解具有函数依赖保持性。

无损连接性与函数依赖保持性

分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。具有无损连接性的分解不一定能够保持函数依赖。同样，保持函数依赖的分解也不一定具有无损连接性。
Pasted image 20250523114312.png

12 数据库设计

1 概述

任务

数据库设计是指根据用户需求研制数据库结构的过程，具体地说，是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能有效的存储数据，满足用户的信息要求和处理要求。

内容

数据库设计包括静态的数据库结构设计和动态的数据库行为设计（即数据库应用程序设计）两方面的内容

  • 数据库的结构设计
    • 数据库的结构设计是指根据给定的应用环境，进行数据库的模式的设计。
    • 它包括数据库的概念设计、逻辑设计和物理设计。
    • 数据库模式是各应用程序共享的结构，是静态的、稳定的，一经形成后通常情况下是不容易改变的，所以结构设计又称为静态模型设计。
设计方法

数据库设计方法主要有直观设计法和规范设计法

  • 直观设计法也叫手工试凑法。这种方法依赖于设计者的经验和技巧，缺乏科学理论和工程原则的支持，设计的质量很难保证
  • 新奥尔良法：将数据库设计分成需求分析（分析用户需求）、概念设计（信息分析和定义）、逻辑设计（设计实现）和物理设计（物理数据库设计）。目前，常用的规范设计方法大多起源于新奥尔良法，并在设计的每一阶段采用一些辅助方法来具体实现。
  • 基于E-R模型的数据库设计方法
    基本思想是在需求分析的基础上，用E-R（实体—联系）图构造一个反映现实世界实体之间联系的企业模式，然后再将此企业模式转换成基于某一特定的DBMS的概念模式。
  • 基于3NF的数据库设计方法
    基本思想是在需求分析的基础上，确定数据库模式中的全部属性和属性间的依赖关系，将它们组织在一个单一的关系模式中，然后再分析模式中不符合3NF的约束条件，将其进行投影分解，规范成若干个3NF关系模式的集合
  • 基于视图的数据库设计方法
    此方法先从分析各个应用的数据着手，其基本思想是为每个应用建立自己的视图，然后再把这些视图汇总起来合并成整个数据库的概念模式。视图合并过程中要解决诸如命名冲突、冗余实体/联系等问题
设计过程
  1. **需求分析阶段
    • 是整个设计过程的基础
  2. 概念结构设计阶段
    • 是整个数据库设计的关键
    • 通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型
  3. 逻辑结构设计阶段
    • 将概念结构转换为某个DBMS所支持的数据模型
  4. 数据库物理设计阶段
    • 为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法 )
  5. 数据库实施阶段
  6. **数据库运行和维护阶段
各阶段的任务
  • 需求分析阶段
  • 概念设计阶段
    形成独立于机器特点，独立于各个DBMS产品的概念模式(E-R图)
  • 逻辑设计阶段
    首先将E-R图转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式
    然后根据用户处理的要求、安全性的考虑，在基本表的基础上再建立必要的视图(View)，形成数据的外模式
  • 物理设计阶段
    根据DBMS特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式

2 需求分析

重点
  • 信息要求
    • 用户需要从数据库中获得信息的内容与性质
    • 由用户的信息要求可以导出数据要求，即在数据库中需要存储哪些数据
  • 处理要求
    • 对处理功能的要求
    • 对处理的响应时间的要求
    • 对处理方式的要求(批处理 / 联机处理)
过程
  • 需求获取
    • 与客户交流
    • 形成需求文档
  • 需求分析与表达
    • 自顶向下的结构化分析方法（Structured Analysis，简称SA方法）：从最上层的系统组织机构入手，采用逐层分解的方式分析系统，并用数据流图和数据字典描述系统。
    • 面向对象的分析方法
数据流图（Data Flow Diagram）

是从实际系统抽象出来的、用特定的符号反映系统的数据传递和变换过程的图。它是系统的逻辑模型，与实际系统中具体的处理人员、处理工具和处理方式等无关，只反映数据处理和传送的过程和方向。

  • 组成部分
    Pasted image 20250530095648.png
  • 画法
    • 自顶向下，逐步求精的方法
      • 顶层图：描述系统的范围和边界
      • 底层图：描述一个简单的独立功能
      • 中间图：描述上一层的某个处理，分解成几个独立的功能
    • 由外向里的原则：在绘制顶层图时先考虑整个系统的输入和输出数据流，然后再考虑系统内部的其他元素。
  • 步骤
    1. 首先把任何一个系统都抽象为：
      Pasted image 20250530095832.png
    2. 分解处理功能和数据
    3. 将分析结果再次提交给用户，征得用户的认可

例
某学校排课业务流程调研如下：

  • 教务科根据各专业教学计划统计出各班级下学期所开课程情况，并将该表交各专业教研室主任。
  • 各教研室主任为本室教师安排课程，每位教师写出自己的上课要求，一并交教务科。
  • 教务科安排排课人员排课。
  • 将班级课表交班主任，教师课表交任课教师和督导室。
  • 各班课表和教师课表都要留一份存档。

第一层DFD图
Pasted image 20250530100144.png
第二层DFD图
Pasted image 20250530100311.png

数据字典(Data Dictionary)

  • 是各类数据描述的集合

  • 数据字典中有五类条目：

    • 数据项：是数据的最小组成单位
    • 数据结构
    • 数据流
    • 数据存储
    • 处理过程：输入输出过程

  • 数据字典通过对数据项和数据结构的定义来描述数据流、数据存储的逻辑内容。

作用
数据字典是关于数据库中数据的描述，在需求分析阶段建立，是下一步进行概念设计的基础，并在数据库设计过程中不断修改、充实和完善

常用符号

  • +表示“与”；
  • |表示“或”，即选择括号中的某一项；
  • {}表示“重复”，即括号中的项要重复若干次；
  • []表示“可选”，即括号中的项可有可无

内容

  1. 数据项
    • 数据项是不可再分的数据单位
    • 对数据项的描述取值范围、与其他数据项的逻辑关系定义了数据的完整性约束条件
    • 数据项描述＝｛数据项名，数据项含义说明，别名，数据类型，长度，取值范围，取值含义，与其他数据项的逻辑关系｝
      数据项名称：CourseName，说明：课程的名称，别名：课程名，数据类型：String，数据长度：30
  2. 数据结构
    • 数据结构反映了数据之间的组合关系。
    • 一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成。
    • 数据结构描述＝｛数据结构名，含义说明，组成:｛数据项或数据结构｝｝
      数据结构名称：教师课表
      说明：记录教师上课时间
      组成：{教师名称＋课程名称＋周次＋星期几＋时间}
  3. 数据流
    • 数据流是数据结构在系统内传输的路径。
    • 数据流描述＝｛数据流名，说明，数据流来源，数据流去向，组成:｛数据结构｝，平均流量，高峰期流量｝
      • 数据流来源是说明该数据流来自哪个过程
      • 数据流去向是说明该数据流将到哪个过程去
      • 平均流量是指在单位时间（每天、每周、每月等）里的传输次数
      • 高峰期流量则是指在高峰时期的数据流量
        数据流名称：教师课表
        说明：下学期教师上课时间表
        数据流来源：排课
        数据流去向：任课教师、督导室、 D1 教师课表
        数据流组成：{教师姓名+课程名+星期 +节次}
        数据流的流通量：300份/学期
  4. 数据存储
    • 数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一。
    • 数据存储描述＝｛数据存储名，说明，编号，流入的数据流 ，流出的数据流 ，组成:｛数据结构｝，数据量，存取方式｝
      • 流入的数据流：指出数据来源
      • 流出的数据流：指出数据去向
      • 数据量：每次存取多少数据，每天（或每小时、每周等）存取几次等信息
      • 存取方法：批处理 / 联机处理；检索 / 更新；顺序检索 / 随机检索
需求分析结果
  • 需求分析最终结果是一份需求规格说明书

3 概念结构设计

什么是概念结构设计
  • 将需求分析得到的用户需求抽象为信息结构即概念模型的过程就是概念结构设计
    Pasted image 20250530101353.png
设计策略
  • 自底向上。常用。即首先定义各局部应用的概念结构，然后将它们集成起来，得到全局概念结构
  • 自顶向下。即首先定义全局概念结构的框架，然后逐步细化
  • 逐步扩张。首先定义最重要的核心概念结构，然后向外扩充，以滚雪球的方式逐步生成其他概念结构，直至总体概念结构
  • 混合策略。即将自顶向下和自底向上相结合，用自顶向下策略设计一个全局概念结构的框架，以它的骨架集成由自底向上策略中设计的各区部概念结构
方法和步骤
  • 自底向上概念结构设计
    第1步：抽象数据并设计局部视图
    第2步：集成局部视图，得到全局概念结构
内容
  • 数据抽象
    常用抽象方法：分类，聚集，概括
  • 相关描述参见ER模型章节

4 逻辑结构设计

任务
  • 为了能够用某一DBMS实现用户需求，还必须将概念结构进一步转化为相应的数据模型，这正是数据库逻辑结构设计所要完成的任务。
  • 逻辑结构设计的步骤
    • 将概念结构转化为一般的关系、网状、层次模型
    • 将转化来的关系、网状、层次模型向特定DBMS支持下的数据模型转换
    • 对数据模型进行优化
内容
  1. E-R图向关系模型的转换
    E-R图由实体、实体的属性和实体之间的联系三个要素组成；关系模型的逻辑结构是一组关系模式的集合。将E-R图转换为关系模型即为：将实体、实体的属性和实体之间的联系转化为关系模式。
    原则
    1. 一个实体型转换为一个关系模式
    2. 一个m:n联系转换为一个关系模式
    3. 一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并
    4. 一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并
    5. 三个或三个以上实体间的一个多元联系转换为一个关系模式
    6. 同一实体集的实体间的联系，即自联系，也可按上述1:1、1:n和m:n三种情况分别处理
    7. 具有相同键的关系模式可合并
  2. 数据模型的优化
    • 数据库逻辑设计的结果不是唯一的。
    • 得到初步数据模型后，还应该适当地修改、调整数据模型的结构，以进一步提高数据库应用系统的性能，这就是数据模型的优化。
    • 关系数据模型的优化通常以规范化理论为指导
    • 方法
      1. 确定数据依赖：按需求分析阶段所得到的语义，分别写出每个关系模式内部各属性之间的数据依赖以及不同关系模式属性之间数据依赖。
      2. 对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系
      3. 按照数据依赖的理论对关系模式逐一进行分析，考查是否存在部分函数依赖、传递函数依赖等，确定各关系模式分别属于第几范式
      4. 按照需求分析阶段得到的各种应用对数据处理的要求，分析对于这样的应用环境这些模式是否合适，确定是否要对它们进行合并或分解。
  3. 设计用户子模式（定义视图）
    • 定义数据库模式主要是从系统的时间效率、空间效率、易维护等角度出发。
    • 定义用户外模式时应该更注重考虑用户的习惯与方便。包括三个方面：
      • 使用更符合用户习惯的别名
      • 针对不同级别的用户定义不同的外模式，以满足系统对安全性的要求
      • 简化用户对系统的使用
      • 具体含义和实现方法参见视图一节

13 NoSQL和云数据库

Pasted image 20250613230233.png

1 分布式数据库

2 NoSQL简介

NoSQL兴起的原因
  1. 关系数据库已经无法满足Web2.0的需求。主要表现在以下几个方面：
    1. 无法满足海量数据的管理需求
    2. 无法满足数据高并发的需求
    3. 无法满足高可扩展性和高可用性的需求
  2. “One size fits all”模式很难适用于截然不同的业务场景
    • 关系模型作为统一的数据模型既被用于数据分析，也被用于在线业务
    • 数据分析强调高吞吐，在线业务强调低延时
    • 用同一套模型来抽象不合适
  3. 关系数据库的关键特性包括完善的事务机制和高效的查询机制，不适合Web2.0时代
    • Web2.0网站系统通常不要求严格的数据库事务
    • Web2.0并不要求严格的读写实时性
    • Web2.0通常不包含大量复杂的SQL查询
      网站设计时通常采用单表主键查询方式，已尽量减少多表连接、选择、投影操作
NoSQL与关系数据库的比较
  1. 关系数据库
    • 优势：以完善的关系代数理论作为基础，有严格的标准，支持事务ACID四性，借助索引机制可以实现高效的查询，技术成熟，有专业公司的技术支持
    • 劣势：可扩展性较差，无法较好支持海量数据存储，数据模型过于死板、无法较好支持Web2.0应用，事务机制影响了系统的整体性能等
    • 应用场景：电信、银行等领域的关键业务系统，需要保证强事务一致性
  2. NoSQL数据库
    • 优势：可以支持超大规模数据存储，灵活的数据模型可以很好地支持Web2.0应用，具有强大的横向扩展能力等
    • 劣势：缺乏数学理论基础，复杂查询性能不高，大都不能实现事务强一致性，很难实现数据完整性，技术尚不成熟，缺乏专业团队的技术支持，维护较困难等
    • 应用场景：互联网企业、传统企业的非关键业务（比如数据分析）

3 NoSQL的技术特点

NoSQL的三大基石：CAP、BASE、最终一致性

CAP理论
  • C（Consistency）：一致性，是指任何一个读操作总是能够读到之前完成的写操作的结果，也就是在分布式环境中，多点的数据是一致的，或者说，所有节点在同一时间具有相同的数据
  • A（Availability）：可用性，是指快速获取数据，可以在确定的时间内返回操作结果，保证每个请求不管成功或者失败都有响应；
  • P（Tolerance of Network Partition）：分区容忍性，是指当出现网络分区的情况时（即系统中的一部分节点无法和其他节点进行通信），分离的系统也能够正常运行，也就是说，系统中任意信息的丢失或失败不会影响系统的继续运作。

一个分布式系统不可能同时满足一致性(C)、可用性(A)和分区容忍性(P)这三个需求，最多只能同时满足其中两个。当处理CAP的问题时，可以有几个明显的选择：

  1. CA：也就是强调一致性（C）和可用性（A），放弃分区容忍性（P），最简单的做法是把所有与事务相关的内容都放到同一台机器上。很显然，这种做法会严重影响系统的可扩展性。传统的关系数据库（MySQL、SQL Server和PostgreSQL），都采用了这种设计原则，因此，扩展性都比较差
  2. CP：也就是强调一致性（C）和分区容忍性（P），放弃可用性（A），当出现网络分区的情况时，受影响的服务需要等待数据一致，因此在等待期间就无法对外提供服务
  3. AP：也就是强调可用性（A）和分区容忍性（P），放弃一致性（C），允许系统返回不一致的数据
    Pasted image 20250530111024.png
BASE

Pasted image 20250530111050.png

BASE的基本含义是基本可用（Basically Availble）、软状态（Soft-state）和最终一致性（Eventual consistency）：

  • 基本可用
    指一个分布式系统的一部分发生问题变得不可用时，其他部分仍然可以正常使用，也就是允许分区失败的情形出现
  • 软状态
    • 是与“硬状态（hard-state）”相对应的一种提法
    • “硬状态” 是数据库保存的数据可以保证数据一直是正确的(一致性)
    • “软状态”是指状态可以有一段时间不同步，具有一定的滞后性
  • 最终一致性
    • 一致性的类型包括强一致性和弱一致性
    • 二者的主要区别在于高并发的数据访问操作下，后续操作是否能够获取最新的数据
    • 强一致性：当执行完一次更新操作后，后续其他读操作就可以保证读到更新后的最新数据
    • 弱一致性：如果不能保证后续访问读到的都是更新后的最新数据
    • 最终一致性：是弱一致性的一种特例，允许后续的访问操作可以暂时读不到更新后的数据，但是经过一段时间之后，必须最终读到更新后的数据
NewSQL数据库
  • 各种新的可扩展、高性能数据库的简称
  • 不仅具有NoSQL的海量数据存储管理能力，还保持了传统DB支持ACID和SQL等特性
  • 不同NewSQL内部结构差异很大，但都有两个显著的共同特征
    • 都支持关系数据模型
    • 都使用SQL作为其主要接口

4 云数据库概述

  • 云数据库是部署和虚拟化在云计算环境中的数据库。
  • 是在云计算的大背景下发展起来的一种新兴的共享基础架构的方法
  • 它极大地增强了数据库的存储能力，消除了人员、硬件、软件的重复配置，让软、硬件升级变得更加容易
  • 具有高可扩展性、高可用性、采用多租形式和支持资源有效分发等特点
  • 云数据库特性：
    1. 动态可扩展
    2. 高可用性
    3. 较低的使用代价
    4. 易用性
    5. 高性能
    6. 免维护
    7. 安全
  • 云数据库是个性化数据存储需求的理想选择
  • 从数据模型的角度看，云数据库并非是一种全新的数据库技术，而只是以服务的方式提供数据库功能
  • 云数据库没有专属于自己的数据模型
  • 同一个公司也可能提供采用不同数据模型的多种云数据库服务
Built with MDFriday ❤️