MySQL不支持INTERSECT和MINUS，及其替代方法

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

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

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;

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 中所有没有主键的表

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' ;

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张最大表

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;

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数据库下所有的数据库

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

SELECT SCHEMA_NAME AS 'database' FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0 , 30 列出指定数据库中的所有表名称

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

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dj214' 列出指定数据库下指定表的表结构

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
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存储过程 [ 删除指定库中所有的空表 ]

    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

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