ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
查看: 146636|回复: 280

MySQL知识点技能点总结贴--个人版!

[复制链接]
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
跳转到指定楼层
1#
发表于 2011-9-8 11:12 | 只看该作者 |只看大图 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 mchdba 于 2013-8-28 18:31 编辑

每天进步一点点 请多多关注我的个人blog http://blog.csdn.net/mchdba

1,2011-09-08
-- ===============================================================
-- mysql root 管理软件
-- ===============================================================
监控并发软件:Mysqlslap


-- ===============================================================
-- 查询数据库的字符集合
-- ===============================================================
select distinct table_schema,table_collation from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema');

select distinct table_schema,table_collation, table_name from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema')   ;

-- 修改表的字符集
alter table cam.account charset utf8;



-- 建库
delimiter $$
CREATE DATABASE `csf_fdp` /*!40100 DEFAULT CHARACTER SET utf8 */$$



2, 2011-09-08
-- ===============================================================
-- 设置mysql slow log 开启select语句检测
-- ===============================================================
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes

在线关闭slow log
set global slow_query_log='off';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 10,100000;
-- slow log 没有写入记录

在线开启slow log
set global slow_query_log='on';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 20,100000;
-- slow log 有写入记录

5.1.40以上肯定支持的,set global slow_query_log='off'; set global slow_query_log='on';

[ 本帖最后由 mchdba 于 2011-9-8 11:17 编辑 ]
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
2#
发表于 2011-9-8 11:12 | 只看该作者
-- ===============================================================
-- mysql root 管理软件
-- ===============================================================
监控并发软件:Mysqlslap


-- ===============================================================
-- 查询数据库的字符集合
-- ===============================================================
select distinct table_schema,table_collation from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema');

select distinct table_schema,table_collation, table_name from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema')   ;

-- 修改表的字符集
alter table cam.account charset utf8;



-- 建库
delimiter $$
CREATE DATABASE `csf_fdp` /*!40100 DEFAULT CHARACTER SET utf8 */$$

1人打赏

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
3#
发表于 2011-9-8 11:13 | 只看该作者
-- ===============================================================
-- 设置mysql slow log 开启select语句检测
-- ===============================================================
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes

在线关闭slow log
set global slow_query_log='off';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 10,100000;
-- slow log 没有写入记录

在线开启slow log
set global slow_query_log='on';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 20,100000;
-- slow log 有写入记录

5.1.40以上肯定支持的,set global slow_query_log='off'; set global slow_query_log='on';

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
4#
发表于 2011-9-8 15:13 | 只看该作者
-- ===============================
--  往linux下mysql库里面导入excel数据
-- ===============================

1 用mysql自带的mysqlimport工具。

2 打开excel文件,copy出所有文字信息,保存在rpt.txt里面,如果里面有中文字符串,就需要打开rpt.txt,点击文件,点击另存为,选择编码选择UTF8,然后覆盖掉rpt.txt文件。

3, 将rpt.txt传到linux目录里面,chown mysql:mysql rpt.txt; chmod 777 rpt.txt; 然后cp rpt.txt到数据库目录(数据库名字叫csf),比如/usr/local/mysql/var/csf/。

4 在csf库里面,建一张表,名字叫rpt,跟rpt.txt文件名字相同。建立的field跟excel上面的标题要一致。

5 执行mysqlimport命令
Shell> mysqlimport -uroot -p --default-character-set='utf8' --r csf /usr/local/mysql/csf/rpt.txt

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
5#
发表于 2011-9-9 09:55 | 只看该作者
-- ===============================
--  MySQL Server has gone away !
-- ===============================

创建用户 feng.gao
连接的时候,报错  MySQL Server has gone away !
查了好久,才从后台的错误日志中:
110909  9:31:25 [Warning] Aborted connection 200 to db: 'unconnected' user: 'feng.gao' host: '192.168.0.90' (init_connect command failed)
110909  9:31:25 [Warning] INSERT command denied to user 'feng.gao'@'192.168.0.90' for table 'accesslog'

发现原来是还没有赋予用户对监控日志表 accesslog的insert权限。

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
6#
发表于 2011-9-9 16:06 | 只看该作者
-- ===================================
-- 日期
-- ===================================
当前week的第一天:
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)

当前week的最后一天:
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY)

前一week的第一天:
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY)

前一week的最后一天:
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY)

前两week的第一天:
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY)

前两week的最后一天:
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY)


当前month的第一天:
SELECT concat(date_format(LAST_DAY(now()),'%Y-%m-'),'01')

