《视图和索引》数据库实验报告

实验报告（四）

一、实验题目

    实验四  视图和索引

二、实验目的

1. 掌握使用SQL语言进⾏视图的创建、修改与删除操作。

2. 掌握视图的使用方法。

3. 掌握使用SQL语言创建索引。

4. 理解索引对查询优化的影响，并能根据实际情况决定索引的使用。

三、实验内容与实现

实验要求1：

1. 创建视图，并通过视图查询200401班学生的学号、姓名、选修的课程名称及其正考成绩。

代码：

# 创建视图

create view vw_one

as

  select

   c.student_id as id,

   s.name as name,

   cr.name as cname,

   c.score as score,

   c.type as type,

   s.class_id as class_id,

   ci.id as cid

 from students s, scores c, courses cr, course_info ci

 where s.id = c.student_id

 and c.cs_id = ci.id

 and ci.course_id = cr.id

 and type='正考'

# 查找

select id, name, cname, score

from vw_one

where class_id=200401 

实验结果：

1. 使用第1步的视图，统计每个班级每门课的总成绩、平均分、最高成绩与最低成绩。

代码：

select class_id, cname, sum(score), avg(score), max(score), min(score)

from vw_one

group by class_id, cname

order by class_id, sum(score)

 

实验结果：

1. 创建视图，并通过视图查询算机科学与⼯程学院教师编号、姓名、为2019级上过的课程名称及开课学期。

代码：

# 创建视图

create view vw_two

as

select t.id as teacher_id,

t.name teacher_name,

ci.grade as grade,

cr.name as cname,

ci.semester as semester,

ci.id as cid

from teachers t,course_info ci, courses cr

where t.id = ci.teacher_id

and ci.course_id = cr.id

 

# 查找

select teacher_id, teacher_name, cname, semester

from vw_two

where grade=2019

 

实验结果：

1. 使用上述2个视图，查询计算机科学与工程学院教师姓名、所授课程名称、该课程每个班级的正考总成绩，按课程名称升序总成绩降序排列。

代码：

select teacher_name as t_name,s.cname, class_id, sum(score) as sum_score

from vw_one s, vw_two t

where s.cid = t.cid and s.cname = t.cname

group by teacher_name, s.cname, class_id

order by cname, sum_score desc

 

实验结果：

实验要求2：

1. 分析200401班学生的学号、姓名、选修的课程名称及其正考成绩查询语句的可执行计划。

代码：

select c.student_id as id, s.name as name, cr.name as cname,  c.score as score

from students s, scores c, courses cr, course_info ci

where class_id=200401

 and s.id = c.student_id

 and c.cs_id = ci.id

 and ci.course_id = cr.id

 and type='正考'

 

1. 给出对上述语句的优化方案。

方案：

先将班级class_id为200401的学生，考试类型type为正考的筛选出来，再与courses表和course_info表进行连接。

同时全表查询降低了查询效率，我们可以通过创建索引，提高查询效率。

3. 创建所需索引，并对比索引创建前后可执行计划发生的改变。

代码：

# 创建所需要索引

#主键

#将students表中的id字段设置为主键

alter table students add primary key(id)

#将scores表中的type字段设置为主键

alter table scores add primary key(type)

#将course_info表中的id字段设置为主键

alter table course_info add primary key(id)

#将courses表中的id字段设置为主键

alter table courses add primary key(id)

#外键

#给students表中的class_id字段添加classes表中的id字段为外键约束

alter table students add foreign key(class_id) references classes(id)

#给scores表中的students_id字段添加students表中的id字段为外键约束

alter table scores add foreign key(students_id) references students(id)

#给scores表中的cs_id字段添加course_info表中的id字段为外键约束

alter table scores add foreign key(cs_id) references course_info(id)

#给course_info表中的course_id字段添加courses表中的id字段为外键约束

alter table course_info add foreign key(course_id) references courses(id)

# 创建索引后的可执行计划

select c.student_id as id, s.name as name, cr.name as cname,  c.score as score

from courses cr, course_info ci,

     (select id,name

     from students

     where class_id='200401') as s,

     (select student_id, cs_id, score

     from scores

     where type='正考') as c

where s.id = c.student_id

 and c.cs_id = ci.id

 and ci.course_id = cr.id

 

  实验结果：

对比：

优化前：

优化后：

四、实验问题总结与心得

实验中遇到的问题：

1.在第一步创建视图时对后面的问题未考虑全面，导致解决后面解决问题的时候发现缺少需要的字段。

【解决办法】：

删除已经构建的视图，重新创建所需字段完整的视图，再进行后续查找任务

实验心得：

通过本次实验我掌握了使用SQL语言进⾏视图的创建、修改与删除操作。 掌握了视图的使用方法。 掌握了使用SQL语言创建索引。 理解了索引对查询优化的影响，并能根据实际情况决定索引的使用。

本次实验过程中更加明确了创建视图的意义和作用，以及创建视图时所需要注意的事项；通过对查询的优化，更加熟练使用主外键的设置和取消，明确explain信息中rows字段是解释根据表统计信息及索引选用情况，大致估算出找到所需要的记录所需要读取的行数（ 即每张表有多少行被优化器查询 ），所需读取的行数越少越好，通过rows可以看到优化后的效果。在解决问题的过程中一步步对sql语句使用逐渐熟练，更加了解mysql和workbench的使用，在今后继续努力。