ACCESS实例教程(查询)

Access实例教程潘明寒赵义霞主编中国水利水电出版社ACCESS实例教程——查询的操作（第3章）ACCESS实例教程1，建立各种类型的查询2，建立计算字段3，使用统计函数4，SQL语句与子查询本章要点：ACCESS实例教程3.1认识查询对象查询是Access数据库的一个重要对象，用来查看、处理和分析数据。查询的数据源可以是一个或多个数据表或已存在的查询。查询产生的操作结果形式上看类似于数据表，实际上是一个动态的数据集合，每次打开查询，都会显示数据源的最新变化情况。查询与数据源表是相通的，在查询中对数据所做的修改可以在数据源表中得到体现。ACCESS实例教程查询对象有5类：选择查询、交叉表查询、操作查询、参数查询、SQL查询。其中操作查询又包括4种：生成表查询、更新查询、追加查询、删除查询。操作查询需要用“运行”命令使查询生效。3.1.1查询对象的基本类型ACCESS实例教程3.1.2查询的设计视图常用的查询视图有3种：设计视图、数据表视图、SQL视图。查询的设计视图窗口分上下两部分，上半部分是“字段列表”区，放置查询的数据源。下半部分是“设计网格”区，放置在查询中显示的字段和在查询中做条件的字段。ACCESS实例教程3.2统配符、运算符与常用函数3.2.1通配符1．*，代表任意多个任何字符。例如：李*，通配所有第一个字符为“李”的字符串。2．?，代表任意一个任何字符。例如：李？，通配所有第一个字符为“李”且只有2个字符的字符串。3．[]，通配方括号内的任何单个字符。例如：淡[红绿黄]色，代表的字符串有：淡红色、淡绿色、淡黄色。ACCESS实例教程4．!，通配不在方括号内的任何单个字符。例如：淡[!红绿黄]色，代表的字符串有：淡蓝色、淡紫色、等，中间的字不能是红、绿、黄3个字中的任何一个。。5．-，通配指定范围内的任何字符，该范围必须是升序，通常针对英文字母。例如：a[e-g]b，代表的字符串有：aeb、afb、agb。6．#，代表任意一个数字字符。例如：2##2，代表的字符串有：2112、2802、等，第一个和最后一个必须是2，中间2个可以是任何数字。ACCESS实例教程3.2.2普通运算符1．算术运算符:+、-、*、/，乘、除同级，加、减同级，同级运算从左到右，乘、除运算优先于加、减运算。2．关系运算符:、=、、=、!=、==，Access系统用true或-1表示“真”，用false或0表示“假”。3．逻辑运算符：not、and、or，运算结果是逻辑值。4．连接运算符：+、&，用于字符连接，+号要求两边必须是字符型，连接后得到新字符串。&号不论两边的操作数是字符串还是数字，都按字符串连起来，得到新字符串。ACCESS实例教程3.2.3特殊运算符1．like，为文本字段设置查询模式，支持通配符。如：like李*，Like*红*“2．in，指定一个值列表作为查询的匹配条件，不支持通配符。如：in(张三,李四,王五)3．between，指定数据范围，用and连接起始数据和终止数据。如：between10and30，相当于：=10and=304．isNull，查找为空的数据。5．isnotNull，查找非空的数据。ACCESS实例教程3.2.4字符函数1．left函数，从字符串左边取n个字符，得到左子串。格式：left(“字符串”,n)或left(string型变量名,n)2．right函数，从字符串右边取n个字符，得到右子串。格式：right(“字符串”,n)或right(string型变量名,n)3．mid函数，从字串第n1个字符开始取n2个字符，得到子字串。格式：mid(“字符串”,n1,n2)或right(string型变量名,n1,n2)说明：如果省略n2，则从字串第n1个字符开始一直取到最后。ACCESS实例教程3.2.5日期函数1．date函数，返回系统当前日期。格式：date()或date2．now函数，返回系统当前日期和时间。格式：now()或now3．year函数，返回日期数据中的年份。格式：year(日期常量)或year(日期/时间型变量名)4．month函数，返回日期数据中的月份。格式：month(日期常量)或month(日期/时间型变量名)5．day函数，返回日期数据中日子的号码。格式：day(日期常量)或day(日期/时间型变量名)ACCESS实例教程3.2.6统计函数1．sum函数，对数字型表达式求和。格式：sum(数字型表达式),如：sum([工资]+[奖金])2．avg函数，对数字型表达式求和。格式：avg(数字型表达式)，如：avg([奖金])3．count函数,对表达式统计个数。格式：count(表达式),如：count([编号])4．max函数，求数字型表达式的最大值。格式：max(数字型表达式)，如：max([奖金])5．min函数，求数字型表达式的最小值。格式：min(数字型表达式)，如：min([年龄])ACCESS实例教程3.3建立简单的选择查询简单的选择查询包括从一个或多个表中提取字段、按一定条件提取记录、对查询结果进行排序等操作。按条件从一个或多个表中提取字段，得到的查询结果就象组建了新数据表。3.3.1从一个或多个表中提取字段ACCESS实例教程3.3.2从一个或多个数据源中提取记录例：以查询为源建立简单查询1，已知数据表：ACCESS实例教程2，建立查询1，显示家长的身份证号与家长的姓名ACCESS实例教程3，建立查询2，显示学生的身份证号、姓名和该学生家长的身份证号。ACCESS实例教程4，建立查询3，显示了家长是本校校友的学生。ACCESS实例教程3.3.3用函数和特殊运算符设置查询条件例：查找姓李的学生和有绘画特长的学生，显示字段为：年级、姓名、爱好。其中年级取学号的前4位。ACCESS实例教程3.4建立带计算的选择查询3.4.1用表达式建立计算字段例：计算教师工资ACCESS实例教程3.4.2用统计函数建立计算字段统计函数功能分组对记录按字段值分组，字段值相同的记录只显示一个。总计对数字型字段的值求和。平均值对数字型字段的值求平均值。最小值求字段的最小值。最大值求字段的最大值。计数求字段值的个数。标准差对数字型字段的值求标准差。方差对数字型字段的值求方差。第一条记录求第一条记录的值。最后一条记录求最后一条记录的值。表达式定义表达式，不分组执行统计函数时用此选项。条件定义条件，执行统计函数时做条件的字段不显示在结果中。ACCESS实例教程例：求最高奖金、最低奖金、奖金最大差额ACCESS实例教程例：统计教师人数与奖金总额ACCESS实例教程例：统计男、女讲师人数ACCESS实例教程例：查询平均分低于班平均分的学生（1）先求班平均分ACCESS实例教程（2）再求人平均分（3）最后求小于班平均的人ACCESS实例教程3.5建立交叉表查询交叉表查询是一种从水平和垂直两个方向对数据表进行分组统计的查询方法，用独特的概括形式返回表的统计数字。建立交叉表查询至少要指定3个字段，一个字段用来分组作为行标题（行标题最多可以有3个），一个字段用来分组作为列标题（列标题只能有1个），一个字段放在行与列交叉位置作为统计项（统计项只能有1个）。ACCESS实例教程例：查询学生各门公共课的成绩ACCESS实例教程显示结果：ACCESS实例教程例：查询各班男女生成绩不及格的人数ACCESS实例教程显示结果：ACCESS实例教程3.6建立操作查询操作查询包括：生成表查询、更新查询、追加查询、删除查询。3.6.1生成表查询生成表查询能将查询结果保存成数据表，使查询结果由动态数据集合转化为静态的数据表。新表不继承数据源表的关键字属性。生成表查询通常用几个表中的数据组合起来生成新表，如果仅用一个表的数据生成新表，可以在数据库窗口用复制、粘贴的方法实现。ACCESS实例教程例：将男学生成绩查询转换成数据表（1）先建查询ACCESS实例教程（2）将查询转换为生成表查询（3）为新表起名ACCESS实例教程（4）单击“运行”按钮ACCESS实例教程3.6.2更新查询更新查询能成批修改记录。用更新查询更改记录的数据项以后，无法用“撤消”命令取消操作。更新查询可以同时更新多个数据源和多个字段的值。在设计网格中，只将要更新的字段和做条件的字段放入即可。例：将职称为讲师的“教师编号”字段前加“讲师”字符串，并将讲师的奖金增加50%。ACCESS实例教程3.6.3追加查询追加查询能将数据源中符合条件的记录追加到另一个表尾部。数据源可以是表或查询，追加的去向是一个表。字段列表区只放提供字段的表或查询，被追加的表不能放入查询的字段列表区。数据源与被追加表对应的字段之间要类型匹配。ACCESS实例教程例：将籍贯为河北的记录追加到空表stud中，追加的字段为：班级、姓、名、性别。（1）选择追加去向ACCESS实例教程（2）确定追加字段和条件ACCESS实例教程（3）运行查询ACCESS实例教程3.6.4删除查询删除查询能将数据表中符合条件的记录成批的删除。删除查询可以给单个表删除记录，也可以给建立了关系的多个表删除记录，多个表之间要建立参照完整性，并选择了“级联删除”选项。在删除查询的设计网格中，只放入做删除条件的字段即可。运行删除查询后，被删除的表中记录不能用“撤消”命令恢复。ACCESS实例教程例：删除1998年参加工作的记录ACCESS实例教程3.7建立参数查询参数查询是按用户给定的字段值来查找记录的查询。参数查询提供一个对话框，提示用户输入一个参数值，然后查找并显示符合参数值的记录。参数查询有单参数查询和多参数查询两种。3.7.1单参数查询单参数查询只需要在字段中指定一个参数，参数通常是完整的字段值，也可以是字段的部分值，还可以是窗体控件中的值。ACCESS实例教程例：以输入的姓名为参数，显示该同学的公共课成绩运行时提示ACCESS实例教程例：用部分字段值做参数ACCESS实例教程运行时提示：显示结果：ACCESS实例教程3.7.2多参数查询多参数查询在多个字段中指定参数，执行时提示对话框从左到右依次显示，用户则依次输入多个参数，最后根据多个参数显示查询结果。ACCESS实例教程3.8建立SQL查询与子查询SQL（StructureQueryLanguage）称为结构化查询语言，被所有关系型数据库支持。它包括了数据定义（DataDefintion）、数据查询（DataQuery）、数据操纵（DataManipulation）、数据控制（DataControl）。SQL语句在Access中的应用分为4个方面：联合查询、子查询、数据定义查询、传递查询。这里我们只介绍前三个查询。ACCESS实例教程3.8.1创建联合查询联合查询将两个select语句的查询结果合成一个查询结果，是集合的并运算。在介绍联合查询之前，先看一下select语句的语法。1．select语句的语法select字段列表from表名[where查询条件][orderby排序项][groupby分组项]例：select*from学生where性别=男“例：select姓名,成绩from学生,公共课成绩where学生.学号=公共课成绩.学号ACCESS实例教程例：select姓名,成绩from学生,公共课成绩where学生.学号=公共课成绩.学号orderby成绩desc例：selectleft([学号],4)as班级,姓名from学生2．联合查询的语法select语句1union[all]select语句2ACCESS实例教程例：教师表法律系的教授与teacher表中文系的副教授显示在一个查询结果中1，“查询”菜单→“SQL特定查询”→“联合”2，写SQL命令ACCESS实例教程3.8.2创建子查询1．在select语句使用统计函数在select语句使用统计函数返一个值，然后用这个值当条件建立选择查询，称为子查询。子查询不能单独作为一个查询，必须与其他查询相结合。做子查询的select语句只能返回一个值，而不是一组值。（1）coun