mysql数据库操作备用

创建数据库设定字符集
CREATE DATABASE `lim` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
用户授权
grant all privileges on lim.* to root@'%' identified by 'lenovo';
FLUSH PRIVILEGES;
查看数据库引擎
show engines;


数据备份，包含存储过程，event，数据结构和表
mysqldump -hlocalhost -uroot -plenovo -R -E database >存储路径和备份的文件名称
修改mysql的字符集
SET character_set_client = utf8 ;
SET character_set_connection = utf8 ;
SET character_set_database = utf8 ;
mysql> SET character_set_results = utf8 ;
mysql> SET character_set_server = utf8 ;
mysql> SET collation_connection = utf8 ;
mysql> SET collation_database = utf8 ;
mysql> SET collation_server = utf8 ;
-- 查看是否开启定时器


　　SHOW VARIABLES LIKE '%sche%';


　　-- 开启定时器 0：off 1：on


　　SET GLOBAL event_scheduler = 1;




删除表分区
alter table test drop partition p0;
创建表分区
ALTER TABLE test PARTITION BY RANGE (testid) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
查看表分区


SELECT    
        partition_name part,     
        partition_expression expr,     
        partition_description descr,     
        table_rows     
FROM    
        INFORMATION_SCHEMA.partitions     
WHERE    
        TABLE_SCHEMA = schema()    
        AND TABLE_NAME='表名'; 


启用event
alter event event_test on completion preserve disable;


ADD PARTITION
DROP PARTITION
COALESCE PARTITION
REORGANIZE PARTITION
ANALYZE PARTITION
CHECK PARTITION
OPTIMIZE PARTITION
REBUILD PARTITION
REPAIR PARTITION


每月的1号零点
DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 0 HOUR)


select DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 0 HOUR)


select DATE_ADD(date_sub(now(),interval -1 day),INTERVAL 0 HOUR)


select DATE_FORMAT(date_sub(now(),interval -1 day),'%Y%m%d')
select date_format(date_sub(now(),interval -1 day),'%Y-%m-%d 00:00:00')




SELECT    
        partition_name part,     
        partition_expression expr,     
        partition_description descr,     
        table_rows     
FROM    
        INFORMATION_SCHEMA.partitions     
WHERE    
        TABLE_SCHEMA = schema()    
        AND TABLE_NAME='history';


 CREATE TABLE `history1` (                                               
           `itemid` bigint(20) unsigned NOT NULL,                               
           `clock` int(11) NOT NULL DEFAULT '0',                                
           `value` double(16,4) NOT NULL DEFAULT '0.0000',                      
           `ns` int(11) NOT NULL DEFAULT '0',                                   
           KEY `history_1` (`itemid`,`clock`)                                   
         ) ENGINE=MyISAM DEFAULT CHARSET=utf8  


alter event event_test1111 on completion preserve enable;
alter table history drop partition p_20130125;


select DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 0 HOUR)


select DATE_ADD(date_sub(now(),interval -1 day),INTERVAL 0 HOUR)


select DATE_FORMAT(date_sub(now(),interval -1 day),'%Y%m%d')
select date_format(date_sub(now(),interval -1 day),'%Y-%m-%d 00:00:00')

case应用（竖表转横表）

select b.hostname,sum(b.class2) as e2,sum(b.class3) as e3,sum(b.class4) as e4,sum(b.class5) as e5 from (select  a.host_v as hostname ,case a.priority_v when '2' then a.tte else 0 end  as class2,case a.priority_v when '3' then a.tte else 0 end  as class3,case a.priority_v when '4' then a.tte else 0 end  as class4,case a.priority_v when '5' then a.tte else 0 end  as class5   from (select i.hostid as hostid_v,h.host as host_v,e.clock as clock_v,t.priority as priority_v,count(t.priority) as tte  from events e,triggers t,functions f,items i,hosts h 
where f.itemid=i.itemid and f.triggerid=t.triggerid and t.triggerid=e.objectid  and DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(FROM_UNIXTIME(e.clock)) and i.hostid=h.hostid and t.priority>1 group by i.hostid,t.priority
) a ) b group by b.hostname