SQL这些点，你是否还记得呢？

数分小白龙 2021-10-23

586

什么是SQL？简单地说，SQL就是访问和处理关系数据库的计算机标准语言。也就是说，无论用什么编程语言（Java、Python、C++……）编写程序，只要涉及到操作关系数据库，比如，一个电商网站需要把用户和商品信息存入数据库，或者一个手机游戏需要把用户的道具、通关信息存入数据库，都必须通过SQL来完成。

所以，现代程序离不开关系数据库，要使用关系数据库就必须掌握SQL。

1. 熟悉数据库的服务器、架构、表格、字段

(1) 服务器：

数据库中一切数据和信息所存储的地方，可以理解为你的电脑硬盘；服务器往往有对应的物理主机和访问地址，连接数据库时主机后面所填的就是访问地址；

(2) 架构：

服务器中具体的数据库，用于存储不同用途的表格，可以理解为电脑中的文件夹；常见的架构有ods/edw/edw_s/ddm他们各自有不同的储存特点：

ods(Operational Data Store) : 操作型数据仓库；同步业务生产过程中所有数据，比edw更加详细，冗余和复杂度更高；

edw（Enterprise Data Warehouse）：企业数据库；已处理完成并进行汇总的，可供业务直接使用的企业数据库；

edw_s：安全级别更高的企业数据库；加密性更高，一般需要独立授权；存储安全级别更高的敏感或机密数据，例如用户的身份证、手机号、真实姓名等；

ddm（Distributed Database Middleware）：局部数据库；专门针对某个具体的应用或需求建设的局部数据库，只关心自己需要的数据库，不会全盘考虑企业整体的数据架构和应用，每个应用都有自己的DM，所以DM可以基于仓库建设也可以独立建设。

(3) 表格：

存在于不用架构中具体的数据表格，可以理解为文件夹中的Excel；如ddm.shop，ddm.cpc，ods.orders；

真实场景中“架构-表格”的形式存储表名更加方便跨数据库取数；

对表格使用默认统一的别名，更方便后续的代码移植（用a b c的话还需要修改select后的字段前缀）

2. 一些SQL简单知识点

(1) 运行顺序

select 查询字段 - from - join - where - group by - having - select - order by - limit

(2) in 和not in

in ('A', 'B', 'C')实现多选;
not in ()表示反选;

(3) like 配合通配符进行模糊匹配

% ：表示任意数量字符;_：表示任何字符出现一次;[]：指定一个字符集，必须匹配该位置的一个字符;^：再[]中使用，表示否定;

(4) 查找日期区间

date between '2019-12-01' and '2019-12-07' #（除了除了数字外，其他都需要加引号，日期也是）

(5) 连接join：遍历匹配所有，不断向下增添，原理就是笛卡尔积

连接类型:
  内连接 inner join;
  左连接 left join;
  右连接 right join;
  完全外部连接 full join -- mysql不支持，需要使用union进行左右连接，即可实现;

union和union all的区别:
union是行连接,去重;
union all 不去重;

on和where，它们各自的筛选条件有何不同:on是在连接前进行筛选;where是在连接完成后，再进行筛选;

(6) group by分组

select后的非聚合字段一定要与group by后指定的字段一致;
group by 可以使用1,2等数字直接引用select后的字段，why?（select不是最后才运行吗）
因为select会执行两次，第一次的时候，直接先把列名打印出来;

(7) having和where区别

having是聚合后筛选;
where是聚合前筛选;

(8) order by 和 limit

order by：在select 前运行，默认升序，降序DESC;
注意：order by字段内容为中文时，可能会出现排序混乱情况;
解决方法：order by convert(门店名称 using gbk);

limit 10 显示前n行;
limit 10,2 会显示第11和12行;
不写order by时，使用limit会可以提升运算效率，帮助Debug;

(9) CASE WHEN ... THEN... ELSE ... END

原文链接：https://blog.csdn.net/rongtaoup/article/details/82183743

# 场景1：判断考试等级
CASE WHEN SCORE = 'A' THEN '优' WHEN SCORE = 'B' THEN '良' WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END

# 判断是否为null
CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END

# 场景2：统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格
SELECT 
  SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
  SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
  SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
  SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
  THTF_STUDENTS

# 场景3：经典行转列，并配合聚合函数做统
# 现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果
SELECT 
  E_CODE,
  SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
  SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
  SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
FROM 
  THTF_ENERGY_TEST
GROUP BY
  E_CODE

# 场景4：CASE WHEN中使用子查询
#根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。
CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)

3. 窗口函数

(1) 语法：

