数据库操作

(一、ORACLE)

(1.索引)

索引创建**

普通索引
create index index_name on table(column_name1,column_name2);
唯一索引
create index unique index_name on table(column_name1,column_name2);
主键索引
create index unique constraint index_name on table(column_name1,column_name2);

索引删除

drop index index_name;

索引监控

alter index index_name monitoring usage;-----监视索引是否被用到
alter index index_name nomonitoring usage;----取消监视
select * from v$object_usage;–观察监控结果

主键索引是唯一索引区别

主健可作外健，唯一索引不可；
主键有not null属性，并且每个表只能有一个；

(数据回滚)

ALTER TABLE IPLAT.T_ETL_ODS ENABLE row movement ;
flashback table IPLAT.T_ETL_ODS to timestamp to_timestamp(‘2018-08-16 15:40:00’,‘yyyy-mm-dd hh24:mi:ss’);

(特殊表、特殊语句)

all_tab_cols 所以字段信息
ALL_TABLES所以表信息
ALL_tab_comments所以表的说明信息

(查询表的建表语句)

select dbms_metadata.get_ddl(‘TABLE’,‘T_JS_203_CLZQFX’,‘APMGR’) from dual;

(查询指定表的所有字段名和字段说明)

select * from all_col_comments t where t.table_name = ‘T_ZXD_DJ_PROJECTFACTOR’ and OWNER=‘APMGR’
select * from SYSCAT.COLUMNS T WHERE T.TABSCHEMA = ‘TABLESCHEMA’ AND T.TABNAME = ‘TABLENAME’

(表的注解)

select * from SYSCAT.TABLES WHERE TABNAME = ‘TABLENAME’ AND TABSCHEMA = ‘TABLESCHEMA’

(把科学计数法转换为数字)

select to_char( to_number(‘3.62301199010211E+17’)) from dual;
select TO_CHAR(CEIL(9999999999.99999 * 9999999.9999999999999998/99999999999100000)/100000) FROM DUAL

FM9999990.999999999999 最小到厘，最大到百万亿

批量导数)

alter table hyperW nologging;
insert /*+ append */ into hyperW select * from hyper;
commit;

(方法)

trunc(n,p)

取指定位置部分(截取)，p指定截取数值的位置，p为0时表示截取整数部分，可以或略：
如trunc(251.0001)=251，trunc(-251.0001)=-251，trunc(-251.9999)=-251；
当p为正数时，表示截取时保留小数的位数；当p为负数时，表示截取时保留整数的位置，例如：
trunc(251.1234,2)=251.12，trunc(251.1234,-2)=200，trunc(251.1234,-1)=250；

round(n,p)

取指定位置部分(四舍五入)，同trunc类似，p指定截取数值的位置，p为0时表示截取整数部分，可以或略：
round(251.34)=251，round(251.56)=252，round(-251.34)=-251，round(-251.56)=-252；
当p为正数时，表示截取时保留小数的位数；当p为负数时，表示截取时保留整数的位置，例如：
round(251.1234,2)=251.12，round(251.1234,-2)=300，round(251.1234,-1)=250；
四舍五入时只考虑绝对值，不用关心正还是负。

(查询锁表和解锁)

select object_name,machine,s.sid,s.serial#
from v$locke{d}_{o}bjectl,db{a}_{o}bjectso,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

alter system kill session ‘70,16789’; //sid,serial

(二、DB2)

解决Operation not allowed for reason code “7” 问题

CALL SYSPROC.ADMIN_CMD(‘reorg table gxzl.T_ADS_REPORT3’);

(2.Functions)

