正文 

当前位置： 首页 > 代码 > java教程

mysql8一文读懂存储过程(代码片段)

在奋斗的大道 在奋斗的大道     2022-12-09     556

关键词：

1、存储过程语法结构

create procedure 存储过程名称(参数)
begin
    存储过程执行体
end

2、变量及其赋值

变量主要分为三大类：局部变量、用户变量和系统变量。

局部变量

局部变量特效：用户自定义，在begin/end块中有效。

局部变量语法：declare 局部变量名称 局部变量数据类型  default 默认值；

局部变量定义实例：

declare R_LSBH varchar(100) default '';

局部变量Set赋值

局部变量set 赋值语法：set 局部变量名 = 值；

局部变量set 赋值实例：

set error_number = error_number + 1;

局部变量Into赋值

局部变量into 赋值语法：into 局部变量名

局部变量into 赋值实例：

-- 判断计算局部变量R_YWBH 字符串的长度,执行前提判断R_YWBH 是否为空，为空赋值为''字符串，再将得到R_YWBH 长度赋值给变量YWBH_LENGTH
select LENGTH(IFNULL(R_YWBH , '')) into YWBH_LENGTH;

用户变量

用户变量特性：

  •         用户自定义，当前会话（连接）有效。
  •         不需要提前声明，使用即声明。

用户变量语法: @用户变量名

用户变量 定义实例：

create procedure procedure_test()
begin
    -- 定义用户变量且使用set 赋值
    set @intNow = 1;
    -- 定义用户变量且使用into 赋值
    select now() into @dataTime
end

存储过程调用，并输出当前执行会话的用户变量值。

-- 调用存储过程
call procedure_test();

-- 输出当前会话定义用户变量
select @intNow;
select @dataTime;

全局变量

全局变量特性：由系统提供，整个MySQL服务器有效。

全局变量语法: @@global.全局变量名

全局变量定义实例：

create procedure procedure_test()
begin
    -- 查询指定系统全局变量的值
    select @@global.character_set_datebase;
end

存储过程调用，并输出指定全局变量的值

call procedure_test();

-- 输出的值
utf8mb4

-- 温馨提示:查看全局变量中变量名有char的记录
show global variables like '%char%';

3、出参和入参

语法规则：in|out|inout  参数名   参数类型

入参实例

create procedure procedure_in(in parame_in varchar(10))
begin
    -- 输出入参值
    select concat('入参参数(parame_in):', parame_in);
end

调用存储过程，输出存储过程结果

-- 调用存储过程
call procedure_in('10');

-- 存储过程结果输出
入参参数(parame_in):10

     

出参实例

create procedure procedure_out(out parame_out varchar(10))
begin
    -- 设置出参值
    set parame_out  = '10';
end

 调用存储过程，输出存储过程结果

-- 定义用户变量
set @parame_out = '';
-- 调用存储过程
call proceduce_out(@parame_out);
-- 查询存储过程out 值
select @parame_out;

出入参实例

create procedure procedure_in_out(inout parame_in_out varchar(10))
begin
    -- 设置出入参值
    set parame_in_out = concat('出入参值:', parame_in_out );
end

 调用存储过程，输出存储过程结果

-- 定义用户变量
set @parame_in_out = '10';
-- 调用存储过程
call proceduce_in_out(@parame_in_out);
-- 查询存储过程out 值
select @parame_in_out;

4、流程控制

IF语句

语法结构：

IF 条件1 THEN 语句序列1
[ELSEIF 条件2 THEN 语句序列2]
...
[ELSE 语句序列n]
END IF

语法实例：

create procedure procedure_test(in score int)
begin
    if score <= 60 then
        select '成绩:D';
    elseif score > 60 and score <= 70 then
        select '成绩:C';
    elseif score > 70 and score <= 80 then
        select '成绩:B';
    elseif score > 80 and score <= 90 then
        select '成绩:A';
    else
        select '成绩:A+';
    end if;
end

CASE语句

语法结构：

CASE
WHEN 条件1 THEN 语句序列1
[WHEN 条件2 THEN 语句序列2]
...
[ELSE 语句序列n]
END CASE

语法实例：

create procedure procedure_test(in score int)
begin
    case
    when score <= 60 then
        select '成绩:D';
    when score > 60 and score <= 70 then
        select '成绩:C';
    when score > 70 and score <= 80 then
        select '成绩:B';
    when score > 80 and score <= 90 then
        select '成绩:A';
    else
        select '成绩:A+';
    end case;
