- Foxtable数据库应用开发宝典
- 贺辉 周菁
- 4885字
- 2021-03-29 01:26:58
1.4 数据列与表达式列
1.4.1 数据列
之前在创建或修改数据表结构时,用到的都是普通数据列,这些列都可直接输入数据,所输入的全部内容也都将物理保存在项目文件中。
1.4.2 表达式列
所谓的表达式列,其内容是由一个表达式计算得出的。这些内容并不会进行物理保存,仅在运行时动态生成。这和Excel中的公式计算是一样的道理。
以Excel的订单表为例,假如希望通过单价、折扣和数量得到金额的值,可以先在G2单元格设置计算公式,然后按住G2单元格右下角的加号(填充柄)向下拖曳即可得到结果(或者双击填充柄),如下图所示。
拖曳或双击后得到的金额数值如下图所示。
此时,如果单击金额列的任意一个单元格,其上方的编辑栏显示的都是计算公式而不是具体的值,这就表明该列数据并不是物理存在的,而是根据其他列的内容动态生成的。
Foxtable的表达式列也是同样的道理。
例如,在订单表中增加一个表达式列:单击【数据表】功能区的【表相关】功能组中的【其他】命令按钮,选择下拉菜单中的【查看表结构】命令,或者直接单击【数据表】功能区的【列相关】功能组中的【增加列】按钮,选择下拉菜单中的【表达式列】命令,如下图所示。
弹出对话框如下图所示,先设置列名和数据类型。
然后单击“表达式”输入框右侧的【…】按钮,打开【表达式生成器】对话框,如下图所示。
其中,表达式编辑框下方的一排按钮为常用运算符,单击即可添加到编辑框中;下方左侧列表框列出的为常用函数,中间列表框列出的为当前数据表已经存在的数据列,双击均可添加到编辑框中;下方右侧列表框列出的为在线帮助,双击相应选项可查看具体的帮助内容。
需注意,字段名称列表中的“_Identify”和“_Locked”是隐藏列,其作用在之前的“创建数据表结构”一节中有过详细讲解。
表达式编辑完成后,单击【确定】按钮,将生成一个表达式列,如下图所示。
上图中,“金额”列的值就是根据其他列的值动态生成的。只要相关列的数据发生变化,该列数据也会自动即时更新。当需要修改表达式列的列名、标题或数据类型时,可以单击菜单上的【更改列】命令按钮;当需要修改表达式时,可单击【fx表达式】命令按钮。
注意:当表达式列没有设置计算公式时,虽然可以输入数据,但这些数据并不会被保存;已经设置了计算公式的表达式列,虽然手工可以修改列中的数据,但按“Tab”键或“Enter”键确认时仍然会自动还原为公式计算。
表达式不仅可以用于生成表达式列,还可用于条件设置等其他场合。例如,复制“订单”表时,可同时设置要复制的数据条件,如下图所示。
需注意,这里设置的条件只能使用数据列,不能使用表达式列。如上图所示,假如将条件改为:
[金额] > 500
单击【确定】按钮将不会有任何结果,这是因为“金额”列是表达式列!而且,在复制完成的数据表中表达式列不会被复制。
1.4.3 表达式编写规则
表达式由运算符、列名称及函数组成。首先说明一点,这些都是不区分大小写的。例如,运算符“In”可写成“in”,列名“产品ID”可写成“产品id”,函数“Len”可写成“len”等。
❶运算符
如同前面的“表达式生成器”对话框所示,常用的运算符有20个,实际数量比20个还要多。具体可分为以下几类。
● 算术运算符
此类运算符共有5个,分别为+(加)、-(减)、*(乘)、/(除)、%(取余数)。
其中,前4个都很好理解,第5个表示取余数:当使用%计算的结果为0时,表明前一个数可以被后一个数整除。例如,5 % 2的结果为1,表示5除以2的余数是1。
需要注意的是,使用%时,前后两个数字必须是整数,如51%2就会提示错误。
再如,将订单表中的“金额”列表达式改为:
[折扣] % [单价]
同样也会出错。这是因为折扣和单价列并不完全是整数。
● 比较运算符
此类运算符共有8个,一般需要配合表达式函数来使用。具体包括=(等于)、<(小于)、>(大于)、<=(小于等于)、>=(大于等于)、<>(不等于)、In(包含)、Like(匹配)。
其中,前6个运算符同样很好理解,例如:
[数量] < 100
现重点学习后面两个运算符。
○ In:用来判断某一个值是否为指定的多个值中的任何一个
例如,以下3个表达式就分别使用了字符、数值和日期3种数据类型进行判断,以表示指定列的值是否在所列举的范围之中:
[产品ID] In ('P01','P02','P03')
[数量] In (170,500,820)
[日期] In ('7/2/2012', #7/12/2012#, #2012-8-10#)
很显然,当使用In时,如果指定值为字符,要用单引号括起来;如果为日期,可用单引号或#号,日期值可以是“年-月-日”,也可以是“月/日/年”;其他类型数值可直接写。当判断的内容中有英文字母时,是不区分大小写的。例如,上面的第一行代码,当“产品ID”为p01、p02或p03时,也是符合判断条件的。
当指定的字符本身就带有单引号时,编写表达式时要用两个单引号代替。例如,以下代码表示判断标题名称是否为“你在用foxtable吗”:
[标题名称] In ('你在用''foxtable''吗')
由于这里只有一个判断值,因此也可以写成:
[标题名称] =‘你在用''foxtable''吗'
如果在In运算符之前加上Not关键词,表示不在指定的数值中。例如:
[产品ID] Not In ('p01','p02','p03')
○ Like:使用“*”或“%”通配符比较字符串
在这种比较中,“*”和“%”是可以互换的,表示任意个数的字符,且只能用在开头或者结尾。例如,判断以“P”开头的“产品ID”:
[产品ID] Like 'P%'
再如,判断是否以单引号结尾:
[产品ID] Like '*'''
需注意,在字符串的中间是不允许使用通配符的。如果要将通配符作为比较内容的一部分,必须使用中括号括起来,这样就会将“*”和“%”还原为普通的字符。例如,判断“产品ID”是否以“A*”开头:
[产品ID] Like 'A[*]%'
和In一样,Like运算符的前面也可以加上Not关键词,表示不类似的意思。例如:
[产品ID] Not Like '*A*'
● 连接运算符
在之前所举的表达式示例中,已经多次用到这种连接运算符。例如,[]用于连接列名称或者将“*”“%”还原为普通字符,单引号“‘”用于连接字符串,“#”用于连接日期。此外,还有以下几种常用的连接运算符。
():用于组合表达式或者强制改变优先级。例如,之前用于计算金额列的表达式为:
[数量] * [单价] * (1 - [折扣])
And:前后连接的表达式必须同时满足。例如,“产品ID”以P开头、同时数量小于500:
[产品ID] Like 'P%' And [数量] < 500
Or:前后连接的表达式只需满足一个即可。例如,“产品ID”以P开头或者数量小于500:
[产品ID] Like 'P%' Or [数量] < 500
计算表达式值时And优先于Or,可使用括号来强制改变这种优先级。例如:
([产品ID] ='P01' Or [产品ID] ='P02') And [数量] < 500
❷列名称
当在表达式中需要用到列名时,列名称最好用方括号括起来,尽管这并不是必需的。
但是,当列名称中含有一些特殊字符时,列名则必须用中括号括起来。关于这一点将在第3章中再作详细说明。
❸表达式函数
在“表达式生成器”对话框中,下方左侧的列表框就是一些常用的表达式函数。具体可分为以下几种。
● 聚合函数
此类函数一般用于数据统计,共有7个,即Sum(求和)、Avg(平均)、Min(最小值)、Max(最大值)、Count(计数)、Var(方差)、St Dev(标准偏差)。
其中,最后两个函数是用于测量数值型数据离散程度的重要指标,一般用于专业性较强的数理统计,日常工作中很少用到,基本可以忽略。
需要说明的是,表达式中的聚合函数很少在单独的数据表中使用,它一般用于关联表的统计。关于这方面的知识将在“表间关联”一节中学习。
● IIF函数
该函数使用频率很高,如果表达式的计算结果为True,则返回一个指定值;否则将返回另一个指定值。例如,将“金额”列的表达式修改为:
IIF([单价] = 0, Null ,[单价] * (1 - [折扣]) * [数量])
该表达式的意思是,如果“折扣”列的值为0,那么“金额”就设置为Null;否则就以单价、折扣、数量三列的乘积生成“金额”值。
在列表达式中,Null用于表示空值,也就是什么都没有。
● Is Null函数
该函数用于判定一个指定表达式的值是否为空值。如果不为空,则返回表达式的值;否则返回一个替代值。例如,将下图所示的第3行和第5行“折扣”为0的值按“Delete”键删除,它们就变成了空值。
然后将"金额"列的表达式修改为:
[单价] * (1 - [折扣]) * [数量]。
执行后发现,第3行和第5行“金额”列的值仍然是空。此种情况表明,只要表达式中存在空值,那么它的计算结果肯定也为空。如果希望将空值列正常参与运算,有以下两种处理方法。
第一种,使用IIF函数:
[单价] * (1 - IIF([折扣] Is Null, 0,[折扣])) * [数量]
该表达式的重点在于加粗的部分:如果“折扣”为空值,就以0替代;否则仍然取原来的折扣值。如果判断不为空,可使用Is Not Null。
第二种,使用Is Null函数:
[单价] * (1 - Is Null([折扣],0)) * [数量]
这里Is Null函数的意思是:如果“折扣”为空,那么就以0替代;否则仍然用原来的折扣值。
很显然,使用Is Null语句返回的是一个逻辑值;而使用Is Null函数则直接返回具体的数值。虽然仅有一个空格之差,但意义却完全不同!
以上两种方式的处理结果都是一样的,如下图所示。
● 字符处理函数
这方面的表达式函数有3个,即Trim(移除字符串的前后空格)、Substring(从字符串中的指定位置开始返回指定长度的子字符串)、Len(返回字符串的长度)。
例如,再增加一个临时的字符型表达式列,如下图所示。
这里表达式的意思是,取“产品ID”列和“客户ID”列的前两个字符并进行拼接(Substring的起始位置是从1开始的)。生成的表达式列效果如下图所示。
● Convert转换函数
该函数可以将表达式转换为指定的数据类型。其语法格式为:
Convert(expression,type)
其中,参数expression为要转换的表达式;type为转换成的数据类型。可转换的数据类型包括以下几种。
System String:字符型
System Date Time:日期时间型
System Boolean:逻辑型
System Byte:微整数
System Int16:短整数
System Int32:整数
System Int64:长整数
System Single:单精度小数
System Double:双精度小数
System Decimal:高精度小数
其中,只有“System Int64”和Foxtable数据表中的列类型不存在对应关系。该类型表示长整数,其值介于-9 223 372 036 854 775 808~+9 223 372 036 854 775 807之间。在Foxtable数据表中,此范围的整数一般是用双精度小数代替。
Convert函数除了可以转换数据类型外,还可变相实现一些其他功能。例如,列表达式并没有提供专门的四舍五入函数,只能用Convert函数来间接实现。假如将金额列保留两位小数,表达式可修改为:
Convert([单价] * (1 - Is Null([折扣],0)) * [数量] * 100, 'System.Int64')/100
该表达式的意思是,先将得到的计算结果乘以100,再转换为长整数;然后再以这个长整数除以100,得到的数据肯定最多只有两位小数。同理,如果要保留3位小数,将表达式中的100改为1000即可,其余类推。
再比如,利用数据表中隐藏的自动编号列“_Identify”,可以生成指定格式的编号列。假如希望编号列能够按照下面的格式显示:
MP0001
MP0002
┇
MP0011
MP0012
┇
MP0123
MP0124
┇
MP9998
MP9999
这种编码的规则是:前面是两个字母“MP”,后接4个数字,当数字的长度没有达到4位时要自动补充0。实现此目的的重点在于如何根据“_Identify”的值来确定补充多少个0:当“_Identify”的值是一位数时,要加3个0;是两位数时,加2个0;是3位数时加1个0。由此可见,需要补充的0的数量为:用4减去“_Identify”值的长度。用代码表示为:
4 - Len(Convert([_Identify],’System.String’))。
把这个长度用到Substring函数中,就表示要从字符串“0000”中截取的长度,也就是需补充多少个0:
Substring(‘0000’, 1, 4 - Len(Convert([_Identify],’System.String’)))
然后再在其前面加上字符串“MP”、在尾部加上“_Identify”列的值即可:
'MP'+ Substring('0000', 1, 4 - Len(Convert([_Identify],'System.String'))) + Convert([_Identify], 'System.String')
运行效果如下图所示。
如果要将编码的数字固定为6位,可将上述表达式中的“0000”改为“000000”,将4改为6。因而这种自动编号的表达式是可以通用的。
需要注意的是,当在列表达式中使用Convert函数进行数据类型转换时,日期型的数据只能与字符串进行转换,逻辑型数据只能与字符串及整数类型(Byte、Int16、Int32、Int64)进行转换。
1.4.4 表达式列和数据列的相互转换
单击【数据表】功能区的【列相关】功能组中的【其他】按钮,将弹出表达式列与数据列相互转换的下拉菜单,如下图所示。
❶ 当数据列转换为表达式列时,该数据列中的原有内容将全部消失,因为表达式列只能通过设置的表达式来动态生成数据。
❷ 同理,当表达式列转换为数据列时,由于该列的表达式已经被删除,因此数据一样会消失。既然已经转换为数据列,就只能自行编辑输入数据。
不论是数据列转换为表达式列,还是表达式列转换为数据列,一旦确定转换,Foxtable都会自动重新打开当前项目。