数据库原理及应用总结【纯享版】

1. 数据库系统概论

数据库3个特点：永久存储，有组织，可共享

数据库管理技术：

1. 人工管理：无专门的软件，数据不共享，程序与数据不具有独立性

2. 文件系统管理：有专门用于管理数据的应用软件（以文件的形式存储在存储设备上）,程序与数据具有一定的独立性，但独立性差，数据共享性差，冗余度大

3. 数据库系统管理

数据库系统的组成：

数据库系统（DBS）包括：数据库(DB)，数据库管理系统(DBMS)，应用程序，数据库管理员(DBA)，用户

DBMS：一种系统软件

数据库系统的特点:

1. 数据结构化(数据库系统与文件系统的本质区别)

2. 数据的共享性高，冗余度低，易扩充

3. 数据独立性高

4. 数据由DBMS统一管理和控制

2. 数据库系统结构

数据模型的组成元素：

1. 数据结构
2. 数据操作
3. 数据的完整性约束

数据模型的分类：

1. 概念模型：信息世界，E-R模型

   实体(矩：形)，属性(椭圆)，联系(菱形)

   联系：一对一，一对多，多对多(商品与顾客，学生与课程)

2. 逻辑模型：机器世界，如层次模型(数据结构是一棵“有向树”)，网状模型，关系模型(二维表)

3. 物理模型：机器世界，物理存储方式与存取方法

数据库系统的三层模式结构：

1. 外模式(又称子模式、用户模式，可有多个)
2. 概念模式(又称模式，只有一个)
3. 内模式(又称存储模式，只有一个)

数据库的二级映像：

1. 外模式/模式映像 （保证逻辑独立性）
2. 模式/内模式映像 （保证物理独立性）

3. 关系数据库基础

关系就是表，表不一定是关系

关系的描述称为关系模式 eg:师生关系(教师，学生)

关系数据库：

1. 元组/记录（关系中的每一行数据）
2. 属性/字段（关系中的每一列）
3. 域 （属性的取值范围）
4. 码/关键字/键 （可以唯一表示表中的一个元组，可由一个或多个属性组成） 不能为空值，取值具有唯一性
5. 主码/主键 （被选中的候选码）一个关系只有一个主码
6. 候选键/候选码：一个关系中的所有码构成候选码
7. 替换键/替换码 （未选中的候选码）
8. 主属性 :包含在任何一个候选码中的属性
9. 非主属性：不包含在任何一个候选码中的属性
10. 组合键：多个属性组合起来才能唯一标识
11. 外码 ：与所参照的主码具有相同的域，只能引用所参照主码中的值或者为空值（取值可重复）

关系的完整性：

1. 域完整性 ：属性的值域
2. 实体完整性：必须有主码，且主码唯一不为空
3. 参照完整性：外码的取值
4. 用户定义完整性：符合实际意义或业务规则

关系操作：

1. **基本运算:**①并②差 ③笛卡尔积 ④投影⑤选择

2. 传统的集合运算：并、交、差、广义笛卡尔积

   并相容性：关系R与关系S属性个数相同，属性对应的域相同，对应属性名不一定相同（若不同，以前一个关系的属性名为准）

3. 专门的关系运算：选择、投影、连接、除

   选择：选符合的行

   投影：选符合的列

   连接：在笛卡儿积的结果中选择符合连接条件的

   ​ eg: 等值连接(同名的属性不会合并)，自然连接(同名的属性会合并)

4. 关系数据库标准语言SQL

SQL方言：

1. SQL Server 的T-SQL
2. Oracle的PL/SQL

SQL的功能组件：

1. DDL：definition定义 create ,drop

2. DML: manipulation操纵 select ,update, delete,insert

3. DCL: control 控制 grant

   以下面为例讲解SQL语法：

   注意事项：

   1. sql不区分大小写
   2. sql中全部用单引号，不用双引号
   3. 必须切换到英文输入法

--创建学生数据库：
create database 学生

--使用学生数据库
use 学生