end

循环之LOOP 

需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出。

可以把leave看成我们java中的break；与之对应的，就有iterate（继续循环）——类比java的continue

语法结构：

[开始标号:]LOOP
程序段
END LOOP[结束标号]

 语法实例：

create procedure procedure_test()
begin
    declare num int default 0;
    cnt:Loop
        IF num >= 10 then
            select '退出Loop 循环';
            Leave cnt;
        END IF;
        
        SET num = num + 1;
        select concat('当前num值为',num);
    END Loop cnt;
end

业务实例：基于Loop 遍历指定表的查询记录

create procedure procedure_test()
begin
    -- 定义行记录字段
    declare R_ID varchar(100) default '';
    -- 定义游标遍历时,判断是否遍历完全部记录
    declare no_more_row int default 0;
    -- 定义查询语句游标
    declare c_result cursor for select ID  from HOUSE;
    -- 声明当游标遍历全部记录后将退出变量标记为1
    declare continue handler for not found set no_more_row = 1;

    -- 打开游标
    open c_result;

    cnt:Loop
        -- 读取行记录
        fetch c_result into R_ID;
        -- 判断游标是否到底，到底则退出
        IF no_more_row = 1 then
            select '退出Loop 循环';
            Leave cnt;
        END IF;
        select R_ID;
    END Loop cnt;
end

循环之REPEAT

语法结构：

[开始标号:]REPEAT
程序段
UNTIL 条件
END REPEAT[结束标号]

语法实例：

create procedure procedure_test()
begin
    declare num int default 0;
    cnt:Repeat
        
        SET num = num + 1;
        select concat('当前num值为',num);
        
        until num >= 10
    END Repeat cnt;
end

业务实例：基于Repeat遍历指定表的查询记录

create procedure procedure_test()
begin
    -- 定义行记录字段
    declare R_ID varchar(100) default '';
    -- 定义游标遍历时,判断是否遍历完全部记录
    declare no_more_row int default 0;
    -- 定义查询语句游标
    declare c_result cursor for select ID  from HOUSE;
    -- 声明当游标遍历全部记录后将退出变量标记为1
    declare continue handler for not found set no_more_row = 1;

    -- 打开游标
    open c_result;

    cnt:Repeat
        -- 读取行记录
        fetch c_result into R_ID;
       
        select R_ID;

         -- 判断游标是否到底，到底则退出
        UNTIL no_more_row = 1
    END Repeat cnt;
end

温馨提示：不推荐使用此种方式遍历Select集合，因为最后一条记录会重复读取。

循环之WHILE

语法结构：

[开始标号:]WHILE条件 DO
程序段
END WHILE[结束标号]

语法实例：

create procedure procedure_test()
begin
    declare num int default 0;
    while num < 10 do
        SET num = num + 1;
        select concat('当前num值为',num);
    END while;
end

5、运算符

算术运算符

运算符作用
+加法
-减法
*乘法
/ 或 DIV除法
% 或 MOD取余

比较运算符 

符号描述备注
=等于
<>, !=不等于
>大于
<小于
<=小于等于
>=大于等于
BETWEEN在两值之间>=min&&<=max
NOT BETWEEN不在两值之间
IN在集合中
NOT IN不在集合中
<=>严格比较两个NULL值是否相等两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE模糊匹配
REGEXP 或 RLIKE正则式匹配
IS NULL为空
IS NOT NULL不为空

逻辑运算符  

运算符号作用
NOT 或 !逻辑非
AND逻辑与
OR逻辑或
XOR逻辑异或

 6、函数

字符串函数

函数描述实例
ASCII(s)返回字符串 s 的第一个字符的 ASCII 码。

返回 CustomerName 字段第一个字母的 ASCII 码：

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;
CHAR_LENGTH(s)返回字符串 s 的字符数

返回字符串 RUNOOB 的字符数

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CHARACTER_LENGTH(s)返回字符串 s 的字符数，等同于 CHAR_LENGTH(s)

返回字符串 RUNOOB 的字符数

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2...sn)字符串 s1,s2 等多个字符串合并为一个字符串

合并多个字符串

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn)同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符

合并多个字符串，并添加分隔符：

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2...)返回第一个字符串 s 在字符串列表(s1,s2...)中的位置

