SQL学习

一、SQL怎么运行的：

1、执行顺序

  FROM、JOIN(第三步)、ON(第二步)、WHERE、GROUP BY、AVG/SUM…、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT

2、MySQL 基本架构概览

  连接器： 身份认证和权限相关(登录 MySQL 的时候)。

  查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

  分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

  优化器： 按照 MySQL 认为最优的方案去执行。

  执行器： 执行语句，然后从存储引擎返回数据。

  简单来说 MySQL 主要分为 Server 层和存储引擎层：

  Server 层： 主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

  存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。 现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

3、Server 层基本组件介绍

  连接器
  连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

  主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

  查询缓存(MySQL 8.0 版本后移除)
  查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。 通过配置have_query_cache参数来开启或关闭查询缓存

  连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询语句，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

  MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

  所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

  MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

  缓存不适用的情况：
  缓存的锁的力度比较大，而且对于动态sql的支持度不够。

  缓存在数据进行更新的时候，是进行的表级锁，更新结束后，会把所有与更新内容相关的缓存全部删除。所以，如果表的写入比较多的话，缓存是比较浪费性能的。如果写入特别多，可能缓存反而会导致mysql变慢。

  查询不到缓存的情况：
  查询条件有不确定数据：如now ，current_time等。

  缓存对大小写敏感，如select * from test 和SELECT* FROM test 就不会解析为同一条sql

  查询带来的额外开销：
  开始前需要先检查缓存是否命中。

  结果输出的时候，需要额外进行数据的缓存操作。

  写入数据时，mysql会将对应表的所有缓存都设置为失效。当缓存内存较大的时候，会导致系统消耗较大。

  分析器
  MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

  第一步，词法分析： 一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
  第二步，语法分析： 主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。
  完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

  优化器
  优化器的作用就是它认为的最优的执行方案去执行。

  比如在一个语句查询某个表时，该表可能有多个索引，此时使用哪个索引会使语句的执行效率最高？这就是优化器要做的事情。

  再比如，执行语句select * from t1 join t2 on t1.ID=1 and t2.ID=2，该语句执行时，是先从t1表中找到ID=1的行关联到t2表之后，再从t2表中查找ID=2的行。还是先从t2表中找到ID=2的行关联到t1表之后，再从t1表中查找ID=1的行。两种执行顺序可能就导致执行效率的不同，怎样选择执行顺序会提高执行效率，这也是优化器要做的事情。

  可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

  执行器
  当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

4、语句分析

  查询语句
  说了以上这么多，那么究竟一条 sql 语句是如何执行的呢？其实我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

  先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
  通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

  接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：
  先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。
  先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

  那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

  进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

  更新语句

update tb_student A set A.age='19' where A.name=' 张三 ';

  我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

  先查询到张三这一条数据，如果有缓存，也是会用到缓存。
  然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
  执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
  更新完成。

  这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

  这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

  并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行， 为什么 redo log 要引入 prepare 预提交状态？ 这里我们用反证法来说明下为什么要这么做？

  先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份(MySQL主从是基于binlog方式进行数据同步的)的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

  先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

  如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

  判断 redo log 是否完整，如果判断是完整的，就立即提交。
  如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。这样就解决了数据一致性的问题。

  SQL 的执行过程分为两类：
  对于查询等语句执行流程如下：权限校验 --> 查询缓存 --> 分析器 --> 优化器 --> 权限校验 --> 执行器 --> 引擎

  对于更新等语句执行流程如下：分析器 --> 权限校验 --> 执行器 --> 引擎—redo log prepare --> binlog --> redo log commit

二、索引（index）

  索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。简单说来，索引就是指向表中数据的指针。索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。创建索引需要使用 CREATE INDEX 语句，该语句允许对索引命名，指定要创建索引的表以及对哪些列进行索引，还可以指定索引按照升序或者降序排列。同 UNIQUE 约束一样，索引可以是唯一的。这种情况下，索引会阻止列中（或者列的组合，其中某些列有索引）出现重复的条目。

1、索引的使用成本

  在表中插入、修改或者删除数据时，数据库引擎也必须维护索引，以保持索引和原始表的同步；也就是说，使用索引是有额外开销的。不适合的索引，或者过多的索引，都会降低插入、修改和删除数据的效率。

  索引还会占用磁盘空间，增加 I/O 成本，过多的索引甚至会增加碎片。

  鉴于以上两点，使用索引时应该遵循以下几条原则：
  仅在被频繁检索的字段上创建索引。
  针对大数据量的表创建索引，而不是针对只有少量数据的表创建索引。
  通常来说，经常查询的记录数目少于表中总记录数据的 15% 时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
  尽量不要在有大量重复值得字段上建立索引，比如性别字段、季度字段等。

2、语法

  在某个字段上创建索引的基本语法如下：

CREATE INDEX index_name
ON table_name;

  index_name 是索引的名字，以后在删除索引时会用到。

  单列索引：
  单列索引基于单一的字段创建，其基本语法如下所示：

CREATE INDEX index_name
ON table_name (column_name);

  唯一索引：
  唯一索引不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值。其基本语法如下所示：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

  聚簇索引：
  聚簇索引在表中两个或更多的列的基础上建立。其基本语法如下所示：

CREATE INDEX index_name
on table_name (column1, column2);

  创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。

  如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。

  隐式索引：
  隐式索引由数据库服务器在创建某些对象的时候自动生成。例如，对于主键约束和唯一约束，数据库服务器就会自动创建索引。

  使用 SQL 语句创建一个包含七列的 website 表：