函数名() over(partition by 用于分区的字段名 order by 用于排序的字段名)只要用窗口函数给出序号，后面要对序号进行筛选，一定要进行子查询

表面上，窗口函数是基于某个排序规则进行排序并计算，如：

row_number() over(partition by 门店名称 order by GMV desc)；

实际上，窗口函数是对准备好的表格，在任意分组内部进行排序和计算（为了区分，我们称之为分区）（也就是指定任意详细级别进行各种自定义的运算）；

(2）各种聚合运算都支持

sum(xxx) over()
avg() over()
count() over()
max() over()
min() over()

(3）关于over()

over()中分为partition by子句，order by子句，where子句；

只使用partition by子句，未指定order by，聚合为分组内的聚合；

使用partition by和order by子句，未使用window子句的情况下，默认从起点到当前行；

window子句：（求移动平均） -- 需要用到的时候在研究
  preceding 往前
  following 往后
  current row 当前行
  unbounded 起点    
    unbounded preceding 表示从分区前面的起点    
    unbounded following 表示到分区后面的终点
  
区别下面几个窗口函数的结果差别    
  sum(GMV) over(partition by 门店名称, 平台) 分区和；    
  sum(GMV) over(partition by 门店名称, 平台 order by GMV desc) 分区第1行到当前行的累计和；     
  sum(GMV) over(partition by 门店名称, 平台 order by GMV desc rows 2 preceding) 分区前2行和当前行的累计和；     
  sum(GMV) over(partition by 门店名称, 平台 order by GMV desc rows between unbounded preceding and 1 preceding) 分区第1行到前1行的累计和；     
  注：对于窗口子句Null值得判断；当前两行或者前一行找不到得时候，到底设置为null还是为0？    
  应用场景：求移动平均；

(4）根据排序分配序号

row_number()：根据分区内的排序，分配唯一且连续的，如123456；

row_number() over(partition by 门店名称, 平台 order by 日期)

dense_rank()：根据分区内的排序，分配不唯一且连续，如112234556；

dense_rank() over(partition by 门店名称, 平台 order by GMV)

rank()：根据分区内的排序，分配不唯一且不连续排名序号，如高考113446；

rank() over(partition by 门店名称, 平台 order by GMV)

(5) 根据排序取具体字段数值

first_value(字段)：根据分区内的排序，返回排在第一行的对应的字段数值（降序后就可以去最后一行对应的数值）last_value(字段)

first_value(GMV) over(partition by 门店名称, 平台 order by 日期) first_gmv
last_value(GMV) over(partition by 门店名称, 平台 order by 日期 desc) last_gmv
# 应用场景：比如算一下所有门店跟第一名的分差；

nth_value(字段, n) ：返回分区内的排序第n行的字段数值。如果第n行还未运行到，则返回NULL。n必须是正整数，例如1,2和3。

nth_value(GMV, 3) over(partition by 门店名称, 平台 order by 日期) nth_gmv_3

(6) 根据排序取上下几行的数值（计算同环比最重要的函数）

lag(字段, n, 默认值)：返回分区内，本行前n行的字段数值，如果为空则填充默认值；

,lag(gmv, 2) over(partition by 门店名称, 平台 order by 日期) lag_2a
,lag(gmv, 2, 666) over(partition by 门店名称, 平台 order by 日期) lag_2b

lead(字段, n, 默认值)：返回分区内，本行后n行的字段数值，如果为空则填充默认值；

,lead(gmv, 2) over(partition by 门店名称, 平台 order by 日期) lead_2a
,lead(gmv, 666) over(partition by 门店名称, 平台 order by 日期) lead_2b

(7) 根据排序统计分布位置

1) percent_rank()：根据分区内的排序，从0开始统计当前行所在的排序中的处于百分之多少的位置（不管指定值与分组序列中某值是否重复，均将此值视为序列一部分）

percent_rank()也是0 <= percent_rank() <= 1, 0和1都可以取到；

percent_rank()的计算公式应该是 = ((统计的列的种类) / (统计的列的总数)) * 100%，但是第一个总是从0开始的，不管是重复几次，要是567有4次，那就是有四个都是0，最后一个是1。

,percent_rank() over(partition by 门店名称, 平台 order by 日期) perpos

2) cume_dist()：根据分区内的排序，从0之后开始统计当前行所在排序中的百分比分布位置（如果指定值与分组序列中某值重复，则将二值视为一个值处理）

cume_dist()函数统计的范围是0 < cume_dist() <= 1, 但是取不到0；

cume_dist() 的计算公式应该是 = ((统计的列的种类) / (统计的列的总数)) * 每个重复种类中的个数；如value值为567的两个值，公式就是(4/5) * 2 = 0.4。而最大就是1了。

,cume_dist() over(partition by 门店名称, 平台 order by 日期) pos
# 场景：求消费金额排名前1%的用户；

(8) 再排序内分组（用的极少）

ntile(n)：将排序内的行分为n组，根据分区内的排序，返回每一行是第几组；

,ntile(4) over(partition by 门店名称, 平台 order by 日期) ntile_r

4. 错误总结

(1) 语法错误：You have an error in your SQL syntax；

(2) 子查询未命名时，报错：Every derived table must have its own alias；

(3) 要对窗口函数的排序结果进行where筛选时，需将其放在子查询中：Unknown column 'r' in 'where clause'：找不到窗口函数r的结果；由于窗口函数是再where后运行的, 所以窗口不能再当前所在查询中直接运行，需要再做一次子查询；

(4) 当同时查询时，上面的别名不能被下面引用，要不然会出现找不到该列的错误：Unknown column '前一日GMV' in 'field list'；

5. 做题总结:分步解题法--看/想到哪就写到哪

第一步：翻译它的数据需求，让它说人话

第二步：看/想到哪，就写到哪

第三步：重新组合代码

第四步：穷尽思考优化代码：

窗口函数实现任意级别聚合运算；

表连接进行数据筛选；

直接用having筛选；

用if函数省略子查询；

6. 解题思路总结

(1) 尽可能使用分步解题法，顺着题目一步步写;

(2) 写SQL代码时应该尽量脱离数据库，在脑海里完成代码的组合和调试;

(3) 遇到特别复杂的逻辑，一次查询无法解决，可以直接上子查询，然后再慢慢优化;

(4) 遇到暂时想不清楚的地方，可以先搁置，最后基于其他步骤已成型的代码进行推导;

(5) 实在不行就画图，或者用excel表格先处理出来，再一步步用SQL实现;

(6) 完成代码后一定要尽量优化自己的代码，这样逻辑才能越来越好;

(7) 不断反复练习，最终能在读题时就在脑海里理清代码逻辑;

7. 代码规范

(1) 只有select和from不换行，直接写成一行

(2) 核心语句不换行，join连接表换行，on连接键换行，多连接键也换行

(3) where/having多条件 and/or 换行，单条件语句不换行，例如：between and 、 sif.branch in ('上海分行'，'北京分行')

(4) group by 后字段不换行， order by 后字段不换行

(5) 括号内不加空格，函数的括号前不加空格，括号内不加空格，例如('上海分行','北京分行')、sum(amount)

(6) 运算符前加空格：=、 >=、 <=、 >、 <、 !=、 -、 +

(7) 含乘除的运算符前后不加空格：*、/、%

(8) select后只有一个字段不换行，select后的*算作一个字段来看

(9) distinct不换行，后面的字段大于一个字段换行

(10) 子查询缩进 -select 后子查询，括号换行，子查询缩进在括号后，括号上下对齐

(11) 子查询缩进-from后子查询，括号换行，括号与from同一缩进，括号中的查询缩进在括号后;

(12) 子查询缩进-from后两个子查询连接，join前后空行，保证两个被连接的子查询与from和join在同一缩进，括号上下对齐

(13) 子查询缩进-where后子查询，括号不换行，括号在in/运算符后，子查询缩进在括号后，括号上下对齐

(14) union前后为完整查询语句，且需要前后空行，完整查询语句不需要括号

(15) 窗口函数不换行

(16) 函数嵌套，函数除了case when 其余函数和函数的多重嵌套都写一行

(17) case when 函数，仅有一对when 和then时，全部写一行

(18) case when 函数，when后仅一个条件时，when和then在同一行，若when后有多个条件时then换行，且前后的then都换行，保持代码块内格式统一

(19) case when函数，else和end在同一行，case when 函数在本身有换行时，外面嵌套的函数，后半个括号要换行与前半个括号上下对齐

(20) case when函数嵌套，then 换行与 when对齐

(21) with as 中间表，表名换行，括号换行，代码缩进在括号后，多个中间表时用空行区分

(22) 别名尽量统一缩进

(23) 代码结束处加；

(24) 多写注释

8. 刷题提升

(1) SQLZOO：https://sqlzoo.net/

(2) 牛客网数据库SQL实战一定要刷完！

https://www.nowcoder.com/ta/sql

(3) 历年大厂真题里的SQL

https://www.nowcoder.com/contestRoom?mutiTagIds=894

END

作者：龙小仔

数据库

文章转载自数分小白龙，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL这些点，你是否还记得呢？

评论