数据库相关概念及SQL用法

第一章绪论

1.1 数据库系统概述

数据库管理技术的发展

人工管理阶段
- 数据保存在穿孔卡片，由人工管理
文件系统阶段
- 数据以文件的形式存储在磁盘
- 用户程序通过**操作系统 **对文件进行访问
数据库系统阶段
- 网状模型
  - 以有向图的方式表示现实世界中实体间的关系
  - 第一个网状模型：集成数据存储（IDS）系统
- 层次数据模型
  - 采用树状结构组织数据
  - 最典型的：IMS
- 关系模型
- 相比文件系统的优势：没有不必要的冗余
数据库高级应用阶段
- 决策支持系统（DSS）：基于数据的决策支持
- 数据仓库
  - 为DSS集成企业大量的数据，是一个数据集合
  - 特点：面向主题，集成，相对稳定，反映历史变化
- 商业智能（BI）：通过对数据分析得到信息，并指导企业运作的行为（啤酒纸尿裤）
多元数据管理阶段
- 键值（key-Value）模型
  - 哈希表实现键到值的定位
  - 基于键值的数据库：内存型数据库，持久化数据库
- 列式存储模型
- 文档模型
  - 通过键-值定位一个文档，文档是数据库的最小单位
  - 基于文档内容的索引和查询能力是文档数据库与KV数据库的主要区别
- 图数据模型
  - 以图论为基础
  - 模型由节点和边组成
  - 节点表示实体，边表示两个实体之间的关系
  - 边可以有自己的属性和方向
  - 适用于高度关联的数据

数据库

是什么：数据库就是数据的集合
利用数据库管理数据的特点
- 数据的组织结构需事先定义好
- 数据库中的数据是有联系的，这区别于文件系统

数据库管理系统

DBMS功能：①定义数据库②数据操作③安全控制④并发控制⑤故障恢复
主要模块
- 查询处理模块
- 事务管理模块
  - 主要组件：并发控制管理器、日志及恢复管理器
- 存储管理模块（磁盘和内存数据交换）

1.2 数据模型

是什么：数据模型就是对现实世界中的数据特征及数据之间关系的抽象表达
数据模型三要素：数据结构、数据操作、抽象约束
对数据实施三个层次的抽象形成3种数据模型
- 概念模型：对现实世界数据的最高层次的抽象
- 逻辑数据模型：具体DBMS所支持的模型，比如层次模型、网状模型、关系模型
- 物理数据模型：面向计算机存储的模型
常用逻辑数据模型：层次模型、网状模型、关系模型、键值模型、列式存储模型、文档模型、图模型

1.3 数据库系统结构

①体系结构

两层体系结构：客户端应用程序 - DBMS - 数据库
三层体系结构：应用程序i - 应用服务程序 - DBMS - 数据库

②模式结构 & 数据独立性

三层模式结构：直接背概念那个word吧
映射 - 实现不同模式之间的相互映射，保证数据的正确定位和分配
- 外模式和模式之间有外模式/模式映射
- 模式和内模式之间有模式/内模式映射
数据独立性（概念第20题）
- 逻辑独立性
- 物理独立性

第二章关系模型

2.1 关系模型的数据结构

元组：（关系）二维表中的一个数据行
码：一个属性（或属性组）可以唯一标识一个元组（一行），则该属性称为关系的码
候选码：一个关系不止一个码时，将码称为候选码
主码：一个关系有多个码时，从中选一个作为主码
外码：某属性在关系A中不是主码，但在关系B中是主码，则该属性是关系A的外码
主属性：包含在任意一个码中的属性（可能不是码，eg一个属性组是码，则属性组中的其中一个属性就是主属性）
关系实例：一个关系中当前元组的集合
关系模式：关系名和属性的集合（这三个注意一下小心概念题）
关系的数据库模式：若干相关的关系模式的集合

2.2 关系代数

进行集合运算（并交差）的前提是：这两个关系模式是同元的（相容关系），即属性数目相同，对应属性的顺序和域也相同（相同位置的列对应数据类型必须一样）

第三章数据库设计

3.1 数据库设计过程

设计目标：一个合理的数据库应该在满足业务逻辑的基础上，以较小的冗余提供较高的应用效率
设计步骤
1. 需求分析：用户需求
2. 概念设计：需要什么数据，数据之间的联系，遵循什么约束
3. 从系统功能的角度出发，完善概念模型设计：考虑操作（增删查改）
4. 逻辑设计：概念模型映射到关系模型
5. 物理设计：为数据库设计合理空间、存储路径、索引、约束

3.2 概念模型

常用概念模型
- 实体-关系模型
- UML（统一建模语言）
- ODL（对象定义语言）：易于转化为面向对象语言编写的程序

3.3 E-R模型

①基本概念

实体：可区分的事物，可区分即可以通过唯一的标识查找、确认
属性：主码，候选码概念
联系
- 两个实体集之间：二元联系
  - 一对一联系：表1的一个属性和表2的一个属性有联系
  - 多对一联系：很多个学生都属于一个班级这样
  - 多对多联系：学生和选修课这样：一个学生选多门课，一个课被多个学生选
- 三个及以上实体集之间：多元联系
- 一个实体集内部实体的联系：一元递归联系

②表示方法

实体集和属性
- 矩形：实体，椭圆：属性
- 主码下划线标出，其余码不标出
联系
- 菱形表示实体集之间的联系
- 菱形到实体集有箭头：表示“一” eg一个班主任对应一个班级：班主任<-----对应----->班级
- 菱形到实体集无箭头：表示“多” eg多个学生属于一个班级：学生-----属于----->班级
- 联系也可以有属性
多元联系
- 如何确定谁是"一"的一方：对于n个实体集，如果从其他n - 1个实体集分别任取一个实体，如果这n - 1个实体能够确定唯一一个未选的实体E_i，则E_i为一的一方
- 比如班级，课程和教师这三个实体集之间的关系
  - 一个课程、一个班级可确定唯一教师，所以教师是"一"
  - 一个课程、一个教师，不能确定唯一一个班级，所以班级是“多”
  - 一个教师、一个班级，不能确定唯一一个课程，所以课程是“多”
一元递归联系&角色
- 是什么：一个实体集内部的不同实体之间的联系
- 例子：职工之间上下级的关系，多位下级由一位上级领导
- 一元递归联系同样有一对一，多对一和多对多的联系
- 角色的标注：一个实体（矩形）和联系（菱形）有多条线连接，需要进行角色标注

