1. Introduction
SQLite is an open source, embedded relational database which implements a self-contained, serverless, zero-configuration,transactional 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 most cases — just make sure the SQLite binaries file exists anywhere you want and start to create, connect, and using the database. If you are looking for an embedded database for your projects or solutions, SQLite is definitely worth considering.
2. Installation
SQLite on Windows
- Navigate to SQLite download page at http://www.sqlite.org/download.html
- Download the following Precompiled Binaries For Windows:
- sqlite-shell-win32-x86-<build#>.zip
- sqlite-dll-win32-x86-<build#>.zip
- Unpack the ZIP files into your favourite folder. Add folder path to the PATH system variable to make the SQLite command line shell available within the environment.
- OPTIONAL: If you plan to develop any application that host a sqlite database then you will need to download the source code in order to compile and utilize 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 you are using.
$ sudo apt-get install sqlite3 sqlite3-dev
$ yum install SQLite3 sqlite3-dev
SQLite on Mac OS X
If you are running a Mac OS Leopard or later, then it alraedy have pre-installed SQLite.
3. Create you first SQLite Database
you now should have 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:
NOTE: At least 1 table or view need to be created in order to commit the new database to disk. Otherwise, it won’t database won’t be created.
To insert data into mytable:
sqlite> insert into mytable(id, value) values(2, 'Jenny');
sqlite> insert into mytable(value) values('Francis');
sqlite> insert into mytable(value) values('Kerk');
To fetch data from mytable:
1|Micheal
2|Jenny
3|Francis
4|Kerk
To fetch data from mytable by improving the formatting a little:
sqlite> .header on;
sqlite> select * from mytable;
id value
----------- -------------
1 Micheal
2 Jenny
3 Francis
4 Kerk
The .header on will display table’s column name.
To add additional column into mytable:
To create a view for mytable:
To create an index for mytable:
3. Useful SQLite’s command
Display table schema:
Retrieve a list of tables (and views):
Retrieve a list indexes for a given table:
Export database objects to SQL format:
sqlite> .dump
sqlite> .output stdout
Import database objects(SQL format) to database:
Formatting exported data into CSV format:
sqlite>.separator ,
sqlite> select * from mytable;
sqlite>.output stdout
Import CSV formatted data to a new table:
sqlite>.import [filename.csv] newtable
To backup database:
sqlite3 mytable.db .dump > backup.sql
To restore database:
sqlite3 mytable.db < backup.sql
Is it possible to do some configuration that I don’t need to type all the time the same command?
I’d suggest 2 options for you:
1. You can either write a series of batch files which consist of your sqlite shell command or
2. Use a sqlite database browser which come with better GUI experience.
I can give you an example on how to write a sqlite shell command in a batch file should you wanna try with option #1:
1. Create and save the a line of sqlite shell command into a script.bat file.
sqlite3.exe test.db “select * from mytable”
2. Click or run the script.bat should return all records from mytable in test.db database.
Hi, Thank you for useful tutorials.
Please correct your examples, your table name is mytable but you enter test in commands. (select * from test).
Good spot! Correction done.
Hi,
I appreciate the tutorial. Can you shed some light on why I keep getting an error every time I enter this: alter table mytable add column email text not null ” collate nocase;;
Error: near “””: syntax error
It seems to be a problem with the single quotes after ‘not null’. Thanks
try this:
alter table mytable add column email text not null default ” collate nocase;
I try to restore and I get this error:
The ‘<' operator is reserved for future use.
why these errors occured,can get some explanation here,anybody
sqlite> .mode column;
Error: mode should be one of: column csv html insert line list tabs tcl
sqlite> .mode cloumn;
Error: mode should be one of: column csv html insert line list tabs tcl
When I am opening again sqlite3 previously stored data is gone(tables). everything is fine regarding permission.Why is that not being stored in hard drive?
Please make sure that your are opening the correct db file.
e.g sqlite3 sample.db
Nice cheat sheet, just enough of what I need. Thanx!
sir, below 2-3rd lines, should have no “;”
To fetch data from mytable by improving the formatting a little:
sqlite> .mode column; — should no ;
sqlite> .header on; — should no ;
sqlite> select * from mytable;