循环查询一张表
CREATE OR REPLACE FUNCTION “BG00MASZGJ”.“F_RP_SZGJ_GJ0002”
(parm VARCHAR(20))
RETURNS VARCHAR(20)
LANGUAGE SQL
SPECIFIC “F_RP_SZGJ_GJ0002”
BEGIN
DECLARE a VARCHAR(20); DECLARE b VARCHAR(20);
DECLARE c VARCHAR(20); DECLARE d VARCHAR(2);
DECLARE e VARCHAR(20); DECLARE f INTEGER;
set e =parm; set d=‘0’; set f=0;
WHILE (d=‘0’) DO
select IN_MAT_NO_1 INTO a from(
select IN_MAT_NO_1,PRE_UNIT_CODE from mmsi.tmmsi01 where AT_NO = e);
select PRE_UNIT_CODE INTO b from(
select IN_MAT_NO_1,PRE_UNIT_CODE from mmsi.tmmsi01 where MAT_NO = e );
IF b=‘Q114’ THEN set c = e; set d = 1;
ELSEIF b=‘Q214’ THEN set c = e; set d = 1;
ELSEIF b=‘Q314’ THEN set c = e; set d = 1;
ELSEIF b=‘Q414’ THEN set c = e; set d = 1;
ELSE set e = a;
END IF;
IF f>2 THEN set d = 1; set c = parm;
ELSE set f = f+1;
END IF;
END WHILE;
RETURN c;
END

3.相同数据设置一列序号

把MAT_NO一样的数据，按照SAMPLE_POS排序设置行号从1开始
ROW_NUMBER() OVER (PARTITION BY MAT_NO ORDER BY SAMPLE_POS) AS RW

4.多行数据设置成一行

LISTAGG(x,y)连个参数，y非必填，x必填；x代表需要合并的列，y代表合并之后中间以什么连接。
SELECT LISTAGG(MAT_NO,‘-’) ,LISTAGG(PRE_UNIT_CODE,‘,’) FROM zjgx.HMMSI01 group by MAT_NO;

5。权限

------------------把一个表的查写改删权限给另外一个用户（MMCR.TMMCR01表的权限给bgmsrz00用户）--------------------------
grant select,insert,update,delete on MMCR.TMMCR01 to bgmsrz00
GRANT INSERT ON TABLE account TO USER jeff --把表account的写入权限给用户jeff
REVOKE ALL PRIVILEGES ON TABLE staff FROM USER jen --撤销用户jen对表staff的所有权限
GRANT DBADM ON DATABASE TO USER sally --把DBADM权限给用户sally
REVOKE DBADM ON DATABASE FROM USER maint --撤销用户maint的DBADM权限
------------------把存储过程(BG00MAGXDB.P_ADS_FACT_GXDB)的权限给另外一个用户（bgmsrz00）--------------------------
GRANT EXECUTE ON PROCEDURE BG00MAGXDB.P_ADS_FACT_GXDB_LZZB1 TO USER bgmsrz00 WITH GRANT OPTION
–下面这句写入存储过程里面使用
GRANT EXECUTE ON PROCEDURE “BG00MAGXDB”.“P_ADS_FACT_GXDB_LZZB1”( VARCHAR(8) ) TO USER “BG00MAGXDB” WITH GRANT OPTION;
-----------------------把函数(BG00MASZGJ.F_RP_SZGJ_GJ0001)给用户(bgmsrz00)--------------------
GRANT EXECUTE ON FUNCTION BG00MASZGJ.F_RP_SZGJ_GJ0001 TO USER bgmsrz00 WITH GRANT OPTION;
循环复制权限（把用户v_from_user的所有表权限给v_to_user ）
declare
– Local variables here
i integer;
v_sql varchar2(1000);
v_from_user varchar2(100);
v_to_user varchar2(100);
begin
v_to_user := ‘user1’;
v_from_user := ‘user1’;
– Test statements here
for x in (select t.* from dba_tables t where t.OWNER = upper(v_from_user)) loop
v_sql := 'grant select,insert,update,delete on ’ || v_from_user || ‘.’ || x.table_name ||
’ to ’ || v_to_user;
execute immediate v_sql;
end loop;
end;

获取schema下面的表名称

select NAME,REMARKS from sysibm.systables where CREATOR = ‘BGTAMSRZ00’

获取schema名称

select schemaname from syscat.schemata

获取字段名称

SELECT TABNAME,COLNAME,REMARKS FROM SYSCAT.COLUMNS WHERE TABSCHEMA=‘BGTAMSRZ00’;

WHERE

IS NULL 过滤null数据
=‘’ 过滤为空和多个空格的数据

sql优化

优化前：
SELECT 各种字段 FROM table_name WHERE 各种条件 LIMIT 0,10;
优化后：
SELECT 各种字段 FROM table_name main_tale RIGHT JOIN
(
SELECT 子查询只查主键 FROM table_name WHERE 各种条件 LIMIT 0,10;
) temp_table ON temp_table.主键 = main_table.主键