您的位置:首页 - 教程 - MYSQL - 正文
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

评论: