数据库学习之一：MySQL

最新推荐文章于 2023-07-07 22:00:50 发布

柠meng草的味道

最新推荐文章于 2023-07-07 22:00:50 发布

阅读量225

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_42963969/article/details/104469300

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一. MySQL 数据库概述

数据库是是依照某种数据模型组织起来数据的集合。在数据库之上存在着数据库实例程序，该程序是用户与操作系统之间的数据管理软件。因此，MySQL其实是一种基于客户端-服务器的数据管理软件(DBMS)，用于管理存放数据的文件。
在这里插入图片描述

MySQL数据库由三部分组成：数据库系统，存储引擎，索引功能。

💗 0.2 数据库应用类型
数据库包含两种类型的应用：联机事务处理过程( OLTP)和联机分析处理( OLAP)。
① OLTP的处理过程为：前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一，常用于实时系统，如飞机订票、股票交易、超市销售、饭店前后管理等。
② OLAP主要用于数据的分析，其业务数据量较大。
💗 0.3 数据库应用层次结构
数据库应用层次结构如下图所示：

在数据库系统结构中，最基础的就是存储引擎与数据索引。MySQL中的数据用各种不同的技术存储在文件(或内存)中，每一种技术都使用不同的存储机制、索引方式、锁定水平并且最终提供广泛的不同的功能和能力。
MySQL的存储引擎包括：
① InnoDB：面向在线事务处理(OLTP)的应用。
② MylSAM：面向一些OLAP应用。
③ NDB：集群存储引擎
④ Memory：将表中的数据存放在内存中
⑤ 其他引擎：Archive,CSV,Maria等

MySQL是属于关系型数据库，采用了关系模型来组织数据的数据库，其以行和列的形式存储数据。其特点如下：在这里插入图片描述

二. MySQL 基本使用及语法

MySQL是数据库管理软件，通过结构化查询语言(SQL)来对数据进行增查改删。每个SQL语句都是由一个或多个关键字组成的。

一. InnoDB存储引擎概述

InnoDB存储引擎由后台线程，内存缓冲池组成。其中，后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中内存缓存的是最近的数据。其体系架构如下图所示：

1.1 InnoDB 引擎后台线程

InnoDB是多线程模型，后台进程主要分为4个部分：
①. Master Thread：负责将缓冲池中的数据异步刷新到硬盘，保证数据的一致性，是InnoDB引擎的主要线程。
②. IO Thread：write,read,insert buffer和log IO Thread
③. Purge Thread：回收已经使用脏页刷新并分配的undo页
④. Page Cleaner Thread：
💗 1.1.1 Master Thread

1.2 InnoDB 引擎内存 (日志，缓冲池，内存池)

由于InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理 。包括日志缓冲，缓冲池和额外内存池三个部分。

💗 1.2.1 innodb_buffer_pool 缓冲池
1. 缓冲池概述
由于Innodb引擎是在硬盘存储的，因此，提出了 缓冲池方法，通过内存来弥补磁盘速度较慢对性能的影响。缓冲池的大小直接影响着数据库的整体性能。

对数据库中页的操作过程如下：
① 读取页操作：将从磁盘读到的页放在缓冲池，这一过程称为页的FIX。
② 修改页操作：首先修改缓冲池中的页，然后刷新到磁盘上。缓冲池是通过 LRU(Lastest Recent Used，最近最少使用)算法进行管理，并在 LRU中加入了midpoint位置。
缓冲池中缓存的数据页类型有： 数据页(data page)， 索引页(index page)， 插入缓冲(insert buffer)， 自适应哈希索引(adaptive hash index)， 锁信息(lock info)， 数据字典信息(data dictionary)
2. 缓冲池的调度 LRU List，Free List，Flush List
● LRU List
LRU List用于页的管理和调度。
由于缓冲池是通过 LRU(Lastest Recent Used，最近最少使用)算法进行管理， LRU对缓冲池的性能有较大影响。InnoDB引擎中，对 LRU算法进行了改进，在 LRU列表中加入了 midpoint位置，新读取到的页并不直接放入 LRU列表的首部，而是放到LRU列表的 midpoint位置。
Q1. InnoDB为什么要改进LRU,增加 midpoint？
因为若直接将读取的页放入 LRU首部，则索引或数据扫描操作 (这类操作会访问表中许多的页，但这些页仅在本次操作中需要，不是活跃数据) 会使缓冲池中的页被刷出，从而影响缓冲池效率。
● Free List
Free List用于数据库刚启动的时候， LRU List为空，需要从 Free List中调入页到 LRU List中，同时删除 Free List中的页。
● Flush List
在 LRU List中的页被修改后，会导致缓冲池中的页和磁盘上的页的数据产生了不一致，则该页称为脏页， Flush List用于存储脏页，并管理将页刷新回磁盘。