③设计实例

④设计中的常见问题

客户需求
冗余问题
属性与实体集
联系与实体集
- 联系对应于活动，一般是动词
- 实体集对应于事物，一般是名词

⑤子类实体集与弱实体集

子类实体集

是什么：当若干实体集拥有公共属性和联系时，应该设计这些公共属性和联系的超类实体集，称为父类实体集，而将父类实体集细分之后的，具有特殊联系和属性的实体集，定义为子类实体集，子类实体集继承父类实体集所有的属性和联系。
如何在E-R图表示：子类实体集与父类实体集之间通过内嵌入"is a"的等边三角形连接，其中三角形的顶点连父类实体集，三角形的底边连子类实体集
提出子类实体集的必要性：本质上是为了解决超类以及各个子类信息的空间浪费问题

弱实体集

是什么
- 弱实体集依附于其他实体集，它的码的一部分取自其所依附的实体集。
- 一个弱实体集可以依附若干实体集，此时该弱实体集的码的一部分分别取自其依附的不同的实体集
双线矩形表示弱实体集，双线菱形指向其依附的实体集
弱实体集的本质：自身的码有一部分取自其他实体集
一个实体集是否要设计成弱实体集：看这个实体集的属性是否可以唯一标识该实体，p63学生和班级的例子看懂！
例3.13没看

3.4 E-R模型 -> 关系模式

①强实体集 -> 关系模式

每一个强实体集转换为一个独立的关系模式即可
下划线表出主码

②联系 -> 关系模式

一对一联系转换为关系模式（新建 or合并）
- 法一：将联系转为一个独立的关系模式，关系模式的名称用联系的名称，属性包括联系所连接的两个实体集的主码和联系的属性
  - 两个实体集的码作为候选码，从里面随机选一个作为主码
- 法二：将联系与任意一个关系模式合并，需要在合并的模式中增加另一模式的码和联系本身的属性
- 两个实体集中参与联系的实体个数不多时，可以用法一，否则法二，一般法二居多
多对一联系转换为关系模式（新建 or合并）
- 法一：和上面基本一样，不同点在于，联系的关系模式的码选择“多”的一方的实体集的码（多对一的“多”）
- 法二：将联系合并到“多”的一方，在合并的实体集中增加一的一方的码和联系的属性即可
多对多联系转换为关系模式（only 新建）
- 只能将联系转换为一个独立的关系模式，其属性为两个实体集的码和联系的属性
- 两个实体集的码作为组合作为新模式的主码
多元联系转换为关系模式（only新建）
- 只能将联系转换为一个独立的关系模式，属性为涉及到的实体集的码和联系的属性
- 设定主码的原则：是否唯一标识，一般由“多”的一方的实体集组合而成
一元递归联系转换为关系模式
- 对于一对一和多对一，和二元是一样的两种方法*，例3.19没怎么看懂
- 多对多联系，必须建立一个独立的关系模式
子类实体集转换为关系模式
- 法一：E-R图方法
  - 超类按正常方式转换
  - 子类单独建立关系模式，包含超类的（主）码和子类特定属性
  - 子类不包含超类的非码属性
  - is a 不能作为属性转换
- 法二：面向对象方法：可能造成信息冗余存储
- 法三：使用空值组合关系
  - 对超类和子类只建立一个关系模式，里面包含超类和子类的所有属性
  - 没有子类属性的行对应值就设为null
  - 缺点：空值也会占用空间，过多空值影响查询效率
弱实体集转换为关系模式
- 包含弱实体集的属性
- 包含其依附实体集的码

第四章关系数据库设计理论

4.1 问题の提出

把一个实体集涉及到的关系都放在一个关系模式中的问题
- 数据冗余
- 数据插入异常
- 数据删除异常
一言以蔽之，就是很不方便且占空间
出现上述问题的原因：某些属性间存在着不良的依赖关系
一个好的关系模式：不会发生插入异常和删除异常，冗余也较小

4.2 规范化

1)函数依赖

X->Y，即X确定后，可确定Y的值，例如学号确定后，可以确定班号（这个学生在的班级）
平凡函数依赖：整体决定部分恒成立
非平凡函数依赖：X->Y，且Y不是X的子集
完全函数依赖：X去掉一个属性就不能->Y （Full）
非完全函数依赖：X去掉一些属性还能->Y （Part）
传递函数依赖：X->Y->Z （Transfer）

2)关系模式的码

对于关系模式R<U,F>
- U为关系模式R的属性集
- F为属性集U上的函数依赖
码的判定原则
- 若属性X仅出现在F的左部，或在左部和右部均未出现，则X是码
- 若属性X仅出现在F的右部，则X不是码
候选码和超码
- 若Y完全依赖于X，则X是Y的候选码
- 若Y部分依赖于X，则X的Y的超码
全码：整个属性组都作为码

3)范式

前提：xNF以满足(x-1)NF为前提（x > 1 且 x∈Z)
规范化：满足低级范式的关系模式通过模式分解转化为若干高一级范式的关系模式
1NF：关系模式中的每个属性都不可再分
2NF
- 每一个非码属性完全依赖于码
- 若主码只有一个属性，则该模式一定满足2NF
- 2NF解决了部分数据冗余，增删异常的问题
- 模式分解：增加新的关系模式，把部分依赖的码拆成完全依赖
3NF
- 每一个非码属性不传递依赖与码（当然也没有部分依赖）
- 模式分解：将传递依赖的后半部分单独设立一个关系模式
- 若关系R的所有属性都是主属性，则必然∈3NF
BCNF
- 在满足3NF的基础上，要求码属性也不能有部分依赖或传递依赖，码属性们必须完全依赖于其候选码
- 任何决定其他属性的组合，都必须是候选码
- 注意区分：候选码 != 码属性，码属性是出现在候选码中的属性，候选码可能只有一个，也可能是多个码属性的组合
- 例子
  - (学号，课号)->教师号
  - (学号，教师号)->课号
  - 教师号->课号
  - 码属性有：学号，课号，教师号
  - 但候选码只有（学号，课号）和（学号，教师号）
  - 而教师号->课号，但教师号不是候选码，所以不满足BCNF
