^-^
CV | FE
数据库复习参考
背就完了
  1. 数据库相关概念
    1. 数据库
    2. 数据库系统组成
    3. 数据库应用系统
    4. 数据库管理系统
    5. 数据字典
  2. 实体、关系的概念：
  3. 完整性约束
    1. 关系模型完整性
    2. 实体完整性
    3. 参照完整性
    4. 用户自定义完整性
  4. SQL 语句
    1. SQL 分类
    2. 外键
    3. 复合主键
    4. 多表查询
    5. 索引
  5. 数据库设计
    1. 数据库结构模型设计
    2. E-R 模型
    3. 数据库建模设计
    4. 规范化
      1. 函数依赖
      2. 关系规范化范式
  6. 数据库管理
    1. DBA
    2. DBMS
    3. 事务管理
    4. 并发控制
      1. 并发控制问题
      2. 可串行化调度
      3. 加锁力度
      4. 加锁协议
      5. 两阶段锁定协议：
      6. 隔离等级和锁协议等级
    5. 数据库恢复
    6. 安全管理
  7. 数据库应用编程
    1. 存储过程
    2. 触发器
    3. JDBC
    4. 游标
  8. NOSQL

数据库相关概念

数据库

理解为一种依照特定数据模型组织、存储和管理数据的文件集合。在信息系统中，数据库的基本作用是组织与存储系统数据，并为系统软件从中存取数据提供支持。与文件系统中普通数据文件有明显不同，数据库文件具有如下特点。数据一般不重复存放。可支持多个应用程序并发访问。数据结构独立于使用它的应用程序。对数据增、删、改、查操作均由数据库系统软件进行管理和控制。

数据库系统组成

  • 硬件
  • 软件
  • 数据库
  • 数据库管理系统
  • 相关人员和用户

数据库应用系统

借助数据库进行信息化处理的计算机应用系统被称为数据库应用系统。

从功能角度数据库应用系统可以划分为四个层次来实现：

  1. 表示层：负责所有与用户交互的功能，用户对数据库应用系统的最直观感受均在这层实现。
  2. 业务逻辑层：负责根据业务逻辑需要将表示层获取的数据进行组织后，传递给数据访问层，或将数据访问层获取的数据进行相应的加工处理后，传送给表示层用于展示。
  3. 数据访问层：负责与 DBMS 系统进行交互，提取或存入应用系统所需的数据。
  4. 数据持久层：负责保存和管理应用系统数据。存储过程是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

数据库应用系统的生命周期：

  1. 系统需求分析
  2. 系统设计
  • 系统架构设计
  • 软件功能结构设计
  • 功能模块逻辑设计
  • 系统数据库设计
  • 概念数据模型
  • 逻辑数据模型
  • 物理数据模型设计
  • 系统接口设计
  1. 系统实现
  2. 创建数据库对象
  3. 载入测试数据
  4. 系统测试
  5. 系统运行与维护

数据库管理系统

  • 保障数据库系统正常稳定运行。
  • 充分发挥数据库系统的软硬件处理能力。
  • 确保数据库系统安全和用户数据隐私性。
  • 有效管理数据库系统用户及其角色权限。
  • 解决数据库系统性能优化、系统故障与数据损坏等问题。
  • 最大限度地发挥数据库对其所属机构的作用。

关系数据库的突出优势是，数据表示的 3 级模式（外模式、模式、内模式） 保证了数据的逻辑独立性（外模式和模式之间，需要修改外模式/映像）和物理独立性（模式和内模式间，需要修改模式/内模式映像），完整的事务处理机制保持数据的一致性（事务的 ACID，即原子性、一致性、隔离性、持久性）；

数据字典

对数据的数据项、数据结构、数据流、数据存储、处理逻辑等进行定义和描述。

实体、关系的概念：

  1. 实体（entity）—— 是指包含有数据特征的事物对象在概念模型世界中的抽象名称。
  2. 关系（relation）—— 是指具有关系特征、用于存放实体数据的二维表。关系也常被称为关系表。关系是指在集合域 D1、…、Dn 上运算笛卡儿积的有意义子集，其数学描述为 R ⊆ D1 × … × Dn。
  • 表中每行存储实体的一个实例数据
  • 表中每列包含实体的一项属性数据
  • 表中单元格只能存储单个值
  • 不允许有重复的行
  • 不允许有重复的列
  • 列顺序可任意
  • 行顺序可任意
  • 元组是行
  • 投影选列
  • 自然连接要去重复列

