数据库原理考点复习

 1.1
 数据是数据库中存储的基本对象。
 描述事物的符号记录称为数据。
 数据与其语义是不可分的。
 数据库是长期存储在计算机内的大量的，有组织的，可共享的数据集合。
 数据库中的数据按一定的数据模型组织，描述，存储，有较小的冗余度，较高的数据独立性，易拓展性和并且可以为各种用户共享。
 数据库的数据有永久存储， 有组织和可共享 三个基本特点。
 数据库管理系统是数据库系统的核心。
 数据库管理系统是位于用户和操作系统之间的数据管理软件（用户之上，操作系统之下，是一个 系统 软件）。
 DBMS通常提供数据定义（主要功能）， 数据操纵，数据控制和 数据库维护 。
 数据库管理系统提供的 数据定义功能，包括创建和删除表，视图和索引，可以对表进行修改。
 数据控制功能 包括数据库的恢复，数据库的并发控制，数据库的完整性，数据库安全性。
 数据库管理的三阶段：人工管理，文件系统，数据库系统。
 数据库系统阶段的特点：1共享性高，冗余度小2高度的物理独立性和一定的逻辑独立性3 整体结构化，用数据模型描述 4由数据库管理系统提供的数据安全性，完整性，并发控制和恢复能力。

 1.2数据模型
 数据模型是对现实世界的抽象，是真实的，容易理解的，好在计算机上实现
 模型分为1概念模型2逻辑模型和物理模型
 1现实世界（用户）到概念模型，概念模型到逻辑模型由数据库设计人员完成2由逻辑到物理由 数据库管理系统 完成。
 数据独立性可分为逻辑独立性和物理独立性。
 数据模型 由 数据结构，数据操作， 完整性约束条件 三部分构成。
 数据结构是描述数据库的组成对象与对象之间的联系。
 常用数据模型有层次模型，网状模型，关系模型和面向对象模型。
 层次模型（树）是只能直接处理一对多的实体联系，结点双亲唯一。
 网状模型（图）多对多。
 关系模型是由美国IBM公司的E.F.Codd首次提出的。
 关系模型就是用关系表表示实体之间联系的模型。
 关系模型的 数据结构 是一张规范的二维表。
 表中有表就不是关系。
 关系模式（表头）对关系的描述表示为   关系名（属性1，属性2，……）
 分量是一条记录（一行）的一个列值。
 关系模型的 数据操纵 有增删改查。
 关系模型的数据操作是1集合操作，操作的对象和结果都是关系，是若干元组的集合（可以一次操作多行，非关系模型一次一行）。2关系模型把存取路径向用户隐蔽起来，用户只要指出“干什么找什么”，不用细说“怎么干怎么找”，提高了数据独立性和用户生产率。
 关系的完整性约束条件有实体完整性，参照完整性，用户定义的完整性。
 关系模型优点1是建立在严格的数学基础上的，2概念单一，3存取路径对用户透明，有更高的数据独立性和安全保密性。
 若采用关系数据库来实现应用， 在数据库设计的 逻辑设计 阶段，要把E-R模型转换成关系数据模型。

 1.3数据库系统的结构
 模式是对数据库中全体逻辑结构和特征的描述。
 模式的一个具体值称为一个实例，同一个模式可以有很多实例。
 模式是相对稳定的，实例是相对变动的。
 数据库系统通常采用三级模式结构，分别是外模式 （逻辑模式） ，模式和内模式 （存储模式） 。
 一个数据库模式和内模式都只有一个，外模式可以多个。
 外模式又称为 子模式，是模式的（逻辑）子集 ，不是副本。
 DBMS提供模式定义语言（模式DDL）定义模式，外模式数据定义语言（外模式DDL）来定义外模式。
 书28页
 二级映像是1外模式/模式映像（便于用户观察使用）2模式/内模式映像（便于计算机存储和处理数据）
 二级映像保证了数据库系统的数据有较高的逻辑独立性和物理独立性。

 2.1关系数据结构和形式化定义
 域是一组具有相同数据类型的值的集合。
 几表笛卡尔积的基数（元组）是几个表的属性个数相乘。
 某一属性组的值能唯一标识一个元组，而其子集不能，则称该属性组为 候选码 。
 主码一定是候选码。
 候选码的各个属性称主属性。
 不包含在任何候选码中的属性称非主属性。
 关系模式中所有码都是候选码称全码。
 关系模式有三种  基本关系（基本表，实际存在的表），查询表（虚表）和视图表（虚表）
 基本关系的6条性质  1每一列的分量来自一个域，是同一数据类型
