SQLite is a minimalistic implementation of a relational database that supports most of SQL, although not all of it. It is less powerful than, for example, PostgreSQL and MySQL, but it’s also super lightweight, and great for learning, experiments, and getting started quickly.
We are going to use the database SQLite in our examples, because it’s the least hassle to set it up. So you want to make sure you have it installed, many operating systems have it preinstalled.
Check if it’s installed by running this in your terminal:
$ sqlite3 --version
If that outputs a version string then SQLite is installed. If it’s not then
you’ll see something like
command not found: sqlite3. In that case Mislav has
written up some nice instructions over here.
SQLite comes with a handy command line tool that one can use to create databases and interact with them. It also has an interactive shell: Just like with IRB (where we can execute Ruby code interactively) we can log into the SQLite database, and execute SQL statements interactively.
Let’s try it:
$ sqlite3 members
This should put you into the interactive SQLite shell, and look something like this:
SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite>
sqlite> waits for your input. If you type an SQL command and hit
return it will execute it.
Let’s create our
sqlite> CREATE TABLE members (id INTEGER PRIMARY KEY, name VARCHAR(255), joined_on DATE);
If this does not output an error, then the command was successful.
The command says that we’d like to create a table
member with 3 columns
joined_on. The column
id is supposed to be an integer column,
and we’d like to use it as our primary key (meaning that it will be unique, and
it will autoincrement the id for us). The column
name is a string column, and
values can be 255 characters long. And
joined_at is a date column.
Cool. So we’ve just created a table in our database.
We can list our tables like so:
sqlite> .tables members
And we can check the structure (schema) of our
members table like so:
sqlite> .schema members CREATE TABLE members (id INTEGER PRIMARY KEY, name VARCHAR(255), joined_on DATE);
Now let’s insert a row to the table:
sqlite> INSERT INTO members(name, joined_on) VALUES('Anja', '2013-06-24');
Again, if this does not output anything, that means our command was successful.
We can now retrieve the data using a
SELECT statement like so:
sqlite> SELECT * FROM members; 1|Anja|2013-06-24
So this displays on row.
Now should be a good time to do some exercises on SQL.