多值依赖
- （课程，教师，教材）的例子，其码是（课程，教师，教材），（课程，教师）可以确定教材，但只给课程其实就可以确定教材，此时就说课程-->教材为多值依赖
- 平凡多值依赖，对于(X,Z)-->Y，且Z为空，则是X-->Y就是平凡的多值依赖
第四范式
- 对于R的每个非平凡多值依赖X-->Y,X都含义码，则R∈4NF
- 4NF不允许模式的属性间有非平凡且非函数依赖的多值依赖？？？说的啥玩意

4.3 函数依赖理论

这两节一轮不耗了，二轮直接看速成做题

第五章 SQL

5.1 表的管理

①建表

create table 教师
(
    职工号 char(4) primary key,
    姓名 varchar(20) not null ,
    性别 nchar(1) not null check (性别 in ('男','女')),	-- 表示性别只能取男/女这两个值 
    出生日期 date,
    职称 nchar(5) check ( 职称 in ('讲师','副教授','教授') ),
    专业方向 varchar(20)
)

②约束

主码约束：primary key
- 主码的取值一定非空且唯一
- 主码可以由多个属性一起组成，此时主码约束必须作为表约束单独定义（表级约束）
```
create table 选课
(
    学号 char(8) not null ,
    课号 char(4) not null ,
    primary key (学号,课号),
)
```
非空约束
- 不加not null默认可空，除了主码默认非空
- 空值！= 数字0、空格、空串

check约束 - check xxx in xxx

设定属性的取值范围
使用default设定默认值

create table 课程
(
    类别 varchar(10) check ( 类别 in ('必修','选修','专业必修') ) default '必修'
)

外码约束
- 允许外码为空
- 外码不一定是选其他表的主码，也可引用其他表的unique key，关键是要值唯一（但是这本书好像默认就是其他表主码了）
- 多个属性组合构成外码，和主码一样需要作为表约束单独定义
  - 只有一个属性作为外码，直接定义属性时加reference约束即可
  - 有多个属性作为外码，需要在属性下面，一个一个定义，格式如下
```
create table 选课
(
    学号 char(8) not null ,
    课号 char(4) not null ,
    成绩 smallint,
    primary key (学号,课号),
    foreign key (学号) references 学生(学号),
    foreign key (课号) references 课程(课号)
)
```
UNIQUE约束：取值一定唯一，但不要求非空，比如身份证号，手机号

create table 学生
(
    手机号 char(11) unique ,
)

③删除表

drop table 被删除的表

④修改表 alter table add/drop/alter column⭐

增删属性（列）
- add
- drop column

alter table 授课 add 周序 varchar(20) not null
alter table 授课 drop column 周序

添加约束

alter table 教师 add 电话 char(8) default 'no list;' check ( 电话 like '[0,9][0,9][0,9][0,9][0,9][0,9][0,9][0,9]' )

修改属性的数据类型：alter column

alter table 授课 alter column 周几 nchar(3) not null

5.2 数据更新

①数据约束

主码约束：插/改主码时，要求非空且唯一
外码约束
- 插入/修改外码时，要求外码值在主码表中有对应值
- 删除主码表中数据时，要求该数据没有被其他表的外码引用
- 省流版：改外码时，数据必须和主码表能对上；改主码时，必须保证该数据没被外码引用
check约束：插入/修改数据时，若不满足check约束，则失败
非空约束：插入/修改数据时，非空列取值 != 空
默认值default：若设置default，插入时，没赋值，则赋值default

②insert

按一个表的列顺序插入时，可以省略属性名

insert into 课程(课号,课名,学分,类别) values ('k004','高等数学','3','必修')

insert into 课程 values ('k005','线性代数','3','必修')

从另一张表选择列插入本表（用于使用旧表构造新表）

insert into 课程1 select 课号，课名 from 课程

③delete

where给出删除行的条件

delete from 选修 where 学号='s001' and 成绩=85

delete from 选修 where 学号='s003' and 成绩 is null

where结合select

delete from 授课 where 课号 in (select 课号 from 课程 where 课名='数据库技术')

删除授课表中所有行

delete from 授课

④update - set - where

基本用法
- set后面跟需要重新设定的属性，可以跟多个
- where来找需要更新的对象

update 教师 set 职称='副教授' where 姓名='郑洁如'

update 课程 set 课名='软件工程导论' where 课名='软件工程'

改字符串可以使用拼接的方法

update 课程 set 课名=课名+'导论' where 课名='软件工程'

同时修改多列

update 学生 set 姓名='王一鸣',手机='130323' where 姓名='王一明' and 手机='110'

update 课程 set 学分=2.0 where 学分=1.0 and 类别='选修'

5.3 数据查询

select [distinct]列 [as ] from 表 [where 条件] [group by 组 [having 过滤组条件]] [order by 排序]

①单表查询

基本用法：select选择要查询的列，from选择从哪个表查询
查询学生表的所有信息

select * from 学生表

查询作者的编号、姓名和出生日期

select 编号,姓名,出生日期 from 作者

distinct关键字去重：distinct修饰要查询的列

select distinct 城市 from 出版社 -- 查询出版社所在城市，结果集去掉重复

as关键字实现查询出的列重命名：as放在select之后，from之前，as重命名不需要加引号

select ISBN,出版年 as 出版信息 from 图书

where：从基本表中选择行
查询2016年9月1日之前出版的图书的ISBN、书名和出版社

select ISBN,书名,出版社 from 图书 where 出版日期 < '2016-09-01'

日期改为2016年9月一日及以后，有两种方式，注意not直接跟在where之后

select ISBN,书名,出版社 from 图书 where not 出版日期 < '2016-09-01'
select ISBN,书名,出版社 from 图书 where 出版日期 >= '2016-09-01'

查询1980年前出版的类别为“外国文学”的图书的ISBN和书名

select ISBN,书名 from 图书 where 出版年份 < 1980 and 类别 = '外国文学'

例5.30：使用括号控制运算顺序

select ISBN,书名,定价 from 图书 where (出版年份 < 1950 or 出版年份 > 1990) and 类别 = '外国文学'

例5.29：表达式between and

select * from 图书 where 定价 between 20 and 50
select * from 图书 where 定价 not between 20 and 50

例5.30：表达式 is[not] null

select ISBN,书名 from 图书 where 定价 is null

例5.31：表达式in(值的集合)

select ISBN,书名 from 图书 where 定价 in(50,55,60) and 类别 = '计算机'
select ISBN,书名 from 图书 where 定价 not in(50,55,60) and 类别 = '计算机'

