MySQL学习记录 – 04- 常见函数学习

根据尚硅谷的视频教程学习MySQL，学习记录-04-常见函数学习。
所有操作开始之前，建议先打开对应的库：

USE 库名;USE employees;

=>基本概念
将一组逻辑语句封装在方法体中，对外暴露方法名
好处：隐藏了实现的细节，提高代码的重用性
特点：函数名是什么 + 实现什么功能

SELECT 函数名(实参列表) FROM 表名

=>分类

1 单行函数：如 concat, length, isfull等，包含：

字符函数: length, concat, substr, instr, trim, upper, lower, ipad, rpad, replace
数学函数: round, ceil, floor, truncate, mod
日期函数: now, curdate, curtime, year, month, str_to_date, date_format
其他函数: version, user
流程控制函数: if else, case when

2 分组函数：做统计使用，或称为 统计函数、聚合函数：

sum, avg, max, min, count, mean

=>具体分类

一、字符函数

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

SELECT LENGTH(\'John\')；SELECT LENGTH(\'张三丰hahaha\');---一个汉字占2/3个字节

1. concat：拼接字符串

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

1. upper , lower：改变大小写

SELECT UPPER(\'john\');SELECT LOWER(\'john\');SELECT CONCAT(UPPER(last_name),\'_\',LOWER(first_name)) 姓名 FROM employees;

1. substr, substring：截取字符串

注意，索引从1开始，不是0
1个参数：截取从索引处开始到后面所有字符
2个参数：截取从索引处指定字符长度的字符

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;

1. instr：返回子串在原字符串中第一处出现的索引，找不到返回0

SELECT INSTR(\'杨不悔爱上了殷六侠\',\'殷六侠\') AS out_put; --> 7SELECT INSTR(\'杨不悔爱上了殷六侠\',\'殷八侠\') AS out_put; --> 0

1. trim：去除字符前后的指定信息，默认为空格

SELECT TRIM(\'      张翠山    \') as result; -->张翠山SELECT TRIM(\'a\' FROM \'aaaaaaa张aa翠aaaa山aaaaaaaaaaaaa\') as result; -->张aa翠aaaa山

1. lpad：用指定的字符实现左填充指定长度

SELECT LPAD(\'殷素素\',5,\'*\'); --> ***殷素素，左侧填充3个*，实现整体长度为5SELECT LPAD(\'殷素素\',2,\'*\'); --> 殷素，原字符长度已经超过指定数据，需要截断

1. rpad：用指定的字符实现右填充指定长度

SELECT RPAD(\'殷素素\',12,\'ab\'); --> 殷素素ababababa,右侧填充ab实现指定长度

1. replace：将所有指定字符替换为目标字符

SELECT REPLACE(\'张无忌爱上了周芷若\',\'周芷若\',\'赵敏\') as result; -->将周芷若替换为赵敏

二、数学函数

1. round：四舍五入

SELECT ROUND(1.65);SELECT ROUND(1.658,2);

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

SELECT CEIL(1.52);SELECT CEIL(1.00); ---->1SELECT CEIL(-1.25); ---->-1

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

SELECT  FLOOR(1.52); ----1SELECT  FLOOR(-1.52);  ---->-2

1. truncate 截断，截断要求小数点位数后的内容

SELECT  TRUNCATE(1.6999,1); ----> 1.6

1. mod 取余

计算思路：mod(a,b):   a-a/b*bSELECT  MOD(10,3); ---> 1SELECT 10%3;SELECT  MOD(-10,-3);--->-1SELECT  MOD(10,-3); --->1

三、日期函数

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

SELECT NOW()

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

SELECT CURDATE();

1. curtime 返回当前时间，不包含日期

SELECT CURTIME();

1. year，month, hour 获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;SELECT MONTH(NOW()) 月;SELECT YEAR(\'1998-8-29\');SELECT MONTHNAME(\'1998-08-23\') 月;

1. str_to_date: 将日期格式的字符转化成日期格式

SELECT STR_TO_DATE(\'9-13-1999\',\'%m-%d-%Y\') date;  -- 1999-09-13SELECT STR_TO_DATE(\'1998-3-2\',\'%Y-%c-%d\') as out_put;--1998-03-02

%Y –> 四位的年份：1998
%y –> 两位的年份：98
%m –>月份（01，02，…11,12)
%c –>月份（1，2，…11,12)
%d –> 日（01，02，…11,12)
%H –> 小时（24小时制）
%h –>小时（12小时制）
%i –> 分钟（00，01，02，…59)
%s –> 秒(00,01,02,…59)

SELECT * FROM employeesWHERE hiredate = STR_TO_DATE(\'4-3 1992\',\'%m-%d %Y\');

1. date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),\'%y年-%m月-%d日\') as out_put; ---20年-08月-22日SELECT last_name, DATE_FORMAT(hiredate,\'%m月/%d日 %y年\') 入职日期 FROM employees;

四、其他函数

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

五、流程控制函数

1. if 函数： if else 的效果

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

1. case 函数（控制结构） – switch 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 department_id, salary,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS new_salaryFROM employees;

1. case 函数（控制结构） – 类似于多重if，判断区间

CASEWHEN 条件1 THEN 要显示的值1或语句1WHEN 条件2 THEN 要显示的值2或语句2...ELSE 要显示的值n或者语句nEND

案例：查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则，显示为D级别

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

六、单行函数小节练习

1. 显示系统时间（注：日期+时间）

SELECT NOW();

1. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT employee_id, last_name, salary, salary*1.2 as \"new salary\" FROM employees;

1. 将员工姓名按照首字母排序，并写出名字的长度（Length）

SELECT last_name, SUBSTR(last_name, 1,1) 首字符,LENGTH(last_name) as len FROM employeesORDER BY 首字符;

七、分组函数

1. 简单使用

SELECT SUM(salary） FROM employees;SELECT  sum(salary) 和, AVG(salary) 平均, COUNT(salary) 计数 FROM employees;

1. 参数支持类型：
   SUM , AVG 只处理数值型数据
   MAX, MIN, COUNT 可以处理所有类型的数据

SELECT SUM(last_name) , AVG(last_name) FROM employees;SELECT MAX(last_name) , MIN(last_name) FROM employees;SELECT MAX(hiredate) , MIN(hiredate) FROM employees;SELECT COUNT(hiredate) FROM employees;SELECT COUNT(commission_pct) FROM employees;

1. 以上分组函数都忽略NULL值

SELECT SUM(commission_pct), AVG(commission_pct), SUM(commission_pct)/35, SUM(commission_pct)/107FROM employees;

1. 和DISTINCT 搭配

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

1. COUNT函数详细介绍

SELECT COUNT(*) FROM employees;  -->统计行数，只要一条记录中有一个数据不为null，就会被统计SELECT COUNT(1) FROM employees; -->依旧统计行数，在前面加入一列常量值，然后统计个数

效率：
MYISAM存储引擎下，COUNT() 的效率最高
INNODB存储引擎下，COUNT() 和COUNT(1)的效率一致，但是高于COUNT(字段)的效率

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

SELECT AVG(salary), employee_id FROM employees; -->虽然有结果，但是对应平均薪资的员工号毫无意义

八、分组函数小节练习

1. 查询员工工资的最大值，最小值，平均值，总和

SELECT max(salary), min(salary), avg(salary), sum(salary) FROM employees;

1. 查询员工表中的最大入职时间和最小入职时间之间的相差天数（DIFFERENCE)

SELECT max(hiredate), min(hiredate), DATEDIFF(max(hiredate),min(hiredate))  DIFFERENCE from employees;--使用DATEDIFF函数，用来计算日期差

1. 查询部门编号为90的员工个数

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