关系型数据库小结

后端

1. 数据模型种类

   • 层次数据模型：类似二叉树、每个子数据都有一个母数据
   • 网状数据模型：每次子数据可以有多个母数据
   • 关系数据模型：易于理解的二维表

   层次、网状数据模型用指针连接数据表示它们的关系，指针就是表示数据在硬盘上的存储位置的架构。处理数据时必须知道数据的物理位置和构造，检索困难，技术要求高。

2. 关系型数据库的运算

   • 集合运算（相当于数学中的集合运算）
     
     • 并（Union）
     • 差（difference）
     • 交（intersection）
     • 笛卡尔积（cartesian product）
   • 关系运算
     
     • 投影（projection）
     • 选择（selection）
     • 连接（join）
     • 除（division）

3. 设计数据库

   • E-R模型：从现实世界中抽取实体，分析实体之间的关系：实体之间的关系有一对一、一对多、多对多
   • 步骤：
     
     1. 概念模式：E-R模型、表格规范化
     2. 内部模式：即物理地创造数据库阶段
     3. 外部阶段：从用户和应用的角度来观察数据库，方便应用程序从数据库调取必要的数据

4. 表格的范式

   1. 非范式：没有除去数据重复的表格，非普通的二元表格，需要进行分割
   2. 第一范式：分割表格、去除重复项目，一栏只有一个栏目，单纯的二元表格
   3. 第二范式：分割表格，通过主键确定其他列的值（函数依赖）
   4. 第三范式：继续分割表格，去除传递依赖

5. SQL 命令对大小写不敏感，但是推荐写成大写，与其他非命令字符区分；

6. 数据库、表、字段名称都可以采用[]包住，也可以不采用，建议统一采用[]包住；

7. select 查询的是某个字段，或者所有字段（*）；

8. 字符串采用单引号：'；

9. LIKE 用于模糊匹配的等于，% 通配任意字符，_ 通配单个字符；

10. 查询多个列名、多个表、多个数据库时，使用,将各个列、表、数据库名隔开，各个列名最好加[]，跨表多表格查询出现相同列名时，在表名和列名之间加上.连接以区分；

    SELECT Hope_ContentArticle.GeneralID,Hits,Hope_U_Article.ID,Author
    FROM Hope_ContentArticle,Hope_U_Article
    WHERE Hope_ContentArticle.GeneralID = Hope_U_Article.ID

11. 每次查询的结果数据库管理系统（DBMS）都会生成一个临时的表格以展示；

12. 关系型数据库的精髓在于拆分，便捷在于虽然拆分但是还能能够从各个不同的表抽取数据生成查询结果表；

13. select的多个字段内含avg(...)时，必须使用group by ...为结果分组：使用计算函数和GROUP BY组合

    select NodeID,AVG(Hits)
    FROM Hope_ContentArticle
    GROUP BY NodeID
    /*** 
    HAVING ...(条件)
    此条件语句可再次从上述结果中进行筛选
    ***/

14. 子查询检索：在一个查询中嵌套另一个查询，或者说将一个查询的结果嵌套在另一个查询中作为参数；其查询是由内到外的。

    select * from PE_CommonModel
    where generalID in
    (select ID
    from PE_U_Article
    where ID>1000)

15. 通过对列给予限制，防止向表格中输入相互矛盾的数据；

16. 把多个存在关系的表格拼接起来的操作，称之为“连接”

    • 同等连接：以相同意义的列为媒介，设定拥有相同数值的行作为连接条件，连接多个表格的多个列成新表格。
      
      • 自然连接：在同等连接时，将两表中重复的列整合成一列的连接叫做自然连接。一般连接表格时，使用自然连接。
      • 外部连接：在同等连接时，全部保留任何一方表格的所有行，将一方中没有的行设置为空值的连接方法称为外部连接。分为：左外部连接和右外部连接

17. 表格字段的限制种类：

    限制	含义
    PRIMARY KEY	设定为主关键词
    UNIQUE	唯一
    NOT NULL	不许出现空值
    CHECK	检查范围
    DEFAULT	设定默认值
    FOREIGN KEY/REFERENCES	设定外关键词、参考键

