An Introduction To SQLite

SQLite, like MySQL, is a relational database management system (RDBMS).  With SQLite, your entire database is a single file on your local disk, meaning it is really portable.  However, it doesn’t have network capabilities built in.  Even though SQLite uses SQL, it is missing some basic SQL commands such as RIGHT OUTER JOIN and FOR EACH but it also has others built in.  Additionally, SQLite doesn’t have user management.  You’ll want to use SQLite if you want an embedded application or if you want your database to be really portable and you’ll want to avoid it if you are using it for a multi-user application or if you need a complicated database.

Installing SQLite

To install SQLite, go to the official SQLite download page and download the precompiled binaries for Windows: the dll and the SQLite tools.  Create a folder where you’d like SQLite to live.  For me, I chose E:\sqlite.  Unzip the precompiled binaries to this folder.  Next, add that path to your PATH environment variable.  Test to make sure this worked by opening your command prompt and typing

You should receive something like:

Creating A Database

If you just tested your environment variable by typing ‘sqlite3’ into your command line, go ahead and quit sqlite by typing .quit.  In order to create a database with SQLite, within the command line ‘cd’ to the directory where you’d like the database file to live and type:

sqlite3 databasename

I’m going to recreate my firearms database, so I want to create a database called ‘firearms’:

Creating A Table

Now, we can create a table.  The first table I’ll create will be called acquisition.  This table will need the columns:

Acquisition Table
Field Type Null Key Default Extra
acq_id int no primary null auto increment
acq_date date no null
customer_id int no null
gun_id int no null
transaction_date date no null
date_entered timestamp no current time
purchase_price float yes null

Some of the syntax will be different with SQLite than it is with MySQL.  To create this table with SQLite, the command is:

 

Note that INTEGER needs to be spelled out and to auto increment we use AUTOINCREMENT instead of MySQL’s syntax of auto_increment.  To see the tables in our database, use:

The output for me is:

To see the schema for a table, type:

Inserting values into a table is very similar in SQLite and MySQL.  I have a table called ‘caliber’ that has a schema:

to insert a value into this table, we can use:

Finally, to see all of the values in a table:

 

In order to delete a row from a table, we can use:

 

And checking to make sure it did delete:

This should be enough for you to get your SQLite database up and running with some tables and data.  If you have any questions, please let me know in the comments or contact me.

Leave a Reply

Your email address will not be published. Required fields are marked *