第五章 数据库

1.数据模型

信息=数据+数据处理

1.1概念数据模型

独立于计算机系统的模型，完全不涉及信息在系统中的表示

实体：客观存在并可以相互区别的事物

属性：描述实体的特性

码：唯一标识实体的属性集

域：属性的取值范围

两个实体间的联系：
一对一：1 : 1
一对多：1 : n
多对多：m : n

2.结构数据模型

面相数据库的逻辑结构

关系模型(二维表)：实体与实体之间

2.数据库的三级模式结构

1.三级模式：
概念模式(模式)→基本表
外模式→视图
内模式→存储文件

2.两级映像：
模式/内模式：数据的物理独立性
外模式/模式：数据的逻辑独立性

3.关系模式

R<U,F>
R：表名
U：一组属性
F：为属性组U上的一组函数依赖

3.1基本术语

关系的描述称为关系模式

元组：表中的一行为一个元组

后选码(后选键)：能唯一的标识一个元组

主码(主键)：在多个后选码中选一个为主码

主属性：包含在后选码里的属性

外码(外键)：一个关系中的属性或属性组并非该关系的码，而是另一个关系的码

全码：关系模式的所有属性组是这个关系的后选码

超码：一个包含码的属性集

3.2完整性约束

1.实体完整性：关系中的主码不能为空或部分为空

2.参照完整性：外码的每个值必须在关系中主码的值中找到或为空

3.用户定义的完整性：用户对某一数据指定的约束条件的检验

3.3传统的集合运算

1.关系的并

2.关系的差

3.关系的交

4.笛卡尔积：R的每个元组 * S的所有元组

3.4专门的关系运算符

1.投影(π)：关系的垂直方向进行运算(列)

2.选择(σ)：关系的水平方向进行运算(行)

3.连接连接(⨝)：
在R和S的笛卡尔积中选择满足条件的元组
①θ连接：R⨝S，XθY是条件，更选择类似
②等值连接：R⨝S，X=Y是条件
③自然连接：是去除重复属性列的等值连接

3.5外连接

经过自然连接后会丢失某些数据，外链接可以避免这些数据的丢失

1.左外连接：不会丢失左表的信息，用null填充右表的属性

2.右外连接：不会丢失右表的信息，用null填充左表的属性

3.全外连接=自然连接+左外连接+右外连接

3.6除

R/S：R表中可以找到S的所有行(一个属性组)

4.关系代数转SQL语言

1.投影→SQL语言

2.选择→SQL语言

3.笛卡尔积→SQL语言

4.自然连接→SQL语言

5.数据语言

1.SQL语言的分类
DDL：数据定义语言
DML：数据操纵语言
DQL：数据查询语言
DCL：数据控制语言

5.1数据定义语言(DDL)

//1.建立数据库
CREATE DATABASE 数据库名 
//create database 数据库名

//2.建立基本表
CREATE TABLE 表名
(
    列名 数据类型 列级完整性约束
    ...
    表级完整性约束
)

//3.添加列
ALTER TABLE 表名 ADD 列名 类型
//alter table 表名 add 列名 类型

//4.修改列
ALTER TABLE MODIFY 列名 类型
//alter table 表名 modify 列名 类型

//5.删除列
ALTER TABLE 表名 DROP 列名
//alter table 表名 drop 列名

//6.删除基本表
DROP TABLE 表名
//drop table 表名

DROP TABLE TEST
ALTER TABLE TEST DROP f3
ALTER TABLE TEST MODIFY f3 FLOAR
ALTER TABLE TEST ADD f3 DATATIME

CREATE TABLE TEST
(
    f1 char(10),
    f2
)
/*
drop table test
alter table test drop f3
alter table test modify f3 float
alter table test add f3 datatime

create table test
(
    f1 char(10),
    f2 int
)
*/

1.列级完整性：
not null：不能为空
unique：唯一
not null unique：不能为空且唯一
default：默认

2.表级完整性约束：
①主键子句(PRIMARY KEY)实体完整性
②检查子句(CHECK)用户自定义完整性
③外键子句(FOREIGN KEY)参照完整性primary key == not null unique

CREAT TABLE test
(
    sid INT,
    PRIMARY KEY(sid)
)

CREAT TABLE test1
(
    cid INT,
    sid INT,
    score DOUBLE,
    CHECK(score >= 0 AND score <= 100)
    FOREIGN KEY(sid) REFERENCE test(sid)
)

5.2数据操纵语言(DML)

//1.直接插入元组值
INSERT INTO 表名(列名序列) VALUES(元组值)
//insert into 表名(列名序列) values(元组值)

//2.插入查询结果值
INSERT INTO 表名(列名序列) SELECT 查询语句
//insert into 表名(列名序列) select 查询语句

