MySQL数据库知识体系

---

MySQL为关系型数据库

---

一 数据库介绍

1 相关定义

数据（data）

描述事物的符号记录称为数据；
可以是数字、文字、图形图像等，数据有多种表现形式，都可以经过数字化后存入计算机。

数据库(database、DB)

数据库是指长期存储在计算机内、有组织、可共享的数据集合；
数据库的数据是按一定的数据模型组织、描述、存储；
数据库的数据具有较小的冗余度、较高的数据独立性和扩展性。

数据库管理系统（DataBase Management System , DBMS）

数据库管理系统是操纵和管理数据库的大型软件，用于建立、使用和维护数据库。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

一般我们说的MySQL、Oralce等数据库，指的是数据库管理系统（DBMS）。

2 数据模型-数据库原理及应用

数据模型实际上是模型化数据和信息的工具。根据模型应用的不同目的，可将模型分为两大类：

概念层数据模型（概念模型）

从数据的语义视角来抽取模型，是按用户的观点来对数据和信息进行建模，主要用于数据库设计。
概念模型主要使用实体-联系（Entity-Relationship）表示，也称E-R模型。

组织层数据模型（组织模型）

从数据的组织层次来描述数据，是按计算机的观点来对数据和信息建模，主要用于DBMS的实现。

3 概念模型

基本概念

概念层数据模型是指抽象现实系统中有应用价值的元素及其关联关系；
反映现实系统中有应用价值的信息结构；
不依赖于数据的组织层数据模型。

常用的概念层数据模型有：实体-联系（Entity-Relationship, E-R）模型、语义对象模型。

实体（Entity）

客观存在的、可以相互区别的事物，可以是具体的人、事、物，也可以是抽象的概念。

实体集（entity collection）

具有相同性质的多个实体所组成的集合。

属性（attribute）

实体所具有的特征，一个实体可以用若干属性来刻画。

属性的域（domain）

属性的取值范围。

码或关键字（key）

在实体属性中，能够区别实体集中不同实体的某个属性或某几个属性的组合。

图1 使用E-R图表示员工实体及其属性

4 实体间的联系

实体与实体间的关系，主要有以下三种。

实体、属性与实体之间的关系结合起来就是一张完整的E-R图。用E-R图表示的概念模型独立于具体的DBMS所支持的数据模型，是数据模型的共用基础。

5 组织模型

层次模型

用树形结构表示实体和实体之间的关系
典型的层次结构模型数据库有：IMB公司的IMS

网状模型

用图形结构标识实体与实体之间的联系的数据模型称为网状数据模型，典型网状模型数据库有：CODASYL系统。

关系模型

把数据看成是二维表中的元素，而这个二维表就是关系。
用关系（表格数据）表示实体和实体之间联系的模型称为关系数据模型，典型关系数据模型有：MySQL、Oracle

6 数据库分类

按传统模型分类

• 层次式数据库
• 网络式数据库
• 关系型数据库

按应用分类

互联网中，最常用的数据库模型是两种，即关系型数据库和非关系型数据库。

• 关系型数据库产品主要有（Oracle、MySQL、DB2、Access、SQLlite等）
• 非关系型数据库（Redis、Mongdb、Hbase等）
• 实时/时序数据库（RTDB-Real Time DataBase）：适用于工业监控领域，包括数据采集，海量监测数据的压缩、存储及检索，基于监测数据的反馈及控制等功能。

7 关系型数据库与非关系型数据库比较

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织。

• 优点：
  1、易于维护：都是使用表结构，格式一致；
  2、使用方便：SQL语言通用，可用于复杂查询；
  3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
• 缺点：
  1、读写性能比较差，尤其是海量数据的高效率读写；
  2、固定的表结构，灵活度稍欠；
  3、高并发读写需求，对传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
  非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。
• 优点：
  1、格式灵活：存储数据的格式可以是key-value形式、文档形式、图片形式等，使用灵活，应用场景广泛；
  2、使用方便：可以使用硬盘或者随即存储器作为载体，而关系型数据库只能使用硬盘；
  3、高扩展性。
