Working With MySQL

A brief introduction to the MySQL database and the query language, SQL.

MySQL — The Relational Database

MySQL is a relational database management system (RDBMS). So, what is a database? A database is an application that stores a collection of data from various sources. It has the features like ‘Creating the database’, ‘Searching the database’, ‘Modifying the database’, ‘Managing the database’ and so on.

MySQL provides an implementation for the SQL database. SQL is a query language designed for managing data held in a database. MySQL is open-source, has a wide community and support all over the world. Since MySQL is RDBMS, it implements a database with tables, columns, rows, and indexes.

What this article is about

This article gives a brief introduction to MySQL and its implementation on the Windows platform. Then we will get to know about databases and tables.

Then we will come to know about the CRUD (CREATE, READ, UPDATE & DELETE) operations of MySQL. Then we will see some helpful operators like boolean, logical, comparison operators and aggregation functions for performing certain operations and functions on the data. After that, we will see how grouping and ordering are working. Without a further due let’s get started off.

Setting the environment up

In here we will work with MySQL Community Server 8.0.13. Download the installer and install it. We can work with MySQL in two ways —’ Command Line Client’ & ‘Workbench’. We will use both in this article depending upon the ease to work with.

After installing it, we can check if the installation is done or not by searching for MySQL Command Line Client. After opening it, we will be asked for the password that we set while installing. After entering the correct password for the MySQL password the console will produce output as follows.

This means the installation is successful. Let’ get moving to the next section.

Database & Table Creation

A MySQL server can have multiple databases and each database can have multiple tables. Type the following the printout the existing databases.

show databases;

We can see there are four databases by default. Now we will see how to create a new database.

CREATE DATABASE new;

We can see the ‘new’ database has been successfully created.

In order to work in a database, we have to type in USE <database_name>;, where ‘database_name’ is the name of the database we intend to work on. Let’s create a table by the following syntax.

CREATE TABLE <table_name> ( column1 datatype, column2 datatype2, ....);

Let’s create a table name ‘first_table’ with five columns — ‘id’, ‘movie’, ‘director’, ‘genre’ as follows.

CREATE TABLE first_table (id INT NOT NULL, movie VARCHAR(50) NOT NULL, director VARCHAR(50), genre VARCHAR(50), PRIMARY KEY(id));

Here the first column is an integer datatype and by ‘NOT NULL’ means the values in the column should not be a null value, that a value should be present in the column for all rows of the table. Second column’s datatype is VARCHAR(), that it is a string data type with the length inside the parenthesis. The last one ‘PRIMARY KEY’ is not a column but it determines which of all columns should be a primary key, that each row for that column should be unique. One table should contain only one primary. Here ‘id’ is the primary key.

CRUD Operations

CRUD stands for CREATE, READ, UPDATE & DELETE. For every database, we have data, also called as records, to be inserted, read, updated or deleted from a table. CREATE means inserting a record into the table, READ means retrieve the record from the table, UPDATE means modifying the existing record in the table and DELETE means deleting a record from the table. We will take a look at these operations now.

CREATE

To insert a new record into a table in MySQL we have the following format.

INSERT INTO table_name (column1, column2, column3,....) VALUES (value1, value2, value3,....);

At the same time if we have all the values in the same order as the column in the table then we don’t have to write down the column names. Let’s see an example in the MySQL Workbench.

INSERT INTO first_table VALUES (1, "Troy", "Wolfgang Peterson", "Historical Fiction");

If we want to insert multiple values at the same time then we can chain the values as follows.

