oracle不得不知道的sql

一、oracle 查询语句

1.translate

select translate('abc你好cdefgdc','abcdefg','1234567')from dual;
select translate('abc你好cdefgdc','abcdefg','')from dual;--如果替换字符整个为空字符 ，则直接返回null
select translate('abc你好cdefgdc','abcdefg','122')from dual;
select translate('12334567','1233' ,'abcd') from dual;--相同字符对应多个字符，按第一个来替换

2.处理空值 nvl 

null和任何数据做任何操作都是null。这点很重要
select nvl(nvl(nvl(null,0),0),0)from dual;
select nvl(1,0)from dual;
select coalesce(null,null,9,0)from dual;

3.decode  即if-then-else 逻辑

select decode(59,70,'C',80,'B',90,'A',60,'D','E')from dual;
select decode(59,null,0)from dual;

在update 语句后携带 where 条件，否则就会报错。
set sql_safe_updates=1; --mysql中 表示开启该参数 

4.拼接列：||

select '姓名：' || 'wx' from dual;
select concat('姓名','性别')from dual;

5.在where 字句中引用取别名的列

在where 字句中引用取别名的列 ，引用别名的时候要嵌套一层，因为这个别名是在select之后才有效的
select *from (
select trunc(sysdate) as t1, trunc(sysdate-1) as t2 from dual) where t1 > trunc(syadate);

6.在select语句中使用条件逻辑：case when then

select 档次,count(*) as 人数 from (
select (case when score <=60 then '0-60'
             when score <=70 then '60-70'
             when score <=90 then '70-90'
             when score <=100 then '90-100' else '0' end )as 档次
from tbl_stu u) group by 档次 order by 1;
或者
select  nvl(sum(case when score <=60 then 1 else 0 end),0),
        nvl(sum(case when score <=70 and score > 60 then 1 else 0 end),0),
        nvl(sum(case when score <=90 and score > 70 then 1 else 0 end),0),
        nvl(sum(case when score <=100 and score > 90 then 1 else 0 end),0)       
 from  tbl_stu ;

6.限制返回的行数：rownum

select *from tbl_stu where rownum < 3;

7.视图 view

视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

视图隐藏了底层的表结构，简化了数据访问操作，用户不需要知道底层表的结构和表之间的关系

create or replace view v_1 as 
select *from tbl_stu r where r.id=1; 

create or replace view v_2 as
select *from tbl_stu u where u.score='55';

create or replace view v_t4 as
select id,c1,c2,c3 from t4_1;

通过view 新增数据，不能再使用关键字default
insert into v_t4 values(5,'输入',default,'c4');
select *from v_t4;

8.复制表的定义及数据（不包含默认值，索引，约束）

8.1 复制表结构及数据

create table tbl_stu_copy as select *from tbl_stu;
select *from tbl_stu_copy;

先复制表，再复制数据

8.2 只复制表结构

create table tbl_stu_copy2 as select *from tbl_stu where 1=2;
create table tbl_stu_copy3 like tbl_stu; 
select *from tbl_stu_copy2;

8.3只复制数据

insert into tbl_stu_copy2 select *from tbl_stu; --表结构一样
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old;--表结构不一样
insert into tbl_s(id,name,sex) select id,name,'女' from tbl_stu;--表结构不一样

8.4  select into from 和 insert into select from

select into from ;--要求目标表不存在，因为在插入时会自动创建。
select id,name,descs,score,object into tbl_stu23 from tbl_stu;--  一般在PL/SQL程序块(block)中使用。 否则会报 ORA-00905: 缺失关键字。T-SQL中该句正常

insert into select from ;--要求目标表存在
insert into tbl_stu1 select *from tbl_stu where id=1;

9.with check option 限制数据的录入

alter table tbl_stu add constraint CONSTRAINT_score check(score>33);
insert into tbl_stu values (22,'ee','hahah1',35,'english');

insert into (select id,name,descs,score,object from tbl_stu
where score <=100 with check option)
values (23,'test','with check option',100,'biology');