• 缺点：
  1、不提供sql支持，学习和使用成本较高；
  2、无事务处理；
  3、数据结构相对复杂，复杂查询方面稍欠。

---

二 关系型数据库设计

1 E-R图设计原则

• E-R图的设计质量决定了关系数据库的设计质量。而E-R图的设计又依赖于软件前期的需求分析与功能设计。
• 数据库设计人员通常采用“一事一地”的原则从功能描述中抽象出E-R图。
  • 实体不是一个具体的对象，而是某一类别所有事物的统称；
  • 属性通常用于表示实体的某种特性，也可以表示实体关系的特征；
  • 属性应该存在且只存在于某一地方（实体或关系中）；
  • 实体是一个单独的 个体，不能存在于另一个实体中成为另一个实体的属性；
  • 同一个实体在一个E-R图中仅能出现一次。

2 关系型数据库设计原则

• 根据E-R图来设计数据库的逻辑表结构
  • 为E-R图中的每一个实体建一张表
  • 实体的属性作为该表的字段
  • 为每张表定义一个主键（如果需要，可以向表中添加一个没有实际意义的字段作为该表的主键，比如Id）
  • 增加外键标识E-R图中的一对多关系
  • 建立新表表示E-R图中的多对多关系
  • 为表中的字段选择合适的数据类型
  • 根据需要定义约束条件
  • 评价关系的质量，进行表的改进

3 数据的规范化

（1）范式

从关系模型满足的确定约束条件称为范式。根据满足约束条件的级别不同，范式由低到高分为1NF、2NF、3NF、BCNF、4NF等。

第一范式（1NF）

1NF是最低的规范化。如果关系R中所有属性的值域都是简单域，属性不可再分，那么关系模型R是1NF，记作R∈1NF。

第二范式（2NF）

如果一个关系R属于1NF，且所有的非主属性都完全依赖于主属性，则称之为第二范式，记作R∈2NF。

工号、证书编号、证书名称是主码，但是发证部门依赖证书名称，即证书名称->发证部门，由于不满足非主属性完全依赖于主属性，所以不符合2NF。（主码和非主码如果存在依赖关系，发证部门只能有一个主键，其他两个主键影响依赖关系）

可以用分解的方法消除部分依赖的情况，使关系达到2NF的标志。从现有的关系中分解出新的关系表，使每个表中所有非主关键字都完全依赖各自的主关键字。

第三范式（3NF）

如果一个关系数据2NF，并且每个非主属性不传递依赖于主属性，则称之为第三范式。记作R∈3NF。

工号是主属性，由于岗位级别->岗位工资，即非主属性岗位工资传递主属性工号（间接依赖），不符合3NF。
理解：工号是主键，岗位级别是次主键，岗位工资依赖次主键，间接依赖主键，所以有双重关系。要将双重的关系拆分。

使用分解，将关系分解为两个表，满足3NF。

反规范化

数据规范化减少了数据的冗余，节约了存储空间，相应逻辑和物理的IO次数减少，加快了增、删、改的速度，但是对完全规范的数据查询，通常需要更多的链接操作，从而影响了查询速度。有时为了提高某些查询的性能而破坏规范规则，长长采用反规范化处理。

常见的反规范化技术：

• 增加冗余列
  在多个表中具有相同的列，用来在查询时避免链接工作。

学生基本信息

学号	姓名	…
1005	李华	…

学生成绩

学号	科目	分数
1005	数学	88

合并成

学生成绩

学号	姓名	科目	分数
1005	李华	数学	88

• 增加派生列
  增加可以通过表中其他数据计算生成的列，可以在查询时减少计算量，从而增加查询速度。

订单信息

编号	商品号	商品名	单价	数量
10001	2001	小米6	3000	1

改成：

订单信息

编号	商品号	商品名	单价	数量	总价
10001	2001	小米6	3000	1	3000

• 重新组表
  如果需要大量查询两个表连接出来的数据，可以把这两个表重组成一个表来减少连接，从而提高性能。

员工信息

工号	姓名	性别
1005	李华	男

员工信工资条目信息