返回字符串 c 在列表值中的位置：

SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置

返回字符串 c 在指定字符串中的位置：

SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n)函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。

格式化数字 "#,###.##" 形式：

SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56
INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

从字符串第一个位置开始的 6 个字符替换为 runoob：

SELECT INSERT("google.com", 1, 6, "runoob");  -- 输出：runoob.com
LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置

获取 b 在字符串 abc 中的位置：

SELECT LOCATE('st','myteststring');  -- 5

返回字符串 abc 中 b 的位置：

SELECT LOCATE('b', 'abc') -- 2
LCASE(s)将字符串 s 的所有字母变成小写字母

字符串 RUNOOB 转换为小写：

SELECT LCASE('RUNOOB') -- runoob
LEFT(s,n)返回字符串 s 的前 n 个字符

返回字符串 runoob 中的前两个字符：

SELECT LEFT('runoob',2) -- ru
LOWER(s)将字符串 s 的所有字母变成小写字母

字符串 RUNOOB 转换为小写：

SELECT LOWER('RUNOOB') -- runoob
LPAD(s1,len,s2)在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len

将字符串 xx 填充到 abc 字符串的开始处：

SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s)去掉字符串 s 开始处的空格

去掉字符串 RUNOOB开始处的空格：

SELECT LTRIM("    RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len)从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)

从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：

SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置

返回字符串 abc 中 b 的位置：

SELECT POSITION('b' in 'abc') -- 2
REPEAT(s,n)将字符串 s 重复 n 次

将字符串 runoob 重复三次：

SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
REPLACE(s,s1,s2)将字符串 s2 替代字符串 s 中的字符串 s1

将字符串 abc 中的字符 a 替换为字符 x：

SELECT REPLACE('abc','a','x') --xbc
REVERSE(s)将字符串s的顺序反过来

将字符串 abc 的顺序反过来：

SELECT REVERSE('abc') -- cba
RIGHT(s,n)返回字符串 s 的后 n 个字符

返回字符串 runoob 的后两个字符：

SELECT RIGHT('runoob',2) -- ob
RPAD(s1,len,s2)在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len

将字符串 xx 填充到 abc 字符串的结尾处：

SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s)去掉字符串 s 结尾处的空格

去掉字符串 RUNOOB 的末尾空格：

SELECT RTRIM("RUNOOB     ") AS RightTrimmedString;   -- RUNOOB
SPACE(n)返回 n 个空格

返回 10 个空格：

SELECT SPACE(10);
STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

比较字符串：

SELECT STRCMP("runoob", "runoob");  -- 0
SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串

从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length)

从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING_INDEX(s, delimiter, number)返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。		 
SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1)	-- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)	-- c
TRIM(s)去掉字符串 s 开始和结尾处的空格

去掉字符串 RUNOOB 的首尾空格：

SELECT TRIM('    RUNOOB    ') AS TrimmedString;
UCASE(s)将字符串转换为大写

将字符串 runoob 转换为大写：

SELECT UCASE("runoob"); -- RUNOOB
UPPER(s)将字符串转换为大写

将字符串 runoob 转换为大写：

SELECT UPPER("runoob"); -- RUNOOB

数字函数

函数名描述实例
ABS(x)返回 x 的绝对值　　

返回 -1 的绝对值：

SELECT ABS(-1) -- 返回1
ACOS(x)求 x 的反余弦值（单位为弧度），x 为一个数值 
SELECT ACOS(0.25);
ASIN(x)求反正弦值（单位为弧度），x 为一个数值 
SELECT ASIN(0.25);
ATAN(x)求反正切值（单位为弧度），x 为一个数值 
SELECT ATAN(2.5);
ATAN2(n, m)求反正切值（单位为弧度） 
SELECT ATAN2(-0.8, 2);
AVG(expression)返回一个表达式的平均值，expression 是一个字段

返回 Products 表中Price 字段的平均值：

SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)返回大于或等于 x 的最小整数　 
SELECT CEIL(1.5) -- 返回2
CEILING(x)返回大于或等于 x 的最小整数　 
SELECT CEILING(1.5); -- 返回2
COS(x)求余弦值(参数是弧度) 
SELECT COS(2);
COT(x)求余切值(参数是弧度) 
SELECT COT(6);
COUNT(expression)返回查询的记录总数，expression 参数是一个字段或者 * 号

