A command line interface to the SQLite database

2010-12-08  来源:本站原创  分类:Database  人气:115 

sqlite3: SQLite database, a command line interface

Original Address: http://www.sqlite.org/sqlite.html

SQLite library includes a name called sqlite3 command line, it allows users to manually enter and execute SQL commands for the SQLite database. This document provides a brief description of sample using sqlite3.

Start

Start sqlite3 program, just need to type in the name of the SQLite database with the "sqlite3" command. If the file does not exist, create a new (database) file. Sqlite3 program will then prompt you to enter SQL. Typing the SQL statement (a semicolon ";" end), hit Enter key, SQL statement is executed.

For example, create a table "tb11" named "ex1" SQLite database, you can do:

$ Sqlite3 ex1
SQLite version 3.3.17
Enter ". Help" for instructions
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>

You can knock you use system file character (usually Ctrl + D) or the interrupt character (usually Ctrl + C). To terminate the sqlite3 program. Make sure you typed the end of each SQL statement the semicolon! sqlite3 program by looking for a semicolon to determine the end of SQL statement. If you omit the semicolon, sqlite3 will give you a continuous command prompt and wait for you to add more current SQL command text. This feature allows you to enter multiple lines of multiple SQL statements, such as:

sqlite> create table tbl2 (
...> F1 varchar (30) primary key,
...> F2 text,
...> F3 real
...>);
sqlite>

Off-topic: Query SQLITE_MASTER Table

SQLite database is stored in a framework called "sqlite_master" special table. You can query other tables as the same by executing "SELECT" query this particular table. For example:

$ Sqlite3 ex1
SQlite vresion 3.3.10
Enter ". Help" for instructions
sqlite> select * from sqlite_master;
type = table
name = tbl1
tbl_name = tbl1
rootpage = 3
sql = create table tbl1 (one varchar (10), two smallint)
sqlite>

But you can not run the table in the sqlite_master such as DROP TABLE, UPDATE, INSERT or DELETE command. sqlite_master table you create, delete, and automatically update the index database tables. You can not manually change sqlite_master table.

TEMPORARY table structure is not stored in the "sqlite_master" table, due to TEMPORARY tables are not visible to the application, rather than the application to create the table. TEMPORARY tables is stored in another structure called "sqlite_temp_master" of a particular table. "Sqlite_temp_master" table is temporary table itself.

The special command sqlite3

Most candidates, sqlite3 reads the input line, and passes them on to the SQLite library to run. However, if the input line to a point (".") start, then this line will be intercepted and sqlite3 program their own interpretation. These "dot commands" are often used to change the query output format, or perform a pre-packet whip (predefined prepackaged) query.

You can enter at any time ". Help", lists the available dot commands. Such as

sqlite>. help
. Bail ON | OFF Stop after hitting an error. Default OFF
. Databases List names and files of attached databases
. Dump? TABLE? ... Dump the database in an SQL text format
. Echo ON | OFF Turn command echo on or off
. Exit Exit this program
. Explain ON | OFF Turn output mode suitable for EXPLAIN on or off.
. Header (s) ON | OFF Turn display of headers on or off
. Help Show this message
. Import FILE TABLE Import data from FILE into TABLE
. Indices TABLE Show names of all indices on TABLE
. Load FILE? ENTRY? Load an extension library
. Mode MODE? TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See. width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values ​​delimited by. separator string
tabs Tab-separated values
tcl TCL list elements
. Nullvalue STRING Print STRING in place of NULL values
. Output FILENAME Send output to FILENAME
. Output stdout Send output to the screen
. Prompt MAIN CONTINUE Replace the standard prompts
. Quit Exit this program
. Read FILENAME Execute SQL in FILENAME
. Schema? TABLE? Show the CREATE statements
. Separator STRING Change separator used by output mode and. Import
. Show Show the current values ​​for various settings
. Tables? PATTERN? List names of tables matching a LIKE pattern
. Timeout MS Try opening locked tables for MS milliseconds
. Width NUM NUM ... Set column widths for "column" mode
sqlite>

Change the output format

sqlite3 program can display up to eight different format a query result: "csv", "column", "html", "insert", "line", "watch" and "tcl". You can use ". Mode" dot command to switch between these output formats.

The default output format is "List." In list mode, each record is written in the query results and the line between each column separated by a separator string. The default delimiter is a pipe symbol ("|")。 When you output a list of symbols in the query results to another character added processing program (such as AWK) to go is particularly useful.

sqlite>. mode list
sqlite> select * from tbl1;
hello | 10
goodbye | 20
sqlite>

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

