SQLite Common commands

From: http://www.cnblogs.com/caizhimin816/articles/1885349.html

SQLite shell http://www.sqlite.org/download.html page precompiled binaries for Windows

Sqlite_master table

The data structure of an SQLite database is stored in the "sqlite_master" table. You can execute the SELECT statement on the sqlite_master table like other data tables. For example:

#Sqlite3 ex1
SQLite vresion 3.3.10
Enter ". Help" for instructions
SQLite>Select * From sqlite_master;
    Type = table
  Tbl_name = tbl1 

  Name = tbl1
 Rootpage = 3
     SQL = CREATE TABLE tbl1 (one varchar (10), two smallint)
SQLite>

 

Write the result to a file

By default, sqlite3 sends the result to the standard output. You can use ". output "to change, just pass the output file name as a parameter. output. All subsequent query results are written into the file. When ". Output stdout" is used, it is written to the standard output again. For example:

SQLite>. Mode list display mode
SQLite>. Separator | direct field Interval
SQLite>. Output test_file_1.txt output to that file
SQLite>Select * From tb1; query data
SQLite>. Exit exit
#Cat test_file_1.txt
Hello | 10
Goodbye | 20

 

Change output format

The sqlite3 program can display the query results in eight different formats: "CSV", "column", "html", "insert", "row ", "tabulation" and "TCL ". You can use the ". mode" command to switch between these output formats.

   The default output format is "list ". In list mode, each query result record is written in one row and each column is separated by a string delimiter. The default Delimiter is a pipe sign ("| "). List symbols are particularly useful when you output the query result to another program (such as awk) that is processed by another operator.

SQLite>. ModeList
SQLite>Select*FromTb1;
Hello | 10
Goodbye | 20
SQLite>

   You can use the ". separator" command to change the separator. For example, to change the delimiter to a comma and a space, you can do this:

SQLite>. Separator","
SQLite>Select*FromTb1;
Hello,10
Goodbye,20
SQLite>

   In line mode, each column in a record is displayed in its own row. Each row consists of a column name, an equal sign, and column data. The next record is separated by an empty row. This is an example of Row-mode output:

SQLite>. ModeLine
SQLite>Select*FromTb1;
One=Hello
Two=10

One=Goodbye
Two=20
SQLite>

   In column mode, each record is displayed as a data column alignment in a separate row. Example:

SQLite>. ModeColumn
SQLite>Select*FromTbl1;
One        Two      
---------- ----------
Hello      10       
Goodbye    20       
SQLite>

       By default, each column must have at least 10 characters in width. Data that is too wide will be intercepted. You can use the ". width" command to adjust the column width. As follows:

SQLite>. Width126
SQLite>Select*FromTbl1;
One          Two  
------------ ------
Hello        10   
Goodbye      20   
SQLite>

    In the preceding example, the ". width" command sets the first column width to 12 and the second column width to 6. The width of other columns remains unchanged. You can specify the ". width" parameter that is the same as the number of columns required for your query results.

   If you specify a column width of 0, the column width will automatically use the maximum value of the three numbers below as the column width: 10, the header width, and the width of the widest data column. This allows the column to automatically adjust the width. The default value of each column is 0.

   You can use the ". Header" command to close the column marker that appears in the first two rows of the output. In the preceding example, the column ID is open. You can use the following method to disable column labeling:

SQLite>. HeaderOff
SQLite>Select*FromTb1;
Hello        10   
Goodbye      20   
SQLite>

. HeaderOn can open the column label;

   Another useful output mode is "insert ". In insert mode, the quilt is formatted as a SQL insert statement. You can use the insert mode to generate files (for ease of use) for input to different databases.

   When specifying the insert mode, you must specify a specific parameter that is the name of the table to be inserted. For example:

SQLite>. ModeInsertNew_table
SQLite>Select*FromTb1;
InsertInto'New _ table'Values ('hello', 10 );
InsertInto'New _ table'Values ('Goodbye ', 20 );
SQLite>

   The latest output format is "html ". In this mode, sqlite3 writes the query results into an XHTML table. The START <Table> and end </table> are not written, but there are <tr>, <TH>, and <TD> delimiters. HTML output is quite useful for CGI.

 SQLite>. Mode html
