-- left join 以左边的表为主，根据on关联条件，左边所有的数据都会显示出来，右边表中在左边没有匹配的数据会以Null来代替-- right join 是以右边为主，根据on关联查询出数据，根据on关联出来的数据会选出来，左边表中在右边没有匹配的数据会以Null来代替-- inner join 要根据on为查询条件 查询出左边和右边都能根据on匹配的数据 ，没有匹配的不会选出来，就用 inner join ##*****************一下都是DB2中的操作*********************************************************************select * from conf_bt_before_task where name like '%信函预处理%'select * from conf_bt_after_task where name in('导出直催PDA外访总结码', '待外催案件自动退案处理','预测拨号结果更新','更新诉讼失效日期') SELECT T.* FROM TBL_PCCS_BIZ_ACT_TC T WHERE T.case_id = 120151021000061668AND T.crt_time LIKE '2015-10-20%'AND EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_CUST_TEL AS E WHERE T.phone = E.phone AND data_source='HOST' ) WITH ur-----------------------------------------------------------------------------------------------------SELECT case_id ,QUEUE_ID FROM TBL_PCCS_BIZ_CASE_CASEMAIN m WHERE value(m.QUEUE_ID,'')<>'E0100'AND NOT EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_CASE_ACCT a WHERE m.CASE_ID=a.CASE_ID AND value(a.STIS_FLAG,'N')='N' ) ------------------------------------------------------------------------------------------------------ SELECT * FROM TBL_PCCS_BIZ_CASE_CASEMAIN m WHERE value(m.QUEUE_ID,'')<>'E0100'AND EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_CASE_ACCT a WHERE m.CASE_ID=a.CASE_ID AND value(a.STIS_FLAG,'N')='N' )-----------------------------------------------------------------------------------------------------SELECT * FROM tbl_pccs_biz_case_casemain AS mLEFT JOIN ( SELECT DISTINCT case_id,acct_no FROM tbl_pccs_biz_case_acct WHERE stis_flag !='N' ) AS aON m.case_id = a.case_id WHERE value(m.queue_id,'')<>'E0100'-----------------------------------------------------------------------------------------------------SELECT * FROM tbl_pccs_biz_case_casemainWHERE case_id IN ( SELECT case_id FROM tbl_pccs_biz_case_acct WHERE stis_flag !='N')AND value(queue_id,'')<>'E0100'-----------------------------------------------------------------------------------------------------SELECT CHK.ASST_CHK_ID, CHK.STATUS, CHK.CASE_ID, CHK.CUST_COMPANY,CHK.BEGIN_TIME, CHK.NODE_TIME_LMT,DIC.BUSIN_NAMEFROM TBL_PCCS_BIZ_CASE_ASTCHK AS CHKLEFT OUTER JOIN TBL_PCCS_BNDICT_T_DICTIONARY AS DICON CHK.CUST_COMPANY = DIC.BUSIN_IDWHERE DATE(CHK.ASS_CHK_PROC_TIME_LMT) < ( DATE('2012-2-12')+ 1 DAY)AND CHK.STATUS IN('02', '05', '07', '09')AND DIC.BUSIN_TYPE_ID='PDA_Org_Center' WITH UR-----------------------------------------------------------------------------------------------------SELECT CHAR(UPDATE_DATE,ISO),SOURCE,SEQ,CUSTID,FUN,ACCOUNT,STATUS_CODE,STATUS_CODE_2,MEMO,PAY_TYPE,OPERATOR,EXT, rownumber() over (ORDER BY SEQ) AS ROW_NEXT FROM BT_OPT_UPLOADHOST-------------------------------------------------------------------------------------------------------正常25号大于26号，数据库中 26是大于25号的SELECT * FROM TBL_PCCS_BIZ_CASE_CASEMAINWHERE IN_QUEUE_TIME <=(DATE('2020-08-30') - (2) DAY) AND QUEUE_ID ='R0204' AND CASE_ID= 12016082800AND AS_MAINTAINER <> 'ASPECT'-----------------------------------------------------------------------------------------------------SELECT T.* FROM TBL_PCCSWB_BIZ_OTHER_LINK_TEL AS T INNER JOIN WBCL_USR.TBL_PCCSWB_BIZ_CASE_CASEMAIN AS C ON T.CUST_NO=C.CUST_NO AND C.CUST_NO <>''INNER JOIN WBCL_USR.TBL_PCCSWB_BIZ_CASE_OUTAGREE AS O ON C.CASE_ID=O.CASE_ID AND EXISTS (SELECT 1 FROM DBCL_USR.TBL_PCCS_CONF_GL_GLOBAL as L where O.OA_CASE_PROTL_ADJ_DATE=L.BATCH_DATE FETCH FIRST ROWS ONLY)AND O.DEPUTE_DATE '' -----------------------------------------------------------------------------------------------------SELECT caseId.CASE_IDFROM ( SELECT caseMain.CASE_ID FROM TBL_PCCS_BIZ_CASE_CASEMAIN AS caseMain WHERE EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_PREVIOUS_STOP_COLL AS proStopColl WHERE proStopColl.CUST_NO=caseMain.CUST_NO) AND caseMain.QUEUE_ID!='E0100') AS caseId --------------------------------------------------------------------------------------------------SELECT * FROM TBL_PCCS_FP_DEDUCT_DETAIL_BATCH fetch first 1 rows only--清空表ALTER TABLE TBL_PCCS_BIZ_TMP_HANDWORK_CREDITL ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLEDELETE FROM TBL_PCCS_BIZ_TMP_HANDWORK_CREDITLDROP TABLE TBL_PCCS_FP_DEDUCT_DETAIL_BATCHCREATE TABLE TBL_PCCS_FP_DEDUCT_DETAIL_BATCH ( ID_PCCS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, CURRENCY VARCHAR(4), NAME VARCHAR(20), AGE INTEGER, RELATION_LEVELS INTEGER DEFAULT 1, SALARY DECIMAL(15,2) DEFAULT 0.0, BIRTHDAY DATE, SYS_TIME TIMESTAMP, UPDATE_USER VARCHAR(20), DEBIT_CARD_NO VARCHAR(30) NOT NULL, REQ_DEDUCT_AMT DECIMAL(15,2),--double 类型 CERT_TYPE VARCHAR(4), CERT_NO VARCHAR(30), CRT_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL , LST_UPD_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, LST_UPD_DATE DATE NOT NULL DEFAULT CURRENT DATE, LST_UPD_USER VARCHAR(32) NOT NULL DEFAULT 'SYSTEM', REC_STATUS CHARACTER(1) DEFAULT '0' NOT NULL, SCR_LEVEL CHARACTER(2) NOT NULL DEFAULT '00', CONSTRAINT ident PRIMARY KEY(ID_PCCS) )insert into TBL_PCCS_FP_DEDUCT_DETAIL_BATCH_HIS( BRANCH_ORG,COMM_TYPE,CURRENCY,ACCT_NO,DEBIT_CARD_NO,CREDIT_CARD_NO,REQ_DEDUCT_AMT,CERT_TYPE,CERT_NO,CREATE_TIME,CREATE_USER,LST_UPD_TIME, LST_UPD_USER) values('徐家汇分行','贷款','美元','908654678756','235643535435','686787797897',1000.00,'身份','3709231567802864471','2015-07-22 09:37:34','SYSTEM',current timestamp ,'SYSTEM') SELECT DISTINCT bca.CUSTID, pc.CUST_NAME, pa.CARDID, pc.PRINCIPAL_RMB_AMT, pa.FEE_AMT, pda.BALANCE_AMT_CUR, pc.AUTO_STATE_CODE, pct.TEL_NO, pa.BRANCHID FROM BT_RPT_CREDIT_ACCT bca LEFT JOIN ( SELECT * FROM PCCS_CASE_ACCT WHERE PCCS_CASE_ACCT.MONEY_TYPE='156') pa ON pa.CASEID = bca.CASEID LEFT JOIN PCCS_CASE_CASEMAIN pc ON pc.CASEID = bca.CASEIDLEFT JOIN PCCS_CASE_DEBIT_ACCT pda ON pda.CUSTID = bca.CUSTID LEFT JOIN( SELECT TEL_NO, custid FROM PCCS_CUST_TEL WHERE TEL_TYPE = 'MB' AND CUST_REL = 'SELF')AS pct ON pct.CUSTID = bca.custid -----------------------------------------------------------------------------------------------------SELECT a.CustID, a.Tel_No, a.Dial_Time, b.DISPOSITION_NAMEFROM BT_IMP_DAILERCONSQ a, CONF_AUTODIAL_Disposition bWHERE a.DispID=INT(b.DispositionId) ORDER BY a.CustID-----------------------------------------------------------------------------------------------------select c.ACCT_NO from TBL_PCCS_BIZ_BT_TMP_CREDIT_ACCT c where not exists(select 1 from TBL_PCCS_BIZ_BT_TMP_DEBIT_ACCT d where d.CUST_NO = c.CUST_NO)-----------------------------------------------------------------------------------------------------SELECT c.CUST_NAME, acct.CREDIT_CARD_NO, c.STATEMENT_DATE_STR, SUM( CASE WHEN acct.CURRENCY = '156' THEN value(CUR_CYC_STMT_BAL,0) END) AS CYCLE_BAL_AMT_RMB, SUM( CASE WHEN acct.CURRENCY = '840' THEN value(CUR_CYC_STMT_BAL,0) END) AS CYCLE_BAL_AMT_DOLLARFROM TBL_PCCS_BIZ_CASE_CASEMAIN cLEFT JOIN TBL_PCCS_BIZ_CASE_ACCT acct ON c.CASE_ID = acct.CASE_IDWHERE c.CASE_ID = 120150818000000611 AND c.CARD_PROD=201GROUP BY c.CUST_NAME, acct.CREDIT_CARD_NO, c.STATEMENT_DATE_STRORDER BY c.CUST_NAME,acct.CREDIT_CARD_NO WITH ur-----------------------------------------------------------------------------------------------------SELECT * FROM TBL_PCCS_BIZ_CASE_CASEMAIN aINNER JOIN ( SELECT COLL_ID, SCEN_ID , CENTER_NO FROM TBL_PCCS_CONF_GL_COLLIDDEAL WHERE DAIL_TYPE IN ('PRDT', 'PRVW') ) bON a.CENTER_NO = b.CENTER_NO AND a.PROMPT_MSG_SEQ_NO = b.COLL_ID AND a.TRIAD_SCENID = b.SCEN_IDWHERE VALUE( ( SELECT T.check_date FROM TBL_PCCS_BIZ_ACT_ACTION T WHERE a.CASE_ID=T.CASE_ID AND T.check_date IS NOT NULL ORDER BY T.LST_UPD_TIME FETCH FIRST row only) ,CURRENT DATE) <= ( SELECT BATCH_DATE FROM TBL_PCCS_CONF_GL_GLOBAL FETCH FIRST rows only)AND( ( a.CASE_STOP_COLL_FLAG = 'N' OR a.CASE_STOP_COLL_FLAG IS NULL) AND ( a.TEL_COLL_STOP_COL_FLG = 'N' OR a.TEL_COLL_STOP_COL_FLG IS NULL) )AND NOT EXISTS( SELECT 1 FROM TBL_PCCS_BIZ_APPR_APPR T1 WHERE T1.CASE_ID=a.CASE_ID AND T1.APPR_STATUS='APPR' )AND a.BELONG_BUSI_GRP = 'MDFY' AND a.QUEUE_ID !='E0100'AND ( a.EVER_COLL_OPER_ID IS NULL OR EVER_COLL_OPER_ID='ASPECT')-----------------------------------------------------------------------------------------------------select count( distinct(case_id) ) from wbcl_usr.TBL_PCCSWB_TMP_BIZ_CASE_CASEMAINselect CASE_ID,COUNT(1) from wbcl_usr.TBL_PCCSWB_TMP_BIZ_CASE_CASEMAIN GROUP BY CASE_ID ORDER BY 2-----------------------------------------------------------------------------------------------------SELECT M.CASE_ID, M.QUEUE_IDFROM TBL_PCCS_BIZ_CASE_CASEMAIN M, TBL_PCCS_BIZ_CASE_ACCT T, VIEW_CASE_TRANS VWHERE M.CASE_ID=T.CASE_ID AND T.ACCT_NO= V.ACCT_NO AND ( M.QUEUE_ID = 'O0100' OR M.QUEUE_ID = 'O0200')AND VALUE(M.COLL_NO,'')='' AND M.FAKE_TYPE IS NULLAND EXISTS ( SELECT 1 FROM TBL_PCCS_CONF_GL_QUEUEDEF WHERE QUEUE_ID = M.LAST_QUEUE_NO AND TEAM_ID IN('TELE', 'MDFY'))GROUP BY M.CASE_ID, M.QUEUE_ID, M.MIN_PAY_BAL_OF_FS_OA_BUHAVING SUM(V.TRANS_AMT) >= M.MIN_PAY_BAL_OF_FS_OA_BU / 2.0 -----------------------------------------------------------------------------------------------------select max(G.appr_ser_no) from dbcl_usr.TBL_PCCS_BIZ_APPR_OAORGCHG G where LST_UPD_DATE = date('2015-12-31')and NEW_COLL_ORG is not null and NEW_COLL_ORG <>'' group by G.case_id----------------------------------------------------------------------------------------------------- select * from TBL_PCCS_BIZ_CUST_TEL where tel_no in ( select max(tel_no) from ( select * from TBL_PCCS_BIZ_CUST_TEL T where T.cust_no = '0019712660186' and T.phone in(select phone from TBL_PCCS_BIZ_CUST_TEL where DATA_SOURCE in('HOST') ) ) group by phone )------------------------------------------------------------------------------------------------------- CASE WHEN THEN 用法SELECT MOVE_CODE,MOVE_TYPE,IS_VISIBLE,CASE MOVE_CODE WHEN 'CUP' THEN 'W' WHEN 'LJYD' THEN 'L' WHEN 'BRYD' THEN 'B' WHEN 'WNXT' THEN 'W' ELSE 'E' ENDfrom TBL_PCCS_CONF_GL_ACTCODEDEFSELECT CASE WHEN DATA_SOURCE= 'WLFK' THEN '网络发卡' WHEN DATA_SOURCE='SJWL' THEN '社交网络' WHEN DATA_SOURCE='BANK' THEN '人行' ELSE '其它' ENDfrom TBL_PCCS_BIZ_CUST_TEL_EXPAND -------------------------------------------------------------------------------------------------------N天不通 SELECT LST_UPD_DATE, SUM(EFFECTFLAG) AS STATUSFROM ( SELECT DISTINCT LST_UPD_DATE, ( CASE WHEN TEL_CODE IN ('MESS','LESD', 'LESP', 'LESS','LESK', 'LESR', 'LESF','LESC''LESX','PTP', 'PTPD','PTPP', 'PTPS', 'PTPK','PTPR', 'PTPF', 'PTPC','PTPX','ALPA','QUIT','OOOC', 'FEE','REGO','MOVE','MEET', 'REST','WORK','ONTK','INSY','NOIN','KNOW','CUT','DLYD','DLYP','DLYS', 'DLYF','DLYC','DLYX','BRKD', 'BRKP','BRKS', 'BRKK','BRKR','BRKF','BRKC', 'BRKX', 'CHEK','IIVR','REP') THEN 1 ELSE 0 END) AS EFFECTFLAG FROM TBL_PCCS_BIZ_ACT_TC WHERE CASE_ID = 120160823000081758 )GROUP BY LST_UPD_DATE ORDER BY LST_UPD_DATE DESC WITH ur-----------------------------------------------------------------------------------------------------SELECT LST_UPD_DATE, SUM(EFFECTFLAG) AS STATUSFROM ( SELECT DISTINCT LST_UPD_DATE,--对两个字段去重复 ( CASE WHEN MOVE_CODE IN ('XZDK','QXDK','XZZB', 'QXZB') THEN 1 ELSE 0 END) AS EFFECTFLAG FROM TBL_PCCS_BIZ_ACT_ACTION WHERE CASE_ID = 120160823000081756)GROUP BY LST_UPD_DATE ORDER BY LST_UPD_DATE DESC WITH ur -----------------------------------------------------------------------------------------------------values date('2015-12-03');values substr(char('2015-12-03'),9,2);-- 数据库下标是重1开始的 截取2位values substr(char('2015-02-30'),1,8);----------------------------------------------------------------------------------------------------- SELECT B.*FROM ( SELECT A.*, ( CASE WHEN STATEMENT_DATE_STR > ( SELECT SUBSTR(CHAR(BATCH_DATE),9,2) FROM TBL_PCCS_CONF_GL_GLOBAL ) THEN ( SELECT SUBSTR(CHAR(BATCH_DATE + 1 MONTH),1,8) || A.STATEMENT_DATE_STR FROM TBL_PCCS_CONF_GL_GLOBAL ) ELSE ( SELECT SUBSTR(CHAR(BATCH_DATE),1,8) || A.STATEMENT_DATE_STR FROM TBL_PCCS_CONF_GL_GLOBAL ) END) AS CYCLE_DAY FROM TBL_PCCS_BIZ_CASE_CASEMAIN A WHERE value(A.EVER_COLL_OPER_ID,'') <> '' AND CENTER_NO = '027' AND QUEUE_ID = 'T0101' AND CASE_AMT >= 0.0 AND CASE_ID =120160823000081758) BWHERE ( SELECT BATCH_DATE + 1 days FROM TBL_PCCS_CONF_GL_GLOBAL) < B.CYCLE_DAY WITH ur -----------------------------------------------------------------------------------------------------SELECT A.CITY AS AREA_CODE, A.ACT_ORG_ID, TO_CHAR(A.DEPUTE_DATE,'yyyymm') AS OA_DATE, ( CASE WHEN A.CURRENCY = '840' THEN A.RECOVERY_AMT*4141 ELSE A.RECOVERY_AMT END) AS ACHIEVE_AMTFROM TBL_PCCS_BT_OPT_OAPMT A-----------------------------------------------------------------------------------------------------DELETEFROM WBCL_USR.TBL_PCCSWB_TMP_BIZ_CUST_TEL CS WHERE CS.TEL_NO not IN ( SELECT MAX(G.TEL_NO) FROM WBCL_USR.TBL_PCCSWB_TMP_BIZ_CUST_TEL G GROUP BY G.CUST_NO,G.PHONE) ----------------------------------------------------------------------------------------------------- SELECT *FROM ( SELECT CUST_NO, CERT_NO, count num FROM TBL_PCCS_BIZ_CUST_CUSTOMER GROUP BY CUST_NO, CERT_NO ) as T where T.num>2 ------------------------------------------------------------------------------------------------------- 67897987 截取，从倒数第二位开始截取，截取两位 为87 一个参数就是从开始位置截取到最后--db2 下标是从1开始的select substr(cust_no,length(cust_no)-1,2) from TBL_PCCS_BIZ_TMP_HANDWORK_CREDITLselect substr(DEPUTE_DATE,3, 2)||substr(DEPUTE_DATE,6,2)||substr(DEPUTE_DATE,9,2) from TBL_PCCS_BIZ_CASE -- Right(CUST_NO,2) 获取倒数2位数 如0019719809655 结果 55 ; select cust_no,Right(CUST_NO,2),left(cust_no,2) from tbl_pccs_biz_case_casemain where case_id=120151021--从右边开始截取到7位select right(DEPUTE_DATE, 7) from TBL_PCCS_BIZ_CASE_OUTAGREE--从左边开始截取到7位select LEFT(DEPUTE_DATE, 7) from TBL_PCCS_BIZ_CASE_OUTAGREE------------------------------------------------------------------------------------------------------- to_char 将其他类型的时间转换为指定格式的日期时间select * from TBL_PCCS_BIZ_TMP_HANDWORK_CREDITL where to_char(current timestamp,'yyyy-MM-dd') ='2012-2-12'select DEPUTE_ORG_ID,OUTS_HAND_CNT,CUR_OA_CASE_AMT,TO_CHAR(A.DEPUTE_DATE,'yyyymm') AS oa_month from TBL_PCCS_BIZ_CASE_OUTAGREE_HIS A -- to_date 将字符串日期转化为指定格式的日期时间select * from TBL_PCCS_BIZ_TMP_HANDWORK_CREDITL where to_date('2012-02-12','yyyy-MM-dd') ='2012-2-12'INSERT INTO TBL_PCCS_CONF_OA_OUTCA (LST_UPD_DATE) values(to_date('1991-02-14','yyyy-MM-dd')); select * from TBL_PCCS_BIZ_CUST_TEL--trim 为去空 length 为长度SELECT length(trim(PHONE)) FROM TBL_PCCS_BIZ_CUST_TEL WHERE PHONE='12345678910'select * from TBL_PCCS_BT_DEDUCT_DETAIL where length(trim(DEBIT_CARD_NO)) <= 21 order by BRANCH_ORG asc-----------------------------------------------------------------------------------------------------SELECT T.*FROM dbcl_usr.TBL_PCCS_BIZ_APPR_OAORGCHG T WHERE T.APPR_TYPE= 'ORG'AND T.APPR_STATUS='PASS' AND T.STATUS='Y'AND T.appr_ser_no IN ( SELECT MAX(G.appr_ser_no) FROM dbcl_usr.TBL_PCCS_BIZ_APPR_OAORGCHG G WHERE LST_UPD_DATE = DATE('2015-12-31') GROUP BY G.case_id)AND NEW_COLL_ORG IS NOT NULLAND NEW_COLL_ORG <>'' select DIRE_COLL_CNT as 直催 ,PACKAGE_CNT as 打包 ,LAW_CNT as 司法,BEF_DEPUTE_CNT as 委前 from TBL_PCCS_BIZ_CASE_CASEMAINselect h.case_id,h.cust_id from TBL_BT_TMP_HOST h where IN_QUEUEID = 'T' and OUT_QUEUEID in ('O0100','O0100')UPDATE pccs_case_casemainSET PRE_DAY_FLAG = 'Y', UPDATE_DATE = '2012-1-12',UPDATE_TIME = CURRENT TIMESTAMP,UPDATE_USER = 'SYSTEM'WHERE QUEUEID IN( 'O0500', 'O0601') AND ( date('2012-2-12') +5 DAY) >= OA_CASE_DEADLINE;-- (current date + 5 day ) ------------------------------------------------------------------------------------------------------- ROW_NUMBER() OVER() AS ROW_NEXT 增加行号，从1开始select case_id,cust_no,cust_name,crt_time ,ROW_NUMBER() OVER () AS ROW_NEXT from TBL_PCCS_BIZ_CASE_CASEMAIN -- ROW_NUMBER() OVER (ORDER BY 字段column ) AS rownum 增加行号，按某列排序select row_number() OVER (ORDER BY LST_UPD_DATE DESC) AS ROW_NEXT,t.* from TBL_PCCS_BIZ_CASE_CASEMAIN t--DB2 分页select * from ( select ROW_NUMBER() OVER (ORDER BY LST_UPD_DATE DESC) AS ROWNUM, CASE_ID,CUST_NO,CENTER_NO,QUEUE_ID FROM TBL_PCCS_BIZ_CASE_CASEMAIN ) AWHERE ROWNUM >20 AND ROWNUM<=30----------------------------------------------------------------------------------------------------- --查看表结构select * from sysibm.columns where table_schema='DBCL_USR' and table_name='TBL_PCCS_BIZ_CASE_CASEMAIN';--修改变名字RENAME TABLE 表名 TO 新表名--增加子增长alter table mafenglei alter column OA_CASE_PROTL_DL_LOG_ID set generated by default as identity --增加一列alter table mafenglei add column address varchar(20)alter table DBCL_USR.TBL_PCCS_BIZ_CUST_SMISORG add column CRT_TIME TIMESTAMP not null default CURRENT TIMESTAMP;alter table DBCL_USR.TBL_PCCS_BIZ_CUST_SMISORG add column LST_UPD_DATE DATE not null default CURRENT DATE;alter table DBCL_USR.TBL_PCCS_BIZ_CUST_SMISORG add column LST_UPD_USER VARCHAR(32) not null default 'SYSTEM';alter table DBCL_USR.TBL_PCCS_BIZ_CUST_SMISORG add column SCR_LEVEL CHAR(2) not null default '00';--删除一列 alter table TBL_PCCS_BT_IMP_CSWF_PRE2 drop column lst_upd_date--修改表字段类型-- 其它数据库中alter table 表名 alter column update_user varchar(20) --da2 数据库中alter table table_name alter column column_nmae set data type type-- 如： alter table T alter column QUEUEID set data type varchar(50)--OR用法 用OR要注意 括号select * from TBL_PCCS_BIZ_CASE_CASEMAIN where QUEUE_ID ='O0704' and DEPUTE_ORG_ID is not null and (EVER_COLL_OPER_ID is null OR TEMP_COLL_OPER_ID is null )SELECT * FROM TBL_PCCS_CONF_GL_CASENOTES WHERE (SOURCE_QUEUE = 'O0300' OR SOURCE_QUEUE = '*') ------------------------------------------------------------------------------------------------------- move_code <> '’ 是等于空，等于NNULL的数据都包括在内select A.case_id from TBL_PCCS_CASE_GRADECODE A where A.move_code <> '' with ur--coalesce函数 如果OTH_LINKMAN_PH_LOSS_TY 没有值就返回3select coalesce(OTH_LINKMAN_PH_LOSS_TY,3,4,5) from TBL_PCCS_CUST_NOCONINFO-----------------------------------------------------------------------------------------------------SELECT A.CASE_ID, A.CUST_NO, A.CRD_HLR_PHON_IN_VALI_TP, A.CRD_HLR_C_TEL_IN_VALI_TP, A.CRD_HLR_H_TEL_IN_VALI_TP, A.OTH_LINKMAN_PH_LOSS_TY, B.QUEUE_ID,B.DELINQUENT_BUCKET, B. CASE_AMTFROM ( SELECT CASE_ID, CUST_NO, CRD_HLR_PHON_IN_VALI_TP, CRD_HLR_C_TEL_IN_VALI_TP, CRD_HLR_H_TEL_IN_VALI_TP, OTH_LINKMAN_PH_LOSS_TY FROM TBL_PCCS_CUST_NOCONINFO ) AS A, ( SELECT CASE_ID, CUST_NO, QUEUE_ID,DELINQUENT_BUCKET, CASE_AMT FROM TBL_PCCS_BIZ_CASE_CASEMAIN WHERE QUEUE_ID IN ('R0101', 'R0102')) BWHERE A.CASE_ID = B.CASE_ID AND A.CUST_NO = B.CUST_NO WITH ur------------------------------------------------------------------------------------------------------SELECT a.CUST_NO, a.PHONE , a.DIAL_TIME, b.CONF_CODE_NAME FROM TBL_PCCS_BT_IMP_DAILERCONSQ a,TBL_PCCS_CONF_AUTODIAL_DISPOSITION b WHERE a.DIAL_RESULT=INT(b.CONF_CODE) ORDER BY a.CUST_NO , a.DIAL_TIME-----------------------------------------------------------------------------------------------------INSERT INTO TBL_PCCS_BT_TMP_INFORCA ( CASE_ID,CENTER_NO, QUEUE_ID, CASE_AMT, DELINQUENT_BUCKET, LST_UPD_DATE )SELECT CASE_ID, CENTER_NO, QUEUE_ID, CASE_AMT, DELINQUENT_BUCKET, CURRENT DATE AS LST_UPD_DATEFROM TBL_PCCS_BIZ_CASE_CASEMAIN CWHERE VALUE(EVER_COLL_OPER_ID,'')= ''AND EXISTS ( SELECT 1 FROM TBL_PCCS_CONF_GL_CACOL A WHERE A.DIV_TYPE = 'MDFY' AND A.QUEUE_ID = C.QUEUE_ID AND A.CENTER_NO = C.CENTER_NO ) ----------------------------------------------------------------------------------------------------- insert into TBL_PCCS_BIZ_TMP_ASSIGEN_PLANS_WB (OA_ORG_ID,CASE_AREA ) (select OA_ORG_ID ,CASE_AREA from TBL_PCCS_BIZ_ASSIGEN_PLANS_WB) -----------------------------------------------------------------------------------------------------SELECT a.CASE_ID, a.NEW_COLL_ORG, a.COLL_WAY, a.ASSIGN_COLL_OPER_ID FROM TBL_PCCS_BIZ_APPR_OAORGCHG aINNER JOIN TBL_PCCS_BIZ_CASE_CASEMAIN b ON a.CASE_ID = b.CASE_ID AND b.QUEUE_ID = ''WHERE a.APPR_SER_NO IN ( SELECT MAX(APPR_SER_NO) APPR_SER_NO FROM TBL_PCCS_BIZ_APPR_OAORGCHG WHERE APPR_DATE = CURRENT DATE AND APPR_STATUS = 'PASS' GROUP BY CASE_ID ) -----------------------------------------------------------------------------------------------------SELECT * from TBL_PCCS_BIZ_CASE_CASEMAIN where create_time >= '2015-09-10 00:00:00'UPDATE PCCS_CUST_SMISSTAFF SET USERID = 'SCO'||'STAFF_ID' SELECT CASEID FROM PCCS_ACT_ACTION WHERE EXISTS( SELECT 1 FROM TEMP_HIS_PCCS_CASE_CASEMAIN A WHERE A.CASEID=PCCS_ACT_ACTION.CASEID)-----------------------------------------------------------------------------------------------------select A.case_id , A.rank_code, A.move_code,A.queueid from TBL_PCCS_CASE_GRADECODE A where A.id_pccs in(select max(id_pccs) from TBL_PCCS_CASE_GRADECODE group by case_id)------------------------------------------------------------------------------------------------------- AND VALUE(R.TEMPLATE_NO,'') R.TEMPLATE_NO如果有值就是值本身，如果没有值，就为'' SELECT bit.CUST_NO AS CUST_NO, MAX(bit.STRATEGY_NO) AS STRATEGY_NO, MAX(bit.TRIAD_LETTER_ID) AS TRIAD_LETTER_IDFROM TBL_PCCS_BT_IMP_TRIADSTR bitWHERE TRIAD_LIMIT_NO IS NOT NULLAND TRIAD_LIMIT_NO != ''AND EXISTS ( SELECT 1 FROM TBL_PCCS_CONF_RL_TRIADTEMPLATE R WHERE R.TRIAD_TYPE = 'MAIL' AND R.TMPLATE_TYPE = 'MAIL' AND R.TEMPLATE_NO='pppp' AND R.TRIAD_CODE = bit.TRIAD_LETTER_ID AND VALUE(R.TEMPLATE_NO,'') <> '' AND VALUE('ppppp','') <> '')AND EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_CASE_CASEMAIN pcc WHERE pcc.CUST_NO = bit.CUST_NO AND VALUE(pcc.QUEUE_ID,'') <> 'E0100' AND value(pcc.LETTER_STOP_COLL_FLG,'') <> 'Y' AND NOT EXISTS ( SELECT 1 FROM TBL_PCCS_CASE_CACACCT CACACCT WHERE pcc.case_id=CACACCT.case_id AND SUBSTR(bit.TRIAD_LETTER_ID,1,1)< 'pppp') )GROUP BY bit.CUST_NO---------------------------------------------------------------------------------------------------------------如果指定字段就插入指定字段的值，如果不指定就插入表中的所有字段--问题 根据连接条件 如果找到 BT_IMP_CSWF_PRE 表里存在两条数据 ，匹配 BT_IMP_CSWF_PRE2--表中的数据就无法匹配 就会报 -788 数据重复问题 ，所要要保证 BT_IMP_CSWF_PRE 不能有重复数据MERGEINTO BT_IMP_CSWF_PRE2 AS CS2 -- 默认插入这个表USING BT_IMP_CSWF_PRE AS CSON CS2.ACCTID = CS.ACCTIDWHEN MATCHED THENUPDATESET CS2.CHEAT_TYPE = CS.CHEAT_TYPE, CS2.BRANCH_BLAME = CS.BRANCH_BLAME, CS2.COLL_MODE = CS.COLL_MODE, CS2.CSWFID = CS.CSWFID, CS2.OA_NUM = CS.OA_NUMWHEN NOT MATCHED THENINSERT (CS2.ACCTID,CS2.CHEAT_TYPE,CS2.BRANCH_BLAME,CS2.COLL_MODE,CS2.CSWFID,CS2.OA_NUM,CARDID ) VALUES( CS.ACCTID, 'e3','name', 'CODE', 'er', null, NULL ) -----------------------------------------MERGEINTO TBL_PCCS_BIZ_CUST_ADDR AUSING ( SELECT * FROM TBL_PCCS_BT_IMP_ADDR WITH ur)BON ( A.REC_STATUS=B.REC_STATUS AND A.CUST_NO=B.CUST_NO AND A.SYS_ADDR_TYPE=B.ADDR_TYPE AND A.DATA_SOURCE = 'HOST' AND A.LETTER_POST_ADDR1=B.LETTER_POST_ADDR1 AND A.LETTER_POST_ADDR2=B.LETTER_POST_ADDR2 AND A.REC_STATUS='0' )WHEN MATCHED THENUPDATESET A.LETTER_POST_ADDR1=B.LETTER_POST_ADDR1, A.LETTER_POST_ADDR2=B.LETTER_POST_ADDR2, A.MAIN_CONTACT_ADDR3=B.MAIN_CONTACT_ADDR3, A.POST_CODE=B.POST_CODE, A.BACKLETTER_FLAG=NULL, A.CITY=B.CITY, A.LST_UPD_TIME= CURRENT TIMESTAMP, A.LST_UPD_USER='SYSTEM', A.LST_UPD_DATE = '2015-11-23'WHEN NOT MATCHED THENINSERT ( A.CUST_NO,A.SYS_ADDR_TYPE,A.ADDR_TYPE,A.POST_CODE, A.LETTER_POST_ADDR1,A.LETTER_POST_ADDR2, A.MAIN_CONTACT_ADDR3,A.NAME, A.REL_WITH_CUST,A.CITY,A.DATA_SOURCE,A.BACKLETTER_FLAG, A.VISIT_SUM_CODE, A.CRT_TIME,A.CRT_USER, A.LST_UPD_TIME,A.LST_UPD_USER,A.REC_STATUS, A.SCR_LEVEL,A.LST_UPD_DATE )VALUES ( B.CUST_NO, B.ADDR_TYPE, B.ADDR_TYPE, B.POST_CODE, B.LETTER_POST_ADDR1, B.LETTER_POST_ADDR2, B.MAIN_CONTACT_ADDR3, B.NAME, B.REL_WITH_CUST, B.CITY, 'HOST', '', NULL, CURRENT TIMESTAMP, 'SYSTEM', CURRENT TIMESTAMP, 'SYSTEM', '0', '00','2015-2-12' ); -------------------------------------------------------------------------------------------------------------select HOUR(CURRENT TIMESTAMP),MINUTE(CURRENT TIMESTAMP) from (VALUES 2) AS TEMPLETE-------------------------------------------------------------------------------------------------------------select * from TBL_PCCS_TEMP_TABLE--删除视图drop view TBL_PCCS_TEMP_TABLE--创建视图CREATE VIEW TBL_PCCS_TEMP_TABLE( CASE_ID,CUST_NO,CUST_NAME,QUEUE_ID,CENTER_NO,BRANCH_ORG,CASE_AMT,CRT_TIME,ACCT_NO) AS SELECT T.CASE_ID,T.CUST_NO,T.CUST_NAME,T.QUEUE_ID,T.CENTER_NO,T.BRANCH_ORG,T.CASE_AMT,T.CRT_TIME,A.ACCT_NO FROM TBL_PCCS_BIZ_CASE_CASEMAIN TLEFT JOIN TBL_PCCS_BIZ_CASE_ACCT A ON T.CASE_ID = A.CASE_IDWHERE A.ACCT_NO <>''---------------------------------------------------------------------------------------------------------------创建索引CREATE INDEX searc ON mafenglei(age) CREATE INDEX 索引名 ON 表名(指定的列)--删除索引DROP INDEX searc --创建序列 CREATE SEQUENCE MAFENGLEI_SQL--查询下一个序列VALUES(MAFENGLEI_SQL.nextval)--删除序列DROP SEQUENCE MAFENGLEI_SQL--使用序列插入数据INSERT INTO mafenglei(id_pccs,name,age,saralery,monery,birthday)values(MAFENGLEI_SQL.nextval,'小马',25,4500.30,5000,'2015-12-30')##*****************以下都是MYSQL中的操作*********************************************************************##*****************MySQL_数据分页查询（limit用法）***********************************************************##当前股票前26天的历史行情数据SELECT h.tick,h.dt,h.OPEN,h.high,h.low,h.CLOSE,h.vol,h.amount,h.t_rateFROM hq_price hLEFT JOIN hq_stock_tp tON h.tick =t.tick AND h.dt=t.dtWHERE h.tick='002075' AND t.dt IS NULL AND h.dt <='2015-02-27'ORDER BY dt DESC LIMIT 26##取前5条数据SELECT * FROM hq_stock_tp LIMIT 0,5 ## 或 SELECT * FROM hq_stock_tp LIMIT 5 ##取第11条到第15条数据，共5条SELECT * FROM hq_stock_tp LIMIT 10,5 ##取出2540后面的20条数据 ,就是一页20条 当前是2540/20= 127 页##可以让页面一页显示50条 ，查找5页的时候就是 50*5 = 250，就是 limit 250,50select * from kam.topic_hp_news limit 2540,20-------------------------------------------------------------------------------------------------------------select * from kam.event_timeline where abbr like concat("%",'中',"%")##将字符串转化为数字再排序SELECT * FROM topic_hp_show WHERE `date`='20180322' ORDER BY CONVERT(idx,SIGNED) ASC##***********************************************************************************************************CREATE TABLE `equity_bonus_jc` ( `id` bigint(10) UNSIGNED NOT NULL AUTO_INCREMENT, ##unsigned表示无负号的意思，也就是非负数，只用于整型 `secu` VARCHAR(20) NOT NULL COMMENT '' , `exrdt` DATE NOT NULL COMMENT '除权除思日', `givsr` DECIMAL(15,6) DEFAULT NULL COMMENT '运转比列', `bns` DECIMAL(15,6) DEFAULT NULL COMMENT '派息比例', `amou` DECIMAL(15,6) DEFAULT NULL COMMENT '', `aft_bns` DECIMAL(15,6) DEFAULT NULL COMMENT '', `sid` VARCHAR(20) DEFAULT NULL COMMENT '', `y` VARCHAR(10) DEFAULT NULL COMMENT '', `givsr_stock` DECIMAL(15,6) DEFAULT NULL COMMENT '送股比例', `givsr_transf`DECIMAL(15,6) DEFAULT NULL COMMENT '转增比例', `tpsj` INT(11) NOT NULL COMMENT '', `upt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `date` char(10) comment '交易日期', PRIMARY KEY (`id`), UNIQUE KEY `dt_secu` (`exrdt`,`secu`), KEY `secu_dt` (`secu`,`exrdt`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='停牌信息记录表'-------------------------------------------------------------------------------------------------------------CREATE TABLE `topic_hp_news` ( `id` int(20) unsigned NOT NULL AUTO_INCREMENT, `objectid` varchar(50) DEFAULT NULL, `url` varchar(200) DEFAULT NULL, `sum` text, `t` varchar(400) DEFAULT NULL, `key` varchar(20) DEFAULT NULL, `dt` varchar(30) DEFAULT NULL, `com` text, `update_time` datetime DEFAULT NULL, `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=112440 DEFAULT CHARSET=utf8CREATE TABLE `corpus_param` ( `id` int(11) NOT NULL AUTO_INCREMENT, `corpus_id` int(11) DEFAULT NULL COMMENT '语料外键id', `name` varchar(100) DEFAULT NULL COMMENT '参数名称', `entity_type` varchar(100) DEFAULT NULL COMMENT '实体类型', `value` varchar(100) DEFAULT NULL COMMENT '参数值', PRIMARY KEY (`id`), KEY `curpos_id_index` (`corpus_id`), CONSTRAINT `FK_Reference_13` FOREIGN KEY (`corpus_id`) REFERENCES `corpus` (`id`) ON DELETE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='用户提问参数'-------------------------------------------------------------------------------------------------------------##删除一列ALTER TABLE TBL_PCCS_BT_IMP_CSWF_PRE2 DROP COLUMN lst_upd_date##修改表中字段名称 ALTER TABLE 表名 CHANGE 现有字段名称 修改后字段名称 数据类型##将表nlp_dict_event中，列名update_time改为uptALTER TABLE nlp_dict_event CHANGE update_time upt TIMESTAMP;ALTER TABLE equity_bonus_jc CHANGE givsr1 givsr_stock DECIMAL(15,6) DEFAULT NULL## 增加字段alter table nlp_dict_event add upu VARCHAR(300)##修改字段类型alter table nlp_dict_product modify state Int;##删除数据delete from kam.`topic_hp_news` WHERE id IN (112442,112440) ##查看表结构SHOW CREATE TABLE kam.topic_hotDESC kam.topic_hot-------------------------------------------------------------------------------------------------------------##用Linux登录远程myql数据库## ps aux|grep mysql##进入mysql主目录 输入以下命令 ---> mysql -u 用户名 -p -> 输入密码就行了## 查看当前有哪些数据库SHOW DATABASES;## 使用当前数据库(ced)USE ced; ## 查看当前有哪些表SHOW TABLES;-------------------------------------------------------------------------------------------------------------##CREATE TABLE语句可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引##ALTER TABLE用来创建普通索引、唯一索引、主键索引。ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE table_name ADD UNIQUE (column_list)ALTER TABLE table_name ADD PRIMARY KEY (column_list)##ALTER TABLE可以在同时创建多个索引,多列时各列之间用逗号分隔。##CREATE INDEX可对表增加普通索引或UNIQUE索引。CREATE INDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)##具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。##删除索引##可利用ALTER TABLE或DROP INDEX语句来删除索引DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY## 查看表创建的索引 SHOW INDEX FROM kam.hq_stock_tp##删除索引ALTER TABLE 表名 DROP FOREIGN KEY 外键（区分大小写）;ALTER TABLE corpus_user_say_annoted DROP FOREIGN KEY corpus_user_say_annoted_ibfk_1##添加索引ALTER TABLE corpus_user_say_annoted ADD FOREIGN KEY (corpus_usid) REFERENCES corpus_user_say (id); -------------------------------------------------------------------------------------------------------------## 创建临时表create table nlp_dict_industry_temp as select * from nlp_dict_industry where id in (SELECT MAX(id) id FROM nlp_dict_industry GROUP BY NAME)## 插入临时表数据insert into nlp_dict_industry select * from nlp_dict_industry_temp-------------------------------------------------------------------------------------------------------------CREATE TABLE `hq_stock_tp` ( `id` INT(10) PRIMARY KEY AUTO_INCREMENT, `dt` DATE NOT NULL COMMENT '日期', `tick` VARCHAR(20) NOT NULL COMMENT '股票code', `tpsj` INT(11) NOT NULL COMMENT '停牌天数', `upt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `tick` (`dt`,`tick`) USING BTREE, UNIQUE KEY `tick_dt` (`tick`,`dt`), KEY `dt` (`dt`) USING BTREE) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='停牌信息记录表'## 主键不存在则insert 否 update## ON DUPLICATE KEY UPDATE INSERT INTO hq_stock_tp (dt, tick, tpsj) VALUES('2016-11-16','006398',10) ON DUPLICATE KEY UPDATE tpsj=VALUES(tpsj), dt ='2016-11-19'INSERT INTO `hq_stock_tp`(`id`,`dt`,`tick`,`tpsj`,`upt`) VALUES (94491,'1991-02-14','000001',1,'2015-09-07 10:43:01'),(94492,'1991-02-19','000001',2,'2015-09-07 10:43:01'),(94500,'1991-03-20','000001',6,'2015-09-07 10:43:01'),(94501,'1991-03-21','000001',7,'2015-09-07 10:43:01'),(94502,'1991-03-22','000001',8,'2015-09-07 10:43:01'),(94503,'1991-03-25','000001',9,'2015-09-07 10:43:01'),(94504,'1991-03-26','000001',10,'2015-09-07 10:43:01');##*****************一下都是ORACLE中的操作*********************************************************************--oracle 中的分页SELECT T.* FROM ( SELECT B.ob_seccode_0007, A.f004n_0093, A.f005n_0093, A.f012d_0093, A.ob_object_id, ROWNUM RN FROM tb_company_0093 A JOIN tb_public_0007 B ON A.ob_orgid_0093=B.ob_secid_0007 WHERE B.f003v_0007='A股' AND A.f002V_0093='A股' ) T WHERE T.RN BETWEEN 10 AND 30select t.* from ( select f005n_0093 ,ob_object_id,rownum as rn from tb_company_0093) Twhere T.rn >20 and T.rn<30 -------------------------------------------------------------------------------------------------------------select t.* from ( select textid, f002v as title, rectime as pdt, f003v as jcurl, f004v as jcext, f006v as types, f001d as pub, rectime, modtime, rownum as rn from info3015 where textid in ( '1202075322' )and ( rectime>=to_date('2016-03-20','yyyy-mm-dd') and rectime<=to_date('2016-04-02','yyyy-mm-dd') or modtime>=to_date('2016-03-20','yyyy-mm-dd')and modtime<=to_date('2016-04-02','yyyy-mm-dd') and textid='1202075322' ) order by textid ) twhere t.rn>0 and t.rn<200 --------------------------------------------------------------------------------------------------------------- to_timestamp 可以指定更加具体的时间 如 2010-12-18 14:14:15.00005select * from nlp_news_concept where upt between to_timestamp('2017-05-08 23:59:59.999','yyyy-mm-dd hh24:mi:ss.ff')and to_timestamp('2017-11-11 00:00:00.0','yyyy-mm-dd hh24:mi:ss.ff')-- to_date 可以指定时分秒 如 2010-12-18 14:14:15select * from nlp_news_concept where upt between to_date('2017-05-08 23:59:59','yyyy-mm-dd hh24:mi:ss')and to_date('2017-11-11 00:00:00','yyyy-mm-dd hh24:mi:ss')select to_date('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')-sysdate from dual;select to_timestamp('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')-systimestamp from dual;---------------------------------------------------------------------------------------------------------------增加一列 字段为statalter table orcl_train_model add stat number(4)-- 修改字段长度alter table 表名 modify 字段名 字段类型(字段长度);-- 修改字段名称alter table 表名 rename column 旧字段 to 新字段-- 删除字段alter table 表名 drop column 字段;--修改表名rename 原表名 to 新表名rename nlp_dict_entity_xiaoma to nlp_test--把一张表的数据导入到另一张表insert into nlp_dict_entity SELECT * FROM NLP_DICT_ENTITY_temp--添加主键约束alter table nlp_dict_entity add constraint pk_nlp_dict_entity primary key (id)--添加唯一性约束alter table nlp_dict_entity add constraint constraint_id unique(id)-- 删除约束关系alter table nlp_dict_entity drop constraint constraint_id---------------------------------------------------------------------------------------------------------------创建中国地图表drop table china_addresscreate table china_address( id number(4) not null primary key,--主键 此时还不是自增的 address_id number(4), name varchar2(30) not null, price number(7,2),--小数位 status char(1), creatime date default sysdate not null, upt timestamp default current_timestamp);--创建中国地图序列 来实现主键自增加drop sequence china_address_sequcreate sequence china_address_sequ start with 1 increment by 1;--查询下一个序列select china_address_sequ.nextval from dualselect * from china_address--插入带自增主键的sql 前提创建序列； 使用china_address_sequ.nextval来实现insert into china_address(id,address_id,name,price,status,creatime) values(china_address_sequ.nextval,8,'长江三角洲',2.312,'1',sysdate)-------------------------------------------------------------------------如果在插入语句中不调用序列来获取递增主键的话 可以用触发器来实现drop trigger china_address_increase_tr--创建中国地图主键自增长触发器create or replace trigger china_address_increase_tr --触发器名称 china_address_increase_trbefore insert on china_address -- 表的名字 china_addressfor each row -- 固定语句begin select china_address_sequ.nextval into:new.id from dual; --触发器内容 new.id这里的id 就是表中的id字段为自增长end;--插入 实际是用触发器来实现主键自增加insert into china_address(address_id,name,price,status,creatime) values(8,'长江三角洲',2.312,'1',sysdate)---------------------------------------------------------------------------------------------------------------重命名rename nlp_dict_company to nlp_dict_company_temp--把一张表的数据导入到另一张表insert into nlp_dict_company SELECT * FROM nlp_dict_company_tempdrop table nlp_dict_company; --创建表create table nlp_dict_company ( id number(20), code varchar2(32), abbr varchar2(128), upt timestamp, primary key (id) ); drop sequence seq_news_company; --创建自增序列create sequence seq_news_company start with 1 increment by 1; drop trigger trgr_news_company; --创建触发器 实现主键自增 create or replace trigger trgr_dict_companybefore insert on nlp_dict_companyfor each rowbegin:new.id := seq_news_company.nextval;end;-------------------------------------------------------------------------------------------------------------select ob_bondid_0067,count(*) from tb_bond_0067 group by ob_bondid_0067 order by 2 desc select to_char(OB_RECTIME_0067, 'yyyy') , count(1) from tb_bond_0067group by to_char(OB_RECTIME_0067, 'yyyy') -------------------------------------------------------------------------------------------------------------select f007d_0067 as f007d0067, f001v_0067 as f001v0067, f006v_0067 as f006v0067, f002v_0067 as f002v0067, f003v_0067 as f003v0067, f004v_0067 as f004v0067, f005v_0067 as f005v0067from ( select rownum as rn, a.* from tb_bond_0067 a where to_char(a.ob_rectime_0067,'yyyy-mm-dd') between '2009-08-05' and '2016-08-05' order by a.ob_object_id ) bwhere b.rn > 3and b.rn <= 200 order by rn------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------1.创建索引CREATE INDEX 索引名 ON 表名 (列名) create index member_search on member (memberMail)--2.创建唯一索引:CREATE unique INDEX 索引名 ON 表名 (列名) create unique index member_name on member (memberName) --删除索引drop index member_search;-- 注：当表结构被删除时，有其相关的所有索引也随之被删除。--1. 根据索引名，查询被索引的字段:select * from user_ind_columns where index_name=upper('member_search'); -- member_search 为索引名称--根据表名，查询一张表的所有索引信息select * from user_indexes where table_name=upper('member') -- member 为表名--查询表的所有列及其属性select * from user_tab_columns where table_name=upper('member'); --member 为表名--------------------------------------------------------------------------------------------------------------- 修改序列的名称--alter sequence 旧序列名称 rename to 新序列名称;alter sequence seq_table rename to seq_table_test;-- 修改索引名称--alter index 旧索引名称 rename to 新索引名称;alter index index_base_stock_tick rename to indcsf_base_stock_tick;---------------------------------------------------------------------------------------------------------------获取 表tb_bond_0067的建表语句select dbms_metadata.get_ddl('TABLE','TB_BOND_0067') from dual;//这里表名要大写/*查询锁表*/select s.username, decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL, o.owner, o.object_name, o.object_type, s.sid, s.serial#from gv$session s, gv$lock l, dba_objects owhere l.sid = s.sid and l.id1 = o.object_id(+) and s.username is not null;/*USERNAME LOCK_LEVEL OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#ZHAOSHANG (null) SYS ORA$BASE EDITION 77 2036SYS ROW LOCK (null) (null) (null) 195 25941ZHAOSHANG TABLE LOCK ZHAOSHANG FIN_SAM_PRODUCT_ITEM_VO TABLE 205 60915备注 ： 此时 FIN_SAM_PRODUCT_ITEM_VO 表已经被招商这个用户 锁定了*/-- 杀掉session 释放被锁住的这张表alter system kill session '205,60915';-- 查看session 和session总数select count(*) from v$sessionselect * from v$session--查看不同用户的连接数select username,count(username) from v$session where username is not null group by username##*****************以下都是sql server中的操作*********************************************************************CREATE TABLE BLOCK_MEMBER ( ID INT NOT NULL, BLOCKTYPE TINYINT, BLOCKCODE NVARCHAR(50 ) COLLATE Chinese_PRC_CI_AS, STOCKCODE NVARCHAR(50 ) COLLATE Chinese_PRC_CI_AS, USESTATUS TINYINT, BEGINDATE INT, ENDDATE INT, TMSTAMP BIGINT, ENTRYDATE DATETIME, CHKTMS BIGINT, CONSTRAINT PK_BLOCK_MEMBER PRIMARY KEY (ID) );---------------------------------------------------------------------------------------------------------------sql server 中的分页select pre_rpt_id as prerptid, reliability as reliability, entrydate as entrydate, entrytime as entrytime, tmstamp as tmstamp, cmb_rpt_id as cmb_rpt_id from ( select *,row_number() over(order by pre_rpt_id asc) as rt from t_pre_reliability where entrydate > '1900-01-01' and entrydate <= '2016-07-27' ) b where b.rt > 10 and b.rt <= 200 ----------------------------------------------- --分页select * from ( select *,row_number() over(order by PRE_RPT_ID asc) as rt from ( select PRE_RPT_ID from t_pre_reliability where left(convert(varchar(24),entrydate,112),8) like '20120214%' ) as b) as a where a.rt>0 and a.rt<= 1000 ------------------------------------------------------------------------------------------------------------- select id as id, blocktype as blocktype , blockcode as blockcode , stockcode as stockcode , usestatus as usestatus, begindate as begindate, enddate as enddate, tmstamp as tmstamp, entrydate as entrydate, chktms as chktms from ( select *,row_number() over(order by id asc) as rt from block_member where entrydate > #{fromDate} and entrydate <= #{toDate} ) b where b.rt > #{startRow} and b.rt <= #{endRow} select count(*) from con_forecast_idx where entrydate > '2015-07-27' and entrydate >= '2015-07-27' --------------------------------------------------------------------------------------------------------------- 112 yyyyMMdd-- 120 yyyy-MM-dd hh:mm:ss-- 将 entrydate字段 从Timestamp 类型 的转为string类型的select convert(varchar(24),entrydate,120) from t_pre_reliability--将时间进行转化，然后模糊查询select count(*) from t_pre_reliability where left(convert(varchar(24),entrydate,112),8) like '20170109%'select top 3 * from t_pre_reliability