sqlite>. separator ","
sqlite> select * from tbl1;
hello, 10
goodbye, 20
sqlite>

In the "line" mode, each column in the records of the line in its own display. Each row consists of column names, an equal sign and columns of data. Next record with a blank line. This is an example of line mode output:

sqlite>. mode line
sqlite> select * from tbl1;
one = hello
two = 10

one = goodbye
two = 20
sqlite>

In column mode, each record in a separate line in the alignment of the data columns displayed. Columns such as:

sqlite>. mode column
sqlite> select * from tbl1;
one two
---------- ----------
hello 10
goodbye 20
sqlite>

In the case of default, each column at least 10 characters wide. Too wide data will be intercepted. You can use ". Width" command to adjust the column width. As follows:

sqlite>. width 12 6
sqlite> select * from tbl1;
one two
------------ ------
hello 10
goodbye 20
sqlite>

The above example ". Width" command to set the first column width to 12 in the second column width 6. Other column width unchanged. You can specify the query results you need with the number of columns as much ". Width" parameters.

If you specify a width of 0, then the column width automatically to the following three figures as the maximum column width: 10, header data width and the widest width of the column. This can automatically adjust the column width. Each column is set to automatically adjust the default value of 0.

First two lines in the output column label can be used ". Header" command off point. In the above example, the column is marked open. You can use the following method to close the column labeled:

sqlite>. header off
sqlite> select * from tbl1;
hello 10
goodbye 20
sqlite>

Another useful output mode is "insert". In insert mode, the format for the quilt looks like the SQL INSERT statement style. You can use insert mode to generate the file (easy) after the input for different databases.

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

sqlite>. mode insert new_table
sqlite> select * from tbl1;
INSERT INTO 'new_table' VALUES ('hello', 10);
INSERT INTO 'new_table' VALUES ('goodbye', 20);
sqlite>

The latest output format is "html". In this mode, sqlite3 query result is written to the XHTML table. <TABLE> Beginning and end </ TABLE> (tag) did not write, but <TR>, <TH> and <TD> other delimiters. html output of the CGI is quite land.

The results written to a file

By default, sqlte3 the results to standard output. You can use ". Output" command to change it. Only the output file name as the. Output command output parameter and all subsequent results will be written to that file. Use ". Output stdout" once again to the standard output. For example:

sqlite>. mode list
sqlite>. separator |
sqlite>. output test_file_1.txt
sqlite> select * from tbl1;
sqlite>. exit
$ Cat test_file_1.txt
hello | 10
goodbye | 20
$

Query the database structure

sqlite3 program provides several useful shortcut for the command to query the database structure. These are not can not be achieved in other ways. The command is only a shortcut to it.

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

sqlite>. tables
tbl1
tbl2
sqlite>

". Tables" command similar to the set list mode and then execute 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 sqlite3 source code (in the source tree src / shell.c), you can find the above specific query. ". Indices" command role in a similar way is to list all the specific index table. ". Indics" command to be an argument that the index table name. Last but not least, is ". Schema" command. Without any arguments, ". Schema" command to display the original used to create the current database that the CREATE TABLE and CREATE INDEX statements. If you give ". Schema" command to a table name, it shows the original creation of the table and all its indexes in the CREATE statement. 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> ". schema" command can be used to set the list and then execute the following query to achieve:

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

