MySql - 存储过程以及视图

视图

-- 创建视图
CREATE VIEW studentView AS ( 
		 SELECT sid, 
		        sname, 
				caption 
		   FROM studentmanagement.student, 
			    studentmanagement.class 
		  WHERE student.class_id = class.cid );
-- 查看视图结构
DESC studentView;
-- 查看视图的创建信息
SHOW CREATE VIEW studentView;
-- 查看视图的数据
SELECT * FROM studentView;
-- 修改视图
ALTER VIEW studentView as 
     SELECT sid, 
		    sname, 
			caption ,
			cid
	   FROM studentmanagement.student, 
		    studentmanagement.class 
	  WHERE student.class_id = class.cid ;
-- 删除视图
DROP VIEW studentView;

存储过程

-- 无参
delimiter $
CREATE PROCEDURE selectTable()
BEGIN
SELECT * FROM student;
SELECT * FROM teacher;
END$

CALL selectTable();

-- IN 传入参数
delimiter $
CREATE PROCEDURE selectNum(IN cname VARCHAR(3))
BEGIN
SELECT * FROM student
WHERE sname = cname;
END$

CALL selectNum('理解');

-- 返回一个结果
delimiter $
CREATE PROCEDURE selectCid(OUT num INT(3))
BEGIN
SELECT COUNT(*) INTO num FROM student;
END$

CALL selectCid(@res);
SELECT @res;

-- 传入一个数，返回一个结果
delimiter $
CREATE PROCEDURE selectName(INOUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM score
WHERE sid = num;
END$

SET @res = 2;
CALL selectName(@res);
SELECT @res;