三级数据库学习笔记

数据库的三级模式
答：外模式、模式、内模式。外模式指用户可见的部分数据的表现形式；模式指全体数据的逻辑结构，用户不可见，是三级模式的中间部分；内模式指数据库的物理结构和存储方式

数据库的二层映像>>保证了数据库系统中数据独立性

[外模式/模式映像]： 数据逻辑独立性
[模式/内模式映像]： 数据物理独立性
浏览器/服务器架构中的应用服务器是什么？
应用服务器指通过各种协议把商业逻辑提供给客户端的程序。它提供了访问商业逻辑的途径以供客户端程序使用，并接受来自web浏览器用户的请求，根据相关规则执行相应数据库应用程序，通过访问接口向数据库服务器提出数据处理请求；它也接受来自数据库服务器的数据访问结果，并通过客户端将结果返回用户。数据库服务器出问题时，应用服务器无法向用户提供服务。

关系模式和关系

[关系模式]：描述关系的静态结构，是静态、稳定的
[关系]：是动态、随用户对数据库的操作而变化的
数据库设计的六个阶段
需求分析、概念设计、逻辑结构设计、物理结构设计、数据库实施阶段、数据库运行维护阶段

概念模型中属性相关规则

一个实体集的属性名要唯一
属性一般用名词或名词短语命名
一个属性的值必须属于唯一的域，域相当于数据类型的子集
实体集的标识属性能够唯一识别实体集中每一个实体
标识属性的取值不能重复，也不能为空
什么是候选码
某一组属性可以唯一标识一个元组，候选码取值不能重复不能为空

人机界面相关

人机界面应向用户及时反馈，保持人机界面和用户的双向交流
系统出错时，人机界面应提供用户有意义的反馈信息，如截屏功能等
良好人机界面容忍用户的错误操作，并能够方便地从错误中恢复
人机界面的设计可采用原始迭代法，首先初步设计，再进行用户界面细节设计，最后是原型设计与改进
SQL Server 2018中，如果tempdb的空间不够用该怎么办？

手动扩大tempdb中某文件的大小

设置tempdb中数据文件为自动增长方式，空间不够时自动增加

为tempdb增加一个数据文件

复制数据库的方法

使用数据库向导实现服务器之间的复制或移动数据库
还原数据库备份，通常使用backup和Restore Transact-SQL语句。
SQL中 UNIQUE是对创建的表的属性的约束，与其出现位置无关

SQL语言中，建立索引是加快查询速度的有效手段。建立索引使用CREATE INDEX语句
一般格式：CREATE [UNIQUE] INDEX(索引名) on <表名>(<列名>,[次序],<列名>[次序]) INCLUDE(属性名) WHERE 约束条件

概念数据模型
数据概念模型不依赖于具体的计算机系统华人数据库管理系统；
主要用于数据库设计，是设计人员与用户交流的工具；
概念数据模型主要描述世界的概念化结构，集中分析数据与数据的关系，与DBMS无关，没有完整型约束条件。

数据库系统实现与部署阶段，主要是系统编码和测试，针对系统的不足的升级是维护阶段的事

存储管理器
存储管理器负责检查用户是否有数据访问权限；
为提高访问效率，存储管理器会将部分内存用于数据缓冲；
为编译好的查询语句生成执行计划是查询管理器的功能，
存储管理器以事务方式管理用户对数据的访问，以确保数据库并发访问的正确性。

三层B/S结构：
第一层：浏览器（对客户机配置要求不高）
第二层：web服务器（浏览器和web服务器使用超文本传输协议HTTP）
第三层：数据库服务器

函数依赖：研究关系属性之间的依赖关系
比如学生表，知道学号就可以知道姓名，则称姓名依赖于学号。

关于三级结构独立性
一个数据库只能有一个内模式；
可以有多个外模式，是应用程序或其他接口；
三级模式提供了数据独立性，即当逻辑结构和数据结构发送变化时，应用程序不受影响；
数据独立性和应用程序分离简化了数据库应用程序开发，存取交给DBMS处理没有怎增加用户负担。

bigint:8Byte
int：4Byte
smallint：2Byte
tinyint：1Byte 0~255

触发器是用户定义在关系表上的一类由时间驱动的特殊过程

