合肥工业大学数据库上机实验2

实验5：创建和删除索引

 

1. 实验目的

 

1. 了解索引的类型和作用。
2. 学会用SQL语句对表创建和删除索引

 

1. 实验要求

 

1. 完成索引的创建和删除。
2. 注意操作结果的截图与保存，供撰写实验报告使用。

 

1. 实验内容

 

1. 创建索引

1. 在Supp表的Sname列上建立普通降序索引。
2. 在Project表的Jname列上建立唯一索引。
3. 在SPJ表的Sno(升序)和Pno(降序)上创建一个普通索引。

1. 检查创建索引的完成情况。
2. 用SQL语句删除索引

 

1. 实验步骤

 

在查询分析器中完成实验内容。

 

1. 实验结果

 

1. 1. 所用命令

 

create index supp_sname_desc_index on supp(sname desc);

 

create unique index project_jname_unique_index on project(jname);

 

create index spj_sno_asc_pno_desc_index on spj(sno asc, pno desc);

 

exec sp_helpindex supp;

 

exec sp_helpindex project;

 

exec sp_helpindex spj;

 

drop index supp_sname_desc_index on supp;

 

drop index project_jname_unique_index on project;

 

drop index spj_sno_asc_pno_desc_index on spj;

 

1. 1. 实验截图

 

 

 

图 5.１ 在表supp上建立普通索引supp_sname_desc_index

 

 

图 5.２ 在表project上建立unique索引project_jname_unique_index

 

 

图 5.３ 在表spj上建立普通索引spj_sno_asc_pno_desc_index

 

 

图 5.４ 查询表supp上的索引

 

图 5.５ 查询表project上的索引

 

 

图 5.６ 查询表spj上的索引

 

图 5.７ 分别删除三个表上的各索引

 

1. 实验总结

 

建立适当的索引有利于提高数据库数据的存取效率，但值得注意的是，对索引的维护会造成一定开销。因此，是否要建索引，要建什么类型的索引都值得斟酌考虑。比如，对于经常需要查询，且查询取出的数据量普遍小于全表10%时，或许就有建立索引的价值。索引的物理实现有很多，如Hash，B+树等，对数据库的存取效率有很大影响。

 

 

 

 

 

 

 

 

 

 

实验6：数据查询

 

1. 实验目的

 

1. 在查询分析器中使用SELECT语句进行查询。
2. 熟练掌握基本表的数据查询、数据排序和数据连接查询的操作方法。

 

1. 实验要求

 

1. 完成所要求的全部查询。
2. 注意操作结果的截图与保存，供撰写实验报告使用。

 

1. 实验内容

 

1. 根据教材P130第4题，采用SQL语句，对SPJDB数据库实现查询。
2. 根据教材P130第5题，采用SQL语句，对SPJDB数据库实现查询（1）-（7）。

 

1. 实验步骤

 

在查询分析器中完成实验内容。

 

1. 实验结果

 

1. 1. 所用命令

 

1.

(1)

select sno from spj where jno='j1';

 

(2)

select sno from spj where jno='j1' and pno = 'p1';

 

(3)

select sno from spj, part where spj.jno='j1' and spj.pno = part.pno and color='红';

 

(4)

select jno from spj where jno not in (select jno from spj, part, supp where supp.city = '天津' and color = '红' and supp.sno = spj.sno

and part.pno = spj.pno);

 

(5)

select pno from spj where sno = 's1';

select jno from spj where pno = 'p1' and jno not in (select pno from spj where pno = 'p2');

 

2.

(1)

select sname,city from supp;

(2)

select pname,color,weight from part;

(3)

select jno from spj where sno='s1';

(4)

select pname,qty from spj,part where part.pno=spj.pno and spj.jno='j2';

(5)

select pno from spj,supp where supp.sno=spj.sno and city='上海';

(6)

select jname from spj,supp,project where supp.sno=spj.sno and supp.city='上海' and project.jno=spj.jno;

(7)

注意: 

select jno from spj  where jno not in (select jno from spj,supp where supp.sno=spj.sno and supp.city='天津');

