mysql触发器

#创建销售表

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
('王刚','14111','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
('王刚','14111','2016-08-23');

#触发器2:删除语句

DELIMITER $
CREATE TRIGGER bed
AFTER DELETE ON student FOR EACH ROW
BEGIN
DELETE FROM cj WHERE cj.stu_id=old.stuid;
END $
DELIMITER ;

#触发器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 ;

#创建存储过程

DELIMITER $
CREATE PROCEDURE testa()
BEGIN
        SELECT * FROM student;
        SELECT * FROM cj;
END $
DELIMITER ;

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

#变量的作用范围

DELIMITER $
CREATE PROCEDURE testa3()
BEGIN
       BEGIN
                DECLARE un VARCHAR(32) DEFAULT '';
                SET un='xiaoxiao';
                SELECT username INTO un FROM student WHERE stuid =1;
                SELECT un;
        END;
        BEGIN
                DECLARE un VARCHAR(32) DEFAULT '';
                SET un='xiaoxiao';
                SELECT username INTO un FROM student WHERE stuid =2;
                SELECT un;
        END;
END $
DELIMITER ;

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