数据库原理及应用

学堂在线：https://www.xuetangx.com/learn/EST08091001150/EST08091001150/14767710/video/30164527?channel=i.area.learn_title

尚硅谷MySQL：MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！_哔哩哔哩_bilibili

数据库应用原理(强推！)：https://www.bilibili.com/video/BV1tm4y1w7Sz?t=369.2

考试重点

群公告：

期末重点： SQL编程--难度类似实验测评关系模型---概念、关系运算 CDM设计---根据题目给出的需求，画出PD格式的CDM图 PDM设计---课件4-4数据库规范化设计 ODBC---概念、分级、方法 JDBC----概念、分级、编程存储过程、触发器----难度类似实验第七章考核点：cap base 5v 四种nosql数据库类型和分类比较

一、数据库系统概论

1.1 数据库及其系统概念

数据库：

是一种依照特定数据模型组织，存储和管理数据的文件集合

数据库和普通数据文件的主要区别：

可以支持不同应用对数据的共享访问，普通数据文件难以支持

可以实现复杂的数据管理

可以独立应用程序，普通数据文件和应用程序紧耦合

数据库的操作访问和控制管理由数据库管理系统软件实现，普通的文件都必须让应用程序实现

数据模型：

是指描述事物对象的数据结构组成，数据语义关系，数据约束的抽象结构及其说明

用白话文理解：就是把现实世界转换成计算机能理解的形式，能够被计算机理解或者说实现

数据模型的组成：

数据结构：描述事物对象的静态特征，包括事物对象的数据组成，数据类型，数据性质

数据操作：用于描述事物对象的动态特征，比如增删改查

数据约束：描述数据之间的制约和依存关系

实际就是相当于虚拟世界的规则，限定实体的规则叫实体完整性，限定联系的规则为参照完整性，针对属性列的规则称为用户自定义完整性

数据模型的分类：

概念模型：可以理解为画图

逻辑模型：可看作把概念模型画的图转化成二维表

物理模型：是对数据最底层的抽象，描述数据在系统内部的表示方法，一般我们不关心

常见的数据模型：

层次数据模型：最早使用，采用树存储管理数据

网状数据模型：采用网状图存储管理数据

关系数据模型：实际就是个二维表在组织和管理数据

优点：

数据结构简单，操作灵活

支持关系与集合运算操作

支持SQL标准

拥有众多的软件厂商产品与用户

局限：

只用于结构化数据的组织与存储管理

支持的数据类型较简单

难以支持互联网广泛应用的非结构化数据和复杂数据管理

数据库系统的组成：

用户

数据库管理系统

数据库

数据库应用程序

1.2 数据库技术发展

数据管理技术发展阶段

人工管理阶段

文件系统管理阶段

数据库系统管理阶段

第一代数据库技术

第二代数据库技术

第三代数据库技术

第四代数据库技术

1.3 数据库系统的结构

业务处理系统：比如航空机票订票系统

管理信息系统：比如人力资源管理信息系统，办公管理信息系统

决策支持系统：比如证券分析与辅助决策信息系统

数据库系统应用结构：

单用户结构

集中式结构

分布式结构

客户/服务器结构

数据库应用系统生命周期：

需求分析

系统设计

系统实现

系统测试

系统运行与维护

1.4 典型数据库管理系统

常见数据库

Oracle

MySQL

SQLite：常用在小型数据库

DB2：常用在银行系统中

PostgreSQL：稳定性极强

MariaDB：因为MySQL有闭源的风险，所以有MariaDB分支项

按用途分类：

通用DBMS：支持公共领域数据，如SQL server

专用领域DBMS：支持专用领域数据库，如嵌入式的SQLite

按用户数分类：

单用户DBMS：仅支持单用户访问，如miniSQL

多用户DBMS：支持多用户并行，如MySQL

按系统部署分类：

集中式DBMS：数据库集中部署在单一物理机器中，如Access

分布式DBMS：数据库可分布在不同位置的物理机器，如Oracle Database

按使用场景分类：

桌面级DBMS：适用于微小型的信息服务应用：Access，SQLite

企业级DBMS：适用于中大型企业，如Oracle，DB2

按软件版权分类：

产品DBMS：数据库厂商拥有版权，比如SQL server，Oracle

开源DBMS：如MySQL，PostgreSQL

二、数据库关系模型

2.1 关系及其相关概念

其实不管什么模型，都可以分成三点来理解。因为数据模型是对计算机模拟出来的世界，跟真实世界是一一对应的。初看很抽象，但一旦和物理世界结合起来就不难理解了。首先是数据结构，里面有数据的各种定义，然后是数据操纵，就是增删改查，最后是完整性约束，也就是即使在真实世界中，也不能随意的增删改查，需要有一定的规则

什么是关系数据模型？

是一种基于二维表结构存储数据实体及实体间联系的数据模型

下面将从3方面介绍下关系数据模型

关系模型的数据结构(相关概念)：

实体：是指包含有数据特征的事务对象在概念数据模型世界中的抽象名称

关系：是指具有关系特征、用于存放实体数据的二维表

元组：表中的一行

候选键：表中的某个属性可以唯一确定一个元组，可以有多个候选码。

主键：是关系表中最具代表性的一个候选键，每个关系表只能定义一个主键

代理键：DBMS自动生成的数字序列作为关系表的主键，可替代复合主键，提高效率

主属性：候选码的属性

关系模式：对关系的描述-->关系名(属性1，属性2...属性n).如何理解关系模式呢，可以这么理解它，不同数据库比如mysql，postgresql，oracle等，关系模式可以让所有人一眼就能看出来想传达的是什么，可以说是一种格式，或者说框架。其实前面的是一种简化的写法，完整的写法应该如下关系名(属性名集合U，U中属性来自的域D，属性向域的映射集合DOM，属性间数据的依赖关系的集合)

关系的特征：

表中每行存储实体的一个实例数据

表中每列包含实体的一个属性信息

表中单元格只能存储单个值

不允许有重复行/列

行/列顺序任意

关系模型的数据操作

关系代数运算符的分类

集合运算符：并交差和笛卡尔积