💗 1.2.2 InnoDB 日志
Mysql中有六种日志文件：回滚日志(undo log)，二进制日志(bin log)，错误日志(error log)，慢查询日志(slow query log)，一般查询日志(general log)，中继日志(relay log)。
① 重做日志(redo log)
redo log用来确保事务的持久性，其包含两部分：重做日志缓冲(易丢失)和重做日志文件(持久的)。;在当前事务数据库都采用Write Ahead Log策略，当事务提交时，先写redo log，在修改页(先复制，再修改)，以避免数据丢失。

当事务 commit时,为了保证 redo log缓冲写入 redo log文件，InnoDB都会调用 fsync操作，将事务的所有日志缓冲写入到重做日志文件中进行持久化。在InnoDB中，参数 innodb_flush_log_at_trx_commit用来控制redo log缓冲刷新到磁盘文件。

redo log缓存结构如下图所示：

② 回滚日志(undo log)
undo log用来事务回滚及MVCC功能。 undo log是随机读写的。
③ 二进制日志(binlog)
binlog用来进行POINT_IN_TIME的恢复及主从复制环境的建立。binlog是在MySQL的上层产生的，是一种逻辑日志，记录的是对应的SQL语句。
Q1. redo log与binlog的区别？
① 产生位置不同： redo log是在InnoDB引擎层产生； binlog是在MySQL的上层产生。
② 内容形式不同：redo log的重做日志是物理格式日志，记录的是每个页的修改；binlog是逻辑日志，其记录的是对应的SQL语句。
③ 写入磁盘时间点不同： binlog只在事务提交完成后进行一次写入； redo log在事务进行中不断的诶写入。

💗 1.2.3 insert buffer插入缓冲 后续补充
insert buffer既是缓冲池的一部分，也是物理页的一部分。在InnoDB中，主键是行唯一的标识符，行记录是按照主键递增的顺序进行插入的。因此插入聚集索引一般是顺序的，不需要随机读取。但更多情况是一张表有多个非聚集的辅助索引，产生一个非聚集的且不是唯一的索引。数据页是按照主键顺序存放的，但对非聚集索引则不是顺序的了，就会产生离散访问非聚集索引页，降低效率。
利用Insert buffer，将对非聚集索引的插入或更新操作，先判断插入的非聚集索引页是否在缓存池中，若在，则直接插入，若不在，则先放入Insert Buffer中,然后以一定频率对Insert Buffer和辅助索引页进行合并。通过Insert Buffer可以将多个插入合并到一个操作中，提高了插入性能。
Insert Buffer使用的两个条件：
① 索引是辅助索引；
② 索引不是唯一的。

1.3 CheckPoint技术

因为页的操作是在缓冲池中完成的，操作后的页是脏页，数据库需要将脏页从缓冲池中刷新到磁盘。但频繁的刷新会使数据库效率降低。CheckPoint技术解决了以下问题：
① 缩短数据库的恢复时间。
② 缓冲池不够用时，将脏页刷新到磁盘。
③ 重做日志不可用时，刷新脏页。
④ 当缓冲池不够用时，LRU算法会溢出最近最少使用的页，当此页为脏页，则需要强制执行CheckPoint，将脏页刷回磁盘。
在InnoDB引擎中有两种CheckPoint：
① Sharp CheckPoint：用于数据库关闭时，将所有脏页刷新回硬盘
② Fuzzy CheckPoint：用于数据库运行时，将一部分脏页刷新回硬盘。

二. MySQL 文件

