MySQL笔记——常见函数整理

一、概述

调用：select 函数名(实参列表) 【from 表】;

二、单行函数

1、字符函数

• concat:连接

select concat(last_name,'_',first_name) 姓名 from employees

• upper:变大写
• lower：变小写
• substr、substring:截取子串

#将姓变大写，名变小写，然后拼接
SELECT 
  CONCAT(
    UPPER(last_name),
    '_',
    LOWER(first_name)
  ) 姓名 
FROM
  employees ;

#截取从指定索引处后面所有字符 【subsrt(字符串，索引起始位置)】
SELECT SUBSTR('好喜欢可爱的小威廉',7) out_put;

#截取从指定索引处指定字符长度的字符 【subsrt(字符串，索引起始位置，长度)】 
SELECT SUBSTR('好喜欢可爱的小威廉',1,3) out_put;

#例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT 
  CONCAT(
    UPPER(SUBSTR(last_name, 1, 1)),
    '_',
    LOWER(SUBSTR(last_name, 2))
  ) output 
FROM
  employees ;

• replace：替换
  
• length：获取字节长度
  （数据库utf-8 一个字母占一个字节，一个汉字占三个字节【JDK为两个字节】）
• trim:去前后空格
  若想指定字符：

  
  #去掉首尾的A
  SELECT TRIM('A' FROM 'AAAAAAAAAAA小威廉好可爱AAAAAAAAAA') AS output;

• lpad：左填充
• rpad：右填充
  用指定的字符实现填充指定长度 【lpad(字符串，总长度，所用字符)】

select lpad('kris',15,'!') as out_put;

• instr:获取子串第一次出现的索引,找不到返回0

  SELECT INSTR('小威廉好可爱好乖好幼稚','可爱') AS find;

2、数学函数

• ceil:向上取整（返回大于等于该参数的最小整数）

select ceil(1.02);

• round：四舍五入

SELECT ROUND (1.67)
SELECT ROUND (1.47)
SELECT ROUND (1.578,2); #小数点后保留两位

• mod:取模

SELECT MOD(10,3);  #相当于select 10%3  结果是1,，一正一负结果符号与被除数一致

• floor：向下取整(返回小于等于该参数的最大整数)

select floor(9.99);

• truncate:截断(保留小数多少位)

select truncate (1.5555,1);

rand:获取随机数，返回0-1之间的小数

3、日期函数

• now：返回当前日期+时间

SELECT NOW();

• year:返回年
• month：返回月
• day:返回日

SELECT YEAR(NOW()) 年;
SELECT YEAR(hiredate) 年 FROM employees;  #查询员工入职年份
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月; #返回 月份的英文

• date_format:将日期转换成字符

• str_to_date:将字符转换成日期
  
  
  实际应用：

1. date_format:将日期转换成字符

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

2. str_to_date:将字符转换成日期

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put；

#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

• curtime：返回当前系统时间，不包含日期

select curtime();

• curdate:返回当前日期，不包含时间

SELECT CURDATE();

hour:小时
minute:分钟
second：秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月

4、其他函数

• version 当前数据库服务器的版本

select version();

• database 查看当前打开的数据库

select database();

• user当前用户

select user();

• password(‘字符’)：返回该字符的密码形式
• md5(‘字符’):返回该字符的md5加密形式

5、流程控制函数

• ①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2【类似三目运算符】

SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呜呜呜','有奖金，嘤嘤嘤') 备注
FROM employees;

• ②case使用1
  case 要判断的变量或表达式或字段
  when 常量1 then 要显示的值1或语句1
  when 常量2 then 值2
  …
  else 值n
  end

/*
案例：查询员工的工资，要求：
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
部门号=50，显示工资为1.3倍
其他部门，显示为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

• ③case使用2
  case
  when 条件1 then 要显示的值1或语句1
  when 条件2 then 值2
  …
  else 值n
  end

/*
案例：查询员工的工资情况
如果工资>2w,A
工资>15000 B
工资>1w,C
否则，等级D
*/

SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

三、分组函数（统计函数，聚合函数，组函数）

作用：做统计使用

1、分类

max 最大值
min 最小值
sum 和
avg 平均值
count 计算非空值个数

简单使用的用法都一样，例如：

SELECT SUM(salary) FROM employees;

2、特点

①语法
select max(字段) from 表名;

②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型

③以上分组函数都忽略null

④都可以搭配distinct使用，实现去重的统计
select sum(distinct 字段) from 表; 【去重之后再求和】

⑤count函数

• count(字段)：统计该字段非空值的个数
• count(*):统计结果集的行数【使用此统计行数较多】
• count(‘赵丽颖’) from 表【表示在每一列加一个‘赵丽颖‘字段，再去统计有多少列，妙哇！】
  此处‘赵丽颖’最简单就是变成1：
• count(1):统计结果集的行数

效率上：
MyISAM存储引擎，count()最高
InnoDB存储引擎，count()和count(1)效率>count(字段)

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

习题

# 1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均数,SUM(salary) 总和
FROM employees;

# 2.查询员工表中的最大入职时间和最小入职时间的相差天数（diffrence）
#用到datediff这个函数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence
FROM employees;

#3.查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id = 90;