一、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不支持事务，只支持表锁，不支持外键
- 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
- SQL正则表达式
regexp
select * from user where name regexp '正则表达式'
- 日志分类：1）错误日志2）二进制日志（BINLOG）3）查询日志4）慢查询日志
- 主从复制-通过二进制日志文件进行复制
- 使用AOP记录访问日志
- 分页优化：1）Count-使用一张表记录日志表的总数据量2）优化分页：子查询-主键索引
- 读写分离-AOP动态切换数据源
- 继承AbstractRoutingDataSource
- 拦截service方法 前置通知 切换数据源
网友评论