维护索引、游标使用、创建视图、触发器、事务、存储过程

维护索引、游标使用、创建视图、触发器、事务、存储过程

????写在前面
???? 博客主页 ：勇敢link牛牛
???? 欢迎关注：????点赞????收藏⭐️留言????
????本文由 勇敢link牛牛 原创，CSDN首发！
???? 首发时间：????2021年12月4日???? ????最新更新时间：????2021年12月4日????
✉️愿你熬过万丈孤独，藏下星辰大海！
???? 参考书籍：????《数据库开发技术》
????作者水平很有限，如果发现错误，请留言轰炸哦！万分感谢感谢感谢！

目录

维护索引

--创建聚集索引
create clustered index Index_cno
	on course(courseno)
------如果存在***，就删掉它，再创建一个自己想要的索引之类的
if exists(select name from sysindexes
			where name='IDX_SCORE')
	drop index score IDX_SCOREJJ
else
create clustered index IDX_SCORE
	on score(studentno,courseno)
--创建唯一聚集行索引
create unique clustered index Index_cour_cno
	on course(courseno,cname)
--索引的重组
alter index IDX_SCORE--//all
	on course reorganize
--索引的重建
alter index IDX_SCORE--//all
	on course rebuild
		with(pad_index=on,fillfactor=80)
--索引的重建（稍加修改）
create clustered index Index_cour_cno
	on course(courseno,cname)
	with(pad_index=on,fillfactor=80,drop_existing=on)
--第一问
create unique clustered index Index_TER
	on teacher(tname)
--第二问
create unique clustered index Index_cour_cno
	on score(courseno,studentno)
alter index Index_cour_cno
	on score reorganize
--第三问
alter index Index_cour_cno
	on course(courseno,cname)
	rebuild
	with(pad_index=on,fillfactor=80,drop_existing=on)
--第四问
alter index Index_cour_cno
	on course(courseno,cname)
	with(pad_index=on,fillfactor=80,drop_existing=on)

游标使用

--声明一个全局游标
--substring 函数是用来截取字符串中的一部分字符
declare cemploy cursor static
	for select sname,studentno from student
			where sex='女'and substring(studentno,1,2)='17'
--打开游标
open cemploy
--获取信息
---声明几个需要的变量
declare @sno nchar(11),@name nchar(8)
fetch first from cemploy
	into @name, @sno 
--检测是否正确	print '姓名:'+'   '[email protected]+'学号:'[email protected]
--现在将游标移动到第一行
--循环
while @@FETCH_STATUS=0
	begin
		print '姓名:'+'   '+@name+'学号:'+'   '+@sno
		fetch next from cemploy
			into @name, @sno 
		
	end 
close cemploy
deallocate cemploy

创建视图

--虚表，只是定义，调用就是会执行代码
--存储在内存当中，用户视角处理数据
--简化数据查询和处理
--屏蔽数据的复杂性
--安全性，语句查询
--视图加密、修改视图时的约束
--有order by 要用top
--视图在使用聚合函数时要制定别名
-----------------------------------
create view V_COURSE as select * from course
		where type='必修'
-----------------------------------
go
create view V_final as 
select top(100) percent student.studentno,sname,course.courseno,cname,final
	from student join score on student.studentno= score.studentno join course on course.courseno=score.courseno
		order by student.studentno,course.courseno
---------------------------------------
go
create view V_max as 
--视图在使用聚合函数时要制定别名
select top(100) percent cname,final,max(final) as maxfinal
	from student s join score sc on s.studentno = sc.studentno join course on course.courseno=sc.courseno
	group by s.classno,cname
	order by s.classno
---------------------------------------
go
create view V_saf as 
select sname,cname,final
	from student s join score sc on s.studentno = sc.studentno join course on course.courseno=sc.courseno
	where point>700

----------------------------------------
--视图的修改
--加密with encryption
go
alter view  V_final with encryption as
	select top(100) percent s.sname,c.courseno,final 
		from student s join score on s.studentno=score.studentno join course c on  c.courseno = score.courseno
		order by s.sname

----------------------------------------
--假定原加密视图为
go
create view v with encryption as
select * from student

--取消加密
alter view v
as
select * from t


----------------------------------------
go
create view vcs 
	with encryption as 
			select c.cname,classno,avg(final) as 平均成绩 
				from score sc join course c on sc.courseno = c.courseno join student s on s.studentno = sc.studentno
				group by classno,c.cname
				having avg(final) > 70	
