欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-03 23:34:47
...

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

????写在前面
???? 博客主页勇敢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