--创建学生数据库中的Student表
CREATE TABLE Student (
    --格式：列名 数据类型(长度) 约束,
	Sno char (7) PRIMARY KEY  ,
	Sname char (10)  NOT NULL ,
	Ssex char (2) check(ssex in('男','女')) ,
	Sage tinyint  ,
	Sdept char (20) 
)
--常用数据类型:
--1. char(n)  固定长度为n
--2. varchar(n) 可变长度,允许最大长度为n
--3. int 占四个字节
--4 tinyint  0~255 占一个字节
--5. float 
--6. datetime 日期+时间 eg:1988年1月1日19:23:59

--约束分为:
--1. 主键约束 primary key
--2. 非空约束 not null
--3. check约束 
--4. 默认约束 default
--5. 唯一约束 unique
--6. 外键约束 foreign key XX references 表名(xx)


CREATE TABLE Course (
	Cno char (10) PRIMARY KEY ,
	Cname char (20)  NOT NULL ,
	Ccredit tinyint  ,
	Semester tinyint  ,
)

CREATE TABLE sc (
	sno char (7) NOT NULL ,
	cno char (10) NOT NULL ,
	grade smallint  ,
	XKLB char (6)  ,
	 PRIMARY KEY(sno,cno),
	 FOREIGN KEY (cno) REFERENCES Course (Cno),
	 FOREIGN KEY (sno) REFERENCES Student (Sno),
	 CHECK (grade >= 0 and grade <= 100)
) 

go

--向Student表中插入数据:
insert student values('9512101','李勇','男',19,'计算机系')
insert student values('9512102','刘晨','男',20,'计算机系')
insert student values('9512103','王敏','女',20,'计算机系')
insert student values('9521101','张立','男',22,'信息系')
insert student values('9521102','吴宾','女',21,'信息系')
insert student values('9521103','张海','男',20,'信息系')
insert student values('9531101','钱小平','女',18,'数学系')	 
insert student values('9531102','王大力','男',19,'数学系')	

insert course values('c01','计算机文化学',3,1)	
insert course values('c02','VB',2,3)
insert course values('c03','计算机网络',4,7)
insert course values('c04','数据库基础',6,6)
insert course values('c05','高等数学',8,2)
insert course values('c06','数据结构',5,4)

