数据库系统概念

1. 绪论 

数据库的基本概念:

        数据(data): 数据库中存储的基本对象, 可以是文字, 声音, 图片, 视频等。

        数据库(DB): 概括来说就是永久存储, 有组织, 可共享的大量数据的集合。

        数据库管理系统(DBMS): 和操作系统一样是计算机基础软件, 主要有数据定义语言(DDL, 对数据对象的组成与结构进行定义)和数据操作语言(DML, 增删改查)等

        数据库系统(DBS):  由数据库, 数据库管理系统(及其应用开发工具), 应用程序和数据库管理员(DBA)组成的存储、管理、处理和维护数据的系统。

引入数据库后计算机系统的层次结构:

1.1 数据模型

        两类数据模型: 1) 概念模型 2) 逻辑模型+物理模型

        数据模型=数据结构+ 数据操作+数据的完整性约束条件

客观对象的抽象过程:

概念模型: 

        1) 实体: 客观存在可互相区别的事物, 如学生, 职工, 部门, 选课等。

        2) 属性: 实体所具有的某一特性, 如学生的学号, 姓名, 出生年月等。

        3) 码: 唯一标识实体的属性集, 如学生的学号, 整个属性组是码, 则称为全码

        4) 实体型: 实体名及其属性名集合, 如学生(学号, 姓名, 出生年月)就是一个实体型

        5) 实体集: 同一类型实体的集合

        6) 联系: 实体之间的联系, 如一对一, 一对多, 多对多。

逻辑模型:

1) 层次模型

        优点: 数据结构清晰简单, 查询效率优

        缺点: 多对多联系表示不自然, 对插入和删除限制多 

2) 网状模型

        优点:  具有良好性能

        缺点: 结构比价复杂

3) 关系模型

4) 面向对象数据模型

5) 对象关系数据模型

6) 半结构化数据模型

相关概念:  

        关系: 一个关系对应一张表

        元组: 表中的一行为一个元组

        属性: 表中的一列即为一个属性

        码: 可以唯一确定一个元组

        域: 属性的取值范围, 如 {男, 女}

        分量: 元组中的一个属性值

        关系模式: 关系名(属性1, 属性2, ... , 属性n)

        备注: 关系必须是规范化的, 每一个分量是一个不可分的数据项, 即不允许表中表。

数据库系统结构

        模式: 逻辑模式, 数据库中全体数据的逻辑结构和特征的描述, 是所有用户的公共数据视图。

        外模式: 称子模式或用户模式, 是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述。

        内模式: 存储模式, 数据物理结构和存储方式的描述。

1.2 数据库系统模式

数据库系统的三级模式结构:

数据库的二级映像: 

        1) 外模式/模式映像: 模式改变时, 可使外模式不变, 保证数据与程序的逻辑独立性

        2) 模式/内模式映像: 数据库的结构改变时, 可使模式保持不变, 保证数据与程序的物理独立性

 2 关系数据库

        笛卡尔积: 域上的一种运算。

例图:

        备注: 其中(刘逸, 计算机专业, 刘晨) 是元组(记录), 刘逸是分量。R(A1,A2 ...An), 即关系名(属性1, 属性2 ... 属性n) 称为关系模型

2.1 关系操作

关系的完整性: 

        实体完整性: 关系R中主属性不能取空值, 如学生(学号, 姓名, 年龄), 学号为主码不能是空值。

        参照完整性: 外码取空值或取对应关系表中某个元组的值

        用户定义的完整性: 如成绩只能取 0~100

关系代数运算符表:

示例表: 

2.1.1 选择

2.1.2 投影

        备注: 去重

2.1.3 连接

        备注: 自然连接是两个关系的相同属性上的等值连接。结果中把重复的属性去掉, 而等值连接不必。

2.1.4 除

2.2 数据库标准语言SQL

SQL的基本概念:

        视图: 数据库中只存放视图定义而不存放视图对应数据, 因此是一个虚表

2.2.1 SQL理论

数值类型：

        bit[(m)]      m制定位数，默认为1                tinyint           1字节

        smallint      2字节                                        int                4字节

        bigint         8字节                                        float(M,D)     4字节

        double(M,D)   8字节                                   decimal(M,D)  相当于java BigDecimal

        日期类型： datetime  8字节

        运算符: and, or, not

2.2.2 SQL定义

约束:

        NOT NULL: 不能为空                                                                            (用户定义的完整性)

        unque: 数据唯一                                                                                    (用户定义的完整性)

        check (条件) : 检查列值是否满足条件表达式                                         (用户定义的完整性)

        default: 默认值 

        primary key auto_increment：限制列为自增主键                                   (实体完整性)

        primary key (列名)：定义关系主码                                                         (实体完整性)

        foreign key (子表的列名) references父表名(列名)                                  (参照完整性)

