MySQL视图、存储过程复习

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