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.teacherNameFROM student s, teacher twhere 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

?