数据库系统概论(笔记)

发表于 2022-12-05 更新于 2024-02-28 分类于分享

https://bytodance.feishu.cn/docx/OfKudqqCvochuwx38XqcqCBmnob

数据库系统概论(笔记)

1.绪论

1.1数据库的四个基本概念：

数据：描述事物的符号记录。
数据库：概括地讲，数据库数据具有永久存储、有组织和可共享的三个基本特点。
- 严格地讲：数据库是⻓期储存在计算机内、有组织的、可共享的⼤量数据的集合。数据库中的数据按⼀定的数据模型组织、描述和储存，具有较⼩的冗余度、较⾼的数据独⽴性和易扩展性，并可为各种⽤户共享。
数据库管理系统：是位于⽤户和操作系统之间的⼀层数据管理软件，和操作系统⼀样是计算机的基础软件。
- 主要功能：
  - 1、数据定义功能
  - 2、数据组织、存储和管理
  - 3、数据操纵功能
  - 4、数据库的事务管理和运⾏管理
  - 5、数据库的建⽴和维护功能
  - 6、其他功能（通信功能，数据转换功能，互访和互操作功能等）
数据库系统：是由数据库、数据库管理系统（及其应⽤开发⼯具）、应⽤程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统。

在这里插入图片描述

1.2数据库的发展阶段:

⼈⼯管理阶段：数据不保存、不共享，不具有独⽴性。
⽂件系统阶段：可保存，但共享性差，冗余度⼤，独⽴性差。
数据库管理系统：数据库系统的出现使信息系统从以加⼯数据的程序为中⼼转向围绕共享的数据库为中⼼的新阶段
- 实现整体数据的结构化
- 数据的共享性⾼、冗余度低且易扩充：数据共享可以⼤⼤减少数据冗余，节约存储空间
- 数据独⽴性⾼：
  - 物理独⽴性：是指⽤户的应⽤程序与数据库中数据的物理存储是相互独⽴的。
  - 逻辑独⽴性：是指⽤户的应⽤程序与数据库的逻辑结构是相互独⽴的。

数据由数据库管理系统统⼀管理和控制

1.3 数据模型

两类数据模型：概念模型+逻辑模型和物理模型
（⼈们⾸先将现实世界抽象为信息世界，然后将信息世界转还为机器世界）

概念模型
也称信息模型，它是按⽤户观点来对数据和信息建模，主要⽤于数据库设计。
- 基本概念：
- 实体：客观存在并可相互区别的事物。⼈
- 属性：实体所具有的某⼀特性。⼈的⿐⼦嘴巴
- 码：唯⼀标识实体的属性。学号
- 实体型：⽤实体名及其属性集合来抽象和刻画同类实体。含有⿐⼦和嘴巴的⼈
- 实体集：同⼀类型实体的集合。学校
- 联系：实体之间的联系通常是指不同实体集之间的联系。实体之间的联系有⼀对⼀、⼀对多和多对多等多种联系。

概念模型的⼀种表示⽅法：实体-联系⽅法。⽤E-R图来描述现实世界的概念模型，E-R⽅法也称为E-R模型。

逻辑模型：它是按计算机系统的观点对数据建模，主要⽤于数据库管理系统的实现。

包括:
- 层次模型
- ⽹状模型
- 关系模型(第三章我们主要讲它)

物理模型：是对数据最底层的抽象，它描述数据在系统内部的表示⽅法和存取⽅法，或在磁盘或磁带上的存储⽅式和存取⽅法，是⾯向计算机系统的。

联系:
- 实体之间的联系通常是指不同实体集之间的联系。实体之间的联系有⼀对⼀、⼀对多和多对多等多种联系。
- 实体内部的联系通常是指实体各属性之间的联系

1.4 ER图

在这里插入图片描述

ER图分为实体、属性、关系三个核⼼部分。实体是⻓⽅形体现，⽽属性则是椭圆形，关系为菱形。

1.5 常用的数据模型

常⽤的数据模型：层次模型，⽹状模型，关系模型，
⾯向对象数据模型，对象关系数据模型，半结构化数据模型。
层次模型：
1. 有且只有⼀个结点没有双亲结点，这个结点称为根结点。
2. 根以外的其他结点有且只有⼀个双亲结点。

在这里插入图片描述

⽹状模型：
条件：
- 允许⼀个以上的结点⽆双亲。
- ⼀个结点可以有多于⼀个双亲。

在这里插入图片描述

关系模型：
- 关系：⼀个关系对应通常说的⼀张表。
- 元组：表中的⼀⾏。
- 属性：表中的⼀列。
- 码：也称码键，表中的某个属性组。（主键）
- 域：是⼀组具有相同数据类型的值的集合。
- 分量：元组中的⼀个属性。

1.6 数据库系统的结构

模式数据库中全体数据的逻辑结构和特征的描述，它仅仅涉及型的描述，不涉及具体的值。其⼀个具体值称为模式的⼀个实例。

