The difference between SQLite and SQL top and limit makes me confused.

Source: Internet
Author: User
Tags scalar

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:
[Pre]

    Select top10 * From[ Index] OrderIndexid Desc;

[/PRE] but this SQL statement cannot be executed in SQLite. It should be changed:
[Pre]

    Select* From[ Index] OrderIndexid DescLimit
    0, 10;

[/PRE] 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:
[Pre]

    Create ViewWatch_single As selectDistinctwatch_item. [watchid], watch_item. [Itemid] FromWatch_item;

[/PRE] After the preceding SQL statement is executed, it is displayed as successful,
[Pre]

    SelectCount (*) From[Watch_single] WhereWatch _
    Single. watchid = 1;

[/PRE] 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:
[Pre]

    Create ViewWatch_single As select distinct[Watchid], [Itemid] FromWatch_item;

[/PRE] But the question that arises is what 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:
[Pre]

    SelectCount ( DistinctWatchid) From[Watch_item] WhereWatch_item.watchid
    = 1;

[/PRE] the reason is that all built-in functions of SQLite do not support distinct limitation, so it may be difficult 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:
[Pre]

    SelectTags. [tagid] From[Tags], [tag_rss] WhereTags. [tagid] = tag_rss. [tagid] (*);

  1. SelectTags. [tagid]From[Tags], [tag_rss]WhereLeft Outer Join tag_rss. [tagid]
    = Tags. [tagid];SelectTags. [tagid]From[Tags], [tag_rss]WhereLeft join tag_rss. [tagid]
    = Tags. [tagid];

[/PRE] 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:
[Pre]

    Select top2 * FromAA

  1. OrderIDSDesc

[/PRE] limit is used in SQLite. The statement is as follows:
[Pre]

    Select* FromAA

  1. OrderIDSDescLimit 2

[/PRE] 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)
[Pre]

    If not exists ( Select* FromAA WhereIDS = 5)

  1. Begin insertAA (nickname)
  2. Select'T'End 

[/PRE] this can be done in SQLite
[Pre]

    InsertAA (nickname)

  1. Select'T'WhereNot exists (Select*FromAAWhereIDS = 5)

[/PRE]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.

 

 

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.

 

SQLite queries all tables in the database

From within a C/C ++ Program (or a script using Tcl/Ruby/perl/Python bindings) you can get access to table and index names by doing a select on a special table named "sqlite_master ". every SQLite database has an sqlite_master table that defines the schema
For the database.

SQL code
Select name from sqlite_master
Where type = 'table'
Order by name;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.