----------------------------------------
--删除视图
go
drop view vcs
----------------------------------------
insert into V_COURSE values('C00123','数据库编程','必修',64 ,4 ,5)
--在视图后面加了with check option 那么插入的数据满足视图的where条件
----------------------------------------
--不能修改主属性
--update V_COURSE set canme='数据库开发与应用'
----------------------------------------
delete from V_sex
	where studentno='12343211'
go
select * from student 
--1、通过SSMS在teaching数据库的teacher表的tname列创建唯一非聚集索引（截图）。
create unique index index_name on teach_class (teacherno,classno,courseno)
alter index index_name on teach_class 
	rebuild	
	with(fillfactor = 80)
drop index teacher.NonClusteredIndex-20211105-094121
----------------------------------------
go
create view vsc as
	SELECT s.classno ,c.courseno ,c.cname,AVG(sc.final) AS Expr1
		FROM   score sc join course c on sc.courseno = c.courseno join student s on s.studentno = sc.studentno join class on s.classno = class.classno
		GROUP BY class.classno
----------------------------------------
go
create view T as
	select * from teacher
		where prof='副教授'
go
alter view T with encryption as
	select * from teacher
		where prof='副教授'
-------------------------------------------
insert into vsc values('t05039','张德', '计算机应用','讲师','计算机学院'),
('t06019','李月','机械制造','副教授','机械工程学院')
-------------------------------------------
update T 
	set prof='教授'
	where tname='张得'

	

触发器

--为student表创建一个触发器禁止更新学号
create trigger TRI_student1 
--alter update(studentno) 
	on student
	after update
as 
	begin
		if UPDATE(studentno)--只有修改学号操作的时候出发
		raiserror('禁止更新学号',16,2)
		rollback--撤销掉
	end 

--感知触发器的存在
update student set studentno = '17112101172'
	where studentno ='17112101173'

select * from student
	where studentno='17112101173'
--禁止插入入学成绩子在1000分以上的触发器
go
create trigger TRI_student2
	on student
	after insert
as
	if exists(select * from student where point >1000)
	begin
		raiserror('禁止插入入学成绩在1000分以上的学生信息',16,2)
		rollback
	end
insert into student values('17112100072','宿致远','男','1999-02-04','170501','2000','12545678998 ','[email protected]')
----禁止删除course表中的删除必修课
go
create trigger TRI_student3
	on course
	after delete--临时表在内存中
as
	if exists(select *  from course where type = '必修')--等价于if exists(select *  from delete where type = '必修')
		begin
			raiserror('禁止删除必修课',16,2)
			rollback
		end

delete from course 
	where type='必修'
select * from course
----
go
create trigger TRI_student4
	on course
	instead of delete--临时表在内存中
as
	if exists(select *  from course where type = '选修')--等价于if exists(select *  from delete where type = '必修')
		begin
			raiserror('禁止删除选项课',16,2)
		end
delete from course 
	where type='选修'
select * from course

-----禁止插入
go
create trigger TRI_student5
	on course 
	instead of insert--临时表在内存中
as
	if exists(select * from course)
		begin
			raiserror('禁止插入任何数据',16,2)
		end
--禁止在score表插入任何信息
go
CREATE TRIGGER TRI_SCORE
	ON SCORE
	AFTER INSERT
AS
	IF EXISTS (SELECT * FROM SCORE)
		BEGIN
			RAISERROR('禁止在score表中插入任何信息',16,2)
			rollback
		END
----------------------------------------------
go 
CREATE TRIGGER TRI_SCORE_1
	ON SCORE
	instead of INSERT ,update --禁止所有删除和插入任何信息
AS
	IF EXISTS (SELECT * FROM SCORE)
		BEGIN
			RAISERROR('禁止在score表中插入任何信息',16,2)
			rollback
		END
----------------------------------------------以下未执行
--删除student表中的学生信息，同时删除score表中的信息
--利用触发器实现级联删除
--有外键约束的表是实现不了的
go
CREATE TRIGGER TRI_STUDENT_SCORE
	ON student
	after delete
as
	delete from score where studentno =(select studentno from deleted)
