Copyright statement: This article is Wang Xiaolei's original article, and may not be reproduced without the permission of the blogger https://blog.csdn.net/dream_an/article/details/48222443
**Note: Fedora comes with sqlite3, no need to install, just enter the command sqlite3 directly. **
———————————— Ubuntu enter sqlite3 on the command line and confirm that the installation is not in progress---
1、 Install sqlite3
Install sqlite3 under ubuntu and run the command directly in the terminal:
View version information:
——————————————
2 、 sqlite3 commonly used commands
Create or open the test.db database file in the current directory, and enter the sqlite command terminal, identified by the sqlite> prefix:
View the database file information command (note the character'.' before the command):
sqlite>.database
View the creation statement of all tables:
sqlite>.schema
View the creation statement of the specified table:
sqlite>.schema table_name
List the contents of the table in the form of sql statements:
sqlite>.dump table_name
Set the separator of the displayed information:
sqlite>.separator symble
Example: Set the display information to be separated by':'
sqlite>.separator :
Set the display mode:
sqlite>.mode mode_name
Example: The default is list, set to column, other modes can be viewed through .help to view mode related 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 configuration of the current display format:
sqlite>.show
Exit the sqlite terminal command:
sqlite>.quit
or
sqlite>.exit
3、 sqlite3 instructions
The command format of sql: all sql commands end with a semicolon (;), and two minus signs (--) indicate 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 The type of data stored in sqlite3
NULL: Identifies a NULL value
INTERGER: integer type
REAL: floating point number
TEXT: string
BLOB: Binary number
3.2 Sqlite3 storage data constraints
Sqlite commonly used constraints are as follows:
PRIMARY KEY-Primary key:
1 ) The value of the primary key must be unique, used to identify each record, such as the student ID
2 ) The primary key is also an index at the same time, it is faster to find records through the primary key
3 ) If the primary key is an integer type, the value of the column can automatically grow
NOT NULL-not empty:
The constraint column record cannot be empty, otherwise an error will be reported
UNIQUE-unique:
In addition to the primary key, the value of the data constraining other columns is unique
CHECK-condition check:
Constrain the value of the column to meet the conditions before it can be stored
DEFAULT-Default value:
The values in the column data are basically the same, such a field column can be set as the default value
3.3 sqlite3 common instructions
1 ) Create a data sheet
create table table_name(field1 type1, field2 type1, ...);
table_name is the name of the data table to be created, fieldx is the name of the field 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 ID 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 that needs to be stored in the field.
For example, add data to the student information table:
Insert into student_info(stu_no, name) values(0001, 'lei');
Note: When inserting the TEXT type, you need to add '' that is quotation marks. Otherwise error Error: no such column: lei
3 ) Modify data records
update table_name set field1=val1, field2=val2 where expression;
where is the command used for conditional judgment in sql statement, expression is the judgment expression
For example, modify the data record of student information table whose student number 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 will be cleared.
For example, delete the data record of student information table whose student number is 0001:
delete from student_info where stu_no=0001;
5 ) Query data records
Basic format of select command:
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 distinguish column data
select distinct field from table_name;
The values of some fields may appear repeatedly. Distinct removes the duplicates and lists each field value in the column individually.
6 ) Index
When the data table has a large number of records, the index helps to speed up the search data table.
create index index_name on table_name(field);
For example, create an index for the stu_no field of the student table:
create index student_index on student_table(stu_no);
After the establishment is complete, sqlite3 will automatically use the index when querying the field.
7 ) Delete the data table or index
drop table table_name;
drop index index_name;
3.4 View table structure
. table
select * from sqlite_master where type="table";
By default, the header in the red box will not appear, it needs to be set before, the command is:
. header on
select * from sqlite_master where type="table" and name="student_info";
or:
sqlite> .schema student_info
CREATE TABLE student_info( stu_no integer primary key, name text);
Recommended Posts