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．求至少选修了两门课程的学生学号；5．求某门课程不及格学生姓名和考试成绩select姓名,成绩from学生,学习,课程where学生.学号=学习.学号and学习.课程号=课程.课程号and课程名='数据库'and成绩<'60';6．求至少选修了与某个同学选修的课程相同的学生学号selectdistinct学号from学习asawherenotexists(select*from学习asbwhere学号='08101'andnotexists(select*from学习ascwherea.学号=b.学号andb.课程号=c.课程号));7．求至少选修了某两门课程的学生姓名和学院名称。select姓名,学院名称from学生,学习,课程,学院where学生.学号=学习.学号and学习.课程号=课程.课程号and学生.学院代码=学院.学院代码and课程名='数据库'and学生.学号in(select学生.学号from学生,学习,课程,学院where学生.学号=学习.学号and学习.课程号=课程.课程号and学生.学院代码=学院.学院代码and课程名='数据结构');8．查询某个同学没有选修的课程号和课程名9．查询没有被任何学生选修的课程的课程号select课程号from课程where课程.课程号notin(select课程.课程号from课程,学习where课程.课程号=学习.课程号);10．求选修了全部课程的学生姓名11．查询各学院某门课程的平均分，并按照成绩从高到低的顺序排列。12．查询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列。13．求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师。14．检索选修数据库原理课程的最高分学生的姓名15．查询选课人数超过50（根据情况自己定人数）人的课程的课程号及课程名。16．自行提出查询要求，内容包括并运算、差运算、交运算、笛卡儿积运算、连接运算、自然连接运算以及复杂统计表。二、实验目的使用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图图1-1供应商图1-1供应商供应商品包括顾客销售我公司配送雇员属于填写订单订单明细图1供应商图1供应商供应商品包括顾客销售我公司配送雇员属于填写订单订单明细1.3数据库逻辑结构设计（数据库的表结构设计）根据ER图可以抽象出七个实体和六个关系，经分析得出数据库逻辑结构，也就是数据库中的表：供应商表、商品表、顾客表、雇员表、订单表、订单明细表。各个表的结构如下:（红色字体为表名、括号中为标的属性、中括号内为属性的类型、有下划线的属性为表的主键）供应商（供应商ID[自动编号]、供应商名称[文本]、供应商地址[文本]、供应商电话[文本]、供应商传真[文本]）商品（商品ID[自动编号]、商品名称[文本]、单价[货币]、供应商ID[数字]）顾客（顾客ID[自动编号]、顾客名[文本]、顾客地址[文本]、顾客电话[文本]）雇员（雇员ID[自动编号]、雇员姓名[文本]、雇员电话[文本]、雇员职位[文本]）订单（订单ID[自动编号]、订单编号[文本]、顾客ID[数字]、雇员ID[数字]、订单日期[时间日期]、送货日期[时间日期]）订单明细（订单明细ID[自动编号]、订单ID[数字]、商品ID[数字]、商品数量[数字]）2数据库实现2.1新建数据库新建一个数据库将其命名为自己公司的名称，如图2-1所示。图图2-1122.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-9图图2-9图2-8为各个表添加记录。（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-图2-162.3查询在设计一个数据库时，为了节省存储空间，常常把数据分类，并分别存放在多个表里。尽管可以在基本表中可以进行许多操作，如浏览、筛选、更新等，但很多时候还是需要检索一个或多个表（或查询）中符合条件的数据，将这些数据集合在一起，执行浏览、计算等各种操作。查询实际上就是将这些分散的数据再集合起来。使用查询可以执行一组选定的数据记录集合，虽然这个记录在数据库中实际上并不存在，只是在运行查询时，ACCESS才从查询源数据表中创建它，但正是这个特性，使查询具有了灵活方便的数据操纵能力。2.3（1）通过查询浏览表中的数据，分析数据和修改数据（2）利用查询可以使用户的注意力集中在自己感兴趣的数据上，而将当前不需要的数据排除在查询之外。（3）将经常处理的原始数据或统计计算定义为查询，可大大简化数据的处理工作。（4）查询的结果集可以用于生成新的基本表，可以进行新的查询，还可以为窗体、报表提供数据。下面就根据查询的四个主要作用，针对我们的公司数据库进行查询设计与实施。2.31）通过查询浏览表中的数据实例：查询出哪些供应商提供哪些商品，操作过程如图2-17所示。图2-17２选择需要的属性（字段），将表中的字段“拖动”到如图图2-17２运行效果为“供应商供应的商品”，如图2-18所示图图2-182）建立有查询条件的查询实例：查询商品价格小于2.00元的所有商品在单价下面的条件中输入”>0.5”，保存后运行，如图2-图图2-192.4窗体在Access中，窗体是一种数据对象的格式，是输入和维护表中数据的另一种方式。窗体起着联系数据库用户与用户的桥梁作用，窗体提供了查阅、新建、编辑和删除数据的最富弹性的方法。窗体与下一章将要讲到的报表都是用来进行数据库数据维护的，但窗体主要用于数据的输入，报表则用于屏幕或打印输出的窗体中查阅的数据。2.4数据的显示和编辑数据输入应用程序流控制信息显示和数据打印2.4下面根据窗体的四个作用来设计并实施四个窗体。数据的显示和编辑（1）使用“窗体向导”创建窗体，如图2-20所示，。图图2-202）数据输入本实例是创建一个带有子窗体的窗体，效果图如图2-21所示。左侧红框内的内容是订单相关信息，即哪位顾客的哪个订单左侧红框内的内容是订单相关信息，即哪位顾客的哪个订单为子窗体，其中显示主窗体的订单中订购的所有商品的名称、数量和单价图2-21该窗体中显示的字段分别涉及订单表（订单ID、订单编号、顾客ID），顾客表（顾客名），订单明细表（订单明细ID、商品ID、商品数量）和商品表（商品名称、单价）四个表。3）应用程序流控制窗体不只是数据的入口。例如，切换面板窗体就是一种具有特殊功用的窗体，它包含了一些命令按钮，用于启动其他窗体和报表。在Access中包含了一个称为切换面板管