oracle–常用sql介绍

oracle常用sql介绍

oracle是我们生产环境使用最频繁的数据库之一，因此将之前日常使用的相关操作记录下，备忘和方便使用。

1、建表

create table

-- Create table
create table TABLE1
(
  COLUMN1               VARCHAR2(40) not null,
  COLUMN2                  NUMBER(2),
  COLUMN3               DATE not null,
  COLUMN4               CHAR(1) default 0
)
tablespace spacetest
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table TABLE1
  is '表1';
-- Add comments to the columns 
comment on column TABLE1.COLUMN1
  is '字段1';
comment on column TABLE1.COLUMN2
  is '字段2';
comment on column TABLE1.COLUMN3
  is '字段3';
comment on column TABLE1.COLUMN4
  is '字段4';
-- Create/Recreate indexes 
create index TABLE1_IDEX_COLUMN1 on TABLE1 (COLUMN1)
  tablespace spacetest
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TABLE1
  add primary key (COLUMN1)
  using index 
  tablespace spacetest
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

create table as

create table table2 as
select * from table1;

2、修改表

添加字段

alter table TABLE1 add COLUMN1 varchar(40) not null; --如果有数据的话，执行报错
--处理已存在数据的表
alter table TABLE1 add COLUMN1 varchar(40) DEFAULT '无' null;
update TABLE1 set COLUMN1 = '无'
 where COLUMN1 is null;
alter table TABLE1 MODIFY COLUMN1 varchar(40) not null;

修改字段

--修改为可为空字段
alter table TABLE1 modify COLUMN1 varchar(50);
--修改为非空字段
update TABLE1 set COLUMN1 = '无'
 where COLUMN1 is null;
alter table TABLE1 MODIFY COLUMN1 varchar(40) DEFAULT '无' not null;
--修改字段类型
alter table TABLE1 rename column COLUMN1 to COLUMN1_bak;
alter table TABLE1 add COLUMN1 clob;
update TABLE1 set COLUMN1=COLUMN1_bak;
commit;
alter table TABLE1 drop column COLUMN1_bak;

3、插入更新数据

--插入
insert into TABLE1(COLUMN1,COLUMN2)
values('AA','BB');
--我们用的更多的是通过查询直接插入
insert into TABLE1(COLUMN1,COLUMN2)
select t.COLUMNA,t.COLUMNB from TABLE2 t;
--有时候还需要利用union（不包括重复行），union all（包括重复行）
insert into TABLE1(COLUMN1,COLUMN2)
select COLUMN1,COLUMN2 from(
  select t1.columnA COLUMN1,t1.columnB COLUMN2 from TABLE2 t1
  union all
  select t2.columnC COLUMN1,t2.columnD COLUMN2 from TABLE3 t2
);
--更新
update TABLE1 t
set t.COLUMN1 = 'AA'
where t.COLUMN2 ='BB';
--更新多个字段
update TABLE1 t
set t.COLUMN1='AA',t.COLUMN2='CC'
where t.COLUMN2='BB';
--多表关联更新
update TABLE1 t
set t.COLUMN1,t.COLUMN2 = (select t1.column3,t2.column4 from TABLE2 t1 where t1.ID=t.ID)
where exists(select 1 from TABLE2 t1 where t1.ID=t.ID);

4、数据删除

全表删除

--delete
delete from TABLE1;
--truncate(推荐)
truncate table TABLE1;

删除重复数据

delete from TABLE1 
where DATAID  in (select  ID  from TABLE1  group  by  DATAID   having  count(DATAID) > 1)
and rowid not in (select min(rowid) from  TABLE1  group by DATAID  having count(DATAID )>1);

5、连接查询

--内连接
select t.COLUMN1,t.COLUMN2,t1.COLUMN3 
from TABLE1 t inner join TABLE2 t1 on t1.ID=t.ID
--外连接
select t.COLUMN1,t.COLUMN2,t1.COLUMN3
from TABLE1 t left join TABLE2 t1 on t1.ID=t.ID

在左外连接和右外连接查询中，需要注意查询效率、过滤条件位置等情况

效率上来说，如果全表数据连接后在where条件中过滤，效率比较高

oracle的语法是从右到左优先执行，但是同一条sql条件先后顺序改变对效率影响不大，所以大家不比纠结于此。

--此种效率较高，从逻辑上来说按道理这种效率会低，但是oracle的机制是此种方法会效率略高（推荐使用）
select t.COLUMN1,t.COLUMN2,t1.COLUMN3
from TABLE1 t left join TABLE2 t1 on t1.ID=t.ID 
where t.COLUMN4='DD';
--先过滤再连接
select t.COLUMN1,t.COLUMN2,t1.COLUMN3
from TABLE1 t left join TABLE2 t1 on t1.ID=t.ID and t.COLUMN4='DD'；

过滤条件的位置有可能会影响查询的结果

--过滤后再连接,TABLE2表只有COLUMN4='DD'的数据会与TABLE1关联从而产生结果集
select t.COLUMN1,t.COLUMN2,t1.COLUMN3
from TABLE1 t left join TABLE2 t1 on t1.ID=t.ID and t2.COLUMN4='DD'；
--关联后再过滤,TABLE2表与TABLE1表主键关联后的结果集只展示TABLE2中COLUMN4='DD'的数据，左连接原本TABLE2应该不影响TABLE1数据展示，但是现在会过滤掉部分TABLE1的数据不展示。
select t.COLUMN1,t.COLUMN2,t1.COLUMN3
from TABLE1 t left join TABLE2 t1 on t1.ID=t.ID 
where t2.COLUMN4='DD'；

