导读
1.单行函数
2.多行函数(组函数)
函数:oracle内置了很多函数
单行函数:作用于一行,可以得到一个结果
多行函数(组函数):作用于多行(一组),可以得到一个结果
单行函数
单行函数:
字符函数
数值函数
日期函数
转换函数
字符函数:
LOWER:转换成小写
UPPER:转换成大写
INITCAP:每个单词首字母变大写,其余小写, 单词通过空格隔开
CONCAT:拼接
SUBSTR(str,start,length):截取 数据库中位置从1开始
LENGTH:长度
eg1:
SELECT LOWER('HelloWorld')
FROM dual;
SELECT UPPER('HelloWorld')
FROM dual;
eg2:查询包含字母b(不区分大小写)的姓
SELECT last_name
FROM s_emp
WHERE last_name LIKE '%b%' OR last_name LIKE '%B%';
SELECT last_name
FROM s_emp
WHERE LOWER(last_name) LIKE '%b%';
SELECT last_name
FROM s_emp
WHERE UPPER(last_name) LIKE '%B%';
eg3:
SELECT INITCAP('hello woRlD')
FROM dual;
eg4:
SELECT CONCAT('aa','bb')
FROM dual;
eg5:
SELECT SUBSTR('helloworld',2,6)
FROM dual;
数值函数:
ROUND:四舍五入
TRUNC:截取
MOD:取余
eg1:ROUND
SELECT ROUND(415.627,2) FROM dual; 精确到小数点后2位 415.63
SELECT ROUND(415.627,0) FROM dual; 416
SELECT ROUND(415.627,-1) FROM dual; 420
SELECT ROUND(415.627) FROM dual; 416 默认是0
eg2:TRUNC
SELECT TRUNC(415.627,2) FROM dual; 415.62
SELECT TRUNC(415.627,0) FROM dual; 415
SELECT TRUNC(415.627,-1) FROM dual; 410
SELECT TRUNC(415.627) FROM dual; 415
eg3:MOD
SELECT MOD(15,8) FROM dual; 7
日期型:
MONTHS_BETWEEN(date1,date2):求两个时间月份差,有小数的,date1-date2
ADD_MONTHS(date,n):给指定时间加n个月
NEXT_DAY(date,'星期几') 日期是中文,直接写星期几,英文:对应的英文单词
date之后最近的一个星期几
LAST_DAY(date),date所在月份的最后一天
ROUND(date,精确度)
year:年
yyyy:年
month:月
mm:月
dd:日
hh:小时
mi:分
ss:秒
精确到年,看月,满7进一
精确到月,看日,满16进一
精确到日,看小时,满12进一
TRUNC(date,精确度):不必考虑进位,直接后边归为默认值
SYSDATE函数:当前系统时间,默认显示格式 01-SEP-17
DATE类型
SYSTIMESTAMP函数:可以看到更多信息
TIMESTAMP类型
eg1:
SELECT MONTHS_BETWEEN('01-AUG-17','30-MAY-17')
FROM dual;
eg2:
SELECT ADD_MONTHS(sysdate,3) FROM dual;
eg3:增加1年:
SELECT ADD_MONTHS(sysdate,12) FROM dual;
增加1天:
SELECT sysdate+1 FROM dual;
增加1小时:
SELECT sysdate+1/24 FROM dual;
增加1分:+1/24/60
增加1秒:+1/24/60/60
eg4:
SELECT NEXT_DAY(sysdate,'Tuesday') FROM dual;
SELECT NEXT_DAY(sysdate,'Sunday') FROM dual;
SELECT LAST_DAY(sysdate) FROM dual;
eg5:ROUND()
SELECT ROUND(sysdate,'year') FROM dual; 2018-01-01
SELECT ROUND(sysdate,'month') FROM dual; 2017-08-01
eg6:TRUNC()
SELECT TRUNC(sysdate,'year') FROM dual; 2017-01-01
SELECT TRUNC(sysdate,'month') FROM dual; 2017-08-01
eg7:查询本月入职的所有员工
通用:
WHERE start_date BETWEEN TRUNC(sysdate,'month') AND sysdate;
本年:
WHERE start_date BETWEEN TRUNC(sysdate,'year') AND sysdate;
本周:
WHERE start_date BETWEEN TRUNC(NEXT_DAY(sysdate-7,'monday'),'dd') AND sysdate;
转换函数:
数值<==>文本 (了解)
数值->文本:to_char
占位符:
0:不满的位用0填充
9:整数位不满用空格填充,小数位用0填充
货币:$表示美元, L表示本地货币单位
文本->数值:to_number
eg1:
SELECT to_char(123456.789,'99999999.9999') FROM dual;
SELECT to_char(123456.789,'00000000.0000') FROM dual;
SELECT to_char(123456.789,'99,999,999.9999') FROM dual;
SELECT to_char(123456.789,'$99,999,999.9999') FROM dual;
SELECT to_char(123456.789,'L99,999,999.9999') FROM dual;
SELECT to_number('123') FROM dual;
时间<==>文本,掌握
时间->文本:to_char
日期格式模型元素:
d:星期几,1~7:星期日~星期六
dd:一个月的第几天
ddd:一年中的第几天
yyyy:四位的年
mm:两位的月
hh24:24进制的小时
hh:12进制的小时
mi:分
ss:秒
rr:两位的年,50以上表示上个世纪,49以下是本世纪
yy:两位的年,和当前时间在同一个世纪
文本->时间:to_date
eg1:查询当前系统时间,要求显示年月日时分秒
SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual;
eg2:'1997-01-01':是一个字符串,能否转换成时间?
SELECT to_date('1997-01-01','yyyy-mm-dd') FROM dual;
多行函数(组函数)
常用组函数:统计
SUM():求和
AVG():求平均
MAX():求最大
MIN():求最小
COUNT():统计个数,通过它统计记录数
注意:
1.组函数统计不包含空值
2.SUM(),AVG()仅针对于数值型统计
MAX(),MIN(),COUNT()任何类型都可以,字符串的大小是指在字典中的排序
3.COUNT(),统计个数,可以利用他统计行数,要注意,不要针对可能是空的做统计
COUNT():主键列 或者*(所有列联合不为空就可以)
4.组函数不能直接出现在WHERE条件中,因为此时不能确定数据有哪些
5.SELECT中如果出现组函数,不能再出现普通的列
SELECT dept_id,AVG(salary)
FROM s_emp; 错误的
eg1:求所有人的工资总和
SELECT SUM(salary)
FROM s_emp;
eg2:求41号部门员工的薪资总和,平均薪资,最高薪资,最低薪资,人数
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(id)
FROM s_emp
WHERE dept_id=41;