例5.32：关键字like，模糊查询，查询字符串中是否包含特定的子串，如果仅知道字符串的一部分，这种查询不错。%代表任意一个字符串

select 书名,出版日期 from 图书 where 书名 like '%人工智能%'

例5.33:模式匹配[CW]代表C W其中只有之一

select 姓名,国籍 from 作者 where 姓名 like '[CW]%'

例5.34：下划线_表示任意一个字符，一个汉字 = 两个下划线

select 书名,定价 from 图书 where 书名 like 'steve_ _ _ _'

例5.35：escape指定转义字符，下式表示书名以%软工开头，再加上任意字符串，最后以%结束。可以指定任意字符为转义字符

select * from 图书 where 书名 like '$% 软工% $%' escape '$'

②排序order by

例5.36，默认升序asc，指定降序为desc

select ISBN,书名,千字数 from 图书 where 类别 = '文学' order by 千字数

例5.37

select 书名,出版社号,出版日期 from 图书 where 类别 = '计算机' order by 出版社号 desc 出版日期 asc

③聚集查询

使用聚集函数的查询称为聚集查询
例5.28：count的使用，count(列名)，放在select后，as前
- count和sum的区别：count是返回列的数值的个数，sum是计算列的数值的和

select count(*) as 作者总数 from 作者
select count(编号) as 作者总数 from 作者 -- 这两句等价

例5.39：avg的使用

select avg(定价) as 平均定价 from 图书 where 类别 = '计算机'

例5.40：distinct使用，去除类别这一列中重复的行

select count(distinct类别) as 图书类别 from 图书

group by：将所选列取值相同的归为一组
having：组内提取条件，类似where，但是group by不能搭配where使用，只能用having
where和having的区别
- WHERE 用于在查询结果返回之前，过滤表中的行。它作用于 单行数据，在 分组（GROUP BY） 和 聚合函数计算之前执行
- HAVING 用于在 分组后，过滤组。它作用于组而不是单行数据，在 分组（GROUP BY） 和 聚合函数计算之后执行。
例5.41：查询各个出版社计算机图书的数目，输出出版社号以及每一个出版社计算机图书的数目

select 出版社号,count(*) as 图书数目 from 图书 where 类别 = '计算机' group by 出版社号

例5.42

select 出版社号,类别,max(定价) as 最高定价 from 图书 group by 出版社号,类别

例5.43：having过滤组

select 出版社号,avg(定价) as 平均定价 from 图书 where 类别 = '计算机' group by 出版社号 having avg(定价)>=100

例5.44

select 语言,count(distinct 开本)as 开本总数 from 图书 where 类别 = '地理' group by 语言

④多表查询（内连接）

简约表示法：from子句写出参与连接的所有表，where语句写出连接条件以及选择条件
例5.45

select 名称 as 出版社名 from 图书,出版社 where 图书.出版社号=出版社.出版社号 and 定价<=50 and 类别='外国文学类'

例5.46

select 书名,名称 as 图书馆名,电话 from 收藏,图书,图书馆 where 收藏.图书馆号=图书馆.图书馆号 and 收藏.ISBN=图书.ISBN 
	and 书名 like '%数据库%'

例5.47

select sum(飞行距离) as 总飞行距离,日期 from 航线,航班 where 航线.航线号=航班.航线号 group by 日期 order by sum(飞行距离)desc

例5.48：使用表的别名简化书写

select 航班号,出发地,起飞时间,载客人数,制造商 from 航线 T1,航班 T2,飞机类型 T3 where T1.航线号=T2.航线号 and T2.机型=T3.机型 and 起飞之间=2018-05-01

例5.49：自连接查询，对同一个表使用多次

select * from 航线T1,航线T2 where T1.航线号='1005' and T2.飞行距离>T1.飞行距离

⑤子查询

子查询：即在一个查询中嵌套另一个查询
where语句引入子查询的方式
- 使用in
- 使用比较运算符
- 使用exists

[not]in 引入的子查询

select 列 from 表 where 表达式 in (子查询)

例5.50

select 名称,邮编,网址 from 出版社 where 出版社号 in (select 出版社号 from 图书 where 书名='数据库系统导论')
select 名称,邮编,网址 from 出版社 where 出版社号 not in (select 出版社号 from 图书 where 书名='数据库系统导论')

例5.51多层嵌套：找到表与表之间的可连接列即可

select 姓名,国籍 from 作者 where 作者编号 in  (
	select 作者编号 from 编著 where ISBN in (
    	select ISBN from 图书 where 书名 = 'Steve Jobs'
    )
)

例5.52：子查询可以返回多列，此时where子句对应的匹配对象需和返回的列个数相同

比较运算符引入的子查询

子查询返回单一数值时，直接使用比较运算符即可，与上面in对比，in可能返回多个列
例5.53

select 名称,地址 from 图书馆 where 图书馆号 = (
	select 图书馆号 from 收藏 where ISBN = (
    	select ISBN from 图书 where 定价 = (
        	select min(定价) from 图书 
        )
    )
)

例5.54：使用比较运算符>=，以及关键字all

select 航线号,出发地,到达地 from 航线 where 飞行距离 >= all(
	select 飞行距离 from 航线 where 航信号 in ('1003','1005','1009')
)

例5.55：使用关键字any

select 航线号,出发地,到达地 from 航线 where 飞行距离 >= any(
	select 飞行距离 from 航线 where 航信号 in ('1003','1005','1009')
)

使用聚合查询也可实现上述语句

select 航线号,出发地,到达地 from 航线 where飞行距离 >= (
	select min(飞行距离) from 航线 where 航信号 in ('1003','1005','1009')
)

[not]exists引入的子查询

exists用于判断是否存在满足条件的记录
查询结果集返回行时，exists为真，否则为假
返回单列多列无所谓，仅关注是否有行返回
例5.56：select的内外层的表是可以直接连接的，注意一下

select 名称,邮编,网址 from 出版社 where exists (
	select * from 图书 where 图书.出版社号 = 出版社.出版社号 and 书名 = '数据库原理'
)

例5.57

select 姓名,国籍 from 作者 where exists (
	select * from 编著 where 作者.作者编号=编著.作者编号 and exists (
    	select * from 图书 where 编著.ISBN=图书.ISBN and 书名='Steve Jobs'
    )
)

例5.58

select 职工号 from 职工 where 职务='乘务员' and not exists(
	select 航班号 from 航班 where 机长号='1001' and not exists (
    	select * from 工作 where 工作.航班号=航班.航班号 and 职工号=职工.职工号
    )
)

