ACCESS数据库实验指导书252710853
数据库原理及应用实验指导书
(ACCESS数据库)
指导老师:陈俊智
昆明理工大学国土资源工程学院学院
2012年3月
目 录
实验(一):SQL数据定义功能
实验(二):练习SQL数据操纵功能 实验(三):数据库综合设计
实验(一):SQL数据定义功能 一、实验内容及要求
1. 使用SQL语句建立学生管理系统相关的
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
,同时完善各表的相关完整性约束。其中带有下划线的为主码
学院(学院代码,学院名称)
create table 学院
(学院代码 char(8) primary key, 学院名称 char(8));
学生(学号,姓名,性别,学院代码) ,学院代码为外码
create table 学生
(学号 char(8) not null unique, 姓名 char(8),
性别 char(8),
学院代码 char(8),
primary key(学号),
foreign key(学院代码) references 学院(学院代码));
教师(教师号,教师姓名,学院代码),学院代码为外码
create table 教师
(
教师号 char(8) primary key, 教师姓名 char(8),
学院代码 char(8),
foreign key(学院代码) references 学院(学院代码));
课程(课程号,课程名,学时)
create table 课程
(
课程号 char(8) primary key, 课程名 char(8),
学时 char(8));
学习(学号,课程号,成绩),学号为外码,课程号为外码
create table 学习
(学号 char(8),
课程号 char(8),
成绩 char(8),
primary key(学号,课程号),
foreign key(学号) references 学生(学号), foreign key(课程号) references 课程(课程号));
开课(教师号,课程号),教师号为外码,课程号为外码
create table 开课
(教师号 char(8),
课程号 char(8),
primary key(教师号,课程号),
foreign key(教师号) references 教师(教师号),
foreign key(课程号) references 课程(课程号));
2. 对各表进行增加、删除、修改属性操作
添加操作:
对学生表添加出生日期字段,和家庭地址字段,教师表增加性别字段,出生日期字段,对课程表增加先修课程字段,并且先修课程为本关系外码。
可以在access图形界面中用鼠标点击单元格‘添加新字段’右键选择‘插入列’
新建一个列,
同时也可以用sql建立表例如
alter table 学生 add 出生日期 char(8);
alter table 学生 add 地址 char(20);
alter table 教师 add 性别 char(8);
alter table 教师 add 出生日期 date;
alter table 课程 add 先修课程 char(8) ;
alter table 课程 foreign key(先修课程) references 课程(先修课程);
删除操作:
删除学生表家庭地址字段,删除教师表出生日期字段 alter table 学生 drop column 地址;
alter table 教师 drop column出生日期;
修改操作:
修改学生姓名字段,该字段值不允许取空值
Alter table 学生 alter column 姓名 char(20) not null unique;
3. 建立索引
为学生表在学生姓名上建立名为sname-index 的索引,在学院代码字段上建立名为dept-index,降序。
Create unique index sname_index on 学生(姓名);
Create unique index dept_index on 学院(学院代码 desc) 4. 利用ACCESS 2003 /2007完善各表的数据内容
数据可以由所建表中直接输入,也可以由 insert into 学生 values(……….. )插入
例如在学生表中插入一组数据
Insert into 学院 values ('101','计算机' );
5. 建立tempstudent表,表模式自行定义,之后将其删除 建立tempstudent表
create table tempstudent (sno char(8),
sname char(8));
删除表可以由语句 drop table tempstudent cascade; 执行 二、实验目的
熟练掌握SQL的各种数据定义功能,包括
1.定义表的功能,包括主码和外码的定义
2.修改表的定义功能,包括增加属性,删除属性,修改属性类型 4.建立和删除索引操作,理解索引的作用
5.删除表功能
三、实验步骤及运行结果
四、实验体会
通过本次实验我了解到了数据库基本的建表方法,明白了如何去对一
个表进行插入修改删除和建立索引,掌握了基本的建表方法
实验(二):练习SQL数据操纵功能
一、实验内容及要求
建表截图
学院表
学生表
教师表
学习表
开课表
课程表
1(求选修了某个老师上课的学生,成绩在90分以上的姓名、课程名
称和成绩;
SELECT 姓名, 课程名, 成绩
FROM 学生, 教师, 学习, 课程, 开课
WHERE 学生.学号=学习.学号
And 学习.课程号=课程.课程号
And 课程.课程号= 开课.课程号
And 开课.教师号=教师.教师号
And 教师姓名='张强'
And 成绩 >'90';
2(求选修了某个老师所授全部课程的学生姓名和学院名称;
select 姓名,学院名称
from 学生,学院
where 学生.学院代码=学生.学院代码 and 姓名 in
(
select distinct 姓名
from 学生,学院
where 学号 in
(select 学号
from 学习
where 课程号 in
(select 课程号
from 开课
where 教师号 in
(select 教师号
from 教师
where 教师姓名='王强')))) and 学生.学院代码=学院.学院代码;
3(求没有选修某门课程的学生学号和姓名;
select distinct 学生.学号,姓名 from 学生,课程,学习
where 学生.学号=学习.学号
and 学习.课程号=课程.课程号 and 学生.学号 not in (select 学生.学号
from 学生,课程,学习
where 学生.学号=学习.学号
and 学习.课程号=课程.课程号 and 课程名='数据结构'
);
4(求至少选修了两门课程的学生学号;
select 学号,count(课程号) as 选课数
from 学习
where 学号 between '08101'and '08108'
group by 学号
having count(课程号)>=2;
5(求某门课程不及格学生姓名和考试成绩 select 姓名,成绩
from 学生,学习,课程
where 学生.学号=学习.学号
and 学习.课程号=课程.课程号
and 课程名='数据库'
and 成绩<'60';
6(求至少选修了与某个同学选修的课程相同的学生学号
select distinct 学号
from 学习 as a
where not exists (select *
from 学习 as b
where 学号='08101' and not exists (select *
from 学习 as c
where a.学号=b.学号
and b.课程号=c.课程号));
7(求至少选修了某两门课程的学生姓名和学院名称。
select 姓名,学院名称
from 学生,学习,课程,学院 where 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 学生.学院代码=学院.学院代码 and 课程名='数据库'
and 学生.学号 in
(select 学生.学号
from 学生,学习,课程,学院
where 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 学生.学院代码=学院.学院代码 and 课程名='数据结构');
8(查询某个同学没有选修的课程号和课程名
select 课程号,课程名
from 课程
where 课程.课程号 not in
(select 课程.课程号
from 学生,学习,课程
where 学生.学号=学习.学号
and 学习.课程号=课程.课程号
and 姓名='顾晓娜');
9(查询没有被任何学生选修的课程的课程号
select 课程号
from 课程
where 课程.课程号 not in (select 课程.课程号
from 课程,学习
where 课程.课程号=学习.课程号);
10(求选修了全部课程的学生姓名
select 姓名
from 学生
where not exists
(select *
from 学习
where not exists
(select *
from 课程
where 学生.学号=学习.学号
and 学习.课程号=课程.课程号));
11(查询各学院某门课程的平均分,并按照成绩从高到低的顺序排列。
select 学院名称,avg(成绩) as 平均成绩
from 学生,学院,学习,课程
where 学生.学号=学习.学号
and 学生.学院代码=学院.学院代码
and 学习.课程号=课程.课程号
and 课程名='数据库'
group by 学院名称
order by avg(成绩) desc
12(查询选修数据库原理课程的学生姓名和所在院系,结果按各院系
排列,同时成绩从高到低排列。
select 学院名称,成绩
from 学生,学院,学习,课程
where 学生.学号=学习.学号
and 学生.学院代码=学院.学院代码
and 学习.课程号=课程.课程号
and 课程名='数据库'
order by 学院名称,成绩 desc
13(求学时在30-45之间(含30和45)的课程的课程号和课程名称
及授课教师。
select 课程.课程号,课程名,教师姓名
from 教师,开课,课程
where 教师.教师号=开课.教师号 and 开课.课程号=课程.课程号 and 学时 between '30' and '45';
14(检索选修数据库原理课程的最高分学生的姓名
select 姓名,成绩
from 学生,学习,课程
where 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 课程名='数据库'
and 成绩=
(select max(成绩)
from 学生,学习,课程
where 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 课程名='数据库');
15(查询选课人数超过50(根据情况自己定人数)人的课程的课程
号及课程名。
select 课程号,课程名
from 课程
where 课程名 in
(select 课程名
from 课程,学习
where 学习.课程号=课程.课程号
group by 课程名
having count(课程.课程号)>=2);
16(自行提出查询要求,内容包括并运算、差运算、交运算、笛卡儿
积运算、连接运算、自然连接运算以及复杂统计表。
16查询选修了张强老师和王慧老师却没有选修王鹏老师课程的学生
的学号和姓名和成绩,并且按成绩由高到低排序.
select 学生.学号,姓名,成绩
from 学生,学习
where 学生.学号=学习.学号
and 学习.学号 in
(select 学号
from 开课,教师,学习
where 学习.课程号=开课.课程号
and 开课.教师号=教师.教师号
and 教师姓名='张强'
and 学号 in
(select 学号
from 开课,教师,学习
where 学习.课程号=开课.课程号
and 开课.教师号=教师.教师号
and 教师姓名='王慧'
and 学号 not in
(select 学号
from 开课,教师,学习
where 学习.课程号=开课.课程号
and 开课.教师号=教师.教师号
and 教师姓名='王强')))
order by 成绩 desc;
二、实验目的
使用SQL语句进行各种查询
1、熟练掌握单表查询,包括 (1) 选择表中的若干列(投影) (2) 选择表中的若干元组(选择) (3) 对查询结果排序 (4) 使用集函数 (5) 对查询结果分组
2、熟练掌握复杂查询,包括1) 等值与非等值连接查询 2) 自身连接:一个表与其自己进行连接。3) 外连接查询4) 复合条件连接:WHERE 子句中可以有多个连接条件。
3、熟练掌握嵌套查询,包括1) 带有IN谓词的子查询2) 带有比较运算符的子查询(子查询一定要跟在比较符之后)3) 带有ANY或ALL谓词的子查询(使用ANY和ALL时必须同时使用比较运算符)4) 带有EXISTS谓词的子查询(查询结果不返回任何数据,只产生逻辑“真”或“假”)
4、使用SQL和ACCESS结合完成一些复杂的统计
三、实验步骤及运行结果
四、实验体会
通过本次实验我了解到了如何在语句中进行查询,而且还学会了很多不同的查询方法,明白了如何通过多种途径来实现自己的查询结果,
accsse中得到了验证。 最总都在
实验(三):数据库综合设计
1 数据库设计
1.1公司描述
该虚拟公司的主营业务为日常商品的销售,业务描述为其上游有供应商为其提供所有商品,下游面向所有个人主体客户和法人客户。公司有送货上门服务,是由公司的员工完成。
1.2 部分ER图
方框表示实体,菱形表示实体之间的关系。数据库的中表是根据ER图中的实体和关系来确定的。如图1-1所示
供应商供应商 供应供应
订单明细订单明细
商品商品 订单订单 配送配送 包括包括
填写填写
雇员雇员 销售销售
顾客顾客
我公司我公司 属于属于
图图1-1 1
1.3数据库逻辑结构设计(数据库的表结构设计)
根据ER图可以抽象出七个实体和六个关系,经分析得出数据库逻辑结构,也就是数据库中的表:供应商表、商品表、顾客表、雇员表、订单表、订单明细表。
各个表的结构如下:(红色字体为表名、括号中为标的属性、中括号内为属性的类型、有下划线的属性为表的主键)
供应商(供应商ID[自动编号]、供应商名称[文本]、供应商地址[文本]、供应商电话[文本]、供应商传真[文本])
商品(商品ID[自动编号]、商品名称[文本]、单价[货币]、供应商ID[数字])
顾客(顾客ID[自动编号] 、顾客名[文本]、顾客地址[文本]、顾客电话[文本])
雇员(雇员ID[自动编号]、雇员姓名[文本]、雇员电话[文本]、雇员职位[文本])
订单(订单ID[自动编号]、订单编号[文本]、顾客ID[数字]、雇员ID[数字]、订单日期[时间日期]、送货日期[时间日期])
订单明细(订单明细ID[自动编号]、订单ID[数字]、商品ID[数字]、商品数量[数字])
2 数据库实现
2.1 新建数据库
新建一个数据库将其命名为自己公司的名称,如图2-1所示。
1
2
图2-1
2.2新建表
用“设计视图”进行表设计,如图2-2所示。
图2-2
建立“供应商”表,并将“供应商ID”设置为主键,如图2-3所示
图2-3
然后根据前面设计的数据库表结构分别建立商品、顾客、我公司、雇员、订单和订单明细6个表。最终建成的表如图4——9所示。 (1) “商品”表,如图2-4所示
图2-4
(2)“顾客”表,如图2-5所示
图2-5
(3)“订单”表,如图2-6所示
图2-6
(4)“订单明细”表,如图2-7所示
图2-7
(6)“雇员”表,如图2-8所示 (7)“我公司”表,如图2-9所示
图2-8 图2-9
为各个表添加记录。
(1)订单表:如图2-10所示
图2-10
(2)订单明细表:如图2-11所示
图2-11
(3)供应商表:如图2-12所示
图2-12
(4)顾客表:如图2-13所示
图2-13
(5)雇员表:如图2-14所示
图2-14
(6)商品表,如图2-15所示
图2-15
为表之间建立关系,如图2-16所示
图2-16
2.3查询
在设计一个数据库时,为了节省存储空间,常常把数据分类,并分别存放在多个表里。尽管可以在基本表中可以进行许多操作,如浏览、筛选、更新等,但很多时候还是需要检索一个或多个表(或查询)中符合条件的数据,将这些数据集合在一起,执行浏览、计算等各种操作。查询实际上就是将这些分散的数据再
集合起来。使用查询可以执行一组选定的数据记录集合,虽然这个记录在数据库中实际上并不存在,只是在运行查询时,ACCESS才从查询源数据表中创建它,但正是这个特性,使查询具有了灵活方便的数据操纵能力。
2.3.1查询的基本作用
(1)通过查询浏览表中的数据,分析数据和修改数据
(2)利用查询可以使用户的注意力集中在自己感兴趣的数据上,而将当前不需要的数据排除在查询之外。
(3)将经常处理的原始数据或统计计算定义为查询,可大大简化数据的处理工作。
(4)查询的结果集可以用于生成新的基本表,可以进行新的查询,还可以为窗体、报表提供数据。
下面就根据查询的四个主要作用,针对我们的公司数据库进行查询设计与实施。
2.3.2.查询的设计与实施
1)通过查询浏览表中的数据
实例:查询出哪些供应商提供哪些商品,操作过程如图2-17所示。 选择需要的属性(字段),将表中的字段“拖动”到如图2-28所示的2位置
,
图2-17
运行效果为“供应商供应的商品”,如图2-18所示
图2-18
2)建立有查询条件的查询
实例:查询商品价格小于2.00元的所有商品
在单价下面的条件中输入” >0.5”,保存后运行,如图2-19 。
图2-19
2.4窗体
在Access中,窗体是一种数据对象的格式,是输入和维护表中数据的另一
种方式。窗体起着联系数据库用户与用户的桥梁作用,窗体提供了查阅、新建、编辑和删除数据的最富弹性的方法。
窗体与下一章将要讲到的报表都是用来进行数据库数据维护的,但窗体主要用于数据的输入,报表则用于屏幕或打印输出的窗体中查阅的数据。 2.4.1窗体的基本作用
1)数据的显示和编辑
2)数据输入
3)应用程序流控制
4)信息显示和数据打印
2.4.2窗体的设计与实施
下面根据窗体的四个作用来设计并实施四个窗体。
1)数据的显示和编辑
(1)使用“窗体向导”创建窗体,如图2-20所示,。
图2-20
2)数据输入
本实例是创建一个带有子窗体的窗体,效果图如图2-21所示。
左侧红框内的内容是订 单相关信息,即哪位顾客
的哪个订单
为子窗体,其中显
示主窗体的订单中
订购的所有商品的
名称、数量和单价
图2-21
该窗体中显示的字段分别涉及订单表(订单ID、订单编号、顾客ID),顾客表(顾客名),订单明细表(订单明细ID、商品ID、商品数量)和商品表(商品名称、单价)四个表。
3)应用程序流控制
窗体不只是数据的入口。例如,切换面板窗体就是一种具有特殊功用的窗体,它包含了一些命令按钮,用于启动其他窗体和报表。在Access中包含了一个称为切换面板管理器的使用工具,可以用来为数据库创建和编辑切换面板。使用方法是:选择“工具”菜单的“数据库实用工具”,“切换面板管理器”命令。如图2-22所示。
图2-22
(1)窗体切换面板
按照图55中操作,打开切换面板管理器。如图2-23所示,点击“新建”,输入“窗体切换面板”。然后选中“窗体切换面板”,单击“编辑”出现如图2-24所示界面。
图2-23 图2-24
在图2-24中点击新建,出现图2-25,然后在如图2-25所示1、2、3输入“输入|产查看 供应商”、选择“在编辑模式下打开窗体”、选择“供应商”。
1
2 3
图2-25
以同样方式,为顾客、雇员、商品和订单建立切换面板项目。
(2)主切换面板
如图2-23所示,选中“主切换面板”,单击“编辑”。如图2-24所示,为主切换面板添加按钮,点击“新建”后,按照图2-25所示1、2、3输入 “输入,查看 订单”、“选择“在编辑模式下打开窗体”、选择“订单主”和“输入,查看其它信息((((”、选择“转至切换面板”、选择“窗体切换面板”,和“退出该数据库”、选择“退出应用程序”。最终主切换面板如图2-26所示
图2-26
然后分别“关闭”,“编辑切换面板页”和“切换面板管理器”。
最后设置“主切换面板”自动打开功能,就是在进入数据库时候,主切换面板自动打开。
选择“宏”,单击“新建”,如图2-27所示。
图2-27
在图2-28所示界面中,在1所指处选择“OpenForm”命令,在2所指处“窗体名称”选择“切换面板”。最后单击“保存”,将该宏命名为“autoexec”.
1
2
图2-28
关闭数据库,在重新打开时数据库将自动打开主切换面板。如图2-29所示
图2-29