Command Line shell for SQLite

Document directory

• Getting started
• Except: Only sqlite_master tables are used.
• Special Command to sqlite3
• Change output format
• Write the result to a file
• Submit to database mode
• Convert the entire database into an ASCII text file
• Other point commands
• Use sqlite3 in shell script
• End shell command
• Compile the sqlite3 program from the source

Transfer http://www.helplib.net/s/sqlite/9/15.shtml

 

 

The SQLite library contains a simple command line utility namedSqlite3(OrSqlite3exeWindows), which allows users to manually enter and execute SQL commands for an SQLite database. This document provides a brief introduction to how to useSqlite3Program

Getting started

To start"Sqlite3During the program, you only need to type "sqlite3", followed by the SQLite file. If the file does not exist in the database, it will automatically"Sqlite3The program will prompt you to enter the SQL statement type in the SQL statement (by a semicolon) to terminate, SQL press "enter", will be executed

For example, to create a new database named "ex1" SQLite database using a "Table named tbl1", you may do this:

$Sqlite3 ex1In SQLite 3611, enter "help" for Instructions ";" enter an SQL statement to terminate SQLite>Create Table tbl1 (one varchar (10), two smallint );SQLite>Insert into tbl1 values (''hello! '', 10 );SQLite>Insert into tbl1 values (''goodbye '', 20 );SQLite>Select * From tbl1;Hello! | 10 goodbye | 20 SQLite>

Run the following command to terminate the sqlite3 program. Your system end-of-file character (usually control-d) uses the interrupt character (usually a control-C) stop long-running SQL statements

Make sure you enter a semicolon! At the end of each SQL command, find a semicolon in the sqlite3 program to know that if your SQL command is omitted, sqlite3 will give you a continue character if prompted, wait for more text to be entered and add it to the current SQL command. This function allows you to enter SQL statements across multiple lines, for example:

SQLite>Create Table tbl2 (...>F1 varchar (30) primary key,...>F2 text....>F3 real number...>);SQLite>

Except: Only sqlite_master tables are used.

"An SQLite database in database mode is stored in a special table" sqlite_master ". You can execute the" select "statement for a special sqlite_master table, just like any other table in the SQLite database. For example:

$Sqlite3 ex1SQLite 3611 input "help" Related Description SQLite>Select * From sqlite_master;Type = table name = tbl1 tbl_name = tbl1 rootpage = 3sql = create table tbl1 (one varchar (10), two smallint) SQLite>

However, you cannot execute drop table, update, insert or delete operations on the sqlite_master table. The sqlite_master table is automatically created or updated on the sqlite_master table. You cannot manually modify the sqlite_master table.

In this mode, the temporary table is not stored in the temporary "sqlite_master" table and the table is invisible, application for creating the table the temporary table in this mode is stored in another special table "sqlite_temp_master" "sqlite_temp_master" the table is temporary itself

Special Command to sqlite3

In most cases, the input read on sqlite3 is dragged to the SQLite library for execution. However, if an input row uses a dot, when intercepting and nocase sqlite3 program itself ", these" dot commands are usually used to change the output format or some built-in query statements to be executed.

For the point commands available on the server, you can enter "help" at any time, for example:

SQLite>HelpBackup? DB? File bail on | after the off operation is stopped, what is the database with the default off list name and the file attached database dump? Table? Dumping to database SQL text format echo on | off open or close command echo exit this program interpretation on | off or output mode is suitable for explain genfkey? Options? Options include -- no-drop: Do not delete the old fkey trigger -- ignore-errors: Ignore the fkey error provided by the table -- Exec: for the generated SQL statement, see file tool/genfkeyreadme for further information header (s) in source distribution) on | off close or show title help show this message import file table in file table used to separate index table only show all indexes on the specified table iotrace file enable I/O diagnosis log records set to file to Load file? Entry? Load the extended library mode? Table? Set the output mode. mode is one of the following values: the column in which CSV values are separated by commas (,) in the center of the value column (see width) HTML <Table> code insert SQL insert statement rows for a table each line corresponds to a value in the Value List separated by semicolons (;) tab-separated value TCL list element nullvalue replace string print string null Value Output Filename send output to filename output stdout send output to screen prompt main continue replace standard prompt quit exit this program read filename Execute SQL restore? DB? File to restore the DB content (the default "Main") from the file architecture? Table? Show create statement output mode and use separator string change delimiter import display current different set value table? Pattern? If the name of the table in the list matches the like pattern ms, please try to enable the MS table to lock the time-out timer on | off or CPU timer measurement width num setting "column width" Mode SQLite>

