mysql常用增删改脚本 DBA可重复执行.sql文件

------------------------------------------------------------------------------------------------------------

建表语句

use dxt;

CREATE TABLE if not exists `tent_config_desc` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `ENT_ID` bigint(20) DEFAULT NULL COMMENT '企业id',
  `ADMIN_ID` int(10) NOT NULL COMMENT '管理员主键id',
  `Pic` varchar(300) DEFAULT NULL COMMENT '企业头像',
  `ADD_TIME` datetime DEFAULT NULL COMMENT '录入时间',
  `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
  `REMARK` varchar(200) DEFAULT NULL COMMENT '备注',
  `ORDER_BY` int(11) DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1975 DEFAULT CHARSET=utf8 COMMENT='企业管理员头像相关信息';

1 ENGINE=InnoDB AUTO_INCREMENT=1975 DEFAULT CHARSET=utf8 id索引从1975开始递增
2 if not exists 判断是否存在，是可重复执行的关键
------------------------------------------------------------------------------------------------------------

修改表字段

use 库名;
DROP PROCEDURE IF EXISTS add_tb_column;
DELIMITER $$
CREATE PROCEDURE add_tb_column()
    BEGIN
		DECLARE  CurrentDatabase VARCHAR(100);
		SELECT DATABASE() INTO CurrentDatabase;
        IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS  WHERE   TABLE_SCHEMA=CurrentDatabase
            AND TABLE_NAME='user_info'
            AND COLUMN_NAME='age' )
		THEN
          ALTER TABLE 'user_info' ADD COLUMN 'age' VARCHAR(50) after 'user_id';
        END IF;
  END;

CALL add_tb_column;

1 PROCEDURE 用这个字段必须用 CALL 来执行
2 DELIMITER // 分段执行 DELIMITER ; 结尾不用 // 用;
------------------------------------------------------------------------------------------------------------

插入数据

use sg_message;

delete from template_info where TEMPLATE_NAME ='app-signet-flow-ccsend' and STATUS = '0';

INSERT INTO `template_info`(`TEMPLATE_NAME`, `GROUP_ID`, `TEMPLATE_TEXT`, `TEMPLATE_URL`, `TEMPLATE_DESC`, `STATUS`) VALUES ('app-signet-flow-ccsend', 4, '${approveName}有盖章任务抄送了您，请关注。', 'http://www.yhxy.cn', '抄送提醒', 0);

1 插入前现根据关键索引查询是否有该数据，防止重复插入
------------------------------------------------------------------------------------------------------------