mysql

基础知识内容

char和varchar的区别： char是一种固定长度的类型，varchar则是一种可变长度的类型。 char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足。（在检索操作中那些填补出来的空格字符将被去掉）varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节。（即总长度为L+1字节）

MySQL 导出和导入数据：
导出：
表： SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt'; 将runoob_tbl 表中的数据导出到 /tmp/runoob.txt 文件中。
数据库： mysqldump -u root -p RUNOOB [--all-databases(数据库备份策略)] > database_dump.txt; 将导出整个数据库的数据。
mysqldump 命令中指定数据库名及数据表。 mysqldump -u root -p database_name table_name > dump.txt;
导入：
mysql -u root -p database_name < dump.txt; 将拷贝的数据库数据拷贝到新的数据库中。
mysql -uroot -p123456 < runoob.sql; 导入sql。
source /home/abc/abc.sql; 进入sql命令行，导入sql文件。
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl; 使用LOAD DATA INFILE 文件来导入mysqldump 拷贝的数据。
mysqlimport -u root -p --local mytbl dump.txt; 从文件 dump.txt 中将数据导入到 mytbl 数据表中

MVCC 原理

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。
当然存储的并不是实际的时间值，而是系统版本号（system version number)。
每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
SELECT： InnoDB会根据以下两个条件检查每行记录：
InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），
这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果
INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE：InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，
并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

快照读 (snapshot read)：读取的是记录的可见版本 (有可能是历史版本)，不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）。
当前读 (current read)：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

快速数据库数据和表结构迁移：

mysqldump -u root -p –database db1 > /tmp/user.sql

mysql 日志类型

mysql 日志：bin log、relay log、redo log、undo log
bin log: 用于复制，在主从复制中，从库利用主库上的 bin log 进行重播，实现主从同步。
relay log: MySQL 进行主主复制或主从复制的时候会在配置文件制定的目录下面产生相应的 relay log。从服务器线程将主服务器的二进制日志（bin log）读取过来记录到从服务器的本地文件 relay log 中，然后 SQL 线程会读取 relay log 的内容并应用到从服务器。
redo log: 确保事务的持久性。防止在发生故障的时候，尚有脏页未写入磁盘，在重启MySQL 服务的时候，根据redo log 进行重做，从而达到事务的持久性这一特征。
undo log: 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读(MVVC) ，也即非锁定读。
error log
slow query log：慢查询日志。
general log

mysql中各个执行顺序

mysql 中各个部分的执行顺序：from -> on -> join -> where -> group by -> having(聚集函数) -> select -> order by -> limit

事务

事务是必须满足4个条件（ACID）:原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

原理：MySQL事务回滚是依靠undo log实现。undo log属于逻辑日志，其中记录的是sql执行的信息，当发生回滚时，InnoDB存储引擎会依据undo log的内容做出相反的操作。例如delete操作相反就是insert操作。

脏读：A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。
不可重复读：事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间。
幻读：事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后。

事务的四种隔离级别：未提交读、已提交读、可重复读、串行化读。
mysql 默认隔离级别：可重复读

分布式事务

分布式事务是指事务的参与者、支持事务的服务器、资源服务器和事务管理器分别位于不同的分布式系统的不同节点上。本质上，分布式事务就是保证不同数据库之间的一致性。
CAP原则：指的是一个系统中Consistency（一致性，指在分布式系统中的所有数据备份，在同一时刻都保持同样的值）、availability（可用性，集群一部分节点故障后，集群依旧可以响应客户端的响应）、Partition tolerance（分区容错性，可以理解为不同节点之间通信的时限要求），三者不可兼得，要么CP 要么 AP ，其中CAP不可能同时满足。
BASE理论：指的是基本可用Basically Available，软状态Soft State，最终一致性Eventual Consistency，核心思想是即便无法做到强一致性，但应该采用适合的方式保证最终一致性。

