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

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 编辑 ]

mchdba

-- ===============================================================
-- 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 */$$

mchdba

-- ===============================================================
-- 设置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

-- ===============================
--  往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

mchdba

-- ===============================
--  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权限。

mchdba

-- ===================================
-- 日期
-- ===================================
当前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)

mchdba

-- ===================================
-- 通过生日查看年龄(周岁)
-- ===================================

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 编辑 ]

mchdba

-- ===================================
-- 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);

mchdba

-- ======================
-- 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 $$

mchdba

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


如果是mysql5.5还是可以做到的，5.1不行
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分区