前言
本篇文章为本人在学习sql语句时总结出的学习笔记，本文将我所学习到的sql语句关键字都总结了一下。
总结的来源多种多样，所以每条的书写格式会有写区别
查询语句
select
select 查找的字段 from 表名
select * from 表名 （查找该表所有字段）
select * from 表名 where 条件 （查找符合条件的字段）
例：
select * from students where sex = "男"
where
=等于，<小于，>大于，<=小于等于，>=大于等于，<>或!= 不等于
and和 or或 between…and… 介于两个值之间(包含等于)
in 包含 例：where id in (3,5,9,4,2)
is null 为null
模糊查询（like)
需要配合占位符使用
代表一位字符 李 王__
%代表任意位字符 李%
例：
select * from students where name like **"李_"**
条件分支
示例语法
CASE WHEN (条件1) THEN 结果1
WHEN (条件2) THEN 结果2 ...
ELSE 其他结果 END
例如
假设有一个学生表 student，包含以下字段：name（姓名）、age（年龄）。请你编写一个 SQL 查询，将学生按照年龄划分为三个年龄等级（age_level）：60 岁以上为 “老同学”，20 岁以上（不包括 60 岁以上）为 “年轻”，20 岁及以下、以及没有年龄信息为 “小同学”。
返回结果应包含学生的姓名（name）和年龄等级（age_level），并按姓名升序排序。
SELECT name,
CASE WHEN (age > 60) THEN '老同学'
WHEN (age > 20) THEN '年轻'
ELSE '小同学' END AS age_level
FROM student
ORDER BY name asc
;
时间函数
使用时间函数获取当前日期、当前日期时间和当前时间：
-- 获取当前日期
SELECT DATE() AS current_date;
-- 获取当前日期时间
SELECT DATETIME() AS current_datetime;
-- 获取当前时间
SELECT TIME() AS current_time;
查询结果：
为了方便对比，放到同一个表格
| current_date | current_datetime | current_time |
|---|---|---|
| 2023-08-01 | 2023-08-01 14:30:00 | 14:30:00 |
字符串处理
1）使用字符串处理函数 UPPER 将姓名转换为大写：
-- 将姓名转换为大写
SELECT name, UPPER(name) AS upper_name
FROM employees;
查询结果：
| name | upper_name |
|---|---|
| 小明 | 小明 |
| 热dog | 热DOG |
| Fish摸摸 | FISH摸摸 |
| 鸡哥 | 鸡哥 |
2）使用字符串处理函数 LENGTH 计算姓名长度：
-- 计算姓名长度
SELECT name, LENGTH(name) AS name_length
FROM employees;
查询结果：
| name | name_length |
|---|---|
| 小明 | 2 |
| 热dog | 4 |
| Fish摸摸 | 6 |
| 鸡哥 | 2 |
3）使用字符串处理函数 LOWER 将姓名转换为小写：
-- 将姓名转换为小写并进行条件筛选
SELECT name, LOWER(name) AS lower_name
FROM employees;
查询结果：
| name | lower_name |
|---|---|
| 小明 | 小明 |
| 热dog | 热dog |
| Fish摸摸 | fish摸摸 |
| 鸡哥 | 鸡哥 |
聚合查询
**avg()最大值 **
**count()数量 **
**min()最小值 **
**max()最大值 **
sum()总和
例：select max(age) from student
查表中的年龄最大值
分组查询
group by
例：select max(age) from student group by sex
查表中的男的和女的年龄最大值，两个结果
综合应用：
`select
name,sex,sum(sx+yw+yy) 总分,
(yy+sx+yw)/3 平均分
from student
where sex="男"
group by id order by 总分 desc limit 0,3`
having
分组之后加条件
select
name,sex,sum(sx+yw+yy) 总分,(yy+sx+yw)/3 平均分
from student
where sex="男"
group by id **having 平均分>60**
order by 总分 desc limit 0,5
关联查询
inner join
它根据两个表之间的关联条件，将满足条件的行组合在一起。
注意，INNER JOIN 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。
假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：
| emp_id | emp_name | department | salary |
|---|---|---|---|
| 1 | 小明 | 技术部 | 5000 |
| 2 | 鸡哥 | 财务部 | 6000 |
| 3 | 李华 | 销售部 | 4500 |
假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：
| department | manager | location |
|---|---|---|
| 技术部 | 张三 | 上海 |
| 财务部 | 李四 | 北京 |
| 销售部 | 王五 | 广州 |
| 摸鱼部 | 赵二 | 吐鲁番 |
使用 INNER JOIN 进行关联查询，根据员工表和部门表之间的公共字段 部门名称（department） 进行匹配，将员工的姓名、工资以及所属部门和部门经理组合在一起：
SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
JOIN departments d ON e.department = d.department;
outer join
它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。
在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。
示例
假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：
| emp_id | emp_name | department | salary |
|---|---|---|---|
| 1 | 小明 | 技术部 | 5000 |
| 2 | 鸡哥 | 财务部 | 6000 |
| 3 | 李华 | 销售部 | 4500 |
假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：
| department | manager | location |
|---|---|---|
| 技术部 | 张三 | 上海 |
| 财务部 | 李四 | 北京 |
| 人事部 | 王五 | 广州 |
| 摸鱼部 | 赵二 | 吐鲁番 |
使用 LEFT JOIN 进行关联查询，根据员工表和部门表之间的部门名称进行匹配，将员工的姓名、工资以及所属部门和部门经理组合在一起，并包含所有员工的信息：
SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
LEFT JOIN departments d ON e.department = d.department;
查询结果：
| emp_name | salary | department | manager |
|---|---|---|---|
| 小明 | 5000 | 技术部 | 张三 |
| 鸡哥 | 6000 | 财务部 | 李四 |
| 李华 | 4500 | 销售部 | NULL |
子查询
子查询是指在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据
假设我们有以下两个数据表：orders 和 customers，分别包含订单信息和客户信息。
orders 表：
| order_id | customer_id | order_date | total_amount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 200 |
| 2 | 102 | 2023-01-05 | 350 |
| 3 | 101 | 2023-01-10 | 120 |
| 4 | 103 | 2023-01-15 | 500 |
customers 表：
| customer_id | name | city |
|---|---|---|
| 101 | Alice | New York |
| 102 | Bob | Los Angeles |
| 103 | Charlie | Chicago |
现在，我们希望查询出订单总金额 > 200 的客户的姓名和他们的订单总金额，示例 SQL 如下：
-- 主查询
SELECT name, total_amount
FROM customers
WHERE customer_id IN (
-- 子查询
SELECT DISTINCT customer_id
FROM orders
WHERE total_amount > 200
);
exists
子查询中的一种特殊类型是 “exists” 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。
现在，我们希望查询出 存在订单的 客户姓名和订单金额。
使用 exists 子查询的方式，SQL 代码如下：
-- 主查询
SELECT name, total_amount
FROM customers
WHERE EXISTS (
-- 子查询
SELECT 1
FROM orders
WHERE orders.customer_id = customers.customer_id
);
上述语句中，先遍历客户信息表的每一行，获取到客户编号；然后执行子查询，从订单表中查找该客户编号是否存在，如果存在则返回结果。
查询结果如下：
| name | total_amount |
|---|---|
| Alice | 200 |
| Bob | 350 |
| Charlie | 500 |
和 exists 相对的是 not exists，用于查找不满足存在条件的记录。
组合查询
- UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。
- UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。
假设我们有以下两个数据表：table1 和 table2，分别包含不同部门的员工信息。
table1 表：
| emp_id | name | age | department |
|---|---|---|---|
| 101 | Alice | 25 | HR |
| 102 | Bob | 28 | Finance |
| 103 | Charlie | 22 | IT |
table2 表：
| emp_id | name | age | department |
|---|---|---|---|
| 101 | Alice | 25 | HR |
| 201 | David | 27 | Finance |
| 202 | Eve | 24 | HR |
| 203 | Frank | 26 | IT |
现在，我们想要合并这两张表的数据，分别执行 UNION 操作和 UNION ALL 操作。
UNION 操作：
SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;
UNION 操作的结果，去除了重复的行（名称为 Alice）：
| name | age | department |
|---|---|---|
| Alice | 25 | HR |
| Bob | 28 | Finance |
| Charlie | 22 | IT |
| David | 27 | Finance |
| Eve | 24 | HR |
| Frank | 26 | IT |
UNION ALL 操作：
-- UNION ALL操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;
结果如下，保留了重复的行：
| name | age | department |
|---|---|---|
| Alice | 25 | HR |
| Bob | 28 | Finance |
| Charlie | 22 | IT |
| Alice | 25 | HR |
| David | 27 | Finance |
| Eve | 24 | HR |
| Frank | 26 | IT |
datediff
计算两个数据的差值
例子：力扣197——计算上升的温度
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是该表具有唯一值的列。 没有具有相同 recordDate 的不同行。 该表包含特定日期的温度信息
编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。
解决：
select w1.id
from weather w1 join weather w2 on
datediff(w1.recorddate , w2.recorddate) = 1
where w1.temperature>w2.temperature;
开窗函数
sum over
在 SQL 中，开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。
开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。
打个比方，可以将开窗函数想象成一种 “透视镜”，它能够将我们聚焦在某个特定的分组，同时还能看到整体的全景。
用法：
SUM(计算字段名) OVER (PARTITION BY 分组字段名)
sum over order by
可以实现同组内数据的 累加求和
示例用法如下：
SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)
Rank
Rank 开窗函数是 SQL 中一种用于对查询结果集中的行进行 排名 的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。
Rank 开窗函数的常见用法是在查询结果中查找前几名（Top N）或排名最高的行。
Rank 开窗函数的语法如下：
RANK() OVER (
PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column
其中，PARTITION BY 子句可选，用于指定分组列，将结果集按照指定列进行分组；ORDER BY 子句用于指定排序列及排序方式，决定了计算 Rank 时的排序规则。AS rank_column 用于指定生成的 Rank 排名列的别名。
假设我们有订单表 orders，表格数据如下：
| order_id | customer_id | order_date | total_amount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 200 |
| 2 | 102 | 2023-01-05 | 350 |
| 3 | 101 | 2023-01-10 | 120 |
| 4 | 103 | 2023-01-15 | 500 |
现在，我们希望为每个客户的订单按照订单金额降序排名，并显示每个订单的详细信息。
SELECT
order_id,
customer_id,
order_date,
total_amount,
RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
orders;
查询结果：
| order_id | customer_id | order_date | total_amount | customer_rank |
|---|---|---|---|---|
| 1 | 101 | 2023-01-01 | 200 | 1 |
| 3 | 101 | 2023-01-10 | 120 | 2 |
| 2 | 102 | 2023-01-05 | 350 | 1 |
| 4 | 103 | 2023-01-15 | 500 | 1 |
Row_Number
Row_Number 开窗函数是 SQL 中的一种用于为查询结果集中的每一行 分配唯一连续排名 的开窗函数。
它与之前讲到的 Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。
Row_Number 开窗函数的语法如下（几乎和 Rank 函数一模一样）：
ROW_NUMBER() OVER (
PARTITION BY column1, column2, ... -- 可选，用于指定分组列
ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS row_number_column
其中，PARTITION BY子句可选，用于指定分组列，将结果集按照指定列进行分组。ORDER BY 子句用于指定排序列及排序方式，决定了计算 Row_Number 时的排序规则。AS row_number_column 用于指定生成的行号列的别名。
Lag 和 Lead
开窗函数 Lag 和 Lead 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。
1）Lag 函数
Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。
Lag 函数的语法如下：
LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
参数解释：
-
column_name：要获取值的列名。 -
offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。 -
default_value：可选参数，用于指定当没有前一行时的默认值。 -
PARTITION BY和ORDER BY子句可选，用于分组和排序数据。
2）Lead 函数
Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。
Lead 函数的语法如下：
LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
参数解释：
-
column_name：要获取值的列名。 -
offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推。 -
default_value：可选参数，用于指定当没有后一行时的默认值。 -
PARTITION BY和ORDER BY子句可选，用于分组和排序数据。
以下是一个示例，假设我们有一个学生成绩表scores，其中包含学生的成绩和考试日期：
| student_id | exam_date | score |
|---|---|---|
| 101 | 2023-01-01 | 85 |
| 101 | 2023-01-05 | 78 |
| 101 | 2023-01-10 | 92 |
| 101 | 2023-01-15 | 80 |
现在我们想要查询每个学生的考试日期和上一次考试的成绩，以及下一次考试的成绩，示例 SQL 如下：
SELECT
student_id,
exam_date,
score,
LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
scores;
结果将是：
| student_id | exam_date | score | previous_score | next_score |
|---|---|---|---|---|
| 101 | 2023-01-01 | 85 | NULL | 78 |
| 101 | 2023-01-05 | 78 | 85 | 92 |
| 101 | 2023-01-10 | 92 | 78 | 80 |
| 101 | 2023-01-15 | 80 | 92 | NULL |
在上面的示例中，我们使用 Lag 函数获取每个学生的上一次考试成绩（previous_score），使用 Lead 函数获取每个学生的下一次考试成绩（next_score）。如果没有上一次或下一次考试，对应的列将显示为 NULL。
增删改
添加：insert
insert into 表名（字段1，字段2，字段3) values(数据1，数据2，数据3)
例：
insert into students (name,sex,age,classid) values("伯元","男",21,1)
删除：delete
delete from 表名 where 条件
例：
delete from students where calssid = 1
修改：update
update 表名 set 修改的内容 where 条件
例：
update students set sex="女",name="线程",age=12 where id =4