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

This article mainly explains the "what are the differences between the SQL sentences of Access and SQL Server". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the differences between the SQL sentences of Access and SQL Server"?

1. Data type conversion:

Access: SELECT 'Survey' + CStr (Did) as diaocha FROM CZdengji

SQL Server: select artid,' survey'+ Cast (listid As varchar) as did from kingart where artdate > = getdate ()

The data type conversion of SQL queries in Access has a number of functions, each of which can force an expression to be converted to a specific data type. The 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 we can understand the meaning of these conversion symbols without explanation.

2. IIF function (for ACCESS only)

Access: SELECT Id,IIF (Isfinished=1,' completed','in progress'), Name,Diaocharen FROM CZdiaocha ORDER BY Isfinished ASC

SQL Server can be written in more ways. You can customize functions or use case statements, such as select Id,case Isfinished when 1 then 'completed'.

When 0 then'in progress' end,Name,Diaocharen FROM CZdiaocha ORDER BY Isfinished ASC

Third, DISTINCT keyword usage: the use of distinct (expression) in Access is single, expression can only be a specified field, not an expression, SQL Server is different, expression can be a single field, can also be a subquery and other complex expressions.

IV. Date formatting

Access: use the format () function, SELECT TOP 5 arttitle,format (artdate,'YY-MM-DD') as arttime FROM kingart WHERE listid=9, here use the format () function to specify that the query format of artdate (date type) is YY-MM-DD.

SQL Server: use the convert () function, select Id,lanmu,title,content, convert (varchar (10), updatetime, 120) as update_time from news, where the format is specified as YY-MM-DD, if it is another number (101120), it is another format.

A collection of other commonly used SQL sentences

SELECT Top 5 * From Food WHERE Fid not in (Select top 5 Fid FROM Food) / / query articles 5 to 10 records

Delete from Food where charindex ('2007 records, uptime) > 0 or charindex (' 2008 records, uptime) > 0 / delete the uptime column in the Food table contains 2007 or 2008 records, that is, the updated records were deleted in 2007 and 2008

The convert function in select Id,lanmu,title,content, convert (varchar (10), updatetime, 120) as update_time from news / / SQL Server, converted to varchar (10) type, 120 is the specified converted format.

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 / / grouping query, the original meaning of GROUP BY is that the repeated fields are combined into one, and the corresponding information is summarized (count, sum, etc.)

Select Count (ID) from Flow where convert (varchar (21), visittime,120) like '2007-01-26%', fuzzy query of date data.

Select * from ('MSDASQL','DRIVER= {SQL Server}; SERVER= server name; account number of UID= peer database; PWD= peer password', peer database. The person with authority. Peer table) as table / / cross-server query

Thank you for your reading, the above is the content of "what are the differences between Access and SQL Server SQL sentences". After the study of this article, I believe you have a deeper understanding of the differences between Access and SQL Server SQL sentences, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.