5.MySQL函数

转载：https://www.cnblogs.com/hellokuangshen/p/10261657.html

官方文档 : 官方文档

常用函数

分类: 数学函数 , 字符串函数 , 日期和时间函数 , 系统信息函数

# 数学函数 (这里只列出一些常用的)

SELECT ABS(-8);  /*绝对值*/

SELECT CEILING(9.4);  /*向上取整*/

SELECT FLOOR(9.4);  /*向下取整*/

SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/

SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/


#字符串函数

SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/

SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/

SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/

SELECT LOWER('KuangShen'); /*小写*/

SELECT UPPER('KuangShen'); /*大写*/

SELECT LEFT('hello,world',5);  /*从左边截取*/

SELECT RIGHT('hello,world',5);  /*从右边截取*/

SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/

SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/

SELECT REVERSE('狂神说坚持就能成功'); /*反转*/


#日期和时间函数

SELECT CURRENT_DATE();   /*获取当前日期*/
SELECT CURDATE();   /*获取当前日期*/

SELECT NOW();   /*获取当前日期和时间*/
SELECT LOCALTIME();   /*获取当前日期和时间*/
SELECT SYSDATE();   /*获取当前日期和时间*/

/*获取年月日,时分秒*/
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());


#系统信息函数
SELECT VERSION();  /*版本*/
SELECT USER();  /*用户*/


# 查询姓李的同学,改成立
SELECT REPLACE(studentname,'李','历') AS 新名字
FROM student WHERE studentname LIKE '李%';

 聚合函数

#聚合函数

/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;  -- Count(字段)，对null值的字段记录不加
SELECT COUNT(*) FROM student;　　-- Count(*)，不会忽略null值记录
SELECT COUNT(1) FROM student;  /*推荐*/  -- Count(1)，不会忽略null值记录和Count(*)差不多


SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;


# 查询不同课程的平均分,最高分,最低分
# 前提:根据不同的课程进行分组
/*分组和过滤一般连用group by ... having ...
group by : 分组之后，在每一组内进行查询
having : 对于每一分组查询的结果进行过滤(不用where）
*/

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno　　　　-- 通过什么字段分组，不然只能查询出一个，分组后没有组进行查询最高分，最低分和平均分
HAVING 平均分>80;　　　　　　 -- 过滤分组之后查询的条件

/*
where写在group by前面.
要是放在分组后面的筛选
要使用HAVING..
*/

 数据库级别的MD5加密（扩展）

• 主要目的是将用户密码加密
• MD5是不可逆的，具体指的MD5是一样的
• 网站破解MD5的密码是通过字典暴力破解

-- 更新
-- update 表名 set 字段名=MD5(字段名) 
update testmd5 set pwd=MD5(pwd);

-- 插入
insert into testmd5 VALUES(4,'小明',MD5('123456'));

-- 查询
select * from testmd5 where id=4 and pwd=MD5('123456');

 

 mysql之自定义函数

什么是函数：

    函数存储着一系列SQL语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。
    但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的SQL语句集合。

    函数与存储过程的区别：函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。

函数的创建：

/*语法*/
create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

/*参数列表的格式是：  变量名 数据类型*/

-- 最简单的仅有一条sql的函数
create function myselect2() returns int return 666;
select myselect2(); -- 调用函数

--
create function myselect3() returns int
begin 
    declare c int;
    select id from class where cname="python" into c;
    return c;
end;
select myselect3();
-- 带传参的函数
create function myselect5(name varchar(15)) returns int
begin 
    declare c int;
    select id from class where cname=name into c;
    return c;
end;
select myselect5("python");

补充：

    还可以有一些特别的选项，特别的选项写在RETURN 之后，BEGIN之前，如：
        COMMENT:一个关于函数的描述
         还有一些比如SQL SECURITY等选项，有兴趣可以自行百度。这里不讲解，仅一提有此知识点。

SQL编程

/* SQL编程 */ ------------------

--// 局部变量 ----------
-- 变量声明
    declare var_name[,...] type [default value] 
    这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个default子句。值可以被指定为一个表达式，不需要为一个常数。如果没有default子句，初始值为null。 

-- 赋值
    使用 set 和 select into 语句为变量赋值。

    - 注意：在函数内是可以使用全局变量（用户自定义的变量）


--// 全局变量 ----------
-- 定义、赋值
set 语句可以定义并为变量赋值。
set @var = value;
也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行，但是可以是多个字段，就意味着同时为多个变量进行赋值，变量的数量需要与查询的列数一致。
还可以把赋值语句看作一个表达式，通过select执行完成。此时为了避免=被当作关系运算符看待，使用:=代替。（set语句可以使用= 和 :=）。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;

