SQL进阶

已于 2024-09-26 11:51:21 修改
阅读量611 收藏 27
点赞数 12
分类专栏： 2-数据分析技术之sql（笔记+面试） 文章标签： sql 数据库 前端
于 2024-08-23 11:18:47 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Daguai_upgrade/article/details/141402917
版权

1、CASE WHEN函数语句多条件

-- 请在此处输入 SQL
select name, 
case when (age>60) then '老同学'
     when (age>20) then '年轻' 
     else '小同学'
     end as age_level
from student
order by name

2、函数 - 时间函数

  • DATE：获取当前日期
  • DATETIME：获取当前日期时间
  • TIME：获取当前时间
-- 获取当前日期
SELECT DATE() AS date();

-- 获取当前时间
SELECT TIME() AS time();

-- 获取当前日期时间
SELECT DATETIME() AS datetime();

date（）

time（）datetime（）
2023-08-0114:30:002023-08-01 14:30:00

3、函数 - 字符串处理

字符串进行各种操作，如转换大(upper)小(lower)写、计算字符串长度(length)以及搜索和替换子字符串等。

4、聚合函数：汇总计算

  • COUNT()：计算指定列的行数或非空值的数量。
  • SUM()：计算指定列的数值之和。
  • AVG()：计算指定列的数值平均值。
  • MAX()：找出指定列的最大值。
  • MIN()：找出指定列的最小值。

5、分组聚合 - 多字段分组

要查询使用多字段分组查询表中 每个客户 购买的 每种商品 的总金额，相当于按照客户编号和商品编号分组：

-- 查询每个用户购买的每种商品的总金额，按照客户编号和商品编号分组
SELECT customer_id, product_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, product_id;

6、关联查询

6.1 (cross)join或者 ,------笛卡尔积

方式1(,):
SELECT e.emp_name, e.salary, e.department, d.manager
FROM 
employees e,departments d;

方式2(join):
SELECT e.emp_name, e.salary, e.department, d.manager
FROM 
employees e JOIN departments d;

方式3(cross join):
SELECT e.emp_name, e.salary, e.department, d.manager
FROM 
employees e CROSS JOIN departments d;

员工表 employees：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_idemp_namedepartmentsalary
1小明技术部5000
2鸡哥财务部6000
3李华销售部4500

部门表 departments：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

departmentmanagerlocation
技术部张三上海
财务部李四北京
销售部王五

广州

查询结果：

emp_namesalarydepartmentmanager
小明5000技术部张三
小明5000财务部李四
小明5000销售部王五
鸡哥6000技术部张三
鸡哥6000财务部李四
鸡哥6000销售部王五
李华4500技术部张三
李华4500财务部李四
李华4500销售部王五

6.2 (inner)join on

方式1
SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
JOIN departments d ON e.department = d.department;

方式2
SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
INNER JOIN departments d ON e.department = d.department;

员工表 employees：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_idemp_namedepartmentsalary
1小明技术部5000
2鸡哥财务部6000
3李华销售部4500

部门表 departments：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

departmentmanagerlocation
技术部张三上海
财务部李四北京
销售部王五广州
摸鱼部赵二吐鲁番

查询结果如下：

emp_namesalarydepartmentmanager
小明5000技术部张三
鸡哥6000财务部李四
李华4500销售部王五

6.3 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 

left join

员工表 employees：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_idemp_namedepartmentsalary
1小明技术部5000
2鸡哥财务部6000
3李华销售部4500

部门表 departments：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

departmentmanagerlocation
技术部张三上海
财务部李四北京
人事部王五广州
摸鱼部赵二吐鲁番
SELECT e.emp_name, e.salary, e.department, d.manager
FROM 
employees e LEFT JOIN departments d 
ON e.department = d.department;

查询结果：

emp_namesalarydepartmentmanager
小明5000技术部张三
鸡哥6000财务部李四
李华4500销售部NULL

right join

员工表 employees：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_idemp_namedepartmentsalary
1小明技术部5000
2鸡哥财务部6000
3李华销售部4500

部门表 departments：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

departmentmanagerlocation
技术部张三上海
财务部李四北京
人事部王五广州
摸鱼部赵二吐鲁番
SELECT e.emp_name, e.salary, e.department, d.manager
FROM 
employees e RIGHT JOIN departments d 
ON e.department = d.department;

查询结果：

7、查询进阶 - 组合查询:UNION 和 UNION ALL

table1 表：

emp_idnameagedepartment
101Alice25HR
102Bob28Finance
103Charlie22IT

table2 表：

