-- 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 = 120151021000061668
AND 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 m
LEFT JOIN
( SELECT DISTINCT case_id,acct_no FROM tbl_pccs_biz_case_acct WHERE stis_flag !='N' ) AS a
ON m.case_id = a.case_id WHERE value(m.queue_id,'')<>'E0100'
-----------------------------------------------------------------------------------------------------
SELECT * FROM tbl_pccs_biz_case_casemain
WHERE 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_NAME
FROM TBL_PCCS_BIZ_CASE_ASTCHK AS CHK
LEFT OUTER JOIN TBL_PCCS_BNDICT_T_DICTIONARY AS DIC
ON CHK.CUST_COMPANY = DIC.BUSIN_ID
WHERE 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_CASEMAIN
WHERE IN_QUEUE_TIME <=(DATE('2020-08-30') - (2) DAY) AND QUEUE_ID ='R0204' AND CASE_ID= 12016082800
AND 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_DATE
AND O.CUST_NO<>''
-----------------------------------------------------------------------------------------------------
SELECT
caseId.CASE_ID
FROM
(
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 TABLE
DELETE FROM TBL_PCCS_BIZ_TMP_HANDWORK_CREDITL
DROP TABLE TBL_PCCS_FP_DEDUCT_DETAIL_BATCH
CREATE 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.CASEID
LEFT 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_NAME
FROM
BT_IMP_DAILERCONSQ a,
CONF_AUTODIAL_Disposition b
WHERE 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_DOLLAR
FROM TBL_PCCS_BIZ_CASE_CASEMAIN c
LEFT JOIN TBL_PCCS_BIZ_CASE_ACCT acct ON c.CASE_ID = acct.CASE_ID
WHERE c.CASE_ID = 120150818000000611 AND c.CARD_PROD=201
GROUP BY c.CUST_NAME, acct.CREDIT_CARD_NO, c.STATEMENT_DATE_STR
ORDER BY c.CUST_NAME,acct.CREDIT_CARD_NO WITH ur
-----------------------------------------------------------------------------------------------------
SELECT * FROM TBL_PCCS_BIZ_CASE_CASEMAIN a
INNER JOIN
( SELECT COLL_ID, SCEN_ID , CENTER_NO FROM TBL_PCCS_CONF_GL_COLLIDDEAL WHERE
DAIL_TYPE IN ('PRDT', 'PRVW') ) b
ON a.CENTER_NO = b.CENTER_NO AND a.PROMPT_MSG_SEQ_NO = b.COLL_ID AND a.TRIAD_SCENID = b.SCEN_ID
WHERE 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_CASEMAIN
select 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_ID
FROM
TBL_PCCS_BIZ_CASE_CASEMAIN M, TBL_PCCS_BIZ_CASE_ACCT T, VIEW_CASE_TRANS V
WHERE 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 NULL
AND 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_BU
HAVING
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' END
from TBL_PCCS_CONF_GL_ACTCODEDEF
SELECT
CASE WHEN DATA_SOURCE= 'WLFK' THEN '网络发卡'
WHEN DATA_SOURCE='SJWL' THEN '社交网络'
WHEN DATA_SOURCE='BANK' THEN '人行'
ELSE '其它'
END
from TBL_PCCS_BIZ_CUST_TEL_EXPAND
-----------------------------------------------------------------------------------------------------
--N天不通
SELECT
LST_UPD_DATE, SUM(EFFECTFLAG) AS STATUS
FROM
( 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 STATUS
FROM
( 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) B
WHERE ( 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_AMT
FROM TBL_PCCS_BT_OPT_OAPMT A
-----------------------------------------------------------------------------------------------------
DELETE
FROM
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_CREDITL
select 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 NULL
AND NEW_COLL_ORG <>''
select DIRE_COLL_CNT as 直催 ,PACKAGE_CNT as 打包 ,LAW_CNT as 司法,BEF_DEPUTE_CNT as 委前 from TBL_PCCS_BIZ_CASE_CASEMAIN
select 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_casemain
SET 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 ) A
WHERE 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 没有值就返回3
select 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_AMT
FROM
(
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')) B
WHERE 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_DATE
FROM TBL_PCCS_BIZ_CASE_CASEMAIN C
WHERE 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 a
INNER 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_ID
FROM
TBL_PCCS_BT_IMP_TRIADSTR bit
WHERE
TRIAD_LIMIT_NO IS NOT NULL
AND 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 不能有重复数据
MERGE
INTO
BT_IMP_CSWF_PRE2 AS CS2 -- 默认插入这个表
USING
BT_IMP_CSWF_PRE AS CS
ON
CS2.ACCTID = CS.ACCTID
WHEN MATCHED
THEN
UPDATE
SET
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_NUM
WHEN NOT MATCHED
THEN
INSERT (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 )
-----------------------------------------
MERGE
INTO
TBL_PCCS_BIZ_CUST_ADDR A
USING
( SELECT * FROM TBL_PCCS_BT_IMP_ADDR WITH ur)B
ON
( 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
THEN
UPDATE
SET
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
THEN
INSERT
(
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 T
LEFT JOIN TBL_PCCS_BIZ_CASE_ACCT A ON T.CASE_ID = A.CASE_ID
WHERE 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_rate
FROM hq_price h
LEFT JOIN hq_stock_tp t
ON h.tick =t.tick AND h.dt=t.dt
WHERE 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,50
select * 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=utf8
CREATE 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改为upt
ALTER 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_hot
DESC 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_name
ALTER TABLE table_name DROP INDEX index_name
ALTER 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 30
select t.* from (
select f005n_0093 ,ob_object_id,rownum as rn from tb_company_0093
) T
where 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 ) t
where t.rn>0 and t.rn<200
-------------------------------------------------------------------------------------------------------------
-- to_timestamp 可以指定更加具体的时间 如 2010-12-18 14:14:15.00005
select * 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:15
select * 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;
-------------------------------------------------------------------------------------------------------------
--增加一列 字段为stat
alter 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_address
create 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_sequ
create sequence china_address_sequ start with 1 increment by 1;
--查询下一个序列
select china_address_sequ.nextval from dual
select * 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_tr
before insert on china_address -- 表的名字 china_address
for 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_temp
drop 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_company
before insert on nlp_dict_company
for each row
begin
: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_0067
group 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 f005v0067
from (
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
) b
where 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_0067 as obbondid0067,
ob_bondname_0067 as obbondname0067,
ob_isvalid_0067 as obisvalid0067,
ob_object_id as obObjectId
from
( 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
) b
where 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 o
where 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 2036
SYS ROW LOCK (null) (null) (null) 195 25941
ZHAOSHANG 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$session
select * 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
