MySQL系列(十)：函数

将一段代码块封装到一个结构中，在需要执行代码块的时候调用结构，可以提高代码的复用性。

任何函数都有返回值，因此函数的调用通过select调用。

一、系统函数

Mysql系统定义好的函数，可以直接使用。

更多函数可以参考MySQL官方文档。

1.数字类

-- 返回0到1间的随机数
select rand();

-- 随机取出2件商品
select * from goods order by rand() limit 2;

-- 向下取整，输出3
select floor(3.9)

-- 向上取整，输出4
select ceil(3.1)

--四舍五入，输出4
select round(3.5)

2.大小写转换

-- 转成大写
select ucase('nin hao');

--转成小写
select lcase('NIN HAO');

3.字符串类

-- 从左边截取
select left('abcde',3);

-- 从右边截取
select right('abcde',3);

-- 从第二个位置开始，截取3个,(位置从1开始)
select substring('abcde',2,3);

-- 字符串相连
select concat(10,'hahaha');

-- coalesce(str1,str2)：如果第str1为null，就显示str2
select coalesce(null,123);
select stuname,stusex,coalesce(writtenexam,'缺考'), coalesce(labexam,'缺考') from stuinfo natural left join stumarks;

-- 显示字节的个数
select length('abcde');

-- 显示字符的个数
select char_length('abcde');

-- trim用来去字符串两边空格
select length(trim(' abc '));

-- 字符串替换，将bc替换成pache
select replace('abc','bc','pache');

-- 判断字符串是否在某个具体的字符串中存在, 存在返回位置
select instr('1','123');

-- lpad左填充，将字符串按照某个指定的填充方式，填充到指定长度(字符)
select lpad('1',30,'hello');

4.时间类

-- 时间戳
select unix_timestamp();

-- --将时间戳转成日期格式
select from_unixtime(unix_timestamp());


-- 返回今天的时间日期
curdate();

-- --取出当前时间
select now();

-- 年
select year(now());

-- 月
select month(now())

-- 日
select day(now());

-- 小时
select hour(now());

-- 分钟
select minute(now());

-- 秒
select second(now());

-- 两个日期相距多少天
select datediff(now(),'1997-7-1');

-- if(表达式,值1，值2)，类似于三元运算符
select concat(10,if(10 % 2=0,'偶数','奇数'));

5.例子

-- 一个电影网站，求出今天添加的电影，在添加电影时，有一个添加的时间戳。
-- curdate()求出今天的日期
-- 把添加的时间戳，转换成日期
select title from movies where curdate()=from_unixtime(senddate,'%Y-%m-%d');

二、自定义函数

自定义函数的大部分操作和存储过程差不多，不过函数有返回值，存储过程没有。

1.创建函数

语法：

create function 函数名([形参列表]) returns 数据类型    -- 规定要返回的数据类型

begin

    -- 函数体

    -- 返回值:  return 类型(指定数据类型);

end

• 函数内部可以有各种编程语言的元素，变量、流程控制、函数调用等
• 函数内部可以有增删改等语句
• 函数内部不可以有select（或show或desc）这种返回结果集的语句
• 自定义的函数，是隶属于数据库的，只能在创建函数的数据库中使用

-- 创建函数
create function display_1() returns int return 100;

-- 调用函数
select display_1();

2.查看函数

-- 查看所有函数
show function status [like 'pattern'];

-- 查看函数的创建语句
show create function 函数名;

3.修改与删除函数

函数只能先删除后新增，不能修改。

drop function 函数名;

4.函数参数

定义时的参数叫形参，调用时的参数叫实参(实参可以是数值也可以是变量)。

形参要求必须指定数据类型：

function 函数名(形参名字 字段类型) returns 数据类型

• 在函数内部使用@定义的变量在函数外部也可以访问

5.作用域

MySQL中的作用域与Javascript中的作用域完全一样，全局变量可以在任何地方使用，局部变量只能在函数内部使用。

• 全局变量：使用set关键字定义，使用@符号标志
• 局部变量：使用declare关键字声明，没有@ 符号，所有的局部变量的声明必须在函数体开始之前

-- 计算1-指定数之间的和
delimiter $$
create function sum_1(num int) returns int
begin
    -- 定义条件变量
    set @i = 1;    -- @定义的变量是全局变量,没有的可以理解为局部变量
    set @res = 0;  -- 保存结果

    -- 循环求和
    while @i <= num do

        -- 求和: 任何变量要修改必须使用set关键字
        -- mysql中没有+=,没有++
        set @res = @res + @i;

        -- 修改循环变量
        set @i = @i + 1;
    end while;

    -- 返回值
    return @res;

end
$$  -- 函数结束

delimiter ;

 

转载于:https://my.oschina.net/programs/blog/1789530