//3.删除语句
DELETE FROM 表名 [WHERE 条件表达式]
//delete from 表名 [where 条件表达式]
//[]里面的可以加也可以不加，不加就删除整张表

//4.修改语句
UPDATA 表名
SET 列名 = 表达式值[,列名 = 表达式值]
[WHERE 条件表达式]

INSERT INTO student VALUES('张三',18,'男')
INSERT INTO student(姓名,年龄) VALUES('李华',28)

INSERT INYO student SELECT '旺旺',28,'男'

DELETE FROM student WHERE 年龄 = 18

UPDATA student
SET 姓名 = '李四'
WHERE 姓名 = '旺旺'

SELECT * FROM student//*表示查询表中所有

5.3数据查询语言(DQL)

SELECT子句的输出项可以是列名，或由列名，常数和算数运算符构成的表达式

SELECT 目标表的列名或列表达式序列
FROM 关系名表序列
[WHERE 行条件表达式]
[GROUP BY 列名序列]
[HAVING 组条件表达式]
[ORDER BY 列名[ASC|DESC]...]

SELECT 学号,学号 * 100
FROM student

5.3.1投影查询

DISTINCE：去掉重复显示行

AS：当前列起别名

SELECT 学号,性别 FROM student

SELECT DISTINCE 系别 FROM teacher

SELECT 学号 AS 'SNO',性别 AS 'SEX'
FROM student

5.3.2选择查询

BETWEEN：检索两个范围内

LIKE：字符串的匹配运算，表示查找指定的属性值与’匹配串’相匹配的元素
%：任意长度的字符串，a%b：以a开头以b结尾
_：任意单个字符，a_b：以a开头以b结尾的3个字符串，a__b：4个字符串

IN：包含集合成员

SELECT * FROM score WHERE 分数 BETWEEN 60 AND 80;
SELECT * FROM score WHERE 分数 >= 60 AND 分数 <= 80

//[NOT]LINK '匹配串'
SELECT * FROM student WHERE 姓名 LIKE '王%'

SELECT * FROM student WHERE score IN (96,85,67,43)

SELECT * FROM student WHERE 姓名 = '张三' OE 性别 = '女'

5.3.3排序查询

ORDER BY：控制显示行的显示顺序，默认，ASC(升序)，DESC(降序)必须是SELECT中最后一个子句

SELECT 姓名,年龄,性别
FROM student
WHERE 性别 = '男'
ORDER BY 年龄 DESC

SELECT *
FROM score
ORDER BY 课程号 ASC,分数 DESC

5.3.4聚合函数

数据统计，并返回单一的值

AVG：求平均值

COUNT：求总数

MIN：求最小值

MAX：求最大值

SUM：求和

SELECT 课程号,SUM(分数),AVG(分数)
FROM score
GROUP BY 课程号
//按课程号把值相同的分为一组
//分别计算每组的聚合函数

当条件中有聚合函数时用HAVING

SELECT 学号
FROM score
WHERE 分数 IS NOT NULL
GROUP BY 学号
HAVING MIN > 70 AND MAX < 90

SELECT后面有聚合函数，要把除聚合函数的所有列进行分组

5.3.5表的连接查询(JOIN)

SELECT 列名1,列名2...
FROM 表1,表2...
WHERE 连接条件

1.内连接
①等值连接：链接条件是=
②非等值连接：链接条件是其它
③自连接：同一个表进行连接
2.外连接
①左外连接(LIFT JOIN)
②右外连接(RIGHT JOIN)
③全外连接(FULL JOIN)

5.4子查询

1.一般子查询
用于SELECT语句中的WHERE语句
查询的条件是另一个查询

一个子查询返回值为多行时，用ANY,ALL,IN,NOT IN

只执行一次

2.相关子查询
不止执行一次，会根据查询的变化而变化

3.带EXISTS测试的子查询
不返回任何数据，
子查询的结果不为空时，返回逻辑真值
子查询结果为空时，返回逻辑假值

5.5查询结果的并，交，差

UNION：并
INTERSECT：交
EXCEPT：差

6.SQL访问控制

1.授权的语句格式

GRANT 权限[,权限]
[ON 对象类型 对象名]
[TO 用户]

WITH GRANT OPTION：获得权限的用户还可以把权限赋给其它用户

6.1视图

从一个或多个基本表或视图中导出的虚拟表

//视图的创建
CREATE VIEW 视图名(列表名)
AS SELECT 查询子句 
[WITH CHECK OPTION]

//视图删除
DROP VIEW 视图名

索引改变的是内模式或存储文件的内容

7.函数依赖

1.函数依赖：X→Y是X决定Y，Y依赖于X

2.非平凡的函数依赖：X→Y，Y⊈X

