如果要将 Access 数据迁移到 SQL Server 或创建一个以 SQL Server 为后端数据库的 Access 解决方案,则了解 Access SQL 与 SQL Server Transact SQL (TSQL) 之间的差异至关重要。 下面是为了让解决方案按预期工作而需要了解的重要差异。
有关详细信息,请参阅Access SQL:基本概念、词汇和语法以及 Transact-SQL 参考。
语法和表达式差异
有些语法和表达式差异需要转换。 下表总结了最常见的差异。
差异 |
Access SQL |
SQL Server TSQL |
关系数据库属性 |
通常称为字段 |
通常称为列 |
字符串文本 |
双引号 ("),如 "Mary Q. Contrary" |
单引号 ('),如 'Mary Q. Contrary' |
日期文本 |
井号 (#),如 #1/1/2019# |
单引号 ('),如 '1/1/2019' |
多个通配符 |
星号 (*),如 "Cath*" |
百分号 (%),如 'Cath%' |
单个通配符 |
问号 (?),如 "Cath?" |
下划线 (_),如 "Cath_" |
求模运算符 |
MOD 运算符,如 Value1 MOD Value2 |
百分号 (%),如 Value1% Value2 |
布尔值 |
WHERE Bitvalue = [True | False] 或 WHERE Bitvalue = [-1 | 0] |
WHERE Bitvalue = [1 | 0] |
参数 |
[<不是已定义列的名称>] 或 在 SQL 视图中,使用 SQL 参数声明 |
@ParamName |
注意
-
Access 在表名称和对象周围使用双引号字符 (")。 T-SQL 可将双引号用于带空格的表名称,但这不是标准的命名惯例。 在大多数情况下,对象名称应重命名为不带空格,但还必须重写查询以反映新的表名称。 不能重命名的表可使用方括号 [ ],但这不符合命名标准。 Access 还会在查询中的参数周围添加额外的圆括号,但在 T-SQL 中可将其删除。
-
请考虑使用规范的日期格式 yyyy-mm-dd hh:nn:ss,这是一种将日期存储为字符的 ODBC 标准,这样可以提供一种在数据库之间表示日期的一致方法,并能保留日期排序顺序。
-
若要在比较布尔值时避免混淆,可对 Access 和 SQL Server 使用以下比较:
-
测试是否为 false 值 WHERE Bitvalue = 0
-
测试是否为 true 值 WHERE Bitvalue <> 0
-
Null 值
Null 值不是表示“完全没有任何值”的空字段。 Null 值是一个占位符,表示数据缺失或未知。 能识别 Null 值的数据库系统会实现“三值逻辑”,表示可能会出现 true、false 或未知的情况。 如果未正确处理 Null 值,则在进行相等比较或计算 WHERE 子句时,可能会得到错误的结果。 下面比较了 Access 和 SQL Server 对 Null 值的处理方式。
在表中禁用 Null 值
在 Access 和 SQL Server 中,默认体验是启用 Null 值。 若要在表列中禁用 Null 值,请执行下列操作:
-
在 Access 中,将字段的 Required 属性设置为 Yes。
-
在 SQL Server 中,在 CREATE TABLE 语句中向列添加 NOT NULL 属性。
在 WHERE 子句中测试是否有 Null 值
使用 IS NULL 和 IS NOT NULL 比较谓词:
-
在 Access 中,使用 IS NULL 或 IS NOT NULL。 例如:
SELECT … WHERE column IS NULL.
-
在 SQL Server 中,使用 IS NULL 或 IS NOT NULL。 例如:
SELECT … WHERE field IS NULL
使用函数转换 Null 值
使用 Null 函数来保护表达式并返回替代值:
-
在 Access 中,使用 NZ (value, [valueifnull]) 函数来返回 0 或另一个值。 例如:
SELECT AVG (NZ (Weight, 50) ) FROM Product
-
在 SQL Server 中,使用 ISNULL(Value, replacement_value) 函数来返回 0 或另一个值。 例如:
SELECT AVG (ISNULL (Weight, 50)) FROM Product
了解数据库选项
某些数据库系统包含专有机制:
-
在 Access 中,没有与 Null 相关的数据库选项。
-
在 SQL Server 中,可以借助 SET ANSI_NULLS OFF 选项,使用 = 和 <> 运算符进行与 NULL 之间的直接相等比较。 建议避免使用此选项,因为已将其弃用,可能会使依赖符合 ISO 标准的 Null 值处理方式的其他人感到困惑。
转换和强制转换
每当使用数据或进行编程时,总是需要从一种数据类型转换为另一种数据类型。 转换过程可能简单,也可能复杂。 需要考虑的常见问题有:隐式或显式转换、当前日期和时间区域设置、数字的四舍五入或截断以及数据类型大小。 完全测试和确认结果是无可替代的操作。
在 Access 中,需要使用类型转换函数,其中包含 11 个函数,每个函数都以字母 C 开头,每个数据类型对应一个函数。 例如,若要将浮点数字转换为字符串,请运行:
CStr(437.324) returns the string "437.324".
在 SQL Server 中,主要使用 CAST 和 CONVERT TSQL 函数,但还有满足特殊需求的其他转换函数。 例如,若要将浮点数字转换为字符串,请运行:
CONVERT(TEXT, 437.324) returns the string "437.324"
DateAdd、DateDiff 和 DatePart 函数
在 Access 和 TSQL 中,这些常用的日期函数(DateAdd、DateDiff 和 DatePart)是类似的,但第一个参数的使用会有所不同。
-
在 Access 中,第一个参数称为 interval,它是需要用引号引起来的字符串表达式。
-
在 SQL Server 中,第一个参数称为 datepart,它使用不需要引号的关键字值。
组件
Access
SQL Server
年
"yyyy"
year、yy、yyyy
季度
"q"
quarter、qq、q
月
"m"
month、mm、m
一年中的某一天
"y"
dayofyear、dy、y
日
"d"
day、dd、d
周
"ww"
wk、ww
一周中的某一天
"w"
weekday、dw
小时
"h"
hour、hh
分钟
"n"
minute、mi、n
秒
"s"
second、ss、s
毫秒
millisecond、ms
函数比较
Access 查询中可能包含的计算列有时会使用 Access 函数获取结果。 将查询迁移到 SQL Server 时,需要将 Access 函数替换为等效的 TSQL 函数(如果可用)。 如果没有相应的 TSQL 函数,则通常可以创建计算列(同 TSQL 术语“计算列”)来执行所需的操作。 TSQL 有大量函数,因此了解哪些函数可用将很有好处。 有关详细信息,请参阅 SQL 数据库函数有哪些?。
下表显示了 Access 函数与 TSQL 函数的对应关系。
Access 类别 |
Access 函数 |
TSQL 函数 |
转换 |
||
转换 |
||
转换 |
||
转换 |
||
转换 |
||
转换 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
日期/时间 |
||
域聚合 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
数学 |
||
程序流程 |
||
程序流程 |
||
统计 |
||
SQL 聚合 |
||
SQL 聚合 |
||
SQL 聚合 |
||
SQL 聚合 |
||
SQL 聚合 |
||
文本 |
||
文本 |
||
文本 |
||
文本 |
||
文本 |
||
文本 |
||
文本 |
||
文本 |
||
文本 |