SQL 基础（九）视图与索引实战演练

发表于 分类于 阅读次数： 本文字数： 3.6k 阅读时长 ≈ 3 分钟

实验任务 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。

1
2
3
4
5
6
CREATE VIEW V_STU
AS
SELECT *
FROM tb_student

SELECT * FROM V_STU

任务二

创建视图 V_Sco，显示学生成绩信息。

1
2
3
4
5
6
CREATE VIEW V_Sco
AS
SELECT sno,score
FROM tb_score

SELECT * FROM V_Sco

任务三

创建视图 V_SCORE, 要求只显示学生的学号、姓名、系别、课号、课程名称及成绩。

1
2
3
4
5
6
7
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。

1
2
3
4
5
6
7
8
9
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。

1
2
3
4
5
6
CREATE VIEW V_YEAR
AS
SELECT sno,sn,YEAR(birthday) AS 出生年份 -- 计算公式
FROM tb_student

SELECT * FROM V_YEAR

任务六

将各位学生选修课程的门数及平均成绩创建视图 V_AVG_S_G。

1
2
3
4
5
6
7
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。

1
2
3
4
5
6
7
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 年之后出生的学生信息。

1
2
3
4
5
6
7
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,查看男党员的信息。

1
2
3
4
5
6
CREATE VIEW V_SEX
AS
SELECT * FROM V_STU
WHERE sex='男' AND polity='党员'

SELECT * FROM V_SEX

任务十

修改视图 V_YEAR，显示软件工程系出生日期在 2000 年之前出生的学生信息，并删除视图 V_YEAR。

1
2
3
4
5
6
7
8
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 的信息。

1
2
INSERT INTO V_Sco(sno,cno,score)
VALUES('xxx','10002',87)

任务十二

修改视图 V_Sco，将学号为‘XXX’的学生，选修的课程号为10002 的成绩更改为 90。

1
2
3
UPDATE VIEW V_Sco -- 修改值,实质为更新视图,关键字 UPDATE
SET score=90
WHERE sno='XXX' AND cno='10002'

任务十三

在视图 V_Sco 中，将学号为‘XXX’的学生，选修的课程号为10002 的记录删除。

1
2
DELETE FROM V_Sco
WHERE sno='XXX' AND cno='10002'

任务十四

查询以上所建视图结果 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- (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
2
3
4
5
6
7
8
9
10
11
12
13
-- (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.查看与删除索引

1
2
3
4
5
6
7
8
9
-- 查看索引
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.查看视图创建源码

1
Sp_helptext name -- name:视图名