数据库原理与系统设计(基于SQL Server)

最新推荐文章于 2024-08-07 22:07:28 发布

liwfay

最新推荐文章于 2024-08-07 22:07:28 发布

阅读量860

点赞数

文章标签： sql 数据库 sqlserver

本文链接：https://blog.csdn.net/lofone/article/details/114300704

版权

数据库原理与系统设计

数据模型：描述数据结构、数据操作以及数据约束的数学形式体系
- 概念(数据)模型
  - 独立于计算机系统的模型，是现实世界的第一层抽象
  - 比如实体-联系模型(E-R模型)、面向对象模型(OO模型)
- 逻辑(数据)模型
  - 用于描述数据库数据的逻辑结构，是现实世界的第二层抽象
  - 按计算机系统的观点对数据建模，即数据的计算机实现形式，主要用DBMS实现
  - 比如，层次模型(树)、网状模型(图)、关系模型(二维表)、面向对象模型、XML模型等
- 物理(数据)模型
  - 是数据抽象的最低层，用来描述数据的物理存储结构和存取方法
  - 物理模型的具体实现是DBMS的任务，数据库设计人员要了解和选择模型
关系模型
- 元组：表中的一行成为一个元组
- 属性：表中的一列成为一个属性
- 超码：唯一地标识关系r中的一个元组
- 候选码：是超码，但属性集的任意真子集都不是超码
- 主码：候选码中的一个
- 外码：用来描述本关系中的元组与另一个关系中的元组之间的联系
完整性规则
- 实体完整性：所有主属性不能为空
- 参照完整性：外码的取值要么为null，要么对那个某一元组中的主码
- 用户定义的完整性
数据库的三级模式结构
- 外模式
  - 也称用户模式，对应于视图层数据抽象，他是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述
  - 外模式是模式的子集
- 外模式/模式映像
  - 模式描述数据的全局逻辑结构，外模式描述数据的局部逻辑结构
  - 对应于一个模式可以有多个外模式
  - 对应于每一个外模式，数据库都有一个模式/外模式映像，它定义了该外模式与模式之间的对应关系
- 模式
  - 也称逻辑模式，对应于逻辑层数据抽象，是所有用户的公共数据实体图
  - 他是数据库管理系统模式结构的中间层，既不涉及数据的物理存储细节和硬件环境，也与具体的应用程序、所使用的应用开发工具及高级程序设计语言无关
- 模式/内模式映像
  - 数据库只有一个模式，也只有一个内模式，所以模式/内模式映像是唯一的
    它定义了数据全局逻辑结构与存储逻辑结构之间的对应关系
- 内模式
  - 也称存储模式，对应于物理层数据抽象，它是数据的物理结构和存储方式的描述，是数据在数据库内部的表示方式
数据库中包含4类数据：用户数据、元数据、索引、应用元数据
- 用户数据：通过结构化关系(二维表)组织的所有业务数据的集合
- 元数据：是对关系数据库结构的描述数据和数据库的有关统计数据，也成为数据字典
- 索引：为了改进数据库性能和访问行而建立的附加数据
- 应用元数据：用户窗体、报表、查询和其他形式的应用组件
连接
- 等值连接：属性值相等的元组
- 自然连接：等值连接基础上去掉重复属性列
- 自表连接：某表与自己进行连接
- 左外连接：连接结果中包含做关系中的所有元组，对于做关系中没有连接上的元组，其右关系中的相应属性用空值代替
查询中的关键字：
- SELECT [DISTINCT] institute [AS 学院]
- COUNT [DISTINCT|ALL] 统计关系的元组个数或者一列中值的个数
- SUM [DISTINCT|ALL] 统计一列中的总和(次列必须为数值型)
- AVG [DISTINCT|ALL] 统计一列中值的平均值(次列必须为数值型)
- [MAX|MIN] [DISTINCT|ALL] 统计一列中值的最[大|小]值
- FROM class [FULL|LEFT|RIGHT] [OUTER|INNER] [JOIN]
- WHERE [NOT] BETWEEN AND
- WHERE [NOT] IN
- WHERE IS [NOT] NULL
- WHERE [NOT] LIKE
- WHERE [AND] [OR] [NOT]
- WHERE > >= < <= = <> != [ANY|ALL]
- UNION
- GROUP BY 对查询结果按列分组，值相等的一组
- HAVING 对分组结果进行选择，仅输出满足条件的组
- ORDER BY [ASC|DESC]
通配符
- % 任意长度的字符串
- _ 任意一个字符
- ESCAPE’’ 声明’'为换码字符，对通配符进行转义
嵌套子查询 [IN|比较运算符|EXISTS]
- 非相关子查询：子查询结果不依赖上层查询
- 相关子查询：上层查询元组发生变化时，子查询必须重新执行
数据库设计过程：
- 需求分析：功能需求、数据需求、数据联系及约束、性能需求、数据使用业务规则…形成需求规格说明书
- 概念设计：是根据需求分析中得到的信息，运用适当的工具将这些需求转化为数据库的概念模型。形成E-R图
- 逻辑设计：层次、网状、关系、面向对象、XML
- 模式求精：对相关逻辑模式进行优化，如减少数据冗余，消除更新、插入与删除异常等。
- 物理设计：数据库文件的组织格式、文件内部的存储结构、建立索引、表的聚集
- 应用与安全设计：访问权限
存储过程和函数的区别：
- 函数有限制只能返回一个标量，而存储过程可以返回多个。并且函数是可以嵌入在SQL中使用的，可以在SELECT等SQL语句中调用，而存储过程不行
SQL数据定义语言

