SQLite Common SQL statements

Source: Internet
Author: User
Tags sqlite uppercase character

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

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.