创建数据库设定字符集
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
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