一. 函数概述
函数是指一段可以被另一段程序调用的程序或代码
二. 字符串函数
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;