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;

      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='审批流程明细';