8.SQL函数

函数的认识

SQL函数的分类：

1. 单行函数：仅对单个行运算，并且返回一个结果；
2. 多行函数：才足以成组的行，每组给出一个结果。（聚合函数）

使用函数的目的：

1. 执行数据运算
2. 修改单个数据项
3. 操作输出进行行分组
4. 格式化显示的数字和日期
5. 转换列数据类型

单行函数

单行函数分类

字符函数

字符函数功能：

1. 字母大小写处理函数
2. 字符处理函数

1.字母大小写处理函数

函数	描述	示例
LOWER(s) | LCASE(s)	转换小写	SELECT LOWER("WZQ");
UPPER(s) | UCASE(s)	转换大写	SELECT UPPER("wzq");

2.字符处理函数

函数	描述	示例
LENGTH(S)	返回字符串的长度	SELECT LENGTH("oldlu"); --5;
CONCAT(s1,s2,s3...)	拼接多个字符串为一个字符串	SELECT CONCAT("sxt ","teacher ", "oldlu");--sxt teacher oldlu;
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达len	SELECT LPAD('oldlu',8,'x'); -- xxxoldlu
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	SELECT RPAD('oldlu',8,'x'); -- oldluxxx
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	SELECT REPLACE('oldlu','o','O'); --Oldlu
REVERSE(s)	将字符串s的顺序反过来	SELECT REVERSE('abc'); -- cba
SUBSTR(s,start,length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	SELECT SUBSTR("OLDLU", 2, 3); --<br/>LDL
TRIM(s)	去掉字符串 s 结尾处的空格	SELECT RTRIM("oldlu "); -- oldlu
INSTR(s1,s2)	返回在s1中s2字符串的位置	SELECT INSTR("wzq","z"); -- 2

数字函数

1、3个比较常用：

• ROUND(x,n)：四舍五入，n是保留的位数，n=-2：值四舍五入保留到百位；n=-1：保留到十位
• TRUNCATE(x,n)：不四舍五入，保留整数，截断为整数，n为截断的小数位数
• MOD(m,n)：m/n的余数

2、其他数学函数：（没什么用，保存以防万一）

函数	描述	示例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值：SELECT ABS(-1) -- 返回1
ACOS(x)	求 x 的反余弦值(参数是弧度)	SELECT ACOS(0.25);
ASIN(x)	求反正弦值(参数是弧度)	SELECT ASIN(0.25);
ATAN(x)	求反正切值(参数是弧度)	SELECT ATAN(2.5);
ATAN2(n, m)	求反正切值(参数是弧度)	`SELECT ATAN2(-0.8, 2);``
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)	返回大于或等于 x 的最小整数	SELECT CEIL(1.5) -- 返回2
CEILING(x)	返回大于或等于 x 的最小整数	SELECT CEILING(1.5); -- 返回2
COS(x)	求余弦值(参数是弧度)	SELECT COS(2);
COT(x)	求余切值(参数是弧度)	`SELECT COT(6);``
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 * 号	返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)	将弧度转换为角度	SELECT DEGREES(3.1415926535898) -- 180
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5：SELECT 10 DIV 5; -- 2
EXP(x)	返回 e 的 x 次方	计算 e 的三次方：SELECT EXP(3) -- 20.085536923188
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)	返回列表中的最大值	返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34;返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Nhooo", "Apple"); -- Nhooo
LEAST(expr1, expr2, expr3, ...)	返回列表中的最小值	返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3;返回以下字符串列表中的最小值：SELECT LEAST("Google", "Nhooo", "Apple"); -- Apple
LN	返回数字的自然对数，以 e 为底。	返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453
LOG(x) 或 LOG(base, x)	返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。	SELECT LOG(20.085536923188) -- 3;SELECT LOG(2, 4); -- 2
LOG10(x)	返回以 10 为底的对数	SELECT LOG10(100) -- 2
LOG2(x)	返回以 2 为底的对数	返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数：SELECT MOD(5,2) -- 1
PI()	返回圆周率(3.141593）	SELECT PI() --3.141593
POW(x,y)	返回 x 的 y 次方	2 的 3 次方：SELECT POW(2,3) -- 8
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方：SELECT POWER(2,3) -- 8
RADIANS(x)	将角度转换为弧度	180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898
RAND()	返回 0 到 1 的随机数	SELECT RAND() --0.93099315644334
ROUND(x)	返回离 x 最近的整数	SELECT ROUND(1.23456) --1
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	SELECT SIGN(-10) -- (-1)
SIN(x)	求正弦值(参数是弧度)	SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x)	返回x的平方根	25 的平方根：SELECT SQRT(25) -- 5
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)	求正切值(参数是弧度)	SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234

