mysql 分区

CALL proc_add_part(NULL , NULL ) 
CREATE TABLE `qqqqqq` (
  `code` VARCHAR(32) DEFAULT NULL COMMENT '编码',
  `voltage` DECIMAL(10,3) DEFAULT NULL COMMENT '电压',
  `current` DECIMAL(10,3) DEFAULT NULL COMMENT '电流',
  `power` DECIMAL(10,3) DEFAULT NULL COMMENT '功率',
  `sjsj` DATETIME DEFAULT NULL COMMENT '时间',
  `comment` VARCHAR(4000) DEFAULT NULL COMMENT '备注',
  UNIQUE KEY `IDX_SB_jnsj_SJSJ` (`sjsj`,`code`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='异常分析'
/*!50100 PARTITION BY RANGE (to_days(sjsj))
(PARTITION p_201509 VALUES LESS THAN (736420) ENGINE = InnoDB,
 PARTITION p_201510 VALUES LESS THAN (736450) ENGINE = InnoDB,
 PARTITION p_201609 VALUES LESS THAN (736634) ENGINE = InnoDB,
 PARTITION p_201611 VALUES LESS THAN (736664) ENGINE = InnoDB,
 PARTITION p_201612 VALUES LESS THAN (736695) ENGINE = InnoDB) */

调用事件中的ADD_PART CALL proc_add_part(NULL , NULL )

1 时间

SELECT DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY) 本月第一天 2017-12-01
SELECT LAST_DAY(DATE_SUB(NOW(),INTERVAL 1 MONTH)) 上月最后一天 2017-11-30
SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL -1 MONTH) 上月第一天 2017-11-01

SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE(NOW(), '%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')
SELECT DATE_FORMAT(DATE_ADD("2017-04-01",INTERVAL -1 DAY),'%Y-%m-%d')

SELECT DATE_FORMAT(DATE_ADD("2017-07-04 08:00:00",INTERVAL -1 HOUR),'%Y-%m-%d %H:%i:%s')

分组求和
SELECT SUM(s.quan) FROM (SELECT 
    MAX(nodequantity)-MIN(nodequantity) quan
  FROM
    rpt_nodes_power qu,
    g_byq byq 
  WHERE qu.nodeid = byq.byqid 
    AND byq.wgid = "86430001003" 
    AND qu.nodetype = 'byq' 
    AND TIMESTAMP >= DATE_FORMAT(NOW(), '%Y-%m-%d') 
    AND TIMESTAMP < DATE_ADD(
      DATE_FORMAT(NOW(), '%Y-%m-%d'),
      INTERVAL 1 DAY
    ) GROUP BY qu.nodeid) s

SELECT COUNT(*) AS c, GROUP_CONCAT(bjjh) AS cid FROM da_bj ;

DELIMITER $$

ALTER DEFINER=`dbbp`@`%` EVENT `JOB_STATISTICS_POWER_TEMP_BY_5MINUTE` ON SCHEDULE EVERY 5 MINUTE STARTS '2016-04-22 12:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN
  /*********************************************
desc :
	每隔5分钟统计哥哥节点功率及平均温度并写入到rpt_nodes_power
author： wuxiang@beepower.cn
create time：2016/04/20
update time         reason       
**********************************************/
  CALL rpt_statistical_power('10000','10000','dw',NOW(),@power,@temp,@quantity,@demand);
END$$

DELIMITER ;

CREATE TABLE IF NOT EXISTS rpt_nodes_power_grade (LIKE rpt_nodes_power)

CREATE TABLE IF NOT EXISTS rpt_nodes_power_grade (select * from rpt_nodes_power)

代替like

一、like语句
SELECT "abc" LIKE "%b%" 1 返回0，1

二、或是 locate 的別名 position(locate测试失败)
POSITION语句
SELECT POSITION("irc" IN "ircdsfaa") 返回1，存在>0

三、INSTR语句
SELECT INSTR("ircdsfaa", "irc") 返回1 ，存在>0

show variables like '%event%' 事件每5分钟执行

DELIMITER $$
ALTER DEFINER=`dbbp`@`%` EVENT `JOB_STATISTICS_POWER_TEMP_BY_5MINUTE` ON SCHEDULE EVERY 5 MINUTE STARTS '2016-04-22 12:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN
  CALL rpt_statistical_power('10000','10000','dw',NOW(),@power,@temp,@quantity,@demand);
  CALL rpt_statistical_grade_power('1628','100001802','tq',NOW(),@power,@temp,@hum,@quantity,@demand,@demand2,@demand3);
END$$
DELIMITER ;

EVERY 5 MINUTE，EVERY 1 DAY

//1关联修改
UPDATE point p ,bak32dev d 
   SET p.dev=d.dev,p.xishu=d.xishu
   WHERE p.rn=d.rn AND p.type=d.type  
   AND p.type="104" 

//2序列号
SELECT @rowno:=@rowno + 1 AS 序号, uname 用户,utime 注册时间 FROM `user`,(SELECT @rowno:=0) b WHERE urole =1 AND uname >1000000000 AND utime >="2017-10-01" AND utime <"2017-11-01" ORDER BY utime DESC 

//3修改自增号
ALTER TABLE order AUTO_INCREMENT = #id#

//4复制表，包括数据，中间可加as
CREATE TABLE point0207 SELECT *  FROM ak32dev

//5只复制表结构
加where 1=0

//6线程取消订单
new MyThread(id,list).start();//一定要start
class MyThread extends Thread {

        String id;
        List<ZhctOrderDetail> list;

        public MyThread(String id, List<ZhctOrderDetail> list) {
            this.id = id;
            this.list = list;
        }

        public void run(){
            try {
                Thread.sleep(15*60*1000l);
            } catch (InterruptedException e) {
                logger.info("15分钟未付款，修改订单异常...", e);
                e.printStackTrace();
            }
            orderManager.updAfter30min(id,list);

        }
    }

分数都大于80的人

SELECT s.name FROM mianshi s GROUP BY NAME HAVING MIN(s.fenshu)>80
SELECT DISTINCT NAME FROM mianshi WHERE NAME NOT IN( SELECT NAME FROM mianshi WHERE fenshu<=80)

复制表结构到新表
CREATE TABLE sb_fhsjtest SELECT * FROM sb_fhsj WHERE 1=2

http://www.jb51.net/article/30099.htm 插入百万数据

共 5313615 行受到影响
执行耗时 : 3 min 53 sec

LOAD DATA LOCAL INFILE 'e:/kkk.csv' INTO TABLE sb_fhsjtest(sjid,sjsj,jssj,ssyg);

每个字段后面有/t，每条数据需要/n

mysql 事务未提交导致死锁 Lock wait timeout exceeded; try restarting transaction 解决办法
select * from information_schema.innodb_trx
kill 62174
set innodb_lock_wait_timeout=100

show processlist;

DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000;

https://my.oschina.net/quanzhong/blog/222091