Access和SQL Server的语法区别

作者:

现在 Access 用的人应该不多了,本来我以为我也不可能用这玩意儿,但最近在用 VBA 通过 SQL 处理数据时,发现它的语法是 Access 的语法。平时对 SQL Server 语法相对熟悉一些。下文总结了 Access 和 SQL Server 语法的差异。

1. 数据类型

1.1. Yes/No vs. BIT

对比布尔类型的列, Access 中可以使用 True/False 来比较或赋值,但 SQL 中,最好直接使用整数。

-- DETERMINING TRUE  
-- Access:  
[...] WHERE ynColumn = TRUE  
[...] WHERE ynColumn = -1  
-- SQL Server:  
[...] WHERE ynColumn <> 0  

------------------------------  

-- DETERMINING FALSE  
-- Access:  
[...] WHERE ynColumn = FALSE  
[...] WHERE ynColumn = 0  
-- SQL Server:  
[...] WHERE ynColumn = 0

1.2. Currency vs. Money

Currency 类型的好处是可以使用 Format 函数去添加货币符号和合适的小数点、千分位位置。但在 SQL Server 内部做不到这一点,需要先将结果取出然后使用外部函数去实现。

1.3. Date/Time vs. Datetime

Access 里的日期两边用「#」号包括起来,在 SQL Server 则用单引号「'」:

-- Access: 
[...] WHERE dtColumn >= #2001-11-05# 

-- SQL Server: 
[...] WHERE dtColumn >= '2013-10-01'

另外,获取当时日期时间的函数也不一样。Access 里获取时间用 Now(),获取日期用 Date(), SQL 中则使用 GetDate()或者 Current_TIMESTAMP。

1.4. Access 和 SQL 类型对比

Access SQL Server SQL Server Definition
Yes/No BIT (Integer: 0 or 1)
Number (Byte) TINYINT (Positive Integer 0 -> 255)
Number (Integer) SMALLINT (Signed Integer -32,768 -> 32,767)
Number (Long Integer) INT (Signed Integer -(2^31) -> (2^31)-1)
(no equivalent) BIGINT (Signed Integer -(2^63) -> (2^63)-1)
Number (Single) REAL (Floating precision -1.79E + 308 -> 1.79E + 308)
Number (Double) FLOAT (Floating precision -3.40E + 38 -> 3.40E + 38)
Currency MONEY (4 decimal places, -(2^63)/10000 -> ((2^63)-1)/10000)
Currency SMALLMONEY (4 decimal places, -214,748.3648 -> 214,748.3647)
Hyperlink (no equivalent - use VARCHAR())
Decimal DECIMAL (Fixed precision -10^38 + 1 -> 10^38 - 1)
Numeric NUMERIC (Fixed precision -10^38 + 1 -> 10^38 - 1)
Date/Time DATETIME (Date+Time 1753-01-01 -> 9999-12-31, accuracy of 3.33 ms)
Date/Time SMALLDATETIME (Date+Time 1900-01-01 -> 2079-06-06, accuracy of one minute)
Text(n) CHAR(n) (Fixed-length non-Unicode string to 8,000 characters)
Text(n) NCHAR(n) (Fixed-length Unicode string to 4,000 characters)
Text(n) VARCHAR(n) (Variable-length non-Unicode string to 8,000 characters)
Text(n) NVARCHAR(n) (Variable-length Unicode string to 4,000 characters)
Memo TEXT (Variable-length non-Unicode string to 2,147,483,647 characters)
Memo NTEXT (Variable-length Unicode string to 1,073,741,823 characters)
OLE Object BINARY (Fixed-length binary data up to 8,000 characters)
OLE Object VARBINARY (Variable-length binary data up to 8,000 characters)
OLE Object IMAGE (Variable-length binary data up to 2,147,483,647 characters)
AutonumberAutoincrement IDENTITY (any numeric data type, with IDENTITY property)

2. 字符串处理

2.1. 字符串语法

Access 中的字符串两边可以使用双引号和单引号, SQL 则只能使用单引号; Access 里使用「&」连接字符串,而 SQL 中使用「+」; Access 使用 Chr()将 ASCII 码转为字符,而 SQL 中类似函数为 Char()。

-- Access: 
SELECT CHR(13) & CHR(10) 

-- SQL Server: 
SELECT CHAR(13) + CHAR(10)

2.2. 字符串处理函数

