SQL笔记（oracle）

Dear 丶 2022-11-05 00:58 329阅读 0赞

> # 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;