Linux 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 table content in the form of SQL statements:
Sqlite>. dump table_name

Set the delimiter for display information:
Sqlite>. separator symble
Example: Set the display information to be separated ':'
Sqlite>. separator:

Set the display mode:
Sqlite>. mode mode_name
Example: The default value is list and column. For other modes, you can use. help to view the mode content.
Sqlite>. mode column

Output help information:
Sqlite>. help

Set the display width of each column:
Sqlite>. width width_value
Example: Set the width to 2.
Sqlite>. width 2

List the configurations 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 a 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 that contains student information such as student ID and name:
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 of the field to be saved.
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 for condition judgment in SQL statements. expression is the judgment expression.
For example, modify the data record of the student information table whose student ID is 0001:
Update student_info set stu_no = 0001, name = hence where stu_no = 0001;

4) delete data records
Delete from table_name [where expression];
If no judgment condition is added, all data records in the table are cleared.
For example, delete the data record with the student information table student ID 0001:
Deleted from student_info where stu_no = 0001;

5) query data records
Basic select command format:
Select columns from table_name [where expression];
A. query and output all data records
Select * from table_name;
B. Limit the number of output data records
Select * from table_name limit val;
C output data records in ascending order
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 query the number of records
Select count (*) from table_name;
G-partition data
Select distinct field from table_name;
Some field values may be repeated. distinct removes the repeated items and lists each field value in the column.

6) Create an index
When there are a large number of records in the data table, the index helps to speed up data table searching.
Create index index_name on table_name (field );
For example, create an index for the stu_no field in the student table:
Create index student_index on student_table (stu_no );
After the index is created, sqlite3 automatically uses the index when querying this field.

7) delete a data table or index
Drop table table_name;
Drop index index_name;