专门的关系运算符：选择，投影，连接，除

运算符详细解释：

并：R和S的属性个数相同，并且对应的属性应该取自同一个域，结果由属于R或者S的元组构成

差：R和S的属性个数相同，并且对应的属性应该取自同一个域，结果由属于R但不属于S的元组构成

交：参照并

笛卡尔积：R和S的属性并在一起，然后进行组合

选择：在关系R中选择满足给定条件的元组 F：选择条件，是一个逻辑表达式

投影：选择某些列注意：投影不仅取消了原关系中的某些列，还可能去掉某些元组(避免重复行)

连接：可以理解为先做笛卡尔积，然后去除掉某些行其中为比较运算符，为时成为等值连接

自然连接：

是等值连接的一种特殊情况，就是把重复列去掉了；也就是先笛卡尔积，然后筛选属性相同的行，然后去掉重复列

悬浮元组：两个关系R和S在做自然连接时，关系R中的某些元组有可能在S中不存在对应，从而造成R中的这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组。

外连接：把悬浮元组的结果保留在结果关系中，而在其他属性上填空值。

左外连接(left join)：只保留左边关系R中的悬浮元组。

右外连接(right join)：只保留右边关系S中的悬浮元组。

除：强推除运算进行学习

象集的概念：

t[A]代表元组中属性A对应的分量

给定一个关系R(X,Z)，当t[X]=x时，x在R中的象集为表示R上属性组X上值为x的诸元组在Z上分量的集合

记R(X,Y)和S(Y,Z) 其中为x在R中的象集

除一般用来选择具有“包含关系”的数据，总体上可以分为两步骤。

第一步，选择最终的列，即除去Y列和Z列，得到剩下的X列

第二步，选择最终的行，即选择象集中全部包含除数的数据

https://www.bilibili.com/video/BV1tm4y1w7Sz?t=4151.9

关系模型的完整性约束

实体完整性

主属性不能取空值且唯一

参照完整性

设F是关系R的一个或一组属性，但不是R的候选键，若F在另一个关系S中做主键，则成F为R的外码

其中R称为参照关系，S称为被参照关系。注意R和S可能是同一个关系，而且F在R和S中的名字可能不一样

对于R上每个元组在F中的值必须满足：

或者取空值

或者等于S中的某个主键值

用户自定义完整性

这个通常是针对某一列的，比如某一列只能取1或2或3

完整性约束的作用：

消除关系表的元组重复存储

保持关联表的数据一致性

实现业务数据规则

三、数据库操作SQL语言

3.1 SQL语言概述

SQL语言特点

一体化

使用方式灵活

非过程化

语言语句简单

SQL是一种规范，类似普通话，MySQL含有一些特有语言，类似方言

ORM：对象关系映射

SQL的分类

DDL：数据定义语言

create alter drop rename truncate

DML：数据操作语言

insert delete update select

DCL：数据控制语言

commit rollback savement

关系型数据库

关系型数据库把复杂的数据结构归结为简单的二维表格形式

关系型数据库以行和列的形式存储信息

SQL就是关系型数据库的查询语言

非关系型数据库

即NoSQL，基于键值对存储数据，不需要经过SQL层的解析，性能非常高

分类

键值型数据，如Redis

文档型数据，如MongoDB

搜索引擎数据库，Elasticsearch，Solr

列式数据库，HBase

图形数据库

ER模型

实体集

属性

联系集

表的关联关系

一对一

一对多

在从表创建一个字段，字段作为外键指向主表

多对多

要表示多对多，必须创建第三个表：联结表

自我引用

MySQL相关

MySQL在WIndows环境下是大小写不敏感的,在Linux下是大小写敏感的

注释:

单行注释:#或者--

多行注释:/**/

在规范方面,字符串和日期时间类型的变量需要使用单引号,列的别名需要使用双引号

所有的运算符或者列值遇到null,运算结果都为null

着重号:反引号

若要保证表中的字段和表名没有和保留字冲突,就需要使用反引号

MySQL从5.7版本直接跳跃发布了8.0版本，在MySQL6.x后分了社区版和商业版333

算数运算符

+ select 1+'1'返回为2,sql会把字符串转换为数字
-
*
/或者div
%或者mod

比较运算符

= 等于
<=> 安全等于,安全地判断两个值\字符串或表达式是否相等,和等于的唯一区别是可以对null进行判断,null<=>null返回1
<>或者!= 不等于
<
<=
>
>=
is null或者isnull
is not null
least(a,b) 在多个值中返回最小值
greatest(a,b)
in #elect name from employees where salary not in (6000,7000,8000)
not in
like # 模糊查询select * from employees where last_name like '%a%',%代表0个或多个字符
	# _代表一个不确定的字符
regexp或者rlike
between and

逻辑运算符与位运算符

not或者!
and或者&& 优先级高于or
or或者||
xor
&
|
^
~
>>
<<

postgresql和mysql在引号上的区别

postgresql上的单引号是用来标识值的，双引号标识列名表名等数据库中存在的值，不能用反单引号

mysql支持用反单引号标识系统标识符(类似postgresql的双引号)，可以用双引号或者单引号表示值

3.2 数据定义DDL

数据库相关：

创建与删除

1 2	`create database xxx; drop database yyy;`

改名
1
alter database xxx rename to yyy;

表相关

创建与删除

create table xxx (列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束]);
drop table yyy;

改名

1 2	`alter table xxx rename to yyy; alter table xxx rename 原列名 to 新列名;`

列的增加与删除

1 2	`alter table add 新列名数据类型约束; alter table drop column 列名;`

列的数据类型修改

1	`alter table xxx alter column yyy type 新的数据类型;`

约束相关

约束：primary key,null,not null,default,unique,check

删除约束：alter table drop constraint 约束名;

主键相关

create table xxx (列名 数据类型 [类完整性约束] primary key,
               列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束]);
使用列约束关键词`primary key`只能定义单列主键。若要定义复合主键，只能使用表约束，比如,
create table xxx (列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束]
constraint 约束名 primary key(主键列));

外键相关

