Sql Server期末复习

基础概念

数据库管理系统：是位于用户与操作系统之间的一层数据管理软件。

数据库系统：由数据库、数据库管理系统、应用程序和数据库管理员组成

​ 即 DBS=DB+DBMS + 应用程序 + DBA

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

• 模式：也成逻辑模式，是数据库中全体数据的逻辑结构和特征的描述。
• 外模式：子模式/用户模式，一个数据库可以有多个外模式，数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述。
• 内模式：存储模式,一个数据库只有一个内模式，是数据物理结构和存储方式的描述。

二级映像功能：外模式/模式映像 和 模式/内模式映像

• 外模式/模式映像 ：模式改变时由数据库管理员对各个外模式/模式的映像进行改变，从而保证外模式保持不变，应用程序是依据外模式编写的，从而保持应用程序保持不变。保证了数据和程序的逻辑独立性。
• 模式/内模式映像：数据库的存储结构发生改变时，由数据库管理员对模式/内模式的映像作改变，可以使模式保持不变，从而应用程序也不必发生改变。保证了数据和应用程序的物理独立性。

域： 是一组具有相同数据类型的值得集合。

候选码： 某一属性的值能唯一标识一个元组，其子集不能。

主码： 在多个候选码中选定一个作为主码。

主属性： 候选码的属性叫作主属性。非主属性就是不是候选码的属性也叫非码属性。

全码： 关系模式中所有属性都是候选码，都是主属性。

关系代数

• 传统的关系代数： 并、交、差、笛卡尔积
• 专门的关系代数： 选择、投影、连接、除

SQL功能	动词
数据查询	SELECT
数据定义	CREATE、DROP、ALTER
数据操纵	INSERT、UPDATE、DELETE
数据控制	GRANT、REMOVE

创建模式：
CREATE SCHEMA 模式名 AUTHORIZATION 用户名 模式下可以定义表等

删除模式：
DROP SCHEMA 模式名 CASCADE|RESTRICT

创建表：
CREATE TABLE 表名（列名 数据类型 列级完整性约束，…）

修改表：ALTER TABLE 表名

​ ADD 新列名 数据类型 约束条件，

		  ADD 表级完整性约束，	

​ DROP 列名 CASCADE|RESTRICT

​ ALTER COLUMN 列名 数据类型 ，修改列属性

删除表: DROP TABLE 表名 CASCADE|RESTRICT 默认RESTRICT

建立索引： CREATE UNIQUE|CLUSTER INDEX 索引名

• UNIQUE: 每一个索引值对应一个唯一的数据记录
• CLUSTER：聚簇索引。

修改索引ALTER INDEX 旧索引名 RENAME TO 新索引名

删除索引： DROP INDEX 索引名

数据查询

SELECT <ALL|DISTINCT> 目标列 FROM 表名 DISTINCT去重，

SELECT * FROM 表名 ；查询全部表的全部列信息

查询满足条件的元组：用到where子句

SELECT * FROM 表名 WHERE 查询条件

查询条件	谓词
比较	=,>,<,>=,<=,!=,!>,!<
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN , NOT IN
字符匹配	LIKE, NOT LIKE
空值（*）	NULL ,IS NOT NULL 不用==0
多重条件（逻辑运算）	AND ，OR ， NOT

如：查询年龄不大于20的学生姓名：

SELECT * FROM STUDENTS WHERE age<=20;

集合的使用：IN 查询 年龄在18 19 20的学生信息

select * from students where age IN (18,19,20);

字符匹配：LIKE

查询姓刘的学生信息

select * from students where name LIKE “刘%”

查询姓李并且名字为两位的学生信息

select * from students where name LIKE “李_”

(如果数据库为ASCII编码需要两个_，GBK编码一个 _)

查询第二个字为阳的学生信息

select * from students LIKE “_阳%”

空值： NULL

查询成绩为0的学生信息

select * from students where grade IS NULL;

AND 表并且 OR 表或者 可以用来连接多个条件

排序 : ORDER BY

按照某列属性的值对数据进行排序

默认为升序，DESC为降序 ASC为升序

如：学生信息按照成绩降序

select name from students ORDER BY grade DESC;