返回 Products 表中 products 字段总共有多少条记录：

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)将弧度转换为角度　　 
SELECT DEGREES(3.1415926535898) -- 180
n DIV m整除，n 为被除数，m 为除数

计算 10 除于 5：

SELECT 10 DIV 5;  -- 2
EXP(x)返回 e 的 x 次方　　

计算 e 的三次方：

SELECT EXP(3) -- 20.085536923188
FLOOR(x)返回小于或等于 x 的最大整数　　

小于或等于 1.5 的整数：

SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)返回列表中的最大值

返回以下数字列表中的最大值：

SELECT GREATEST(3, 12, 34, 8, 25); -- 34

返回以下字符串列表中的最大值：

SELECT GREATEST("Google", "Runoob", "Apple");   -- Runoob
LEAST(expr1, expr2, expr3, ...)返回列表中的最小值

返回以下数字列表中的最小值：

SELECT LEAST(3, 12, 34, 8, 25); -- 3

返回以下字符串列表中的最小值：

SELECT LEAST("Google", "Runoob", "Apple");   -- Apple
LN返回数字的自然对数，以 e 为底。

返回 2 的自然对数：

SELECT LN(2);  -- 0.6931471805599453
LOG(x) 或 LOG(base, x)返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。　　 
SELECT LOG(20.085536923188) -- 3
SELECT LOG(2, 4); -- 2
LOG10(x)返回以 10 为底的对数　　 
SELECT LOG10(100) -- 2
LOG2(x)返回以 2 为底的对数

返回以 2 为底 6 的对数：

SELECT LOG2(6);  -- 2.584962500721156
MAX(expression)返回字段 expression 中的最大值

返回数据表 Products 中字段 Price 的最大值：

SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)返回字段 expression 中的最小值

返回数据表 Products 中字段 Price 的最小值：

SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)返回 x 除以 y 以后的余数　

5 除于 2 的余数：

SELECT MOD(5,2) -- 1
PI()返回圆周率(3.141593）　　 
SELECT PI() --3.141593
POW(x,y)返回 x 的 y 次方　

2 的 3 次方：

SELECT POW(2,3) -- 8
POWER(x,y)返回 x 的 y 次方　

2 的 3 次方：

SELECT POWER(2,3) -- 8
RADIANS(x)将角度转换为弧度　　

180 度转换为弧度：

SELECT RADIANS(180) -- 3.1415926535898
RAND()返回 0 到 1 的随机数　　 
SELECT RAND() --0.93099315644334
ROUND(x)返回离 x 最近的整数 
SELECT ROUND(1.23456) --1
SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1　 
SELECT SIGN(-10) -- (-1)
SIN(x)求正弦值(参数是弧度)　　 
SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x)返回x的平方根　　

25 的平方根：

SELECT SQRT(25) -- 5
SUM(expression)返回指定字段的总和

计算 OrderDetails 表中字段 Quantity 的总和：

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)求正切值(参数是弧度) 
SELECT TAN(1.75);  -- -5.52037992250933
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） 
SELECT TRUNCATE(1.23456,3) -- 1.234

日期函数 

ADDDATE(d,n)计算起始日期 d 加上 n 天的日期 
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
ADDTIME(t,n)n 是一个时间表达式，时间 t 加上时间表达式 n

加 5 秒：

SELECT ADDTIME('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:16 (秒)

添加 2 小时, 10 分钟, 5 秒:

SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); 
-> 2020-06-15 11:44:26
CURDATE()返回当前日期 
SELECT CURDATE();
-> 2018-09-19
CURRENT_DATE()返回当前日期 
SELECT CURRENT_DATE();
-> 2018-09-19
CURRENT_TIME返回当前时间 
SELECT CURRENT_TIME();
-> 19:59:02
CURRENT_TIMESTAMP()返回当前日期和时间 
SELECT CURRENT_TIMESTAMP()
-> 2018-09-19 20:57:43
CURTIME()返回当前时间 
SELECT CURTIME();
-> 19:59:02
DATE()从日期或日期时间表达式中提取日期值 
SELECT DATE("2017-06-15");    
-> 2017-06-15
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数 
SELECT DATEDIFF('2001-01-01','2001-02-02')
-> -32
DATE_ADD(d，INTERVAL expr type)计算起始日期 d 加上一个时间段后的日期，type 值可以是：
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
 
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);    
-> 2017-06-25

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
-> 2017-06-15 09:49:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
->2017-06-15 06:34:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH);
->2017-04-15
DATE_FORMAT(d,f)按表达式 f的要求显示日期 d 
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type)函数从日期减去指定的时间间隔。