创建数据库：

        create database 数据库名 default charset=字符集类型

创建表：

        create table 表名（列名 类型，列名 类型）default charset=utf8

创建索引:

        create unique (创建唯一索引) index 索引名 on 表名(列名asc/desc , 列名asc/desc)

创建断言:                

        略

创建视图:

        1) create view 视图名 as select 语句 (whit check option 表示要符合创建视图时的where语句, 不符合where则不能修改视图)

        2) create or replace view 视图名 as select 语句: 存在视图名则替换。

创建触发器:

        create trigger 触发器名 before/after 增删查改关键字 on 表名 for each row 触发器事件

创建存储过程:

        delimiter 符号:自定义结束标志符

        create procedure 存储过程名 (in/out/inout 参数名,参数类型)

        begin

        …过程体…

        end (自定义结束符)

存储的调用: 

        call 存储过程名();

创建函数: 只能用于查询

        create function 函数名(参数 参数类型)

        returns +返回值类型

        begin

        ……函数体

        return(xxx)

        end (自定义结束符)

函数的调用:

        select 函数名()

声明游标:

        declare 游标名 cursor is +select语句

打开游标:

        open 游标名

使用游标:

        fetch 游标名into 变量1,变量2,…………

关闭游标:

        close 游标名

创建会话变量:

        set @变量名:=变量值

创建局部变量:

        declare 变量名 变量类型 default 变量值   

创建用户:

        Create user ‘用户名’  identified by ‘密码’

多行插入：

        insert into 表名 values（列的值）,（列的值）

插入部分列数据：

        insert into 表名 （列名） values（列的值）

新增查询的数据(列要对应):

        insert into 表名 select 列名,列名 from 表名;

2.2.3 SQL 删除

删除数据库：

        drop database 数据库名

删除表：

        drop table 表名

删除数据：

        delete from 表名 where

        truncate table 表名:清空表

删除表索引:

        drop index 索引名 on 表名

2.2.4 SQL查询

显示所有数据库：

        show databases

查看表结构:

        show create table 表名;

查看表（所有表名）

        show tables；

查看表的列结构：

        desc（describe） 表名；

查看表内容：

        1) 查看全列：select * from 表名；

        2) 查看某列并设别名：select 列名，列名 as 别名 from 表名 as 别名；

        3) 查看某列（含去重）：select distinct 列名 from 表名；

        4) 排序查看：select 列名 from表名 order by 列名 asc（默认升序）/desc(降序)

        5) 筛选查看：select 列名 from 表名 where 条件（ NULL=NULL结果是NULL 为假 )

where条件: 

模糊匹配like：

        1) %: 匹配任意多个（包括 0 个）字符

        2) _ :  匹配严格的一个任意字符

正则表达式示例:

   select * from users where mail regexp  '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

        ^：这个符号表示字符串的开始。

        [a-zA-Z]：匹配任何单个字母，无论大小写。

        [a-zA-Z0-9_.-]*：匹配任何数量（包括零个）的字母，数字，下划线，点或破折号。星号表示前面的字符可以出现任意次数。

        \@leetcode\.com： \用于转义特殊字符@和.，使它们被视为普通字符而不是正则表达式的特殊符号。

        $：这个符号表示字符串的结束。

分页查询limit：

        select xxx from 表名 [where ...] [order by ...] limit n offset s; ( s 始，选 n 条, 起始下标为0 ) 

聚合查询(行查询):

函数:

        1) count(*): 对某列的查询,返回行数,为null的不记录当中

        2) sum(列): 对数字进行累加求和

        3) avg(): 求平均数

        4) round(x,2): 对x精确到小数点后两位

        5) min(): 最小值

        6) max(): 最大值

        7) DATEDIFF(end_date, start_date) BETWEEN 0 AND 29 : 用于计算两个日期之间的差距

        8) DATE_FORMAT(order_date,'%Y%m') = '202002': 用于筛选为2020-02开头的日期

        9) ifNull(xx, 默认值): xx为空则取默认值

分组操作:

        1）select 列名,max(列名) from 表名 group by 列名(分组依据);

        2）group by 列名:根据列名,将相同的值进行分组

筛选条件:

        having 条件:分组之后进行的条件筛选(where是在分组前筛选)

        备注: 分组查询后, 某列可能有多个不同值, 但查询显示只显示一个值

比如:

