Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what is the difference between Access and sql server grammar. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Date separator

Access: Sterling sign (#)

Sql server: apostrophe (')

Boolean constant

Access:True, False;On, Off;Yes, No; integers:-1 (true), 0 (false).

Sql server: integer: 1 (true), 0 (false)

String concatenation

Access: and sign (&)

Sql server: plus sign (+)

Wildcard character

Access: the asterisk (*) matches zero or more characters.

The question mark matches a single character.

An exclamation point (!) means that it is not in the list.

The pound sign (#) means a single number.

Sql server: percent sign (%) matches zero or more characters.

The underscore (_) matches a single character.

The caret (^) on the means it is not in the list.

There is no character corresponding to the pound sign (#).

DROP INDEX

Access:Drop Index ON

Sql server:Drop Index.

Table add identity column

Access:alter table add Counter (1 dint 1)

Sql server:alter table add bigint identity (1 dint 1) not null

1. Different functions and solutions

The functions in the solution shown below are defined in the methods of the TAdoConn class in the untDataBase unit.

Brief introduction of Access syntax SqlServer syntax Oracle syntax DB2 syntax solution 01 system time Date () GETDATE () SYSDATEGetSysTimeStr02 connection string & + | + GetConcatStr03 intercept string SubString

SubStr

SubStringSubStringGetSubStr04 lowercase string LCaseLowerLowerLowerGetLowerStr05 uppercase string UCaseUpper

Upper

Upper

GetUpperStr06 lookup string InStr

InStr

CharIndex

InStr

GetFindStr07 replaces null IIF+IsNullCoalesce

NvlCoalesceGetNullStr08 condition is IIFCase+When+ElseDeCode or Case.

IIF

GetCaseStr09 field type conversion Str, var, … .convert or cast

To_Char,To_Number.GetConvertStr

GetConvertStr10 date string

'2004-10-9'

# 2004-10-10

'2004-10-9'GetDateStr11 maximum plus 1

GetNextNumStr12Like statement function Like '101*Like' 101%'

Like '101%'

GetLikeStr

Access and SQLSERVER part of the same database functions and keyword list

1. Function

Serial number

A brief introduction of 01 counting function

Count02 maximum Max

2. Keywords

Serial number

Brief introduction 01

Like02 connects Join03 to determine empty Is Null

Third, the difference of sentence syntax between Access and statement SqlServer.

1. Inser Into... .. Select... From statement:

In ACCESS, the following statement

Insert INTO

PubSubJectAccCopys (Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) (Select 2002011Accessionidgrad Accedence PidMagi AccelernameGravity AcceleratorAccedCommentEng AccelertypeAccept ProcrityAccedityAccelerityAccelerityAccelerityAccedtypeAccentryAccelerityAccedityAcceleromityAccelerityAccelertypeAccedityAccelerictypeAccelerityAccelertiy; AcceleryAccedtypeAccedityAccept;

The parentheses ("(", ")") in the middle and back of "(select 200201.coexisting typewritten characters 03')" must be removed before they can be executed, as follows:

Insert INTO

PubSubJectAccCopys (Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) Select 2002011AcceleridLegend AccelerPidGradeAccednamePercentAccedCommentPersonAccedityAccelertypeAccelerityAccept PidGrai AccelernameGravity Acceleration index FROM PubSubJectAcc Where PubSubJectAcc.co_type='03'

It can be done in SQL SERVER.

2. Inner Join statement 1

StrSql:='select a.userroomidon a.user_id=c.user_id where a. Userroomopcodereb. Copywriter name from sysuser an inner join (syscopysuser c inner join syscopys b on c.copy_id=c.copy_id) on a.user_id=c.user_id where

A. Username opcodefficient users'+ EdtUserOpCode.text+''' And copy_name='''+Tmpcopyname +'

Should be changed to

StrSql:='select a.userroomidon a.user_id=c.user_id where a. Userroomopcodereb. Copywriter name from sysuser an inner join (syscopysuser c inner join syscopys b on c.copy_id=d.copy_id) on a.user_id=c.user_id where

A. Username opcodefficient users'+ EdtUserOpCode.text+''' And copy_name='''+Tmpcopyname +'

The search condition of this line of code is wrong: C.copy_id=C.Copy_id should be changed to c.copy_id=d.copy_id

Note: both writing methods can be run in SQL-SERVER, but c.copy_id=C.copy_id cannot be run in ACCESS

3. Inner Join statement 2

StrSql:='select copy_year,copy_name,a.copy_id from SysCopys an inner join SysCopysUser b on a.curcopy_flag=1 and a.copy_id=b.copy_id where b.usernames'+''+ TmpPubUserID+'

Should be

StrSql:='select copy_year,copy_name,a.copy_id from SysCopys an inner join SysCopysUser b on a.copy_id=b.copy_id where a. Curcopycats, please read 1, 'and b.usernames,' +''+ TmpPubUserID+''

Note: both writing methods can be run in SQL-SERVER, but the first one cannot be run in ACCESS

4. Inner Join statement 3

The following statements can be executed in SQl server

'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id AND sysroleoption.role_id=:roleid'

But not in ACCESS, only

'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id Where sysroleoption.role_id=:roleid'

5. Update statement

Can be executed in Sql SerVer but not in Access

'Update sysuserrole SET sysuserrole.role_sort = (Select sysrole.role_sort FROM sysrole Where sysuserrole.role_id = sysrole.role_id and sysuserrole.user_id='01')'

6. Date comparison

Used in SQL SERVER

StrSql:='select copy_year,Start_month,Cur_month,Start_Flag,Start_date,End_date'

+ 'From SysCopys'

+ 'where copy_id='''+LoginCopyID+''''

+ 'and start_date='''+datetostr (LoginDate) +'

Used in ACCESS

StrSql:='select copy_year,Start_month,Cur_month,Start_Flag,Start_date,End_date'

+ 'From SysCopys'

+ 'where copy_id='''+LoginCopyID+''''

+ 'and start_date=#'+datetostr (LoginDate) +' #'

Refer to the 10th function "GetDateStr" above

7. Maximum value acquisition statement

StrSql:='insert into sysRoleOption'

+ 'select''+ fidRoleId+''' as Role_ID,opti_id,'

+ 'convert (numeric,opti_id)-(convert (numeric,opti_parentid) * 100) +' + MaxOptiSort

+'as opti_Sort from sysoption where opti_parentid='''

+ PCoTypeID (self.trvRoles.Selected.data) ^ .StrCoTypeID

+ 'and opti_bottom=''1'+''''

Change to

StrSql:='insert into sysRoleOption'

+ 'select''+ fidRoleId+''' as Role_ID,opti_id,'

+ 'opti_id-opti_parentid*100+'+ MaxOptiSort

+'as opti_sort from sysoption where opti_parentid='''

+ PCoTypeID (self.trvRoles.Selected.data) ^ .StrCoTypeID

+ 'and opti_bottom=''1'+''''

Note: both writing methods can be run in SQL-SERVER, but the first one cannot be run in ACCESS

However, considering the occurrence of null values and the generality of statements, you can use the 07 and 09 functions "GetNullStr" and "GetConvertStr" above to convert strings to numbers, null values and 0 numbers: refer to the GetNextNumStr code.

After reading the above, do you have any further understanding of the difference between Access and sql server grammar? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.