⑥集合查询

union：并，结果集出现在左右两个select之一
except：差，出现在左侧select但没出现在右侧select
intersect：交，出现在两侧select之中
集合运算前提：左右select得到的列数相同，数据类型相同，列名可以不同，不同时按左侧select列名
例5.59：基本用法

(select 乘客号 from 乘坐 where 航班号='CA111') union (select 乘客号 from 乘坐 where 航班号='CA222')

(select 乘客号 from 乘坐 where 航班号='CA111') except (select 乘客号 from 乘坐 where 航班号='CA222')

(select 乘客号 from 乘坐 where 航班号='CA111') intersect (select 乘客号 from 乘坐 where 航班号='CA222')

例5.60：包含连接的集合运算

(select 身份证号,姓名 from 乘坐,乘客 where 乘坐.身份证号=乘客.身份证号 and 乘坐.航班号='CA111') except (select 身份证号,姓名 from 乘坐,乘客 where 乘坐.身份证号=乘客.身份证号 and 乘坐.航班号='CA222')

例5.61：加入排序，order by一定处于查询语句的最外层，且置于最后一行

(select 身份证号,姓名 from 乘坐,乘客 where 乘坐.身份证号=乘客.身份证号 and 乘坐.航班号='CA111') except (select 身份证号,姓名 from 乘坐,乘客 where 乘坐.身份证号=乘客.身份证号 and 乘坐.航班号='CA222')
order by 姓名

⑦外连接与交叉连接查询

使用时机：不仅要查询连接条件匹配的数据，还要查询连接条件不匹配的数据
写法
- left outer join
- right outer join
- full outer join
例5.63：左外连接

select 书名 from 计算机图书 left outer join 国外出版社 on 出版社号=编号

交叉连接：即笛卡尔积，写作cross join
区分全外连接和笛卡尔积：全外连接不满足条件的写null，笛卡尔积没null，就是全部组合拼接

⑧查询小结

各子句次序

select 列 from 表 [where 条件] [group by 组 [having 过滤组条件]] [order by 排序]

第六章 SQL进阶

6.1 视图

①虚拟视图

不物理存储，仅以SQL语句的形式存在于数据库中
每次查询时都会重新执行基表查询，数据始终保持最新
适用于实时性要求高、但不频繁查询的场景。

创建视图

基本语句

create view 视图名(列名...) as 子查询

例6.1

create view 1001航班信息 as
	select 航班号,起飞时间,到达时间 from 航班 where 航线号='1001'

例6.2

create view 用户航班信息 as 
	selec 航班号,出发地,目的地,起飞时间,到达时间,类型,飞行时间,座位号 from 航班,机型,航线,乘坐 where ...

例6.3：自定义列名（不设置的话就默认原表中的列名）

create view 1001航班信息(航班,起飞,到达) as
	select 航班号,起飞时间,到达时间 from 航班 where 航线号='1001'

例6.4：基于视图定义视图（把视图当表用）

create view 航班起飞降落 as 
	select 起飞时间,降落时间 from 航班信息

基于视图的查询

和查询普通表一样的
只是数据库在执行查询语句时，会将对视图的查询转化为对基本表的查询

删除视图

drop view

drop view 1001航班信息

更新虚拟视图

内容很多，一轮先挑些重点记一下

视图可更新的条件
- 如果一个表的部分列构成了视图，插入时，未被引用的列必须满足以下之一：允许空、有默认、是标识列（自增自减）
- 定义视图的select中不能用distinct、聚集函数和group by
- with check option：更新的数据需满足条件
- 引用多个表时，无法通过delete删除数据
更新和删除的语句和表是一样的：update，delete

②物化视图

存储了查询结果，不像虚拟视图那样每次查询时都重新计算。它更像是一个缓存，适用于需要提升查询性能的场景。
基本表发生变化时，需要手动或周期性刷新以保证数据最新
适用于计算量大、查询频繁、但数据变动较少的场景。
创建物化视图

create materialized view 乘客乘坐航班信息 as 子查询

③视图作用

简化逻辑数据，从而简化查询语句
提供一定的安全保障（只给用户看你能看的）
提供一定的数据逻辑独立性：如果基本表变化，只需修改视图的定义，一般不影响应用程序
为用户定制数据

6.2 索引

①索引相关概念

是什么：KV键值对，K：表中某个or某些列的值，V：记录的存储地址PA
聚集索引&非聚集索引：区别于是否要求索引文件中索引值的顺序和表中记录的存储顺序一致
- 聚集索引：按索引顺序存储数据，适用于范围查询
- 非聚集索引：B+树，叶子结点存储数据地址，适用精准查询
- 一个表只能建一个聚集索引，可以建多个非聚集索引
唯一索引：索引项无重复性（被索引的列取值唯一），一般是建立在主码列上
复合索引：索引的列有多个，比如将（学号，姓名）两列组合建立索引
视图索引：为物化视图建立的索引
XML索引：以二进制大对象的实例存储在位图
全文索引：用于在大量文本上搜字符串（Ctrl F）

②SQL创建索引

基本语句

create index 索引名 on 表（列）

在航班表的日期列建索引

create index dateindex on 航班(日期)

再写个例子

create index ddindex on 航班(日期,起飞时间)

删除索引

drop index dateindex

③索引的选择

一般只对基本表的主码或检索频率高的列建立索引
检索频率低的列不需要建立索引

6.3 约束、断言与触发器

①约束

第五章讲了主码约束、外码约束、列级check约束等，这一章补充外码约束的维护 & 表级check约束
复习一下外码约束
- 插入/修改外码时，要求外码值在主码表中有对应值
- 删除主码表中数据时，要求该数据没有被其他表的外码引用
- 省流版：改外码时，数据必须和主码表能对上；改主码时，必须保证该数据没被外码引用
外码约束的维护（对含外码的表操作违反了上述约束该怎么办）
- 默认原则：直接拒绝此次操作
- 级联原则cascade：改被参照表的码，则参照当前码的外码也对应修改
- 置空原则set null：改被参照表的码，则参照当前码的外码置空
例子：set null和cascade放在操作后面，操作前用on连接参照列和操作

create table 图书(
	ISBN char(10) primary key,
    出版社号 char(7) references 出版社(出版社号)
    	on delete set null
    	on update cascade
)

