MySql--存储过程与触发器的使用

一、存储过程

什么是存储过程？

存储过程可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程的好处？

1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

#使用数据库
USE test;

DELIMITER $
CREATE PROCEDURE testa()
BEGIN
	SELECT * FROM student;#查询两个结果
	SELECT * FROM cj;
END $
DELIMITER ;

#调用存储过程
CALL testa();

#创建存储过程
DELIMITER $
CREATE PROCEDURE test2()
BEGIN
	#声明变量类型
	DECLARE un VARCHAR(32) DEFAULT '';
	#给username变量赋值
	SET un='xiaoxiao';
	#将查询结果赋值给un变量
	SELECT username INTO un FROM student WHERE stuid=1;
	#查询un变量，返回
	SELECT un;
END $
DELIMITER ;

#调用存储过程
CALL test2();

#变量的作用范围
DELIMITER $
CREATE PROCEDURE test3()
BEGIN
	BEGIN
		#声明变量类型
		DECLARE un VARCHAR(32) DEFAULT '';
		#给username变量赋值
		SET un='xiaoxiao';
		#将查询结果赋值给un变量
		SELECT username INTO un FROM student WHERE stuid=1;
		#查询un变量，返回
		SELECT un;
	END;
	BEGIN
		#声明变量类型
		DECLARE un VARCHAR(32) DEFAULT '';
		#给username变量赋值
		SET un='xiaoxiao';
		#将查询结果赋值给un变量
		SELECT username INTO un FROM student WHERE stuid=2;
		#查询un变量，返回
		SELECT un;
	END;
END $
DELIMITER ;

#调用存储过程3
CALL test3();

二、触发器

基本概念：

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（
insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从
DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。 触发器可以查询其他表，而且可以包含复杂的 SQL语句。

作用：

用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。
触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

使用方法：

深刻理解 FOR CREATE TRIGGER 语句的 FOR 关键字之后可以跟 INSERT、UPDATE、DELETE
中的一个或多个，也就是说在其它情况下是不会触发触发器的， 包括 SELECT、TRUNCATE、WRITETEXT、UPDATETEXT。

USE test;
DROP TABLE student,cj;
#创建学生表
CREATE TABLE IF NOT EXISTS student(username VARCHAR(50),
PASSWORD VARCHAR(50),stuid INT PRIMARY KEY AUTO_INCREMENT,
birthday DATE
);

INSERT INTO student(username,PASSWORD,birthday)VALUES
('王二','11111','2016-08-23');

#成绩表
CREATE TABLE IF NOT EXISTS cj(num INT AUTO_INCREMENT PRIMARY KEY,stu_id INT,
stu_name VARCHAR(50),math FLOAT,chinese FLOAT,english FLOAT
);

#触发器
DELIMITER $
CREATE TRIGGER abc
AFTER INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO cj(stu_id,stu_name)VALUES(new.stuid,new.username) ;
END $
DELIMITER ;

#插入数据-执行触发
INSERT INTO student(username,PASSWORD,birthday)VALUES
('李四','2222','2016-08-23');

#触发器
DELIMITER $
CREATE TRIGGER bcd
AFTER DELETE ON student FOR EACH ROW
BEGIN
DELETE FROM cj WHERE cj.stu_id=old.stuid;
END $
DELIMITER ;
DELETE FROM student WHERE stuid=4;

#触发器3：修改语句
DELIMITER $
CREATE TRIGGER def
AFTER UPDATE ON student FOR EACH ROW
BEGIN
UPDATE  cj SET cj.stu_name=old.username WHERE cj.stu_id=old.stuid;
END $
DELIMITER ;