MySQL 基础教程

MySQL 高级教程

MySQL SQL 语句

MySQL 笔记

MySQL 导入数据
MySQL 运算符
original icon
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.knowledgedict.com/tutorial/mysql-functions.html

MySQL 内置函数及自定义函数详解

MySQL 自身提供了大量的内置函数（built-in functions），这些函数的存在给日常的开发和数据操作带来了极大的便利。除此之外，开发者也可以按照自身的需求来实现自己功能的函数，即自定义函数（user-defined function）。 

MySQL 内置函数

MySQL 的内置函数主要包括字符串函数、数字函数、日期函数、高级操作的函数等。

MySQL 字符串函数

函数 描述 版本支持
ASCII(s) 返回字符串 s 的第一个字符的 ASCII 码。  
CHAR_LENGTH(s) 返回字符串 s 的字符数  
CHARACTER_LENGTH(s) 返回字符串 s 的字符数  
CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串  
CONCAT_WS(x, s1,s2...sn) 同 CONCAT(s1,s2,...) 函数，但是每个字符串直接要加上 x，x 可以是分隔符  
FIELD(s,s1,s2...) 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置  
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置  
FORMAT(x,n) 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。  
INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串  
LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置  
LCASE(s) 将字符串 s 的所有字母变成小写字母  
LEFT(s,n) 返回字符串 s 的前 n 个字符  
LEFT(s,n) 返回字符串 s 的前 n 个字符  
LENGTH(str) 返回字符的长度，一个汉字算三个字符，一个数字或字母算一个字符。  
LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置  
LOWER(s) 将字符串 s 的所有字母变成小写字母。 3.23 版本开始
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len  
LTRIM(s) 去掉字符串 s 开始处的空格  
MID(s,n,len) 从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,len)  
POSITION(s1 IN s) 从字符串 s 中获取 s1 的开始位置  
REPEAT(s,n) 将字符串 s 重复 n 次  
REPLACE(str,from_str,to_str) 将字符串 str 中出现的字符串 from_str 替代成字符串 to_str，并返回替换后新字符串，该方法对大小写敏感。  
REVERSE(s) 将字符串s的顺序反过来  
RIGHT(s,n) 返回字符串 s 的后 n 个字符  
RPAD(s1,len,s2) 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len  
RTRIM(s) 去掉字符串 s 结尾处的空格  
SPACE(n) 返回 n 个空格  
STRCMP(s1,s2) 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1  
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串  
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串  
SUBSTRING_INDEX(s, delimiter, number) 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。		  
TRIM(str) 去掉字符串 str 开始和结尾处的指定的符号，不指定默认为空格。  
UCASE(s) 将字符串转换为大写  
UPPER(s) 将字符串转换为大写  

MySQL 数字函数

