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

1、第5章 创建交叉表查询与操作查询,教学目标： 通过本章学习，使读者能够使用查询向导和设计视图创建设计交叉表查询和操作查询。主要能力目标： 能够利用Access 2003 的查询功能创建交叉表查询； 能够利用Access 2003 的查询功能为数据库创建操作查询 能够了解Access 2003 的查询对象与 SQL 关系。,工作任务:,本章以“迷你超市业务信息管理系统” 中的表为数据源，使用Access 2003 中的查询方法创建“销售数据记录_交叉表”及一系列操作查询，创建生成表查询、创建更新查询、创建追加查询、创建删除查询。,5.1 创建交叉表查询,使用交叉表查询可以计算并重新组织数据的结构

2、，可以更加方便地分析数据。交叉表查询的数据可分为两组信息：一类在数据表左侧排列，另一类在数据表的顶端排列。,5.1.1使用交叉表查询向导创建查询,下面我们用交叉表查询向导创建一个“销售数据记录_交叉表”。,5.1.2在设计视图修改交叉表查询,上节中，我们用“交叉表查询向导”创建了一个“销售数据记录 _交叉表”，交叉表查询可以获得数据库的汇总信息，下面我们在“设计”视图中创建一个“销售数据记录_ 交叉表 1 ”，以获得各个销售人员的业绩。 操作方法： 在设计视图中打开上面所做的交叉表查询，使用生成器创建“值”和“总计”的表达式：“值:Sum(销售数据记录!销售单价*销售数据记录!销售数量)” 和

3、“总计: Sum(销售数据记录!销售单价*销售数据记录!销售数量) ”,5.2 创建操作查询,操作查询不仅可以搜索、显示数据库还可以对数据库进行动态的修改，根据功能的不同又可以分为创建生成表查询、创建更新查询、创建追加查询、创建删除查询。,5.2.1创建“销售数据记录_生成表”查询,生成表查询可以利用一个或多个表或查询中的全部或部分数据创建一个新表，生成表查询可以根据一定的准则来新建表格，还可以将生成的表导出到其他数据库或者在窗体、报表中加以利用，实际上就是把查询生成的动态集以表的形式保存下来。,5.2.2创建“销售记录_追加查询,上述操作中创建了一个“销售数据记录_生成表查询”，用“销售数据

4、记录”表的数据筛选生成一个“销售数据记录生成表”。随着商品销售业务的不断进行，新的销售数据记录不断产生，希望能够利用新产生的销售数据记录形成新的一段时间内的销售业绩数据，将其追加到“销售数据记录生成表”表中。,5.2.3 创建“销售单价_更新表”更新查询,作为一个超市来说经常会调整销售价格，如果需要对数据表中的某些数据进行有规律的成批更新替换操作，就可以使用更新查询来完成，例如，我们要对所有商品销售单价高于70元的下调10%，采用手工操作比较繁琐，设计一个更新查询就可以很方便地完成这样的操作。,5.2.4 创建删除查询,对于超市营业来说，有些商品由于某种原因不再经营，而且商品也销售一空，也就是

5、在“库存数据记录”表中的“库存数量”为零。这时可以将这些商品的记录从“库存数据记录”表中删除，用删除查询来完成这项工作是轻而易举的事。,5.4 SQL查询,SQL查询是使用SQL语句创建的结构化查询。在Access 2003中每个查询创建后都会在“SQL”视图中自动生成SQL语句，打开“SQL”视图即可查看到已经创建的查询的“SQL”语句。SQL查询语句是数据库领域内通用的关系数据库的数据处理规范，SQL查询包括联合查询、传递查询、数据定义查询和子查询等。,5.4.1 使用SQL语句创建数据定义查询,数据定义查询用于创建或更改数据库对象，Access 2003用SQL数据定义查询可以创建、删除