create table xxx (列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束]
constraint 约束名 foreign key(外键列) references xxx (yyy));
或者
create table xxx (列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束] references xxx (yyy));

代理键相关

create table xxx (
 			  代理键列名 <Serial 数据类型> not null,
 			  列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束],
               列名 数据类型 [类完整性约束]
constraint 约束名 primary key(代理键列名);

索引相关

1
2
3

create index 索引名 on 表名 (列名)
drop index 索引名;
alter index 索引名 rename to 新索引名;

3.3 数据操纵DML

数据插入

1	`insert into 视图名/表名 [列名] values (列值表)`

数据更新

1	`update 视图名/表名 set 列名=值 [where ...]`

数据删除
1
delete from 视图名/表名 [where]

3.4 数据查询DQL

3.4.1 单表数据查询

排序问题：
1
2
3
4
select * from employees order by salary DESC # 降序
select * from employees order by salary AES # 升序
select * from yyy order by xxx DESC,zzz ASC; # 二级排序
select * from employees order by a,b # 二级排序
去重问题：
1
select distinct xxx from yyy;
正则查询：
1
2
% 代表一个或多个字符
_ 代表一个字符
聚合函数：
1
2
3
4
5
avg();
count(); # 计算行数可以用count(1)或者count(*)，但是括号内不能是字段，因为可能是null
min();
max();
sum();
若未对查询结果分组，聚集函数将作用于整个查询结果

对查询结果分组后，聚集函数将分别作用于每个组
分组统计 Group by：

select中出现的非组函数的字段必须出现在group by中

group by声明在from之后，where之后，order by前面，limit前面

Having的使用：

如果过滤条件中使用了聚合函数，则必须使用HAVING替换where，否则报错

HAVING的使用必须有group by，且必须出现在group by的后面

having的效率低于where

在select中给列起别名后，不能在where中使用，可以在order by中使用
分页：
1
2
3
4
5
select * from employees limit 0,20
从第0条数据开始,每页显示20个
或者
select * from employees limit 3 offset 4
注意:limit语句必须放在整个select语句的最后

3.4.2 多表数据查询-连接

多表查询的分类：

等值连接与非等值连接

自连接与非自连接：可以用别名区分两个相同的表

内连接与外连接
内连接：使用JOIN...ON连接查询语句
定义：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

从SQL优化的角度，建议每个字段前指明字段所在的表

一旦给表起了别名，则在select和where中必须使用别名，不能使用原名
对于SQL92语法：表的连接条件在where中填写
1
select a.xxx, b.yyy from zzz where ...
对于SQL99语法：表的连接条件在ON中填写
1
select c1 from t1 join t2 on xxx;
特殊情况：当表的连接条件没有或者失效的时候，变为交叉连接/笛卡尔积
1
select last_name, department_name from employees cross join deparments;
外连接
左外连接

结果中除了包含满足条件的，还包含左表中不满足条件的行

Mysql只支持SQL99语法的左/右外连接，不支持SQL92语法的左/右外连接
1
select a,b from xxx left join yyy
右外连接

结果中除了包含满足条件的，还包含右表中不满足条件的行
1
select a,b from xxx right join yyy
满外连接

结果中除了包含满足条件的，还包含左右表中不满足条件的行

Mysql不支持满外连接
1
select a,b from xxx full join yyy;
既然SQL不支持满外连接，那么如何满足诉求呢？
UNION

利用UNION，可以给出多条SELECT语句，并将他们的结果组成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应，各个SELECT语句之间使用UNION(取并集后去重，所以效率低)或UNION ALL(取并集后不去重)关键字分割
1
2
3
4
5
6
7
8
select employee_id,department_id
from employees e left join departments d
on e.department_id=department_id
union all
select employee_id,department_id
from employees e right join departments d
on e.department_id=department_id
where e.department_id is null;
SQL99语法新特性：
自然连接

可以把自然连接理解为等值连接，会自动帮你查阅两张连接表中所有相同的字段，然后进行等值连接，这种方式并不推荐，可维护性差
1
2
3
select xxx,yyy
from qqq
natural join yyy;
USING连接

对join on中的等值连接进行了优化
1
2
3
4
5
6
7
8
9
select name,sex,addr
from personInfo
join houseInfo
on personInfo.name=houseInfo.name
可以写为
select name,sex,addr
from personInfo
join houseInfo
using(name)
SQL99语法综合：
1
2
3
4
5
6
7
8
9
>select xxx,xxx,xxx(存在聚合函数)
from xxx
>join q on y
join z on q
where xxx
group by xxx
>having xxx
order by xxx
>limit xxx
执行过程

from->on->join->where->group->having->select->distinct->order->limit

参考资料：https://www.bilibili.com/video/BV1iq4y1u7vj?t=1934.7&p=41

3.4.3 多表数据查询-子查询(嵌套查询)

子查询(结果在一个表中)

一般来说，子查询只适合结果数据均来自一个表，若大于等于2个表，则不能使用子查询
1
select xxx from yyy where (含select的另一结果集);
相关注意点：

子查询要放在括号内

子查询分类：

从内查询返回的结果的条数：

单行子查询

多行子查询

从内查询是否被执行多次：

相关子查询

不相关子查询

单行子查询：

单行比较操作符：= > >= < <= <>(不等于)
多行子查询(集合比较子查询)：
多行比较运算符：
1
2
3
4
in
any 需要和单行比较操作符一起使用，和子查询返回的某一个值进行比较
all 需要和单行比较操作符一起使用，和子查询返回的所有值进行比较
some 实际是any的别名，作用相同，一般使用any

3.5 数据控制SQL语句DCL

数据控制语句是对用户数据访问权进行控制的操作语句

创建用户
1
create user 'q' identified by 'y'; 
删除用户
1
drop user 'q';
设置当前用户密码
1
2
3
set password = '123';
或者
alter user user() identified by '123';
设置其他用户密码
1
alter user 'q' identified by '456';
查看所有用户
1
select user from mysql.user;
查看Mysql所有支持的权限
1
show privileges;
查看权限列表
1
2
show grants;
show grants for 'q'@'localhost';root用户可以查看其他用户的权限
grant授权

