MySQL笔记

MySQL笔记

一、基础语法

1.SQL以分号;结尾，关键字不区分大小写

2.按照一列或者多列对检索出的结果进行排序

select pro_id,pro_price,pro_name
from products
order by pro_price,pro_name;  //按照字段排序，这里具有优先顺序

3.排序的升序降序问题

order by pro_price DESC;  //DESC表示降序排序
order by pro_price DESC,pro_name;  //如果用多列排序，指定某字段降序，否则默认升序

4.限制搜索结果条数

order by pro_price DESC
limit 5;  //取价格最贵的前五条记录

5.数据过滤条件where子句，同时使用order by和where子句时，where应该在order by之前，否则将产生错误

select pro_name,pro_price
from products
where pro_price=2.50;

不匹配检查,MySQL中的不等有<>和!=两种，二者都可以使用

where v_id!=1003;  //不匹配检查
where pro_price between 5 and 10;  //范围检查
where pro_price is null;  //空值检查

注意空值检查的NULL与值为0，空字符串，或者纯空格不同，是指什么也不包含

6.组合where子句

select pro_id,pro_price,pro_name
from products
where vend_id=1003 and pro_price<=10;

where vend_id=1002 or vend_id=1003;
where vend_id not in(1002,1003);  //NOT关键字相当于‘非’
//MySQL支持使用NOT对IN,BETWEEN,EXISTS的子句进行取反，这与其他DBMS有较大差别

等同于 where vend_id IN(1002,1003); //另一种匹配方法，适用于匹配对象多的时候，且IN操作符执行比OR更快

关于and 和or 的运算次序问题，优先计算and

where vend_id=1002 or vend_id=1003 and pro_price<=20;
等同于where vend_id=1002 or（ vend_id=1003 and pro_price<=20）;

7.除去检索结果中的重复值

select DISTINCT pro_price
from products;

8.使用通配符进行过滤，MySQL中有%和_两种通配符，其中%匹配0个或者多个字符，下划线_则精准匹配一个字符

select pro_name
from products
where pro_name LIKE “jet%”;  //或者”s%e”

...
where pro_name LIKE”_jet_”

关于使用通配符，（1）不要过度使用通配符，其他操作符可以实现的效果优先使用；（2）除非绝对必要，不要把通配符用于搜索模式的开始处，这样的搜索效率是最慢的

9.正则表达式
。。。

10.group by子句和having的使用。当我们需要把数据分成若干组并在这些组内进行某些计算或者聚集函数，我们可以使用group by子句进行分组操作，然后使用having子句对分组查询结果进行筛选，功能与where相似，where的全部语法可用于having，但是having可以包含聚合函数，而where不允许有

select中出现的所有列（除了聚集函数语句）需出现在group by中
select order_num,sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price)>=50
order by ordertotal

11.子句顺序：

select,from,where,group by,having,order by,limit

12.联结
join (inner join) ：只展示两边符合条件的记录
left join：左边不符合的记录也输出
right join：右边不符合的记录也输出
full join：符合的、不符合的都输出
cross join：笛卡尔积，全排列m*n个结果输出

二、底层原理（简要提纲）

InnoDB和MyISAM的区别：事务、表锁行锁、外键支持、全文索引支持、存储文件（myisam索引和数据分开存放，Innodb则在一起）、容灾恢复

1、一条SQL语句的执行流程

2、MySQL存储文件

• 文件结构（mytest是数据库名）：
  

• 表空间结构：行、页、区、段，InnoDB是以页（16kb）读写的，数据量大的时候为索引分配空间上升到区以减少随机IO提升性能。
  段的空间最大，用途最宏观：
  

• 行记录结构：
  
  额外信息部分都是逆序存放原因：指针夹在【额外】与【真实】之间，使左右移动读取距离最小。。。
  NULL列表至少1字节（存储八个列），往后类推。。。

• varchar(n)的n最大值多少？一行记录除了text,blob其余加起来不超过65535字节（额外信息字段也要减掉），所以和字符集有关
  

3. 索引

• 索引分类：
  
  

• 索引优缺点：加速查询；占空间；创建维护需要时间；

• 索引适用条件：
  适用：字段唯一；where经常查询的条件；经常用于group by和order by的，利用索引自身有序的特性；
  不适用：上面的条件不符合的；区分度不高；数据量很小；经常变动更新的字段；

• 索引优化方法：前缀索引优化、覆盖索引优化、主键自增、防止索引失效

• 索引失效：左右模糊匹配、对索引使用函数、对索引进行表达式计算、对索引隐式类型转换、联合索引非最左匹配、where子句中的or

• 名词：回表、索引下推、覆盖索引

4. 事务

问题：脏读、不可重复读、幻读
对应隔离级别：读提交、可重复读、串行化
默认隔离级别：可重复读，解决方案：

• 普通快照读：MVCC
• 更新读：加锁机制

MVCC工作的核心原理，生成数据快照的不同时期，以及不同快照之间的可见性