第4章 Access 2010数据库应用教程 查询

.,Access2010数据库应用教程,第4章查询,.,知识要点,查询的概念和类型查询条件选择查询的创建参数查询的创建交叉表查询的创建操作查询的创建SQL基础知识与SQL语句,.,重点与难点,重点：查询条件选择查询参数查询交叉表查询操作查询SQL查询难点：运算符、函数和表达式选择查询交叉表查询SQL查询,.,4.1查询概述,查询是Access2010数据库的主要对象，是Access2010数据库的核心操作之一。查询主要有以下几个方面的功能：（1）选择字段：选择表中的部分字段生成所需的表或多个数据集。（2）选择记录：根据指定的条件查找所需的记录，并显示查找的记录。（3）编辑记录：添加记录，修改记录和删除记录。（更新查询，删除查询）（4）实现计算：查询满足条件的记录，还可以在建立查询过程中进行各种计算。（计算平均成绩，年龄等）（5）建立新表：操作查询中的生成表查询可以建立新表。（6）为窗体和报表提供数据：可以作为建立报表和查询的数据源。,.,4.1.1查询的类型,根据对数据源的操作方式及查询结果的不同，Access2010提供的查询可以分为5种类型，分别是选择查询、交叉表查询、参数查询、操作查询、SQL查询。,.,4.1.2创建查询的方法,在Access2010中，创建查询的方法主要有以下两种：1.使用查询设计视图创建查询使用查询设计视图创建查询首先要打开查询设计视图窗口，然后根据需要进行查询定义。操作步骤如下：（1）打开数据库；（2）选择“新建”选项卡的“查询”组，单击“查询设计”按钮，打开“查询设计器”窗口，如图所示。,.,查询设计器窗口由两部分组成，上半部分是数据源窗口，用于显示查询所涉及的数据源，可以是数据表或查询，下半部分是查询定义窗口，也称为QBE网格，主要包括以下内容：字段：查询结果中所显示的字段。表：查询的数据源，即查询结果中字段的来源。排序：查询结果中相应字段的排序方式。显示：当相应字段的复选框被选中时，则在结构中显示，否则不显示。条件：即查询条件，同一行中的多个准则之间是逻辑“与”的关系。或：查询条件，表示多个条件之间的“或”的关系。,1.使用查询设计视图创建查询,.,（3）在打开查询设计视图窗口的同时弹出“显示表”对话框，如图所示；,1.使用查询设计视图创建查询,.,（4）在“显示表”对话框中，选择作为数据源的表或查询，将其添加到查询设计器窗口的数据源窗格中。在查询设计器窗口的查询定义窗口中，通过“字段”列表框选择所需字段，选中的字段将显示在查询定义窗口中；（5）在查询设计器窗口的查询定义窗口中，打开“排序”列表框，可以指定查询的排序关键字和排序方式；排序方式分为升序、降序和不排序三种。（6）使用“显示”复选框可以设置某个字段是否在查询结果中显示，若复选框被选中，则显示该字段，否则不显示；（7）在“条件”文本框中输入查询条件，或者利用表达式生成器输入查询条件；（8）保存查询，创建查询完成。,1.使用查询设计视图创建查询,.,4.1.2创建查询的方法,2使用查询向导创建查询操作步骤如下：（1）打开数据库；（2）选择“创建”选项卡的“查询”组，单击“查询向导”按钮，打开“新建查询”对话框；（3）在“新建查询”对话框中，选择需要的查询向导，根据系统引导选择参数或者输入信息；（4）保存查询。,.,4.2查询条件的设置,在实际应用中，经常查询满足某个条件的记录，这需要在查询时进行查询条件的设置。通过在查询设计视图中设置条件可以实现条件查询，而查询条件是通过输入表达式来表示的。表达式是由操作数和运算符构成的可计算的式子。其中操作数可以是常量、变量、函数、甚至可以是另一个表达式（子表达式）；运算符是表示进行某种运算的符号，包括算术运算符、关系运算符、逻辑运算符、连接运算符、特殊运算符和对象运算符等，表达式具有唯一的运算结果。下面对表达式的各个组成部分进行介绍。,.,4.2查询条件的设置,1.常量常量代表不会发生更改的值。按其类型的不同有不同的表示方法，如表所示。,.,4.2查询条件的设置,2.变量变量是指在运算过程中其值允许变化的量。在查询的条件表达式中使用变量就是通过字段名对字段变量进行引用，一般需要使用字段名的格式，如姓名。如果需要指明该字段所属的数据源，则要写成数据表名!字段名的格式。其他类型变量及其引用参见VBA编程部分的内容。,.,4.2查询条件的设置,3.函数函数是用来实现某指定的运算或操作的一个特殊程序。一个函数可以接收输入参数（并不是所有函数都有输入参数），且返回一个特定类型的值。函数一般都用于表达式中，其使用格式为：函数名（实际参数列表）。当函数的参数超过一个时，各参数间用西文半角“,”隔开。函数分为系统内置函数和用户自定义函数。Access2010提供了上千个标准函数，可分为数学函数、字符串处理函数、日期/时间函数、聚合函数等，其中聚合函数可直接用于查询中。具体函数内容详见教材。,.,4.2查询条件的设置,4运算符运算符是表示进行某种运算的符号，包括算术运算符、关系运算符、逻辑运算符、连接运算符和特殊运算符等。（1）算术运算符算术运算符包括加（+）、减（-）、乘（*）、除（/）、乘方（）、整除（）、取余（Mod）等，主要用于数值计算。例如表达式44的运算结果为16；表达式9/2的运算结果为4.5；表达式92的运算结果为4；表达式9Mod2的运算结果为1。,.,4.2查询条件的设置,（2）关系运算符关系运算符由=、=、等符号构成，主要用于数据之间的比较，其运算结果为逻辑值，即“真”和“假”。如表所示。,.,4.2查询条件的设置,（3）逻辑运算符逻辑运算符由And、Or、Not、Xor、Eqv等符号构成，具体含义如表所示。,.,4.2查询条件的设置,（4）连接运算符连接运算符包括“号,2)=02ORDERBY出生日期DESC【例4.18】在成绩表中查找课程编号为CJ005且分数在80分到90分之间的学生的学号。SELECT学号,课程编号,分数FROM成绩WHERE课程编;号=Cj005AND分数=80And分数=85【例4.27】求每门课程的平均分。SELECT课程编号,Avg(分数)AS平均分FROM成绩GROUPBY课程编号【例4.28】查找选修课程超过3门课程的学生学号。SELECT学号FROM成绩GROUPBY学号HAVINGCOUNT(*)3,.,4.7.3单表查询,6.查询排序按指定的某个（或多个）字段对结果进行排序的查询。【例4.29】从学生表中查询学生的信息，并将查询结果按出生日期升序排序。SELECT*FROM学生ORDERBY出生日期【例4.30】从成绩表中查询每个学生的选课信息，并将结果按成绩从高到低排序。SELECT*FROM成绩ORDERBY分数DESC【例4.31】从成绩表中查找课程编号为CJ003的学生学号和分数，并按分数降序排序。SELECT学号,分数FROM成绩WHERE课程编号=CJ003ORDERBY分数DESC,.,4.7.3单表查询,7.包含谓词的查询在查询语句中包含有谓词的查询。【例4.32】从成绩表中查询有选修课程的学生的学号（要求同一个学生只列出一次）。SELECTDISTINCT学号FROM成绩,.,4.7.4多表查询,若查询涉及两个以上的表，即当要查询的数据来自多个表时，必须采用多表查询方法，该类查询方法也称为连接查询。连接查询是关系数据库最主要的查询功能。连接查询可以是两个表的连接，也可以是两个以上的表的连接，也可以是一个表自身的连接。使用多表查询时必须注意：（1）在FROM子句中列出参与查询的表。（2）如果参与查询的表中存在同名的字段，并且这些字段要参与查询，必须在字段名前加表名。（3）必须在FROM子句中用JOIN或WHERE子句将多个表用某些字段或表达式连接起来，否则，将会产生笛卡儿积。,.,4.7.4多表查询,有两种方法可以实现多表的连接查询。1.用WHERE子句写连接条件格式为：SELECTFROMAS,AS,ASWHEREANDAND【例4.33】查找学生信息以及所选修课的课程名称及分数。SELECTA.*,课程名称,分数FROM学生ASA,课程ASB,成绩ASCWHEREB.课程编号=C.课程编号ANDA.学号=C.学号,.,4.7.4多表查询,2.用JOIN子句写连接条件在Access中JOIN连接主要分为INNERJOIN和OUTERJOIN。INNERJOIN是最常用类型的连接。此连接通过匹配表之间共有的字段值来从两个或多个表中检索行。OUTERJOIN用于从多个表中检索记录，同时保留其中一个表中的记录，即使其他表中没有匹配记录。Access数据库引擎支持OUTERJOIN有两种类型：LEFTOUTERJOIN和RIGHTOUTERJOIN。想象两个表彼此挨着：一个表在左边，一个表在右边。LEFTOUTERJOIN选择右表中与关系比较条件匹配的所有行，同时也选择左表中的所有行，即使右表中不存在匹配项。RIGHTOUTERJOIN恰好与LEFTOUTERJOIN相反，右表中的所有行都被保留。格式为：SELECTFROMASINNER|LEFTOUTER|RIGHTJOINOUTER(ASON.=.WHERE,.,4.7.4多表查询,【例4.34】根据教师表和授课表，查询有授课信息的教师的教师编号、姓名、所授课程的课程编号和授课班级的班级编号。SELECT教师.教师编号,姓名,课程编号,班级编号FROM教师INNERJOIN授课ON教师.教师编号=授课.教师编号如果没有授课信息的教师也显示其教师编号和姓名信息，则需用左连接，如下语句所示。SELECT教师.教师编号,姓名,课程编号,班级编号FROM教师LEFTJOIN授课ON教师.教师编号=授课.教师编号,.,4.7.5嵌套查询,在SQL语言中，当一个查询是另一个查询的条件时，即在一个SELECT语句的WHERE子句中出现另一个SELECT语句时，这种查询被称为嵌套查询。通常把内层的查询语句称为子查询，外层查询语句称为父查询。嵌套查询的运行方式是由里向外，也就是说，每个子查询都先于它的父查询执行，而子查询的结果作为其父查询的条件。子查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序。,.,4.7.5嵌套查询,1带关系运算符的嵌套查询父查询与子查询之间用关系运算符（、=、）进行连接。【例4.35】根据学生表，查询年龄大于所有学生平均年龄的学生，并显示其学号、姓名和年龄。SELECT学号,姓名,YEAR(DATE()-YEAR(出生日期)AS年龄FROM学生WHEREYEAR(DATE()-YEAR(出生日期)(SELECTAVG(YEAR(DATE()-YEAR(出生日期)FROM学生),.,4.7.5嵌套查询,2带有IN的嵌套查询【例4.36】根据学生表和成绩表查询没有选修课程编号为“CJ005”的学生的学号和姓名。SELECT学号,姓名FROM学生WHERE学号NOTIN(SELECT学号FROM成绩WHERE课程编号=CJ005),.,4.7.5嵌套查询,3带有ANY或ALL的嵌套查询使用ANY或ALL谓词时必须同时使用比较运算符，即ANY|ALL，ANY代表某一个，ALL代表所有的。【例4.37】根据“学生”表，查询其他班中比“2012020101”班所有学生年龄都小的学生的学号、姓名、出生日期和班级编号。SELECT学号,姓名,出生日期,班级编号FROM学生WHERE出生日期ALL(SELECT出生日期FROM学生WHERE班级编号=2012020101),.,4.7.5嵌套查询,4带有EXISTS的嵌套查询【例4.38】根据“学生”表和“成绩”表，查询所有选修了“CJ005”课程的学生的学号和姓名。SELECT学号,姓名FROM学生WHEREEXISTS(SELECT*FROM成绩WHERE成绩.学号=学生.学号AND课程编号=CJ005),.,4.7.6联合查询,联合查询可以将两个或多个独立查询的结果组合在一起。使用“UNION”连接的两个或多个SQL语句产生的查询结果要有相同的字段数目，但是这些字段的大小或数据类型不必相同。另外，如果需要使用别名，则仅在第一个SELECT语句中使用别名，别名在其他语句中将被忽略。如果在查询中有重复记录即所选字段值完全一样的记录，则联合查询只显示重复记录中的第一条记录；要想显示所有的重复记录，需要在“UNION”后加上关键字“ALL”，即写成“UNIONALL”。【例4.39】查询所有学生的学号和姓名以及所有教师的教师编号和姓名。SELECT学号,姓名FROM学生UNIONSELECT教师编号,姓名FROM教师,.,4.8其他SQL语句,4.8.1数据定义语句数据定义功能是SQL的主要功能之一。利用数据定义功能可以完成建立、修改、删除数据表结构以及建立、删除索引等操作。1创建数据表数据表定义包含定义表名、字段名、字段数据类型、字段的属性、主键、外键与参照表、表约束规则等。在SQL语言中使用CREATETABLE语句来创建数据表，使用CREATETABLE定义数据表的格式为：CREATETABLE（）NOTNULLPRIMARYKEY|UNIQUEREFERENCES（）,主键）,.,4.8.1数据定义语句,说明：（1）PRIMARYKEY将该字段创建为主键，被定义为主键的字段其取值唯一；UNIQUE为该字段定义无重复索引。（2）NOTNULL不允许字段取空值。（3）REFERENCES子句定义外键并指明参照表及其参照字段。（4）当主键由多字段组成时，必须在所有字段都定义完毕后再通过PRIMARYKEY子句定义主键。（5）所有这些定义的字段或项目用逗号隔开，同一个项目内用空格分隔。（6）字段数据类型是用SQL标识符表示的,.,4.8.1数据定义语句,【例4.40】在“学生管理”数据库中，使用SQL语句定义一个名为“Student”的表，结构为：学号（文本，10字符）、姓名（文本，6字符）、性别（文本，2字符）、出生日期（日期/时间）、简历（备注）、照片（OLE），学号为主键，姓名不允许为空值。CREATETABLEStudent(学号TEXT(106)PRIMARYKEYNOTNULL,姓名TEXT(6)NOTNULL,性别TEXT(2),出生日期DATE,简历MEMO,照片OLEOBJECT),.,4.8.1数据定义语句,【例4.41】在“学生管理”数据库中，使用SQL语句定义一个名为“Course”的表，结构为：课程编号（文本型，5字符）、课程名（文本型，15字符）、学分（字节型），课程编号为主键。CREATETABLECourse（课程编号TEXT（5）PRIMARYKEYNOTNULL,课程名TEXT（15）,学分BYTE,.,4.8.1数据定义语句,【例4.42】在“学生管理”数据库中，使用SQL语句定义一个名为“Grade”的表，结构为：学号（文本，10字符）、课程编号（文本型，5字符）、分数（单精度型），主键由学号和课程编号两个字段组成，并通过学号字段与“Student”表建立关系，通过课程编号字段与“Course”表建立关系。CREATETABLEGrade(学号TEXT(6)NOTNULLREFERENCESStudent(学号),课程编号TEXT(3)NOTNULLREFERENCESCourse(课程编号),分数SINGLE,PRIMARYKEY(学号,课程编号),.,4.8.1数据定义语句,2修改表结构ALTERTABLE语句用于修改表的结构，主要包括增加字段、删除字段、修改字段的类型和大小等。（1）修改字段类型及大小，格式为：ALTERTABLEALTER（）（2）添加字段，格式为：ALTERTABLEADD（）（3）删除字段，格式为：ALTERTABLEDROP,.,4.8.1数据定义语句,【例4.43】使用SQL语句修改表，为Student表增加一个“电子邮件”字段（文本型，20字符）。ALTERTABLEStudentADD电子邮件TEXT(20)【例4.44】使用SQL语句修改表，修改Student表的“电子邮件”字段，将该字段长度改为25字符，并将该字段设置成唯一索引。ALTERTABLEStudentALTER电子邮件TEXT(25)UNIQUE【例4.45】使用SQL语句修改表，删除Student表的“简历”字段。ALTERTABLEStudentDROP简历,.,4.8.1数