mysql可参考

• 获取批量修改列为大写SQL脚本

 

SELECT
    concat( 'alter table ', TABLE_NAME, ' change column ', COLUMN_NAME, '  ', UCASE( COLUMN_NAME ), '  ', COLUMN_TYPE,' ',EXTRA,' comment "',COLUMN_COMMENT,'";' ) AS '修改脚本'
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = '数据库名'

 

• 获取批量修改表为小写SQL脚本

 

SELECT
    concat( 'alter table ', TABLE_NAME, ' rename to ', LCASE( TABLE_NAME ), ';' ) AS ‘修改脚本’
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = '数据库名'

 

• 获取批量修改表列SQL脚本

SELECT
    concat( 'alter table ', TABLE_NAME, ' modify ', COLUMN_NAME,' decimal(18,3);' ) AS '修改脚本'
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = 'meritdata' AND (COLUMN_TYPE LIKE 'decimal%' or COLUMN_TYPE LIKE 'float%') AND COLUMN_TYPE LIKE '%,2)' AND TABLE_NAME in (
        'I_AM_TABLE_NAME'
        )

 

• 备份SQL查询结果

 

mysql -uroot -p -e 'select * from testdb.info'>/opt/supwang/wzz.sql

 

• 查询数据库表索引(这里我排除了主键)

select TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
from
information_schema.statistics
where
table_schema='库名' and INDEX_NAME!='PRIMARY'
GROUP BY TABLE_NAME, INDEX_NAME;

  

• 存储过程（查询出记录，然后拼接，union all）

       例子：

select
    'syx0919lb_st1' EC,
    'syx0919lb_st1' EN,
    'syx0919lb' MC,
    'syx0919lb' MN,
    count(*) N
from
    syx0919lb_st1
where
    FLOWSTATUS = 'DONE'
union all
select
    'syx0919lb_st1' EC,
    'syx0919lb_st1' EN,
    'syx0919lb' MC,
    'syx0919lb' MN,
    count(*) N
from
    syx0919lb_st1
where
    FLOWSTATUS = 'DONE'

 

　　存储过程：

DROP PROCEDURE IF EXISTS cloud_wzz.TABLE_DATA_NUM;

DELIMITER $$
$$
CREATE PROCEDURE cloud_wzz.TABLE_DATA_NUM()
begin
    DECLARE c_ec,c_en,c_mc,c_mn varchar(250);
    -- 拼接union all的辅助
    DECLARE i INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE exeSql longtext DEFAULT '';
    -- 定义游标
    DECLARE cur CURSOR FOR SELECT a.code ec,a.name en,b.CODE mc,b.NAME bn FROM mdm_model_entity a,mdm_model b WHERE a.MASTER ='1' AND b.FLOWSTATUS='DONE' AND a.MODELID =b.ID;
    -- 游标循环结束后，设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    -- 打开游标
    OPEN cur;
    entity_model_loop:LOOP
        FETCH cur INTO c_ec,c_en,c_mc,c_mn;
        IF done=1 THEN
            LEAVE entity_model_loop;
        END IF;
        -- 查询是否存在当前表名，赋值个数给cnt
        SELECT COUNT(1) FROM information_schema.tables WHERE table_name = c_ec INTO @cnt;
        -- 判断表名是否存在，当表不存在时直接跳过，不判断的话会报错
        IF @cnt <> 0 THEN
            SET @selSql=CONCAT("SELECT '",c_ec,"' EC,'",c_en,"' EN,'",c_mc,"' MC,'",c_mn,"' MN,COUNT(*) N FROM ",c_ec," WHERE  FLOWSTATUS ='DONE'");
            IF i <> 0 THEN
                SET @selSql=CONCAT(' union all ',@selSql);
            END IF;
            SET exeSql=CONCAT(exesql,@selSql);
            SET i=i+1;
        END IF;
    END LOOP entity_model_loop;
    -- 关闭游标
    CLOSE cur;
    -- 赋值变量
    SET @v_s=exeSql;
    -- 预定义sql语句
    PREPARE stmt FROM @v_s;
    -- 执行预定义sql语句
    EXECUTE stmt;
    -- 释放资源
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;