五维思考

学习要加，骄傲要减，机会要乘，懒惰要除。 http://www.5dthink.cn
  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
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


posted on 2017-02-03 10:54  五维思考  阅读(2267)  评论(0)  编辑  收藏  举报

QQ群：1. 全栈码农【346906288】2. VBA/VSTO【2660245】