MySQL-函数

函数

类似于java方法，将一组逻辑语句封装在方法体中，对外暴露方法名
隐藏了实现细节，提高代码的重用性
语法：select 函数名（列表参数）[from 表]
分类：

单行函数

拼接CONCAT(str,str,…)

SELECT
CONCAT(employees.last_name,employees.first_name) 姓名
FROM
employees

去重 DISTINCT

SELECT
DISTINCT employee_id AS id
FROM
employees

判null IFNULL(有null的字段,0)

SELECT
IFNULL(commission_pct,0)
FROM
employees

ISNULL()

判断某字段或表达式是否为null，如果是返回1，否则返回0

字符函数

查看字符集

SHOW VARIABLES LIKE '%char%';

LENGTH()

USE myemployees;
SELECT LENGTH(first_name) 
FROM employees

CONCAT(str,str)

SELECT
CONCAT(last_name,first_name)
FROM
employees

UPPER()、LOWER()

SELECT
CONCAT(UPPER(last_name),LOWER(first_name))
FROM
employees

SUBSTR()、SUBSTRING()

索引从1开始
截取从指定索引处后面的字符

SELECT
SUBSTR('ZHNAGRUIZAISHANGLE' ,2) output;

截取从指定索引处指定字符长度

SELECT
SUBSTR('ZHNAGRUIZAISHANGLE' ,2,4) output;

首字母大写

SELECT 
CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)))
from employees;

INSTR()

返回子串第一次出现的索引，找不到返回0

SELECT 
INSTR('abcdefg','cde');

TRIM()

SELECT 
TRIM('    ZHNAG     ');

SELECT 
TRIM('a' FROM 'aaaaaaHHHaaaaaaa');

LPAD() 左填充

如果小于字符串长度，从左到右剪切

SELECT 
LPAD('zzz',6,'#');

RPAD() 右填充

SELECT 
RPAD('abc',7,'#');

REPLACE() 替换

SELECT 
REPLACE('aaabbbsss','b','H');

数学函数

ROUND() 四舍五入

SELECT ROUND(-1.65);

小数点后保留3位

SELECT ROUND(-1.65323,3);

ceil() 向上取整，返回>=该参数的最小整数

SELECT CEIL(-1.23);

floor() 向下取整 返回<=该参数的最大整数

SELECT FLOOR(-1.23);

truncate() 截断

SELECT TRUNCATE(1.99999,2);

mod() 取余

mod(a,b)-> a-a/b*b

SELECT MOD(-3,-5),MOD(-10,-3),MOD(10,-3);

rand() 获取随机数，返回0~1之间的小数

日期函数

格式符	功能
%Y	四位的年份
%y	两位的年份
%m	月份（01、02…）
%c	月份（1、2、3…）
%d	日（01、02…）
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（01、02…）
%s	秒（01、02…）

now() 返回当前系统日期+时间

SELECT NOW();

CURDATE() 返回当前系统日期，不包含时间

SELECT CURDATE();

CURTIME() 返回当前系统时间，不包含日期

SELECT CURTIME();

可以获取指定的部分，年、月、日、时、分、秒

SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());

SELECT YEAR('1997-05-13');

monthname()以英文形式返回月

SELECT MONTHNAME(NOW()),MONTHNAME('1997-07-13');

str_to_date 将字符转换为日期

SELECT STR_TO_DATE('1997-5-13','%Y-%m-%d');

date_format 将日期转换成文字

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');

datediff() 计算两个日期之差

SELECT DATEDIFF('2019-6-1','2019-5-30');

其他函数

VERSION() 数据库版本

DATABASE() 数据库名

USER() 当前用户

password(‘字符’) 返回密文

MD5(‘字符’) MD5加密

流程控制函数

if()

if(条件表达式，表达式1，表达式2)：如果表达式成立，返回表达式1，否则返回表达式2

SELECT if(10>5,'da','xiao');

case 使用方式一 类似switch语句

case 要判断的表达式或字段
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

case使用方式二 类似多重if

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

分组函数

做统计使用，又称统计函数、聚合函数、组函数
分组函数都忽略null值

1、分组函数分类：

1、sum 求和
2、avg 求平均
3、max 最大值
4、min 最小值
5、count 计算个数

2、参数支持

• sum、avg 处理数值型
• max、min 、count 可以处理任何类型

3、分组函数都忽略null

SELECT
SUM(commission_pct)/35,AVG(commission_pct),COUNT(employee_id)
from 
employees

4、和distinct搭配使用

SELECT
SUM(salary),SUM(DISTINCT salary)
FROM
employees

5、count函数

统计行数

SELECT
COUNT(*)
FROM
employees

SELECT
COUNT(1)
FROM
employees

效率：
MYISAM存储引擎下，count( * )效率高些
INNODB存储引擎下，count( * )和count(1)效率差不多

6、和分组函数一同查询的字段有限制

和分组函数一同查询的字段要求是group by 后的字段