Access 的字符串函数不光可以在查询数据库的语句内部使用,也可以在 VBA 中直接使用。

Access SQL Server TEXT Equivalent
CINT(), CLNG() CAST() CAST(SUBSTRING())
FORMAT() CONVERT() CONVERT(SUBSTRING())
INSTR() CHARINDEX() CHARINDEX(), PATINDEX()
ISDATE() ISDATE() ISDATE(SUBSTRING())
ISNULL() ISNULL() ISNULL()
ISNUMERIC() ISNUMERIC() ISNUMERIC(SUBSTRING())
LEFT() LEFT() SUBSTRING()
LEN() LEN() DATALENGTH()
LCASE() LOWER() LOWER(SUBSTRING())
LTRIM() LTRIM() LTRIM(SUBSTRING())
REPLACE() REPLACE() STUFF()
RIGHT() RIGHT() SUBSTRING()
RTRIM() RTRIM() RTRIM(SUBSTRING())
CSTR() STR() STR(SUBSTRING())
MID() SUBSTRING() SUBSTRING()
UCASE() UPPER() UPPER(SUBSTRING())
StrConv() n/a n/a
TRIM() n/a n/a

2.3. 字符串排序

Access 和 SQL Server 的排序顺序并不严格一致。主要区别是 Access 对特殊字符如下划线和引号的处理和普通 ASCII 顺序不一样。

Access 和 Windows 程序一致,将下划线视为最大的非字母字符,同时它在排序中会忽略引号(')以及中划线(-)。

2.4. 比较 NULL

SQL 中用 is 去判断是否 NULL ,而 Access 可以直接 使用等于或不等于的符号:

-- Access: 
[...] WHERE column <> NULL 

-- SQL Server: 
[...] WHERE column IS NULL 
[...] WHERE column IS NOT NULL

为了保持一致性,最好在 SQL Server 和 Access 里都是用 is null 语法。

3. 其它语法改变

3.1. Case vs. IIF

SQL Server 使用 Case 语法控制多分支输出,在 Access 中只有功能相对较弱的 IIF 函数:

-- Access: 
SELECT alias = IIF(Column<>0, "Yes", "No") 
    FROM table 

-- SQL Server: 
SELECT alias = CASE WHEN Column<>0 THEN 'Yes' Else 'No' END 
    FROM table

3.2. Case vs. Switch

Access 中还有 switch 函数功能和 SQL Server 中的 Case 关键词类似:

-- Access: 
SELECT Switch( 
    On=1,'On', 
    On=0,'Off' 
) FROM table 

-- SQL Server 
SELECT CASE  
    WHEN On=1 THEN 'On' 
    WHEN On=0 THEN 'Off' 
END FROM table

3.3. DistinctRow

Access 支持 SQL Server 中没有的关键词 DistinctRow。

DISTINCTROW 关键字和 DISTINCT 关键字类似,但前者是基于整行而非个别的域的。他只有在处理多个表时,并且只有在用户从某几个但非全部的表中选择数据域时才是有用的。如果用户的查询是基于一个表的,或者要从所有的表中选择数据域,则 DISTINCTROW 关键字本质上和 ALL 关键字相同。

3.4. 列别名的关键字 As

Access 里列别名必须使用 As 进行标记, SQL Server 则可要可不要。例如:

-- Access 
select column1 as col1 from table1 t1

-- SQL Server 
select column1 col1 from table1 t1

表的别名则都是可用可不用。

3.5. Access 的 NOT IN 速度慢

在通过 VBA 的 JET 引擎调用 Access 语法时, NOT IN 速度非常慢,上千行的数据可能就需要好几分钟。替代方法是:

Select t1.id from t1 
left join t2 on t1.id = t2.id 
where t2.id is null

Q. E. D.

类似文章:
如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查找、比较的效率。而如果用数字来表示日期,在不同的系统之间差别就大了。
中债的综合类指数除了中债综合以及新综合外,还有中债总指数、中债-公司信用类债券指数、中债信用债总指数。这些指数的名字取得很乱,幸好编制规则、样本选取方法都可以在 中债网站 上可以查到。下面简单摘要之:
以前总是被教育说注释写得越详细越好,我自己写代码的时候也恨不得将每一步都用自然语言给它翻译一遍,如果没写注释就觉得不专业。后来看到健硕写的 notes ,再加上最近做一个东西,也有一些感受。