[Database] Summary of differences between SQLite and SQL

Editor's note: Have you ever encountered any of these questions about the difference between SQLite and SQL? I have summarized some of the differences that often occur between SQLite and SQL. I hope this will be helpful to you. I. Summary of common questions 1TOP is a frequently asked question. For example, you can use the following syntax in SQLSERVER:

Editor's note: Have you ever encountered any of these questions about the difference between SQLite and SQL? I have summarized some of the differences that often occur between SQLite and SQL. I hope this will be helpful to you. I. Summary of FAQ 1 TOP: This is a frequently asked question. For example, you can use the following syntax in SQLSERVER:

Editor's note: Have you ever encountered any of these questions about the difference between SQLite and SQL? I have summarized some of the differences that often occur between SQLite and SQL. I hope this will be helpful to you.

I. Summary of Common Problems

1 TOP

This is a frequently asked question. For example, you can use the following statement in SQLSERVER to obtain the first 10 records in the record set:

 
 

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

However, this SQL statement cannot be executed in SQLite. It should be changed:

 
 

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

Here, limit 0th indicates that 10 records are read from records.

2. Create View)

SQLite has a BUG when creating a multi-Table view. The problem is as follows:

 
 

  
  
CREATE VIEW watch_single AS SELECT DISTINCTwatch_item.[watchid],watch_item.[itemid] FROM watch_item; 
 
 

After the preceding SQL statement is executed, it is displayed as successful.

 
 

  
  
SELECT COUNT(*) FROM [watch_single ] WHERE watch_ single.watchid = 1; 
 
 

Other statements cannot be executed. The reason is that the table name of the field is specified when the view is created, and SQLite cannot identify it correctly. Therefore, the above statement should be changed:

 
 

  
  
CREATE VIEW watch_single AS SELECT DISTINCT [watchid],[itemid] FROM watch_item; 
 
 

However, what should I do if a multi-Table view has a duplicate Name field between tables?

3 COUNT (DISTINCT column)

SQLite reports an error when executing 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.

4 external connections

Although SQLite officially claims that left outer join has been implemented, there is no right outer join or full outer join. However, the actual test shows that it does not seem to work properly. When executing the following three statements, an error is reported:

 
 

  
  
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 LEFT JOIN tag_rss.[tagid] = tags.[tagid]; 
 
 

In addition, it is not feasible to replace "*" with "+.

2. Collect the syntax differences between SQLite and SQL Server

1. Return the last inserted id value

Returns the last inserted id value. @ IDENTITY is used by SQL server.

Sqlite uses the scalar function LAST_INSERT_ROWID ()

Returns the row identifier (generated primary key) of the last row inserted to the database through the current SQLConnection ). This value is the same as the value returned by the SQLConnection. lastInsertRowID attribute.

2. top n

In SQL server, the first two rows can be returned as follows:

 
 

  
  
select top 2 * from aa 
  
  
order by ids desc 
 
 

Use LIMIT in sqlite. The statement is as follows:

 
 

  
  
select * from aa 
  
  
order by ids desc 
  
  
LIMIT 2 
 
 

3. GETDATE ()

GETDATE () in SQL server Returns the current system date and time

Not in sqlite

4. EXISTS statement

Insert data in SQL server (insert data if ids = 5 does not exist)

 
 

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

This can be done in sqlite.

 
 

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

5. nested transactions

Sqlite only allows 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 DELETE, INSERT, or UPDATE statements on a view. SQL server can DELETE, INSERT, or UPDATE a view.

3. New Content

1. default settings for date and time columns:

The "column" setting includes three fields: Name, Type, and Default.

Name:LogTime (random name );

Type:

Date type. The resulting value is like ",

DATETIME type. The resulting value is like "11:49:04. 000 ";

Default:Datetime ('now ', 'localtime') uses two parameters. Do not discard the subsequent localtime. Otherwise, the time is inaccurate.