一、 实验目的：　　

1 熟悉access软件操作环境。　　

2 掌握在access中新建数据库、新建表及表结构设计，新建窗体及利用窗体录入数据，新建报表及新建页等基本操作。　　

3 掌握在access中通过sql进行查询、插入、更新和删除。　　

二、实验设备：access2003软件　　

三、实验步骤和结果：　　

首先建立要用到的订单管理系统数据库，其中主要用到的两张主要表有“产品”表和“订单”表，如下图所示：　　

　　

　　

　　

　　

1、利用sql创建产品信息数据表： 　　

（1）打开“订单管理系统”数据库。　　

（2）选择“查询”对象，单击“新建”按钮，在出现的“新建查询”对话框中选择“设计视图”选项，单击“确定”按钮。　　

（3）在出现的“显示表”对话框中直接单击“关闭”按钮；　　

（4）右击，从弹出的菜单中选择“sql特定查询”子菜单中的“数据定义”命令。　　

（5）在sql视图中输入如下图所示的语句：　　

　　

（6）单击工具栏中的“运行”按钮，执行该sql语句；　　

（7）保存该查询或直接退出，打开表结构    　　

2、添加和删除一个“供货商”字段：在出现的“数据定义查询”对话框中直接输入数据定义sql语句，如下图所示，执行该sql语句后，“产品_sql”表就添加上了“供货商”字段，如下图所示。　　

　　

　　

对于“产品_sql”表，删除“供货商”字段，其操作步骤同上，在出现的“数据定义查询”对话框中直接输入数据定义sql语句，如下图所示。执行该sql语句后，“产品_sql”表就删除了“供货商”字段。删除“产品_sql”数据表的操作步骤同上，输入的sql语句如下图所示。运行后，“产品_sql”表不再存在。　　

　　

　　

3、在“订单1”表中将“谢先生”的公司改为“嘉元实业”，对应的sql命令如下：　　

update 订单1 set 公司名称="嘉元实业"  where 姓名="谢先生"　　

“订单1”表如下图所示，该sql语句运行后，“订单1”表中的记录如下所示，从中看到已修改了第一条记录。　　

　　

　　

4、实现向“订单1”中删除姓林的订单记录功能，对应的sql命令如下：　　

delete from 订单1 where 姓名 like"林*"　　

该sql语句运行后，“订单1”表中的记录如下图所示，从中看到已删除了一条记录。　　

　　

　　

　　

　　

5、实现向“订单1”表中插入一条订单记录功能，对应的sql命令如下：　　

insert into student1 values("16","静心轩","林女士","4800","18")　　

该sql语句运行后，“订单1”表中的记录如下图所示，从中看到已添加了一条记录。　　

　　

6、投影查询：实现输出“产品”表中所有记录的“产品名称”、“产品单价”功能，对应的sql命令如下：　　

select 产品名称,产品单价 from 产品　　

查询运行如果如下图所示：　　

　　

如果要去掉重复的显示行，可以在字段名前加上distinct关键字来说明。　　

实现输出“订单1”表所有数量不重复的“数量”列功能，对应的sql命令如下：　　

select distinct 产品单价　　

from 产品　　

查询运行如果如下图所示：　　

　　

7、实现输出“产品”表中单价在10～20之间的所有记录功能，对应的sql命令如下：　　

select * from 产品 where 产品单价 between 10 and 20　　

查询运行如果如下图所示：　　

　　

8、实现输出“产品”表中单价为10、15或20的记录功能，对应的sql命令如下：　　

select * from 产品 where 产品单价 in (10,15,20)　　

查询运行如果如下图所示：　　

　　

9、实现输出“产品”表中单价为10或产品名称为“苹果汁”的记录功能，对应的sql命令如下：　　

select * from 产品 where 产品单价 like"1*"　　

查询运行如果如下图所示：　　

　　

10、排序查询：实现以产品单价降序输出“产品”表的所有记录功能，对应的sql命令如下：　　

select * from 产品 order by 产品单价 desc　　

查询运行如果如下图所示：　　

　　

实现输出产品名称升序、产品单价降序显示所有记录功能，对应的sql命令如下：　　

select * from 产品 order by 产品名称,产品单价 desc　　

查询运行如果如下图所示：　　

　　

11、使用聚合函数实现数据统计：　　

（1）实现输出“产品”表产品单价大于10的产品个数功能，对应的sql命令如下：　　

select count(*)  as 产品单价大于10　　

from 产品　　

where 产品单价>10　　

查询运行如果如下图所示：　　

　　

（2）实现输出“产品”表产品单价最高的功能，对应的sql命令如下：　　

select max(产品单价)  as 最高产品单价　　