模式是相对稳定的，实例是相对变动的。
三级模式结构：外模式+模式+内模式
外模式：也称⼦模式或⽤户模式，它是数据库⽤户（包括应⽤程序员和最终⽤户）能够看⻅和使⽤的局部数据的逻辑结构和特征的描述，是数据库⽤户的数据视图，是与某⼀应⽤有关的数据的逻辑表示。
模式：也称为逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有⽤户的公共数据
内模式：也称存储模式，⼀个数据库只有⼀个内模式。它是数据物理结构和存储⽅式的描述，是数据在数据库内部的组织⽅式。

⼆级映像：

外模式/模式映像：当模式改变时，由数据库管理员对各个外模式/模式映像作相应改变，可以使外模式保持不变。应⽤程序不必修改。保证了数据与程序的逻辑独⽴性。
模式/内模式映像：当数据库的存储结构改变时，有数据库管理员对模式/内模式作相应改变，可以使模式保持不变，从⽽应⽤程序也不⽤改变。保证了数据与程序的物理独⽴性。
数据与程序之间的独⽴性使得数据的定义和描述可以从应⽤程序中分离出去。
另外，由于数据的存取由数据库管理系统管理，从⽽简化了应⽤程序的编制，⼤⼤减少了应⽤程序的维护和修改。

在这里插入图片描述

1.7 数据库系统的组成

硬件平台及数据库：⾜够⼤内存、磁盘或磁盘阵列等设备，较⾼的通道能⼒以提⾼数据的传
软件：数据库管理系统，⽀持数据库管理系统运⾏的操作系统，具有与数据库接⼝的⾼级语⾔及其编译系统，以数据库管理系统为核⼼的应⽤开发⼯具，为特定应⽤环境开发的数据库应⽤系统。
⼈员：开发、管理和使⽤数据库的⼈员主要包括数据库管理员、系统分析员和数据库设计⼈员、应⽤程序员和最终⽤户。

数据库管理员职责：决定数据库中的信息内容和结构、决定数据库的存储结构和存取策略、定义数据的安全性要求和完整性约束条件、监控数据库的使⽤和运⾏、数据库的改进和重组、重构。

2. 关系数据库

2.1 关系模式(了解即可)

关系模式：对关系的描述
那么什么是关系？
- 关系是⼀张表，⼀张⼆维表
关系有哪些需要描述？
- 关系中有哪些属性
- 这些属性来⾃哪些域
- 属性与域之间的映射关系
可以形象地表示为

R(U,D,DOM,F)
R:关系名
U:所有属性名
D：属性来⾃哪些域
DOM：属性和域的映射
F:属性间的依赖关系

关系数据库：关系数据库也有关系数据库模式

2.2 关系操作 (后面会讲)

关系操作：
- 插⼊、查询、删除、修改
- 其中查询操作分为：选择、投影、连接、除法、并、差、交、笛卡尔积
- 查询操作的基本操作：选择、投影、并、差、笛卡尔积

2.3 关系语言的分类

关系代数语言
- 关系代数是⼀种抽象的查询语⾔，它⽤对关系的运算来表达查询。
- 三⼤要素：运算对象（关系）、运算符（集合运算符和专⻔的关系运算符）和运算结果（关系）

关系代数运算：5种基本运算：并，差，笛卡⼉积，选择，投影及交，连接，除。
- 交
- 并
- 差
- 笛卡尔积
- 选择：选出满足关系的行
- 投影Π：多列变少列
- 连接
- 自然连接: 把共同属性进行等值连接
  - 写出全部字段名，找到相同字段名（列名），寻找相同属性左边1a，1a，2b，右边1a，1a，2b，笛卡尔积拼接，左第一个1a-右第一个1a，左第一个1a-右第二个1a，左第二个1a-右第一个1a，左第二个1a-右第二个1a，2b-2b
- 悬浮元组（Dangling tuple）：没用过的行
  - 两个关系R和S在做自然连接时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，
  - 从⽽造成R中这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组。
- 外连接（Outer Join）：左+右
  - 如果把悬浮元组也保存在结果关系中，⽽在其他属性上填空值(Null)，就叫做外连接
- 左外连接(LEFT OUTER JOIN或LEFT JOIN)：将R里面没用的行写到右边，然后剩下的值填Null
  - 只保留左边关系R中的悬浮元组
- 右外连接(RIGHT OUTER JOIN或RIGHT JOIN)：将S里面没用的行写到右边，然后剩下的值填Null
  - 只保留右边关系S中的悬浮元组

2.4关系的完整性

实体完整性
- 主码唯一且非空
参照完整性
- 外码要么为空,要么对应另一表的主码
用户定义完整性
- 你自己写的要完整,比如学号多少,姓名,班级,专业之类

3. SQL语言

3.1 SQL的基本概念

SQL是Structured Query Language的缩写，意思是结构化查询语⾔，是⼀种在数据库管理系统中查询或对数据库⾥⾯的数据进⾏更改的语⾔
而数据库管理系统呢,主要是
- 不同数据库管理系统的SQL语言略有不同

数据定义语⾔DDL（Data Definition Language）
- SQL数据定义语⾔主要⽤来定义逻辑结构，包括定义基表，视图和索引。
- 删除表定义表修改表
数据查询语⾔DQL（Data Query Language）
- SQL的数据查询语⾔主要⽤来对数据库中的各种数据对象进⾏查询。
数据操纵语⾔DML（Data Manipulation Language）
- SQL的数据操纵语⾔，⽤于改变数据库中的数据，包括插⼊，删除，修改
数据控制功能DCL（Data Control Language）
- SQL的数据控制语⾔，对表和视图的授权，完整性规则的描述以及事务开始和结束等控制语句。

