Mysql私有增强性命令小记

 

从MySQL 5开始, 你可以看到多了一个系统数据库information_schema . information_schema 存贮了其他所有数据库的信息。information_schema是一个虚拟数据库，并不物理存在，在select的时候，从其他数据库获取相应的信息。 让我们来看看几个使用这个数据库的例子：

 

查询数据库dj214中表数据超过1000行的表

view plaincopy to clipboardprint?

select concat(table_schema,'.',table_name) as table_name,table_rows    

 from information_schema.tables where table_rows > 1000  and table_schema = 'dj214' order by table_rows desc;    

 

查询数据库dj214中所有没有主键的表

view plaincopy to clipboardprint?

SELECT CONCAT(t.table_schema,".",t.table_name) as table_name    

FROM information_schema.TABLES t    

LEFT JOIN information_schema.TABLE_CONSTRAINTS tc    

ON t.table_schema = tc.table_schema    

AND t.table_name = tc.table_name    

AND tc.constraint_type = 'PRIMARY KEY'    

WHERE tc.constraint_name IS NULL    

AND t.table_type = 'BASE TABLE'  AND t.table_schema = 'dj214' ;   

 

查询所有数据库中10张最大表

view plaincopy to clipboardprint?

SELECT concat(table_schema,'.',table_name) 表名称,    

concat(round(data_length/(1024*1024),2),'M') 表大小    

 FROM information_schema.TABLES    

ORDER BY data_length DESC LIMIT 10;  

 

查看MYSQL数据库下所有的数据库

view plaincopy to clipboardprint?

SELECT SCHEMA_NAME AS 'database' FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0 , 30   

 

列出指定数据库中的所有表名称

 

view plaincopy to clipboardprint?

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dj214'  

 

列出指定数据库下指定表的表结构

 

view plaincopy to clipboardprint?

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT   

FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog' AND table_schema = 'dj214'  

 

一段MYSQL存储过程[删除指定库中所有的空表]

    begin  

    /*局部变量的定义,默认值为空 */  

    declare tmpName varchar(200) default '' ;  

 

    /*定义游标*/  

    DECLARE reslutList Cursor FOR select table_name from information_schema.tables where table_rows <1 and table_schema = 'sz8_news' order by table_rows desc;  

    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;  

    OPEN reslutList;/*打开游标*/  

    FETCH reslutList into tmpname; -- 取数据  

    /* 循环体 */  

WHILE ( tmpname is not null) DO  

     set @sql = concat('drop table sz8_news.',tmpname,";");  

     PREPARE stmt1 FROM @sql ;  

     EXECUTE stmt1 ;  

     DEALLOCATE PREPARE stmt1;  

      /*游标向下走一步*/  

     FETCH reslutList INTO tmpname;  

END WHILE;  

CLOSE reslutList; /*关闭游标*/  

end  

 

 

一段MYSQL存储过程[删除指定库下所有表中的空列，即表中的任何一条记录该列都没有值]

 

BEGIN  

    DECLARE done INT DEFAULT 0;  

    DECLARE cTbl varchar(64);  

    DECLARE cCol varchar(64);  

    DECLARE cur1 CURSOR FOR  

    select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS   

    where TABLE_SCHEMA='sz8_news' and IS_NULLABLE='YES' order by TABLE_NAME;  

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  

    set @sqlDrop='';  

    OPEN cur1;  

    FETCH cur1 INTO cTbl, cCol;/*得到表名及列名*/  

WHILE done = 0 DO  

    set @x=0;  

    /*主要改进了这里把空值也纳入判断条件中去，即如果字段为null或空*/  

    set @sql=concat('select 1 into @x from ',cTbl,' where ',cCol,' is not null and ',cCol,' !=""  limit 1');  

    PREPARE stmt1 FROM @sql;  

    EXECUTE stmt1;  

    DEALLOCATE PREPARE stmt1;  

    if @x=0 then  

        set @sqlDrop=concat('alter table `',cTbl,'` drop COLUMN`',cCol,'`;');  

        PREPARE stmt1 FROM @sqlDrop;  

        EXECUTE stmt1;  

        DEALLOCATE PREPARE stmt1;  

    end if ;  

    set done = 0;  

    FETCH cur1 INTO cTbl, cCol;  

END WHILE;  

CLOSE cur1;  

END