Java 9---Oracle数据库函数使用

导读

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;