6、统计查询

case when

--根据不同的情况 将case when 作为结果集的一个列进行查询，以下例子中的workdays是一个自定义方法，用于获取两个时间之间的工作日天数
-- 按照办理天数进行分类统计
select case when workdays(d.occurtime,t.occurtime)5 and workdays(d.occurtime,t.occurtime)10 and workdays(d.occurtime,t.occurtime)15 and workdays(d.occurtime,t.occurtime)20 then '20天以上' end,count(distinct t.projid)  from workinfogj.ea_jc_step_basicinfo t
inner join workinfogj.ea_jc_step_done d on t.projid=d.projid
where t.region_id like '6103%' and to_char(t.occurtime,'yyyy')='2019'
group by case when workdays(d.occurtime,t.occurtime)5 and workdays(d.occurtime,t.occurtime)10 and workdays(d.occurtime,t.occurtime)15 and workdays(d.occurtime,t.occurtime)20 then '20天以上' end;

group by

统计查询中还经常用到分组查询，以便结果集按照实际的业务需要，按照分组维度进行统计。

-- 统计2019年度，各月份的申报量和办结量
select to_char(t.occurtime,'MM'),
count(distinct t.projid) 申报,
count(distinct case when d.projid is not null then d.projid else null end) 办结 from workinfogj.ea_jc_step_basicinfo t
left join workinfogj.ea_jc_step_done d on t.projid=d.projid
where t.region_id like '6103%' and to_char(t.occurtime,'yyyy')='2019'
group by to_char(t.occurtime,'MM')
order by to_char(t.occurtime,'MM');

7、视图

视图一方面用来筛选需要的字段、表进行一定逻辑的组装后进行展现，另一方面用来屏蔽部分大字段以保证查询效率。

--与国家对接时陕西使用视图的方式按照国家需要的数据结构提供给交换平台，避免陕西原有办件库规范的大规模调整
CREATE OR REPLACE VIEW UP_PRO_ACCEPT AS
SELECT
T.COUCATALOGCODE  AS CATALOGCODE,
trim(T.COUTASKCODE) AS TASKCODE,
T.ITEMNAME AS TASKNAME,
PA.ADDRESS AS ADDRESS,
T.PROJECTNAME AS PROJECTNAME,
(case t.approvaltype
when 3 then 2
when 4 then 2
else t.approvaltype end) AS PROJECTTYPE,
T.PROJID AS PROJECTNO,
PA.APPLYNAME AS APPLYERNAME,
T.APPLYERTYPE AS APPLYERTYPE,
m.sx_enumcode AS APPLYERPAGETYPE,
PA.APPLY_CARDTYPENUMBER AS APPLYERPAGECODE,
PA.RECEIVETIME AS APPLYDATE,
PA.APPLYFROM AS APPLYTYPE,
PA.LEGALMAN AS LEGAL,
PA.CONTACTMAN AS CONTACTNAME,
m1.sx_enumcode AS CONTACTTYPE,
PA.CONTACTMAN_CARDNUMBER AS CONTACTCODE,
substr(PA.TELPHONE,1,11) AS CONTACTMOBILE,
PA.POSTCODE AS ZIPCODE,
T.TRANSACTOR AS HANDLEUSERNAME ,
T.OCCURTIME AS ACCEPTDATE,
T.ACCEPTDOCNO AS ACCEPTDOCNO,
T.PROMISEDATE AS PROMISEDATE,
T.ACCEPTDEPTNAME AS ORGNAME,
T.ACCEPTDEPTID AS  ORGCODE,
T.CD_OPERATION AS  CD_OPERATION,
--to_char(sysdate,'yyyyMMdd')*100000+1 AS CD_BATCH ,
T.TASKHANDLEITEM AS TASKHANDLEITEM,
T.ITEM_CODE AS LOCALCATALOGCODE,
T.IMPLEMENT_CODE AS LOCALTASKCODE,
 '161' AS DATASOURCE ,
getGJGUID(t.projid,T.ORGBUSNO,t.dataver) AS ROWGUID,
T.Localtime AS CD_TIME,
PA.ITEMVERSION AS TASKVERSION
FROM EA_JC_STEP_BASICINFO T
inner JOIN PRE_APASINFO PA ON T.PROJID = PA.PROJID
inner join GJ_ENUMMAP m on m.jy_enumcode = PA.APPLY_CARDTYPE
inner join GJ_ENUMMAP m1 on m1.jy_enumcode = PA.Contactman_Cardtype
where t.isok='1' and pa.isok='1' 
and exists(select 1 from sx_canuplist sx where sx.sourceid = T.Sourceid or (sx.coutaskcode=T.Coutaskcode and sx.coucatalogcode = T.Coucatalogcode));

8、DBLINK

数据库远程链接，当存在第三方数据源、多个数据库实例等情况时，需要查询远程数据源的部分表、视图时需要建立DBLINK实现。

-- oracle 12c
-- Drop existing database link 
drop database link SERVERNAME@spacetest;
-- Create database link 
create database link SERVERNAME@spacetest
  connect to spacetest identified by 123456
  using 'SERVERNAME';

9、session查询

--查询当前会话
select USERNAME,SID,SERIAL# from v$session where username is not null;
--杀死会话
alter system kill session 'SID,SERIAL#';
--查询锁死的会话
SELECT object_name, machine, s.sid, s.serial#,s.state,s.status,s.username, l.oracle_username, 
   l.os_user_name, l.locked_mode
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid;