DELIMITER //
 
CREATE PROCEDURE CreateTableIfNotExists()
BEGIN
    IF NOT EXISTS (
        SELECT 1 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() AND table_name = 'NewTable'
    ) THEN
        SET @sql = 'CREATE TABLE NewTable (
            Id INT PRIMARY KEY AUTO_INCREMENT,
            Name VARCHAR(100) NOT NULL
        )';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT '數(shù)據(jù)表 NewTable 已新增' AS Message;
    ELSE
        SELECT '數(shù)據(jù)表 NewTable 已存在' AS Message;
    END IF;
END //
 
DELIMITER ;

DELIMITER //
 
CREATE PROCEDURE CreateViewIfNotExists()
BEGIN
    IF NOT EXISTS (
        SELECT 1 
        FROM information_schema.views 
        WHERE table_schema = DATABASE() AND table_name = 'NewView'
    ) THEN
        SET @sql = 'CREATE VIEW NewView AS SELECT Id, Name FROM NewTable';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT '視圖 NewView 已新增' AS Message;
    ELSE
        SELECT '視圖 NewView 已存在' AS Message;
    END IF;
END //
 
DELIMITER ;

DELIMITER //
 
CREATE PROCEDURE AddFieldIfNotExists()
BEGIN
    IF NOT EXISTS (
        SELECT 1 
        FROM information_schema.columns 
        WHERE table_schema = DATABASE() AND table_name = 'NewTable' AND column_name = 'Description'
    ) THEN
        SET @sql = 'ALTER TABLE NewTable ADD COLUMN Description TEXT';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT '字段 Description 已新增到 NewTable' AS Message;
    ELSE
        SELECT '字段 Description 已存在' AS Message;
    END IF;
END //
 
DELIMITER ;

DELIMITER //
 
CREATE PROCEDURE CreateIndexIfNotExists()
BEGIN
    IF NOT EXISTS (
        SELECT 1 
        FROM information_schema.statistics 
        WHERE table_schema = DATABASE() AND table_name = 'NewTable' AND index_name = 'idx_name'
    ) THEN
        SET @sql = 'CREATE INDEX idx_name ON NewTable (Name)';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT '索引 idx_name 已新增到 NewTable' AS Message;
    ELSE
        SELECT '索引 idx_name 已存在' AS Message;
    END IF;
END //
 
DELIMITER ;

DELIMITER //
 
CREATE PROCEDURE AddUniqueConstraintIfNotExists()
BEGIN
    IF NOT EXISTS (
        SELECT 1 
        FROM information_schema.table_constraints 
        WHERE table_schema = DATABASE() AND table_name = 'NewTable' AND constraint_name = 'uc_name'
    ) THEN
        SET @sql = 'ALTER TABLE NewTable ADD CONSTRAINT uc_name UNIQUE (Name)';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT '唯一約束 uc_name 已新增到 NewTable' AS Message;
    ELSE
        SELECT '唯一約束 uc_name 已存在' AS Message;
    END IF;
END //
 
DELIMITER ;

DELIMITER //
 
CREATE PROCEDURE CreateStoredProcedureIfNotExists()
BEGIN
    IF NOT EXISTS (
        SELECT COUNT(*) 
        FROM information_schema.routines 
        WHERE routine_schema = DATABASE() AND routine_name = 'MyNewProcedure'
    ) THEN
        SET @sql = 'CREATE PROCEDURE MyNewProcedure() BEGIN SELECT ''Hello, World!''; END';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT '存儲(chǔ)過(guò)程 MyNewProcedure 已新增' AS Message;
    ELSE
        SELECT '存儲(chǔ)過(guò)程 MyNewProcedure 已存在' AS Message;
    END IF;
END //
 
DELIMITER ;

DELIMITER //
 
CREATE PROCEDURE CreateEventIfNotExists()
BEGIN
    IF NOT EXISTS (
        SELECT COUNT(*) 
        FROM information_schema.events 
        WHERE event_schema = DATABASE() AND event_name = 'MyNewEvent'
    ) THEN
        SET @sql = 'CREATE EVENT MyNewEvent ON SCHEDULE EVERY 1 DAY DO BEGIN SELECT ''定時(shí)事件執(zhí)行''; END';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT '定時(shí)事件 MyNewEvent 已新增' AS Message;
    ELSE
        SELECT '定時(shí)事件 MyNewEvent 已存在' AS Message;
    END IF;
END //
 
DELIMITER ;

CALL CreateTableIfNotExists();
CALL CreateViewIfNotExists();
CALL AddFieldIfNotExists();
CALL CreateIndexIfNotExists();
CALL AddUniqueConstraintIfNotExists();
CALL CreateStoredProcedureIfNotExists();
CALL CreateEventIfNotExists();