mysql数据库操作备用
来源:互联网 发布:access数据库实例 编辑:程序博客网 时间:2024/05/16 17:40
创建数据库设定字符集
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
- mysql数据库操作备用
- mysql 备用
- 【备用】非常不错的ASP操作数据库类,支持多数据库MSSQL,ACCESS,ORACLE,MYSQL等
- 创建物理备用数据库
- 创建逻辑备用数据库
- 备用数据库的配置
- 备用数据库的维护
- 逻辑备用数据库介绍
- 备用数据库快照
- 备用数据库的应用
- 创建备用数据库
- mysql 备用命令
- 如何正确合理的建立MYSQL数据库索引(写的太好了,备用)
- mysql 数据库操作类
- mysql数据库操作
- mysql的数据库操作
- MYSQL 数据库操作类
- Mysql数据库操作新手入门
- MyEclipse优化网址
- 解决重启机器后,ArcGIS Server for Java的REST接口无法访问的问题
- 绘制流向地图(Flow Maps)
- 管理者应对流言有"四招"
- 主流RAID简介
- mysql数据库操作备用
- Tuxedo安装license例子和远程客户端配置
- unix 内存越用越紧张,很多内存不释放
- Pre-check while you are applying one-off patch
- Android学习笔记03:学习过程中碰到的一些问题及解决方法
- android的事务
- 在Linux里设置环境变量的方法(export PATH)
- android中AsyncTask和Handler对比
- Delphi编译、调试的路径搜索