Databases and MySQL

Introduction

Databases are, simply put, places where data is stored and recalled in a non-volatile state. When we refer to a database, we typically mean a system to actually manage the stored data, called a DBMS (Data Base Management System).

The primary use of a database is storing data, but to do so to the fullest effect, the concepts of databases need to be fully understood. And those come in the form of an acronym called ACID (Atomicity, Consistency, Isolation, Durability). These will allow for the safe and proper storage and transferral of data. Without these ACID properties, everyday occurrences such as using computer systems to buy products would be difficult and the potential for inaccuracy would be huge. Imagine more than one person trying to buy the same size and color of a sweater at the same time -- a regular occurrence. The ACID properties make it possible for the merchant to keep these sweater purchasing transactions from overlapping each other -- saving the merchant from erroneous inventory and account balances.

ACID

Atomicity

If I were to use a single phrase to describe this word, it would be "all, or nothing". The word is based upon atoms, the supposedly smallest building blocks of the universe. Getting any smaller will become conceptual and irrelevant to whatever you're trying to do. And on a similar level, when you store data in a database, either all or none of the updates in a database should be available to the user(s) of whereever it is accessing. This includes adding, removing, or changing existing data.

Consistency

"Good musicians can play a piece well. Great musicians can do it again."

Among the same lines as the quote above, being consistent with your data is vital to storing it. This means that every instance of that data should and will be identical or else it is not worth the hassle. What good is storing data if it changes on you every time you boot up your computer?

Isolation

Isolation refers not to being able to manage data well internally, but rather to handling multiple external requests, such as multiple clients trying to add items to the database all at once. Isolating these separate calls to the database is a safeguard in any DBMS, to prevent the unintentional splicing and possible corruption of data.

Durability

Durability refers to the previous states of the database, in case something happens and data is corrupted or lost. Git does this extremely well; every committed change is stored in its history, and you can head back to any previously committed state at any point. It also should handle unintentional state loss, such as your application or disk crash, write or read failure, or even your entire computer crashing.

SQL

All of this talk about databases is fine, but we don't have any way to directly edit our database. Well, not without digging directly into our computers kernel¹, anyways. Which is why database languages exist. Today we'll be talking about SQL, short for Structured Query Language, which is a fairly old but still useful database language.

SQL has its own terminal, much like a bash terminal, where you can directly interact with your databases. To work with databases, we need to create one and "use" it, which is their way of scoping into a database to manage its data. We do this with CREATE DATABASE <name> and USE <name>.

From there we need to create what's called a table, which will define the structure of data stored within it. This is done with CREATE TABLE <name> (<clauses>);. More details on the (<clauses>) part in the code example below.

Queries

As said in its name, we use queries to manage data in SQL. We will primarily do this with SELECT/FROM clauses (the keywords for SQL are technically case insensitive, but it's customary to uppercase it).

CREATE DATABASE example; -- commands only end with a semi-colon, so you can write multi-line commands.

USE example; -- Scopes into database

CREATE TABLE items (
  id INTEGER PRIMARY KEY AUTO_INCREMENT, 
  -- An id property. This is universally useful in all SQL queries. INTEGER defines
  -- its datatype, PRIMARY KEY makes it so it must be a unique value and NOT NULL.
  -- AUTO_INCREMENT makes it so every new item added will have a unique id with no
  -- extra user input.

  itemname VARCHAR(25)
  -- The name of the item. VARCHAR() tells it to be a string, limited to the number in
  -- the parentheses, defaulted to 255.
);

SELECT * FROM items; -- This will pull all data from the items table.

This is a basic setup for a SQL database, but one glaring problem is that there is no data in this table yet. SQL has that covered, with INSERT INTO <table> (<columns>) VALUES (<values>);. Let's take the example above as our baseline.

CREATE DATABASE example;

USE example;

CREATE TABLE items (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  itemname VARCHAR(25)
);

INSERT INTO items (item) VALUES ('Apple'); 
-- Inserts an Apple into items table. We don't need to mess with id because SQL does
-- that for us.

SELECT * FROM items;

And just like that, we should get something along the lines of:

id | itemname
-------------
1  | Apple

What if you want to delete items from the table? Well, that's where DELETE queries come in. A DELETE query looks like DELETE FROM <table> WHERE <conditional clause>.

CREATE DATABASE example;

USE example;

CREATE TABLE items (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  itemname VARCHAR(25)
);

INSERT INTO items (item) VALUES ('Apple');
SELECT * FROM items;

DELETE FROM items WHERE itemname='Apple';
-- This deletes the apple from the items table.

Conclusion

Databases store data in non-volatile memory, using the ACID methods to keep the structure universal and to prevent data loss. We then use SQL to manage said database, using it to insert data, grab it for whatever we need, and even delete it, all in just a few lines.

Superscript References

1. Computer Kernels https://en.wikipedia.org/wiki/Kernel_(operating_system)