視圖、存儲(chǔ)過程以及權(quán)限控制
導(dǎo)讀：
該文章為視圖、存儲(chǔ)過程、用戶權(quán)限練習(xí)；
如果有不對(duì)的地方歡迎指出與補(bǔ)充；
該基礎(chǔ)練習(xí)基于MySQL5.0以上；
語句格式：
1. 視圖格式：
create view view_name[列名，列名.....] as select 子查詢 with check option
其中with check option 作用是：在視圖進(jìn)行update\delete\insert操作時(shí)要保證更新、插入、刪除的行滿足視圖定義中的謂詞公式（既子查詢中的條件表達(dá)式）；
視圖操作如正常表一樣可以增刪改查。
2.存儲(chǔ)過程格式：
create procedure pro_name(in 參數(shù)名 類型，out 參數(shù)名 類別）begin
過程體 end
3.用戶權(quán)限格式：
create user 用戶名 indentified by '密碼'--設(shè)置權(quán)限grant 權(quán)限 on 數(shù)據(jù)庫名.表名 to 用戶@登錄主機(jī) identified by "用戶密碼"；
視圖：
（1）建立視圖IS_STUDENT,視圖中包含信息系全體學(xué)生的基本信息。（要求限制更新）
Create view IS_STUDENT as select * from studentWhere sdept=’信息系’ with check option;
（2）建立視圖CJ_STUDENT,視圖中包含所有成績不及格的學(xué)生的學(xué)號(hào)，姓名，課程名，成績。
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，視圖包括學(xué)生的學(xué)號(hào)以及他們的平均成績，按成績降序排列。
Create view AVG_CJ as select sno ,avg(grade)
from sc where grade is null group by sno order by avg(grade) desc;
（4）根據(jù)視圖IS_STUDENT，修改該視圖中年齡都增加1。觀察基本表Student中相應(yīng)的哪些數(shù)據(jù)發(fā)生了變化。
Update IS_STUDENT set sage=sage+1;
select * from IS_STUDENT;
（5）在視圖IS_STUDENT中插入新的記錄，學(xué)號(hào)為9531103，姓名為張玉，女，21歲，計(jì)算機(jī)系。（是否能執(zhí)行？若不行，為什么？）
insert into IS_STUDENT values('9531103','張玉','女',21,'計(jì)算機(jī)系');
不能執(zhí)行,因?yàn)?違反了視圖創(chuàng)建規(guī)則，計(jì)算機(jī)系不滿足sdept中的信息系要求；
（6）根據(jù)視圖CJ_STUDENT創(chuàng)建視圖CJ_TJ，包含課程名，不及格人數(shù)，不及格人姓名列表，按照不及格人數(shù)降序排列。
Create view CJ_TJ as select cname,count(*),GROUP_CONSCAT(sname SEPARATOR ',') from CJ_STUDENT group by cname order by count(*) desc;
存儲(chǔ)過程：
（1）建立存儲(chǔ)過程course_grade：根據(jù)課程名參數(shù)，查詢?cè)撜n程的成績表，包括學(xué)號(hào)，姓名，成績，按學(xué)號(hào)升序排序。
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("計(jì)算機(jī)導(dǎo)論");
（2）建立存儲(chǔ)過程search_grade：根據(jù)姓名和課程名參數(shù)，查詢?cè)搶W(xué)生相應(yīng)的課程成績，若存在成績，則返回成績值，否則返回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 ;#創(chuàng)建成功-- 執(zhí)行存儲(chǔ)過程call search_grade('王大力','數(shù)據(jù)庫原理',@grade, @rname);select @grade, @rname;call search_grade('王大力','高等數(shù)學(xué)',@grade, @rname);select @grade, @rname;
（3）創(chuàng)建存儲(chǔ)過程take_course：根據(jù)參數(shù)學(xué)號(hào)和課程號(hào)，完成選課功能（要求能夠根據(jù)選課人數(shù)加以限制）。
要求：
§ 檢查該學(xué)生選課門數(shù)網(wǎng)站監(jiān)控是否超過3門，若超過，不插入記錄；
§ 檢查該課程選課人數(shù)是否已滿（在course表里添加一個(gè)屬性列，記錄課程人數(shù)限制）；
ü 選課人數(shù)已滿：不插入記錄；
ü 選課人數(shù)未滿且未插入過：完成數(shù)據(jù)插入操作；
ü 選課人數(shù)未滿，已插入過：不插入記錄
§ 最后返回執(zhí)行結(jié)果信息，例如：
ü 超過3門，返回-1；
ü 選課人數(shù)已滿。返回0；
ü 選課人數(shù)未滿且未插入過：返回1；
ü 選課人數(shù)未滿已插入過：返回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 ;
權(quán)限控制：
（1）創(chuàng)建超級(jí)用戶admin，具有對(duì)所有數(shù)據(jù)庫的所有訪問權(quán)限，僅限本機(jī)連接（密碼自行設(shè)置），并驗(yàn)證用戶和權(quán)限設(shè)置是否正確。
CREATE user admin @localhost
IDENTIFIED by '123';
（2）創(chuàng)建管理員teacher_liu，具有對(duì)SC表、IS_STUDENT視圖（實(shí)驗(yàn)5創(chuàng)建）的增刪改查權(quán)限，并且具有授權(quán)的權(quán)限，允許從任意位置連接（密碼自行設(shè)置），并驗(yàn)證用戶和權(quán)限設(shè)置是否正確。
CREATE USER teacher_liu @'%' IDENTIFIED by '123456';
GRANT SELECT ,INSERT, UPDATE, DELETE on studentTable.sc TO teacher_liu @'%' WITH GRANT OPTION
聯(lián)系客服
