安徽工程大学数据库笔记

数据：描述事物的符号记录。
数据库：长期存储在计算机内的、有组织的、可共享的大量数据集合。
数据库管理系统：是位于用户与操作系统之间的具有数据定义、数据操纵、数据库的运行管理、数据库的建立和维护功能的一层数据管理软件。
数据库系统：在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户构成。

DDL、DML（操纵）、DCL

1.1.3 DBS的特点

数据结构化
数据共享性高、冗余度低且易扩充
数据独立性高

物理独立性：应用程序与数据库中数据的物理存储是相互独立的

逻辑独立性：应用程序与数据库的逻辑结构是相互独立的，数据的逻辑结构改变时用户程序可以不变

数据由DBMS统一管理和控制

1.2 数据模型

1.2.1 两类数据模型

概念模型（信息模型）按用户的观点对数据和信息建模，用于DB设计
逻辑和物理模型逻辑模型用于DBMS的实现物理模型是对底层的抽象

1.2.2 概念模型

实体
属性
联系
码（唯一标识实体的属性集）
实体型比如学生
实体集比如全体学生

ER图：概念模型的表示方法

1.2.3 数据模型的组成要素

数据结构（静态特性）
数据操作（动态特性）
数据的完整性约束条件

1.2.7 关系模型

关系模型的数据结构

关系：表
元组：行
属性：列
码：表里的某个属性组，它可以唯一确定一个元组
域：一组具有相同数据类型的集合
分量：行中的某个值
关系模式：对关系的描述
关系的每一个分量必须是一个不可分的数据项，即不允许表里有表

第2章关系数据库

（完整性放置在笔记第五章内容）

2.1 关系数据结构及形式化定义

2.1.1 关系

域一组具有相同数据类型的值的集合；一个域允许的不同取值的个数成为基数
笛卡儿积每个域的基数相乘=行数
关系

候选码，选其中一个作为主码全码关系有三种类型：基本关系、视图表、查询表

2.2 关系操作

增删改查

查又分为：

基本操作是：选择、投影、并、差、笛卡儿积

2.3 关系的完整性

主码不能为空，若主码有多个，则都不能空

实体完整性：主属性不能取空值
参照完整性：外码要么为空值、要么为某个元组的主码值
用户定义的完整性

2.4 关系代数

2.4.1 传统的集合运算

并
差
交
笛卡儿积（用R X S表示）

2.4.2 专门的关系运算P53

选择针对行
投影针对列，也可能取消某些行，避免重复
连接

等值连接：从广义笛卡儿积中选取A、B属性值相等的元组（注意相等的不能合并）

自然连接：特殊的等值连接，将相等的合并了（它要求两个关系中进行比较的分量必须是相同的属性组，并且要在结果中把重复的属性去掉）

非等值连接：从广义笛卡儿积中选择符合条件的

在连接中被舍弃的元组为悬浮元组

若不舍弃，则为外连接

不舍弃左边关系中的，就叫左外连接

3.关系代数
1.传统集合操作

并
差
交
笛卡尔积
2 .专门的关系运算

选择：在关系 R 中选择满足给定条件的元组（>、<、=……）行
投影：关系 R 的投影是从 R 中选出若干属性列 列（会去除重复行）
连接：从两个关系的笛卡尔积中选取满足给定条件的元组
除运算：设 R 除 S 的结果为T，则T包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有的所有组合都在R中

外码：

第3章关系型数据库标准语言SQL

3.1 SQL概述

3.1.3 SQL的基本概念P78

数据类型;

运算符：

操作函数：

外模式：包括若干视图和部分基本表，基本表是本身独立存在的表，一个关系就对应一个基本表，一个或多个基本表对应一个存储文件；视图是从一个或多个基本表导出的表，本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放其数据，数据仍存在基本表中，因此视图是虚表，视图上可以再定义视图。

模式：包括若干基本表

内模式：包含若干存储文件，存储文件的逻辑结构组成了关系数据库的内模式，存储文件的物理结构对用户是隐蔽的

3.3 数据定义

3.3.1 模式的定义和删除

3.3.2 基本表的定义、删除与修改

定义基本表

create table 表名(sno char(4) primary, sname char(6) unique, foreign key(Cno) references Course(Cno));

跟在列名后面的是列级完整性约束

单独定义的是表级

create table SC(primary key(Sno, Cno), Cno char(4), Sno char(3), grade smallint);

如果完整性约束条件涉及多个属性列，则必须定义在表级，如上所示

数据类型
模式与表
修改基本表*************
删除列

alter table 表名 drop column 属性名 cascade/restrict    //column是列的意思
注：后面的条件可不写，默认restrict；
restrict是指删除是有条件的，所删除的属性不能被其他表的约束所引用，不能有视图、触发器、存储过程或函数； 
cascade是指删除没有条件，删除的同时，所有相关的对象都将删除