循环约束：表1的码参照表2的码作为外码，而表2被参照的码参照表1的那个码作为外码，顾名思义循环
- 问题：如果向任一表插入数据，都会违反外码约束
- 如何解决：将对两个表的插入操作组成一个事务，设置一个时间，通知DBMS在事务执行完毕要提交时再检查外码约束
- 声明约束检查的时间
  - deferrable：将约束检查推迟到事务完成之后
  - not deferrable：在一条数据更新后就进行约束检查
- deferrable后面还可以跟的选项
  - initially deferred：检查被推迟到事务提交前执行
  - initially immediate：检查在每个语句后执行

create table 图书(
	ISBN char(10) primary key,
    出版社号 char(7) unique references 出版社(出版社号)
    	deferrable initially deferred
)

create table 出版社(
	出版社号 char(7) primary key references 图书(出版社号) 
    	deferrable initially deferred
)

表级check约束：在表的最后声明约束，称为基于行的约束
- 列级约束只能针对一列，表级约束可以约束表内多列
- 没有参照完整性，不能取代外码约束（记一下）

create table 图书 (
	ISBN char(10) primary key,
    定价 money check(定价>0 and 定价<5000),
   	千字数 smallint check(千字数>0)
    页数 smallint 
    -- 在表的最后：要求千字数>10的书定价需>=30
    	check (千字数<10 or 定价>=30)
)

约束的定义和修改
- alter table add添加约束
- alter table drop删除约束

alter table 图书 add constraint price check(千字数<10 or 定价>=30)
alter table 图书 drop constraint price

②断言

是什么：断言一般为SQL表示的逻辑表达式，当断言被定义后，每当数据有更新操作时，都会检验断言的约束条件，当条件为真时数据才更新成功
断言是单独用create建立的，约束一般是在表内就定义好的
创建断言 - 例6.23

create assertion 断言名 check(条件)

create assertion OldWorker check(
	2 <= all (
    	select sum(航班号) from 职工 T1,工作 T2 where T1.职工号=T2.职工号 group by 职工.职工号 
    )
)

例6.24

create assertion Notcollect check(
	not exists(
    	select * from 图书,收藏 where 图书.ISBN=收藏.ISBN and 定价<30
    )
)

断言和普通约束的区别
- 基于列/行的约束只对插入行或列修改操作起作用，对删除操作不起作用
- 断言对任何涉及数据库的改变都起作用
删除断言

drop assertion 断言名

③触发器

是什么：定义在特定表上，通过事件触发自动执行，事务可以回滚，基于事件（不是事务）
语法格式
- 第二行：before|after表名在触发条件前or后进行条件测试，instead of表示触发器被唤醒时，会拦截此操作而替换为另一个操作
- 第三行表示要触发的事件（插入、更新、删除）
- 第5-7行referencing子句，允许触发器引用正在被修改的行
  - 修改：old row是改前的行，new row是改后的行
  - 插入：new row as命名被插入的行，old row as 不起作用
  - 删除：old row as命名被删除的行，new row as 不起作用
- 第八行：for each row / for each statement，前者表示行级触发器，一次修改一行；后者表示语句级触发器，被触发时影响整个表，此时referencing子句使用new table as 和 old table as 命名旧表和新表
- 第九行when（可选）：表示执行动作需要满足的条件，没有where则触发器被唤醒即执行
- 第10-12行表示触发器执行的动作

create trigger 触发器名
[before|after|instead of]
[insert|update of|delete]
on 表
[referencing]
    [old row as 旧元组名 | old table as 旧表名]
    [new row as 新元组名 | new table as 新表名]
for each row | statement
[when (状态测试条件)]
begin 
	数据库操作命令
end

例6.25

create trigger PriceTrigger
after update of 定价 on 图书
referencing 
	old row as OldTuple,
	new row as NewTuple
for each row
when (OldTuple.定价 > NewTuple.定价)
brgin
	update 图书 set 定价=OldTuple.定价 where ISBN=NewTuple.ISBN
end

例6.26

create trigger NumTrigger
after update of 千字数 on 图书
referencing
	old table as OldBook,
	new table as NewBook
for each statement
when(50 > (select avg(千字数) from 图书))
begin
	delete from 图书 where (ISBN,书名,类型,语言,定价,开本,千字数,页数,出版社号) in NewBook
	Insert into 图书 (select * from OldBook)
end

6.4 存储过程

①基本概念&语句

是什么：一组SQL语句的集合，用于封装业务逻辑，类似函数，需要手动调用
过程关键字：procedure；函数关键字：function；
对于参数的描述
- in：只用于输入数据（函数的参数只能用于输入数据，因此in可省略）
- out：只用于输出数据
- inout：既用于输入数据，也用于输出数据
过程声明和函数声明

create procedure 过程名(参数)
局部声明
过程体；

create function 函数名(参数) return 类型
局部声明
函数体：

局部变量声明：declare关键字
赋值语句：set关键字
语句组：使用begin和end包含，作为一组不可拆分的语句
返回语句：return关键字
条件分支：注意最后需要end if结束分支
- if ... then
- else if ... then
- **else **
- end if
循环
- loop
```
loop 
	语句列表
end loop
```
- for、while、repeate

②存储过程的异常处理机制

错误状态：SQLSTATE xxxxx

异常处理格式

declare 下一步到哪 handler for 条件
	语句

下一步到哪（执行完异常处理后 - 理解成中断返回到哪）
- continue：执行完异常后，继续执行异常语句的下一条语句
- exit：执行完异常后，离开异常处理的begin...end代码块，执行该代码块后面的语句
- undo：同exit，加上撤销已执行的事务
保留字call调用已定义的存储过程

第七章数据库控制

7.1 安全控制

SQL中的权限
- 对基本表/视图设置的权限：select，insert，update，delete
- 其他权限：reference，usage，trigger，execute，under
  - reference设定可以被引用（作为外码）的属性
  - trigger：触发器上的权限
  - execute：指向存储过程（过程or函数）等代码的权限
  - under：创建子类的权限
权限创建：数据库创建者通过关键字authorization指定数据库用户，则用户和创建者具有相同权限

connect to student as conn1
authorization du

权限授予
- 权限列表：上面9种SQL权限
- 数据库元素：表、视图
- with grant option：授予用户授予权限的权限

grant 权限列表
on 数据库元素
to 用户列表
[with grant option]

例7.2