强一致性：指任何一次读都能读到某个数据的最近一次写的数据。系统中的所有进程，看到的操作顺序，都和全局时钟下的顺序一致。换言之就是在任意时刻，所有节点中的数据是一致的。
弱一致性：指数据更新后，如果能忍受后续的访问只能访问到部分或者全部访问不到，则是弱一致性。
最终一致性：不保证在任意时刻任意节点上的同一份数据都是相同的，但是随着时间变化，不同节点上的数据同一份数据在朝着趋近一致化的方向演进。简单点就是说在一段时间后，节点间的数据会达到最终一致性。

柔性事务：不同于ACID 的刚性事务，在分布式事务的场景下基于BASE理论，就出现了柔性事务的概念。如果想通过柔性事务来达到最终一致性，就需要依赖于一些特性，这些特性在具体的方案中不一定都满足，因为不同的方案要求不同，但是若都不满足的情况下，是不可能做柔性事务的。

幂等性操作：任意多次执行所产生的影响均与第一次执行的影响相同。幂等函数，或幂等方法，是指可以使用相同参数重复执行，并能获得相同结果的函数。例如支付接口回调网络正常的情况下无论回调多少次都会返回成功。

分布式事务的解决方案：
1、两阶段提交/XA：
两阶段提交顾名思义就是分两步提交。存在着一个负责协调各个本地资源管理器的事务管理器，本地资源服务器一般由数据库实现，事务管理器在第一阶段的时间询问各个资源管理器是否准备就绪？如果收到每个资源回复的都是yes，则在第二阶段提交事务，如果其中一个资源的恢复是no，则回滚事务。
大致流程：
第一阶段（prepare）：事务管理器向所有本地资源管理器发起请求，询问是否为ready 状态，所有参与者都将本事务能否成功的信息反馈给协调者。
第二阶段（commit/rollback）：事务管理器根据所有本地资源服务器的反馈，通知所有本地资源管理器，步调一致地在所有分支上提交或者回滚。
存在的问题：
同步阻塞：当参与事务者存在占用公共资源的情况，其中一个占用了资源，那么其他的事务参与者就只能阻塞等待资源释放，处于阻塞状态。
单点故障：一旦事务管理器出现故障，就会导致整个系统不可用。
数据不一致：在阶段二中，如果事务管理器只发送了部分commit消息，那么此时网络故障，只有部分参与者收到了commit 消息，也就是说部分参与者提交了事务，这也就导致系统数据不一致。
不确定性：当事务管理器发送commit 之后，并且此时只有一个事务参与者收到了commit ，那么当该事务参与者与事务管理器同时宕机之后，重新选举的事务管理器无法确定该条消息是否提交成功。

2、TCC：
TCC 事务机制相比上面地两阶段提交解决了以下问题：
解决协调者单点：由主业务方发起并完成这个业务活动。业务活动管理器也变成多点，引入集群。
同步阻塞：引入超时，超时后补偿机制，并且不会锁定整个资源，将资源转化为业务逻辑形式，粒度变小。
数据一致性：有了补偿机制后，由业务活动管理器控制一致性。
TCC （Try Confirm Cancel）：
Try 阶段：尝试执行，完成所有业务检查（一致性），预留必须业务资源（准隔离性）。
Confirm 阶段：确认执行真正执行任务，不作任何业务检查，只使用 Try 阶段预留的业务资源，Confirm 操作满足幂等性。要求具备幂等性设计，Confirm 失败后需要进行重试。
Cancel 阶段：取消执行，释放 Try 阶段预留地业务资源 Cancel 操作满足幂等性，Cancel 阶段地异常和Confirm 阶段的异常处理方案基本上一致。
在 Try 阶段是对业务系统进行检查和资源预览，比如订单和存储操作，需要检查库存剩余是否足够，并进行预留，预留的话就是新建一个可用库存数量字段，Try 阶段操作是对这个可用库存数量进行操作。
基于 TCC 实现事务分布式，会将原有的一个接口就可以实现的逻辑拆分为三个接口Try、Confirm、Cancel实现，所以代码复杂度会相对较高。