6、或更改表，或者在当前的数据库中创建索引。 操作一 使用SQL语句，按表5.1的字段属性创建数据表“超市职员基本情况”。,查询1：创建新表“超市职员基本情况” 操作方法： 打开数据库“迷你超市业务管理信息系统”； 选择“查询”为操作对象，单击新建，选择“设计视图”，将显示表“关闭”； 单击“查询”菜单，选择“SQL特定查询”选项，再选择其中的子项“数据定义”； 在“编辑SQL语言”窗口，输入SQL语言，再按“退出”按钮，保存查询，结束SQL查询的创建。,查询1的SQL语句： CREATE TABLE 超市职员基本情况 (员工编号 INTEGER,姓名 TEXT (6),性别 TEXT (2),出

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

8、查询3”,查询4：删除表“超市职员基本情况”表中的“照片”字段。 操作步骤如同查询1。 查询4的SQL语句： ALTER TABLE 超市职员基本情况 DROP 照片 保存此查询为“数据定义查询4”。,操作二 按下表的字段属性，用SQL语句创建 “超市职员职务工资”表。,操作步骤同查询1。 查询的SQL语句： CREATE TABLE 超市职员职务工资 (员工编号 INTEGER,职务 TEXT (6),基本工资 FLOAT) 保存此查询为“数据定义查询5”。 在上述创建的数据表中输入相应的数据，以供后面的查询使用。,5.4.2 使用SQL语句创建选择查询,操作一 使用SQL语句，创建两个选择

9、查询。 操作方法： 打开数据库“迷你超市业务管理信息系统”； 选择“查询”为操作对象，单击新建，选择“设计视图”，将显示表“关闭”； 单击“查询”菜单，选择“SQL特定查询”选项，再选择其中的子项“联合查询”； 在“编辑SQL语言”窗口，输入SQL语言，再按“退出”按钮，保存查询，结束SQL查询的创建。 查询1：要简单查询超市所有职员的情况，SQL语句为： SELECT 员工编号,姓名,性别,出生年月 FROM 超市职员基本情况 保存其查询,查询2：要查询超市男性职员的情况，并以“出生年月”降序排列，SQL语句为： SELECT 员工编号, 姓名, 性别, 出生年月 FROM 超市职员基本情况

10、 WHERE 性别=男 ORDER BY 出生年月,查询3：根据“超市职员基本情况”和“超市职员职务工资”表创建一个多表查询，汇总职员的基本信息，SQL语句： SELECT 超市职员基本情况.员工编号, 超市职员基本情况.姓名, 超市职员基本情况.性别, 超市职员基本情况.出生年月, 超市职员基本情况.民族, 超市职员基本情况.家庭住址, 超市职员基本情况.联系电话, 超市职员职务工资.职务,超市职员职务工资.基本工资 FROM 超市职员基本情况 INNER JOIN 超市职员职务工资 ON 超市职员基本情况.员工编号=超市职员职务工资.员工编号; 保存此查询为“超市员工信息汇总”。,5.4.

11、3 使用SQL语句创建动作查询,利用SQL语句不仅可以创建选择查询，同样可以创建动作查询。创建动作查询的SQL语句有三种：创建插入查询、创建更新查询、创建删除查询。,操作方法： 打开数据库“迷你超市业务信息管理系统”； 选择“查询”为操作对象，单击新建，选择“设计视图”，将显示表“关闭”； 单击“查询”菜单，选择“SQL特定查询”选项，再选择其中的子项“联合查询”； 在“编辑SQL语言”窗口，输入SQL语言，再按“退出”按钮，保存查询，结束SQL查询的创建。 查询1：为“超市职员职务工资”添加一条新记录（”110107”，”职员”，”750”），SQL语句： INSERT INTO 超市职员职务工资 ( 员工编号, 职务, 基本工资 ) VALUES (110107, 职员, 750); 保存查询为“添加职员职务工资查询”。,查询2：将“超市职员职务工资”表的所有员工的工资提高10%，SQL语句：