1 建表命令
DROP TABLE IF EXISTS test;
CREATE TABLE `test` (
`versionid` int(11) NOT NULL AUTO_INCREMENT COMMENT '版本ID',
`name` varchar(25) NOT NULL COMMENT '版本名称',
`version_name` varchar(50) NOT NULL COMMENT '版本号',
`version_code` int(11) NOT NULL COMMENT '版本编号',
`version_url` varchar(256) NOT NULL COMMENT 'APK下载地址',
`description` varchar(256) NOT NULL COMMENT '版本描述',
`flag` tinyint(4) NOT NULL COMMENT '是否强制升级 0:否 1:是 默认 0',
`state` tinyint(4) NOT NULL DEFAULT '0' COMMENT '版本状态 0:下线 1:上线 2:删除 默认 :0',
`updater` varchar(10) NOT NULL DEFAULT '管理员' COMMENT '最后修改人',
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更改时间',
PRIMARY KEY (`versionid`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COMMENT='测试表的备注';
2 删除表字段
ALTER TABLE test DROP add_field;
SELECT IF (outNum.num >= 1, TRUE, FALSE) AS nnum FROM
(
SELECT count(*) num FROM information_schema. COLUMNS WHERE table_name = 'test' AND column_name = 'add_field'
) outNum;
3 新增add_field字段
DROP PROCEDURE if EXISTS proc_tempPro;
SET @table_name1= 'test';
SET @column_name1= 'add_field';
CREATE PROCEDURE proc_tempPro()
BEGIN
SELECT count(*) INTO @count FROM information_schema. COLUMNS WHERE table_name = @table_name AND column_name = @column_name1;
IF @count >0 THEN
ALTER TABLE test DROP add_field ;
END IF;
END;
CALL proc_tempPro;
DROP PROCEDURE IF EXISTS proc_tempPro;
ALTER TABLE test ADD add_field VARCHAR (10) NOT NULL DEFAULT '默认值' COMMENT '增加字段备注';
4 索引操作
ALTER TABLE member_health ADD INDEX index_family_member_id(family_member_id) ;
DROP INDEX family_member_id_key on member_health;
5 存储过程
5.1 查询存储过程
show PROCEDURE STATUS;
5.2 创建存储过程 (新增某一个字段)
DROP PROCEDURE if EXISTS proc_tempPro;
SET @table_name:= 'test';
SET @column_name= 'add_field';
CREATE PROCEDURE proc_tempPro()
BEGIN
SELECT count(*) INTO @count FROM information_schema. COLUMNS WHERE table_name = @table_name AND column_name = @column_name;
IF @count >0 THEN
ALTER TABLE test DROP add_field ;
END IF;
END;
CALL proc_tempPro;
5.3 删除存储过程
DROP PROCEDURE IF EXISTS proc_tempPro;