把一个用户的某个数据库下的某个或者所有表授权给另一个用户

with grant option表明在授权时授予被授权用户将自己获得的权限授予其他用户的能力
1
2
grant select, update on mydb.* to 'q'@'%' [with grant option];
grant all privileges on *.* to 'q'@'%'; #所有表的所有权限都授权，此时和root用户的唯一区别就是不能给其他用户赋权
revoke收权：就是取消已经赋予用户的某些权限
1
2
revoke update on *.* from 'q'@'%';
revoke all privileges on *.* from 'q'@'%'
deny权限拒绝
1
deny 权限列表 on 数据库对象 to 用户/角色

3.6 视图SQL语句

为什么使用视图？

可以帮助我们使用表的一部分而不是所有的表，隐藏一些敏感数据

针对不同的用户制定不同的查询视图

简化复杂的SQL查询操作

外部人员无法知道数据库结构，提高数据访问安全性

创建视图

查询语句中字段的别名会作为视图字段名
1
2
create view 视图名 as 查询语句;
select * from 视图名;
也可以自己指定字段名
1
create view 视图名 (xxx, yyy, zzz)as 查询语句;
查看视图
1
2
3
4
show tables;查看表和视图
desc 视图名; 查看视图结构
show table status like '视图名'\G; 查看视图的属性信息
show create view 视图名; 查看视图的详细定义信息
修改
1
2
3
create or replace view xxx as ...; # 这里的or replace类似if not exists，若存在则修改
或者
alter view xxx as ...;
删除视图
1
drop view xxx;

3.7 Postgresql数据库SQL实践

四、数据库设计与实现

4.1 数据库设计概述

数据库应用架构可分为四种：

单用户结构

集中式结构

客户端/服务器结构

分布式结构

数据库结构模型设计分为三种：

概念模型

逻辑模型

物理模型

数据库应用访问方式设计：

基于本地接口

基于标准接口

基于数据访问层框架

4.2 E-R模型

E-R模型：

实体-联系模型

实体之间的关系：

两个实体型之间的联系：

一对一

一对多

多对多

标识符：

表示不同实体实例的属性，标识符可以是一个或多个属性

标识符和主键的区别是标识符是一个逻辑概念，主键是一个物理概念

鸟足版本的实体-联系符号含义

联系度数：

联系中关联的实体数目

在继承性联系中，可分为两种：

互斥性继承联系：父实体中的一个实例只能属于某个子实体，不能同时属于多个子实体

非互斥性继承联系：

继承性联系还有另一种分类：

完整继承联系：父实体中的实例完整地被各个子实体分别继承

非完整继承联系

继承联系的图形符号(最后一行的符号是错误的，考完了，懒得改了QAQ)：

按照语义联系可分为两种：

强实体

弱实体

标识符依赖

非标识符依赖

如果弱实体的标识符中含有所依赖实体的标识符，则称为标识符依赖弱实体

如果弱实体的标识符中不含有所依赖实体的标识符，则称为非标识符依赖弱实体

4.3 数据库建模设计

概念数据模型设计步骤：

业务数据分析，抽取数据实体

定义实体属性及标识

建立实体联系，构建局部ER图

分类、聚集、概括各个部分ER模型图

完善全局ER图，建立系统业务数据组成结构

数据模型元素对应关系：

CDM LDM PDM

实体实体表

属性属性列

标识符标识符主键、外键

联系联系参照完整性约束

CDM	LDM	PDM
实体	实体	表
属性	属性	列
标识符	标识符	主键、外键
联系	联系	参照完整性约束

4.4 数据库规范化设计

为什么需要规范化数据库设计？

减少数据库中的冗余数据，尽量使同一数据在数据库中仅保存一份，有效降低维护数据一致性的工作量。

设计合理的表间依赖关系和约束关系，便于实现数据完整性和一致性。

设计合理的数据库结构，便于系统对数据的高效访问处理。

数据库的表可能存在哪些问题？

数据冗余

增删改异常

函数依赖理论：

设有一个关系模式R(U)，U为关系R的属性集合，X和Y分别为U的子集。设t，s为关系R中的任意两个元素，若t[x]=s[x]，则t[Y]=s[Y]，那么称Y函数依赖于X，表示为X->Y

函数依赖的左部称为决定因子，右部称为一看函数，他们都是属性的集合

函数依赖反映属性或属性组之间相互依存，相互制约的关系

补充：

什么是关系？就当做数据表就行

关系模式：对关系的描述

什么是关系模型？就是若干关系的集合，也就是一个是数据库

实体：客观存在的对象

联系：实体之间的关系

函数依赖的类型：

完全函数依赖

设X，Y为关系R的不同属性集，若，且不存在，使得，则称Y为完全函数依赖，否则称Y部分函数依赖

部分函数依赖

传递函数依赖

设X，Y，Z为某关系的不同属性集，有，若，则称Z对X存在函数依赖传递

多值函数依赖

设U是关系模式R的属性集，X和Y是U的子集，Z=U-X-Y，对于R的关系r，在r中存元组(x，y1，z1)和(x，y2，z2)时，也存在元组(x，y1，z2)和(x，y2，z1)，则称在模式R上存在多值函数依赖

如何看函数依赖关系？

首先找到主属性

然后看主属性到非主属性的关系

然后看主属性内部和非主属性内部的关系

关系规范化范式：

第一范式1NF：

关系表中的属性不可再细分，就是说列里面存放的东西只能有一个

第二范式2NF：

若关系满足第一范式，并且消除了关系中的部分函数依赖，也就是每个非主属性都完全依赖于任何一个候选码，则称满足第二范式

第三范式3NF：

若关系满足第二范式，并且切断了关系中的属性传递函数依赖，则称满足第三范式

巴斯-科德范式BCNF：

关系中的所有函数依赖的决定因子都是候选键，则称满足BCNF

BCNF有两种思路可以理解：

消除了主属性内部的部分和传递函数依赖

把所有的决定关系都列出来，箭头的左侧必须是候选码，则满足BCNF

第四范式4NF：

若关系满足BCNF，并且消除了多值函数依赖，则称满足第四范式

