mysql可参考的查询

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

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

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

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

获取批量修改表列SQL脚本

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

备份SQL查询结果

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