Oracle中SQL

置顶 vigour_190719

已于 2022-06-06 16:33:24 修改

阅读量336

点赞数

分类专栏： Oracle 文章标签： SQL总结

于 2019-09-27 14:05:32 首次发布

本文链接：https://blog.csdn.net/weixin_44743134/article/details/98493388

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

oracle账号获取http://bugmenot.com/view/oracle.com

实用的SQL操作

一、关于时间的SQL

1）给表添加注释

comment on table tablename is ‘我的aaa表’;

2）恢复删除且已提交的数据

查询某个时间节点的数据

select * from 表名 as of timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss');

恢复删除且已提交的数据

flashback table 表名 to timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss');

已过执行第二步报错，就执行这一步【允许更改时间戳】

alter table 表名 enable row movement;

3）去除重复数据

完全重复记录的删除：distinct

create table table_name01 as select  distinct * table_name;
drop table table_lee;
alter table table_name01 rename to table_name;

部分字段的重复：where里面的字段必须和select后面跟的字段对应

--查询重复数据
select * from xczzb_month a where (a.fl, a.zbmc,a.rq,a.bqz,a.bqzdw,a.bqtb,a.bqhb,a.ljzdw,a.ljtb,a.ljhb) in (select b.fl, b.zbmc,b.rq,b.bqz,b.bqzdw,b.bqtb,b.bqhb,b.ljzdw,b.ljtb,b.ljhb from xczzb_month b group by b.zbmc,b.fl,b.rq,b.bqz,b.bqzdw,b.bqtb,b.bqhb,b.ljz,b.ljzdw,b.ljtb,b.ljhb having count(*) > 1) order by a.rq
--删除重复的数据，保留yc_ljz最大的那组数据
delete xczzb_month a where (a.fl, a.zbmc,a.rq,a.bqz,a.bqzdw,a.bqtb,a.bqhb,a.ljzdw,a.ljtb,a.ljhb) in (select b.fl, b.zbmc,b.rq,b.bqz,b.bqzdw,b.bqtb,b.bqhb,b.ljzdw,b.ljtb,b.ljhb from xczzb_month b group by b.zbmc,b.fl,b.rq,b.bqz,b.bqzdw,b.bqtb,b.bqhb,b.ljz,b.ljzdw,b.ljtb,b.ljhb having count(*) > 1) and yc_ljz not in (select max(yc_ljz) from xczzb_month group by zbmc,fl,rq,bqz,bqzdw,bqtb,bqhb,ljz,ljzdw,ljtb,ljhb having count(*) > 1)

4）将一个表的数据迁移到另一个表中

--将查询到的数据放到另一个表中
insert into xczzb_quarter_lee_02 select * from xczzb_quarter_lee_01
--将查询到的固定几个列放到另一个表中
insert into xczzb_quarter_lee_02(fl,zbmc) select fl,zbmc from xczzb_quarter_lee_01

5*）将fl,zbmc字段用到小括号的都改为下划线；将下划线都改为‘-’

--like查询下划线的，用到escape
select * from xczzb_month_lee where fl like '%a_%' escape 'a' order by fl,zbmc,rq
--like查询小括号的【需要解决】

6）计算浦东商品销售总额占上海的比重

7）替换字符串

--将201312,201412,201512,201612等等以此类推的数据改为2013,2014,2015,2016
update xczzb_quarter_lee_01 set rq = replace(rq,'12','')

拼接字符串

select concat('aaa',name) from tablename;
select 'aaa'||name from tablename;

8）修改字段大小

alter table DZXXCPZZYZB_1 modify (c01_4 number(15,2));

9）添加字段，并设置默认值和注释

--添加字段
alter table tablename add (aaa number(1) default '0' not null);
--给字段添加注释
comment on column tablename.is_tb
  is '是否为同比，1：同比 0：非同比';

10)查看数据库的表

