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

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

导读:

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

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

该基础练习基于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 studentWhere 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

(0)

相关推荐

  • 《数据库》实验报告2

    回复关键词:实验报告 更多实验报告内容分享 往期 <数据库>实验报告1 实验5 2.列出同时选修了'数学'和'数据库'的学生学号.姓名: select student.sno,sname ...

  • 《数据库》实验报告1

    往期:<多元回归分析>实验报告 实验四 四.1.列出所有不姓王的学生信息: select * from student where sname not like '王%' 2.列出姓&qu ...

  • 手把手教你 SQL 多表查询

    本文通过经典的学生-课程模式 S-T 数据库带大家学习 SQL 常用的多表查询 : - 学生表:Student( Sno,Sname,Ssex,Sage,Sdept ) - 课程表:Course( C ...

  • 权限控制(delphi actionlist)

    在软件开发中,为软件加入权限控制功能,使不同的用户有不同的使用权限,是非常重要的一项功能, 由其在开发数据库方面的应用,这项功能更为重要. 但是,要为一个应用加入全面的权限控制功能,又怎样实现呢? 大 ...

  • DRF之访问权限控制和访问频率控制(节流)

    权限控制 前言 用户验证用户权限,根据不同访问权限控制对不同内容的访问. 建议了解视图.token验证的内容. 使用流程 自定义访问权限类,继承BasePermission,重写has_permiss ...

  • NET CORE API权限控制之JWT的创建和引用

    在我们的接口调用中,都需要配置权限控制,下面介绍下在ASP NET CORE下使用JWT的步骤: 1.创建鉴权项目 由于鉴权并不需要每次调用都鉴权,所以我们可以自己创建一个项目工程作为鉴权中心,用户拿 ...

  • elasticsearch7.8权限控制和规划

    由于在版本7开始,x-pack可以免费使用了,但是权限控制免费的不够细,但是控制到索引级别都基本够用了.付费的可以体验更细致的权限控制.本文的基础是已经有了es集群的基础上进行的.官网:https:/ ...

  • delphi 权限控制(delphi TActionList方案)

    delphi 权限控制(delphi TActionList方案) 作者:admin 来源: 日期:2011/8/11 9:35:22 人气:2043 标签: delphi 权限控制(delphi T ...

  • 利用Excel和这个函数,对PowerBI报告进行动态的权限控制

    关于PowerBI的行级安全性,之前已经介绍过怎么用,如果你还没有看过,请先看一下这两篇文章: 利用Power BI行级安全性,限制用户访问权限 Power BI行级安全性三种常见的角色规则设置  有 ...

  • Power BI页面级权限控制,其实只需要这3步

    平时被经常问到的一个问题,就是PowerBI能不能按页面进行权限控制?比如A用户只允许查看报告的第2页,B用户只能查看第6页等,PowerBI本身是没有这个功能的,行级安全性(RLS)也是只能限制数据 ...

  • centos的安装与配置,Linux下基本命令、权限控制,解压缩文件以及软件的安装与卸载

    centos安装与网络配置 关机:shutdown -h now 重启:shutdown -r now 或 reboot linux目录结构与操作命令 使用ls命令查看目录结构 目录查看: ls [- ...

  • linux入门系列9--用户管理及文件权限控制

    ​前面文章分享了Linux下常用命令以及Shell编程相关知识,本节继续学习Linux用户管理及文件权限控制. ​Linux是多用户多任务操作系统,具有很好的稳定性和安全性.既然是多用户,那就意味着多 ...