MySQL 常用函数公式

Question

1. 数学函数

函数	说明
ABS(x)	返回 x 的绝对值
BIN(x)	返回 x 的二进制（OCT 返回八进制，HEX 返回十六进制）
CEILING(x)	返回大于 x 的最小整数值
EXP(x)	返回值 e（自然对数的底）的 x 次方
FLOOR(x)	返回小于 x 的最大整数值
GREATEST(x1,x2,...,xn)	返回集合中最大的值
LEAST(x1,x2,...,xn)	返回大于 x 的最小的值
LN(x)	返回 x 的自然对数
LOG(x,y)	返回 x 的以 y 为底的对数
MOD(x,y)	返回 x/y 的模（余数）
PI()	返回 pi 的值（圆周率）
RAND()	返回０到１内的随机值,可以通过提供一个参数（种子) 使 RAND() 随机数生成器生成一个指定的值。
ROUND(x,y)	返回参数 x 的四舍五入的有 y 位小数的值
SIGN(x)	返回代表数字 x 的符号的值
SQRT(x)	返回一个数的平方根
TRUNCATE(x,y)	返回数字 x 截短为 y 位小数的结果

2. 文本/字符串函数

函数	说明
ASCII(char)	返回字符的 ASCII 码值
BIT_LENGTH(str)	返回字符串的比特长度
CONCAT(s1,s2...,sn)	将 s1,s2...,sn 连接成字符串
CONCAT_WS(sep,s1,s2...,sn)	将 s1,s2...,sn 连接成字符串，并用 sep 字符间隔
INSERT(str,x,y,instr)	将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr，返回结果
FIND_IN_SET(str,list)	分析逗号分隔的 list 列表，如果发现 str，返回 str 在 list 中的位置
LCASE(str) 或 LOWER(str)	返回将字符串 str 中所有字符改变为小写后的结果
UCASE(str) 或 UPPER(str)	返回将字符串 str 中所有字符改变为大写后的结果
LEFT(str,x)	返回字符串 str 中最左边的 x 个字符
LTRIM(str)	去掉字符串 str 中开头的空格
RIGHT(str,x)	返回字符串 str 中最右边的 x 个字符
RTRIM(str)	去掉字符串 str 尾部的空格
LENGTH(s)	返回字符串 str 中的字符数
POSITION(substr,str)	返回子串 substr 在字符串 str 中第一次出现的位置
QUOTE(str)	用反斜杠转义 str 中的单引号
REPEAT(str,srchstr,rplcstr)	返回字符串 str 重复 x 次的结果
STRCMP(s1,s2)	比较字符串 s1 和 s2
SUBSTRING（str, pos）	SUBSTRING（被截取字段，从第几位开始截取，截取长度）
SUBSTRING_INDEX（str,delim,count）	SUBSTRING_INDEX（被截取字段，关键字，关键字出现的次数）

3. 日期及时间函数

函数	说明
CURDATE() 或 CURRENT_DATE()	返回当前的日期
CURTIME() 或 CURRENT_TIME()	返回当前的时间
DATE_ADD(date,INTERVAL int keyword)	返回日期 date 加上间隔时间 int 的结果(int 必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH)
DATE_FORMAT(date,fmt)	依照指定的 fmt 格式格式化日期 date 值
DATE_SUB(date,INTERVAL int keyword)	返回日期 date 加上间隔时间 int 的结果(int 必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH)
DAYOFWEEK(date)	返回 date 所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)	返回 date 是一个月的第几天(1~31)
DAYOFYEAR(date)	返回 date 是一年的第几天(1~366)
DAYNAME(date)	返回 date 的星期名，如：SELECT DAYNAME(CURRENT_DATE)
FROM_UNIXTIME(ts,fmt)	根据指定的 fmt 格式，格式化 UNIX 时间戳 ts
HOUR(time)	返回 time 的小时值(0~23)
MINUTE(time)	返回 time 的分钟值(0~59)
MONTH(date)	返回 date 的月份值(1~12)
MONTHNAME(date)	返回 date 的月份名，如：SELECT MONTHNAME(CURRENT_DATE)
NOW()	返回当前的日期和时间
QUARTER(date)	返回 date 在一年中的季度(1~4)，如 SELECT QUARTER(CURRENT_DATE)
WEEK(date)	返回日期 date 为一年中第几周(0~53)
YEAR(date)	返回日期 date 的年份(1000~9999)

4. 聚合函数

常用于 GROUP BY 从句的 SELECT 查询中

函数	说明
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非 NULL 值的个数
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和

5. 其它函数

