1. 窗口函数

1) 窗口函数的概念

窗口函数也称为OLAP函数，OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。窗口函数（Window Function）是 MySQL 8.0 新增的一个重要的功能，可以为数据分析提供强大的支持，例如计算分组排名、累积求和、同比/环比增长率等。

常见的聚合函数，包括 AVG、COUNT、MAX、MIN、SUM，它的作用就是对一组数据行进行汇总计算，并且返回单个分析结果。窗口函数和聚合函数类似之处在于它也是对一组数据进行分析；但是，窗口函数不是将一组数据汇总为单个结果；而是针对查询中的每一行数据，基于和它相关的一组数据计算出一个结果，如下图所示：

2) 窗口函数的使用

窗口函数的使用方法如下：

1
2
3
4
5
window_function ( expr ) OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [frame_clause]
)

其中，window_function是窗口函数的名称；expr是参数，有些函数不需要参数；OVER子句包含三个选项：分区（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）。

PARTITON BY用来分组，即选择要看哪个窗口，类似于 GROUP BY 子句的分组功能，但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。

ORDER BY用来排序，即决定窗口内，是按那种规则(字段)来排序的。

举个栗子:

1
2
3
4
5
6
SELECT product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (PARTITION BY product_type
                         ORDER BY sale_price) AS ranking
  FROM product;  

得到的结果是:

PARTITION BY能够设定窗口对象范围。在上面的例子中，为了按照商品种类进行排序，指定了product_type。即一个商品种类就是一个小的"窗口"。

ORDER BY能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，指定了sale_price。此外，窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样，可以通过关键字ASC(默认)或DESC来指定升序/降序。

3) 窗口函数种类

大致来说，窗口函数可以分为两类：

  1. 将SUM、MAX、MIN等聚合函数用在窗口函数中；

  2. RANK、DENSE_RANK等排序用的专用窗口函数。

a. 专用窗口函数

  • RANK函数

    计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

    例如：有3条记录排在第 1 位时：1 位、1 位、1 位、4 位……

  • DENSE_RANK函数

    同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

    例如：有3条记录排在第 1 位时：1 位、1 位、1 位、2 位……

  • ROW_NUMBER函数

    赋予唯一的连续位次。

    例如：有3条记录排在第 1 位时：1 位、2 位、3 位、4 位

例子如下：

1
2
3
4
5
6
7
SELECT  product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (ORDER BY sale_price) AS ranking
       ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
       ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product;  

结果如下图：

b. 聚合函数在窗口函数上的使用

聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。

例子：

1
2
3
4
5
6
SELECT  product_id
       ,product_name
       ,sale_price
       ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  
  FROM product;  

由上图可以看出，聚合函数结果是，按我们指定的排序（这里是product_id）进行的当前所在行及之前所有的行的合计或均值，即累计到当前行的聚合。

2. GROUPING运算符

1) ROLLUP - 计算合计及小计

常规的GROUP BY只能得到每个分类的小计，有时候还需要计算分类的合计，可以用ROLLUP关键字。

1
2
3
4
5
SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type, regist_date WITH ROLLUP;  

得到的结果为：

这里ROLLUP对product_type、 regist_date两列进行合计汇总。结果实际上有三层聚合，如下图中模块3是常规的 GROUP BY的结果，需要注意的是衣服有个注册日期为空的，这是本来数据就存在日期为空的，不是对衣服类别的合计； 模块2和1是ROLLUP带来的合计，模块2是对产品种类的合计，模块1是对全部数据的总计。

3. 存储过程和函数

1) 什么是存储过程

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

2) 创建存储过程

使用CREATE PROCEDURE语句创建存储过程，语法格式如下：

1
2
3
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

上述格式内容解释如下：

  1. 过程名

    存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。

    需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

  2. 过程参数

    存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。

    MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用IN、OUT和INOUT三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

    需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。

  3. 过程体

    存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字END结束。若存储过程体中只有一条 SQL 语句，则可以省略BEGIN-END标志。

关于IN，OUT，INOUT参数，其中：

  • IN 是入参。每个参数默认都是一个 IN 参数。如需设定一个参数为其他类型参数，请在参数名称前使用关键字 OUT 或 INOUT 。一个IN参数将一个值传递给一个过程。存储过程可能会修改这个值，但是当存储过程返回时，调用者不会看到这个修改。

  • OUT 是出参。一个 OUT 参数将一个值从过程中传回给调用者。它的初始值在过程中是 NULL ，当过程返回时，调用者可以看到它的值。

  • INOUT ：一个 INOUT 参数由调用者初始化，可以被存储过程修改，当存储过程返回时，调用者可以看到存储过程的任何改变。