MySQL文件=MySQL系统文件+InnoDB引擎文件。MySQL中有六种日志文件：回滚日志(undo log)，二进制日志(bin log)，错误日志(error log)，慢查询日志(slow query log)，一般查询日志(general log)，中继日志(relay log)。其结构如下图所示：
在这里插入图片描述

2.1 MySQL 系统日志文件

💗 2.1.1 错误日志文件(error log)
错误日志文件对MySQL的启动，运行，关闭过程进行记录。如下图，为MySQL的错误日志：

💗 2.1.2 慢查询日志文件(slow log)
slow log 用来记录在MySQL中响应时间超过阀值( long_query_time)的语句,默认情况下，MySQL数据库不启动慢查询日志。
💗 2.1.3 查询日志文件(log)
查询日志文件记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认的文件名为： 主机名.log。
💗 2.1.4 二进制日志文件(binlog)
记录对MySQL数据库执行更改的所有操作，但不包括 select和 show操作，因为这类操作没有对数据本身进行修改。如果想记录select和show操作，只能使用查询日志，而不是二进制日志。二进制日志主要有三个作用：
● 恢复 recovery：对数据库进行 point-in-time恢复
● 复制 replication：通过复制和执行二进制日志使远程MySQL数据库与本地数据库实时同步。
● 审计 audit：通过二进制文件信息，判断是否有对数据库的注入攻击。

2.2 InnoDB 存储引擎文件

💗 2.2.1 重做日志(redo log)
redo log用来确保事务的持久性，其包含两部分：重做日志缓冲(易丢失)和重做日志文件(持久的)。;在当前事务数据库都采用Write Ahead Log策略，当事务提交时，先写redo log，在修改页(先复制，再修改)，以避免数据丢失。

当事务 commit时,为了保证 redo log缓冲写入 redo log文件，InnoDB 都会调用 fsync操作，将事务的所有日志缓冲写入到 redo log日志文件中进行持久化。在InnoDB中，参数 innodb_flush_log_at_trx_commit用来控制 redo log缓冲刷新到磁盘文件。

redo log缓存结构如下图所示：

Q1. redo log与binlog的区别？
● 产生位置不同： redo log是在InnoDB引擎层产生； binlog是在MySQL的上层产生。
● 内容形式不同：redo log的重做日志是物理格式日志，记录的是每个页的修改；binlog是逻辑日志，其记录的是对应的SQL语句。
● 写入磁盘时间点不同： binlog只在事务提交完成后进行一次写入； redo log在事务进行中不断的诶写入。
💗 2.2.2 表空间文件
InnoDB采用将存储的数据按表空间进行存放，默认配置下会有一个初始大小为10MB的表空间文件，文件名为 ib_logfile0和 ib_logfile1。

三. MySQL 索引与数据存储方式

由第一节InnoDB的应用架构可以知道，MySQL数据库中，数据都是存储在磁盘文件当中，当需要对数据进行操作时将数据从磁盘读入内存中，因此，MySQL的数据读取的基本单位是页(磁盘块)。
索引是数据库中重要的一部分。索引的本质是一种数据结构，用于快速找出某一特定的数据，不使用索引，MySQL必须从第一条记录开始读完整个数据库。MySQL中不同存储引擎与索引的对应关系如下表所示：

InnoDB的数据存储是基于索引组织的，因此对于InnoDB存储引擎，数据即索引，索引即数据。在 InnoDB存储引擎中常见的索引包括： B+树索引，全文索引，自适应哈希索引。InnoDB默认索引为B+树索引。因此在介绍MySQL数据存储之前先介绍MySQL的索引。

3.1 B+树索引(B+ Tree)

B+树索引是以B+树数据结构为基础，B+树索引不能找到一个给定键值的具体行，其能检索到的只是被查找数据行所在的页，然后数据库通过将页读入到内存，再在内存中进行查找。在这里插入图片描述
💗 3.1.1 B+树索引的"进化"过程

