Access数据库技术与应用课件第5章+创建交叉.ppt

1、第第5 5章章 创建交叉表查询与操作查询创建交叉表查询与操作查询 教学目标：教学目标：通过本章学习，使读者能够使用查询向导通过本章学习，使读者能够使用查询向导和设计视图创建设计交叉表查询和操作查和设计视图创建设计交叉表查询和操作查询。主要能力目标：询。主要能力目标： 能够利用能够利用Access 2003 的查询功能创建交叉的查询功能创建交叉表查询；表查询；能够利用能够利用Access 2003 的查询功能为数据库的查询功能为数据库创建操作查询创建操作查询能够了解能够了解Access 2003 的查询对象与的查询对象与 SQL 关系。关系。工作任务工作任务: :本章以本章以“迷你超市业务信息管

2、理系统迷你超市业务信息管理系统” 中中的表为数据源，使用的表为数据源，使用Access 2003 中的中的查询方法创建查询方法创建“销售数据记录销售数据记录_交叉表交叉表”及一系列操作查询，创建生成表查询、创及一系列操作查询，创建生成表查询、创建更新查询、创建追加查询、创建删除查建更新查询、创建追加查询、创建删除查询。询。5.1 5.1 创建交叉表查询创建交叉表查询 使用交叉表查询可以计算并重新组织数据使用交叉表查询可以计算并重新组织数据的结构，可以更加方便地分析数据。交叉的结构，可以更加方便地分析数据。交叉表查询的数据可分为两组信息：一类在数表查询的数据可分为两组信息：一类在数据表左侧排列，

3、另一类在数据表的顶端排据表左侧排列，另一类在数据表的顶端排列。列。5.1.1使用交叉表查询向导创建查询下面我们用交叉表查询向导创建一个下面我们用交叉表查询向导创建一个“销销售数据记录售数据记录_交叉表交叉表”。5.1.2在设计视图修改交叉表查询上节中，我们用上节中，我们用“交叉表查询向导交叉表查询向导”创建了一个创建了一个“销售数销售数据记录据记录 _交叉表交叉表”，交叉表查询可以获得数据库的汇总，交叉表查询可以获得数据库的汇总信息，下面我们在信息，下面我们在“设计设计”视图中创建一个视图中创建一个“销售数据销售数据记录记录_ 交叉表交叉表 1 ”，以获得各个销售人员的业绩。，以获得各个销售人

4、员的业绩。操作方法：操作方法：在设计视图中打开上面所做的交叉表查询，使用生成器创在设计视图中打开上面所做的交叉表查询，使用生成器创建建“值值”和和“总计总计”的表达式：的表达式：“值值:Sum(销售数据记销售数据记录录!销售单价销售单价*销售数据记录销售数据记录!销售数量销售数量)” 和和“总计总计: Sum(销售数据记录销售数据记录!销售单价销售单价*销售数据记录销售数据记录!销售数销售数量量) ”5.2 创建操作查询操作查询不仅可以搜索、显示数据库还可操作查询不仅可以搜索、显示数据库还可以对数据库进行动态的修改，根据功能的以对数据库进行动态的修改，根据功能的不同又可以分为创建生成表查询、创

5、建更不同又可以分为创建生成表查询、创建更新查询、创建追加查询、创建删除查询。新查询、创建追加查询、创建删除查询。5.2.15.2.1创建创建“销售数据记录销售数据记录_ _生成表生成表”查查询询 生成表查询可以利用一个或多个表或查询中的全部或部分生成表查询可以利用一个或多个表或查询中的全部或部分数据创建一个新表，生成表查询可以根据一定的准则来数据创建一个新表，生成表查询可以根据一定的准则来新建表格，还可以将生成的表导出到其他数据库或者在新建表格，还可以将生成的表导出到其他数据库或者在窗体、报表中加以利用，实际上就是把查询生成的动态窗体、报表中加以利用，实际上就是把查询生成的动态集以表的形式保存