函数	说明
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果
CAST()	将一个值转换为指定的数据类型
JSON_EXTRACT	从 json 对象中取值
GROUP_CONCAT() 函数： 常与关键字 GROUP BY 一起使用，能够将分组后的指定字段值都显示出来。

-- 使用 abs 函数求所有水果平均值与最大值差值的绝对值
select abs(avg(f_price)-max(f_price)) from fruits;

-- 使用 length 函数求每个 f_name 的名字与他们的字符长度
select f_name, length(f_name) from fruits group by f_name;

-- 使用 now 函数求当前的日期和时间
select now();

-- 使用 group_concat 函数查询不同 s_id 下对应的所有 f_name 信息
SELECT s_id, GROUP_CONCAT(f_name) FROM fruits
GROUP BY s_id;

--  使用 concat 函数在 f_name 字段值前添加'fruit_'信息
update fruits set f_name = concat('fruit_',f_name);

6. 比较运算符

符号	说明	备注
=	等于
<>, !=	不等于
	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=	严格比较两个 NULL 值是否相等	两个操作码均为 NULL 时，其所得值为 1；而当一个操作码为 NULL 时，其所得值为 0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

like 操作符

查询 name 字段中以“王”开头的所有员工：

SELECT * FROM employee WHERE name LIKE '%王';

SQL 通配符

SQL 通配符必须与 LIKE 运算符一起使用

通配符	说明
%	匹配一个或多个字符
_	仅匹配一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或者 [!charlist]	不在字符列中的任何单一字符

示例表示

'%a'    //以 a 结尾的数据
'a%'    //以 a 开头的数据
'%a%'    //含有 a 的数据
'_a_'    //三位且中间字母是 a 的
'_a'    //两位且结尾字母是 a 的
'a_'    //两位且开头字母是 a 的

TOP

SELECT column_name(s)
FROM table_name
LIMIT number

其它操作符

速查列表，一看就懂。

通配符	说明
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
BETWEEN ... AND	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
别名（Alias）	SELECT column_name AS alias_name FROM table_name
INNER JOIN	在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

一些示例

-- 创建大气质量表
create table Monthly_Indicator(
	city_name varchar(20) not null,
    month_key date not null,
    aqi int(4) not null default 0,
    aqi_range varchar(20) not null default '-',
    air_quality varchar(20) not null default '-',
    pm25 float(6,2) not null default 0,
    pm10 float(6,2) not null default 0,
    so2 float(6,2) not null default 0,
    co float(6,2) not null default 0,
    no2 float(6,2) not null default 0,
    o3 float(6,2) not null default 0,
    ranking int(4) not null default 0,
    primary key(city_name,month_key)
    );
    
-- 为 Monthly_Indicator 表导入外部 txt 文件
load data local infile 'D:/liwork/CDA/MySQL - Li Qi/data/all.txt' 
	into table Monthly_Indicator 
    fields terminated by '\t'
    ignore 1 lines;
    
-- SQL 语句单表查询练习
-- 查询不同月份 PM2.5 的最大值
select month_key, max(pm25) from monthly_indicator
group by month_key;

-- 降序查询不同城市 PM10 的平均值
select city_name, avg(pm10) from monthly_indicator
group by city_name
order by avg(pm10) desc;

-- 对大气质量表进行有选择的查询
select city_name, avg(pm25), avg(pm10) from Monthly_Indicator
where pm25 50
group by city_name, month_key having city_name <'北京'
order by avg(pm25) desc;

-- 多表连接练习
-- 创建学员信息表
create table 学员信息表(
	学号 varchar(5),
    学员姓名 varchar(10),
    年龄 int
);

-- 创建学员成绩表
create table 学员成绩表(
	学号 varchar(5),
    成绩 int
);

-- 为学员成绩表导入数据
load data local infile 'D:/liwork/SQL/data/xycj.csv' 
	into table 学员成绩表
    fields terminated by ','
    ignore 1 lines;

-- 内连接
select 学员信息表.*, 学员成绩表.* 
from 学员信息表 inner join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;

-- 左连接
select 学员信息表.*, 学员成绩表.* 
from 学员信息表 left join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;

-- 右连接
select 学员信息表.*, 学员成绩表.* 
from 学员信息表 right join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;

-- 纵向合并练习
create table t1(
	key1 varchar(20),
    v1 int(4)
    );
    
load data local infile 'D:/liwork/SQL/data/t1.csv' 
	into table t1
    fields terminated by ','
    ignore 1 lines;
    
create table t2(
	key2 varchar(20),
    v2 int(4)
    );

load data local infile 'D:/liwork/SQL/data/t2.csv' 
	into table t2
    fields terminated by ','
    ignore 1 lines;

