实验任务 1
在
studen数据库中，以tb_student、tb_course和tb_score表为基础完成下列视图的设计与创建
表结构
- tb_student(sno,sn,dept,sex,birthday,polity)
- tb_score(sno,cno,score)
- tb_cource(cno,cn,ct,th)
任务题解
任务一
创建学生的基本情况视图 V_STU。
CREATE VIEW V_STU AS SELECT * FROM tb_student SELECT * FROM V_STU
任务二
创建视图 V_Sco，显示学生成绩信息。
CREATE VIEW V_Sco AS SELECT sno,score FROM tb_score SELECT * FROM V_Sco
任务三
创建视图 V_SCORE, 要求只显示学生的学号、姓名、系别、课号、课程名称及成绩。
CREATE VIEW V_SCORE AS SELECT s.sno,sn,dept,sc.cno,cn,score FROM tb_student s,tb_score sc,tb_course co WHERE s.sno=sc.sno AND sc.cno=co.cno SELECT * FROM V_SCORE
任务四
各系学生人数、平均年龄创建视图 V_NUM_AVG。
CREATE VIEW V_NUM_AVG AS SELECT dept,COUNT(*) AS 学生人数, -- 同时包含聚合函数和基本字段,需要分组 GROUP BY AVG(year(GETDATE())-year(birthday)) AS 平均年龄 FROM tb_student GROUP BY dept SELECT * FROM V_NUM_AVG
任务五
创建一个反映学生出生年份的视图 V_YEAR。
CREATE VIEW V_YEAR AS SELECT sno,sn,YEAR(birthday) AS 出生年份 -- 计算公式 FROM tb_student SELECT * FROM V_YEAR
任务六
将各位学生选修课程的门数及平均成绩创建视图 V_AVG_S_G。
CREATE VIEW V_AVG_S_G AS SELECT sno,COUNT(*) 选修课程数,AVG(score) 平均分 FROM tb_score GROUP BY sno SELECT * FROM V_AVG_S_G
任务七
将各门课程的选修人数及平均成绩创建视图 V_AVG_C_G。
CREATE VIEW V_AVG_C_G AS SELECT cno,COUNT(*) 课程选修人数,AVG(score) 平均成绩 FROM tb_score GROUP BY cno SELECT * FROM V_AVG_C_G
任务八
创建视图 V_YEAR_RJ，显示软件工程系出生日期在 2001 年之后出生的学生信息。
CREATE VIEW V_YEAR_RJ AS SELECT * FROM tb_student WHERE major='软件工程' AND YEAR(birthday)>2001 SELECT * FROM V_YEAR_RJ
任务九
基于视图 V_STU,创建视图 V_SEX,查看男党员的信息。
CREATE VIEW V_SEX AS SELECT * FROM V_STU WHERE sex='男' AND polity='党员' SELECT * FROM V_SEX
任务十
修改视图 V_YEAR，显示软件工程系出生日期在 2000 年之前出生的学生信息，并删除视图 V_YEAR。
ALTER VIEW V_YEAR AS SELECT * FROM tb_student WHERE dept='软件工程' AND YEAR(birthday) < 2002 SELECT * FROM V_YEAR DROP VIEW V_YEAR
任务十一
向视图 V_Sco 中添加学号为‘xxx’，课程号为‘10002’,成绩为87 的信息。
INSERT INTO V_Sco(sno,cno,score) VALUES('xxx','10002',87)
任务十二
修改视图 V_Sco，将学号为‘XXX’的学生，选修的课程号为10002 的成绩更改为 90。
UPDATE VIEW V_Sco -- 修改值,实质为更新视图,关键字 UPDATE SET score=90 WHERE sno='XXX' AND cno='10002'
任务十三
在视图 V_Sco 中，将学号为‘XXX’的学生，选修的课程号为10002 的记录删除。
DELETE FROM V_Sco WHERE sno='XXX' AND cno='10002'
任务十四
查询以上所建视图结果
-- (1) 查询平均成绩为 90 分以上的学生的学号、姓名和成绩。 -- method1 SELECT sno,sn,score FROM v_score WHERE sno in(SELECT sno FROM V_AVG_S_G WHERE 平均分>85) -- method2 SELECT s.sno,s.sn,score FROM v_score s,v_avg_s_g s2 WHERE s.sno=s2.sno AND s2.平均分>85 -- (2) 查询各课程成绩均大于平均成绩的学生的学号、姓名、课程和成绩。 SELECT sno,sn,cno,score FROM v_score WHERE score>ANY(SELECT 平均分 FROM v_avg_c_g) -- (3) 按系别统计各系平均成绩在 80 分以上的人数，结果按降序排列。 -- method1 SELECT dept 系别,COUNT(*) 总人数 FROM v_stu WHERE sno IN(SELECT sno FROM v_avg_s_g WHERE 平均分 > 80) GROUP BY dept ORDER BY 人数 DESC -- method2 SELECT dept,COUNT(*) 人数 from v_stu s,v_avg_s_g s2 WHERE s.sno=s2.sno AND s2.平均分 > 80 GROUP BY dept ORDER BY 人数 DESC
实验任务 2
在
student数据库中，以tb_student、tb_course和tb_score表为基础完成下列索引的设计与创建。
-- (1) 对学生信息表 tb_student 中的学号 sno 创建聚集索引，并按降序排列。 CREATE CLUSTERED INDEX IX_SNO ON tb_student(sno DESC) -- (2) 对学生成绩信息表 tb_score 先按上课编号 cno 升序排列，再按学生成绩 score 降序排列。 SELECT cno,score FROM tb_score ORDER BY cno ASC ,score desc -- (3) 对课程信息表 tb_course 中的课程编号创建唯一索引，并按升序排列。 CREATE UNIQUE INDEX IX_CNO ON tb_course(cno ASC) -- (4) 在 tb_student 表中的 sn 列创建唯一索引 CREATE CLUSTERED INDEX IX_SN ON tb_student(sn)
总结
1.查看与删除索引
-- 查看索引 Sp_helpindex name -- name:数据库表名 -- 删除索引的方式 -- method1 DROP INDEX <table or view name>.<index name> -- method2 DROP INDEX <index name> ON <table or view name>
2.索引类型
| 类型名称 | 关键字 |
| 普通索引 | INDEX |
| 唯一索引 | UNIQUE INDEX |
| 聚集索引 | CLUSTERED INDEX |
| 非聚集索引 | NONCLUSTERED INDEX |
| 全文索引 | FULLTEXT |
3.索引分析
索引相当于一本书的目录，优缺点分析如下：
虽然索引很大程度上提高了查询速度，但同时也会降低更新表的速度，如：对表进行 insert、update 和 delete。这是因为更新表时，不仅要保存数据，还要保存一下索引文件
索引只是提高效率的一个因素，如果有大数据量的表，则需要花时间研究建立最优秀的索引，或优化查询语句
建立索引会占用磁盘空间的索引文件。如果在一个大表上创建了多种组合索引，索引文件的增速会很快
4.查看视图创建源码
Sp_helptext name -- name:视图名
