公司查询语句比较多,大量用到sql语句,特此整理一番,加深自己的印象!
- 日期函数
ADD_MONTHS 返回日期d1基础上再加上n1个月之后的日期
--获取两个月之后的时间
select add_months(sysdate,2) from dual
--获取两个月之前的时间
select add_months(sysdate,-2) from dual
MONTHS_BETWEEN 返回日期d1到日期d2之间的月数
--下面练习了一下时间日期字符串转换
select months_between(to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),to_date('2017-09-10','yyyy-mm-dd')) from dual
--如果date1在日历中比date2早,那么MONTHS_BETWEEN()就返回一个负数。
select months_between(sysdate,to_date('2017-09-10','yyyy-mm-dd')) from dual
--如果date1在日历中比date2晚,那么MONTHS_BETWEEN()就返回一个正数。
select months_between(to_date('2017-09-10','yyyy-mm-dd'),sysdate) from dual
--如果date1和date2日期一样,那么MONTHS_BETWEEN()就返回一个0。
select months_between(sysdate,sysdate) from dual
TRUNC(D1[,C1]) 返回日期d1所在期间的第一天日期
--获取本月第一天
select trunc(sysdate,'mm') from dual
--获取本季第一天
select trunc(sysdate,'Q') from dual
--获取本年第一天
select trunc(sysdate,'yyyy') from dual
SYSDATE 返回系统当前日期
--显示系统日期
select sysdate from dual
--调整显示日期格式
select to_char(sysdate,'yyyy-mm-dd') from dual
NEXT_DAY 给出日期date和星期x之后计算下一个星期的日期
--next_day(日期,星期几)
select next_day(sysdate,7) from dual
LAST_DAY 返回日期的最后一天
--返回指定日期所在月份的最后一天
select last_day(sysdate) from dual
- 数字函数
CEIL 返回大于或者等于给出数字的最小整数(天花板)
select ceil(9.8) from dual
FLOOR 对给定数字取整数(地板)
select floor(9.8) from dual
TRUNC 返回x按照精度y截取之后的数值
--trunc(number,num_digits)
--number 需要截尾取整的数字,num_digits用于指定整精度的数字默认为0,截取的时候不尽兴四舍五入
select trunc(98.675,2) from dual
ROUND 返回四舍五入后的值
select round(5.5) from dual
select round(6.4) from dual
MOD 返回x除以y的余数
select mod(8,2) from dual
POWER 返回x的y次幂
select power(3,2) from dual
ABS 返回x的绝对值
select ABs(-3) from dual
- 转换函数
TO_CHAR 将日期或数字转换为char数值类型
select to_char(sysdate,'yyyy-mm-dd') from dual
select to_char(123) from dual
TO_DATE 将字符串x转换为日期类型
select to_date('2017-09-08','yyyy-mm-dd') from dual
select to_date('2017-09-08 13:09:22','yyyy-MM-dd hh24:mi:ss') from dual
TO_NUMBER 将字符串x转换为数字类型
select to_number('24') from dual
CONVERT 将源字符串从一个语言字符集转换为另一个语言字符集
select convert('Ä Ê Í Ó Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual
- 分析函数
SUM(…) OVER 连续求和分析函数
select e.deptno,e.ename, e.sal,sum(sal) over(order by e.ename) 连续求和,sum(sal) over() 总和,
-- 此处sum(sal) over () 等同于sum(sal)100 * round(sal / sum(sal) over(), 4) "份额(%)" from emp e;
RANK() 和 DENSE_RANK() 聚合函数rank和dense_rank主要的功能是计算一组数值的排序值中的排序值
select s.fname,s.fid,s.fage, rank() over(order by fage desc) 排名 from s_user s
select s.fname,s.fid,s.fage, dense_rank() over(order by fage desc) 排名 from s_user s
ROW_NUMBER() 取前几名或者最后几名
select fid,fname,fsex,row_number() over(partition by fsex order by fage desc) as 排名 from s_user
- 聚组函数
AVG 统计数据表中x列的数值平均数
select avg(fage) 平均年龄 from s_user
SUM 统计数据表中x项的合计值
select sum(fage) from s_user
COUNT 统计数据表中x项行的合计值
select count(fage) from s_user
MAX 取数据表中x项的最大值
select max(fage) from s_user
MIN 取数据表中x项的最小值
select min(fage) from s_user
- 字符函数
LOWER 将字符串全部转换为小写
select lower('adSASDA') from dual
UPPER 将字符串全部转换为大写
select upper('sasdaSDADA') from dual
INSTR 在字符串中搜索指定字符并返回第一次出现的位置,oracle下标从1开始
select INSTR('sadsa','d') from dual
LENGTH 返回字符串的长度
select length('dasda') from dual
LPAD 在字符串c1的左边用字符串c2填充,直到长度为n时为止
select lpad('scl',8,'sadadsadas') from dual
RPAD 在字符串c1的右边用字符串c2填充,直到长度为n为止
select rpad('scl',8,'sadadsadas') from dual
REPLACE 将字符表达式中部分相同的字符串替换成新的字符串
select replace('dassda','s','@') from dual
SUBSTR 对字符串进行截取,oracle字符串下标从1开始
select substr('scl',1,2) from dual
TRANSLATE 将字符表达式中指定字符串替换成新的字符串
select translate('scl','c','3') from dual
TRIM 删除左边和右边出现的字符串
select rtrim('scl','l') from dual
select ltrim('scl','s') from dual
select trim(' sdsa ') from dual
- 其他函数
NVL 如果判断的值为null则返回f2的值
select nvl(faddress,'scl') address from s_user
ROWNUM 返回当前行号
select rownum from s_user where fname = '特朗普'
SYS_GUID() 产生全球唯一的32位随机数
select sys_guid() from dual
USEREVN 返回当前用户环境信息
select userenv(isdba) from dual;
VSIZE 返回字节数(大小)
select VSIZE('dasa') from dual