3、本地消息表：
本地消息表这个方案主要是由消息生产者和消费者这两个角色组成。
当系统A被其他系统调用发生数据库表更新操作时，首先会写入更新数据库的业务表，其次会往相同数据库的消息表中插入一条数据，两个操作发生在同一个事务中。
系统A的脚本定期轮询本地消息向mq队列中写入一条消息，如果消息发送失败会进行重试。
系统B消费mq队列中的消息，并处理业务逻辑。如果本地事务处理失败，则会再继续消费mq队列中的消息进行重试，如果业务上失败，可以通知系统A进行回滚操作。
本地消息表实现的条件：
生产者与消费者接口都要支持幂等性。
生产者需要创建额外的消息表。
需要提供补偿机制，如果消费者业务失败，则生产者需要进行回滚操作。
容错机制：
步骤1失败，本地事务可以直接回滚。
步骤2和3写入mq队列与消费mq队列中的消息失败会进行重试。
步骤3业务失败系统B会向系统A发起事务回滚操作。

此方案的核心在于将需要分布式处理的任务通过消息日志的方式异步执行。消息日志可以保存在本地文本、数据库或者消息队列，再通过业务规则自动或人工发起重试。人工重试更多的是应用于支付场景，通过对账系统对事后问题的处理。

4、可靠消息一致性：
系统A首先向mq队列发送一条prepare消息，如果prepare消息发送失败，则直接取消操作。
如果消息发送成功，则执行本地任务。
如果本地任务执行成功，则向mq队列中发送一条confirm 消息，如果发送失败则回滚任务。
系统B定期消费mq队列中的confirm 消息，执行本地事务，并发送ack消息。如果系统B 本地事务执行失败，会一直重试，但若是业务失败则会向系统A 发送回滚请求。
mq队列定期轮询所有prepare 消息调用系统A 提供的接口查询消息处理的状态。如果该prepare 消息本地事务处理成功，则重新发送confirm 消息，否则直接回滚该任务。

该方案与本地消息表最大的不同在于去掉了本地消息表，其次本地消息表依赖消息表重试写入mq队列代表该方案中的轮询 prepare 状来重试或者回滚该任务。其实现方案基本上与容错方案基本一致。

5、尽最大努力通知：
最大努力通知是简单的一种柔性事务，适用于一些最终一致性时间敏感度比较低的业务，且被动方处理结果不影响主动方的处理结果。主要思路就是：
系统A 本地事务执行完成后，发送消息到mq队列。
脚本轮询mq队列，调用系统B对应的接口。
系统B 执行成功就ok。若是其中出现失败，则会进入重试，反复重试N 次，依旧失败则会放弃。

分布式事务实践：
1、两阶段提交用于保障在大规模分布式环境下事务的最终一致性。
2、TCC 需要事务接口提供Try、Confirm、Cancel 三个接口，提高了编程的复杂性。依赖于业务方来配合提供这样的接口，推行难度较，所以一般不推荐。
3、阿里的RocketMQ。
4、跨行转账可通过该方案实现。
5、最大努力通知最常见的场景就是支付回调，支付服务收到第三方服务支付成功通知后，先更新自己库中订单支付状态，然后同步通知订单服务支付成功。如果此次同步通知失败，会通过异步脚步不断重试地调用订单服务的接口。

锁

共享锁（读锁）： 其他事务可以读，但不能写。
排他锁（写锁）： 其他事务不能读取，也不能写。

表级锁：开销小，加锁快。不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢。会出现死锁。锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间。会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。

间隙锁

解决可重复读下的幻读问题。幻读的问题在于插入或者更新操作时会出现不一致问题，随即锁定一个范围，不包括记录本身。
加锁的基本单位是（next-key lock）,遵循前开后闭原则，给唯一索引加锁的时候，next-key lock升级为行锁。
原理：当进行范围查询而不是相等条件检索，并且请求共享锁或者排他锁时，会为符合条件的已有数据的索引上加锁，对于键值在条件范围内但是不存在的记录会加上间隙锁。

