oracle基础sql

目录

分页查询

日期转字符串

创建自增ID字段

JOIN

解锁用户

Trim ( string )

Oracle 大小写转换函数

EXISTS

in&exists：

获取时间的时间间隔

时间间隔转换为时分秒格式

取前5条记录和最后5条记录

占百分比的分析函数 ratio_to_report() over()

定时任务job

查询

创建

停止 

删除

修改间隔时间

修改下一次执行时间

修改定时任务 job 要执行的操作

---

分页查询

(1)、第一种

SELECT TT.ROAD_ID, TT.TRAVEL_TIME
  FROM (SELECT ROAD_ID,
               TRAVEL_TIME,
               ROW_NUMBER() OVER(ORDER BY ROAD_ID ASC) AS RN
          FROM BASE_AMAP_DATA) TT
 WHERE tt.rn >= 5
   AND 8 >= tt.rn

(2)、第二种

--表示根据ID分组，在分组内部根据DATE1排序

SELECT ID,
       NAME,
       DATE1,
       ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as RN
  FROM T1

日期转字符串

TO_CHAR(number, '格式') 是把日期或数字转换为字符串，不能指定字符串长度。

TO_DATE 字符类型按一定格式转化为日期类型

to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss')

创建自增ID字段

首先，创建一张表：

CREATE TABLE example(
ID Number(4) NOT NULL PRIMARY KEY,
NAME VARCHAR(25)
);

然后，自定义一个序列(sequence)：

CREATE SEQUENCE example_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE -- 不建缓冲区

再创建一个触发器：

CREATE TRIGGER example_triger BEFORE
INSERT ON example FOR EACH ROW WHEN (new.id is null)--只有在id为空时，启动该触发器生成id号
begin
select example_sequence.nextval into: new.id from dual;
end;

JOIN

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行

解锁用户

命令为：alter user username account unlock

其中username为被锁定的用户名。

Trim ( string )

参数string：string类型，指定要删除首部和尾部空格的字符串返回值String

Oracle 大小写转换函数

转大写UPPER

转小写LOWER

EXISTS

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)

EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因

SELECT columns FROM tables WHERE column1 in (value1, value2, .... value_n);

in&exists：

（1）、select * from A where id in(select id from B)

in()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次。

如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次,效率很差。

再如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。

（2）、select a.* from A a where exists(select 1 from B b where a.id=b.id)以上查询使用了exists语句，exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要,重要的是结果集中是否有记录，如果有则返回true，没有则返回false。当B表比A表数据大时适合使用exists()，因为它没有那么遍历操作，只需要再执行一次查询就行。

如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。

再如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。

结论：exists()适合B表比A表数据大的情况。

获取时间的时间间隔

两个Date类型字段：START_DATE，END_DATE，计算这两个日期的时间差（分别以天，小时，分钟，秒，毫秒）：
天：
ROUND(TO_NUMBER(END_DATE - START_DATE)) 
小时：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)

时间间隔转换为时分秒格式

EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }   | { TIMEZONE_HOUR | TIMEZONE_MINUTE }  | { TIMEZONE_REGION | TIMEZONE_ABBR }  FROM { date_value | interval_value } )  

select extract(day from date'2011-05-17') day from dual; 

1、获取时间间隔的截取值

select extract(day from dt2-dt1) day  
      ,extract(hour from dt2-dt1) hour  
      ,extract(minute from dt2-dt1) minute  
      ,extract(second from dt2-dt1) second  
from (   
     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1   
           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2   
     from dual)  

2、时间间隔在一天之内，可以使用如下方式，将不到一天的时间间隔*86400转换成秒，再将秒数利用to_date('','SSSSS')换算成当天的时间，换算完成后的具体时间即是要求的时间间隔的时分秒格式数据。

select to_char(to_date(trunc((to_date('2012-11-1 23:59:59',
                                      'yyyy-mm-dd hh24:mi:ss') -
                             to_date('2012-11-1 00:00:00',
                                      'yyyy-mm-dd hh24:mi:ss')) * 86400),
                       'SSSSS'),
               'fmhh24"小时"mi"分钟"ss"秒"')
  from dual

3、把100分钟显示成1小时40分钟：
方法1：超过24小时会自动转换成1天

select NUMTODSINTERVAL(100,'MINUTE') from dual;
select SUBSTR(numtodsinterval(123.13, 'hour'), 9, 11) from dual;

