SQL细节与备忘
本文最后更新于 182 天前,其中的信息可能已经有所发展或是发生改变。

基础部分

去重

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关键字一起使用来完成模糊匹配

图片alt

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优化原则

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

总结进实战,大概就三点:

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

    前置知识

    查询语句执行顺序

    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相同,就可以省去排序的过程,直接从索引中拿就好

暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