MySQL基础2-函数

一. 函数概述

函数是指一段可以被另一段程序调用的程序或代码

二. 字符串函数

MySQL中内置了许多字符串函数，常用的如下：

函数	功能
CONCAT(S1, S2, …Sn)	字符串拼接，将S1, S2, …Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str, n, pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str, n, pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)	返回字符串str的start位置起的长度为len的字符串

字符串函数的使用：

SELECT 函数(参数)；

-- Example

-- concat():
select concat('Hello', 'MySQL');

-- lower():
select lower('HELLO');

-- upper():
select upper('hello');

-- lpad():
select lpad('1', 5, '-');

-- rpad():
select rpad('2', 3, '*');

-- trim():
select trim('   Hello, MySQL        ');

-- substring():
select substring('Hello, MySQL', 2, 4);

三. 数值函数

常见的数值函数如下：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0-1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

-- Example

-- ceil(x):
select ceil(1.5);

-- floor(x):
select floor(1.2);

-- mod(x,y):
select mod(3,4);

-- rand:
select rand();

-- round(x,y):
select round(3.141592653, 3);

案例：使用函数生成6位数的验证码

-- 1.生成随机数
select rand() * 1000000;

-- 2.取整，去掉小数位
select round(rand() * 1000000, 0);		# 若是生成的随机数如0.001....之类的数就无法保证生成的随机数是6位

-- 3.使用左填充OR右填充
select lpad(round(rand() * 1000000, 0), 6, round(rand() * 1000000, 0));

三. 日期函数

常见的日期函数如下：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前时间和日期
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date 的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

-- Example

-- curdate():
select curdate();

-- curtime():
select curtime();

-- now():
select now();

-- year(date):
# 获取当前时间的年份
select year(now());
# 获取指定时间的年份
select year('2020-05-20 05:20:20');
# 使用年月日亦可
select year('2020-05-20');

-- month(date):
select month(now());

-- day(date):
select day(now());

-- date_add(date, interval expr type):
# 以下使用当前时间做演示
# 获取70天之后的date
select date_add(now(), interval 70 day);
# 获取3个月前的date
select date_add(now(), interval -3 month);
# 获取20年后的date
select date_add(now(), interval 20 year);

-- datediff(date1, date2):
# 使用方法：date1-date2，返回之间的天数
# 求两年前的今天和现在的间隔天数
select datediff(now(), now() - interval 2 year);
# 求8个月后和今天的间隔天数
select datediff(now() + interval 8 month, now());

四. 流程函数

流程函数也是常用的函数之一，可在SQL语句中实现条件筛选，以此提高语句的效率，流程函数中常用函数如下：

函数	功能
IF(value, t, f)	如果value为true，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果expr的值等于val1，返回res1，… 否则返回default默认值

-- Example

-- if():
select if(0, 1, 2);		# 返回2
select if(1, 3, 2);		# 返回3

-- ifnull():
select ifnull('', 8);	# 返回空字符串(空字符串≠null)
select ifnull(null, 10);	# 返回10；

案例演示1：

查询emp表中员工地址，若为北京/上海，则展示’一线城市’，否则展示’二线城市’，emp表的创建可参考本专栏中MySQL基础1

select
    name,
    case workAddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地址' 
from emp;

select * from scores;

案例演示2：
统计scores中各个同学的成绩(scores表的创建可参考本专栏中MySQL基础1)，展示规则如下：

>= 85，展示优秀；>= 60， 展示及格；否则，展示不及格

select
    id '学号',
    name '姓名',
    case when Chinese >= 85 then '优秀' when Chinese >= 60 then '及格' else '不及格' end '语文',
    case when Math >= 85 then '优秀' when Math >= 60 then '及格' else '不及格' end '数学',
    case when English >= 85 then '优秀' when English >= 60 then '及格' else '不及格' end '英语',
    age '年龄',
    gender '性别'
from scores;