emp_idnameagedepartment
101Alice25HR
201David27Finance
202Eve24HR
203Frank26IT

7.1 UNION 操作的结果，去除了重复的行（名称为 Alice）：

SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;
nameagedepartment
Alice25HR
Bob28Finance
Charlie22IT
David27Finance
Eve24HR
Frank26IT

7.2 UNION ALL 操作，保留了重复的行：：

SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;
nameagedepartment
Alice25HR
Bob28Finance
Charlie22IT
Alice25HR
David27Finance
Eve24HR
Frank26IT

8、开窗函数

开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

8.1 sum over（分组统计）

SUM(计算字段名) OVER (PARTITION BY 分组字段名)

订单表 orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

计算每个客户的订单总金额，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROM
    orders;

查询结果：

order_idcustomer_idorder_datetotal_amountcustomer_total_amount
11012023-01-01200320
31012023-01-10120320
21022023-01-05350350
41032023-01-15500500

在上面的示例中，我们使用开窗函数 SUM 来计算每个客户的订单总金额（customer_total_amount），并使用 PARTITION BY 子句按照customer_id 进行分组。从前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单总金额。

8.2 sum over order by（同组内数据的 累加求和 ）

SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

计算每个客户的历史订单累计金额，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROM
    orders;

结果将是：

order_idcustomer_idorder_datetotal_amountcumulative_total_amount
11012023-01-01200200
31012023-01-10120320
21022023-01-05350350
41032023-01-15500

500

在上面的示例中，我们使用开窗函数 SUM 来计算每个客户的历史订单累计金额（cumulative_total_amount），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 order_date 进行排序。从结果的前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的历史订单累计金额；相比于只用 sum over，同组内的累加列名称

8.3 rank（用于对查询结果集中的行进行 排名）

⚠️ 当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

表 orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

为每个客户的订单按照订单金额降序排名，并显示每个订单的详细信息。

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_idcustomer_idorder_datetotal_amountcustomer_rank
11012023-01-012001
31012023-01-101202
21022023-01-053501
41032023-01-155001

在上面的示例中，我们使用开窗函数 RANK 来为每个客户的订单按照订单金额降序排名（customer_rank），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 total_amount 从大到小进行排序。

可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单金额排名。

8.4 row_number（为查询结果集中的每一行 分配唯一连续排名）

表 orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15

500

为每个客户的订单按照订单金额降序排列，并且分配一个 row_number 编号。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS row_number
FROM
    orders;

结果将是：

order_idcustomer_idorder_datetotal_amountrow_number
41032023-01-155001
21022023-01-053501
11012023-01-012001
31012023-01-10120

2

在上面的示例中，我们使用开窗函数 ROW_NUMBER 为每个客户的订单按照订单金额降序排列，并为每个订单分配了一个编号（row_number），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 total_amount 进行排序。

8.5 lag/lead（获取在当前行之前或之后的行的值）

💡 比较相邻行数据或进行时间序列分析时非常有用

LAG(取值的列名, 向⬆️偏移的行数, 指定当没有前一行时的默认值) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段名)

LEAD(取值的列名, 向⬇️偏移的行数, 指定当没有前一行时的默认值) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段名)

学生成绩表scores，其中包含学生的成绩和考试日期：

student_idexam_datescore
1012023-01-0185
1012023-01-0578
1012023-01-1092
1012023-01-15

80

查询每个学生的考试日期和上一次考试的成绩，以及下一次考试的成绩。

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_idexam_datescoreprevious_scorenext_score
1012023-01-0185NULL78
1012023-01-05788592
1012023-01-10927880
1012023-01-158092NULL