3.2 SQL语言的特点

综合统⼀(独⽴完成数据库⽣命周期中的全部活动，包括定义关系模式、录⼊数据、建⽴数据库、査询、更新、维护、数据库重构、数据库安全性控制等⼀系列操作)
⾼度⾮过程化 (⽤户只需提出“做什么”，⽽不必指明“怎么做”。)
⾯向集合的操作⽅式 (SQL采⽤集合操作⽅式)
以同⼀种语法结构提供两种使⽤⽅式(SQL既是⾃含式语⾔，⼜是嵌⼊式语⾔。SQL语句能够嵌⼊到⾼级语⾔程序中)
语⾔简洁，易学易⽤ (SQL语⾔语法简单，接近英语⼝语，因此容易学习，也容易使⽤。)

3.3 SQL的基本语法

3.3.1SQL的数据类型(变量类型)

char(n),character(n)				长度为n的定长字符串
varchar(n),charactervarying(n)		最大长度为n的变长字符串
clob								字符串大对象
blob								二进制大对象
int,integer							4字节长整数
smallint							2字节短整数
bigint								8字节大整数
numeric(p,d)		 				定点数,由p位数字组成,小数点后面有d位数字
decimal(p,d),dec(p,d)				同numeric
real								取决于机器精度的单精度浮点数
double precision					取决于机器精度的双精度浮点数
float(n)							可选精度的浮点数,精度至少位n位数字
boolean								逻辑布尔值
data								日期
time								时间
timestamp							时间戳类型
interval							时间间隔类型

3.3.2 模式定义&删除

模式定义

1	CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

模式定义+视图

1	create schema <模式名> authorization <用户名> [<表定义子句>\|<视图定义子句>\|<授权定义子句>];

授权我们后面讲

1	create schema "Learn" authorization zhangxu create table user( id int primary key , age int ,name varchar(255));

模式删除

1	DROP SCHEMA <模式名> <CASCADE\|RESTRICT>;

删除模式, 其中 CASCADE 和RESTRICT 必须⼆选⼀,
- CASCADE是(级联)
  - 删除模式的同时也把该模式的所有数据库对象删除.
- RESTRICT(限制)
  - 如果该模式下有下属对象,⽐如表视图就拒绝这个删除语句的执⾏

3.3.3 表的定义和删除和修改

定义

