mySQL语句整理 – 小飞侠

2016-2-1整理

select CONCAT(‘bh’,YHBH) AS YHBH,YHXM from sfjc_yw_ryxx where FYDM=’1300B00′ AND BZLX != ‘8’ AND LKYY IS null and yhbh NOT IN (select yhbh from sfjc_jl_djlxr) ORDER BY NBPXH,cast(ZWMC as int)

SELECT * FROM SFJC.SFJC_YW_RYXX A left join sfjc.sfjc_xt_fy B on A.FYDM=B.FYDM LEFT JOIN sfjc.sfjc_v_wz_jxjb C on A.FYDM=C.FYDM AND a.yhbh=C.yhbh WHERE YHXM =”dddd”

select YHBH,YHXM from sfjc_yw_ryxx where FYDM=’1300B00′ and YHBH in (select yhbh from sfjc_jl_djlxr) ORDER BY NBPXH,cast(ZWMC as int)

ALTER table scfz_xewp add BGR varchar(255) after KYR

INSERT INTO scfz_clky (CLBH, AH, CPH, CLX, PGJ, PMJ, KYSJ, KYDD, BGR, CBR, LRYH, LRSJ, WJMC) VALUES (‘CLKY-1510-00001’, ‘345’, ’64’, ‘121’, ”, ”, ‘2015-10-15’, ‘rest’, ‘风格’, ‘1303B12140801001’, ‘4’, now(), ”)

drop trigger sfjc.sfjc_yw_ryxx_before_update;

–/

CREATE

DEFINER=`sfjc`@`%

` TRIGGER `sfjc`.`sfjc_yw_ryxx_before_update`

BEFORE UPDATE ON

sfjc.sfjc_yw_ryxx

FOR EACH ROW begin

set new.xgsj=now();

if new.LKYY is not null then

update sfjc.sfjc_xt_user set sfqy=0 where yhbh=new.yhbh;

end if;

if new.LKYY is null then

update sfjc.sfjc_xt_user set sfqy=1 where yhbh=new.yhbh;

end if;

end

/

–/

CREATE

DEFINER=`sfjc`@`%

` TRIGGER `sfjc`.`sfjc_wz_lm_before_insert`

BEFORE INSERT ON

sfjc.sfjc_wz_lm

FOR EACH ROW begin

if new.cjsj is null then

set new.cjsj=now();

end if;

end

/

call web_list_proc(’19’,1,20,”)

java中调用时，

