常用的Oracle集合

--无密码登录数据库(Dos命令)
sqlplus / as sysdba;
------------------||表示连接符--------------------------------
select contact_id,ptn_id,title || ' ' ||first_name from pc_contact；
-----------------截取字符，dual:oracle的虚拟表----------------
select substr ('huawei',2,5) as good from dual;
-----------------排序-----------------------------------------
select * from bb_bill_charge_day a order by a.deal_date asc;
select * from bb_bill_charge_day a order by a.deal_date desc;
-----------------操作符---= ，<> > < >= <= !------------------
select * from bb_bill_charge_day a where amt !> 99999
-------------------限制行-----------------------------------
select * from bb_bill_charge_day where rownum <= 8;
-------------------AND-----------------------------------
select * from bb_bill_charge_day where hot_seq = 0 and bill_cycle_id != 20200401
-------------------OR--------------------------------------
select * from bb_bill_charge_day where bill_cycle_id = 20200401 or billcharge_id = 701000100000000000
-------------------NOT-------IN------------------------------
select * from bb_bill_charge_day where bill_cycle_id not in (20200401,20200301)
------------------BETWEEN----------------------------------
select * from bb_bill_charge_day a where amt between 20000 and 60000
------------------模糊匹配---------------------------------
select * from all_all_tables where table_name like '%LOVE%'

------------------DISTINCT去重--------------------------------
select distinct bill_cycle_id from bb_bill_charge_day;

------------------聚合函数----------------------------
-------COUNT--SUM---AVG---MIN---MAX-------------------
select SUM(AMT) from BB_BILL_CHARGE_DAY WHERE PTN_ID = 100041000000006003
select AVG(AMT) from bb_bill_charge_day where billcharge_id in (701000000000000988,701000000000000989);
select COUNT(*) FROM BB_bill_charge_day WHERE PTN_ID = 100041000000006003
select min(amt) from bb_bill_charge_day where ptn_id = 100031000000003071
select max(amt) from bb_bill_charge_day where ptn_id = 100031000000003071

-----------------------分组---------------------------
select ptn_id as Partner,count(ptn_id) from bb_bill_charge_day group by ptn_id;

-----------------------内连接-------------------------
select * from pc_agr_inst a inner join pc_partner b on a.ptn_id = b.ptn_id;
select * from pc_partner,pc_agr_inst where pc_agr_inst.ptn_id = pc_partner.ptn_id;

-----------------------左右连接,全连接---------------------------

select * from pc_partner a left join pc_agr_inst b on a.ptn_id = b.ptn_id left join admin.pm_agr_baseinfo c on b.agr_id = c.agr_id where a.ptn_id = 100031000000003071
select a.ptn_id,b.agr_id,c.agr_name from pc_partner a right join pc_agr_inst b on a.ptn_id = b.ptn_id right join admin.pm_agr_baseinfo c on b.agr_id = c.agr_id where a.ptn_id = 100031000000003071
select * from pc_agr_inst a full join pc_partner b on a.ptn_id = b.ptn_id;

-----------------------创建视图-------------------------
create view PartnerAgr as
select b.ptn_id,a.agr_id from pc_agr_inst a left join pc_partner b on a.ptn_id = b.ptn_id left join admin.pm_agr_baseinfo c on a.agr_id = c.agr_id;

 

select * from PartnerAgr; --------直接取视图数据

drop view PartnerAgr --------删除视图

----------------------子查询-----------------------------
select * from pc_partner a where a.ptn_id in (select ptn_id from pc_agr_inst where agr_id = 1446336824)

----------------------关联子查询-------------------------
select * from pc_partner a where ptn_id in (select ptn_id from pc_agr_inst b where a.ptn_id = b.ptn_id)
select * from pc_partner a where exists (select ptn_id from pc_agr_inst b where a.ptn_id = b.ptn_id)

----------------------UNION------------------------------
select ptn_type as "TYPE",status_date as "DATE"
from pc_partner where ptn_id = 100031000000012030
union
select owner_type as "TYPE",status_date as "DATE"
from pc_agr_inst where ptn_id = 100031000000012030
order by "DATE"

----------------------创建存储过程-----------------------
create procedure procedureName
as
OptionalParameterDeclarational
begin
SQLStatements
END

----------------------删除表------------------------------------
drop table 表名

---------------------插入数据-----------------------------------
insert into pc_ast_his (agr_inst_id,
src_status,
src_status_detail,
dst_status,
dst_status_detail,
oper_id,
change_date) values (3,2,'',9,'','10006',to_date('2020-05-06 17:08:00', 'yyyy-mm-dd hh24:mi:ss'));

