oracle常用sql语句以及案例,Oracle常用SQL语句整理

一、查询操作

1.1 查询单表语句

select t.*, t.rowid from Table t;

1.2 查询多表关联语句

select T.id NVL(T.nesid,0) nesid,T2.name FROM Table T,(select * from Table2 where id>10) T2 WHERE T.fid =T2.id(+);

或者：

select T.id NVL(T.nesid,0) nesid,T2.name FROM Table T left join (select * from Table2 where id>10) T2 on T.fid =T2.id;

1.3 查询表100~200条

select * FROM (SELECT A.*, rownum r FROM (select t.* from Table T where T.ID>5 ORDER BY T.ID desc) A WHERE rownum <=200 ) B WHERE r >=100;

1.4 查询表的重复记录

select * from temp_111 t WHERE ROWID!=(SELECT MAX(ROWID) FROM temp_111 t1 WHERE t.username=t1.username);

1.5 查询一张表存在的数据而另一张表中没有的数据

select * from temp_111 t where t.userid not in (select distinct t1.e_user_id from hr_employee t1)

二、插入操作

2.1 插入表数据(查询满足条件的结果导入到表中)

insert into temp_111(userid,username) select T.value1,T.value2from Table T where T.id>1;

2.2 备份表

create table temp_20181119 as (select T.* from Table T where T.id=1);

三、更新操作

3.1 更新单表数据

update temp_111 set username=‘张三‘ where userid=1740;

3.2 更新表数据(根据字段更新另一张表)

update temp_111 t set t.username=(select t1.e_realname from Table t1 where t1.userid =t.userid)

四、删除删除

4.1 删除表的重复记录

delete from temp_111 t WHERE ROWID!=(SELECT MAX(ROWID) FROM temp_111 t1 WHERE t.username=t1.username);

4.2 删除表的重复记录(保留最大一条)

delete from temp_111 a where a.createdate < (select max(b.createdate) from temp_111 b where a.linkid=b.linkid);

4.3 清空表(保留该表，删除表所有记录)

truncate table temp_2018_1119;

4.4 删除记录(删除部分记录)

delete from temp_2018_1119 T where T.isdel=1;

4.5 直接删除表

drop table temp_2018_1119;

五、日期查询

5.1 常用时间格式

select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘)currenttime, --2018-11-11 15:51:01

to_char(sysdate,‘yyyy‘) year, 　　　　　　　　　　　　　　 --2018

to_char(sysdate,‘mm‘) month, 　　　　　　　　　　　　　   --11

to_char(sysdate,‘dd‘) day, 　　　　　　　　　　　　　　　  --11

to_char(sysdate,‘day‘) week, 　　　　　　　　　　　　        --星期日

to_char(sysdate,‘hh24‘)hour, 　　　　　　　　　　　　　　 --15

to_char(sysdate,‘mi‘) minute,　　　　　　　　　　　　　　  --51

to_char(sysdate,‘ss‘) second　　　　　　　　　　　　　　  --01

from dual;

5.2 查询某个时间段

SELECT * FROM Table t WHERE 1=1 AND t.create_time >=to_date(‘2018-8-22 8:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘) and t.create_time < to_date(‘2020-8-22 12:30:00‘,‘yyyy-mm-dd hh24:mi:ss‘);

5.3 查询当天

select TRUNC(SYSDATE) from dual;

5.4 查询1天前数据

select T.* from Table T where T.E_SEX=1 and TRUNC(T.CREATE_TIME)> TRUNC(SYSDATE - 1);

5.5 查询上个月日期

select add_months(trunc(sysdate),-1) from dual;

5.6 查询上个月1号

select TRUNC(add_months(trunc(sysdate),-1),‘MM‘) from dual;

六、常用函数

6.1 处理Null值：nvl(空值，代替值)

select nvl(t.isnull,1) value from dual;      --(1)

6.2 绝对值：abs()

select abs(-2) value from dual; 　　　  --(2)

6.3 取整函数(大)：ceil()

select ceil(-2.001) value from dual;      --(-2)

6.4 取整函数(小)：floor()

select floor(-2.001) value from dual;     --(-3)

6.5 取整函数(截取)：trunc()

select trunc(-2.001) value from dual;    --(-2)

6.6 四舍五入：round()

select round(1.256,2) value from dual; --(1.26)

七、字符函数select substr(‘abcdefg‘,1,5)substr,     --字符串截取 abcde instr(‘abcdefg‘,‘bc‘) instr, 　　　　     --查找子串 2 ‘Hello‘||‘World‘ concat, 　　　　　　 --连接 HelloWorld trim(‘ wish ‘) trim, 　　　　　　　　  --去前后空格 wish rtrim(‘wish ‘) rtrim,　　　　　　　　  --去后面空格 wish ltrim(‘ wish‘) ltrim,　　　　　　　　   --去前面空格 wish ascii(‘A‘) A1, 　　　　　　　　　　   --ascii(转换为对应的十进制数) 65 chr(65) C1, 　　　　　　　　　　     --十进制转对应ascii编码 A length(‘abcdefg‘) len,　　　　　　      --length 7 lower(‘WISH‘)lower,                              --转小写 wish upper(‘wish‘)upper, 　　　　　　　　 --转大写 WISH replace(‘wish1‘,‘1‘,‘youhappy‘) replace, --替换 wishyouhappy translate(‘wish1‘,‘1‘,‘y‘)translate,            --转换 wishy concat(‘11‘,‘22‘) concat 　　　　           --连接 1122from dual;