Access与SQL Server 语法差异

 

序号	简述	Access语法	SqlServer语法	Oracle语法	解决方案
01	系统时间	Now(),Date()	GETDATE()	SYSDATE	GetSysTimeStr
02	连接字符串	&	+	||	GetConcatStr
03	截取字符串	Mid	SubString	SubString	GetSubStr
04	小写字符串	LCase	Lower	Lower	GetLowerStr
05	大写字符串	UCase	Upper	Upper	GetUpperStr
06	查找字符串	InStr	InStr	CharIndex	GetFindStr
07	替换空值	IIF+IsNull	Coalesce	Nvl	GetNullStr
08	条件取值	IIF, Switch	Case+When+Else	DeCode或Case	GetCaseStr
09	字段类型转换	CStr,CInt,CDate	Convert或cast	To_Char,To_Number.	GetConvertStr
10	日期字符串	#2004-10-19#	‘2004-10-9’	‘2004-10-9’	GetDateStr
11	最大值加1				GetNextNumStr
12	Like语句函数	Like ‘101*	Like ‘101%’	Like ‘101%’	GetLikeStr

 

1、select，update语句区别

select，update对于单表操作时基本一致。  

但多表操作时Access与SqlServer中的update语句会有差别：

SqlServer中更新多表的update语句：  

update Table1 set a.Name = b.Name from Table1 a, Table2 b where a.ID = b.ID;

Access中同样功能的sql语句:

update Table1 a, Table2 b set a.Name = b.Name where a.ID = b.ID;

比较得出：Access中的update语句没有from子句,所有引用的表都列在update关键字后

2、delete语句  

   SqlServer中：delete  from <表名> where ID > 1;

   Access中： delete * from<表名>where ID > 1;

3、as后面的计算字段区别

 SqlServer中：

select sum(num) as sum_num, sum(num)*num as all_num from <表名>;

    Access中:    

select sum(num) as sum_num, sum_num*num  as all_num from <表名>;

比较得出：

SqlServer中：不可以把as后的字段当作一个数据库字段参与计算。

Access中:可以把as后的字段当作一个数据库字段参与计算。 

     SQL Server as可以省略，Access不能

4、时间字段、日期分隔符号

    SqlServer中：单引号（’）

select * from<表名> where RegDate = '2011-08-10' 

    Access中：要用#号分隔，转换为日期

select * from<表名>where 生日=#2011-08-10#

select * from<表名>where 生日=2011-08-10

select * from<表名>where 生日=CDate(#2011-08-10#)

select * from<表名>where 生日=CDate(2011-08-10)

select * from<表名>where 生日=CDate('2011-08-10')

5、Boolean所表示的常量

    SqlServer中：整数：1（真）、0（假）

    Access中：True、False；On、Off；Yes、No；整数：-1(真)、0(假)。

6、字符串连接

    SqlServer中：加号（+）

    Access中：和号（&）

7、通配符

    SqlServer中：

     百分号（%）与零个或更多字符匹配。 

     下划线（_）与单个字符匹配。

     上插入符（^）意味着不在列表中。

     没有与英镑符（#）对应的字符。

    Access中：   星号（*）与零个或更多字符匹配。 

   　　　　　　  问号（?）与单个字符匹配。

   　　　　　　  叹号（!）意味着不在列表中。

   　　　　　　  英镑符（#）意味着单个数字。

8、DropIndex

    SqlServer中：Drop Index<表名>.<索引名>

    Access中： Drop Index<索引名> ON <表名>

9、表添加标识列

 Access：alter table <表名> add 列名 Counter(1,1);

SqlServer：alter table <表名> add 列名 bigint identity(1,1) not null;

10、系统时间及常用时间函数

SqlServer:   select getdate()

Access:    select date()+time()    SelectNow()

SqlServer 中获取日期时间函数是convert(datetime,getdate(),0)

    而在Access中为date()+time() 或者 Now()

在Access中，datediff ()和dateadd ()函数表示时间类型的部分必须用单引号括起来

select datediff('n',addtime,now()) from 表名;

select dateadd('d',5,now());

而在SqlServer中，必须写成

select datediff(n,addtime,getdate()) from 表名;

select dateadd(d,5,getdate());

11、 Access不支持between语句和Case+When+Else语句

12、字符串函数（截取字符串、大写、小写、查找字符串位置）

   Access:  Mid、UCase、LCase、InStr

         select Mid(列名,2,4) from <表名>;

        select UCase(列名) from  <表名>;

        select LCase(列名) from  <表名>;

        select InStr(列名, 'abc') from  <表名>;//查找字符串位置

   SqlServer: SubString、Upper、Lower、CharIndex

         select substring(列名,2,4) from <表名>;

        select Upper(列名) from <表名>;

        select Lower(列名) from  <表名>;

        select CharIndex('abc', 列名) from  <表名>;//查找字符串位置

        select CharIndex('abc', 列名,2) from  <表名>;//查找字符串位置

13、 Access中的逻辑值在库中为-1和0,而SQL Server中为1和0,所以写BoolField= 1这样的语句有兼容性问题，应该改为BoolField <>0

14、Access的varchar（文本）型最大只有255，所以如果一个文本型字段大于255时，最好定义成备注型（Access中）或text型（SQL Server中）；

15、数据类型：

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)

 

