4_常见函数
/*
好处:
1.隐藏实现细节
2.提高代码重用性
调用: select 函数名(实参列表) 【from 表】函数参数 需要 用到表中字段;
特点:
1.叫什么(函数名)
2.干什么(功能)
分类:
1.单行函数
字符函数
数学函数
日期函数
其他函数
流程控制函数
如 concat length,ifnull
2.分组函数 统计函数 聚合函数 组函数
功能:统计使用
*/
1.单行函数
一.字符函数
1.length 获取 参数值 的 字节个数
SELECT LENGTH('JOHN');
SELECT LENGTH('张三丰hahaha'); #一个汉字 三个字节 一个英文字母 一个字节 3*3+6=15
SHOW VARIABLES LIKE '%char%'; #utf8 一个汉字 三个字节 ,jdk 一个汉字 两个字节
2.concat 拼接字符串
SELECT CONCAT(LAST_NAME,'_',FIRST_NAME)FROM employees;
3.upper lower
SELECT UPPER('john'); #变大写
SELECT LOWER('SDA'); #变小写
案例:姓大写 名小写 拼接
SELECT CONCAT(UPPER(LAST_NAME),'_',LOWER(FIRST_NAME))FROM employees; # 函数可以嵌套函数
4.substr、substring 截取字符 下标从1开始
SELECT SUBSTR('李莫愁爱上路展元',6); #返回 从6开始的所有字符 路展元
SELECT SUBSTR('李莫愁爱上路展元',4,2)#返回 爱上, 截取 指定索引处(4) 指定字符长度(2) 的 字符
案例:姓名中首字符大写,其他字符小写,然后用 _ 拼接,显示
SELECT CONCAT(UPPER(SUBSTR(LAST_NAME,1,1)),'_',LOWER(SUBSTR(LAST_NAME,2))) FROM EMPLOYEES;
5. instr 返回 字串第一次出现的 索引,如果找不到返回 0
SELECT INSTR('杨不悔爱上了刘霞哥','刘霞哥') #返回 7
SELECT INSTR('杨不刘霞哥悔爱上了刘霞哥','刘霞哥') #返回 3
SELECT INSTR('杨不悔爱上了刘霞哥','刘哥') #返回 0
6.trim 祛 前后空格
SELECT LENGTH(TRIM(' 海洋 ')); #返回6 而不是15
SELECT TRIM('a' FROM 'aaaaaaaaaaaaa海aaaaaaaaa洋aaaaaaaa') 名; #区分大小写 返回 海aaaaaaaaa洋
7. lpad 用指定的字符 实现 左填充 指定长度
SELECT LPAD('苏苏',10,'*') #********苏苏
8. rpad 用指定的字符 实现 右填充 指定长度
SELECT RPAD('苏苏',10,'-') #苏苏--------
9.replace 替换
SELECT REPLACE('阿珍爱上了阿强','阿强','你爹') # 阿珍爱上了你爹
SELECT REPLACE('阿强阿强阿强阿强阿强阿强阿珍爱上了阿强','阿强','你爹') # 阿强 全部替换为 你爹
二.数学函数
1.round 四舍五入
SELECT ROUND(1.45)# 返回1
SELECT ROUND(1.55)# 返回2
SELECT ROUND(1.456,2)# 返回1.46 2 表示 保留小数点后2位
2.ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.003) #返回2
SELECT CEIL(2.56) #返回3
SELECT CEIL(-1.2) #返回 -1
3.floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(1.22)#返回1
SELECT FLOOR(-1.22)#返回 -2
4.truncate 截断
SELECT TRUNCATE(1.2563,2)#返回1.25
5. mod 取余
/*
mod(a,b) : a-a/b*b (a/b取整) a为正数 结果为正数 a为负数 结果为负数 a是被除数
*/
SELECT MOD(10,-3) #10-10/(-3)*(-3)=10-(-3)*(-3)=10-9=1
SELECT MOD(10,3) #10-10/3*3=10-3*3=1
SELECT MOD(-10,-3) #-10-(-10)/(-3)*(-3)=-10-3*(-3)=-10-(-9)=-10+9=-1
SELECT MOD(-10,3) #-10-(-10)/(3)*(3)=-10-(-3)*3=-10-(-9)=-1
三.日期函数
1.now 返回当前系统日期和时间
SELECT NOW();
2.curdate 返回当前系统日期 没有时间
SELECT CURDATE();
3.curtime 返回当前时间,没有日期
SELECT CURTIME();
4.可以获取指定的部分、年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) #年数 返回2022
SELECT YEAR('2023-12-3') #返回2023
SELECT YEAR(HIREDATE) 年 FROM employees;
SELECT MONTH(NOW()) #月数 返回7
SELECT MONTHNAME(NOW()) #返回 英文月数 July
SELECT DAY(NOW()) #天数 返回22
SELECT DAYNAME(NOW()) #返回 英文天数 Friday
SELECT HOUR(NOW()) #小时 返回9
SELECT MINUTE(NOW()) #分钟
SELECT SECOND(NOW()) #秒
5.str_to_date 将字符 通过 指定的格式 转化为日期
SELECT STR_TO_DATE('7-2022-8','%c-%Y-%d') AS OUT_PUT;#输出 2022-07-08
例:查询入职日期为 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');
SELECT *FROM EMPLOYEES WHERE hiredate=STR_TO_DATE('4.3 1992','%c.%d %Y');
6.date_format 日期转化为字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') #22年07月22日
例:查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT LAST_NAME,DATE_FORMAT(HIREDATE,'%m月/%d日 %y年')
FROM employees
WHERE commission_pct IS NOT NULL;
7.两个日期差多少天
SELECT DATEDIFF('2022-10-1','2022-7-22')# 10月1与7月22日相差71天
四.其他函数
SELECT VERSION() #mysql版本
SELECT DATABASE() #当前数据库
SELECT USER() #当前用户
SELECT MD5('王诗雨');#返回该字符密码形式
五.流程控制函数
1.if函数 if else 效果 if(,,_)如果第一个参数为真,那么输出第二个参数,否则输出第三个参数
SELECT IF(5>3,'大于','小于')# 输出大于
案例:输出 有奖金的人 与 无奖金的人 并备注
SELECT LAST_NAME,commission_pct,IF(commission_pct IS NULL,'没奖金 白给','有奖金 海底捞!')
FROM employees;
2.case函数 控制结构
/*
语法1:
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
/
/
案例1
语法1:查询员工的工资,要求
部门号为30 ,显示工资为原工资1.1倍
40 1.2
50 1.3
其他部门 原工资
按升序排列
*/
案例1
SELECT `department_id`,salary 原工资,
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` ASC;
/*
语法2: 类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
/
/
案例2:查询员工的工资的情况
如果工资>20000 显示A级别
如果工资>15000 ,显示B级别
如果工资>10000 显示C级别
否则,显示D级别
*/
案例2
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 级别
FROM employees
ORDER BY salary DESC;
六、练习
例1:将员工的姓名按首字母排序,并写出姓名长度
SELECT SUBSTR(LAST_NAME,1,1)AS 首字符,LAST_NAME,LENGTH(LAST_NAME) AS 姓名长度
FROM EMPLOYEES ORDER BY 首字符 ASC;
2.分组函数
/*
功能:用作统计使用,又称聚合函数 统计函数 组函数
分类 :sum 求和 ,avg 平均值, max 最大值, min 最小值, count 计算个数
特点 :
1 sum avg 处理 数值型
max min count 可以处理 任何类型
2. 是否忽略null值
以上 分组函数 都忽略 null值
3. 可以和 distinct 搭配实现 去重 运算
4. count 函数 单独介绍
一般使用count(*) 用作 统计行数
5. 和分组函数一同查询的字段 要求是 group by 后的 字段
*/
一、简单使用
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),AVG(SALARY),MAX(SALARY),MIN(SALARY),COUNT(SALARY) FROM EMPLOYEES;
SELECT SUM(SALARY),TRUNCATE(AVG(SALARY),2),MAX(SALARY),MIN(SALARY),COUNT(SALARY) FROM EMPLOYEES;
二、参数支持哪些类型
SELECT SUM(LAST_NAME),AVG(LASE_NAME) FROM employees;#对名字 求和,求平均,没意义
SELECT SUM(HIREDATE),AVG(HIREDATE) FROM employees;#对日期 求和,求平均,没意义
SELECT MAX(LAST_NAME),MIN(LAST_NAME)FROM employees;#返回 Zlotkey Abel
SELECT MAX(hiredate),MIN(HIREDATE)FROM employees;#返回 2016-03-03 00:00:00 1992-04-03 00:00:00
SELECT COUNT(commission_pct)FROM employees; #返回36(返回不为空的值的个数) 因为有的人的奖金率为空
SELECT COUNT(last_NAME)FROM employees; #返回108 108个人
三、 是否忽略null值?
#输出结果 :7.95 0.220833 0.220833 0.073611
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/36,SUM(commission_pct)/108 FROM EMPLOYEES;
#表示:忽略null值 对 sum 来说 如果没有忽略null 那么 null+任意数值应该为0 而不是7.95 因此 表示忽略null值
#对 avg 来说 如果没有忽略null值 那应该是总数除以108 得到0.073611 而答案是 0.220833 表示 除以的人数为36 因此 计算忽略 null值
SELECT MAX(commission_pct),MIN(commission_pct)FROM EMPLOYEES;
输出结果:0.40 0.10 没有null值 表示 忽略了 null值
SELECT COUNT(commission_pct)FROM employees;
输出结果36 表示 忽略了 null 值 (count本身 就是 计算 非空值)
四、 和 distinct 搭配使用
SELECT SUM(DISTINCT salary),SUM(SALARY)FROM EMPLOYEES;#去重后总计397900.00 没有去重总计698400.00
SELECT COUNT(DISTINCT salary),COUNT(SALARY)FROM EMPLOYEES;#去重后总计57,没有去重 总计108
五、 count函数的详细介绍
SELECT COUNT(SALARY) FROM employees;
SELECT COUNT(*) FROM employees; #一行中 只要有一个字段 的 值 不为0 count值就加1
SELECT COUNT(1)FROM employees; #相当于 加了一列1 行数为表的行数 ,返回这列1有多少行
六、和分组函数一同查询的字段有限制
SELECT AVG(salary),EMPLOYEE_ID FROM EMPLOYEES;#输出6466.666667和100 这个100没有意义 因为avg函数平均的值是一行,而EMPLOYEE_ID字段行数就是它在表中的行数,但为了统一,所以用了一行。
七、练习
1.例1 查询员工表中 最大入职时间 和 最小入职时间 的相差天数(difference)
SELECT DATEDIFF('2022-10-1','2022-7-22')# 10月1与7月22日相差71天
SELECT DATEDIFF(MAX(HIREDATE),MIN(HIREDATE)) AS "difference" FROM EMPLOYEES;#8735天
2.例2 查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE DEPARTMENT_ID = 90;