当前位置:   article > 正文

个人笔记之数据库基础概念_数据询查跟读取有区别吗

数据询查跟读取有区别吗

1.数据库范式

1.1什么是范式

  设计关系数据库时,需要遵从不同的规范要求,这些不同的规范要求被称为不同的范式
  要想设计—个好的关系,必须使关系满足一定的约束条件,此约束已经形成了规范,分成几个等级,一级比一级要求得严格。各种范式呈递次规范,越高的范式数据库冗余越小。满足这些规范的数据库是简洁的、结构明晰的,同时,不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟,不仅给数据库的编程人员制造麻烦,而且面目可憎,可能存储了大量不需要的冗余信息。

  目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。博客就讲前三个范式,水平比较低。
各个范式的详细内容强烈建议认真阅读此博客

1.2第一范式

   所有的域都应该是原子性的,每一列都不可再拆分。 即实体中的某个属性有多个值时,必须拆分为不同的属性。
例如:在这里插入图片描述
1NF的定义为:符合1NF的关系中的每个属性都不可再分 所以上表不符合,应该为下表:
在这里插入图片描述

1.3第二范式

  满足2NF的前提是必须满足1NF。关系模式需要包含两部分内容,一是必须有一个及其以上的主键;二是没有包含在主键中的列必须全部依赖全部主键,而不能只依赖于主键的一部分而不依赖全部主键。简明的说就是:不产生局部依赖,表中非码的列都完全依赖于主键。
在这里插入图片描述
  假如当 K 确定的情况下,该表除 K 之外的所有属性的值也就随之确定,那么 K 就是候选码。主键也就是说主码。
在这里插入图片描述
  部分函数依赖可以理解为码A存在属性(学号,课名),有个属性为姓名,其中可以通过学号->姓名,但是课名不能->姓名,则称非主属性姓名对码存在部分函数依赖。
在这里插入图片描述

1.4第三范式

  不产生非主键对于主键的传递函数依赖,表中每一列都直接依赖于主键。

  例如确认了学号就可以确认系名,确认了系名就可以确认系主任,所以系主任是通过传递函数对主键(只有一个)的完全依赖,是符合2NF的。但是不符合3NF。所以应该改为图二模式。
在这里插入图片描述
在这里插入图片描述
各个范式的详细内容强烈建议认真阅读[此博客]

2.MySQL查询

2.1表连接查询

  在数据库的真正使用当中,经常需要从多个数据库表中读取数据。这时候就可以使用mysql的join在两个或者多个表中查询数据。

  • INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
    在这里插入图片描述
  • LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
    在这里插入图片描述
  • RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
    在这里插入图片描述

2.2子查询

  使用子查询是指,在一个select语句中还嵌套着另一个select语句。子查询的结果分为四种情况:

  1. 单行单列:返回的是一个具体列的内容,可以理解为一个单值数据;
  2. 单行多列:返回一行数据中多个列的内容;
  3. 多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围;
  4. 多行多列:查询返回的结果是一张临时表;

示例一:单行单列

--查询公司之中工资最低的雇员的完整信息
SELECT * FROM emp e WHERE e.sal=
	(SELECT MIN(sal) FROM emp);
  • 1
  • 2
  • 3

示例二:单行多列

  --查询与雇员7566从事同一工作且领导相同的全部雇员信息
SELECT * FROM emp e WHERE (e.job,e.mgr) =
	(SELECT job,mgr FROM emp WHERE empno=7566);
  • 1
  • 2
  • 3

示例三:多行单列

--查询出与每个部门中最低工资相同的全部雇员信息
SELECT * FROM emp e WHERE e.sal IN
	(SELECT MIN(sal) FROM emp GROUP BY deptno);
  • 1
  • 2
  • 3

示例四:

-- 查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门 id 等于的 dept_id
SELECT * FROM dept d, 
(SELECT * FROM emp WHERE join_date >='2011-1-1') e WHERE d.`id`= e.dept_id;
  • 1
  • 2
  • 3

3.数据库事务

  事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常,则所有的SQL 语句都要回滚,整个业务执行失败。事务是由存储引擎实现的。MySQL支持事务的存储引擎有InnoDB、NDB Cluster。

3.1事务的四大特性ACID

特性描述
原子性(Atomicity)每个事务都是一个整体,不可再拆分,事务中的语句要么执行成功,要么都失败
一致性(Consistency)事务在执行前数据库的状态和执行后的状态保持一致。
隔离性(Isolation)事务与事务之间不应该相互影响,执行时保持隔离的状态。
持久性(Durability)一旦事务执行成功,对数据库的修改是持久的,就算关机也保存下来。

3.1.1原子性

  原子性是指一个事务是一个不可分割的工作单位,其中的操作要么都做,要么都不做;如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。
  InnoDB存储引擎还提供了两种事务日志:redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性;undo log则是事务原子性和隔离性实现的基础。
  InnoDB实现回滚,靠的是undo log:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。

3.1.2一致性

  这里单独提一下这个一致性的问题,查阅了资料说一致性的理解为:在转账系统中,数据库的约束为账户余额不能小于0,a和b各有100元,但是a向b转120元,根据约束,这个转账违反了规则,不满足一致性的要求,所以事务会回滚。
  所以总结为一致性就是:你的操作满不满足数据库定义的一些规则(约束条件),数据库的完整性约束没有被破坏,事务执行的前后都是合法的数据状态。,从而保证把数据库从一个有效(正确)的状态“转移”到另一个有效(正确)的状态。