Change output format

The sqlite3 program can display the query results in eight different formats: "CSV" "column" "html" "insert" "line" "list" "tabs" and "TCL" can use the "Mode" vertex command to switch between these output formats

The default output mode is "list" in "location, each record list is written to one query. each row of output and record is a specific separator. The default Delimiter is a pipeline symbol ("| ") in the list mode, it is particularly useful to create a query output and send it to other programs (for example, awk for other processing)

SQLite>Mode listSQLite>Select * From tbl1;Hello | 10 goodbye | 20 SQLite>

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

SQLite>Separator ","SQLite>Select * From tbl1;"Hello, 10 goodbye, 20 SQLite>

In "line" mode, each column and a row in the database are in a separate row with an equal number column name, and make appropriate changes to the continuous records separated by a blank line below is an example of the output of the mode line:

SQLite>Mode lineSQLite>Select * From tbl1;One = Hello two = 10 One = goodbye two = 20 SQLite>

In column mode, each record is aligned with the data column in a separate row, for example:

SQLite>Mode ColumnSQLite>Select * From tbl1;One or two ---------- Hello 10 goodbye 20 SQLite>

By default, each column must contain at least 10 characters and the data size is too large to be truncated. You can use the following command to adjust the column width & W:

SQLite>Width: 12 6SQLite>Select * From tbl1;One or two ------------ Hello 10 goodbye 20 SQLite>

The "width & W" command in the preceding example sets the width of the first column to a specified value. if you want to view the width of all other columns with the width of 12 and the third column as 6, you can give any number of parameters to "width when necessary & W" any number of Columns with the specified column width in your query results

If the width of the specified column is 0, then the column width is automatically adjusted to a maximum of three numbers: the width of the first line of the header with a width of 10, set this data so that the column width self-adjusting is set to the width of each column. The default value of auto-adjusting is 0.

In the preceding example, you can use the "Open and Close Header" command to mark the columns in the column where the flag is located. You can also do the following:

SQLite>The header is closed.SQLite>Select * From tbl1;Hello 10 goodbye 20 SQLite>

Another useful output mode is "insert". In the insert mode, the output will be formatted as an SQL insert statement. You can use the insert mode to generate data that can be used to input data to different databases.

When the insert mode is specified, the table for which you want to create an additional parameter name is not archived, for example:

SQLite>Insert new_table ModeSQLite>Select * From tbl1;Insert into ''new _ table "values (''hello'', 10); insert into ''new _ table "values (''goodbye'', 20); SQLite>

The last output mode is "html". In this mode, sqlite3 write query results as XHTML table start <Table> and end </table> are not written, but all sins <tr> S <TH> S and <TD> S are html the output mode is CGI _ ganglia. PY labels are very useful

Write the result to a file

By default, sqlite3 will send: to use the "change this" command, you only need to set the name of the output file as a parameter. The output command and all subsequent query results will be written to the file. Use "output stdout" to start writing standard output. For example::

SQLite>Mode listSQLite>Delimiter |SQLite>Output test_file_1 text file txtSQLite>Select * From tbl1;SQLite>Exit$Cat test_file_1 text file txtHello | 10 goodbye | 20 $

Submit to database mode

When the sqlite3 program is specified to provide several convenient commands that can be very useful for starting, the database server name in the mode is invalid. The commands cannot be executed in some other ways. These commands provide a pure shortcut.

For example, to view the list of tables in the database, you can enter "table"

SQLite>TableTbl1 tbl2 SQLite>

The "list mode table" command is similar to setting and then execute the following query:

