Some sqlite tricks

The SQLite self-increment ID is used by the INTEGER PRIMARY KEY autoincrement such as:

CREATE TABLE 21andy (id INTEGER PRIMARY KEY autoincrement, 21andy VARCHAR (+ ) not NULL , date date);

Attention is AutoIncrement, and MySQL is not the same as the wording

SQLite Empty Table SQLite does not have a truncate empty table command, so this only

DELETE from 21andy;

SQLite Delete record, self-increment ID 0

Only this way.

DELETE from sqlite_sequence;

In addition, this sqlite_sequence can be crud

when SQLite deletes a record, it does not release space that must be like this

VACUUM

SQLite paging queries are written in two ways:

Select * from account Limit 9 Offset ten ; SELECT * from account LIMIT , 9

Both of them have the same effect, and the first one is clearer, skipping 10 rows and reading the next 9 rows of data.
SQLite Batch Insert Data Unfortunately, it seems that SQLite can only be inserted in one line, but this is very very slow behavior, the execution of one is to perform a write disk operation, this is really terrible. Perform bulk inserts in SQLite, only insert operations into the Service. Examples are as follows:

BEGIN;CREATE TABLET2 (A INTEGER, b INTEGER, C VARCHAR(100));INSERTIntoT2VALUES(1, 59672,‘Fifty nine thousand six hundred seventy‘);INSERTIntoT2VALUES(24999 eighty nine thousand five hundred sixty nine ); insert   into Span style= "color: #000000;" > t2  values (25000 ninety four thousand six hundred sixty six "); commit ; 

In SQLite, a series of multiple write operations, it is recommended to put into the transaction, the optimization of performance improvements can be clearly felt. The difference between the use and the unused is very large.
SQLite Data does not exist insert, there is an update SQLite SQL Syntax class MySQL, in SQLite has a keyword REPLACE, you can use it to achieve the purpose:

REPLACE into [ table ] (Row1, row2) VALUES (2 , 3 );

If the table has a primary key, then when the primary key value is equal, the row data does not exist to perform the insert, and the update operation is performed. But if there is no primary key, then it will always execute the insert operation.
SQLite uses the IsNull function SQLite SQL syntax class is different from MySQL, can not use IsNull, but can use ifnull directly instead

ifnull (null,0)

Some sqlite tricks