ubuntuでSQLite3を使うための基本コマンド

#apt-get install sqlite3

#sqlite3 -version

#sqlite3 test.db

sqlite>.database

sqlite>.schema

sqlite>.schema table_name


/{br

List the table contents as a sql statement.
sqlite>.dump table_name
 
Set the separator for displaying information.
sqlite>.separator symble
Example: Set the display information to be separated by ':'
sqlite>.separator :
 
Set the display mode.
sqlite>.mode mode_name
Example: default is list, set to column, other modes can be viewed via .help for mode related content
sqlite>.mode column
 
Output help information.
sqlite>.help
 
Set the width of each column to be displayed.
sqlite>.width width_value
Example:Set the width to 2
sqlite>.width 2
 
List the configuration of the current display format.
sqlite>.show
 
Exit sqlite terminal command.
sqlite>.quit
or
sqlite>.exit

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);

1) Create the data table
create table table_name(field1 type1, field2 type1, ...) ;
table_name is the name of the table to be created, fieldx is the name of the field in the table, and typex is the field type.
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 records
insert into table_name(field1, field2, ...) values(val1, val2, ...) ;
valx is the value to be stored in the field.
Example, to add data to the student information table.
Insert into student_info(stu_no, name) values(0001, alex);
 
3) Modify the data record
update table_name set field1=val1, field2=val2 where expression;
where is the sql statement for conditional judgment of the command, expression for judgment expression
Example, modify the student information table student number 0001 data record.
update student_info set stu_no=0001, name=hence where stu_no=0001;
 
4) Delete the data record
delete from table_name [where expression];
Clear all the data records of the table without adding the judgment condition.
Example, delete the data record with the student information table number 0001.
delete from student_info where stu_no=0001;
 
5) Query data records
The basic format of the select command is
select columns from table_name [where expression];
a query output all data records
select * from table_name;
bLimit the number of output data records
select * from table_name limit val;
cExport 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;
eConditional 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;
There are some fields whose values may be repeated, distinguish to remove duplicate items and list each field value in the column individually.
 
6) Create indexes
When say there are a large number of records in the data table, indexes help speed up finding the data table.
create index index_name on table_name(field);
Example, for the student table stu_no field, create an index.
create index student_index on student_table(stu_no);
After the creation, sqlite3 will automatically use the index when querying the field.
 
7) Delete the table or index
drop table table_name;
drop index index_name;