SQL基础（九）视图与索引实战演练
实验任务 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:视图名腾讯云开发者
