常用SQL方法学习笔记
常用SQL方法学习笔记
笔记一:
---截取 SELECT SUBSTR('BAAAAAAAAAAAA',1,5)AS S FROM DUAL;---BAAAA ---為空判斷 SELECT NVL('A','B') AS FLAG FROM DUAL;---A SELECT NVL('','B') AS FLAG FROM DUAL;---B SELECT NVL(null,'B') AS FLAG FROM DUAL;---B ---小數四捨五入保留 SELECT ROUND(50.26448,2) AS NM FROM DUAL;---50.26 ---去空格 select trim(' AB CDE ') AS S FROM DUAL;---左右去空格 SELECT ltrim(' ABCD ')AS S FROM DUAL;---左去空格 SELECT rtrim(' ABCDE ') AS S FROM DUAL;---右去空格 ---DECODE SELECT DECODE('3', '1', 'UNLOCK', '2', 'LOCK', '3', 'OTHER') AS STATUS FROM DUAL;---OTHER ---TRUNC 不近似保留 SELECT TRUNC(0.123456789, 5) AS STOPTIME_HN FROM DUAL; ---0.12345
笔记二:
1.查询当前年、月、周相关时间
1.1.查询当前年份
SELECT TO_CHAR(SYSDATE,'YYYY') AS YEAR FROM DUAL--查询当前年份 SELECT TO_CHAR(SYSDATE,'YYY') AS YEAR FROM DUAL--查询当前年份后两位 SELECT TO_CHAR(SYSDATE,'YY') AS YEAR FROM DUAL--查询当前年份最后两位 SELECT TO_CHAR(SYSDATE,'Y') AS YEAR FROM DUAL--查询当前年份最后一位
1.2.查询当前年份第几天
SELECT TO_CHAR(SYSDATE,'DDD') AS DAYS FROM DUAL
1.3.查询当前月份第几天
SELECT TO_CHAR(SYSDATE,'DD') AS DAYS FROM DUAL
1.4.查询当前周的星期几
SELECT TO_CHAR(SYSDATE,'dy') AS WEEK FROM DUAL SELECT TO_CHAR(SYSDATE,'day') AS WEEK FROM DUAL
1.5.查询当前年的第几天:
SELECT TO_CHAR(SYSDATE,'DDD') AS DAYS FROM DUAL
2.查询当前第几季度
SELECT TO_CHAR(SYSDATE,'Q') AS JD FROM DUAL
3.查询当前在一年内是第几周
SELECT TO_CHAR(SYSDATE,'IW') AS WEEKNUM FROM DUAL SELECT TO_CHAR(SYSDATE,'WW') AS WEEKNUM FROM DUAL SELECT TO_CHAR(SYSDATE,'W') AS WEEKNUM FROM DUAL
4.查询当前某年某月有多少天
SELECT TO_CHAR(last_day(to_date('2019/02/01','YYYY/MM/DD')),'DD') AS MONTH_DAYS_NUMBER FROM DUAL
5.查询当前的日期格式
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') AS MONTH FROM DUAL--获取当前日期的'YYYY/MM/DD'格式:2020/01/02 SELECT TO_CHAR(SYSDATE,'YYYYMMDD') AS MONTH FROM DUAL--获取当前日期的'YYYYMMDD'格式:20200102 SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH:MI:SS') AS now FROM DUAL--获取当前时间,精确到秒的12小时制格式:20200102 03:18:25 SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') AS now FROM DUAL--获取当前时间,精确到秒24小时制的格式:20200102 15:18:13 SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS:PM:DY') AS now FROM DUAL--获取当前时间的格式,显示上、下午和星期几:2020/01/02 15:17:59:下午:星期四
6.查询距离当前时间一段时间间隔的时间,当前时间之前用“-”当前时间之后用“+”
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' second,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM DUAL --查询当前时间的7秒前时间 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR(SYSDATE - INTERVAL '7' minute,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM DUAL --查询当前时间的7分钟前时间 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR(SYSDATE - INTERVAL '7'hour ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM DUAL --查询当前时间的7小时前时间 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR(SYSDATE - INTERVAL '7'day ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM DUAL --查询当前时间的7天前时间 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR(SYSDATE - INTERVAL '7'month ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM DUAL --查询当前时间的7月前时间 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR(SYSDATE - INTERVAL '7'year ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM DUAL --查询当前时间的7年前时间 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR(SYSDATE - 8*INTERVAL '7'hour ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM DUAL --查询当前时间参数年、月、日、时、分、秒乘以一个数字倍数之前的时间
后续边学习边整理........
本文来自博客园,作者:码农阿亮,转载请注明原文链接:https://www.cnblogs.com/wml-it/p/14732049.html
技术的发展日新月异,随着时间推移,无法保证本博客所有内容的正确性。如有误导,请大家见谅,欢迎评论区指正!
开源库地址,欢迎点亮:
GitHub:https://github.com/ITMingliang
Gitee: https://gitee.com/mingliang_it
GitLab: https://gitlab.com/ITMingliang
建群声明: 本着技术在于分享,方便大家交流学习的初心,特此建立【编程内功修炼交流群】,为大家答疑解惑。热烈欢迎各位爱交流学习的程序员进群,也希望进群的大佬能不吝分享自己遇到的技术问题和学习心得!进群方式:扫码关注公众号,后台回复【进群】。
![](https://images.cnblogs.com/cnblogs_com/wml-it/2196906/o_230823011905_wxgzhss.png)