《数据库原理》实验报告DB4——视图与索引

一. 实验内容、步骤以及结果

1． 在Student数据库中，用SQL语句创建一个选修了数据库课程并且是2001年出生的学生的视图，视图中包括学号，姓名，性别，成绩。（5分)

create view student_2001(sno,sname,sgender,grade)
as
select s.sno,sname,sgender,grade from s,sc,c
where s.sno = sc.sno and sc.cno = c.cno and cname = '数据库'
and birth like '2001%';

2． 用两种不同的SQL语句创建以下视图（视图名：V_SPJ）。(第五版教材第三章第九题)（10分)

①：

create view V_SPJ(sno,pno,qty)
as
select sno,pno,qty from spj
where jno in
(
	select jno from j
	where jname = '三建'
);

②：

create view V_SPJ(sno,pno,qty)
as
select sno,pno,qty from spj,s
where spj.jno = j.jno and jname = '三建';

3． 用SQL语句完成下面的视图查询。（10分)

(1) 找出三建工程项目使用的各种零件代码及其数量。

select pno,sum(qty) from V_SPJ
group by pno;

(2) 找出供应商S1的供应情况。

select * from V_SPJ
where sno = 's1';

4． 用SQL语句完成视图的数据更新。(15分)

(1) 给视图V_SPJ中增加一条数据。

提示：

1. SPJ表中JNO允许为空时，数据可以插入基本表，此时JNO为NULL，由于JNO为NULL，所以视图中没有该条数据。
2. SPJ表中JNO不能为空时，可以使用instead of触发器实现。MySQL不支持视图上创建触发器，在MySQL环境下不需完成该题。

(2) 修改视图V_SPJ中的任意一条数据的供应数量。

update V_SPJ set qty = 1000 
where sno = 's1' and pno = 'p1';

(3) 删除视图V_SPJ中的任意一条数据。

delete from V_SPJ where sno = 's1' and pno = 'p1';

5． 用图形用户界面对Student数据库中C表的Cno字段创建一个降序排列的索引，索引名称IX_CNo。(5分)

略

6． 使用SQL语句对Student数据库完成以下的索引操作。（15分）

(1) 在C表的CName属性上创建一个非唯一性的普通索引，索引名IX_CName。

create index IX_CName on c(cname);

(2) 在S表上创建一个名为IX_ngd的复合索引，该索引是针对sname, sgender, sdept属性集建立的升序索引。

create index IX_ngd on s(sname asc, sgender asc, sdept asc);

提示：可以使用show index from tablename查看该表上的索引信息。

(3) 删除C表的索引IX_CNo。

drop index IX_CNo on c;

(4) 基于以上的索引（c表：cno的主键索引，cname的普通索引；S表：sno的主键索引，IX_nga复合索引），用explain得到的查询计划观察每个查询语句中索引的使用情况。

① explain select * from c;

未使用索引

② explain select * from c where cname=’数据库’ ;

使用主键索引

③ explain select * from c where cname like ‘数据库%’;

未使用索引

④ explain select * from s where sname =‘张立’ and sno=‘2001’;

未使用索引

⑤ explain select * from s where sname =‘张立’ and sgender=‘男’ and sdept=‘IS’;

使用复合索引

⑥ explain select * from s where sname =‘张立’ and sgender=‘男’;

使用复合索引

⑦ explain select * from s where sname =‘张立’;

使用复合索引

⑧ explain select * from s where sgender =‘男’;

未使用索引

⑨ explain select * from s where sgender =‘男’ and sdept=‘IS’;

未使用索引

具体情况之后补充

7． 假设有一个如下的基本表userinfo，自己设计一个实验验证索引对数据库查询效率的提升作用。（30分）

create table userinfo
(
	user_id int primary key,  //用户ID
	username varchar(10),   //用户名
	gender char(1), 		//性别
	age int,			//年龄
       c_id int			//学院编号
)；

(1) 验证有索引和无索引的查询效率差异。

生成一万条测试数据插入表中。

• 有索引：

explain SELECT * FROM userinfo WHERE age = 30;

• 无索引：

explain SELECT * FROM userinfo WHERE age = 30;

对有索引和无索引的查询效率对比可以得到，首先看type列，无索引需要扫描全表，而有索引是ref类型，效率提高；再看rows列，有索引的预估扫描行数约为无索引的1%，可以看出效率大大提高。

(2) 验证单字段窄索引和多字段构成的宽索引的查询效率异，注意理解宽索引中的最左匹配原则。

• 单字段窄索引：
  建立一个窄索引，索引内仅有age

explain SELECT username,age,gender,c_id FROM userinfo WHERE gender = 'w' and age = 30 and c_id = 2;
 

• 多字段宽索引：
  建立一个宽索引，索引内包含age，gender和c_id（按顺序）

explain SELECT username,age,gender,c_id FROM userinfo WHERE gender = 'w' and age = 30 and c_id = 2;

对比可得宽索引效率要高于窄索引，它的预期查询行数为窄索引的11/196。
宽索引中的最左匹配原则是指，在宽索引的查询中，查询条件最左优先，在检索数据时从联合索引的最左边开始匹配，若不符合顺序，则无法使用所有的宽索引。

二.实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)