SQLite basic Syntax

SQLite, is a lightweight database, is to comply with acid-related database management system, its design goals are embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory enough.

It can support Windows/linux/unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl,C #,PHP,Java, and ODBC interface, the same compared to Mysql,PostgreSQL The two open-source world-famous database management system, it processing speed faster than they.

SQLite Common Instructions

1) Set up a data sheet
CREATE TABLE table_name (field1 type1, Field2 type1, ...);
TABLE_NAME is to create a data table name, FIELDX is the field name in the datasheet, and Typex is the field type.
example, create a simple student information table that contains student information such as number and name:
CREATE TABLE Student_info (stu_no Interger primary key, name text);

The CREATE table if not EXISTS table name (field name 1, field name 2 ...);

2) adding data records
INSERT INTO table_name (field1, field2, ...) VALUES (Val1, val2, ...);
Valx is the value that needs to be stored in the field.
example, add data to the Student information table:
Insert into Student_info (stu_no, name) VALUES (0001, Alex);

3) Modify the data record
UPDATE table_name set FIELD1=VAL1, field2=val2 where expression;
Where is the command used in the SQL statement for conditional judgment, and expression is the judge-
example, modify the data record for student information table number 0001:
Update Student_info set stu_no=0001, name=hence where stu_no=0001;

4) Delete data records
Delete from table_name [where expression];
Clears all data records of the table without judging the condition.
example, delete the data record of Student information table number 0001:
Delete from Student_info where stu_no=0001;

5) Querying data records
Select instruction Basic Format:
Select columns from table_name [where expression];
A query outputs all data records
SELECT * FROM table_name;
b limit the number of output data records
SELECT * FROM table_name limit Val;
C Output data record in ascending order
SELECT * FROM table_name order BY field ASC;
D Descending Output data record
SELECT * FROM table_name order BY field DESC;
E conditional Query
SELECT * from table_name where expression;
SELECT * FROM table_name where field in (' Val1 ', ' val2 ', ' val3 ');
SELECT * FROM table_name where field between Val1 and Val2;
F Number of query records
Select COUNT (*) from TABLE_NAME;
G Distinguishing Column data
SELECT DISTINCT field from table_name;
Some of the values of the fields may recur, distinct remove duplicates, and each field value in the column is listed individually.

6) Building the index
When you say that a data table has a large number of records, indexing helps speed up finding data tables.
CREATE INDEX index_name on table_name (field);
example, for the Student Table Stu_no field, create an index:
Create INDEX Student_index on student_table (STU_NO);
When Setup is complete, Sqlite3 automatically uses the index when it queries the field.

7) Delete data table or index
DROP TABLE table_name;
Drop index index_name;

SQLite basic Syntax