目录

1.Mysql函数分类

2.常见数学函数

3.字符串函数(重点)

4.日期和时间函数

5.流程控制函数

6.其它函数


1.Mysql函数分类

(1)单行函数

特点:

  • 单行函数对单行操作
  • 每行返回一个结果,有可能返回值与原参数数据类型不一致
  • 单行函数可以写在SELECT、WHERE、ORDER BY子句中
  • 有些函数没有参数,有些函数包括一个或多个参数
  • 函数可以嵌套

(2)多行函数

特点:多行返回一个结果

2.常见数学函数

ABS(x):返回x的绝对值;
SQRT(x):返回非负数x的平方根;
PI():返回圆周率;
MOD(x,y)(x % y):返回x被y除的余数;
CEIL(x)CEILING(x):返回大于或者等于x的最小整数值;
FLOOR(x):返回小于或者等于x的最大整数值;
ROUND(x,y):返回保留小数点后面y位,四舍五入的整数;
TRUNCATE(x,y):返回被舍弃的小数点后y位的数字x;
RAND():每次产生不同的随机数;
SIGN(x):返回参数的符号;

POW(x,y)POWER(x,y): 返回x的y次乘方的结果值;
EXP(x):返回以e为底的x乘方后的值;
LOG(x):返回x的自然对数,x相对于基数e的对数;
LOG10(x):返回x的基数为10的对数;
RADIANS(x):将参数x由角度转化为弧度;
DEGREES(x):将参数x由弧度转化为度。
SIN(x):返回x正弦,其中x为弧度值;
ASIN(x)返回x的反正弦,即正弦为x的值;
COS(x):返回x的余弦;
ACOS(x):返回x反余弦
TAN(x):返回x的正切;
ATAN(x):返回x的反正切;

例子:

select sqrt(4) from dual;--2
select PI() from dual;--3.141593
select mod(10, 3) , (10 % 3) from dual;--都是1
select ceil(12.8), floor(12.8), round(12.8) from dual;--13 12 13
select pow(2, 3) from dual;--8
select sign(-8), sign(8), sign(0) from dual;--  (-1 1 0)

3.字符串函数(重点)

CHAR_LENGTH(str):返回字符串str的所包含字符个数;
LENGTH(str):返回字符串str的长度;
CONCAT(s1,s2,...): 字符串连接;
CONCAT_WS(x,s1,s2,…):字符串连接, x是其它参数的分隔符;
INSERT(s1,x,len,s2) :返回字符串s1,s1中插入字符串s2;
LOWER (str)|LCASE (str):将字符串全部转换成小写字母;
UPPER(str)|UCASE(str):将字符串全部转换成大写字母;
LEFT(s,n):返回最左边指定长度的字符;
RIGHT(s,n):返回最右边指定长度的字符;
LPAD(s1,len,s2)| RPAD(s1,len,s2) :填充字符串函数;
TRIM(s1 FROM s)|LTRIM(s)|RTRIM(s):删除空格函数;
REPEAT(s,n):重复生成字符串函数;
SPACE(n):返回一个由n个空格组成的字符串;
REPLACE(s,s1,s2):字符串替换函数;
STRCMP(s1,s2):比较字符串大小函数;
SUBSTRING(s,n,len):获取子串函数;
LOCATE(str1,str)|POSITION(str1 IN str)|INSTR(str, str1):匹配子串开始位置函数;
REVERSE(s):将字符串s反转;
ELT(N,字符串1,字符串2,字符串3,…):返回指定位置函数;

例子:

select char_length('my name is 123') from dual;-- 14
select length('tom') from dual;-- 3
select concat('I ', 'love ', 'mysql') from dual;-- 结果为'I love mysql'
select concat_ws(' ', 'I', 'am', 'student!') from dual;-- 结果为‘I am student!’
select insert('Iam', 2, 1, '\'') from dual;-- 在第2个位置,插入长度1的',结果为 I'm
select left('LQH', 2), substring('LQH', 1, 2) from dual;-- 都是提取字符串的前2位字符,结果为“LQ”
select locate('am', 'I am student, I am stupid boy'), position('am' in 'I am student, I am stupid boy'), instr('I am student, I am stupid boy', 'am') from dual;-- 都是查询字符串中“am”第一次出现的位置,结果都为3

