ACCESS常用函数

1ACCESS查询教程（包含常用函数）2查询是关系数据库中的一个重要概念，利用查询可以让用户根据选择条件对数据库进行检索，筛选出一组满足指定条件的记录，从而构成一个新的数据集合，以方便用户对数据库进行查看和分析。3本章主要内容：•查询的功能与类型•查询对象的创建与设计方法•操作查询及SQL查询的方法•使用查询操作表或表数据4查询对象以一个或多个表及其它查询对象为基础，重组并加工这些表及查询对象中的数据，提供一个新的数据集合。53.1.1查询对象的功能1．选择字段2．选择记录3.编辑记录4.实现计算5.利用查询的结果生成窗体或报表6．建立新表63.1.2查询对象的类型1.选择查询2.参数查询3.交叉表查询4.操作查询5.SQL查询71.选择查询选择查询是最常见的查询类型，它是按照规则从一个或多个表，或其它查询中检索数据，并按照所需的排列顺序显示出来。如下表的数据来源于“学生信息”、“选课信息”及“课程信息”3个表82.参数查询参数查询可以在执行时显示自己的对话框，提示用户输入信息。它不是一种独立的查询，只是在其它查询中设置了可变化的参数。如下图查询对象的数据源是“学生信息”表，运行查询时需输入查询参数。93.交叉表查询使用交叉表查询可以计算并重新组织数据的结构，这样可以更加方便地分析数据，如下表所示。数据源表交叉表查询对象104.操作查询使用操作查询只需进行一次操作，就可以对许多记录进行更改和移动。操作查询有以下4种：删除查询：可以从一个或多个表中删除一组记录。更新查询：可以对一个或多个表中的一组记录做全局的更改。追加查询：可以将一个或多个表中的一组记录添加到一个或多个表的末尾。生成表查询：可以根据一个或多个表中的的全部或部分数据新建表。例如，可以使用删除查询来删除所有毕业学生的记录。例如，可以将某一工作类别的人员的工资提高5个百分点。例如，假设用户获得了一些新的客户以及包含这些客户信息的数据表。若要避免在自己的数据库中键入所有这些信息的麻烦，可以利用“追加查询”将其追加到“客户”表中。115.SQL查询•SQL(StructureQueryLanguage)是一种结构化查询语言，是数据库操作的工业化标准语言。•可使用SQL查询、更新和管理任何数据库系统。•用户在设计视图中创建查询时，Access将在后台构造等效的SQL语句。•只能在SQL视图中创建的查询，称为“特定查询”，包括：传递查询：传递查询可以直接向ODBC数据库服务器发送命令联合查询：联合查询可使用Union运算符来合并两个或更多选择查询结果数据定义查询：利用数据定义语言(DDL)语句创建或更改数据库中的对象。123.1.3查询视图◆设计视图：设计视图就是查询设计器，通过该视图可以设计除SQL查询之外的任何类型的查询。◆数据表视图：数据表视图是查询的数据浏览器，是一个动态记录集。◆SQL视图：SQL视图是按照SQL语法规范显示查询，即显示查询的SQL语句，此视图主要用于SQL查询。◆数据透视表视图和数据透视图视图：在这两种视图中，可以更改查询的版面，从而以不同方式观察和分析数据。。注意：通过选择“视图”菜单中的相应选项（或点击工具栏中的“视图”图标的下拉按钮，在下拉表中选择相应选项），可实现在5种视图间的转换。133.1.4查询准则用户的查询经常需要指定一定的条件，这就需要设置准则来实现。查询准则也称为查询条件，是运算符、常量、字段值、函数以及字段名和属性等的任意组合，能够计算出一个结果。1．准则中的运算符及表达式◆算术运算符运算符功能表达式举例含义+加法运算[小计]+[销售税]求两个字段值的和-减法运算，或表示负值[价格]-[折扣]求两个字段值的差*乘法运算[数量]*[价格]求两个字段值的乘积/除法运算[总计]/[数据项计数]求平均值\整除法121\6结果为20Mod整除取余数121Mod6结果为1^指数运算5^3计算5的立方14◆关系运算符用关系运算符连接的两个表达式构成关系表达式，结果为一个逻辑值True、False或者Null运算符功能表达式举例含义小于#2010-3-10#2010-3-10之前的日期=小于等于=100小于等于100的数大于234大于234的数=大于等于=#2010-3-10#2010-3-10当天或之后日期=等于=HALL值为HALL不等于100不等于10015◆连接运算符可以使用连接运算符将两个文本值合并成一个值。运算符功能表达式举例结果&强制两个表达式作字符串连接abc&123abc123+将两个字符串合并为一个字符串abc＋123abc123◆逻辑运算符逻辑运算符用于连接两个关系表达式，对表达式进行真、假判断。运算符功能表达式举例含义Not逻辑非[性别]=Not男查找女性的记录And逻辑与[性别]=女And[职称]=教授查找女教授Or逻辑或[职称]=讲师or[职称]=实验师查找职称为讲师或实验师的记录16◆特殊运算符与比较运算有关，这些运算符根据字段中的值是否符合这个运算符的限定条件返回True或False。Like：查找匹配的文字可与以下4个通配符配合使用：“*”匹配0或多个字符，“?”匹配一个字符，“#”匹配一个数字，“[]”匹配一个字符范围。如：like“李*”，指姓李的名字。In：指定一系列值的列表如：In（”山东”，”浙江”，”安徽”）。Betweena1anda2：指位于a1和a2之间的值（包括端值）如：Between75and90。IsNull、IsNotNull：判断某一值是否为Null值字段，即不包括任何数据，为空值。17◆？：代表任意一个字符◆*：代表任意字符串（0或多个字符）◆＃：代表单一数字◆[字符表]：字符表中的单一字符◆[！字符表]：不在字符表中的单一字符【例】Like“p[b-g]###”通配以字母p开头，后根b~g之间的1个字母和3个数字的字符串。【例】Like“f?[a-f][!6-10]*”通配第一个字符为f，第2个为任意字符，第3个为非6-10的任意字符，其后为任意字符串的字符串。补充--通配符181．准则中的标准函数常用的统计函数求和函数：Sum(字符串表达式)求平均函数：Avg(字符串表达式)统计记录个数函数：Count(字符串表达式)最大、最小值函数：Max(字符串表达式)Min(字符串表达式)例如：显示“运费”字段中各值的总和，即为Sum(运费)；Avg(运费)为平均运费；显示“单价”字段和“数量”字段乘积的总和，即为Sum(单价*数量)；按姓名统计人数，即为Count（姓名）；统计包括空值Null在内的所有记录数为Count(*)；设有一个“成绩”字段，可以用Max(成绩)求该字段中的最大值，用Min(成绩)求该字段中的最小值。191．准则中的标准函数日期函数在包含日期的表达式中，须将日期型数据的两端加上“＃”号（此处#不是通配符），以区别于其它数字。例如：1990年1月2日以后出生的学生，表达式可为：#1990/1/2#或#/1/2/1990#。另外，系统还提供了以下时间函数：Date()：返回系统当前日期Year()：返回日期中的年份Month()：返回日期中的月份Day()：返回日期中的日数Weekday()：返回日期中的星期几Hour()：返回时间中的小时数Now()：返回系统当前的日期和时间20【例】时间函数的应用1.构造查询条件表达式，把7月份出生的学生查找出来。Month（[csrq]）=72.构造计算表达式，根据出生日期计算学生年龄。Year（Date（））-Year（[出生日期]）3.构造表达式，表示出系统日期10天前的日期。Date（）-10说明：在任何计算表达式中的符号都必须在英文半角输入状态下输入，否则无效。[出生日期]和[csrq]为字段标识符，方括号内是字段名。如果要明确表示“学生信息”表中的“出生日期”字段，表达式为：[学生信息]![出生日期]213.2创建选择查询选择查询是最常用的查询类型，它从一个或多个的表中检索数据，并以表格的形式显示这些数据。3.2.1使用“简单查询向导”创建查询【例3-1】以“教学管理”数据库中的“学生信息”表、“课程信息”表和“选课信息”表为数据源，利用向导创建学生成绩明细查询如图所示。22【例3-2】利用“简单查询向导”向导创建院系成绩汇总查询。在“教学管理”数据库中，利用“学生信息”表、“选课信息”表和“课程信息”表中的有关字段，创建各院三门课程的成绩汇总如图所示。233.2.2在设计视图中创建查询1.认识查询“设计”视图组件设计视图查询类型运行显示表总计上限值属性生成器数据库新对象视图设计工具栏查询显示区查询设计区242．创建查询在查询“设计”视图中创建查询，首先应在打开的“显示表”对话框中选择查询所依据的表或查询，并将其添加到查询“设计”视图的窗口中，如果选择多个表，多个表之间应先建立关联。【例3-3】通过多个表创建选择查询。在“教学管理”数据库中，利用“学生信息”表、“选课信息”表和“课程信息”表创建一个具有“学号”、“姓名”、“课程名称”和“课程成绩”字段的查询，查询条件是“土建学院”，按“学号”升序排序。253.2.3在查询中进行计算查询对象还可以对数据进行分析和加工，生成新的数据与信息。生成新的数据一般通过计算的方法，常用的有求和、计数、求最大最小值、求平均数及表达式等。1．了解查询计算功能预定义计算：即所谓的“总计”计算，用于对查询中的记录组或全部记录进行下列的数量计算：总和、平均值、计数、最小值、最大值、标准偏差或方差。自定义计算：使用一个或多个字段中的数据在每个记录上执行数值、日期或文本计算。对于这类计算，需要直接在查询设计区中创建新的计算字段，方法是将表达式输入到查询设计区中的空“字段”单元格中。262．总计查询、分组总计查询•建立总计查询时需要在查询“设计”视图中单击工具栏上的【合计】按钮，Access将在查询设计区中添加“总计”组件，然后在“总计”行的单元格中，可列出“分组”、“总计”、“平均值”等选项。【例3-4】在“教学管理”数据库中，利用“学生信息”表统计男女生的人数。274．添加计算字段•当要统计的数据在表中没有相应的字段，或者用于计算的数据来自于多个字段时，应该在“设计网格”中添加一个计算字段。计算字段是指根据一个或多个表中的一个或多个字段，并使用表达式建立的新字段。【例3-5】分别统计各个学院各门课程的平均成绩。283.4创建参数查询•参数查询是一种可以重复使用的查询，每次使用时都可以改变其准则。•每当运行一个参数查询时，都会显示一个对话框，提示用户输入新的准则。•将参数查询作为窗体、报表和数据访问页的基础是非常方便的。•设置参数查询在很多方面类似于设置选择查询。可以使用“简单查询向导”，先从要包括的表和字段开始，然后在“设计”视图中添加查询条件；也可以直接到“设计”视图中设置查询条件。293.4.1单参数查询【例3-7】建立一个查询，显示任意月份出生的教师编号、姓名及职称。•首先创建包含所需显示字段的简单查询•在设计视图中添加查询条件•设置查询参数的数据类型303.4.2多参数查询•一个参数可视为一组条件，若想针对多组条件设置查询，可创建“多参数查询”。【例3-8】以“学生信息”表、“课程信息”表和“选课信息”表为数据源，查询某门课程和某个分数段的的学生成绩情况。31323.5创建操作查询•操作查询用于对数据库进行复杂的数据管理操作，用户可以根据自己的需要利用查询创建一个新的数据表以及对数据表中的数据进行增加、删除和修改等操作。•操作查询不像选择查询那样只是查看、浏览满足检索条件的记录，而是可以对满足条件的记录进行更改。•操作查询共有4种类型：生成表查询、更新查询、追加查询和删除查询。•所有查询都将影响到表，其中，生成表查询在生成新表的同时，也生成新表数据，而删除查询、更新查询和追加查询只修改表中的数据。33创建操作查询的步骤：1.创建简单查询或参数查询2.单击工具栏【查询类