mysql 一些命令

jiay

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';