数据库技术基础（笔记三）

数据库SQL语言

SQL的特点

• 综合统一
  SQL集数据定义，数据操作和数据控制功能一体，可独立完成数据库生命周期的所有活动。
• 高度非过程化
  存储路径对用户透明化
• 面向集合的操作方式
  SQL语言采用面向集合的操作方式，其操作对象、查找结果可以是元组的集合。
• 使用方式方便
  用户可以在键盘输入SQL命令对数据库操作
  把SQL语言嵌入到高级语言程序中
• 简洁易懂
  数据查询：SELECT。
  数据定义：CREATE、DROP、ALTER。
  数据操纵：DNSERT、UPDATE、DELETE.
  数据控制：GRANT、REVORK。

SQL的基本组成

（1）数据定义浙言。SOL DDL提供定义关系模式和视图、删除关系和视图、签改关系模式的命令。
（2）交互式数据操纵语言。SOL DML提供查询、插入、删除和修改的命令。
（3）事务控制（Transaction Control）。SOL提供定义事务开始和结束的命令。
（4）嵌入式SQL和动态SOL（Embeded SOL and Dymamic SQL）.用于嵌入到某种通用的高级语言（C、C叶、Java、PLA、COBOL 和VB等）中混合编程。其中，SOL.负责景纵数据库，高级语言负责控制程序流程。
（5）完整性（lntegity）。SOL DDL包括定义数据库中的数据必须满足的完整性约束条件的命令，对于破坏完整性约束条件的更新将被禁止。
（6）权限管理（Authorization）。SOL DDL中包括说明对关系和视图的访问权限。

SQL语言

1.创建表（CREATE TABLE）
语句格式：
CREATE TABLE<表名>(<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]…
[,<表级完整性约束条件>])；

CREANTE TABLE S(Sno CHAR(5) NOT NULL UNIQUE, 
Sname CHAR(30)UNIQUE, 
Status CHAR(8), 
City CHAR(20)
PRIMARY KEY(Sno));

2.修改表（ALTER TABLE）
语句格式：
ALTER TABLE<表名>[ADD<新列名><数据类型>[完整性约束条件]]
[DROP<完整性约束名>]
[MODIFY<列名><数据类型>]；

例如，向“供应商”表S增加ZAp“邮政编码”可用如下语句：

ALTER TABLE S ADD Zap CHAR（6)；

3.删除表（DROP TABLE）
语句格式：
DROP TABLE<表名>
例如:
执行

DROP TABLE Student.

此后关系Sudet不再是数据库模式的一部分，关系中的元组也无法访问。

索引

作用
（1）通过创建唯一索引，可以保证数据记录的唯一性。
（2）可以大大加快数据的检索速度。
（3）可以加速表与表之间的连接，这一点在实现数据的参服完整性方面有特别的意义，
（A）在使用ORDER BY和GROUP BY子句中进行检素数据时，可以显著减少查询中分组和排序的时间。
（5）使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

建立索引：
语句格式：
语句格式：CREATE [UNIQUE] [CLUSTER] INDEX <素引名>
ON<表名>（<列名>[<次序>] [,<列名>[<次序>]]……）；
参数说明如下。
次序：可选ASC（升序）或DSC（降序），默认值为ASC.
UNIQUE：表明此索引的每一个索引值只对应难一的数据记录。
CL0STER:表明要建立的索引是聚获索引，意为索引项的顺序是与表中记录的物理顺序一致的索引组织。

例题
假设供应销售数据库中有供应商S、零件P、工程项目J、供销情况SPJ关系。希望建立4个素引。其中，供应商S中Sno按升序建立索引；零件P中Pno按升序建立索引；工程项目中J加Jno升序建立索引；供销情况SPJ中Sno按升序、Pno按降序、Jno按升序建立索引。
解：