1	create table表名(字段名类型字段约束，字段名类型字段约束，字段名类型字段约束）//创建表

例如

create table user(
 
name varchar(20),
 
age int,
 
sex char(1)
 
)

删除

1	drop table <表名> <CASCADE\|RESTRICT>;

CASCADE呢是如果表有外键,视图触发器的话,也会强⾏删除, RESTRICT恰恰相反
修改

1	alter table <表名> [add [column] <新列名><数据类型>[完整性约束]][add <表级完整性约束>]

例如：为test表添加email属性，为可变字符串类型，最多255个字符

1	alter table test add email VARCHAR(255);

3.3.4 索引的建立与修改&删除

数据量⽐较⼤的时候, 查询耗时间⻓, 建⽴索引可以有效减少消耗时间
索引可以建⽴在⼀列或者多列上
建立:

1	create [unique][cluster] index <索引名> on <表名>(<列名>[<次序>]...);

CLUSTER :聚簇索引:物理顺序与索引的逻辑顺序相同。⽐如买书，C区必须在A区B区后面
UNIQUE 唯⼀索引,
例如

1	CREATE UNIQUE Stusno INDEX ON Student(Sno);

修改索引

1
2
3

ALTER INDEX<旧索引名>RENAME TO<新索引名>;
例子：将SC表的SCno索引名改为SCSno。
ALTER INDEX SCno RENAME TO SCSno;

删除:

1
2
3

DROP INDEX<索引名>;
[例]删除Student表的Stusname索引。
DROP INDEX Stusname;

3.4 SQL 查询语法(举例说明)

3.4.1 查询所有 & 查询部分列

1
2
3

select * from Student;
 
等价于select Sno,Sname,Ssex,Sage,Sdept from Student;

只想看个别列

1	select Sno from SC;

3.4.2 起别名

给列起别名
select username 名字 from user;
等价于
select username as 名字 from user;
 
 
给表起别名
select username from user u;
等价于
select username from user as u;

3.4.3 结果去重

1 2	select distinct title from edu_course; 查询不重复标题从表中

3.4.4 查询结果加条件

首先我们看下条件

select * from edu_course where price>2; 
查询  所有 来自 表        要求   价格高于2

select * from edu_course where price between 21 and 50; 
查询  所有 来自 表        要求   价格在21和50之间

select * from edu_course where teacherid in (1,2,3);
查询  所有 来自 表        要求   老师id在123集合之间

select * from edu_course where title like '%速成';
查询  所有 来自 表        要求   标题   想    任何几个字符+速成
 
%    任何几个字符
_    任意1个字符

select * from edu_course where title like '_速成';
查询  所有 来自 表        要求   标题   想    1个字符+速成
 
%    任何几个字符
_    任意1个字符

因为% 可以代替多个字符, 但是_ 只能替换⼀个字符


select * from edu_course where (price>2 and teacher_id=1) or (title= "mysql速成');
查询  所有 来自 表        要求   （价格>2   并且 老师id=1）  或者 （标题为mysql速成）

select * from edu_course ORDER BY lesson_num;
查询  所有 来自 表        按课程数量排序
 
select * from edu_course ORDER BY lesson_num DESC;
查询  所有 来自 表        按课程数量倒序排序

3.4.5 聚集函数的使用

AVG() - 返回集合的平均值。
COUNT() - 返回集合中的项目数。
MAX() - 返回集合中的最大值。
MIN() - 返回集合中的最小值
SUM() - 返回集合中所有或不同值的总和。

select count(title)       from  edu_course;
查询   title列中的属性个数 来自   表
包括重复，不含空NULL

select count(distinct title)               from  edu_course;
查询   title列中不重复的属性个数 来自   表
不包括重复，不含空NULL,加上DISTINCT是去除列⾥⾯重复的  来计算 

select avg(price) from  edu_course;
价格平均值

select max(price) from  edu_course;
价格最大值

select min(price) from  edu_course;
价格最小值

3.4.6 分组查询

GROUP BY分组 ,如果要进行筛选,请使用Having

select title FROM edu_course GROUP BY title 
按照title进行分组，相同title一组

select title FROM edu_course GROUP BY title having title='SSM速成';
按照title进行分组，相同title一组,查询title为SSM速成的一组

3.4.7 连接

等值非等值连接 , 以where为关键词

1 2	select t.* ,c.* from edu_teacher t ,edu_course c where t.id=c.teacher_id; 查询 t表全部和c表全部从 edu_teacher表别名t edu_course表别名c 连接 t的id和c的tid同

自身连接:⼀个表与⾃身连接,为本身这个表起两个别名,然后进⾏操作

1	Select c1.version,c2.view_count from edu_course c1 ,edu_course c2 where c1.version=c2.view_count;

外连接:把被舍弃的值, 也保留在结果中,但是要加NULL

select * from edu_course ec left outer join edu_course_description ecd on ec.id=ecd.id;
 
select * from edu_course ec right outer join edu_course_description ecd on ec.id=ecd.id;
 
select * from edu_course ec inner join edu_course_description ecd on ec.id=ecd.id;

3.4.8 多表查询

两个表或者以上的连接

1
2

select c.id ID ,c.title 课程名,cp.title 章节,cd.description 课程措述 from edu_course c ,edu_chapter cp,edu_course_description cd where c.id=cp.course_id and c.id=cd.id
查询   c表中id 别名ID，c表中title 别名课程名，cp表中title别名章节，cd表中的description别名课程措述    从edu_course表，别名c ,edu_chapter表，别名cp,edu_course_description表别名cd，连接 c.id=cp.course_id 和 c.id=cd.id

3.4.9 嵌套查询：查询语句里面嵌套了查询

select c.title 课程名,c.price 价格 from edu_course c where c.title in (select c2.title from edu_course c2 where c2.title="mysql速成");
 
select c2.title from edu_course c2 where c2.title="mysql速成"
在c2表中查出title="mysql速成"的表
 
select c.title 课程名,c.price 价格 from edu_course c
从c表中查出课程名和价格
 
where c.title in()
要求从c表中查出课程名在c2表中查出title="mysql速成"的表里面

3.4.10 带有ANY ALL的子查询

select c.title课程名,c.price价格 from edu_course c where c.price >ALL(select c2.price from edu_course c2 where c.price between 1 and 20) ;
 
1.在edu_course表中查出价格在1到20之间的价格，组成一张表
2.edu_course中查询价格高于（）表中所有价格的行，组成一张表
3.2中表只保留title和price


select c.title 课程名,c.price价格 from edu_course c where c.price >ANY (select c2.price from edu_course c2 where c2.price between 22 and 24);
 
1.在edu_course表中查出价格在22到24之间的价格，组成一张表
2.edu_course中查询价格高于（）表中最低价格的行，组成一张表
3.2中表只保留title和price

3.4.11 带EXISTs 的子查询

not exists 如果后⾯的⼦查询没有值,返回1 否则为0
exists 相反如果后⾯的⼦查询有值,返回1 否则为0
例如:查询这样的学生，没有一门课程是他不选修的（他选修了所有课）。其SQL语句如下:

select Sname from Student where NOT EXISTS 
(select * from course where NOT EXISTS 
(select * from SC where Sno=Student.Sno and Cno=Course.Cno));
 
查询他选择的所有的课程门数SC        select * from SC 。。。
查询他没有选择的所有的课程门数SC    NOT EXISTS (select * from SC 。。。
查询所有课程                      (select * from course where 。。。o));
查询没选的所有课程                 NOT EXISTS (select * from course 。。。no));

最里面的子查询是这个人没选一门课程
外面的子查询是没有这样的课程

3.4.12 集合查询

并UNION

1	select * from student where sdept = "cs" union select * from student where sage <= 19;

交 INTERSECT

1	select * from student where sdept = "cs" intersect select * from student where sage <= 19;

差 EXCEPT

1	select * from student where sdept = "cs" except select * from student where sage <= 19;

3.5 表中数据的增删改及视图的增删改查

3.5.1 数据插入修改删除

插入

insert into 表名 [( 字段列表 )] values( 值列表 ...);
 
 //标准添加（指定所有字段，给定所有的值）
 insert into stu(id,name,age,sex,classid) values
 ( 1 , 'zhangsan' , 20 , 'm' , 'lamp138' );
 //指定 部分 字段添加值
 insert into stu(name,classid) value( 'lisi' , 'lamp138' );
 //不指定 字段添加值
 insert into stu value( null , 'wangwu' , 21 , 'w' , 'lamp138' );
 //批量 添加值
 insert into stu values
( null , 'zhaoliu' , 25 , 'w' , 'lamp94' ),
( null , 'uu01' , 26 , 'm' , 'lamp94' ),
( null , 'uu02' , 28 , 'w' , 'lamp92' ),
( null , 'qq02' , 24 , 'm' , 'lamp92' ),
( null , 'uu03' , 32 , 'm' , 'lamp138' ),
( null , 'qq03' , 23 , 'w' , 'lamp94' );

修改

1
2
3

update 表名 set 字段1= 值1, 字段2= 值2, 字段n= 值n... where 条件
-- //将 id 为 11 的 age 改为 35 ， sex 改为 m 值
update stu set age=35 ,sex='m' where id= 11 ;

删除

1
2
3

格式： delete from 表名 [where 条件 ]
//删除stu表中id值为100的数据
delete from stu where id=100;

3.5.2 创建视图

视图(VIEW)也被称作虚表，即虚拟的表，是⼀组数据的逻辑表示,
其本质是对应于⼀条SELECT结果集被赋予⼀个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的⼀个查询语句，当基表数据发⽣变化，视图中的数据也会发生变化
⽬的: ⽅便,简化数据操作
当我们业务需求要查出多张表的数据,这时我们可能会关联多张表查询处理.
如果这个查询sql复杂的话也影响了查询效率.
这个时候我们就可以创建视图,查询时候只需要 select * from view 就ok啦~

1
2
3

create view <视图名> [(<列名>...)] as <子查询> [with check option];\
例如:
create view IS_student as select sno,sname,sage from student where sdept ='IS' with check option;

加了WITH CHECK OPTION可以防⽌⽤户对不属于试图范围的操作进⾏拒绝
删除视图:

1	drop view <视图名>[cascade];

查询视图:

1 2	例如: select sno,sage from is_student where sage<20;

更新视图:
- 视图是不存在的,所以我们对视图的操作,最终要反映到对基本表的操作

例如:
update is_student set sname ='liu' where sno = '111';
转换后的更新语句为:
update student set sname = 'liu' where sno = '111' and sdept = 'IS';

4. 数据库的安全性

4.1 ⽤户身份鉴别

概述:
- 不安全因素
  1. ⾮授权对数据库的恶意存取和破坏
  2. 数据库中重要的数据泄露
  3. 安全环境的脆弱性
- 因此我们需要对数据库进行安全性控制

静态⼝令鉴别

动态⼝令鉴别

⽣物特征鉴别

智能卡鉴别

4.2 ⾃主存取控制⽅法

字⾯意思就是我们⽤户可以⾃定义和分配其他⽤户的操作
主要通过grant和revoke来进⾏控制
由两个元素构成: 数据库对象和操作权限
我们定义⽤户的存取权限为授权

1
2
3

grant <权限> on table <表名> to <⽤户>;
 
revoke <权限> on table <表名> from <⽤户>;

主要存取权限
Reference权限代表是否允许创建外键：用户在创建表时候，没有这个权限，无法创建外键

4.2.1 授权 Grant

Grant单词翻译:
- vt.承认；同意；准许；授予
- n.拨款；补助⾦；授给物（如财产、授地、专有权、补助、拨款等）

1	Grant <权限> on 表名[(列名)] to ⽤户 With grant option

授权命令是由数据库管理员使⽤的，若给⽤户分配权限时带 With grant option⼦句，则普通⽤户获权后,可把⾃⼰的权限授予其他⽤户。

4.2.2 回收权限 revoke

1	REVOKE <权限> ON <数据对象> cascade

cascade 加了，在回收的时候，会回收他赋予其它人的权限
revoke翻译:vt.撤销，取消；废除

1
2
3

//授予用户user1拥有对表student的查询权限
Grant select on student to user1 with grant option
revoke select on student from user1

4.3 数据库角色

角色指的是一类人，比如说CEO、总监、普通职员,可以给一类人授权

角色的创建:

1
2
3

create role <角色名>
 
create role CEO

角色授权

grant <权限> on <对象类型> 对象名 to <角色1>,<角色2>
 
grant select on Student to CEO

把角色授权个其他用户或者角色

1
2
3

grant <角色>to 角色或者用户名字 [with admin option]
 
grant CEO to lihua

如果加上with admin option ,意味着，这个用户还可以吧这权限授予给其他角色或者用户

角色权限的收回

1
2
3

revoke<权限> on<对象名>from 角色名字
 
revoke select on Student from CEO

4.4 视图机制

为不同的用户定义不同的视图，把不需要的数据给隐藏起来，这样用户就不会进行误操作

create view CS_Student as select* from student where sdept='IS';
 
grant select on CS_Student to 王平;
 
grant all privilegeson CS_Student to 王平;

4.5 审计:

把对数据库的所有操作都记录到审计日志中，然后就可以通过日志审查这个里面是否有一些非对修改SC数据的操作进行审计

1	audit update on sc;

取消对SC表的一切审计

1	noaudit update on sc;

数据加密:通过一些加密算法，把明文变成密文，这样别人就无法查看

5. 数据库的完整性

正确性
- 符合现实世界的描述
相容性
- 同⼀对象在不同表⾥⾯是符合逻辑的
维护完整性
- 提供定义完整性约束条件的机制
- 提供完整性检查的⽅法
- 进⾏违约处理

5.1 三大完整性:

5.1.1 实体完整性

主码唯⼀,且⾮空
⽐如我的地址,年龄在两个表⾥⾯都应⼀致

1
2
3

create table course (id int not null ,time varchar(255),primary key(id));
 
create table course (id int not null primary key,time varchar(255));

5.1.2 参照完整性

外码的要么没有,要么只有⼀个

1 2	create table course (id int,time varchar(255),title varchar(255),teacher_id int, primary key(id),foreign key (id) references course_description(course_id);

course表有id

course_description 表有id和course_id

查询：

select * from course c,course_description cd where c.id=cd.course_id

5.1.3⽤户定义完整性

⾮空

create table student(
no char(9) primary key,
age int not null
 );

列值唯⼀

create table student(
 no char(9) primary key,
age int unique
);

满⾜某⼀个条件表达式 check来写

create table student(
no char(9) primary key,
sex char(2) check (sex in ('男','⼥')),
age int not null
);

5.2 断言:

1	create assertion <断⾔名> <check⼦句>

其中check字句与where⼦句的表达式类似
删除断⾔名称:

1	drop assertion 断⾔名字;

5.3 触发器

触发器也叫做事件->条件->动作规则.
当对⼀个表增删改时候, 对触发器⾥⾯的条件进⾏检查,
如果成⽴,就执⾏触发器⾥⾯的动作.否则不执⾏⾥⾯的动作.
添加触发器

create trigger <触发器名>				/*每当触发事件发生时,该触发器被激活*/
{before |after} <触发事件> on <表名>		/*指明触发器激活时间是在执行触发事件前或后*/	
referencing new|old row as<变量> 		/*referencing指出引用的变量*/
for each{row|statement}					/*定义触发器的类型,指明动作体执行的频率*/
[where <触发条件>]<触发动作体>			/*仅当触发条件为真时才执行触发动作体*/

删除触发器:

1	drop trigger <触发器名> on <表名>

7. 关系数据理论

7.1 范式分类

设计关系数据库时，遵从不同的规范要求，这些不同的规范要求被称为不同的范式，
各种范式呈递次规范，越⾼的范式数据库冗余越⼩。
⽬前关系数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、
巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，⼜称完美范式）。
⼀般来说，数据库只需满⾜第三范式(3NF）就⾏了。

7.2 依赖

1 2	X→Y,但Y⊈X,则称X→Y是非平凡的函数依赖 X→Y,但Y⊆X,则称X→Y是平凡的函数依赖

7.3 候选码

概念:可以推出所有属性
如何选出候选码?
- 只出现在左边的一定是候选码
- 只出现在右边的一定不是候选码
- 左右都出现的不一定
- 左右都不出现的一定是候选码
- 再求确定的候选码的闭包,如果可以推出全部,那么当前确定的就是候选码
- 否则你要吧每一个可能的值放进当前确定的候选码里面进行求闭包

⽐如
R<U,F>,        U(A,B,C,D,E,G),         F={AB-->C,        CD-->E,        E-->A,        A-->G) ,求候选码:
F中只出现在左边的一定是候选码：B⼀定是候选码 ，D⼀定是候选码
F中只出现在右边的一定不是候选码：G⼀定不是候选码
F中左右都出现的不一定：A不⼀定C不⼀定 E不⼀定
再求确定的候选码的闭包：BD->啥也推不出来,所以要把每⼀个可能的求闭包
(BDA)+= 可推出C E A G所以可以推出ABCDEG
(BDC)+= 可推出 E A G所以可以推出ABCDEG
(BDE)+= A G C可以推出ABCDEG
那么他的候选码最终是{ (BDA),(BDC),(BDE) };

7.4 超码:能表示出所有属性的集合

比如(BDA),(BDC),(BDE)BDCA BDEA ABCDE
候选码是最小的超码.
主码:从候选码里面任意挑出一个作为主码
主属性:包含在所有候选码的属性比如ABCDE
非主属性:不包含在候选码中的属性,上题为G
全码:所有的属性都是主码

7.5三大范式

1NF : 所有字段值都是不可分解的原子值
2NF : 不包含非主属性对码的部分函数依赖,一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
3NF : 不包含非主属性对码的传递函数依赖,确保数据表中的每一列数据都和主键直接相关，而不能间接相关
BCNF : 消除每一属性对候选键的传递依赖,BCNF是修正的第三范式

7.5.1 1NF

在这里插入图片描述

比如某些数据库系统中需要用到“地址”这个属性，
本来直接将“地址”属性设计成一个数据库表但是如果系统经常会访问“地址”属性中的“城市”部分，
那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，
这样在对地址中某一部分操作的时候将非常方便

7.5.2 2NF

一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。
所以在这里违反了第二范式的设计原则

在这里插入图片描述

7.5.3 3NF

⽐如在设计⼀个订单数据表的时候，可以将客户编号作为⼀个外键和订单表建⽴相应的关系。
⽽不可以在订单表中添加关于客户其它信息（⽐如姓名、所属公司等）的字段

7.5.4 BCNF

在这里插入图片描述

存在关系∶
- 书号-书名
- (书名、作者)→书号
上述关系存在传递依赖，不能是BCNF

7.5 公理系统

Armstrong公理系统：一套推理规则，模式分解算法的理论基础
用途：1.求给定关系模式的码 2.从一组函数依赖求得蕴涵的函数依赖
自反律，增广律，传递律，可得到合并规则，伪传递规则，分解规则
自反律，增广律，传递律称为Armstrong公理系统，该系统是有效的，完备的

1
2
3

合并规则:由X→Y,X→Z,有X→YZ.
伪传递规则:由X→Y,WY→Z,有XW→Z.
合并规则:由X→Y,Z⊆Y,有X→Y.

7.6 求最小函数的依赖集

什么是依赖?
- 依赖是指关系中一个或一组属性的值可以决定其它属性的值比如A->B这就是一个依赖
如何求最小依赖集?
1. 拆右边为多个元素的比如A->BC拆为A->B和A->C
2. 除去当前元素,求它的闭包,把集合里面所有元素都弄完
3. 左边最小化(通过遮住元素来看能不能退出其他元素)
- 比如BCD,遮住B看能退出CD吗，遮住C能退出BD吗遮住D看能退出BC吗

F={B->D，DG->C，BD->E，AG->B，ADG->B，ADG->C}

最小函数依赖集求法：

（1）：右边拆成单属性的，例如：ADG->BC拆成ADG->B,ADG->C;

（2）：F中去掉X->Y,求X->Y左边X的闭包X+,若Y属于X+，则删去多余，否则保留。例如B->D,B+={B},则保留。

（3）：X->Y左边的拆成单属性，然后求个单属性的闭包,若Y属于单属性闭包，则删去拆掉的闭包。例如：BD->E拆成B->E,D->E,B+={BDE},E属于B+,所以删去D;D+={D}不变。故BD->E用B->E代替。

（1）（2）求得：

Fm={B->D,DG->C,BD->E,AG->B}

（3）求得：

Fm={B->D,DG->C,B->E,AG->B}


最终答案：Fm={B->D,DG->C,B->E,AG->B}

7.7 模式分解

准则: ⽆损连接和保持函数依赖
- ⽆损: 就是分解后再次连接,和分解之前⼀样
- 依赖:依赖不变

已知R(ABCDEGH)  ,F={A->D,E->D,D->B,BC->D,DC->A} ,求保持函数依赖的3NF
的分解

1) 求出最⼩函数依赖集

2)把不在F⾥⾯的属性都找出来,单独分⼀类

3) 把每⼀个依赖左边相同的分成⼀类,没有⼀样的,那么就把A->D改为{AD},如果⼀样{A->B,A->C} 那么久改为{ABC}

