This page last modified: Aug 22 2008
keywords:sql,sql,sequel,database,db,rdbms,relational,database,sqlite3 description:Sample session transcript of running SQLIte version 3, creating a table, inserting record, and selecting records. title:SQLite example for beginners In this example I have logged in to a computer called "zeus". If you are using Linux or OSX, run your terminal program (instead of "logging in". On OSX the terminal is called "Terminal" and can be found in the Applications folder. Linux and KDE is "Terminal", and is found under the "System" item in the Start (K, Fedora) menu. (The Linux application's name is "konsole"; the menu item doesn't match the actual name of the program.) In this example, the file holding my database is "first.db". SQLite is transactional, and ACID compliant, and thus is a true RDBMS. All tables are contained in a single file, and the SQL engine is a single executable. There is no server. No configuration is necessary. The database file is portable to other platforms (other operating systems.) SQLite is really amazing. The SQLite home page: http://www.sqlite.org/index.html If you are new to the OSX or Linux command line (we call it a "shell"), you may enjoy my Intro to Unix, Linux and Mac OSX. http://itc.virginia.edu/achs/documents/intro_unix.html (All trademarks are properties of their respective owners.) What follows is a session transcript: [zeus ~]$ sqlite3 first.db SQLite version 3.4.0 Enter ".help" for instructions sqlite> .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 .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 .width NUM NUM ... Set column widths for "column" mode sqlite> .header on sqlite> .mode column sqlite> create table address (street1 text, street2 text, city text, state text, country text, zip text); sqlite> .schema address CREATE TABLE address (street1 text, street2 text, city text, state text, country text, zip text); sqlite> insert into address values ('123 Locust Road', '', 'Eden', 'VA', 'USA', '22123'); sqlite> insert into address values ('123 Murky Hollow', 'Box 456', 'Eden', 'VA', 'USA', '22123'); sqlite> select * from address; street1 street2 city state country zip --------------- ---------- ---------- ---------- ---------- ---------- 123 Locust Road Eden VA USA 22123 123 Murky Hollo Box 456 Eden VA USA 22123 sqlite> .quit [zeus ~]$ sqlite3 first.db SQLite version 3.4.0 Enter ".help" for instructions sqlite> .mode column sqlite> .header on sqlite> select * from address; street1 street2 city state country zip --------------- ---------- ---------- ---------- ---------- ---------- 123 Locust Road Eden VA USA 22123 123 Murky Hollo Box 456 Eden VA USA 22123 sqlite> .quit [zeus ~]$