sql语句大全（db2 oracle mysql sql server）

-- 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 = 11021000061668AND T.crt_time LIKE '-10-20%'AND EXISTS(SELECT 1 FROM TBL_PCCS_BIZ_CUST_TEL AS EWHERE 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 aWHERE 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('-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('-08-30') - (2) DAY) AND QUEUE_ID ='R0204' AND CASE_ID= 1082800AND 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<SUBSTR(T.CRT_TIME,1,10) AND SUBSTR(T.CRT_TIME,1,10)<O.REAL_BACK_CASE_DATEAND O.CUST_NO<>'' -----------------------------------------------------------------------------------------------------SELECTcaseId.CASE_IDFROM(SELECT caseMain.CASE_IDFROM TBL_PCCS_BIZ_CASE_CASEMAIN AS caseMainWHEREEXISTS(SELECT1FROMTBL_PCCS_BIZ_PREVIOUS_STOP_COLL AS proStopCollWHEREproStopColl.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 TABLETBL_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 TIMESTAMPDEFAULT CURRENT TIMESTAMP NOT NULL ,LST_UPD_TIME TIMESTAMPNOT NULL DEFAULT CURRENT TIMESTAMP,LST_UPD_DATE DATENOT NULL DEFAULT CURRENT DATE,LST_UPD_USER VARCHAR(32) NOT NULL DEFAULT 'SYSTEM',REC_STATUS CHARACTER(1) DEFAULT '0' NOT NULL,SCR_LEVELCHARACTER(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','-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_NAMEFROMBT_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)-----------------------------------------------------------------------------------------------------SELECTc.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 = 10818000000611 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 TWHERE a.CASE_ID=T.CASE_ID AND T.check_date IS NOT NULL ORDER BY T.LST_UPD_TIMEFETCH 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_IDFROMTBL_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_QUEUEDEFWHEREQUEUE_ID = M.LAST_QUEUE_NO AND TEAM_ID IN('TELE', 'MDFY'))GROUP BYM.CASE_ID, M.QUEUE_ID, M.MIN_PAY_BAL_OF_FS_OA_BUHAVINGSUM(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('-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天不通 SELECTLST_UPD_DATE, SUM(EFFECTFLAG) AS STATUSFROM( SELECT DISTINCT LST_UPD_DATE,( CASEWHEN 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 0END) AS EFFECTFLAGFROM TBL_PCCS_BIZ_ACT_TC WHERE CASE_ID = 10823000081758 )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,--对两个字段去重复 (CASEWHEN MOVE_CODE IN ('XZDK','QXDK','XZZB', 'QXZB')THEN 1ELSE 0END) AS EFFECTFLAGFROM TBL_PCCS_BIZ_ACT_ACTIONWHERE CASE_ID = 10823000081756)GROUP BY LST_UPD_DATE ORDER BY LST_UPD_DATE DESC WITH ur-----------------------------------------------------------------------------------------------------values date('-12-03');values substr(char('-12-03'),9,2);-- 数据库下标是重1开始的 截取2位values substr(char('-02-30'),1,8);----------------------------------------------------------------------------------------------------- SELECT B.*FROM( SELECT A.*,( CASEWHEN 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_STRFROM TBL_PCCS_CONF_GL_GLOBAL )ELSE( SELECT SUBSTR(CHAR(BATCH_DATE),1,8) || A.STATEMENT_DATE_STRFROM TBL_PCCS_CONF_GL_GLOBAL )END) AS CYCLE_DAYFROM TBL_PCCS_BIZ_CASE_CASEMAIN AWHERE value(A.EVER_COLL_OPER_ID,'') <> ''AND CENTER_NO = '027' AND QUEUE_ID = 'T0101' AND CASE_AMT >= 0.0 AND CASE_ID =10823000081758) BWHERE ( SELECT BATCH_DATE + 1 days FROM TBL_PCCS_CONF_GL_GLOBAL) < B.CYCLE_DAY WITH ur-----------------------------------------------------------------------------------------------------SELECTA.CITY AS AREA_CODE,A.ACT_ORG_ID,TO_CHAR(A.DEPUTE_DATE,'yyyymm') AS OA_DATE,( CASEWHEN A.CURRENCY = '840'THEN A.RECOVERY_AMT*4141ELSE A.RECOVERY_AMTEND) AS ACHIEVE_AMTFROM TBL_PCCS_BT_OPT_OAPMT A-----------------------------------------------------------------------------------------------------DELETEFROMWBCL_USR.TBL_PCCSWB_TMP_BIZ_CUST_TEL CS WHERECS.TEL_NO not IN(SELECT MAX(G.TEL_NO)FROM WBCL_USR.TBL_PCCSWB_TMP_BIZ_CUST_TEL GGROUP BYG.CUST_NO,G.PHONE)----------------------------------------------------------------------------------------------------- SELECT *FROM(SELECT CUST_NO, CERT_NO, count numFROM 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=11021--从右边开始截取到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') ='-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('-02-12','yyyy-MM-dd') ='-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 GWHERE LST_UPD_DATE = DATE('-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 = '-1-12',UPDATE_TIME = CURRENT TIMESTAMP,UPDATE_USER = 'SYSTEM'WHERE QUEUEID IN( 'O0500', 'O0601') AND ( date('-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 DATEnot 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_LEVELCHAR(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-----------------------------------------------------------------------------------------------------SELECTA.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(SELECTCASE_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_TYFROM TBL_PCCS_CUST_NOCONINFO ) AS A,(SELECTCASE_ID, CUST_NO, QUEUE_ID,DELINQUENT_BUCKET, CASE_AMT FROM TBL_PCCS_BIZ_CASE_CASEMAINWHERE 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 )SELECTCASE_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 AWHEREA.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_NOFROM TBL_PCCS_BIZ_APPR_OAORGCHGWHERE APPR_DATE = CURRENT DATE AND APPR_STATUS = 'PASS' GROUP BY CASE_ID) -----------------------------------------------------------------------------------------------------SELECT * from TBL_PCCS_BIZ_CASE_CASEMAIN where create_time >= '-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如果有值就是值本身，如果没有值，就为'' SELECTbit.CUST_NO AS CUST_NO,MAX(bit.STRATEGY_NO)AS STRATEGY_NO,MAX(bit.TRIAD_LETTER_ID) AS TRIAD_LETTER_IDFROMTBL_PCCS_BT_IMP_TRIADSTR bitWHERETRIAD_LIMIT_NO IS NOT NULLAND TRIAD_LIMIT_NO != ''AND EXISTS(SELECT1FROMTBL_PCCS_CONF_RL_TRIADTEMPLATE RWHERER.TRIAD_TYPE = 'MAIL'AND R.TMPLATE_TYPE = 'MAIL'AND R.TEMPLATE_NO='pppp'AND R.TRIAD_CODE = bit.TRIAD_LETTER_IDAND VALUE(R.TEMPLATE_NO,'') <> ''AND VALUE('ppppp','') <> '')AND EXISTS(SELECT1FROMTBL_PCCS_BIZ_CASE_CASEMAIN pccWHEREpcc.CUST_NO = bit.CUST_NOAND VALUE(pcc.QUEUE_ID,'') <> 'E0100'AND value(pcc.LETTER_STOP_COLL_FLG,'') <> 'Y'AND NOT EXISTS(SELECT1FROMTBL_PCCS_CASE_CACACCT CACACCTWHEREpcc.case_id=CACACCT.case_idAND SUBSTR(bit.TRIAD_LETTER_ID,1,1)< 'pppp') )GROUP BYbit.CUST_NO---------------------------------------------------------------------------------------------------------------如果指定字段就插入指定字段的值，如果不指定就插入表中的所有字段--问题 根据连接条件 如果找到 BT_IMP_CSWF_PRE 表里存在两条数据 ，匹配 BT_IMP_CSWF_PRE2--表中的数据就无法匹配 就会报 -788 数据重复问题 ，所要要保证 BT_IMP_CSWF_PRE 不能有重复数据MERGEINTOBT_IMP_CSWF_PRE2 AS CS2 -- 默认插入这个表USINGBT_IMP_CSWF_PRE AS CSONCS2.ACCTID = CS.ACCTIDWHEN MATCHEDTHENUPDATESETCS2.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 MATCHEDTHENINSERT (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 )-----------------------------------------MERGEINTOTBL_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_NOAND 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_ADDR2AND A.REC_STATUS='0')WHEN MATCHEDTHENUPDATESETA.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 = '-11-23'WHEN NOT MATCHEDTHENINSERT(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','-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,'-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 <='-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`='0322' ORDER BY CONVERT(idx,SIGNED) ASC##***********************************************************************************************************CREATE TABLE `equity_bonus_jc` (`id`bigint(10) UNSIGNED NOT NULL AUTO_INCREMENT, ##unsigned表示无负号的意思，也就是非负数，只用于整型`secu` VARCHAR(20)NOT NULLCOMMENT '' ,`exrdt` DATE NOT NULLCOMMENT '除权除思日',`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 NULLCOMMENT '',`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('-11-16','006398',10) ON DUPLICATE KEY UPDATE tpsj=VALUES(tpsj), dt ='-11-19'INSERT INTO `hq_stock_tp`(`id`,`dt`,`tick`,`tpsj`,`upt`) VALUES (94491,'1991-02-14','000001',1,'-09-07 10:43:01'),(94492,'1991-02-19','000001',2,'-09-07 10:43:01'),(94500,'1991-03-20','000001',6,'-09-07 10:43:01'),(94501,'1991-03-21','000001',7,'-09-07 10:43:01'),(94502,'1991-03-22','000001',8,'-09-07 10:43:01'),(94503,'1991-03-25','000001',9,'-09-07 10:43:01'),(94504,'1991-03-26','000001',10,'-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 ( '175322' )and ( rectime>=to_date('-03-20','yyyy-mm-dd') and rectime<=to_date('-04-02','yyyy-mm-dd') or modtime>=to_date('-03-20','yyyy-mm-dd')and modtime<=to_date('-04-02','yyyy-mm-dd') and textid='175322' ) order by textid ) twhere t.rn>0 and t.rn<200 --------------------------------------------------------------------------------------------------------------- to_timestamp 可以指定更加具体的时间 如 -12-18 14:14:15.00005select * from nlp_news_concept where upt between to_timestamp('-05-08 23:59:59.999','yyyy-mm-dd hh24:mi:ss.ff')and to_timestamp('-11-11 00:00:00.0','yyyy-mm-dd hh24:mi:ss.ff')-- to_date 可以指定时分秒 如 -12-18 14:14:15select * from nlp_news_concept where upt between to_date('-05-08 23:59:59','yyyy-mm-dd hh24:mi:ss')and to_date('-11-11 00:00:00','yyyy-mm-dd hh24:mi:ss')select to_date('-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')-sysdate from dual;select to_timestamp('-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),namevarchar2(30) not null,price number(7,2),--小数位status char(1), creatimedate 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 tablenlp_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_0067as f007d0067,f001v_0067as f001v0067,f006v_0067as f006v0067,f002v_0067as f002v0067,f003v_0067as f003v0067,f004v_0067as f004v0067,f005v_0067as f005v0067from (select rownum as rn, a.* from tb_bond_0067 a where to_char(a.ob_rectime_0067,'yyyy-mm-dd') between '-08-05' and '-08-05' order by a.ob_object_id) bwhere b.rn > 3and b.rn <= 200 order by rn-------------------------------------------------------------------------------------------------------------<select id="fetchJuchaoData" resultType="com.csf.ada.datashift.refactor.entity.juchao.bond.TbBond0067"><![CDATA[select ob_subid_0067 as obsubid0067, ob_bondid_0067as obbondid0067,ob_bondname_0067 as obbondname0067,ob_isvalid_0067as obisvalid0067,ob_object_id as obObjectIdfrom( select rownum as rn, a.* from tb_bond_0067 a where a.ob_rectime_0067 >= #{fromDate} and a.ob_rectime_0067 < #{toDate}order by a.ob_object_id) bwhere b.rn > #{startRow} and b.rn <= #{endRow} order by rn]]></select>---------------------------------------------------------------------------------------------------------------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;/*USERNAMELOCK_LEVELOWNEROBJECT_NAMEOBJECT_TYPE SID SERIAL#ZHAOSHANG (null) SYS ORA$BASE EDITION772036SYSROW LOCK (null) (null)(null) 195 25941ZHAOSHANG TABLE LOCKZHAOSHANGFIN_SAM_PRODUCT_ITEM_VO TABLE 20560915备注 ： 此时 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 TABLEBLOCK_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,ENDDATEINT,TMSTAMPBIGINT,ENTRYDATE DATETIME,CHKTMS BIGINT,CONSTRAINT PK_BLOCK_MEMBER PRIMARY KEY (ID));---------------------------------------------------------------------------------------------------------------sql server 中的分页selectpre_rpt_idas prerptid,reliability as reliability,entrydate as entrydate,entrytime as entrytime,tmstamp as tmstamp,cmb_rpt_idas cmb_rpt_idfrom (select *,row_number() over(order by pre_rpt_id asc) as rt from t_pre_reliabilitywhere entrydate > '1900-01-01' and entrydate <= '-07-27') bwhere 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 '0214%') 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 chktmsfrom (select *,row_number() over(order by id asc) as rt from block_memberwhere entrydate > #{fromDate} and entrydate <= #{toDate}) bwhere b.rt > #{startRow} and b.rt <= #{endRow} select count(*) from con_forecast_idx where entrydate > '-07-27' and entrydate >= '-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 '0109%'select top 3 * from t_pre_reliability