工号	工资条目	金额
1005	岗位工资	2500

改成：

员工工资信息

工号	姓名	性别	工资条目	金额
1005	李华	男	岗位工资	2500

• 分割表
  水平分割：根据一列或多列数据的值把数据行放到多个独立的表中。水平分割通常在表很大、表中的数据具有独立性、需要把数据放在不同的介质上存储时使用。

语音详单表

地市	服务号码	通话时长	…
0851	15300214678	60	…
0852	19350214678	50	…

拆分成：

贵阳地区语音详单表

地市	服务号码	通话时长	…
0851	15300214678	60	…

安顺地区语音详单表

地市	服务号码	通话时长	…
0852	19350214678	50	…

• 垂直分割：
  把包含主码和列的表拆分成多个包含主码和不同列的表。

商品信息表

编号	名称	单价	颜色	…
10007	小米6	3200	红	…

垂直拆分为：

商品信息表

编号	名称	…
10007	小米6	…

商品属性表

编号	单价	颜色	…
10007	3200	红	…

（2）函数依赖

设X、Y是关系R的两个属性集合，当任何时刻R中的任意两个元素中的X属性值相同时，则他们的Y属性值也相同，则称X函数决定Y，或Y函数依赖X。

员工信息

工号（ID）	姓名（NAME）	岗级（LEVEL）

对于员工信息，ID函数决定NAME和LEVEL，NAME和LEVEL函数依赖ID，记为：ID->NAME,ID->LEVEL；

关系R<U,F>中的一个属性或一组属性K，如果给定一个K则唯一决定U中的元素，也就是U函数完全依赖于K，称K为R的码；

包含在任一码中的属性称为主属性，不包含任何码中的属性称为非主属性；
关系R中的属性或属性值X不是R的码，但X是另一个关系模型的码，则称X是R的外码。

---

三 关系型数据库事务

1 事务及特性

事务是由一系列对系统中数据进行访问与更新操作所组成的一个程序执行逻辑单元。

• 原子性：事务是一个不可再分割的工作单元，事务中的操作要么都发生，要么都不发生。
• 隔离性：多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其他事务的运行效果。
• 一致性：事务必须使数据库从一个一致状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
• 持久性：事务对数据库所作的更改持久的保存在数据库之中，并不会被回滚。

2 事务并发带来的问题

对多个用户同时访问同一个数据库，当他们的事务同时使用相同的数据时发生的问题。由于并发操作带来的数据不一致性包括：丢失数据修改、读“脏数据”（脏读）、不可重复读、产生幽灵数据。

员工信息表

工号	姓名	性别	年龄
1005	李华	男	30

• 第一类丢失更新（lost update）
  在完全未隔离事务的情况下，两个事务更新同一条数据资源，某一事务异常终止，回滚造成第一个完成的更新也同时丢失。

时间序列	事务1	事务2
T1	开启事务
T2		开启事务
T3	取出数据，年龄 = 30
T4		取出数据，年龄 = 30
T5	更新数据，年龄 = 32
T6		更新数据，年龄 = 36，提交数据
T7	异常，回滚事务

说明：
T1时刻开启了事务1
T2时刻开启了事务2
T3时刻事务1从数据库中读取数据
T4时刻事务2取出了同一条数据
T5时刻事务1将年龄字段值更新为32
T6时刻事务2更新年龄为36并提交了数据
T7时刻事务1，由于事务1异常，导致回滚。回滚后，年龄值最后仍然为30，事务2的更新丢失了。

出现这种情况成为“第一类丢失更新（lost update）”。

• 脏读（dirty read）
  如果第二个事务查询到第一个事务还未提交的更新数据，形成脏读。

时间序列	事务1	事务2
T1	开启事务
T2		开启事务
T3	查询数据，年龄 = 30
T4	更新数据，年龄 = 35
T5		查询数据，年龄 = 35
T6	异常，回滚事务

说明：
T1时刻开启了事务1
T2时刻开启了事务2
T3时刻事务1从数据库中取出数据
T4时刻事务1将年龄字段值更新为35，但是未提交
T5时刻事务2从数据库中取出同一条数据，此时年龄值为35
T6时刻事务1异常，回滚事务，年龄字段值变为30，但事务2读取到的年龄字段值为35