2不同列可以出自一个域，称每一列为一个属性，不同的属性属性名不同
3列的顺序无所谓
4任意两个元组的候选码不能一样（不能有两个相同元组）
5行的顺序无所谓
6（最基本的）分量必须为原子值，每一个分量都是不可分的数据项（表中不能有表）
 主码唯一标识一个元组，主码相等表示这是同一个元组。
 关系的描述称关系模式    R(U,D,DOM,F)
 R是关系名，U是组成该关系的属性名集合，D是U中所有属性所来自的域，DOM为属性向域的映像（属性的类型，长度），F为属性间数据的依赖关系集合。
 关系模式是静态的，关系是动态的。

 2.2关系操作
 关系操作是集合操作方式，操作的对象和结果都是集合。
 SQL的功能  查询功能，数据定义功能和数据控制功能 ，集查询，数据定义语言， 数据操纵 语言，数据控制语言于一体。
 SQL是结构化查询语言。
 SQL语言的四大功能：数据查询功能，数据定义功能，数据操纵功能， 数据控制功能 。

 2.3关系的完整性
 数据模型中有三类完整性约束 实体完整性，参照完整性和用户定义的完整性，前两者是关系模型必须满足的完整性约束条件称关系的两个不变性。
 实体完整性 主属性（主码）不能为空。
 参照完整性 外码或者取空值（该属性下的所有值为空），或者填引用的表的主码的值。

 2.4关系代数（3'）
 几目关系就有几个属性。
 分别为n目和m目的关系R和关系S的笛卡尔积是一个（n+m）列的元组的集合，若R有k1个元组，S有k2个元组，则关系R和关系S的笛卡尔积有(k1*k2)个元组。（属性相加，元组相乘）
 书49页
 传统关系运算  并  交  差  笛卡儿积
 专门的运算关系   选择  投影  连接  除运算
 基本的运算是 并  差  选择  投影  笛卡儿积
 优先级       算术运算符>逻辑运算符>关系运算符
 看到“全部”，“至少”用÷
 R÷S除运算：1求R除去相同属性的属性组的投影
2求上述属性组中属性的象集
3求S在相同属性上的投影
4对比得结果
 两个表之间要有公共属性才能连接。
 三表连接时有另外两个表公共属性的要放中间。

 3.1SQL概述
  SQL功能                                           动词
         数据查询                              select
         数据定义                              create,alter,drop(表)
         数据操纵                              insert,update,delete(表里的数据)
         数据控制                              grant,revoke
 SQL是结构化的，非过程化的。

 3.3数据定义
 定义基本表
 主码可以用列级或表级完整性约束条件
create table S
(Sno varchar(8) primary key,
Sname varchar(8) unique,         -------->每一行这一列的值都不同
Sage  smallint);                           -------->最后一行不要逗号
 不取空值
create table C
(Cno varchar(8) primary key,
Cname varchar(8) not null        --------->Cname不取空值
);
 有两个属性以上的主码和 外码必须用表级完整性约束条件
create table SC
(Sno varchar(8),
Cno varchar(8),
grade smallint,
primary key(Sno,Cno),
foreign key (Sno) references S(Sno),
foreign key (Cno) references C(Cno)
);
 数据类型
1char（n）是定长为n的字符，varchar（n）是最长为n的字符
2numeric(p,d)是由p位数字组成，小数点后有d位小数
3date类型       YYYY-MM-DD
4time类型       HH:MM:SS
 修改表
 向S表中加一列“入学时间”，数据类型为日期型
alter table S add 入学时间 date;
 将S表Sage的数据类型改为整数
alter table S alter column Sage int;
 在C表增加课程名取值必须唯一的约束条件
alter table C add unique(Cname);
 删除表
 drop table S  cascade ;       -------->会把用该表产生的视图，索引，触发器也一起删掉
 删除表时，与该表相关的规则和约束 会被删除 。
 索引能快速定位到需要查询的内容，用户可以在一个基本表上建立一个或多个索引。
 表中记录的物理顺序和逻辑顺序完全相同的索引是聚集索引。
 建立索引  p88
create unique index Studio on SC(Sno asc,Cno desc);
------>在SC表建立按Sno升序，Cno降序的唯一索引
 修改索引
