Difference between SQL and other databases in Sqlite, sqlitesql

Difference between SQL and other databases in Sqlite, sqlitesql

Sqlite is widely used as a mainstream database in Android, but its SQL language is different from the SQL language used by general large databases. This article summarizes the following:

1. TOP

In SQL Server, TOP is used to obtain the first N data records:

SELECT TOP 10 * FROM [index] ORDER BY indexid DESC;

But in Sqlite, we will find that this is not feasible. We need to write this:

SELECT * FROM [index] ORDER BY indexid DESC limit 0,10;

Use limit to implement the TOP function.

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 built-in functions of SQLite do not support DISTINCT limitation. Therefore, it is troublesome to count the number of records that are not repeated. It is feasible to create a view of the record table that does not repeat, and then count the view. Or when inserting data, you need to use the primary key to determine.

3. Sqlite cannot use external connection

Please advise if you have any solutions ....

4. EXISTS statement

SQL Server writes:

IF NOT EXISTS (select * from aa where ids=5) BEGIN insert into aa(nickname) select 't' END 

Sqlite writes:

insert into aa(nickname) select 't' where not exists(select * from aa where ids=5)

If you have something new, please leave a message to add it.

Above.

What is the difference between sqlite and sqlyog? Which one is better?

Hello, Questioner:
SQLite is a database, and currently the most widely used is android development.
SQLyog is a visual MySQL database management interface.
MySQL is a database and a small and easy-to-use database. It is essential for beginners.
Sun Cheng [authoritative expert]

What is the difference between sqlite3 and mysql databases?

The simplest difference: SQLite3 is a single-host data that cannot be used in the network. It is generally used to make desktop single-host small programs, such as small dictionaries.
MYsql is mostly used for creating web pages.