4) 如果候选码没出现在分离⾥⾯, 把任意⼀个候选码作为⼀类

最先函数依赖集Fmin={A->D,E->D,D->B,BC->D,DC->A}

GH没在F⾥⾯, 单独⼀类{GH}

候选码:        CE        AE

{AD}{ED}{DB}{BCD}{DCA }{CE}{GH}

7. 数据库设计

数据库设计的基本步骤
- 需求分析
- 概念结构设计 ER
- 逻辑结构设计 ER转逻辑模型
- 物理结构设计逻辑转物理
- 数据库实施写SQL
- 数据库运⾏维护性能检测
ER图怎么画,请参考:https://zhuanlan.zhihu.com/p/270299029?utm_source=qq

8. 数据库编程

嵌入式SQL
- 就是把SQL语句嵌⼊到其他编程语⾔⾥⾯⽐如java
嵌入式SQL的处理过程:
1. 预编译转换为函数调⽤
2. 主语⾔编译
3. 变成主语⾔所编译的类型
SQL与主语言的通信
- SQL给主语⾔传递状态
- 主语⾔给SQL提供参数
- SQL把查询结果交给主语⾔处理(游标,和主变量实现)

9. 关系查询处理和查询优化

查询过程
查询优化的四个阶段:
- 查询分析
- 查询检查查询优化查询执行
- 查询优化包括代数优化,物理优化.
- 代数优化就是本章讲的重点，优化树
- 选择运算尽量先做
- 把投影运算和选择运算同时执⾏
- 把投影同它前后的双⽬运算符连接起来