如何从提升范式？

强推！！！https://www.bilibili.com/video/BV1wv4y1m7J2?t=3236.7&p=15

用白话文就是：“谁跟你好，复制自己，把它带走”

关系规范化程度的利弊：

关系的规范化程度越高，冗余数据越少，可消除的异常访问越多；但是同时分解出来的关系表也就越多，实现数据查询时，需要关联多表，效率比较低

https://www.bilibili.com/video/BV1784y1k7NR?t=1227.1

一些名词的解释参考资料：

https://www.cnblogs.com/fudashi/p/6856628.html

https://www.bilibili.com/video/BV1wv4y1m7J2?t=77.4!!!!!!

五、数据库管理

5.1 事务管理

什么是事务：

是一组逻辑单元，使数据从一种状态变成另一种状态

事务的ACID属性：

原子性：事务所有操作在数据库中要么全部执行，要么全部不执行

一致性：事务多次执行，其结果应一致

隔离性：事务与事务之间隔离，并发执行透明

持续性：事务完成之后，数据的改变必须是永久的

事务的状态：

活动的：事务对应的数据库操作正在执行过程中

部分提交的：事务执行完成，但是只是在内存中，还没有刷新到磁盘上

失败的：失败后暂未回滚

中止的：失败后回滚完毕

提交的：已经将修改过的数据同步到磁盘上

开启事务：
1
2
3
start transaction read only / read write(默认) / with consistent snapshot;
或者
begin;
关于SAVEPOINT：
1
2
3
savepoint xxx; 创建保存点
release savepoint xxx; 删除保存点
rollback to xxx; 回滚到保存点

5.2 并发管理

数据并发问题：

脏写(更新数据丢失)：B正在执行一个事务，暂未提交，此时A也执行了一个事务，对B暂未提交的数据进行了修改，但是B突然又回滚

脏读：B正在执行一个事务，暂未提交，此时A也执行了一个事务，对B暂未提交的数据进行了读取，但是B突然又回滚；也就是一个事务读取了被取消持久化的共享数据

不可重复读：A读取了一个字段，B更新了这个字段，A再次读取发现值变了

幻读：A读取了一个字段，B插入了几行数据，A再次读取发现多了几行

什么是可串行化调度：

就是事务中数据操作调度顺序的结果和事务串行执行结果一致，符合这样效果的调度称为可串行化调度

锁：

简介：可以理解为解决多个事务并发带来的数据不一致问题的一种机制

并发事务访问相同记录的情况大大致可以划分为三种：

读-读情况

写-写情况：会出现脏写

读-写情况：会出现脏读，不可重复读，幻读

并发问题的解决方案：

方案一：读操作利用多版本并发控制MVCC，写操作进行加锁

方案二：读、写操作都采用加锁的方式，意味着就要像写-写操作那样排队等待

锁的分类：

从数据操作的类型划分

读锁/共享锁/S锁：多个事务的读操作相互不会堵塞

写锁/排他锁/X锁：若当前写操作未完成，会阻断其它写锁和读锁

从资源锁定的粒度划分

数据库

表

页面

行

从资源锁定实施方式

隐式锁定

显式锁定

加锁协议：

一级加锁协议

任何事务在修改共享数据对象之前，必须对共享数据单元执行排他锁指令

解决了更新数据丢失问题

二级加锁协议

在一级加锁协议上，对共享数据进行读操作前，必须对共享数据单元执行共享锁指令，读完数据即可释放共享锁定

解决了更新数据丢失，脏读问题

三级加锁协议

在一级加锁协议上，对共享数据进行读操作前，必须对共享数据单元执行共享锁指令，事务处理结束即可释放共享锁定

解决了更新数据丢失，脏读，不可重复读问题

两阶段锁定协议：

指所有并发事务在进行共享数据操作处理时，必须按照两个阶段对共享数据进行加锁申请。在增长阶段，事务可以对共享数据进行加锁申请，但是不能释放已有的锁；在缩减阶段，事务可以对已有的锁进行释放，但是不能对共享数据提出新的加锁申请。

事务的死锁：

定义：

在事务的并发执行过程中，若这些事务同时锁定两个及以上资源时，可能出现彼此都不能继续执行的状态，称为事务死锁状态

死锁的必要条件：

互斥条件：事务对所分配的资源进行排他性使用，也就是一段时间内某资源只由一个事务占用

请求和保持条件：事务已经保持执照一个资源，但又提出了新的资源请求

不剥夺条件：事务已占有的资源，在未使用完之前，不能被剥夺，只能在使用完自己释放

环路等待条件：指在发生死锁时，必然存在一个事务-资源的等待环路

防范死锁的策略：

允许用户一次发出当前所需全部资源的锁定，使用完后再全部释放

规定所有应用程序锁定资源的顺序必须完全相同

解决死锁的方法：

当发生死锁时，回滚其中一个事务，并取消它对数据库所做的改动

事务隔离级别：

读未提交

读已提交

可重复读

可串行化

隔离等级越高，并行度越低，选择何种等级，取决于我们对数据不一致的容忍程度

5.3 安全管理

创建角色
1
create role 'qy'[@'%'];
赋予权限
1
grant select on db1.* to 'qy';
查看角色权限
1
show grants for 'qy';
回收权限
1
revoke select on db1.* from 'qy'
删除角色
1
drop role 'qy';
用户赋予角色
1
grant 'qy_role'@'%' to 'qy_user'@'%';
查看当前用户已激活的角色
1
select current_role();
激活用户

MySQL中创建角色之后默认都是没有被激活，必须手动激活
方法一：
1
set default role 'qy_role'@'%' to 'qy_user'@'%';
执行完需要重新登录
方法二：
1
2
show variables like 'activate_all_roles_on_login';
set global activate_all_roles_on_login=ON;
收回角色
1
revoke 'qy_role'@'%' from 'qy_user'@'%';
需要重新登录
设置强制角色

意思是给每个创建的账户设置默认角色，不需要手动设置。强制角色无法revoke或者drop
方式一：

在配置文件中指明
1
2
[mysqld]
mandatory_roles='role1,role2@localhost'

