Go Learn SQLite in 1 hour

Reprint Description:

1. Original address: http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/

2. Address: http://www.oschina.net/question/12_53183 (sweet potato translation)

3. There are 5 errors in the original English text, the following has been amended

The original text reads as follows:

Learn SQLite in 1 hour

Askyb on could, 9th in SQLite

1. Introduction

SQLite is an open source, embedded relational database which implements a self-contained, serverless, zero-configuration,t Ransactional SQL Database engine. SQLite has a well-deserved reputation for being highly portable, easy to use, compact, efficient, and reliable. Unlike Client–server database management systems, installing and running of SQLite database is pretty straightforward in M OST cases-just make sure the SQLite binaries file exists anywhere you want and start to create, connect, and using the D Atabase. If you were looking for a embedded database for your projects or solutions, SQLite is definitely worth.

(SQLite is an open-source, embedded relational database that implements a self-contained, 0 configuration, transactional SQL database engine.) It is characterized by its high portability, ease of use, compact structure, high efficiency and reliability. Unlike other database management systems, SQLite is very simple to install and run, in most cases-just make sure that sqlite binaries exist to start creating, connecting, and using the database. If you are looking for an embedded database project or solution, SQLite is definitely worth considering. [Sweet potato translation])

2. Installation

SQLite on Windows

1. Navigate to SQLite download page at http://www.sqlite.org/download.html
2. Download the following precompiled Binaries for Windows:
   • sqlite-shell-win32-x86-<build#>. zip
   • sqlite-dll-win32-x86-<build#>. zip
   Note: <build#> is the running build number of SQLite.
3. Unpack the ZIP files into your favourite folder. Add folder path to the path system variable the SQLite command line shell available within the environment.
4. OPTIONAL: IF you plan to develop any application this host a SQLite database then you'll need to download the source code in order To compile and utilize the its API.
   • sqlite-amalgamation-<build#>. zip

SQLite on Linux
SQLite binaries can be obtained in a variety of ways depending on the Linux distro that is using.

/* For Debian or Ubuntu/*$ sudo apt-get install sqlite3 libsqlite3-dev/* for RedHat, CentOS, or fedora/*$ yum install S QLite3 Libsqlite3-dev

SQLite on Mac OS X
If you were running a MAC OS Leopard or later, then it Alraedy has pre-installed SQLite.

3. Create you first SQLite Database

You now should has the SQLite binaries ready and time-to-Create your first SQLite database now. Type the following command in Windows ' s command prompt or Linux ' s terminal.

To create a new database called Test.db:

Sqlite3 test.db

To create a table in the database:

SQLite>createtableintegerprimarykeytext);

2 columns were created. a primary key column called "id" which have the ability to automatically generate value by default and a simple text fi Eld called "value".

Note: At least 1 table or view need to is created in order to commit the new database to disk. Otherwise, it won ' t database won ' is created.

To insert the data into mytable:

Sqlite> Insert  intoMyTable (ID, value)Values(1,'Micheal'); SQLite> Insert  intoMyTable (ID, value)Values(2,'Jenny'); SQLite> Insert  intoMyTable (value)Values('Francis'); SQLite> Insert  intoMyTable (value)Values('Kerk');

To fetch data from mytable:

SQLite>Select* from mytable; 1 | Micheal 2 | Jenny 3 | Francis 4 | Kerk

To fetch data from mytable by improving the formatting a little:

Sqlite>. modecolumnSQLite>. Header onSQLite> Select *  fromMytable;id Value----------- -------------1Micheal2Jenny3Francis4Kerk

The. Mode column is display data into column format.
The. Header on would display table's column name.

To add additional column into mytable:

SQLite>altertableaddcolumntextnotnull  "' collate nocase;

To create a view for mytable:

SQLite>createviewasselect* from MyTable;

To create a index for mytable:

SQLite>Createindex on mytable (value);

4. Useful SQLite ' s command

Display Table Schema:

sqlite>. Schema [table]

Retrieve a list of tables (and views):

Sqlite>. Tables

Retrieve A list indexes for a given table:

sqlite>. indices [table]

Export database objects to SQL format:

sqlite>. Output [filename]
sqlite>. Dump
sqlite>. Output stdout

Import database Objects (SQL format) to database:

sqlite>. read [filename]

Formatting exported data into CSV format:

Sqlite>.output [Filename.csv]
Sqlite>.separator,
Sqlite> select * FROM MyTable;
Sqlite>.output stdout

Import CSV formatted data to a new table:

Sqlite>create table newtable (ID integer primary key, value text);
Sqlite>.import [Filename.csv] NewTable

To backup database:

/* Usage:sqlite3 [Database]. dump > [filename] */
Sqlite3 mytable.db dump > Backup.sql

To restore Database:

/* Usage:sqlite3 [Database] < [filename] */
Sqlite3 Mytable.db < Backup.sql

Go Learn SQLite in 1 hour