数据库代码汇总

1.oracle序列设置

create sequence SE_R_DOCUMENTDETAIL_1356
  minvalue 1
    nomaxvalue start with 1
      increment by 1
        noorder nocycle cache 20;

2.设置oracle外键关联

ALTER TABLE R_RECTIFICATIONSTAGE_1151 ADD CONSTRAINT SYS_C00110337 FOREIGN KEY(P_RECTIFICATIONSTAGEID) REFERENCES R_RECTIFICATIONSTAGE_1151(RECTIFICATIONSTAGEID);

3.设置新的最大连接数

set GLOBAL max_connections=200;

4.结束当前进程

kill 加进程ID

5.查看当前所有用户的连接

show full processlist;

6.我们来查看mysql的最大连接数

show variables like '%max_connections%';

7.查看序列下一个号码

select ACHILLES.SE_Q_CMN_OPPTAUDITDETAIL_558.nextval from dual

8.查询统计档案查询SQL语句

select opt20.operatepointid operatepointid,opt20.operatepointname operatepointname,opt20.OPERATEPOINTADDRESS OPERATEPOINTADDRESS,ind90.industrytypename industrytypename,
      un18.LEGALPERSON LEGALPERSON,opt20.OPPTRESPONSIBLEPERSON OPPTRESPONSIBLEPERSON,cmn1123.currentsupervisedptid currentsupervisedptid,
       opt20.opptresponsiblepersonphone opptresponsiblepersonphone,cmn1123.currentsuperviseopptid currentsuperviseopptid,
       replace(replace(opt21.operatepointname,'安全生产监察队',''),'浦东新区安全生产监察大队','安监大队') operatepointname1,opt20.longitude,opt20.latitude,cmn431.istemparchived,opt20.businessstatusid
       from q_cmn_operatepoint_20 opt20,d_industrytype_90 ind90,q_cmn_operatepoint_20 opt21,q_cmn_pandbunitsspecial_431 cmn431,
                       q_cmn_unit_18 un18,Q_CMN_OPTCURRENTSUPERVISE_1123 cmn1123
        where opt20.isdeleted=0
                      and cmn431.isdeleted = 0 and cmn431.ISNEEDREPORT=1 and cmn431.pandbunitsspecialid = cmn1123.pandbunitsspecialid
                      and cmn1123.CURRENTSUPERVISEYPE = 1 and cmn1123.isdeleted = 0 and opt20.operatepointid = cmn431.operatepointid and un18.isdeleted=0
                      --and opt20.businessstatusid between 1 and 2
                      and cmn1123.currentsuperviseopptid=opt21.operatepointid and opt20.industrytypeid=ind90.industrytypeid and opt20.unitid=un18.unitid;

9.如果查询出来的是null则用0代替

NVL(A.VERIFYNODEID, 0) VERIFYNODEID