from 产品　　

查询运行如果如下图所示：　　

　　

(3)实现输出“产品”表产品单价平均价格记录的功能，对应的sql命令如下：　　

select avg(产品单价)  as 产品平均价格　　

from 产品　　

查询运行如果如下图所示：　　

　　

(4)实现输出“产品”表中产品单价大于10的平均价格记录功能，对应的sql命令如下：　　

select 产品名称,产品单价　　

from 产品　　

where(((产品单价)>( select avg (产品单价) from 产品)))　　

order by 产品单价 desc　　

查询运行如果如下图所示：　　

　　

(5)实现输出“产品”表中产品单价大于等于12且小于等于15的记录功能，对应的sql命令如下：　　

select 产品.产品单价　　

from 产品　　

group by 产品.产品单价　　

having min(产品.产品单价)>=12 and max(产品.产品单价) <=15　　

查询运行如果如下图所示：　　

　　

12、表的连接查询：　　

(1)实现输出所有“订单”表中与“产品id”相对应的“产品名称”记录的功能，对应的sql命令如下：　　

select 订单.[订单 id],产品.产品名称　　

from 产品,订单　　

where 产品.产品id=订单.[产品 id]　　

查询运行如果如下图所示：　　

　　

(2)实现输出所有产品的产品id、数量和产品单价列的功能，对应的sql命令如下：　　

select x.产品id,y.数量, x.产品单价　　

from 产品 x,订单 y　　

where x.产品id=y.[产品 id]　　

查询运行如果如下图所示：　　

　　

　　

(3)实现输出所有产品的产品id、数量和产品单价列的功能，对应的sql命令如下：　　

select y.[客户 id],avg(y.数量) as 平均数量　　

from 产品 x,订单 y　　

where x.产品id=y.[产品 id] and x.产品单价=10    　　

group by y.[客户 id]　　

查询运行如果如下图所示：　　

　　

(4)实现连接“产品”和“订单”表，输出相同产品id记录的功能，对应的sql命令如下：　　

select *　　

from 产品,订单　　

where 产品.[产品id]=订单.[产品 id]　　

order by 产品.产品id　　

查询运行如果如下图所示：　　

　　

(5)例如，实现“产品”表的横向复制，对应的sql命令如下：
select *　　

from 产品 x,产品 y　　

where x.[产品id]=y.[产品id]　　

查询运行如果如下图所示：　　

　　

13、子查询：　　

(1)实现输出订单中数量为2500的产品名称记录的功能，对应的sql命令如下：　　

select  *　　

from 产品   　　

where 产品id=　　

(select[产品 id]  　　

from 订单 　　

where 数量=2500)
查询运行如果如下图所示：　　

　　

(2)实现输出产品单价10以上的产品名称记录的功能，对应的sql命令如下：　　

select distinct 产品.产品名称, 产品.产品单价　　

from 产品　　

where 产品.产品单价 in　　

(select 产品.产品单价　　

    from 产品 　　

where 产品.产品单价>10)　　

查询运行如果如下图所示：　　

　　

(3)实现输出“产品”表和“客户”表的id和名称记录的功能，对应的sql命令如下：　　

select 产品 id as id, 产品名称 as 名称　　

from 产品　　

    union　　

    select客户id as id, 姓名 as 名称　　

    from 客户 　　

    查询运行如果如下图所示：　　

　　

14、相关子查询：要显示单价小于剩余产品单价平均价的产品表，对应的sql命令如下：　　

select *　　

    from 产品 x　　

    where 产品单价<　　

    (select avg(产品单价)　　

    from 产品 y　　

    where [x.产品id]< [y.产品id])　　

    查询运行如果如下图所示：　　

　　

15、带exist测试的子查询：实现输出“订单”表中所有产品功能的sql命令如下：　　

select *　　

    from 产品 x　　

    where exist　　

    (select [y.产品 id]　　

    from 订单 y　　

    where  [x.产品id]= [y.产品 id])　　

    查询运行如果如下图所示：　　

　　

四、实验分析：　　

该实验最基础的是建立订单管理系统数据库，其中包括建立“产品”表和“订单”表。在这个操作的基础上进行一系列操作，如新建窗体及利用窗体录入数据、通过sql进行查询、插入、更新和删除等。这些操作基本上非常简单，难点在于数据库查询语句，要记住这些语句，还要将其区分开来相对而言较难。　　

　　

五、结论：　　

通过这个实验，熟悉了access软件操作环境，了解了如何在access中新建数据库、新建表及表结构设计，新建窗体及利用窗体录入数据，新建报表及新建页等基本操作。基本上掌握了通过sql进行查询、插入、更新和删除等操作。