索引

原理：索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。索引的原理很简单，就是把无序的数据变成有序的查询：
把创建了索引的列的内容进行排序。
对排序结果生成倒排表。
在倒排表内容上拼上数据地址链。
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

最左匹配原则：在 MySQL 中建立联合索引时要遵循最左前缀匹配的原则，即最左优先。在检索数据时从联合索引的最左边开始匹配。
最左匹配原则原理：索引的底层是一个b+树，而b+树中存储的是键值对。然而在b+树中存储的排序是以联合索引最左边的第一个字段排序为基准，再依次按照其他字段进行排序操作，这样在不知道前一个字段排序的基础上，是没有办法找到下一个排序的基准的，所以就没有办法利用索引。

索引条件下推：在 MySQL 5.6 中引入 Index Condition Pushdown 优化。可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引失效：
多个排序方向。 order by a desc, b asc
引用不在索引中的列。
无法构成最左使用前缀。

使用范围查询。 IN、 NOT IN、 like '%a'、
表关联使用列。

哪些字段适合作为索引： 主键、外键。 与其他表进行连接的字段。 选择性高的字段。（字段的的内容比较多） 经常出现在where子句中的字段。
不适合作为索引的字段： 选择性低的字段。（字段内容较少） 大的文本字段甚至是超长字段内容。 频繁修改的字段。
优点和缺点： 极大的缩短查询时间。 在进行排序和分组时，可以极大地节省时间。 索引的创建和维护都需要时间和额外的存储空间。 表数据的增加同时索引也需要对应的维护。

b+树的叶子节点存储的是什么？
当索引为聚簇索引时，叶子节点存储的是主键和该行的数据。但如果为二级索引时，存储的是该行数据的主键值。

主从复制

在master mysql 上提交事务后，写入到 binlog 中，返回事务成功标记。然后主服务器中的线程将 binlog 发送到slave 服务器，转储到从服务器的 relay log上。然后从服务器上再将 relay log 读取出来应用到 mysql 中。

表碎片化

碎片化是指在 MySQL 的数据文件中存在着一些不连续的空白空间，这些空间因为可利用长度太小或数据存储位置不连续，久而久之造成数据逻辑存储位置于逻辑存储位置不一致的情况。

原因

InnoDB 表的数据存储在页中，每个页中可以存放多条数据，这些数据以树形结构存储，被称为 B+ 树索引。表中的数据和辅助索引均使用 B+ 树索引，而维护所有数据的 B+ 树被称为聚簇索引，通过主键来组织，聚簇索引的叶子结点中包含所有的值，辅助索引的叶子结点中包含索引列和主键列。

碎片化的一个重要特征就是占用的磁盘空间比应该占用的磁盘空间要多。

• delete

当执行 delete 操作时，影响范围内的数据只是被标记已删除，而不是真正从索引中删除，因而磁盘空间并没有被回收。InnoDB 的 purge 线程会异步清理这些无用的数据和索引，但其占用的空间并没有被释放给磁盘，因此会导致在页中存在着很多的不连续未使用空间。

执行 delete 操作，经过 InnoDB 的 purge 线程异步清理，页中的未使用空间可能会被后来 insert 的数据所填充，从而会导致数据的存储位置不连续，即物理存储位置与逻辑存储位置不一致，这就造成数据碎片。

• update

对于 update 操作也会出现页分裂，频繁的页分裂会导致页变得稀疏，被数据不断填充，从而导致数据碎片。

寻找

SHOW TABLE STATUS FROM databases [LIKE 'table_name'];

该语句可以查看数据库下所有或单个表的信息，其中 Data_free 字段表示该表碎片化信息的大小。

解决

• optimize table

optimize table 会重组表和索引的物理页，同时也会锁表。其支持的表类型：InnoDB、MyISAM、ARCHIVE、NDB，通过重组存储物理页，减少访问空间和优化 IO 有效果。

