索引与视图

–创建索引
create nonclustered index ix_StuInfo_stuName
on StuInfo(stuName)

    --1.    在分数表上成绩字段创建非聚集索引index_score，填充因子40%
    create nonclustered index index_score
    on StuMarks(score)
    WITH FILLFACTOR= 40

    --2.    使用index_score索引查询成绩小于80分的同学的学号、姓名、科目和成绩
    select StuInfo.stuid,stuName,subject,score from StuMarks with(index=index_score) ,StuInfo

    where score<80 and StuInfo.stuid=StuMarks.stuid

    --3.删除索引index_score
    if exists (select * from sys.indexes where name ='index_score')
    drop index StuMarks.index_score

    --4.创建视图 View_stuinfo_marks，包括学生学号、姓名、性别、科目和成绩

    create view View_stuinfo_marks
    --加密
    with encryption
    as
    select StuInfo.stuid,stuName,stusex,[subject],score from StuInfo,StuMarks where StuInfo.stuid = StuMarks.stuid;
    go

    --5.    使用视图View_stuinfo_marks查询所有男同学的成绩
    select * from  View_stuinfo_marks where stusex='男'

    --6.    使用视图View_stuinfo_marks查询所有不及格的女同学的学号、姓名及科目

    select stuid,stuName,subject from  View_stuinfo_marks where stusex='女'

    --7.    在原有视图View_stuinfo_marks基础上创建新的加密视图View_stuinfo，包括学号、姓名和总成绩，并按总成绩降序排列
    create view View_stuinfo
    --加密
    with encryption
    as
    select DENSE_RANK() over (order by  SUM(score)desc)
     stuid,stuName,SUM(score) as'总成绩' from  View_stuinfo_marks 
     group by stuid,stuName
    go

    --8.    使用新视图View_stuinfo，显示所有学生的总成绩排名（使用排序函数）


        select * from View_stuinfo


    --9.    删除所有视图


declare @vname varchar(8000) 
set @vname='' 
select @vname=@vname+Name+',' from sysobjects where xtype='V' 
select @vname='drop View '+ left(@vname,len(@vname)-1) 
exec(@vname)

--使用索引
select * from StuInfo with(index=ix_StuInfo_stuName)
where stuid=5
--删除索引
if exists (select * from sys.indexes where name ='ix_StuInfo_stuName')
drop index stuinfo.ix_StuInfo_stuName

–创建视图
create view sumscorerank
–加密
with encryption
as
–查询语句
select * from StuInfo with(index=ix_StuInfo_stuName)
where stuid=5
go

--使用视图

select * from sumscorerank

--非加密状态可查看查询语句

select * from information_schema.views

--删除视图
if exists (select * from sys.views where name ='sumscorerank')
drop view sumscorerank