MySQL数据库整体知识点简述
目录
第一章：数据库系统概述
第二章：信息与数据模型
第3章 关系模型与关系规范化理论
第四章——数据库设计方法
第六-七章——MySQL存储引擎与数据库操作管理
第九章——索引
第10章——视图
第11章——MySQL存储过程与函数
第12章——MySQL 触发器与事件调度器
第13章——MySQL 权限管理
第14章——事物与MySQL的并发控制
第15章——MySQL的备份与恢复
第16章——MySQL的日志
第一章：数据库系统概述
定义：数据库(Database,简称DB)是长期储存在计算机内、有组织的、可共享的大量的数据集合。
数据库管理系统（Database Management System，简称DBMS）是位于用户与操作系统之间的一系列以统一的方式管理和维护数据库中数据的软件集合。
|
|
产业化的DBMS称为数据库产品，常用的数据库产品有Oracle、MySQL、SQL Server、DB2
数据库系统的特点：
1、数据结构化
2、数据的共享性高，冗余度低，易扩充
3、数据独立性高
4、DBMS对数据统一管理和控制
1. 数据的安全性（Security）保护
2. 数据的完整性（Integrity）检查
3. 并发（Concurrency）控制
4. 数据库恢复（Recovery）
数据库系统结构：
数据库系统的三级模式结构是指数据库系统是由外模式、模式和内模式三级构成
外模式：局部数据的逻辑结构和特征的描述
模式：全体数据的逻辑结构和特征的描述
内模式：数据物理结构和存储方式的描述
三级模式与二级映像
-
- DBMS内部实现这三个抽象层次的联系和转换
|
|
用途：
（1）保证数据的逻辑独立性
（2）保证数据的物理独立性
第二章：信息与数据模型
- 信息的三种世界及描述
-
-
-
-
-
- 信息的三种世界是指现实世界、信息世界和计算机世界
-
-
-
-
-
|
|
-
-
-
-
-
-
- 实体(Entity)。实体可以分为事物实体和概念实体，
-
-
-
-
-
- 一个学生、一台设备、等是事物实体，一门课、一个班级等称为概念实体。
- 特征( Characteristic)，利用实体的特征可以区别不同的实体。
- 对于人事管理，职工的特征可选择姓名、性别、年龄、工资、职务等；而在描述一个人健康情况时，可以用职工的身高、体重、血压等特征表示。
-
-
-
-
-
-
- 实体集（Entity Set）。 例如：学生、工人、汽车等都是实体集。
-
-
-
-
-
信息世界通过概念模型（也称信息模型）、过程模型和状态模型反映现实世界，它要求对现实世界中的事物、事物间的联系和事物的变化情况准确、如实、全面地表示。
概念模型通过E-R图中的对象、属性和联系对现实世界的事物及关系给出静态描述。
过程模型通过信息流程图和数据字典描述事物的处理方法和信息加工过程。
状态模型通过事物状态转换图对事物给出动态描述。）
|
|
- 数据模型
- 数据模型主要用来抽象、表示和处理现实世界中的数据和信息，以便于采用数据库技术对数据进行集中管理和应用,是对客观事物及其联系的数学描述。
在数据库系统中，在不同层次采用不同的数据模型。一般可分为概念层、逻辑层、物理层
(1)概念层
概念层是数据抽象级别的最高层，其目的是按用户的观点来对现实世界建模。概念层的数据模型称为概念数据模型，简称概念模型。
常用的概念模型有实体—联系模型（Entity-Relationship Model，简称E-R模型）。
(2)逻辑层
逻辑层是数据抽象的中间层，描述数据库数据整体的逻辑结构。这一层的数据抽象称为逻辑数据模型，简称数据模型。
常见的数据模型有层次模型（Hierarchical Model）、网状模型（Network Model）、关系模型（Relation Model）和面向对象模型（Object Oriented Model）。
层次模型：
|
|
网状模型：
|
|
关系模型：
|
|
数据模型通常由数据结构、数据操作和完整性约束三部分组成。
(1)数据结构
数据结构描述数据库的组成对象以及对象之间的联系。
(2)数据操作
数据操作是指对数据库中各种对象（型）的实例（值）允许执行的操作的集合，包括操作及有关的操作规则。
(3)完整性约束
数据的完整性约束条件是一组完整性规则。
(3)物理层
物理层是数据抽象的最底层，用来描述数据物理存储结构和存储方法。
物理数据结构一般都向用户屏蔽，用户不必了解其细节。
- 概念模型
- 实体（Entity）
- 客观存在并可相互区别的事物称为实体。可以是具体的人、事、物或抽象的概念。
- 属性（Attribute）
- 实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。
- 码（Key）
- 唯一标识实体的属性集称为码。
- 域（Domain）
- 属性的取值范围称为该属性的域。
- 实体型（Entity Type）
- 用实体名及其属性名集合来抽象和刻画同类实体称为实体型
- (学号, 姓名, 性别，专业)
- 实体集（Entity Set）
-
-
-
-
-
-
-
- 同型实体的集合称为实体集，如：全体学生
-
-
-
-
-
-
-
(7) 联系（Relationship）
现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部
的联系和实体之间的联系
一对一联系（1:1）
一对多联系（1:n）
多对多联系（m:n）
E-R模型
|
|
|
|
完整版E-R
|
|
转换为关系模式
|
|
实体间是1:1联系如何转换为关系模式
|
|
实体间是1:n联系如何转换为关系模式
|
|
实体间是m:n联系如何转换为关系模式
|
|
第3章 关系模型与关系规范化理论
1.关系模型概述——基本术语
（1)元组(Tuple) 行
元组也称记录，关系表中的每行对应一个元组，组成元组的元素称为分量。数据库中的一个实体或实体之间的一个联系均使用一个元组来表示。
（2)属性(Attribute)字段
关系中的每列对应一个域。由于域可以相同，因此为了加以区分，必须给每列一个命名，这个命名就称为属性。N 目关系必有n 个属性。
（3)候选码(Candidate key) 主键们
若关系中的某一属性或属性组的值能唯一地标识一个元组，则称该属性或属性组为候选码(Candidate key)。
（4)主码(Primary key) 主键
若一个关系中有多个候选码，则选定其中一个为主码（也可以称为主键，主关键字）。
（5)全码(All-key) 全是主键
在最简单的情况下，候选码只包含一个属性；在最极端的情况下，关系模式的所有属性是这个关系模式的候选码，称为全码。全码是候选码的特例。
（6)主属性(Prime attribute)和非主属性(Non-prime attribute)
在关系中，候选码中的属性称为主属性，不包含在任何候选码中的属性称为非主属性。
（7)代理键
代理键是具有DBMS分配的唯一标识符，该标识符已经作为主键添加到表中。
2.关系模型概述——关系类型
关系数据库中的关系可以有3种类型：
-
-
-
-
- 基本表 是实际存在的表，它是实际存储数据的逻辑表示；
-
-
- 查询表 是查询结果表或查询中生成的临时表；
- 视图表 是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据。
-
3.关系模型概述——关系模式
关系的描述称为关系模式(Relation Schema)，它可以形式化地表示为:
R(U，D，Dom，F)
其中：
R：关系名；
U：组成该关系的属性的集合；
D：属性组U 中的属性所来自的域；
Dom：属性向域的映像集合；
F：为属性间数据依赖关系的集合。
4.关系操作
关系操作包括数据查询、数据维护（操作、定义）和数据控制三大功能。
(1)数据查询指数据检索、统计、排序、分组以及用户对信息的需求等功能。
(2)数据维护指数据增加、删除、修改等数据自身更新的功能。
(3)数据控制是为了保证数据的安全性和完整性而采用的数据存取控制及并发控制等功能。
5.关系的完整性
关系模型允许定义3 类完整性约束：实体完整性、参照完整性和用户自定义的完整性。
实体完整性和参照完整性是关系模型必须满足的完整性约束条件，称为两个不变性，应该由关系系统自动支持；
用户自定义的完整性是应用领域需要遵循的约束条件，体现了具体领域中的语义约束。
实体完整性规则：
若属性A 是基本关系R 的主属性，则属性A 不能取空值(即关系中的主键不能为空值)。也不能重复。
参照完整性规则：
若属性(或属性组)F 是基本关系R 的外码，它与基本关系S 的主码Ks 相对应(基本关系R 和S 有可能是同一关系)，则对于R 中每个元组在F 上的值必须为以下值之一。
(1)取空值(F 的每个属性值均为空值)。
(2)等于S 中某个元组的主码值。
用户定义的完整性：
就是针对某一具体关系数据库的约束条件的，它反映某一具体应用所涉及的数据必须满足的语义要求。
6.传统的集合运算
传统的集合运算是二目运算，
-
-
- 设关系R和S均是n元关系，且相应的属性值取自同一个值域，则可以定义四种运算：
-
|
|
|
|
|
|
|
|
7.专门的关系运算
（1）.选择
|
|
|
|
2.投影
|
|
|
|
3.连接
|
|
|
|
|
|
4.除
|
|
象集
|
|
象集举例
|
|
利用象集计算
|
|
|
|
最后的结果就是存在PQ投影的象集。也就是根据S里的内容去R中找就彳亍
|
|
8.关系代数操作小结
|
|
9.数据库设计的规范化
（1）规范化
规范化是指用形式更为简洁、结构更加规范的关系模式取代原有关系模式的过程。
（2）数据依赖
数据依赖是指通过一个关系中属性间值的相等与否体现出来的数据间的相互关系，是现实世界属性间相互联系的抽象，是数据内在的性质。
数据依赖共有3种：函数依赖(Functional Dependency，FD)、多值依赖(MultiValued Dependency，MVD)和连接依赖(Join Dependency，JD)，其中最重要的是函数依赖和多值依赖。
函数依赖：A字段可以唯一确定B字段，B依赖于A
|
|
|
|
10.范式
关系按其规范化程度从低到高可分为5 级范式(Normal Form)，
分别称为1NF、2NF、3NF(BCNF)、4NF、5NF。
规范化程度较高者必是较低者的子集，即5NF⊆4NF⊆BCNF⊆3NF⊆2NF⊆1NF
一个低一级范式的关系模式，通过模式分解可以转换成若干个高一级范式的关系模式的集合，这个过程称作规范化。
（1）第一范式（1NF）
|
|
例如：
|
|
（2）第二范式（2NF）
|
|
举例:
|
|
|
|
|
|
（3）第三范式（3NF）
|
|
|
|
（4）BCNF
|
|
第四章——数据库设计方法
1.数据库设计步骤
1．需求分析阶段
进行数据库设计首先必须准确了解与分析用户需求(包括数据与处理需求分析）。
2．概念结构设计阶段
它通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。
3．逻辑结构设计阶段
将概念结构转换为某个DBMS所支持的数据模型，并对其进行优化。
4．物理结构设计阶段
为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法)。
5．数据库实施阶段
设计人员运用DBMS提供的数据库语言(如SQL)及其宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。
6．数据库运行和维护阶段
对数据库系统进行评价、调整与修改。
|
|
【1】概念结构设计
|
|
【2】逻辑结构设计
概念结构是独立于任何一种数据模型的信息结构。逻辑结构设计的任务就是把概念结构设计阶段设计好的基本E-R图转换为与选用DBMS产品所支持的数据模型相符合的逻辑结构。
步骤：
⑴初始关系模式设计；
⑵关系模式规范化 ；
⑶模式的评价与改进。
【3】物理结构设计
◼ 物理结构指数据库在物理设备上的存储结构和存取方法。
◼数据库物理设计指为逻辑数据模型选取一个最适合应用环境的物理结构的过程。
◼依赖于所选用的DBMS和计算机硬件环境，要求设计人员充分了解DBMS的内部特征（尤其是存储结构和存取方法）和性能，充分了解应用环境的处理频率和响应时间要求，充分了解外存的特性。
- 存储方法（建立存取路径）
顺序、散列、聚簇
- 物理存储结构
如：索引、日志、备份
【4】数据库实施
（1）数据装载
（2）调试和运行应用程序
【5】数据库运行和维护
- （1）数据库的备份和恢复
- （2）数据库的安全性和完整性控制
- （3）数据库性能的监督、分析和改善
- （4）数据库的重组织与重构造
银行取款过程数据流图
|
|
2.数据字典：
是系统中各类数据描述的集合，它的功能是存储和检索各种数据描述，并为DBA提供有关的报告。
第六-七章——MySQL存储引擎与数据库操作管理
1.查看存储引擎
|
|
2.InnoDB存储引擎
支持事物和外键，采用B+树索引
InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID）存储引擎；
InnoDB是为处理巨大数据量提供最大性能而设计的；
InnoDB引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池；
InnoDB支持外键约束；
InnoDB被用在众多需要高性能的大型数据库站点上。
3.MyISAM
不支持事物和外键，采用B+树索引
如果某个表主要提供OLAP支持，建议选用MyISAM存储引擎。
MyISAM表可以被压缩，而且最早支持全文索引，但MyISAM表不支持事务，也不支持外键（foreign key）。
如果某张表需要执行大量的select语句（或Insert），出于性能方面的考虑，MyISAM存储引擎是更好的选择。
4.MEMORY
采用哈希索引
Memory存储引擎（之前称为HEAP存储引擎）将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。
它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。它默认使用哈希(HASH)索引，而不是我们熟悉的B+树索引。
优势：速度非常快
缺点：只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型，会浪费内存。
存储引擎的选择
|
|
|
|
|
|
5.字符集
Unicode编码中一个中文字符占2个字节。
UTF-8 编码中一个中文字符占3个字节
GB2312是我国制定的中文编码，GBK是对GB2312的扩充，CP936是微软公司在GBK基础上开发的windows下的中文编码方式。这些编码均使用2个字节表示中文。
查看MySQL的字符集
show character set 查看所有可以使用的字符集
MySQL校验规则
|
|
6.MySQL之数据库SQL语句
【1】创建数据库
|
|
例如：create database if not exists GWJ；
【2】修改数据库
|
|
例如：alter database GWJ character set GBK；
【3】删除数据库
例如：drop database if exists GWJ；
7.MySQL之运算符
【1】算术运算符
【2】比较运算符
|
|
【3】逻辑运算符
|
|
8.MySQL之表操作
【1】创建表
|
|
例如：create table if not exists G1；
【2】查看表
|
|
例如：显示数据库GWJ中的所有表：
show tables;
查看表-显示表的结构
|
|
【3】修改表
|
|
【4】复制表
|
|
【5】删除表
|
|
删除G1和G2表
例如：drop table if exists G1,G2;
-----------------------------------------------------------------------------------------------
9.MySQL之约束控制
【1】数据完整性
|
|
【2】字段的约束
|
|
【3】参照完整性
|
|
外键约束
|
|
例题：
|
|
第九章——索引
1.索引简介
索引（index）是帮助MySQL高效获取数据的数据结构。在数据之外，数据库系统还维护满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据上实现高级查找算法，这种数据结构就是索引。
|
|
索引有两种存储类型：B型树（BTREE）索引和哈希（HASH）索引。其中B型树为系统默认索引存储类型。
优点
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参照完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
特征
|
|
分类
|
|
2.创建索引
|
|
|
|
例题：
|
|
3.查看索引
4.删除索引
|
|
例题：删除newTable中的索引的name_index
drop index name_index on newTable;
第10章——视图
1.概述
|
|
2.视图的优势
（1）增强数据安全性
（2）提高灵活性，操作变简单。
（3）提高数据的逻辑独立性。
3.视图的工作机制
当调用视图的时候，才会执行视图中的SQL，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。
4.创建视图
|
|
5.删除视图
|
|
6.查看视图定义
|
|
7.修改视图定义
|
|
第11章——MySQL存储过程与函数
1.变量
|
|
2.局部变量
|
|
|
|
3.SQL流程控制语句
（1）BEGIN…END语句块。BEGIN…END可以定义SQL语句块，这些语句块作为一组语句执行，允许语句嵌套。关键字BEGIN定义SQL语句的起始位置，END定义同一块SQL语句的结尾。它的语法格式如下：
|
|
（2）IF…ELSE条件语句
|
|
|
|
4.存储过程
【1】概念
存储过程（stored procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
【2】优点
1. 存储过程增强了SQL语言的功能和灵活性
2. 存储过程允许标准组件式编程
3. 存储过程能实现较快的执行速度
4. 存储过程能够减少网络流量
5. 存储过程可被作为一种安全机制来充分利用
【3】创建和使用存储过程
|
|
|
|
【4】游标的使用
|
|
|
|
【5】系统信息函数
|
|
第12章——MySQL 触发器与事件调度器
1.概念
触发器：是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
触发器的优点：
（1）安全性：可以基于数据库的值使用户具有操作数据库的某种权利。
（2）实现复杂的数据完整性规则
（3）审计：可以跟踪用户对数据库的操作。
（4）实现复杂的非标准的数据库相关完整性规则。
2.创建使用触发器
|
|
例题：
|
|
|
|
3.查看触发器
|
|
例题：
|
|
4.删除触发器
|
|
说明：数据库（schema_name）是可选的。如果省略了schema，将从当前数据库中删除触发程序。
例题;
|
|
5.事件概念
事件调度器(event scheduler)：可以用做定时执行某些特定任务，可以精确到每秒钟执行一个任务，而操作系统的计划任务只能精确到每分钟执行一次。
事件调度器有时也可称为临时触发器(temporal triggers)，因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器(triggers)是基于某个表所产生的事件触发的。
6.创建事件
|
|
例题：
|
|
类型一：每隔一分钟插入一条数据
|
|
类型二：特定时间插入一条数据
|
|
7.修改事件
|
|
|
|
8.删除事件
|
|
例题：
|
|
第13章——MySQL 权限管理
【1】权限系统
在MySQL数据库中可以使用3种不同类型的安全检查：
（1）登录验证
（2）授权
（3）访问控制
在MySQL权限工作原理：
阶段一：服务器检查是否允许连接；
阶段二：假定允许，服务要检查用户发的每一个请求，判断是否有足够的权限
【2】用户管理
- 添加用户
使用create user创建用户账号
|
|
|
|
使用insert语句新建普通用户
|
|
使用grant语句来新建普通用户
可以使用grant语句来创建新的用户，在创建用户时可以为用户授权。grant语句是MySQL中非常重要的一个命令，不仅可以创建用户、授予权限、还可以修改密码（后面讲）。
- 查看用户
|
|
例题：
|
|
- 修改用户账号
|
|
- 修改用户口令（密码）
|
|
|
|
- 删除用户
|
|
|
|
【3】账号权限管理
- 权限授予
|
|
- 权限的撤销
|
|
第14章——事物与MySQL的并发控制
【1】事物的概念
|
|
数据库与文件系统在数据管理优势在于数据库实现了数据的一致性以及并发性。对于数据库管理系统而言，事务与锁是实现数据一致性与并发性的基石。
【2】MySQL事物控制语句
|
|
【3】事物的ACID特性
术语“ACID”是一个简称，每个事务的处理必须满足ACID原则，即原子性(A)、一致性(C)、隔离性(I)和持久性(D)。
|
|
|
|
【4】MySQL的并发控制
在单处理机系统中，事务的并行执行实际上是这些并行事务轮流交叉进行，这种并行执行方式称为交叉并发方式。
在多处理机系统中，每个处理机可以运行一个事务，多个处理机可以同时运行多个事务，实现事务真正的并发运行，这种并发执行方式称为同时并发方式。
当多个用户并发地存取数据库时就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制可能会存取和存储不正确的数据，就会出现数据的不一致问题。
- 丢失更新问题
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新。
|
|
（2）脏读问题
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另—-个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未交的数据依赖关系，这种现象被形象地叫作“脏读”。
|
|
（3）不可重复读问题
当一个事务多次访问同一行而且每次读取不同的数据时，会发生不可重复读( unrepeatableread)问题。
|
|
（4）幻读问题
当一个事务对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻读(phantom read)问题。
|
|
四种隔离级别比较
|
|
【5】锁
当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定，它是实现数据库并发控制的主要手段。
锁定可以防止丢失更新、脏读、不可重复读和幻读。
锁是一种用来防止多个客户端同时访问数据而产生问题的机制。
【6】MySQL三种锁的特点
|
|
不同存储引擎支持不同的锁机制
|
|
死锁：
|
|
第15章——MySQL的备份与恢复
【1】备份分类
|
|
【2】备份
mysqldump程序可以备份数据库表的结构，还可以备份一个数据库，甚至整个数据库系统，只需在mysql客户端实用程序的运行界面中输入mysqldump --help命令，即可查看到mysqldump程序对应的命令。
（1）备份表
|
|
（2）备份数据库
|
|
（3）还原
|
|
|
|
（4）导出备份语句 select into … outfile
|
|
这种方法有一点不足，就是只能导出或导入数据的内容，而不包括表的结构，若表的结构文件损坏，则必须先设法恢复原来表的结构。
|
|
第16章——MySQL的日志
MySQL日志是记录MySQL数据库的日常操作和错误信息的文件。
|
|
（1）错误日志
错误日志是MySQL数据库中最常用的一种日志。
错误日志主要用来记录MySQL服务的开启、关闭和错误信息。
在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。
默认情况下，错误日志存储在MySQL数据库的数据文件夹下。
|
|
（2）二进制日志
|
|
不能直接打开并查看二进制日志。如果需要查看二进制日志，必须使用mysqlbinlog命令
|
|
二进制日志还原数据库
|
|
（3）慢查询日志
默认情况下，慢查询日志功能是关闭的。在windows下，通过修改my.ini文件的slow-query-log选项可以开启慢查询日志。
|
|
执行时间超过指定时间的查询语句会被记录到慢查询日志中。
慢查询日志也是以文本文件的形式存储的。可以使用普通的文本文件查看工具来查看。
（4）通用查询日志
通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、更新语句、查询语句等。
默认情况下，通用查询日志功能是关闭的。在windows下，通过修改my.ini文件的log选项可以开启通用查询日志。
在[mysqld]组，把general-log的值设置为1（默认是0），重新启动MySQL服务即可开启查询日志，general_log_file表示日志的路径，形式如下:
|
|
