sql查询语句（详细）

前言

本篇文章为本人在学习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，用于查找不满足存在条件的记录。

组合查询

1. UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。
2. 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