工作原理：

• InnoDB 对于 InnoDB 表，执行 optimize table 会映射执行 alter table ... force，通过重建表以更新索引统计信息并释放聚簇索引中未使用的空间。

  optimize table 在以下条件下使用表复制方法重建表：

  • 启用 old_alter_table 系统变量。
  • 启用 mysqld --skip-new 选项。

• MyISAM 对于 MyISAM 表，如果表有已删除的行或拆分行，则修复该表；如果未对索引页面进行排序，则进行排序；如果表的统计信息不是最新的，则更新。

• alter table xxx engine=InnoDB

当执行该操作时，实际执行的是一个空 ALTER 操作，但是该命令可以起到优化的作用，其会重建整个表，删除未使用的空间。

mysql 优化

开启查询缓存，优化查询。
explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。
当只需要一行数据时建议使用limit 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
为搜索字段建索引。
在值有限的字段建议使用 ENUM 而不是 VARCHAR。
Prepared Statements （参数化查询）很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。
Prepared Statements （参数化查询）可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击
垂直分表：将一个表按照字段分成多表，每个表存储其中一部分字段。
选择正确的存储引擎

慢查询

打开慢查询的日志：
show variables like ‘%slow_query_log%’;
long_query_time = 1 // 慢查询超时时间
log-slow-queries = /var/lib/mysql/mysql-slow.log // 慢查询日志记录地址
分析慢查询日志：mysqldumpslow
慢查询优化步骤：
1、关闭mysql缓存。
2、where条件查看最小返回记录。
3、explain 查看sql执行计划。
4、存在order by、limit等先进行排序后查询。
5、按照索引规则添加索引。
6、了解业务使用场景。
sql优化 Explain：
id：SELECT 的查询序列号，体现执行优先级，如果是子查询，id的序号会递增，id 值越大优先级越高，越先被执行。
select_type：表示查询的类型。
table：输出结果集的表。
partitions：匹配的分区。
type：对表的访问方式。ALL：全表扫描。Index：索引遍历。Range：只使用索引检索给定范围的行。
possible_keys：表示查询时，可能使用的索引。
key：表示实际使用的索引。key 列显示了 SQL 实际使用索引，如果没有则是 NULL。
key_len：索引字段的长度。
ref：列与索引的比较。
rows：扫描出的行数（估算的行数）。
filtered：按表条件过滤的行百分比。
Extra：执行情况的描述和说明。
Using index：查询的列被索引覆盖。Using where：即没有用到索引，回表查询。Using temporary：使用一个临时表。Using filesort：使用一个外部索引排序，而不是按索引次序从表里读取行。Using index condition：查询的列不全在索引中，where 条件中是一个前导列的范围。
using filesort:出现这个选项的常见情况就是 Where 条件和 order by 子句作用在了不同的列上。建立联合索引可以避免这种情况。
Using temporary：group by的列上没有索引。group by有索引但是order by没有索引。解决办法就是解决对应的产生原因即可。

视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。通过视图，可以展现基表（用来创建视图的表）的部分数据，视图数据来自定义视图的查询中使用的表，使用视图动态生成。
视图优点：
重用SQL。
简化复杂的SQL 语句。
使用表的部分而不是整个表。
保护数据并且可以修改数据格式和表示。
视图使用 create view product as select * from a 语句来创建。
使用 show create view product 来查看创建视图的语句。
使用 DROP 删除视图，其语法是 drop view viewName 。
更新视图时，可以先用 DROP 再使用 CREATE ，也可以直接使用 CREATE OR REPLACEVIEW 。如果要更新的视图不存在第二个语句就会创建一个视图。

游标

游标是一个存储在mysql 服务器上的数据库查询，他不是一条select 查询语句，而是被该语句检索出的结果集。在存储了游标之后，应用程序就可以根据需要滚动或者浏览其中的数据。
使用游标步骤：
在能够使用游标之前，必须首先声明它，在这个过程中并没有检索数据，而是定义要使用的select 语句。
一旦声明之后，必须打开游标以供使用，这个过程用前面定义的select 语句把数据实际检索出来。
对于填有数据的游标，根据需要检索各行。
结束游标使用时，必须关闭游标。
创建游标：