出现这种情况称为“脏读”。

• 虚读（phantom read）
  一个事务执行两次查询，第二次结果集包含第一次事务中没有或者某些行已经被删除，造成两次结果不一致，只是另一个事务在这两次查询中间插入或者删除了数据造成的。

时间序列	事务1	事务2
T1	开启事务
T2		开启事务
T3	查询数据，有一条数据
T4		插入一条数据，提交事务
T5	查询数据，有两条数据
T6	异常，回滚事务

说明：
T1时刻开启了事务1
T2时刻开启了事务2
T3时刻事务1从数据库中查询出一条数据
T4时刻事务2插入一条数据，并提交
T5时刻事务1从数据库中查询出2条数据，导致2次插叙结果不一致。
出现这种情况称为“虚读”。

• 不可重复读（unrepeated read）
  一个事务两次读取同一行数据，结果得到不同状态结果，如中间正好另一个事务更新了该条数据，两次结果相异，不可信任。

时间序列	事务1	事务2
T1	开启事务
T2		开启事务
T3	取出数据，年龄 = 30
T4		取出数据，年龄 = 30
T5		更新数据，年龄 = 35
T6		提交事务
T7	取出数据，年龄=35

说明：
T1时刻开启了事务1
T2时刻开启了事务2
T3时刻事务1从数据库中读取数据，年龄字段值为30
T4时刻事务2取出了同一条数据，年龄值为30
T5时刻事务1将年龄字段值更新为35
T6时刻事务2提交
T7时刻事务1从数据库中取出数据，年龄字段值为35，发现两次读取的数据不一致

出现这种情况称为“不可重复读”。

• 第二类丢失更新（second lost update）
  是不可重复读的特殊情况，如果两个事务都读取同一行，然后两个都进行写操作，并提交，第一个事务所做的改变就会丢失。

时间序列	事务1	事务2
T1	开启事务
T2		开启事务
T3	更新数据，年龄=30
T4		更新数据，年龄=35
T5	提交事务
T6		提交事务

说明：
T1时刻开启了事务1
T2时刻开启了事务2
T3时刻事务1将年龄字段值更新为30
T4时刻事务2将年龄字段值更新为35
T5时刻事务1提交
T6时刻事务2提交事务，将事务1的更新覆盖了
出现这种情况称为“第二类丢失数据”。

3 事务的隔离级别

为了解决事务并发带来的问题，数据库使用4中事务隔离级别机制。

• Serializable串行化
• Repeatable Read可重复读（MySQL默认）
• Read Commited 可读已提交
• Read Uncommited 可读未提交

隔离级别	第一类丢失更新	脏读	虚读	不可重复读	第二类丢失更新
Serializable	禁止	禁止	禁止	禁止	禁止
Repeatable Read	禁止	禁止	允许	禁止	禁止
Read Commited	禁止	禁止	允许	禁止	禁止
Read Uncommited	禁止	允许	允许	允许	允许

---

四 MySQL简介及安装

1 MySQL简介

• MySQL是一个关系型数据库管理系统，由瑞迪MySQL AB公司开发，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一。
• MySQL所使用的SQL语言是用于访问护具库的罪常用标准化语言。MySQL采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低。尤其是社区版是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。
• MySQL是一个单进程多线程、支持多用户，基于客户端/服务器（Client/Server C/S）的SQL数据库服务器，支持多处理器。
• MySQL可以工作在不同的开发平台上。支持C、C++、Java、Perl、PHP、Python等开发语言的API。MySQL可以运行在不同的操作系统下。
• MySQL支持Windows以及UNIX、Linus和SUN OS等多种操作系统上；在一个操作系统中实现的应用可以很方便地移植到其他的操作系统下。MySQL有一个非常灵活且安全的权限和口令系统。当客户端与MySQL服务器连接时，他们之间所有的口令传送被加密，具备良好的安全性。

2 目录

