1、创建一个学生表、教师表
CREATE TABLE `student` (
`studentID` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`teacherID` int(11) NOT NULL COMMENT '教师ID',
`studentName` varchar(25) NOT NULL COMMENT '学生姓名',
`studentAge` int(2) NOT NULL COMMENT '学生年龄',
PRIMARY KEY (`studentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `teacher` (
`teacherID` int(11) NOT NULL AUTO_INCREMENT COMMENT '教师ID',
`teacherName` varchar(25) NOT NULL COMMENT '教师姓名',
`teacherAge` int(2) NOT NULL COMMENT '教师年龄',
PRIMARY KEY (`teacherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、向表中插入数据
3、创建视图
CREATE VIEW `student_teacher` AS
SELECT s.studentName, t.teacherName
FROM student s, teacher t
where s.teacherID = t.teacherID
4、使用视图
select * from student_teacher
5、创建存储过程——每一次执行该过程,都向s_t表中插入所有的学生、教师信息
首先创建s_t表
CREATE TABLE `s_t` (
`s_t_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`studentID` int(11) NOT NULL COMMENT '学生ID',
`studentName` varchar(25) NOT NULL COMMENT '学生姓名',
`studentAge` int(2) NOT NULL COMMENT '学生年龄',
`teacherID` int(11) NOT NULL COMMENT '教师ID',
`teacherName` varchar(25) NOT NULL COMMENT '教师姓名',
`teacherAge` int(2) NOT NULL COMMENT '教师年龄',
PRIMARY KEY (`s_t_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建存储过程
BEGIN
/*定义变量 开始*/
DECLARE v_no_more INT DEFAULT 0;
DECLARE v_error INT DEFAULT 0;
DECLARE v_studentID INT DEFAULT 0;
DECLARE v_studentName VARCHAR(25) DEFAULT "";
DECLARE v_studentAge INT DEFAULT 0;
DECLARE v_teacherID INT DEFAULT 0;
DECLARE v_teacherName VARCHAR(25) DEFAULT "";
DECLARE v_teacherAge INT DEFAULT 0;
/*定义变量 结束*/
DECLARE cur_s_t CURSOR FOR
SELECT
s.studentID,
s.studentName,
s.studentAge,
t.teacherID,
t.teacherName,
t.teacherAge
FROM student s
INNER JOIN teacher t
WHERE s.teacherID = t.teacherID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error = 1;
-- 开启事务
OPEN cur_s_t;
FETCH cur_s_t INTO v_studentID,v_studentName,v_studentAge,v_teacherID,v_teacherName,v_teacherAge;
WHILE v_no_more != 1 DO
INSERT INTO s_t (studentID,studentName,studentAge,teacherID,teacherName,teacherAge) VALUES (v_studentID,v_studentName,v_studentAge,v_teacherID,v_teacherName,v_teacherAge);
FETCH cur_s_t INTO v_studentID,v_studentName,v_studentAge,v_teacherID,v_teacherName,v_teacherAge;
END WHILE;
CLOSE cur_s_t;
-- 结束事务
-- 事务处理
IF v_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END