实验四 索引与视图

一、实验目的
1．掌握索引的创建与删除方法，理解索引的原理。
2．通过对视图的操作掌握视图虚表的本质，学会视图的定义方法。
3．掌握对视图的查询和更新操作，理解系统内部对视图查询和更新的视图消解方法，针对不同视图能够分析视图更新的限制。
二、实验内容
1．在aam数据库的student，course，teacher，sc和tc五张表中完成以下操作：
（1）创建名为xg_view的视图，在视图中显示所有信息工程学院教师的授课信息，包括教师姓名，职称，所授课程名称，授课班级，授课学期。

CREATE VIEW xg_view AS
SELECT t.Tname AS 教师姓名, t.Ttitle AS 职称, c.Cname AS 所授课程名称, tc.Sclass AS 授课班级, tc.Semester AS 授课学期
FROM teacher t
JOIN TC tc ON t.Tno = tc.Tno
JOIN Course c ON tc.Cno = c.Cno
WHERE t.Tcollege = '信息工程学院';

（2）创建每个学生已修课程总学分的视图，视图名为xf_view，要求在视图中显示学生学号、姓名和总学分。

CREATE VIEW xf_view AS
SELECT s.Sno AS 学生学号, s.Sname AS 学生姓名, SUM(c.Credit) AS 总学分
FROM Student s
JOIN SC sc ON s.Sno = sc.Sno
JOIN Course c ON sc.Cno = c.Cno
GROUP BY s.Sno;

（3）在创建的视图中查询大数据2班每位学生已修的总学分。

SELECT `学生姓名`, `总学分`
FROM xf_view
INNER JOIN student
ON student.Sclass='大数据2班' AND student.Sno=xf_view.`学生学号`;

（4）在视图上执行插入insert或者修改update或删除delete操作，验证视图xf_view中的数据可否更新，如果不能写出不能更新的原因。
视图是虚拟表，不是实际存储数据的表，所以不能在视图上执行插入、更新、删除操作。因为这些操作可能会影响到多张实际表的数据，而视图并没有实际存储这些数据，所以无法进行这些操作。
（5）通过alter table语句在student表的sname属性列上创建普通索引，索引表中sname降序排序。

ALTER TABLE Student ADD INDEX idx (Sname DESC);

（6）创建系部关系departments（dptid，dptname），其中dptid为系编号，是关系的主键，dptname为系名称，在建表的同时为dptname列创建唯一性索引，索引名为un_dptname，然后查看departments表的索引情况。

CREATE TABLE departments (
  dptid INT PRIMARY KEY,
  dptname VARCHAR(255) UNIQUE,
  INDEX un_dptname (dptname)
);
SHOW INDEX FROM departments;

2．在project数据库的s，p，j和spj表中完成以下操作：
（1）创建零件名为螺丝刀的供应情况的视图sd_view，视图中显示供应商名（Sname），零件名（Pname），零件重量（Weight），工程项目代码（Jno），供应数量（QTY）。

CREATE VIEW sd_view AS
SELECT s.sname AS 供应商名, p.pname AS 零件名, p.weight AS 零件重量, j.jno AS 工程项目代码, spj.qty AS 供应数量
FROM s
JOIN spj ON s.sno = spj.sno
JOIN p ON spj.pno = p.pno
JOIN j ON spj.jno = j.jno
WHERE p.pname = '螺丝刀';

（2）修改sd_view视图，让该视图中不再显示螺丝刀的供应情况，修改后显示螺栓的供应情况，视图中的属性列不变。

ALTER VIEW sd_view AS
SELECT s.sname AS 供应商名, p.pname AS 零件名, p.weight AS 零件重量, j.jno AS 工程项目代码, spj.qty AS 供应数量
FROM s
JOIN spj ON s.sno = spj.sno
JOIN p ON spj.pno = p.pno
JOIN j ON spj.jno = j.jno
WHERE p.pname = '螺栓';

（3）在视图sd_view中查询供应数量在400到600之间的供应商姓名。
SELECT 供应商名 FROM sd_view

WHERE 供应数量 BETWEEN 400 AND 600;

（4）如果只想让某个用户看到和操作三建工程项目零件供应情况的数据，其它数据对该用户隐藏，应该如何给该用户展示数据？
可以创建一个具有新用户权限，授予该用户对视图sd_view的部分查询权限。
（5）通过create index语句在spj表的sno、pno和jno属性列上创建复合索引，索引名为fh_sno_pno_jno，然后查看spj表的索引情况。

CREATE INDEX fh_sno_pno_jno ON spj(sno, pno, jno);
SHOW INDEX FROM spj;