mysql 一些命令

1、修复数据表
check table

repair table

2.、if and then
CASE WHEN b.if_state=1 THEN '连载中' WHEN b.if_state=2 THEN '全本' WHEN b.if_state=3 THEN '选载' WHEN b.if_state=4 THEN '首发' WHEN b.if_state=5 THEN '普通' WHEN b.if_state=6 THEN '独家' WHEN b.if_state=7 THEN '转载' ELSE "未知" END AS states

3 如果发现一个数据异常的大了，需要查看一下其中哪些表变大了，可以使用如下命令

select table_name,data_length from information_schema.tables;


4.查看整库

select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size, 
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size, 
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free, 
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size 
from information_schema.tables where TABLE_SCHEMA = 'databasename';

5.查看单表

select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size, 
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size, 
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free, 
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size 
from information_schema.tables where TABLE_NAME = 'tablename'; 

6.到处数据：

SELECT user_name,title INTO OUTFILE '/tmp/book_finish_title.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM online_book where if_state = 2;


7.sql case

select b.book_id,b.title,b.user_name,b.fauthor,m.email,b.cate_name,b.total_roll,b.total_chapter,b.total_words, case b.if_state when 1 then '连载中' when 2 then '全本' when 3 then '选载' else '其他' end ,case b.if_audit when 4 then '首发 when 5 then '普通' when 6 then '独家' when 7 then '转载' else '其他' end , b.price from ecm_online_book as b left join ecm_member as m on (b.user_id = m.user_id)  where b.mes_from =1 and b.approve =1 into outfile '/tmp/book613.txt' fields terminated by '|' LINES TERMINATED BY '\r\n';