当前month的最后一天:
SELECT LAST_DAY(now())

前一month的第一天:
SELECT concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')

前一month的最后一天:
SELECT LAST_DAY(now() - interval 1 month)

前两month的第一天:
SELECT concat(date_format(LAST_DAY(now() - interval 2 month),'%Y-%m-'),'01')

前两month的最后一天:
SELECT LAST_DAY(now() - interval 2 month)


当前quarter的第一天:
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01')

当前quarter的最后一天:
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month)

前一quarter的第一天:
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-6 month),'%Y-%m-'),'01')

前一quarter的最后一天:
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-4 month)

前两quarter的第一天:
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-9 month),'%Y-%m-'),'01')

前两quarter的最后一天:
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-7 month)

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
7#
发表于 2011-9-9 16:08 | 只看该作者
-- ===================================
-- 通过生日查看年龄(周岁)
-- ===================================

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS('1982-12-12')), '%Y') + 0;


-- ===================================
-- 查看数据库资源
-- ===================================

SELECT  
table_schema AS 'Db Name',   
Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',   
Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ;

【】在myisam为主的数据库服务器上面,执行这个sql很快,2秒之内就有结果了,但是在innodb为主的服务器上面,执行sql比较慢,花了40秒才出来结果,为神马?

[ 本帖最后由 mchdba 于 2011-9-9 16:10 编辑 ]

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
8#
发表于 2011-9-9 16:14 | 只看该作者
-- ===================================
-- mysql交叉报表
-- ===================================

--生成随机数round
select round(round(rand(),4)*10000);

drop table tb;
create table tb
select '张三' name,'语文' cource,74 score union all
select '张三','数学',83 union all
select '张三','物理',93 union all
select '李四','语文',74 union all
select '李四','数学',84 union all
select '李四','物理',94 union all
select '王二','语文',69 union all
select '王二','数学',84 union all
select '王二','物理',94;
select * from tb where name='王二';

select name 姓名,
  max(case cource when '语文' then score  else 0 end) 语文,
  max(case cource when '数学' then score  else 0 end) 数学,
  max(case cource when '物理' then score  else 0 end) 物理,
  cast(avg(score*1.0) as decimal(18,2)) 平均分,
  sum(score) 总分
from tb
group by name having(min(score)>69);

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
9#
发表于 2011-9-9 16:17 | 只看该作者
-- ======================
-- MySQL While循环例子
-- ======================
use test;
delimiter $$
create procedure p_file_t()
begin
  declare a int;
  set a=1;
  loop1:while a<3 do
         insert into drbd_t(hash,path) values('abcd123456', '/192.168.250.100/file');
        select a;
  set a=a+1;
  end while loop1;
end $$

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
10#
发表于 2011-9-9 16:37 | 只看该作者
本帖最后由 mchdba 于 2013-8-20 18:25 编辑

-- ======================
-- MySQL 分区例子
-- ======================


如果是mysql5.5还是可以做到的,5.1不行[后补:这里的测试方案是没有安装partitioning插件,所以不行]
CREATE TABLE part_date
          ( c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
     c2 varchar(40) not null default '',
     c3 datetime not  NULL,
     PRIMARY KEY (c1,c3),
     KEY partidx(c3))  ENGINE=InnoDB DEFAULT CHARSET=utf8
         partition by range  COLUMNS(c3)
     (
     PARTITION p201012 VALUES LESS THAN ('2011-01-01 06:00:00'),
     PARTITION p201101 VALUES LESS THAN ('2011-01-01 12:00:00'),
     PARTITION p201102 VALUES LESS THAN ('2011-01-01  18:00:00'),
     PARTITION p201103 VALUES LESS THAN ('2011-01-01  23:59:59'),
     PARTITION p201912 VALUES LESS THAN MAXVALUE );

然后用函数录入数据
DELIMITER $$

DROP PROCEDURE IF EXISTS `load_data` $$
CREATE DEFINER=`root`@`%` PROCEDURE `load_data`()
BEGIN
    declare v int default 0;
    while v < 10000
    do
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 01:00:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 03:00:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 05:01:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 07:01:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 15:01:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 16:01:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 17:01:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 18:01:00');
         insert into part_date(c2,c3)
        values (uuid(),'2011-01-01 19:01:00');
         set v = v + 1;
    end while;

END $$

DELIMITER ;

分区
explain partitions select count(*) from part_date where c3 > date '2011-01-01 06:02:00' and c3 < date '2011-01-01

08:02:00'

看一下只走了p201101分区

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
http://www.vxiaotou.com