Sqlite3 Simple Application

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. Reproduced http://www.lupaworld.com/article-217676-1.html, the original some errors, this article has been corrected, will continue to update. 2. Installation SQLite on Windows Go to SQL download page: http://www.sqlite.org/download.html To download the precompiled binaries package under Windows: Sqlite-shell-win32-x86-<build#>.zip Sqlite-dll-win32-x86-<build#>.zip Note: <build#> is the compiled version number of SQLite Unzip the zip file to your disk and add the extracted directory to the system's PATH variable for easy execution of the SQLite command at the command line. Optional: If you plan to publish an application based on the SQLite database, you will also need to download the source code to compile and utilize its API Sqlite-amalgamation-<build#>.zip SQLite on Linux A number of Linux distributions provide convenient commands to get SQLite: /* for Debian or Ubuntu/*$ sudo apt-get install sqlite3 sqlite3-dev/* for RedHat, CentOS, or fedora/*$ yum install SQLite 3 Sqlite3-dev SQLite on Mac OS X If you are using Mac OS Snow Leopard or a newer version of the system, then the system is already equipped with SQLite. 3. Create your first SQLite database Now that you have the SQLite database installed, we will create the first database. In the Command Line window, enter the following command to create a database named Test.db. Sqlite3 test.db To create a table: Sqlite> CREATE TABLE MyTable (ID integer primary key, value text); The table contains a primary key field named ID and a text field named value. Note: At a minimum, you must create a table or view for the newly created database so that you can save the database to disk or the database will not be created. Next, write some data into the table: sqlite> INSERT INTO MyTable (ID, value) VALUES (1, ' micheal ');sqlite> inserts into MyTable (ID, value) VALUES (2, ' Jenny ') ;sqlite> INSERT INTO MyTable (value) VALUES (' Francis ');sqlite> inserts into MyTable (value) VALUES (' Kerk '); Query data: Sqlite> SELECT * from mytable;1| micheal2| jenny3| francis4| Kerk To set the results of a formatted query: sqlite>. Mode columnsqlite>. Header onsqlite> Select * from Test;id value------------------------1 Micheal2 Jenny3 Francis4 Kerk The. Mode column is set to the column display mode, and the. Header displays the column name. To modify the table structure, add columns: Sqlite> ALTER TABLE mytable add column email text not NULL ' collate nocase;; To create a view: Sqlite> CREATE VIEW Nameview as SELECT * FROM MyTable; To create an index: Sqlite> CREATE index Test_idx on mytable (value); 4. Some useful SQLite commands Show Table structure: sqlite>. Schema [table] Get all tables and Views: SQLite >. Tables Gets the list of indexes for the specified table: SQLite >. indices [table] Export database to SQL file: SQLite >. output [filename] SQLite > Dump SQLite >. Output stdout Import Database from SQL file: SQLite >. read [filename] Format output data to CSV format: SQLite >.output [Filename.csv] SQLite >.separator, SQLite > select * from Test; SQLite >.output stdout Import data from CSV file into table: SQLite >create table newtable (ID integer primary key, value text); SQLite >.import [Filename.csv] NewTable To back up the database: /* Usage:sqlite3 [Database]. dump > [filename] */ Sqlite3 mytable.db dump > Backup.sql Recover database:/* usage:sqlite3 [Database] < [filename] */ Sqlite3 Mytable.db < Backup.sql

Sqlite3 Simple Application