MySQL函数

MySQL函数

MySQL数据库提供了很多函数包括：

• 数学函数；
• 字符串函数；
• 日期和时间函数；
• 条件判断函数；
• 系统信息函数；
• 加密函数；
• 格式化函数；

常用函数有：

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

数学函数

数学函数主要用于处理数字，包括整型、浮点数等。

ABS(x)； -- 返回x的绝对值　　
SELECT ABS(-1)； -- 返回1
CEIL(x),CEILING(x)； 	-- 返回大于或等于x的最小整数　　
SELECT CEIL(1.5)； -- 返回2
FLOOR(x)； -- 返回小于或等于x的最大整数　　
SELECT FLOOR(1.5)； -- 返回1
RAND()； 	--  返回0->1的随机数　　
SELECT RAND()； -- 0.93099315644334
RAND(x)；-- 返回0->1的随机数，x值相同时返回的随机数相同　　
SELECT RAND(2)； -- 1.5865798029924
SIGN(x)；-- 返回x的符号，x是负数、0、正数分别返回-1、0和1　　
SELECT SIGN(-10)； -- (-1)
PI()；-- 返回圆周率(3.141593）　　
SELECT PI()； -- 3.141593
TRUNCATE(x,y)；-- 返回数值x保留到小数点后y位的值（与ROUND最大的区别是不会进行四舍五入）　　
SELECT TRUNCATE(1.23456,3)； -- 1.234
ROUND(x)； 	-- 返回离x最近的整数　　
SELECT ROUND(1.23456)； -- 1
ROUND(x,y)；-- 保留x小数点后y位的值，但截断时要进行四舍五入　　
SELECT ROUND(1.23456,3)； -- 1.235
POW(x,y).POWER(x,y)；-- 返回x的y次方　　
SELECT POW(2,3)； -- 8
SQRT(x)；-- 返回x的平方根　　
SELECT SQRT(25)； -- 5
EXP(x)；-- 返回e的x次方　　
SELECT EXP(3)； -- 20.085536923188
MOD(x,y)； 	-- 返回x除以y以后的余数　　
SELECT MOD(5,2)； -- 1
LOG(x)；-- 返回自然对数(以e为底的对数)　　
SELECT LOG(20.085536923188)； -- 3
LOG10(x)； 	-- 返回以10为底的对数　　
SELECT LOG10(100)； -- 2
RADIANS(x)； 	-- 将角度转换为弧度　　
SELECT RADIANS(180)； -- 3.1415926535898
DEGREES(x)； 	-- 将弧度转换为角度　　
SELECT DEGREES(3.1415926535898)； -- 180
SIN(x)；	-- 求正弦值(参数是弧度)　　
SELECT SIN(RADIANS(30))； -- 0.5
ASIN(x)； 	-- 求反正弦值(参数是弧度)
COS(x)； 	-- 求余弦值(参数是弧度)
ACOS(x)； 	-- 求反余弦值(参数是弧度)
TAN(x)； 	-- 求正切值(参数是弧度)
ATAN(x) ATAN2(x)； 	 -- 求反正切值(参数是弧度)
COT(x)； 	-- 求余切值(参数是弧度)

字符串函数

字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。

CHAR_LENGTH(s)；-- 返回字符串s的字符数
SELECT CHAR_LENGTH('你好123')； -- 5
LENGTH(s)； 	-- 返回字符串s的长度
SELECT LENGTH('你好123') -- 9
CONCAT(s1,s2,...)；-- 将字符串s1,s2等多个字符串合并为一个字符串
SELECT CONCAT('12','34')； -- 1234
CONCAT_WS(x,s1,s2,...)； 	-- 同CONCAT(s1,s2,...)函数，但是每个字符串直接要加上x
SELECT CONCAT_WS('@','12','34')； -- 12@34
INSERT(s1,x,len,s2)； 	-- 将字符串s2替换s1的x位置开始长度为len的字符串
SELECT INSERT('12345',1,3,'abc')； -- abc45
UPPER(s),UCAASE(S)； 	-- 将字符串s的所有字母变成大写字母
SELECT UPPER('abc')； -- ABC
LOWER(s),LCASE(s)； 	 -- 将字符串s的所有字母变成小写字母
SELECT LOWER('ABC')； -- abc
LEFT(s,n)； 	-- 返回字符串s的前n个字符
SELECT LEFT('abcde',2)； -- ab
RIGHT(s,n)； 	-- 返回字符串s的后n个字符
SELECT RIGHT('abcde',2)； -- de
LPAD(s1,len,s2)； 	-- 字符串s2来填充s1的开始处，使字符串长度达到len
SELECT LPAD('abc',5,'xx')； -- xxabc
RPAD(s1,len,s2)； 	-- 字符串s2来填充s1的结尾处，使字符串的长度达到len
SELECT RPAD('abc',5,'xx')； -- abcxx
LTRIM(s)； 	-- 去掉字符串s开始处的空格
RTRIM(s)； 	-- 去掉字符串s结尾处的空格
TRIM(s)； 	-- 去掉字符串s开始和结尾处的空格
TRIM(s1 FROM s)； 	-- 去掉字符串s中开始处和结尾处的字符串s1
SELECT TRIM('@' FROM '@@abc@@')； -- abc
REPEAT(s,n)； 	-- 将字符串s重复n次
SELECT REPEAT('ab',3)； -- ababab
SPACE(n)； 	-- 返回n个空格
REPLACE(s,s1,s2)； 	-- 将字符串s2替代字符串s中的字符串s1
SELECT REPLACE('abc','a','x')； -- xbc
STRCMP(s1,s2)； 	-- 比较字符串s1和s2
SUBSTRING(s,n,len)； 	-- 获取从字符串s中的第n个位置开始长度为len的字符串
MID(s,n,len)； 	-- 同SUBSTRING(s,n,len)
LOCATE(s1,s),POSITION(s1 IN s)； 	
-- 从字符串s中获取s1的开始位置
SELECT LOCATE('b', 'abc')； -- 2
INSTR(s,s1)； 	-- 从字符串s中获取s1的开始位置
SELECT INSTR('abc','b')； -- 2
REVERSE(s)； 	-- 将字符串s的顺序反过来
SELECT REVERSE('abc')； -- cba
ELT(n,s1,s2,...)； 	-- 返回第n个字符串
SELECT ELT(2,'a','b','c')； -- b
EXPORT_SET(x,s1,s2)； 	-- 返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个 “off”字符串。每个字符串用“separator”分隔(缺省“,”)，并且只有“bits”的“number_of_bits” (缺省64)位被使用。
SELECT EXPORT_SET(5,'Y','N',',',4)； -- Y,N,Y,N
FIELD(s,s1,s2...)； 	-- 返回第一个与字符串s匹配的字符串位置
SELECT FIELD('c','a','b','c')； -- 3
FIND_IN_SET(s1,s2)； 	-- 返回在字符串s2中与s1匹配的字符串的位置
MAKE_SET(x,s1,s2)； 	-- 返回一个集合 (包含由“,”字符分隔的子串组成的一个 字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对 应位1，等等。
SELECT MAKE_SET(1|4,'a','b','c')； -- a,c
SUBSTRING_INDEX； 	-- 返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回第count个字符左边的字符串。如果count是负数，返回第(count的绝对值(从右边数))个字符右边的字符串。
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
LOAD_FILE(file_name)； 	-- 读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权 限。文件必须所有内容都是可读的并且小于max_allowed_packet。 如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。

日期时间函数

MySQL的日期和时间函数主要用于处理日期时间。

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());

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

SELECT VERSION();  /*版本*/
SELECT USER();  /*用户*/


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

聚合函数

/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;  /*推荐*/


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


# 查询不同课程的平均分,最高分,最低分
# 前提:根据不同的课程进行分组

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..
*/

函数的创建

/*语法*/
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");

**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