Everything You Need to Get Started With MySQL

Despite the advent of modern, NoSQL databases like MongoDB, Firebase and Redis in recent years, SQL databases still remain very popular among developers.

SQL (structured query language) is the language used to interact with data in a wide variety of  databases, including popular ones like MySQL, PostgreSQL, Oracle, and MS SQL Server. 

In this article, you'll learn everything you need to know to get started with MySQL, an open-source database that powers content management systems like WordPress, eCommerce platforms like Shopify, and social media platforms like Twitter.

You'll learn about relational databases and some of their key concepts, how to install and interact with MySQL database using the command-line, and the modern SQL syntax to create, read, update and delete data in MySQL.

Understanding Object Relational Mappers (ORMs)

Most developers using relational databases do not actually write raw SQL. More often they use libraries that perform object-relational mapping or ORM.

These libraries basically make your database tables appear like objects on the server-side of your site so you can use any object-oriented programming language of your choice to easily manipulate data. 

Examples of ORMs are Sequelize (JavaScript), Eloquent (Laravel), SQLAlchemy (Python) and Active Record (Ruby on Rails).

ORMs dispense with the need to write raw SQL code. Instead, you employ your knowledge of object-oriented programming to create, read, update and delete data on the SQL database. ORMs makes using relational databases easier and more intuitive.

Relational Database Management Systems (RDMS)

In essence, a relational database management system is comprised of two main components: the database and the query language.

The database itself is simply a collection of tables. In each table, you have your actual data organized in columns and rows, identical to a spreadsheet. Tables in a relational database can be linked—or related—based on data common to each of them.

The query language is used to manipulate and read data in the database. For most relational databases, the query language used to manipulate data is most often a variation of SQL.

Some key concepts in SQL databases

To explain the following SQL database concepts in a visual way, I'll make use of a database visualization tool called DrawSQL. First, we'll take a look at schemas and datatypes.

Schemas and Datatypes

You can think of a schema as a blueprint that defines the overall structure of a table and its relationship with other tables. 

Consider the schema of the following users table for example:

In the table image above, you'll notice that each row is a dictionary where the key represents a column in the database and the value represents the type of data that can be stored there.

There are many data types in MySQL, and data types vary across different SQL databases.

You'll also notice that the id key, which accepts integer values, also has a key in front to it. This indicates that id is defined with a primary key constraint. 

Therefore, the value of id on each column cannot be null and must be unique. As a result, two or more users can never share the same primary key and every row can be identified by its primary key.

Together, these rules constitute the schema for the users table. 

Let's go deeper on constraints in MySQL databases.

Constraints

We earlier saw the primary key constraint, which ensures that the value of a specified key is unique for every column across the table. 

In MySQL, constraints are rules that allow or restrict what values will be stored in the table. They help to limit the type of data that will be inserted inside the table, ensuring data accuracy and integrity inside the table.

The following constraints are commonly used in MySQL:

• 
  NOT NULL: ensures that a column cannot have a NULL value


• 
  UNIQUE: ensures that all values in a column are different from each other


• 
  PRIMARY KEY: a combination of a NOT NULL and UNIQUE—uniquely identifies each row in a table


• 
  FOREIGN KEY: used to link two tables together


• 
  CREATE INDEX: used to create and retrieve data from the database very quickly


• 
  CHECK: ensures that the values in a column satisfies a specified condition


• 
  DEFAULT: sets a default value for a column if no value is specified

Database normalization

To add more context, let's assume that the users table is for storing the registered users in an online shopping website.

Typically, in the database of an online shopping site, there has to be a products table and a carts table. products will contain all the products available to the user, and carts will contain the specific items that a particular user intends to buy, along with the quantity of each item.

All of the tables so far created are as follows: 

All of these tables are unique entities. This means that the carts table for example doesn't need to store any information about the user.

Technically, there is nothing stopping us from merging the carts data into the users table. After all, both sets of information are related to each other (a user owns a cart). However, this approach will eventually lead to congested tables that can be quite difficult to manage. 

Instead, we took a more sustainable approach by creating a separate carts table to house all cart-related data. We then created a reference to the other tables by placing the foreign keys user_id and product_id on the carts table.

That way, cart can access any information on the user who owns the cart and the products present in the cart despite not having any of this information physically stored on it.

Installing MySQL

Now that we know a little bit about MySQL on a higher level, let's get hands-on by installing MySQL on our system.

To install MySQL on your system, go to the MySQL Community Downloads page and download the installer for MySQL. 

After the download is complete, run the installer and complete the setup wizard to install MySQL.

When choosing a setup type, make sure you include the MySQL server product. I recommend going with the Developer Default option.

On successful installation, you should then have access to the MySQL commands from the command line.

To use MySQL from the command line, however, you'll need to first authenticate yourself. Run the following command and fill in your username in the placeholder:

mysql -u <your username> -p

You'll be prompted to input your admin password, and if both details are correct, you'll be granted permission to access MySQL from the command line.

Keep in mind that the default username for MySQL is root, the password is empty by default.

Common SQL Operations in MySQL

Creating a Database

To create a database in MySQL, we use the CREATE DATABASE statement followed by the database name:

CREATE DATABASE shopping_site;

A statement is a code that tells the system to do something. SQL statements always end with a semi-colon. The capitalized words are reserved SQL keywords that SQL will interpret to do something. 

Running the above command will create an empty database named shopping_site in MySQL.

To view all the databases present in your MySQL instance, run SHOW DATABASE.

Creating a Table in a Database

To create a table in your new database, use the CREATE TABLE statement followed by the table name and a parenthesis containing the list of columns we want to include on this table:

CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

For every column in users, we specified the data type for the column. We also added a bunch of constraints to the id and email columns.

Running the command will create an empty but structured table called users inside your database.

Creating New Records in A Table

To create a new record on your table, use the INSERT INTO statement followed by the table you want to insert the values into:

INSERT INTO users(first_name, last_name, email, password)
VALUES(
    'Kingsley',
    'Ubah',
    '[email protected]',
    '12345678'
);

In the first parenthesis, we specified the columns we want to insert values into. Then, in the second parenthesis, we specified the values we want to insert following the correct order.

Running the command will insert those values into the specified columns in the users table.

Reading from The Table

To query for data from your database, use the SELECT statement. 

For example, if we want to select everything from our users table, we use the following statement:

SELECT * FROM users;

The asterisk (*) represents everything in our database. This statement will return the entire table along with every single column.

If we only want a subset of columns, say id and email, we specify the columns in place of the asterisk:

SELECT id, email FROM users;

When it comes to selecting data, there is no limit to what we can do.

Dropping a table

To completely delete a table from MySQL, use the DROP statement followed by the table name:

DROP TABLE users;

Now you need to be careful when using this command! It will permanently delete all the data in the users table. Also, don't confuse it with the DROP DATABASE statement which deletes your entire database. 

Wrapping Up

MySQL is an open-source relational database management system that powers a significant part of the web even today. The structured query language or SQL is used to manipulate and store data within a MySQL database.

In this post, we went over some key concepts of SQL, including ORMs, schemas, constraints and database normalization. We also went over the installation and setup process for MySQL. 

Finally, we covered some of the SQL statements responsible for manipulating data in MySQL.