grant select insert
on 航班,乘客
to limei,zhangyu
with grant option

权限收回 - revoke
- 收回已授权用户的权限时，该用户授予权限的用户的权限也被顺带收回（级联，类似删文件夹，里面所有文件都删除）
- revoke关键字
```
revoke 权限列表 
on 数据库元素
from 用户列表
[cascade | restrict]
```
- cascade：级联收回权限，restrict：若该用户A将权限授予给他人过，则撤销此次收回该用户A的权利
例7.4

revoke insert on 航班,乘客 from limei cascade

收回“授予权限“这个权限

revoke grant option for 权限列表 on 数据库元素 from 用户
-- 例
revoke grant option for insert,select on 航班 from limei

7.2 事务管理 & 并发控制

①事务概念

并发：多用户，同一时间，操作相同数据的情况
事务：若干对数据库执行的操作，格式如下，以begin transaction & end transaction界定

begin transaction 
select....
insert....
end transaction

SQL server的事务：以begin transaction开始，以commit transaction或 rollback结束
- commit说明事务执行成功
- rollback说明事务执行过程出错误，回滚该事物操作

②事务性质（ACID）

原子性(Atomicity)：对数据库的所有操作必须原子执行
一致性(Consistency)：事务执行前数据库在一个一致性状态，事务执行后数据库转为另一个一致性状态（eg转账前后双方金额的变化，一致性指总金额是不变的）
隔离性(Isolation)：多个事务并发时，一个事务的执行不能干扰其他事务的执行
持久性(Durability)：一个事务完成后，对数据库的改变是永久的

③事务正确执行的准则

一致性的概念：满足数据库模式的所有约束（主码约束，外码约束，用户定义的约束）
判断事务是否正确执行的准则：事务执行前后是否都处于一致性状态
所以：事务的ACID性质之一被破坏时，事务不能正确执行
破坏ACID的因素
- 事务并发运行
- 系统故障
所以引出了事务的并发运行控制机制和故障恢复机制

④事务的并发及问题

脏读（写后读）：事务B读取了事务A修改了但未提交的数据（若事务A错误导致回滚，则事务B脏读）
不可重复读（读后写）：事务A修改数据，事务B多次读取同一数据，可能出现查询同一数据但值不同的情况
修改丢失（写后写）：两个事务同时修改同一数据库对象，造成“修改丢失”

⑤并发控制 - 锁

共享锁（S）：读锁，资源可共享
排他锁（X）：写锁，资源独占
相容矩阵的锁机制
- 当前读锁，可授予读锁
- 当前读锁，不可授予写锁
- 当前写锁，不可授予读锁和写锁

lock-S(D) -- 给数据项D加S锁
unlock(D) -- 解锁

对锁需要正确使用 - 类似PV操作的顺序很关键，不然还是会出上面的问题，所以三级锁协议出现了
三级锁协议
- 一级锁协议：写前加写锁，避免修改丢失
- 二级锁协议：在一级基础上，读前加读锁，读完立即释放（新解锁功能：避免脏读）
- 三级锁协议：在一级基础上，读前加读锁，事务结束释放（新解锁功能：避免重复读）
三级锁问题：①强理论，难实操。②无法预估运行结果稳定。所以两阶段锁协议出现了
两阶段锁协议：每个事务分两个阶段加锁和解锁
- 增长阶段 - 事务可以获得锁，但不能释放锁
- 缩减阶段 - 事务可以释放锁，但不能获得锁
两阶段协议的问题
- 活锁（饥饿）：读优先导致写饥饿，解决：先来先服务（读写均衡法）
- 死锁
死锁处理
- 死锁预防
  - 封锁法：事务在执行前封锁所有需要的数据（一次性申请完所有数据再运行）
  - 顺序封锁法：按封锁顺序对数据进行封锁
  - 抢占和回滚
    - 概念：回滚即让当前事务的状态回滚到某个安全点
    - 需要给事务一个时间戳，系统根据时间戳决定事务是等待还是回滚
    - Wait-die（等待-死去）（非抢占，关注请求后是等待还是回滚）
      - 请求数据的事务更老（时间戳更小），则等待
      - 请求数据的事务更新（时间戳更大），则回滚
    - Wound-wait（伤害-让步）（抢占，关注请求后是抢占还是等待）
      - 请求数据的事务更老（时间戳更小），则抢占数据，让被抢占事务回滚
      - 请求数据的事务更新（时间戳更大），则等待
- 死锁检测与恢复
  - 超时法：对申请锁（P操作）的事务设一个时间，若时间到还没申请到则认为死锁，回滚该事物
  - 等待图
    - 怎么画：事务T1请求的数据被T2占用，则画T1->T2
    - 如果有环，则死锁，环中的每个事务都死锁；无环则无死锁
  - 如何确定死锁检测的周期，关注这两个因素
    - 死锁发生频率
    - 受死锁影响事务的多少
  - 回滚事务的代价
    - 事务已经计算了多久，还需多久完成（时间）
    - 事务使用了多少数据，还需使用多少（数据）
    - 回滚设计多少事务

⑥并发控制的时间戳机制

时间戳生成
- 使用系统时钟按事务顺序生成，不能一个时钟周期内生成俩（必须先后顺序）
- 使用逻辑计数器，一个事务开始计数器++
时间戳操作
- WT(Q)：写数据项Q最近一次事务的时间戳
- RT(Q)：读数据项Q最近一次事务的时间戳
- C(Q)：表示修改数据项Q的事务已提交（commit）
时间戳排序协议
- 事务T_i查询数据项Q
  - 若TS(T_i) < WT(Q)，即查询前Q就已被修改，故将T_i回滚
  - 若TS(T_i) >= WT(Q)，查询成功，且RT(Q)更新为最近一次读取的时间戳
- 事务T_i修改数据项Q
  - 若TS(T_i) < RT(Q)，即写之前已经被人读了，说明写晚了，回滚写事务T_i
  - 若TS(T_i) < WT(Q)，即写之前已经被人写了，说明写完了，回滚T_i
  - 除了上面俩外，可以写，写完后更新WT(Q)修改为TS(T_i)
- 事务回滚后赋予新的时间戳并启动
- Thomas写规则：与时间戳协议不同点在于，若TS(T_i) < WT(Q)，即写之前已经被写了，则忽略此写操作（不回滚了），这样可以减少不必要的回滚次数

⑦时间戳vs锁

