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;
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
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='审批流程明细';
相关推荐
MySql语句整理
今天小编就为大家分享一篇关于MySQL语句整理及汇总介绍,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
个人整理的所有MySQL基础常用语句的使用和用法。文档内含有各种标题方便查找。工具WPS(使用microsoft office 可能没有标题)
Mysql中创建各种索引的语句整理 代码 添加PRIMARY KEY(主键索引) 添加UNIQUE(唯一索引) 添加INDEX(普通索引) 添加FULLTEXT(全文索引) 添加多列索引 ) mysql>ALTER TABLE `...
一些常用的mysql中的sql语句,本人业余时间整理而成
MySQL语法语句大全,这是多年我收集整理出来的实用文档
mysql的简单语句整理
mysql数据库的基本操作语句,自己整理的,喜欢的话后期还会有mysql其他方面的整理。
这是我学习mysql时顺便整理的sql语句,从创建表,修饰表到增删改查、视图、存储过程、触发器、索引、函数、常用的循环、判断。
本文为本人整理的一些与mysql数据库介绍及常用SQLl语句
整理了常用的mysql 语句,积累了很长时间的,值得看下!
mysql中创建各种索引的语句整理.docx
NULL 博文链接:https://yxwang0615.iteye.com/blog/970114
mysql中创建各种索引的语句整理知识.pdf
主要介绍了MySQL DML语句整理汇总,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
主要介绍了最全的mysql查询语句整理,需要的朋友可以参考下
mysql数据库介绍及常用SQLl语句(整理)
本人整理了mysql常用函数,仅供小白学习