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

This article mainly introduces what are the common problems in the differences between SQLite and SQL, which can be used for reference by interested friends. I hope you will gain a lot after reading this article.

Summary of frequently asked questions

1 TOP

This is a question that people often ask. For example, in SQLSERVER, you can use the following statement to get the first ten records in the recordset:

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

However, this SQL statement cannot be executed in SQLite and should be changed to:

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

Among them, limit 0J 10 means to start with the 0 record, and then read a total of 10 entries.

2 create a view (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

The above SQL statement will show success after execution, but in fact, except

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

No statement can be executed except that it can be executed. The reason for this is that the table name of the field is specified when the view is created, 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 arises is what if it is a multi-table view and there are duplicate fields between the tables.

3 COUNT (DISTINCT column)

SQLite will report an error when executing the following statement:

SELECT COUNT (DISTINCT watchid) FROM [watch_item] WHERE watch_item.watchid = 1

The reason for this is that all the built-in functions of SQLite do not support DISTINCT qualification, so there will be some trouble if you want to count the number of records that do not repeat. It is more feasible to create a view of a record table that does not repeat, and then count the view.

4 external connection

Although SQLite officially claims that LEFT OUTER JOIN has been implemented, there is no RIGHT OUTER JOIN or FULL OUTER JOIN. But the actual test shows that it doesn't seem to work properly. The following three statements will report an error when executed:

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 use the + sign instead of the * sign.

Collect the grammatical differences between SQLite and Sql Server

1. Returns the last inserted identity value

Returns the last inserted identity value sql server with @ @ IDENTITY

Sqlite uses the scalar function LAST_INSERT_ROWID ()

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

2.top n

To return the first two rows in sql server, you can do this:

Select top 2 * from aa order by ids desc

LIMIT is used in sqlite with the following statement:

Select * from aa order by ids desc LIMIT 2

3.GETDATE ()

GETDATE () returns the current system date and time in sql server

Not in sqlite

4.EXISTS statement

Determine insertion in sql server (insert if there is no ids=5)

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

You can do this in sqlite.

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

5. Nested transaction

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 view

The sqlite view is read-only. You cannot execute DELETE, INSERT, or UPDATE statements against a view. Sql server can be used against a view DELETE, INSERT, or UPDATE

III. New content

1. Default settings for date-time type columns:

The setting of the column includes three fields: Name, Type, and Default

Name:logTime (named at will)

Type:

Date type, the resulting value is like "2011-04-23"

DATETIME type, the resulting value is like "2011-04-23 11-49-49-04.000"

Default:datetime ('now','localtime') I use two parameters, do not discard the following localtime, otherwise the time is not accurate.

Thank you for reading this article carefully. I hope the article "what are the common questions about the differences between SQLite and SQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.