--查询
select * from score where studentno in (select studentno from student)
delete  from  student where studentno ='18122221324'
-----
--对库做一个触发器DDL
--禁止对数据库中的表做任何操作
--不能写instead of触发方式
go
create trigger tri_table
	on database
	for  drop_table ,alter_table
	as
	BEGIN
			PRINT'禁止对teaching数据库有禁止删除和修改操作'
			rollback
	END
drop table teaching
----------
--一个表中只能有一个instead of触发器
--修改触发器
--alter trigger TRI_student3
	--后面一样
----查看触发器

---删除触发器
drop trigger TRI_student3

--禁用触发器的语法，先修改再禁用
alter table student
disable  trigger  tri_student
--启用触发器
alter table student
enable trigger  tri_student

--exce sp_helptext tri_student

-----------------------------------------------------------------------------------------
--（1）创建一个名称为stuinfo的存储过程，要求完成以下功能：在student表中查询18级学生的学号、姓名、性别、出生日期和电话5个字段的内容
go
create procedure stuinfo 
as 
 select studentno,sname,sex,birthdate,phone from student where substring(studentno,1,2)='18'
--（2）创建一个存储过程stuinfo，完成的功能是在表student、表course和表score中查询以下字段：学号、姓名、性别、课程名称、期末分数
go
create procedure stuinfo_1
as 
 select s.studentno,sname,sex,cname,final from student s join score sc on s.studentno = sc.studentno join course c on
  sc.courseno = c.courseno 
--*****（3）创建一个带有参数的存储过程stu_age，该存储过程根据输入的学号，在student表中计算此学生的年龄，并根据程序的执行结果返回不同的值，
--程序执行成功，返回整数0；如果执行出错，返回错误号
go
--3
GO 
CREATE PROCEDURE STU_AGE
@STUDENTNO NVARCHAR(10), @AGE INT OUTPUT
	AS
	DECLARE @ERRORVALUE INT
	SET @ERRORVALUE=0
	SELECT @AGE=YEAR(GETDATE())-YEAR(BIRTHDATE) FROM STUDENT
		WHERE STUDENTNO=@STUDENTNO
	IF(@@ERROR<>0)
		SET @ERRORVALUE=@@ERROR
	RETURN @ERRORVALUE
--（4）创建一个insert触发器tr_stu_insert,当在student表中插入一条新纪录时触发该触发器，并给出“你插入了一条新纪录！”的提示信息
go
create trigger tr_stu_insert 
on student
after insert
as 
 begin
  declare @abu nchar(10)
  set @abu = '你插入了一条新纪录'
  print @abu
 end
--（5）创建一个after触发器，要求实现以下功能：在score表上创建一个插入、更新类型的触发器tr_scorecheck,当在score字段中插入或修改考试
--分数后触发该触发器，检查分数是否为0~100
go
create trigger tr_scorecheck 
on score
for update,insert
as 
 begin
  delete score where studentno=(select studentno from deleted)
--（6）创建一个after触发器，要求实现以下功能：在course表上创建一个删除类型的触发器tr_notallowdelete,当在course表中删除记录时触发该
--触发器，显示不允许删除表中数据的提示信息。
if exists(select name from sysobjects where name = 'tr_notallowdelete')
 drop trigger tr_notallowdelete
go
create trigger tr_notallowdelete
on course
after delete 
as 
  delete from course 
  print '不允许删除表中数据的提示信息'

事务

----------------------------------------------------------------------------------------------------------------------------------------
--11111在course表上创建一个显式事务，更新课程的学分
--set implicit_transactions off;
begin tran
	update course set credit = '123'
	print N'tran count at 1st = ' +cast(@@trancount  as nvarchar(10));	/*输出查看打开的事务，结果为1即为当前连接已经打开一个事务*/
commit tran
select * from course
----------------------------------------------------------------------------------------------------------------------------------------
--22222在student表上创建一个隐式事务，插入一个学生的信息
print '隐式事务';
set implicit_transactions on;				/*设置为隐式事务模式*/
	insert into student values('17112100000','吴晓冉','男','1999-02-04 00:00:00.000','111111','800','13139534189',';[email protected]');
	print N'tran count in 1st implicit tran = ' +cast(@@trancount  as nvarchar(10));