id           p_id            date

1              1                2018

2              2               2018

3              1                2017

        用select * from x group by p_id 后, date只能显示一个值了

笛卡尔查询:

        select * from 表1,表2;

多表查询:

        select * from 表1,表2 where 表名.列名=表名.列名;

外连接:

        select * from 表1 left/right join 表2 on 条件

自链接:

        select * from 表1 as 别名,表1 as 别名; 

        说明: 对自己笛卡尔积后,将行问题变列问题

子查询:

         略

集合查询:

        1) union :去重

        2) union all:不去重

        4) intersect: 交集

        5) minus: 差集

查看用户权限:

        Show grants

2.2.5 SQL修改

修改列数据：

        update 表名 set列名=值，列名=值 where 条件

alter修改表结构:

添加列:

        alter table 表名 add 列名 类型

添加索引:

        alter table 表名 add (fulltext)index 索引名(列名)

删除列:

        alter table 表名 drop 列名

改变列的类型:

        1）alter table 表名 modify 列名 类型 after 列名(可以after到某个列名后面)

        2）alter table 表名 change 旧列名 新列名 类型

改变列的名字:

        alter table 表名 change 就列名 新列名

改变表的名字:

        alter table 旧表名 rename 新表名

删除约束:

        alter table 表名drop 约束名 外键名

改变表的字符集:

        alter table 表名 character set utf8;

更改数据库密码:

        Set password=’新密码’

更改其他用户密码:

        1）Alter user ‘用户名’@’主机名(或者%)’ identified by ‘新密码’

        2）Revoke 权限 on 数据库.表名 from 用户

2.2.7 SQL 常用函数

        Year();   month();    now();    day(); 

        char_length(列名): 计算字符长度

        substr(string,start,length): string是字符串, start是起始截取位置(从0开始), length是截取长度

concat()函数

        举例：select concat (id, name, score) as 别名 from 表名；

group_concat()函数

        语法：group_concat( [distinct] 要连接的字段 [order by字段 asc/desc ])

case when语法:

        case 表达式

        when 值1 then 结果1的语句

        when 值2 then 结果2的语句

        ….

        end(结束)

if语法:

        if 表达式 then 执行语句

        elseif 表达式2 then 执行语句

        else 执行语句

        end if;

举例:

        SELECT id,

        SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,

        SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,

        FROM department

        GROUP BY id

        说明: case when 只会匹配 数据库的第一个数据，如果不是它就返回空了 用sum 就可以去 数据里的每个值都匹配一次 ，返回匹配得上的值总和。

while语法:

        while 表达式 do

        循环体

        end while

2.2.6 SQL其他

        cmd登入：mysql -u root -p123456

        source + 路径:导入数据库语句;

        选中数据库：use 数据库名

        注释: --

备份单个数据库:(不能加分号)

        Mysqldump -uroot -p 数据库名>备份的路径

备份全部数据库:

        Mysqldump -uroot -p -A>备份的路径

备份部分数据库:

        Mysqldump -uroot -p –databases 数据库1 数据库2>备份的路径

备份表:

        Mysqldump -uroot -p 数据库名 表名1 表名2>备份的路径

表的导出:

        1) 先查看mysql允许的导出路径: show global variables like '%secure%'

        2) select语句 into outfile “文件路径”

赋予用户权限:

        Grant select/update/drop(或all privileges)等等 on 数据库名.表名(或*) to ‘用户名’@’主机名’

3 关系数据库理论

3.1 数据依赖

1) 函数依赖：

        解释: 学号(Sno)决定学生所在的系(Sdept), 学号, 课程号(Cno)决定成绩(Grade)。

        完全函数依赖: X→Y X的任意一个真子集X' 使得X' --/-> Y

        部分函数依赖示例:  (学号, 课程号)能决定学生信息, 但 (学号) 就可决定学生信息, (学号)是(学号, 课程号)的子集, 所以不满足完全函数依赖, 所以为部分函数依赖。

        主属性: 包含在任何一个候选码中的属性称为主属性, 反之为非主属性。

2) 多值依赖: 

3.2 范式

        1NF: 每一个分量必须是不可分的数据项。

反例:

        2NF: 满足1NF基础上, 每个非主属性完全依赖于任何一个候选码。即不存在部分函数依赖。

        3NF: 在2NF的基础上, 消除了非主属性的传递依赖

        BCNF: 在3NF基础上, 消除了主属性中的部分函数依赖, 如以下例子, 其中S, J, T都为主属性。

