数据库学习笔记

雨时的前端小站
69 阅读14分钟

数据库

SQL

1 概述

  • 核心功能9个动词
    • 数据查询 SELECT
    • 数据定义 CREATE DROP ALTER
    • 数据操纵 INSERT UPDATE DELETE
    • 数据控制 GRANT REVOKE

2 数据定义

  • SQL对关系数据库模式的支持：外模式对应于视图（View）和部分基本表（BASE TABLE），模式对应于基本表，内模式对应于存储文件

  • 模式、外模式、内模式的基本对象有表、视图、索引，因此SQL的数据定义包括定义表、定义视图、定义索引

  • 定义、删除、修改基本表

    CTEATE TABLE Stduent
	(Sno CHAR(5) NOT NULL UNIQUE,
	 Sname CHAR(20) UNIQUE,
	 Sage INT,
	)

// 修改表
ALTER TABLE <表名>
	[ADD] 新列名 数据类型 完整性约束
	[Drop] 完整性约束
	[MODIFY] 列名 数据类型

ALTER TABLE Student Drop UNIQUE(Sname)

// 删除表
DROP TABLE <表名>

  • 建立、删除索引

    • 建立索引是为了减小查询时间
    CREATE [CLUSTER|UNIQUE] INDEX 索引名 on 表名(列名[<次序>]，列名[<次序>]，)
// 次序可选ASC/DESC，默认ASC

// 在Student表上Sname列建立一个聚簇索引(索引项的顺序与表中的物理顺序一致的物理组织)，记录将会按照Sname升序排放
CREATE CLUSTER INDEX Stusname on Student(Sname);

// 唯一索引 按学号升序建立唯一索引
CREATE UNIQUE INDEX Stusno ON Student(Sno);
    • 聚簇索引
      • 在一个基本表上最多建立一个聚簇索引
      • 会导致记录的物理顺序变更，代价较大，经常更新的列不宜建立聚簇索引
    • 如果数据增删改频繁，则系统会花费时间维护索引，可删除一些不必要的索引DROP INDEX <索引名>

3 查询

SELECT [ALL|DISTINCT] <目标列表达式>[, <目标列表达式>]...
FROM <表名or视图名>[, <表名or视图名>]...
[WHERE <条件表达式>]
[GROUP BY <列名1>[ HAVING<条件表达式>]] // 按列名1的值进行分组 属性列值相等的元组为一个组， having只有满足指定条件的组才输出
[ORDER BY <列名2>[ ASC|DESC]] // 按值的升序/降序

  • 使用集函数

    • COUNT SUM AVG MAX MIN

  • 单表查询

  • 连接查询（2个以上表）

    • 等值与非等值连接查询
    • 自身连接
      • 表与自己进行连接
    • 外连接

  • 嵌套查询

    SELECT Student *, SC *
FROM Student, SC
WHERE Student.Sno=SC.Sno; // 通过公共属性Sno实现

  • 集合查询（UNION INTERSECT MINUS）

4 数据更新

  • INSERT INTO xxx VALUES()
  • UPDATE xx SET xxx
  • DELETE FROM xxx [WHERE ]

5 视图

虚表，基本表数据变化，则视图中查询的数据也发生改变

  • CREATE VIEW <视图名>[(<列名>, <列名>)] AS <子查询> [WITH CHECK OPTION]
    • 子查询不能有order by 和distinct
  • 视图的作用，简化用户操作，多种角度看数据，对重构数据库提供逻辑独立性，对机密数据的安全保护

6 数据控制

  • 授权 GRANT
  • 回收权限 REVOKE

数据库恢复技术