对于每个 OUT 或 INOUT 参数，在调用过程的 CALL 语句中传递一个用户定义的变量，以便在过程返回时可以获得其值。如果你是在另一个存储过程或函数中调用存储过程，你也可以将一个常规参数或本地常规变量作为 OUT 或 INOUT 参数传递。如果从一个触发器中调用存储过程，也可以将 NEW.col_name 作为一个 OUT 或 INOUT 参数传递。

3) 使用示例

  • 查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    mysql> DELIMITER //
    mysql> DROP PROCEDURE IF EXISTS citycount //
    Query OK, 0 rows affected (0.01 sec)

    mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
        BEGIN
            SELECT COUNT(*) INTO cities FROM world.city
            WHERE CountryCode = country;
        END//
    Query OK, 0 rows affected (0.01 sec)

    mysql> DELIMITER ;
    mysql> CALL citycount('CHN', @cities); -- cities in China
    Query OK, 1 row affected (0.01 sec)

        -> SELECT @cities;
    +---------+
    | @cities |
    +---------+
    |     363 |
    +---------+
    1 row in set (0.04 sec)

    <!-- 该查询显示了一个简单的存储过程，给定一个国家代码，计算在 `world` 数据库的城市表中出现的该国家的城市数量。使用 `IN` 参数传递国家代码，使用 `OUT` 参数返回城市计数。 -->

  • 创建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    mysql> use world;
    Database changed
    mysql> DELIMITER $$
    mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `product_test`()
    BEGIN
        #Routine body goes here...
        CREATE TABLE product_test like shop.product;
    END$$
    Query OK, 0 rows affected (0.01 sec)

    mysql> DELIMITER;
    mysql> call `product_test`();
    Query OK, 0 rows affected (0.04 sec)

    mysql> show tables;
    +-----------------+
    | Tables_in_world |
    +-----------------+
    | city            |
    | country         |
    | countrylanguage |
    | product_test    |
    +-----------------+
    4 rows in set (0.02 sec)

  • 插入数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_product_test`()
    BEGIN
        declare i int;
        set i=1;
        while i<9 do
            set @pcid = CONCAT('000', i);
            PREPARE stmt FROM 'INSERT INTO product_test() SELECT * FROM shop.product where product_id= ?';
            EXECUTE stmt USING @pcid;
            set i=i+1;
        end while;
    END

4. 预处理声明PREPARE Statement

MySQL从4.1版本开始引入了 PREPARE Statement 特性，使用 client/server binary protocol 代替 textual protocol，其将包含占位符的查询传递给 MySQL 服务器，如以下示例所示：

1
2
3
SELECT * 
FROM products 
WHERE productCode = ?;

当MySQL使用不同的 productCode 值执行此查询时，它不必完全解析查询。因此，这有助于MySQL更快地执行查询，特别是当MySQL多次执行相同的查询时。

由于预准备语句使用占位符，这有助于避免 SQL 注入的许多变体，从而使应用程序更安全。

基本语法如下：

1
PREPARE statement_name FROM preparable_statement

1) 使用步骤

MySQL PREPARE Statement 使用步骤如下：

  1. PREPARE – 准备需要执行的语句预处理声明。

  2. EXECUTE – 执行预处理声明。

  3. DEALLOCATE PREPARE – 释放预处理声明。

下图说明了预处理声明的使用过程：

2) 使用示例

这里使用数据库shop 中的表 product 表进行演示。

首先，定义预处理声明如下：

1
2
3
4
5
6
PREPARE stmt1 FROM 
	'SELECT 
   	    product_id, 
            product_name 
	FROM product
        WHERE product_id = ?';

其次，声明变量 pcid，代表商品编号，并将其值设置为 0005：

1
SET @pcid = '0005';

第三，执行预处理声明：

1
EXECUTE stmt1 USING @pcid;

第四，为变量 pcid 分配另外一个商品编号：

1
SET @pcid = '0008';

第五，使用新的商品编号执行预处理声明：

1
EXECUTE stmt1 USING @pcid;

最后，释放预处理声明以释放其占用的资源：

1
DEALLOCATE PREPARE stmt1;

参考

https://github.com/datawhalechina/wonderful-sql

https://juejin.cn/post/7031157682434211853

http://m.biancheng.net/view/7234.html