第一层：最上层的服务器不是MySQL所独有的，大多数是基于网络的客户端/服务器工具或者服务器都有类似的系统。比如链接处理、授权认证、安全等等。
第二层：大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数等）。所有跨存储引擎的功能都砸这一层实现：存储过程、触发器、视图。
第三层：包含了存储引擎。存储引擎负责MySQL中的数据存储和提取。服务器通过API和存储引擎进行通信，这些接口屏蔽了不同的存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含了几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行数据”等操作。

3 MySQL安装

• MySQL数据库引擎
• innoDB(默认引擎，支持事务，应用广泛)
• ISAM
• MyISAM
• HEAP
• CSV
• BLACKHOLE
• ARCHIVE

五 SQL基础操作

• 定义
  SQL结构化查询语句（Structed Query Language），是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据及查询、更新和管理数据库管理系统；同时也是数据库脚本文件的扩展名。

• 标准
  SQL是1986年10月由美国国家标准局（ANSI）通过的数据库语言美国标准。1989年4月，ISO提出了具有完整性特征的SQL89标准，1992年11月公布了SQL92标准。

• 语句结构

DQL	DML	TPL	DCL	DDL	CCL
数据查询语言	数据操作语言	事务处理语言	数据控制语言	数据定义语言	指针控制语言
从表中查询数据，关键字：select、WHERE、ORDER BY、GROUP BY	对表进行新增、删除、修改。关键字：INSERT、DELETE、UPDATE	S生命、操作事务。关键字：TRANSACTION、COMMIT、ROLLBACK	控制用户对数据对象的访问权限。关键字：GRANT、REMOVE	创建、删除、修改数据库表、视图对象。关键字：CREAT、DROP、ALTER	用于对一个或者多个表单独行的操作。关键字：DECLARE、CURSOR

数据查询语言-DQL

• 查询字段选择
  • 全表查询
    select * from student;
  • 查询一条数据中的属性信息（字段）
    select id, name from student;
• 条件查询
  • 简单条件查询
    select *from student where name=‘李华’；
  • 组合条件查询（查询性别是男性，并且年龄大于18岁，小于25岁的学生信息）
    select * from student where sex = ‘1’ and age >= ‘18’ and age < ‘25’;
• 排序
  • 升序（查询学生年龄，按年龄从小到大排序）
    select * from student order by age;
  • 降序（查询学生年龄，按年龄从大到小排序）
    select * from student order by age desc;
• 分组、聚合查询
  • 简单分组
    select age,count(*) nums from student group by age;
  • 分组、聚合
    select sex,count(*)nums,avg(age)avg_age,min(age)min_age,max(age)max_age,sum(age)sum_age from student group by sex;
  • 带having条件的分组、聚合
    select age,count()nums from student group by age having count() > 50;

数据操作语言-DML

• 数据插入
  • 全字段数据插入
    insert into student values(‘1’,‘李明’，‘1’,30);
  • 部分字段数据插入
    insert into student(id,name,sex) values(‘2’,‘张三’,‘1’);
• 数据更新
  • 全表数据更新（更新全部学生年龄+1）
    update student set age=age + 1;
  • 条件更新(更新姓名是李明的学生年龄为35岁)
    update student set age = 35 where name = ‘李明’;
• 数据删除
  • 全表删除（删除所有学生信息）
    delete from student;
  • 条件删除
    delete from student where name = ‘李明’;

事务处理语言-TPL

略。

数据控制语言-DCL

• 授权
  grant
• 授权
  revoke

数据定义语言-DDL

• 创建表
  creat table student
  {
  id varchar(32) comment ‘学号’,
  name varchar(8) not null comment ‘姓名’,
  age int comment ‘年龄’,
  sex char(1) comment ‘性别:0 女性, 1:男性’,
  primary key:id
  }
• 创建用户
  creat user ‘test’ identified by ‘123456’;
• 修改表
  alter table student modify NAME VARCHAR(16);
  alter table student add column height DOUBLE;
  alter table student drop column height;
• 删除表
  drop table student;
• 删除用户
  drop user test;

指针控制语言-CCL

略。