锁：事务等待锁时，会推迟事务，可能死锁，恢复死锁需要回滚
时间戳：事务执行不允许时，回滚
读多则时间戳好，写多则锁好

⑧SQL隔离级别

未提交读级别：可以读取别人修改但未提交的数据（隔离级别最低）
提交读级别：允许对同一数据多次查询时，得到不同结果
可重复读级别：保证多次查询结果一致
可串行化级别：强制事务串行执行（隔离级别最高）

7.3 数据库故障 & 修复

故障四种类型
- 事务故障
- 系统故障
- 介质故障
- 灾难性故障

①四种故障概述

事务故障
- 原因：(1)程序员事务写的有问题；(2)系统死锁，事务不能进行
- 一般不会信息丢失
系统故障
- 硬件故障、软件漏洞、OS漏洞、断电
- 因为修改都是先在内存修改，若写回外存前出现故障，则数据丢失
- 使用日志恢复解决系统故障
介质故障
- 比如磁盘损坏
- 解决：RIAD磁盘阵列（镜像备份）
灾难性故障

②基于日志的恢复技术

1.日志概述

日志记录的组成
- 事务标识符
- 事务的操作类型
- 数据项标识符
- 更新前数据项的值
- 更新后数据项的值
格式
- 表示事务T开始
- 表示事务T提交（至内存）
- 表示事务T终止（需要撤销修改）

<start T1>
<T1,x,10,20>
<commit T1>

<start T2>
<T2,y,20,10>
<abort T2>

常用于恢复的日志：undo日志、redo日志、undo/redo日志

2.undo日志及恢复机制

概述：将未完成的事务撤销，恢复数据库到事务执行前的状态，确保未提交的事务修改不会影响数据库的一致性
记录结构（不记录修改后的值）

<T,X,V> -- 事务，数据项，修改前的值

规则
- 若修改数据，则对应日志应先于数据更改写入磁盘
- 数据更改写入磁盘后，立即将commit记录写入磁盘
- 即顺序：先写日志，再写数据更改值，最后commit
刷新日志(flush log)：通知缓冲区管理器将日志写入磁盘
恢复方法：寻找日志记录中有但没有的事务，这就是未完成的事务，在恢复时撤销，将对应数据项恢复修改前的值
恢复规则（把例7.18看完）
- 从尾部开始逆向扫描日志
- 遇到直接过
- 否则，即遇到未完成的事务，恢复管理器将崩溃前已经发生变化的数据值改为旧值
- 最后将未完成事务T书写日志记录，然后刷新日志
检查点
- 引入的必要性：查日志总不能每次都从尾查到头，checkpoint就是把日志分段
- 恢复时只需要扫描某一检查点后面的日志记录就可以
- 设置方法
  1. 某个时间点后，停止接收新事务（这也是ckpt的致命缺点）
  2. 等当前在执行的事务commit或abort后，写入，并刷新日志
  3. 重新接收新事物
- 遇到故障扫描日志时，遇到就说明未完成的事务已经检查完毕了
- 省空间：只保存某一检查点之后的log即可
动态检查点技术 - 解决上面提到的缺点
- 生成规则
  - 写入<start CKPT(T1,T2,T3)>，其中T1-T3为正在执行的事务，并刷新日志
  - 等待T1-T3提交or终止，期间运行新事务进入
  - 当T1-T3完成后，将更新数据写盘，写记录
- 如何利用动态检查点进行数据恢复
  - 还是从后向前扫描
  - 如果先扫描到，则需要扫描到与其对应的为止，之前的记录可抛弃
  - 如果先扫描到<start CKPT(T1,T2,T3)>，则需要扫描到前一个

3.redo日志及其恢复机制

确保已提交事务的修改不丢失
记录结构（不记录修改前的值）

<T,A,V> -- 事务，数据项，修改后的值

规则
- 先写日志记录和commit到磁盘
- 再将数据修改写到磁盘（log，commit - update）
- （对比undo是log - update - commit）
恢复方法
- 从后向前扫描，对遇到的<T,A,V>记录，如果T未完成（无）则忽略，如果已完成，则将新值S写给A
- 对每一个未完成的事务T，在日志写入
检查点生成规则
1. 写入记录<start CKPT(T1,T2,T3)>，并刷新日志，其中T1-T3为未提交或未将数据更新写盘的事务
2. 写入记录，将所有已经commit但还没有将数据写盘的事务进行数据写盘
后面的先跳过

posted @ 2025-04-15 12:21 mumumu1 阅读(58) 评论(0) 收藏举报

刷新页面返回顶部

数据库相关概念及SQL用法

第一章 绪论

1.1 数据库系统概述

数据库管理技术的发展

数据库

数据库管理系统

1.2 数据模型

1.3 数据库系统结构

①体系结构

②模式结构 & 数据独立性

第二章 关系模型

2.1 关系模型的数据结构

2.2 关系代数

第三章 数据库设计

3.1 数据库设计过程

3.2 概念模型

3.3 E-R模型

①基本概念

②表示方法

③设计实例

④设计中的常见问题

⑤子类实体集与弱实体集

子类实体集

弱实体集

3.4 E-R模型 -> 关系模式

①强实体集 -> 关系模式

②联系 -> 关系模式

第四章 关系数据库设计理论

4.1 问题の提出

4.2 规范化

1)函数依赖

2)关系模式的码

3)范式

4.3 函数依赖理论

第五章 SQL

5.1 表的管理

①建表

②约束

③删除表

④修改表 alter table add/drop/alter column⭐

5.2 数据更新

①数据约束

②insert

③delete

④update - set - where

5.3 数据查询

①单表查询

②排序order by

③聚集查询

④多表查询（内连接）

⑤子查询

[not]in 引入的子查询

比较运算符引入的子查询

[not]exists引入的子查询

⑥集合查询

⑦外连接与交叉连接查询

⑧查询小结

第六章 SQL进阶

6.1 视图

①虚拟视图

创建视图

基于视图的查询

删除视图

更新虚拟视图

②物化视图

③视图作用

6.2 索引

①索引相关概念

②SQL创建索引

③索引的选择

6.3 约束、断言与触发器

①约束

②断言

③触发器

6.4 存储过程

①基本概念&语句

②存储过程的异常处理机制

第七章 数据库控制

7.1 安全控制

7.2 事务管理 & 并发控制

第一章绪论

第二章关系模型

第三章数据库设计

第四章关系数据库设计理论

第七章数据库控制