SQLite Common SQL statements

Create a table

Sql= "CREATE TABLE IF Not exists MusicList (id integer primary  key Autoincrement,name varchar ( 20), path  varchar ( 20 ";      

Enquiry Form

View table Structure
Desc <table>;

View All data
SELECT * FROM <table>;

View the specified column data
Select, from <table>;

Viewing non-repeating data
Select DISTINCT, from <table>;

Copying data
Insert into Users (_id,username,password) select * from users;

Data for the first letter S
Select username from the users where username like ' s% ';

Data for the third letter S
Select username from the users where username like ' __s% ';

Querying 001,220,230 of the data
SELECT * from the users where _id in (001,220,230);

Sequential query
SELECT * from the users where _id in (001,220,230);

Reverse order Query
SELECT * from the user order by _id Desc;

Paging function

Get the total number of data rows
Select COUNT (Word) as number from <table>;

Paging Query
Select, from <table> order by word limit 200 offset;

SQLite built-in function table

Arithmetic functions

• ABS (X) returns the absolute value of the given numeric expression.
• Max (x,y[,...]) returns the maximum value of an expression.
• Min (x,y[,...]) returns the minimum value of an expression.
• Random (*) returns the number.
• Round (X[,y]) Returns a numeric expression that is rounded to the specified length or precision.

Character processing functions

• Length (X) returns the number of characters for the given string expression.
• Lower (X) returns a character expression after converting the uppercase character data to lowercase characters.
• Upper (X) returns the character expression that converts the lowercase character data to uppercase.
• substr (x, Y, z) returns part of an expression.
• Randstr ()
• QUOTE (A)
• A like (A, B) determines whether the given string matches the specified pattern.
• Glob (A, B)

Conditional Judgment function

• COALESCE (x,y[,...])
• Ifnull (x, y)
• Nullif (x, y)

Aggregate functions

• AVG (X) returns the average of the values in the group.
• Count (X) returns the number of items in the group.
• Max (X) returns the maximum value of the value in the group.
• Min (X) returns the minimum value of the value in the group.
• SUM (X) returns the sum of all values in an expression.

Other functions

• typeof (X) returns the type of data.
• Last_insert_rowid () returns the ID of the last inserted data.
• Sqlite_version (*) returns the version of SQLite.
• Change_count () returns the number of rows affected by the previous statement.
• Last_statement_change_count ()

SQLite Common SQL statements