SQLite database mounting and Common commands (from: http://blog.csdn.net/windone0109/article/details/5514948)

Installation:

Download the latest SQLite version from the official website

Http://www.sqlite.org/

For: http://www.sqlite.org/download.html

The official download page provides download of many versions... Here we will introduce it;

Source code: Download the source code version

Documentation: Related Documents

Precompiled binaries for Linux/precompiled binaries for Mac OS X/

Precompiled binaries for Windows

Precompiled version of Linux/MAC/win

If you are only a database user, download the compiled version! Here I am a win user. The downloaded file is sqlite3.exe.

Put this file in a directory to complete the installation of all SQLite databases!

 

New:

At the command prompt ($ is the shell prompt number), the path of sqlite3.exe to be tested is E:/sqlite3/sqlite3.exe.

There are also many methods available on the Internet, but it seems that there are some problems. After some exploration, the conclusions are as follows, but we are not sure whether it is affected by the operating system!

Mount after generating database files
$> Sqlite3.exe test. dB;

$> Sqlite3.exe test. DB

Directly mount to generate

$> Sqlite3.exe test. dB;

Select an existing data file for selective mounting
$> Sqlite3.exe test. DB or

$> Sqlite3.exe E:/sqlite3/test. DB

After the database is attached, you can operate the connected database by using the command line!

Run cmd to enter the command line
1)
Create a database file:
> Sqlite3 D:/test. DB press ENTER
A test. DB is generated on disk D.
In this way, sqlite3 also hangs the test. DB

2)
Use. Help to see what commands are available
>. Help press Enter.

3) You can enter the SQL statement to create a table. After that, press enter to view the table.

4) Check the number of tables created.
>. Tables

5) view the table structure
>. Schema table name

6) Check the database currently on.
>. Database

7) If you want to output the query to a file
>. Output file name
> Query statement;
The query result is output to the file C:/query.txt.
Output query results on the screen
>. Output stdout

8) output the table structure and the index will also output
. Dump table name
9) Exit
>. Exit or. Quit

 

SQLite official help prompt:

sqlite> .help.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.timer ON|OFF          Turn the CPU timer measurement on or off.width NUM NUM ...     Set column widths for "column" mode

 

 

It supports most SQL commands. This is a list:

Attach Database

Begin transaction

Comment

Commit transaction

Copy

Create Index

Create Table

Create trigger

Create View

Delete

Detach Database

Drop Index

Drop table

Drop trigger

Drop View

End transaction

Explain

Expression

Insert

On conflict clause

Pragma

Replace

Rollback transaction

Select

Update

It also provides virtual machines for processing SQL statements. This is an interesting thing. Supports transaction functions.