SQL存储过程

1.DDL

1.1 建表

DELIMITER ;;
	DROP PROCEDURE IF EXISTS UPPRDE;;
	BEGIN
		CREATE TABLE IF NOT EXISTS table_name (
			'IID' decimal(12,0) NOT NULL COMMENT ''
		) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';
	END;;
DELIMITER ;
CALL UPPRDE();

1.2 新增字段

DELIMITER ;;
	DROP PROCEDURE IF EXISTS UPPRDE;;
	BEGIN
		IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema= (select database())) and TABLE_NAME = 'table_name' AND COLUMN_NAME = 'c_name ' THEN
			ALTER TABLE table_name ADD c_name TINYINT(1);
		END IF;
	END;;
DELIMITER ;
CALL UPPRDE();

1.3 新增索引

DELIMITER ;;
	DROP PROCEDURE IF EXISTS UPPRDE;;
	BEGIN
		IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema= (select database())) and TABLE_NAME = 'table_name' AND COLUMN_NAME = 'c_name ' THEN
			ALTER TABLE table_name ADD index A_index(IID);
		END IF;
	END;;
DELIMITER ;
CALL UPPRDE();

1.4 修改字段

DELIMITER ;;
	DROP PROCEDURE IF EXISTS UPPRDE;;
	BEGIN
		ALTER TABLE table_name MODIFY c_name TINYINT (1);
	END;;
DELIMITER ;
CALL UPPRDE();

1.5 修改字段（重命名）

DELIMITER ;;
	DROP PROCEDURE IF EXISTS UPPRDE;;
	BEGIN
		ALTER TABLE table_name CHANGE c_name new_name TINYINT (1);
	END;;
DELIMITER ;
CALL UPPRDE();

2.DML

2.1 插入数据

DELIMITER ;;
	DROP PROCEDURE IF EXISTS UPPRDE;;
	BEGIN
		INSERT IGNORE INTO table_name (IID, AAA) VALUES (1, 'AAA');   // 检查主键，存在忽略
		REPLACE INTO TableName (name, age) values('aa', '11'); // 检查主键，删除原有的，新增一条
		insert into test values('unique', '654321') on duplicate key update col1 = 'update_unique'; // 检查主键，对原有数据做替换
		SELECT IVALUE INTO table_name1 FROM table_name2 WHERE a = 'a';
	END;;
DELIMITER ;
CALL UPPRDE();

2.2 更新数据（数据已经存在）

DELIMITER ;;
	DROP PROCEDURE IF EXISTS UPPRDE;;
	BEGIN
		UPDATE table_name SET IVALUE = 'AAA' WHERE a = 'a';
	END;;
DELIMITER ;
CALL UPPRDE();