Orders 表中 OrderDate 字段减去 2 天：

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
DAY(d)返回日期值 d 的日期部分 
SELECT DAY("2017-06-15");  
-> 15
DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday 
SELECT DAYNAME('2011-11-11 11:11:11')
->Friday
DAYOFMONTH(d)计算日期 d 是本月的第几天 
SELECT DAYOFMONTH('2011-11-11 11:11:11')
->11
DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推 
SELECT DAYOFWEEK('2011-11-11 11:11:11')
->6
DAYOFYEAR(d)计算日期 d 是本年的第几天 
SELECT DAYOFYEAR('2011-11-11 11:11:11')
->315
EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值。
type可取值为：
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
 
SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') 
-> 11
FROM_DAYS(n)计算从 0000 年 1 月 1 日开始 n 天后的日期 
SELECT FROM_DAYS(1111)
-> 0003-01-16
HOUR(t)返回 t 中的小时值 
SELECT HOUR('1:2:3')
-> 1
LAST_DAY(d)返回给给定日期的那一月份的最后一天 
SELECT LAST_DAY("2017-06-20");
-> 2017-06-30
LOCALTIME()返回当前日期和时间 
SELECT LOCALTIME()
-> 2018-09-19 20:57:43
LOCALTIMESTAMP()返回当前日期和时间 
SELECT LOCALTIMESTAMP()
-> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year)基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 
SELECT MAKEDATE(2017, 3);
-> 2017-01-03
MAKETIME(hour, minute, second)组合时间，参数分别为小时、分钟、秒 
SELECT MAKETIME(11, 35, 4);
-> 11:35:04
MICROSECOND(date)返回日期参数所对应的微秒数 
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23
MINUTE(t)返回 t 中的分钟值 
SELECT MINUTE('1:2:3')
-> 2
MONTHNAME(d)返回日期当中的月份名称，如 November 
SELECT MONTHNAME('2011-11-11 11:11:11')
-> November
MONTH(d)返回日期d中的月份值，1 到 12 
SELECT MONTH('2011-11-11 11:11:11')
->11
NOW()返回当前日期和时间 
SELECT NOW()
-> 2018-09-19 20:57:43
PERIOD_ADD(period, number)为 年-月 组合日期添加一个时段 
SELECT PERIOD_ADD(201703, 5);   
-> 201708
PERIOD_DIFF(period1, period2)返回两个时段之间的月份差值 
SELECT PERIOD_DIFF(201710, 201703);
-> 7
QUARTER(d)返回日期d是第几季节，返回 1 到 4 
SELECT QUARTER('2011-11-11 11:11:11')
-> 4
SECOND(t)返回 t 中的秒钟值 
SELECT SECOND('1:2:3')
-> 3
SEC_TO_TIME(s)将以秒为单位的时间 s 转换为时分秒的格式 
SELECT SEC_TO_TIME(4320)
-> 01:12:00
STR_TO_DATE(string, format_mask)将字符串转变为日期 
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10
SUBDATE(d,n)日期 d 减去 n 天后的日期 
SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n)时间 t 减去 n 秒的时间 
SELECT SUBTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)
SYSDATE()返回当前日期和时间 
SELECT SYSDATE()
-> 2018-09-19 20:57:43
TIME(expression)提取传入表达式的时间部分 
SELECT TIME("19:30:10");
-> 19:30:10
TIME_FORMAT(t,f)按表达式 f 的要求显示时间 t 
SELECT TIME_FORMAT('11:11:11','%r')
11:11:11 AM
TIME_TO_SEC(t)将时间 t 转换为秒 
SELECT TIME_TO_SEC('1:12:00')
-> 4320
TIMEDIFF(time1, time2)计算时间差值 
mysql> SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
    ->                 '2000:01:01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
    ->                 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'
TIMESTAMP(expression, interval)单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 
mysql> SELECT TIMESTAMP("2017-07-23",  "13:10:11");
-> 2017-07-23 13:10:11
mysql> SELECT TIMESTAMP('2003-12-31');
        -> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
        -> '2004-01-01 00:00:00'
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差 
mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少天
        -> 89
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少月
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');    // 计算两个时间相隔多少年
        -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');  // 计算两个时间相隔多少分钟
        -> 128885
