06 SQL高级开发-函数

# 1. 函数介绍

> https://dev.mysql.com/doc/refman/8.0/en/functions.html
https://c.biancheng.net/mysql/function/

概念

```sql
可以直接被另一段程序或代码引用的程序或代码，也叫方法

a(  
功能1
功能2
）
```
作用

```sql
1.提高代码重复性，提升开发效率
2.隐藏开发细节，方便接口调用
3.在MySQL中就是一段C语言的API功能
```

如何使用
```sql
select 函数名(参数)
```

大概分类
- 聚合函数
- 字符函数
- 数学函数
- 日期函数
- 流程控制函数

## 1.1.聚合函数

![image-20240319110932808](https://img.sunrisenan.com/img/2024/03/19/110935035.png)

## 1.2.字符串函数

![image-20240319111408989](https://img.sunrisenan.com/img/2024/03/19/111410574.png)

![image-20240319111508484](https://img.sunrisenan.com/img/2024/03/19/111510127.png)

## 1.3.数值函数

![image-20240319111827400](https://img.sunrisenan.com/img/2024/03/19/111829410.png)

## 1.4.日期函数

![image-20240319111935425](https://img.sunrisenan.com/img/2024/03/19/111937088.png)

![image-20240319112107484](https://img.sunrisenan.com/img/2024/03/19/112109288.png)

![image-20240319112213097](https://img.sunrisenan.com/img/2024/03/19/112214842.png)

## 1.5.流程控制函数

![image-20240319112607155](https://img.sunrisenan.com/img/2024/03/19/112608977.png)
![image-20240319112643745](https://img.sunrisenan.com/img/2024/03/19/112645566.png)

# 2.MySQL-变量

- 概念:一个拥有名字的对象，可以用于存储和共享数据。
- 分类
- 系统变量

```sql
1.系统变量（System Variable），由 MySQL 系统定义和维护的变量。
全局变量：从命令行或者配置文件获取
会话变量：客户端连接mysql服务端,复制全局变量给会话变量,断开连接销毁

#1.语法
SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]

#2.全局变量使用
-- 使用 LIKE 运算符返回了所有名字以 version 开头的全局变量
show global variables like 'version%';
-- 查询系统变量的值
select @@global.sql_mode;
-- 某个全局变量没有对应的会话变量
select @@max_connections;
--where条件
show global variables where Variable_name='wait_timeout';

--设置全局变量
set global max_connections = 1000;
set @@global.max_connections = 1000;

--持久化设置,不是所有参数都生效
set persist max_connections = 1000;
set @@persist.max_connections = 1000;

#3.会话变量使用,做测试
- 设置会话变量
set session sql_mode = 'TRADITIONAL';
set local sql_mode = 'TRADITIONAL';
set @@session.sql_mode = 'TRADITIONAL';
set @@local.sql_mode = 'TRADITIONAL';
set @@sql_mode = 'TRADITIONAL';
set sql_mode = 'TRADITIONAL';
```

- 用户变量：作用范围在当前会话之内，关闭连接时自动销毁；

```sql

performance_schema.user_variables_by_thread记录

-- 语法
SET @var_name = expr [, @var_name = expr] ...
set @employee_count = (select count(*) from employee);
set @user_name = 'qian long';
```

# 3.存储过程

- 需求:保存使用的一条或者多条SQL+逻辑控制,实际是函数
- 优点
    - 简单：简化复杂逻辑
    - 安全：减少对基础数据访问
    - 高性能:比单独SQL快
    - 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
- 缺点
    - 团队开发:脱离业务,DBA管理,不利于业务开发
    - 安全机制多余
    - 不支持面向对象的设计

## 3.1.基础应用

```sql
#语法
DELIMITER $$ 
CREATE
	PROCEDURE productpricing(参数列表) 
  BEGIN 
				过程体（1组SQL语句） 
  END$$ 
DELIMITER ;

#1.创建存储过程,无参数
DELIMITER //
CREATE 
	PROCEDURE  productpricing()
BEGIN
	select Avg(prod_price) as priceaverage
  FROM sql_test.products;
END // 
DELIMITER ;

-- 解释
DELIMITER // 声明新的解释分割符,如果不声明 语句products;就断了，出\都可以使用

--调用
CALL productpricing();

-- 删除
DROP PROCEDURE IF EXISTS productpricing;

#2.使用参数,将结果返回指定的变量
DELIMITER //
CREATE
	PROCEDURE  productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT min(prod_price) into pl from products;
	SELECT max(prod_price) INTO ph from products;
  SELECT avg(prod_price) INTO pa from products;
END //
DELIMITER ;

-- 解释
(0)OUT指参数从存储过程传出(返回调用者),IN指传入存储过程，INOUT(两个类型参数都有)
(1)这里接受了三个参数:p1存储价格最低,ph最高，pa平均
(2)每个参数必须指定类型,使用DECIMAL(8,2)2位小数,共8位精度

#调用存储过程，计算产品最高,最低和平均价格
CALL productpricing(@pl,@ph,@pa);

#使用变量
select @pl,@ph,@pa;

#3.使用IN和OUT参数，ordertotal接受订单号,并返回改订单号的总数
-- 声明存储过程和这个存储过程用到的变量
DELIMITER //
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)  -- 储存过程对外输入的变量
) COMMENT '获取加税之后的订单的总额'  -- 起到注释说明的作用
BEGIN
	-- 声明局部变量
	DECLARE total DECIMAL(8,2); 
	-- 指定局部变量的默认值,假定税率为6%
	DECLARE taxrate INT DEFAULT 6;
	
	SELECT SUM(item_price*quantity) 
	FROM orderitems
	WHERE order_num = onumber
	-- 把查询的结果存入声明的局部变量中
	INTO total;
	
	-- 如果含税的话,就需要计算税
	IF taxable  
		THEN
			SELECT total + (total/100*taxrate) INTO total;
	END IF;
	
	-- 把计算的总的结果放入对外的输出的变量中
	SELECT total INTO ototal;
END //
DELIMITER ;

-- 解释
(1)onumber 为IN,订单号呗传入
(2)ototal定义为OUT,从存储过程返回合计

-- 调用
CALL ordertotal(20005,0,@total)

--查看订单
SELECT @total;
```

## 3.2.高级应用-流程控制
包含业务规则和流程控制(if,case等)

```sql
#1.加上If判断条件的存储过程
DELIMITER //
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,  --布尔值,用来判断条件真假
	OUT ototal DECIMAL(8,2)  -- 储存过程对外输入的变量
) COMMENT '获取加税之后的订单的总额'  -- 起到注释说明的作用
BEGIN
	-- 声明局部变量
	DECLARE total DECIMAL(8,2); 
	-- 指定局部变量的默认值,假定税率为6%
	DECLARE taxrate INT DEFAULT 6;
	
	SELECT SUM(item_price*quantity) 
	FROM orderitems
	WHERE order_num = onumber
	-- 把查询的结果存入声明的局部变量中
	INTO total;
	
	-- 如果含税的话,就需要计算税
	IF taxable
		THEN
			SELECT total + (total/100*taxrate) INTO total;
	END IF;
	-- 把计算的总的结果放入对外的输出的变量中
	SELECT total INTO ototal;
END //
DELIMITER ;

-- 调用存储过程进行税率的计算,0代表false,代表不含税
CALL ordertotal(20005,0,@total)

-- 查询不含税的结果
SELECT @total;

-- 查询含税的结果
CALL ordertotal(20005,1,@total);
SELECT @total;
```

- 检查存储过程

```sql
-- 查看存储过程语句
show create procedure ordertotal;

-- 或者元信息
show procedure status like 'ordertotal';
```

## 3.3.存储过程-流程控制结构

> 1.流程控制与其他语言并无不同,懂得逻辑和例题思路测试即可。
MySQL追求轻量级,存储过程过于笨重.实际生产不会用到。        2.测试时候比如造数据 可以偶尔用一下

### 3.3.1.分支结构

**1.case**

```sql
方法1： 
case 变量|表达式|字段 
when 判断的值 then 结果或语句 
when 判断的值 then 结果或语句 ... 
else 结果或语句 end case;

方法2： 
case when 条件1 then 语句; 
when 条件2 then 语句; 
else 语句 
end case;
```

**2.if**

```sql
if 条件1 then 语句1; 
else if 
条件2 then 语句2; 
...; 
else 语句n; 
end if;
```

### 3.3.2.循环结构

- 满足条件,则重复执行代码,直到条件不满足或者遍历
- 分类: `while`  ,`loop` , `repeat`
- 循环控制:
    - `iterate`类似于 continue，继续，结束本次循环，继续下一次
    - `leave` 类似于 break，跳出，结束当前所在的循环
    - 只能放在`begin end`中
    
- while

```sql
[标签:] while 循环条件 do
     循环体;
 end while [标签];
```

批量插入，根据次数插入到admin表中多条记录

```sql
USE sql_practice;
 
 DELIMITER //
 CREATE OR REPLACE PROCEDURE test_while(IN total INT)
 BEGIN
 
 DECLARE i INT DEFAULT 0;
 
 WHILE i<total DO
     INSERT INTO admin VALUES(NULL,CONCAT('员工',i),'8888');
     SET i:=i+1;
 END WHILE;
 
 END //
```

## 3.4.游标

- 概念:
    - 不是SLEECT,是SELECT的结果集
    - 每次提取一条记录的机制,对查询数据库所返回的记录进行遍历和操作
- 特性
    - 不敏感:不复制结果集
    - 只读
    - 不滚动:向一个方向前进，不跳行
- 优点
    - 针对行操作,每一行可以独立进行操作.
- 缺点
    - 性能不高
    - 无法批量操作
    - 可能会产生死锁，内存开销大
- 适用场景
    - 存储过程
    
### 3.4.1.创建游标

```sql
#创建游标
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	-- 游标的使用
	-- 声明本地的变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INt;
	DECLARE t DECIMAL(8,2);

-- 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders; #结果集

-- 指定游标条件
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

-- 创建一张新表
	CREATE TABLE IF NOT EXISTS ordertotals (order_num INT ,total DECIMAL(8,2));

-- 打开游标
	OPEN ordernumbers;

-- 准备循环所有的行
	REPEAT
		-- 游标一行的数据放到变量o当中
		FETCH ordernumbers INTO o;  
		-- 调用提前准备好的存储函数
		-- o是存放游标数据的变量,1表示要计算税,t表示储存函数的输出值
		CALL ordertotal(o,1,t);
		-- 向创建的表中插入数据
		INSERT INTO ordertotals(order_num,total) VALUES(o,t);
	UNTIL done END REPEAT;

-- 关闭游标
	CLOSE ordernumbers;
END //
DELIMITER ;

#调用存储过程
CALL processorders();

#查询数据
SELECT * FROM ordertotals;
```

## 3.5.触发器

- 必须了解的概念,非常重要的DDL操作开源工具pt-osc,gh-osc都依赖触发器概念

```sql
pt-osc简易执行流程(留个坑)
1、创建一个和源表一样表结构的新表 a(1000W) a_new(0)
2、在新表执行DDL语句（空表嘛，所以。。。） a_new(0)
3、在源表创建三个触发器分别对应insert、update、delete操作
4、从源表拷贝数据到新表，拷贝过程中源表通过触发器把新的DML操作更新到新表中
5、rename源表到old表中，把新表rename为源表，默认最后删除源表
```

```sql
概念:触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

触发器的特性： 
1、有begin end体，begin end;之间的语句可以写的简单或者复杂,触发器名称唯一
2、条件:响应DELETE,INSERT,UPDATE语句自动执行一个SQL 
3、时机：在增删改前或者后(before/after) 
4、触发频率：针对每一行执行 
5、触发器定义在表上,视图临时表都不支持
6.每个表最多6个触发器
也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助应用在数 据库端确保数据的完整性。

四要素:
1.监视地点(table)
2.监视事件(insert/update/delete) 
3.触发时间(after/before) 
4.触发事件(insert/update/delete)
```

- 建议:业务禁用尽量少使用触发器逻辑
- 流量高的表禁用,消耗资源,花费内存。用触发器insert 500次就要触发500次触发器操作

- 语法

```sql
CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
　　[trigger_order]
trigger_body

-- 指定触发执行时间,事件之前还是之后
trigger_time: { BEFORE | AFTER }

--  FOR EACH ROW:对每一行触发器执行一次

-- 事件分类 
(1)INSERT:插入某一行时激活触发器（LOAD DATA和REPLACE均可）
(2)UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；
DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
```

1.INSERT触发器

```sql
#1.INSERT触发器
(1)创建新的new虚拟表,表示呗插入的行

CREATE TRIGGER neworder AFTER INSERT ON orders_bak
FOR EACH ROW SELECT NEW.order_num INTO @args;

-- 插入一条数据,由于我们使用了触发器,因此当产生新的自增长主键的时候
-- 会把自动增长的主键保存到我们定义的变量@args中
INSERT INTO orders (order_date,cust_id) VALUES(NOW(),10001);

-- 查询定义变量
select @args;
```

2.DELETE触发器

```sql
#从orders表删除数据的时候,把被删除的数据保存到archive_orders中(该表和orders的表结构相同)

create table archive_orders like orders;

DELIMITER //
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW  -- 表示该触发器作用于每一行
BEGIN
	INSERT INTO archive_orders(order_num,order_date,cust_id)
	VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);  
END //
DELIMITER ;

--验证
DELETE FROM orders where order_num=20005;

--验证记录
select * from archive_orders;
```

3.UPDATE触发器

```sql
#保证输入的所有的vend_state字段的数据都是大写的
create table vendors_bak like vendors;

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors_bak
FOR EACH ROW SET NEW.vend_name = Upper(NEW.vend_name);

insert into vendors_bak select * from vendors;
```

4.删除查看触发器

```sql
DROP TRIGGER updatevendor;
show TRIGGERS
```

## 3.6.事件管理器

- 有,但是不用,crontab或者异步调度任务模块

```sql
#1.介绍
(1)5.1.引进event概念,类似linxu crontab,精确到秒
(2)一般配合MySQL存储过程使用
(3)主要操作 创建、查询、修改、删除、开启和关闭

#2.语法
CREATE
    [DEFINER={user | CURRENT_USER}]
    EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

-- 参数说明
DEFINER    -- 可选，用于定义事件执行时检查权限的用户
IF NOT EXISTS    -- 可选，用于判断要创建的事件是否存在
EVENT event_name    -- 必选，用于指定事件名，event_name的最大长度为64个字符，如果为指定event_name，则默认为当前的MySQL用户名（不区分大小写）
ON SCHEDULE schedule    -- 必选，用于定义执行的时间和时间间隔
ON COMPLETION [NOT] PRESERVE    -- 可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
ENABLE | DISABLE | DISABLE ON SLAVE    -- 可选，用于指定事件的一种属性。
                                       -- 其中，关键字ENABLE表示该事件是活动的，也就是调度器检查事件是否必选调用；
                                       -- 关键字DISABLE表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用；
                                       -- 关键字DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它立即变为活动的。
COMMENT 'comment'    -- 可选，用于定义事件的注释 
DO event_body    -- 必选，用于指定事件启动时所要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN...END复合结构

#3.开启
SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1;
```

- 案例

```sql
-- 创建表
create table ev1 
(
ev_name varchar(20) not null, ev_started timestamp not null);

-- 立即启动事件
create event event_now on schedule at now() do insert into ev1 values('ev_test', now());

--（每分钟启动事件）
create event ev2 on schedule every 1 minute do insert into ev1 values('ev_test1', now());

-- （每秒钟调用存储过程）
CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus` ON SCHEDULE EVERY 1 SECOND STARTS '2017-11-21 00:12:44' ON COMPLETION PRESERVE ENABLE DO call updateStatus()

#删除事件
DROP EVENT IF EXISTS 事件名;

#查看事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
```