视图、存储过程以及权限控制练习

.NET 云原生架构师训练营(模块二 基础巩固 RabbitMQ Masstransit 详解)–学习笔记

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

导读:

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

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

该基础练习基于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("计算机导论"); 

DevOps,CI,CD,自动化简单介绍

(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;

总结:

期末考试结束,开始陆续更新。。。

以上练习如果有不对的地方欢迎指正。。。。

MySQL的架构

给TA买糖
共{{data.count}}人
人已赞赏
经验教程

k8s之RBAC授权模式

2021-1-13 22:30:00

经验教程

.NET 云原生架构师训练营(模块二 基础巩固 RabbitMQ Masstransit 详解)--学习笔记

2021-1-13 22:52:00

⚠️
免责声明:根据《计算机软件保护条例》第十七条规定“为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬。”您需知晓本站所有内容资源均来源于网络,仅供用户交流学习与研究使用,版权归属原版权方所有,版权争议与本站无关,用户本人下载后不能用作商业或非法用途,需在24个小时之内从您的电脑中彻底删除上述内容,否则后果均由用户承担责任;如果您访问和下载此文件,表示您同意只将此文件用于参考、学习而非其他用途,否则一切后果请您自行承担,如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。 本站为个人博客非盈利性站点,所有软件信息均来自网络,所有资源仅供学习参考研究目的,并不贩卖软件,不存在任何商业目的及用途,网站会员捐赠是您喜欢本站而产生的赞助支持行为,仅为维持服务器的开支与维护,全凭自愿无任何强求。本站部份代码及教程来源于互联网,仅供网友学习交流,若您喜欢本文可附上原文链接随意转载。
无意侵害您的权益,请发送邮件至 momeis6@qq.com 或点击右侧 私信:momeis 反馈,我们将尽快处理。
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索