Mysql私有增强性命令小记

从MySQL 5 开始 ,  你可以看到多了一个系统数据库 information_schema . information_schema  存贮了其他所有数据库的信息。 information_schema是一个虚拟数据库，并不物理存在。Mysql的INFORMATION_SCHEMA数据库包含了一些表和视图，提供了访问数据库元数据的方式，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面. 让我们来看看几个使用这个数据库的例子http://hudeyong926.iteye.com/blog/859398

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

Java代码  

1. select concat(table_schema,'.',table_name) as table_name,table_rows    
2.  from information_schema.tables where table_rows > 1000  and table_schema = 'dj214' order by table_rows desc;    

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

Java代码  

1. SELECT CONCAT(t.table_schema,".",t.table_name) as table_name    
2. FROM information_schema.TABLES t    
3. LEFT JOIN information_schema.TABLE_CONSTRAINTS tc    
4. ON t.table_schema = tc.table_schema    
5. AND t.table_name = tc.table_name    
6. AND tc.constraint_type = 'PRIMARY KEY'    
7. WHERE tc.constraint_name IS NULL    
8. AND t.table_type = 'BASE TABLE'  AND t.table_schema = 'dj214' ;   

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

Java代码  

1. SELECT concat(table_schema,'.',table_name) 表名称,    
2. concat(round(data_length/(1024*1024),2),'M') 表大小    
3.  FROM information_schema.TABLES    
4. ORDER BY data_length DESC LIMIT 10;  

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

Java代码  

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

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

Java代码  

1. SELECT table_name, table_type, engine  FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dj214' AND table_type='BASE TABLE'  

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

Java代码  

1. SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT   
2. FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog' AND table_schema = 'dj214'  

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

Java代码  

1.     begin  
2.     /*局部变量的定义,默认值为空 */  
3.     declare tmpName varchar(200) default '' ;  
4.       
5.     /*定义游标*/  
6.     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;  
7.     declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;  
8.     OPEN reslutList;/*打开游标*/  
9.     FETCH reslutList into tmpname; -- 取数据  
10.     /* 循环体 */  
11. WHILE ( tmpname is not null) DO  
12.      set @sql = concat('drop table sz8_news.',tmpname,";");  
13.      PREPARE stmt1 FROM @sql ;  
14.      EXECUTE stmt1 ;  
15.      DEALLOCATE PREPARE stmt1;  
16.       /*游标向下走一步*/  
17.      FETCH reslutList INTO tmpname;  
18. END WHILE;  
19. CLOSE reslutList; /*关闭游标*/  
20. end  

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

Java代码  

1. BEGIN  
2.     DECLARE done INT DEFAULT 0;  
3.     DECLARE cTbl varchar(64);  
4.     DECLARE cCol varchar(64);  
5.     DECLARE cur1 CURSOR FOR  
6.     select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS   
7.     where TABLE_SCHEMA='sz8_news' and IS_NULLABLE='YES' order by TABLE_NAME;  
8.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
9.     set @sqlDrop='';  
10.     OPEN cur1;  
11.     FETCH cur1 INTO cTbl, cCol;/*得到表名及列名*/  
12. WHILE done = 0 DO  
13.     set @x=0;  
14.     /*主要改进了这里把空值也纳入判断条件中去，即如果字段为null或空*/  
15.     set @sql=concat('select 1 into @x from ',cTbl,' where ',cCol,' is not null and ',cCol,' !=""  limit 1');  
16.     PREPARE stmt1 FROM @sql;  
17.     EXECUTE stmt1;  
18.     DEALLOCATE PREPARE stmt1;  
19.     if @x=0 then  
20.         set @sqlDrop=concat('alter table `',cTbl,'` drop COLUMN`',cCol,'`;');  
21.         PREPARE stmt1 FROM @sqlDrop;  
22.         EXECUTE stmt1;  
23.         DEALLOCATE PREPARE stmt1;  
24.     end if ;  
25.     set done = 0;  
26.     FETCH cur1 INTO cTbl, cCol;  
27. END WHILE;  
28. CLOSE cur1;  
29. END