5.4 数据库备份与恢复

常见的备份方法：

完全数据库备份

差异数据库备份

事务日志备份

文件备份

备份分类：

冷备份

热备份

备份实现和恢复：

backup database xxx to disk = 'D:\demo.bak';

restore database xxx from disk = 'D:\demo.bak'

六、数据库应用编程

6.1 数据库连接技术

ODBC

定义：开放式数据库互联(Open DataBase Connectivity)，实现了应用程序对多种不同DBMS数据库的访问

内容：

ODBC定义了一套基于SQL的、公共的、与数据库无关的API

作用：

使每个应用程序利用相同的源代码就可访问不同的数据库系统，从而使得应用程序与DBMS之间存在逻辑上的独立性

层次结构：

客户端数据库应用程序

ODBC应用程序接口

ODBC驱动程序管理器

JDBC

package org.example;

import java.sql.*;

public class Main {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        String url = "jdbc:postgresql://localhost:5432/estatedb";
        String username = "postgres";
        String password = "root";
        try {
            Class.forName("org.postgresql.Driver");//第一步：加载驱动
            conn = DriverManager.getConnection(url, username, password);//第二步：获取连接
            System.out.println("success");
            stmt = (Statement) conn.createStatement();//第三步：创建Statement对象
            String sql = "select * from owner";
            ResultSet rs = stmt.executeQuery(sql);//第四步：执行SQL语句，获取结果集
            System.out.println("Result:");
            while (rs.next()) {
                System.out.println(rs.getString("PersonID"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ":" + e.getMessage());
        }
    }
}

6.2 数据库存储过程

6.2.1 存储过程

定义：

就是一组经过预先编译的SQL语句的封装，需要调用的时候，用户只需要向服务器发出调用存储过程的命令，服务器就会把这一系列的SQL语句全部执行。

好处：

减少网络传输量

简化操作，提高SQL语句的重用性

减少操作过程中的失误，提高效率

减少了SQL语句暴露在网络上的风险，提高安全性

和视图、函数的对比：

视图是虚拟表，通常不对底层数据表直接操作，通常只作查询

存储过程是程序化的SQL

与函数相比，存储过程是没有返回值的

存储过程的使用：
无参数类型
创建
1
2
3
4
5
6
delimiter $
create procedure 存储过程名 (in|out|inout 参数名 参数类型)
begin
	xxx;
end $
delimiter ;
若未写in|out|inout，默认为in
调用
1
call 存储过程名()
带OUT类型

into
创建
1
2
3
4
5
6
7
delimiter $
create procedure select_min_salary(out ms double)
begin
	select min(salary) into ms
	from employees
end $
delimiter ;
调用和查看
1
2
call select_min_salary(@ms)
select @ms;
带IN类型
创建
1
2
3
4
5
6
7
8
delimiter $
create procedure select_someone_salary(in employeename varchar(20))
begin
	select salary
	from employees
	where name=employeename
end $
delimiter ;
调用
1
2
3
4
call select_someone_salary('qy');
或者
set @name:='qy';
call select_someone_salary(@name);
带IN和OUT
创建
1
2
3
4
5
6
7
8
delimiter $
create procedure select_someone_salary(in employeename varchar(20),out empsalary double)
begin
	select salary into empsalary
	from employees
	where name=employeename
end $
delimiter ;
调用
1
2
3
set @name:='qy';
call select_someone_salary(@name,@emp);
select @emp;
带INOUT
创建
1
2
3
4
5
6
7
8
9
delimiter $
create procedure show_mgr_name(inout empname varchar(20))
begin
	select empname
	from employees t1,employees t2
	where t1.manager_id=t2.empid
	and t1.name=empname
end $
delimiter ;
调用：
1
2
3
set @name:='qy';
call select_someone_salary(@name);
select @name;

6.2.2 存储函数

语法分析：

create function xxx(参数名 参数类型)
returns 返回值类型
[characteristics]
begin
	函数体
end

调用存储函数：

1	`select 函数名(实参列表)`

举例：

实现

delimiter //
create function email_by_name()
returns varchar(25)
deterministic #返回结果确定
contains sql # 包含SQL
reads sql data #可以读SQL中的数据
begin
	return (select email from employees where last_name = 'Abel');
end //
delimiter ;

调用

1	`select email_by_name();`

解决方法二：

实现

set global log_bin_trust_function_creators = 1;
delimiter //
create function email_by_name()
returns varchar(25)
begin
	return (select email from employees where last_name = 'Abel');
end //
delimiter ;

调用

1	`select email_by_name();`

6.2.3 存储过程和存储函数的比较

关键字调用语法返回值应用场景

存储过程 procedure call 存储过程() 理解为可以有0或者多个一般用于更新

存储函数 function select 函数() 只能是1个一般用于查询结果为一个值并返回时

存储函数可以放在查询语句中使用，存储过程不行

6.2.4 存储过程和函数的查看修改和删除

查看

查看创建信息：
show create procedure xxx
show create function xxx
查看状态信息：
show procedure status [like xxx]
show function status [like xxx]
从元数据表查看
select * from information_schema.Routines where ROUTINE_NAME='存储过程或函数的名' [and ROUTINE_TYPE = 'FUNCTION/PROCEDURE'(必须大写)]

修改(不能修改函数体，只能改一些属性)

alter procedure xxx
comment '这是注释'
alter function xxx
comment '这是注释'

删除

1 2	`drop procedure xxx [if exists xxx] drop function xxx [if exists xxx]`

6.2.5 相关争议

优点：

存储过程可以一点编译多次使用，执行效率高

可以减少开发工作量

存储过程的安全性强

可以减少网络传输量

良好的封装性

微软，IBM等大型公司都要求大型项目使用存储过程

缺点：

可移植性差，不能夸数据库移植

调试困难

存储过程的版本管理很困难

不适合高并发的场景

阿里明确禁止使用存储过程

6.2.6 变量

系统变量

全局系统变量

被global修饰，又称全局变量，全局系统变量会在重启后重新赋值

会话系统变量

被session修饰，又称local变量，对一次会话有效

操作：
查看

MySQL中的系统变量以两个@开头，@@首先标记会话系统变量，若不存在，标记全局系统变量
1
2
3
4
5
6
7
8
查看所有或部分系统变量
show global variables [like xxx]
show session variables [like xxx]
show variables; # 默认查询session变量
查看指定系统变量：
select @@global.xxx
select @@session.xxx
select @@xxx
修改
方式一：修改配置文件，但是需要重启MySQL服务
方式二：使用set命令
1
2
3
4
5
6
对于系统变量：
set @@global.xxx=yyy
set global xxx=yyy
对于会话变量：
set @@session.xxx=yyy
set session xxx=yyy
用户变量

MySQL中的用户变量以一个@开头，一般只在会话用户变量前加上@，局部变量不用
会话用户变量：只对当前连接有效
定义
1
2
3
4
5
6
方式一："="或":="
set @xxx=yyy
set @xxx:=yyy
方式二：":="或 INTO关键字
select @xxx := 表达式
select 表达式 into @xxx
举例：
1
2
set @xxx=123;
select @xxx;
局部变量：只在begin和end语句块中有效，局部变量只能在存储过程和函数中使用
定义

使用declare定义，而且必须放在第一句
1
2
3
4
5
6
7
8
begin
 declare xxx int [default xxx] # 声明
 #赋值
 set xxx=yyy;
 select value into xxx;
 #查看
 select xxx;
 end
举例：
1
2
3
4
5
6
7
8
9
 delimiter //
create procedure myProcedure()
begin
declare name varchar(20);
declare sex char(2) default '男';
set name='qy'
select sexInfo into sex where empName=name;
end //
delimiter ;
如何区分局部变量和会话用户变量

局部变量必须通过declare声明

局部变量必须声明并使用在begin ... end ...中

declare必须放在首行

会话用户变量并没有指明类型，但是局部变量需要指明

6.3 数据库触发器

触发器是由事件来触发某个操作，这些事件包括insert update delete
创建
1
2
3
4
5
6
7
8
delimiter //
create trigger 触发器名称
{before|after} {insert|update|delete} on 表名
for each row
begin
触发器执行的语句块(也可以是begin...end结构组成的附和语句块);
end //
delimiter ;
查看
1
2
3
4
5
6
方式一：查看所有触发器
show triggers;
方式二：查看某个触发器
show create trigger xxx
方式三：从元数据表查询
select * from information_schema.TRIGGERS;
删除
1
drop trigger if exists xxx;
优点：

保证数据的完整性

帮助我们记录日志

对操作数据前，对数据进行合法性检查

缺点：

可读性差(应用层不可控)

相关数据的变更，可能会导致触发器报错

6.4 数据库游标

使用游标的步骤：

声明游标：

1 2	`declare cursor_name cursor for select_statement; -- mysql declare cursor_name cursor is select_statement; -- postgresql`

打开游标：
1
open cursor_name;

使用游标：

1	`fetch cursor_name into 变量名1，变量名2...变量名n`

关闭游标
1
close cursor;

示例：

不带参数的游标：

create or replace function cursor_demo()
    returns void as
$$
declare
    my_cursor refcursor;
    tmp_id text;
    tmp_name text;
begin
    open my_cursor for execute 'select "PersonID","Name" from owner';-- 打开游标
    loop
        fetch my_cursor into tmp_id,tmp_name;
        if FOUND then
            raise notice '%,%',tmp_id,tmp_name;
        else
            exit;
        end if;
    end loop;
    close my_cursor;
    raise notice 'finished...';
end;
$$ language plpgsql;
select * from cursor_demo();

或者

create or replace function cursor_demo()
    returns void as
$$
declare
    my_cursor cursor for select "PersonID", "Name"
                         from owner;
    tmp_id   text;
    tmp_name text;
begin
    open my_cursor;-- 打开游标
    loop
        fetch my_cursor into tmp_id,tmp_name;
        if FOUND then
            raise notice '%,%',tmp_id,tmp_name;
        else
            exit;
        end if;
    end loop;
    close my_cursor;
    raise notice 'finished...';
end;
$$ language plpgsql;

带参数的游标：

create or replace function cursor_demo2(tmp_id text)
    returns void as
$$
declare
    my_owner owner%rowtype;
    my_cursor cursor (tmp_id_ text) for select *
                                        from owner
                                        where "PersonID" = tmp_id_;
begin
    open my_cursor(tmp_id);
    loop
        fetch my_cursor into my_owner;
        if not found then
            exit;
        else
            raise notice 'found!-->%,%',my_owner."PersonID",my_owner."Name";
        end if;
    end loop;
    close my_cursor;
end;
$$ language plpgsql;

七、NoSQL

7.1 NoSQL数据库概述

关系数据库的局限：

数据库高并发读写需求难以满足

数据的高效存储和处理

数据库的高拓展性和高可用性需求

大数据处理需求

常见NoSQL数据库：

键值(key/value)存储数据库

介绍：

这一类数据库会用到一个哈希表，表中有一个特定的键和指针指向的数据

优点：

查找速度快

简单，易部署

缺点：

数据无结构化，通常只被当做字符串或二进制数据

如果只对部分值进行查询或更新的时候，key/value显得效率低下

应用场景：

内容缓存，主要用于处理大量数据的高访问负载

也用于一些日志系统

举例：

Redis，Oracle BDB，Tokyo，Voldmort

列存储数据库

介绍：

主要用来应对分布式存储的海量数据，键仍然存在，但是它们的特点是指向了多个列，这些列是由列家族来安排的

优点：

查找速度快

可拓展性强，更容易进行分布式扩展

缺点：

功能相对哦局限

应用场景：

分布式的文件系统

举例：

HBase，Riak，Cassandra

文档型数据库

介绍：

该类型的数据模型是版本化的文档，半结构化的文档以特定的格式存储。文档型数据库可以看做是键值数据库的升级版，允许之间嵌套键值，而却文档型数据库比键值数据库的查询效率高

优点：

数据结构要求不严格，表结构可变，不需要像关系型数据库一样需要预先定义表结构

缺点：

查询性能不高，而且缺乏统一的语法

应用场景：

与Key/value类似，Value是结构化的，不同的数据库能够了解Value的内容

举例：

MongoDB，CouchDB

图形数据库

介绍：

图形结构的数据库同其他行列以及刚性结构的SQL数据库不同，它是使用领过的图形数据库，并且能够拓展到多个服务器上

优点：

利用图结构相关算法，比如最短路径寻址，N度关系查找等

缺点：

很多时候需要对整个图像计算才能得出需要的信息，而且这种结构不太好做分布式的集群方案

应用场景：

社交网络，推荐系统，专注于构建关系图谱

举例：

Neo4J，InfoGrid，Infinite Graph

八、Postgresql补充

8.1 命令行常见参数

\l 查看所有数据库
\c 连接某个数据库
\d 查看当前所有表
\d xxx 查看xxx表的创建信息
\du 查看所有用户/角色
\df 查看所有存储过程
select * from pg_trigger; 查看所有触发器

8.2 函数

postgresql将存储过程和函数合并在一起，称为function；在postgresql 11开始引入procedure
语法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create function xxx()
returns integer as $$
declare
	xxx
begin
end;
$$ language plpgsql
或者是下面这种形式
create function qqq(out a)
as $$
declare
begin
end;
$$ language plpgsql;
argmode：IN OUT INOUT]，默认为IN
例子
1
2
3
4
5
6
7
8
9
create or replace function countRecords()
returns integer as $$
declare
	count integer;
