Access 和 SQL Server 的语法区别

作者: , 共 5274 字

现在 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.

类似文章:
编程 » Matlab, 优化
最近做了些东西,用到了 Matlab 的优化工具箱, optimization toolbox。因为以前没用过这东西,今天把这个工具箱的帮助文档基本上翻了一遍。
相似度: 0.163
投资 » 债券学院, 基点
我在前面债券的敏感性里简单介绍过债券的 DV01 ,即俗称的基点价值。那篇文章还不够完整,我这里再补充一下。
如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查找、比较的效率。而如果用数字来表示日期,在不同的系统之间差别就大了。
该文为学习总结笔记。逐步完善中。
美式期权是指可以在期权到期之前任何一个时点行权的期权,欧式期权则只能在期权到期日行权。从这个角度上看,美式期权的价值不低于同样条款(指同样标的、到期日和行权价)的欧式期权。
相似度: 0.092
BASEL 的框架要求所有使用内部模型法计量市场风险必须要进行回溯测试。回溯测试可以:
编程 » Excel, 数据库
在前面的文章里,我已经提到Excel 数据本身可以当做一张 SQL 查询的数据表,并在 Excel 内进行数据库运算操作。数据库查询函数可以用我之前写的Excel 数据库操作函数类。我们可以用以下方式
投资 » 投行笔记
以下总结仅限于 2010 年 12 月 31 日以前的法规,如有更新,请自行勘误。
更新一下之前写的Excel 的数据库类,将其改成函数的形式,调用更简单(省却了生成类实例的步骤)。现在这个代码在工作中用了一年多,已经比较健壮。若有问题,请留言指出或与我联系。
1970 年,年仅 22 岁的 Yasuo Hamanaka (滨中泰男)加盟日本 Sumitomo 公司(住友商社),后来成为有色金属的首席交易员。从 1986 年他开始征战 LME (伦敦金属交易所)的金属铜。因为长期占据全球铜交易量的 5%,江湖人称之 Mr. 5%和 Mr. Copper ,又因为他姓 Hamanaka 且投资风格极其坚强,欧美同行称其 Mr. Hammer ,「锤子先生」。
中债的综合类指数除了中债综合以及新综合外,还有中债总指数、中债-公司信用类债券指数、中债信用债总指数。这些指数的名字取得很乱,幸好编制规则、样本选取方法都可以在中债网站上可以查到。下面简单摘要之:
以前总是被教育说注释写得越详细越好,我自己写代码的时候也恨不得将每一步都用自然语言给它翻译一遍,如果没写注释就觉得不专业。后来看到健硕写的 notes ,再加上最近做一个东西,也有一些感受。