Most people think that relational databases are boring, but they’re awesome! Why else would they be one of the most used technologies in the world. You’re probably not aware of it, but you’re using one right now. Surprised? The web browser or app that you’re using to watch this uses a database to store. You don’t use the database directly, but it’s in there handling your bookmarks, browsing history, URL recommendations etc.
When you browse the internet, there’s a good chance that the websites you’re visiting are database powered. For example, WordPress uses a database, and it accounts for about about 43% of the world’s websites. Online stores, banking websites, cloud storage services. The list of systems we interact with daily that use databases goes on and on…
Why are relational databases everywhere? To answer that we need to look at how they work. Relational databases are used via S.Q.L, or Structured Query Language. SQL is a bit of a mouthful, so everyone abbreviates it to sequel (as in,. movie sequel).
Why sequel? When I look at SQL I se SeQuL. Hmm. Sounds a lot like sukkel in Dutch, which means: someone who is clumsy or stupid. Maybe sequel is better, although sukkel is funnier.
Taking a Peek at KSD’s MySQL Database
Lets take a peek at the MySQL database behind my website. It’s a WordPress website, so all the pages, comments and other stuff are stored in the database. Here’s a screenshot of the table that stores the blog posts. Each page is a row in this table, which various fields such as the ID, author, date, post contents, title, etc.
And here’s the table for comments. Like the posts table it has an ID, author, content, but it also has a special column for the post ID called comment_post_ID (see below). This is where the “relational” part comes in. The comments were made on a specific blog post, and that post ID column records which post it belongs to.
Searching the Database
Lets do some SQL queries. First, let’s look up this blog post: https://keasigmadelta.com/blog/building-a-cross-platform-c-gui-app-with-cmake-raylib-and-dear-imgui/. To do this we ask the database to search for all entries where the post name matches the last bit of the URL:
SELECT * FROM `*_posts` WHERE post_name = 'building-a-cross-platform-c-gui-app-with-cmake-raylib-and-dear-imgui' AND post_type = 'post'
NOTE: *_posts should be replaced with the actual name of the posts table. I’m redacting the full name for security purposes (security via obscurity).
This returns one entry with the page that we want:
Now lets see if this blog post has any comments. To do this we take the page’s ID (10767) and use it in a new query on the *_comments table:
SELECT * FROM `*_comments` WHERE comment_post_ID = 8890
It’s found two comments on that blog post. One of them is mine:
This is exactly what WordPress does when you visit the blog post via your web-browser. It runs queries like these to look up the necessary data, and then generates the blog post’s HTML code.
Why Are Databases Everywhere?
Why are databases everywhere? Because they’re a great way to store and look up data with complex relationships. My little demo above is just the tip of the iceberg of what’s possible.
In the next video we’ll start using SQL in our C/C++ code via SQLite. See you then.