实验九视图及索引心得 视图和索引实验报告

实验7索引和视图
【实验名称】索引和视图
【实验目的】
 了解索引的作用
 掌握聚集索引的非聚集索引的特点
 掌握创建索引的技术
 掌握索引的维护和优化技术
【实验内容】
 练习课本的例题和实验课本中的实验7。
 完成课本P150页的上机作业。
实验过程（步骤）或程序代码：
use teaching;

在student表的phone列上建立一个升序普通序列phone_index

create index phone_index on student(phone asc);

在course表的cname列建立一个唯一性索引cname_index

create unique index cname_index on course(cname);

在score表的studentno和courseno列上建立一个复合索引sc_index

create index sc_index on score(studentno,courseno);

创建表时创建索引

创建teacher1表的tname字段建立一个唯一性索引tname_index,一个前缀索引dep_index

use mysqltest;
create table if not exists teacher1(
teacherno char(6) not null comment '教师编号',
tname char(8) not null comment '教师姓名',
major char(8) not null comment '专业',
prof char(10) not null comment '职称',
department char(16) not null comment '部门',
primary key (teacherno),
unique index tname_index(department(5))
);

在teacher1表上建立teacherno主键索引，建立tname与prof的复合索引

alter table teacher1
add primary key(teacherno),
add index mark (tname,prof);

删除索引

use teaching;
drop index mark on teacher1;
alter table course drop index cname_index;

视图的创建和管理

create view teach_view1 as select * from teacher;
select * from teach_view1;

在多表上创建视图

create view stu_score1
as select student.studentno,sname,phone,cname,final
from score join student on student.studentno = score.studentno
join course on course.courseno = score.courseno
where sex ='女' and left(student.studentno,2) = '18';

select * from stu_score1;

在已存在的视图上创建视图

create view teach_view2
as select teacherno,tname,major
from teach_view1
where prof like '%教授' and department = '计算机学院';

select * from teach_view2;

利用图形化管理工具

通过视图修改基本表数据

insert into teach_view1(teacherno,tname,major,prof,department)
values ('t06027','陶启年','纳米技术','教授','材料学院');

update teach_view1 set prof ='副教授' where teacherno = 't07019';
 

delete from teach_view1 where teacherno = 't08017';
 
update stu_score1 set phone = '132123456777'
where studentno = '1812312117';
 

select * from stu_score1;

检查视图的应用

insert into V_dept
values('t08017','时观','金融管理','副教授','计算机学院');

实验课本

为student表的phone列建立一个降序普通索引 phone_idx

create index phone_idx on student(phone desc);

在course表的cname列建立一个唯一性索引cname_idx;

create unique index cname_idx on course(cname);

在score表的studentno和courseno列上建立一个复合索引stu_cour_idx

create index stu_cour_idx on score(studentno,courseno);

在teacher表上建立teacherno主键索引,建立tname和prof的复合索引

alter table teacher
add primary key(teacherno),
add index mark(tname,prof);

删除teacher表的mark索引

drop index mark on teacher;
#利用alter table同样可以删除
alter table course drop index cname_idx;

利用图形化管理创建索引

创建和管理视图

create view v_teacher as
select * from teacher;

在student表、course表和score表上创建一个名stu_score的视图。视图中保留19级的学生的学号、姓名、名称、电话、课程名和期末成绩；

create view stu_score
as select student.studentno,sname,phone,cname,final
from score join student on student.studentno = score.studentno
join course on course.courseno = score.courseno
where sex='女' and left(student.studentno,2) = '19';

创建视图v_teach，统计材料学院的教师中不是副教授或教授的教师号、教师名和专业

create view v_teach
as select teacherno,tname,major
from teach_view1
where prof not like '%教授' and department = '材料学院';

查看视图定义情况

show create view stu_score;

修改视图v_teach，统计材料学院的教师中的教授和副教授的教师号、教师名和专业，并在视图名后指明视图列名称

alter view v_teach(教师号,教师名,专业)
as select teacherno,tname,major
from teach_view1
where prof like '%教授' and department = '材料学院';
 
select * from v_teach;

删除视图v_teach的命令

drop view v_teach;

实验结果与分析：

利用视图修改表数据

创建视图view_avg，统计各门课程的平均成绩，并按课程名称升序排列

create view view_avg
as select cname 课程名, avg(final) 平均成绩
from score join course on score.courseno = course.courseno
group by cname asc;
 
select * from view_avg;

通过视图v_teacher，对基表teacher进行插入，更新和删除

insert into v_teacher (teacherno,tname,major,prof,departname)
values ('t07027','陶谷年','合金技术','副教授','材料学院');
update v_teacher set prof = '副教授' where teacherno = 't07019';
delete from v_teacher where teacherno = 't07027';

视图stu_score依赖于源表student，course和score 3张表，包括studentno，sname、phone

cname和final字段，通过stu_score修改表student中的学号为19112100072的电话号码

update stu_score set phone = '133123456777'
where studentno ='19112100072';
select studentno,sname,phone from student where studentno ='19112100072';
 
update stu_score set phone ='135123456777'
where studentno = '18125121107';

修改v_teacher的视图定义，添加with check option

alter view v_teacher
as
select * from teacher
where department = '计算机学院'
with check option;

课本作业：

在course表的cname列上创建索引IDX_cname

create index IDX_cname on  course(cname);

在student表的studentno和phone列上创建唯一性索引uq_stu。并输出student表中的记录，查看输出结果的顺序

create unique index uq_stu on student(studentno,phone);

创建一个视图v_teacher，查询所有“计算机学院”教师的信息

create view v_teacher
as select * from teacher
where department ='计算机学院';

创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并按照平均分降序排序

create view v_avgstu
as select student,sname,avg(final)
from score join student on score.studentno = student.studentno
group by cname desc;

出现报错：

经过查询：错误原因为：两表连接后，有两个同名的属性，不知道select的是哪个表的属性。

解决办法：在要select的属性前加上表名。

且：group by cname desc会出现语法错误，需要将desc改成：order by avg(final) desc;

修改v_teacher的视图定义，添加with check option

alter view v_teacher
as
select * from teacher
where department = '计算机学院'
with check option;

通过视图v_teacher向基表teacher中分别插入下列数据，并查看数据的情况

insert into v_teacher (teacherno,tname,major,prof,department)
values 
('t05039','张馨月','计算机应用','讲师','计算机学院'),
('t06018','李书成','机械制造','副教授','机械学院');

通过视图v_teacher将基表teacher中教师编号t05039的教师职称改为副教授

update v_teacher set prof = '副教授' where teacherno = 't05039'

成 绩：
教师签名：
月 日