Access如果用自动编号且主键，如下：

create table table1 (idautoincrement(1,1) primary key)

create table AAA(RID Counter,url Memo,ActNameMemo, ActDate text(150), Up_Time text(100))

连接字符串：conn = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source = database.mdb";

16、随机读取若干条记录

   SqlServer: select top 5 * from <表名> order by newid();

   Access:  select top 5 * From <表名> ORDER BY Rnd(id);

17、条件取值Case+When+Else、IIF、Switch的使用

  使用SQL语句用...代替过长的字符串显示

  SqlServer: select case

when len(列名)>10 then left(列名,10)+'...'

else 列名

end as 别名

from  表名;

Access:  select IIF(len(列名)>2,left(列名,2)+'...', 列名) from  表名;

select Switch( On=1,'On', On=0,'Off') fromtable

18、余数

   Access:   a mod b

SqlServer: a % b

19、判断字段值为空的区别

普通空：

Access和SqlServer一样  where code is null 或where code is not null

条件空：

Access：iif(列名 is null,0, 列名) 或iif(列名 is null, 列名2, 列名)

SqlServer: isnull(列名,0) 或isnull(列名, 列名2)

20、截取字符串

Access:   select Mid(列名,2,4)from  <表名>;

SqlServer: select substring(列名,2,4) from  <表名>;

21、字段类型转换及一些函数异同

  Access:

    CDate将字符串转化成为日期select CDate(“2011/08/11”)

CStr  将括号中的内容转换为字符串，括号中的内容可以是值、变量或表达式

select CStr(155)  值是"155"

CInt  将     select Cint("200"&"40")  值是"20040"

Format   格式化字符串，select Format(now(),'yyyy-mm-dd')返回类似于"2011-08-11" , select Format (3/9,"0.00")返回0.33

Space产生空格select Space(4)返回4个空格

StrComp比较两个字符串是否内容一致(不区分大小写) select StrComp ("abc","ABC")

返回0, select StrComp("abc","123")返回-1

InStr   查询子串在字符串中的位置select Instr("abc","a")返回1, select Instr ("abc","f")返回0

Mid    取得子字符串select Mid ("123",1,2) 返回12

Choose根据第一参数，返回后面字符串组的值, select Choose(1,"a","b","c")返回a,将1改成2后，返回b,改成3后，返回c(第一个参数也可以是某个字段)

     IIF根据表达式返回特定的值select IIF("3>1","OK","False")，返回OK

SqlServer:

 select cast('2011-08-11' as datetime)

 select convert(varchar(10),getdate(),120) --2011-08-11

 select replace(convert(varchar(10),cast('2011-08-11' as datetime),120), '-','') –20110811223308

22、Access使用Chr()将ASCII码转为字符，而SQL中类似函数为Char()

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

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

23、字符串处理函数

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

 

24、Access里列别名必须使用As进行标记，SQLServer则可要可不要；表的别名则都是可用可不用。

25、Access的NOT IN速度慢

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

SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHEREt2.id IS NULL

26、Currency与Money

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

27、Yes/No与BIT

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

--Access: 

[...]WHERE ynColumn = FALSE 

[...]WHERE ynColumn = 0 

-- SQLServer: 

[...]WHERE ynColumn = 0