SQLite> select * From tb1;
<Tr> <TD> hello! </TD>
<TD> 10 </TD>
</Tr>
<Tr> <TD> hello! </TD>
<TD> 10 </TD>
</Tr>
<Tr> <TD> hello! </TD>
<TD> 10 </TD>
</Tr>
<Tr> <TD> hello! </TD>
<TD> 10 </TD>
</Tr>
<Tr> <TD> goodbye </TD>
<TD> 20 </TD>
</Tr>

 

Query database structure

 The sqlite3 program provides several useful shortcut commands for querying database structures. These cannot be implemented in other ways. These commands are just a shortcut.

   For example, to view the table list of a database, you can type ". Tables ".

SQLite>. Tables
Tbl1
Tbl2
SQLite>

   The ". Tables" command is similar to setting the list mode and then executing the following query:

SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1 

In fact, you can view the source code of sqlite3 (in src/shell. C of the source file tree). You can find the specific query above.

The ". Indices" command is similar to listing all the indexes of a specific table. The ". indics" command requires a parameter, that is, the name of the table to be indexed. Last, but not least, it is the ". schema" command. Without any parameters, the ". schema" command displays the original create table and create index statements used to create the current database. If you give the ". schema" command a table name, it displays the create statement for creating the table and all its indexes. We can:

sqlite> .schemacreate table tbl1(one varchar(10), two smallint)CREATE TABLE tbl2 (  f1 varchar(30) primary key,  f2 text,  f3 real)sqlite> .schema tbl2CREATE TABLE tbl2 (  f1 varchar(30) primary key,  f2 text,  f3 real)sqlite> 

You can use the ". schema" command to set the list and execute the following query:

 

SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' ORDER BY tbl_name, type DESC, name 

 

Or, if you give a ". schema" command, because you only want to get the structure of a table, the query can be like this:

SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY substr(type,2,1), name 

You can provide the. schema command with a parameter. In this case, the query can be as follows:

SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE tbl_name LIKE '%s' AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY substr(type,2,1), name 

In the query, "% s" is replaced by your parameter. This allows you to query a subset of the database structure.

sqlite> .schema %abc% 

 Together with these, the ". Table" command also accepts a mode as its parameter. If you give ". Table" a parameter, "%" will be expanded before and after, and a like clause will be appended to the query. This allows you to list tables that only match the specified schema.

The ". datebasae" command displays a list of all databases opened by the current connection. It will allow up to two at a time. The first one is "Main", the database that was initially opened. The second is "Temp", which is used for the temporary table database. The data appended with the attach statement may have an additional database list. The name of the database associated with the first column of output, and the second column is the external file name.

sqlite> .databases 

sqlite> .databasesseq  name             file

---  ---------------  ----------------------------------------------------------

0    main             //ex1

1    temp             /tmp/etilqs_SAmtUWMcNv0tqMn

Convert the entire database to an ASCII text file 

 ". Dump" command into a single ASCII text file. This file can be used as a pipeline to be passed to the sqlite3 command for conversion back to the database.

The best command to copy a database file is:

$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz  

It generatesEx1.dump.gzFile, which contains all the information about restructuring the database in the future or on other machines. To reconstruct the database, you only need to enter:

$ zcat ex1.dump.gz | sqlite3 ex2  

This text format is pure SQL statements, so you can use the. Dump command to export an SQLite database to another commonly used SQL database engine. For example:

$ createdb ex2$ sqlite3 ex1 .dump | psql ex2  

Other point commands 

The ". Explain" command can be used to set the output format to "column"Set the column width to the reasonable width of the explain command. The explain command is a SQL extension specific to SQLite and is useful for debugging. If any common SQL statement is explained, the SQL command is decomposed and analyzed but not executed. Instead, the VM command sequence is used to execute SQL commands and return a similar query result. For example: 

The ". Timeout" command sets sqlite3 to wait for a total time to lock an attempt to store files, except until the error returns. The default timeout value is 0. Therefore, if any database table or sequence column is locked, an error is returned immediately. 

SQLite>. Explain
SQLite>ExplainDeleteFromTbl1WhereTwo <20;
ADDR Opcode       P1    P2    P3         
---- ------------ ----- ----- -------------------------------------  
0    Listopen     0     0                 
1    Open         0     1     Tbl1       
2    Next         0     9                 
3    Field        0     1                 
4    Integer      20    0                 
5    Ge           0