DQL语言4：常见函数

概述

概念：

类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处：

1、隐藏了实现细节 2、提高代码的重用性‘

调用：

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

特点：

①叫什么（函数名）
②干什么（函数功能）

分类：

1.单行函数
如 concat、length、ifnull等
2.分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

常见函数一：单行函数

单行函数的分类

1. 字符函数

 length concat substr instr trim upper lower lpad rpad replace

2. 数学函数

 round ceil floor truncate mod

3.日期函数

now curdate curtime year month monthname day hour mintue second str_to_date date_format

4.其他函数

version database user

5.控制函数

if case

一、字符函数

1. length 获取参数值的字节个数
SELECT LENGTH('join');
SELECT LENGTH('站立xyxy');

2. concat 拼接字符串
SELECT CONCAT(last_name, '***',first_name) FROM employees;

3. upper lower 字母大小写的转换
SELECT UPPER('happy');
SELECT LOWER('HAPPY');

4. substr\substring(有多个重载的函数）
注意：索引从1开始，不是0
-- 截取从指定索引处开始到末尾的所有字符
SELECT SUBSTR('abcdefg',3);
-- 截取从指定索引处开始 指定字符长度 的字符
SELECT SUBSTR('abcdefghijklmn',6,3);

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

5. instr 返回子串第一次出现的索引，若子串没有出现则返回0
SELECT INSTR('database management system and database', 'data') AS output;

6. trim 去掉字符串前后所有指定的字符
SELECT LENGTH(TRIM('           www   ')) AS output;
SELECT TRIM( 'a' FROM 'aaa bbbccc aaaddd aaaaaaa') AS output;

7. lpad 用指定字符('x')对字符串('database')进行左填充，使其填充到指定长度(10)
SELECT LPAD('database',10,'x') AS output;

8. rpad
SELECT RPAD('database',10,'x') AS output;

9. replace 替换
SELECT REPLACE('database management system and database', 'database', 'hello') AS out_put;

二、数学函数

1. round 四舍五入
SELECT ROUND(-2.33);
SELECT ROUND(-2.5555, 3); -- 第二个参数为保留的小数点后的位数

2. ceil 向上取整，返回值为>=该参数的最小整数
SELECT CEIL(-1.11);

3. floor 向下取整，返回值为<=该参数的最小整数
SELECT FLOOR(-2.10);

4. truncate 截断
SELECT TRUNCATE(-1.111222, 1);

5. mod 求余
/*
mod(a,b): a- a/b*b;
*/
SELECT MOD(10,-3);

三、日期函数

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

1. now：返回当前系统日期+时间
SELECT NOW();

2. curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

3. curtime 返回当前时间，不包含日期
SELECT CURTIME();

#可以获取指定的部分 年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1999-1-1') AS 'year';

SELECT MONTH(NOW()) AS 'month';
SELECT MONTHNAME(NOW()) AS 'month';

4. str_to_date(str,format)函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值。
SELECT STR_TO_DATE('1999-1-1', '%Y-%m-%d') AS output;

--案例：查询入职日期为1992-4-3的员工信息
SELECT * FROM employees
	WHERE hiredate <=> STR_TO_DATE('1992-4-3', '%Y-%c-%d');

5. date_format(date,format)函数把数据库的日期转换为format形式的字符串
SELECT DATE_FORMAT(NOW(), '%Y年 %m月%d日');

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

四、其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

五、流程控制函数

1. if函数 
IF(expr1,expr2,expr3):
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，
则 IF()的返回值为expr2; 否则返回值则为 expr3

#案例
SELECT IF(10>5, '1', '2');

SELECT commission_pct AS 奖金率, IF(commission_pct IS NOT NULL, 'yes', 'no')
FROM employees;

2. case函数
使用一:switch case 的效果

/* java中的用法 */
switch(变量或表达式){
	case 常量1:语句1;break;
	...
	default:语句n;break;
}


/* mysql中的用法 */
case 要判断的字符或表达式
when 常量1 then 要显示的值1 或 语句1
when 常量2 then 要显示的值2 或 语句2
when 常量3 then 要显示的值3 或 语句3
……
else 要显示的值n 或 语句n;
end


/*
案例：查询员工的工资，要求
部门号=30， 显示的工资为1.1倍
部门号=40， 显示的工资为1.2倍
部门号=50， 显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT salary AS 原工资, 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
ORDER BY department_id;

case函数的使用二：类似与于多重if
/* java中的用法 */
if(条件1){
	语句1;
}else if(条件2){
	语句2;
}
...
else{
	语句n;
}


/*	mysql中的用法 */
case  				#注意，此时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 class
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;

SELECT SUM(salary) 总共, ROUND(AVG(salary),2) 平均, MAX(salary) 最高工资,
MIN(salary) 最低工资, COUNT(salary) 总数 FROM employees;

# 参数支持哪些类型
# sum, avg 函数的参数只能是数值，否则没有意义
SELECT SUM(last_name), AVG(last_name) FROM employees;

# max, min, count 任何参数都可以
SELECT MAX(last_name), MIN(last_name) FROM employees;
SELECT COUNT(last_name), COUNT(commission_pct) FROM employees; #count统计非空值的个数


#2. 是否忽略null
#已知null和任何数相加结果均为Null, 故avg,sum都是忽略null的
SELECT AVG(commission_pct), SUM(commission_pct),SUM(commission_pct)/35, SUM(commission_pct)/105 FROM employees;

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

#3. 和distinct搭配使用
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;

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

#4. count函数的详细介绍
-- 统计总行数
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees; #相当于加了一列
SELECT COUNT('w') FROM employees;

效率问题：
MYISAM 存储引擎下，COUNT(*)的效率高
INNODB 存储引擎下，COUNT(*)和COUNT(1)的效率差不多，但都比COUNT('w')效率高
故一般使用count(*)统计行数

#5. 和分组函数一同查询的字段有限制
SELECT AVG(salary), employee_id FROM employees; #没有意义