数据库

"数据库世界浩瀚如海，掌握核心方能游刃有余！本次分享将围绕数据库全栈知识体系展开：从MySQL、PostgreSQL等主流产品到分片、复制等分布式核心，涵盖索引、事务等经典理论，解析SQL语法与NoSQL特性。特别梳理数据库设计全流程从需求分析到实施维护，详解查询优化、锁机制等实战技巧。无论是开发者关注的存储引擎、连接工具，还是DBA必备的备份策略、云数据库管理，这里都有体系化拆解。快来一起探索数据存储的奥秘吧！"

数据库

• 理论和基础
  • 数据库是如何工作的
    • 数据结构
      • 时间复杂度
        • 如果要处理2000条元素： O(1) 算法会消耗 1 次运算 O(log(n)) 算法会消耗 7 次运算 O(n) 算法会消耗 2000 次运算 O(n*log(n)) 算法会消耗 14,000 次运算 O(n^2) 算法会消耗 4,000,000 次运算
      • 归并排序
        • 归并排序是把问题拆分为小问题，通过解决小问题来解决最初的问题（注：这种算法叫分治法，即『分而治之、各个击破』）
      • 二叉搜索树
        • 数据库中查询的时间复杂度，是我们无法使用矩阵，转而使用二叉搜索树(BST) 二叉搜索树只需 log(N) 次运算，而如果你直接使用阵列则需要 N 次运算
      • B+树索引
        • 不需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多，除了用于查找，还可以用于排序和分组
      • 哈希表
        • 当你想快速查找时，哈希表是非常有用的
      • B树
        • 每个节点都存储数据，查询不稳定
    • 数据查询的流程
      • 客户端管理器
        • 客户端管理器是处理客户端通信的。客户端可以是一个服务器或者一个最终用户或最终应用。客户端管理器通过一系列知名的API（JDBC、ODBC、...）提供不同的方式来访问数据库。客户端管理器也提供专有的数据库访问API
      • 查询管理器
        • 在这部分里，一个写的糟糕的查询可以转换成一个快速执行的代码，代码执行的结果被送到客户端管理器。
        • 查询解析器
          • 每一条SQL语句都要送到解析器来检查语法，如果你的查询有错，解析器将拒绝该查询。 在解析过程中，SQL查询被转换为内部表示（通常是一个树） 如果一切正常，内部表示被送到查询重写器
        • 查询重写器
          • 重写器的目标： 1.预优化查询 2.避免不必要的运算 3.帮助优化器找到合理的解决方案 重写器按照一系列已知的规则对查询执行检测。如果查询匹配一种模式的规则，查询就会按照这条规则来重写
        • 统计
          • 当你要求数据库收集统计信息，数据库会计算下列值： 表中行和页的数量 表中每个列中的： 唯一值 数据长度（最大，最小，平均） 数据范围（最小，最大，平均） 表的索引信息 这些统计信息会帮助优化器估计查询所需的磁盘I/O、CPU和内存使用。 这些额外的统计会帮助数据库找到更佳的查询计划 统计信息必须及时更新，统计唯一的不利之处是需要时间来计算，这就是为什么数据库大多默认情况下不自动计算统计信息
        • 查询优化器
          • 用基于成本的优化来优化查询。道理是针对每个运算设置一个成本，通过应用成本最低廉的一系列运算，来找到最佳的减低查询成本的方法 优化器真正的工作是在有限时间里找到一个好的解决方案。多数时候，优化器找到的标识最佳的方案，而是一个不错的 1.动态规划，对于都有相同的子树，所以，不必在每个计划中计算这个子树的成本，计算一次，保存结果，当再遇到这个子树时重用。 2.贪心算法，优化器面对一个非常大的查询，或者为了尽快找到答案（查询速度快不起来）。 原理是按照一个规则（或启发）以渐进的方式制定查询计划。在这个规则下，贪婪算法逐步寻找最佳算法，先处理一条JSON，接着每一步按照相同规则加一条新的JSON
        • 查询执行器
          • 计划中操作符（JOIN、SORT BY...）可以顺序或并行执行，这取决于执行器
      • 数据管理器
        • 查询管理器执行了查询，需要从表和索引获取数据，于是向数据管理器提出请求。但是有 2 个问题： 1.关系型数据库使用事务模型，所以，当其他人在同一时刻使用或修改数据时，你无法得到这部分数据。 2.数据提取是数据库中速度最慢的操作，所以数据管理器需要足够聪明地获得数据并保存在内存缓冲区内。
        • 缓存管理器
          • 数据库的主要瓶颈是磁盘 I/O。为了提高性能，现代数据库使用缓存管理器。 查询执行器不会直接从文件系统拿数据，而是向缓存管理器要。缓存管理器有一个内存缓存区，叫做缓冲池，从内存读取数据显著地提升数据库性能。 缓冲只是容量有限的内存空间，因此，为了加载新的数据，它需要移除一些数据。加载和清除缓存需要一些磁盘和网络I/O的成本。如果你有个经常执行的查询，那么每次都把查询结果加载然后清除，效率就太低了。现代数据库用缓冲区置换策略来解决这个问题。 LRU代表最近最少使用（Least Recently Used）算法，背后的原理是：在缓存里保留的数据是最近使用的，所以更有可能再次使用。
        • 事务管理器
          • 进程是如何保证每个查询在自己的事务内执行的
        • 并发控制
          • 确保隔离性、一致性和原子性的真正问题是对相同数据的写操作（增、更、删）： 1.如果所有事务只是读取数据，它们可以同时工作，不会更改另一个事务的行为 2.如果（至少）有一个事务在修改其他事务读取的数据，数据库需要找个办法对其它事务隐藏这种修改。而且，它还需要确保这个修改操作不会被另一个看不到这些数据修改的事务擦除。 这个问题叫并发控制。 最简单的解决办法是依次执行每个事务（即顺序执行），但这样就完全没有伸缩性了，在一个多处理器/多核服务器上只有一个核心在工作，效率很低。
        • 锁管理器
          • 为了解决并发控制，多数数据库使用锁或数据版本控制。 锁管理器是添加和释放锁的进程，在内部用一个哈希表保存锁信息（关键字是被锁的数据），并且了解每一块数据是： 1.被哪个事务加的锁 2.哪个事务在等待数据解锁
          • 悲观锁
            • 悲观锁就是在读取数据的时候，先对自己读取的数据加锁，只有把数据读完了才允许别人读取修改数据 原理是： 1.如果一个事务需要一条数据,它就把数据锁住 2.如果另一个事务也需要这条数据, 它就必须要等第一个事务释放这条数据 这个锁叫排他锁。 但是对一个仅仅读取数据的事务使用排他锁非常昂贵，因为这会迫使其它只需要读取相同数据的事务等待。因此就有了另一种锁，共享锁。 共享锁 1.如果一个事务只需要读取数据A, 它会给数据A加上『共享锁』并读取 2.如果第二个事务也需要仅仅读取数据A, 它会给数据A加上『共享锁』并读取 3.如果第三个事务需要修改数据A, 它会给数据A加上『排他锁』，但是必须等待另外两个事务释放它们的共享锁。 4.同样的，如果一块数据被加上排他锁，一个只需要读取该数据的事务必须等待排他锁释放才能给该数据加上共享锁。
          • 乐观锁
            • 乐观锁认为用户读取数据的时候，别人不会去写自己所读的数据，不会对数据加锁，在进行数据更新提交时才会对数据进行是否冲突的检测
          • 死锁
            • 事务A 给 数据1 加上排他锁并且等待获取数据2 事务B 给 数据2 加上排他锁并且等待获取数据1 这叫死锁。 在死锁发生时，锁管理器要选择取消（回滚）一个事务，以便消除死锁。这可是个艰难的决定： 1.杀死数据修改量最少的事务（这样能减少回滚的成本）？ 2.杀死持续时间最短的事务，因为其它事务的用户等的时间更长？ 3.杀死能用更少时间结束的事务（避免可能的资源饥荒）？ 4.一旦发生回滚，有多少事务会受到回滚的影响？ 在作出选择之前，锁管理器需要检查是否有死锁存在。 出现循环就说明有死锁，由于检查循环是昂贵的，经常会通过简单的途径解决： 使用超时设定。如果一个锁在超时时间内没有加上，那事务就进入死锁状态。
          • 两段锁
            • 两段锁协议（Two-Phase Locking Protocol，由 DB2 和 SQL Server使用），在这里，事务分为两个阶段： 成长阶段：事务可以获得锁，但不能释放锁。 收缩阶段：事务可以释放锁（对于已经处理完而且不会再次处理的数据），但不能获得新锁。 所有独占锁必须在事务结束时释放
          • 数据版本控制
            • 版本控制是这样的： 每个事务可以在相同时刻修改相同的数据 每个事务有自己的数据拷贝（或者叫版本） 如果2个事务修改相同的数据，只接受一个修改，另一个将被拒绝，相关的事务回滚（或重新运行） 这将提高性能，因为： 读事务不会阻塞写事务 写事务不会阻塞读 没有『臃肿缓慢』的锁管理器带来的额外开销 除了两个事务写相同数据的时候，数据版本控制各个方面都比锁表现得更好。只不过，你很快就会发现磁盘空间消耗巨大。 数据版本控制和锁机制是两种不同的见解：乐观锁和悲观锁。两者各有利弊，完全取决于使用场景（读多还是写多）。
        • 日志管理器
          • 为了提升性能，数据库把数据保存在内存缓冲区内。但如果当事务提交时服务器崩溃，崩溃时还在内存里的数据会丢失，这破坏了事务的持久性。你可以把所有数据都写在磁盘上，但是如果服务器崩溃，最终数据可能只有部分写入磁盘，这破坏了事务的原子性。 事务作出的任何修改必须是或者撤销，或者完成。 有2个办法解决这个问题 1.影子副本/页：每个事务创建自己的数据库副本（或部分数据库的副本），并基于这个副本来工作。一旦出错，这个副本就被移除；一旦成功，数据库立即使用文件系统的一个把戏，把副本替换到数据中，然后删掉『旧』数据。 2.事务日志：事务日志是一个存储空间，在每次写盘之前，数据库在事务日志中写入一些信息，这样当事务崩溃或回滚，数据库知道如何移除或完成尚未完成的事务。 影子副本/页在运行较多事务的大型数据库时制造大量磁盘开销，所以现在数据库使用事务日志 日志管理器处于缓存管理器（cache manager）和数据访问管理器（data access manager，负责把数据写入磁盘）之间，每个 update / delete / create / commit / rollback 操作在写入磁盘之前先写入事务日志。这个过程并不简单，原因在于如何找到写日志的同时保持良好的性能的方法，如果事务日志写得太慢，整体都会慢下来。
          • WAL（预写式日志）
            • WAL协议有 3 个规则： 每个对数据库的修改都产生一条日志记录，在数据写入磁盘之前日志记录必须写入事务日志。 日志记录必须按顺序写入；记录 A 发生在记录 B 之前，则 A 必须写在 B 之前。 当一个事务提交时，在事务成功之前，提交顺序必须写入到事务日志。
          • ARIES
            • 这个技术要达到一个双重目标： 写日志的同时保持良好性能 快速和可靠的数据恢复 有多个原因让数据库不得不回滚事务： 因为用户取消 因为服务器或网络故障 因为事务破坏了数据库完整性（比如一个列有唯一性约束而事务添加了重复值） 因为死锁
          • 日志
            • 事务的每一个操作（增删改）产生一条日志
          • 日志缓冲区
            • 为了防止写日志成为主要的瓶颈，数据库使用了日志缓冲区 当查询执行器要求做一次修改： 1.缓存管理器将修改存入自己的缓冲区； 2.日志管理器将相关的日志存入自己的缓冲区； 3.到了这一步，查询执行器认为操作完成了（因此可以请求做另一次修改）； 4.接着（不久以后）日志管理器把日志写入事务日志，什么时候写日志由某算法来决定。 5.接着（不久以后）缓存管理器把修改写入磁盘，什么时候写盘由某算法来决定。
          • 恢复
            • ARIES从崩溃中恢复有三个阶段： 1) 分析阶段：恢复进程读取全部事务日志，来重建崩溃过程中所发生事情的时间线，决定哪个事务要回滚（所有未提交的事务都要回滚）、崩溃时哪些数据需要写盘。 2) Redo阶段：这一关从分析中选中的一条日志记录开始，使用 REDO 来将数据库恢复到崩溃之前的状态。 3) Undo阶段：这一阶段回滚所有崩溃时未完成的事务。回滚从每个事务的最后一条日志开始，并且按照时间倒序处理UNDO日志（使用日志记录的PrevLSN）。 对于哪些需要 REDO 哪些需要 UNDO 的信息在 2 个内存表中： 事务表（保存当前所有事务的状态） 脏页表（保存哪些数据需要写入磁盘） 当新的事务产生时，这两个表由缓存管理器和事务管理器更新。因为是在内存中，当数据库崩溃时它们也被破坏掉了。 分析阶段的任务就是在崩溃之后，用事务日志中的信息重建上述的两个表。 为了加快分析阶段，ARIES提出了一个概念：检查点（check point），就是不时地把事务表和脏页表的内容，还有此时最后一条LSN写入磁盘。
  • 数据库设计理论
    • 函数依赖
      • 通过函数依赖关系，来帮助你确定表中的合理主外健等； 记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。 对于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。 对于 A->B，B->C，则 A->C 是一个传递函数依赖。
    • 异常
      • 不符合范式的关系，会产生很多异常，主要有以下四种异常: 冗余数据: 例如 学生-2 出现了两次。 修改异常: 修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。 删除异常: 删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。 插入异常: 例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入
    • 范式
      • 范式理论是为了解决以上提到四种异常 数据库设计三大范式 高级别范式依赖于低级别的范式，1NF是最低级别的范式 第一范式，字段不可分；确保每列的原子性，每列都是不可再分的最小数据单元 第二范式，一个表只说明一个事物 第三范式，每列都与主键有直接关系，不存在传递依赖。比如一个学生表中除了这个学生属于那个班级的信息外，不能有班级的其他信息了
  • 数据库设计流程
    • 需求分析阶段（常用自顶向下）
    • 概念结构设计阶段（常用自低向下）
    • 逻辑结构设计阶段（E-R图）
    • 物理设计阶段
    • 数据库实施阶段
    • 数据库运行和维护阶段
  • 数据库核心知识点
    • 事务ACID
      • 事务是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元。 事务的ACID特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系： 1.只有满足一致性，事务的执行结果才是正确的 2.在无并发的情况下，事务串行执行，隔离性一定能够满足，此时只要能满足原子性，就一定能满足一致性 3.在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性 4.事务满足持久化是为了能应对数据库崩溃的情况
      • A原子性
        • 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚 原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
      • C一致性
        • 数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的 一致性，一般由代码层面保证
      • I 隔离性
        • 一个事务所做的修改在最终提交以前，对其他事务是不可见的 由MVCC机制保证
      • D 持久性
        • 一旦事务提交，则其所做的修改将会永远保存到数据库中，即使系统发生崩溃，事务执行的结果也不能丢失。 可以使用数据库备份和恢复来实现，在系统发生崩溃时，使用备份的数据库进行数据恢复 由 内存 + redo log来保证，mysql修改数据的同时在内存和redo log中记录这次操作，事务提交的时候通过 redo log 刷盘，宕机的时候可以从redo log中恢复
    • 并发一致性问题
    • 封锁
      • Mysql中有哪几种锁？ 1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低 2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高 3.页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
      • 封锁粒度
      • 封锁类型
      • 封锁协议
    • 隔离级别
      • MySQL InnoDB存储引擎的默认支持的隔离级别是可重复读 ① 所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。 也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。 ② 不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。 也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。 ③ 幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。 也就是说，当前事务读第一次取到的数据比后来读取到数据条目少。 不可重复读和幻读比较： 两者有些相似，但是前者针对的是update或delete，后者针对的insert。
      • 读未提交
        • 最低的隔离级别，允许读取其他事务尚未提交的数据变更，可能会导致脏读、幻读或不可重复读 当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下 update account set money=money+100 where name=’B’; (此时A通知B) update account set money=money - 100 where name=’A’; 当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。
      • 读提交
        • 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生 不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。 　　例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。 　　不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。 　　在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……
      • 可重复读
        • 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
      • 可串行化
        • 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读
      • 举例说明
        • 我们希望的情况是：两个事务不因该相互干扰  若隔离级别是“读未提交”， 则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2。 若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以， V3的值也是2。 若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2。
    • 多版本并发控制
  • SQL语言
    • 语法基础
      • 基础
      • 创建表
        • CREATE TABLE mytable ( id INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL DEFAULT 1, col2 VARCHAR(45) NULL, col3 DATE NULL, PRIMARY KEY (`id`));
      • 修改表
        • 添加列
          • ALTER TABLE mytable ADD col CHAR(20);
        • 删除列
          • ALTER TABLE mytable DROP COLUMN col;
        • 删除表
          • DROP TABLE mytable;
      • 插入
        • 普通插入
          • INSERT INTO mytable(col1, col2) VALUES(val1, val2);
        • 插入检索出来的数据
          • INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2;
        • 将一个表的内容插入到一个新表
          • CREATE TABLE newtable AS SELECT * FROM mytable;
      • 更新
        • UPDATE mytable SET col = val WHERE id = 1;
      • 删除
        • 使用更新和删除操作时 一定要用 WHERE 子句
          • DELETE FROM mytable WHERE id = 1;
      • 查询
        • DISTINCT
        • LIMIT
      • 排序
        • ASC
        • DESC
      • 过滤
        • 不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。 应该注意到，NULL 与 0、空字符串都不同。 AND 和 OR 用于连接多个过滤条件。 优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。 IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。 NOT 操作符用于否定一个条件。
        • = 等于
        • < 小于
        • > 大于
        • <> != 不等于
        • <= !> 小于等于
        • >= !< 大于等于
        • BETWEEN 在两个值之间
        • IS NULL 为 NULL 值
        • in/not in
          • 在使用not in 的时候，如果查询的结果中有null的话，返回的结果会是null
      • 通配符
        • 使用 Like 来进行通配符匹配
        • %
          • 匹配 >=0 个任意字符
        • _
          • 匹配 ==1 个任意字符
        • [ ]
          • 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
      • 计算字段
        • *
        • CONCAT()
      • 函数
        • 汇总
          • 函 数 说 明 AVG() 返回某列的平均值 AVG() 会忽略 NULL 行。 使用 DISTINCT 可以让汇总函数值汇总不同的值。 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值之和 having row_count() 返回上一句sql影响的行数 要在同一个session中才会有正确的结果，navicat中执行会错误 found_rows() 获取上一句查询结果集的记录数 least(value1,value2)返回最小的参数 greatest(value1,value2) 返回最大的参数
        • 文本处理
          • 函数 说明 LEFT() 从左边截取指定长度的字符 select left("hello world",7); RIGHT() 右边的字符 和上一个相反 LOWER() 转换为小写字符 UPPER() 转换为大写字符 LTRIM() 去除左边的空格 RTRIM() 去除右边的空格 LENGTH() 字符串的长度 select LENGTH("test1") SUBSTRING_INDEX(blog_url,'/',-1) SUBSTRING_INDEX(str,delim,count)，是一个通过特定标识符"delim"来截取子串的函数，我们日常使用频率是很高的； delim：通过该标识符来进行截取的，delim可以为任意字符，不要为空； count：代表第几次出现；count为正数，代表从左边取标识符出现第count次之前的子串；负数则相反，从右边取标识符出现第count次之后的子串。【'左边’代表‘前’， '右边’代表‘后’】 SOUNDEX() 转换为语音值 其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。 SELECT * FROM mytable WHERE SOUNDEX(col1) = SOUNDEX('apple') concat() 返回连接参数后产生的字符串，如果含有null，结果会返回null select concat("aaa","bbb"); concat_ws() 类似concat,但是第一个参数是分隔符，如果含有null，结果会返回null select CONCAT_WS(",","aaa","bbb"); space(N) 返回空格组成的字符串 select SPACE(6) strcmp(expr1,expr2) ;如果expr1>expr2 返回1 expr1<expr2返回 -1 等于返回0 select STRCMP("txt1","txt1") replace(str,str1,str2) 将str中的str1 替换成str2 reverse(str) 反转字符串 format(x,d) 对字段进行格式化显示 find_in_set(str,strlist)查询str,在strlist中的位置 instr(str,substr)返回substr在str中所在的位置 locate(substr,str,[pos]) 返回substr在str中所在的位置，如果没有返回0，pos表示开始查找的位置
        • 日期和时间处理
          • 日期格式: YYYY-MM-DD 时间格式: HH:MM:SS 函 数 说 明 AddDate() 增加一个日期(天、周等) AddTime() 增加一个时间(时、分等) CurDate() 返回当前日期 CurTime() 返回当前时间 Date() 返回日期时间的日期部分 DateDiff(date1,date2) 计算两个日期之差 select DATEDIFF("2021-8-3","1997-7-21")----天数 Date_Add() 高度灵活的日期运算函数 Date_Format() 返回一个格式化的日期或时间串 select DATE_FORMAT(now(),'%m-%d-%Y %h-%i-%s-%p') get_format()取时间格式 select GET_FORMAT(datetime,'USA') Day() 返回一个日期的天数部分 DayOfWeek() 对于一个日期，返回对应的星期几 Hour() 返回一个时间的小时部分 Minute() 返回一个时间的分钟部分 Month() 返回一个日期的月份部分 Now() 返回当前日期和时间 Second() 返回一个时间的秒部分 Time() 返回一个日期时间的时间部分 Year() 返回一个日期的年份部分 to_days（date） 给定一个日期返回一个天数（从年份0开始的） select TO_DAYS("1997-7-21") last_day(date) 返回该月最后一天对应的值 select LAST_DAY("1997-7-21") date_sub(date,INTERVAL expr type）冲指定时间中减去 select date_sub("1997-7-21",INTERVAL 2 day) date_add() 指定的日期加上 timestampdiff(interval,datetime_expr1,datetime_expr2) 返回两个日期之间的整数差 select TIMESTAMPDIFF(MONTH,"1997-6-21","1997-10-21") UNIX_TIMESTAMP(now()) 将时间转化为时间戳 select UNIX_TIMESTAMP(now())
        • 数值处理
          • 函数 说明 SIN() 正弦 COS() 余弦 TAN() 正切 ABS() 绝对值 SQRT() 平方根 MOD() 余数 EXP() 指数 PI() 圆周率 RAND() 随机数 round(x,d) 四舍五入返回参数最近的数,d表示保留几位小数
      • 分组
      • 子查询
      • 连接
        • 内连接
          • 内连接又称等值连接，使用 INNER JOIN 关键字。 SELECT A.value, B.value FROM tablea AS A INNER JOIN tableb AS B ON A.key = B.key; 可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。 SELECT A.value, B.value FROM tablea AS A, tableb AS B WHERE A.key = B.key;
        • 自连接
          • 自连接可以看成内连接的一种，只是连接的表是自身而已。 一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。 子查询版本： SELECT name FROM employee WHERE department = ( SELECT department FROM employee WHERE name = "Jim"); 自连接版本： SELECT e1.name FROM employee AS e1 INNER JOIN employee AS e2 ON e1.department = e2.department AND e2.name = "Jim";
        • 自然连接
          • 自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。 内连接和自然连接的区别: 内连接提供连接的列，而自然连接自动连接所有同名列。
        • 外连接
          • 外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。 a left join b a 是左表，b是右表 检索所有顾客的订单信息，包括还没有订单信息的顾客。 SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id; customers 表: cust_id cust_name 1 a 2 b 3 c orders 表: order_id cust_id 1 1 2 1 3 3 4 3 结果: cust_id cust_name order_id 1 a 1 1 a 2 3 c 3 3 c 4 2 b Null
      • 组合查询
      • 视图
      • 存储过程
      • 游标
      • 触发器
      • 事务管理
      • 字符集
      • 权限管理
• 常见数据库
  • SQL DB
    • MySQL
      • 索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现
    • PostgreSQL
  • NOSQL DB
    • Redis
    • MongoDB
    • ElasticSearch
• 分布式DB核心
  • CRUD
  • 索引
  • 事务
    • ACID
  • 复制
    • 选举机制
    • 一致性算法
  • 分片
    • 分片存储
    • 分片算法和调度
  • 存储
    • 数据结构
    • 存储引擎
• 数据库产品生态
  • Drivers-开发语言
  • Tools
    • Connector
    • IDE tools plugin
    • Shell Tool
    • 管理工具
  • product
    • Standalone
      • 社区版本
      • 商业版本
    • Cloud
  • 文化
    • 社区
    • 培训
• 常问面试题
  • 数据库设计三大范式 事务 事务的四大特性 数据库的隔离级别 Mysql常用引擎 SQL优化 索引 索引使用的准则 大表如何优化 mysql有哪几种锁？ myisam和innodb的区别 悲观锁、乐观锁 主从复制的原理 数据库存储引擎 InnoDB MyISAM 数据库引擎有哪些 常见索引的原则 什么是视图 什么是内联接、左外联接、右外联接 并发事务带来哪些问题 事务隔离级别有哪些？Mysql的默认隔离级别 水平分区 分库分表之后，id主键如何处理 存储过程 存储过程优化思路 触发器 数据库并发策略 Mysql有哪几种锁 Mysql中有哪些不同的表格 char和varchar的区别 like声明中的%和_是什么意思 什么是通用sql函数 如何显示前50行 mysql支持事务吗 可以使用多少列创建索引 锁的优化策略 索引的底层实现原理和优化 SQL注入漏洞产生的原因？如何防止 NULL是什么意思？ 主键、外键和索引的区别 SQL优化有哪些方法