Linux sqlite3 Basic Command _sqlite

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

View Create statements for all tables:
Sqlite>.schema

View the CREATE statement for the specified table:
Sqlite>.schema table_name

Lists the contents of the table as an SQL statement:
Sqlite>.dump table_name

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

Set display mode:
Sqlite>.mode mode_name
Example: Default to List, set to column, Other modes are available through. Help view mode related
Sqlite>.mode column

Output help:
Sqlite>.help

Set the display width for each column:
SQLite >.width width_value
Example: Set width of 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 Directive
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>6), School text DEFAULT ' xx primary school);
The statement creates a data table that records student information.

3.1 sqlite3 The type of data stored
NULL: Identifying a null value
Interger: integer type
Real: Floating-point number
TEXT: string
BLOB: Binary count

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) Set up data table
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.
For example, create a simple student information table, which contains student information such as learner number and name:
CREATE TABLE Student_info (stu_no Interger primary key, name text);

2) adding 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 that is used in the SQL statement for conditional judgment, expression for the judgment expression
For example, revise the data record of 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];
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 records
Select instruction Basic Format:
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 Descending Output data record
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 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) Establish the index
When a data table has a large number of records, indexing helps to speed up look-up tables.
CREATE INDEX index_name on table_name (field);
For example, create an index for the Student table Stu_no field:
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 data table or index
DROP TABLE table_name;
Drop index index_name;