select into 可以将表中查询获得的数据赋给变量。
    -| select max(height) into @max_height from tb;

-- 自定义变量名
为了避免select语句中，用户自定义的变量与系统标识符（通常是字段名）冲突，用户自定义变量在变量名前使用@作为开始符号。
@var=10;

    - 变量被定义后，在整个会话周期都有效（登录到退出）


--// 控制结构 ----------
-- if语句
if search_condition then 
    statement_list    
[elseif search_condition then
    statement_list]
...
[else
    statement_list]
end if;

-- case语句
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END


-- while循环
[begin_label:] while search_condition do
    statement_list
end while [end_label];

- 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。

    -- 退出循环
        退出整个循环 leave
        退出当前循环 iterate
        通过退出的标签决定退出哪个循环


--// 内置函数 ----------
-- 数值函数
abs(x)            -- 绝对值 abs(-10.9) = 10
format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x)            -- 向上取整 ceil(10.1) = 11
floor(x)        -- 向下取整 floor (10.1) = 10
round(x)        -- 四舍五入去整
mod(m, n)        -- m%n m mod n 求余 10%3=1
pi()            -- 获得圆周率
pow(m, n)        -- m^n
sqrt(x)            -- 算术平方根
rand()            -- 随机数
truncate(x, d)    -- 截取d位小数

-- 时间日期函数
now(), current_timestamp();     -- 当前日期时间
current_date();                    -- 当前日期
current_time();                    -- 当前时间
date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间
unix_timestamp();                -- 获得unix时间戳
from_unixtime();                -- 从时间戳获得时间

-- 字符串函数
length(string)            -- string长度，字节
char_length(string)        -- string的字符个数
substring(str, position [,length])        -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str)    -- 在str中用replace_str替换search_str
instr(string ,substring)    -- 返回substring首次在string中出现的位置
concat(string [,...])    -- 连接字串
charset(str)            -- 返回字串字符集
lcase(string)            -- 转换成小写
left(string, length)    -- 从string2中的左边起取length个字符
load_file(file_name)    -- 从文件读取内容
locate(substring, string [,start_position])    -- 同instr,但可指定开始位置
lpad(string, length, pad)    -- 重复用pad加在string开头,直到字串长度为length
ltrim(string)            -- 去除前端空格
repeat(string, count)    -- 重复count次
rpad(string, length, pad)    --在str后用pad补充,直到长度为length
rtrim(string)            -- 去除后端空格
strcmp(string1 ,string2)    -- 逐字符比较两字串大小

-- 流程函数
case when [condition] then result [when [condition] then result ...] [else result] end   多分支
if(expr1,expr2,expr3)  双分支。

-- 聚合函数
count()
sum();
max();
min();
avg();
group_concat()

-- 其他常用函数
md5();
default();


--// 存储函数，自定义函数 ----------
-- 新建
    CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型
        函数体

    - 函数名，应该合法的标识符，并且不应该与已有的关键字冲突。
    - 一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。
    - 参数部分，由"参数名"和"参数类型"组成。多个参数用逗号隔开。
    - 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。
    - 多条语句应该使用 begin...end 语句块包含。
    - 一定要有 return 返回值语句。

-- 删除
    DROP FUNCTION [IF EXISTS] function_name;

-- 查看
    SHOW FUNCTION STATUS LIKE 'partten'
    SHOW CREATE FUNCTION function_name;

-- 修改
    ALTER FUNCTION function_name 函数选项


--// 存储过程，自定义功能 ----------
-- 定义
存储存储过程 是一段代码（过程），存储在数据库中的sql组成。
一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。
而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。

-- 创建
CREATE PROCEDURE sp_name (参数列表)
    过程体

参数列表：不同于函数的参数列表，需要指明参数类型
IN，表示输入型
OUT，表示输出型
INOUT，表示混合型

注意，没有返回值。


/* 存储过程 */ ------------------
存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。
调用：CALL 过程名
-- 注意
- 没有返回值。
- 只能单独调用，不可夹杂在其他语句中

-- 参数
IN|OUT|INOUT 参数名 数据类型
IN        输入：在调用过程中，将数据输入到过程体内部的参数
OUT        输出：在调用过程中，将过程体处理完的结果返回到客户端
INOUT    输入输出：既可输入，也可输出

-- 语法
CREATE PROCEDURE 过程名 (参数列表)
BEGIN
    过程体
END