PL/SQL：procedure language过程化SQL，主要由命名块和匿名块构成。匿名块每次执行都需要编译，不能存储到数据库中，也不能在其他PL/SQL中调用。存储过程和函数是命名块，他们被编译后保存在数据库中，可以反复调用 。结构为：
CREATE Procedure 过程名(参数列表)
AS < PL/SQL块>; //参数列表可以输入输出，且参数均可以有多个

创建新用户数据库的主要数据大小不能小于原model数据库的大小，model数据库为新创建的数据库提供模板

可以先创建列，再在后面写约束；//如primary key（列名）
也可以创完列后立即写约束，如：ID tinyint primary key；

当用CHECK进行条件约束时，若只涉及对某一列的约束则可以放在该列后面；若约束条件涉及多个列，则是对整个表的约束，要将CHECK放在最后面

当用户权限和所在角色权限发生冲突时，以角色权限为标准；如角色有DENY UPDATE权限，而用户被授予UPDATE权限，则最终用户是没有UPDATE权限的

系统数据库maser、model、msdb修改完要及时备份，tempdb属于系统临时数据库，不需要备份

分配了写权限的用户有增删改的权限，没有查询权限。

多表查询复杂的话，可以将多表合并，可以提高查询效率，如果使用嵌套或者放入存储过程，没有简化链接的计算量，从而提高效率

排他锁和共享锁
1.排他锁，又叫独占锁（exclusive locks）当事务T对某个数据加上排他锁时，T可以对该数据进行读写操作，其他任何事务都不能对该数据加任何锁，直到T释放该锁才行。
2.共享锁，又叫读锁（share locks）当事务T对数据加上共享锁，则T只能对该数据读操作，不能修改数据，且可以让其他事务加上共享锁但不能加排他锁。只有该数据没有被其他任何事务访问时才能加排他锁。

照片存储在数据库中，不方便多台机器同时访问，同一时间的高并发访问对数据库的要求极高

索引

主索引：根据对象的主键建立索引，决定被索引记录的位置
辅索引：没有限制，可以查询多个数据问价的存放位置
位图索引：一般在范围查询时使用
数据库系统日志文件

日志的写入顺序必须和事务执行次序一致
为保证数据库可恢复，必须严格保证先写 日志再写数据库，反过来可能刚操作完数据库 就出故障
日志文件中检查点主要作用是提高系统出故障后的恢复效率
数据恢复

还原备份数据库语句：restore database 数据库名
还原日志备份：restore log 数据库名
备份日志时使用norecovery来备份尾日志，数据库进行还原，其他进程不能访问
master是非常重要的数据库，备份时只能使用完整备份

并行数据库分为两层，顶层是无共享结构，底层是共享内存或共享磁盘
无共享结构降低资源竞争，有很高的拓展性，常用于OLTP（联机事务处理）
并行数据库系统经常使用负载均衡方法提高数据库系统业务吞吐率

分布式数据库系统主要目的是实现场地自治和数据透明共享

OLAP和OLTP

OLAP（联机分析处理）：对海量数据进行查询和分析，访问数据量大，操作十分复杂，属于宏观操作，一般由企业中上层或决策层使用
OLTP（连击事务处理）：对一条记录的查询和修改，要求响应速度快，对安全性和吞吐量要求很高，属于微观操作，一般由中下层业务人员使用
数据定义语言DDL经编译后，各种对象的描述存放在数据库的数据字典中

数据模型有三要素：数据结构、数据操作、完整性约束

SELECT TOP n WITH TIES ,找出排名前三项，ties表示并列的也包括

SQL Sever数据库存储基本单位是页， 每页大小8KB，页的单个行中最大数据量和开销是8060字节。存储一张表的空间利用率=表的大小/所占存储空间。

数据表中唯一性约束有两个，UNIQUE和PRIMARY。
PRIMARY KEY约束多为强调主键不可出现相同元素，但强调一列或多列组合的唯一性时应使用UNIQUE约束；
UNIQUE允许出现空值，但不能重复，PRIMARY KEY 不行。

数据库给角色赋予权限：EXEC sp_addsrvrolemember ‘角色名’,权限名
bulkadmin:执行BULK INSERT语句
dbcreator:创建、修改、删除、还原数据库
diskadmin:管理磁盘文件
processadmin:管理在SQL Sever实例中运行的进展
securityadmin:管理服务器登录账户
serveradmin:配置服务器范围的设置
setupadmin:添加和删除连接数据库
sysadmin:在SQL Server中进行任何活动，该角色权限跨越所有其他固定服务器橘色

数据库管理系统一般通过周期性检查事务等待图来实现死锁检测