1 事务的基本概念

  • 事务（Transaction）： 是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位，可以是一句sql语句、一组sql语句、或整个程序

    • 事务和程序是两个概念，一般一个程序中包含多个事务
    • 定义事务的语句
      • BEGIN TRANSATION（开始）
      • COMMIT（提交，提交事务的所有操作，将事务中所有对数据库的更新写回到磁盘上的物理数据库中，事务正常结束）
      • ROLLBACK（回滚，事务运行中发生故障，系统将事务对数据库的所有已完成操作全部撤销，回滚到事务开始时的状态）
    • 事务特性(ACID特性)
      • 原子性（Atomicity）、一致性(consistency)、隔离性(isolation)、持续性(durability)
      • 可能破坏ACID：

        • 多个事务并行运行，不同事务操作交叉执行

        • 事务在运行过程中被强行停止

    2 数据库恢复概述

    • 故障种类：
      • 事务内部故障
        • 事务撤销UNDO：强行ROLLBACK该事务，撤销该事务已作出的任何对数据库的修改，使得该事务好像没有启动一样
      • 系统故障
        • CPU故障，操作系统故障，DBMS代码错误，停电；
        • 此类故障影响正在运行的所有事务，但不破坏数据库；主存内容，尤其是数据库缓冲区（内存中）的内容被丢失，所有运行事务非正常终止。
        • UNDO + REDO（重做所有已提交的事务）
      • 介质故障
        • 外存故障，磁盘损坏等，可能性小，破坏性大
      • 计算机病毒
    • 故障类型总结
      • 数据库本身被破坏
      • 数据库没有破坏但数据不正确，因为事务的运行被非正常终止
    • 恢复原理：冗余

      • 被破坏或者不正确的数据，可以根据存储在系统别处的冗余数据来重建

    3 恢复的实现技术

    • 数据转储
      • 动态/静态
      • 海量/增量
    • 登记日志文件
      • 以记录为单位/以数据块为单位
      • 登记次序，严格按并发事务执行的时间次序
      • “先写日志文件”原则：先写日志文件，后写数据库
    • 恢复策略
      • 事务故障恢复：
        • 利用日志文件撤销「UNDO」此事务已对数据库进行的修改，由系统自动完成（反向扫描文件日志，对事务更新操作进行逆操作）
      • 系统故障的恢复
        • 两种情况，一是未完成事务第数据库的更新已写入数据库，二是已提交事务对数据库的更新可能还停在缓冲区没来得及写入；
        • 因此恢复操作要撤销故障发生时未完成的事务，重做已完成的事务
        • 系统重启时自动完成，不需用户干预（正向扫描日志文件，找出故障发生前已提交事务，记入REDO队列，同时找出故障发生时未完成事务，记入UNDO队列，进行undo和redo）
      • 介质故障恢复
        • 重装数据库，重做已完成事务
    • 具有检查点的恢复技术
      • 在日志文件中增加一类新的记录——检查点（checkpoint）记录，增加一个重新开始文件，并让恢复子系统在登陆日志文件期间动态维护日志
    • 数据库镜像
      • 每当主数据库更新时，DMMS自动把更新后的数据复制过去，保证镜像数据和主数据的一致性；介质故障时，可由镜像磁盘提供使用；
      • 还可用于并发操作，一个用户加了排它锁，其他用户可以读取镜像数据库的数据，不必等待用户释放锁
      • 只对关键数据和文件镜像

    4 并发控制

    • 并发操作带来的数据不一致性包括三类：
      • 丢失修改，不可重复读，读“脏数据”
      • 破坏事务的隔离性，并发控制就是要用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰，从而避免造成数据的不一致性
      • 并发控制的主要技术为封锁（Locking）
    • 封锁（Locking）
      • 基本类型：
        • 排它锁（Exclusive Locks，X锁），写锁，事务T对数据对象A加X锁，则只允许T读取和修改A，其他事务不能对A加锁，直到T释放A上的锁。
        • 共享锁（Share Locks，S锁），读锁，事务T对数据对象A加S锁，则事务T可以读A但不能修改A，其他事务职能对A加S锁，而不能加X锁，直到T释放A上的S锁。保证其他事务可以读A，但在T释放A的S锁之前，不能对A做任何修改
    • 封锁协议
      • 需要约定规则，如合适申请X锁或S锁、持锁时间、何时释放等
      • 一级封锁协议：
        • 事务T在修改数据R之前必须先对其加X锁，直到事务结束（包括COMMIT、ROLLBACK）才释放
        • 可防止丢失修改，保证事务T是可恢复的
      • 二级封锁协议：
        • 一级封锁协议加上事务T在读取数据R之前必须对其加S锁，读完后即可释放S锁
        • 可解决读“脏数据”的问题
      • 三级封锁协议：
        • 一级封锁协议加上事务T在读取数据R之前必须对其加S锁，直到事务结束才释放
    • 活锁和死锁
      • 封锁的方法可能引起活锁和死锁
      • 活锁：
        • 比如事务T1封锁数据R，事务T2又请求封锁R，T2等待；T3也请求封锁R，T1释放后，系统首先批准了T3的请求，T3释放后，又批准T4的请求……T2有可能永远等待
        • 避免活锁：采用先来先服务的策略
      • 死锁：
        • 事务T1封锁数据R1，T2封锁了R2，然后T1又请求封锁R2，T1等待T2释放R2的锁；接着T2又申请封锁R1，T2也只能等待T1释放R1上的锁。这样出现了T1在等待T2，而T2又在等待T1的局面，T1和T2两个事务永远不能结束，出现死锁。
        • 解决方法两种：预防死锁发生 or 允许发生死锁，采用一定手段定期诊断系统中有无死锁，有则解除之。
        • 预防：
          • 一次封锁法：
            • 要求每个事务必须一次将所有要使用的数据全部加锁，否则不能继续执行。比如T1将R1和R2一次加锁，则T1可继续执行，T2等待，T1释放后T2执行
            • 但扩大了封锁范围，降低了并发度
          • 顺序封锁法：
            • 预先对数据对象规定一个封锁顺序，所有事务按顺序实行封锁
            • 难以确定封锁顺序
          • 预防死锁的策略ing不适合数据库特点，因此DBMS在死锁问题上普遍采用诊断&解除死锁的方法
        • 死锁的诊断与解除：
          • 超时法：
            • 如果一个事务的等待时间超过了规定的时限，就认为发生了死锁。
            • 可能误判死锁，或者时限设置太长导致死锁发生后不能及时发现
          • 等待图法：
            • 有向图G=（T，U），T为节点集合，表示正运行的事务；U为边的集合，表示事务等待情况；若T1等待T2，则T1，T2之间划一条有向边，从T1指向T2。事务等待图反映了所有事务的等待情况。并发控制子系统周期性的（比如隔1min）检测事务等待图，如果存在回路，则有死锁。
            • 一旦检测到死锁，选择一个处理死锁代价最小的事务，将其撤销。对撤销的事务所执行的数据修改操作必须加以恢复；
    • 并发调度的可串行性
      • 定义：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行的执行它们时结果相同，我们称这种调度策略为可串行化（Serializable）的调度
      • 可串行性（Serializability）是并发事务正确性的准则，按这个规定，一个给定的并发调度，当且仅当它是可串行化的，才认为是正确调度。
      • DBMS的并发控制机制必须提供一定手段保证调度是可串行化的。普遍采用封锁方法实现并发操作调度的可串行性，从而保证调度的正确性
      • 两段锁（Two-Phase Locking，2PL）协议就是保证并发调度可串行性的封锁协议。
      • 还有其他方法，如时标方法、乐观方法等来保证调度的正确性。
    • 两段锁协议
      • 定义：所有事务必须分两个阶段对数据项加锁和解锁。
        • 在对任何数据进行读、写操作之前，首先申请并获得对该数据的封锁；
        • 在释放一个封锁之后，事务不再申请和获得其他封锁；
      • “两段”锁含义：
        • 事务分为两个阶段，第一阶段是获得封锁，也称为扩展阶段。在这个阶段，事务可以申请获得任何数据项上任何类型的锁，但不能释放任何锁；
        • 第二阶段是释放封锁，也称收缩阶段。在这阶段，事务可以释放任何数据项上任何类型的锁，但是不能再申请任何锁。
      • 事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件
      • 和一次封锁法的区别，一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则benign继续执行，一次封锁法遵循两段锁协议；但是两段锁协议并不要求事务一次嫁给你所有要使用的数据全部加锁，因此遵守两段锁协议的事务可能发生死锁。
    • 封锁粒度
      • 封锁对象的大小称为封锁粒度（Granularity）
      • 封锁对象可以是逻辑单元（属性值、属性值集合、元组、关系、索引项、整个数据库）
      • 封锁粒度与系统并发度和并发控制开销相关；
        • 封锁粒度越大，数据库能封锁的数据单元越少，并发度越小，系统开销越小
        • 封锁粒度越小，并发度越高，系统开销越大
      • 多粒度封锁
        • 定义：在一个系统中同时支持多种封锁粒度供不同事务选择是比较理想的，称为多粒度封锁；
        • 包含显式封锁（应事务要求加锁）和隐式封锁（上级节点加锁而使该数据对象加锁），两种效果相同
      • 意向锁
        • 含义：如果对一个结点加意向锁，则说明该节点的下层节点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁（如对一个元组加锁，则先对其所在关系加意向锁）
        • 3种常用意向锁：
          • IS锁（意向共享锁，Intent Share Lock）
            • 给一个数据对象加IS锁，他的后裔结点拟（意向）加S锁，对元组加S锁，则对关系&数据库加IS锁
          • IX锁（意向排它锁，Intent Exclusive Lock）
            • 给一个数据对象加IX锁，他的后裔结点拟（意向）加X锁，对元组加X锁，则对关系&数据库加IX锁
          • SIX锁（共享意向排它锁，Share Intent Exclusive Lock）
            • 给一个数据对象加SIX锁，表示对它加S锁，再加IX，对表加SIX锁，则表示该事务要读整个表，对该表加S锁，同时会更新个别元组（对该表加IX锁）

    5 数据库完整性

    • 完整性约束条件作用对象
      • 关系：若干元组间、关系集合以上以及关系之间的联系的约束
      • 元组：元组间各字段间的联系的约束
      • 列： 列的类型、取值范围、精度、排序
    • 涉及的三类对象的状态，可以是
      • 静态：数据库每一确定状态时的数据对象应满足的约束条件，反映数据库状态合理性约束
      • 动态：数据库从一种状态转变为另一种状态时，新旧值之间所应满足的约束条件，反映数据库状态变迁的约束
    • 完整性约束条件可分为六类
      • 动态+列级/元组/关系
      • 静态+列级/元组/关系
    • 完整性规则可以用一个五元组（D,O,A,C,P）来表述
      • DATA：约束作用的数据对象
      • Operation：触发完整性检查的数据库操作，即当用户发出什么吗操作请求时需要检查该完整性规则，是立即检查还是延迟检查；
      • Assertion：数据对象必须满足的断言或语义约束，这是规则的主体
      • Condition：选择A作用的数据对象值的谓词；
      • Procefure：违反完整性规则时触发的过程；
avatar
文章
阅读
粉丝