cStm = con.prepareCall(

“{call web_list_pf_proc(‘” + lmdm + “‘,” + curPage + “,”

+ perPage + “,'”+aSearchKey+”‘,'” + selectTimeWhere +”‘,'” + sortTime + “‘)}”);

CREATE

DEFINER=`sfjc`@`%

` PROCEDURE `sfjc`.`web_list_proc`(in lmdm varchar(10), in showPageNum int, in paginalLinage int, in keyWords varchar(255))

begin

declare countStmt varchar(200);

declare selectStmt varchar(300);

declare beginRecordNum int;

set @beginRecordNum=(showPageNum-1)*paginalLinage;

if lmdm=’search’ then

set @countSql = concat(‘select count(1) recNum from (select ID,BT FROM SFJC.SFJC_WZ_TPXW WHERE ‘,keyWords,’ and xxzt=8 union select ID,BT FROM SFJC.SFJC_WZ_XXNR WHERE ‘,keyWords,’ and xxzt=8) A ‘);

set @selectSql = concat(‘select ID,BT,FWCS,FBSJ,datediff(now(),FBSJ) FBTS,LMDM from (select concat(”sp=”,ID) ID,BT,FWCS,FBSJ,LMDM FROM SFJC.SFJC_WZ_TPXW WHERE ‘,keyWords,’ and xxzt=8 union select concat(”nsp=”,ID) ID,BT,FWCS,FBSJ,LMDM FROM SFJC.SFJC_WZ_XXNR WHERE ‘,keyWords,’ and xxzt=8) A ORDER BY FWCS DESC limit ‘,@beginRecordNum,’,’,paginalLinage);

elseif lmdm=’0′ then

set @countSql = concat(‘select count(1) recNum from (select ID,BT FROM SFJC.SFJC_WZ_TPXW WHERE FWSJ >DATE_ADD(now(), INTERVAL -11 DAY) and xxzt=8 union select ID,BT FROM SFJC.SFJC_WZ_XXNR WHERE FWSJ >DATE_ADD(now(), INTERVAL -11 DAY) and xxzt=8) A ‘);

set @selectSql = concat(‘select ID,BT,FWCS,FBSJ,datediff(now(),FBSJ) FBTS,LMDM,FWSJ from (select concat(”sp=”,ID) ID,BT,FWCS,FBSJ,LMDM,FWSJ FROM SFJC.SFJC_WZ_TPXW WHERE FWSJ >DATE_ADD(now(), INTERVAL -11 DAY) and xxzt=8 union select concat(”nsp=”,ID) ID,BT,FWCS,FBSJ,LMDM,FWSJ FROM SFJC.SFJC_WZ_XXNR WHERE FWSJ >DATE_ADD(now(), INTERVAL -11 DAY) and xxzt=8) A ORDER BY FWCS,FWSJ DESC limit ‘,@beginRecordNum,’,’,paginalLinage);

elseif lmdm=’19’ then

set @countSql = concat(‘select count(1) recNum from (select A.BT, B.FILEPATH,NR,A.CJSJ,1 LX from SFJC.SFJC_WZ_TPXW A, SFJC.SFJC_WZ_TP_PATH B where A.ID=B.TPGLID and A.FBSJ IS NOT NULL and A.xxzt=8 AND A.LMDM=’,lmdm,’ group by A.ID,A.BT union select BT,”” FILEPATH,NR,CJSJ, 0 LX from SFJC.SFJC_WZ_XXNR where LMDM=’,lmdm,’ and FBSJ IS NOT NULL and xxzt=8) a’);

set @selectSql = concat(‘select ID,RID,BT,FILEPATH,NR,FBSJ,datediff(now(),FBSJ) FBTS,LX,LMDM,XXLX,LYZ,FWCS,(select count(1) from SFJC.SFJC_WZ_LTLY A where A.XXID=RID and A.XXLX=XXLX) HFS,(select concat(A.LYRXM,”#=#”,date(A.LYSJ)) from SFJC.SFJC_WZ_LTLY A where A.XXID=RID and A.XXLX=XXLX order by LYSJ DESC limit 1) ZHFB from (select concat(”sp=”,A.ID) ID,A.BT, B.FILEPATH,NR,A.FBSJ,1 LX,A.LMDM,A.ID RID,”tp” XXLX,LYZ,FWCS from SFJC.SFJC_WZ_TPXW A, SFJC.SFJC_WZ_TP_PATH B where A.ID=B.TPGLID and A.FBSJ IS NOT NULL and A.xxzt=8 AND A.LMDM=’,lmdm,’ group by A.ID,A.BT union select concat(”nsp=”,ID) ID,BT,”” FILEPATH,NR,FBSJ, 0 LX,LMDM,ID RID,”xx” XXLX,LYZ,FWCS from SFJC.SFJC_WZ_XXNR where LMDM=’,lmdm,’ and FBSJ IS NOT NULL and xxzt=8) a ORDER BY FBSJ DESC limit ‘,@beginRecordNum,’,’,paginalLinage);

else

set @countSql = concat(‘select count(1) recNum from (select A.BT, B.FILEPATH,NR,A.CJSJ,1 LX from SFJC.SFJC_WZ_TPXW A, SFJC.SFJC_WZ_TP_PATH B where A.ID=B.TPGLID and A.FBSJ IS NOT NULL and A.xxzt=8 AND A.LMDM=’,lmdm,’ group by A.ID,A.BT union select BT,”” FILEPATH,NR,CJSJ, 0 LX from SFJC.SFJC_WZ_XXNR where LMDM=’,lmdm,’ and FBSJ IS NOT NULL and xxzt=8) a’);

set @selectSql = concat(‘select ID,BT,FILEPATH,NR,FBSJ,datediff(now(),FBSJ) FBTS,LX,LMDM from (select concat(”sp=”,A.ID) ID,A.BT, B.FILEPATH,NR,A.FBSJ,1 LX,A.LMDM from SFJC.SFJC_WZ_TPXW A, SFJC.SFJC_WZ_TP_PATH B where A.ID=B.TPGLID and A.FBSJ IS NOT NULL and A.xxzt=8 AND A.LMDM=’,lmdm,’ group by A.ID,A.BT union select concat(”nsp=”,ID) ID,BT,”” FILEPATH,NR,FBSJ, 0 LX,LMDM from SFJC.SFJC_WZ_XXNR where LMDM=’,lmdm,’ and FBSJ IS NOT NULL and xxzt=8) a ORDER BY FBSJ DESC limit

‘,@beginRecordNum,’,’,paginalLinage);

end if;

prepare countStmt from @countSql;

prepare selectStmt from @selectSql;

execute countStmt;

execute selectStmt;

end

//修改

知识点整理总结：

1.select CONCAT(‘bh’,YHBH) AS YHBH,YHXM from sfjc_yw_ryxx where FYDM=’1300B00′ AND BZLX != ‘8’

AND LKYY IS null and yhbh NOT IN (select yhbh from sfjc_jl_djlxr) ORDER BY NBPXH,cast(ZWMC as int)

concat连接字符串，

IS NULL/IS NOT NULL，

cast(expression AS data_type… int/decimal),

如SELECT CAST(’12’ AS int)但是cast()和convert()都不能执行四舍五入或者截取操作；

SELECT CAST(‘12.50’ AS decimal(9,2)),显示为12.50

2.SELECT * FROM SFJC.SFJC_YW_RYXX A left join sfjc.sfjc_xt_fy B

on A.FYDM=B.FYDM LEFT JOIN sfjc.sfjc_v_wz_jxjb C on A.FYDM=C.FYDM AND a.yhbh=C.yhbh WHERE YHXM =”dddd”

表连接：

tableA A left join tableB B on A.xx = B.xx(left join … on …)

3.ALTER table scfz_xewp add BGR varchar(255) after KYR(改变表结构)

4.INSERT INTO scfz_clky (CLBH, AH, LRYH, LRSJ, WJMC)

VALUES (‘CLKY-1510-00001’, ‘(2015)案件’, ‘4’, now(), ”)

5.drop trigger sfjc.sfjc_yw_ryxx_before_update;删除触发器

6.创建触发器

–/

CREATE

DEFINER=`sfjc`@`%

` TRIGGER `sfjc`.`sfjc_yw_ryxx_before_update`

BEFORE UPDATE ON

sfjc.sfjc_yw_ryxx

FOR EACH ROW begin

set new.xgsj=now();

if new.LKYY is not null then

update sfjc.sfjc_xt_user set sfqy=0 where yhbh=new.yhbh;

end if;

if new.LKYY is null then

update sfjc.sfjc_xt_user set sfqy=1 where yhbh=new.yhbh;

end if;

end

/

7.存储过程的创建和使用

call web_list_proc(’19’,1,20,”)

java中调用时，(程序中使用)

cStm = con.prepareCall(“{call web_list_pf_proc(‘” + lmdm + “‘,” + curPage + “,” + perPage

+ “,'”+aSearchKey+”‘,'” + selectTimeWhere +”‘,'” + sortTime + “‘)}”);

数据库中创建存储过程：

CREATE

DEFINER=`sfjc`@`%

` PROCEDURE `sfjc`.`web_list_proc`(in lmdm varchar(10), in showPageNum int, in paginalLinage int, in keyWords varchar(255))

begin

declare countStmt varchar(200);

declare selectStmt varchar(300);

declare beginRecordNum int;

set @beginRecordNum=(showPageNum-1)*paginalLinage;

8.union & union all （其中union会去除重复部分，union all则不会）

select tn,num from t1 union (all) select tn,num from t2

9.select (CASE B.SFQY WHEN 1 THEN ‘启用’ ELSE ‘未启用’ END) SFQY from xxx

10.ALTER TABLE bghc_dept ADD COLUMN SFJY int NOT NULL COMMENT ‘是否禁用’ AFTER BMBH (MySQL)

11.CREATE TABLE 创建表

bghc_splcmx

(

ID VARCHAR(20) NOT NULL,

LCJL VARCHAR(200) NOT NULL COMMENT ‘流程记录’,

SFQY INT NOT NULL COMMENT ‘是否启用’,

SCSJ DATETIME,

XGSJ DATETIME,

PRIMARY KEY (ID)

)

ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’审批流程明细’;

原文链接：https://blog.csdn.net/iteye_6153/article/details/82647366

你可能也喜欢