函数名 描述 实例
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 CEIL(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", "Runoob", "Apple");   -- Runoob
LEAST(expr1, expr2, expr3, ...) 返回列表中的最小值

返回以下数字列表中的最小值：

SELECT LEAST(3, 12, 34, 8, 25); -- 3

返回以下字符串列表中的最小值：

SELECT LEAST("Google", "Runoob", "Apple");   -- Apple
LN 返回数字的自然对数

返回 2 的自然对数：

SELECT LN(2);  -- 0.6931471805599453
LOG(x) 返回自然对数(以 e 为底的对数)　　 
SELECT LOG(20.085536923188) -- 3
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 LargestPrice 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

MySQL 日期和时间函数

下面列出了 MySQL 内置的可以操作日期和时间的函数。

函数名 描述
ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期
ADDTIME(t,n) 时间 t 加上 n 秒的时间
CURDATE() 返回当前日期
CURRENT_DATE() 返回当前日期
CURRENT_TIME 返回当前时间
CURRENT_TIMESTAMP() 返回当前日期和时间
CURTIME() 返回当前时间
DATE() 从日期或日期时间表达式中提取日期值
DATEDIFF(expr1, expr2) 计算日期 expr1 表达式与日期 expr2 之间相隔的天数。
DATE_ADD(d，INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d
DATE_SUB(date, INTERVAL expr unit) 从指定日期减去指定的日期时间间隔。
DAY(d) 返回日期值 d 的日期部分
DAYNAME(d) 返回日期 d 是星期几，如 Monday,Tuesday
DAYOFMONTH(d) 计算日期 d 是本月的第几天
DAYOFWEEK(d) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
DAYOFYEAR(d) 计算日期 d 是本年的第几天
EXTRACT(type FROM d) 从日期 d 中获取指定的值，type 指定返回的值。
type可取值为：
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
FROM_DAYS(n) 计算从 0000 年 1 月 1 日开始 n 天后的日期
HOUR(t) 返回 t 中的小时值
LAST_DAY(d) 返回给给定日期的那一月份的最后一天
LOCALTIME() 返回当前日期和时间
LOCALTIMESTAMP() 返回当前日期和时间
MAKEDATE(year, day-of-year) 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期
MAKETIME(hour, minute, second) 组合时间，参数分别为小时、分钟、秒
MICROSECOND(date) 返回日期参数所对应的毫秒数
MINUTE(t) 返回 t 中的分钟值
MONTHNAME(d) 返回日期当中的月份名称，如 Janyary
MONTH(d) 返回日期d中的月份值，1 到 12
NOW() 返回当前日期和时间。
PERIOD_ADD(period, number) 为 年-月 组合日期添加一个时段
PERIOD_DIFF(period1, period2) 返回两个时段之间的月份差值
QUARTER(d) 返回日期d是第几季节，返回 1 到 4
SECOND(t) 返回 t 中的秒钟值
SEC_TO_TIME(s) 将以秒为单位的时间 s 转换为时分秒的格式
STR_TO_DATE(string, format_mask) 将字符串转变为日期
SUBDATE(d,n) 日期 d 减去 n 天后的日期
SUBTIME(t,n) 时间 t 减去 n 秒的时间
SYSDATE() 返回当前日期和时间
TIME(expression) 提取传入表达式的时间部分
TIME_FORMAT(t,f) 按表达式 f 的要求显示时间 t
TIME_TO_SEC(t) 将时间 t 转换为秒
TIMEDIFF(time1, time2) 计算时间差值
TIMESTAMP(expression, interval) 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和
TO_DAYS(d) 计算日期 d 距离 0000 年 1 月 1 日的天数
UNIX_TIMESTAMP([date]) 获取对应的时间戳，无参时，返回当前的时间戳。
WEEK(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53
WEEKDAY(d) 日期 d 是星期几，0 表示星期一，1 表示星期二
WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53
YEAR(d) 返回年份
YEARWEEK(date, mode) 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推

MySQL 高级函数

函数名 描述
BIN(x) 返回 x 的二进制编码
BINARY(s) 将字符串 s 转换为二进制字符串 
CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END
 CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。
CAST(expr AS type [ARRAY]) 转换数据类型
COALESCE(expr1, expr2, ...., expr_n) 返回参数中的第一个非空表达式（从左向右）
CONNECTION_ID() 返回服务器的连接数
CONV(x,f1,f2) 返回 f1 进制数变成 f2 进制数
CONVERT(s USING cs) 函数将字符串 s 的字符集变成 cs
CURRENT_USER() 返回当前用户
DATABASE() 返回当前数据库名
IF(expr, v1, v2) 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
IFNULL(v1, v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
ISNULL(expression) 判断表达式是否为 NULL
LAST_INSERT_ID() 返回最近生成的 AUTO_INCREMENT 值
NULLIF(expr1, expr2) 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1
SESSION_USER() 返回当前用户
SYSTEM_USER() 返回当前用户
USER() 返回当前用户
VERSION() 返回数据库的版本号

MySQL 聚合函数

MySQL 提供了进行聚合操作的内置函数，它们通常与 GROUP BY 子句一起使用，并将值分组作为子集。

MySQL 内置聚合函数
函数名 描述 实例
AVG() 返回指定参数的平均值。  
BIT_AND() 返回表达式的所有位按位 AND 结果。  
BIT_OR() 返回表达式的所有位按位 OR 结果。  
BIT_XOR() 返回表达式的所有位按位 XOR 结果。如果参数为 NULL，返回值也将为 NULL。  
COUNT() 返回条数。  
COUNT(DISTINCT) 返回指定不同值的条数。  
GROUP_CONCAT() 将 GROUP BY 产生的同一个分组中的值连接起来，返回一个字符串结果。  
MAX() 返回最大值。  
MIN() 返回最小值。  
STD() 返回总体标准差。  
STDDEV() 返回总体标准差。  
STDDEV_POP() 返回总体标准差。  
STDDEV_SAMP() 返回样本标准差。  
SUM() 返回运算和的结果。  
VAR_POP() 返回总体方差。  
VAR_SAMP() 返回样本方差。  
VARIANCE() 返回总体方差。  

 

MySQL 导入数据
MySQL 运算符