SQL核心语法总结：从基础操作到高级窗口函数

目录

• SQL核心语法总结：从基础操作到高级窗口函数
• • 一、数据插入：INSERT IGNORE INTO & REPLACE INTO
  • 二、字符串处理函数
  • • 1. 基础长度与替换
    • 2. 字符串截取
    • • （1）通用截取：SUBSTRING
      • （2）定向截取：LEFT/RIGHT
  • 三、条件判断：CASE表达式
  • • 1. 简单CASE表达式（匹配字段值）
    • 2. 搜索CASE表达式（更灵活，匹配条件）
  • 四、存在性查询：EXISTS/NOT EXISTS
  • • 基本语法
  • 五、高级分析：窗口函数OVER()
  • • 1. 基本语法
    • 2. 三大核心组件
    • 3. 常见窗口函数分类
    • • （1）聚合窗口函数
      • （2）排名窗口函数
      • （3）取值窗口函数
    • 4. 窗口函数经典场景
    • • （1）累计占比计算
      • （2）移动窗口计算（如7天移动平均）
      • （3）获取每组最新记录
  • 六、易混淆点：SUM() OVER(ORDER BY) vs SUM() + GROUP BY + ORDER BY
  • 七、补充：GROUP_CONCAT() 语法注意
  • 总结

SQL核心语法总结：从基础操作到高级窗口函数

SQL作为数据处理的核心语言，掌握其常用语法和高级特性是数据分析、开发的必备技能。本文将系统梳理SQL高频语法，涵盖数据插入、字符串处理、条件判断、存在性查询、窗口函数等核心知识点，结合示例帮你快速理解和应用。

一、数据插入：INSERT IGNORE INTO & REPLACE INTO

在数据插入场景中，针对“重复数据”的处理有两种常用语法，核心差异如下：

语法	逻辑
INSERT IGNORE INTO	插入数据时，若记录已存在（主键/唯一索引冲突）则忽略，不存在则正常插入
REPLACE INTO	插入数据时，若记录已存在则先删除原有记录，再插入新记录；不存在则正常插入

二、字符串处理函数

SQL提供丰富的字符串操作函数，以下是高频使用的几个：

1. 基础长度与替换

• LENGTH(string)：获取字符串的长度
• REPLACE(string, 'old_value', 'new_value')：替换字符串内容
  示例：REPLACE(col, ',', ' ')将字段中的英文逗号替换为空格

2. 字符串截取

（1）通用截取：SUBSTRING

-- 两种标准语法SUBSTRING(string,start,length)SUBSTRING(stringFROMstartFORlength)

（2）定向截取：LEFT/RIGHT

-- 从左侧提取指定数量字符LEFT(string,number_of_chars)-- 从右侧提取指定数量字符RIGHT(string,number_of_chars)

三、条件判断：CASE表达式

CASE表达式是SQL中的条件判断语句，类比编程语言的if-else/switch-case，分两种语法形式：

1. 简单CASE表达式（匹配字段值）

CASEcolumn_nameWHENvalue1THENresult1WHENvalue2THENresult2...ELSEdefault_resultEND

2. 搜索CASE表达式（更灵活，匹配条件）

CASEWHENcondition1THENresult1WHENcondition2THENresult2...ELSEdefault_resultEND

示例：根据分数分级

SELECTscore,CASEWHENscore>=90THEN'优秀'WHENscore>=80THEN'良好'ELSE'及格'ENDASgradeFROMstudent;

四、存在性查询：EXISTS/NOT EXISTS

EXISTS是逻辑运算符，用于检查子查询是否至少返回一行数据，常用来替代IN提升查询效率（尤其大数据量）。

基本语法

-- 存在匹配结果则返回主查询数据SELECTcolumn1,column2FROMtable1WHEREEXISTS(subquery);-- 无匹配结果则返回主查询数据SELECTcolumn1,column2FROMtable1WHERENOTEXISTS(subquery);

示例：查询未关联部门的员工

SELECT*FROMemployeesWHERENOTEXISTS(SELECTemp_noFROMdept_empWHEREdept_emp.emp_no=employees.emp_no);

五、高级分析：窗口函数OVER()

OVER()是窗口函数的核心，用于在一组行（窗口）上执行计算，且保留原始行数据（区别于GROUP BY的汇总折叠），是SQL高级分析的核心工具。

1. 基本语法

聚合函数/窗口函数OVER([PARTITIONBY分区字段]-- 按字段分组，组内独立计算[ORDERBY排序字段]-- 窗口内行的排序规则[ROWS/RANGE 框架子句]-- 定义窗口的具体行范围)

2. 三大核心组件

组件	作用
PARTITION BY	分区：将数据按指定字段分组，每个分组独立计算（类比GROUP BY，但不折叠行）
ORDER BY	排序：确定窗口内行的顺序，用于累计计算、排名等
ROWS/RANGE	窗口框架：限定计算的行范围（如“最近3行”“前N行到当前行”）

