SQL笔记（oracle）

1、建表语句

create table table_name
(
    tk_id       VARCHAR2(32)  not null PRIMARY KEY,   
    CREATE_REN_ID     VARCHAR2(32),
    CREATE_TIME       DATE, 
    UPDATE_REN_ID      VARCHAR2(32),
    UPDATE_TIME       DATE
);

---

2、添加注释

comment on table  table_name                         is  '这里是表注释';
comment on column table_name.column_name             is  '这里是字段注释';

---

3、追加联合主键

alter table tablename add constraint unionkeyname primary key (column1,column2);
-- tablename为要添加联合约束的表
-- unionkeyname为添加的联合约束的名称
-- column1，column2为联合主键作用的两个列列名

---

4、增删改查

-- 查询语句
select * from table_name;

-- 新增语句
insert into table_name (column_name) values('99999');

-- 更新语句
update table_name set value = 'value',value2 = 'value2' where id = 'id';

-- 删除语句
delete from table_name where id = 'id';

---

5、新增字段 

-- 新增字段
ALTER TABLE table_name ADD column_name VARCHAR2(32);
ALTER TABLE table_name ADD column_name VARCHAR2(32) Default '0';

---

6、修改字段大小

-- 修改字段大小 
alter table table_name modify  column_name varchar(30);

---

7、 sequence 

-- 创建序列
CREATE SEQUENCE seq_name 
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E20
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER


-- 查看创建的 序列
select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences where sequence_owner='用户名';

--  删除sequence
DROP SEQUENCE seq_name;

---

8、left join 后接 条件  in ()

-- left join + instr 妙用
SELECT wm_concat(u.name)
FROM table a
         LEFT JOIN table_b u ON instr( a.user_id ,  u.user_id ) > 0
where a.id = '123';

---

9、按照一个字段去重，其他字段正常展示

select * from (
    select t.*, row_number() over(partition by 去重字段 order by rownum) rn from 表明 t where 条件
) where rn = 1

---

10、字符串按照指定字符,拆分成表

select regexp_substr('85,125,2,15,66', '[^,]+', 1, level) abc, level lv
from dual
connect by regexp_substr('85,125,2,15,66', '[^,]+', 1, LEVEL) is not null;

---

11、 结果 按照 in 的顺序 排序

select max(str) str
from (
         select ncol, wm_concat(PT_NAME) over ( partition by ncol order by lv) str
         from (
                  select 1 ncol, t.PT_NAME, o.lv
                  from table_VB_T t,
                       (select abc, lv
                        from (
                                 select regexp_substr(
                                                    (select zg_id from table_A_T where HT_ID = '28282'),
                                                    '[^/]+', 1, level) abc,
                                        level                          lv
                                 from dual
                                 connect by regexp_substr(
                                                        (select zg_id from table_A_T where HT_ID = '28282'),
                                                        '[^/]+', 1, LEVEL) is not null
                             )) o
                  where t.PT_ID = o.abc
                  order by o.lv) a)
group by ncol;