insert sc values('9512101','c01',90,'必修')
insert sc values('9512101','c02',86,'选修')
insert sc values('9512101','c06',NULL,'必修')
insert sc values('9512102','c02',78,'选修')
insert sc values('9512102','c04',66,'必修')
insert sc values('9521102','c01',82,'选修')
insert sc values('9521102','c02',75,'选修')
insert sc values('9521102','c04',92,'必修')
insert sc values('9521102','c05',50,'必修')
insert sc values('9521103','c02',68,'选修')
insert sc values('9521103','c06',NULL,'必修')
insert sc values('9531101','c01',80,'选修')
insert sc values('9531101','c05',95,'必修')
insert sc values('9531102','c05',85,'必修')
go
```

单表查询：select * from 表名

多表查询：

1. 内连接 innner join （inner可省略）
2. 外连接 left join, right join , full join

嵌套查询：

1. 使用in的子查询
2. 使用any ,some,all的查询
3. 使用exists 的子查询

数据更新：

1. 插入数据
2. 更新数据
3. 删除数据

视图：

create view 视图名

as

在视图上不能创建基本表，可以创建视图
系统不保存视图的数据，只存储视图的定义
视图并不会加快查询速率

索引：其价值在于能优化存取路径

1. 经常用到的表，考虑建立索引
2. 对于频繁更新的表，索引不能定义太多
3. 聚集索引一个表只有一个，非聚集索引一个表可以有多个
4. 删除索引用drop

5. 关系数据库设计理论

数据依赖的影响：数据冗余度大，更新异常，插入异常，删除异常，但不会查询异常

1. 第一范式(1NF):给的一个关系最基本会满足1NF

2. 第二范式(2NF): 不存在非主属性部分函数依赖于码

3. 第三范式(3NF): 不存在非主属性传递函数依赖于码

4. BC范式：不存在主属性对非所在码(不包含本属性的码)的传递与部分依赖（一般有2个及以上候选码时才考虑）

5. 第四范式(4NF): 属性之间没有非平凡且非函数依赖的多值依赖

关系模式的规范化：并不是规范化程度越高越好，有时候会适当反规范化来增加冗余，以此来提高查询效率

6. 数据库设计

基本步骤：

1. 需求分析：数据流图
2. 概念结构设计：系统说明书，E-R图，实体联系图
3. 逻辑结构设计：系统结构图(模块结构图)，关系模型
4. 物理结构设计：确定存取方法：索引方法，聚簇方法，Hash方法
5. 数据库实施
6. 数据库运行与维护

其中，

概念结构设计中局部E-R模型的合并过程中会产生三种冲突：

1. 属性冲突 ：类型，取值范围不同
2. 命名冲突：同名异义，异名同义
3. 结构冲突

逻辑结构设计中把概念模型(E-R图)转化为关系数据模型是重点

聚簇：一个数据库可有多个聚簇，一个关系只能有一个聚簇

7. 数据库保护

数据库的保护：

1. 并发控制
2. 数据恢复
3. 安全性控制
4. 完整性控制

事务：数据库应用程序的最小逻辑工作单位。

或者定义为：是用户定义的一个数据操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单元。

事务的特征：(ACID特性）

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

并发操作引起的问题：

1. 丢失修改
2. 读”脏“数据 rollback回滚
3. 不可重复读 同一事务两次读取同一个数据项

对于以上的问题，采取封锁技术

基本的封锁类型：

1. 排他锁/写锁/X锁
2. 共享锁/读锁/S锁

其中，在一个事务加了S锁后只能再加S锁，在一个事务加了T锁后不能再加任何锁

封锁协议：

1. 一级封锁协议：保证 丢失修改
2. 二级封锁协议：保证 丢失修改 和 读”脏“数据
3. 三级封锁协议：保证 丢失修改 和 读”脏“数据 和 不可重复读

死锁：

预防死锁：

1. 一次封锁法
2. 顺序封锁法

诊断死锁：

1. 超时法
2. 事务等待法

并发调度的可串行性：

1. 可串行化是并发事务正确性的准则：即只有可串行化，才认为是正确的调度
2. 两段锁协议保证可串行性

数据恢复：

1. 建立冗余：数据转储，登陆日志文件
2. 实施数据库的恢复

其中,

数据转储可分为

1. 静态转储：系统无运行事务时进行的转储操作。得到的一定是一个数据一致的副本。降低了数据库的可用性
2. 动态转储：不用等待正在运行的用户事务结束，也不影响新事务的运行。并不能保证数据正确有效。

数据转储也可分为

1. 海量转储：每次转储全部数据库
2. 增量转储：每次只转储上一次转储后更新过的数据，适合较大的数据库

登陆日志文件必须遵循的两个原则：

1. 登记的次序严格按照并发事务的时间执行
2. 必须先写日志文件，后写数据库

8. 安全管理

数据库文件：

1. 主数据文件 .mdf 唯一
2. 次数据文件/辅助数据文件 .ndf
3. 事物日志文件/日志文件 .ldf

SQL Server提供的安全控制：

1. 确认用户是否是数据库服务器的合法用户（具有登录账号）
2. 确认用户是否是特定数据的合法用户（具有访问数据库的权限）
3. 确认用户是否具有合适的操作权限（具有操作数据库数据的权限）

9. 备份与恢复数据库

备份方法：

1. 完整备份：不适合频繁备份
2. 差异备份 ：在完整备份的基础上
3. 事务日志备份：在上一次备份的基础上

10. 存储过程与触发器

T-SQL语法变量：

Declare @name varchar(10)

set @name=‘张三’

print @name

存储过程：

create procedure XX

as

begin

…

end

执行：execute XX

11. 数据库新技术与其他知识点

NoSQL:一种非关系型数据库

分为：

1. 键-值存储数据库:redis
2. 列存储数据库:HBase
3. 文档型数据库:MongoDB
4. 图形数据库:InfoGrid

2017年，SQL server 2017支持Linux