将 Access SQL 与 SQL Server TSQL 进行比较

如果要将 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 函数

转换

Chr 函数

CHAR

转换

Day 函数

DAY

转换

FormatNumber 函数

FORMAT

转换

FormatPercent 函数

FORMAT

转换

Str 函数

STR

转换

类型转换函数

CAST 和 CONVERT

日期/时间

Date 函数

CURRENT_TIMESTAMP

日期/时间

Day 函数

DATEFROMPARTS

日期/时间

DateAdd 函数

DATEADD

日期/时间

DateDiff 函数

DATEDIFF

DATEDIFF_BIG

日期/时间

DatePart 函数

DATEPART

日期/时间

DateSerial 函数

DATEFROMPARTS

日期/时间

DateValue 函数

DATENAME

日期/时间

Hour 函数

TIMEFROMPARTS

日期/时间

Minute 函数

TIMEFROMPARTS

日期/时间

Month 函数

MONTH

日期/时间

Now 函数

SYSDATETIME

日期/时间

Second 函数

TIMEFROMPARTS

Time 函数

TIMEFROMPARTS

日期/时间

TimeSerial 函数

TIMEFROMPARTS

日期/时间

Weekday 函数

DATEPART

DATENAME

日期/时间

Year 函数

YEAR

DATEFROMPARTS

域聚合

DFirst、DLast 函数

FIRST_VALUE

LAST_VALUE

数学

Abs 函数

ABS

数学

Atn 函数

ATAN

ATN2

数学

Cos 函数

COS

ACOS

数学

Exp 函数

EXP

数学

Int、Fix 函数

FLOOR

数学

Log 函数

LOG

LOG10

数学

Rnd 函数

RAND

数学

Round 函数

ROUND

数学

Sgn 函数

SIGN

数学

Sin 函数

SIN

数学

Sqr 函数

SQRT

程序流程

Choose 函数

CHOOSE

程序流程

IIf 函数

IIF

统计

Avg 函数

AVG

SQL 聚合

Count 函数

COUNT

COUNT_BIG

SQL 聚合

Min、Max 函数

MIN

MAX

SQL 聚合

StDev、StDevP 函数

STDEV

STDEVP

SQL 聚合

Sum 函数

SUM

SQL 聚合

Var、VarP 函数

VAR

VARP

文本​​

Format 函数

FORMAT

文本​​

LCase 函数

LOWER

文本​​

Left 函数

LEFT

文本​​

Len 函数

LEN

文本​​

LTrim、RTrim 和 Trim 函数

TRIM

LTRIM

RTRIM

文本​​

Replace 函数

REPLACE

文本​​

Right 函数

RIGHT

文本​​

StrReverse 函数

REVERSE

文本​​

UCase 函数

UPPER

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。