Sqlite3 Introduction to use

(Transferred from: http://www.cnblogs.com/kfqcome/archive/2011/06/27/2136999.html)

installation of SQLite
1. First is to download SQLite, you can download this page: http://www.sqlite.org/download.html
The latest version is: Sqlite-shell-win32-x86-3070701.zip This is the Windows SQLite command-line management tool, which can be used to manage SQLite database files

At the same time, it is best to download the document, which contains a detailed description of the use of SQLite: Sqlite-doc-3070701.zip

2. SQLite does not require any configuration and installation, just extract the downloaded shell files to any place you feel fit, then add them to the PATH environment variable (the PATH environment variable is added to use sqlite3 directly at the command line. If not added, you need to specify the path of the sqlite3, such as d:/sqlite/sqlite3, in detail.

3. Verify that the installation is successful.



The best way to master the use of a software is to use the software's own help and documentation, rather than always using Google. Documentation and help generally contain all the use of the software, after all, it is written by the developer of the software, he is the most familiar with the software.

First look at the help of SQLite:
Win + R input cmd, enter the command line, and enter Sqlite3 to enter SQLite's command line management tool.
Then enter. Help, and you can see all the usage of the Sqlite3 management tool:


In order to take care of E's bad friends, here are all the commands explained again, and give the corresponding example:
First create a database test.db, and create a table in the database user

1. Because before entering the sqlite3, first use. Quit the SQLite
2. Use sqlite3 test.db to load or create the specified database
3. Then use the SQL statement to create a table user (about the SQL statement, you can go to see some of the SQL get started books, in the SQLite document also has a corresponding SQL introduction, but feel that it is not suitable for getting started, Because after all, a lot of the basic knowledge of the database is not mentioned in it (also note that SQLite can not specify the type of column, which is a feature of SQLite, its column type is dynamic)
4. A command is then used to display the data tables that exist in the current database. Tables (third from the bottom of help)
5. Finally, a data is inserted into the data table (SQL statements, not commands in. help)


Next, the use of all commands is described from top to bottom:
. backup? Db? FILE
Backs up the database file to the specified file, which backs up the main database by default (when no database name is specified)

After the backup generated file is opened



. Bail on| OFF
Sets the execution of the SQLite tool to stop when an error is encountered, which is off by default.


. databases
Lists the names and files of all databases attached (in the current database file)



. Dump? TABLE1?? TABLE2? ...
Print the database as a SQL text format. If? TABLE1? Specifies that only the data table containing the TABLE1 in the name is printed.



. Echo on| OFF
Whether the input command is displayed before the displayed result
Note: This is shown in the column mode, and later on how to set the display mode to column



. exit
No explanation, quit the program


. Explain? on| OFF?
Turns the appropriate output mode on or off. When on or off is not specified, the default is on.



. Genfkey? OPTIONS?
Options have the following values:
--no-drop: Do not delete old foreign key triggers
--ignore-error: Ignoring foreign key errors for tables
--exec: Executing the generated SQL statement immediately
This should be set in violation of FOREIGN key constraints when SQLite does. The concrete did not try.


. Headers on| OFF
Whether to display the table header
. Mode mode? TABLE?
Set the output mode, when? Table? When specified, the output mode should only be on the output of the table

Each output mode when the header is on


Each output mode when the header is off



. Help
Show Help


. Import FILE TABLE
Reads the data from the file into the specified table
Notice the delimiter here. Separator is \ t, so data.txt is tab-delimited, by default, (this is 2,yuan2,2)





Indices? TABLE?
Displays all indexes for the specified table. When the table is not specified, all indexes are displayed.



. Load FILE? ENTRY?
Loading an external library file


. Log File|off
Turn the log on or off feature.
OFF:. Log off
Open and output the log to the standard output stream:. Log stdout
Turn on and output the log to standard error six:. Log stderr
Open and output the log to the specified file:. log d:/sqlite3/log.txt


Nullvalue STRING
The string that is displayed when the value is null. The default is ""



. Output FILENAME
Outputs all outputs to the specified file



. Output stdout
Output all outputs to the standard output stream (default), or output to the console


. prompt MAIN CONTINUE
Replace the standard hint. Not tried


. Quit
Do not explain, with. Exit, exit the program.


. Read FILENAME
Executes the SQL statement in the specified file




. Restore? Db? FILE
Restore the database from the backup file. The default is to restore the main database.

1. First we back up the main database
2. Then delete the data
3. Then restore the main database to see if the data is restored successfully



. Schema? TABLE?
Displays the creation statement for the specified table. When the table is not specified, the CREATE statement for all tables is displayed.

The index creation statement is included because the index was previously created.



. Separator STRING
Sets the output mode. Mode and import data.



. Show
Displays the current settings.



. Tables? TABLE?
Lists all table names.


. Timeout MS
Attempts to open a locked table only within a specified millisecond, instead of trying to open it all the time.


. width NUM1 NUM2 ...
Sets the width of each column in the. Mode column. The default width for each column is 10 characters, which is truncated when it is too long.

The first column is set to 1 characters, the second column is set to 0 to remain unchanged, and the third class is not set and remains unchanged.



. Timer on| OFF
Whether to turn on CPU time-consuming metrics.



data types and date functions supported by SQLite: http://blog.csdn.net/fer_ba/article/details/4582205

Sqlite3 Introduction to use