增加列

alter table 表名 add column 属性名 数据类型;
如：alter table student add column Sname char(6);//mqsql里column可不写

修改列的数据类型//主码好像改不了,外码似乎也是 alter table 表名 change 原列名现列名数据类型;

删除主键约束

alter table student drop primary key;

增加主键约束

alter table student add primary key(sno);//注意：mysql一张表里只能有一个主键

设置唯一性约束

3. alter table student add unique（sname）;//注意：主键本身就是唯一的

6.设置外键约束（注意外键一定要是所参考的表（student）的主键，不然报错）
alter table student_course add foreign key(sno) references student(sno); 

7. 元组上的约束条件 P164
   a. 检查约束check //mysql不支持检查约束，但写上不会报错
   alter table student_course add constraint check(score >= 0 and score <= 100);

8. 设置默认约束
   alter table 表名 change 原列名 现列名 数据类型 default 默认值;
   如：alter table student change entime entime date default '2002-09-01'; 

9. 删除表
   drop table stu cascade/restrict;

3.3.3 索引的建立与删除

3.4 数据查询

数据查询是数据库的核心操作

3.4.1 单表查询

选择表中的若干列
查询指定列 select 属性列,属性列,··· from 表名;//各列的顺序可以和原表不一样
查询所有列 select * from 表名;
查询经过计算的值 select Sname,2014-Sage from 表名;

属性列还可以是算术表达式、字符串常量、函数等。如：select sname,'year of birth',2014-sage,LOWER(sdept) from student;//小写字母表示系名

还可以通过指定别名来改变查询结果的列标题，例如有些含有算术表达式、函数、常量的列名就可指定别名

如：select sname NAME,'yaer of birth' BIRTH,2014-sage BIRTHDAY from student;

选择表中的若干元组（行）
消除取值重复的行（两个不相同的元组投影到指定列后，可能会变成相同的行）使用distinct消除: select distinct sno from sc;

查询满足条件的元组

a. 比较大小 b. 确定范围 c. 确定集合 d. 字符匹配

  select * from student where Sno like '12313';//like等价于=
    //如果like后面的匹配串中不含通配符(%和_)，则可用=取代like，用!=或<>取代not like
    select * from student where Sname like '刘%';//有通配符必须用like
    //通配符%代表任意长度（包括0）的字符串，_表示任意单个字符
    select * from student where Sname like '欧阳_';
    select * from student where Sname like '_阳%';//第二个字为阳的
    select * from student where Sname not like '杨%';

//如果要查询的字符串本身有通配符，要用escape转义符
select Cno,Credit from Course where Cname like 'DB\_Design' escape'\';
//escape\表示\为换码字符，这样跟在\后面的字符不再具有通配符的含义

e. 涉及空值的查询 f. 多重条件查询

order by 子句（排序用）

select Sno,Grade from SC where Cno = '3' order by Grade desc;
desc: 降序
asc: 升序

聚集函数

count()//统计元组个数 count(distinct 列名)//统计一列中值的个数，取消重复值 sum(distinct 列名)//计算一列值的总和 max(distinct 列名)//计算一列中的最大值 avg(列名)//平均值当聚集函数遇到空值时，除count()，都跳过空值

where 子句中不能用聚集函数

group by子句（分组用）

将查询结果按某一列或多列的值分组，值相等的一组

1.求各个课程号及相应的选课人数

select Cno,count(Sno) 
from sc 
group by Cno;


如果分组后还要筛选，用having语句
select Cno,count(Sno) 
from sc 
group by Cno
having count(*) > 3;

where语句和having语句的区别：

where语句不能用聚集函数，作用于基本表或视图；having短语作用于组，也就是group

3.4.2 连接查询

等值与非等值连接查询

select stu.sno, sname
from stu,sc
where stu.sno = sc.sno and sc.cno'2';

//sno在两个表都有，所以要加前缀

自身连接

查询每一门课的间接先修课（先修课的先修课）

外连接

左外连接

select student.sno, sname, ssex, sage, sdept, cno, grade
from student LEFT OUTER JOIN SC ON (student.sno = sc.sno);

多表连接

查询每个学生的学号、姓名、选修的课程名、成绩

select student.sno, Sname, Cname, Grade
from student, sc, course
where student.sno = sc.sno AND sc.cno = course.cno;

3.4.3 嵌套查询（nested query）凸显结构化

子查询的select语句不能用order by子句，order by只能对最终查询结果排序

带有IN谓词的子查询

查询和刘成在同一个系学习的学生 SELECT sname,sno,sdept FROM student WHERE sdept IN (select sdept from student where sname = '刘成');

在此例中，IN可以用=替换子查询的条件不依赖与父查询，称不相关子查询(相关子查询（相关嵌套查询）反之)