commit tran									/*一定要提交事务*/
print N'tran count after implicit tran = ' +cast(@@trancount  as nvarchar(10));	/*检测@@trancount的值是否为0，是则代表事务结束*/
set implicit_transactions off				/*退出隐式事务模式*/
select * from student
delete from student where sname = '吴晓冉'
----------------------------------------------------------------------------------------------------------------------------------------
--33333在score表上创建一个事务，删除一条选课记录，然后回滚该事务
/*
begin tran 
	delete from score where courseno = 'c05109';
	save tran savepoint;
	rollback tran savepoint				/*使用回滚不会删除数据 */
commit tran
select * from course 
*/
begin tran 
	delete from score where courseno = 'c05103';
	rollback tran 			/*使用回滚不会删除数据 */
commit tran
select * from course 
----------------------------------------------------------------------------------------------------------------------------------------
--44444在score表上创建一个事务，更新学生的成绩，并设置保存点，然后删除该记录，并回滚事务到保存点
begin tran 
	update score set final = '110' where courseno = 'c05109';
	save tran savepoint;
	delete from score where courseno = 'c05103';
	rollback tran savepoint;
commit tran
select * from score
-----------------------------------------------------------------------------------------------------------------------------------------
--自动提交模式下,遇到语法编译时，回滚所有语句
--遇到逻辑（运行）错误，保留之前执行的正确语句
--事物的嵌套commit是提交最近的事务，rollback是回滚到最外层的事务，以外层的事务为准，外层回滚掉，内存也会全部回

存储过程

--创建存储过程
--输出所有学生的姓名，课程名，期末成绩信息（不带参数）
create procedure Pstu_sc_cou as
	select sname,cname,final  
		from student join  score on student.studentno = score.studentno 
			join course on score.courseno =course.courseno
--输出指定学生的姓名，课程名，期末成绩信息
--等待输入参数
go
create procedure Pstu_sc_cou1 
	@student_name nchar(8)
	as
	select sname,cname,final  
		from student join  score on student.studentno = score.studentno 
			join course on score.courseno =course.courseno
	where sname = @student_name
--用输出参数“返回”指定学生的所有课程的期末成绩的平均成绩
go
create procedure Pstu_sc_cou2
	@student_name nchar(8),
	@average numeric(6,2) output--返回值（输出）
	as
	select @average = avg(final)  
		from student join  score on student.studentno = score.studentno 
			join course on score.courseno =course.courseno
		where sname = @student_name
-------------------------------------------------
--没有传递值时，返回NULL，返回所有的学生成绩，
--有的话传出指定学生
go
create procedure Pstu_sc_cou3
	@student_name nchar(8)=null,
	@average numeric(6,2) output --返回值（输出）
	as
	select @average = avg(final)  
		from student join  score on student.studentno = score.studentno 
			join course on score.courseno =course.courseno
		where sname = @student_name or @student_name is NULL
	select student.studentno,sname,score.final 
		from student join score on student.studentno = score.studentno 
		where final<70
-------------------------------------------------------------------------------
go
create procedure Pstu_sc_cou4 as
	select sname,cname,final  
		from student join  score on student.studentno = score.studentno 
			join course on score.courseno =course.courseno
	where SUBSTRING(student.studentno,1,2)='17'
-------------------------------------------------------------------------------
go
create procedure Pstu_sc_cou5 
	@student_cno nchar(8)=null
	as
	select course.courseno,cname, student.sname,student.studentno final  
		from student join  score on student.studentno = score.studentno 
			join course on score.courseno =course.courseno
		where score.courseno = @student_cno

-------------------------------------------------------------------------------
go
create procedure Pstu_sc_cou7
	@student_cno nchar(8)=null,
	@average numeric(6,2) output --返回值（输出）
	as
	select @average = avg(final) 
		from student join  score on student.studentno = score.studentno 
			join course on score.courseno =course.courseno
		where score.courseno = @student_cno or @student_cno is NULL
-------------------------------------------------------------------------------

--修改存储过程
--删除
drop  procedure  Pstu_sc_cou5

--执行存储过程（查询）
--1
exec  Pstu_sc_cou5 @student_cno='c05109'
--2
exec Pstu_sc_cou1 @student_name ='赵**'
--3
declare @avg numeric(6,2)
exec Pstu_sc_cou2  @student_name ='赵**' ,@average = @avg output --必须写output
select @avg
--4
declare @ave numeric(6,2)
exec Pstu_sc_cou3  @student_name ='赵**' ,@average = @ave output --必须写output
select @ave