3.平凡的函数依赖：X→Y，Y⊆X

4.完全函数依赖：X→Y，对于X的任意一个真子集X¹，所有X¹都不能决定Y，记作X——>Y

5.部分依赖函数：X→Y，某一个X¹可以决定Y

6.传递依赖：X→Y，Y→Z，Z对X传递依赖

7.码：设K为R<U,F>中属性的组合，
若K→U，且每一个K¹不能决定U，则K为U的候选码

8.主属性：包含在候选码中的属性，否则就是非主属性
U={ABCD}
后选关键字为AC
有两个主属性，2个非主属性

9.外码：X在R中，但X是另一个关系的码

10.全码：所有属性都是候选码

11.超码：属性组中包含码

12.函数依赖的公理系统
A3传递率：X→Y，Y→Z，X→Z
合并规则：X→Y，X→Z，X→YZ
分解规则：X→Y，Z⊆Y，X→Z

7.1属性闭包计算

8.范式

1.第一范式(1NF)不可再分
不能排除数据冗余和更新异常(修改，插入，删除)等问题，可能存在部分函数依赖

2.第二范式(2NF)
R必须是1NF
每个非主属性都完全依赖于候选码
分解后不能丢失信息
可能存在传递函数依赖
(学号→学院，学院→院长)

3.第三范式(3FN)
R必须是2FN
每个非主属性都非传递函数依赖于候选码
可能存在主属性对码的部分依赖和传递依赖


4.BC范式
所有非主属性对每个码都是完全依赖
所有的主属性对每个不包含它的码也完全依赖
已经消除了插入删除异常

5.第四范式(4NF)
多值依赖：X→→Y
对于R的每个非平凡多值依赖X→→Y，Y⊈X，
X都包含了R的一个候选码


伪传递：X→Y，WY→Z，XW→Z不属于3NF

无损连接：对关系模式分解后可以通过自然连接运算恢复

9.数据库设计

1.设计策略：自顶向下，自底向上

2.设计步骤(新奥尔良法)：
①用户需求分析(手机用户需求,确定系统边界)
②概念设计(E-R图)
③逻辑设计(E-R图→关系模式,规范化)
④物理设计

9.1用户需求分析

用户需求分析(逻辑设计，数据字典)
系统需求说明书包括
数据流图，数据字典，需求说明文档

9.2概念模型(E-R模型)

属性：
1.简单属性和复合属性
2.单值属性和多值属性
3.null属性
4.派生属性：通过其它属性计算得出

弱实体：这种实体对于另一些实体具有很强的依赖性

9.3概念结构设计

1.选择局部应用
2.逐一设计分E-R图
3.E-R图合并

分E-R图之间的冲突
①属性冲突
②命名冲突(异名同义，同名异义)
③结构冲突

9.4逻辑结构设计

E-R图转换为关系模型
实体→关系模型
实体(属性)

联系→关系模型
1:1：把联系的属性归并到任意一方实体中

1:n：把1方的码归并到多方里

m:n：把双方实体的码放到联系中

规范化：
根据语义→数据依赖→范式(3NF，BCNF)

10.数据库控制功能

事务管理特性：
原子性，一致性，隔离性，持久性

备份方法：
建立数据冗余(重复存储)，建立冗余数据的方法是进行数据转储和登记日志文件

数据的转储：
①静态转储和动态存储：静态在数据存储期间不允许对数据库进行任何存取，修改操作;动态允许
②海量转储和增量转储：海量每次转储全部数据，增量每次转储上次转储后更新过的数据
③日志文件：事务开始到事务结束的每一次操作都写入日志文件(事务故障，系统故障，介质故障)

恢复：反向扫码日志文，执行逆操作，直至事务的开始标志

11.并发控制技术(封锁)

封锁：排它锁(X锁/写锁)，共享锁(S锁/读锁)

1.排它锁：事务T对数据对象A加上X锁，就只允许T读取和修改A，其它事务不可以再对A加任何类型的锁，直到T释放A上的锁

2.共享锁：事务T对数据对象A加上S锁，就只允许T读取A，其它事务只能对A加S锁，直到T释放A上的锁

12.分布式数据库

分片透明：用户或应用程序不需要知道逻辑上访问的表具体是怎么分块处理的
复制透明：采用复制技术的分布方法，用户不需要知道数据是恢复到哪些节点，如何复制的
位置透明：用户无需知道数据存放的物理位置
逻辑透明：用户或应用程序不需要知道局部场地使用的是哪种数据类型

共享性：数据存储在不同的节点数据共享
自治性：每节点对本地数据都能独立管理
可用性：当某一场地故障时，系统可以使用其它场地上的副本而不至于使整个系统瘫痪
分布性：数据在不同场地上的存储