关系特性

  • 在关系表中，单元格必须是原子值，即仅存储单个值。
  • 在关系表中，每个属性列定义同一数据类型或取值同一域。
  • 在关系表中，任意两个元组不能完全相同。
  • 在关系表中，不同属性列定义不同列名。
  • 在关系表中，行的顺序可以任意交换。
  • 在关系表中，列的顺序可以任意交换。
  1. 象集

  1. 除

完整性约束

关系模型完整性

实体完整性

  • 每个关系表中的主键属性列都不允许为空值（NULL），否则就不可能标识实体。
  • 现实世界中的实体是靠主键来标识，主键取值应该唯一，并区分关系表中的每个元组。

参照完整性

  • 若关系 R 中的外键 F 与关系 S 中的主键 K 相关联，则 R 中外键 F 值必须与 S 中主键 K 值一致。

用户自定义完整性

SQL 语句

SQL 分类

  1. 数据定义语言（Data Definition Language,DDL）类型语句用于创建与维护数据库对象，如数据库、数据库表、视 图、索引、触发器、存储过程等。该类语句包括创建对象、修改对象和删除对象等语句。例如，在数据库中创建新表 或删除表（CREATE TABLE 或 DROP TABLE）、创建或删除索引（CREATE INDEX 或 DROP INDEX）。
  2. 数据操纵语言（Data Manipulation Language,DML）类型语句用于对数据库中的数据表或视图进行数据插入、数据删除、数据更新等处理。例如，使用 INSERT、UPDATE 和 DELETE 语句，分别在数据表中添加、更新或删除数据行。
  3. 数据查询语言（Data Query Language,DQL）类型语句用于从数据库表中查询或统计数据，但不改变数据库中的数据。例如，使用 SELECT 语句可从数据库表中查询数据。
  4. 数据控制语言（Data Control Language,DCL）类型语句用于 DBA 用户管理数据库对象的访问权限。例如，使用 GRANT 语句授权用户或角色对指定数据库对象的访问权限。
  5. 事务处理语言（Transaction Process Language,TPL）类型语句用于数据库事务的编程处理。例如，使用 BEGIN TRANSACTION、COMMIT 和 ROLLBACK 语句进行事务开始、事务提交、事务回退等处理。
  6. 游标控制语言（Cursor Control Language,CCL）类型语句用于数据库游标结构的使用。例如，DECLARE CURSOR、 FETCH INTO 和 CLOSE CURSOR 用于数据库游标对象声明、提取游标所指向的缓冲区数据、关闭游标对象等。

外键

CONSTRAINT CoursePlanID_FK FOREIGN Key(CoursePlanID) 

REFERENCES Plan(CoursePlanID)

复合主键

CONSTRAINT CoursePlan_PK PRIMARY Key(CourseID,TeacherID)

多表查询

SELECT C.CourseName AS 课程名称，T.TeacherName AS 教师，COUNT(R.CoursePlanID) AS 选课人数

FROM Course AS C JOIN Plan AS P 

ON C.CourseID=P.CourseID 

JOIN Teacher AS T ON P.TeacherID=T.TeacherID 

LEFT JOIN Register AS R ON P.CoursePlanID=R.CoursePlanID GROUP BY C.CourseName, T.TeacherName; 



SELECT College.CollegeName AS 学院名称， COUNT(Teacher.CollegeID) AS 教师人数 

FROM Teacher, College WHERE Teacher.CollegeID=College.CollegeID

GROUP BY College.CollegeName ORDER BY College.CollegeName DESC;

索引

CREATE INDEX BirthDay_Idx ON Student(BirthDay);

数据库设计

  • 需求分析
  • 概念
  • 逻辑
  • 物理
  • 实施
  • 运行和维护

数据库结构模型设计

E-R 模型

转换为关系模型

  1. 实体到关系表的转换
  2. 实体联系的转换
  • 1:1 实体联系的转换
  • 1:N 实体联系的转换
  • M:N 实体联系的转换
  • 实体继承联系的转换
  • 实体递归联系的转换

数据库建模设计

  1. CDM：

