SQL语句分为以下四种类型：（前三种常见）
（1）DML: Data Manipulation Language 数据操纵语言(insert、update、delete、select)
（2）DDL: Data Definition Language 数据定义语言(create、drop、alter、rename、truncate)
（3）DCL: Data Control Language 数据控制语言(grant、revoke)
（4）TCL: Transaction Control Language事务控制语言(commit、rollback、savepoint);
/*
函数类型：系统函数，自定义函数
类别分类：
*/
转换函数：TO_CHAR() TO_NUMBER() TO_DATE() to_timestamp()
/*TO_DATE()省略YYYY默认为当年，省略MM默认为当月，省略DD默认为第一天
TO_CHAR(d|n[,fmt])
把日期和数字转换为制定格式的字符串。Fmt是格式化字符串
代码演示：TO_CHAR对日期的处理*/
SELECT TO_CHAR(SYSDATE,'YYYY"年"M"月"DD"日" HH24:MI:SS')"date" FROM dual;
参数 示例 说明
9 999 指定位置处显示数字
. 9.9 指定位置返回小数点
, 99,9 指定位置返回一个逗号
$ $999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字式负数则用尖括号进行表示
SELECT to_char(4565.55, '9999.9') FROM EMP; -- 4565.6
SELECT to_char(4565.55, '9.9') FROM EMP; --'#####'
SELECT to_char(4565.55, '0.00') FROM EMP; --'######' --如果整数位不够的话会返回一堆#
SELECT to_char(4565.55, '9,999.9') FROM EMP; -- 4,565.6
SELECT to_char(4565.55, '$9999.9') FROM EMP; --- $4565.6
SELECT to_char(4565.55, '9999.9EEEE') FROM EMP; --- 4.6E+03
SELECT to_char(4565.55, 'L9999.9') FROM EMP; --- ￥4565.6
SELECT to_char(-4565.55, '9999.9PR') FROM EMP; --- <4565.6>
SELECT emp.*, 0014234, TO_CHAR(001234.05) FROM EMP;
--9:指定任何数字，若整数位置无数字，则格式不生效，小数位值五数字以0补全
--0：指定任何数字，如整数位没有数字，则强制显示0，小数位一般不用0指代
--,英文逗号作为添加千分符,精度过低四舍五入
select to_char(12223.250, '00,099.0000') from emp; --转换后正数有一个空格
select to_char(-0012223.250, '00,099.0000') from emp --日期转换字符
select /*DISTINCT*/ SYSDATE, SYSTIMESTAM FROM DEPT;
select cHR(44543) from AC; --?
====================================================================================
常用的字符函数：
ASCII(X) 返回字符X的ASCII码
chr(x) 返回ascll码对应的字符
CONCAT(X,Y) 连接字符串X和Y
INSTR(X,STR[,START][,N) 从X中查找str，可以指定从start开始取第N个匹配项位置，找不到返回0
LPAD(STR,NUM,S)
RPAD(STR,NUM,)左右填充函数，str原字符，num填充后的字符串场长度，s要填充的字符串，可以是多个字符
LENGTH(X) 返回X的长度
LOWER(X) X转换成小写
UPPER(X) X转换成大写
INITCAP(X) 将X首字母大写其余小写
LTRIM(X[,TRIM_STR]) 把X的左边截去trim_str中含有的字符，缺省截去空格
RTRIM(X[,TRIM_STR]) 把X的右边截去trim_str中含有的字符，缺省截去空格
TRIM(leading/trailing/both [S FROM] X) 把X的两边截去S字符串，缺省截去空格
--LTRIM()与RTRIM()中的S可以是单个字符，也可以是多个字符，但TRIM()中的S只能是一个字符；
REPLACE(X,old,new) 在X中查找old，并替换成new
SUBSTR(X,start[,length]) 返回X的字串，从start处开始，截取length个字符，
缺省length，默认到结尾,START为负数从右边开始截取,省略参数三截取到最后，参数三为负数截取为空
select ascii('我') from dual;--15108241
select chr(15108241) from dual;--我
SELECT LPAD('SAA',10,'S') FROM DUAL;--SSSSSSSSAA
SELECT RPAD('SAA',10,'S') FROM DUAL;--SAASSSSSSS
select length('dsdahjd') from dual;--7
select RTRIM('JKDAHK ') FROM DUAL;--JKDAHK
SELECT LTRIM(' DSADA') FROM DUAL;--DSADA
SELECT TRIM(BOTH 'S' FROM 'SSNKJDKSS') FROM DUAL;--NKJDK
select RTRIM('JKDAHKllll','lK') FROM DUAL;--JKDAH
SELECT LTRIM('DSADA','DS') FROM DUAL;--ADA
SELECT SUBSTR('QWERTYUIOP',1,6) FROM DUAL;--QWERTY
SELECT SUBSTR('QWERTYUIOP',4,-4) FROM DUAL;--
SELECT SUBSTR('QWERTYUIOP',-5,6) FROM DUAL; --YUIOP
SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) FROM DUAL --14
select INSTR('QWERTYUIOP','W',1,1) FROM DUAL;--2
=====================================================================================
--日期截断：
round() 四舍五入日期截断函数 TRUNC()向下截断函数
select trunc(3.24564,3) from dual; --3.245
select round(3.24564,3) from dual; --3.246
-- 天的界限，年的界限，月的界限，季度的界限，小时的界限，星期的界限
1.天的界限 'DD' 默认指定则为天的界限 11：59：59-12：00：00
SELECT SYSDATE,ROUND(SYSDATE,'DD'),TRUNC(SYSDATE,'DD') FROM DUAL;
SELECT TO_DATE('20201007 11:59:59','YYYYMMDD HH24:MI:SS'),--2020/10/7 11:59:59
ROUND(TO_DATE('20201007 11:59:59','YYYYMMDD HH24:MI:SS'),'DD'),--2020/10/7
ROUND(TO_DATE('20201031 12:00:00','YYYYMMDD HH24:MI:SS'),'DD'),--2020/11/1
ROUND(TO_DATE('20201007 12:00:01','YYYYMMDD HH24:MI:SS'),'DD'),--2020/10/8
ROUND(TO_DATE('20201007 13:00:01','YYYYMMDD HH24:MI:SS'),'DD'),--2020/10/8
ROUND(TO_DATE('20201007 11:59:59','YYYYMMDD HH24:MI:SS'),'DD'), --2020/10/7
TRUNC(TO_DATE('20201031 12:0:00','YYYYMMDD HH24:MI:SS'),'DD')--2020/10/31
FROM DUAL;
2.年的界限'YYYY' 6/30-7/01
SELECT SYSDATE,ROUND(SYSDATE,'YYYY'),TRUNC(SYSDATE,'YYYY') FROM DUAL;--2023/4/5 8:02:43 2023/1/1 2023/1/1
SELECT TO_DATE('20201007','YYYYMMDD'),--2020/10/7
ROUND(TO_DATE('20200630','YYYYMMDD'),'YYYY'),--2020/1/1
TRUNC(TO_DATE('20200630','YYYYMMDD'),'YYYY'),--2020/1/1
ROUND(TO_DATE('20200701','YYYYMMDD'),'YYYY'),--2021/1/1
TRUNC(TO_DATE('20200701','YYYYMMDD'),'YYYY') --2020/1/1
FROM DUAL;
3.月的界限 --15 16
SELECT SYSDATE,ROUND(SYSDATE,'MM'),TRUNC(SYSDATE,'MM') FROM DUAL;--2023/4/5 8:11:42 2023/4/1 2023/4/1
SELECT TO_DATE('20201007','YYYYMMDD'),--2020/10/7
ROUND(TO_DATE('20200615','YYYYMMDD'),'MM'),--2020/6/1
TRUNC(TO_DATE('20200615','YYYYMMDD'),'MM'),--2020/6/1
ROUND(TO_DATE('20200616','YYYYMMDD'),'MM'),--2020/7/1
TRUNC(TO_DATE('20200616','YYYYMMDD'),'MM') --2020/6/1
FROM DUAL;
4.季度的界限 --round极度的三分月中间的月份的16日 trunc每季度月初为分界点12/31-1/01 3/31-4/1
SELECT SYSDATE,ROUND(SYSDATE,'Q'),TRUNC(SYSDATE,'Q') FROM DUAL;
SELECT ROUND(TO_DATE('20201115','YYYYMMDD'),'Q'),--2020/10/1
TRUNC(TO_DATE('20200101','YYYYMMDD'),'Q'),--2020/10/1
ROUND(TO_DATE('20201116','YYYYMMDD'),'Q'),--2021/1/1
TRUNC(TO_DATE('20201231','YYYYMMDD'),'Q') --2020/10/1
FROM DUAL;
SELECT ROUND(TO_DATE('20200215','YYYYMMDD'),'Q'),--2020/1/1
TRUNC(TO_DATE('20200331','YYYYMMDD'),'Q'),--2020/1/1
ROUND(TO_DATE('20200216','YYYYMMDD'),'Q'),--2020/4/1
TRUNC(TO_DATE('20200401','YYYYMMDD'),'Q') --2020/4/1
FROM DUAL;
SELECT ROUND(TO_DATE('20200815','YYYYMMDD'),'Q'),--2020/7/1
TRUNC(TO_DATE('20200815','YYYYMMDD'),'Q'),--2020/07/01
ROUND(TO_DATE('20200816','YYYYMMDD'),'Q'),--2020/10/1
TRUNC(TO_DATE('20200816','YYYYMMDD'),'Q') --2020/7/1
FROM DUAL;
5.小时的界限 ROUND 29:59-30:00 TRUNC 59:59-00:00
SELECT SYSDATE,ROUND(SYSDATE,'HH24'),TRUNC(SYSDATE,'HH24') FROM DUAL;
SELECT TO_DATE('20201007 11:59:59','YYYYMMDD HH24:MI:SS'),--2020/10/7 11:59:59
ROUND(TO_DATE('20201007 11:29:59','YYYYMMDD HH24:MI:SS'),'HH24'),--2020/10/7 11:00:00
ROUND(TO_DATE('20201031 11:30:00','YYYYMMDD HH24:MI:SS'),'HH24'),--2020/10/31 12:00:00
TRUNC(TO_DATE('20201007 11:59:59','YYYYMMDD HH24:MI:SS'),'HH24'),--2020/10/7 11:00:00
TRUNC(TO_DATE('20201031 12:00:00','YYYYMMDD HH24:MI:SS'),'HH24')--2020/10/31 12:00:00
FROM DUAL;
6.分钟的界限(没有秒的界限)
SELECT TO_DATE('20201007 11:59:59','YYYYMMDD HH24:MI:SS'),--2020/10/7 11:59:59
ROUND(TO_DATE('20201007 11:30:29','YYYYMMDD HH24:MI:SS'),'MI'),--2020/10/7 11:30:00
ROUND(TO_DATE('20201031 11:30:30','YYYYMMDD HH24:MI:SS'),'MI'),--2020/10/31 12:31:00
TRUNC(TO_DATE('20201007 11:30:59','YYYYMMDD HH24:MI:SS'),'MI'),--2020/10/7 11:30:00
TRUNC(TO_DATE('20201031 12:31:00','YYYYMMDD HH24:MI:SS'),'MI')--2020/10/31 12:31:00
FROM DUAL;
6.星期的界限
关键词 清零 界限
IW 星期一 四 12：00
DAY 星期日 三 12：00
DY 星期日 三 12：00
========================================================================================
--日期型函数：
select
to_char(sysdate, 'YYYY') --年
, to_char(sysdate, 'MM') --月
, to_char(sysdate, 'DD') --日
, to_char(sysdate, 'HH24') --时
, to_char(sysdate, 'MI') --分
, to_char(sysdate, 'SS') --秒
, to_char(sysTIMESTAMP, 'FF') --秒
, to_char(sysdate, 'DAY') --天
, to_char(sysdate, 'Q') --第几季度
, to_char(sysdate, 'W') --当月第几周
, to_char(sysdate, 'WW') --当年第几周
, to_char(sysdate, 'D') --当周第几天
, to_char(sysdate, 'DDD') --当年第几天
from dual;
1.获取月份差值；MONTHS_BETWEEN(D1,D2)求d1和d2相差几个月*/
select MONTHS_BETWEEN(SYSDATE,TO_DATE('20230101','YYYYMMDD'))from dual;
select MONTHS_BETWEEN(trunc(SYSDATE,'mm'),TO_DATE('20230101','YYYYMMDD'))from dual;
select MONTHS_BETWEEN(to_date('20240101','yyyymmdd'),trunc(sysdate,'mm')) from dual;
--获取当月最后后一天 LAST_DAY()
SELECT LAST_DAY(trunc(SYSDATE,'DD')) FROM DUAL;
--查询某月份最大天数
select TO_CHAR(LAST_DAY(TO_DATE('20160201','YYYYMMDD')),'DD') FROM DUAL;
SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE),LAST_DAY(TO_DATE('20220203','YYYYMMDD'))) FROM DUAL;
--月份加减 add_MONTHS(D,N) 在d日期的基础上加上n个月，n可为正负
select ADD_MONTHS(SYSDATE,2) FROM DUAL;
select ADD_MONTHS(SYSDATE,-2) FROM DUAL;
select ADD_MONTHS(SYSDATE,2.6) FROM DUAL;--劫取整数
select ADD_MONTHS(to_date('20230331','yyyymmdd'),-1) FROM DUAL;--2023/2/28
--如果日期天数部分大于改变后的日期天数则改变后的为月末日期
select * from emp where hiredate<ADD_MONTHS((select HIREDATE FROM EMP WHERE ENAME='ALLEN'),-2);
--当月第一天 TRUNC(SYSDATE,'MM')
select TRUNC(SYSDATE,'MM') from dual;
SELECT SUBSTR('2023/04/05',1,7)||'/01' FROM DUAL;
--获取下个周几 NEXT_DAY(D,W) 给定日期D的下个星期W
select next_day(trunc(sysdate,'DD'),'星期五') from dual;--2023/4/5 2023/4/7
select next_day(trunc(sysdate,'DD'),5) from dual;--2023/4/5 2023/4/6 数字1从周日开始计算
--EXTRACT():从一个date或者interval类型中截取到特定的部分，
extract (
{ year | month | day | hour | minute | second }
| { timezone_hour | timezone_minute }
| { timezone_region | timezone_abbr }
from { date_value | interval_value } );
select extract (year from sysdate) year, extract (month from sysdate) month,
extract (day from sysdate) day from dual;
select date '2011-05-04' from DUAL; --输出日期格式
select extract (year from date '2015-05-04') year, extract (month from date'2015-05-04')
month, extract (day from date '2011-05-04') day from dual;
select sysTIMESTAMP
,extract(year from systimestamp) year
,extract(month from systimestamp) month
,extract(day from systimestamp) day
,extract(hour from systimestamp) hour
,extract(minute from systimestamp) minute
,extract(second from systimestamp) second
,extract(timezone_hour from systimestamp) th
,extract(timezone_minute from systimestamp) tm
,extract(timezone_region from systimestamp) tr
,extract(timezone_abbr from systimestamp) ta
from dual;
SELECT EXTRACT(YEAR FROM SYSDATE) YEAR
,extract(YEAR from systimestamp) YEAR_NEW
,EXTRACT(DAY FROM SYSDATE) DAY
,EXTRACT(DAY FROM SYSTIMESTAMP) DAY_NEW
,EXTRACT(MONTH FROM SYSDATE) MONTH
,EXTRACT(MONTH FROM SYSTIMESTAMP) MONTH_NEW
,EXTRACT(HOUR FROM SYSTIMESTAMP) HOUR_NEW
,EXTRACT(MINUTE FROM SYSTIMESTAMP) MINUTE_NEW
,EXTRACT(SECOND FROM SYSTIMESTAMP) SECOND
FROM EMP;
--日期加法
sysdate+1 加一天
sysdate+1/24 加1小时
sysdate+1/(24*60) 加1分钟
sysdate+1/(24*60*60) 加1秒钟
类推至毫秒0.001秒
select sysdate,add_months(sysdate,12) from dual; --加1年
select sysdate,add_months(sysdate,1) from dual; --加1月
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒
--减法改变符号
======================================================================================
--数字函数：
ABS(X) X的绝对值 ABS(-3)=3
ACOS(X) X的反余弦 ACOS(1)=0
COS(X) 余弦 COS(1)=0.54030230586814
CEIL(X) 向上取整 CEIL(5.4)=6
FLOOR(X) 向下取整 FLOOR(5.8)=5
LOG(X,Y) X为底Y的对数 LOG(2，4)=2
MOD(X,Y) X除以Y的余数 MOD(8，3)=2
POWER(X,Y) X的Y次幂 POWER(2，3)=8
ROUND(X[,Y]) X在第Y位四舍五入 ROUND(3.456，2)=3.46
SQRT(X) X的平方根 SQRT(4)=2
TRUNC(X[,Y]) X在第Y位截断 TRUNC(3.456，2)=3.45
SELECT ABS(-1),abs(1),acos(1),cos(45) FROM DUAL;
select CEIL(4.3),FLOOR(4.6) FROM DUAL;-- 5,4
select CEIL(-4.3),FLOOR(-4.6) FROM DUAL;-- -4,-5
SELECT LOG(2,4),MOD(8,3),POWER(3,3),SQRT(9),SQRT(8) FROM DUAL;
SELECT TRUNC(45655.985,-2) FROM DUAL;
SELECT ROUND(45655.985,-2) FROM DUAL;
=======================================================================================
-- 其他函数：
1.条件取值
decode函数：DECODE(expr,value1,result1,value2,result2,...,def_result)
/* 效果等同于：
SELECT CASE 列名
WHEN 值1 THEN 結果1
WHEN 值2 THEN 結果2
ELSE 结果3
END
FROM table1;*/
select * from emp;
select ENAME,SAL,decode(ENAME,'CLARK',Sal+1000,'WARD',SAL+300,SAL+100) NEW_SAL FROM EMP;
SELECT DEPTNO,DECODE(DEPTNO,10,1,20,2,0) NEW_DEPTNO FROM EMP;
SELECT CASE DEPTNO
WHEN 10 THEN 1
WHEN 20 THEN 2
ELSE 0
END,DEPTNO
FROM EMP;
--2.空值的赋值
-- NVL(X,VALUE) 如果X为空，返回value，否则返回X
--NVL2(x,value1,value2) 如果x非空，返回value1，否则返回value2*/
select NVL(COMM,8888) FROM EMP;
select NVL2(COMM,1,0),comm FROM EMP;
-- 3.去重
--distinct()去重函数 对distinct后面的字段联合去重
select distinct JOB from emp;
select distinct deptno from emp;
select DISTINCT JOB,DEPTNO FROM EMP;
--返回括号中第一个非空表达式
COALESCE(c1,c2,c3,c4,......cn):返回括号中第一个非空表达式，如果都为空，则返回空
--5.listagg 函数有两个参数：
1、 要合并的列名
2、 自定义连接符号
LISTAGG 函数既是分析函数，也是聚合函数所以，它有两种用法：
1、分析函数，如： row_number()、rank()、dense_rank() 等，用法相似
listagg(合并字段, 连接符) within group(order by 合并的字段的排序) over(partition by 分组字段)
2、聚合函数，如：sum()、count()、avg()等，用法相似
listagg(合并字段, 连接符) within group(order by 合并字段排序) --后面跟 group by 语句
--6.与listagg类似的函数：WM_CONCAT(字段名) 聚合拼接函数 结果以逗号隔开
select wm_concat(ename) from emp group by deptno;
--7.数据类型转换： case(参数1 as 参数2) 参数1：字段 参数2：类型包括精度
select cast(deptno as varchar2(3)) from emp;
--九九乘法表
create A TABLE( NUMS NUMBER);
INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);
INSERT INTO A VALUES(4);
INSERT INTO A VALUES(5);
INSERT INTO A VALUES(6);
INSERT INTO A VALUES(7);
INSERT INTO A VALUES(8);
INSERT INTO A VALUES(9);
COMMIT;
SELECT CASE WHEN NUMS>=1 THEN '1*'||NUMS||'='||NUMS*1 ELSE NULL END A,
CASE WHEN NUMS>=2 THEN '2*'||NUMS||'='||NUMS*2 ELSE NULL END B,
CASE WHEN NUMS>=3 THEN '3*'||NUMS||'='||NUMS*3 ELSE NULL END c,
CASE WHEN NUMS>=4 THEN '4*'||NUMS||'='||NUMS*4 ELSE NULL END D,
CASE WHEN NUMS>=5 THEN '5*'||NUMS||'='||NUMS*5 ELSE NULL END E,
CASE WHEN NUMS>=6 THEN '6*'||NUMS||'='||NUMS*6 ELSE NULL END F,
CASE WHEN NUMS>=7 THEN '7*'||NUMS||'='||NUMS*7 ELSE NULL END G,
CASE WHEN NUMS>=8 THEN '8*'||NUMS||'='||NUMS*8 ELSE NULL END H,
CASE WHEN NUMS>=9 THEN '9*'||NUMS||'='||NUMS*9 ELSE NULL END I
FROM A;
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
