The other day I learned all about SQL and databases and how you can pronounce SQL like “ess-que-ell” or “sequel” and both are correct. Today I’m focusing on learning SQLite.
SQL is a programming language used to communicate with a database. SQLite is a library that implements a self-contained, server-less, zero-configuration, transactional SQL database engine. There are lots of other SQL software libraries, such as MySQL and PostgreSQL. I’m learning SQLite because it is the most widely deployed SQL database engine in the world and also because Dev Bootcamp told me to.
sqlite3 is the command line utility for the SQLite library that allows users to manually enter and execute SQL commands against an SQLite database.
To get started with sqlite3, you first need to download and install sqlite3. If you have a Mac, sqlite3 should come pre-installed in the latest version of OS X. You can check out what version of sqlite3 you have by simply typing $sqlite3 into the Terminal. You should see something like this:
We can create a new database called “test” with the command sqlite3 test.db. If the “test” database had already existed on my computer, that command would have opened it. Because it didn’t exist yet, that command created it.
Then type .tables to see if there are any tables currently in that database. There are none (because we didn’t create any yet) so it returns nothing. You can use .exit at any time to terminate the interactive session of the sqlite3 command line tool.
So now let’s try to create a database called “movies” with a table called “actors” in it, and populate that table with the names of some actors. Again, we can create a database and name it “movies” just by typing sqlite3 movies.db into the Terminal.
To create our actors table, we need to use the command create table, give the table a name (in this case “actors”), and then include names for all of the columns in that table.
Syntax => create table name(col1, col2, col3…coln);
We already know about using primary keys in databases, so let’s include one of those first. It will be an integer, so we set integer as that column’s recommended data type(*see list). Then we’ll name the next column FirstName and tell it to expect a string of text, and the last column LastName which should also expect text.
At this point we’re just telling the table how many columns to make, what they should be named and what kind of data we recommend to be stored in each column.
We can populate the rows of our table with data using the statement insert into. We have to specify which table we want to insert data into, then pass in the names of the columns in parentheses, then type values and pass in the data we want to populate each column. In this case we’re passing in strings of names as our values.
Syntax => insert into tablename (column1, column2,…columnN) values (value1, value2,…valueN);
After we finish creating and populating the table, we can type .tables into the Terminal again and this time instead of returning nothing, it will return our actors table.
The command select * from tablename will return all of the values as we’ve inserted (the asterisk meaning all, just like in regular expressions). If you just want a specific column, such as FirstName, use the command select FirstName from actors.
To format the results of select and make them look like a nice and neat, type the commands .header on and .mode column into the Terminal before you issue your select command. (For the next photo I used a different table with more data to show how nice everything looks all lined up in columns and rows). The command .show will list all of the various sqlite3 settings that you can play around with.
The command .schema will return the blueprint that we laid out when we created the table.
Now let’s say we don’t need our actors table any more and we want to get rid of it. The command drop table will completely delete the table from the database schema. The table can not be recovered and any trigger or index associated with the table will also be deleted.
Also, now I actually understand this xkcd comic:
*What data types can we use in a table?
- Null – no value
- Integer – whole numbers (no decimals)
- Real – floating point numbers (decimals)
- Text – a string of text in quotation marks
- Char(num) – part of text, limits the number of characters allowed
- Blob – aka Binary Large Object, is a collection of binary data stored as a single entity. Blobs are typically images, audio or other multimedia objects, though sometimes binary executable code is stored as a blob.
- Boolean values are stored as integers 0 (false) and 1 (true)
- SQLite does not have a storage class set aside for storing dates and/or times, it can store dates and times as text values (“YYYY-MM-DD HH:MM:SS.SSS”), real values using Julian day numbers (used primarily by astronomers, it’s the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar), or integer values using Unix Time (the number of seconds since 1970-01-01 00:00:00 UTC).
What other commands are there for sqlite3?
The command .help will show you a list of all of the metacommands available to you:
A note about capitalization: Most of the tutorials I’ve seen have capitalized commands like SELECT, CREATE, DROP and LIKE, even though most of them also say that SQL is case-insensitive. All of the commands I used were in lowercase, but I guess it’s best practice to upcase them all.
- SQLite3 command line tool
- Tutorials Point – SQLite Quick Guide
- SQLite data types
- Converting date and time values
*As always, if you see an error please let me know! I’m still learning!