SQL

DBMS分类

MySQL关系型、MongoDB文档型、Redis键值型
SQLite：轻量级，一般用于嵌入式等小型设备的本地存储，如微信在手机端的本地存储

SELECT的查询顺序

select、from、where、group by、having、order by、limit

数据过滤

WHERE、比较运算符、逻辑运算符(AND、OR、IN、NOT)、通配符

SQL函数

1. 算术函数
2. 字符串函数
3. 日期函数
4. 转换函数(类型转换)
5. 聚集函数：COUNT、MAX、MIN、SUM、AVG
   1. COUNT(xx)会忽略值为 NULL 的数据行，而 COUNT(*) 则统计所有的数据行数，不管某个字段是否为 NULL
   2. AVG、MAX、MIN、SUM会自动忽略值为 NULL 的数据行

子查询

从查询结果集中再次进行查询，这个“查询结果集”就是子查询
辅助关键词：EXISTS、IN、SOME、ANY和ALL

1. 关联子查询
   1. 子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做非关联子查询。
2. 非关联子查询
   1. 如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询。

EXISTS与IN的选择：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)   -- B<A用IN
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)  --B>A用EXISTS

可参考此链接

连接

1. 笛卡尔积：就是笛卡尔积
   1. select * from tb1, tb2;
2. 等值连接：等值连接就是用两张或多张表中都存在的列进行连接
   1. select * from tb1, tb2 where tb1.id = tb2.id
3. 非等值连接：进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询
   1. select * from tb1, tb2 where tb1.id > tb2.id
4. 外连接（左连接、右连接）：除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。两张表的外连接，会有一张是主表，另一张是从表。如果是多张表的外连接，那么第一张表是主表，即显示全部的行，而剩下的表则显示对应连接的信息
   1. 左连接，左表为主表：select * from tb1 left join tb2 on tb1.id = tb2.id
   2. 右连接，右表为主表：select * from tb1 right join tb2 on tb1.id = tb2.id
5. 自连接：对一个表进行连接操作
   1. select * from tb1 as a, tb1 as b where a.author = 'xx' and a.id < b.id 表示查询tb1中比author xx的id大的数据

注：自联结通常比子查询要快

视图

视图是虚拟表，本身不存储数据，如果想要通过视图对底层数据表的数据进行修改也会受到很多限制，通常我们是把视图用于查询，也就是对 SQL 查询的一种封装。
与临时表的区别：临时表是真实存在的数据表，不过它不用于长期存放数据，只为当前连接存在，关闭连接后，临时表就会自动释放。

优点：

1. 安全性：虚拟表是基于底层数据表的，我们在使用视图时，一般不会轻易通过视图对底层数据进行修改，即使是使用单表的视图，也会受到限制，比如计算字段，类型转换等是无法通过视图来对底层数据进行修改的，这也在一定程度上保证了数据表的数据安全性。同时，我们还可以针对不同用户开放不同的数据查询权限，比如人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。
2. 简单清晰：视图是对 SQL 查询的封装，它可以将原本复杂的 SQL 查询简化，在编写好查询之后，我们就可以直接重用它而不必要知道基本的查询细节。同时我们还可以在视图之上再嵌套视图。这样就好比我们在进行模块化编程一样，不仅结构清晰，还提升了代码的复用率。

存储过程

视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。存储过程可以说是由 SQL 语句和流控制语句构成的语句集合，它和我们之前学到的函数一样，可以接收输入参数，也可以返回输出参数给调用者，返回计算结果。

简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合，相当于批处理

事务

MySQL中completion_type参数的作用：

1. completion=0，这是默认情况。也就是说当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要我们使用 START TRANSACTION 或者 BEGIN 来开启。
2. completion=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务（隔离级别会在下一节中进行介绍）。
3. completion=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接。

MySQL中autocommit参数的作用：

1. autocommit=0 时，不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事务，都需要用 COMMIT 进行提交，让事务生效，使用 ROLLBACK 对事务进行回滚。
2. autocommit=1 时，每条 SQL 语句都会自动进行提交。不过如果你采用 START TRANSACTION 或者 BEGIN 的方式来显式地开启事务，那么这个事务只有在 COMMIT 时才会生效，在 ROLLBACK 时才会回滚。

游标