18. INSERT

    INSERT INTO onetable(column1,column2,column3)
    VALUE(value1,value2,value3)

19. UPDATE SET

    UPDATE onetable
    SET KEY = 'valueNew'
    WHERE ...

20. DELETE

    DELETE FROM onetable
    WHERE ...

21. 数据的插入、更新、删除等操作必须遵循约束条件

22. 基本表、视图、CREATE VIEW...AS
    基本表即为数据库中真实存在的表，视图是基于基本表制作的仅从用户的视角看到的、虚拟的表格；视图是由基本表内的数据拼凑生成的表格，在只希望对外公开数据的一部分时十分方便，使用视图十分方便。创建视图后，对视图的操作与表格基本相近了。

    CREATE VIEW viewName(column01,column02,...)
    AS ...
    /*** AS 后面接 SELECT 查询语句作为表的内容 ***/

23. 在程序语言中使用 SQL

    • 静态SQL：编译程序时预置SQL命令
    • 动态SQL：执行程序时送出SQL命令

24. 游标：游标用于显示行位置，通过游标，访问每一行；使用程序语言的循环语句移动游标，可以遍历多个行。

    一般程序语言不会像SQL一样一条命令集中处理多个行。因此在程序语言中使用SQL，使用游标可以抽取结果表格中每一行的数据。

25. 事务：
    用户的一个连贯操作（增删读写组合）我们称之为数据库事务；数据库事务必须由提交或回滚中的任意一个来结束；数据库应该具备 同时执行多个事务，同时不允许各事物之间产生矛盾 的特性。

    数据库事务的ACID属性

    性质	内容	含义
    A(Atomicity)	原子性	数据库事务必须结束于提交或回滚中的任意一个任务
    C(Consistency)	一致性	执行数据库事务时不能损坏数据库的一致性
    I(Isolation)	隔离性	两个事物的执行时互不干扰的，一个事务不可能看到其他事物执行时中间某一时刻的数据
    D(Durability)	持久性	在事务完成之后，该事务对数据库所做的更改便持久地保持在了数据库之中，不会被回滚

    • 提交(COMMIT)：确定 数据库事务处理的指令
    • 回滚(ROLLBACK)：取消数据库事务处理的指令

    提交和回滚可在事务结束时自动执行，也可分别使用二者的命令来显式执行

26. 同时执行控制

    • 可序列化：即使多个事务并行处理也和逐个处理得到的结果一样的功能

    • 锁：用于避免并行处理的两个数据库事务同时访问相同的 资源 (事务操作对象的表格和行) 时出现矛盾，防止更新遗失，实现可序列化

      • 共享锁：读取数据时对资源使用
        一个资源已经被一个事务加上共享锁，其他事务可以添加共享锁，但是不能加设独占锁
      • 独占锁：写入数据时对资源使用
        一个资源已经被一个事务设置了独占锁，其他事务不能再加设共享锁和独占锁

      	共享锁	独占锁
      共享锁	O	O
      独占锁	×	×

    • 两相锁
      各事务是由加设锁和解除锁两相构成。如何理解？

      现有一个数据库事务操作两个资源对象A和B，那么这个事务的上锁情况是：第①步为A、B都上锁，②为A、B进行事务操作，③为A、B进行解锁
      而非 上锁A——操作A——解锁A 上锁B——操作B——解锁B 这样的单次单对象操作

    • 锁的粒度

      1. 以表格为单位上锁--->粒度大--->每个事务加设锁次数少--->CPU负担相对小--->等待解除那些其他加锁事务时间长，可同时执行的事务少
      2. 以行为单位上锁--->粒度小--->每个事务加锁的次数增加--->CPU负担相对大--->等待解除那些其他加锁事物时间短，可同时执行的事务多
    • 其他同时执行控制
      
      • 使用情景：事务数少或读取行为占多数
        
        1. 时间戳控制
        2. 乐观控制

