SQLite usage notes

 

I. Command Summary

Command	Command description	Remarks
. Database	View Database
. Dump	Back up the database and export SQL-format files from the database	SQLite>. output file. SQL SQLite>. Dump SQLite>. Output stdout file system contains a file. SQL file. After cat, it is all SQL statements.
. Tables	What are the database tables?
. Show	View the parameters	SQLite>. Show ECHO: Off explain: Off headers: Off Mode: columnnullvalue: "" output: stdoutseparator: "|" stats: Off width:
. Schema [Table name]	View All or single table Structures
. Indices [Table name]	View table Indexes
. Output	Output results to files or standard screens	. Output file1.txt. Output file2.cvs
. Separator [separator]	Set the delimiter Between Columns	. Separator,
. Mode	Change output format	. Mode Insert tab. Mode column. Mode list default
. Header	Change output format	. Header on output Header
. Nullvalue string	String displayed when a null value is encountered
. Prompt [value]	Change the shell prompt of the CLP	SQLite>. Prompt 'sqlite3> 'sqlite3>
. Width num1 num2...	Set the display width of each column in column mode.
. Help	Help
. Quit	Exit sqlite3 command line

  Note: Sqlite3 Command " . " And SQL The operation statement uses " ; " End   2. An important system table sqlite_master   Sqlite_masterA table is a system table that contains information about all tables, views, indexes, and triggers in the database. The root pages of all tables and indexes are stored in Sqlite_masterTable. All pages in the database start from 1. A database consists of multiple B-trees. The B-tree is used for each table and index. The first page (page 1) in the database is a bit special. The first 100 bytes of page 1 are special file headers that describe database files. It includes the database version, format version, page size, encoding, and other permanent parameters set during database creation. The document about this special file header is in btree. C, and page 1 is also Sqlite_master table. SQLite>. schema sqlite_master
Create Table sqlite_master (
Type text,
Name text,
Tbl_name text,
Rootpage integer,
SQL text
);

No.	Field	Description
1	Type	The value is one of "table", "Index", "Trigger", or "View.
2	Name	Object Name. The value is a string.
3	Tbl_name	For a table or view object, the field value is the same as Field 2. If it is an index or trigger object, the field value is the table name associated with it.
4	Rootpage	For a trigger or view object, the value of this field is 0. Table or index object. The value of this field is the number of its root page.
5	SQL	String, the SQL statement used to create this object.