INSERT INTO first_table VALUES 
(2, "Inception", "Christopher Nolan", "Science Fiction"),
(3, "The Greatest Showman, "Michael Gracey", "Musical Drama");

As we can see two rows have been successfully written into our table. Next section we will see how we can retrieve data residing in a table.

READ

When we want to retrieve the records from a table we use the ‘SELECT’ clause as follows.

SELECT column1, colum2, column3... FROM table_name; OR

SELECT * FROM table_name;

The data will be selected from the table mentioned after the ‘FROM’ clause.

The first syntax is to retrieve only the columns we wanted and the second one is to select all the columns using the wildcard ‘*’. Let’s see an example from MySQL workbench.

We can see all the records with all the columns have been retrieved. Now let’s select only the ‘movie’ and ‘genre’ columns.

Here, we have only retrieved the ‘movie’ and ‘genre’ columns of all the records.

UPDATE

Sometimes, we will have to update the existing records, then we use the UPDATE clause. Here it goes.

UPDATE table_name 
SET column1 = value1, column2 = value2...
WHERE condition

We need to mention only the names columns we update the values. If we intend to change the value of one column only then we have to use that column only.

‘WHERE’ is a clause used to filter records. It retrieves only those records that fulfill a specified condition. Let’s see an example of ‘WHERE’ in the SELECT clause.

Here we want to select the record which has the id value 3.

Let’s get back to updating. Here is an example where we change the ‘genre’ column ‘Historical Fiction’ of ‘Troy’ to ‘War Film’.

DELETE

To delete one or more records we use the DELETE clause along with the WHERE clause. Here is the syntax for deleting the records.

DELETE FROM table_name WHERE condition;

Let’s see an example in action.

Here we successfully deleted the record in which its id is 2.

Logical and Comparative Operators

Now we will load a sample SQL database named ‘sampledatabase.sql’ load it into our Workbench by going to File ->Open SQL Script->Load the ‘.sql’ file from the folder where it resides. Execute the file in order to insert the data into the various tables in the database.

Logical Operators

These are the operators used in SQL language to perform the logical operations such AND, OR, NOT etc. We will execute some commands to see how these work. Now we are going to use the ‘customers’ table of the sampledatabse.sql imported into our database ‘new’.

SELECT * FROM customers where country = "Australia" AND state = "Victoria";

This code returns the records where the ‘country’ column’s value is ‘Australia’ and ‘state’ column’s value is ‘Victoria’. Other than these all the records are omitted. Let’s see the working of ‘OR’.

SELECT * FROM customers WHERE country = "Norway" OR country = "France";

This code extracts all the records if the value of the ‘country’ column is either ‘France’ or ‘Norway’.

Comparison Operators

The common comparison operators are less than ‘<’, greater than ‘>’, less than or equal to ‘<=’, greater than or equal to ‘>=’ or equal to ‘=’.

Let’s see an example.

This example retrieves the ‘customerNumber’, ‘customerName’, ‘column’, and ‘creditLimit’ columns from the table where the value in the ‘creditLimit’ is greater than or equal to 100,000.

Aggregate Functions

These functions operate on all the rows to give a combined or single result based on the criteria specified. The mostly used aggregate functions are COUNT(), SUM(), AVG(), MIN(), MAX().

The COUNT() function counts the total number of rows and gives a single output. If grouping is applied, more on this next section, it gives multiple results for each group. The behavior of aggregate functions is as same as the behavior of the COUNT() function in the grouping.

SUM() computes the total numerical value of the column, AVG() computes the average of values in the column, MIN() and MAX() computes the minimum and maximum values of the columns respectively.

Let’s see the action of these in the ‘creditLimit’ column of the ‘customers’ table.

SELECT COUNT(*) AS total_num_of_rows, SUM(creditLimit) AS total_sum, MIN(creditLimit) AS minimum, MAX(creditLimit) AS maximum FROM customers;

It shows the total number of columns is 122, the total sum of the values in the ‘creditLimit’ column is 8,254,400, the minimum is 0.00 and the maximum is 227,600. You can see we have the ‘AS’ clause. This is an alias operator in SQL to name the columns of the result. The name that will be presented in the output comes after the ‘AS’ operator. If aliasing is not done then the name of the columns will be what we used in the code, for eg, COUNT(*), SUM(creditLimit)..etc.

Grouping and Ordering the records

Grouping

‘GROUP BY’ is the clause to arrange identical data into groups to yield the result by one or more columns. For eg, consider the case of customers who are purchasing more than once. If we want to know total how much each customer spent can be found out using grouping. Let’s see an example to understand it more. Now, we will use the ‘payments’ table from the ‘sampledatabase.sql’.

SELECT customerNumber, COUNT(amount) FROM payments GROUP BY customerNumber;

This yields the result with the total amount spent by each customer. Earlier in the table, each customer had multiple transactions, now we have the total money spent by each one of them. We can also find the average, minimum or maximum money spent by each customer using grouping.

Ordering or Sorting

‘ORDER BY’ is the clause to sort the result in ascending or descending order. ‘ASC’ and ‘DESC’ are the keywords used to sort a column in the ascending and descending order. After the ‘ORDER BY’ clause, we have to specify the column with which we need to sort and it comes after the ‘GROUP BY’ clause. Let’s see it in action with the last example.

SELECT customerNumber, AVG(amount) as totalAmount FROM payments GROUP BY customerNumber ORDER BY totalAmount DESC;

Here we have found the average of the amount spent by each customer and sorted descending by the ‘averageAmount’ column.

That is all for this article. Please share your thoughts and suggestion in the comments.