这个箭头是标识符依赖的意思

  1. LDM：

  1. 逻辑数据模型比概念数据模型对信息系统数据结构的描述更具体，不但有业务实体，也有新增的、便于信息化处理的数据实体。
  2. 逻辑数据模型将概念数据模型的多对多实体联系转化为易于关系数据库实现的一对多实体联系。
  3. 逻辑数据模型将概念数据模型中的标识符依赖实体进一步细化，并区分主键标识符和外键标识符，以便于数据模型规范化处理。
  4. PDM

规范化

函数依赖

X → Y：Y 依赖于 X，若 Y 不依赖于 X 的任何真子集，则称为 Y 完全依赖于 X。例如{X,Y}是主键，故{X,Y}→N，则该关系中属性间约束为完全函数依赖，反之为部分函数依赖。

X→Y, Y 不 →X,Y→Z 称为传递依赖。第二个条件为了保证 X 和 Y 不一一对应。

多值依赖： 有课程、教师和参考书，其属性间约束的语义：一门课程可以有多个任课教师，也可以有多本参考书；每个任课教师可以任意选择他的参考书。例如，存在(课程 A，教师 1，参考书 1)、(课程 A，教师 2，参考书 2)、(课程 A，教师 1，参考书 2)和(课程 A，教师 2，参考书 1)等元组，即该关系存在多值 依赖 Course→→Teacher,Course→→Book。简单来说，对任意确定的课程都有一组教师的取值与之对应，同样每个课程 都有一组参考书与之对应，而教师的取值与参考书的取值是相互独立的。

关系规范化范式

一个只满足 1NF 的关系可能存在的四方面问题是:数据冗余度大、插入异常、修改异常和删除异常

  1. 第一范式： 关系表的属性列不能重复，并且每个属性列都是不可分割的基本数据项；
  2. 第二范式： 关系表中所有数据都要和该关系表的主键有完全函数依赖；
  3. 第三范式： 属性均不存在传递函数依赖。 学号 → 系名，系名 → 住址，故学号 → 住址；
  4. 巴斯-科德范式： 所有函数依赖的决定因子必须是候选键；
  5. 第四范式：不存在多值依赖情况（在学生关系中，一个学生的 STU_ID 是 21，它有两门课程，计算机和数学以及两个爱好，舞蹈和歌唱。 因此，对 STU_ID 存在多值依赖性，这导致不必要的数据重复)
  6. 第五范式：

数据库管理

DBA

  • 负责数据库系统开发与运维
  • 负责数据库用户与权限管理
  • 负责数据库备份与数据库恢复管理
  • 负责数据库性能调优管理

DBMS

  • 数据库定义
  • 数据库运行管理
  • 数据组织与存储
  • 数据库维护
  • 数据库通信

事务管理

在数据库中，事务（Transaction）是指由构成单个业务处理单元的一组数据库访问操作，要求它们要么都成功执行，要么都不执行。同时，事务也是 DBMS 最小的故障恢复任务单元和并发控制任务单元。其生命周期状态变迁如图所示。

事务 ACID 特性：

  • 原子性（Atomicity）：事务所有操作在数据库中要么全部执行，要么全部不执行。
  • 一致性（Consistency）：事务多次执行，其结果应一致。
  • 隔离性（Isolation）：事务与事务之间隔离，并发执行透明。
  • 持续性（Durability）：事务完成后，数据改变必须是永久的

事务并发执行是指多个事务程序在数据库系统中同一时段运行。

事务并发执行原因：

  • 改善系统的资源利用率
  • 减少事务运行的平均等待时间

BEGIN 或 START TRANSACTION ；事务开始语句

ROLLBACK 事务回滚语句

COMMIT 事务提交语句

SAVEPOINT 事务保存点语句，回滚到这里

并发控制

