SQLite Foundation and its syntax differences with SQL Server

1 Top This is a frequently asked question, such as the following statements can be used in SQL Server to obtain the first 10 records in a Recordset: SELECT TOP * FROM [index] ORDER by IndexID DESC; However, this SQL statement is not executable in SQLite and should be changed to: SELECT * FROM [index] ORDER by IndexID DESC limit 0, 10; Where limit 0,10 means starting from the No. 0 record, reading 10 entries

2 Creating a view (CREATE VIEW) SQLite has a bug when creating a multi-table view with the following problem: Create View Watch_single as SELECT Distinctwatch_item. [Watchid],watch_item. [Itemid] From Watch_item; The above SQL statement will show success after execution, but actually except for SELECT COUNT (*) from [Watch_single] WHERE Watch_ single.watchid = 1; No other statements can be executed outside of the executable. The reason for this is that the name of the table in which the field is located is specified when the view is established, and SQLite does not recognize it correctly. So the above creation statement should be changed to: Create VIEW watch_single as SELECT DISTINCT [Watchid],[itemid] from Watch_item; But the question that comes up is, what if it's a multi-table view with a rename field between the tables?

3 count (DISTINCT column) SQLite will error when executing the following statement: SELECT COUNT (DISTINCT watchid) from [Watch_item] where Watch_item.watchid = 1; The reason is that all of SQLite's built-in functions do not support distinct qualification, so there is some trouble if you want to count the number of records that are not duplicated. It is more feasible to first create a view of the record table that is not duplicated, and then count the view.

4 external Connections Although SQLite has officially claimed that the left OUTER join has been implemented, there is no right OUTER join and full OUTER join. But the actual tests show that it does not seem to work properly. The following three statements are executed with an error: SELECT tags. [TagID] From [Tags],[tag_rss] WHERE tags. [TagID] = Tag_rss. [TagID] (*); SELECT tags. [TagID] From [Tags],[tag_rss] WHERE left OUTER JOIN tag_rss. [TagID] = tags. [TagID]; SELECT tags. [TagID] From [Tags],[tag_rss] WHERE the left JOIN Tag_rss. [TagID] = tags. [TagID]; It is also not feasible to use the + sign instead of the * number after testing.

Collection of syntax differences between SQLite and SQL Server

1. Returns the last inserted identity value returns the last inserted identity value for SQL Server with @ @IDENTITY SQLite is returned with a scalar function last_insert_rowid () through the current SQLConnection The row identifier (the generated primary key) to insert into the last row of the database. This value is the same as the value returned by the Sqlconnection.lastinsertrowid property.

2.top N Returns the first 2 rows in SQL Server This can be: Select top 2 * from AA ORDER BY IDs DESC

Use LIMIT in SQLite with the following statement: SELECT * from AA order by IDs DESC LIMIT 2

3.GETDATE () in SQL Server GETDATE () returns the current system date and time in SQLite no

4.EXISTS statement in SQL Server to determine insert (ids=5 is not present) if not EXISTS (SELECT * from AA where ids=5) BEGIN insert into AA (nickname) Selec T ' END in SQLite can be such insert into AA (nickname) select ' t ' where NOT EXISTS (SELECT * from AA where ids=5)

5. Nested transactions SQLite allows only a single active transaction

6.RIGHT and full OUTER join SQLite does not support right OUTER join or full OUTER join

7. Updatable views The SQLite view is read-only. You cannot execute a delete, insert, or UPDATE statement on a view, SQL Server is able to view delete, insert, or update

• Previous Article RS

SQLite Foundation and its syntax differences with SQL Server