--查看当前用户下的所有表（表名、表的数据条数、表说明）
select  a.TABLE_NAME,a.NUM_ROWS, b.COMMENTS
  from user_tables a, user_tab_comments b
 WHERE a.TABLE_NAME = b.TABLE_NAME and a.TABLE_NAME like 'XCZZB%'
 order by TABLE_NAME;

批量删除表

select 'drop table '||table_name||';' from user_tables where table_name not like 'TL_%';

11）行转列

--使用decode函数，读法[ˌdiːˈkoʊd]
--输入length(bqz)，如果等于4, 就返回 rq*100 ,如果 不等于 4 就返回 rq   rq>2017
	decode(length(rq), 4, rq * 100, rq)
https://blog.csdn.net/xiaogg3678/article/details/41744813
--使用case when
https://blog.csdn.net/xiaogg3678/article/details/41744813
附加1：使用decode函数来使用表达式来搜索字符串
https://blog.csdn.net/qq_38941937/article/details/81625691【第五条有详解】
附加2：行转列并求和
https://blog.csdn.net/u014242422/article/details/52782537【第三条有详解】

12）group by rollup和 group by cube区别

--参见别人的总结
https://blog.csdn.net/make_1998/article/details/82938038

13）update或者insert高效执行

--语法一：
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES(T2.a,T2.b);
--语法二：
merge into 目标表 a

using 源表 b

on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  

when matched then update set a.更新字段=b.字段,a.更新字段2=b.字段2……

应用

merge into emp a
using (select dempno,sal from demp) b
on (a.dempno= b.dempno and a.name = '张三' )
when matched then
  update set a.sal= b.sal
when not matched then
  insert (dempno,sal) values(b.dempno,b.sal)

14.零散汇总

汉字按照拼音排序

ORDER BY nlssort(NAME, 'NLS_SORT=SCHINESE_PINYIN_M')

修改表名

ALTER TABLE old_table_name RENAME TO new_table_name;

查看表大小

select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='默认表名大写';

截取字符串

--<1> 截取“hello,world”字符串中“,”分隔符之前的字符串
SELECT SUBSTR('hello,world', 1, INSTR('hello,world', ',')-1) FROM DUAL;
--结果：hello
--<2> 截取“hello,world”字符串中“,”分隔符之后的字符串                                 
SELECT SUBSTR('hello,world', INSTR('hello,world', ',')+1) FROM DUAL;
--结果：world
--<3> 截取“hello,world,HH”字符串中第1次出现的“,”字符和第2次出现的“,”字符之间的字符串 
SELECT SUBSTR('hello,world,HH', INSTR('hello,world,HH', ',',1)+1, INSTR('hello,world,HH', ',', 2)-1) FROM DUAL;
--结果：world

一、关于时间的SQL

取随机时间

--随机时间
select to_date(to_char(lpad(floor(dbms_random.value(1017, 2019)), 4, '0') ||
                       lpad(floor(dbms_random.value(1, 12)), 2, '0') ||
                       lpad(floor(dbms_random.value(14, 17)), 2, '0') ||
                       lpad(floor(dbms_random.value(9, 16)), 2, '0') ||
                       lpad(floor(dbms_random.value(0, 59)), 2, '0') ||
                       lpad(floor(dbms_random.value(0, 59)), 2, '0')),
               'yyyyMMddhh24miss')
  from dual;

取部分随机时间

select to_date((to_char(sysdate, 'yyyyMM') ||
               lpad(floor(dbms_random.value(14, 17)), 2, '0') ||
               lpad(floor(dbms_random.value(9, 16)), 2, '0') ||
               lpad(floor(dbms_random.value(0, 59)), 2, '0') ||
               lpad(floor(dbms_random.value(0, 59)), 2, '0')),
               'yyyyMMddhh24miss')
  from dual;

增加时间

--增加一天
select sj,sj+1 from table_name
--增加一个小时
select sj,sj+1/24 from table_name
--增加一分钟
select sj,sj+1/(24*60) from table_name
--增加一秒钟
select sj,sj+1/(24*60*60) from table_name