基础
查询：SELECT DISTINCT <Top Num> <select list> FROM <table> <join_type> JOIN <right_table> ON <join_condition> WHERE <condition> GROUP BY <list> HAVING <condition> ORDER BY <list> LIMIT <list> 插入：INSERT INTO <table> VALUES(list) 更新：UPDATE <table> SET a=b WHERE <condition> 删除：DELETE FROM <table> WHERE <condition>进阶
LIMIT #取第N到第M条记录 IN #用于子查询 BETWEEN AND #设置区间 LIKE/REGEXP #匹配通配符/正则表达式 GROUP BY HAVING #按组查询，设置条件语句 ALIAS(AS) #可以为表或列取别名 LEFT/RIGHT/FULL JOIN #左连接/右连接/全连接 OUT/INNER JOIN #内连接/外连接 UNION/UNION ALL #并集，后者不去重 INTERSECT #交集 EXCEPT #差集 SELECT INTO #查询结果赋给变量或表 CREATE TABLE #创建表 CREATE VIEW AS #创建视图 CREATE INDEX #创建索引 CREATE PROCEDURE BEGIN END #创建存储过程 CREATE TRIGGER T_name BEFORE/AFTER INSERT/UPDATE/DELETE ON MyTable FOR #创建触发器 ALTER TABLE ADD/MODIFY COLUMN/DROP #修改表:增加字段/修改字段属性/删除字段 UNIQUE #字段、索引的唯一性约束 CHECK #限制字段值的范围 TRUNCATE TABLE #删除表数据，不删表结构函数
#日期时间函数 Date() #返回日期部分 DateDiff() #计算两个日期之差 Date_Add() #高度灵活的日期运算函数 Date_Format() #返回一个格式化的日期或时间串 CurDate() #返回当前日期 CurTime() #返回当前时间 AddDate() #增加一个日期，天、周等 AddTime() #增加一个时间，天、周等 Day() #返回一个日期的天数部分 DayOfWeek() #返回一个日期对应的星期几 Hour() #返回一个时间的小时部分 Minute() #返回一个时间的分钟部分 Month() #返回一个日期的月份部分 Now() #返回当前日期和时间 Second() #返回一个时间的秒部分 Time() #返回一个日期时间的时间部分 Year() #返回一个日期的年份部分 #数值函数 Max() Min() Avg() Sum() Count() #求均值 Round(n,m) #以m位小数来对n四舍五入 Mid(ColumnName,Start,[,length]) #得到字符串的一部分 Convert(xxx,TYPE/Cast(xxx AS TYPE)) #把xxx转为TYPE类型的数据 Abs() #求绝对值 Mod() #求余，同'%' Sqrt() #求开方 Exp(n) #求e^n，同'**' Pi() #求圆周率 Rand() #返回一个随机数 Sin() #求一个角度的正弦值 Cos() #求一个角度的余弦值 Tan() #求一个角度的正切值 Format() #用来格式化数值 First(ColumnName) #返回指定字段中第一条记录 Last(ColumnName) #返回指定字段中最后一条记录 #文本函数 Length(str) #返回字符串str长度 Locate(substr,str) #返回子串substr在字符串str第一次出现的位置 LTrim(str) #移除字符串str左边的空格 RTrim(str) #移除字符串str右边的空格 Trim(str) #移除字符串str左右两边的空格 Left(str,n) #返回字符串str最左边的n个字符 Right(str,n) #返回字符串str最右边的n个字符 Soundex() #读音类似 SubString(str,pos,len)/Substr() #从pos位置开始截取str字符串中长度为的字符串 Upper(str)/Ucase(str) #小写转化为大写 Lower(str)/Lcase(str) #大写转化为小写一、MySQL架构
连接层：客户端和连接服务 服务层：核心服务功能， 包括查询解析、分析、优化、缓存、内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等。 引擎层：数据的存储和提取 存储层：将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互
客户端请求 —> 连接器（验证用户身份，给予权限） —> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —> 分析器（对SQL进行词法分析和语法分析操作） —> 优化器（主要对执行的sql优化选择最优的执行方案方法） —> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
第一范式(1NF) 数据表的每个字段(属性)必须是唯一的、不可分割的。
第二范式(2NF) 数据表的每条记录必须是唯一的(主键约束)，且非主键字段只依赖于主键。
第三范式(3NF) 数据表中不应该存在多余的字段，也就是说每个字段都不能由其他字段推理得到。
逆范式：不按照标准的范式去设计数据库。
二、存储引擎
常用存储引擎： InnoDB、MyISAM、Memory、NDB
InnoDB引擎的4大特性：插入缓冲、二次写、自适应哈希索引、预读。
三、数据类型
整数、浮点数、字符串、日期、其他 整数：tinyint（8位二进制）、smallint（16位二进制）、mediumint（24位二进制）、int（32位二进制） 浮点数：float（单精度）double（双精度）decimal（压缩严格定点数） 日期：year (yyyy) date(yyyy-mm-dd) time(hh:mm:ss) datetime timestamp 文本：char(m) 0-255 varchar(m) 0-65535的整数 枚举：ENUM
四、索引
帮助MySQL高效获取数据的 数据结构
优势：提高数据检索效率，降低数据库IO成本；降低数据排序的成本，降低CPU的消耗。 劣势：索引也是一张表，需要占用内存；降低更新表的速度，如对表进行INSERT、UPDATE和DELETE
数据结构角度： B+树索引（常用） Hash索引 Full-Text全文索引 R-Tree索引 物理存储角度: 聚集索引（clustered index） 非聚集索引（non-clustered index），也叫辅助索引（secondary index） 聚集索引和非聚集索引都是B+树结构逻辑角度 主键索引：主键索引是一种特殊的唯一索引，不允许有空值 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。 创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。
索引结构
index是在存储引擎（storage engine）层面实现的，而不是server层面 InnoDB 存储引擎就是用 B+Tree 索引结构 主键索引与辅助索引的结构： MyISAM的索引文件和数据文件是分离的。叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。 InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录。InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。 Hash索引:目前有Memory引擎和NDB引擎支持Hash索引 full-text全文索引: MyISAM\ InnoDB 哪些情况需要创建索引
主键自动建立唯一索引 频繁作为查询条件的字段 查询中与其他表关联的字段，外键关系建立索引 单键/组合索引的选择问题，高并发下倾向创建组合索引 查询中排序的字段，排序字段通过索引访问大幅提高排序速度 查询中统计或分组字段
哪些情况不要创建索引
表记录太少 经常增删改的表 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义） 频繁更新的字段不适合创建索引（会加重IO负担） where条件里用不到的字段不创建索引
MySQL高效索引 覆盖索引(不需要回表操作)
五、MySQL查询
count() 和 count(1)和count(列名)区别 count()包括了所有的列，相当于行数，不会忽略列值为NULL count(1)包括了所有列，用1代表代码行，不会忽略列值为NULL count(列名)只包括列名那一列，会忽略列值为NULL
MySQL中 in和 exists 的区别？* 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
UNION和UNION ALL的区别? 将两个结果集合并为一个，两个要联合的SQL语句 字段个数必须一样，而且字段类型要“相容”（一致）； UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录 UNION会按照字段的顺序进行排序
三大范式
第一范式：每个列都不可以再拆分。 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
六、MySQL 事务（存储引擎层实现）
事务的隔离级别有哪些？MySQL的默认隔离级别是什么？ 什么是幻读，脏读，不可重复读呢？MySQL事务的四大特性以及实现原理。 MVCC熟悉吗，它的底层原理？ 1.Read Uncommited （未提交读） 事务可以读取未提交的数据，也称脏读。非常可怕。 2.Read Commited （提交读） 一个事务从开始到提交之前，所做的修改对其他事务是不可见的。但是重复执行同样的查询可能会导致不同的结果。 3.Repeatable Read （可重复读） 这是mysql的默认隔离级别，重复执行同样的查询结果相同。 4.Serializable （可串行化） 最高的隔离级别，它强制事务串行执行。会导致大量的超时和锁争用问题。虽然数据一致性好，但是并发能力很弱，一般也很少使用。 注： 在隔离级别是repeatable read下，select是快照读。但是如果使用的是 select for update, 就是不可重复读的，也就是说可以读取到其他事务的修改。
ACID属性 A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。 C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏 I(Isolation)隔离性：一个事务的执行不能其它事务干扰。 D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
支持事务，能崩溃恢复，行级锁，非锁定读，聚簇索引，外键 InnoDB：用于MySQL的事务安全（ACID支持）存储引擎，具有提交，回滚和崩溃恢复的功能以保护用户数据。 InnoDB行级锁定（不升级到更粗的粒度锁）和Oracle风格的非锁定读取增加了多用户并发性和性能。 InnoDB将用户数据存储在聚簇索引中，以减少基于主键的常见查询的I / O。 为了保持数据完整性，InnoDB还支持FOREIGN KEY参照完整性约束。
存储过程（属于事务） 1.创建：
create procedure <name>() begin declare var_name type *declare num int* set num=num+3; SQL语句--- end; delimitor $：更改分隔符2.调用： call <name>; 3.查看：
select name from mysql.proc where db=<database>; #查看proc 表记录所有存储过程； show procedure status; show create procedure px;4.删除：drop procedure [if exists] <name>;
传递参数
create procedure px4(in height int,out description varchar(10) ) begin if height >= 180 then set description='身材高挑'; elseif height >= 170 and height < 180 then set description='标准身材'; else set description='一般身材'; end if; end$ call px4(176)case 语法
create procedure px6(mon int) begin declare result varchar(10); case when mon>=1 and mon<=3 then set result= '第一季度'; when mon>=4 and mon<=6 then set result= '第二季度'; when mon>=7 and mon<=9 then set result= '第三季度'; else set result= '第四季度'; end; select concat('结果为',result); end$循环结构while
#从1到n累加 create procedure px7 (n int) begin declare total int default 0; declare num int default 1; while num<=n do set total = total + num; set num=num+1; end while; select total; end$repeat
begin declare total int default 0; repeat set total = total + n; set n=n-1; until n=0 end repeat; select total; end$loop leave… 游标 游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。 声明、open、fetch、clase
create procedure px8 () begin declare declare stu_result cursor for select * from student; open stu_result into ; fetch stu_result; close stu_result;; end$存储函数（有返回值） create function fun() select fun(*);
七、MySQL锁机制
数据库的乐观锁和悲观锁？MySQL 中有哪几种锁，列举一下？MySQL中InnoDB引擎的行锁是怎么实现的？MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁 锁是计算机协调多个进程或线程并发访问某一资源的机制。
锁：完全在存储引擎层实现，分：锁策略：表锁和行锁、锁粒度 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
八、MySQL调优
日常工作中你是怎么优化SQL的？SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？如何写sql能够有效的使用到复合索引？一条sql执行过长的时间，你如何优化，从哪些方面入手？什么是最左前缀原则？什么是最左匹配原则？ 慢查询日志 记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time（默认10） 值的 SQL，则会被记录到慢查询日志中。
性能优化
索引优化【全值匹配、最佳左前缀法则、不在索引列上做任何操作、存储引擎不能使用索引中范围条件右边的列、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select is null ,is not null 也无法使用索引、字符串不加单引号索引失效、少用or，用它来连接时会索引失效、<，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描
触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。old new SQL执行频次查询 show global status like ‘Com_______’; show status like ‘innodb_rows_%’\G; 定位低效的查询语句： 慢查询日志和show processlist；查看所有客户端正在操作的信息； ±—±-----±----------------±-----±--------±-----±---------±-----------------+ | Id | User | Host | db | Command | Time | State | Info | ±—±-----±----------------±-----±--------±-----±---------±-----------------+ | 14 | root | localhost:61212 | tons | Query | 0 | starting | show processlist | ±—±-----±----------------±-----±--------±-----±---------±-----------------+
安全管理 第一层网络：有保护的局域网或跳板机做端口转发的公网 第二层主机：1.系统账号都改成基于ssh key认证，不允许远程密码登入，且ssh key的算法、长度有要求以确保相对安全。2.禁止root账号远程登录主机。3.正确设置MySQL及其他数据库服务相关目录权限，不要全是755，一般750就够了。 第三层数据库：1.严格限制数据库账号权限级别。业务帐号，权限最小化，坚决不允许DROP、TRUNCATE权限。2.设置MySQL账号的密码安全策略，包括长度、复杂性。
未完待续
基础：sql语句、表结构设计 调优：索引、慢查询优化、配置参数调优 核心原理： InnoDb存储引擎 （包括隔离级别、事务、锁、缓存池、回滚日志等等） Mysqld (包括连接管理、进程管理、查询缓存、查询优化、日志等等) 架构与运维：用户与权限、安全备份与恢复、日志、分布式与高可用
