文章目录
- 数据库通用概念
- 数据库的产生
- 理论分类
- 关系数据库
- 非关系数据库
- 规模分类
- 内存型
- 文档型
- 服务型
- 应用场景
- 事务型OLTP
- 分析型OLAP
- 建模思路
- 范式建模(雪花型模型)
- 维度建模(星型模型)
- 大数据分析
- 数据迁移ETL
- 数据仓库DW
- 多维数据
- 数据分析
- 关系数据库(RDBMS)
- 基本概念
- ACID规则
- A-原子性
- C-一致性
- I-独立性
- D-持久性
- E-R模型
- SQL语言
- 关系数据库的分类
- 数据库设计范式
- 第一范式1NF:列不可再分(确保每列保持原子性)
- 第二范式2NF:消除部分依赖(确保每列都和主键列完全相关,而不是部分相关)
- 第三范式3NF:消除传递依赖(确保每列都和主键列直接相关,而不是间接相关)
- 实体间映射关系
- 1对1
- 1对n
- m对n
- 数据库建模步骤
- 概念数据模型CDM
- 逻辑数据模型LDM
- 物理数据模型PDM
- 内容
- 数据库database(单独一个数据库文件,一般会伴随生成日志文件,数据库是数据存储最基本的独立单元)
- 数据表table(数据表之间可形成外键关系,各表之间一般不独立)
- 临时表
- 记录item(数据表中的行(row),每一行是一条记录/实体)
- 字段column(数据表中的列,每一列是一种属性/一个字段)
- 索引index(通过对表中的某些列进行排序,提高查询速度,建立主键会自动建立关于主键的索引)
- 视图view(从表中查询得到的虚拟表,简化查询,保护私有数据,在SQL语句的执行过程中也会形成临时的视图表)
- 约束constraint(完整性约束,限制条件)
- primary key(主键)约束
- 【难点】foreign key(外键)约束
- not null(非空)约束
- unique(惟一)约束
- default (默认)约束
- check(校验)约束
- 锁lock
- 按层次分
- 库级锁
- 表级锁
- 行级锁
- 按类型分
- 共享锁
- 排他锁
- 触发器trigger(特定事件触发特定操作)
- 事务transaction(数据库的增删改操作流程)
- 存储过程procedure(对数据进行操作的函数)
- 数据类型
- 字符型
- 数字型
- 日期时间型
- 其他类型
- 特性
- 网络接口一般为TCP协议,IP地址或网址+端口号
- 服务型数据库的数据库文件一般存放在它自己的目录
- 大小写不敏感
- 自然语言脚本式SQL
- 与关键词重名可用中括号[]括起来区分
- SQL中的字符串用引号‘’括起来
- 存储特殊字符需加反斜杠\\转义
- 常用关系数据库
- Sqlite(文档型)
- Mysql(开源灵活)
- Sqlserver(微软系)
- Postgresql(学院派)
- Oracle(大型商用)
- Greenplum(大数据)
- 数据库的使用和运维
- 数据库安装运行
- 数据库的管理员和用户
- 数据库的备份和导入导出
- 备份/复制表结构
- 备份/复制表数据
- 数据库连接
- 连接字符串
- 连接池
- 编程语言接口
- 数据库安全
- 防SQL注入
- 对用户输入进行白名单校验
- 不要动态拼接SQL语句
- 数据库连接必须限制权限
- 机密信息如密码必须加密存储
- 捕获所有异常信息不能暴露给用户
- 对用户输入SQL进行转码过滤特殊字符
- 终极策略:改用ORM模型
- 数据库性能
- SQL优化
- 并发优化
数据库通用概念
数据库的产生
人类最开始持久化存储数据:文字、书籍、图书档案馆
信息科技兴起后数字化存储:数字文档、数字多媒体、树型文件管理
数据库:特点——持久化存储、优化读写、保证数据的有效性
理论分类
关系数据库
传统数据库,按照关系理论建立二维表格。
非关系数据库
没有关系或者是树数据结构的字典关系,表内部记录之间没有联系。
规模分类
内存型
只加载到内存的微型数据库。
文档型
单个数据库就是单个文件,单用户、简洁。
服务型
大规模数据库系统,一般开机启动作为服务,多用户、占用端口、复杂。
应用场景
事务型OLTP
联机事务处理OLTP(on-line transaction processing)
OLTP
系统强调数据库内存效率,强调内存各种指标的命令率,强调绑定变量,强调并发操作。
用于日常事务处理,要求速度快、实时性高,逻辑明确、准确性好,记录最原始、最精确的信息。
性能瓶颈在CPU。
分析型OLAP
联机分析处理OLAP(On-Line Analytical Processing)
OLAP 系统则强调数据分析,强调SQL执行时长,强调磁盘I/O,强调分区等。
用于统计性的决策分析,要求海量数据,可按照维度分析,可读性,可扩展性,一般也要求准实时性。
性能瓶颈在IO和内存。
建模思路
范式建模(雪花型模型)
传统数据库建模方法,严格按照三范式规范化建模。
维度建模(星型模型)
事实表-维度表。
不严格按照三范式,存在数据冗余,但可读性好,查询效率高。
大数据分析
数据迁移ETL
抽取-转换-加载,一般是指将已有的传统数据库数据加载到数据仓库的过程。
数据仓库DW
对海量历史数据进行汇总,适用于大数据分析的数据仓库。
多维数据
分析模型:数据立方体Cube;将数据构造成按照多维表,而不是关系数据库里的二维表。
维、维的粒度(层次)、度量、钻取、切片、旋转等概念。
数据分析
传统数据库也有数据分析,但比较受限。
大数据时代,可对专门适用于大数据分析的数据仓库进行数据分析。此过程也叫商业智能BI。
关系数据库(RDBMS)
基本概念
ACID规则
A-原子性
原子性很容易理解,也就是说事务里的所有操作要么全部做完,要么都不做,事务成功的条件是事务里的所有操作都成功,只要有一个操作失败,整个事务就失败,需要回滚。
比如银行转账,从A账户转100元至B账户,分为两个步骤:1)从A账户取100元;2)存入100元至B账户。这两步要么一起完成,要么一起不完成,如果只完成第一步,第二步失败,钱会莫名其妙少了100元。
C-一致性
一致性也比较容易理解,也就是说数据库要一直处于一致的状态,事务的运行不会改变数据库原本的一致性约束。
例如现有完整性约束a+b=10,如果一个事务改变了a,那么必须得改变b,使得事务结束后依然满足a+b=10,否则事务失败。
I-独立性
所谓的独立性是指并发的事务之间不会互相影响,如果一个事务要访问的数据正在被另外一个事务修改,只要另外一个事务未提交,它所访问的数据就不受未提交事务的影响。
比如现在有个交易是从A账户转100元至B账户,在这个交易还未完成的情况下,如果此时B查询自己的账户,是看不到新增加的100元的。
D-持久性
持久性是指一旦事务提交后,它所做的修改将会永久的保存在数据库上,即使出现宕机也不会丢失。
E-R模型
关系数据库基于E-R模型设计,即实体-关系模型(Entity-Relationship),实体即数据对象(每一个表的每一条记录都是一个实体,每条记录的不同的列是这个实体的各种属性),关系即数据对象之间的关系。
SQL语言
关系数据库使用SQL语言进行操作,不同品牌的数据库SQL语言略有差别,但大体一致。
关系数据库的分类
内存型关系数据库,无文件,直接保存在内存中,供程序直接修改与使用,临时性保存。(典型如redis,但它是非关系数据库)
文档型关系数据库,典型例子sqlite,对数据库文件直接操作,常用于移动设备或单用户简单场景。
服务型关系数据库,大多数数据库是这种类型,在系统中添加服务,程序需访问服务端口间接访问对应的数据库文件,支持多用户、复杂操作、复杂存储,但使用起来也复杂,占用资源也多。
数据库设计范式
(设计范式的资料中讲人话的不多,本该容易理解的地方被别人绕晕了)
第一范式1NF:列不可再分(确保每列保持原子性)
反例:学生信息表,学生班级列存储(商学院,13级,2班)
应改为:把学生班级列拆分为学院、年级、班级三列
第二范式2NF:消除部分依赖(确保每列都和主键列完全相关,而不是部分相关)
(注:主要指联合主键的情况)
反例:订单信息表,订单号与商品编号作为联合主键,商品名称、商品价格等只依赖于联合主键中的商品编号,而不依赖于订单号,存在部分依赖
应改为:拆分为两张表,订单表只存储订单相关,商品编号列作为外键;商品表只存储商品相关
第三范式3NF:消除传递依赖(确保每列都和主键列直接相关,而不是间接相关)
反例:用户表,在一张表中存储身份证号、用户ID、用户等级,用户等级依赖于用户ID,用户ID又依赖于身份证号,存在传递依赖
应改为:拆分为两张表,一张存储身份证号和用户ID,身份证号为主键;另一张表引用外键用户ID,存储用户等级
实体间映射关系
1对1
任意一方存储外键都可
1对n
n侧表存储外键
m对n
额外设计一张关系表,存储双方的关系
数据库建模步骤
概念数据模型CDM
即设计E-R模型。把现实问题抽象,形成数据库的初步设计概念。
逻辑数据模型LDM
进一步确定存储逻辑,遵循设计范式,确定数据表、数据列、主键、外键约束,特别是外键。要根据实体间映射关系的三种分类确定不同的设计思路,m对n的情况要特别建立关系表。
物理数据模型PDM
物理模型就是根据逻辑模型对应到具体的数据模型的机器实现。物理模型是对真实数据库的描述。如关系数据库中的一些对象为表、视图、字段、数据类型、长度、主键、外键、索引、约束、是否可为空、默认值。
物理模型会适配到具体的系统、数据库软件,可生成直接能够使用的SQL建表语句,形成最终的存储模型。
内容
数据库database(单独一个数据库文件,一般会伴随生成日志文件,数据库是数据存储最基本的独立单元)
数据表table(数据表之间可形成外键关系,各表之间一般不独立)
临时表
进行数据库操作需要用到临时表时,可通过某些关键字新建临时表,关闭数据库连接后会自动销毁,不会对原数据库产生影响
记录item(数据表中的行(row),每一行是一条记录/实体)
字段column(数据表中的列,每一列是一种属性/一个字段)
索引index(通过对表中的某些列进行排序,提高查询速度,建立主键会自动建立关于主键的索引)
视图view(从表中查询得到的虚拟表,简化查询,保护私有数据,在SQL语句的执行过程中也会形成临时的视图表)
约束constraint(完整性约束,限制条件)
约束是用来确保数据的准确性和一致性。常见的约束就是主键、外键、非空等。
实体完整性:主键。
域完整性:数据列的类型、长度、是否允许空。
参照完整性:外键与主表的主键的数据应当一致。
用户定义完整性:用户自定义的约束。
primary key(主键)约束
被约束的列或一组列作为主键,唯一且不能为空
【难点】foreign key(外键)约束
foreign
key约束指定某一个列或一组列作为外部键,其中包含外键的表称为子表(从表),外键所引用的主键的表称为父表(主表),外键名称规范:fk_从表名_主表名。
定义语句:
CONSTRAINT E_SAL FOREIGN KEY(emp_id,account) REFERENCES EmployeeInfo
(emp_id,account))
在E_SAL表中定义外键,引用EmployeeInfo中的主键。E_SAL为子表,EmployeeInfo为主表。外键的命名为
fk_外键所在的表名_外键引用的表名。因为外键所在的表为从表,所以上式可以写为
fk_从表名_主表名。在维度模型中,维度表是主表,事实表为从表。
更新、删除操作规则:
在删除或更新有primary key值的行,且该值与子表的foreign
key中一个或多个值相匹配时,会引起匹配完整性的丧失。
在foreign key创建语法中,提供了可选的on update和on
delete子句,可用此保持引用完整性。
on update / on delete
no action|cascade|restrict|set null|set default
no
action:更新或删除父表中的数据时,如果会使子表中的外键违反引用完整性,该动作将被禁止执行。不过在某些条件下,可出现暂时的,但在数据的最终状态中,不能违反外键的引用完整性。
cascade:
当父表中被引用列的数据被更新或删除时,子表中的相应的数据也被更新或删除。
restrict:与no
action规则基本相同,只是引用列中的数据永远不能违反外键的引用完整性,暂时的也不行。
set
null:当父表数据被更新或删除时,子表中的相应数据被设置成Null值,前提是子表中的相应列允许null值。
set
default:当父表数据被更新或删除时,子表中的数据被设置成默认值。前提是子表中的相应列设置有默认值。
not null(非空)约束
被约束的列列值不能为空
unique(惟一)约束
被约束的列列值不能重复,可允许为空,但只能有一个空值
default (默认)约束
给列设置默认值
check(校验)约束
用来检查字段值所允许的范围。DBMS每当执行delete,insert或update语句时,都对这个约束过滤。如果为true,则执行。否则,取消执行并提示错误。
锁lock
锁用来限制并发情况下的写操作(增删改),防止产生冲突。此处的锁都是悲观锁,设计人员在数据表中定义的时间戳等标识字段是乐观锁。
按层次分
库级锁
数据库加锁(超级管理员级别)
表级锁
每个表都有单独的锁(普通管理员级别)
行级锁
每个数据行都有单独的锁(用户级别)
按类型分
共享锁
多个用户均可加锁,加锁后只能读不能写
排他锁
禁止任何人给表加锁,更不能做写操作
触发器trigger(特定事件触发特定操作)
事务transaction(数据库的增删改操作流程)
存储过程procedure(对数据进行操作的函数)
数据类型
字符型
char(长度)——定长非unicode
varchar(一般长度)——变长非unicode,变长能高效利用存储空间
nvarchar(一般长度)——变长unicode,unicode对汉字支持比较好
[n]varchar(MAX)——存储大容量字符串
数字型
bit——0和1
int(长度)——整形
decimal(整体长度,小数长度)——浮点数
日期时间型
datetime(date/time)——存放日期时间
timestamp——实时时间戳
其他类型
二进制型——存储图片等二进制内容
特性
网络接口一般为TCP协议,IP地址或网址+端口号
服务型数据库的数据库文件一般存放在它自己的目录
大小写不敏感
自然语言脚本式SQL
与关键词重名可用中括号[]括起来区分
SQL中的字符串用引号‘’括起来
存储特殊字符需加反斜杠\转义
常用关系数据库
对比:
sqlite | mysql | sqlserver | oracle | postgresql | greenplum | |
性能与适用范围 | 小数据量,测试demo环境,应用内自维护数据库,移动APP | 互联网,关系简单,中等数据量,简单操作性能高 | windows平台服务,操作简单,中等数据量 | 大数据量,大企业平台,复杂关系,高可靠性 | 适合关系特别复杂的应用,大数据量 | 适合关系特别复杂的应用,大数据量 |
开放性 | 完全开源 | 商业开源 | 仅限于windows | 商业 | 完全开源 | 完全开源 |
数据库特性 | 最简单的数据库引擎,单库单文件,内置SQL也有很多删减,没有服务和用户系统 | 简单开源 | 简单易用 | 传统关系数据库的最高水平,大型企业级 | 学院派,设计理念先进合理,支持标准SQL,支持json等非关系数据类型 | 基于postgresql,适合构建集群支持大数据 |
字符串边界 | 单引号 | 单引号 | 单引号 | 单引号 | 单引号 | 单引号 |
多表联查 | 只支持左外连接 | 所有连接 | 所有连接 | 所有连接 | 所有连接 | 所有连接 |
数据类型 | 数据类型只与数据本身有关,与容器列无关,可选设置列的首选数据类型 | 数值、字符串、日期时间等标准化格式,同一列只能存储同类数据 | 数值、字符串、日期时间等标准化格式,同一列只能存储同类数据 | 数值、字符串、日期时间等标准化格式,同一列只能存储同类数据 | 支持json等非结构数据类型 | 支持json等非结构数据类型 |
Sqlite(文档型)
数据类型较为独特,数据类型只与数据本身有关,与容器列无关,可选设置列的首选数据类型:
数据的存储类——
列的首选数据类型——
Mysql(开源灵活)
Sqlserver(微软系)
Postgresql(学院派)
Oracle(大型商用)
Greenplum(大数据)
数据库的使用和运维
数据库安装运行
Linux命令行安装,windows图形界面安装可选组件,默认添加到服务。
数据库的管理员和用户
首次运行进入管理程序设置管理员账户的密码。
添加其他用户,只需像操作一般数据一样往系统自带的user数据表中添加用户项。
数据库SQL交互程序一般开放一些元数据供管理员查看。
数据库的备份和导入导出
备份/复制表结构
导出建表语句,直接在另一处执行,即可达到复制效果。
备份/复制表数据
由于数据过大,不能采用导出包含数据的建表语句的方法。
先在另一处构建好表结构。导出数据,到另一处导入数据。
数据库连接
连接字符串
服务主机(IP地址)、端口、数据库、用户名、密码;其他参数(连接池、字符编码等)
连接池
数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。提高执行效率。
在数据库连接的时候会有设置连接池的选项。
编程语言接口
编程语言中把数据库的连接封装为某个编程对象,然后开放为对外的接口。于是数据库的连接除了通过数据库客户端直连,还可以通过接口连接。
例:jdbc/odbc
数据库安全
防SQL注入
对用户输入进行白名单校验
不要动态拼接SQL语句
数据库连接必须限制权限
机密信息如密码必须加密存储
捕获所有异常信息不能暴露给用户
对用户输入SQL进行转码过滤特殊字符
终极策略:改用ORM模型
数据库性能
SQL优化
并发优化