3. 常见窗口函数分类

（1）聚合窗口函数

基于窗口计算聚合值，保留原始行：

-- 示例：计算每个部门的平均薪资，保留员工明细SELECTname,department,salary,AVG(salary)OVER(PARTITIONBYdepartment)ASdept_avg_salaryFROMemployees;

常用聚合窗口函数：SUM() OVER()、AVG() OVER()、COUNT() OVER()、MAX() OVER()、MIN() OVER()。

（2）排名窗口函数

用于分组内排序，核心差异如下：

函数名	特性	示例结果（分数：95、90、90、85）
ROW_NUMBER()	分组内生成唯一连续行号，无并列	1、2、3、4
RANK()	并列名次重复，后续名次跳号（如1、2、2、4）	1、2、2、4
DENSE_RANK()	并列名次重复，后续名次连续（如1、2、2、3）	1、2、2、3
PERCENT_RANK	按公式(rank-1)/(rows-1)计算相对排名，结果0~1	0.0、0.33、0.33、1.0
CUME_DIST	分组内≤当前值的行数/总行数，结果0~1	0.25、0.75、0.75、1.0

示例：薪资排名

SELECTemp_name,salary,ROW_NUMBER()OVER(ORDERBYsalaryDESC)ASrow_num,RANK()OVER(ORDERBYsalaryDESC)ASrankFROMemployees;

（3）取值窗口函数

用于获取窗口内指定位置的行数据：

• LAG(column, n)：获取当前行前n行的字段值
• LEAD(column, n)：获取当前行后n行的字段值
• FIRST_VALUE(column)：窗口内第一个值
• LAST_VALUE(column)：窗口内最后一个值

示例：对比每日销售额与前一日

SELECTdate,sales,LAG(sales,1)OVER(ORDERBYdate)ASprev_day_sales,sales-LAG(sales,1)OVER(ORDERBYdate)ASdaily_changeFROMdaily_sales;

4. 窗口函数经典场景

（1）累计占比计算

SELECTdate,sales,SUM(sales)OVER(ORDERBYdate)ASrunning_total,-- 累计销售额ROUND(100.0*SUM(sales)OVER(ORDERBYdate)/SUM(sales)OVER(),2)AScum_percentage-- 累计占比FROMdaily_sales;

（2）移动窗口计算（如7天移动平均）

SELECTdate,sales,AVG(sales)OVER(ORDERBYdateROWSBETWEEN6PRECEDINGANDCURRENTROW-- 包含当前行及前6行)ASmoving_avg_7dFROMdaily_sales;

（3）获取每组最新记录

-- 示例：获取每个用户最后一次登录的客户端信息SELECTu_n,c_n,dateFROM(SELECTu.nameasu_n,c.nameasc_n,l.date,row_number()over(partitionbyu.idorderbydatedesc)asrk-- 按用户分区，按时间降序排名FROMlogin ljoinuseruonl.user_id=u.idjoinclient conc.id=l.client_id)sWHERErk=1-- 取排名1（最新）的记录ORDERBYu_n;

六、易混淆点：SUM() OVER(ORDER BY) vs SUM() + GROUP BY + ORDER BY

初学者常混淆累计求和与分组求和，核心差异如下：

特性	SUM() OVER(ORDER BY date)	SUM(sales) GROUP BY date ORDER BY date
返回行数	原始行数（保留明细）	分组行数（仅汇总行）
计算逻辑	累计求和（运行总和）	分组求和（单组独立值）
结果示例	100、250、450（累计）	100、150、200（每日单独值）
适用场景	趋势分析、累计占比	分组汇总、单维度统计

七、补充：GROUP_CONCAT() 语法注意

MySQL中使用GROUP_CONCAT()时，别名尽量避免直接使用引号（部分版本/配置会报错），优先使用无引号或反引号（`）包裹别名。

总结

1. 数据插入优先根据“重复数据处理逻辑”选择INSERT IGNORE INTO或REPLACE INTO；
2. 字符串处理掌握LENGTH/REPLACE/SUBSTRING/LEFT/RIGHT即可覆盖大部分场景；
3. 条件判断用CASE表达式，灵活处理多分支逻辑；
4. 存在性查询优先用EXISTS替代IN，提升大数据量查询效率；
5. 窗口函数OVER()是高级分析核心，掌握PARTITION BY/ORDER BY/ROWS三大组件，可实现累计、排名、移动计算等复杂分析；
6. 区分SUM() OVER(ORDER BY)（累计）与GROUP BY（分组汇总）的核心差异，避免用错场景。

掌握以上语法，可覆盖从基础数据操作到高级数据分析的绝大部分SQL使用场景，建议结合实际业务场景多练习，加深理解。