alter index SCno rename to SCsno;            ---->把SC表的索引名SCno改为SCsno
 删除索引
drop index <索引名>;
 3.4数据查询
 确定范围
查询年龄不在18～20（包括18，20）之间的姓名和年龄
select Sname,Sage
from S
where Sage not between 18 and 20;
 谓词in可以用来找 属性值 在指定集合内的元组
查CS系和MA系的学生姓名和年龄
select Sname,Sage
from S
where Sdpet in('CS','MA');
 字符匹配(=等价于like)
查不姓王不姓李的学生信息
select *
from S
where Sname not like'王%' and
            Sname not like'李%' ;

转义
查课程名为C_DB的课程号
select Cno
from C
where Cname like'C_DB'    escape '';
 多重查询（and的比较级高于or，可以用括号改变比较级）
 排序 order by
select  *
from S
order by Sdpet,Sage desc;
----->先按Sdpet的升序排，若一样再按Sage的降序排
 group by
         group by后面填因为什么把这组数分组合并
        有条件的分组查询要用having子句
 聚集函数(不出现在where子句)
聚集函数用在select子句和group by的having子句
查平均成绩大于90的学生学号和平均成绩
select Sno,avg(grade)
from SC
group by Sno
having avg(grade)>90;
 连接查询（ 两张表有相同属性才能连接 ）
外连接
select *
from S left outer join SC on(S.sno=SC.sno);
左连接保留左边关系的所有元组
 嵌套查询（从最里层写起）
相关子查询
e.g.查每个学生超过选修课程平均分的课程号
select Sno,Cno
from SC x
where grade>(select avg(grade)
                          from SC y
                          where x.Sno=y.Sno);  
 exists/not exists
带有exists谓词的子查询查到一条记录就返回true
带有exists谓词的子查询通常select *

带有not exists谓词的子查询是扫描全部，找一条记录就返回false，一条记录都没有就返回true
not 
1查没有选修1号课程的学生姓名
select Sname
from S
where not exists(select * from SC 
                               where Sno=S.Sno and Cno='1');
2查选修了全部课程的学生姓名
select Sname
from S
where not exists(select * from C ---->一次从全部课程取出一条和和内层比较
                              where not exists (select * from SC---->一个学生
                                                             where Sno=S.Sno and
                                                                         Cno=C.Cno));
3查至少选修了学生2612选修的全部课程的学号
（看学生z有没有选学生y选的课）
select distinct Sno
from SC x
where not exists(select * from SC y  ----->取出2612的一条选课记录
                              where y.Sno='2612' and
                                         not exists(select * from SC z------>要去比对的人
                                                           where z.Sno=x.Sno and
                                                                       z.Cno=y.Cno));
从第一个not exists里取一行与第二个not exists里的一行做比较，如果一样内层not exists里的记录为false，外层not exists查询为空返回true，内层not exists一般要把所有表连接起来。
 3.5数据更新
 插入元组
insert
into <表名>
values(……);
1插入元组到SC表
insert
into SC(Sno,Cno,grade)                 ------->写了属性名对应赋值，没写的赋默认值
values('2261','3',88);

insert
into SC                       ------->不写属性名默认按顺序插入
values('2261','3',88);
 把查询结果插入一个新表
select *
into 新表
from 旧表;
或
create table 新表名;
insert
into 新表名（属性1，属性2，…)
select (属性1，属性2，…)
from 旧表名;
 修改数据
update 表名
set 列名=表达式----------->若一个表要更改多个数据，中间用逗号连接
[where 条件];
eg.set sage=18,Sno=1
 删除数据
