------------------------------------------------------------------------------------------------------------
建表语句
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 插入前现根据关键索引查询是否有该数据,防止重复插入
------------------------------------------------------------------------------------------------------------