order by 与where 和group by 之间可以联合使用

select * from 表名 where 条件 group by 列名 order by 列名

聚集函数

聚集函数只能用于select 子句和GROUP BY HAVING子句

函数	作用
COUNT(*)	统计元组个数
COUNT（DISTINCT |ALL） <列名>	统计一列中值得个数
SUM … <列名>	计算一列值的总和
AVG … <列名>	计算一列值的平均值
MAX … <列名>	计算一列最大值
MIN … <列名>	计算一列值的最小值

使用DISTINCT表示在计算时取消重复值，重复值不重复计算

分组 ：GROUP BY

将查询到的结果按照一列或多列的值分组，值相等为一组。

查询课程号及选课人数

select Cno,COUNT(*) from SC group by Cno;

嵌套查询：在查询中不能增加order by语句

EXISTS：带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值true和逻辑假值false

查询所有选修了1号课程的学生姓名

select name from students where EXISTS (select * from SC where Sno=students.Sno AND Cno=“1”);

集合查询：

集合操作主要包括并操作UNION、交操作INTERSECT、差操作EXCEPT

数据更新

数据更新包括数据的插入、修改、删除。

数据插入

数据的插入通常有两种形式，一种是插入元组，另一种是插入子查询结果。

INSERT INTO <表名> [插入的属性列] VALUES <插入对应属性列数值>；不写属性列默认为全部属性

INSERT INTO <表名>[属性列] 子查询

将student表中的年龄大于18的插入到students表中

如：insert into Students(name,age) select name,age from student where age>18;

数据修改

UPDATE 表名 SET <列名>=<表达式值> [WHERE <条件>]

修改一个元组的值：

将姓名为张三的学生年龄改为20

update students set age=20 where name=“张三”

修改多个元组的值：

如：将所有学生的年龄加1

update students set age = age+1

通过子查询进行数据的修改：

如:将男生的成绩全部置0

update students set grade=0 where Sno IN (select Sno from students where sex = “男” );

数据删除

DELETE FROM <表名> WHERE <条件>

删除一个元组的值

DELETE FROM <表名> WHERE <条件>

删除多个元组的值

DELETE FROM <表名> 删除表中的所有数据，清空表

带子查询的删除语句

视图操作

视图

• 可以用来简化用户的操作
• 让用户以多种角度看待同一数据
• 对重构数据库提供了一些逻辑独立性
• 能够对机密数据提供安全保护
• 更清晰的表达查询

创建视图：

CREATE VIEW <视图名> [<列名>] AS <子查询> [WITH CHECK OPTION]

子查询可以是任意的SELECT语句

WITH CHECK OPTION 表示对视图进行更新操作的行满足满足视图定义的谓语条件（子查询的条件表达式）

删除视图：

DROP VIEW <视图名> [CASCADE]

查询视图Student(和表的操作类似)

SELECT * FROM Student WHERE age>18

视图的更新和表的更新类似，增加、修改、删除，语法也相同 注：并不是所有的视图都是可以更新的

数据库安全性控制

关系数据库的存取权限表

对象	操作类型
模式	CREATE SCHEMA
基本表	CREATE TABLE , ALTER TABLE
视图	CREATE VIEW
索引	CREATE INDEX
基本表和视图	SELECT , INSERT INTO , UPDATE ,DELETE ,REFERENCES , ALL PRIVILEGES
属性列	SELECT , INSERT , UPDATE , REFERENCES , ALL PRIVILEGES(全部属性)

授权

授予与收回 GRANT <权限> ON <对象类型> TO <用户> [WITH GRANT OPTION]

WITH GRANT OPTION ：传播授权的权利，可以对其他用户授权

例：将查询表Stu的权限授予用户u1

grant select on table Stu to u1

REVOKE 回收 REVOKE <权限> ON <对象类型> FROM <用户>[CASCADE|RESTRICT]

例：回收u1查询Stu表的权限

revoke select on table Stu from u1 (from public 则对所有用户回收权限)

角色

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合

创建角色 CREATE ROLE <角色名>

给角色授权 GRANT <权限> ON <对象类型> TO ROLE

