一、SQL语法

• 数据操作语言(DML)
  INSERT INTO
  DELETE
  SELECT
  UPDATE
• 数据定义语言(DDL)
  CREATE DATABASLE
  ALTER DATABASE
  CREATE TABLE
  ALTER TABLE
  DROP TABLE
  CREATE INDEX
  DROP INDEX

二、关键字

• DISTINCT 返回唯一的值
• ORDER BY 查询结果集进行排序（默认升序 ASC、降序DESC）
• TOP number | percent/LIMIT number（限制返回数量）
• LIKE & NOT LIKE 通配符（% _ [] [!]）
• BETWEEN AND | NOT BETWEEN AND
• AS | Alias
• INNER JOIN ON（表中存在至少一个匹配时返回行）
• LEFT JOIN ON（从左表返回所有的行，即使在右表中没有匹配的行）
• RIGHT JOIN ON（从右表返回所有的行，即使在左表中没有匹配的行）
• FULL JOIN ON（左表和右表那里返回所有的行；如果左表中的行在右表中没有匹配，或者如果右表中的行在左表中没有匹配，这些行同样会列出。）
• UNION （合并两个或多个 SELECT 语句的结果集，UNION 去重；UNION ALL不去重）
• SELECT INTO（从一个表中选取数据，然后把数据插入另一个表中，常用于备份）
• FOREIGN KEY（外键）
• CHECK（限制列中的值的范围）
• AUTO INCREMENT（新记录插入表中时生成一个唯一的数字，Oracle用序列（SEQUENCE nextval）实现）
• VIEW（基于 SQL 语句的结果集的可视化的表）
• IS (NOT) NULL
• ISNULL | NVL
• FIRST(column_name)-返回指定的字段中第一个记录的值
• GROUP BY（结合合计函数，根据一个或多个列对结果集进行分组）
• HAVING（解决WHERE 关键字无法与合计函数一起使用）
• UCASE() | LCASE()
• MID(column_name,start[,length]) （用于从文本字段中提取字符）

三、高级用法 & 注意事项

图3-1 MySQL查询过程.png 图3-2 SQL-Join.png

  二叉查找树-平衡二叉树（AVL树）-m叉树（多路搜索树）。B+Tree：第一，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次，所有的叶子节点由指针连接。

图3-3 B+Tree.png

  最佳左前缀法则：带头大哥不能死、中间兄弟不能断。

• 索引列不能是表达式的一部分，也不能是函数的参数
• 使用SQL函数不会开启查询缓存，使用常量代替
• 使用LIMIT 1减少搜索
• 为常用搜索字段建索引
• 在Join表的时候使用相当类型的例，并将其索引
• 千万不要 ORDER BY RAND()
• 尽可能的使用 NOT NULL
• 把IP地址存成 UNSIGNED INT
• 存储引擎 MyISAM 和 InnoDB
• ACID（原子性-Atomicity/一致性-Consistency/隔离性-Isolation/持久性-Durability）
• 行锁/表锁
• InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁

三、MySQL优化

• MySQL存储引擎基于表
• InnoDB支持事务，行级锁和表锁，同时支持外键
• MyISAM不支持事务，只支持表锁，不支持外键

EXPLAIN 命令详解

• ID值越大优先级越高
• key实际使用的索引
• key_len索引长度
• 索引失效：范围查询、运算操作、字符串不加单引号、or前后只要一个没用到索引
• 以%开头的索引会失效，%结尾不会失效
• 覆盖索引解决模糊查询索引失效问题

select id from user where name like '%%'; //id主键索引

• 如果全表扫描比走索引更快，则会走全表扫描
• is null | is not null 可能走索引
• 尽量使用复合索引，少使用单列索引
• 单列索引只会使用辨识度最高的
• Order by可能会走filesort排序或索引排序
• 多字段排序如果一个ASC、一个DESC则索引排序会失效
• order by 排序顺序和索引顺序保持一致，否则索引排序会失效
• group by使用order by null进行索引优化
• 使用多表联查替换子表查询
• 使用union替换or
• 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

select * from user t,(select id from user order by id limit 1,10) a where t.id = a.id;

• 并发事务-产生1）丢失更新2）脏读3）不可重复读4）幻读 - 定制事务隔离级别
• 索引失效时行锁可能升级为表锁
• 间隙锁-键值在范围内，但存在空缺

where id < 10
1 2 3 5 6 8

• SQL执行顺序

select distinct s.id from T t join S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2 order by s.create_time limit 5;

图3-1 SQL执行顺序.png

mysql执行过程以及顺序

• SQL正则表达式

regexp
select * from user where name regexp '正则表达式'

• 日志分类：1）错误日志2）二进制日志（BINLOG）3）查询日志4）慢查询日志
• 主从复制-通过二进制日志文件进行复制
• 使用AOP记录访问日志
• 分页优化：1）Count-使用一张表记录日志表的总数据量2）优化分页：子查询-主键索引
• 读写分离-AOP动态切换数据源
• 继承AbstractRoutingDataSource
• 拦截service方法 前置通知 切换数据源