sql语句

--给表授权

GRANT SELECT ON BPMOU55.HD_USER to BPMAPP55;

--初始化脚本

INSERT INTO "BPMAPP55"."xxx"("ID", "DICT_VALUE", "NAME") VALUES ('applyCode', 1, 'applyCode');

--给表中新加字段

ALTER TABLE "BPMAPP55"."xxx" ADD ("fieldNameId" NUMBER(20,0));

COMMENT ON COLUMN "BPMAPP55"."xxx"."fieldNameId" IS 'xxxID';

ALTER TABLE "BPMAPP55"."xxx" ADD ("fieldName" VARCHAR2(255) );

COMMENT ON COLUMN "BPMAPP55"."xxx"."fieldName" IS 'xxx名称';

--设置字段长度

alter table "BPMAPP"."xxx" modify "fieldName" VARCHAR2(1000);

--新加字段设置默认值为0

ALTER TABLE BPMAPP55.xxx ADD IS_SHOW NUMBER(1) default '0';

COMMENT ON COLUMN BPMAPP55.xxx.IS_SHOW IS '是否修改处理人 0：是 1：否';

--清表脚本

truncate table xxx; delete from xxx;

--查询表中有多少字段 xxx:表面

select count(*) from user_tab_columns where table_name=upper('xxx');

--条件查询 case when

SELECT CASE WHEN HANDLE_NAME is null THEN '否' ELSE '是' END AS HANDLE_NAME FROM xxx;

--DECODE 函数 GENDER 字段为1：男;字段2：女

SELECT DECODE(GENDER, 1, '男', 2, '女', '未知') FROM xxx;

--oracle 查询clob类型的数据 instr()函数

SELECT * FROM xxx u WHERE dbms_lob.instr(u.WDL_DEPT_NAME,'MQD') > 0 ORDER BY u.ID DESC;

--查询索引在哪个表中加的

SELECT DISTINCT table_name,index_name FROM all_indexes WHERE index_name = '索引id';

/*

Oracle相同字段有多条记录取id最大的一条

t.business_id：多条数据中出现相同的值

t.id ：倒序排取id的最大值

rn : 倒序排列的值

*/

select * from (select t.*, row_number() over(partition by t.business_id order by t.id desc) rn from PMS_APPROVAL_APPLICATION t) c where rn = 1 and DELETED = 0 AND BUSINESS_ID =243;

/*查询多张表同字段

查询数据库中所有包含‘CHECK_WAY’字段的表*/

select owner,table_name, COLUMN_NAME,DATA_TYPE,DATA_LENGTH from all_tab_columns where table_name=upper('xxx') and owner=upper('bpmapp') and OLUMN_NAME=upper('CHECK_WAY');

--级联查询

select * from 表名 where 条件1 start with 条件2 connect by prior 当前表字段=级联表字段

--去重 distinct

select distinct * from xxx;

--删除视图 xxx：视图名称

DROP VIEW x'xx;

--创建视图 xxx：视图名称

create view xxx as select * from xxx-a;

--查询两张表中相同字段的数据 UNION ALL

select * from xxx1 union all select * from xxx2;

--删除表中重复的数据
DELETE FROM  table WHERE  ROWID NOT IN (
        SELECT MIN(ROWID) FROM  table GROUP BY id HAVING COUNT(*) > 1)
    AND id IN ( SELECT id FROM table GROUP BY id HAVING COUNT(id) > 1);

oracle:

        原始数据：1>.字段值为多个json对象，取对象中name的值，函数：JSON_TABLE        

SELECT jt.name FROM your_table,JSON_TABLE(your_table.json_data,'$[*]' COLUMNS ( name VARCHAR2(255) PATH '$.name' )) AS jt;

查询结果：

2>.如上图，将查询结果用函数将字符串拼接，函数：LISTAGG（字数有限制，4000）

SELECT LISTAGG(jt.name, ', ') WITHIN GROUP (ORDER BY jt.name) AS concatenated_names FROM your_table,JSON_TABLE(your_table.json_data, '$[*]' COLUMNS (name VARCHAR2(255) PATH '$.name')) AS jt;

3>.没有字数限制   函数：xmlagg(xmlparse(content jt.name|| ',') ORDER BY jt.name).getclobval()

SELECT xmlagg(xmlparse(content jt.name|| ',') ORDER BY jt.name).getclobval() FROM your_table,JSON_TABLE(your_table.json_data,'$[*]' COLUMNS (name VARCHAR2(255) PATH '$.name')) AS jt WHERE APPROVAL_ID = 41232 AND DELETED = 0;

--删除表中重复的数据
DELETE FROM your_table WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM your_table  GROUP BY your_filed HAVING COUNT(*) > 1)  AND your_filed IN (SELECT your_filed FROM      your_table GROUP BY your_filed HAVING COUNT(your_filed) > 1);