带有比较运算符的子查询

找出每个学生超过他自己选秀课程平均成绩的课程号

SELECT Sno,Cno 
FROM SC x
WHERE Grade >= (SELECT AVG(Grade)
                FROM
                WHERE y.Sno = x.Sno);

3.带有any（some）或all谓词的子查询

带有exists谓词的子查询

3.4.4 集合查询

3.4.5 基于派生表的查询

3.4.6 select语句的一般格式

3.5 数据更新

3.5.1 插入数据

插入元组

insert into 表名（属性列，···，···） values（对应的值，···，···）；

注意：1. 没有出现的属性列，将自动取空值；但说明了not null 的属性列不能取空值，否则出错

 2. 如果没有指明任何属性列， 则新插入的必须在每个属性列上都有值（如果为null要显式给出），且顺序要和表的属性列顺序一样
 
 3.值如果为字符串常数，要用单引号括起来，数字不用
 
 4. 可以把属性列显式的置为NULL

例：insert into student（Sno，Sname，Ssex） values（'29193','李明','男'）;

一次同时插入多条数据： insert into 表名（属性列，···，···） values（对应的值，···，···），（对应的值2，···，···），（对应的值3，···，···）， ··· ；

插入子查询结果

子查询既可以嵌套在select语句中，也可以用在insert语句中用以生成要插入的数据

insert into dept_age(sdept, avg_age) select sdept, avg(sage) from stu group by sdept;

3.5.2 修改数据

1. 修改某一个元组的值

update 表名 set 属性名 = '' where 属性名 = ''；
如：update student set Sage = 16 where Sno = '201215121'；

2. 修改多个元组的值

update 表名 set Sage = Sage + 1；

3. 带子查询的修改语句

update SC set Grade = 0 where Sno in (select Sno from stu where Sdept 'CS');
3.5.3 删除数据

1. 删除一个元组

delete from 表名 where 属性名 = ''；

2. 删除多个元组

delete from 表名；//删除所有，该表成为空表

3. 带子查询的删除语句

delete from SC where Sno in (select Sno from stu where Sdept = 'CS');
3.6 空值的处理

3.7 视图

3.7.1 定义视图

建立视图

with check option 表示对视图操作时要满足视图定义中的条件(比如下例中的sdept=’IS‘)

组成视图的列名要么全部省略，要么全部指定

以下三种情况必须全部指定：

某个目标列是函数表达式有同名列需要更合适的名字 create view IS_student(sno,sname,sage) as select sno,sname,sage from student where sdept = 'IS'; 若一个视图是从单个基本表导出，且只去掉了某些行列，但保留了主码，称这类视图为行列子集视图

带虚拟列（派生属性，在基本表中不存在）的视图称为带表达式的视图

带有聚集函数和group by 子句的查询来定义的视图称为分组视图

删除视图

drop view IS_stu cascade; cascade表示把由该视图导出的所有视图都删除

当基本表删除后，视图的定义还在，必须用删除视图语句显式删除

3.7.2 查询视图视图消解：把视图的查询转换为对基本表的查询

3.7.3 更新视图 3.7.4 视图的作用

第4章数据库安全性

4.1用户身份鉴别

静态口令鉴别
动态口令鉴别：比如短信验证、动态令牌
生物特征鉴别
智能卡鉴别

4.2多层存取控制

主要是：定义用户权限与合法权限检查

自主存取控制：用户对不同的数据库有不同的访问权限，不同的用户对同一数据库也有不同的权限，且用户还可以将拥有的权限转授给其它用户

强制存取控制：每一个数据库都被标以一定的密级，每一个用户也被授予某一个级别的许可证。

自主存取控制方法：

1.GRANT 授权

GRANT <权限>
ON <对象>
TO <用户>
[WITH GRANT OPTION] #转授权

例如：把查询 student 表的权限授予给 U4 用户
GRANT SELECT
ON TABLE student
TO U4

2.REVOKE 收回

REVOKE <权限>
ON <对象>
FROM <用户>

例如：把用户 U4 修改学生号的权限收回
REVOKE UPDATE(Sno)
ON TABLE student
FROM U4

强制存取控制方法

主体：系统中活动的实体，指数据库管理系统所管理的实际用户客体：系统中被动实体，指文件、基本表、索引等

规则：

主体大于或等于客体密级，才能读
主体小于或等于客体密级，才能写

第5章

5.3 用户定义的完整性

5.3.1 属性上的约束条件(P163)

属性上约束条件的定义

包括：

not null
unique
check

1.实体完整性

通过 PRIMARY KEY 定义

2.参照完整性

用 FOREIGN KEY 定义哪些列为主码，用 REFERENCES 指明这些外码参照哪些表的主码

例如：

