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.


上一篇:Matlab、Excel、SQL中的日期的数字序列形式2010年10月6日
如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查

下一篇:什么是好的程序注释2014年11月28日
注释的作用主要解释why。关于What和How应直接用代码体现。


  • 支持使用微薄、微信和QQ的账户登陆进行评论。由各自网站直接认证,不会泄露你的密码。
  • 登陆后可选择分享评论到所绑定的社交网络,如微薄、人人和QQ空间。
  • 评论提交后无法修改。如需修改,请删除原评论再重新提交。
  • 评论支持LaTeX代码,行内公式请用\(a+b=c\),行间公式请用\[a+b=c\]。公式只支持英文字符。