delete
from 表名
[where 条件];
 3.7视图(5')
 视图是用来 缓存数据 的虚表
 建立视图
create view <视图名>
as<子查询>
[with check option];
---->查询字段里用了表达式或聚集函数的一定要重新命名
e.g 建立信息系学生的视图，且要求进行修改和插入时仍保证该视图只有信息系学生
create view V_IS
as 
select *
from S
where Sdpet='IS'
with check option;--->加了这句话更改数据时会受到where子句条件的影响
 删除视图
drop view <视图名> [cascade];--->加了可以删除该视图和它导出的所有视图
 视图一旦定义，其定义将永久保存在数据字典中，之后所有查询可以使用
 查询更新视图和表类似
 不可以更新的视图
1由两个以上基本表导出的
2字段来自字段表达式和常数的不准insert和update，可以delete
3字段来自聚集函数的
4定义中有group by的
5定义中有distinct的
6定义有嵌套查询且内层查询from涉及的表也是导出该视图的基本表
7不允许更新的视图导出的视图
 视图的作用
1简化用户操作
2使用户能以多种角度看待同一数据
3对重构数据库提供了一定的逻辑独立性
4能够对机密数据提供安全保护
5适当利用视图可以更清晰地表达查询

 4.1数据库安全性
 数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露、更改或破坏。
 保护数据安全性的一般性方法是设置用户标识和存取权限控制。
 安全性控制的一般性方法有用户身份鉴别，存取控制，审计，数据加密和视图的保护。
 存取控制（填空）
 存取控制机制主要包括 定义用户权限 和 合法权限 检查 两部分。
 定义用户权限和合法权限检查机制一起组成了数据库管理系统的存取控制子系统。
 自主存取控制 DAC                    强制存取控制 MAC
 在存取控制机制中先进行DAC再进行MAC。
 自主存取控制方法
 用户权限是由 数据库对象 和 操作类型 两个要素组成的。
 定义用户的存取权限就是要定义这个用户可以在哪些数据库对象上进行哪些类型的操作。
 定义用户存取权限叫 授权 。
 grant授权
1把查询S表和修改Sno的权限授给U1，并允许把该权限再授给其他用户
grant update(Sno),select
on table S
to U1
with grant option;
2把对SC表的所有操作权限授给U2,U3
grant all privileges
on table SC
to U2,U3;
 revoke收回
1收回所有用户对SC表的查询权限
revoke select
on table SC
from public;
2收回U5对SC表的insert权限
revoke insert
on table SC
from U5 cascade;  ----->同时收回了U5传给其他用户的该权限
 角色是权限的集合，可以为有相同权限的用户创建角色来管理授权
 创建角色 create role <角色名>;
 角色授权和收回与用户类似。
 MAC
 主体是实际用户，客体是文件、基本表、索引、视图等。
 密级次序    TS(绝密)>=S(机密)>=C(可信)>=P(公开)
 主 体的许可证级别 大于等于客 体时，该 主 体才能 读取 相应的 客 体
        主体的许可证级别小于等于客体时，该主体才能写相应的客体

 5.数据库完整性
 数据库系统中，保护数据正确及语义有效的功能是完整性控制。
 用户定义的完整性
 列值非空 not null 
        列值唯一 unique
        检查列值是否满足一个表达式（check 短句）
 建立部门表DEPT，要求Dname取值唯一
create table DEPT
(Dno char(8) primary key,
Dnamechar(8) unique not null);
 S表的Ssex只能取“男”或“女”
create table S
(Sno char(8) primary key,
Ssex char(2) check(Ssex in("男","女")),
Sage smallint);
 SC的grade在0到100之间
create table SC
(……,
……,
grade smallint check(grade>=0 and grade<=100)
);
 完整性约束命名    constraint <约束名> <约束条件>
create table S
(Sno numeric(6)
constraint C1 check(Sno between 1 and 19),
Sname char(8)
constraint C2 not null,
……,
constraint Skey primary key(Sno)
);
修改完整性限制（先删后增）
alter table S
   drop constraint C1;
alter table S
   add constraint C1 check(Sno between 1 and 20);
 触发器(10')   
 触发器是由用户 定义在关系表 上一类由 事件驱动 的特殊过程。
 特殊类型的存储过程。
 不需要用户用特殊的语句调用。
 不能对视图定义after触发器，可以定义instead of触发器。
 声明变量declare @变量名   类型[大小]
        变量赋值 select @全局变量名=值  |    set  @局部变量名=值
 inserted表和deleted表是 虚表 ，由 系统在内存定义 ， 不存放在数据库 ，这两张表 可读不可写 ，触发器工作完成后删除。
 inserted表存放插入或更新后的 新 数据，
        deleted表存放删除或更新前的旧数据。
 例题PPT +1-1型
 create trigger  tr_xx
        on 表名(要在哪个表操作就写哪个表)—————>自变量
        for  操作（insert,delete,update）
        as
        declare  @xx ………(声明变量)
        select @xx=………(变量赋值)
        if  (条件)
        begin
        ………(多行)
        end
 员工管理数据库中有部门表和员工表，部门表中的“部门人数”的值，随着员工表记录的增减而增减
create trigger tr_xx
on 员工表
for insert,delete
as
update 部门表
set 部门人数=部门人数+1
where 部门号=(select 所在部门 from inserted)
update 部门表
set 部门人数=部门人数-1
where 部门号=(select 所在部门 from deleted)
 默认操作用户，时间默认
create table sal_log
(……,
 ……,
 uname varchar(8) default user,
update datetime default getdate()
);

 关系数据理论
 数据依赖是一个关系内部属性和属性之间的一种约束关系。它是现实世界属性之间的抽象，是数据的内在性质，是语义的体现。
 函数依赖FD    多值依赖MVD
 关系模式存在的问题   数据冗余，更新异常，插入异常，删除异常
 删除异常指不该删除的数据被删除，插入异常指应该插入的数据未被插入。
 规范化p180
 当关系R（A，B）已属于3NF，仍存在一定的插入和删除异常。
 任何二元关系的最高范式为BCNF。

 求最小函数依赖集
1删掉每个依赖关系看后面的推不推的出依赖关系右边的东西
2看剩下的依赖关系左边不止一个的里面，一个能不能推另一个可以就用这个替换掉多个
 求候选码
 范式15'
BCNF是不存在主属性对候选码的部分依赖和传递依赖。
 一个低一级范式通过 模式分解 可以转换成若干个高一级范式的关系模式的集合，这个过程叫规范化。

 数据库设计
 ER图用来描述信息结构，不涉及信息在计算机中的表示，是概念设计阶段。
 数据库 概念设计 中最常用的模型是 实体联系模型 。
 设计关系模式是逻辑设计阶段。
 数据库设计的步骤（*6）需求分析，概念结构设计，逻辑结构设计，物理结构设计， 数据库实施，数据库运行和维护 。
1需求分析   数字字典，全系统中数据项，数据结构，数据流，数据存储的描述
2概念设计 概念模型ER图，数据字典
3逻辑设计 
4物理设计 a存储安排b存取方法选择c存取路径建立
5数据库实施 a创建数据库模式b装入数据c数据库试运行
6数据库运行和维护 性能监测，转储/恢复，数据库重组和重构
 各子系统之间ER图之间的冲突有    属性冲突，命名冲突，结构冲突

 数据库编程
 嵌入式SQL
 主语言指被嵌入的程序设计语言如C，JAVA等
 主语言与SQL的交互方式：SQL通信区，主变量，游标。
嵌入式SQL语句与主语言之间的通信方法是:
1语言向SQL语句输入数据主要用主变量(主语言中的程序变量)来实现
2SQL语句向主语言输出数据主要用主变量和游标来实现
3所谓游标,指的是系统为用户开设的一个数据缓冲区
 存储过程是存放在 SQL Server服务器 上的预先定义好并编译好的T-SQL语句。
 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
 存储过程存在数据库内，可以由应用程序调用执行
 存储过程分为系统存储过程和用户定义的自存储过程
 存储过程优点
1可以在一个存储过程执行多条SQL语句
2可多次调用
3创建时就在服务器端进行了编译，节省了SQL语句的运行时间
4提供了安全机制，限制了用户访问SQL语句的权利，只对特定的用户开放存储过程

 存储过程 （有参的）
create procedure  <名>
@变量名  变量类型------->声明变量
as
if (条件)
begin
……
end
else
……

执行 exec 名字 实参
1执行存储过程，查询法学院的学生参加“实践”课程的所有学生学号，姓名，所学课程编号和课程名
create procedure getPractice
@dname char(8)
as
if(select count(*)from s<>0 where Sdpet=@dname)
begin
select s.Sno,Sname,b.Cno,Cname
from SC,S,C
where S.SnoSC=.Sno and SC.Cno=C.Cno
 and C.type='实践' 
and Sdpet=@dname
end
else 
print （'院系名不存在'）

exec getPractice '法学院'

2根据调用时输入的学号，查询该生选修的课程和成绩
create procedure p_sc
@sno char(8)
as
select cno,grade
from sc
where sno=@sno

exec p_sc '1233'

 事务是DBMS的基本单位。
 事务的原子性指事务中包括的所有操作要么都做要么都不做。
 事务的一致性是指事务必须是使数据库从一个一致性状态到另一个一致性状态。
 事务的隔离性是指一个事务内部的操作及使用的数据对并发的其他事务是隔离的。
 事务的持续性是指事务一旦提交，对数据库的改变是永久的。