数据库MySQL常见函数

数据库MySQL常见函数：

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。
好处：1.隐藏了实现细节 2.提高了代码的重用性。
调用：select 函数名(实参列表) [from 表]；
特点：
叫什么？（函数名） 干什么(函数功能)。

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

单行函数-字符函数

#案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT 
	CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)),first_name) AS 姓名 
FROM employees;
#5.instr 返回子串第一次出现的索引，如果找不到，返回0.
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;  

#6.trim
SELECT TRIM('    123') AS out_put;  #去除前后空格
SELECT TRIM('a' FROM 'aaa张aa翠山aaaaaa') AS out_put; #去除前后的a

#7.lpad 用指定的字符实现左填充指定长度。
SELECT LPAD('殷素素',10,'*') AS out_put;

#8.rpad用指定的字符实现右填充指定长度
SELECT LPAD('殷素素',10,'*') AS out_put;

#9.replace 替换
SELECT REPLACE('zzzaz','z','b');

单行函数-数学函数

#常见函数-单行函数：
#二、数学函数
#round 四舍五入
SELECT ROUND(-1.45);
SELECT ROUND(-1.45,1); #小数点后保留一位。

#ceil向上取整: 返回>=该参数的最小整数
SELECT CEIL(1.00);

#floor向下取证，返回<=该参数的最大整数
SELECT FLOOR(-9.99);

#truncate 截断
SELECT TRUNCATE(1.65,1);  #小数点后的保留一位，其余的截断。

#mod取余

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

单行函数-日期函数

#常见函数-单行函数：
#三、日期函数
#now 返回当前系统日期+时间
SELECT NOW();

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

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

#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT MONTH(NOW()) 月;

#str_to_date:将日期格式的字符转换成指定的格式
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

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

#data_format 将日期转换成字符
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;

单行函数-其他函数

#常见函数-单行函数：
#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

单行函数-流程控制函数

#常见函数-单行函数：
#五、流程控制函数
#1.if函数：if else的效果
SELECT IF(10<5,'da','xiao');

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

#2.case函数的使用一：swtich case效果。
/
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。
/ 区间判断
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 显示语句
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;

/
总结：
常见函数-单行函数
字符函数：length,concat,substr,instr,trim,upper,lower,lpad,rpad,replace
数学函数: round ceil floor truncate mod
日期函数: now curdate curtime year month monthname day hour minute second str_to_date date_format
其他函数: version database user 
控制函数: if case

*/

分组函数

#分组函数
/

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

分类：
sum 求和、avg 平均值、 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 MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和, ROUND(AVG(salary),1) 均值
FROM employees;

#2.参数支持哪些类型
#3.对于null值的处理
#4.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

#5.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),`job_id` FROM employees; #错误的