. Databases listed in the database file name
. Tables? PATTERN? List? PATTERN? Match table name
. Import FILE TABLE Import data file in the file
. Dump? TABLE? Generate SQL scripts form a database table
. Output FILENAME output file into the specified
. Output stdout to print the output to the screen
. Mode MODE? TABLE? Set the data output mode (csv, html, tcl ...
. Nullvalue STRING with the specified string instead of NULL string output
. Read FILENAME execute SQL statements in the specified file
. Schema? TABLE? Print SQL statements to create database tables
. Separator STRING string instead of the specified field separator
. Show print all environment variables set SQLite
. Quit to exit the command line interface

相关文章
  • A command line interface to the SQLite database 2010-12-08

    sqlite3: SQLite database, a command line interface Original Address: http://www.sqlite.org/sqlite.html SQLite library includes a name called sqlite3 command line, it allows users to manually enter and execute SQL commands for the SQLite database. Thi

  • sqlite3: A command line interface to SQLite database 2010-11-13

    sqlite3: A command line interface to SQLite database Translator's Note Original Address: http://www.sqlite.org/sqlite.html SQLite library contains a named sqlite3 command line, it allows users to manually enter and execute SQL commands for the SQLite

  • Command line import and export oracle database 2010-06-05

    Command line import and export oracle database Recently been using mysql as a database development project, may have a friend ask me one day a rookie import and export data oracle problem, because for some time before been engaged in oracle database,

  • ubuntu boot into the command line interface by default 2010-10-20

    Ubuntu the default boot method is a graphical interface, in order to operate faster, and we let it start the default to a command line interface. Mainly to modify the contents of a file: root @ sucre-ubuntu: ~ # more / etc/X11 / default-display-manag

  • linux switch from the graphical interface to the command line interface 2010-12-16

    I installed a vm virtual host to install linux red hat9.0 when using a graphical interface, the installation is complete boot linux directly into the graphical interface, to switch from the graphical interface to the command line interface Direct inp

  • switch from the graphical interface to the linux command line interface 2010-12-16

    I installed a vm virtual host install linux red hat9.0 when using a graphical interface, the installation is complete boot linux directly into the graphical interface, the switch from the graphical interface to the command line interface Direct input

  • Android combines the interface of the Sqlite database to learn to do add, delete, change, check. Operation 2010-07-14

    In a previous blog has been done on the SQLite database package, combining the blog of this blog do interface operation. Check out the data in the database using ListView display in the interface and increase the menu prompts on the data to do new an

  • linux garbled solve and modify the configuration file directly into the command line interface 2011-03-03

    [B] for domestic Linux users, often troubling question is: The system often needs to display Chinese, when they display become garbled, but for some reason, the English interface, the system needs time, but could not normally enter the system and sho

  • linux command line interface to switch to the graphical interface 2010-12-17

    Console to graphics: Alt + F7 Graphical interface to the console: Ctr + Alt + Fn (n = 1,2,3,4,5,6). Switch between the console: Alt + Fn (n = 1,2,3,4,5,6).

  • Ubuntu is the default entry to the command line interface 2010-09-17

    1. In the character-ended input "switchdesk GNOME", Enter, then enter "startx", Enter 2. In the console login First implementation of the export LANG = C Re-configure X sudo dpkg-reconfigure xserver-xorg

  • Windows into the mysql command line management interface 2011-01-30

    1, you first need to find the folder where your mysql (such as the D drive) Open a command prompt The default is C: \ User \ Username> or C: \ Documents and settings \ Administrator This difference is win7 and xp 2, D: Enter into the D drive Use the

  • From the windows command line and graphical user interface up and using SQL * PLUS 2011-10-11

    First, use the command line interface In all operating systems support SQL * Plus command line interface. To connect to a remote Oracle database, ensure that Oracle Net is installed and working properly. For more information, refer to Oracle9i Net Se

  • Doctrine2 use command line tools 2010-05-09

    Doctrine2 compressed package tools catalog is used to do some command line work. But must be downloaded from the SVN Doctrine2 to use them. Suppose you use the svn on doctrine2, depending on your server's configuration, if the operation for the mysql

  • Windows command line based (on) 2011-01-14

    Windows command-line based Go to the original HTML file http://sites.google.com/site/ghenix/ Download . Directory 1. Introduction : Why use the command line 2. The basis of the file system 2.1 Partition and drive letter 2.2 File with the name 2.3 Fil

  • Command line sql language 2011-07-16

    sql database of language as a standard language, in the computer industry has been gradually opened to a wide range of applications, following me to the mysql database as an example to show you the basic usage of the sql query. Database language is d

  • In the CMD command line method to modify the Windows Registry 2011-09-21

    In the CMD command line method to modify the Windows registry and Hiv Technology Undocumented registry regedit parameters can be used in the cmd. regedit operating parameters REGEDIT [/ L: system] [/ R: user] filename1 REGEDIT [/ L: system] [/ R: use

  • Android database operations and the sqlite command line operation 2010-09-29

    android additions and deletions to the database search on the example of ow much, not long-winded. Personally put forward a case not impress How about the following on the command line query in the database tables created by the code and the correspo

  • [Transfer] [Layout] Sqlite database sqlite3 command a small mind 2011-07-06

    SQLite library includes a name called sqlite3 command line, it allows users to manually enter and execute SQL commands for the SQLite database. Start Start sqlite3 program, just need to type in the name of the SQLite database with the "sqlite3"

  • Visual studio 2010 Database Project release method of the command line 2010-05-20

    "The statement database release," operate within the IDE is very convenient. However, when doing installation of contracted users, or DailyBuild server can not and should install Visual Studio. Fortunately, MS provides auto deploy command line m

  • Visual studio 2010 Database Project command line method for publishing 2010-05-20

    "The statement database release," operate within the IDE is very convenient. However, when doing installation of contracted users, or DailyBuild server can not and should install Visual Studio. Fortunately, MS provides auto deploy command line m