Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2298046
  • 博文数量: 473
  • 博客积分: 12252
  • 博客等级: 上将
  • 技术积分: 4307
  • 用 户 组: 普通用户
  • 注册时间: 2007-10-12 10:02
文章分类

全部博文(473)

文章存档

2012年(8)

2011年(63)

2010年(73)

2009年(231)

2008年(98)

分类: Mysql/postgreSQL

2012-02-14 21:07:42

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

查询数据库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;    
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 中所有没有主键的表
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' ;   
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张最大表
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;  
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数据库下所有的数据库
Java代码 复制代码 收藏代码
  1. SELECT SCHEMA_NAME AS 'database' FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0 , 30   
SELECT SCHEMA_NAME AS 'database' FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0 , 30 列出指定数据库中的所有表名称
Java代码 复制代码 收藏代码
  1. SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dj214'  
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dj214' 列出指定数据库下指定表的表结构
Java代码 复制代码 收藏代码
  1. SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT    
  2. FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog' AND table_schema = 'dj214'  
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog' AND table_schema = 'dj214' 一段MYSQL存储过程 [ 删除指定库中所有的空表 ]
Java代码 复制代码 收藏代码
  1.     begin   
  2.     /*局部变量的定义,默认值为空 */  
  3.     declare tmpName varchar(200default '' ;   
  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  
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存储过程 [ 删除指定库下所有表中的空列,即表中的任何一条记录该列都没有值 ]
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  
阅读(2372) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~