FOREIGN KEY(Sno) REFERENCES Student(Sno);

3.用户定义的完整性

1.不能取空值 NOT NULL
2.列值唯一 UNIQUE
3.用 CHECK 指定列值应满足的条件

Sex VARCHAR(2) CHECK (Sex IN ('男','女'));  #性别只能选 '男','女'

4 .通过 constaraint <完整约束名> <完整约束条件> 定义完整约束命名子句

第7章数据库设计

7.1 数据库设计概述

数据库设计基本步骤

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

7.3 概念结构设计

7.4 逻辑结构设计

将 E-R 转换为数据库系统所支持的数据模型相符合的逻辑结构

如将 E-R 图转换为 关系模型

`职工（职工号 、部门号、职工名、职务）

7.4.1 ER图向关系模型的转换 7.6 DB的实施和维护 7.6.1 数据载入和应用程序的调试

重点：p208图7.2

7.5设计思想：

7.6重点：

构思，画出分解框架
逐一分解画出E-R图
画出各部分详细的图解
具体实现及功能补充

第9章关系查询处理和查询优化

9.1 关系DBS的查询处理

查询分析
查询检查
查询优化
查询执行 9.2 关系DBS的查询优化 9.2.1 查询优化概述查询优化的优点不仅在于用户不必考虑如何最好的表达查询以获得较高的效率，而且在于系统能比用户程序的“优化”做的更好，这是因为：

9.3 代数优化

9.3.2 查询树的启发式优化风格恢复

第10章数据库恢复技术

10.1.事务基本概念

满足 ACID 的一组操作，要么全部做，要么全部不做，是一个不可分割的单位

ACID：

原子性一个事务被视为一个不可分割的单位，不可能只执行其中的一部分一致性数据库总是从一个一致性状态转移到另一个一致性状态隔离性一个事务内部操作及使用的数据对其它事务是隔离的，并发执行的各个事务之间不能相互干扰持续性一旦事务提交，所做的修改将永远保存在数据库中

10.2.故障种类

1.事务内部故障 2.系统故障 3.介质故障 4.计算机病毒

10.3.恢复的实现技术

数据转存定期的将数据库的数据复制到磁带、磁盘或者其它存储介质上存储起来的过程

有：动态转存、静态转存——转存期间能否有事务运行

登记日志文件

1.日志文件格式与内容

日志文件——用来记录事务对数据库更新操作的文件

格式：以记录为单位、以数据块为单位内容：记录型——事务标识、操作类型、操作对象、新值、旧值数据块型——事务标识、数据块

2.为保证数据库是可恢复的，登记日志文件需保证以下两个原则：

登记的次序严格按照并发事务执行的时间次序必须先写日志文件再写数据库

10.4.恢复策略

1.事务故障恢复

事务故障指事务在运行至正常终点前被终止

这时恢复子系统通过日志文件撤销已经对数据库进行的修改；反向扫描日志，查找该事务的更新操作，并对该事务的更新操作执行逆操作

2.系统故障的恢复

撤销故障发生时未完成的任务，重做已完成的任务

1.正向扫描日志，故障前完成加入 REDO 队列，故障后完成加入 UNDO 队列；2.对于 UNDO 队列按事务更新操作逆向恢复；3.对 REDO 重做事务

3.介质故障的恢复

重装数据库，重做已完成的事务

1.重载数据库副本；2.重载日志文件

10.5.具有检查点的恢复

利用日志技术恢复缺点：1.搜索整个日志耗时长；2.有些 REDO 操作可能不需要进行，因为已经写入数据库

在日志文件中新增一类——检查点记录，并增加一个重新开始文件，有恢复子系统在登录日志文件期间动态的维护日志

检查点记录包括：1.建立时刻正在执行的任务清单；2.最近一个日志记录地址

恢复步骤：

通过重新开始文件在日志中找到最后一个检查点记录从检查点开始正向扫描日志，有新开始的事务加入 UNDO，提交事务加入 REDO REDO 重做，UNDO 回滚；

第11章并发控制

事务是并发控制的单位；为了保证事务的隔离性和一致性，数据库要对并发操作进行正确的调度；

并发一致性问题：

丢失修改——A、B 同时修改数据，B 的提交破坏了 A 的提交，导致 A 的修改丢失脏读——事务 B 读取了事务 A 回滚的数据不可重复读——事务 B 在事务 A 执行期间修改了数据，导致A 前后读取数据不一致幻读——事务 B 在事务 A 执行期间新插入数据。导致 A 再次读取某范围内数据时多了一行上述并发一致性问题主要是并发操作破坏了事务隔离性。并发控制机制主要就是用正确的方式调度并发操作，是一个用户事务的执行不受其它事务的干扰

主要技术有：封锁、时间戳、乐观控制法和多版本控制