1- SQL语句散碎知识点
- 主键：一列（一组列），其值能够唯一区分表中每一行。
- DISTINCT:只返回唯一的行；（用于所有行，而不仅仅是前置行）；
- NULL：与字段包含0，空字符串，空格不同。检查某列是否为空：IS NULL；
- AND在计算次序中优先级更高。
- IN用来指定条件范围，范围内每一个条件都能匹配，与OR 类似。优点如下：
语法更清楚，更直观，更简洁。事计算次序更容易管理。执行更快。可以包含其他SECLECT语句，动态创建WHERE子句。 - NOT可以对IN，BETWEEN,EXISTS取反。
- BINARY：加在搜索模式前表示区分大小写。
- LIKE：指示后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。（通配符：用来匹配一部分的特殊字符）。“%”：任意字符出现任意次数（不匹配NULL），“_”：匹配单个字符
- REGEXP：指示后跟的搜索模式用正则表达式匹配。（转义用\）
注意：LIKE和REGEXP的重要区别在于：LIKE匹配整个列，而REGEXP在列值内匹配。而REGEXP的通配符更加丰富。 - Concat()函数：用于拼接多个列；
- Trim,RTrim,LTrim:用于去掉空格；
注意：函数没有SQL可移植性强； - 聚集函数：运行在行组上，计算和返回单个值的函数：AVG,COUNT,MAX,MIN,SUM等。
- GROUP BY：指示分组数据，然后对每个组而不是整个结果进行聚集。（WHERE之后，GROUP之前）。
- HAVING：过滤分组数据，支持所有Where 操作符。（Where过滤行，或者说Where在分组前过滤，HAVING在分组后过滤）
- 查询顺序：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT。
- INSERT INTO SELECT：用于插入被检索出的数据。
- 删除数据：DELETE，删除符合条件的行，而不是表本身，TRUNCATE删除表，然后重新建一个（所以清空表应该使用TRUNCATE TABLE）
2- 内外连接
- 外键：某个表中的一列，关联到了另外一个表中的主键值。定义了两个表的关系。
- 内部联结（等值联结，自然联结）：FROM，表名，INNER JOIN，表名，ON。
列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值（自联结：自己联结自己） - 外部联结：包含了在相关表中没有关联行的行，FROM ，表名 （LEFT,RIGHT,FULL(MySQL不支持全连接)）OUTER JOIN,表名，ON.
注意：左连接以左表为基准进行查询,左表数据会全部显示出来,右表如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为NULL;右连接刚好相反。全连接就是先以左表进行左外连接，然后以右表进行右外连接。
注意：应该总是提供联结条件，否则将返回不正确的数据。
3- 视图
- 从数据库中的基本表中选取出来的逻辑窗口，虚表，本身并不存在。将表与表之间的复杂操作和搜索条件对用户不可见，用户只需要对视图进行查询即可。但是不能提高查询效率。
优点：重用SQL，简化数据库查询，提高数据库的安全性和逻辑独立性。
4- 存储过程
- 一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
- 优点：增加SQL语言的功能，灵活性和安全性。执行速度快，减少网络传输。缺点在于可移植性差。
- 和函数区别：存储过程是独立的部分，而函数作为查询语句的一部分，嵌入在SQL中，执行速度更快。
- 游标：用于定位结果集的行，一种能够从包含多条数据记录的结果集中每次提取一条记录的机制。
5- 触发器
- 一种特殊的存储过程，由事件触发（insert,delete,update）。分为事前触发和事后触发。而语句级触发可以在语句执行前或者后执行，行级触发发生在触发器所影响的每一行触发一次。
- 和存储过程区别：触发器隐时调用，不能接受参数输入。
6- 范式
- 为建立冗余较小，结构合理的数据库时必须遵守的一定规则。
- 1NF: 所有字段值都是不可分解的原子值,同一列中不能有重复值。（地址）
- 2NF: 确保数据库表中的每一列都和主键相关，而不能只与主键的一部分相关。（学分）
- 3NF：数据表中的每一列数据都和主键直接相关，而不能间接相关。（订单表客户ID）
- BCNF：一个关系达到3NF，且候选码都是单属性，即主属性之间不存在相互依赖。
- 4NF：表中不存在多对多关系。（职工）
7- 索引
- 为了提高表的搜索效率而对某些字段中的值建立的目录 。主要有：唯一索引、主键索引、单列索引、多列索引。
- 聚集索引的顺序就是数据的物理存储顺序，而非聚集索引顺序与数据的物理排列顺序无关。一个表最多只能有一个聚集索引。其对于那些经常要搜索范围值的列特别有效。聚集索引不是一种单独的索引类型，而是一种存储数据方式。其具体细节依赖于实现方式。
- 多列索引是指建立一个针对多个列的索引，单列索引是指分别为每个列单独建立的索引；当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。 多列索引有最左前缀匹配的特点。
- Hash索引：检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。但是有如下缺点：
a. 只能用于等值过滤，不能用基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
b. 不支持联合索引的最优前缀，联合索引中的字段要么全用要么全不用。
c. 不支持索引排序，索引值和计算出来的hash值大小并不一定一致。
d. 遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 - MySQL索引结构为B+树实现。MyISAM表的索引和数据是分开的，用指针指向数据的物理地址，而InnoDB表中索引和数据是储存在一起。
注意：索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。 - 在什么情况下适合建立索引
为经常出现在关键字order by、group by、distinct后面的字段， 建立索引。在union等集合操作的结果集字段上， 建立索引。 其建立索引的目的同上。
为经常用作查询选择的字段， 建立索引。在经常用作表连接的属性上， 建立索引。
考虑使用索引覆盖。 对数据很少被更新的表， 如果用户经常只查询其中的几个字段， 可以考虑在这几个字段上建立索引， 从而将表的扫描改变为索引的扫描。
MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。 - 为什么要用B+树结构
- 文件很大，不可能全部存储在内存中，故要存储到磁盘上。
- 索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
- 局部性原理与磁盘预读，预读的长度一般为页的整倍数，(在许多操作系统中，页得大小通常为4k)
- 数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。
- 数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树需要遍历整棵树，效率太低。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。
8- B树，B+树，红黑树
- B树，即多路平衡查找树，节点存放的是键-值对，查找给定关键字的方法是，首先把根结点取来，在根结点所包含的关键字K1,…,Kn查找给定的关键字（可用顺序查找或二分查找法），若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查找的关键字在Ki与Ki+1之间，Pi为指向子树根节点的指针，此时取指针Pi所指的结点继续查找，直至找到，或指针Pi为空时查找失败。支持对保存在磁盘或者网络上的符号表进行外部查找。B树是用于存储海量数据的，一般其一个结点就占用磁盘一个块的大小。
- 插入操作：m-阶B树规定的结点的最大容量是m-1个元素，故当插入操作造成超出容量之后也得分裂，其分裂成两个结点每个结点分m/2个元素。副作用是在其父结点中要插入一个中间元素，用于分隔这两结点。再向父结点插入一个元素也可能会造成父结点的分裂，逐级向上操作，直到不再造成分裂为止。
- 删除操作：首先查找B树中需删除的元素,如果该元素在B树中存在，则将该元素在其结点中进行删除，如果删除该元素后，首先判断该元素是否有左右孩子结点，如果有，则上移孩子结点中的某相近元素(“左孩子最右边的节点”或“右孩子最左边的节点”)到父节点中。然后判断移动之后的情况；移动相应元素之后，如果某结点中元素数目（即关键字数）小于ceil(m/2)-1，则需要看其某相邻兄弟结点是否丰满（结点中元素个数大于ceil(m/2)-1），如果丰满，则向父节点借一个元素来满足条件；如果其相邻兄弟都刚脱贫，即借了之后其结点数目小于ceil(m/2)-1，则该结点与其相邻的某一兄弟结点进行“合并”成一个结点，以此来满足条件。
- B+树是应文件系统所需而出的一种B-树的变型树。差异在于：
- 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。(而B 树的叶子节点并没有包括全部需要查找的信息)
- 所有的非终端结点可以看成是索引部分，B 树的非终节点也包含需要查找的有效信息。
- 红黑树（平衡二叉查找树）：O(log n)时间内做查找，插入和删除。
性质1. 节点是红色或黑色。
性质2. 根是黑色。
性质3. 所有叶子都是黑色（叶子是null节点）。
性质4. 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)
性质5. 从任一节点到其每个叶子的所有简单路径 都包含相同数目的黑色节点。
注：红黑树只要求部分地达到平衡要求，降低了对旋转的要求，从而提高了性能。红黑树能够以O(log2 n) 的时间复杂度进行搜索、插入、删除操作，任何不平衡都会在三次旋转之内解决。算法时间复杂度和AVL相同，但统计性能比AVL树更高。
9- MySQL数据库引擎比较
- MyISAM和InnoDB引擎的区别
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单， 所以在效率上要优于InnoDB，小型应用可以考虑使MyISAM。
MyISAM表是保存成文件的形式， 在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。 - 应用场景
MyISAM管理非事务表。 它提供高速存储和检索， 以及全文搜索能力。 如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序， 具有众多特性， 包括ACID事务支持。 如果应用中需要执行大量的INSERT或UPDATE操
10- 事务
- 原子性: 事务是一个不可分割的整体，要么全执行，要不全不执行。
一致性: 执行之前和之后，数据库数据必须保持状态的一致性。如转账前后总金额一致。
隔离性: 使事务操作彼此独立的和透明的。一个事务内部的操作不能为其他事务看到。
持久性: 保证对事务的修改是永久性的，通过数据库备份和恢复保证。 - 事务隔离级别：
- 未提交读：查询不会受到任何增删改操作的影响，允许其他事务看到没有提交的数据。不能保证事务一致性，会出现脏读、不可重复读和幻读。
- 提交读：一个事务只能看见已经提交事务所做的改变，会出现不可重复读和幻读。事务读取的时候获取读锁， 但是在读完之后立即释放(不需要等事务结束)， 而写锁则是事务提交之后才释放， 释放读锁之后， 就可能被其他事务修改数据。
- 重复读：确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。会出现幻读。因为这个时候其他事务不能更改所选的数据， 但是可以增加数据，MySQL默认。
- 串行化：隔离级别最高，不允许出现脏读、不可重复读和幻读。即一个事务执行结束了另一个事务才能执行。当然并发性也就最差。
- 脏读、不可重复读、幻读含义：
- 脏读：一个事务可以读物另一个事务未提交的数据。事务T1更新了一行记录的内容， 但是并没有提交所做的修改。 事务T2读取更新后的行， 然后T1执行了回滚操作， 取消了刚才所做的修改。 现在T2读取的行就无效。
- 不可重复读：在一个事务中不同时间段查询出现不同的结果，可能被更新或者删除。事务T1读取了一行记录， 紧接着T2修改了T1刚才读取的那一行记录， 然后T1又再次读取这行记录， 发现与刚才读取的结果不同。
- 幻读:在一个事务中不同时间段查询，记录数不同。与不可重复读的区别是：在幻读中，已经读取的数据不会改变，只是与以前相比，会有更多的数据满足查询条件。事务T1读取一条指定的Where子句所返回的结果集， 然后T2事务新插入一行记录， 这行记录恰好可以满足T1所使用的查询条件。 然后T1再次对表进行检索，但又看到了T2插入的数据。
11- Oracle和MySQL数据库比较
- 大体一致
- 分页查询时oracle用的伪列（rownum），mysql用的是limit
oracle对sql语句要求更为严格，而且oracle里变量较mysql更多点，有number型，有大数据类型；
oracle不能插入为空列，而mysql是可以的。
两者函数和语法有不同之处，如转日期函数oracle是to_date，而mysql是str_to_date
oracle不能设置列自动增长，而mysql是可以的，oracle可以用序列加触发器来解决自动增长问题达到与mysql一样的效果。 - 总之，Oracle格式很严格，很安全。
12- SQL优化知识
- 用PreparedStatement一般来说比Statement性能高。
- 因为搜索时间更长，不要过度使用LIKE和通配符，如果使用，不要放在搜索模式的开始处。
- 能小就用小，能用整形就不用浮点型（如IP，价格）：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。
注意：Tinyint、smallint、mediumint、int、bigint，分别需要8、16、24、32、64。 int(1) 和int(11)是一样的，唯一区别是mysql客户端显示的时候显示多少位。 - 避免用NULL，尽可能的使用 NOT NULL，count(列)是不会统计列值为null的行数。
- char适用于存储很短或固定或长度相似字符，如 MD5加密；varchar适用于当最大长度远大于平均长度并且发生更新的时候。
- 利用LIMIT 1取得唯一行。
- 尽量避免SELECT *命令从表中读取越多的数据，查询会变得更慢。
- 不要再Where子句中使用不等于操作符，进行NULL判断，对字段进行表达式操作，函数操作。导致引擎放弃使用索引。
- 为搜索字段建索引。
- 千万不要 ORDER BY RAND()
- 使用 ENUM 而不是 VARCHAR，字段的取值是有限而且固定时。
- 固定长度的表会更快。（ 不包含字段：VARCHAR，TEXT，BLOB。固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的）。
