-
关于sql函数(日期、字符串处理、聚合函数、常用函数)
日期类函数: -
四舍五入 round()函数
用法:
如果括号内只有一个小数,舍弃小数点四舍五入;若有两位,假设第二位为3,则保留小数点后三位四舍五入。 -
向上取整 ceil()
ceil(2.55) —> 结果为3
括号内放一个数,不论小数点后为何数,都进一位。 -
向下取整 floor()
floor(2.55) —> 结果为2
括号内放一个数,不论小数点后为何数,都不进位。 -
DAY() MONTH() YEAR() WEEK() 得到日期的具体天、月、年、周
例如:
select hiredate, DAY(hiredate) from emp;
其中,前三个用法相似,不再赘述;说明WEEK(date, mode)函数用法:
该函数用于返回日期的周数,date放指定日期,mode为计算日期对应周数的规则,
规则如下
例如:
表示截止该日期共有5周。
select WEEK('2023-01-30',1) from dual;
-
获得当前日期的几种表示
SYSDATE(), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
-
DATE_FORMAT 将日期转为制定格式
DATE_FORMAT(date, format)
format常用汇总:
%Y 年
%m月
%d 日
%H时
%i 分
%s 秒
%T 时:分:秒
%w 一个星期中的天数 (0=星期日, 6=星期六)
例如:
- LAST_DAY() 获取每月最后一天的日期
- DATEDIFF(end_date, start_date) 用于对日期进行计算
用途:
可以返回比较的差值,单位有:天、周、月等
例如:
以天为单位——
以周为单位——
例如:
查询雇员表中,姓名为MARTIN的雇员,截止到今天共工作了多少周
select round(datediff(sysdate(),hiredate)/7) as 'week' from emp where ename = 'MARTIN';
字符串函数
- UPPER(str), LOWER(str) 转换大小写
- CONCAT(str,str2…)
- SUBSTR(str, pos, len) (和substring用法一致)
例如:
将所有雇员名称转为首字母大写,其余小写的格式
select CONCAT(UPPER(SUBSTR(ename,1,1)),LOWER(SUBSTR(ename,2))) from emp
其中,CONCAT(str1,str2,…)拼接字符串,SUBSTR(str FROM pos FOR len) 截取字符串,从pos开始,截取长度为len,如果len不选,默认从pos截取到最后一位。
- REPLACE(str,from_str,to_str) 替换from 为to的字符
例如:
显示所有雇员的姓名,将所有’A’均替换为 ‘a’
select REPLACE(ename,'A','a') from emp;
- IFNULL(expr1,expr2) 将null转为指定数值
用法:
如果expr1字段有null值,就将null转为指定数值
例如:
查询各部门薪水与奖金之和的最大值
-- 其中comm为奖金字段,sal为薪水字段
select deptno, MAX(sal + IFNULL(comm,0)) as '部门最高薪水' from emp group by deptno;
2、关于复杂查询
2.1 多表关联
查询薪水大于该部门平均薪水的员工信息
select * from emp e1 where sal > (select AVG(sal) from emp e2 where e1.deptno = e2.deptno);
2.2 条件查询
查询各部门最高薪水的员工信息
-- 此处需要查询多条满足分组条件的信息,把分组的子查询作为条件筛选,使用in罗列所有情况
select * from emp where sal in (select MAX(sal) from emp group by deptno)