3.1.3持久性

  实现原理是redo log。为什么要使用redo log,这是因为InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,但如果每次读写数据都需要磁盘IO,效率会很低。所以InnoDB提供了缓存(Buffer Pool),读数据的时候首先从缓存里面读取,写数据的时候也是首先写入缓存。但是如果MySQL宕机,而此时Buffer Pool中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。redo log被引入来解决这个问题:redo log采用的是WAL(Write-ahead logging,预写式日志),所有修改先写入日志,再更新到Buffer Pool,保证了数据不会因MySQL宕机而丢失,从而满足了持久性要求。

3.1.4隔离性

  针对多个用户同时操作,主要是排除其他事务对本次事务的影响。 简单起见,我们主要考虑最简单的读操作和写操作(加锁读等特殊读操作会特殊说明),那么隔离性的探讨,主要可以分为两个方面:

  • (一个事务)写操作对(另一个事务)写操作的影响:锁机制保证隔离性
  • (一个事务)写操作对(另一个事务)读操作的影响:MVCC保证隔离性

3.2.1锁机制

  首先来看两个事务的写操作之间的相互影响。锁机制的基本原理可以概括为:事务在修改数据之前,需要先获得相应的锁;获得锁之后,事务便可以修改数据;该事务操作期间,这部分数据是锁定的,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁。关于锁机制,后面会有详细的介绍,这里只是引出这个概念。

3.2.2脏读、不可重复读和幻读

  介绍完写操作之间的相互影响,下面讨论写操作对读操作的影响。读操作可能存在的三类问题:

  1. 脏读: 当前事务(A)中可以读到其他事务(B)未提交的数据(脏数据),这种现象是脏读。举例如下(以账户余额表为例):在这里插入图片描述
  2. **不可重复读:**在事务A中先后两次读取同一个数据,两次读取的结果不一样,这种现象称为不可重复读。脏读与不可重复读的区别在于:前者读到的是其他事务未提交的数据,后者读到的是其他事务已提交的数据。举例如下:
    在这里插入图片描述
  3. 幻读: 在事务A中按照某个条件先后两次查询数据库,两次查询结果的条数不同,这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为:事务A 按照一定条件进行数据读取, 期间事务B 插入了相同搜索条件的新数据,事务A再次按照原先条件进行读取时,发现了事务B 新插入的数据。举例如下:
    在这里插入图片描述

3.2.3事务的隔离级别

  SQL标准中定义了四种隔离级别,并规定了每种隔离级别下上述几个问题是否存在。一般来说,隔离级别越低,系统开销越低,可支持的并发越高,但隔离性也越差。隔离级别与读问题的关系如下:
在这里插入图片描述
  因此在大多数数据库系统中,默认的隔离级别是读已提交(如Oracle)或可重复读(后文简称RR,InnoDB默认的隔离级别是RR)。在SQL标准中,RR是无法避免幻读问题的,但是InnoDB实现的RR避免了幻读问题。

3.2.4MVCC

  MVCC全称Multi-Version Concurrency Control,即多版本的并发控制协议。下面的例子很好的体现了MVCC的特点:在同一时刻,不同的事务读取到的数据可能是不同的(即多版本),关于MVCC的具体内容可以参考以下:
https://www.bilibili.com/video/BV1Vk4y1k7KQ?from=search&seid=10302504634611897305

4.锁机制

在这里插入图片描述

  数据库锁一般可以分为两类,一个是悲观锁,一个是乐观锁。

  • 乐观锁一般是指用户自己实现的一种锁机制,相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。乐观锁,大多是基于数据版本( Version )记录机制实现。
    在这里插入图片描述
  • 悲观锁一般就是我们通常说的数据库锁机制,以下讨论都是基于悲观锁。

  为了保证数据的一致性。mysql数据库存在多种数据引擎,MySQL各存储引擎使用了三种类型(级别)的锁定机制:表级锁定,行级锁定和页级锁定。

4.1行锁

  行锁就是一锁锁一行或者多行记录,mysql的行锁是基于索引加载的,所以行锁是要加在索引响应的行上即命中索引,如下图所示:
在这里插入图片描述
  如上图所示,Sql语句基于索引email查询,命中两条记录。此时行锁一锁就锁定两条记录,当其他事务访问数据库同一张表时,被锁定的记录不能被访问,其他的记录都可以访问到。

  行锁的特征:锁冲突概率低,并发性高,但是会有死锁的情况出现。

4.2表锁

  锁就是一锁锁一整张表,在表被锁定期间,其他事务不能对该表进行操作,必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段,即全表扫描,全表扫描时锁定整张表,sql语句可以通过执行计划看出扫描了多少条记录。由于表锁每次都是锁一整张表,所以表锁的锁冲突几率特别高,表锁不会出现死锁的情况。
https://www.bilibili.com/video/BV1LC4y1h7VV?from=search&seid=13515521950184561666
https://zhuanlan.zhihu.com/p/52678870

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/我家自动化/article/detail/263750
推荐阅读
相关标签
  

闽ICP备14008679号