begin
	select count(*) into count from student;
	return count;
end;
$$ language plpgsql
执行存储过程
1
2
3
select 存储过程名();
或者
select * from 存储过程名();
如果需要一个存储过程调用另一个存储过程
1
2
3
select into 自定义变量 from 存储过程名(参数);
若不关心返回值：
perform 存错过程名(参数)
删除存储过程
1
drop function if exists name(argmode argname argtype) [cascade|restrict]
注意：实际上并不注意参数的名字，因为判断函数的身份只需要输入参数的数据类型

cascade：级联删除依赖于存储过程的对象，比如触发器

restrict：若有任何依赖对象在，则拒绝删除该函数，这个为缺省值
PL/SQL基本语法
声明局部变量
1
2
3
declare
	变量名 变量类型
若声明变量为记录类型，变量类型为record，record不是真正的数据类型，只是一个占位符
条件语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
if xxx then
	statements
end if;
或者
if xxx then
	statements
else
	statements
end if;
或者
if xxx then
	statements
elsif yyy then
	statements
elsif zzz then
	statements
else
	statements
end if;
循环语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
loop
	statements
	continue when xxx
	exit [label] when expression
end loop

或者
while expression loop
	statements
end loop;

或者
for name in [reverse] expression...expression
loop
	statements
end loop
比如
for i in 1...10
	raise notice i