10.多表插入语句

create table tbl_stu5 as select id,name,descs from tbl_stu where 1=2;
create table tbl_stu4 as select id,name,score from tbl_stu where 1=2;

10.1无条件 insert

insert all 
into tbl_stu4(id,name,score) values (id,name,score)
into tbl_stu5(id,name,descs)values(id,name,descs)
select  id,name,descs,score from tbl_stu u where u.id <10;
select *from tbl_stu5;

10.2有条件 insert all

delete tbl_stu4;
delete tbl_stu5;
insert all 
  when score =55 then
  into tbl_stu4(id,name,score) values (id,name,score)
  when descs ='阳光开朗，目标是年薪百万' then
  into tbl_stu5(id,name,descs)values(id,name,descs)
  select  id,name,descs,score from tbl_stu u ;

10.3 insert first 第一个条件匹配了，第二个就不会再去匹配

insert first 
  when score =55 then
  into tbl_stu4(id,name,score) values (id,name,score)
  when descs ='阳光开朗，目标是年薪百万' then
  into tbl_stu5(id,name,descs)values(id,name,descs)
  select  id,name,descs,score from tbl_stu u ;

10.4转置insert 

create table t6 (d varchar2(40),des varchar(60));
create table t7 as 
select '熊样，谨慎不加' as d1,
        '猫杨，温馨听话' as d2,
        '狗样，神气活现' as d3,
        '鸟样，向往明天' as d4
        from dual;
select *from t6;
select *from t7;
insert all
  into t6(d,des)values('周一',d1)
  into t6(d,des)values('周二',d2)
  into t6(d,des)values('周三',d3)
  into t6(d,des)values('周四',d4)
select d1,d2,d3,d4 from t7;

11 删除名称重复的数据

create table dupes (id integer,name varchar2(30));
insert into dupes values (1,'wx');
insert into dupes values (2,'wx');
insert into dupes values (3,'wx1');
insert into dupes values (5,'wx1');
insert into dupes values (6,'wx1');
insert into dupes values (7,'hahha');

11.1方法一：通过name相同，id同 删除

select *from dupes;
delete dupes;
delete from dupes a where exists (
select null from dupes b where b.name=a.name and b.id>a.id );

11.2方法二 rowid

delete from dupes a where exists (
select null from dupes b where b.name=a.name and b.rowid>a.rowid );

通过分析函数 根据name生成序列号

select rowid as rid,name,
        ROW_NUMBER () OVER (PARTITION BY name
                                   ORDER BY id) as seq 
      from dupes 
      order by 2,3;

删除seq>1 的就好,这个方法也可以去重

delete from dupes where 
rowid in(select rid from(
  select rowid as rid,name,
          ROW_NUMBER () OVER (PARTITION BY name
                                     ORDER BY id) as seq 
        from dupes )
        WHERE SEQ>1);

12.生成连续数值

select level as rn from dual connect by level<=3;

13.遍历字符串