6、下来。集以表的形式保存下来。5.2.2创建“销售记录_追加查询上述操作中创建了一个上述操作中创建了一个“销售数据记录销售数据记录_生生成表查询成表查询”，用，用“销售数据记录销售数据记录”表的数表的数据筛选生成一个据筛选生成一个“销售数据记录生成表销售数据记录生成表”。随着商品销售业务的不断进行，新的销售随着商品销售业务的不断进行，新的销售数据记录不断产生，希望能够利用新产生数据记录不断产生，希望能够利用新产生的销售数据记录形成新的一段时间内的销的销售数据记录形成新的一段时间内的销售业绩数据，将其追加到售业绩数据，将其追加到“销售数据记录销售数据记录生成表生成表”表中。表中。 5.2.3 创建

7、“销售单价_更新表”更新查询作为一个超市来说经常会调整销售价格，作为一个超市来说经常会调整销售价格，如果需要对数据表中的某些数据进行有规如果需要对数据表中的某些数据进行有规律的成批更新替换操作，就可以使用更新律的成批更新替换操作，就可以使用更新查询来完成，例如，我们要对所有商品销查询来完成，例如，我们要对所有商品销售单价高于售单价高于70元的下调元的下调10%，采用手工操，采用手工操作比较繁琐，设计一个更新查询就可以很作比较繁琐，设计一个更新查询就可以很方便地完成这样的操作。方便地完成这样的操作。5.2.4 创建删除查询对于超市营业来说，有些商品由于某种原对于超市营业来说，有些商品由于某种原因

8、不再经营，而且商品也销售一空，也就因不再经营，而且商品也销售一空，也就是在是在“库存数据记录库存数据记录”表中的表中的“库存数量库存数量”为零。这时可以将这些商品的记录从为零。这时可以将这些商品的记录从“库库存数据记录存数据记录”表中删除，用删除查询来完表中删除，用删除查询来完成这项工作是轻而易举的事。成这项工作是轻而易举的事。 5.4 SQL查询SQL查询是使用查询是使用SQL语句创建的结构化查语句创建的结构化查询。在询。在Access 2003中每个查询创建后都中每个查询创建后都会在会在“SQL”视图中自动生成视图中自动生成SQL语句，语句，打开打开“SQL”视图即可查看到已经创建的视图即

9、可查看到已经创建的查询的查询的“SQL”语句。语句。SQL查询语句是数查询语句是数据库领域内通用的关系数据库的数据处理据库领域内通用的关系数据库的数据处理规范，规范，SQL查询包括联合查询、传递查询、查询包括联合查询、传递查询、数据定义查询和子查询等。数据定义查询和子查询等。5.4.1 使用SQL语句创建数据定义查询数据定义查询用于创建或更改数据库对象，数据定义查询用于创建或更改数据库对象，Access 2003用用SQL数据定义查询可以创数据定义查询可以创建、删除或更改表，或者在当前的数据库建、删除或更改表，或者在当前的数据库中创建索引。中创建索引。操作一操作一 使用使用SQL语句，按表语句

10、，按表5.1的字段属的字段属性创建数据表性创建数据表“超市职员基本情况超市职员基本情况”。 字段名字段名员工编号员工编号姓名姓名性别性别出生年月出生年月民族民族联系电话联系电话家庭住址家庭住址照片照片字段类型字段类型INTEGERTEXTTEXTDATETEXTINTEGERMEMOGENERAL字段长度字段长度62长日期长日期1030小数位小数位查询查询1：创建新表：创建新表“超市职员基本情况超市职员基本情况”操作方法：操作方法：1.打开数据库打开数据库“迷你超市业务管理信息系统迷你超市业务管理信息系统”；2.选择选择“查询查询”为操作对象，单击新建，选择为操作对象，单击新建，选择“设计视图

