檢視、儲存過程以及許可權控制練習

xbhog發表於2021-01-13

檢視、儲存過程以及許可權控制

導讀:

該文章為檢視、儲存過程、使用者許可權練習;

如果有不對的地方歡迎指出與補充;

該基礎練習基於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("計算機導論"); 

img

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

總結:

期末考試結束,開始陸續更新。。。

以上練習如果有不對的地方歡迎指正。。。。

相關文章