事务等待图存在回路说明出现了死锁

多个用户并发操作会破坏事务的隔离性，从而导致数据的不一致性，所以数据库必须提供并发控制机制。

系统故障导致数据库不一致性，对故障发生前已提交的事务进行redo操作（既有BEGIN Transaction，也有commit）；
对故障发生时未完成的事务做ondo操作（只有BEGIN Transaction，没有commit或rollback操作）

并行数据库服务器分两种结构：完全共享资源结构、完全不共享资源结构
完全共享资源结构：每个数据项的存取经过公共通信线路，所有处理器共享内存、磁盘等资源
完全不共享资源结构：每个站点有独立的内存和磁盘

数据仓库：是一个面向主题的、集成的、非易失的、且随时间变化的数据集合，用来支持管理人员决策。

粒度是设计数据仓库的一个最重要方面，细化程度越高，粒度越低；细化程度越低，粒度越高。

数据库优化相关：

数据库优化时首先要确定优化目标；
CPU使用率超过90%以上说明处理器资源不足
优化过程中需要考虑网络情况
服务器运行队列数量超过逻辑CPU数量说明CPU已经饱和
IDED0需求建模方法中，矩形左侧箭头代表input（输入），右侧output（输出），上方是control（控制），下方是mechanisms（机制）

IDEF1X，是IDEF1的拓展版，在ER方法的原则上增加了一些规则。IDEF1X建模方法：实体集（独立实体集、从属实体集）、联系（标定型联系、非标定型联系、分类联系、非确定联系）

将表改为分区表，属于物理结构设计

编写事务时，各事务尽量按相同顺序访问资源，好处是：降低发生死锁的可能性

如果完整性约束涉及到表的多个属性列，则属于关系级完整性约束

辅索引：数据文件非主属性上建立的索引叫辅索引；唯一索引：确保索引列不包含重复值的值，用unique表示

用例模型：是把满足用户需求的功能表示出来的工具，由用例、角色、系统三部分构成
角色是系统外部的实体，既可以是用户也可以是外设硬件；
用例描述从用户的角度观察系统应有的功能，帮助分析人员理解系统行为；
系统用于抽象表示能够实现的所有基本功能；

DESC 降序排序；ASC 升序排序

存储过程：存储在数据库给所有用户调用的子程序
一个存储过程可以输出多个参数；
存储过程不支持输入参数有默认值，但定义了参数默认值或将参数设置作为等于另外一个参数时，可以有默认值；
存储过程最多有2100个参数，返回结果可以是一个集合；存储过程属于DDL语言，删除用DROP PROCEFURE 存储过程名。

定义存储过程：CREATE PROC p1 @x char(10),@y int,@z int
调用该存储过程时，参数个数、数据类型要一一对应：EXEC p1 'abc',100,200

聚集索引：指数据文件中数据记录的排列顺序与索引文件中索引项的排列顺序一致
一个表只能有一个聚集索引，因为表的物理顺序只有一种情况，可以同时有聚集索引和非聚集索引；
聚集索引以外的索引全都是非聚集索引，即数据记录的排列顺序与索引文件中索引项的排列顺序不一致；
聚集索引的作用是加快数据的查询。

分离数据库：指将数据库从SQL Server数据库列表中删除，但不删除数据文件和日志文件；分离数据库需要对数据库具有独占访问权限，所以正被用户访问的数据库不能被分离

SQL Server分区表：
分区表是将表中的数据水平划分成不同的子集，子集映射到一个或多个文件组中；
创建分区表的第一步是创建一个分区函数；
分区函数作用是定义表的分区方式。

回收权限语句：REVOKE 操作命令 ON 对象名 FROM 用户名

如回收用户在表中的查询、插入权限
REVOKE SELECT,INSERT ON T1 FROM U1