方法2：

--floor函数为取整函数
--mod函数为求余函数
select floor(100 / 60) HOURS, mod(100, 60) MINUTES from dual;

参考：https://blog.youkuaiyun.com/yzy85/article/details/80566651

取前5条记录和最后5条记录

SELECT * FROM  
(
  SELECT t.*  from  rc_function t where t.URL like '%X%'  order by id
)  
where ROWNUM <=5  
union
SELECT * FROM  
(
  SELECT t.*  from  rc_function t where t.URL like '%X%'  order by id Desc
 ) 
where ROWNUM <=5 

参考：https://blog.youkuaiyun.com/ameken/article/details/25771549

占百分比的分析函数 ratio_to_report() over()

建测试表

 create table test
 (
 name varchar(20),
 kemu varchar(20),
 score number
 );
 insert into test values('testa','语文',10);
 insert into test values('testa','英语',100);
 insert into test values('testb','语文',60);
 insert into test values('testb','语文',120);
 insert into test values('testc','语文',40);

 分析sql

select 
  name,
  score,
  ratio_to_report(score) over() as  "占所有科目的百分比",
  ratio_to_report(score) over(partition by kemu) as  "占各科目的百分比"
 from test ;

结果

NAME                      SCORE 占所有科目的百分比 占各科目的百分比
-------------------- ---------- ------------------ ----------------
testa                        10          .03030303       .043478261
testb                        60         .181818182       .260869565
testc                        40         .121212121       .173913043
testb                       120         .363636364        .52173913
testa                       100         .303030303                1

参考：https://blog.youkuaiyun.com/lol_3168/article/details/52838303

定时任务job

查询

SELECT t.* FROM  dba_jobs t

创建

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  --自动生成JOB_ID  
        WHAT => 'GET_WARNING_INFO(2, 202, ''2,44,11'');',  --需要执行的存储过程名称或SQL语句
        NEXT_DATE => sysdate,  --初次执行时间-sysdate 立即执行
        INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' --每隔1分钟执行一次
      );  
  commit;
end;

INTERVAL 部分参数值示例：

描述	Interval参数值
每天运行一次	‘SYSDATE+1′
每小时运行一次	‘SYSDATE+1/24′
10分钟运行一次	‘SYSDATE+10/(60*24)’
每30秒运行一次	‘SYSDATE+30/(60*24*60)’
每星期运行一次	‘SYSDATE+7′
每天午夜12点	‘TRUNC(SYSDATE+1)’
每天早上8点30分	‘TRUNC(SYSDATE+1)+(8*60+30)/(24*60)’
每星期二中午12点	NEXT_DAY(TRUNC(SYSDATE),”TUESDAY”)+12/24′
每个月第一天的午夜12点	‘TRUNC(LAST_DAY(SYSDATE)+1)’
每个季度最后一天的晚上11点	‘TRUNC(ADD_MONTHS(SYSDATE+2/24,3),’Q')-1/24′
每星期六,日早上6点10分	‘TRUNC(LEAST(NEXT_DAY(SYSDATE,”SATURDAY”), NEXT_DAY(SYSDATE,”SUNDAY”)))+(6*60+10)/(24*60)’
每月25号00：00	'TRUNC(LAST_DAY(SYSDATE ) + 25)'

1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
或
Interval => sysdate+1/1440
 
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/ (24)

3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
 
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
 
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
 
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
 
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

停止 

begin   
  dbms_job.broken(jobId,true,next_date); --停止一个job,jobId, job的ID,里面参数true也可是false，next_date（某一时刻停止）也可是sysdate（立刻停止）。 
commit;
end;

删除

begin
  dbms_job.remove(83);  --删除自动执行的job,参数是 job的id
  commit;
end;

修改间隔时间

     
      
begin
  dbms_job.interval(job，interval);  --job job的ID,interval 计算下一次任务执行的时间表达式
  commit;
end;
   

修改下一次执行时间

begin
   dbms_job.next_date(job，next_date); --job: job 的ID；nex_date:要修改后的计算下一次执行的时间表达式
   commit;
end;

修改定时任务 job 要执行的操作

    
    
begin
    dbms_job.what('newProcedures();');   --newProcedures(); 要更改的新操作名称
  commit;
end;