跳至主要內容

09.其他操作

12a2024/10/3大约 5 分钟数据库后端sql

09.其他操作

9.1 游标

  • 虽然我们可以通过筛选条件WHERE和HAVING，或者是限定返回记录的关键字LIMIT返回一条记录，但是，却无法在结果集中随意定位到某条记录并进行修改处理

  • 游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构

    • 游标可以用于操作数据行，同时也可以滚动游标，指向结果集中的任意一行

  • 创建游标

    MySQL
    -- 创建游标
DECLARE 游标名 CURSOR FOR 查询语句;

  • 使用游标

    -- 打开游标
OPEN 游标名;

-- 获取游标指向的记录，变量应该在之前DECLARE提前定义
-- 接收的变量对应查询语句的列名
-- 如果已经到达结果集的末尾，FETCH 操作将返回 @@FETCH_STATUS = -1
-- 我们可以通过检查 @@FETCH_STATUS 的值来判断是否已经遍历完结果集
FETCH 游标名 INTO 变量名1, 变量名2, ...;

-- 关闭游标
CLOSE 游标名;

-- 释放游标
DEALLOCATE 游标名;

游标

游标可以逐条读取数据，并且支持在存储过程和函数中使用，比在应用层实现相比，更简洁高效。但是，游标会对数据行加锁，在并发量大的情况下，会影响系统效率，而且游标会占用系统内存资源，应该在需要时开启，使用完成后及时关闭

9.2 窗口函数

  • 窗口函数类似对数据进行分组，但和分组不同的是，窗口函数会把结果置于每一条记录中，而不是写成一条记录

  • 窗口函数可以分为静态窗口函数和动态窗口函数

    • 静态窗口函数的窗口大小是固定的
    • 动态窗口函数的窗口大小会随记录的不同而变化
    窗口函数
    窗口函数
    -- 窗口函数基本语法
窗口函数 OVER 窗口
-- 窗口函数将窗口作为查询子句
SELECT 窗口函数 OVER 窗口名
FROM 表名
WINDOW 窗口名 AS 窗口;

-- 窗口定义
-- 如果有ORDER BY默认窗口是从第一行开始，到当前行结束
-- 如果没有ORDER BY默认窗口是从第一行开始，到最后一行结束
OVER ([PARTITION BY 分组字段] [ORDER BY 排序字段 ASC|DESC] [RANGE|ROWS BETWEEN 窗口起始 AND 窗口结束])
    • RANGE是依据当前排序列的值变化，比如比当前值大3：RANGE 3 FOLLOWING
    • ROWS是依据物理数指定，比如说上一行：ROWS 1 PRECEDING
    • 支持窗口起始和结束符包括
      • CURRENT ROW: 当前行
      • UNBOUNDED PRECEDING: 当前划分的第一行
      • UNBOUNDED FOLLOWING: 当前划分的最后一行
      • expr PRECEDING: 当前行前的expr行(或当前行排序列值减去expr)
      • expr FOLLOWING: 当前行后的expr行(或当前行排序列值加上expr)

  • 例子

    -- 序号函数例子：获取每个类别下的商品价格排名
SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY prices ASC)
 AS row_num, good_name, prices, category
FROM goods;

-- 分布函数例子：查询数据库中在对应类别小于等于当前价格的比例
SELECT CUME_DIST() OVER (PARTITION BY category ORDER BY prices ASC)
 AS cume_dist, prices
FROM goods;

-- 前后函数例子：获取前一个商品价格和当前商品价格差值
SELECT good_name, prices - lag_prices AS diff_prices
FORM (
SELECT prices, LAG(prices, 1) OVER (PARTITION BY category ORDER BY prices ASC) AS lag_prices
FROM goods
);

-- 首尾函数例子：按照价格排序，查询最后一个商品的信息
SELECT LAST_VALUE(prices) OVER (PARTITION BY category ORDER BY prices ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_prices, good_name, prices
FROM goods;

-- 其他函数例子：查询表中价格排名第2、第3的商品信息
SELECT good_name, prices, NTH_VALUE(prices, 2) OVER w AS second_prices, NTH_VALUE(prices, 3) OVER w AS third_prices
FROM goods
WINDOW w AS (PARTITION BY category ORDER BY prices ASC);

9.3 公用表表达式

  • 公用表表达式或通用表表达式CTE，是SQL数据操作DML语句中一个可复用的的子查询，可以被其他CTE引用

    子查询本身不支持复用和被其他子查询引用

  • 定义普通公用表表达式

    -- 相当于使用子查询构建临时表
-- 查询列名可省略，默认使用子查询列名
-- 支持with后定义多个公用表, 以','分隔
WITH cte_name [(查询列名)] AS (子查询)
-- 构建完成后进行操作
SELECT|UPDATE|DELETE 语句;

-- 例子：查询有员工存在的部门信息
WITH cte_emp AS (
  SELECT DISTINCT department_id FROM employees
)
SELECT * FROM department AS d
LEFT JOIN cte_emp AS e ON d.department_id = e.department_id;

  • 定义递归公用表表达式

    -- 递归进行查询
WITH RECURSIVE cte_name [(查询列名)] AS (
  -- 初始条件，一般查询列中会添加一个额外的次数标记
  初始子查询
  UNION [ALL|DISTINCT]
  -- 递归过程使用上次查询结果集，递归终止条件是此查询没有返回任何行
  -- 递归过程中无法使用聚合函数、排序、分组和limit子句
  递归过程子查询
)
SELECT|UPDATE|DELETE 语句;

-- 执行顺序
-- 1. 将成员分为两个：初始和递归成员
-- 2. 执行初始查询得到第一个结果集(R0)，并使用R0进行下一次查询
-- 3. 将 Ri 结果集作为输入执行递归过程，并将 Ri+1 作为输出
-- 4. 重复3过程，直到递归返回一个空结果集，递归停止
-- ，使用并运算将结果集从 R0 到 Rn 合并成最终结果集

-- 例子：查询公司中的所有下下属(第三级及更初级)员工信息
WITH RECURSIVE cte_emp AS (
  -- 初始条件，查询最高级员工信息
  SELECT employee_id, employee_name, senior_id, 1 AS level FROM employee
  WHERE senior_id IS NULL
  UNION ALL
  -- 递归过程，查询上次查询到的员工的所有下属员工信息
  SELECT a.employee_id, a.employee_name, a.senior_id, level + 1 FROM employee AS a LEFT JOIN cte_emp ON a.senior_id = cte_emp.employee_id
)
SELECT employee_id, employee_name FROM cte_emp WHERE level >= 3;

  • 使用公用表表达式：定义完成的公用表表达式就相当于一张表，可以和普通表一样查询

    SELECT senior_name, employee_name FROM employee 
left join cte_emp on employee.senior_id = cte_emp.employee_id;
最近更新