可以让我们从数据结果集中每次提取一条数据记录进行操作。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。可以说，游标是面向过程的编程方式，这与面向集合的编程方式有所不同。

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。在检索出来的行中前进或后退一行或多行，MySQL游标只能用于存储过程（和函数）

索引

在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。另外，当数据重复度大，比如高于 10% 的时候，也不需要对这个字段使用索引。

什么情况下适合创建索引？

1. 字段的数值有唯一性的限制，比如用户名
   1. 索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。
2. 频繁作为 WHERE 查询条件的字段，频繁更新的列也可以创建索引(根据实际情况，因为更新数据的同时也要更新索引)
3. 需要经常 GROUP BY 和 ORDER BY 的列
4. DISTINCT 字段创建索引

注：多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引

什么情况下不适合创建索引：

1. WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引
2. 如果表记录太少，比如少于 1000 个，那么是不需要创建索引的
3. 字段中如果有大量重复数据，也不用创建索引，比如性别字段
4. 频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率

什么情况下索引失效：

1. 索引是表达式的一部分
2. 索引是函数的参数
3. 未满足联合索引的最左前缀匹配规则
4. 遇到范围查询，其之后的索引列失效
5. 如果条件中有or, 即使条件中存在索引也不会使用索引,如果既想使用or,又想使用索引, 就给所有or条件控制的列加上索引
6. 使用like查询时, 如果以%开头,肯定是进行全表扫描；如果%在条件后面，对于主键索引, 索引失效，对于普通索引, 索引不失效
7. 如果列的类型是字符串类型, 那么一定要在条件中将数据用引号引起来,不然也会是索引失效
8. 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效
9. 如果mysql认为全表扫描比用索引块, 同样不会使用索引

多表连接优化：

1. 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率
2. 对 WHERE 条件创建索引(没有where的过滤将非常耗时)
3. 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 user_id 在 A 表和 B 表中都为 int(11) 类型

哈希索引

1. Hash 索引不能进行范围查询，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。
2. Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而 B+ 树可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3. Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。同理，我们也无法用 Hash 索引进行模糊查询，而 B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以起到优化作用。

普通索引和唯一索引

唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止检索

数据库中的存储结构：页、区、段和表空间

在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(16KB)。如果按类型划分的话，常见的有数据页（保存 B+ 树节点）、系统页、Undo 页和事务数据页等。数据页是我们最常使用的页

区（Extent）是比页大一级的存储结构，在 InnoDB 存储引擎中，一个区会分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB。

段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在 InnoDB 中是连续的 64 个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

页结构如下：

再看B+树：

在一棵 B+ 树中，每个节点都是一个页，每次新建节点的时候，就会申请一个页空间。同一层上的节点之间，通过页的结构构成一个双向的链表（页文件头中的两个指针字段）。非叶子节点，包括了多个索引行，每个索引行里存储索引键和指向下一层页面的页面指针。最后是叶子节点，它存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表，但是对记录进行查找，则可以通过页目录采用二分查找的方式来进行。

B+树的每一层的节点之间都使用双向链表来进行连接，在单个节点内部即页的内部，数据是通过单链表进行连接

因页内的单链表查找效率低，因此在页结构中还专门设计了页目录这个模块，专门给记录做一个目录，通过二分查找法的方式进行检索提升效率

MySQL缓冲池

IO较慢，加一层缓冲池来提高访问速度

默认大小为128M

回表

回表指的就是数据库根据索引找到了数据行之后，还需要通过主键再次到数据表中读取数据的情况。

锁

1. 读锁、写锁
2. 意向读锁、意向写锁
   1. 如要对一个表加写锁时，先加意向写锁，本质就是一个标志，此时当另一个事务也要加锁的时候，会先对这个标志进行判断，如果已经被其他事务加上锁了，那就等待。如果没有意向锁的话，判断此表是否有行锁，需要遍历判断，而此时仅仅需要判断一次标志即可
3. 乐观锁、悲观锁
   1. 乐观锁：CAS、MVCC

关于SQL大小写的问题

表名、表别名、字段名、字段别名等都小写；SQL 保留字、函数名、绑定变量等都大写。
SELECT name, hp_max FROM heros WHERE role_main = '战士' 此外在数据表的字段名推荐采用下划线命名，比如 role_main 这种。

DDL对数据库和表进行操作

即增删改，CREATE、DROP、ALTER

DISTINCT

DISTINCT需要放到所有列名的前面，DISTINCT 其实是对后面所有列名的组合进行去重