oracle 常用sql语句积累

1.删除重复列

delete from t t1 where t1.rowid not in(select min(rowid) from t t2 where t1.age=t2.age)

2.获得年龄最大的前三名

select age from (select age from t order by age desc) where rownum<=3

3.解锁表

SELECT /*+ rule */
s.username,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.terminal,
s.machine,
s.program,
s.osuser
FROM v$session s, v$lock l, dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null ;
--kill session语句
alter system kill session's.sid,s.serial#';

4.按照特定的顺序排序

select name from user order by decode(name,'张三',0), decode(name,'李四',1) ;

5.分区 范围分区，散列分区，列表分区和复合分区

create table t
(
id int
)
partition by range(id)
(
partition p1 values less than(60),partition p2 values less than(100)
) ;
create table graderecord
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by hash(sno)
(
partition p1,
partition p2,
partition p3
);
select * from t partition(p2) ;

6.//查询列的注释

select t.table_name as 列名, t.comments as 注释
from user_col_comments t
where t.table_name = 'GPS_LOCATIONTEMP'
and t.column_name = 'CLIENTEVENTID' ;

7.//单引号拼接 ''表示一个单引号

select 'abcd'''||'abd'||'''abcd''' as res from dual;
select * from s t where t.c = ''||chr(39)||''; --也是查询单引号的语句
select * from s t where t.c = ''''; --这条语句和上面的是一样的

8.//行转列

select
Student,
sum(case Course when '数学' then Score else null end) 数学,
sum(case Course when '物理' then Score else null end) 物理,
sum(case Course when '英语' then Score else null end) 英语,
sum(case Course when '语文' then Score else null end) 语文
from
TEST
group by Student ;

9. 子父关系查询

select a.eventid as grouppeventid,a.groupname as groupName
from (select t.eventid,
t.groupname,
t.grouppeventid
from gps_groups t
connect by prior t.eventid = t.grouppeventid
start with t.grouppeventid is null
) a
where 1 = 1 ;

10. id必须显示 而数量则不一定显示 用left join

SELECT T.GROUPPEVENTID AS GROUPPEVENTID, count(p.origineventid)
FROM REP_STATICS T
left join gps_person p
on t.personid = p.origineventid
AND P.HISTORICALSTATE = 1
AND P.JASSTATUS < 3
and p.persontype = '1'
WHERE T.STATICSDATE = '2012-11-23'
GROUP BY T.GROUPPEVENTID;

11.约束示例

create table s(sex varchar2(2)) ;
alter table s add constraints s_cons check (sex in('男','女')) ;
insert into s values('33') ;

12.表名必须得大写

select * from user_constraints t where t.table_name ='S';
alter table s enable constraint s_cons ;
alter table s disable constraint s_cons ;

13.删除约束

alter table s drop constraint s_cons ;

14.某月的下个月

select add_months(t.createddate,1) from gps_person t where t.origineventid='8cc5599f-ba45-
4893-b7ef-771434bf6acc' ;

15.某天的下一天

select t.createddate+1 from gps_person t where t.origineventid='8cc5599f-ba45-4893-b7ef-
771434bf6acc' ;

16.Oracle trunc()函数的用法

/**************日期********************/
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（ number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。 Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

17.因为在存储过程中DBMS_OUTPUT有限制大小，所以一般加上DBMS_OUTPUT.ENABLE
(buffer_size=>null)
加上后就没有大小限制了。。。不设置输出的缓冲如果输出超过2000字节就不可以用了 set
serveroutput on 打开输出
窗口。。。
18.左外连接实例

select a.id,a.age,b.id,b.age from a a left join b on a.id=b.id order by a.id;
select * from a ;
select * from b for update;
其中a的id有 1,2,3 b中的id有 1,1,2,3
这时外连接的条数有 4条

19.job时间格式

TRUNC(SYSDATE + 1) + (9*60+30)/(24*60) 表示下次运行时间 2013/12/27 9:30:00
TRUNC(sysdate) + 1 +(2)/(24*60) 表示下次运行时间 2013/12/27 0:02:00

13.查看重复数据

select * from a a1 where (select count(1) cnt from a a2 where a1.id=a2.id)>1 ;