第7章 使用视图与索引优化查询

1、2select 列表名from 表名 , n where 条件例：select * from xsqk where 出生日期75by 学号order by 学号45select 学号, 姓名, 联系电话 from xsqkwhere ( select avg(成绩) from xs_kc where xsqk.学号=xs_kc.学号 ) 60selectfrom where select fromwhere说明说明:子查询执行后子查询执行后,若返回若返回1个单值个单值,则可用则可用比较运算比较运算符符来设置条件来设置条件:若返回一个列表若返回一个列表值值,则可用则可用:(not) in、any

2、、allin6生成一张新表，其中保存的是查询结果的数据789101112 create view v1 as select studentid，studentname，sex，birthday，classid from student where classid=10702select * from v113create view V综合信息 as select student.studentid,studentname,coursename,score from student,course,score where student.studentid=score.studentidand c

3、ourse.courseid=score.courseidwith encryption对视图的定义进行加密对视图的定义进行加密exec sp_helptext v综合信息14create view V平均成绩(学生学号,个人平均分)with encryptionas select studentid, avg (score) from score group by studentid15create view V单科最高分asselect courseid,max(score) from score group by courseid)16create view V全校最高分asselect

4、 student.studentid,studentname,courseid,score from student,scorewhere student.studentid=score.studentid and score= (select max(score) from score)171819alter view v综合信息 as select student.studentid,studentname,coursename,score from student,course,score where student.studentid=score.studentidand course

5、.courseid=score.courseidwith check option20212223242526对于小表来说，是可行的。但对于有成千上万行的表来说，其搜索的效率非常低。建立索引。272829303132333435生成惟一性聚集索引生成惟一性非聚集索引36默认值37create unique indexon( 课程名 desc)with drop_existingcreate indexon( 课程名 desc)删除同名的原索引，重建新索引。38create unique clustered index pk_xs_kcon xs_kc(学号 desc ,课程号)with pad_index, fillfactor=60, drop_existing中间级索引页也按“填充因子”指定的填充度填充