10. 数据恢复技术

10.1 事务:

事务的四大特性:ACID
- A:原子性autom 要么全做，要么全不做
- c:一致性consistent 一致性与原子性密切相关，要么全做要么全不做，否则就会造成数据不一致。比如说︰银行汇钱，两边都要有操作才行
- I∶隔离性isolate 一个事务的执行不能被其他事务所干扰
- D︰持久性duration 数据库的改变是永久的。比如要落入磁盘

10.2 故障种类:

事物内部故障
- 采取REDO重做和UNDO撤销技术
系统故障DBMS
- 系统突然停转，系统要重启
介质故障
- 硬件损坏
计算机病毒

10.3 恢复方式

数据转储
对失败的事务重新执行
日志文件
记录事务对数据的更新操作的文件

10.4 恢复策略

事务故障的恢复
- 事务异常终⽌，那么就撤销之前的所有操作
系统故障的恢复
- 还没执⾏完的事务UNDO、丢失的事务REDO。
介质故障的恢复
- 重装数据库，重做已经完成的事务

11. 并发控制

11.1 什么是并发?

多个事物同时执行

11.2并发带来的问题

丢失修改
- 我修改的东⻄没⽣效。⽐如说我把票价改成99元，结果还是原来的299元
读脏数据
- ⽐如，你要读取数据库中的字段A、字段B，你读取时恰巧有其他⽤户正在更新这2个字段，而且是先更新A、再更新B，
- ⽽且如果那个⽤户更新⼀半你就读取了，也就是说更新了A，正打算要更新B但尚未更新时，你就读取了，此时你得到的就是脏数据
不可重复读
- 我在⼀个事务中，连续两次读到的数据不⼀样。
- ⽐如我刚开始读到银⾏余额为10元。此时单位突然给发⼯资100到这张卡,那么我第⼆次读就变110元