Daguai_upgrade
关注
专栏目录
sql 进阶
LEIQINGQI的博客
08-09 724
aliases 别名 约束 not null unique primary key foreign key default check join(join方式决定条数) inner join left join right join full join full join = left join + right join 笛卡儿连接 语法: union 索引 单列索引： 唯一索引...
SQL语句进阶
书灯的博客
07-19 329
本文讲解的是SQL语句进阶，主要包含： 1.函数和聚合 2.数据分组group by 3.过滤分组having 4.连接表 5.触发器 6.查询优化-索引 1.函数和聚合 SQL语句支持利用函数来处理数据，函数一般是在数据上执行的，他给数据的转换和处理提供了方便。 常用的文本处理函数有： length() 返回字符串的长度； lower() 将字符串转换为小写； upp
sql进阶
qq_48247261的博客
04-04 1473
文章目录一、锁1.全局锁2.表锁1.表锁2.元数据锁3.意向锁（我们使用select ... lock in share mode后，加表锁共享锁不会阻塞）3.行锁4.意向锁和表锁的关系二、innoDB1.逻辑存储结构2.内存结构3.磁盘结构（存放一个个idb文件的文件夹）4.线程5.事务原理1.特性：2.redolog3.undolog4.当前读和快照读5.MMVC三、索引1.索引结构2.B+Tree3.hash4.索引术语解析5.sql性能分析1.SQL执行频率2.慢查询日志3.profile4.exp
SQL学习——进阶篇
qq_49948651的博客
09-11 2866
窗口函数原则上只能写在select子句中，partition子句可以省略，省略就是不指定分组，但是，这就失去了窗口函数的功能，所以一般不要这么使用。，所以经常用来在每组内排名，同时具有分组（partition by）和排序（order by）的功能。，如排名问题：每个部门按业绩来排名；topN问题：找出每个部门排名前N的员工进行奖励。其中AxB 与 BxA 就是所谓的笛卡尔乘积，很明显AxB!：count(列名)会比count(1)快；：count(1)会比count(列名)快。**窗口函数功能： **
sql进阶教程
保持热爱，持续学习
02-01 4211
SQL进阶教程
PDF00213.SQL进阶教程_sql_
09-30
《SQL进阶教程》是一本面向中级用户的实用指南，旨在帮助读者深入理解并掌握SQL语言的高级技巧，进一步提升在数据库管理和数据处理方面的技能。在本教程中，我们将探讨SQL的多个关键领域，包括复杂的查询、性能优化...
关于SQL的几道小题详解(sql进阶)
01-19
当我们拿到题目的时候，并不是急于作答，那样会得不偿失的，而是分析思路，采用什么方法，达到什么目的，还要思考有没有简单的方法或者通用的方法等等，这样才会达到以一当十的效果，这样的惯性思维其实早在我们度...
进阶sql精华
08-31
进阶sql精华，常用进阶sql技巧汇总集合
SQL进阶（完整总结版||ppt）
03-11
本文档为进阶版sql，建议先学习基础的sql语句知识后查看。
SQL进阶篇
柳小葱的博客
06-21 2466
????今天我们来说一说SQL进阶篇的一些操作，主要的内容包含一些函数介绍，例如：字符函数，时间函数，窗口函数等。 1.字符函数 1.1 拼接两个字段 --CONCAT函数 SELECT CONCAT(user_id,Order_date) #拼接两个字段 from ord; 结果如下： 1.2 转换大小写 --LOWER与UPPER SELECT LOWER("ABCDEFG");#转换为小写 SELECT UPPER("abdefg");#转化为大写 结果如下： 1.3 获取字符 --LEFT
MySQL 数据库管理之 --- SQL 语言进阶一
热门推荐
shenyuanhaojie的博客
10-27 4万+
文章目录一、常用查询1. 按关键字排序(1) 语法(2) 创建模板表(3) 单字段排序(4) 条件查询(5) 多字段排序(6) 区间判断及查询不重复记录(7) distinct 查询不重复记录2. 对结果进行分组3. 限制结果条目（limit）4. 设置别名（alias ---> as）5. 通配符6. 子查询7. 一、常用查询   对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。 例如只取 10 条数据、对查询结果进行排序或分组等等。 1. 按关键字排序   使用
SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
weixin_45732235的博客
09-28 2411
mysql学习
SQL学习分享（进阶篇）
最新发布
zzhqjnm的博客
07-31 1084
- 创建course数据表-- 创建score数据表s_id INT,c_id INT,score INT);-- 创建teacher数据表t_id INT,-- 清空student数据表并插入数据INSERT INTO student VALUES(1,'胡图图','男',6);INSERT INTO student VALUES(2,'牛爷爷','男',55);INSERT INTO student VALUES(3,'壮壮妈','女',33);
SQL Server查询进阶
自学编程_youkewang.top
05-03 1489
常用的SQL查询语句 --选择school数据库 use school; select * from student; select * from grade; --使用内连接查询所有学生的基本信息和班级 select name as '姓名' , sex as '性别', age as '年龄', gradeid as '班别', address as '地址', phon
进阶SQL总结
Biubiubiu!
01-13 951
文章目录零、引入1、mysql8.0的使用2、==zeppelin==的配置以及使用3、创建表的sql文件，我没有上传，需要的可以私聊一、if、case的使用1、sql的if语法：2、sql的case...end语法：二、客户信息分析练习三、交易分析练习四、门店分析练习五、窗口函数(mysql8.0新特性)1、窗口函数定义2、窗口函数分析3、窗口函数练习 零、引入 1、mysql8.0的使用 注意...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值