MySQL DQL语言——常见函数

常见函数

employees 表

概念：
类似于java的方法，将一组逻辑语句封装在方法体重，对外暴露方法名
好处：
1、隐藏了实现的细节
2、提高代码的复用性
调用：
select 函数名（实参列表） 【from 表】
特点：
①叫什么（函数名）
②干什么（函数功能）
分类：
1、单行函数
如：concat、length、ifnull等
2、分组函数 功能：做统计使用，又称为统计函数，聚合函数、组函数

一、单行函数

(1)、字符函数
1.length获取参数值的字节个数

SELECT LENGTH('张');
SHOW VARIABLES LIKE '%char%';

2.concat拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

3.upper、lower

SELECT UPPER('abcd');
SELECT LOWER('ABCD');

示例：将姓变大写，名变小写，拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name))姓名 FROM employees;

4.substr截取字符
注意：索引从1开始
截取从指定索引出后面的字符

SELECT SUBSTR('小龙女爱杨过',4) 神雕侠侣;

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

SELECT SUBSTR('小龙女爱杨过',1,3) 神雕侠侣;

案例：姓名中首字母字符大写，其他字符小写然后用—拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(first_name,2))) 姓名 FROM employees;

5.instr返回字符串第一次出现的索引，如果找不到就返回0

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS 倚天屠龙记;

6.trim 去掉空格

SELECT LENGTH(TRIM('   张无忌   ')) AS 倚天屠龙记;
SELECT TRIM('b' FROM 'bbbbbbbbbbb张bbb无bbb忌bbbbbbbbb') AS 倚天屠龙记;

7.lpad用指定的的字符实现左填冲指定长度

SELECT LPAD('殷素素',10,'*') AS  倚天屠龙记;

8.Rpad 用指定的的字符实现右填冲指定长度

SELECT RPAD('殷素素',10,'*') AS  倚天屠龙记;

9.replace

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS 倚天屠龙记;

(2)、数学函数
1.round 四舍五入

SELECT ROUND(1.45);
SELECT ROUND(1.567,2);

2.ceil向上取整,返回>=该参数最小的整数

SELECT CEIL(1.10);

3.floor向下取整，<=返回该参数的最大整数

SELECT FLOOR(-9.99);

floot解释
其功能是“向下取整”，或者说“向下舍入”，即取不大于x的最大整数(与“四舍五入”不同，下取整是直接去掉小数部分)，例如：[1]
x=3.14，floor(x)=3
y=9.99999，floor(y)=9
z=-9.9，floor(z)=-10

与floor函数对应的是ceil函数，向上取整，一个地板，一个天花板

4.truncate截断

SELECT TRUNCATE(1.6888,2);

5.mod取余

SELECT MOD(10,3);

(3)、日期函数
1.now返回当前系统日期+时间

SELECT NOW();

2.curdate返回当前日期，不包含时间

SELECT CURDATE();

3.curtime返回当前日期，不包含日期

SELECT CURTIME();

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

SELECT YEAR(NOW())  年;
SELECT MONTHNAME(NOW()) 英文月;
SELECT MONTH(NOW()) 月; 
SELECT DAY(NOW()) 日;
SELECT HOUR(NOW())时;
SELECT MINUTE(NOW())分;
SELECT SECOND(NOW()) 秒;

序号	格式符	功能
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份（01，02…11,12）
4	%c	月份（1,2…11,12）
5	%d	日（01,02…）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00,01…59）
9	%s	秒（00,01…59）

4.str_to_date将日期格式的的字符转换为指定的日期格式

SELECT STR_TO_DATE('1997-1-12','%Y-%c-%d') 日期;

案例①：查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate= '1992-4-3' ;
--转换为指定的日期格式
SELECT * FROM employees WHERE hiredate= STR_TO_DATE('4-3 1992','%c-%d %Y') ;

5.date_format将日期转换为字符

SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日') 日期;

案例①：查询有奖金的员工入职日期

 SELECT last_name,DATE_FORMAT(hiredate, '%c月/%d日/%Y年')FROM employees WHERE commission_pct IS NOT NULL;

(4)、其他函数

SELECT VERSION();

SELECT DATABASE();

SELECT USER();

(5)、流程控制
1、if函数：if else的效果

SELECT IF(10>5,'大','小') 比大小;

2、case函数：switch case 的效果

java中：
switch(变量或表达式){

	case:常量1: 语句一;break; 
	...
	default:语句n;break;
}

mysql中：
case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1;

when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句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;

3.case函数的使用二：类似于多重if

java中：
if(条件1){
    语句1；
}else if(语句2){
    语句2;
}else{
    语句n;
}	

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

案例：查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示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;

二、分组函数

功能：用作统计使用，有称为聚合函数或统计函数或组函数

分类:
sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
特点：
1、sum、avg一般处理数值型 ;max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重运算
4、count函数的单独介绍 一般使用count(*)统计行数
5、和分组函数一同查询的字段要求是group by后的字段

1、简单的使用

SELECT SUM(salary) 总和 FROM employees;
SELECT AVG(salary) 平均值 FROM employees;
SELECT MAX(salary) 最大值 FROM employees;
SELECT MIN(salary) 最小值 FROM employees;
SELECT COUNT(salary) 个数 FROM employees;

2、参数支持哪些类型

注意：sum、avg一般处理数值型;
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;

注意：max、min、count可以处理任何类型 
SELECT MAX(last_name),MIN(last_name),COUNT(last_name) FROM employees;
SELECT MAX(hiredate) ,MIN(hiredate),COUNT(last_name) FROM employees;

3、是否忽略NULL值

commission_pct (奖金率)，可能为空，并不是所有员工都有奖金
SELECT SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct/35),SUM(commission_pct/107) FROM employees;

SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

以上分组函数都忽略null值

4、和distinct搭配

去重和没去重：
可以和distinct搭配实现去重运算
SELECT SUM(DISTINCT (salary)),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT (salary)),COUNT(salary) FROM employees;

5、count函数的用法

count函数的单独介绍 一般使用count(*)统计行数
SELECT COUNT(salary)  FROM employees;
 
SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;
效率：
MYISAM 存储引擎下,COUNT(*)的效率高
INNODB 存储引擎下,COUNT(*)和 COUNT(1)效率差不多，比count(字段)要高一些

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

SELECT AVG(salary),employee_id FROM employees;
没有意义，和分组函数一同查询的字段要求是group by后的字段

案例①：查询公司员工工资的最大值、最小值、平均值、总和

select max(salary)最大值,min(salary) 最小值,avg(salary)平均值,sum(salary)总和 from employees;

案例②：查询部门编号为90的员工个数

SELECT COUNT(*) FROM employees WHERE department_id=90;