Databases (MySQL)

Databases make (almost) every piece of software you use possible. In fact, if you consider how we consume software it usually goes a little something like this:

1. You install an app because you have some need to store some data or have some data processed for you
2. You open the app and input the data
3. It sends it over a network to a remote system which processes the raw data into some result
4. The remote software (probably) stores the original data and the transformed data
5. It then sends back to you, the client, the transformed results

It's all about data from start to finish. Computers were literally designed to process vast amounts of data - databases came after the fact and now allows us to store the data forever and retreive it extremely quickly.

Let's learn about about databases.

Subjects

1. Databases Overview
2. SQLite
3. SQL
4. MySQL

Curated Materials

These resources are going to get you an excellent overview of the database types, concepts, and even writing SQL. Once you've learned these topics, we'll eventually be writing our own SQL for the Tightly application in the next section.

Databases Overview

We're going to learn about the concept that is a database. What they are, why they're useful, and more. There are several engine types you need to be aware of. You most certainly don't need to know how-to use (all) of these engines right now, but in time you may come across them and have a need to study for them to perform your duties.

Keep in mind that relational databases like MySQL, PostgreSQL, SQLite, etc., are the most widely used in the world.

Introduction to Databases

Checkout this amazing guide from Digital Ocean. It explains what a database is, explores the various database engine types, and more.

• Introduction to Databases

Next we have a guide from Digital Ocean specifically on relational databases. Again, these are the most widedly used database engines in the world. You're likely going to see them the most throughout your career.

• Understanding Relational Databases

And then we have a comparison between of the various NoSQL database systems and models. These are less frequently used, but they're still very popular and still out there. They're also possibly growing in popularity, too.

• A comparison of NoSQL Databases

SQLite

Now that you've got a good idea of databases, engines, etc., it's time to focus on learning a few fundamentals of playing around with a database and learning SQL. To make this easier, we'll take a look at SQLite. It's very easy to install and operate, and requires very little to work with.

Here's a great article that looks at how SQLite compares to other databases engines.

But before you can play around with SQLite, you're going to have to install SQLite first, of course. You'll need both SQLite and the command-line interface so you can create databases and interact with them.

Windows

For Windows, use this link: SQLite for Windows.

The page this link can be found on is here. It's a bit of a messy page, so you'll have to look down the page a bit for the (latest) Window specific link, but we're included it above to make this easier.

Once it's installed, you should be able to run the sqlite3 command-line tool to create and interact with SQLite databases.

Ubuntu Linux

Digital Ocean have a guide for installing SQLite on Ubuntu. Use this to install SQLite if you're using Ubuntu or most other Linux variations.

macOS

We highly recommend you install Homebrew when using macOS. It really is, "The Missing Package Manager for macOS (or Linux)."

SQL

Now that you've got SQLite installed, it's time to actually start learning SQL. SQLite will make this easier to achieve has you don't need a client/server configuration. That's because SQLite operates from a single, flat file.

First, let's understand:

• Why you should learn SQL

And then follow this tutorial so you can learn SQLite and SQL:

• SQLite Tutorial

The above tutorial has you download and work with an existing database. Make sure you remember that.

MySQL

SQLite is an amazing database, but it's not capable of taking in network connections from remote clients. That can be a problem if you have several applications all trying to access and use the data in the same database. That's where other engines come into play. Most other databases you'll come across will be networked engines, like MySQL.

It's important that you learn about the fundamentals of installing, managing and using MySQL as it's a very common database. But as we always say: learn concepts, not technologies. So although we're pushing you towards MySQL, as it's very popular and used widely, it's simply one of many options.

First, get MySQL installed on your platform:

• Install MySQL

There's an installer for Windows. Linux has packages available in most repositories that ship with most Linux distributions. For macOS, use Homebrew.

Next, get MySQL Workbench, which will make it easier to connect to and manage MySQL. It's not a tool you'll use often and the database tends to be managed by the developer, DBA, and via code connecting to the database for you, not to mention other tools are used to manage databases.

• About MySQL Workbench

And it can be downloaded from it:

• https://dev.mysql.com/downloads/workbench/

Once you have it installed, you can then connect it to your local MySQL connection. The following video goes over everything MySQL Workbench related.

Projects

Challenges

Next