select repeat('.', '6'), space(6) from dual;-- 此处,repeat重复输出6次“.”,而space返回6个空格
select strcmp('a', 'B'), strcmp('a', 'A'), strcmp('z', 'G') from dual;-- 比较字符串大小,因为前者小于后者返回-1,相等返回0,否则返回1;结果为 -1 0 1
select reverse('I miss you') from dual;-- 字符串逆转,结果为“uoy ssim I”
select elt(2, '123', 'abc', 'hhh', 'ddd') from dual;-- 返回第2个字符串,结果为abc“
select lpad(123, 6, '*'), rpad(123, 6, '*') from dual;-- 左、右填充不够用指定字符填充,结果为“***123” 和“123***”
select upper('i love mysql'), lower('I loVE MysQL') from dual;-- 转大、小写,结果为“I LOVE MYSQL”、“i love mysql”

4.日期和时间函数

CURDATE()CURRENT_DATE() :获取当前日期函数;
NOW():返回服务器的当前日期和时间;
CURTIME():返回当前时间,只包含时分秒;
UTC_DATE():返回世界标准时间日期函数;
UTC_TIME():返回世界标准时间函数;
TIMEDIFF(expr1, expr2):返回两个日期相减相差的时间数;
DATEDIFF(expr1, expr2):返回两个日期相减相差的天数;
DATE_ADD(date,INTERVAL expr type):日期加上一个时间间隔值;
DATE_SUB(date,INTERVAL expr type):日期减去一个时间间隔值;
DATE(date)TIME(date)YEAR(date):选取日期时间的各个部分:
EXTRACT(unit  FROM  date):从日期中抽取出某个单独的部分或组合;
DAYOFWEEK(date)DAYOFMONTH(date) DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天
DAYNAMEMONTHNAME:返回日期的星期和月份名称;
DATE_FORMAT(date,format):格式化日期;
TIME_FORMATE(time,formate):格式化时间;
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2):计算日期时间差

(1)其中,DATE_ADD(date,INTERVAL expr type)函数和DATE_SUB(date,INTERVAL expr type)函数的type参数可以选择下列值:

type

解释

HOUR

小时

MINUTE

分钟

SECOND


MICROSECOND

毫秒

YEAR


MONTH


DAY


WEEK


QUARTER


YEAR_MONTH

年和月

DAY_HOUR

日期和小时

DAY_MINUTE

日和分钟

DAY_SECOND

日和秒

HOUR_MINUTE

小时和分钟

HOUR_SECOND

小时和秒

MINUTE_SECOND

分钟和秒

(2)其中,DATE_FORMAT(date,format)TIME_FORMAT(time,format)函数的参数format可以选择一下参数:

格式

描述

%a

星期名

%b

月名

%c

月,数值

%D

带有英文前缀的月中的天

%d

月的天,数值(00-31)

%e

月的天,数值(0-31)

%f

微妙

%H

小时(00-23)

%h

小时(01-12)

%I

小时(01-12)

%i

分钟,数值(00-59)

%j

年的天(001-366)

%k

小时(0-23)

%M

月名

%m

月,数值(00-12)

%p

AM或PM

%r

时间,12-小时(hh:mm:ss AM或PM)

%S

秒(00-59)

%s

秒(00-59)

%T

时间,24小时(hh:mm:ss)

%U

周(0-53),星期日是一周的第一天

%u

周(00-53),星期一是一周的第一天

%V

周(01-53),星期日是一周的第一天,与%X使用

%v

周(01-53),星期一是一周的第一天,与%x使用

%W

星期名

%w

周的天(0=星期日,6=星期六)

%X

年,其中的星期日是周的第一天,4位,与%V使用

%x

年,其中的星期一是州的第一天,4位,与%v使用

%Y

年,4位

%y

年,2位

(3)例子

