mysql视图存储_Mysql 视图、存储过程以及权限控制

导读：

该文章为视图、存储过程、用户权限练习；

如果有不对的地方欢迎指出与补充；

该基础练习基于MySQL5.0以上；

语句格式：

1. 视图格式：

create view view_name[列名，列名.....] as select 子查询 with check option

其中with check option 作用是：在视图进行update\delete\insert操作时要保证更新、插入、删除的行满足视图定义中的谓词公式(既子查询中的条件表达式)；

视图操作如正常表一样可以增删改查。

2.存储过程格式：

create procedure pro_name(in 参数名 类型，out 参数名 类别)

begin

过程体

end

3.用户权限格式：

create user 用户名 indentified by '密码'

--设置权限

grant 权限 on 数据库名.表名 to 用户@登录主机 identified by "用户密码"；

视图：

(1)建立视图IS_STUDENT,视图中包含信息系全体学生的基本信息。(要求限制更新)

Create view IS_STUDENT as select * from student

Where sdept=’信息系’ with check option;

(2)建立视图CJ_STUDENT,视图中包含所有成绩不及格的学生的学号，姓名，课程名，成绩。

Create view CJ_STUDENT as select student.sno,sname,cname,grade from student,sc,course

Where student.sno=sc.sno and sc.cno=course.cno and (grade <60 or grade is null);

(3)建立视图AVG_CJ，视图包括学生的学号以及他们的平均成绩，按成绩降序排列。

Create view AVG_CJ as select sno ,avg(grade)

from sc where grade is null group by sno order by avg(grade) desc;

(4)根据视图IS_STUDENT，修改该视图中年龄都增加1。观察基本表Student中相应的哪些数据发生了变化。

Update IS_STUDENT set sage=sage+1;

select * from IS_STUDENT;

(5)在视图IS_STUDENT中插入新的记录，学号为9531103，姓名为张玉，女，21岁，计算机系。(是否能执行？若不行，为什么？)

insert into IS_STUDENT values('9531103','张玉','女',21,'计算机系');

不能执行,因为,违反了视图创建规则，计算机系不满足sdept中的信息系要求；

(6)根据视图CJ_STUDENT创建视图CJ_TJ，包含课程名，不及格人数，不及格人姓名列表，按照不及格人数降序排列。

Create view CJ_TJ as select cname,count(*),GROUP_CONSCAT(sname SEPARATOR ',') from CJ_STUDENT group by cname order by count(*) desc;

存储过程：

(1)建立存储过程course_grade：根据课程名参数，查询该课程的成绩表，包括学号，姓名，成绩，按学号升序排序。

CREATE PROCEDURE course_grade (in course_name varchar(20))

SELECT student.sno,sname,grade from student,sc,course WHERE student.sno=sc.sno and sc.cno =course.cno and cname=course_name ORDER BY sno;

DROP PROCEDURE course_grade;

CALL course_grade("计算机导论");

(2)建立存储过程search_grade：根据姓名和课程名参数，查询该学生相应的课程成绩，若存在成绩，则返回成绩值，否则返回NULL。

create procedure search_grade(in student_name char(10), in course_name varchar(20), out re_grade smallint, out rname char(10))

Begin

If( not exists

(

select * from student, sc, course

where student.sno = sc.sno

and sc.cno = course.cno

and sname = student_name

and cname = course_name) )

then set re_grade = null;

else

select grade into re_grade from student, sc, course

where student.sno = sc.sno

and sc.cno = course.cno

and sname = student_name

and cname = course_name ;

end if;

end ;

#创建成功

-- 执行存储过程

call search_grade('王大力','数据库原理',@grade, @rname);

select @grade, @rname;

call search_grade('王大力','高等数学',@grade, @rname);

select @grade, @rname;

(3)创建存储过程take_course：根据参数学号和课程号，完成选课功能(要求能够根据选课人数加以限制)。

要求：

§ 检查该学生选课门数是否超过3门，若超过，不插入记录；

§ 检查该课程选课人数是否已满(在course表里添加一个属性列，记录课程人数限制)；

ü 选课人数已满：不插入记录；

ü 选课人数未满且未插入过：完成数据插入操作；

ü 选课人数未满，已插入过：不插入记录

§ 最后返回执行结果信息，例如：

ü 超过3门，返回-1；

ü 选课人数已满。返回0；

ü 选课人数未满且未插入过：返回1；

ü 选课人数未满已插入过：返回2

create procedure take_course (in student_no char(7), in course_no char(3), out i tinyint )

begin

if(select count(*) from sc where sno= student_no) >= 3

then set i = 1;

else

if(select count(*) from sc where cno = course_no) >= 5

then set i= 0;

else

if exists (select * from sc where sno = student_no and cno = course_no)

then set i = 2;

else

insert into sc(sno, cno) values(student_no, course_no);

set i = 1;

end if ;

end if ;

end if ;

end ;

权限控制：

(1)创建超级用户admin，具有对所有数据库的所有访问权限，仅限本机连接(密码自行设置)，并验证用户和权限设置是否正确。

CREATE user admin @localhost

IDENTIFIED by '123';

(2)创建管理员teacher_liu，具有对SC表、IS_STUDENT视图(实验5创建)的增删改查权限，并且具有授权的权限，允许从任意位置连接(密码自行设置)，并验证用户和权限设置是否正确。

CREATE USER teacher_liu @'%' IDENTIFIED by '123456';

GRANT SELECT ,INSERT, UPDATE, DELETE on studentTable.sc TO teacher_liu @'%' WITH GRANT OPTION;

以上内容希望帮助到大家，很多PHPer在进阶的时候总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道该从那里入手去提升，对此我整理了一些资料，包括但不限于：分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家，官方群点击此处进入。