开发者学习笔记【阿里云云数据库助理工程师（ACA）认证：数据库基本概念(SQL，索引，视图，事务，日志等）（二）】
课程地址：https://edu.aliyun.com/course/3112080/lesson/19076
数据库基本概念(SQL，索引，视图，事务，日志等）（二）
二、数据库基础概念
数据库的基础概念，主要分为这个五个部分 SQL 、索引、视图、事务、日志，这是数据库里面最重要的五个基础概念。
2.1、SQL
SQL的基本概念：
SQL 的全称英文是 Structured Query Language 结构化查询语言,是一种非常特殊的编程语言，也可以认为是 DSL 领域的专业语言，是一种数据库查询和程序设计语言，主要是用于存储数据和查询、更新和管理关系数据库系统。
在数据库这几十年的发展里， SQL 是非常关键的，让数据库经久不衰，并持续发展。它是一种结构化的查询语言，然后主要的对象就是针对数据结构上的一些工作，他不要求用户指定对数据的存放方法，也不需要去了解具体的数据存放方式。完全不需要理解整个底层的数据结构。针对不同的实现，由于它是一个解析性的语言，通过结构化查询语言，可以很容易非常简单的去使用操作数据。同时这个语言又是可以支持嵌套的，使它具有有非常大的灵活性和强大的功能。
SQL 语言是1974年被提出，首先在 IBM 的 SystemR 数据库系统上实现。因为它功能非常的丰富，使用非常的简单这个突出的特点受到用户和工业界非常大的欢迎。1980年，美国国家标准局把它定为标准，之后国际化组织的ISO也是做出了同样的决定，通过标准化的工作，把它推向了世界，从1980年到现在，一直是经久不衰。
Structured Query Language 结构化查询浯言，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
SQL 语言1974年由 Boyce 和 Chamberlin 提出，并首先在 IBM 公司研制的关系数据库系统 SystemR 上实现。由于它具有功能丰富、使用方便灵活、语言简洁易学等突出的优点，深受计算机工业界和计算机用户的欢迎。1980年10月，经美国国家标准局（ANSI)的数据库委员会X3H2批准，将SQL作为关系数据库语言的美国标准，同年公布了标准 SQL ，此后不久，国际标准化组织 （ISO） 也作出了同样的决定。
SQL 六大类：
讲到 SQL 语言，根据他的能力或者是用途，主要分为六大类，数据库的查询语言、数据操作语言、事务控制语言、数据控制语言、数据定义语言、指针控制语言。然后分别来讲一下这个六大类的作用。数据查询语言 （DQL：Data Query Language) 是主要是针对数据查询来做的，典型的保留字 SELECT 是用的最多的，还有其他的像 WHERE，ORDER BY，GROUP BY和 HAVINGVR 等，通过这些来一起联合使用，把数据从数据库里查询出来。第二个是数据操作语言，是与查询语言对应的，因为你要查询，首先数据要插入、删除或更新，用数据操作语言来执行，包括 INSERT、UPDATE和DELETE ，它们分别用于添加、修改和删除。第三个是事务控制语言 TCL ，事务控制点主要有几个像 COMMIT、SAVEPOINT、ROLLBACK 。分别是提交、保存点和回滚。它主要是影响事务的控制，和表及时的更新。第四个是 DCL 数据控制语言，通过GRANT或REVOKE实现权限控制，来保证安全。实现单个表或者列的访问。第五个是数据定义语言 DDL ，真正的数据的定义是 CREATE,ALTER 和 DROP ，主要是负责在数据库里面创建表或修改、删除等或者为表加索引。最后一个是指针控制语言 CCL，像SQL 语句，功能非常强大，类似 java 有增加指针的能力，就相当于做一个集合进行操作，像指针一样的进行数据查询，对一个或者多个表单独行操作都是非常强大的功能。
数据查询语言 （DQL：Data Query Language) ：其语句，也称为“数据检索语句“，用以从表中获得数据，确定数据怎样在应用程序给出。保留字 SELECT 是 DQL （也是所有SQL） 用得最多的动词，其他DQL常用的保留字有 WHERE，ORDER BY，GROUP BY和HAVING 。这些 DQL 保留字常与其它类型的SQL语句一起使用。
数据操作语言 （DML：Data Manipulation Language） ：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。
事务控制语言（ TCL ) ：它的语句能确保被 DML 语句影响的表的所有行及时得以更新。包括 COMMIT （提交）命令、 SAVEPOINT （保存点）命令、 ROLLBACK （回滚）命令。
数据控制语言 （ DCL ） ：它的语句通过 GRANT 或 REVOKE 实现权限控制，确定单个用户和用户组对数据库对象的访问。某些 RDBMS可用 GRANT 或 REVOKE 控制对表单个列的访问。
数据定义语言 （ DDL ） ：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表 （CREATE TABLE或DROP TABLE）；为表加入索引等。
指针控制语言 （ CCL ） ：它的语句， 像 DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT 用于对一个或多个表单独行的操作。
SQL 的特点：
SQ L 语句是数据库关键的能力，也是它经久不衰、长盛不衰的核心原因。 SQL 有几个非常重要的特点，第一个是风格是非常统一的，相当于对数据的独立操作来进行整个生命周期的一个定义关系的模型，然后录入数据，对数据库查询、更新、维护，甚至是数据库的重构、安全等一系列操作，为系统提供良好的开发环境，还可以随时逐步修改模式，也不影响数据库的运行模式，各个数据库之间的差异非常小。风格的统一可以大大降低学习难度。第二个是高度的非过程化，过程化即大家要关心具体实现的细节，因为数据库 SQL 语句的一些性能，所以不需要指定数据在哪里，以及存储数据路径，只要去把命令提交给数据库，就可以直接审核结果，高度的非过程化。第三个是面向集合的操作方式， SQL 不单可以针对单个数据源，也可以一次性插入单独更新对象的整个集合，有批量操作的能力。第四个是他不仅是一个自含式语言即它能够独立地用于与数据库联机交互，而且它又可以嵌入到高级的语言里面，如 C、C#、JAVA 等一系列高级语言。编程的时候也可以把它包含进去，使用方式非常简单，而且语法结构是一样的，可以给用户非常大的灵活性和方便性，像 DBA 或开发人员都可以用。最重要的是它非常简洁和易学易用，我们会看到这个引擎的功能非常强大，让它具有持久的生命力的核心原因是它的设计非常巧妙，完成整个数据进行操纵、控制，核心功能就用到9个动词 CREATE、ALTER、DROP、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE ，且与英语口语非常接近，容易学习、容易使用。
SQL 风格统一
SQL 可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据、建立数据库、查询、更新、维护、数据库重构．数据库安全性控制等一系列操作，这就为数据库应用系统开发提供了良好的环境，在数据库投入运行后还可根据需要随时逐步修改模式，且不影响数据库的运行，从而使系统具有良好的可扩充性。
高度非过程化
非关系数据模型的数据操纵是面向过程的言，用其完或用户请求时，必须指定存取路径。而用 SQL 进行数据操作，用户只需提出“做什么“，而不必指明“怎么做“，因此用户无须了解存取路径，存取路径的选择以及 SQL 语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。
面向集合的操作方式
SQL 采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除更新操作的对象也可以是元组的集合。
以同一种法结构提供两种使用方式
SQL 既是自含式语言，又是嵌入式语言。作为自含式言，它能够独立地于朕机交互的使方式，用户可以在终端键盘上直接输入 SQL 命令对数据库进行操作。作为嵌入式语言，SQL 语句能够嵌入到高级语言 （如 C、C#、JAVA ) 程序中，供程序员设计程序时使用。而在两种不同的使用方式下。 SQL 的法结构基本上是一致的。这种以统一的语法结构提供两种不同的操作方式，为户提供了极大的灵活性与方便性。
语言简洁，易学易用
SQL 功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、设计操纵、数据控制的核心功能只用了9个动词： CREATE、ALTER、DROP、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE 。且 SQL 语言语法简单，接近英语口语。因此容易学习，也容易使用。
2.2、事务
事务的核心就是针对数据的操作，需要有时候是一条语句，有时候是一组 SQL 语句或整个程序。这个最小的单元是事务，而这个事务是数据库和并发控制的基本单元。事务有四个属性，一般叫 ACID 特性，原子性、一致性、隔离性和自救性。原子性就是指事务是一个不可分割的工作整体，即这个事情要么做要么不做；一次性就是它一定会从一个一致状态变成另外一个一致状态，跟原子性是密切关联的，且是在同样的条件下，执行一遍，再执行仍然是同样的一个结果。第三个就是隔离性，就是 A 事务在执行的时候，它不会被 B 事务干扰，A 事务 的操作数据会被锁住，不会使在执行的过程中的数据发生变更，这也是保持一致性的原因，即数据是被锁住的，不发生变更，不会出现 A 执行一次，在同样条件下，不能复制，不能重复执行。第四个是持久性或者永久性，就是事物提交后，对数据的改变是永久性的。接下来的操作或者故障都不会影响数据的持久性，可以通过日志实现这个功能，通过整套的日志机制来保证在一些异常情况下，也能得到正确的数据，这个是非常重要的一个事情。
概念
在关系数据库中，一个事务可以是一条SQL语句，一组 SQL 语句或整个程序。指访问并可能新数据库中各的数项的一个程序执行单元 ( unit ) 。
特性
事务是恢复和并发控制的基本单位。事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。
√原子性 （ atomicity ） 。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
√一致性 （ consistency ） 。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
√隔离性 （ isolation ） 。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的具他事务是隔离的，并发执行的各个事务之间不能互相干扰。
√持久性 （ durability ） 。持久性也称永久性 （ permanence ） ，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
2.3、索引
索引可以参考下图字典的示例，我们用字典的时候，字典里面记录了所有字的关键的信息，解释含义。我们需要索引表通过笔画或偏旁部首等方式怎么快速查到这个字，这是一种查询的方法。在数据库里是也是一样的，要快速找到一个数据，一个个去辨认非常慢，如果针对数据库创建索引，就可以快速的去找到数据。索引是构建一个单独的数据结构来加速整个这数据库里特定信息的查询。
以下图为例，可以在一张表里针对某个字段我们就建一个索引CREATE INDEX ，建立索引之后再去查询速度就加快了。很多这是这个索引，所以里面会有几种索引这样的一个唯一索引组件，索引技术索引。然后这个专业的作用会不一样的。
概念
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
生活中的例子：右边图所示字典的索引。
索引的创建和使用举例：
如果在查询时常用类似以下的语句：
SELECT*FROM mytable WHERE category_id=1；
最直接的应对之道，是为category-id建立一个简单的索引：
CREATE INDEX mytable_categoryid ON mytable (category_id);
索引种类：
索引里面会有几种索引类别，比如唯一索引、主键索引、聚簇索引。每种索引的作用是不一样的，像唯一索引的核心是保证整个表的唯一性，很多场景里面都会有用，例学生表里对学号建了唯一索引，就能保证整个组不会出现重复，不会出现多个同样的学生号的学生。第二主键索引，即每个表定义一个主键，来确定整个表的关键字。第三是聚簇索引，它其实不是一个单独的索引，而是数据存储的一个方式。在 InnoDB 里,表是按照 B+Tree 的索引结构成组的，这棵树的叶子节点 data 域就保存了完整的数据记录，而索引的key就是数据表的主键。所以 InnoDB 的表的数据文件本身就是组的索引，也相当于把它用索引存下来，把数据存下来没作用。
唯一索引： UNIQUE
例如在学生表中给学号字段创建唯一索引： create unique index stusno on student（ sno ）；
表明此索引的每一个索引值只对应唯一的数据记录，对于单列唯—性索引，这保证单列不包含重复的值。
对于多了唯一性索引，保证多列值得组合不重复
主索引 primary key 即唯一+非空
数据库关系图中在给表定义主键将自动创建主键索引，该索引要求主键中的每个值都唯一且非空。
聚集索引（又叫聚簇索引）： cluster
聚簇索引并不是一种单独的索引类型，而是一种据存储方式。在 lnnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。
2.4、视图：
视图的作用其实是一个虚拟表。一张实体表里面具体的一些记录需要经常查询、查看，一个方法是反复查看原表，这样会非常麻烦，尤其是多个表的话，只查特定信息，可以针对这个表设定的列或者是一个表里面设计的行或者组合创建一个虚拟的表，再点击这个视图，当原表发生变化的时候也会发生相应的变化。下面也有个简单的例子，比如这个是一个虚拟的视图，显示整个部门10中员工的编码、姓名和薪水，之后再查询这个部门员工的姓名、编码、薪水就非常简单，不用登到原表里，在整个视图里就可以看到整个部门10的员工的信息。
视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，
视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形
式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
创建一个简单视图 V一EMP一10 ，来显示部门10中的员工的编码、姓名和薪水：
CREATE VIEWv_emp_10
AS
SELECT empno, ename , sal , deptno
FROM emp
WHERE deptno =10；
2.5、日志
日志的基本概念：
数据库的事务保证，原子性的 ACID ，保证其核心靠日志这套系统，这些数据库里面关键的一个设计的机制。现在数据库都是支持多用户的，包括操作的是从事多个并发的，通过日志保证HID1个的原子性、隔离性、唯一性，就数据库写数据的时候，并没有把数据先写下去。而是把操作过程用日志记录下来。等到一定的时机，比如日志快写满了或者commit下去，再把这个记录到里面去。数据库都是有事务日志的，用于记录所有事务以及每个事务对数据库所做的修改，不会出现任何问题，甚至系统出现故障也有能力恢复到一致的状态的。
现在的数据库系统 （Oracel、DB2、MS SQL、MySQL 等） 都支持多用户，所有的数据库系统（包括 Exchange ），都是把数据先写到日志中，等某个时机（比如：确认 commit ）后再与到数据库记录中，日志是数据库最重要的数据之一。
数据库都具有事务日志，用于记录所有事务以及每个事务对数据库所做的修改。事务日志是数据库的重要组件，如果系统出现故障，则可能需要使用事务日志将数据库恢复到一致状态。
MySQL日志：
以MySQL为例，有六种日志文件，分别是redo log、undo log、binlog、errorlog、slow query log、general log 、relary log，最重要的就前三种，这三种日志都是和事务操作然后息息相关的。通过对这三种日志的理解，就能知道整个MySQL怎么通过日志来保证事物。这是第二个是那个然后第二个，然后第二个它保存的就是在这个十五之前的一个版本。这个版本是有了这个东西之后，就是说我今天可以写了一半，或者写写了写完之后我可以回这个回滚，然后通过来控制多个版本之间的相互的并发。来通过这个来搞定的。这个是杭州logo的一个作用。再一个就是window，window的主要作用其实就是那个复组，组成复制的时候，然后通过这个日志来进行4000的。
MySQL中有六种日志文件，分别是:
√重做日志 （redo log）
√回滚日志 (undo log)
√二进制日志 (binlog)
√错误日志 (errorlog)
√慢查询日志 (slow query log）
√一般查询日志 (general log)
√中缝日志 （relary log）
其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。
MySQL重做日志：
先讲redo log,它的核心是要保证事务的持久性，记录的是整个事务的执行状态，数据的写入过程中立马会记录一条日志，用来恢复未写入data file的已成功事务更新的数据，一旦故障的时候，有很多脏页没有写入磁盘，他就会通过redo log进行重做，从而达到事务的持久性。
作用：
确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启MySQL服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。
内容：
物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入red log file的物理文件中去的。
什么时候产生：
事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行程中，便开始写入redo log文件中。
什么时候释放:
当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占的空间就可以重用（被覆盖）。
MySQL回滚日志：
undo log保存的就是事务之前的一个版本，可以用于回滚，提供控制多个版本之间的相互并发的读。
作用：
保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
内容：
逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。
什么时候产生：
事务开始之前，将当前是的版本生成undo log，undo也会产生redo来保证undo log的可靠性
什么时候释放:
当事务提交之后,undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。
MySQL二进制日志：
binlog的主要作用是主从复制的时候，通过这个日志来实现的。它就是将事物里面所有SQL语句记下来并存到主柜里面，要获得同样的数据，就把binlog拿过来重放一下。
作用：
用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。
内容：
逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。不光SQL语句本身，而是包括了执行的SQL语句（增删改）反向的信息，也就意味着de|ete对应着de|ete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。
什么时候产生：
事务提交的时候，一次性将事务中的SQL语句（一个事物可能对应多个sql语句）按照一定的格式记录到bin|og中。这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步与入磁盘。
因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了binlog的情况下，对于较大事务的提交，可能会变得比较慢一些。
什么时候释放：
binlog的默认是保持时间由参数expire-logs-days配置，也就是说对于非活动的日志文件，在生成时间超过expire-logs-days配置的天数之后，会被自动删除。