💗 3.1.2 聚集索引，非聚集索引，覆盖索引，联合索引
1. 聚集索引
聚集索引是以InnoDB作为存储引擎的表，表中的每个数据都有一个唯一的主键，若不创建主键，系统会选择表中第一个不允许为NULL的隐式主键。所以，聚集索引一般是表中的主键索引。在B+树中，B+树的键值就是索引，即B+树的非叶子节点，而B+树的叶子节点存储了表中所有的数据。聚集索引的特点如下:
① 聚集索引表按照主键ID排序，检索的是每一行数据的真实内容。
② 实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。
③ 聚集索引对于主键的排序查找和范围查找速度非常快。
④ 聚集索引的存储不是物理上连续，而是逻辑上连续的。
2. 非聚集索引(辅助索引)
非聚集索引是指B+树的叶子节点不存储表中的数据，而是存储该列对应的主键，当查找数据时，需要根据主键再去聚集索引中进行查找，根据聚集索引查找数据的过程叫做回表。非聚集索引不影响聚集索引的组织，因此每张表上可以有多个非聚集索引。

create table Test(
	`id` INT UNSIGNED AUTO_INCREMENT,
	`name` VARCHAR(255),
	`str` VARCHAR(15),
	PRIMARY KEY(`id`),  #id为主键，是聚集索引，表中的每行数据都是按照聚集索引id排序存储的，检索的是每一行数据的真实内容
	KEY(`name`)    #name是非聚集索引，name索引表节点按照name排序，检索的是每一行数据的主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这里插入图片描述
Q1. 聚集索引与非聚集索引在查询数据时有什么区别吗？
聚集索引在查询数据时更快。主键索引树的叶子节点直接就是要查询的整行数据。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过回表，再进行多次查询。
3. 覆盖索引
覆盖索引指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

create table user (
	id int primary key,  #主键
	name varchar(20),
	sex varchar(5),
	index(name)    #普通索引
)engine=innodb;

select id,name from user where name='test'; 
# 1.命中name索引，索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表，
#   符合覆盖索引，直接返回查询所需要的数据。

select id,name,sex from user where name='test';*
# 2.命中name索引，索引叶子节点存储了主键id,通过name的索引树即可获取id和name,
#   但sex字段必须回表查询才能获取到，不符合覆盖索引，需要通过id聚集索引获取sex字段。

4. 联合索引
上面的索引方式都是简单的单列索引，当一个索引中包含多个字段(多条件查询)时，就需要使用多列索引(联合索引)。在多个字段索引时，B+树遵循的是最左匹配原则，即按照从左到右的顺序来建立搜索树。

create table stu
(
	id int,
	name varchar(10),
	age int,
	class varchar(10),
	primary key(id)
);
alter table stu add index stuIndex(name,age,class); #实际建立了(name),(name,age),
                                                    #(name,age,class)三个索引                                               
#以联合索引(a,b,c)为例：
select * from test where a=? and b=? and c=?；查询效率最高，索引全覆盖。
select * from test where a=? and b=?；索引覆盖a和b。
select * from test where b=? and a=?；经过mysql的查询分析器的优化，索引覆盖a和b。
select * from test where a=?；索引覆盖a。
select * from test where b=? and c=?；没有a列，不走索引，索引失效。
select * from test where c=?；没有a列，不走索引，索引失效。
select * from test where a=? and b between ? and ? and c=?；索引覆盖a和b，因b列是范围查询，因此c列不能走索引。
select * from test where a between ? and ? and b=?；a列走索引，因a列是范围查询，因此b列是无法使用索引。
select * from test where a between ? and ? and b between ? and ? and c=?；a列走索引，因a列是范围查询，b列是范围查询也不能使用索引。

Q1. 为什么要使用联合索引？
① 减小开销，联合索引stuIndex实际建立了(name),(name,age),(name,age,class)三个索引，对于大量数据的表，会大大减小开销。
② 利用覆盖索引，对于联合索引stuIndex，能够更好的利用覆盖索引获取数据，无需回表，减少了很多I/O操作。
③ 效率高，索引列越多，通过索引筛选出的数据越少。

💗 3.1.3 B+树索引管理 clustered index
1.索引创建和删除：

ALTER TABLE [表名] ADD  [INDEX|KEY] 索引名称 (创建索引的字段名[length])[ASC|DESC]   --创建索引
alter table t add key idx_b (b(100));  --以列b的前100个字段作为索引
alter table t add key idx_ac (a,c);   --对列(a,c)增加索引

2.索引的查看：
使用命令show index from [表名]来查看索引信息。如下图所示，在索引信息中Cardinality是重要的参数，表示索引中唯一值的数目的估计值。优化器会根据这个值判断是否使用这个索引。Cardinality表的行数应尽可能接近1，接近于1说明索引趋于唯一，索引的效率最高。.

3.2 哈希索引(Hash)

哈希索引是以哈希算法为基础的，哈希算法是一种时间复杂度为O(1)的算法。常用于内存的快速查找。在InnoDB存储引擎中，Hash索引称为自适应哈希索引，由InnoDB引擎自动优化创建的，只用来快速查找内存中的页(加速索引)，而不是存储数据，且无法人为干预。
💗 3.2.1 哈希算法
哈希算法是一种常用的数据结构，利用哈希表(散列表)能很好的解决直接寻址带来的问题。当两个关键字映射到同一地址时，即发生了碰撞。在数据库中，解决碰撞的方法是采用链接法。在这里插入图片描述
Q1. Hash索引与B+ Tree索引的区别？
① Hash索引底层是Hash表，以key-value为数据结构，多个数据在存储关系上没有任何顺序关系，因此Hash索引只适合等值查询，无法进行范围查询。B+ Tree以多路平衡查询树为数据结构，其节点是有序的，在范围查找时不需要做全表扫描。
② Hash索引无法被用来避免数据的排序操作。
③ 哈希索引不支持多列联合索引的最左匹配规则
④ 如果有大量重复键值得情况下，会存在哈希碰撞问题，哈希索引的效率会很低，

3.3 全文索引(Full-Text)

全文索引是将存储于数据库中的整个数据信息中的任意内容信息查找出来的技术，其可以根据需要获取全文中有关章，节，段，句，词等信息，也可以进行统计分析。
💗 3.3.1 正排索引与倒排索引
正排索引称为前向索引，是文档到关键字(doc->word)的映射。

倒排索引基于正排索引，是 关键字到文档 (word->doc)的映射。全文检索通常使用倒排索引来实现，它在辅助表中存储了单词与单词自身在一个或多个文档所在位置之间的映射。倒排索引有两种表现形式：
① inverted file index -> {单词，单词所在文档的ID}
② full inverted index -> {单词，(单词所在文档的ID,在具体文档中的位置)} 在这里插入图片描述

💗 3.3.2 InnoDB 全文检索
InnoDB 全文检索采用 full inverted intex方式，其原理如下图所示：在这里插入图片描述

select * from t where match(body) against ('Hello');  #在body字段中全文索引包含Hello的单词
select count(*) from t where match(body) against('Hello');  #统计match得到的结果数量

三. 表

数据库的数据存储基础是存储引擎，存储引擎的基础是表。表是特定的数据集合，是数据库的核心。

3.1 InnoDB 逻辑存储

💗 3.1.1 InnoDB 存储结构
InnoDB存储引擎表中，每个表都有一个主键。所有的数据存放在表空间中，表空间分为段，区，页(块) 三部分组成。如下图所示：

1. 表空间
表空间是InnoDB逻辑结构的最高层，所有数据都存放在表空间中。表空间的存储方式如下图所示：
在这里插入图片描述

2. 段空间
表空间由各个段组成，常见的段有数据段，索引段，回滚段等。数据段即为B+树的叶子节点，索引段即为B+树的非索引节点。创建一个B+树索引时会同时创建两个段，分别是内节点段和叶子段，内节点段用来存储B+树非叶子(页面)的数据，叶子段用来管理(存储)B+树叶子节点的数据；
3. 区空间
区是由连续页组成的空间，在任何情况下每个区的大小都是 1MB，而InnoDB存储引擎页大小为16KB，即一个区一共有64个连续的页。
4. 页空间
页是InnoDB磁盘管理的最小单元，InnoDB默认每个页的大小为 16KB，在InnoDB中常见的页类型有：

2.2 约束

一个数据库就是一个完整的业务单元，数据库包含多张表，数据被存储在表中。为了准确的存储数据，保证保证存储数据的完整性和数据的关联性，在创建表的时候，为表添加约束机制，包括数据字段的类型、约束。
💗 2.2.1 约束的创建和查找
约束创建采用两种方式：
① 表建立时就进行约束定义。
② 利用ALTER TABLE命令进行创建约束。
💗 2.2.2 约束和索引的区别
约束是一个逻辑概念，为了保证数据的完整性而实现的一套机制。保证数据的准确，为数据的输入和更改设置了条件。
索引是快速定位特定数据，提高查询效率，确保数据的唯一性。

💗 2.2.3 约束方式介绍
1.主键与外键约束：
(1).主键（PRIMARY KEY），也称“主键约束”，其值能唯一地标识表中的每一行，每个表有且仅有一个主键，且不能为NULL。MySQL会自动为所有表的主键创建一个唯一索引.
(2).外键(FOREIGN KEY)，是由用户进行明确的索引。外键可以是数据表之间向关联，减少数据表的数据冗余，保证数据的一致性。例如：

学生表(学号，姓名，性别，班级) // 学号唯一，能确定学生表的一行，是主键
课程表(课程编号,课程名,学分) // 课程编号唯一，能确定课程表的一行，是主键
成绩表(学号,课程号,成绩） // 学号和课程号才能唯一确定哪个人哪门课得了多少分，成绩表中的学号和课程号与学生表和课程表关联，是外键。

2.触发器约束：
触发器就是一张表发生了某件事（插入Insert、删除Delete、更新操作Update），然后自动触发了预先编写好的若干条SQL语句的执行，触发器具有原子性,要么全部执行，要么都不执行。触发器按每行记录进行触发。

DELIMITER ||            //将结束符号变成||，防止与语句中的;结束符冲突
CREATE TRIGGER 触发器名 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END
DELIMITER ||

💗 2.2.4 约束保证了数据的完整性
数据完整性有三种形式：
① 实体完整性(数据行)：保证表中有一个主键。
② 域完整性(数据列)：保证数据每列的值满足特定条件(限制数据类型,缺省值,规则,约束,是否可以为空)，由确定关系结构时所定义的字段的属性决定的。一般用主键,唯一索引(UNIQUE)。
③ 参照完整性：建立关联关系的数据表间数据参照引用的约束,e( 一对多、多对多、一对一)，用于多表之间的设计,主要使用外键约束。

--**一对多**   一个客户可以订多份订单，每份订单只能有一个客户
	create table customer( 
	    id int PRIMARY KEY auto_increment,
	    name varchar (20) not null,
	    adress varchar (20) not null
	);
	create table orders(
	    order_num varchar(20) PRIMARY KEY,    --表建立时进行约束定义
	    price FLOAT not NULL,
	    customer_id int, -- 进行和customer 关联的字段 外键
	    constraint cus_ord_fk foreign key (customer_id) REFERENCES customer(id)
	);
	ALTER TABLE student ADD PRIMARY KEY (id);   -- 利用ALTER TABLE创建约束
	
--**多对多**   一个学生可以有多个老师，一位老师也可以有多个学生
	CREATE TABLE teacher (
	    id INT,
	    NAME VARCHAR (20) NOT NULL,
	    gender CHAR (2) NOT NULL,
	    PRIMARY KEY (id)     --表建立时进行约束定义
	);
	CREATE TABLE student (
	    id INT,
	    NAME VARCHAR (20) NOT NULL,
	    age INT NOT NULL
	);
	ALTER TABLE student ADD PRIMARY KEY (id);  -- 利用ALTER TABLE创建约束
	-- 第三张关系表
	CREATE TABLE tch_std (
	    teacher_id INT,
	    student_id INT,
	    CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (id),
	    CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES teacher (id)
	);
	
--**一对一** 
	CREATE TABLE users (
	    id INT,
	    NAME VARCHAR (20) NOT NULL,
	    gender CHAR (2) NOT NULL,
	    PRIMARY KEY (id)      --表建立时进行约束定义
	);
	CREATE TABLE card (
	    id INT,
	    adress VARCHAR (100) NOT NULL,
	    user_id INT UNIQUE,
	    constraint users_id_fk foreign key(user_id) references users(id)   
	);
	ALTER TABLE card ADD PRIMARY KEY (id);           -- 利用ALTER TABLE创建约束

2.3 视图 VIEW

视图是一个命名的虚表，其数据没有实际的物理存储。视图根据需要选取基表中的数据，在一定程度上起到安全层的作用。Mysql视图主要有以下几个方面作用：
① 提高了数据的重用性，对频繁获取的数据进行“封装”。
② 对数据库进行重构
③ 提高数据库安全性在这里插入图片描述

2.4 分区 PARTITION

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。但对数据库而言，逻辑上就只有一个表或者一个索引，分区对应用来说是完全透明的。
Mysql有四个类型的分区,这四个分区数据必须是整形的：
① RANGE分区：行数据将属于给定连续区间的列值放入分区域。若数据有NULL值，则NULL值会放入最左边的分区中。
② LIST分区：和RANGE分区类似，只是LIST分区面向离散的值。在LIST分区中使用NULL值，必须显式指出哪个分区中放入NULL值。
③ HASH分区：根据自定义表达式的返回值进行分区。若使用NULL，则将含有NULL值的记录返回为0；
④ KEY分区：根据哈希函数进行分区，若使用NULL，则将含有NULL值的记录返回为0；

-- RANAGE分区 (连续分区)
CREATE TABLE t(
	id INT
)ENGINE=InnoDB
PARTITION BY RANGE (id)(     --根据id的值进行分区
PARTITION  p0 VALUES LESS THAN (10),   --id<10，分为p0区
PARTITION  p1 VALUES LESS THAN (20));  --10<id<20，分为p1区

--LIST分区   (离散分区)
CREATE TABLE t(
	a INT,
	b INT)ENGINE=InnoDB
PARTITION BY LIST(b)(    --根据d的值进行分区
PARTITION  p0 VALUES IN (1,3,5,7,9),   --d=1,3,5,7,9分为p0区
PARTITION  p1 VALUES IN (0,2,4,6,8)); --d=0,2,4,6,8分为p1区

--HASH分区
CREATE TABLE t_hash(
	a INT,
	b DATATIME)ENGINE=InnoDB
PARTITION BY HASH(YEAR(b))
PARTITION  4,   

--KEY分区
CREATE TABLE t_hash(
	a INT,
	b DATATIME)ENGINE=InnoDB
PARTITION BY KEY(b)
PARTITION  4,

表与分区的数据交换要满足一下条件：
① 要交换的表和分区有相同的表结构，且表中不能有分区
② 在非分区表中的数据必须在交换的分区定义内
③ 被交换的表中不能含有外键，或其他的表不能含有对该表的外键引用。

四. 锁

五. 事务 Transaction

事务是数据库操作中最小的不可再分的工作单元，一个事务对应一个完整的业务。通常一个完整的事务是由批量的数据库操作(DML)来共同完成的。事务会把数据库从一种一致状态转换为另一种一致状态。事务的并发是提高数据库性能的重要方式。

5.1 事务的特征

事务的四大特征ACID：一致性C，原子性A，隔离性I，持久性D。

💗 5.1.1 一致性
一致性：一致性是事务处理的“统领”，一致性保证了在数据操作前后，数据不会被破坏，保证数据库的完整性约束没有被破坏。原子性，隔离性和持久性的目的是为了维护事务的一致性。
💗 5.1.2 原子性
原子性：原子性是指事务执行的完整性，即原子事务中要么完整执行，要么不执行。在事务中如果有任意 SQL执行失败，则整个工作事务会被终止。此前对数据所作的任何修改都会被撤销。
为了实现事务的原子性，就需要通过日志，将所有对数据的更新操作都写入日志。当事务失败后，将已经执行成功的操作撤销。

💗 5.1.3 隔离性
为了保证在并发情况下的事务一致性，引入了隔离性。隔离性是指每个读写事务的对象对其他事务的操作对象相互分离，即该事务提交前对其他事务都不可见。在保证在并发情况下，每一个事务都是一致性的。
Q1. 为什么要有隔离性？
不同事务之间的并发会带来以下3个问题：
① 脏读：脏读是指事务A 修改了一个数据，但未提交，而事务B读到了事务A未提交的结果，如果事务A提交失败，则事务B读到的就是脏数据。
② 不可重复读：在同一个事务中，对于同一份数据读取到的结果不一致，其原因是事务并发修改数据，如事务B在事务A提交前读到的结果和提交后读到的结果可能会不同。
③ 幻读：同一个事务中，同一个查询多次返回的结果不一致，其原因是由于事务并发增加记录。如事务A新增一个记录，事务B在事务A提交前后各执行一次查询，其结果后一次比前一次多一个记录。
针对事务并发出现的3个问题，事务的隔离性分为了4个隔离级别：.

💗 5.1.4 持久性
持久性：某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束之前保存至物理存储设备，保证所作的改动不会丢失。

5.2 事务的分类

事务可以分为：扁平事务，带保存点的扁平事务，链事务，嵌套事务，分布式事务。
💗 5.2.1 扁平事务
扁平事务是最简单的事务，使用频繁。扁平事务的保存点隐式设置在事务的开始状态。在扁平事务中不能提交或回滚事务的某一部分。每次回滚只能回滚到事务的开始状态，事务需要重新执行所有操作。

💗 5.2.2 带保存点的扁平事务
在扁平事务的基础上，添加了保存点，允许在事务执行过程中回滚到同一事务中较早的状态。

begin;
insert into 'Test' (`No`) values (1);
insert into Test (`No`) values (2);
savepoint p1;  //设置保存点
insert into 'Test' (`No`) values (3);
insert into Test (`No`) values (4);
rollback to p1;    //回滚事务到p1保存点，第3,4条语句不会执行
commit;   //提交事务

💗 5.2.3 链事务
链事务在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务，提交事务操作和开始下一个事务操作合并为一个原子操作。
在链事务中，回滚仅限于当前事务，即只能恢复到最近一个的保存点。当执行commit后即释放了当前事务所持有的锁。
在这里插入图片描述
💗 5.2.4 嵌套事务
嵌套事务是由若干事务组成的一棵树，子树既可以是嵌套事务，也可以是扁平事务。任何子事务都在顶层事务提交后才真正的提交。树中任意一个事务的回滚会引起它的所有子事务一同回滚，故子事务仅保留A,C,I特性，不具有D的特性。
在嵌套事务中，实际的工作是交由叶子节点来完成的，只有叶子节点的事务才能访问数据库，发送消息，获取资源。而高层事务仅负责逻辑控制，决定何时调用相关的子事务。

在这里插入图片描述

5.3 事务的实现

六. 备份与恢复

七. Mysql 基本语法、

7.1 存储过程

存储过程是为了以后使用而保存的一条或多条MySQL语句集合。存储过程有3大好处：简单，安全，高性能。
● 存储过程将SQL处理封装到单元中，简化操作。
● 保证数据的完整性，防止错误。
● 简化对变动的管理，如果表名，列名有变化，只需要更改存储过程的代码即可，从而限制了对基础数据的访问，减少了数据的错误。

delimiter //
create procedure print(
	out plow DECIMAL(8,2);  //decimail(M,D),M为数字最大数，D为小数点后位数
	out pmax DECIMAL(0,2);
	out pavg DECIMAL(0,2);
)   //定义存储过程及返回参数
begin 
	select Min(Temp) into plow from Testdemo;
	select Max(Temp) into pmax from Testdemo;
	select Avg(Temp) into pavg from Testdemo;
end;
//

delimiter ;

//MySQL中所有的变量都以@开始
call print(@pl,@pm,@pa);  //调用存储过程，并将将结果放入变量中
select @pl; //通过变量得到最终结果
select @pm;
select @pa;

drop procedure print;  //删除存储过程

待整理内容

柠meng草的味道

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库学习之一：MySQL

文章目录〇数据库概述一. InnoDB存储引擎1.1 InnoDB引擎后台进程1.2 InnoDB引擎内存1.3 CheckPoint技术二. 表2.1 InnoDB逻辑存储结构2.2 约束2.3 视图 VIEW2.4 分区 PARTITION三. 索引与算法3.1 B+树索引3.2 哈希算法3.3 全文检索四. 锁待整理内容〇数据库概述???? 0.1 数据库简述数据库是...
复制链接

扫一扫