11.3 解决⽅法

排它锁：也叫写锁
- X锁
共享锁：也叫读锁
- S锁

11.4 封锁协议

⼀级封锁协议
- 修改时，必须加X锁，直到结束
⼆级封锁协议
- 读的时候，加S锁，⽤完就放
三级封锁协议
- 读的时候，加S锁，直到结束

⼀级锁协议：解决丢失修改
⼆级锁协议：解决读脏数据
三级锁协议：解决不可重复度

11.5 可串行性

假设多种情况都可以,然后获得结果,
如果并发执⾏的结果跟上⾯的结果⾥⾯任意⼀个⼀样就可以
事务T1: 读取B,A=B+1,写回A
事务T2: 读取A,B=A+1,写回B
那么结果假设先T1, 在T2 , 那么A=4,B =3;
假设先T2 ,那么A=3, B =4;

数据库系统概论(笔记)

1.绪论

1.1数据库的四个基本概念：

1.2数据库的发展阶段:

1.3 数据模型

1.4 ER图

1.5 常用的数据模型

1.6 数据库系统的结构

1.7 数据库系统的组成

2. 关系数据库

2.1 关系模式(了解即可)

2.2 关系操作 (后面会讲)

2.3 关系语言的分类

2.4关系的完整性

3. SQL语言