适用于jno是唯一或不唯一的情况.

 

注意: 

select jno from spj,supp where supp.sno=spj.sno and supp.city<>'天津';

适用于jno是唯一的情况

 

1. 1. 实验截图

 

 

图 5.１ 查询1

 

图 5.２ 查询2

 

 

图 5.３ 查询3

 

图 5.４ 查询4

 

 

图 5.５ 查询5

 

图 5.６ 查询6

 

 

图 5.７ 查询7

 

图 5.８ 查询8

 

 

图 5.９ 查询9

 

图 5.１０ 查询10

 

 

图 5.１１ 查询11

 

图 5.１２ 查询12

 

1. 实验总结

 

通过本次利用SQL语句完成相应的查询操作，主要利用SELECT....FROM.... WHERE语句完成。与关系代数进行对比，SELECT 子句实际就是投影，FROM子句就是广义笛卡尔积，WHERE子句就是选择操作。如果利用SQL语句进行连接操作的话，那么需要WHERE子句指明连接的条件。不过也可以利用嵌套子查询代替连接。

 

 

 

 

 

 

 

 

实验7：使用聚集函数的SELECT语句

 

1. 实验目的

 

1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。
2. 加深对SQL语言的嵌套查询语句的理解。

 

1. 实验要求

 

1. 完成所要求的全部查询。
2. 注意操作结果的截图与保存，供撰写实验报告使用。

 

1. 实验内容

 

在数据库SPJDB中用SQL语句实现如下查询：

1. 查询供应商的总数目。
2. 查询工程项目所在地为‘北京’的项目总数。
3. 查询每个供应商供应零件的总数量，给出供应商代码及零件总量。
4. 查询供应商数目超过3家的工程项目代码。

 

1. 实验步骤

 

在查询分析器中完成实验内容。

 

1. 实验结果

 

1. 1. 所用命令

 

(1)

select count(sname) as supp_count from supp;

 

(2)

select * from project where city = '北京';

select jname, count(city) as project_in_beijing from project where city = '北京' group by jname;

 

(3)

select * from (select sno, count(qty) as sno_qty from spj group by sno) as ha order by ha.sno_qty desc;

 

(4)

select * from (select jno, count(sno) as cnt from spj group by jno) as ha where ha.cnt > 3;

 

1. 1. 实验截图

 

 

图 5.１ 查询供应商的总数目

 

 

图 5.２ 查询工程项目所在地为‘北京’的项目总数

 

 

图 5.３ 查询每个供应商供应零件的总数量，给出供应商代码及零件总量1

 

 

图 5.４ 查询每个供应商供应零件的总数量，给出供应商代码及零件总量2

 

图 5.５ 查询供应商数目超过3家的工程项目代码

 

1. 实验总结

 

利用聚集函数可以很方便进行一些繁琐的操作，比如求平均值、求和COUNT(*)等。同时利用聚集函数时，要注意聚集函数只能用于SELECT 子句和 GROUP BY HAVING 子句中，不能用于WHERE 子句中。

 

 

 

 

 

 

 

 

 

 

 

实验8：视图的定义与使用

 

1. 实验目的

 

1. 理解视图的定义和作用
2. 掌握视图的创建和查询操作。

 

1. 实验要求

 

1. 完成所要求的视图定义和查询操作。
2. 注意操作结果的截图与保存，供撰写实验报告使用。

 

1. 实验内容

 

根据教材P130第9题，采用SQL语句，实现视图的定义及查询。

 

1. 实验步骤

 

在查询分析器中完成实验内容。

 

1. 实验结果

 

1. 1. 所用命令

 

create view exp8_test as select sno, spj.pno,qty from spj,project where spj.jno=project.jno and project.jname='三建';

 

select pno, qty from exp8_test;

 

select * from exp8_test where sno='s1';

 

1. 1. 实验截图

 

 

图 5.１创建视图exp8_test

 

图 5.２ 查询视图exp8_test

 

1. 实验总结

 

建立视图有很多好处，一是可以一定程度上对用户访问数据的范围进行限定，二是可以方便建立各种虚表，简化SQL语句的表达。