Mysql私有增强性命令小记

 

从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;  
• 查询数据库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;  
• 查看MYSQL数据库下所有的数据库
  SELECT SCHEMA_NAME AS 'database' FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0 , 30  
• 列出指定数据库中的所有表名称
   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' 
• 一段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 

 

 

 

• 一段MYSQL存储过程[把指定库中的所有表名改为大写]

      其实这段代码完全没必要，默认在linux下mysql表名是区分大小写的，如果想让linux下的mysql不区分表名大小写，修改办法是：
      vim /etc/my.cnf
      在[mysqld]下加入一行：
      lower_case_table_names=1

      这句话就表示不区分大小写表名。
      重启一下mysql服务再试试看看。
      这是最简便的修改办法，但是这种方法也许不是很科学，建议还是写程序的时候就详细规范一下，注意区分大小写。

      同时给出一段MYSQL存储过程，功能为把指定库中所有的表名改为全部大写
      BEGIN DECLARE done INT DEFAULT 0; DECLARE cTbl VARCHAR(64); DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='lyblog'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET @sqlDrop=''; OPEN cur1; FETCH cur1 INTO cTbl; WHILE done = 0 DO SET @sqlDrop=CONCAT('alter table ',cTbl, ' rename to ',upper(cTbl)); PREPARE stmt1 FROM @sqlDrop; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; FETCH cur1 INTO cTbl; END WHILE; CLOSE cur1; END
  

  MYSQL删除指定库下所有的外键

  SELECT DISTINCT table_name, constraint_name FROM information_schema.key_column_usage WHERE constraint_schema ='kxschool' AND referenced_table_name IS NOT NULL;