并发控制问题

  • 脏读（Dirty Read）指当多个事务并发运行，并操作访问共享数据，其中一个事务读取了被另一个事务所修改后的 共享数据，但修改数据的事务因某种原因失败，数据未被提交到数据库文件，而读取共享数据的事务则读取得到一个垃圾数据，即脏数据。
  • 不可重复读（Unrepeatable Read）指一个事务对同一共享数据先后重复读取两次，但是发现原有数据改变或丢失。 出现这种问题的原因：多个事务并发运行时，一些事务对共享数据进行多次读操作，但其中一个事务对共享数据进行了修改或删除操作。
  • 幻像读（Phantom Read）指一个事务对同一共享数据重复读取两次，但是发现第 2 次读取比第 1 次读取的结果中新增了一些数据
  • 丢失更新（Lost Update）指一个事务对一共享数据进行更新处理，但是以后查询该共享数据值时，发现该数据与自己的更新值不一致。出现这种问题的原因：多个事务并发执行，其中一个事务对共享数据进行了更新，并改变了前面事务的更新值。图 5-11 给出事务并发执行可能出现丢失更新的一个示例。

之所以将插入单独列出来称为幻象读取

是因为新数据的插入除了对聚合函数结果有影响，并没有影响原有数据。

因此对多次读取发现数据有新增的容忍度会高一些。

可串行化调度

在事务并发执行中，只有当事务中数据操作调度顺序的执行结果与事务串行执行结果一样时，该并发事务调度才能保证数据操作的正确性和一致性。符合这样效果的调度称为可串行化调度。

排它锁定(Lock-X)——锁定后，不允许其它事务对共享数据再加锁

共享锁定(Lock-S)——锁定后，只允许其它事务对共享数据添加读取锁

加锁力度

  • 数据库——粒度最大
  • 表——粒度较大
  • 页面——粒度中等
  • 行——粒度小

加锁协议

  • 一级加锁协议：任何事务在修改共享数据对象之前，必须对该数据执行排它锁定指令，直到该事务处理完成，才进行解锁指令执行。可避免出现更新丢失问题。但不能解决“不可重复读取”、“脏读”等数据不一致问题。
  • 二级加锁协议：在一级加锁协议基础上，针对并发事务的共享数据读操作，必须对该数据执行共享锁定指令，读完数据后即刻释放共享锁定，不但可以防止“丢失更新”的数据不一致性问题，还可防止出现脏读数据问题。但有可能会出现“不可重复读取”的数据不一致问题。
  • 在一级加锁协议基础上，针对并发事务对共享数据进行读操作，必须对该数据执行共享锁定指令，直到该事务处理结束才释放共享锁定。

两阶段锁定协议：

增长阶段，事务只能获得锁，但不能释放锁。

缩减阶段，事务只能释放锁，但不能获得新锁

若并发事务执行的所有事务都遵从两阶段锁定协议，则这些事务的任何并发调度都是可串行化调度，即这些并发调度执行结果可以保证数据库一致性。

隔离等级和锁协议等级

  • 读取未提交对应无锁
  • 读取已提交不取决于锁协议，取决于在事务提交前是否写生效
  • 可重读读取需要三级加锁协议
  • 可串行化需要满足两阶段锁定-必要条件

数据库恢复

如果用户希望使用故障前的数据库备份文件进行恢复处理，可采用前滚事务恢复方式将数据库恢复到故障发生前一时刻的数据库状态。这是在数据库备份版本基础上，通过系统执行事务日志文件中记录的操作命令来实现数据库恢复处理，即将系统记录的后像数据重新应用到数据库中，从而将数据库恢复到故障发生前一时刻的状态。 后像数据是数据库备份时刻到故障时刻期间所记录的事务修改数据。

当用户希望使用故障后的数据库进行恢复处理时，可采用回滚事务恢复方式将数据库恢复到故障发生前一时刻的数据库状态。这是在故障后的数据库基础上，通过系统回滚事务操作来实现的。在恢复处理时，取消错误执行或部分完成的事务对数据库的修改，将系统记录的前像数据恢复到数据库中，从而将数据库恢复到故障发生前一时刻的状态。 前像数据是数据库故障时刻之前所记录的事务修改数据。

安全管理

CREATE USER "userA" WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD '123456'; 



GRANT SELECT ON Department TO "userA"; 

CREATE ROLE "Role_Manager" WITH 

    LOGIN

    NOSUPERUSER

    NOCREATEDB

    NOCREATEROLE

    INHERIT

    NOREPLICATION 

    CONNECTION LIMIT -1; 



GRANT SELECT,INSERT,UPDATE,DELETE ON Department TO "Role_Manager" 

 

CREATE USER "StudentUser" WITH 

    LOGIN 

    NOSUPERUSER 

    NOCREATEDB 

    NOCREATEROLE 

    INHERIT 

    NOREPLICATION 

    CONNECTION LIMIT -1

    IN ROLE "R_Student" 

    PASSWORD '123456';