end loop;

遍历命令结果
for record in xxx loop
	statements
end loop;
优点

减少网络通信量

执行速度快

更强的适应性

降低了业务实现与应用程序的耦合

降低了开发的复杂性

保护了数据库元信息

缺点：

过程化处理逻辑相对比较复杂

若存储过程的参数或返回数据发生变化，一般需要修改存储过程的代码

调试复杂

可移植性差

8.3 触发器

触发器的分类：

按照执行次数

语句级触发器：for each statement

行级触发器：for each row

按照触发时间

before触发器

after触发器

instead of触发器：当触发时间发生后，执行触发器而不是执行产生触发事件的SQL语句

对于insert update delete三种触发事件，每种最多定义一个instead of触发器

触发器相关的特殊变量：

new

数据类型是record(标识一个函数返回一个未声明的行变量)，存有insert或update操作产生的新的数据行，对于语句级触发器，值是null

old

数据类型是record，存有delete或update操作产生的旧的数据行，对于语句级触发器，值是null

tg_op

数据类型是text，值是insert、update、delete的一个字符串，表明触发器是为哪个操作引发的
基本语法
1
2
3
4
5
6
7
8
9
10
create trigger qqq
[before | after | instead of] [after | insert | delete  ]
on 表名
[for each row|for each statement]
execute procedure 存储过程名(参数列表)
比如
create trigger qqq()
before insert on books
for each row
excute procedure 存储过程名()

8.4 安全

8.4.1 用户相关操作

用户/角色创建

create user/role qy with
login
nosuperuser -- 不是超级用户
nocreatedb -- 不能创建数据库
nocreaterole -- 不能创建角色
inherit -- 有角色继承权限
noreplication -- 没有数据库复制权限
connection limit -1 -- 数据库连接数不受限
password '123'

用户/角色修改语句

alter user/role qy xxx -- 修改用户属性
alter user/role qy rename to xxx -- 修改用户名称
alter user/role qy set xxx=yyy -- 修改用户的参数值
alter user/role qy reset xxx -- 重置用户=参数值

例子：

1
2
3

alter user/role qy
connection limit 10
password '123'

用户删除语句
1
drop user/role yq

在创建用户的同时赋予某个用户

1
2
3

create user qy with
in role qy_role
password '123';

将某个角色赋予某个用户
1
grant role1 to user1;

8.4.2 权限相关操作

授予权限
1
grant select on sales to qy;
收回权限
1
revoke select on sales to qy;
拒绝权限
1
deny update on sales to qy;

九、Mysql其它操作

9.1 导入和导出

# 导出
mysqldump -u root -p ctfd > ctfData.sql
# 导入
mysql -uroot -proot -D ctfd < ctfData.sql

十、其它

MySQL设置root远程登录

修改/etc/mysql/mysql.conf.d/mysqld.cnf中的bind-address

1
2
3

CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Courses

#数据库原理及应用

数据库原理及应用

https://d4wnnn.github.io/2023/02/28/Courses/数据库原理及应用/

作者

D4wn

发布于

2023年2月28日

许可协议

操作系统上一篇

Go 下一篇

	关键字	调用语法	返回值	应用场景
存储过程	procedure	call 存储过程()	理解为可以有0或者多个	一般用于更新
存储函数	function	select 函数()	只能是1个	一般用于查询结果为一个值并返回时