A brief introduction to Databases and MySQL

Thanks to completing an IT GNVQ during my secondary school days, databases aren’t all that new to me. I had experience querying a relational database in Microsoft Access already. But for the purpose of this post, I’ll revisit some core principles. The first to example is the types of database, which I’ll use an infographic to explain;

Now what we’ll be looking this post is the relational model. This works by having a series of tables linked by public and foreign keys. Each of these keys has to be completely unique.

When we update our database, we term this as a transaction. This means one or more changes are performed to a database. To commit a change we need to ensure transactions follow the four standard principles of atomicity, consistency, isolation, and durability.

Before we go into talking about querying our relational database, let’s get into the NULL value. When searching we can use IS NULL and IS NOT NULL, but beyond this, there are several useful things to know about this value;

Now to perhaps one of the most powerful elements of databases, querying. Querying a database allows us to extract the most relevant and desired information we want out of, potentially, huge datasets. The syntax of how we query in MySQL is as follows;

SELECT
FROM
WHERE
ORDER BY

Here you can see a little example from MySQL workbench I installed on my machine.

But if we wanted to query something a little more complicated, we also need to know ‘Operator precedence’ which is to say, what is the order in which the query is resolved.

As for comments, I thought it best to see them in action in my own MySQL workbench!

Depending on what you might want to do in an SQL database, there are a number of functions (aggregate, string, date-time and control flow) you can use. On top of this, you also have a lot of control about how you choose to display this data. I'm just going to cheat and include an excellent cheat sheet I found that more accurately and succinctly demonstrates this!