11、设计视图”，将显示表，将显示表“关闭关闭”；3.单击单击“查询查询”菜单，选择菜单，选择“SQL特定查询特定查询”选选项，再选择其中的子项项，再选择其中的子项“数据定义数据定义”；4.在在“编辑编辑SQL语言语言”窗口，输入窗口，输入SQL语言，再语言，再按按“退出退出”按钮，保存查询，结束按钮，保存查询，结束SQL查询的查询的创建。创建。查询查询1的的SQL语句：语句：CREATE TABLE 超市职员基本情况超市职员基本情况 (员工员工编号编号 INTEGER,姓名姓名 TEXT (6),性别性别 TEXT (2),出生年月出生年月 DATE,民族民族 TEXT (10),家庭住址家庭住址

12、 MEMO) 保存为保存为“数据定义查询数据定义查询1”。 运行查询得到表结构。运行查询得到表结构。查询查询2：给：给“超市职员基本情况表增加两超市职员基本情况表增加两个新字段个新字段“联系电话联系电话”和和“照片照片”操作方法同上。操作方法同上。查询查询2的的SQL语句：语句：ALTER TABLE 超市职员基本情况超市职员基本情况 ADD 联系电话联系电话 INTEGER ,照片照片 GENERAL保存此查询为保存此查询为“数据定义查询数据定义查询2”。 查询查询3：给表：给表“超市职员基本情况超市职员基本情况”表表中中“联系电话联系电话”字段的类型，由字段的类型，由INTEGER类型改为

13、类型改为TEXT。操作步骤同查询操作步骤同查询1。查询查询3的的SQL语句：语句：ALTER TABLE 超市职员基本情况超市职员基本情况 ALTER 联系电话联系电话 TEXT (13)保存此查询为保存此查询为“数据定义查询数据定义查询3” 查询查询4：删除表：删除表“超市职员基本情况超市职员基本情况”表表中的中的“照片照片”字段。字段。操作步骤如同查询操作步骤如同查询1。查询查询4的的SQL语句：语句：ALTER TABLE 超市职员基本情况超市职员基本情况 DROP 照片照片保存此查询为保存此查询为“数据定义查询数据定义查询4”。 操作二操作二 按下表的字段属性，用按下表的字段属性，用S

14、QL语句创语句创建建 “超市职员职务工资超市职员职务工资”表。表。字段名字段名员工编号员工编号职务职务基本工资基本工资字段类型字段类型INTEGERTEXTFLOAT字段长度字段长度6单精度单精度小数位小数位2操作步骤同查询操作步骤同查询1。查询的查询的SQL语句：语句：CREATE TABLE 超市职员职务工资超市职员职务工资 (员员工编号工编号 INTEGER,职务职务 TEXT (6),基本基本工资工资 FLOAT)保存此查询为保存此查询为“数据定义查询数据定义查询5”。 在上述创建的数据表中输入相应的数据，在上述创建的数据表中输入相应的数据，以供后面的查询使用。以供后面的查询使用。5.

15、4.2 使用SQL语句创建选择查询操作一操作一 使用使用SQL语句，创建两个选择查询。语句，创建两个选择查询。操作方法：操作方法：1.打开数据库打开数据库“迷你超市业务管理信息系统迷你超市业务管理信息系统”；2.选择选择“查询查询”为操作对象，单击新建，选择为操作对象，单击新建，选择“设计视设计视图图”，将显示表，将显示表“关闭关闭”；3.单击单击“查询查询”菜单，选择菜单，选择“SQL特定查询特定查询”选项，再选选项，再选择其中的子项择其中的子项“联合查询联合查询”；4.在在“编辑编辑SQL语言语言”窗口，输入窗口，输入SQL语言，再按语言，再按“退退出出”按钮，保存查询，结束按钮，保存查询