3.1 SQL的基本概念

3.2 SQL语言的特点

3.3 SQL的基本语法

3.3.1SQL的数据类型(变量类型)

3.3.2 模式定义&删除

3.3.3 表的定义和删除和修改

3.3.4 索引的建立与修改&删除

3.4 SQL 查询语法(举例说明)

3.4.1 查询所有 & 查询部分列

3.4.2 起别名

3.4.3 结果去重

3.4.4 查询结果加条件

3.4.5 聚集函数的使用

3.4.6 分组查询

3.4.7 连接

3.4.8 多表查询

3.4.9 嵌套查询：查询语句里面嵌套了查询

3.4.10 带有ANY ALL的子查询

3.4.11 带EXISTs 的子查询

3.4.12 集合查询

3.5 表中数据的增删改及视图的增删改查

3.5.1 数据插入 修改 删除

3.5.2 创建视图

4. 数据库的安全性

4.1 ⽤户身份鉴别

4.2 ⾃主存取控制⽅法

4.2.1 授权 Grant

4.2.2 回收权限 revoke

4.3 数据库角色

4.4 视图机制

4.5 审计:

5. 数据库的完整性

5.1 三大完整性:

5.1.1 实体完整性

5.1.2 参照完整性

5.1.3⽤户定义完整性

5.2 断言:

5.3 触发器

7. 关系数据理论

7.1 范式分类

7.2 依赖

7.3 候选码

7.4 超码:能表示出所有属性的集合

7.5三大范式

7.5.1 1NF

7.5.2 2NF

7.5.3 3NF

7.5.4 BCNF

7.5 公理系统

7.6 求最小函数的依赖集

7.7 模式分解

7. 数据库设计

8. 数据库编程

9. 关系查询处理和查询优化

10. 数据恢复技术

10.1 事务:

10.2 故障种类:

10.3 恢复方式

10.4 恢复策略

11. 并发控制

11.1 什么是并发?

11.2并发带来的问题

11.3 解决⽅法

11.4 封锁协议

11.5 可串行性

3.5.1 数据插入修改删除