27. 隔离级别（ISOLATION LEVEL）：大型以及频繁使用的数据库，同时执行的事务不断增加，A事务对某行的事务操作未结束，B事务便对A事务操作的行进行读取；控制事务之间的互相干涉级别，叫做隔离级别。

    隔离级别——锁的牢固程度

    SET TRANSACTION 命令能设置隔离级别

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    /*** `READ UNCOMMITTED`/`READ COMMITTED`/`REPEATABLE READ`/`SERIALIZABLE` ***/

    设置隔离界别可能带来的现象

    • 脏读：事务2在事务1提交前读取某行，然而事务1发生回滚的情况下，事务2读取了不存在的行的情况
    • 非重复读：事务2在是事务1提交前读取某行，事务1对该行的操作提交成功，事务2再一次读取该行时发生数值不一致
    • 虚读：事务2在读取符合条件的行时，事务2追加了符合事务1条件的行，导致事务2第二次检索的结果发生不同

28. 索引：存储快速访问数据的收纳位置，用于在大量数据对部分数据的快速检索

    • B树索引：等号检索劣于散列索引，非等号检索优于散列索引
    • 散列索引：不适于非等号检索，适于等号精确检索

29. 查询过程分析

    1. 先行根据条件执行选择，减少行数，缩小范围（选择时可以是全条件检索和索引方法检索）；
    2. 再执行投影减少与结果无关的列；
    3. 最后执行列的连接。连接方法：
       
       • 嵌套循环
       • 分类合并
       • 散列

    查询最优化方法的确定标准：规则导向、成本导向

    • 规则导向：预先确定多个规则，按照规定决定的优先顺序选择方法
    • 成本导向：根据数据库内部的统计信息进行选择的方式

30. 故障恢复与日志

    • 事务故障：事务不完备导致事务执行无法结束——发生故障的事务将会被回滚
    • 系统故障：因为停电等原因造成系统停运，于此同时事务仍旧处在执行的状态——回滚或前卷
      
      • 前卷法：见 21. 事务
      • 回滚：见 21. 事务
    • 介质故障：硬盘介质损伤——基于备份文件进行故障恢复，对备份后提交的事务进行前卷处理

31. 数据库安全与权限

    1. 设定访问数据库和表格的权限

    2. 设定用户的操作权限

       GRANT SELECT,UPDATE ON onetable TO someone 
       [WITH GRANT OPTION]/*** 可选项，是否赋予某用户赋权的权限 ***/
       /*** SELECT INSERT UPDATE DLEETE ALL ***/

       REVOKE SELECT,UPDATE ON onetable TO someone;
       /*** 收回权限 ***/

    3. 设定视图权限（一般用于读频率较多的展示性信息）
       抽取部分数据表的部分数据制作成视图，再设定用户对视图的权限，能够实现设定访问表格中部分数据的权限。

32. 网络(web)和数据库
    一般web和数据库构成的系统的分为三层
    1. 表示层：客户端（一般为web浏览器），用户输入、获取对数据库的检索条件显示检索结果
    2. 功能层：应用服务器、web服务器，对SQL命令进行整合，加工并发送所数据库的检索SQL
    3. 数据层：数据库服务器，检索并返回结果

33. 存储过程

    • 定义：数据库预先定义配置的程序，将每次都使用的处理包装成预存步骤
    • 使用的必要性：由于它是预先定义配置好的程序，无需频繁的每次一个个发送SQL命令，能达到减少网络负荷的作用，应用的开发也变简单了（无需拼凑SQL，只需触发预先准备好的存储过程执行操作便可）
    • 分类：
      
      • 自动启动的存储程序
      • 触发存储程序：需要触发器触发，触发器即能触发存储程序的扳机
    • 触发器：能触发预先定义的存储程序的一个事件或一个指令
    • 实例：jd 将某一件商品减少一件库存，在配送表中添加该条商品的操作可以存储为一个存储过程，用户下单时就像一个触发器，数据库马上开始执行预先定义好的存储程序

34. 分布式数据库
    数据库尽管被分散了，但是仍有必要把它当成一个数据库处理。

    1. 水平分布式数据库：多个同级别数据库服务器组成，各数据库服务器间数据共享。
       
       • 实例：按照城市的不同而建立的多个不同的同级分布式数据库
    2. ……

分布式数据库知识点较难，以后需要时再补充！