CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8;
USE test;
#创建学生表
CREATE TABLE IF NOT EXISTS student
(
username VARCHAR(50),
PASSWORD VARCHAR(60),
stuid INT PRIMARY KEY AUTO_INCREMENT,
birthday DATE
);
#插入数据
INSERT INTO student(username,PASSWORD,birthday)
VALUES('王二','1111111','2016-08-23');
#查询
SELECT * FROM student;
#成绩表
CREATE TABLE IF NOT EXISTS cj
(
num INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT,
stu_name VARCHAR(50),
math FLOAT,
chinese FLOAT,
english FLOAT
);
SELECT * FROM cj;
#触发器1:插入
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('张三','222222','2016-08-23');
#触发器2: 删除
DELIMITER $
CREATE TRIGGER bcd
AFTER INSERT ON student FOR EACH ROW
BEGIN
DELETE FROM cj.stu_id=old.stuid;
END $
DELIMITER ;
#触发器3:修改语句
DELIMITER $
CREATE TRIGGER def
ALTER INSERT ON student FOR EACH ROW
BEGIN
UPDATE cj SET cj.stu_name=old.username WHERE cj.stu_id=old.stuid;
END $
DELIMITER ;