-- 循环检索数据，从第一行到最后一行

存储过程

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程优点：
存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。
创建视图：

mysql> delimiter $$

调用视图：

CALL inout_param(12, @a, @b);

触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
什么条件会触发：insert、 delete、 update
触发频率：针对每一行执行。
触发器定义在表上，附着在表上。

mysql> CREATE TRIGGER trig1 AFTER INSERT

window函数

Window 函数可以理解为 GROUP BY 函数的一种补充，其精髓在于一个关键词：动态

一个 frame 可以近似类比为 GROUP BY 中的一个 group，即以某个标准（group 中是 group by 后的条件）下分组产生的一组数据，frame 的分组标准从 某个具体的条件 强化到 每一行数据

大部分 SQL 语言支持通过 PARTITION BY 和 FILTER 来实现在分配到 frame 前的过滤

在指定了 PARTITION BY 后的字段的情况下，分配到每个 frame 的数据必须满足这些字段的值和 frame 中 CURRENT_ROW （也就是“轴”）的值一致，基于 PARTITION BY 的 window 函数近似可以理解为对于全表先做一次 GROUP BY 处理，然后只将某个 GROUP 内的数据分配到具体的 frame 当中

在分配的时候会希望过滤掉一些脏数据，这个时候 FILTER 关键字可以帮助我们实现这一点

窗口函数窗口大小：

N PRECEDING		N 必须是具体的正整数，代表向上取 N 行数据		BETWEEN 1 PRECEDING AND 1 FOLLOWING
N FOLLOWING		N 必须是具体的正整数，代表向下取 N 行数据		BETWEEN 1 PRECEDING AND 1 FOLLOWING
UNBOUNDED		代表不设条目限制		BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
CURRENT ROW		代表当前行，也就是作为分组依据的行		BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

BETWEEN CURRENT ROW | UNBOUNDED PRECEDING | expr PRECEDING AND CURRENT ROW | UNBOUNDED FOLLOWING | expr FOLLOWING

窗口函数：

• 序号函数

row_number(), rank(), dense_rank()
对每个部门的员工按照薪资排序，并给出排名。
rank() 排序相同时会重复，但总数不会少，类似1、1、3；dense_rank() 排序相同时会重复，总数会减少，类似1、1、2；row_number() 排序相同时不会重复，根据顺序排序。
select dname, ename, salary, row_number() over (partition by dname order by salary desc) as rk from employee;

• 开窗聚合函数

sum(), avg(), min(), max(), count()
在窗口中每条记录动态地应用聚合函数，可以动态计算在指定窗口内的各种聚合函数值。
对每个部门的员工按照薪资排序，并加上前面一位薪资，得到新的一列c1。
select dname, ename, salary, sum(salary) over (partition by dname order by salary rows between 2 preceding and current row) as c1 from employee;

• 分布函数

cume_dist()
分组内小于、等于当前rank值的行数 / 分组内总行数。
查询小于等于当前薪资（salary）的比例。
select dname, ename, salary, rank() over (partition by dname order by salary desc) as rn, CUME_DIST() over (partition by dname order by salary desc) as rn2 from employee;

• 前后函数

返回当前行的前n行 lag(expr, n) 或后n行 lead(expr, n) 的 expr 的值。
查询前1名员工的入职时间（默认值'2000-01-01'）和前2名员工的入职时间。
select dname, ename, hiredate, salary, lag(hiredate, 1, '2000-01-01') over (partition by dname order by hiredate) as last_1_time, lag(hiredate, 2) over (partition by dname order by hiredate) as last_2_time from employee;

• 头尾函数