将一个角色授予其他角色或用户 GRANT <角色1>… TO <角色3> [WITH ADMIN OPTION]

角色权限的收回 REVOKE <权限> ON <对象类型> FROM <角色>

数据库完整性

实体完整性

• PRIMARY KEY

主码自动建立索引

• 检查主码是否唯一
• 主码的各个属性是否为空

参照完整性

• FOREIGN KEY REFERENCES

用户定义的完整性

属性约束

• NOT NULL 不为空
• UNIQUE 列值唯一
• CHECK 是否满足条件表达式

完整性约束子句 CONSTRAINT

CONSTRAINT <完整性约束条件名><完整性约束条件>

完整性约束条件：UNIQUE , NOT NULL , PRIMARY KEY , FOREIGN KEY ,CHECK …

例：sname char（20）CONSTRAINT C1 NOT NULL ；创建表，表的列属性的完整性约束

以上的完整性都是建立表时进行使用。

删除完整性 DROP CONSTRAINT C4

增加完整性 ADD CONSTRAINT ….

断言

断言创建后，任何对断言所涉及的操作都会触发断言，任何使断言不为真值的操作都会被拒绝执行

创建断言 CREATE ASSERTION <断言名> <CHECK 子句>

例:限制数据库课程最多60个人选修

CREATE ASSERTION ASS_SC CHECK (60 >= (SELECT COUNT(*) FROM Course,SC WHERE SC.Cno=Course.Cno AND Cname=“数据库” ))

删除断言 DROP ASSERTION <断言名>

触发器 用户定义在关系表上的一类由事件驱动的特殊过程,一个数据表可以定义多个触发器

创建触发器 CREATE TRIGGER

CREATE TRIGGER <触发器名> ；触发事件发生，触发器激活

{BEFORE|AFTER} <触发事件> ON <表名> ；指明触发时间是在执行前还是执行后

REFERENCING NEW|OLD ROW AS <变量> ；REFERENCING 指出引用变量

FOR EACH {ROW|STATEMENT} ；触发器类型 ROW为行级触发器 ，STATEMENT语句级

[WHEN <触发条件>]<触发动作题> ；触发条件为真时才执行触发动作体

例：定义一个before行级触发器，为教师表teacher定义完整性约束“教授规则不低于4000，低于4000则自动改为4000”

create trigger 触发器1 before insert or update ON teacher

REFERENCING NEW row AS newtupl

FOR EACH ROW BEGIN

	IF(newtupl,job = “教授”) AND  (newtupl.sal < 4000)

​ THEN newtupl.sal = 4000;

​ END IF

END

规范化

1NF

↓ 消除非主码属性对码的部分函数依赖

2NF

↓ 消除非主属性对码的传递函数依赖

3NF

↓ 消除主属性对码的部分和传递函数依赖

BCNF

↓ 消除非平凡且非函数依赖的多值依赖

4NF

数据库设计方法

数据库设计基本步骤：

需求分析阶段 – 概念结构设计阶段 – 逻辑结构设计 – 物理结构设计 – 数据库实施 – 运行维护

需求分析阶段 数据字典：数据字典包含数据项、数据结构、数据流、数据存储和处理过程几部分。

概念结构设计：

E-R模型 （实体、属性、实体之间的联系 一对一，一对多，多对多）

E-R图 实体用矩形，属性用椭圆形，联系用菱形

逻辑结构设计：逻辑结构设计的任务就是把概念结构设计阶段设计好的基本E-R图转换为与数据库管理系统产品所支持的数据模型相符合的逻辑结构。

数据库恢复

事务 用户定义的数据库操作序列，这些操作要么全做，要么不做，是不可分割的工作单位

定义事务的语句

• BEGIN TRANSACTION
• COMMIT 表示提交，提交事务操作
• ROLLBACK 回滚，对已完成的操作全部撤销，回滚到事务开始状态

事务的ACID属性

• 原子性
• 一致性
• 隔离性
• 持续性

故障的种类

• 事务内部的故障 非预期的
• 系统故障 软故障，是指造成系统停止运行的任何事件，使得系统重新启动
• 介质故障 硬故障
• 计算机病毒

恢复的技术

• 数据转储
• 登记日志文件