日期函数

1.使用：

在MySQL中允许直接使用字符串表示时间，格式只能为“YYYY-MM-DD HH:MI:SS"或者”YYYY/MM/DD HH:MI:SS“

2.常用函数：

函数名	描述	示例
CURDATE()	返回当前日期，例如：2021-12-31	SELECT CURDATE();->2021-12-31
CURRENT_DATE()	返回当前日期，例如：2021-12-31	SELECT CURRENT_DATE();->2021-12-31
CURTIME()	返回当前时间，例如：12:39:40	SELECT CURTIME();->12:39:40
CURRENT_TIME()	返回当前时间，例如：12:39:40	SELECT CURRENT_TIME();->12:39:40
NOW()	返回当前日期和时间，例如：2021-12-31 12:39:40	SELECT NOW();->2021-12-31 12:39:40
SYSDATE()	返回当前日期和时间，例如：2021-12-31 12:39:40	SELECT SYSDATE();->2021-12-31 12:39:40
DATEDIFF(d1,d2)	计算日期d1和d2之间相隔的天数	SELECT DATEDIFF('2001-01-01','2001-02-02')-> -32
DATE()	从日期或日期时间表达式中提取时间	SELECT DATE("2017-06-15"); -> 2017-06-15
DAY(d)	返回日	SELECT DAY("2021-12-31");-> 31
WEEK(d)	计算日期 d 是本年的第几个星期，范围是0 到 53	SELECT WEEK("2011-11-11");->45
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是0 到 53	SELECT WEEKOFYEAR("2011-11-11");->45
MONTH(d)	返回月份	SELECT MONTH("2011-11-11");->11
YEAR(d)	返回年份	SELECT YEAR("2021-12-31");->2021
DAYOFMONTH(d)	计算日期d是本月第几天	SELECT DAYOFMONTH("2011-11-11"); -> 11
DAYOFWEEK(d)	计算日期d是星期几	SELECT DAYOFWEEK("2011-11-11 11:11:11"); -> 6
DAYOFYEAR(d)	计算日期d是本年的第几天	SELECT DAYOFYEAR("2011-11-11 11:11:11");-> 315
DAYNAME(d)	返回日期d是星期几，如Monday，Tuesday	SELECT DAYNAME('2011-11-11 11:11:11'); -> Friday
MONTHNAME(d)	返回日期d的月份名，如November	SELECT MONTHNAME("2011-11-11");-> November
TIMEDIFF(t1,t2)	计算时间差值	SELECT TIMEDIFF("13:10:11", "13:10:10");-> 00:00:01

转换函数

1.分类：

隐式数据类型转换：MySQL服务器能自动的进行数据类型转换。如：可以将标准格式的字符串日期转换为日期类型。MySQL字符串日期格式为“YYYY-MM-DD HH:MI:SS"或者”YYYY/MM/DD HH:MI:SS“

显式数据类型转换：指需要依赖转换函数来完成类型转换。

2.两个显示数据类型转换函数

• DATE_FORMAT(date,format)：将日期转换为字符串
• STR_TO_DATE(str,format)：将字符串转换为日期

示例一：

# 查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。
SELECT DATE_FORMAT(hire_date,'%Y 年%m 月%d日')
FROM employees 
WHERE last_name="king";

示例二：

insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(400,'oldlu','oldlu@sxt.cn',
       STR_TO_DATE('2049 年 5 月 5 日','%Y 年%m 月%d日'),'IT_PROG');
# str_to_date：后面的format式告诉服务器哪个是年，哪个是月，哪个是日。

通用函数

函数	描述	示例
IF(expr,v1,v2)	expr成立，返回v1；expr不成立，返回v2	SELECT IF(1 > 0,'正确','错误') ->正确
IFNULL(v1,v2)	v1值不为null，返回v1，否则返回v2	SELECT IFNULL(null,'HelloWord') ->HelloWord
ISNULL(expr)	判断表达式是否为null	SELECT ISNULL(NULL);->1
NULLIF(expr1,expr2)	expr1 = expr2,返回null，否则返回expr1	SELECT NULLIF(25,25);->NULL
COALESCE(expr1,expr2,expr3...)	返回参数中第一个非空表达式	SELECT COALESCE(NULL,NULL,'WZQ');->WZQ
CASE expr WHEN condition1 THEN result1 WHEN condition2 THEN result2 END	和if-else功能相同	SELECT CASE 'oldlu' WHEN 'oldlu' THEN 'OLDLU' WHEN 'admin' THEN 'ADMIN' ELSE 'kevin' END;