The simplest database, sqlite3

I. SQLite

Simply put, the SQLite database corresponds to a file (usually named XXX. DB ). You can use the SQLite shell command line to operate the SQLite database, or use APIs in other languages to operate the SQLite database. The username and password are not required for accessing the SQLite database.

 

On Windows you can download: sqlite-shell-win32-x86-3071300.zip, other platforms can download the corresponding shell command line.

 

Help:

 

D: \ sqlite_test> sqlite3.exe test. dbsqlite version 3.7.13 2012-06-11 02: 05: 22 enter ". Help" for instructionsenter SQL statements terminated with a ";" SQLite> . Help . Backup? DB? File backup dB (default "Main") to file. 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 if Table specified, only dump tables matching like pattern table .. 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. with no ARGs, it turns explain on .. 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 if Table specified, only show indices for tables matching like pattern table.. Load file? Entry? Load an extension library. Log File | off turn logging on or off. file can be stderr/stdout. Mode mode? Table? Set output mode where mode is one of: CSV comma-separated values Column left-aligned columns. (See. width) HTML <Table> code insert SQL insert statements for table line one value per line list values delimited. separator string tabs tab-separated values TCL list elements. nullvalue string print string in place of null values. output Filename send output to filename. output stdout send out Put to the screen. Prompt main continue Replace the standard prompts. Quit exit this program. Read filename Execute SQL in Filename. Restore? DB? File restore content of dB (default "Main") from file. schema? Table? Show the create statements if Table specified, only show tables matching like pattern table .. separator string change Separator Used by output mode and. import. show show the current values for various settings. stats on | off turn stats on or off. tables? Table? List names of tables if Table specified, only list tables matching like pattern table .. timeout MS try opening locked tables for MS milliseconds. trace file | off output each SQL statement as it is run. vfsname? Aux? Print the name of the VFS stack. Width num1 num2... Set column widths for "column" mode. timer on | off turn the CPU timer measurement on or OFF

 

 

2. SQLite

1) Open or create an SQLite Database

 

D: \ sqlite_test> sqlite3.exe test. dbsqlite version 3.7.13 2012-06-11 02: 05: 22 enter ". Help" for instructionsenter SQL statements terminated with a ";" SQLite>

 

 

2) create a table and insert data

SQLite> Create Table mytable (name varchar (10), age smallint); SQLite> insert into mytable values ("itech", 5 ); SQLite> insert into mytable values ("Jason", 10); SQLite> select * From mytable; itech | 5jason | 10

SQLite>

 

3) view the table and table Schema

SQLite>. tablesmytablesqlite>. schema mytablecreate table mytable (name varchar (10), age smallint );

SQLite>

 

4) export the database to an ascii SQL file + re-import it to the new database

D: \ sqlite_test> echo. Dump | sqlite3 test. DB> test. DB. Dump

D: \ sqlite_test> type test. DB. dumppragma foreign_keys = off; begin transaction; Create Table mytable (name varchar (10), age smallint); insert into "mytable" values ('itech ', 5 ); insert into "mytable" values ('jason ', 10); Commit;

 

D: \ sqlite_test> type test. DB. dump | sqlite3 test2.dbd: \ sqlite_test> sqlite3 test2.dbsqlite version 3.7.13 2012-06-11 02: 05: 22 enter ". help "for instructionsenter SQL statements terminated with a"; "SQLite>. tablesmytablesqlite>

 

 

5) format the output

SQLite>. Mode columnsqlite>. header onsqlite> select * From mytable; name age ---------- -------- itech 5 Jason 10

SQLite>

 

Complete!