---------------------备份表数据----------------------------------
CREATE TABLE TABLE_NAME1 AS SELECT * FROM TABLE_NAME2

---------------------提取表中xml值--------------------------------
SELECT extractvalue(xmltype(a.PLAN_POLICY_RULE),
'policy-rule/pattern-action-union/pattern/condition-selection-pattern/condition-node/logic-expression/text/simple-logic-expression/compare-expression/right-value/simple-value/text()'
) FROM admin.PE_PP_RULE a;

--------------------如果xml文本有重复节点，可以给xml排序依次取出--后面的路径为xml主节点到重复节点--前面的路径为重复节点到取该值的节点------------------
select extractValue(value(i),'/condition-node/logic-expression/text/simple-logic-expression/compare-expression/right-value/simple-value/text()') xx
from admin.PE_PP_RULE x,
table(XMLSequence(extract (xmltype(x.PLAN_POLICY_RULE),'policy-rule/pattern-action-union/pattern/condition-selection-pattern/condition-node'))) i
where PLAN_POLICY_ID = 1587114875649614726;
-------截取字符串
select substr(a.ori_event,instr(a.ori_event,'|',1,6)+1,(instr(a.ori_event,'|',1,7)-1)-(instr(a.ori_event,'|',1,6))) from ef_error_cdr_bak0703 a;

 

-----------------------------------------------------集合----------------------------------------------------------
--交集 完全一致
A intersect B
--并集
A union B --去重
A union all B--不去重
--A中移除B
A minus B
--数据库新增表字段
alter table 表名 add 字段 varchar(10)
--修改表名
rename 旧表名 to 新表名;

 

------------------------------------------------------删除表-----------------------------------------------------------
--delete删除数据是一条一条的删除数据，后面可以添加where条件，不删除表结构。注意：如果表中有identity产生的自增id列，delete from后仍然从上次的数开始增加。
delete from 表名；
--truncate是一次性删掉所有数据，不删除表结构。注意：如果表中有identity产生的自增id列，truncate后，会恢复初始值。
truncate table 表名；
--drop删除所有数据，会删除表结构。
drop table 表名；

------------------------------------------------------修改表------------------------------------------------
--添加新字段：
alter table 表名 add(字段名 字段类型 默认值 是否为空);
alter table user add(age number(6));
alter table user add (course varchar2(30) default '空' not null);
--修改字段：
alter table 表名 modify (字段名 字段类型 默认值 是否为空);
alter table user modify((age number(8));
--修改字段名：
alter table 表名 rename column 列名 to 新列名;
alter table user rename column course to newcourse;
--删除字段：
alter table 表名 drop column 字段名；
alter table user drop column course;

--------------------------------------------------------索引-------------------------------------------------------
--1、创建单一索引
create index 索引名称 on 表名(列名);
--2、创建复合索引
create index 索引名称 on 表名(列名1,列名2);
--3、删除索引
drop index 索引名称;
--4、查询表的索引
select * from all_indexes where table_name = '表名称';
--5、查询表的索引列
select* from all_ind_columns where table_name = '表名称';

--------------------------------------------------------查看表空间---------------------------------------------------
select tablespace_name,table_name from user_tables where table_name='billing_bill_dat';
select username, DEFAULT_TABLESPACE from dba_users where username='MXH';

SELECT tbs,
sum(totalM),
sum(usedM),
sum(remainedM) ,
sum(usedM)/sum(totalM)*100 ,
sum(remainedM)/sum(totalM)*100 FROM(SELECT b.file_id ID,
b.tablespace_name tbs,
b.file_name name,
b.bytes/1024/1024 totalM,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,
sum(nvl(a.bytes,0)/1024/1024) remainedM,
sum(nvl(a.bytes,0)/(b.bytes)*100),
(100 - (sum(nvl(a.bytes,0))/(b.bytes)*100))
FROM dba_free_space a,dba_data_files b
WHERE a.file_id = b.file_id
GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes
ORDER BY b.tablespace_name)GROUP BY tbs;

select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'billing_bill_dat';

SELECT a.tablespace_name "名",total/1024/1024,free/1024/1024,
(total-free)/1024/1024,
ROUND((total-free)/total,4)*100 
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name;

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024)
from dba_segments t
where t.segment_type='TABLE' and t.tablespace_name = 'billing_bill_dat'
group by OWNER, t.segment_name, t.segment_type;

select username,tablespace,session_num sid,sqladdr,sqlhash,segtype,extents,blocks from v$sort_usage;
select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'billing_bill_dat';