create or replace view v5 as
select '天天向上' as 汉字,'TTXS' as 首拼 from dual;
select *from v5;
select 汉字,首拼,level from v5 connect by level<=length(汉字);
SELECT 汉字,首拼,level ,
  substr(汉字,level,1)as 汉字拆分,
  substr(首拼,level,1)as 首拼拆分,
  'subsrt('''|| 汉字||''',' || level || ',1)'  as fun from v5 
  connect by level<=length (汉字);

13.计算字符在字符串中出现的次数

create or replace view v6 as
  select 'clark,king,miller' as str from dual;
  select regexp_count(str,',')+1 as count  from v6;

14.lag，lead分析函数 lag是前一个记录，lead是后一个记录

select name,descs,score ,object ,id,
    lag(id) over(order by id )lag_id,
    lead(id) over(order by id)lead_id
  from tbl_stu;

15.周的计算

with x as (select date'2019-01-01'as d from dual)
select to_char(d,'dy')as wd1,to_char(d,'day')as wd2,to_char(d,'d')as wd3 from x;
alter session set nls_language=american;

16.取某个月的第一个周二 with ：序号

with x as (select date'2019-01-01'as d from dual)
select to_char(d,'dy')as wd1,to_char(d,'day')as wd2,to_char(d,'d')as wd3 from x;
alter session set nls_language=american;

17.union（去重了）, union all ，连接多个表的select,必须保证查询的字段及字段类型一致

select name from tbl_stu
union
select name from tbl_stu1;

18.sql 约束

--not null :不允许有空值
alter table tbl_stu modify name varchar2(20) not null;
--unique   :每行有唯一的值。一张表可以有多个uniques
--primary key:每行有唯一的值且不能为空。一张表只能有一个primary key
--foreign key:与其他表有对应的数据
--check： 保证列中的值符合指定的条件
alter table tbl_stu add constraint chk_id check(id <77);
insert into tbl_stu (id,name,descs,score)values(101,'wx','www',77);
alter table tbl_stu drop constraint chk_id;--删除约束
alter table tbl_stu modify firstdate date default sysdate;--增加单个列
alter table tbl_stu add (lastdate date,sex varchar2(10));--增加多个列
select * from tbl_stu;
--default: 给默认值

19.oracle auto-increment.通过创建序列实现

create SEQUENCE squ_stu
minvalue 11
start with 11
increment by 1
cache 100;
insert into tbl_stu(id,name,sex,descs,score)values(squ_stu.nextval,'22','2','22',55);
select squ_stu.nextval from tbl_stu;--取出了100个索引

20.sql 基本函数

20.1 avg

select avg(score),sum(score),count(*),sum(score)/count(1)from tbl_stu;

20.2 count

select count(1),count(case when score=55 then 1 end),count(case when score>55 then 1 end)
from tbl_stu;

20.3 rownum

select *from tbl_stu where rownum<=1;
SELECT *FROM(select * from tbl_stu order by id desc) where rownum<=1;

20.4 max(),min(),sum()

select name,max(score),min(score),sum(score) from tbl_stu group by name having sum(score)>55;

20.5 upper().lower()

select upper(name) ,lower(name),descs from tbl_stu;

20.6 substr() 函数用于从文本字段中提取字符

select substr(descs,2,8)from tbl_stu;

20.7 length()函数

select LENGTH(descs)from tbl_stu;

20.8 round

INSERT INTO tbl_stu (id,name,score)values(squ_stu.nextval,'hahh',33.444);
select round(score,2)from tbl_stu where name='hahh' ;
select sysdate,to_char(sysdate,'yyyy-MM-dd') from dual;

20.9 分层查询

select level ,id,sudeptid,empno from tbl_department t start with SUDEPTID is null connect by (prior id)= t.SUDEPTID;

21 抽取重复记录

with t_test as (
select 1 id ,'张三1' name, '男' sex from dual union all
select 2 id ,'张三2' name, '男' sex from dual union all
select 3 id ,'张三3' name, '女' sex from dual union all
select 4 id ,'张三4' name, '女' sex from dual union all
select 5 id ,'张三5' name, '男' sex from dual 
)
select  max(id) id , t.sex --,t.name
  from t_test t
 group by t.sex
having count(*) > 1;

with t_test as (
select 1 id ,'张三1' name, '男' sex from dual union all
select 2 id ,'张三2' name, '男' sex from dual union all
select 3 id ,'张三3' name, '女' sex from dual union all
select 4 id ,'张三4' name, '女' sex from dual union all
select 5 id ,'张三5' name, '男' sex from dual 
)
select *
  from t_test t1
 where t1.id in (select  max(t2.id) id 
  from t_test t2
 group by t2.sex
having count(*) > 1);

with t_test as (
select 1 id ,'张三1' name, '男' sex from dual union all
select 2 id ,'张三2' name, '男' sex from dual union all
select 3 id ,'张三3' name, '女' sex from dual union all
select 4 id ,'张三4' name, '女' sex from dual union all
select 5 id ,'张三5' name, '男' sex from dual 
)
select *
  from (select a.*, row_number() over(partition by sex order by id desc) rn
           from t_test a)
 where rn = 1;

22 删除全表数据

Delete from tbl_operation_cmdb_bak;

truncate table tbl_operation_cmdb_bak;

declare
    v_num       number(8);
    v_rowid     rowid;
    v_row_count NUMBER DEFAULT 500; -- 默认提交行数
    TYPE typ_cursor IS REF CURSOR;
    cur_select_table typ_cursor;
BEGIN
    OPEN cur_select_table FOR
        SELECT s.rowid
          FROM tbl_operation_cmdb_bak s;
    LOOP
        FETCH cur_select_table
            INTO v_rowid;
        EXIT WHEN cur_select_table%NOTFOUND;
        DELETE FROM tbl_operation_cmdb_bak
         WHERE ROWID = v_rowid;
        IF MOD(v_num, v_row_count) = 0 THEN
            COMMIT;
        END IF;
        v_num := v_num + 1;
    END LOOP;
    COMMIT;
    CLOSE cur_select_table;
end;

declare
    v_row_count NUMBER DEFAULT 500; -- 默认提交行数
    TYPE v_rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
    v_rowid v_rowid_type;
    TYPE typ_cursor IS REF CURSOR;
    cur_select_table typ_cursor;
BEGIN
    OPEN cur_select_table FOR
        SELECT s.rowid
          FROM tbl_operation_cmdb_bak s;
    LOOP
        FETCH cur_select_table BULK COLLECT
            INTO v_rowid LIMIT v_row_count;
        FORALL i IN 1 .. v_rowid.COUNT()
            DELETE FROM tbl_operation_cmdb_bak
             WHERE ROWID = v_rowid(i);
        COMMIT;
        EXIT WHEN cur_select_table%NOTFOUND;
    END LOOP;
    COMMIT;
    CLOSE cur_select_table;
END;

23 更新多列

update emp
   set sal =
        (select max(sal)
           from emp),
       comm =
        (select max(comm)
           from emp)
 where empno = 7369;

update emp
   set (sal, comm) =
        (select max(sal), max(comm)
           from emp)
 where empno = 7369;

update emp
   set comm = 1000
 where job in ('ANALYST', 'SALESMAN')
   and mgr = 7698;
update emp
   set sal = 1100
 where job in ('MANAGER', 'CLERK')
   and mgr = 7698;

update emp
   set comm =
        (case job
            when 'ANALYST' then
             1000
            when 'SALESMAN' then
             1000
            else
             comm
        end),
       sal =
        (case job
            when 'MANAGER' then
             1100
            when 'CLERK' then
             1100
            else
             sal
        end)
 where job in ('ANALYST', 'SALESMAN', 'MANAGER', 'CLERK')
   and mgr = 7698;

24 显示表中第5-10条记录-注意排序

with t_test as (
select 1 id ,'张三1' name, '男' sex from dual union all
select 2 id ,'张三2' name, '男' sex from dual union all
select 3 id ,'张三3' name, '女' sex from dual union all
select 4 id ,'张三4' name, '女' sex from dual union all
select 5 id ,'张三5' name, '男' sex from dual union all
select 6 id ,'张三4' name, '女' sex from dual union all
select 7 id ,'张三5' name, '男' sex from dual union all
select 8 id ,'张三4' name, '女' sex from dual union all
select 9 id ,'张三5' name, '男' sex from dual union all
select 10 id ,'张三4' name, '女' sex from dual union all
select 11 id ,'张三5' name, '男' sex from dual union all
select 12 id ,'张三4' name, '女' sex from dual union all
select 13 id ,'张三5' name, '男' sex from dual union all
select 14 id ,'张三4' name, '女' sex from dual union all
select 15 id ,'张三5' name, '男' sex from dual 
)
select *
  from (select rownum r, a.*
           from t_test a
          order by a.id)
 where r > 4
   and r < 11;

with t_test as (
select 1 id ,'张三1' name, '男' sex from dual union all
select 2 id ,'张三2' name, '男' sex from dual union all
select 3 id ,'张三3' name, '女' sex from dual union all
select 4 id ,'张三4' name, '女' sex from dual union all
select 5 id ,'张三5' name, '男' sex from dual union all
select 6 id ,'张三4' name, '女' sex from dual union all
select 7 id ,'张三5' name, '男' sex from dual union all
select 8 id ,'张三4' name, '女' sex from dual union all
select 9 id ,'张三5' name, '男' sex from dual union all
select 10 id ,'张三4' name, '女' sex from dual union all
select 11 id ,'张三5' name, '男' sex from dual union all
select 12 id ,'张三4' name, '女' sex from dual union all
select 13 id ,'张三5' name, '男' sex from dual union all
select 14 id ,'张三4' name, '女' sex from dual union all
select 15 id ,'张三5' name, '男' sex from dual 
)
select *
  from (select rownum r, a.*
           from t_test a
          where rownum < 11
          order by id)
 where r > 4;

with t_test as (
select 1 id ,'张三1' name, '男' sex from dual union all
select 2 id ,'张三2' name, '男' sex from dual union all
select 3 id ,'张三3' name, '女' sex from dual union all
select 4 id ,'张三4' name, '女' sex from dual union all
select 5 id ,'张三5' name, '男' sex from dual union all
select 6 id ,'张三4' name, '女' sex from dual union all
select 7 id ,'张三5' name, '男' sex from dual union all
select 8 id ,'张三4' name, '女' sex from dual union all
select 9 id ,'张三5' name, '男' sex from dual union all
select 10 id ,'张三4' name, '女' sex from dual union all
select 11 id ,'张三5' name, '男' sex from dual union all
select 12 id ,'张三4' name, '女' sex from dual union all
select 13 id ,'张三5' name, '男' sex from dual union all
select 14 id ,'张三4' name, '女' sex from dual union all
select 15 id ,'张三5' name, '男' sex from dual 
)
select *
  from (select /*+ first_rows */
          rownum r, a.*
           from t_test a
          where rownum < 11
          order by id)
 where r > 4;

with t_test as (
select 1 id ,'张三1' name, '男' sex from dual union all
select 2 id ,'张三2' name, '男' sex from dual union all
select 3 id ,'张三3' name, '女' sex from dual union all
select 4 id ,'张三4' name, '女' sex from dual union all
select 5 id ,'张三5' name, '男' sex from dual union all
select 6 id ,'张三4' name, '女' sex from dual union all
select 7 id ,'张三5' name, '男' sex from dual union all
select 8 id ,'张三4' name, '女' sex from dual union all
select 9 id ,'张三5' name, '男' sex from dual union all
select 10 id ,'张三4' name, '女' sex from dual union all
select 11 id ,'张三5' name, '男' sex from dual union all
select 12 id ,'张三4' name, '女' sex from dual union all
select 13 id ,'张三5' name, '男' sex from dual union all
select 14 id ,'张三4' name, '女' sex from dual union all
select 15 id ,'张三5' name, '男' sex from dual 
)
select *
  from (select rownum rn, a.*
           from (select *
                    from t_test aa
                   order by id) a
          where rownum <11) aa
 where rn > 4;

25 递归

with TBL_DIV as (
select '节点1' name, 1 c, null p from dual union all
select '节点2', 2 c, 1 p from dual union all
select '节点3', 3 c, 2 p from dual union all
select '节点4', 4 c, 3 p from dual union all

select '节点5', 5 c, 2 p from dual union all
select '节点6', 6 c, 3 p from dual  

)
SELECT name,c,  p, LEVEL,--层级       
       SYS_CONNECT_BY_PATH(name, '|'),--路径
       CONNECT_BY_ISLEAF,--是否叶子节点
       CONNECT_BY_ROOT(name) --根
  FROM TBL_DIV A
 START WITH c =1 --3
CONNECT BY PRIOR c = p;

26 存在则更新，否则插入

merge into T_POLICY_PERSON aa 
 using (select v_POLICY_NUMBER c1, v_ORGANIZATION_CODE c2 from dual) bb 
 on(aa.POLICY_NUMBER = bb.c1) 
 when matched then 
      update set aa.ORGANIZATION_CODE = bb.c2, aa.MODIFY_DATE = sysdate 
 when not matched then 
      insert(id, POLICY_NUMBER, ORGANIZATION_CODE, MODIFY_DATE) values(LOWER(SYS_GUID()), bb.c1, bb.c2, sysdate);

27 行转列

with temp as
 (select 'China' nation, 'Guangzhou' city
    from dual
  union all
  select 'China' nation, 'Shanghai' city
    from dual
  union all
  select 'China' nation, 'Beijing' city
    from dual
  union all
  select 'USA' nation, 'New York' city
    from dual
  union all
  select 'USA' nation, 'Bostom' city
    from dual
  union all
  select 'Japan' nation, 'Tokyo' city
    from dual)
select nation, listagg(city, ',') within GROUP(order by city)
  from temp
 group by nation;

28 列转行

with tt as
 (select 1 INDEXDEF, 'a,b,c,d,e' GRADECODE
    from dual)
SELECT INDEXDEF, REGEXP_SUBSTR(GRADECODE, '[^,]+', 1, LEVEL, 'i') GRADECODE
  FROM tt
CONNECT BY LEVEL <= LENGTH(GRADECODE) - LENGTH(REGEXP_REPLACE(GRADECODE, ',', '')) + 1;

二、Oracle建表

1.建user表

create table tbl_user
(
  id       NUMBER primary key not null,
  name     NVARCHAR2 (30)not null,
  content    NVARCHAR2(2000),
  createtime     DATE,
  lastmodifytime DATE
 
);
comment on column tbl_user.id is '唯一标识';
comment on column tbl_user.name is '姓名';
comment on column tbl_user.content is '调用内容';
comment on column tbl_user.createtime is '创建时间';
comment on column tbl_user.lastmodifytime is '最后修改时间';
comment on table tbl_user is '用户表';
commit;

2.创建自增的序列号

create sequence user_sequence
minvalue 1
maxvalue 99999999 --最大的值
start with 1    
increment by 1
NOCYCLE -- 一直累加，不循环
nocache; --不建缓冲区

select user_sequence.nextval from tbl_user；
select *from user_sequence ；--查询所有已建立的序列

3.存储过程

create or replace procedure p
    (v_a in number,v_b number, v_ret out number,v_temp in out number)
is 
begin 
  if(v_a >v_b)then
      v_ret :=v_b;
      else
      v_ret:=v_b;
  end if;
  v_temp:=v_temp+1;
end;

4. 创建article表

create table tbl_article(
  id number primary key not null,
  title Nvarchar2 (300),
  content Nvarchar2(2000),
  createtime     DATE,
  lastmodifytime DATE
);
comment on tbl_article.id is '唯一标示';
comment on tbl_article.title is '标题';
comment on tbl_article.content is '内容';
comment on tbl_article.createtime is '插入时间';
comment on tbl_article.lastmodifytime is '修改时间';
commit;
--创建序列
create sequence article_sequence
minvalue 1
maxvalue 99999999
start  with 1
increment by 1
nocycle --一直累加不循环
nocache; --没有缓存
--insert 语句
insert into tbl_article values(article_sequence.nextval,'java','java是面向对象的语言。',sysdate,'');
--动态插入 批量语句
 begin
    for i in 3 .. 10
    loop
         insert into tbl_article values ( article_sequence.nextval, 'java','java是跨平台语言，一次编译多次运行。因为有不同操作系统的JVM,帮我们隔开了底层代码逻辑。',sysdate,null );
    end loop;
    commit;
end;