SELECT now(),date(now()); -- 日期
SELECT now(),time(now()); -- 时间
SELECT now(),year(now()); -- 年
SELECT now(),quarter(now()); -- 季度
SELECT now(),month(now()); -- 月
SELECT now(),week(now()); -- 周
SELECT now(),day(now()); -- 日
SELECT now(),hour(now()); -- 小时
SELECT now(),minute(now()); -- 分钟
SELECT now(),second(now()); -- 秒
SELECT now(),microsecond(now()); -- 微秒



-- EXTRACT(unit  FROM  date):从日期中抽取出某个单独的部分或组合
SELECT now(),extract(YEAR FROM now()); -- 年
SELECT now(),extract(QUARTER FROM now()); -- 季度
SELECT now(),extract(MONTH FROM now()); -- 月
SELECT now(),extract(WEEK FROM now()); -- 周
SELECT now(),extract(DAY FROM now()); -- 日
SELECT now(),extract(HOUR FROM now()); -- 小时
SELECT now(),extract(MINUTE FROM now()); -- 分钟
SELECT now(),extract(SECOND FROM now()); -- 秒
SELECT now(),extract(YEAR_MONTH FROM now()); -- 年月
SELECT now(),extract(HOUR_MINUTE FROM now()); -- 时分

SELECT dayname('2022-01-01') from dual;-- Saturday
SELECT timestampdiff(month, '2020-05-22' ,'2021-12-01') from dual;-- 后面的日期减去前面的日期,结果为18
SELECT date_add('2022-01-01', interval 1 month) from dual;-- 加上1月,结果为2022-02-01
SELECT date_sub('2022-01-01', interval 1 month) from dual;-- 减去1月,结果为2021-12-01
SELECT date_format('2022-01-31', '%Y/%m/%d') from dual;-- 格式化输出,结果为2022/01/31

5.流程控制函数

(1)CASE

①语法:

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

或者

CASE 
WHEN [condition] THEN result 
[WHEN [condition] THEN result ...] 
[ELSE result] 
END

 ②例子:

select 
case 'C'
when 'A' then 'Very Good!'
when 'B' then 'Good!'
when 'C' then 'Normal!'
when 'D' then 'Bad!'
else 'Worse!'
end
from dual;-- 结果为 Normal!

select 
case 
when sal < 1000 then 'D'
when sal < 3000 then 'C'
when sal < 5000 then 'B'
when sal < 6000 then 'A'
end 
from t_emp;-- 当雇员工资小于1000输出D,小于3000输出C,小于5000输出B,小于6000输出A

(2)IF

①语法:

IF(expr1,expr2,expr3)

②例子:

select if(1 < 0, 'true', 'false') from dual;
-- 类似java中的三元运算符,当第一个参数为真时,显示第二个值;为假时显示第三个值

(3)IFNULL

①语法:

IFNULL(expr1,expr2)

②例子:

select ifnull(null, '不为空') from dual;
-- 如果第一个参数为空,返回第二个参数,否则返回第一个参数
-- 相当于coalesce函数,结果为 “不为空”
select coalesce(null, '不为空') from dual;

(4)NULLIF

①语法:

NULLIF(expr1,expr2)

②例子:

select nullif(10, 10) from dual;-- 结果为null
-- 如果第一个参数等于第二个参数,则返回null;否则,返回第一个参数

6.其它函数

Database():返回使用utf8 字符集的默认( 当前) 数据库名

Version():返回指示MySQL 服务器版本的字符串。

User():返回当前MySQL 用户名和机主名

Inet_aton():给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数。

Inet_ntoa():给定一个数字网络地址, 返回作为字符串的该地址的点地址表示。

Password(str):从原文密码str 计算并返回密码字符串,当参数为NULL 时返回NULL。  

Md5(str):为字符串算出一个MD5 128 比特检查和。

例子:

select database() from dual;-- myshop
select version() from dual;-- 8.0.27
select user() from dual;-- root@localhost
select Inet_aton('192.168.254.3'), inet_ntoa('3232300547') from dual;-- 3232300547  192.168.254.3
select md5('123456') from dual; -- e10adc3949ba59abbe56e057f20f883e