索引和视图

use Lession3
go
--如果存在该索引，先将其删除
if exists(select name from dbo.sysindexes where name='IX_score')
        drop index stuMarks.IX_score  --删除索引的语法

--对成绩字段创建非聚集索引，填充因子 30%
create nonclustered index IX_score
on stuMarks(score)
with fillfactor=30  


--在stuinfo表中，给姓名创建一个非聚集索引（IX_stuName）,填充因子为30%
use Lession3
go
create nonclustered index IX_stuName
on StuInfo(StuName)
with fillfactor=30
go
-- 使用索引查询张三的信息
select * from StuInfo with(index=IX_stuName) where StuName='张三'

--查询html科目学员信息和成绩信息
select s1.StuID,s1.StuName,s1.StuSex,s2.Subject,s2.Score from StuInfo s1,
StuMarks s2 where s1.StuID=s2.StuID and Subject='html'

--创建一个视图view_stuInfo_stuMarks用于查询学生成绩
use Lession3
go
--如果存在该视图，先将其删除
if exists(
    select * from dbo.sysobjects where name='view_stuInfo_stuMarks'
)
--删除存在的视图
drop view view_stuInfo_stuMarks
go
--创建view_stuInfo_stuMarks
create view view_StuInfo_StuMarks
as 
    select StuName,StuInfo.StuID,Score from StuInfo 
    left join StuMarks on StuInfo.StuID = StuMarks.StuID

--查看视图
select * from view_StuInfo_StuMarks

--创建一个view_stuMarks 用于查询科目为html学生成绩
use Lession3
go
--如果存在该视图，先将其删除
if exists(
    select * from dbo.sysobjects where name='view_stuMarks'
)
--删除存在的视图
drop view view_stuMarks
go

create view view_stuMarks
as
 select StuInfo.StuID,StuName,StuSex,Subject,Score from StuInfo 
 left join StuMarks on StuInfo.StuID=StuMarks.StuID where Subject='HTML'

 --查询视图view_stuMarks
 select * from view_stuMarks


  --创建加密视图view_encryption_stu
use Lession3
go
 
create view view_encryption_stu
with encryption
as 
    select StuName,StuInfo.StuID,Score from StuInfo 
    left join StuMarks on StuInfo.StuID = StuMarks.StuID
go

select * from view_encryption_stu