Sqlite3 Statement Summary

Sqlite3 long for lightweight data storage, such as single-chip computer, but now the sqlite3, has been very advanced, can not underestimate

Two

sqlite3 common commands
The current directory establishes or opens the test.db database file and enters the sqlite command terminal, identified by the sqlite> prefix:
#sqlite3 test.db

View the database file Information command ( Note the character '. ' before the command) :
Sqlite>.database

to view the creation statements for all tables:
Sqlite>.schema

to view the creation statement for the specified table:
Sqlite>.schema table_name

The table contents are listed as SQL statements:
Sqlite>.dump table_name

to set the delimiter for displaying information:
Sqlite>.separator symble
Example: Set display information to ': ' Delimited
Sqlite>.separator:

To set the display mode:
Sqlite>.mode Mode_name
Example: default is list, set to column, other modes can be viewed through . Help Mode Related content
sqlite>.mode Column

output Help information:
Sqlite>.help

set the display width for each column:
sqlite>.width Width_value
Example: set width to 2
Sqlite>.width 2

lists the configurations for the current display format:
Sqlite>.show

To exit the sqlite terminal command:
Sqlite>.quit
or
Sqlite>.exit

3.sqlite3 directive
SQL Instruction format: All SQL instructions are represented by a semicolon (;) end, and two minus signs (--) are the comments.
such as:
sqlite>create studen_table (stu_no interger PRIMARY KEY, Name text not NULL, Id Interger UNIQUE, age Interger CHECK ( AGE>6), School text DEFAULT ' xx Primary school );
This statement creates a data table that records student information.

3.1 sqlite3 Types of data stored
null: Identifies a null value
interger: integer type
REAL: floating point
TEXT: String
BLOB: binary number

3.2 sqlite3 The constraints of storing data
the usual constraints for Sqlite are as follows:
PRIMARY Key- primary key:
1) The value of the primary key must be unique to identify each record, such as student's number
2) Primary key is also an index, the primary key to find records faster
3) If the primary key is an integer type, the value of the column can grow automatically
NOT null- non-null:
constraint column record cannot be empty, otherwise an error
Unique - single:
The value of the data that constrains the other columns is unique except for the primary key
Check - condition checking:
the value that constrains the column must meet the criteria before it can be deposited
Default- defaults:
The values in the column data are basically the same, so the field column can be set to the default value

3.3 sqlite3 Common directives
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, andTypex 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 records of the Student Information table number to 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 0001 number:
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;

Sqlite3 Statement Summary