数据库原理与应用-第6章.ppt

数据库原理与应用,（Access版）,第6章查询的创建与使用,主要内容,6.1选择查询的创建6.2计算查询6.3参数查询6.4交叉表查询6.5操作查询6.6SQL查询,第6章查询的创建与使用,Access数据查询主要有两种作用：（1）通过查询，可以实现对数据库数据的各种操作；（2）可以将设计好的查询命名保存，就得到查询对象。查询对象的外在视图与表类似，由于查询可以对数据库重新组织，这样，查询对象可以将数据库以不同的形式呈现在不同的用户眼中。,第6章查询的创建与使用,在Access中，查询对象主要有五种类型：(1)选择查询可从一个或多个表中检索出数据，并且在可以更新记录（有一些限制条件）的数据表中显示结果；也可以使用选择查询来对记录进行分组，并且对记录作总计、计数、平均值以及其他类型的总和计算。(2)参数查询参数查询可以在运行时提示用户输入参数信息，并根据该信息执行相关的查询工作，如设定条件、将查询控制在一定的范围内等。(3)交叉表查询使用交叉表查询可以计算并重新组织数据的结构，这样可以更加方便地分析数据。,第6章查询的创建与使用,(4)操作查询使用操作查询（也叫动作查询）可以通过查询的运行对数据源中的数据进行改动，通常这样可以大批量地更改和移动数据。操作查询主要有以下四种：删除查询更新查询追加查询生成表查询(5)SQL查询SQL查询是用户使用SQL语句创建的查询。创建查询的方法主要有查询向导和查询设计视图。,6.1选择查询的创建,选择查询是指根据指定的查询准则,从一个或者多个表中获取数据并显示结果的一种最常见的查询方式，并且在可以更新记录的数据表中显示查询结果；也可以使用选择查询来对记录进行分组，并对记录进行总计、计数以及其他类型的累计计算。6.1.1单数据源查询单数据源查询是指从单个数据表或已经创建的查询中找出满足条件的记录，在实际生活中有着广泛的应用。任务实例6.1使用查询向导找出所有图书的“书名”、“作者”、“出版日期”及“ISBN”的记录，查询名为“图书信息查询”。,6.1选择查询的创建,任务分析查询方式：查询向导数据源：图书信息表输出字段：“书名”、“作者”、“出版日期”、“ISBN”查询名：图书信息查询,6.1选择查询的创建,任务实例6.2使用查询设计视图“查询读者信息表中相关信息，显示“读者姓名”、“读者编号”、“联系电话”，并按出生日期的先后顺序将查询结果进行排序，查询名为“读者信息查询”。任务分析查询方式：查询视图数据源：读者信息表输出字段：“读者姓名”、“读者编号”、“联系电话”排序方式：出生日期、升序查询名：读者信息查询,6.1选择查询的创建,任务实例6.3使用查询设计视图查询“索书号”为“A002”图书的“馆藏地”、“架位号”及“流通状态”的记录，查询名为“图书收藏信息查询”。任务分析查询方式：查询视图数据源：图书馆藏表输出字段：“馆藏地”、“架位号”、“流通状态”查询条件：“索书号”为“A002”查询名：图书收藏信息查询,6.1选择查询的创建,6.1.2查询条件查询条件是运算符、常量、字段值、函数以及字段名和属性等任意组合。（1）运算符运算符是构成查询条件的基本元素，表达式中常用的运算符包括算术运算符、关系运算符、连接运算符、逻辑运算符和特殊运算符等,具体见表6.1、表6.2、表6.3、表6.4、表6.5。,6.1选择查询的创建,6.1选择查询的创建,6.1选择查询的创建,(2)函数函数是由Access提供的具有固定意义的式子，只要给定参数，就会返回一个值。,6.1选择查询的创建,6.1选择查询的创建,表6.7字符函数,6.1选择查询的创建,6.1选择查询的创建,6.1选择查询的创建,6.1选择查询的创建,(3)表达式表达式表达式是运算符、常数、函数和字段名称、控件和属性的任意组合，其计算结果为单个值(4)常量数字型常量如123、89.5等，直接键入数字，Access默认其为数字型常量。文本型常量在能接受任意类型的编辑框中，加了文本常量定界符（ANSI-92中的单引号“”）的输入值，如“”计算机”，被视为常量。日期型常量在数字表达式的两边加了日期常量定界符(#)或ANSI-92中的单引号()（如#1970-1-1#）被视为日期型常量。同样，在数据表视图中输入日期型常量时，也不用加定界符。是/否型常量True、False、Yes、No等，当其不被定义为文本型常量时，Access默认其为是/否型常量。,6.1选择查询的创建,(5)通配符常见的通配符见表6.11。,6.2计算查询,在查询中执行计算，可以使用两种方法：（1）使用预定义计算预定义计算是指“总计”计算，用于对查询中的记录进行总和、平均值、最大值、最小值、计数、标准偏差或方差计算。（2）使用自定义计算自定义计算，对一个或多个字段中的记录进行数值、日期和文本计算。6.2.1总计查询总计查询就是在成组的记录中完成一定的计算功能，在查询设计视图中的“总计”行实现。,6.2计算查询,任务实例6.5利用设计视图求类别码为“D”图书的平均价格，显示字段为平均价格，查询名为“D类图书均价查询”。任务分析查询方式：查询视图数据源：图书信息表输出字段：平均价格查询条件：“类别码”为“D”聚合函数：AVG（）查询名：D类图书均价查询,6.2计算查询,6.2.2分组总计查询分组总计就是对一个字段进行分组，然后再总计，分组字段的“总计”行设置成“分组”。任务实例6.6创建一个名为“价格总计”的查询，统计各类别图书价格的总和，查询结果中包括“类别码”和“价格总和”两项信息。任务分析查询方式：查询视图数据源：图书信息表输出字段：“类别码”、“价格总和”聚合函数：合计查询名：价格总计,6.2计算查询,6.2.3添加计算字段查询前面查询的字段都是在数据表中存在的字段,但是有时候我们需要查询某个字段在数据表中不存在,那么就得用添加计算字段查询。任务实例6.7创建一个名为“借书归还查询，查找还书日期在2014年10月1日前借书人的读者编号、借阅编号、图书条码、借阅天数等信息。任务分析查询方式：查询视图数据源：图书借阅表输出字段：读者编号、借阅编号、图书条码、借阅天数新字段：借阅天数=归还时间-借出时间条件：归还时间2014-10-1查询名：借书归还查询,6.3参数查询,参数查询是在查询过程中，条件不唯一的情况使用的查询方式。主要有两种类型：单参数查询和多参数查询。参数查询注意的事项如下：(1)参数查询可以应用于选择查询、生成表查询、追加查询等查询中。(2)使用两个或多个参数：在“条件”单元格中输入一个表达式，并在方括号中输入相应的提示；每次运行该查询时，系统弹出一个输入对话框，以指定输入值的范围。(3)使用带有通配符的参数：若要提示输入一个或多个搜索字符，然后查找以指定的字符开头或包含这些字符的所有记录，可以创建一个使用操作符LIKE和通配符（*）的参数查询。,6.3参数查询,6.3.1单参数查询单参数查询就是在字段中指定一个参数,在执行参数查询的时候,用户再输入一个参数值.为了在运行时让系统出现对话框，务必在条件中使用符号。任务实例6.8建立一个参数查询，按类别码检索图书信息，参数提示“请输入图书类别码”，输出字段为：“书名”、“作者”、“价格”、“出版社编号”及“出版日期”。任务分析(1)查询方式：查询视图(2)数据源：图书信息表(3)输出字段：“书名”、“作者”、“价格”、“出版社编号”、“出版日期”(4)参数字段：“类别码”(5)查询名：按类别码检索图书信息,6.3参数查询,6.3.2多参数查询单参数查询就是在字段中指定一个参数,在执行参数查询的时候,用户再输入一个参数值.为了在运行时让系统出现对话框，务必在条件中使用符号。任务实例6.9建立一个参数查询，按价格及出版时间范围检索图书信息，参数提示“请输入参数范围值”，输出图书信息表的所有字段。任务分析查询方式：查询视图数据源：图书信息表输出字段：所有字段参数字段：“请输入价格范围”及“请输入出版时间范围”查询名：按价格及出版时间范围检索图书信息,6.4交叉表查询,所谓的交叉表查询，就是将来源于某一个表（数据表或者查询表）中的字段进行分组，一组在数据表的左侧，一组在数据表的上部，行与列交叉处显示某个字段的计算值，因此交叉表查询输出至少有三个字段：行标、列表和值。使用交叉表查询可以计算并重新组织数据的结构，以方便分析数据;交叉表类似Excel中的数据透视表，可显示表中某个字段的汇总值，包括总和、计数和平均等。,6.4交叉表查询,任务实例6.10利用“交叉表查询向导”查询每个读者的借书情况和借书次数，行标题为“读者编号”，列标题为“图书条码”，按“借出日期”字段计数。任务分析查询方式：查询向导数据源：图书借阅表使用字段：行标题-读者编号，列表题-图书条码，值-借出时间总计函数：计数（Count）查询名：查询每个读者的借书情况和借书次数,6.4交叉表查询,任务实例6.11利用“设计视图”建立一个交叉表查询，查询每一类别码每年出版书的数目，行标题为“类别码”，列标题为“出版年份”，按“数量”字段求总计。任务分析查询方式：查询视图数据源：图书借阅表使用字段：行标题-类别码，列表题-出版年份，值-数量总计函数：Sum（合计）查询名：查询不同类别码每年出版书的数目,6.5操作查询,操作查询是在一个操作中更改许多记录的查询，主要包括生成表、删除、更新、追加查询。简单查询、交叉表查询、参数查询都是从表中选择需要的数据，并不能对表中数据进行修改。而操作查询除了从表中选择数据外，还能对表中的记录进行修改，由于是对数据的修改，为了避免进行误修改操作，每一个操作查询的图标后面都有感叹号，使用时一定要注意！6.5.1生成表查询生成表查询可以从一个或多个表的数据中产生新的数据表，生成的表可以作为数据备份，也可作为新的数据集，简单地说，生成表查询所产生的结果可以生成一个新的表。,6.5操作查询,任务实例6.12创建一个名为“查询图书类别信息情况”的生成表查询，将“类别码为A”或“类别码为C”两个不同类型码的的图书情况(包括类别码、书名、作者、价格)保存到一个新表中，新表的名称为“类别码A和C图书信息登记表”。任务分析查询方式：查询视图数据源：图书信息表使用字段：类别码、书名、作者、价格查询条件：“类别码”为“A或C”生成表名：类别码A和C图书信息登记表查询名：查询图书类别信息情况,6.5操作查询,6.5.2更新查询更新查询可以对一个或多个表中的一组记录做批量的更改，它比通过键盘逐一修改表记录更加准确、快捷，但需要被修改的数据有规律。任务实例6.13创建一个名为“图书表更新”的查询，将“价格”少于50图书的数量增加5本。任务分析查询方式：查询视图数据源：图书信息表使用字段：价格、数量查询条件：“价格”少于50更新方式：数量+5查询名：图书表更新,6.5操作查询,6.5.3追加查询追加查询可以将一个或多个表中的一组记录添加到另一个或多个表的末尾。任务实例6.14先用生成表查询方法生成一个新的表（数据源为图书信息表所有字段），表名叫“生成查询新表”。创建一个名为“图书追加”的查询，将图书信息表中“类别码”为D的记录追加到“生成查询新表”中。任务分析查询方式：查询视图数据源：图书信息表使用字段：全部查询条件：“类别码”为D追加到的新表：生成查询新表查询名：“类别码”为D的图书信息追加到“生成查询新表”,6.5操作查询,6.5.4删除查询删除查询可以对一个或多个表中的一组记录做批量的删除，如从学生表中删除所有已经毕业的学生。这种删除操作一旦执行，删除掉的数据将无法恢复，所以应慎用。任务实例6.14将图书信息表中“类别码”为C的记录删除，查询名为“删除图书信息表中类别码为C的记录”。任务分析查询方式：查询视图数据源：图书信息表删除条件：类别码为C查询名：“删除图书信息表中类别码为C的记录”,6.6SQL查询,6.6.1SQL概述SQL(StructuredQueryLanguage)意为结构化查询语言，可用于定义、查询、更新、管理关系型数据库系统。SQL是关系数据库的标准语言，是集数据定义、数据操作和数据控制功能于一身的功能完善的数据库语言。目前，SQL仍处于不断发展过程中。SQL提出于1974年。IBM公司的SystemR中率先实现SQL。1986年10月美国国家标准局（ANSI）批准将SQL作为关系数据库语言的美国标准，并发布了ANSISQL标准文本（SQL-86）。1987年，国际标准化组织（ISO）通过了这一标准。1989年发布SQL-89，1992年发布SQL-92（也称SQL2）。1997年发布SQL3。各数据库厂家推出的关系型DBMS都支持SQL，其基本内容、命令和格式是一致的。掌握SQL对使用关系数据库非常重要。,6.6SQL查询,SQL语言，包括定义数据库和表结构、录入数据及建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作的要求。根据功能可分为：（1）数据定义语言DDL（DataDefinitionLanguage）在数据库系统中，每一个数据库、数据库中的表、视图和索引等都是数据库对象。要建立和删除一个数据库对象，都可以通过SQL语言来完成。DDL包括CREATE、ALTER和DROP等。（2）数据操纵语言DML（DataManipulationLanguage）DML是指用来添加、修改和删除数据库中数据的语句，包括INSERT（插入）、DELETE（删除）和UPDATE（更新）等。,6.6SQL查询,(3)数据查询语言DQL(DataQueryLanguage)查询是数据库的基本功能，查询操作通过SQL数据查询语言来实现，例如，用SELECT查询表中的内容。（4）数据控制语言DCL(DataControlLanguage)SQL可以实现对数据库的安全性和完整性控制。一般用来控制用户对数据库的访问权限的。由GRANT（授权），REVOTE（回收）命令组成。,6.6SQL查询,6.6.2数据定义数据定义语言由CREATE,DROP,ALTER等3个命令组成。（1）表的创建定义基本表语句格式为：createtable表名(列名数据类型default缺省值notnull，列名数据类型default缺省值notnull，primarykey（列名，列名），foreignkey（列名，列名）references表名（列名，列名），check（条件）),6.6SQL查询,（2）表的修改修改表的基本语法如下：ALTERTABLE表名ADD列名数据类型PRIMARYKEY|CONSTRAINFOREIGNKEY(列名)REFERENCES关联表名(关联列名)DROPCONSTRAINT约束名称|COLUMN列名说明;ADD子句:增加列或完整性约束条件DROP子句:删除完整性约束条件,6.6SQL查询,(3)表的删除删除基本表语句格式：DROPTABLE表名；注意:删除基本表后，基本表的定义、表中数据、索引都被删除。,6.6SQL查询,（4）索引建立索引语句格式:CREATEUNIQUEINDEX索引名ON表名(列名ASC/DESC,列名ASC/DESC)说明：UNIQUE：惟一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背惟一性的插入、更新。ASC/DESC选项指定索引排序升序或降序，不指定顺序，索引按升序排列。删除索引语句格式：DROPINDEX索引名ON表名说明：删除主索引即删除了主键,6.6SQL查询,任务实例6.16利用SQL语句实现下面数据的数据定义功能（1）创建“books表”，查询名为“创建books表数据定义”，表的结构为：bid,整型，非空，主键；bname，字符型（20）；author,字符型（20）；liberid，字符型（20），非空，唯一;publish:字符型（50）；price,数值型;isfree,字符（1）。（2）在表“books”中添加字段“是否在馆”，类型为字符，查询名为“添加是否在馆字段数据查询”。（3）修改表“books”中的是否在馆字段类型为“是/否”类型,查询名为“修改字段”。（4）删除表“books”中“是否在馆”字段，查询名为“删除是否在馆字段”。,6.6SQL查询,任务分析方法：使用SQL数据库定义语句目的：学会创建表、修改和删除表字段相关SQL语句：Createtable、Altertable查询名：创建books表数据定义、添加是否在馆字段数据查询、修改字段、删除是否在馆字段,6.6SQL查询,任务解决过程（1）创建表使用“在设计视图中创建查询”命令，关闭显示表对话框，右击鼠标，选择“SQL特定查询”下的数据定义命令。输入SQL语句，在查询框中输入相应的SQL语句，如图6.70.保存及运行。在保存对话框中输入名“创建books表数据定义”，单击运行，在数据库窗口中能够看到新建立的表。,6.6SQL查询,（2）添加字段使用“在设计视图中创建查询”命令，关闭显示表对话框，右击鼠标，选择“SQL特定查询”下的“数据定义”命令。输入SQL语句，在查询框中输入相应的SQL语句，如图6.71保存及运行。在保存对话框中输入名“添加是否在馆字段数据查询”，单击运行，books中可以看到表发生的变化。,6.6SQL查询,（3）修改字段使用“在设计视图中创建查询”命令，关闭显示表对话框，右击鼠标，选择“SQL特定查询”下的“数据定义”命令。输入SQL语句，在查询框中输入相应的SQL语句，如图6.72保存及运行。在保存对话框中输入名“修改字段价格类型范围”，单击运行，books中可以看到表发生的变化。,6.6SQL查询,（4）删除字段使用“在设计视图中创建查询”命令，关闭显示表对话框，右击鼠标，选择“SQL特定查询”下的“数据定义”命令。输入SQL语句，在查询框中输入相应的SQL语句，如图6.73保存及运行。在保存对话框中输入名“删除是否在馆字段”，单击运行，books中可以看到表发生的变化。,6.6SQL查询,6.6.3操纵定义数据操作语句主要是实现对表的添加记录、修改记录及删除记录。（1）插入记录INSERT语句用于向数据表或视图中插入一行数据。其基本格式如下：INSERTINTO表或视图名称(列名表)VALUES(数据值)其中，“列名表”是可选项，指定要添加数据的列，当有多列时，列名称之间用逗号分隔；“数据值”指定要添加的数据的具体值。列名的排序次序不一定要和表定义时的次序一致，但当指定列名表时，后面数据值的次序必须和列名表中的列名次序一致，个数相等，数据类型一一对应。,6.6SQL查询,在使用INSERT语句插入数据时应注意以下几点：必须用逗号将各个数据项分隔，字符型和日期型数据要用单引号括起来。若INTO子句中没有指定列名，则新插入的记录必须在每个列上均有值，且VALUES子句中值的顺序次序要和表中各列的排列次序一致。将VALUES子句中的值按照INTO子句中指定列名的次序插入到表中。对于INTO子句中没有出现的列，则新插入的记录在这些列上取空值。,6.6SQL查询,（2）更新记录UPDATE语句用于修改数据表或视图中特定记录或列的数据。其基本格式如下：UPDATE表或视图名称SET列名1=数据值1,nWHERE条件其中，SET子句给出要修改的列及其修改后的数据值；WHERE子句指定要修改的行应当满足的条件，当WHERE子句省略时，则修改表中所有行。,6.6SQL查询,（3）删除记录DELETE语句用于删除表或视图中一行或多行记录。其基本格式如下：DELETE表或视图名称WHERE条件其中，WHERE子句指定要删除的行应当满足的条件，当WHERE子句省略时，则删除表中所有行。,6.6SQL查询,任务实例6.17利用SQL语句实现下面数据查询功能（1）利用SQL语句，向任务实例6.16建立的表中插入一条记录，插入的新纪录见表6.12，查询名为“添加新记录”。,6.6SQL查询,任务解决过程（1）插入一条新记录使用“在设计视图中创建查询”命令，关闭显示表对话框，右击鼠标，选择“SQL特定查询”下的“数据定义”命令。输入SQL语句，在查询框中输入相应的SQL语句，如图6.74，在保存对话框中输入名“添加新记录”。,6.6SQL查询,（2）将图书信息表中书名为哲学的数量改为6，查询名为“修改哲学书本的数量”。任务解决过程使用“在设计视图中创建查询”命令，关闭显示表对话框，右击鼠标，选择“SQL特定查询”下的“数据定义”命令。输入SQL语句，在查询框中输入相应的SQL语句，如图6.75，在保存对话框中输入名“修改哲学书本的数量”。,6.6SQL查询,（3）将出版社信息表中，出版社名称为“中华书局“的记录删除，查询名为“删除中华书局记录”。任务解决过程使用“在设计视图中创建查询”命令，关闭显示表对话框，右击鼠标，选择“SQL特定查询”下的“数据定义”命令。输入SQL语句，在查询框中输入相应的SQL语句，如图6.76，在保存对话框中输入名“删除中华书局记录”。,6.6SQL查询,6.6.4数据查询数据库查询是数据库的核心操作，SQL语言提供了SELECT语句进行数据查询。该语句的功能强，变化形式较多。SELECT查询语句格式如下：SELECTDISTINCT,（查询的结果的目标列名表）FROM,（要操作的关系表或查询名）WHERE（查询结果应满足选择或联接条件）GROUPBY,HA