大家好,又见面了,我是你们的朋友全栈君。
SELECT ASCII('a') FROM dual;
--结果:97
SELECT CONCAT('1','2') FROM dual;
--结果:12
SELECT INSTR('abcdefgh','de') FROM dual;
--结果:4
SELECT LENGTH('abcdefgh') FROM dual;
--结果:8
SELECT LOWER('ABcdefgh') FROM dual;
--结果:abcdefgh
SELECT UPPER('ABcdefgh') FROM dual;
--结果:ABCDEFGH
SELECT LTRIM('=ABcdefgh=','=') FROM dual;
--结果:ABcdefgh=
SELECT RTRIM('=ABcdefgh=','=') FROM dual;
--结果:=ABcdefgh
SELECT TRIM('='FROM'=ABcdefgh=') FROM dual;
--结果:ABcdefgh
SELECT REPLACE('ABCDE','CD','AAA') FROM dual;
--结果:ABAAAE
SELECT SUBSTR('ABCDE','2','3') FROM dual;
--结果:BCD
select initcap('hEllo') name from dual;
--结果:Hello
select lpad(rpad('111111',10,'*'),17,'*') NAME from dual;
--结果:*******111111****
--将某个字段值设置替换成空
select REGEXP_REPLACE (t.acceptordatajson,'"bankId":\d+','"bankId":""') from t_accept_log t where t.acceptordatajson like '%其它银行%'
SELECT REGEXP_SUBSTR('http://www.baidu.com','http://([[:alnum:]]+\.?)') a from dual;
--结果:http://www.
SELECT TRANSLATE('2abc2234','01234abcde','99999XXXXX') tra from dual
--结果:9XXX9999
--查找字符串',01234,2342,2,'中逗号出现次数
select length(translate(',01234,2342,2,', 'a0123456789', ' ')) from dual;
--结果:4
SELECT ABS('-10') FROM dual;
--结果:10
SELECT ACOS(1) FROM dual;
--结果:0
SELECT COS(1) FROM dual;
--结果:0.54030230586814
SELECT CEIL(4.9) FROM dual;
--结果:5
SELECT FLOOR(4.9) FROM dual;
--结果:4
SELECT LOG(2,8) FROM dual;
--结果:3
SELECT MOD(8,3) FROM dual;
--结果:2
SELECT POWER(2,3) FROM dual;
--结果:8
SELECT ROUND(5.45,1) FROM dual;
--结果:5.5
SELECT SQRT(4) FROM dual;
--结果:2
SELECT TRUNC(6.476,2) FROM dual;
--结果:6.47
d 表示日期,n 表示要加的月数。
SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;
--结果:2021/5/12 3:53:21 2021/10/12 3:53:21
SELECT SYSDATE,last_day(SYSDATE) FROM dual;
--结果:2021/5/12 3:56:15 2021/5/31 3:56:15
模型。默认 fmt 为 DDD,即月中的某一天。
SELECT SYSDATE,
ROUND(SYSDATE),
ROUND(SYSDATE, 'day'),
ROUND(SYSDATE, 'month'),
ROUND(SYSDATE, 'year')
FROM DUAL;
--结果:
2021/5/12 3:57:11
2021/5/12
2021/5/9
2021/5/1
2021/1/1
SELECT SYSDATE "date",
EXTRACT(YEAR FROM SYSDATE) "year",
EXTRACT(MONTH FROM SYSDATE) "month",
EXTRACT(DAY FROM SYSDATE) "day",
EXTRACT(HOUR FROM SYSTIMESTAMP) "hour",
EXTRACT(MINUTE FROM SYSTIMESTAMP) "minute",
EXTRACT(SECOND FROM SYSTIMESTAMP) "second"
FROM DUAL;
--结果:
2021/5/12 3:59:32
2021
5
12
7
59
32.248128
select CURRENT_DATE from dual;
--结果:2021/6/10 14:32:49
select CURRENT_TIMESTAMP from dual;
--结果:10-6月 -21 02.34.20.845299 下午 +08:00
Select LOCALTIMESTAMP from dual;
--结果:10-6月 -21 02.36.17.989733 下午
select months_between('19-12月-2021','19-3月-2021') mon_between from dual;
--结果:9
DD
.Select round(sysdate,'MONTH') from dual;
--结果:2021/6/1
select SYS_EXTRACT_UTC(systimestamp) from dual;
--结果:10-6月 -21 06.41.59.738669 上午
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--结果:2021-06-10 02:42:48
SELECT systimestamp from dual;
--结果:10-6月 -21 02.44.06.551610 上午 -04:00
select systimestamp from dual;
--结果:10-6月 -21 02.47.59.481088 上午 -04:00
--字符型转成timestamp
select TO_TIMESTAMP('01-1月-03') from dual;
--结果:01-1月 -03 12.00.00.000000000 上午
select to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') FROM dual;
--结果:01-10月-08 07.46.41.000000000 上午
--timestamp转成date型
select cast(TO_TIMESTAMP('2015-10-01 21:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff') as date)FROM dual;
--结果:2015/10/1 21:11:11
--date型转成timestamp
select cast(sysdate as timestamp) date_to_timestamp FROM dual;
--结果:10-6月 -21 02.50.07.000000 上午
select TO_TIMESTAMP_TZ('20130101','yyyymmdd') from dual;
--结果:01-1月 -13 12.00.00.000000000 上午 +08:00
select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') FIRST, to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') second from dual;
--结果:2021.06.10 02:00:00,2021.06.10 02:52:00
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" FROM DUAL;
--结果:2021年05月12日 04:01:11
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
--结果:2005/1/1 13:14:20
SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;
--结果:-12345.67
Select TO_CLOB(n'test') from dual;
分组函数也被称为多行函数,它会根据输入的多行数据返回一个结果。主要用于执行数据统计或汇总操作,并且分组函数只能出现在select语句选择列表、order by子句和having子句中。注意分组函数不能直接在plsql中引用,只能在内嵌select语句中使用。
select avg(distinct origin_code) from t_origin;
--结果:1517406.00456621
select avg(ALL origin_code) from t_origin;
--结果:1517406.00456621
select corr(origin_code,leaf) from t_origin;
--结果:0.787679048632485
SELECT count(distinct origin_code) from t_origin;
--结果:219
Select COVAR_POP(origin_code,ID) from t_origin;
--结果:985452046305418
Select COVAR_SAMP(origin_code,ID) from t_origin;
--结果:989972468536177
Select CUME_DIST(4) within group (order by origin_code) from t_origin;
--结果:0.0909090909090909
Select DENSE_RANK (4) within group (order by origin_code) from t_origin;
--结果:20
SELECT SID,
CID,
COUNT(1),
GROUPING(SID),
GROUPING(CID)
FROM SC
GROUP BY ROLLUP(SID, CID);
SELECT SID, CID, SUM(SCORE), GROUPING_ID(SID, CID)
FROM SC
GROUP BY ROLLUP(SID, CID)
select max(distinct score) from SC;
--结果:99
select min(all score) from SC;
--结果:20
select percent_rank(3000) within group(order by score) from sc;
--结果:1
select percentile_cont(.6) within group(order by score) from sc;
--结果:80
select PERCENTILE_DISC(.6) within group(order by score) from sc;
--结果:80
select rank(50) within group(order by score) from sc;
--结果:5
select stddev(score) from sc;
--结果:24.9593132970729
select stddev(distinct score) from sc;
--结果:27.6194707558846
select stddev_pop(score) from sc;
--结果:24.2560925272674
select stddev_samp(score) from sc;
--结果:24.9593132970729
Select sum(score) from sc;
--结果:1234
select VAR_POP (score) from sc;
--结果:588.358024691358
select variance (score) from sc;
--结果:622.967320261438
select variance(score) from sc;
--结果:622.967320261438
Select COALESCE(SID,cid) from sc;
Select decode(a,'金',1,'银',2,0) from table_name;
Select nullif(expr1, expr2) from table_name;
Select nvl(column_name,0) from tbale_name;
SELECT NVL2(to_date('01-jun-2016'),sysdate - to_date('01-jun-2016'),sysdate) FROM dual;
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/154754.html原文链接:https://javaforall.cn