CREATE UNIQUE INDEX S-SNO ON S（Sno）;
CREATE UNIQUE INDEX P-PNO ON P（Pno）;
CREATE UNIQUE INDEX J-JNO ON JJno）;
CREATE UNIQUE INDEX SPJ-NO ON SPJ(Sno ASC，Pno DESC，JNO ASC）;

删除索引
语句格式：DROP INDEX<索引名>
高如执行 DROP INDEX xxxxxx，此后素引xxxxxx不再是数据库模式的一部分。

视图

作用
（1）可以使用视图集中数据、简化和定制不同用户对数据库的不同数据要求。
（2）使用视留可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。
（3）视图可以使用户只关心他感兴趣的某些待定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。
（4）视图大大地简化了用户对数据的操作。
（5）视图可以让不同的用户以不同的方式看到不同或者相同的数据集。
（6）在某些情况下，由于表中数据量太大，因此在设计表时常将表进行水平或者垂直分制，以免表的结构的变化对应用程序产生不良的影响。
（7）视图提供了一个简单而有效的安全机制。

建立视图

语句格式：
CREATE VIEW 视图名（列表名）
AS SELECT查询子句
[WITH CHECK OPTION]；

规则如下：
（1）子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。
（2）WITH CHECK OPTION 表示对UPDATE、INSERT、DELETE操作时保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
（3）组成视图的属性列名或者全部省略或者全部指定。如果省略副性列名，则隐含该视图由SELECT子查询目标列的主属性组成。

例题：
若学生关系模式为Student（Snmo，Sname，Sage，Sex，SD.Email，Tel），建立“计算机系”（CS表示计算机系）学生的视图，并要求进行修改、插入操作时保证该视图中只有计算机系的学生。
CREATE VIEW CS_STUDENT
AS SELECT Sno. Sname, Sage, Sex
FROM Student WHERE SD=‘CS’
WITH CHECK OPTION;

视图的删除
语句格式：DROP VIEW视图名
例如，DROP VIEW CS_STUDENT 将删除视图CS_STUDENT。

SQL数据查询

SELECT基本结构
语句格式：
SELECTE[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASCIDESC]……]

SOL.查询中的子句顺序为SELECT、FROM、WHERE、GROUP BY、HAVING 和ORDER BY.其中，SELECT、FROM是必需的，HAVING条件子句只能与GROUPBY搭配起来使用。

SELECT子句对应的是关系代数中的投影运算，用来列出查询结果中的属性。其输出可以是列名、表达式、集函数（AVG、COUNT、MAX、MIN、SUM），DISTINCT选项可以保证查询的结果集中不存在重复元组。

简单查询
SELECT [表的属性名]
FROM [表名]
WHERE [某个属性名（列名）]=[具体的名称];

连接查询

