mysql常用DML相关sql

#获取所有表名

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA = '数据库名'

#获取添加所有索引的语句
SELECT
    CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 'ADD ',
        IF(NON_UNIQUE = 1,
            CASE UPPER(INDEX_TYPE)
                WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                ELSE CONCAT('INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE)
            END,
            IF(UPPER(INDEX_NAME) = 'PRIMARY',
                CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                CONCAT('UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE)
            )
        ),
        '(', GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'SQL'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'svmain' and UPPER(INDEX_NAME) <> 'PRIMARY'
GROUP BY INDEX_TYPE , NON_UNIQUE , TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

#获取删除所有索引的语句
SELECT
    CONCAT('ALTER TABLE `', TABLE_NAME, '` DROP INDEX `',INDEX_NAME,'`;') AS 'SQL'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'svmain' and UPPER(INDEX_NAME) <> 'PRIMARY'
GROUP BY INDEX_TYPE, NON_UNIQUE, TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

#获取添加所有外键的语句
select concat('ALTER TABLE `', TABLE_NAME, '` add constraint `', CONSTRAINT_NAME, '` FOREIGN KEY (',GROUP_CONCAT(concat('`', COLUMN_NAME, '`')),') references `',referenced_table_name,'`(`',referenced_column_name,'`);') as `SQL`
from  information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'svmain'
AND referenced_table_name IS NOT NULL
GROUP BY TABLE_NAME , CONSTRAINT_NAME,referenced_table_name,referenced_column_name
ORDER BY TABLE_NAME ASC , CONSTRAINT_NAME ASC;

#获取删除所有外键的sql语句
SELECT concat('ALTER TABLE `', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;') AS 'SQL'
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'svmain'
AND referenced_table_name IS NOT NULL;

#数据库升级存储过程

use `svmain`;
DROP PROCEDURE IF EXISTS `svmain_update`;
DELIMITER $$
CREATE PROCEDURE `svmain_update`()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='svmain' AND TABLE_NAME='attendrecord' AND INDEX_NAME='AK_ProjectWorkerId_RecordDate') THEN
    ALTER TABLE `attendrecord` ADD UNIQUE INDEX `AK_ProjectWorkerId_RecordDate` USING BTREE(`ProjectWorkerId`, `RecordDate`);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.key_column_usage WHERE CONSTRAINT_SCHEMA='svmain' AND TABLE_NAME='attendrecord' AND CONSTRAINT_NAME='FK_attendrecord_ProjectWorker') THEN
    ALTER TABLE `attendrecord` add constraint `FK_attendrecord_ProjectWorker`  FOREIGN KEY (`ProjectWorkerId`) references `projectworker`(`Id`);
  END IF;
END$$
DELIMITER ;
CALL `svmain_update`();
DROP PROCEDURE IF EXISTS `svmain_update`;