Sqllite Common Statements

SQlite (structrue Query Language, structured querying language) is a lightweight database that is used primarily for mobile devices. Weakly typed database (although there is no error in writing a segment, but for better porting to an enterprise database it is recommended to write) it is a relational database. (The object's property value is stored), the latest database: Object-type Database (objects that can be stored in a database) Download official website: http://sqlite.org/download.html

Sqllite Advantages:

1) Very small (database compression package, more than 200 k)

2) more powerful (with enterprise-class database capabilities)

3) Self-contained (in some software products integrated (embedded) the database,

such as: Android,adobe pdf)

4) No connection (database on-premises)

5) If the type of database (can not specify the type of field, its field values can be arbitrarily specified)

Summarized as:

Self-contained, server-free, 0-config, transactional SQL engine

SQLite is divided into two types, commands and statements, the command is the properties of SQLite itself, such as open the database, view the table structure, etc., statements are additions and deletions to change.

Sqlit command: (There will be "." in the front.) ）

. Help Display assistance information (which commands can be used)
. databases shows which database files are in the current directory
. Open database file (created if not present)
. read SQL script batch (execute) SQL statement
. Tables: View all tables under this database
. Schema table name: View table structure

SQL script: The suffix is a. sql file that is written in the file
A series of SQL statements.

Sqllite statements: 1. Create a table (you must first create a database)

CREATE TABLE table name (field list);
Field List:
field type, field type

CREATE table student (ID integer primary key autoincrement, name varchar (), age integer);

Primary key: Primary key, which distinguishes each record in the table
AutoIncrement: Automatic growth

2. Modify the table

Modify Table Name

ALTER table Old table name  RENAME to new table name

Add a column

ALTER TABLE name add Field

ALTER TABLE student add sex varchar (5);

3. Delete a tabledrop TABLE StudentInsert statement:

fields correspond to values

INSERT into student (Name,age,sex) VALUES (' Zhangsan ', ' Male ');

Query statement:

Select field name from table where query condition

Search All

SELECT * from student;

Querying multiple fields, separated by commas

Select ID, name from student;

Student information for inquiry number ID1

SELECT * from student where id=1;

To modify a statement:

Update table name Set field = value, field = value where condition
Change the age of all students in the table to 19, gender change to female

Update student set age=19, sex= ' female '

Delete statement:

Delete from table where condition

Delete from student where id=1;

The query is also divided into exact query and fuzzy query, exact query keyword between where comparison operator (> < =), fuzzy query is _ (for any one character)% (represents 0 or more characters) like

Exact query

Search for students aged 20

SELECT * from student where age=20;

Check gender for male students

where sex= ' male ';

Check for students older than 20

where Age > 20

Query for female students older than 20

Where Age > sex= ' female ';

Query age greater than or equal to 20 and the =22 student

Where age >=20 and age<=22where age between and 22

Check with a student aged 20 or age 21

where Age=20 or Age=21where in (20,21) select *from student where sex in (' Male ', ' female ');

Enquiries for female students aged 20 and male students aged 21

Where (age=20 and sex= ' female ') or (age=21 and sex= ' male ')

Fuzzy query

Find a student surnamed Zhang

Where name like ' zhang% '

Query a student whose name is the second character of a

<span style= "White-space:pre" ></span>where name like ' _a% ';

Query the student whose name contains a

Where name like '%a% '

Find out where there is a similar "Mer" in the native place, and the gender is "FE" user information.

SELECT * from the user where (address like '%mer% ') and (gender= ' Fe ');

Aggregation Functions

COUNT (* or a field): Number of records in the table
Max (field): Find the maximum value of the specified field in all records in the table
Min (field): Find the minimum value of the specified field in all records in the table
AVG (field): Find the average of the specified field in all records in the table

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Sqllite Common Statements