ACCESS数据库实验指导书.docx
《ACCESS数据库实验指导书.docx》由会员分享，可在线阅读，更多相关《ACCESS数据库实验指导书.docx（38页珍藏版）》请在冰豆网上搜索。
ACCESS数据库实验指导书
数据库原理及应用实验指导书
（ACCESS数据库）
指导老师：
陈俊智
昆明理工大学国土资源工程学院学院
2012年3月
目录
实验
（一）：
SQL数据定义功能
实验
（二）：
练习SQL数据操纵功能
实验（三）：
数据库综合设计
实验
（一）：
SQL数据定义功能
一、实验内容及要求
1.使用SQL语句建立学生管理系统相关的表，同时完善各表的相关完整性约束。
其中带有下划线的为主码
学院（学院代码，学院名称）
createtable学院
（学院代码char（8）primarykey,
学院名称char（8））;
学生（学号，姓名，性别，学院代码），学院代码为外码
createtable学生
（学号char（8）notnullunique,
姓名char（8）,
性别char（8）,
学院代码char（8）,
primarykey（学号）,
foreignkey（学院代码）references学院（学院代码））;
教师（教师号，教师姓名，学院代码），学院代码为外码
createtable教师
（
教师号char（8）primarykey,
教师姓名char（8）,
学院代码char（8）,
foreignkey（学院代码）references学院（学院代码））;
课程（课程号，课程名，学时）
createtable课程
（
课程号char（8）primarykey,
课程名char（8）,
学时char（8））;
学习（学号，课程号，成绩），学号为外码，课程号为外码
createtable学习
（学号char（8）,
课程号char（8）,
成绩char（8）,
primarykey（学号,课程号）,
foreignkey（学号）references学生（学号）,
foreignkey（课程号）references课程（课程号））;
开课（教师号，课程号），教师号为外码，课程号为外码
createtable开课
（教师号char（8）,
课程号char（8）,
primarykey（教师号,课程号）,
foreignkey（教师号）references教师（教师号）,
foreignkey（课程号）references课程（课程号））;
2.对各表进行增加、删除、修改属性操作
添加操作：
对学生表添加出生日期字段，和家庭地址字段，教师表增加性别字段，出生日期字段，对课程表增加先修课程字段，并且先修课程为本关系外码。
可以在access图形界面中用鼠标点击单元格‘添加新字段’右键选择‘插入列’
新建一个列，
同时也可以用sql建立表例如
altertable学生add出生日期char（8）;
altertable学生add地址char（20）;
altertable教师add性别char（8）;
altertable教师add出生日期date;
altertable课程add先修课程char（8）;
altertable课程foreignkey（先修课程）references课程（先修课程）;
删除操作：
删除学生表家庭地址字段，删除教师表出生日期字段
altertable学生dropcolumn地址;
altertable教师dropcolumn出生日期;
修改操作：
修改学生姓名字段，该字段值不允许取空值
Altertable学生altercolumn姓名char（20）notnullunique;
3.建立索引
为学生表在学生姓名上建立名为sname-index的索引，在学院代码字段上建立名为dept-index，降序。
Createuniqueindexsname_indexon学生（姓名）;
Createuniqueindexdept_indexon学院（学院代码desc）
4.利用ACCESS2003/2007完善各表的数据内容
数据可以由所建表中直接输入，也可以由insertinto学生values（………..）插入
例如在学生表中插入一组数据
Insertinto学院values（'101','计算机'）;
5.建立tempstudent表，表模式自行定义，之后将其删除
建立tempstudent表
createtabletempstudent
（snochar（8）,
snamechar（8））;
删除表可以由语句droptabletempstudentcascade;执行
二、实验目的
熟练掌握SQL的各种数据定义功能，包括
1.定义表的功能，包括主码和外码的定义
2.修改表的定义功能，包括增加属性，删除属性，修改属性类型
4.建立和删除索引操作，理解索引的作用
5.删除表功能
三、实验步骤及运行结果
四、实验体会
通过本次实验我了解到了数据库基本的建表方法，明白了如何去对一个表进行插入修改删除和建立索引，掌握了基本的建表方法
实验
（二）：
练习SQL数据操纵功能
一、实验内容及要求
建表截图
学院表
学生表
教师表
学习表
开课表
课程表
1．求选修了某个老师上课的学生，成绩在90分以上的姓名、课程名称和成绩；
SELECT姓名,课程名,成绩
FROM学生,教师,学习,课程,开课
WHERE学生.学号=学习.学号
And学习.课程号=课程.课程号
And课程.课程号=开课.课程号
And开课.教师号=教师.教师号
And教师姓名='张强'
And成绩>'90';
2．求选修了某个老师所授全部课程的学生姓名和学院名称；
select姓名,学院名称
from学生,学院
where学生.学院代码=学生.学院代码
and姓名in
（
selectdistinct姓名
from学生,学院
where学号in
（select学号
from学习
where课程号in
（select课程号
from开课
where教师号in
（select教师号
from教师
where教师姓名='王强'））））
and学生.学院代码=学院.学院代码;
3．求没有选修某门课程的学生学号和姓名；
selectdistinct学生.学号,姓名
from学生,课程,学习
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and学生.学号notin
（select学生.学号
from学生,课程,学习
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and课程名='数据结构'
）;
4．求至少选修了两门课程的学生学号；
select学号,count（课程号）as选课数
from学习
where学号between'08101'and'08108'
groupby学号
havingcount（课程号）>=2;
5．求某门课程不及格学生姓名和考试成绩
select姓名,成绩
from学生,学习,课程
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and课程名='数据库'
and成绩<'60';
6．求至少选修了与某个同学选修的课程相同的学生学号
selectdistinct学号
from学习asa
wherenotexists
（select*
from学习asb
where学号='08101'
andnotexists
（select*
from学习asc
wherea.学号=b.学号
andb.课程号=c.课程号））;
7．求至少选修了某两门课程的学生姓名和学院名称。
select姓名,学院名称
from学生,学习,课程,学院
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and学生.学院代码=学院.学院代码
and课程名='数据库'
and学生.学号in
（select学生.学号
from学生,学习,课程,学院
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and学生.学院代码=学院.学院代码
and课程名='数据结构'）;
8．查询某个同学没有选修的课程号和课程名
select课程号,课程名
from课程
where课程.课程号notin
（select课程.课程号
from学生,学习,课程
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and姓名='顾晓娜'）;
9．查询没有被任何学生选修的课程的课程号
select课程号
from课程
where课程.课程号notin
（select课程.课程号
from课程,学习
where课程.课程号=学习.课程号）;
10．求选修了全部课程的学生姓名
select姓名
from学生
wherenotexists
（select*
from学习
wherenotexists
（select*
from课程
where学生.学号=学习.学号
and学习.课程号=课程.课程号））;
11．查询各学院某门课程的平均分，并按照成绩从高到低的顺序排列。
select学院名称,avg（成绩）as平均成绩
from学生,学院,学习,课程
where学生.学号=学习.学号
and学生.学院代码=学院.学院代码
and学习.课程号=课程.课程号
and课程名='数据库'
groupby学院名称
orderbyavg（成绩）desc
12．查询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列。
select学院名称,成绩
from学生,学院,学习,课程
where学生.学号=学习.学号
and学生.学院代码=学院.学院代码
and学习.课程号=课程.课程号
and课程名='数据库'
orderby学院名称,成绩desc
13．求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师。
select课程.课程号,课程名,教师姓名
from教师,开课,课程
where教师.教师号=开课.教师号
and开课.课程号=课程.课程号
and学时between'30'and'45';
14．检索选修数据库原理课程的最高分学生的姓名
select姓名,成绩
from学生,学习,课程
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and课程名='数据库'
and成绩=
（selectmax（成绩）
from学生,学习,课程
where学生.学号=学习.学号
and学习.课程号=课程.课程号
and课程名='数据库'）;
15．查询选课人数超过50（根据情况自己定人数）人的课程的课程号及课程名。
select课程号,课程名
from课程
where课程名in
（select课程名
from课程,学习
where学习.课程号=课程.课程号
groupby课程名
havingcount（课程.课程号）>=2）;
16．自行提出查询要求，内容包括并运算、差运算、交运算、笛卡儿积运算、连接运算、自然连接运算以及复杂统计表。
16查询选修了张强老师和王慧老师却没有选修王鹏老师课程的学生的学号和姓名和成绩，并且按成绩由高到低排序.
select学生.学号,姓名,成绩
from学生,学习
where学生.学号=学习.学号
and学习.学号in
（select学号
from开课,教师,学习
where学习.课程号=开课.课程号
and开课.教师号=教师.教师号
and教师姓名='张强'
and学号in
（select学号
from开课,教师,学习
where学习.课程号=开课.课程号
and开课.教师号=教师.教师号
and教师姓名='王慧'
and学号notin
（select学号
from开课,教师,学习
where学习.课程号=开课.课程号
and开课.教师号=教师.教师号
and教师姓名='王强'）））
orderby成绩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.3数据库逻辑结构设计（数据库的表结构设计）
根据ER图可以抽象出七个实体和六个关系，经分析得出数据库逻辑结构，也就是数据库中的表：
供应商表、商品表、顾客表、雇员表、订单表、订单明细表。
各个表的结构如下:
（红色字体为表名、括号中为标的属性、中括号内为属性的类型、有下划线的属性为表的主键）
供应商（供应商ID[自动编号]、供应商名称[文本]、供应商地址[文本]、供应商电话[文本]、供应商传真[文本]）
商品（商品ID[自动编号]、商品名称[文本]、单价[货币]、供应商ID[数字]）
顾客（顾客ID[自动编号]、顾客名[文本]、顾客地址[文本]、顾客电话[文本]）
雇员（雇员ID[自动编号]、雇员姓名[文本]、雇员电话[文本]、雇员职位[文本]）
订单（订单ID[自动编号]、订单编号[文本]、顾客ID[数字]、雇员ID[数字]、订单日期[时间日期]、送货日期[时间日期]）
订单明细（订单明细ID[自动编号]、订单ID[数字]、商品ID[数字]、商品数量[数字]）
2数据库实现
2.1新建数据库
新建一个数据库将其命名为自己公司的名称，如图2-1所示。
2.2新建表
用“设计视图”进行表设计，如图2-2所示。
建立“供应商”表，并将“供应商ID”设置为主键，如图2-3所示
然后根据前面设计的数据库表结构分别建立商品、顾客、我公司、雇员、订单和订单明细6个表。
最终建成的表如图4——9所示。
（1）“商品”表，如图2-4所示
（2）“顾客”表，如图2-5所示
（3）“订单”表，如图2-6所示
（4）“订单明细”表，如图2-7所示
（6）“雇员”表，如图2-8所示 （7）“我公司”表，如图2-9所示
为各个表添加记录。
（1）订单表：
如图2-10所示
（2）订单明细表：
如图2-11所示
（3）供应商表：
如图2-12所示
（4）顾客表：
如图2-13所示
（5）雇员表：
如图2-14所示
（6）商品表，如图2-15所示
为表之间建立关系，如图2-16所示
2.3查询
在设计一个数据库时，为了节省存储空间，常常把数据分类，并分别存放在多个表里。
尽管可以在基本表中可以进行许多操作，如浏览、筛选、更新等，但很多时候还是需要检索一个或多个表（或查询）中符合条件的数据，将这些数据集合在一起，执行浏览、计算等各种操作。
查询实际上就是将这些分散的数据再集合起来。
使用查询可以执行一组选定的数据记录集合，虽然这个记录在数据库中实际上并不存在，只是在运行查询时，ACCESS才从查询源数据表中创建它，但正是这个特性，使查询具有了灵活方便的数据操纵能力。
2.3.1查询的基本作用
（1）通过查询浏览表中的数据，分析数据和修改数据
（2）利用查询可以使用户的注意力集中在自己感兴趣的数据上，而将当前不需要的数据排除在查询之外。
（3）将经常处理的原始数据或统计计算定义为查询，可大大简化数据的处理工作。
（4）查询的结果集可以用于生成新的基本表，可以进行新的查询，还可以为窗体、报表提供数据。
下面就根据查询的四个主要作用，针对我们的公司数据库进行查询设计与实施。
2.3.2.查询的设计与实施
1）通过查询浏览表中的数据
实例：
查询出哪些供应商提供哪些商品，操作过程如图2-17所示。
选择需要的属性（字段），将表中的字段“拖动”到如图2-28所示的2位置
运行效果为“供应商供应的商品”，如图2-18所示
2）建立有查询条件的查询
实例：
查询商品价格小于2.00元的所有商品
在单价下面的条件中输入”>0.5”，保存后运行，如图2-19。
2.4窗体
在Access中，窗体是一种数据对象的格式，是输入和维护表中数据的另一种方式。
窗体起着联系数据库用户与用户的桥梁作用，窗体提供了查阅、新建、编辑和删除数据的最富弹性的方法。
窗体与下一章将要讲到的报表都是用来进行数据库数据维护的，但窗体主要用于数据的输入，报表则用于屏幕或打印输出的窗体中查阅的数据。
2.4.1窗体的基本作用
1）数据的显示和编辑
2）数据输入
3）应用程序流控制
4）信息显示和数据打印
2.4.2窗体的设计与实施
下面根据窗体的四个作用来设计并实施四个窗体。
1）数据的显示和编辑
（1）使用“窗体向导”创建窗体，如图2-20所示，。
2）数据输入
本实例是创建一个带有子窗体的窗体，效果图如图2-21所示。
该窗体中显示的字段分别涉及订单表（订单ID、订单编号、顾客ID），顾客表（顾客名），订单明细表（订单明细ID、商品ID、商品数量）和商品表（商品名称、单价）四个表。
3）应用程序流控制
窗体不只是数据的入口。
例如，切换面板窗体就是一种具有特殊功用的窗体，它包含了一些命令按钮，用于启动其他窗体和报表。
在Access中包含了一个称为切换面板管理器的使用工具，可以用来为数据库创建和编辑切换面板。
使用方法是：
选择“工具”菜单的“数据库实用工具”｜“切换面板管理器”命令。
如图2-22所示。
（1）窗体切换面板
按照图55中操作，打开切换面板管理器。
如图2-23所示，点击“新建”，输入“窗体切换面板”。
然后选中“窗体切换面板”，单击“编辑”出现如图2-24所示界面。
在图2-24中点击新建，出现图2-25，然后在如图2-25所示1、2、3输入“输入|产查看供应商”、选择“在编辑模式下打开窗体”、选择“供应商”。
以同样方式，为顾客、雇员、商品和订单建立切换面板项目。
（2）主切换面板
如图2-23所示，选中“主切换面板”，单击“编辑”。
如图2-24所示，为主切换面板添加按钮，点击“新建”后，按照图2-25所示1、2、3输入“输入｜查看 订单”、“选择“在编辑模式下打开窗体”、选择“订单主”和“输入｜查看其它信息．．．．”、选择“转至切换面板”、选择“窗体切换面板”，和“退出该数据库”、选择“退出应用程序”。
最终主切换面板如图2-26所示
然后分别“关闭”｜“编辑切换面板页”和“切换面板管理器”。
最后设置“主切换面板”自动打开功能，就是在进入数据库时候，主切换面板自动打开。
选择“宏”，单击“新建”，如图2-27所示。
在图2-28所示界面中，在1所指处选择“OpenForm”命令，在2所指处“窗体名称”选择“切换面板”。
最后单击“保存”，将该宏命名为“autoexec”.
图2-28
关闭数据库，在重新打开时数据库将自动打开主切换面板。
如图2-29所示
升级会员 