MySQL管理一些基礎SQL語句

 1、進入information_schema 數據庫（存放了其他的數據庫的信息）
 use information_schema;
 
 2、查詢所有數據的大小：
 select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables;
 
 3、查看指定數據庫的大小：
 比如查看數據庫home的大小
 select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

 查看數據所占的空間大小 
 SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024),2) + ROUND(SUM(index_length)/(1024*1024),2),'MB') AS 'DB Size'
   FROM information_schema.`TABLES` WHERE table_schema='dbname'
 
 
 4、查看指定數據庫的某個表的大小
 比如查看數據庫home中 members 表的大小
 select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';
 select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='dbname' and table_name='t_msg_app_new';
 
 5、查看指定數據庫的表的大小 大到小
 SELECT table_name,CONCAT(ROUND((data_length/1024/1024),2),'MB') AS DATA FROM TABLES WHERE table_schema='dbname'  ORDER BY data_length DESC;
 
 6、查看指定數據庫表的創建時間，更新時間。
 SELECT table_name,create_time,update_time FROM information_schema.`TABLES`  WHERE  table_schema='dbname'  ORDER BY update_time DESC
 
 SELECT table_name,table_rows,create_time,update_time
 FROM information_schema.`TABLES`  WHERE  table_schema='dbname'  and table_name='tablename' ORDER BY update_time DESC


 7、查看所有存儲引擎為MyISAM的表
 SELECT * FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA='dbname' AND `engine`='MyISAM'
 
 8、查詢表中的所有列
 SELECT GROUP_CONCAT(column_name)AS namestr
 FROM information_schema.`COLUMNS`  WHERE  table_name='tablename' ;
 
 9、查詢表中的所有列(拼接插入INSERT SQL)
 SELECT CONCAT('INSERT INTO ',table_name,'(',GROUP_CONCAT(column_name),')VALUES()')AS namestr
 FROM information_schema.`COLUMNS`  WHERE   table_schema='dbname' AND table_name='tablename' ;
 
 10、#查看是否開啟事件
 select @@event_scheduler