16、，结束SQL查询的创建。查询的创建。5.查询查询1：要简单查询超市所有职员的情况，：要简单查询超市所有职员的情况，SQL语句为：语句为：6.SELECT 员工编号员工编号,姓名姓名,性别性别,出生年月出生年月 7.FROM 超市职员基本情况超市职员基本情况8.保存其查询保存其查询 查询查询2：要查询超市男性职员的情况，并：要查询超市男性职员的情况，并以以“出生年月出生年月”降序排列，降序排列，SQL语句为：语句为：SELECT 员工编号员工编号, 姓名姓名, 性别性别, 出出生年月生年月FROM 超市职员基本情况超市职员基本情况WHERE 性别性别=男男ORDER BY 出生年月出生年月 查询

17、查询3：根据：根据“超市职员基本情况超市职员基本情况”和和“超市职员职务工超市职员职务工资资”表创建一个多表查询，汇总职员的基本信息，表创建一个多表查询，汇总职员的基本信息，SQL语句：语句：SELECT 超市职员基本情况超市职员基本情况.员工编号员工编号, 超市职员基本情超市职员基本情况况.姓名姓名, 超市职员基本情况超市职员基本情况.性别性别, 超市职员基本情超市职员基本情况况.出生年月出生年月, 超市职员基本情况超市职员基本情况.民族民族, 超市职员基本超市职员基本情况情况.家庭住址家庭住址, 超市职员基本情况超市职员基本情况.联系电话联系电话,超市职员职务工资超市职员职务工资.职务职务

18、,超市职员职务工资超市职员职务工资.基本工资基本工资FROM 超市职员基本情况超市职员基本情况 INNER JOIN 超市职员职务工超市职员职务工资资ON 超市职员基本情况超市职员基本情况.员工编号员工编号=超市职员职务工资超市职员职务工资.员员工编号工编号;保存此查询为保存此查询为“超市员工信息汇总超市员工信息汇总”。 5.4.3 使用SQL语句创建动作查询利用利用SQL语句不仅可以创建选择查询，同语句不仅可以创建选择查询，同样可以创建动作查询。创建动作查询的样可以创建动作查询。创建动作查询的SQL语句有三种：创建插入查询、创建更语句有三种：创建插入查询、创建更新查询、创建删除查询。新查询、

19、创建删除查询。操作方法：操作方法：1.打开数据库打开数据库“迷你超市业务信息管理系统迷你超市业务信息管理系统”；2.选择选择“查询查询”为操作对象，单击新建，选择为操作对象，单击新建，选择“设计视图设计视图”，将显，将显示表示表“关闭关闭”；3.单击单击“查询查询”菜单，选择菜单，选择“SQL特定查询特定查询”选项，再选择其中的选项，再选择其中的子项子项“联合查询联合查询”；4.在在“编辑编辑SQL语言语言”窗口，输入窗口，输入SQL语言，再按语言，再按“退出退出”按钮，按钮，保存查询，结束保存查询，结束SQL查询的创建。查询的创建。5.查询查询1：为：为“超市职员职务工资超市职员职务工资”添

20、加一条新记录（添加一条新记录（”110107”，”职员职员”，”750”），），SQL语句：语句：6.INSERT INTO 超市职员职务工资超市职员职务工资 ( 员工编号员工编号, 职务职务, 基本工资基本工资 )7.VALUES (110107, 职员职员, 750);8.保存查询为保存查询为“添加职员职务工资查询添加职员职务工资查询”。查询查询2：将：将“超市职员职务工资超市职员职务工资”表的所表的所有员工的工资提高有员工的工资提高10%，SQL语句：语句：UPDATE 超市职员职务工资超市职员职务工资 SET 超市职超市职员职务工资员职务工资.基本工资基本工资 = 超市职员职务工超市职员职务工资资!基本工资基本工资*1.1;保存其查询为保存其查询为“超市职员工资更新查询超市职员工资更新查询”。 查询查询3：若要将查询：若要将查询1添加的记录删除，添加的记录删除，SQL语句：语句：DELETE 员工编号员工编号, 职务职务, 基本工资基本工资FROM 超市职员职务工资超市职员职务工资WHERE 员工编号员工编号 Like 110107;保存此查询为保存此查询为“删除员工记录查询删除员工记录查询”。