The difference between using SQL and other databases in SQLite

SQLite is widely used as a mainstream database in Android, but there are some differences between his SQL language and the SQL language used in general large databases, as summarized in this article:

1. TOP

In SQL Server, we use top to get the top N data:

SELECT TOP Ten * from [index] ORDER by IndexID DESC;

But in SQLite, people will find that this is not possible, we need to write:

SELECT * FROM [index] ORDER by IndexID DESC limit 0, 10;

Use limit to achieve top functionality.

2. COUNT (DISTINCT column)

SQLite cannot execute 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. or when inserting data, you need to use the primary key to judge.

3, SQLite can not use the external connection

There is a way to solve the problem, please advise ....

4. Exists statement

This is written in SQL Server:

This is what SQLite writes:

INSERT into AA (nickname) select ' t ' where isn't exists (SELECT * from AA where ids=5)

If there is something new, welcome you to add a message.

Above.

The difference between using SQL in SQLite and other databases