查询
单表查询
select 分组函数，分组后的字段 from 表名 [where 条件] [group by 分组的字段] [having 分组后的筛选] [order by 排序列表];
排序
SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC | DESC];
- ASC 表示升序，DESC 表示降序，而 ORDER BY 默认值为 ASC。
多字段排序：
常用聚合函数
| 函数名 | 描述 |
|---|---|
| COUNT() | 返回参数字段的数量，不统计为NULL的记录 |
| SUM() | 返回参数字段之和 |
| AVG() | 返回参数字段的平均值 |
| MAX() | 返回参数字段的最大值 |
| MIN() | 返回参数字段的最小值 |
| GROUP_CONCAT() | 返回符合条件的参数字段值的连接字符串 |
| JSON_ARRAYAGG() | 将符合条件的参数字段值作为单个JSON数组返回，MySQL5.7.22新增 |
| JSON_OBJECTAGG() | 将符合条件的参数字段值作为单个JSON对象返回，MySQL5.7.22新增 |
分组统计
SELECT 字段名 FROM 表名 [WHERE 条件表达式] GROUP BY 字段名 [with rollup];
- with rollup 作用是求和
（ select prices,count(name) from goods group by prices with rollup; 的作用是对价格分组，查询分组后的价格 和 每种价格的商品名个数，还有总和）
统计筛选
当对查询的数据执行分组操作时，可以利用 HAVING 根据条件进行数据筛选，与WHERE功能相同，但是在实际运用时两者有一定的区别。
- WHERE 操作是从数据表中获取数据，将数据从磁盘存储到内存中，而 HAVING 是对已存放到内存中的数据进行操作。
- HAVING 位于GROUP BY 子句后，而 WHERE 位于 GROUP BY 子句之前。
- HAVING 关键字后可以使用聚合函数，而 WHERE 则不可以。通常情况下，HAVING 关键字与GROUP BY 一起使用，对分组后的结果进行过滤。
查询限制
select 字段名 from 表名 ... limit [偏移,]限制数;
- 限制数 限定每次获取最多记录数量
- 偏移 表示从哪条记录开始查询
多表查询
联合查询
SELECT 字段名 FROM 表一 …… UNION SELECT 字段名 FROM 表二 ……;
- 相当于求并集
- 默认去重，可以将 UNION 换为 UNION ALL 来取消去重
- 用 IN 可以求交集
- 用 NOT IN 可以求差集
（这个例子不太好）
用 IN 求交集：
（这其实是子查询的例子）
使用 NOT IN 求差集：
连接查询
新建一张表来演示：
交叉查询
将两张表的全部记录交叉组合，通过使用集合运算符GROSS JOIN（笛卡尔积）来完成
SELECT 字段名 FROM 表1 CROSS JOIN 表2 ; -- 或者 SELECT 字段名 FROM 表1 , 表2 ;例如，A={a,b}, B={0,1,2}，则 A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)} B×A={(0, a), (0, b), (1, a), (1, b), (2, a), (2, b)}
内连接查询
内连接查询根据连接条件可以对交叉连接查询的部分结果进行筛选，仅筛选出两张表中相互匹配的记录。
SELECT 字段名 FROM 表1 [ INNER ] JOIN 表2 ON 匹配条件 ;
外连接查询
- 左外连接
左外连接会输出 左表的全部记录 和 右表满足条件的记录：
SELECT 字段名 FROM 表1 LEFT JOIN 表2 ON 匹配条件 ;
- 右外连接
左外连接会输出 右表的全部记录 和 左表满足条件的记录：
SELECT 字段名 FROM 表1 RIGHT JOIN 表2 ON 匹配条件 ;
- 全连接
MySQL不支持全连接，但可以通过 UNION 将 左连接 和 右连接 联合起来实现全连接：
子查询
也可以称为嵌套查询，是一种嵌套在其它SQL查询的Where子句中的查询
- 子查询必须包含在()内
- 子查询不可以直接应用在聚合函数中，子查询也无法使用 ORDER BY
- Ntext、text、image 数据类型不可以在子查询的选择列表中使用
- 子查询外部可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个
- 关键字 DISTINCT 不能与包含 GROUP BY 的子查询一起使用
常用操作符：
- IN 指定的集合范围内多选一
- NOT IN 不在指定的集合范围之内
- ANY 子查询返回列表中，有任意一个满足即可
- SOME 等同于ANY
- ALL 子查询返回列表的所有值都必须满足
- 用法在联合查询的例子中有出现
(查询 比小李和小明销售额高 的记录)
- 先分组统计
- 然后执行 having 条件内部的 select，得到小李和小明的销售额
- 条件是 销售额 大于 子查询得到所有销售额
- 结果就只有小张
- 使用 having 而不用 where，是因为用到了聚合函数
EXISTS 和 NOT EXISTS
- EXISTS 用于检查子查询是否至少会返回一行数据；如果该子查询至少返回了一行数据，则为 True；如果子查询没有返回数据，则为 False，某些情况下，也可以使用 IN 或者 ANY 字段来代替。
- NOT EXISTS 与 EXIST 相反
查询优化
索引是对查询性能优化最有效的手段
索引优化
索引添加
在创表的同时添加索引就不再赘述，这里讲一下如何在已建表种添加索引
alter table 表名 add 索引类型 索引名(字段名,字段名...);
create 索引类型 索引名 on 表名(字段名,字段名...);
索引类型：
- index
- unique index
- fulltext
索引覆盖
我们知道除了主键索引，其他的都是辅助索引，辅助索引检索会发生回表。
但有一种情况不会发生回表，那就是索引覆盖。
索引覆盖是指 查询的数据在索引中就取得了，即覆盖，那就不需要回表查询。
举个例子：
select id,age from person where age=16;
从上图中可以看到，我们想要查询的 id 和 age，在辅助索引中就已经全部得到了，这时候就不用回表查询了。
最左匹配原则
- 最左匹配原则是指索引以最左边的为起点，任何连续的索引都能匹配上，在联合索引中才用的上。
- 当遇到范围查询 (>、<、between、like) 就会停止匹配。
假如我们建立了一个联合索引：
CREATE INDEX INDEX_abc ON test(a,b,c);
通过以下例子理解什么是最左匹配原则：
select * from test where a=1; // 只使用索引 a select * from test where b=2; // 不使用索引 select * from test where c=3; // 不使用索引 select * from test where a=1 and b=2; // 只使用索引 a,b select * from test where a=1 and c=3; // 只是用索引 a select * from test where b=2 and a=1; // 只使用索引 a,b，mysql有查询优化器 select * from test where a=1 and b=2 and c=3; // 使用索引 a,b,c select * from test where a=1 and b>2 and c=3; // 只使用索引 a,b
索引下推
索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。在查询非聚簇索时，拿到了叶子结点的聚簇索引，然后对聚簇索引中包含的字段先做判断，直接过滤掉不满足条件的记录，从而减少回表次数。
以 user 表中的联合索引（name，age）为例：
select * from user where name='张%' and age='10'; -- 表中有四条数据 -- 1 张三 10 -- 2 张四 11 -- 3 张五 12 -- 4 老六 13
MySQL 5.6 之前没有索引下推，它的执行流程如下：
- 在非聚簇索引中根据 name='张%' 查到聚簇索引中匹配的 id
- 使用匹配的 id 进行回表查询
（索引 age 没用上）
MySQl 5.6 之后引入索引下推，它会根据 name='张%' 和 age 一起过滤数据：
索引设计原则
- 单表索引建议控制在5个以内
- 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
- 使用短索引
- 如果有order by、group by的场景，利用索引的有序性
- 索引不会包含有NULL值的列，IS NULL，IS NOT NULL无法使用索引
- 利用覆盖索引来进行查询操作，避免回表，减少select * 的使用
- 更新十分频繁、数据区分度不高的列不宜建立索引
- 强制类型转换会全表扫描
- 不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描
- union、in、or 都能够命中索引，建议使用 in
- like语句的前导模糊查询不能使用索引
表结构优化
- 对于需要经常联合查询的表，可以建立中间表以提高查询效率
- 关联操作设计的表不要太多，否则执行会很慢
- 表设计不能有太多的列，数千的列会影响性能
- 合理应用三大范式，有利于提高查询效率
三大范式
第一范式(1NF)：用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）。
第二范式(2NF)：如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。
第三范式(3NF)：如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。
SQL语句优化
- 避免 SELECT *，只查询需要的字段
- 一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引
- 尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表
- or 查询改写成 union 查询
- 查询语句尽可能简单，大语句拆小语句，减少锁时间
- 尽量避免大事务操作，提高系统并发能力