-- 用 union 合并 t1 与 t2 表
select t1.* from t1
union
select t2.* from t2;

-- 用 union all 合并 t1 与 t2 表
select t1.* from t1
union all
select t2.* from t2;

复杂语句示例

/*数据统计查询 分类 排序*/
select 区域城市,COUNT(0)as 宴会场次,SUM(桌数) as 桌数, SUM(购酒数) as 购酒数, SUM(实际回收瓶盖) as 瓶盖回收数,
	COUNT(终审结果 = '通过' OR NULL) as 通过,
	COUNT(终审结果 = '不通过' OR NULL) as 不通过,
	COUNT(终审结果 ='审核中' OR NULL) as 审核中,
	COUNT(终审结果 = '降档通过' OR NULL) as 降档通过,
	COUNT(星期 = '星期一' OR NULL) as 星期一,
	COUNT(星期 = '星期二' OR NULL) as 星期二,
	COUNT(星期 = '星期三' OR NULL) as 星期三,
	COUNT(星期 = '星期四' OR NULL) as 星期四,
	COUNT(星期 = '星期五' OR NULL) as 星期五,
	COUNT(星期 = '星期六' OR NULL) as 星期六,
	COUNT(星期 = '星期日' OR NULL) as 星期日,
	COUNT(时间 = '早上' OR NULL) as 早上,
	COUNT(时间 = '中午' OR NULL) as 中午,
	COUNT(时间 = '晚上' OR NULL) as 晚上,
	COUNT(时间 = '中午和晚上' OR NULL) as 中午和晚上
FROM
(SELECT 区域城市,宴会编码,终审结果,跟单员,日期,
 CASE DAYNAME(日期) 
		WHEN 'Monday'     THEN '星期一'
		WHEN 'Tuesday'     THEN '星期二'  
		WHEN 'Wednesday'     THEN '星期三'  
		WHEN 'Thursday'     THEN '星期四'
		WHEN 'Friday'     THEN '星期五'  
		WHEN 'Saturday'     THEN '星期六'  
		WHEN 'Sunday'     THEN '星期日' 				
	ELSE '其他' END
	星期,时间,桌数,购酒数,实际回收瓶盖,宴会类型 1
FROM
 (SELECT 区域城市,宴会编码,终审结果,跟单员, SUBSTRING_INDEX(宴会时间,' ',1)as 日期,  
SUBSTRING_INDEX(宴会时间,' ',-1)as 时间,桌数,购酒数,实际回收瓶盖,宴会类型 1
FROM export_828) as t1) as t2 GROUP BY 区域城市 ORDER BY 宴会场次 DESC

/*按日期统计 排序 convert*/
SELECT 日期,COUNT(1)as 当天宴会数量,区域城市,宴会编码,终审结果,跟单员,桌数,购酒数,实际回收瓶盖,宴会类型 1
FROM 
(SELECT 区域城市,宴会编码,终审结果,跟单员, SUBSTRING_INDEX(宴会时间,' ',1)as 日期,
SUBSTRING_INDEX(宴会时间,' ',-1)as 时间,桌数,购酒数,实际回收瓶盖,宴会类型 1
FROM export_828) as t1
GROUP BY 日期
ORDER BY CONVERT(日期,date) ASC

/* 按日期、城市、宴会类型 查询统计*/
SELECT 日期,COUNT(1)as 当天宴会数量,
	COUNT(`区域城市` = '成都' OR NULL) AS 成都,
	COUNT(`区域城市` = '德阳' OR NULL) AS 德阳,
	COUNT(`区域城市` = '嘉兴' OR NULL) AS 嘉兴,
	COUNT(`区域城市` = '遂宁' OR NULL) AS 遂宁,
	COUNT(`区域城市` = '盐城' OR NULL) AS 盐城,
	COUNT(`宴会类型 1` = '升学宴' OR NULL) AS 升学宴数量
FROM 
(SELECT 区域城市,宴会编码,终审结果,跟单员, SUBSTRING_INDEX(宴会时间,' ',1)as 日期,
SUBSTRING_INDEX(宴会时间,' ',-1)as 时间,桌数,购酒数,实际回收瓶盖,宴会类型 1
FROM export_828) as t1
GROUP BY 日期
ORDER BY CONVERT(日期,date),`区域城市`,`宴会类型 1` ASC

/* 查询不同颜色下的产品销售金额 */
select colornote as 颜色, goodsid,sum(goodsprice * amount) as 销售额
from orderdetail left join goodscolor on orderdetail.colorid = goodscolor.colorid
group by 颜色, goodsid
order by 颜色, 销售额 desc;