Use of sqlite basic SQL statements

Sqlite basic SQL statement 1. SQLite common data types SQLite are non-existent. This means that you can save any type of data to any column in any table you want to save, regardless of the Data Type declared in this column (only auto-incrementing IntegerPrimaryKey is useful ). For SQLite, the field does not specify the type.

Sqlite basic SQL statement 1. SQLite common data types SQLite are non-existent. This means that you can save any type of data to any column in any table you want to save, regardless of the Data Type declared in this column (only the Integer Primary Key is automatically incremented ). For SQLite, the field does not specify the type.

Use of sqlite basic SQL statements

I. Common SQLite Data Types

SQLite is non-typed. This means that you can save any type of data to any column in any table you want to save, regardless of the Data Type declared in this column (only the Integer Primary Key is automatically incremented ). For SQLite, it is completely valid for fields without specifying the type. We recommend that you specify the data type in your Create Table statement even if SQLite allows data types to be ignored. Because the data type is used by you and other programmers

Stream, or you are preparing to replace your database engine. SQLite only supports five common storage classes,

NULL

INTEGER -- INTEGER

REAL -- floating point number

TEXT -- TEXT

BLOB-large binary object

The data types defined below are all transferred to the corresponding storage class.

Create table tab (-- note the Comment Method

A VARCHAR (10), -- a string with an unfixed length and a maximum length of n

B NVARCHAR (15 ),

C TEXT, -- binary object

D INTEGER, -- a signed INTEGER, depending on the size of the number to be saved

E FLOAT,

F BOOLEAN,

G CLOB, -- use CHAR to save data

H BLOB, -- use a binary object to save data, such as a saved bitmap

I TIMESTAMP,

J NUMBERIC (10, 5 ),

K varying character (24 ),

L national varying character (16 ),//

J REAL -- floating point number, which is stored as an 8-byte IEEE floating point number

);

2. Basic data operations

1. Create a table

Create table admin (

Username text,

Age integer );

2. Insert data

Insert into Table Name (Field List) values (Value List );

Example: insert into admin values ('song', 25 );

3. Query

Select field name from table name;

Select * from admin;

Select distinct field from table_name; (distinct removes duplicate items and lists the values of each field in the column)

4. delete data

Delete from table name where Condition Clause.

Delete from admin form where username = 'song ';

5. Modify

Update table name set field name = value where Condition Clause.

Update admin set username = 'zhang', age = 24 where username = 'songg' and age = 25;

6. Grouping by conditions

Select * from table name where Condition Clause group by grouping clause having... Order by clause

For example:

Select * from admin;

Select * from admin order by id desc (descending) | asc (ascending );

Select username from admin group by username having count (*)> 1;

7. Multi-condition query statement

Select field name from table name where Clause 1 by clause 2

Select * from admin where username = 'hangzhou' and age = 24;

Select * from table_name where field in ('val1', 'val2', 'val3 ');

Select * from table_name where field between val1 and val2;

Select * from admin limit 5; -- limit the number of output data records

8. Multi-condition sorting

Select field name from table name order by field 1 (desc), Field 2 (desc );

Select * from admin order by t1, t2 desc;

9. Index

For example, create index idxT1 on admin (username, age );

Create index idxUsername on admin (username );

Create index idxAge on admin (age );

10. usage of the foreign key (UNIQUE | primary key | not null ()

Create table (

A1 integer primary key | UNIQUE | not null,

A2 TEXT,

A3 INTEGER );

Create table B ()(

B1 INTEGER,

B2 TEXT,

B3 INTEGER,

Foreign key (b3) references a (a1 ));

11, paging

Select * from account limit 5 offset 3;

Or select * from account limit 5, 3;

12. Fuzzy search

SELECT field FROM table WHERE a field LIKE Condition

(1) %: represents any 0 or multiple characters

(2) _: represents any single character and matches a single character. It is often used to limit the character length of an expression.

(3) []: represents one of the characters listed in parentheses (similar to a regular expression)

Select * from admin where username like '[Zhang Li Wang] 3 ';

It indicates that the search is "Zhang San", "Li San", or "Wang San"

[4]: [^] indicates a single character not listed in parentheses.

[5]: When the query content contains wildcards, use.

13. delete a table | Index

Drop table [if exists] admin;

Drop index index_name

14. query the number of records

Select count (*) from table_name;