操作对象	创建	修改	删除
数据库	CREATE DATABASE	ALTER DATABASE	DROP DATABASE
基本表	CREATE TABLE	ALTER TABLE	DROP TABLE
视图	CREATE VIEW	ALTER VIEW	DROP VIEW
索引	CREATE INDEX	-	DROP INDEX

数据库命名规则
- 长度1~30，第一个字符必须是字母，或者下划线_,或者字符@
- 在首字符后的字符可以是字母、数字或者前面规则中提到的符号
- 名称中不能有空格
基本数据类型

类型	-	-	-	-
整型	int(4B)	smallint(2B)	tinyint(1B)
实型	float	real(4B)	decimal(p,n)	numeric(p,n)
字符型	char(n)	varchar(n)	text
二进制型	binary(n)	varbinary(n)	image
逻辑型	bit(只能取0和1，不能为空)
货币型	money(8B,4位小数)	smallmoney(4B,2位小数)
时间型	datetime(4B,从1753-01-01开始)	smalldatetime(4B,从1900-01-01开始)

其中image为存储图像的数据类型，text存放大文本数据

索引

建立索引后，系统存取数据时会自动选择合适的索引作为存取路径。索引是加快数据检索的一种工具，由系统自动维护，一个基本表可以建立多个索引，可从不同的角度加快查询速度，但如果索引建立得较多，会给数据库维护带来较大的系统开销。

索引中的记录通常由搜索码和指针构成，并按照搜索码值进行排序，但不改变基本表中记录的物理顺序。索引和基本表分别存储。

数据库中的索引一般是按照B+树结构来存储的，但也有Hash索引和二进制位索引等。

索引类型有聚集和非聚集两种。一个基本表可以建立多个非聚集索引，但只能建立一个聚集索引。聚集索引可以极大地提高查询速度，但是给搜索码属性的修改带来困难，一般建立了聚集索引的基本表很少对搜索码属性进行更新操作，仅执行查询操作。

创建索引后，与该索引相关的描述信息会保存到数据库系统表中去。

视图

视图是虚表，是从一个或多个基本表(或视图)中导出的表，在数据库系统表中仅存放了视图的定义，不存放视图对应的数据。当基本表中的数据发生变化时，从视图中查询出的数据也随之改变。

视图的主要作用是：

简化用户操作
使用户能以多种角度看待同一数据库模式
对重构数据库模式提供了一定程度的逻辑独立性
能够对数据库中的机密数据提供一定程度的安全保护
适当地利用视图可以更清晰地表达查询

存储过程

存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合，该集合编译后存放在书韩剧看中，可根据实际情况重新编译。该存储过程可在服务器端运行，也可在客户端远程调用运行。

存储过程的优点：

将业务操作封装
便于事务管理
实现一定程度上的安全性保护(对存储过程只需要授予执行权限，不需要授予表或者视图的操作权限)
特别适合统计和查询操作
减少网络通信量

触发器

触发器是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。触发器是一种特殊的存储过程，它的优点是不管什么原因造成的数据变化都能自动相应，对于每条SQL语句，触发器仅执行一次，事务可用于触发器中。

触发器常用于维护复杂的完整性约束，不用于业务处理，凡是可以用一般约束限制的，就不要用触发器，因为：

触发器是自动执行的，多个触发器必然加大系统开销
如果触发器设计得不好，会带来不可预知的后果
业务处理常常使用存储过程实现

事务

事务是具有完整逻辑意义的数据库操作序列的集合。这些操作是一个不可分割的逻辑工作单元，要么都做，要么都不做。

数据库管理系统允许多个书屋并发执行，但若不对事务的并发执行加以控制，可能会破坏数据库的一致性，主要包括：

读脏数据

T1	T2	解读
		初始有10张票
R(A)		事务T1读取，余10张
A=A-2		事务T1买2，余8张
W(A)		事务T1提交，余8张
	R(A)	事务T2读取，余8张
rollback		事务T1回滚，余10张
	A=A-3	事务T2买3，应余7张
	W(A)	事务T2提交，实余5张

不可重复读

T1	T2	解读
		初始有10张票
	R(A)	事务T2读取，余10张
R(A)		事务T1读取，余10张
A=A-2		事务T1买2，余8张
W(A)		事务T1提交，实余8张
	R(A)	事务T2读取，应余10张

丢失更新

T1	T2	解读
		初始有10张票
R(A)		事务T1读取，余10张
	R(A)	事务T2读取，余10张
	A=A-3	事务T2买3，应余7张
	W(A)	事务T2提交，实余7张
A=A-2		事务T1买2，应余5张
W(A)		事务T1提交，实余8张