数据库应用编程

存储过程

CREATE OR REPLACE FUNCTION countRecords () 

RETURNS integer AS $count$ 

DECLARE 

    count integer; 

BEGIN 

    SELECT count(*) INTO count FROM Student; 

    RETURN count; 

END; 

$ count $ LANGUAGE plpgsql;

触发器

  • NEW ，类型为 RECORD，代表了行级触发器 INSERT、UPDATE 操作之后的新数据行。对于 DELETE 操作或者语句级触发器而言，该变量为 null；
  • OLD，类型为 RECORD，代表了行级触发器 UPDATE、DELETE 操作之前的旧数据行。对于 INSERT 操作或者语句级触发器而言，该变量为 null；
CREATE OR REPLACE FUNCTION score_audit() 

    RETURNS TRIGGER AS $score_audit$ 

    BEGIN 

        IF (TG_OP = 'DELETE') THEN 

            INSERT INTO Audit_score SELECT user, old.sid, old.cid, now(), OLD.score; 

            RETURN OLD; 

        ELSIF (TG_OP = 'UPDATE'') THEN 

            INSERT INTO Audit_score SELECT user, old.sid, old.cid, now(), OLD.score , new.score where old.sid=new.sid and old.cid=new.cid; 

            RETURN NEW;

        ELSIF (TG_OP = 'INSERT'') THEN 

            INSERT INTO Audit_score SELECT user, new.sid, new.cid, now(),null, new.score; 

            RETURN NEW; 

        END IF; 

        RETURN NULL; 

    END; 

$score_audit$ LANGUAGE plpgsql; 



CREATE TRIGGER score_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON Stu_score FOR EACH ROW EXECUTE PROCEDURE score_audit();

JDBC

import java.sql.Connection;

import java.sql.DriverManager;

 

public class Database {

    public String url = "jdbc:postgresql://localhost:5432/GradeDB";

    public String username = "postgres";

    public String password = "1";

    public Connection conn;

 

    Database() {

     try {

         Class.forName("org.postgresql.Driver");

         this.conn = DriverManager.getConnection(this.url, this.username, this.password);

     } 

     catch (Exception e) {

         System.out.println("无法连接到数据库");

         e.printStackTrace();

     }

   }

}

 

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RestController;



import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;



@RestController

public class GradeController {

    public Database database = new Database();



    @GetMapping("/students")

    public List<Grade> getStudentGrade() {

        String SQL = "select grade.sid, student.sname, grade.cid, course.cname, score\n" + "from grade\n" + "inner join student on student.sid = grade.sid\n" + "inner join course on course.cid = grade.cid;";

        ArrayList<Grade> result = new ArrayList<>();

    

        try {

            Statement smt = database.conn.createStatement();

            ResultSet rs = smt.executeQuery(SQL);

            while (rs.next()) {

                String sid = rs.getString("sid");

                String sname = rs.getString("sname");

                String cid = rs.getString("cid");

                String cname = rs.getString("cname");

                int score = rs.getInt("score");

                result.add(new Grade(sid, cid, sname, cname, score));

            }

            smt.close();

            rs.close();

        } 

        catch (Exception e) {

            e.printStackTrace();

        }

}

游标

curStudent CURSOR FOR SELECT * FROM student;

curStudentOne CURSOR (key integer) IS SELECT * FROM student WHERE SID = key;

#为了兼容Oracle，FOR可以改为IS

OPEN curVars1 FOR SELECT * FROM student WHERE SID = mykey

OPEN curVars1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

OPEN curStudent;

OPEN curStudentOne ('20160230302001'); 

FETCH curVars1 INTO rowvar;  --rowvar为行变量

FETCH curStudent INTO SID, Sname, sex;

CLOSE curStudent;

NOSQL

  • Redis 是 KV 类型的内存数据库。
  • MongoDB 是基于分布式文件存储的开源数据库系统
  • HBase 列式数据库
  • Neo4j 是开源的用 Java 实现图数据库
© 2022—∞ SynX Studio | DIG Lab
本站曾由Jekyell驱动｜使用Jekyll-theme-YAT主题
本站现由Hexo驱动｜使用Hexo-theme-A4主题