TO_DAYS(d)计算日期 d 距离 0000 年 1 月 1 日的天数 
SELECT TO_DAYS('0001-01-01 01:01:01')
-> 366
WEEK(d)计算日期 d 是本年的第几个星期，范围是 0 到 53 
SELECT WEEK('2011-11-11 11:11:11')
-> 45
WEEKDAY(d)日期 d 是星期几，0 表示星期一，1 表示星期二 
SELECT WEEKDAY("2017-06-15");
-> 3
WEEKOFYEAR(d)计算日期 d 是本年的第几个星期，范围是 0 到 53 
SELECT WEEKOFYEAR('2011-11-11 11:11:11')
-> 45
YEAR(d)返回年份 
SELECT YEAR("2017-06-15");
-> 2017
YEARWEEK(date, mode)返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 
SELECT YEARWEEK("2017-06-15");
-> 201724

高级函数 

函数名描述实例
BIN(x)返回 x 的二进制编码

15 的 2 进制编码:

SELECT BIN(15); -- 1111
BINARY(s)将字符串 s 转换为二进制字符串 
SELECT BINARY "RUNOOB";
-> RUNOOB
CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 
SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0
CAST(x AS type)转换数据类型

字符串日期转换为日期：

SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29
COALESCE(expr1, expr2, ...., expr_n)返回参数中的第一个非空表达式（从左向右） 
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');
-> runoob.com
CONNECTION_ID()返回唯一的连接 ID 
SELECT CONNECTION_ID();
-> 4292835
CONV(x,f1,f2)返回 f1 进制数变成 f2 进制数 
SELECT CONV(15, 10, 2);
-> 1111
CONVERT(s USING cs)函数将字符串 s 的字符集变成 cs 
SELECT CHARSET('ABC')
->utf-8    

SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbk
CURRENT_USER()返回当前用户 
SELECT CURRENT_USER();
-> guest@%
DATABASE()返回当前数据库名 
SELECT DATABASE();   
-> runoob
IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 
SELECT IF(1 > 0,'正确','错误')    
->正确
IFNULL(v1,v2)如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 
SELECT IFNULL(null,'Hello Word')
->Hello Word
ISNULL(expression)判断表达式是否为 NULL 
SELECT ISNULL(NULL);
->1
LAST_INSERT_ID()返回最近生成的 AUTO_INCREMENT 值 
SELECT LAST_INSERT_ID();
->6
NULLIF(expr1, expr2)比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 
SELECT NULLIF(25, 25);
->
SESSION_USER()返回当前用户 
SELECT SESSION_USER();
-> guest@%
SYSTEM_USER()返回当前用户 
SELECT SYSTEM_USER();
-> guest@%
USER()返回当前用户 
SELECT USER();
-> guest@%
VERSION()返回数据库的版本号 
SELECT VERSION()
-> 5.6.34

 7、游标

用游标得到某一个结果集，逐行处理数据。

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name

语法实例：

脚本语句：

