第四章 数据查询

Access,数据库基础及应用,武汉科技大学计算机科学与技术学院,第4章数据查询,教学目标,掌握查询的概念；掌握各种查询的建立和使用方法；,重点与难点：查询的建立、查询准则、使用查询,相关重点链接,查询的功能查询的类型查询视图数据表视图=设计视图+运行按钮运行或设计查询时查看对应的SQL语句查询设计界面选择查询条件书写出：运算符&函数组合条件查询多表连接查询*参数查询计算查询：预定义计算查询自定义计算查询排序结果查询交叉表查询操作表查询(需使用“查询”菜单)数据表备份删除查询更新查询追加查询生成表查询*SQL查询查询设计SQL命令,相关重点链接,4.1查询的基本概念,查询是Access数据库中的一个主要对象。查询就是按给定的要求（包括条件、范围、方式等）从指定的数据源中查找，将符合条件的数据提取出来，形成类似于表的数据集合，但这个数据集在数据库中实际上并不存在，只是在运行查询时，Access才会从查询源表的数据中抽取出来。,查询的数据源：一个表或多个相关联的表、其他查询。查询的去向(结果)：窗体、报表，另一个查询的基础。,一、查询的功能,1选择字段和记录,2统计和计算,3数据更新,4产生新的表,5作为其他对象的数据源,二、查询的类型,1选择查询,2参数查询,3交叉表查询,4操作表查询,5SQL查询,三、创建查询的方法*,1使用查询向导创建查询,例如，为员工表创建名为“员工工资”的查询，查询结果中包括员工号、姓名、性别、工资4个字段。,2使用设计视图建立查询,例如，在员工表中查询工资在2000元（含2000）以上的记录，查询结果中包括的字段与上例相同，查询名称为“工资高于2000”。,四、创建查询使用的工具,创建查询可以使用的工具有“查询”菜单、不同的视图和工具栏中的按钮。1“查询”菜单,2视图方式创建查询时可以使用的视图方式有3种，分别是设计视图、数据表视图和SQL视图。,3工具栏按钮,五、运行查询,在创建查询时，可以使用以下两种方法运行查询，预览查询结果：单击工具栏中的“运行”按钮,。单击工具栏中的“视图”按钮，将视图方式切换到“数据表视图”。,创建查询后，可以使用以下两种方法显示查询的结果：在“数据库”窗口中，选择“查询”对象，然后双击要运行的查询。在“数据库”窗口中，选择“查询”对象，选择要运行的查询，然后单击“打开”按钮。,4.2查询准则*,一、查询建立的过程,选择数据源；指定查询类型；设置查询准则；为查询命名。,二、查询准则,查询准则是用运算符将常量、字段名（变量）、函数连接起来构成的表达式，即查询表达式，用来指定查询的条件，例如：“基本工资=2000”。,1.常量的书写,如果是数字常量，则直接书写，如2000。如果表示的是文本型常量，要用一对半角的双引号“”或单引号将文本括起来，如武汉科技大学。如果是日期型常量，要用“#”将日期括起来，例如：#2012-10-01#。,2.字段名的书写,在书写字段名时，通常要将字段名放在方括号中，如员工号、姓名等，在输入时，如果不写方括号，系统会在条件中自动加上方括号，如果字段名中含有空格，则方括号是不能省的。如果在一个查询中的数据源不止一个，还应该在字段名前标明字段所在的表或查询，表示格式是：表名!字段名或查询名!字段名例如，员工表中的员工号应该写成员工表!员工号。,三、条件中使用的运算符,1算术运算符,算术运算符包括加（+）、减（-）、乘（*）、除（）、整除（）、乘方（）、求余（mod）7种。,2关系运算符关系运算符用于比较两个表达式之间的大小关系，又称比较运算符，运算结果是逻辑值，即结果为True或False。共有以下6个：等于（=）、不等于（）、小于（）、大于等于（=）。,例如，创建一个“查询”示例，设计包括测试运算符，内置函数等在内的查询准则应用。,as后面注意加中括号,不加出错:,查询语句应首先出现select,后面跟常量,变量(字段名),内置函数等,否则出错:,最简单的查询语句:select,3=5,3逻辑运算符常用的逻辑运算符有3个：与（And）、或（Or）、非（Not），逻辑运算符通常用于设置多重条件。,说明：关系表达式一般用于描述一个简单的条件，例如，工资2000；性别=女。逻辑表达式一般用于描述复合条件，例如，工资2000And性别=女。,4其他特殊运算符,（1）In指定值属于列表中所列出的某个值。如要查找销售一部、销售二部的员工，可在“部门”字段设定查询条件为：In(销售一部,销售二部),建立“查询”,输出逻辑判断,与运算结果,（2）BetweenAandB用于指定A到B之间的范围。A和B可以是数字型、日期型和文本型数据，而且A和B的类型相同。如要查找工资在15002000元的员工，可在“工资”字段设定查询条件为：Between1500and2000。它和表达式=1500and2000,2带特殊运算符的条件查询,SELECT语句可以使用的特殊运算符有：（1）BETWEENAND运算符格式：NOTBETWEENANDBETWEEN运算符用于检测字段的值是否介于指定的范围内。可以是字段名或表达式。BETWEEN表示的取值范围是连续的。,（2）IN运算符格式：NOTIN(,)IN运算符用于检测字段的值是否属于表达式集合或子查询。可以是字段名或表达式。IN表示的取值范围是逗号分隔的若干个值，它表示的取值范围是离散的。,（3）LIKE运算符格式：LIKELIKE运算符用于检测字段的值是否与样式字符串匹配。是字符型字段或表达式。字符表达式中可以使用通配符，其中通配符“*”表示零个或多个字符，通配符“？”表示一个字符。,例4-36，查询员工表中工资在20003000之间的员工号、姓名、工资。SELECT员工号,姓名,工资FROM员工WHERE工资BETWEEN2000AND3000上述语句的功能相当于：SELECT员工号,姓名,工资FROM员工WHERE工资=2000AND工资=30本查询的执行过程是：首先对所有记录按商品号分组统计，然后对分组结果进行筛选，销售数量没有达到30以上的商品被筛选掉。,例4-43，对1980年以后出生的员工分别按部门统计平均工资，并输出平均工资在2000以上的组。SELECT部门,AVG(工资)AS平均工资FROM员工WHERE出生日期=#1980-01-01#GROUPBY部门HAVINGAVG(工资)=2000,本查询的执行过程是：首先根据WHERE子句给出的条件筛选出1980年以后出生的记录，然后按部门分组，最后根据HAVING子句给出的条件筛选出平均工资在2000以上的组。,HAVING与WHERE的区别：WHERE是对表中所有记录进行筛选，HAVING是对分组结果进行筛选。在分组查询中如果既选用了WHERE，又选用了HAVING，执行的顺序是先用WHERE限定记录，然后对筛选后的记录按GROUPBY指定的分组关键字分组，最后用HAVING子句限定分组。,总结：计算查询是对整个表的查询，一次查询只能得出一个计算结果。利用分组计算查询则可以通过一次查询获得多个计算结果。,5排序格式：ORDERBYASC|DESC,ASC|DESCTOPPERCENT说明：ASC表示对查询结果按指定字段升序排序。DESC表示对查询结果按指定字段降序排序，ASC|DESC缺省时默认值是升序。TOP必须与ORDERBY短语同时使用，它的含义是从第一条记录开始，显示满足条件的前N个记录，N为数值表达式的值。选择PERCENT短语时，数值表达式表示百分比。,例4-44，在员工表中查询工资最高在前3名的员工信息。SELECTTOP3*FROM员工ORDERBY工资DESC,例4-45，显示年龄最小的30%的员工的信息。SELECTTOP30PERCENT*FROM员工ORDERBY出生日期DESC,排序关键字可以是字段名，也可以是数字，数字是SELECT指定的输出列的位置序号。在SELECT语句中如果不仅选用了WHERE，还选用了GROUPBY和HAVING，以及ORDERBY子句，执行的顺序是：先用WHERE指定的条件筛选记录，再对筛选后的记录按GROUPBY指定的分组关键字分组，然后用HAVING子句指定的条件筛选分组，最后执行SELECTORDERBY对查询的最终结果进行排序输出。,三、连接查询*,在连接查询的SELECT语句中，通常利用公共字段将若干个表两两相连，使它们像一个表一样以供查询。格式：SELECTFROMINNERJOINONWHERE其中：INNERJOIN用来连接左右两个指定的表，ON用来指定连接条件。,例4-46，在学生成绩管理数据库中查询专业是“工商”或“国贸”的学生的姓名、专业、课程号、平时、期中和期末等字段的信息。SELECT姓名,专业,课程号,平时,期中,期末FROM职工INNERJOIN工资ON学生.学号=成绩.学号WHERE专业IN(“工商”,“国贸”)或者：SELECT姓名,专业,课程号,平时,期中,期末FROM学生,成绩WHERE学生.学号=成绩.学号AND专业IN(工商,国贸),例4-47，以“员工”、“商品”、“销售单”和“销售明细”为数据源，创建一个名为“扩展销售明细”的查询。SELECT销售单.销售号,员工.员工号,姓名,销售日期,销售明细.商品号,商品名,销售明细.价格,销售明细.数量FROM员工INNERJOIN(销售单INNERJOIN(商品INNERJOIN销售明细ON商品.商品号=销售明细.商品号)ON销售单.销售号=销售明细.销售号)ON员工.员工号=销售单.员工号,注意,或者：SELECT销售单.销售号,员工.员工号,姓名,销售日期,销售明细.商品号,商品名,销售明细.价格,销售明细.数量FROM员工,商品,销售单,销售明细WHERE商品.商品号=销售明细.商品号AND销售单.销售号=销售明细.销售号AND员工.员工号=销售单.员工号,注意：如果某字段是两个表的公共字段，为了避免混淆，在语句中字段名的前面加上数据表名，以示区别。如果字段名是唯一的则可以不加表名前缀。,四、嵌套查询*,在SQL中，当一个查询是另一个查询的条件时，即在一个SELECT语句的WHERE子句中出现另一个SELECT语句，这种查询称为嵌套查询。,嵌套查询一般的求解方法是由里向外处理，即每个子查询在上一级查询处理之前求解，这样父查询可以利用子查询的结果。,1带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。,例4-48，查询所有工资高于“王芳”的员工的员工号、姓名、性别和工资。本查询首先要查询王芳的工资，然后再查询工资高于它的信息。确定“王芳”的工资。SELECT工资FROM员工WHERE姓名=王芳结果为：2800,将第1步查询嵌入到第2步查询的条件中，构造嵌套查询，SQL语句如下：SELECT员工号,姓名,性别,工资FROM员工WHERE工资(SELECT工资FROM员工WHERE姓名=王芳),查找工资高于2800的员工号、姓名、性别和工资。SELECT员工号,姓名,性别,工资FROM员工WHERE工资2800,例4-49，显示工资高于女员工平均工资的男员工的员工号、姓名、性别和工资。SQL语句如下：SELECT员工号,姓名,性别,工资FROM员工WHERE性别=男AND工资=(SELECTAVG(工资)FROM员工WHERE性别=女),本查询的执行过程是：首先求出女员工的平均工资，然后找出工资高于女员工的平均工资的男员工信息。,2带有IN谓词的子查询如果子查询返回的值有多个，通常要使用谓词IN。格式：NOTIN(),IN是属于的意思，指定的字段内容属于子查询中任何一个值，运算结果都为真。可以是字段名或表达式。,例4-50，查询2011年3月1日有销售单的员工姓名。,2011年3月1日有销售单的员工可能不止一人，所以子查询的结果是一个集合，因此必须用谓词IN，其SQL语句如下：,SELECT员工号,姓名,性别然后在“员工”表中取出FROM员工员工号、姓名和性别WHERE员工号IN,(SELECT员工号首先在“销售单”表中找出FROM销售单2011年3月1日有销售单的员工号WHERE销售日期=#2011-03-01#),3带有ANY或ALL谓词的子查询,格式：ANY|ALL（）注意：使用ANY或ALL谓词时必须同时使用比较运算符，其语义为：ANY：大于子查询结果中的某个值。ALL：大于子查询结果中的所有值。=ALL：大于等于子查询结果中的所有值。ANY(SELECT工资FROM员工WHERE性别=男),本查询也可以用聚合函数来实现，SQL语句如下：SELECT员工号,姓名,工资FROM员工WHERE性别=女AND工资=(SELECTMIN(工资)FROM员工WHERE性别=男),例4-51-2,查询工资比所有男员工工资都高的女员工的员工号、姓名和工资，则SQL语句如下：SELECT员工号,姓名,工资FROM员工WHERE性别=女AND工资ALL(SELECT工资FROM员工WHERE性别=男）,4.10.5联合查询,联合查询是并操作union，是将多个选择查询合并形成一个新的查询。多个选择语句要求有对应字段(类型相同)。例4-52，合并员工表和销售单表中的员工号select员工号FROM员工表unionselect员工号from销售单表,4.10.6传递查询,传递查询是Access将查询传递给主数据库进行执行，通过创建ODBC连接字符串来实现。使用传递查询，可以直接使用服务器上的表而不要建立连接。例4-52-X，访问Sqlserver/Oracle/DB2/数据表,本章小结,本章介绍了Access数据查询对象，主要内容概括为：1）查询的功能和特点；2）查询准则的构造；3）5种不同类型查询的特点、建立和定制；4）SQL数据定义、操纵功能及其设计。,课堂练习,2013等考真题,课堂练习,课堂练习2,2012等考真题,课堂练习2,2011期末考题（40）假设“公司”表中有编号,名称,法人等字段，查找公司名称中有“网络”二字的公司信息，正确的命令是（