SQL细节与备忘 – Tars Blog

基础部分

去重

distinct

select distinct university
from user_profile;
//即可查询出所有的学校，且每个学校仅出现一次

判空

IS NOT NULL

select *
from user_profile
where gender IS NOT NULL;
//即可查找出性别不为空的用户

IN

允许在 WHERE 子句中规定多个值

select *
from user_profile
where university IN ('北京邮电大学', '北京师范大学');

或是在IN中添加一个子查询

select device_id, question_id, result
from question_practice_detail
where device_id in (select device_id 
                    from user_profile
                   where university = '浙江大学');
//这个问题使用join可以更快的解决

通配符

与LINK关键字一起使用来完成模糊匹配

select * 
from user_profile
where university LIKE('%北京%');
//选出学校名字任意位置带有“北京”字样的元素

特殊筛选

WHERE关键字不能和聚合函数一起使用

select university, avg(question_cnt) as avg_question_cnt, avg(answer_cnt) as avg_answer_cnt
from user_profile
(where xxxxx...)
group by university
HAVING avg_question_cnt < 5 or avg_answer_cnt < 20;

结果合并

UNION操作符将两个及以上的SELECT语句的结果合并到一个结果集合中

select device_id, gender, age, gpa
from user_profile
where university = '山东大学'
union all       //union 默认会去重，加all则不会
select device_id, gender, age, gpa
from user_profile
where gender = 'male';

条件函数

IF和CASE语句可以作为select语句的元素或写在聚合函数中

select 
if(age>=25,'25岁及以上','25岁以下' ) AS age_cut,
count(device_id) as Number from user_profile
group by age_cut;
//********//
select device_id, gender, 
CASE
WHEN age <20 THEN '20岁以下'
WHEN age>= 20 and age<=24 THEN '20-24岁'
WHEN age>=25 THEN '25岁以上'
ELSE '其他'
END as age_cut
from user_profile;

日期函数

详见牛客网链接

字符串函数

详见菜鸟教程链接

窗口函数

详见知乎链接
或者《SQL基础教程第2版》P257

SQL优化

SQL优化原则

减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
返回更少的数据：只返回需要的字段和数据分页处理减少磁盘io及网络io
减少交互次数：批量DML操作，函数存储等减少数据连接次数
减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源

总结进实战，大概就三点：

最大化利用索引
尽量避免全表扫描
减少无效数据查询

前置知识

查询语句执行顺序

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN<join, left join, right join...>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT # 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

具体优化方法

1. 尽量避免在字段开头模糊查询
会导致数据库放弃索引而使用全表扫描，如下

SELECT * FROM table_a WHERE name LIKE '%宋%';

2. 尽量避免使用IN和NOT IN
同样会导致数据库扫描全表
优化方式：如果是连续数值可以改用between，如

SELECT * FROM table_a WHERE age
BETWEEN 2 AND 18;

如果是子查询可以使用exists代替，如

-- 不走索引
select * from table_a where table_a.id in (select id from table_b);
-- 走索引
select * from table_a where exists (select * from table_b where table_b.id = table_a.id);

3. 尽量避免使用or
同样会导致数据库不使用索引而进行全表扫描
优化：可以使用union替代

4. 尽量避免进行null值的判断
会导致数据库不使用索引而进行全表扫描
优化：可以给字段赋合理初值，判断初值即可

5. 避免在WHERE条件中等号左侧进行运算
会导致数据库进行全表扫描
优化：尽量将运算移至等号右侧

6. WHERE条件仅包含复合索引非前置列
MySQL联合索引有一个最左匹配原则，即使用联合索引时必须引用索引中最左的键，否则数据库不会使用索引。

原理：联合索引说到底还是逃不出B+树，B+树只能依据一个KEY构建，所以数据库会根据联合索引最左的键值来构建B+树，将剩余两个键的信息按序存储在叶子节点上。

7. 避免隐式类型转换
如下sql语句，索引键值类型为varchar，但给定值是数值，使用了隐式的类型转换会导致数据库放弃使用索引

SELECT * FROM table_a WHERE col_char = 12345

8. order by条件要与where中的条件一致
如果相同则order by其实不需要额外执行

-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;

原因：执行顺序是先进行where 后order by；如果order by的条件与where相同，就可以省去排序的过程，直接从索引中拿就好