CREATE TABLE `dept` (
	`deptno` INT(11) NOT NULL COMMENT '部门编号',
	`dname` VARCHAR(32) NULL COMMENT '部门名称' COLLATE 'utf8_general_ci',
	`loc` VARCHAR(64) NULL COMMENT '部门地址' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

	
CREATE TABLE `emp` (
	`empno` INT(11) NOT NULL COMMENT '员工编号',
	`ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci',
	`job` VARCHAR(10) NULL COMMENT '职位' COLLATE 'utf8_general_ci',
	`mgr` INT(11) NULL COMMENT '上级编号',
	`hiredate` DATE NOT NULL COMMENT '入职时间',
	`sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资',
	`comm` DECIMAL(7,2) NULL COMMENT '年终奖金',
	`deptno` INT(11) NOT NULL COMMENT '部门编号',
	PRIMARY KEY (`empno`) USING BTREE,
	INDEX `FK_emp_dept` (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

存储过程：

-- 按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
CREATE PROCEDURE sp_cursor01(IN dept_name VARCHAR(32))
BEGIN
	DECLARE e_no INT;
	DECLARE e_name VARCHAR(32);
	DECLARE e_sal DECIMAL(7,2);
	
	DECLARE lp_flag BOOLEAN DEFAULT TRUE;
	
	DECLARE emp_cursor CURSOR FOR 
		SELECT e.empno,e.ename,e.sal
		FROM emp e,dept d
		WHERE e.deptno = d.deptno AND d.dname = dept_name;
		
	-- handler 处理游标到最后时的行为
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET lp_flag = FALSE;
		
	OPEN emp_cursor;
	
	emp_loop:LOOP
		FETCH emp_cursor INTO e_no,e_name,e_sal;
		
		IF lp_flag THEN
			SELECT e_no,e_name,e_sal;
		ELSE
			LEAVE emp_loop;
		END IF;
		
	END LOOP emp_loop;
	SET @end_falg = 'exit_flag';
	CLOSE emp_cursor;
END

CALL sp_cursor01('RESEARCH');

 8、临时表

语法实例：


CREATE PROCEDURE sp_create_table02(IN dept_name VARCHAR(32))
BEGIN
	DECLARE emp_no INT;
	DECLARE emp_name VARCHAR(32);
	DECLARE emp_sal DECIMAL(7,2);
	DECLARE exit_flag INT DEFAULT 0;
	
	DECLARE emp_cursor CURSOR FOR
		SELECT e.empno,e.ename,e.sal
		FROM emp e INNER JOIN dept d ON e.deptno = d.deptno WHERE d.dname = dept_name;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_flag = 1;
	
	-- 创建临时表收集数据
	CREATE TEMPORARY TABLE `temp_table_emp` (
		`empno` INT(11) NOT NULL COMMENT '员工编号',
		`ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci',
		`sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资',
		PRIMARY KEY (`empno`) USING BTREE
	)
	COLLATE='utf8_general_ci'
	ENGINE=INNODB;	
	
	OPEN emp_cursor;
	
	c_loop:LOOP
		FETCH emp_cursor INTO emp_no,emp_name,emp_sal;
		
		
		IF exit_flag != 1 THEN
			INSERT INTO temp_table_emp VALUES(emp_no,emp_name,emp_sal); 
		ELSE
			LEAVE c_loop;
		END IF;
		
	END LOOP c_loop;
	
	SELECT * FROM temp_table_emp;
	
	SELECT @sex_res; -- 仅仅是看一下会不会执行到
	CLOSE emp_cursor;
	
END

CALL sp_create_table02('RESEARCH');

一文读懂mysql-万字长文，肝就完了(代码片段)

...没有权限，直接返回错误信息。如果有权限，在MySQL8.0版本以前，会先查询缓存。如果没有缓存，分析器进行词法分析，提取s 查看详情

一文读懂浏览器存储与缓存机制！(代码片段)

浏览器存储CookieCookie是HTTP协议的一种无状态协议。当请求服务器时，HTTP请求都需要携带Cookie，用来验证用户身份。Cookie由服务端生成，存储在客户端，用来维持状态。通常Cookie由以下值构成：名称（name&#x... 查看详情

一文读懂存储过程

0导读经常听别人说，“调用一个存储过程“，“把处理过程改写为存储过程之后就快了”此类的话，本篇文章我们来聊一聊存储过程。将从以下几个方面去描述存储过程。1存储过程解决了什么样的问题？我们看这样一个场景：... 查看详情

makefile入门(超详细一文读懂)(代码片段)

1、Makefile编译过程  Makefile文件中的命令有一定规范，一旦该文件编写好以后在Linux命令行中执行一条make命令即可自动编译整个工程。不同厂家的make可能会稍有不同，并且语法上也有区别，不过基本思想都差不多&#x... 查看详情

一文读懂java中的代理模式(代码片段)

代理（Proxy）模式是我们在工作中广泛使用的设计模式之一，提供了对目标对象额外的访问方式。通过代理对象来访问目标对象，可以对目标对象进行功能的增强，即扩展目标对象的功能。例如在Spring中，AOP就是使用动态代理来... 查看详情

mysql自定义函数一文读懂(代码片段)

MySQL函数含义：一组预先编译好的SQL语句的集合，理解成批处理语句1、提高代码的重用性2、简化操作3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。存储过程与函数区别：存储过程：... 查看详情

一文读懂数仓中的pg_stat(代码片段)

...也可以通过下面函数进行查询。本文分享自华为云社区《一文读懂pgstat》，作者：leapdb。GaussDB(DWS)在S 查看详情

一文读懂k8s持久化存储流程(代码片段)

作者|孙志恒（惠志）?阿里巴巴开发工程师<br/>导读：众所周知，K8s的持久化存储（PersistentStorage）保证了应用数据独立于应用生命周期而存在，但其内部实现却少有人提及。K8s?内部的存储流程到底是怎样的？PV、PVC、StorageCl... 查看详情

elasticsearch一文读懂(代码片段)

目录1、Elasticsearch简介2、Docker安装 Elasticsearch2.1使用Docker安装ElasticSearch7.6.22.2Elasticsearch目录详解2.3 使用Docker安装elasticSearch--head通过Chrome插件安装ElasticSearch-head  2.4了解ELKELK功能结构图Docker安装Kibana 3ElasticSe 查看详情

modelmapper一文读懂(代码片段)

目录1、ModelMapper简介1.1引入ModelMapper的依赖 1.2进行Mapping映射1.3ModelMapper工作原理 2、ModelMapper基础操作 2.1ModelMapper基本映射2.2  ModelMapper集合转换 2.3  ModelMapper指定默认值 2.4  ModelMapper属性值转换 2.5  ModelMapper属性值跳过 ... 查看详情

fastdfs一文读懂(代码片段)

目录FastDFS介绍FastDFS概念FastDFS作用FastDFS优缺点 FastDFS相关概念FastDFS原理FastDFS系统拓扑图FastDFS核心工作流程FastDFS文件上传FastDFS文件下载 Linux环境搭建FastDFSDocker环境搭建FastDFSSpringBoot封装FastDFS功能模块FastDFS介绍FastDFS概念FastDFS... 查看详情

一文读懂线程池的工作原理（故事白话文）(代码片段)

前言本文以程序员做需求的例子，比喻线程池的工作过程。以故事白话的方式展开，跟大家阐述线程池工作原理，以方便大家更好理解线程池，谢谢阅读哈~什么是线程池？什么是核心线程？什么是阻塞队列？什么是非核心线程... 查看详情

[转帖]一文读懂http/2(代码片段)

一文读懂HTTP/2http://support.upyun.com/hc/kb/article/1048799/又小拍 ? 发表于：2017年05月18日15:34:45 ? 更新于：2017年05月24日15:06:11今天，HTTP1.1已经变成互联网中主要的协议。但是在HTTP协议诞生初期却被认为是简单直接的协议... 查看详情

mapstruct一文读懂(代码片段)

目录1、MapStruct简介1.1MapStructMaven引入 2、MapStruct基础操作 2.1MapStruct基本映射 2.2MapStruct指定默认值2.3MapStruct表达式2.4MapStruct时间格式2.5MapStruct数字格式3、MapStruct组合映射3.1多参数源映射3.2使用其他参数值 3.3嵌套映射  3.4 逆... 查看详情

rabbitmq一文读懂(代码片段)

目录1、RabbitMQ介绍应用场景其他消息队列选择RabbitMQ原因2、AMQP消息队列其他相关知识什么是AMQP？什么是JMS？3、RabbitMQ快速入门RabbitMQ的工作原理RabbitMQ消息发送和接受流程梳理RabbitMQ消息发送RabbitMQ消息接受RabbitMQ安装Rabbi... 查看详情

html一文读懂(代码片段)

目录1、认识HTML2、HTML网页构成 HTML基本结构网页头部信息3、HTML基本标签 4、图像标签 5、链接标签6.行内元素和块元素7.列表 8.表格 9.媒体元素10.页面结构分析 11、HTML内联框架（iframe）12、初识表单表单元素属性13、... 查看详情

一文读懂mysqlexplain执行计划(代码片段)

...反馈的面试题如何排查慢SQL的强相关，索性先出一篇一文读懂MySQLExplain执行计划。Explain执行计划你一定得会看，不然你简历上就别去写什么你会SQL调优，不然面试官会觉得，Explain执行计划你都不会看，那你还... 查看详情

一文读懂mysqlexplain执行计划(代码片段)

...反馈的面试题如何排查慢SQL的强相关，索性先出一篇一文读懂MySQLExplain执行计划。Explain执行计划你一定得会看，不然你简历上就别去写什么你会SQL调优，不然面试官会觉得，Explain执行计划你都不会看，那你还... 查看详情