The difference between SQL statements in Access and SQL Server _ database other

My feeling is that the Accees database, while a small relational database, is also used in Structured Query language SQL, but its syntax (mainly embodied in functions), but similar to the syntax of VBScript, I think, this should be related to access belongs to the Office family, Based on its development and application, it is naturally related to VBA, so access's function library is VBA's function library, not the SQL function library. Next, let's look at the specifics of how access and SQL Server are written in query statements.

One, data type conversion:

1. Access: SELECT ' investigate ' +cstr (Did) as Diaocha from Czdengji
2. SQL Server: select ArtID, ' Investigate ' +cast (listid as varchar) as did from Kingart where Artdate>=getdate ()

There are many functions for data type conversions of SQL queries in Access, and each function can force an expression to be converted to a specific data type. Details are as follows: CBool (expression), cbyte (expression), CCur (expression), CDate (expression), CDbl (expression), CDec (expression), CInt (expression), CLng (expression), csng (expression), CStr (expression), CVar (expression), CStr (expression), I believe it is not necessary to explain, We can also understand the meaning of the various conversion characters.

Second, IIf function (Access-specific)

1. Access: SELECT id,iif (isfinished=1, ' completed ', ' in Progress '), Name,diaocharen from Czdiaocha order by isfinished ASC
2. SQL Server is more often written, you can customize functions, or you can use case statements, such as select Id,case isfinished when 1 Then ' completed '
   When 0 Then ' ongoing ' End,name,diaocharen from Czdiaocha order by isfinished ASC

Three,distinct keyword usage: access inside distinct (expression) the use of a single, expression can only be a specified field, but not an expression, SQL Server is different, Expression can be a complex expression such as a single field, or a subquery.

Four, date format

1. access: using the Format () function, SELECT Top 5 Arttitle,format (artdate, ' yy-mm-dd ') as Arttime from Kingart WHERE listid= 9, this uses the format () function to specify that the Artdate (date-type) query is formatted as YY-MM-DD.
2. SQL Server: using the CONVERT () function, select Id,lanmu,title,content, convert (varchar, updatetime,) as Update_time From news, here's 120, is the specified format for YY-MM-DD, if the other number (101-120), is the other format.

A collection of other common SQL statements

2. Select Top 5 * "from Food WHERE Fid" (select Top 5 Fid Food) //query 5th to 10th Records
4. Delete from Food where CHARINDEX (' 2007 ', uptime) >0 or charindex (' 2008 ', uptime ) >0 // Delete the uptime column in the Food table contains 2007 or 2008 records, that is, the update record for 2007 and 2008 for deletion time
6. Select Id,lanmu,title,content, convert (varchar, updatetime) as Update_time from news// SQL Server con The vert function, converted to the varchar (10) type, and 120 to the specified converted format.
8. Select Mealtype as meal, Fname as food name, sum (RJJINGSHISZ)/2 as per capita net food weight sum from Czdengji WHERE did=6 GROUP by Mealtype,fname //GROUP BY, the original intention is to repeat the field together as a, the corresponding information, to summarize (count, sum, and other operations)
10. Select Count (ID) from the flow where convert (varchar), visittime,120) like ' 2007-01-26% ', date-type data fuzzy query.
    
12. SELECT * FROM (' msdasql ', ' Driver={sql Server}; server= server name; Uid= to the end of the database account; Pwd= to the end of the password ', to the end of the database. The right side of the table as table //cross-server query