Basic commands for using SQLite3 under Ubuntu _sqlite

System platform: ubuntu10.04

Brief introduction
Sqlite3 a lightweight database for embedded purposes, this article aims to provide technical documentation for familiar Sqlite3 basic commands.
Note: All operations in this article are performed under Root.

1. Installation Sqlite3
Ubuntu install sqlite3 directly in the terminal to run the command:

#apt-get Install Sqlite3

To view version information:

#sqlite3-version

2, sqlite3 Common command
Create or open Test.db database files in the current directory, and enter the SQLite command terminal, identified by the sqlite> prefix:

#sqlite3 test.db

View database file Information commands (note the character '. ' Before the command) ：

Sqlite>.database

To view the creation statements for all tables:

Sqlite>.schema

To view the CREATE statement for a specified table:

Sqlite>.schema table_name

Lists the contents of the table as an SQL statement:
sqlite>.dump table_name
 
Set the separator for displaying information:
sqlite>.separator symble
Example: Set display information to ': ' Separate
sqlite>.separator:
 
set display mode:
sqlite>.mode mode_name
Example: Default is List, Set to column, other modes are available through. Help view mode related content
sqlite>.mode column
 
output assistance:
sqlite>.help
 
Sets the display width for each column:
sqlite>.width Width_value
Example: Set width to 2
sqlite>.width 2
 
lists the configuration for the current display format:
sqlite>.show
 
exit SQLite terminal command:
sqlite >.quit
or
sqlite>.exit

3, Sqlite3 instructions
SQL instruction format: All SQL instructions are annotated with a semicolon (;) end, two minus signs (-).
Such as:

Sqlite>create studen_table (stu_no interger PRIMARY KEY, Name text not NULL, Id Interger UNIQUE, age Interger CHECK (age& GT;6), School text DEFAULT ' xx primary school);

This statement creates a data table that records student information.

3.1 Sqlite3 The type of data stored
Null: Identifies a null value
Interger: Integer type
Real: Floating point numbers
TEXT: String
BLOB: Binary number

3.2 sqlite3 Storage data constraint conditions
SQLite commonly used constraint conditions are as follows:
PRIMARY Key-PRIMARY key:
1 The value of the primary key must be unique to identify each record, such as the student's school number
2 primary key is also an index, through 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-empty:
The constraint column record cannot be empty, otherwise the error
Unique-Unique:
In addition to primary keys, the values of data that constrain other columns are unique
Check-condition Check:
Constraint the value of the column must meet the criteria to deposit
Defaults-Default value:
The values in the column data are basically the same, so that the field columns can be set to the default values

3.3 Sqlite3 Common Instructions

1 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, the establishment of a simple student information table, which contains student numbers and names and other students information: CREATE TABLE Student_info (stu_no Interger primary key, name text);
2 Add data records INSERT INTO table_name (field1, field2, ...) VALUES (Val1, val2, ...);
Valx is the value you want to deposit into the field.
 
For example, add data to the Student information table: Insert into Student_info (stu_no, name) VALUES (0001, Alex);
3 Modify data records update table_name set FIELD1=VAL1, field2=val2 where expression; Where is the command used in the SQL statement for conditional judgment, expression for the judgment expression example, modify the data record of the Student Information table number 0001: Update student_info set stu_no=0001, name=hence where
 
stu_no=0001;
4 Delete the data record delete from table_name [where expression];
Empty all data records of the table without a judgment condition.
 
For example, delete data records with Student information table number 0001: Delete from student_info where stu_no=0001;
5 Query data Record SELECT command basic format: SELECT columns from table_name [where expression];
A query output all data records SELECT * FROM table_name;
b Limit Output data record number SELECT * FROM table_name limit Val;
C Ascending Output data record SELECT * FROM table_name order BY field ASC;
D Descending Output data record SELECT * FROM table_name order BY field DESC; Article EQuery 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 Query Record number SELECT COUNT (*) from TABLE_NAME;
G-Differentiate Column data select DISTINCT field from table_name;
 
The values of some fields may recur, distinct remove duplicates, and list individual field values in the column.
6 indexing when the data table has a large number of records, the index helps speed up the lookup table speed.
CREATE INDEX index_name on table_name (field);
For the Student Table Stu_no field, an index is established: CREATE INDEX Student_index on student_table (STU_NO);
 
When the build is complete, Sqlite3 automatically uses the index when it queries the field.
7 Delete datasheet or index drop table table_name;
 Drop index index_name;

Resources:
http://www.sqlite.com.cn/MySqlite/4/378.Html