Use the basic SQLite3 command and the unzip tusqlite3 command in ubuntu

Use the basic SQLite3 command and the unzip tusqlite3 command in ubuntu

System Platform: ubuntu10.04

Introduction
Sqlite3 is mainly used for Embedded lightweight databases. This article aims to provide technical documents for familiar with sqlite3 basic commands.
Note: All operations in this article are performed under the root user.

1. Install sqlite3
Install sqlite3 in ubuntu and run the following command directly on the terminal:

#apt-get install sqlite3

View version information:

#sqlite3 -version

 
2. Common sqlite3 commands
Create or open the test. db database file in the current directory, and enter the sqlite command terminal, marked with sqlite> Prefix:

#sqlite3 test.db

 
Command for viewing database file information (note that the command is prefixed with the character '.'):

sqlite>.database

View creation statements for all tables:

sqlite>.schema

 
View the statement for creating a specified table:

sqlite>.schema table_name

 

List the table contents in the form of SQL statements:
sqlite>.dump table_name
Set the separator for display information:
sqlite>.separator symble
Example: set display information to be separated by ':'
sqlite>.separator :
Set display mode:
sqlite>.mode mode_name
Example: the default value is list, which is set to column. Other modes can be viewed through. Help
sqlite>.mode column
Output help information:
Sqlite>.help
Set the display width of each column:
sqlite>.width width_value
Example: set width to 2
sqlite>.width 2
Lists the configuration of the current display format:
Sqlite>.show
Exit SQLite terminal command:
Sqlite>.quit
or
Sqlite>.exit

 
3. sqlite3 commands
SQL command format: All SQL commands end with a semicolon (;), and two minus signs (--) indicate comments.
For example:

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 data storage type
NULL: identifies a NULL value.
INTERGER: Integer type
REAL: Floating Point Number
TEXT: String
BLOB: Binary Number

3.2 constraints on sqlite3 Data Storage
Sqlite has the following constraints:
Primary key-primary key:
1) The value of the primary key must be unique and used to identify each record, such as the student's student ID.
2) The primary key is also an index, and it is faster to query records through the primary key.
3) if the primary key is of the integer type, the value of this column can automatically increase
Not null-not null:
The constraint column record cannot be blank; otherwise, an error is returned.
UNIQUE-UNIQUE:
Except the primary key, the data values of other columns are restricted to be unique.
CHECK-condition CHECK:
The value of this column must meet the conditions before it can be saved.
DEFAULT-DEFAULT value:
The values in column data are basically the same. Such field columns can be set as default values.

3.3 sqlite3 Common commands

1) Create data table
create table table_name(field1 type1, field2 type1, ...);
Table name is the name of the data table to be created, fieldx is the field name in the data table, and typex is the field type.
For example, create a simple student information table, which contains student information such as student number and name:
create table student_info(stu_no interger primary key, name text);
2) Add data record
insert into table_name(field1, field2, ...) values(val1, val2, ...);
Valx is the value of the field to be stored.
For example, add data to the student information table:
Insert into student_info(stu_no, name) values(0001, alex);
3) Modify data record
update table_name set field1=val1, field2=val2 where expression;
Where is the command used for condition judgment in SQL statements, and expression is the judgment expression
For example, modify the data record with student No. 0001 in student information table:
update student_info set stu_no=0001, name=hence where stu_no=0001;
4) Delete data record
delete from table_name [where expression];
All data records in the table will be cleared without judgment conditions.
For example, delete the data record with student No. 0001 in student information table:
delete from student_info where stu_no=0001;
5) Query data record
Basic format of select instruction:
select columns from table_name [where expression];
A query output all data records
select * from table_name;
B limit the number of output data records
select * from table_name limit val;
C ascending output data record
select * from table_name order by field asc;
D output data records in descending order
select * from table_name order by field desc;
E condition 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;
Area G data
select distinct field from table_name;
Some field values may appear repeatedly. Distinct removes duplicates and lists each field value in the column individually.
6) Indexing
When there are a large number of records in a data table, an index helps speed up data table lookup.
create index index_name on table_name(field);
For example, for the stu no field in the student table, create an index:
create index student_index on student_table(stu_no);
After the establishment, SQLite3 will automatically use the index when querying the field.
7) Drop data table or index
drop table table_name;
drop index index_name; 

References:
Http://www.sqlite.com.cn/MySqlite/4/378.Html

How to Use sqlite3 in linux

For ubuntu, you can use sudo apt-get install sqlite3 to install
 
How does the sqlite3 command open a created database?

Or sqlite3 foo. db.