SELECT [表的属性名]
FROM [表名1],[表名2]
WHERE [表名1.[某个属性名（列名）]] AND [表名2.[某个属性名（列名）]=[具体的名称];

子查询与聚积函数

子查询也称嵌套查询。嵌套查询是指一个SELECT-FROM-WHERE查被块可以嵌入入另一个查询块之中。在SQL中允许多重嵌套。

例子

		SELECT Sno,Sname
		FROM S
		WHERE  Sno= IN(
			SELECT Sno 
			FROM SC 
			WHERE Cno IN(
				SELECT Cno 
				FROM C 
				WHERE Cname=MS))

聚积函数是一个值的集合为输入，返回单个值的函数。SQL提供了5个预定义聚积函数。
平均值AVG、最小值MIN、最大值MAX、求和SUM以及计数COUNT

分组查询

GROUP BY子句
在WHERE子句后面加上GROUPBY子句可以对元组进行分组，保留字GROUPBY后面跟着一个分组属性列表。最简单的情况是FROM子句后面只有一个关系，根据分组照性对它的元组进行分组。SELECT子句中使用的聚集操作符仅用在每个分组上。

HAVING子句
假如元组在分组前按照某种方式加上限制，使得不需要的分组为空，在GROUP BY子句后面跟一个HAVING子句即可。
注意：当元组含有空值时，应该记住以下两点。
（1）空值在任何聚集操作中被忽视。它对求和、求平均值和计数都没有影响。它也不能是来列的最大位或最小值。例如，COUNT（）是某个关系中所有元组装目之和，但COUNTCA0海是A属性非空的元组个数之和。
（2）NULL值又可以在分细属性中看作是一个一般的值。例如，在SELECTA.ANCB）FORMR中，当A的属性值为空时，就金统计A-NULL的所有元组中B的均值。

更名
SQL提供了可为关系和属性重新命名的机制，这是通过使用具有如下形式的AS子句来实现的：
Old-name AS new-name
AS子句既可出现在SELECT子句，也可出现在FROM子句中

字符串
对于字符串进行的最常用的操作是使用操作符LHKE的模式匹配。使用两个特殊的字符来描述模式：“%”匹配任意字符串：“”匹配任意一个字符。模式是大小写敏感的。
例如：
Mary%配任何以Mary开头的字符串;
%idge%匹配任何包含idge的字符串;
“”匹配只含两个字符的字符串；
“_%”匹配至少包含两个字符的字符串。

视图的查询

例子
建立“计算机系”（CS表示计算机系）学生的视围，并要求进行修改、插入操作时保证该视图只有计算机系的学生。
CREATE VIEW CS_STUDENT
AS SELECT Sno，Sname，Sage，Sex
FROM Student
WHERE SD=‘CS’
WITH CHECK OPTION；
此时查询计算机系年龄小于20岁的学生的学号及年龄的SOL语句如下：
SELECT Sno，Age FORM CS_STUDENT WHERE SD-CS AND Age<20;

SQL数据库更新

插入

INSERT INTO基本表名（字段名[，字段名…）
VALUES（常量[，常量]…）；查询语句

INSERT INTO基本表名（列表名）
SELECT查询语句

删除
DELETE FROM 基本表名
[WHERE条件表达式]

修改
UPDATE 基本表名
SET列名一值表达式（列名一值表达式…）
[WHERE条件表达式]

SQL访问设置

功能

• 通过GRANT和REVOKE将授权通知系统，并存入数据字典。
• 用户提出诗求时，根据授权情况检查是否执行操作请求。

授权的语句格式：
GRANT<权限>[,<权限>]……[ON对象类型<对象名>TO<用户>[,<用户>]……
[WITH GRANT OPTION]；

收回权限语句格式
REVOKE<权限>[,<权限>]……[ON<对象类型><对象名>]
FROM<用户>[,<用户]……；

例题
如果用户要求给或者收回数据库SPJ中的供应商S、零件P，项目J表赋各种权限。

1.将对供应商S、零件P、项目J的所有操作权限赋给用户User1 及 User2，其授权表示如下：
GRANT ALL PRIVILEGES ON TABLE S,P,J TO User1，User2;
2.将用户User1 及User2对供应商S、零件P、项目J的所有操作权限收回。
REVOKE ALL PRIVILEGES ON TABLE S,P,J
FROM User1，User2;

关系数据库的规范化

1NF（第一范式）
定义：
若关系模式R的每一个分量是不可再分的数据项，则关系模式R属于第一范式。

例如，
供应者和它所提供的零件信息，关系模式FIRST和函数依赖集F如下：
FIRST（Sno，Sname，.Status.City，.Pno，Qty）
F={Sno→Same，Sno→status.Status→City,（Sno,Pno)→Qty｝

2NF（第二范式）
定义：
若关系模式R∈1NF，且每一个非主属性完全依赖于码，则关系模式R∈2NF。
换句话说，当INF消除了非主属性对码的部分函数依赖，则称为2NF。

例如：
FIRST关系中的码是Sno、Po，而Sno→Satus，因此非主属性Status部分函数依赖于码，故非2NF的。

3NF（第三范式）
定义：
若关系模式R(U,F）中若不存在这样的码X，属性组Y及非主属性Z(Z(不属于)Y)使得X→Y(Y推导出X）,Y→Z成立，则关系模式R∈3NF。
即当2NF消除了非主属性对码的传递函数依赖，则称为3NF。

例如：
FIRST13NF，因为在分解后的关系模式FIRSTI中有Sno-Saus，Saus–City，存在着非主属性City传递依赖于码Sno。若此时将FIRST1继续分解为：
FIRST11（Sno，Sname，Status）∈3NF
FIRST12（Status，City）∈3NF

数据库的控制功能

事务管理

事务定义的语句有以下三条。
（1）BEGIN TRANSACTION：事务开始。
（2）COMMIT：事务提交。该操作表示事务成功地结束，它将通知事务管理器该事务的所有更新操作现在可以被提交或永久地保留。
（3）ROLLBACK：事务回滚。该操作表示事务非成功地结束，它将通知事务管理器出故障了，数据库可能处于不一致状态，该事务的所有更新操作必须回滚或撤销。

事务具有原子性（Atomicity）、一致性（Consisency）、隔离性（lsolation）和持久性（Durability）。这4个特性也称事务的ACID性质。

1. 原子性。事务是原子的，要么都做，要么都不做。
2. 一致性。事务执行的结果必须保证数据库从一个一致性状态变到另一个一致性状态。因此，当数据库只包含成功事务提交的结果时，称数据库处于一致性状态。
3. 隔离性。事务相互隔高。当多个事务并发执行时，任一事务的更新操作直到其成功提交的整个过程，对其他事务都是不可见的。
4. 持久性。一旦事务成功提交，即使数据库崩渍，其对数据库的更新操作也将永久有效。

数据库备份和恢复

故障类型

数据库中的4类故障是事务内部故障、系统故障、介质故障及计算机病毒。
事务内部故障。事务内部的故障有的可以通过事务程序本身发现。例如，银行转账事务，将账户A的金额转X到账户B，此时应该将账户入的余额心，将账户B的余额.如果账户A的余额不足，那么，两个事务都不做，否则都做。但有些是非预期的，不能由事务程界处理，例如运算溢出、并发事务发生死锁等。

系统故障。通常称为软故障，是指造成系统停止运行的任何事件，使得系统要重新启动，例如CPU故障、操作系统故师和突然停电等。

介质故障。通常称为硬故障，如磁盘损坏、磁头碰撞和瞬时蛋磁干扰。此类故障发生的几率小，但破坏性最大。

计算机病毒。计算机病毒是一种人为的故障和破坏，是在计算机程序中插入的破坏，计算机功能或者数据可以繁殖和传播的一组计算机指令或程序代码。

备份方法

恢复的基本原理是“建立数据沉余”（重复存储）。建立冗余数据的方法是进行数据转信和登记日志文件。数据的转储分为静态转储和动态转储、海量转储和增量转德、日志文件。

• 静态转储和动态转储。静态转储是指在转储期间不允许对数据降进行任何存取、修改操作，动态转做是在转做期间允许对规据你进行存限、街改操件。因此，转做和用户事务可并发执行。
• 海量转做和增量转储。海足转储是指每次转储全部数据：增量转储是指每次只特储上次转储后更新过的数据。
• 日志文件，在事务处理的过程中，DBMS把事务开始、事务结束以及对数据库的插入、删除和修改的每一次操作写入日志文件。一旦发生故障，DBMS的恢复字系统利用日志文件城筋事务对数据库的改变，回退到事务的初始状态。因此，DBMS利用日志文件案进行事务故障恢复和系统故障恢复，并可协助后备副本进行介质故障恢复。

恢复
事务恢复有以下3个步骤。

1. 反向扫描文件日态（即从最后向前扫描日态文件），查找该事务的更新操作。
2. 对事务的更新操作执行逆操作。
3. 继婴及物扫品日志文件，查找该事务的其他更新操作，并做同样的处理，直到事务的开始标志。

数据库镜像

为了避免磁盘介质出现故障影响数据库的可用性，许多DBMS提供数据库镜像功能见工数据库候复。需要说明的是，数据降镜像是通过复制数据实现的，但频案地复制数据会降低系统的运行效率。因此，在实际应用中往往对关键的数据和日志文件镜像。

并发控制

并发控制技术
并发控制的主要技术是封做。基本封微的类型有排它锁（简称X锁或写锁）和共享锁（简称S锁或读锁）。

封锁

1. 排它锁，若事务了对数据对象A加上X镜，则只允许了读取和修改A，其他事务都不能再对A加任何类型的锁，直到T释放A上的锁。
2. 共享锁，若事务了对数据对象A加上S，则只允许T读取A，但不能修改A，其他事务只能再对A加S锁，直到了释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A进行任何修改。

三级封锁协议

1. 一级封锁协议，事务在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结来包括正常结束（COMMIT）和非正常结家（ROLBACK），一级封领协议可以解决丢 失更新问题。
2. 二级封锁协议。在一级封健协议的基础上，加上事务工在读教器取之前色须无对其加5锁，接完后即可释放S锁。二级封领协议可以解决读难数据的同题。但是，由于二级封锁协议读完了数据后即可释放S锁，所以不能保证可重复读。
3. 三级封锁协议。在一级封锁协议基础上，加上事务T在读数据民R之前必需先对其加S锁，直到事务来时释放S锁，三级封锁协议除了防止丢失修改和不读“脏”数据外，还进一步防止了不可重复读。

活锁与死锁
活锁，是指当事务T1封锁了数据R时，事务T2求封锁数剧R，于是T2等待，当T，释放R上的封锁后，系统首先批准了T3请求，于是T2下仍等待，当T3释放了R上的封锁后，又批准了T4清求，依此类推，使得T2可能永远等待的现象。
死锁，是指两个以上的事务分别请求封锁对方已经封锁的数据，导致了长期等待无发继续运行下去的现象。

并发调整的可串行性
多个事务的并发执行是正确的，当且仅当其结果与某一次序串行地执行它们时的结果相同，称这种调度策略是可串行化的调度。
可串行性是升发事务正确性的准则，按这个准则规定，一个给定的并发调度，当且仅当它是可串行化的才认为是正确调度。

两段封锁协议
两段封领协议，是指所有事务必须分两个阶段对数据项加锁和解锁。即事务分两个除段。
第一阶段是获得封锁，非务可以获得任的数据项上的任何类型的镜，但不能释放；
第二阶设是释放封锁，事务可以释放任何数据项上的任何类型的锁，但不能申请。

封锁的粒度
封做对象的大小称为封领的粒度。封顿的对象可以是逻辅单元（如属性、元组、关系、索引项、整个案引甚至整个数据库），也可以是物理单元（如数据页或索引页）。