first_value(), last_value()
返回第一个或最后一个 expr 的值。
截止当前，按照日期查询查询第一个入职和最后一个入职员工的薪资。
select dname, ename, hiredate, salary, first_value(salary) over (partition by dname order by hiredate) as first, last_value(salary) over (partiion by dname order by hiredate) as last from employee;

• 其他函数

ntile(n) 将分区中有序数据分为n个等级，记录等级数。
将每个部门员工按照入职日期分成3组。
select dname, ename, hiredate, salary, ntile(3) over (partition by dname order by hiredate) as rn from employee;

nth_value(expr, n) 返回窗口函数的第n个expr的值。
截止当前，显示每位员工的薪资中排名第2和第3的薪资。
select dname, ename, hiredate, salary, nth_value(salary, 2) over (partition by dname order by hiredate) as second_score, nth_value(salary, 3) over (partition by dname order by hiredate) as third_score from employee;

with函数

视图函数（VIEW）或是 with 函数所代表的 CTE （Common Table Expression，公用表表达式）的主要目的是提高部分查询的可复用性，对于相当多的日常工作而言，很多的查询是高度重复的

VIEW 和 WITH 的区别在于哪里？两者的差别在于作用域（如果你熟悉一些编程的基础的话）和 持久性

	VIEW	WITH
作用域	可以跨查询复用	只能在单次查询内复用
持久性	存入数据库，持续可用	单次使用，在数据库不存储

VIEW 函数的结果是一张虚拟表，这种表和在数据库的数据表有什么关系？
VIEW 函数所产出结果的结果称为“虚拟表”。尽管 VIEW 函数的结果确实 "可以像普通表格一样被访问"，但是 VIEW 储存的本质上是一组“当前可用的查询语句”，可以当做一个以某些数据表为参数输入，一张具体的表格作为输出的函数，当其所依赖的数据表出现改变，这些“查询语句”就有可能变得不再可用。

条件表达式函数

• CASE WHEN condition THEN result ELSE result END

SELECT student_name, (CASE WHEN score >= 80 THEN '优秀' WHEN score >= 60 THEN '良好' WHEN score < 60 THEN '不及格' ELSE '异常' END) as desc FROM student;

• CASE expression WHEN val THEN result END else_result

SELECT student_name, (CASE sex WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '未知' END) as sex FROM user;

• IF(expr, result_true, result_false)

SELECT name, IF(sex=1, '男', '女') as sex FROM user;

• IFNULL(expr, result)

判断某个列是否为null，为null 则返回result 值。
SELECT IFNULL(price, 0) as price FROM product;

• NULLIF(expr1, expr2)

判断两列的值是否相等，不相等返回null，相等则返回expr1 的值。
SELECT NULLIF(price, discounted_price) as price FROM product;

• COLALESCE(val, ...)

返回参数中第一个非空表达式
SELECT COLALESCE(null, 'A', 'B'); -- 'A'

• group_concat([distinct] expr [order by expr asc/desc] [separator ','])

select sell_date, group_concat(distinct product order by product separator ',') as products from Activities group by sell_date;

日期函数

now()

返回当前日期和时间

curdate()/curtime()

返回当前日期/时间

date()

提取日期或日期/表达式的日期部分
date(now) --2024-11-01

extract(unit from date)

返回时间/日期的单独部分
extract(YEAR from now()) --2024
extract(MONTH from now()) --11

date_add(date, interval expr type)

给日期添加指定的时间间隔
date_add(now(), interval 7 day) --2024-11-08

date_sub(date, interval expr type)

给日期减去指定的时间间隔
date_sub(now(), interval 1 month) --2024-10-01

datediff(date1, date2)

返回两个日期之间的天数
datediff('2024-11-01', '2024-11-02') --1

date_format(date, format)

以不同的格式显示日期/时间格式
date_fromat(now, '%Y-%m-%d %H:%i:%s') --2024-11-01 00:00:01

str_to_date(str, format)

将字符串的日期转换为日期
str_to_date('20241101', '%Y-%m-%d') --2024-11-01