SQL Server2008 身份验证模式：
windows身份验证模式：sql server 通过windows操作系统获取用户信息，并对用户名和密码重新验证
SQL Server和Windows身份验证模式：sql server允许sql授权和winsows授权用户登录到数据库服务器；
用户有登录账号后，只能连接到数据库服务器，并不具有访问用户数据库的权限，只有称为合法用户后才能访问数据库。
DBAS实现和部署阶段工作：
建立数据结构
数据加载
事务和应用程序的编码和测试
系统集成、测试与运行
系统部署
DBMS维护阶段工作：
数据库的转储和恢复
数据库的安全性和完整性控制
数据库性能分析和改进 //如建立索引
数据库重组和重构
数据库模式调整优化相关：
分割表优化有两种：水平分割和垂直分割
增加派生性冗余列一般可以减少查询时的计算量
模式调整第几范式需要结合实际情况
表中数据量很大的情况，模式调整过程中需要考虑减少表的连接
索引的目的是提高查询效率，建立索引的原则有：
经常作为条件被使用的列
频繁排序或分组的列
值域很大的列
待排列的列有多个时，应建立复合索引
可以使用系统工具检查索引的完整性，必要时进行修复
提高查询效率
建立聚簇；
建立索引；
将数据文件和索引文件分别放不同磁盘；
将数据文件和日志文件分别放不同磁盘，两种I/O操作在同磁盘会产生影响系统性能的I/O竞争，降低系统响应速度。
数据库故障及解决方法相关：
事务内部故障分为预期故障和非预期故障，非预期故障是不能有事务程序处理的（如运算溢出、死锁等），事务故障的恢复时系统自动完成，对用户是透明的
预期的事务内部故障可以通过事务回滚，是数据库达到一致性状态
数据库系统发生软故障后需要进行UNDO和REDO两项工作
采用硬件容错可以降低介质故障的影响
数据库备份相关：
完整数据库备份+日志文件备份：恢复时间较长
完整数据库备份+差异数据库备份+日志备份：备份和恢复速度较快
仅使用差异数据库备份无法恢复数据库
PaaS：Platfrom as a Servce 平台即服务
元数据（metadata）：描述其他数据的数据，如数据字典、说明文档等
决策支持系统：指辅助需要决策的人或系统进行决策的系统
数据挖掘：研究各种方法和技术，从大量数据中挖掘出有用信息
机器学习：研究使计算机模拟或实现人类的学习行为，让计算机自动获取知识
XML数据库：
能够对半结构化数据进行有效存取和管理；
提供对标签和路径的操作；
当数据本身有层次特征时，XML数据格式能清晰表达数据的层次特征，因此XML数据库便于对层次化的数据进行操作
一维数据划分：
轮转法：将元组一次划分到磁盘，适用于扫描整个关系的应用；
散列划分：通过hash函数，将元组分配到不同磁盘，适用于点查询；
范围划分：按照关系中某个属性的取值范围划分，适用于扫描整个关系的应用
在IDEF1X中，多对多联系又叫 非确定联系
如果一个表的数据量很少，且修改操作频繁，则该表最适合的文件组织结构是“堆文件”，因为堆文件无需建立索引，维护代价低，虽访问效率低但数据量小时，访问时间短
在使用UML进行系统建模时，一个系统可能存在许多用例，用例间关系有：“扩展、使用、组合”三种
通配符：
_：匹配任一字符；
%：匹配0到多个字符；
[ ]：用于转义；
^：用于排除一些字符进行匹配
SQL Server2008提供的4个排名函数的区别；
RANK（）：返回结果集分区中行的排名，行的排名是相关行之前的排名数加1，如果两个或多个行与一个排名关联，则每个关联得到相同排名 //
DENSE_RANK（）：返回结果集分区中行的排名，在排名中没有任何间断，行的排名等于所讨论行之前的排名数加1，如果有两个或多个行受同一分区中排名的约束，则每个约束行将接收相同的排名
ROW_NUMBER（）：返回结果集分区内的序列号，从第一行开始，oerder by子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序
NTILE（）：将有序分区中的行分配到指定数目的组中
对模式分解时，要保证“无损连接性”，也要保证分解“保持函数依赖”
分解通常使涉及属性少的查询、简单的跟新事务执行效率高
分解模式能消除冗余数据，但不规范的话会增加冗余
物理设计时日志文件要放在低速磁盘，数据常用的部分放高速磁盘，索引不一定放高速磁盘
B/S在适用internet、维护方面比C/S强，但运行速度受限于网络
在运行速度、数据安全、人机交互方面不如C/S
关系模型支持非过程化语句
执行select、delete等操作时，系统不会检查完整性约束
在频繁修改的表上建立索引可能降低系统性能
数据库在RAID10中存储比RAID5更稳定、速度更快
UML的类图表示：
空心三角实线：类；
空心三角虚线：接口；
空心菱形实线：聚合关系；//弱拥有关系，包含但不属于
实心菱形实线：合成关系；//强拥有关系，整体与部分不可分离