ÊÓͼ¡¢´æ´¢¹ý³ÌÒÔ¼°È¨ÏÞ¿ØÖÆÁ·Ï°

ÊÓͼ¡¢´æ´¢¹ý³ÌÒÔ¼°È¨ÏÞ¿ØÖÆ

µ¼¶Á£º

¸ÃÎÄÕÂΪÊÓͼ¡¢´æ´¢¹ý³Ì¡¢Óû§È¨ÏÞÁ·Ï°£»

Èç¹ûÓв»¶ÔµÄµØ·½»¶Ó­Ö¸³öÓë²¹³ä£»

¸Ã»ù´¡Á·Ï°»ùÓÚ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´´½¨£©µÄÔöɾ¸Ä²éȨÏÞ£¬²¢ÇÒ¾ßÓÐÊÚȨµÄȨÏÞ£¬ÔÊÐí´ÓÈ365betÌåÓýÔÚÏßâλÖÃÁ¬½Ó£¨ÃÜÂë×ÔÐÐÉèÖã©£¬²¢ÑéÖ¤Óû§ºÍȨÏÞÉèÖÃÊÇ·ñÕýÈ·¡£

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

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

×ܽ᣺

ÆÚÄ©¿¼ÊÔ½áÊø£¬¿ªÊ¼Â½Ðø¸üС£¡£¡£

ÒÔÉÏÁ·Ï°Èç¹ûÓв»¶ÔµÄµØ·½»¶Ó­Ö¸Õý¡£¡£¡£¡£