mysql-爱代码爱编程
一.基础篇
SQL语句
DQL:查询语言
查询关键字：select
基础查询
select：字段列表
from：表名列表
where：条件列表
group by：分组字段列表
having：分组后条件列表
oder by：排序字段列表
limit： 分页
条件可以是以下运算符：
比较运算符： > < <>或！= ，between...and..., in(...),link占位符 , null , is null;
逻辑运算符： AND 或 && , OR或|| ， NOT 或 ！ ;
条件查询
语法： select 字段列表 from 表名 where 条件列表;
模糊查询
_ 代表任意1个字符； % 代表任意n个字符；
select * from 表名 where 字段 like '_ _'; //查询字段值=2字符的数据
select * from 表名 where 字段 like '%x'; //查询字段值为 名字中最后一个字为x的数据 =如身份证最后一位为x的人的数据
聚合查询
语法：select 聚合函数(字段列表) from 表名 ;
聚会函数： （count，max，min，avg，sum） 将一列数据为整体纵向计算。
统计数量.最大值.最小值.平均值.求和 注：null 的值不参与计算;
举个列子： select avg(age) from 表名; //统计平均年龄
select avg(age) from 表名 where addr='上海'; //统计上海平均年龄
分组查询
语法： select 字段列表 from 表名 where 条件 group by 分组字段名 having 过滤条件;
举个列子：
select gender，count(*) from 表名 group by gender; //根据性别分组,统计男和女员工数
select gender，avg(*) from 表名 group by gender; //根据性别分组,统计男和女员工平均数
select gender，count(*) from 表名 group by gender; //根据性别分组,统计男和女员工数
别名
--别名：作用类似于替换
select 'name' AS '姓名， 'age' AS '年龄' FROM student;
排序查询
语法： select 字段列表 from 表名 oder by 字段1 排序方式1 ，字段2 排序方式2;
排序方式： ①ASC:升序(默认) ②DESC:降序
举例：select *from 表名 order by age desc; //根据年龄降序排序
分页查询
语法： select 字段列表 from 表名 limt 初始索引，查询记录数;
注：起始索引 0 开始=（查询页数-1）*每页显示记录数，若查询第一页开始到10条可简写 limt10;
select * from 表名 limit 0，10;
select * from 表名 limit 10，10;
连表查询
inner join：只取两张表有对应关系的记录
LEFT JOIN：在内连接的基础上保留左表没有对应关系的记录
RIGHT JOIN：在内连接的基础上保留右表没有对应关系的记录
子查询/合并查询
单行子查询
多行子查询:
多行子查询即返回多行记录的子查询
IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询
到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为
true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出
任何记录。
ALL 关键字：表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才
可以执行外层查询语句。
ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中
的，任意一个比较条件，就返回一个结果作为外层查询条件。
在 FROM 子句中使用子查询：子查询出现在from子句中，这种情况下将子查询当做一个临时表使
用。
DML:数据库操作语言
insert：插入（增） update: 更新（改） delete: 删除
insert into 表名 (字段1，字段2...) values(值1，值2...); //一次插入一行数据
insert into 表名 (字段1，字段2...) values(值1，值2...),()...; //一次插入多行数据
update 表名 set 字段名=值1，字段2=值2... where 条件； //修改指定行字段数据
delete from 表名 where 条件； //删除指定行数据
DDL: 数据定义语言
create： 创建表 视图 alter：修改 表字段 表属性 drop:删除表 视图
DCL:数据控制语言
grant ：授予用于权限 revoke ：收回权限
TCL:事务控制语言
事务
正则表达式
视图
一种虚拟表/逻辑表 非物理表
CREATE VIEW <视图名> AS <SELECT语句>
流程控制
IF
CASE
WHILE
LEAVE
ITERATE
LOOP
REPEAT
触发器
触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行DML操作（ insert ， delete ， update ）时就会激活它执行。
存储过程
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。
语法：CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数 据类型…]]) [特性 ...] 过程体
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。
MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN , OUT , INOUT 。 IN ：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设
置默认值
OUT ：该值可在存储过程内部被改变，并可返回
INOUT ：调用时指定，并且可被改变和返回
过程体的开始与结束使用 BEGIN 与 END 进行标识。
游标
类似于c++的迭代器指针作用。
游标是针对行操作的，对从数据库中 select 查询得到的结果集的每一行可以进行分开的独立的相
同或者不相同的操作。
对于取出多行数据集，需要针对每行操作；可以使用游标；游标常用于存储过程、函数、触发器、
事件；
游标相当于迭代器
定义游标: DECLARE cursor_name CURSOR FOR select_statement;
打开游标: OPEN cursor_name;
取游标数据: FETCH cursor_name INTO var_name[,var_name,......]
关闭游标:CLOSE curso_name;
释放:DEALLOCATE cursor_name;
权限管理
创建用户：
授权;
对视图授权：
刷新权限：
远程连接
创建表结构
外键约束
约束
函数
mysql的结构
crud
sql语句的执行过程
SQL的优化
创建索引：
create 索引类型 索引名 on 表（字段）
单值索引：
主键索引：
唯一索引：
符合索引：
组合索引：
删除索引：
drop index 索引名
查询索引：
show index from 表名;
索引的实现：
存储：
聚集索引：
辅助索引：
最左匹配原则：
覆盖索引：
索引失效：
-
select …where A and B若A或B一个不包含索引则失效
-
索引字段参与运算，则索引失效；
-
索引字段发生隐士转换
-
不等于： 会将数据全部遍历； 改 ：> <
-
LINK 模糊查询，%符开头，
-
组合索引中，没有使用第一列索引。
索引原则：
查询频率较高且数据量大的表。
索引选择使用频次较高
过滤效果好的列或组合
节点包含的信息多，减少磁盘操作；
对很长字符串，考虑前缀索引；
优化器的选择
慢日志查询
分区分表
分区功能不是由存储引擎层实现
事务
为什么有事务？
解决什么问题？
事务的组成: 由一条或多条sql语句组成
特征： 数据库在提交事务时，可确保要么修改成要么一句也不成。 mysql中 通过 set autocommit = 0 ；设置当前会话手动提交。
事务的控制语句：
start transaction | begin ---显示开启事务
commit ---提交事务，并使得数据库做持久化
bollback ---回滚事务，结束用户的事务，并撤销正在进行的所有位提交的修改
savepoint identifier ---创建一个保存点，一事务可多保存点。
release savepoint identifier ---删除一个保存点
rollback to [savepoint] identifier ---事务回滚到保存点
事务的并发的控制异常？
事务并发操作数据库导致的异常；白话就是同时操作数据库导致的读写异常，解决：加锁做同步操作后面讲。
脏读： 读到未提交的数据–原因：read uncommitted 读未加锁也没mcvv支持。 解决：添加mvcc支持
**不可重复读：**读到其他事务提交的数据，导致2次读到数据不一致–原因：read committed隔离级别下mvcc读取最新历史版本数据---- 解决：修改mvc读取定义，读取事务开始的历史版本数据
幻读： 一个事务中某次读取操作不能作为接下来的业务逻辑的依据 —原因：repeatable read 隔离级别下得去操作使用mvcc，读操作未加锁，而进行写操作 —解决：手动给读操作加锁，（s锁/x锁，通过next-key lock，锁住读取范围，避免范围内插入数据读）
ACID特性
原子性（A）
事务要么执行，要么不执行（回滚），是访问更新数据库的基本单元。通过undolog实现回滚
隔离性©
查询
删除 更新
插入
持久性(I)
redolog记录更改的物理地址
一致性(D)
通过满足原子性，持久性以及隔离性
锁
锁类型
共享锁
排他锁
意向共享锁
意向排他锁
锁得兼容性
锁的对象
行级锁是针对表的索引加锁； 索引那个包括聚集和辅助索引； 表级锁是针对页或表进行加锁；
考虑innodb在read committed和repeatable read级别下锁的情况。
MVCC
redo
undo
触发器：具备事务 外键：具备事务 但是不建议使用，
检查事务性，
缓存故障
mysql的缺点： 读取速度较慢，mysql是对磁盘进行操作，虽有自己的缓存但是读性能太低，一般项目都是读远大于写操作。 由此出现解决方案 将读写分离。 但是读写分离会出现数据不一致问题。
缓冲层： 缓存数据库可以使用redis，memcached；它们所有的数据都存储在内存中，也可将内存中数据持久化到磁盘中；内存的数据和磁盘数据是1：1的。
总结： 1由于mysql中的缓冲层不能由用户控制 2访问磁盘速度较慢 3主要解决读性能问题 4项目中需存储的数据远大于内存容量，同时需要进行数据统计分析，许哦一数据的存储分析应依据关系型数据库。
同步问题？
引入缓冲层后，对数据的获取需要分别操作缓存数据库和mysql； 那么这个时候可能存在几个状态
-
mysql有 缓存无
-
mysql无 缓存有
-
都有 但数据不一致
-
都有数据一致
-
都没有
数据的本质来源于mysql ，只要mysql正确就万事大吉。但是缓存有而mysql没有则很危险
读写分离
缓存穿透
问题：缓存和mysql都不存在，一直读取不粗不能在的数据，造成mysql崩溃
解决：1.缓存设置<key,nil> 2.部署布隆过滤器
缓存击穿
问题：缓存无，mysql存在大量并发请求，造成mysql崩溃
解决：1过热数据不过期 2加锁
缓存雪崩
数据库的横向扩展—mysql集群
mysql集群
1项目初始阶段 单节点 可靠性 单点故障 主从复制
读写性能 1读写分离2缓存方式
2 项目发展阶段
使用redis，memcached；它们所有的数据都存储在内存中，也可将内存中数据持久化到磁盘中；内存的数据和磁盘数据是1：1的。
总结： 1由于mysql中的缓冲层不能由用户控制 2访问磁盘速度较慢 3主要解决读性能问题 4项目中需存储的数据远大于内存容量，同时需要进行数据统计分析，许哦一数据的存储分析应依据关系型数据库。
同步问题？
引入缓冲层后，对数据的获取需要分别操作缓存数据库和mysql； 那么这个时候可能存在几个状态
-
mysql有 缓存无
-
mysql无 缓存有
-
都有 但数据不一致
-
都有数据一致
-
都没有
数据的本质来源于mysql ，只要mysql正确就万事大吉。但是缓存有而mysql没有则很危险
读写分离
缓存穿透
问题：缓存和mysql都不存在，一直读取不粗不能在的数据，造成mysql崩溃
解决：1.缓存设置<key,nil> 2.部署布隆过滤器
缓存击穿
问题：缓存无，mysql存在大量并发请求，造成mysql崩溃
解决：1过热数据不过期 2加锁
缓存雪崩
数据库的横向扩展—mysql集群
mysql集群
1项目初始阶段 单节点 可靠性 单点故障 主从复制
读写性能 1读写分离2缓存方式
2 项目发展阶段