维护索引、游标使用、创建视图、触发器、事务、存储过程
🌕写在前面
🍊 博客主页 :勇敢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 '姓名:'+' '+@name+'学号:'+@sno
--现在将游标移动到第一行
--循环
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 ','su12@163.com')
----禁止删除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',';libai12@163.com');
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