CREATE TABLE website (
    id      INT              NOT NULL   AUTO_INCREMENT,
    name    VARCHAR(20)      NOT NULL,
    url     VARCHAR(30),
    age     TINYINT UNSIGNED NOT NULL,
    alexa   INT UNSIGNED     NOT NULL,
    uv      FLOAT                       DEFAULT '0',
    country CHAR(3)          NOT NULL,
    PRIMARY KEY (`id`)
);

  可以针对 name 字段创建索引，用以提高检索姓名时的效率，如下所示：

#myIndex 是索引的名字。
CREATE INDEX myIndex
ON website(name);

3、删除索引

ALTER TABLE website
DROP INDEX myIndex;

三、分析函数

  分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

  分析函数和聚合函数的不同之处：普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

  分析函数的形式

  分析函数带有一个开窗函数：over()

  包含三个分析子句：分组(partition by)，排序(order by)，窗口(rows)

  他们的使用形式如下：

over(partition by xxx order by yyy)

#注：rows between unbounded preceding and current row后为窗口范围子句
over(partition by xxx order by yyy rows between unbounded preceding and current row)                  

1、聚合分析函数

  SUM ：该函数计算组中表达式的累积和

  COUNT ：对一组内发生的事情进行累积计数

  MIN ：在一个组中的数据窗口中查找表达式的最小值

  MAX ：在一个组中的数据窗口中查找表达式的最大值

  AVG ：用于计算一个组和数据窗口内表达式的平均值。

2、排名分析函数

  ROW_NUMBER ：正常排序[1,2,3,4] 必须有order_by

  RANK ：跳跃排序[1,2,2,4] 必须有order_by

  DENSE_RANK ：密集排序[1,2,2,3] 必须有order_by

  FIRST ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行

  LAST ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

  FIRST_VALUE ：返回组中数据窗口的第一个值

  LAST_VALUE ：返回组中数据窗口的最后一个值。

3、数学分析函数

  STDDEV ：计算当前行关于组的标准偏离

  STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根

  STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根

  VAR_POP ：该函数返回非空集合的总体变量（忽略null）

  VAR_SAMP ：该函数返回非空集合的样本变量（忽略null）

  VARIANCE ：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回

  VAR_SAMP COVAR_POP ：返回一对表达式的总体协方差

  COVAR_SAMP ：返回一对表达式的样本协方差

  CORR ：返回一对表达式的相关系数

  CUME_DIST ：计算一行在组中的相对位置

  NTILE ：将一个组分为"表达式"的散列表示（类于Hive的分桶原理）

  PERCENT_RANK ：和CUME_DIST（累积分配）函数类似

  PERCENTILE_DISC ：返回一个与输入的分布百分比值相对应的数据值

  PERCENTILE_CONT ：返回一个与输入的分布百分比值相对应的数据值

  RATIO_TO_REPORT：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比 REGR_ (Linear Regression)

  Functions ：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用

4、行比较分析函数

  LAG和LEAD分析函数可以在同一次查询中取出同一字段的前N行的数据(LAG)和后N行的数据(LEAD)作为独立的列。

  LAG ：可以访问结果集中的其它行而不用进行自连接，lag(a,1,0) over(partition by b order by c)

  LEAD ：LEAD与LAG相反，LEAD3可以访问组中当前行之后的行 ， lead(a,1,0) over(partition by b order by c)

  假设你要看你自己每个月的账单情况，顺便和历史账单做个对比。
  取某一个月，Lag()呢就是跟这个月之前的月份去对比，Lead()就是跟这个月之后的月份去对比。还可以结合sum()或者max()聚合函数一起看数据。

lag(a,1,0) over(partition by b order by c)
  假设某人向银行借了一笔钱，分24期还钱。

  lag(repay_amount,1,0) over(order by cur_stage) 是根据字段cur_stage排序
  lag(repay_amount,1,0)中第二个参数是偏移量的意思，就是取上一期。

  当cur_stage = 16的时候，它的上一期是15，取到值为第15期的repay_amount = 1341.34
  当cur_stage = 17的时候，它的上一期是16，取到值为第16期的repay_amount = 858.35
  当cur_stage = 18的时候，它的上一期是17，取到值为第17期的repay_amount = 858.35
  当cur_stage = 19的时候，它的上一期是18，取到值为第18期的repay_amount = 858.35

  当cur_stage = 1的时候，它的上一期因为没有，所以为空，这里第二个参数是为空的默认值取0。

  lag(repay_amount,2,-2) over(order by cur_stage)

  这里的偏移量为2，第1,2期因为没有上上一期，所以空值默认为-2。

  当cur_stage = 16的时候，它的上上一期是14，取到值为第14期的repay_amount = 1341.34
  当cur_stage = 17的时候，它的上上一期是15，取到值为第15期的repay_amount = 1341.34
  当cur_stage = 18的时候，它的上上一期是16，取到值为第16期的repay_amount = 858.35
  当cur_stage = 19的时候，它的上上一期是17，取到值为第17期的repay_amount = 858.35

  lag(repay_amount,3,-3) over(order by cur_stage)

  这里的偏移量为3，第1,2,3期因为没有往前3期，所以空值默认为-3。

  当cur_stage = 16的时候，它的往前3期是13，取到值为第13期的repay_amount = 1341.34
  当cur_stage = 17的时候，它的往前3期是14，取到值为第14期的repay_amount = 1341.34
  当cur_stage = 18的时候，它的往前3期是15，取到值为第15期的repay_amount = 1341.34
  当cur_stage = 19的时候，它的往前3期是16，取到值为第16期的repay_amount = 858.35

  同理，lead函数就是往后偏移。参数2和参数3是选填。

四、PLSQL

五、cursor

六、store procedure

七、function