数据分析进阶 - 关于SQL的知识点

前言

SQL作为实际工作中的一部分，总是需要与它打交道，但总有种一知半解的感觉，所以此博客就作为学习笔记便于后续的回顾(写博客能够让自己记得更牢- -)，也会根据工作实际遇到的场景来不断地补充…(如果有不对的地方欢迎指出)

1. in和exists的区别

in和exists用于子查询中，例如

select field1
from test1  
where field2 in (select field2 from test2 )

select field1
from test1 a
where exists (select field2 from test2 b where a.field2 = b.field2 )

两者查询的方式是不一样的：
(1) in在查询的时候会先查询子查询的表，将内表和外表做一个笛卡尔积(相当于cross join)，然后按条件进行筛选

针对上面的例子， in()只执行一次，它查出test2表中的所有field2字段并缓存起来.之后,检查
test1表的field2是否与test2表中的field2相等,如果相等则将test1表的记录加入结果集中,
直到遍历完test1表的所有记录.

(2) exists查询的时候是先查询主查询的表，遍历循环外表，若在内表能找到匹配的记录就会返回TRUE并保留该结果到结果集

针对上面的例子，,exists()会执行test1.length次,它并不缓存exists()结果集,因为
exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回
false.

两者区别在于若外部表更大则使用in效率更高，相反若子查询结果集更大则使用exists效率更高。表的规模不是看内部表和外部表，而是外部表和子查询结果集，此外也会受到两个表中索引的影响

2.字符串操作函数

concat(): 将多个字符串连接成一个字符串
select concat(a1, "+", a2) from test

concat_ws(): 使用固定连接符将多个字符串连接成一个字符串
select concat("+",  a1,  a2, a3) from test

group_concat(): 分组连接一个或多个字符串
select group_concat(a1 SEPARATOR '|')  # 可以自定义连接符
from test
group by a2

substr():  用于从字段中截取相应位置的字符
select substr(a1, 1, 3) from test   #取a1左边第1位置起，3字长的字符串

3.处理多个select语句的查询结果

前提两个查询结果字段个数必须一样且字段类型要一致，用韦恩图会很容易理解

# 并集
union: 并集且去重
union all：并集不去重
用union all比用union速度快，因为union会将多个结果中重复的数据合并，union all则是直接合并

MySQL不支持下述关键字

# 交集
intersect：返回查询结果中共有的部分记录

# 减去
minus: 返回在第一个查询结果集中，但不是第二个查询结果集中的行记录，也就是说不包含两个查询返回集合的相交部分

4.on和where的区别

(1)外连接
在使用left join左连接时，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录,on后面的条件只对右表有效），条件不为真的就全部过滤掉，on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。
(2)内连接
在使用inner join内连接时，不管是对左表还是右表进行筛选，on和where都会对生成的临时表进行过滤。
join过程可以这样理解：首先两个表做一个笛卡尔积，on后面的条件是对这个笛卡尔积做一个过滤形成一张临时表，如果没有where就直接返回结果，如果有where就对上一步的临时表再进行过滤。

5.case when的使用

(1)简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
(2)搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
(3)应用场景

行转列——>使用sum(), count()对分类数据进行统计
# 下面呈现sum()和count()在使用上的区别
select 
sum(case when sex = 1 then 1 else 0 end）as male_cnt,
count( case when sex = 0 then 1 else null end）as female_cnt
from score
# group by a

等级划分 - 对一些数值型字段划分区间
SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE

具体案例可参考：https://blog.csdn.net/rongtaoup/article/details/82183743

6.窗口函数

序号函数：row_number() / rank() / dense_rank()

row number: 根据排序生成连续的序列号，1,2,3,4,5
rank：考虑相同数据的排序情况，如1,2,2,4
dense rank: 考虑相同数据的排序情况, 如1,2,2,3

分布函数：percent_rank() / cume_dist()
前后函数：lag() / lead()
头尾函数：first_val() / last_val()
其他函数：nth_value() / nfile()

# 语法
window_function_name(expression) 
    OVER (
        [partition_defintion] # 口按照那些字段进行分组，窗口函数在不同的分组上分别执行
        [order_definition]  # 按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号
        [frame_definition]  # 通常用来作为滑动窗口使用
    )

具体案例可参考：https://www.cnblogs.com/DataArt/p/9961676.html

7.索引

(1)索引的作用

为了提高数据库查询数据的速度而增加的标志符号

索引主要建立在:
经常搜索的列；
主键所在列；
外键所在列

(2)索引的区别

MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。
SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引

聚集索引:可以理解为索引记录的顺序与表记录的顺序一致，SQL默认在依次递增的主键上建立
聚集索引，例如，id为1的数据在第一条，id为2的数据在第二条。聚集索引会按照主键的顺序
来排序。（例如，用字典找字，对于认识的字可以通过拼音排序对应正文找到页码）

非聚集索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个
非聚集索引。例如说，建立数据表登记学生考试成绩，字段包括姓名，学号与分数。假定该表
按照成绩排序、学号信息错乱，可以考虑构建非聚集索引，第一名对应1，第二名对应2……，
想要提取第10个学生的学号，查找索引10指向的数据即可。如果非要把非聚集索引类比成现实
生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

具体案例可参考：https://www.cnblogs.com/s-b-b/p/8334593.html