变量
局部变量
位于 begin … end 中，语句块执行完后即结束生命周期
- 声明局部变量
declare var [, …] varType [defualt initVal];
默认初始值为null，后续通过set赋值
必须放于begin…end函数体最前面的位置
用户变量
定义在当前客户端的连接下的变量，连接断开后结束生命周期。
- 无需声明，直接通过
set赋值使用即可。
用户变量的变量名必须以@开头
查看一个不存在的用户变量时返回null
变量赋值
- set赋值
SET @a = 100, @b = 200;
SET @c :=300;
SELECT @a , @b , @c;
- select 赋值
SELECT @c :=300;
- select into 赋值
通过select语句将所查询出的字段数据依次赋值到 into 后的变量中
select num, price into data1, data2 from test2 where num = idx;
临时表
临时表只在当前连接可见（类似用户变量），连接断开后生命周期结束
创建临时表
- 通过
CREATE TEMPORARY TABLE创建的临时表
同一连接中重复创建，会导致报错（Table '临时表名' already exists），因此需配合if not exist或DROP TEMPORARY TABLE IF Exists使用
drop TEMPORARY Table IF Exists temp_table_name;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_name AS
(
SELECT * FROM admit_record
);
select * from temp_table_name;
通过
select into创建的临时表
MySQL不支持该语法，仅能用于变量赋值派生表
出现在from子句的独立子查询，称为派生表。
派生表必须具有别名，否则报错：Every derived table must have its own alias
SELECT
COUNT(*)
FROM
( SELECT DISTINCT gender FROM users ) AS user_gender;
- 物化表
出现在where子句的独立子查询，称为物化表。
SELECT
*
FROM
users
WHERE
gender IN ( SELECT gender FROM users2 WHERE gender = 1 );
删除临时表
以下两种都可以删除临时表，但推荐使用后者，防止误删永久表。
DROP TABLE 临时表名;
DROP TEMPORARY TABLE 临时表名;
流程控制
if
if condition then
statements
[ elseif condition then
statements ]
[ else
statements ]
end if;
case
CASE
WHEN condition THEN statements;
ELSE statements;
while
[label:] while condition do
statments
end while [label]
repeat
[label:] repeat
statments
until condition
end repeat [label]
loop
死循环，需在过程中使用leave退出
[label:] LOOP
statements
END LOOP [label]
leave 和 iterate
类似 break 和 continue，用于跳出循环
create
function testLeave()
returns varchar(255)
begin
declare i,j int default 0;
loop1: while (i<=5) do
set j = 0;
while (j<=i) do
if(j = 3) then
leave loop1;
end if;
set j = j + 1;
end while;
set i = i + 1;
end while loop1;
return concat('i: ', i, ' j:', j);
end;
函数与存储过程
模块化，方便复用，可在内部通过局部变量和流程控制语句实现复杂逻辑
避免了处理复杂逻辑时，数据从数据库和服务器的多次传输，所有操作都在数据库完成，提升了性能。
但因不方便调试，通常只用于数据逻辑。业务逻辑应在服务器端处理。
内置函数
SQL 拥有很多可用于计数和计算的内建函数。
- AVG
返回数值列的平均值。NULL 值不包括在计算中。 - COUNT
返回匹配指定条件的行数。通常写作count(*)或count(1)。如填具体列，则返回该字段不为null的行数 - FIRST
返回指定的字段中第一个记录的值。 - LAST
返回指定的字段中最后一个记录的值。 - MAX
返回一列中的最大值。NULL 值不包括在计算中。 - MIN
返回一列中的最小值。NULL 值不包括在计算中。 - SUM
返回数值列的总数。 - CONCAT 拼接
SELECT concat(admit_name,'-',admit_type) as "名称-类型",count(*) from admit_record
- UCASE
把字段的值转换为大写。 - LCASE
把字段的值转换为小写。 - MID （LEFT，RIGHT）
用于从文本字段中提取字符。
MID(字符串,开始位置[,截取长度,不填则到底])
LEFT(字符串,截取长度)
RIGHT(字符串,截取长度)
- LEN
返回文本值的长度。 - ROUND
用于把数值字段舍入为指定的小数位数。
SELECT ROUND(列名,小数位数) FROM table_name
- NOW / GETDATE()
返回当前的日期和时间。 - FORMAT
对字段的显示进行格式化。
SELECT FORMAT(column_name,format) FROM table_name
SELECT ProductName, UnitPrice, FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss') as PerDate
FROM Products
- VERSION
当前MySQL版本
select version();
窗口函数
MySQL8开始支持窗口函数函数，只能写在 select 子句中。
函数位置支持row_number、first_value等专用窗口函数，以及sum、count、max等聚合函数：<函数>() over (partition by <用于分组的列名> order by <用于排序的列名>)
示例，输出结果按type分类并排序，且额外输出一列每个type内部按value排序的序号：
SELECT *, max(value) over ( PARTITION BY `type` ORDER BY `value` ASC ) FROM `test`;
自定义函数
通过function声明
函数体中必须通过return返回一个值。当函数体为复合结构时，需使用begin/end 语句。
和内置函数一样，可以直接在SQL语句中通过函数名()使用
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION functionName ( varName varType [, ... ] )
RETURNS returnVarType
[characteristic ...]
routine_body
CREATE FUNCTION hello(param int) RETURNS int
DETERMINISTIC
BEGIN
RETURN (select sum(advert_banner.id) from advert_banner) + param;
END;
select hello(9999)
- characteristic 配置
MySQL强制要求：开启bin-log后，在主服务器上，除非子程序（函数、存储过程、触发器）被声明为确定性的或者不更改数据，否则创建或者替换子程序将报错（因不确定的数据改变会导致主从服务器执行结果不同）。
因此创建子程序时应指定特性为DETERMINISTIC或NO SQL或READS SQL DATA：- NOT DETERMINISTIC 默认值，表示不确定
- DETERMINISTIC 确定
- NO SQL 没有SQL语句
- CONTAINS SQL 有SQL语句
- READS SQL DATA 只读
- MODIFIES SQL 会改变数据
存储过程
通过procedure声明
通过call 存储过程名调用
通过IN、OUT、INOUT定义入参和返回值
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])`
begin
sql语句
end;
call 存储过程名;
- IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
- OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
- INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
一些注意点
替换结束符
命令行中;作为结束符会执行语句。为了防止函数/存储过程在定义时，其过程体中的;执行语句，应使用delimiter暂时修改结束符。删除函数/存储过程
函数/存储过程在声明后会永久存在于数据库中，无需重复使用的函数/存储过程应在调用后通过drop删除。
此外为防止重名问题，可在声明前先进行删除操作。此时需添加if exists避免因不存在而报错。
DELIMITER $$
DROP PROCEDURE IF EXISTS test_two $$
CREATE PROCEDURE test_two()
BEGIN
DECLARE i INT DEFAULT 2;
WHILE i < 17 DO
INSERT INTO `role_permission_group` (`role_id`, `pergroup_id`) VALUES ('1',i);
SET i = i+1;
end WHILE;
END$$
DELIMITER ;
CALL test_two();
DROP PROCEDURE test_two;
事件
事件是MySQL自带的功能，用于执行定时任务。
- 查看已开启事件功能
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
- 启用事件功能
SET GLOBAL event_scheduler = ON;
并在配置文件中添加event_scheduler=ON并重启，否则mysql重启后事件重置。
- 编写事件
CREATE EVENT IF NOT EXISTS test123 ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO ceshisy(lpname) VALUES(NOW());
