常见函数

功能：将一组逻辑语句封装到方法体中，对外暴露方法名

好处：1.隐藏了实现细节 2.提高了代码的重用性

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

特点： ①叫什么（函数名）

​ ②干什么（函数功能）

分类：

​ 1.单行函数

​ 如：concat、length、ifnull等

​ 2.分组函数

​ 功能：做统计使用，又称为统计函数

一、单行函数

字符函数

1. length 获取参数值的字节个数

SELECT LENGTH('芜湖hhh');

2. concat 拼接字符串

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

3. upper、lower

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

4. substr、substring

# 注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆湛远',7) out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆湛远',1,3) out_put;

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

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

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;

6. trim

SELECT TRIM('  	张翠山	');

SELECT TRIM('a' FROM'aaaaaaaa张aaaaa翠山aaaaaaa') AS out_put;

7. lpad 用指定的字符实现左填充指定长度 超过了会从右边截断

SELECT LPAD('殷素素',10,'*');

8. rpad

SELECT RPAD('殷素素',12,'ab') AS out_put;

9. replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏')

数字函数

1. round 四舍五入

SELECT ROUND(4.5);
SELECT ROUND(1.567,2); //1.57

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

SELECT CEIL(1.002);//2
SELECT CEIL(-1.02);//-1

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

SELECT FLOOR(9.99);//10
SELECT FLOOR(-9.99);//-9

4. truncate 截断

SELECT TRUNCATE(1.69999,1);//1.6

5. mod 取余

被除数正，余数正

被除数负，余数负

SELECT MOD(10,3);//1
SELECT MOD(-10,3);//-1

日期函数

1. now 返回当前系统日期-时间

SELECT NOW();

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

SELECT CURDATE();

3. curtime 返回当前系统时间，不包含日期

SELECT CURTIME();

4. year 可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW());
SELECT YEAR('1997-1-1');

SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()); 英文显示月份

5. str_to_date 将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d');
#查询日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%Y-%c-%d');

6. date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
# 查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') FROM employees;

其他函数

SELECT VERSION();
SELECT DATABASES();
SELECT USER();

流程控制函数

1. if 函数 if else 的效果

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

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金')
FROM employees;

2. case 函数 switch case 效果

case 要判断的字段或表达式

when 常量1 then 要显示的值或语句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

case

when 条件1 then 要显示的值或语句1;

when 条件2 then 要显示的值或语句2;

...

else 要显示的值或语句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. 简单的使用

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. 参数支持哪些类型

特点：

1. sum、avg一般用于处理数值型

   max、min、count可以处理任何类型

2. 以上分组函数都忽略null值

3. 可以和distinct 搭配实现去重

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

4. count 函数的详细介绍

 SELECT COUNT(salary) FROM employees;
 #统计行数
 SELECT COUNT(*) FROM employees; 
 SELECT COUNT(1) FROM employees;

效率：

MYISAM 存储引擎下，count(*) 的效率最高

INNODB 存储引擎下，COUNT(*) 和COUNT(1) 的效率差不多，比COUNT(字段)要高一些

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