(S,J)-->T, (S,T)-->J  T-->J 

        4NF: 解决多值依赖, 如上图的多值依赖WSC表, 将关系模式WSC(W,S,C) 分解为WS(W,S) 和WC(W,C)

规范化过程: 

3.3 闭包

        题目: 关系具有属性A,B,C,D,E,F, 函数依赖有{A->C, BC->DE, D->A, F->B}, 那么(AB)+

计算过程如下：

1. 初始闭包：{A, B}

2. 应用 A→C：闭包扩展为 {A, B, C}

3. 应用 BC→DE：闭包扩展为 {A, B, C, D, E}

4. 应用 D→A：A 已在闭包中，无需添加

5. F→B：F 不在闭包中，无法应用

6. 最终闭包：{A, B, C, D, E}

4. 数据库设计

数据库设计的六个阶段:

        1) 需求分析

        2) 概念结构设计

        3) 逻辑结构设计

        4) 物理结构设计

        5) 数据库实施

        6) 数据库运行和维护

各个阶段的描述:

 E-R图举例:

E-R图转化为关系模型(逻辑结构设计):

物理结构设计:

        1) B+树索引适合: 某个属性(组)经常在查询条件中出现。或经常作为最大最小值等聚合函数的参数。经常在连接操作的连接条件中出现

        2) hash索引适合: 一个关系的大小可预知且不变

        3) 聚簇存取: 提高某个属性的查询速度, 把具有相同值的元组集中存放在连续的物理块中。适合: 经常一起进行连接操作的关系操作的关系。经常出现在相等比较条件中。属性上的值重复率很高。

5. 数据库恢复技术

5.1 事务

        用户定义一个数据库操作序列, 这些操作要么全部做, 要么全不做

事务的ACID特性

        1) 原子性(Atomicity): 诸操作要么都做, 要么都不做

        2) 一致性(Consistency): 数据库只包含成功事物提交的结果, 与原子性密切相关

        3) 隔离性(Isolation): 一个事物的执行不能被其他事务干扰, 即一个事物内部操作及使用的数据对其他并发事务是隔离的, 并发执行的各个事务之间不能互相干扰

        4) 持续性(Durability): 也称永久性, 指事务一旦提交, 他对数据库中数据的改变就是永久的, 其他操作或故障对其执行结果不应该有任何影响。

5.2 并发调度

        并发调度的正确性标准是 可串行化

数据不一致性示例:

        1) 丢失修改: 两个事务 T1和T2 读入同一数据并修改,T2提交的结果破坏了 T1 提交的结果,导致 T1的修改被丢失

        2) 不可重复读: 不可重复读是指事务 T1读取数据后,事务T2 执行更新操作,使T1无法再现前一次读取结果。

        3) 读脏数据: 指事务 T1 修改数据并写回磁盘,事务 T2读取同一数据后, T1 由于某种原因被撤销,这时被T1修改过的数据恢复原值, T2 读到的数据就与数据库中的数据不一致, 则T2 读到的数据就为“脏”数据,即不正确的数据。

封锁:

        1）排它锁: 写锁, T事物对数据对象加上X锁, 则只允许T读和写, 其他事务不能加任何锁

        2）共享锁: 读锁, T事物对数据对象加上S锁, 则事物T可读不可写, 其他事务只能加S锁 

封锁协议:

        1) 一级封锁协议: 事务T在修改数据之前必须先对其加X锁, 直到事务结束才释放。 可防止丢失修改

        2) 二级封锁协议: 在一级封锁协议基础上增加事务T在读取数据之前必须先对其 加S锁,读完后即可释放S锁。防止了丢失修改, 和防止读“脏”数据。不能保证可重复读。

        3) 三级封锁协议: 在一级封锁协议的基础上增加事务T在读取数据R之前必须先对 其加S锁,直到事务结束才释放。

不同级别的封锁协议和一致性保证: 

活锁与死锁:

        1) 活锁: 相当于操作系统进程的饥饿现象

        2) 死锁: 同理操作系统进程的死锁现象 

两段锁协议:

        将事务的加锁和解锁操作分为两个不可重叠的阶段。确保事务调度的可串行化，防止脏读、丢失更新等并发问题。

        1) 增长阶段: 此阶段可以逐步申请锁（如共享锁、排他锁），但不能释放任何锁。

        2) 缩减阶段: 此阶段可以释放已持有的锁，但不能再申请新的锁。

5.3 恢复技术

        1）数据转储: 某个时期的整个数据库的备用数据(后备副本)。

        2）登录日志文件: 用来记录事务对数据库的更新操作的文件。 严格按并发事务的时间次序; 先写日志文件, 后写数据库。