Select name from sqlite_master where in type ('able', ''view ") and not like '�� qlite _ % "Union all select name from sqlite_temp_master where in type ('able', ''view'') order by 1

In fact, if you use a source code tree like sqlite3 program (the src/shellc file is found in the list), you will find that it fully complies with the above Query

"How to separate indexes is similar to" command to list all indexes in a table "used to separate indexes" command to accept a parameter, it means that the index of the name table is incorrect last, however, there is no such command. If the "schema" command does not contain parameters, the "schema" command displays that the original create table and create index statements are used to generate the current database. If the user name "schema" is allowed ", it shows that the original create statement is used to store the table and all. If its indexes are:

SQLite>ArchitectureCreate Table tbl1 (one varchar (10), two smallint) Create Table tbl2 (F1 varchar (30) primary key, F2 text. F3 real number) SQLite>Architecture tbl2Create Table tbl2 (F1 varchar (30) primary key, F2 text. F3 real number) SQLite>

If the "architecture" command completes the list mode with the same effect, enter the following query:

Select SQL from (select * From sqlite_master Union all select * From sqlite_temp_master) Where type! = ''Meta" order by tbl_name. Enter DESC, name

Alternatively, if you specify the parameter "schema" because you only want to structure a single table, the query looks 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'' �� qlite _ % "order by substr (type, 2, 1), name

You can provide a parameter BCP command. If you have a query, the query looks like this:

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'' �� qlite _ % "order by substr (type, 2, 1), name

The "% s" parameter in the query is replaced. This allows you to view the mode for databases of certain subsets.

SQLite>Architecture % ABC %

The "table" command also accepts a mode as its first parameter. If you set the parameter to a table command, an "%" APPEND and an instr and like clause are added to the query. This allows you to list only these tables to match specific styles.

The "databases" command shows that there are always at least two databases opened in the current connection. The first one is "Main", and the second one is "Temp" in the original database ", this database is used for temporary tables. There may be other Databases listed in the database appended using the attach statement. The first output column is the appended name, and the second column is the file name of the external file.

SQLite>Database

Convert the entire database into an ASCII text file

Use the "dump command to copy the content of the entire database to a single ASCII text file. This file can be converted back to the pipeline of the database.Sqlite3.

Please copy a good way in archive with this database:

$Echo ''dump "| sqlite3 ex1 | gzip-C> ex1 dump GZ

This generatesEx1 dump GZWhen a database contains everything required for createbackup, or createbackup database on another machine, you only need to enter:

$Zcat ex1 dump GZ | sqlite3 ex2

The text format is pure SQL. Therefore, you can also use the dump command to export the SQLite database set in application configuration to other commonly used SQL database engines like this:

$Database Cluster ex2$Sqlite3 ex1 dump | Psql ex2

Other point commands

"Explanation": You can use the following command to set the output mode to "column" and set the column width to a reasonable value. The "Explain" command is an SQLite- specific SQL extension background mode if any general SQL SCS explain, when the SQL command is parsed but the VM command sequence is not executed, the SQL command is used to return the query result, for example:

SQLite>ExplanationSQLite>Delete tbl1, two of which are <20;ADDR opcode P1 P2 P3 ---- ------------ ----- defaults 0 listopen 001 open 01tbl1 2 next page 0 limit 93 Field 014 integer 20 05ge 026 key 007 listwrite 008 goto 029 Noop 0010 listrewind 0011 listread 014 12 delete 0013 goto 011 14 listclose 00

Time specified by the "timeout" commandSqlite3The program will wait for the file to be locked to clear it. It is trying to access an error and returns the default value zero that Will time out so that an error is returned immediately if any required database table or index is locked.

Finally, we mention that the "exit" command causes the sqlite3 program to exit.

Use sqlite3 in shell script

One way to use sqlite3 in shell scripts is to use "Echo" or "cat" to generate a series of commands in a file and then call the unit test to input this from the generated command file what still fails to run normally is the convenience of attaching in most cases, sqlite3 allows a single SQL command to input a value in the command line as the second parameter in the database name when the sqlite3 program is started, the second parameter of the two parameters will be passed to the SQLite library to print the query results on the standard output for processing in the list. this mode and mechanism are designed to help sqlite3 easy-to-use programs, such as "awk" combined use example:

$Sqlite3 ex1' �� elect * From tbl1 "|>Awk '{printf "<tr> <TD> % S <TD> % Sn", $1, $2 }'<Tr> <TD> Hello <TD> 10 <tr> <TD> goodbye <TD> 20 $

End shell command

SQLite commands are usually terminated by semicolons. In shell, you can also use word "go" (case-insensitive ), it can also be a slash character "/" that becomes messy. The following command is defined by SQL Server and Oracle.Sqlite3_exec ()Through shell, because these are converted into a semicolon before being passed to this function

Compile the sqlite3 program from the source

The single name in the source code to the sqlite3 interface of the command line is "shell can determine C". Download this file from the SQLite website first (use the sqlite3 Library source code together to generate an executable file, for example:

gcc -osqlite3 shellc sqlite3c -ldl -lpthread