Is MySQL a good solution for reporting and data analytics? Is MySQL the right solution for your business intelligence needs?

Sequel to the article on “Understanding Relational Database Management Systems.” this article will attempt to answer the question of how MySQL fits into the realm of data analytics by enumerating some of its characteristics.

What is MySQL?

MySQL is an open-source relational database management system. It is a database management system that allows you to manage relational databases. It is an open-source software currently backed and owned by Oracle Corporation. Open-source software means you can use MySQL without paying a dime.

Type of Data

What type of data are you analyzing? MySQL is designed to manage relational data. If you have a lot of qualitative data (like social media posts, images, or bodies of text), do not consider MySQL.

SO LET’S GET STARTED …..

Introduction to Database

You deal with data every day.

When you want to listen to your favourite songs, you open your playlist from your smartphone. In this case, the playlist is a database.

When you take a photo and upload it to your account on a social network like Facebook, your photo gallery is a database.

So what is a database? A Database is merely a structured collection of data. The data relating to each other by nature, e.g. a product belongs to a product category and associated with multiple tags. Therefore, we use the term relational database.

In the relational database, we model data like products, categories, tags, etc., using tables. A table contains columns and rows. It is like a spreadsheet. A table may relate to another table using a relationship.

Querying a Relational Database

This article will describe how to write queries in MySQL that will allow us to access the data and relationships that are locked away within a RDMS.

But first, we need to install the MySQL server and Workbench.

How to Install MySQL

In order to successfully install the MySQL server, you need to follow these steps or guidelines:

Step 1: Use Official MySQL website

Go to the official website of MySQL and download the community server edition software. Here, you will see the option to choose the Operating System, such as Windows. 

Step 2: Download Community Server

Next, there are two options available to download the setup. Choose the version number for the MySQL community server, preferably the latest version. Choose the MySQL INSTALLER COMMUNITY VERSION.

MySQL Installer

Installing MySQL on Windows

Step 1: Download Set-up

After downloading the setup, unzip it anywhere and double click the MSI installer .exe file. It will give the following screen:

MySQL installer – community

Step 2: Choose Set-up

In the next wizard, choose the Setup Type. There are several types available, and you need to choose the appropriate option to install MySQL products and features. Here, we are going to select the Full option and click on the Next button.

Set-up prompt

Step 3: Requirements

Once we click on the Next button, it may give information about some features that may fail to install on your system due to a lack of requirements. We can resolve them by clicking on the Execute button that will install all requirements automatically or can skip them. Now, click on the Next button.

MySQL requirements

Step 4: Confirmation Box

In the next wizard, we will see a dialog box that asks for our confirmation of a few products not getting installed. Here, we have to click on the Yes button.

MySQl installer dialog box

After clicking on the Yes button, we will see the list of the products which are going to be installed. So, if we need all products, click on the Execute button.

Product installation

Step 5: Product Installation

Once we click on the Execute button, it will download and install all the products. After completing the installation, click on the Next button

MySQL installation completion

Step 6: Product Configuration

In the next wizard, we need to configure the MySQL Server and Router. Here, I am not going to configure the Router because there is no need to use it with MySQL. We are going to show you how to configure the server only. Now, click on the Next button.

Product configuration

Step 7: Server Configuration

As soon as you click on the Next button, you can see the screen below. Here, we have to configure the MySQL Server. Now, choose the Standalone MySQL Server/Classic MySQL Replication option and click on Next. Here, you can also choose the InnoDB Cluster based on your needs.

MySQL server installation

Step 8: Configuration type and connectivity

Here, we are going to select the Config Type as ‘Development Machine’ and Connectivity as TCP/IP, and Port Number is 3306, then click on Next. This is essentially the default option when installing on Windows.

MySQL configuration type

Step 9: Authentication method

Now, select the Authentication Method and click on Next. Here, I am going to select the first option.

Authentication method

Step 10: Root password creation

The next screen will ask you to create your MySQL Root Password (make sure it is a password that is easy to remember because you will always be asked for this password each time you want to access the MySQL Database). After filling the password details, click on the Next button.

Root Password creation

Step 11: Windows service configuration

The next screen will ask you to configure the Windows Service to start the server. Keep the default setup and click on the Next button.

Windows service configuration

Step 12: Apply configuration

In the next wizard, the system will ask you to apply the Server Configuration. If you agree with this configuration, click on the Execute button.

Server configuration application

Step 13: Completion

Once the configuration has completed, you will get the screen below. Now, click on the Finish button to continue.

Apply configuration

Step 14: Product configuration completion

In the next screen, you can see that the Product Configuration is completed. Keep the default setting and click on the Next button to complete the MySQL package installation.

MySQL product configuration

Step 15: Router configuration

In the next wizard, we can choose to configure the Router. So click on Finish and then click the Next button.

Router configuration

Step 16: server connection

In the next wizard, we will see the Connect to Server option. Here, we have to mention the root password, which we had set in the previous steps.

Server connection

In this screen, it is also required to check whether the connection is successful or not by clicking on the Check button. If the connection is successful, click on the Execute button. Now, the configuration is complete, click on Next.

Step 17: Apply configurations

In the next wizard, select the applied configurations and click on the Execute button.

Apply configuration

Step 18: Finish button

After completing the above step, we will get the following screen. Here, click on the Finish button.

Finish button

Step 19: Installation completion

Now, the MySQL installation is complete. Click on the Finish button.

Installation completion

Getting set up

We will be using Vanni Manufacturing Company’s database for this article to showcase examples of how to use basic SQL statements and clauses.

Download Database 321 Downloads

Accessing the Vannimanufacturing MySQL Database

STEP 1: Run MySQL Workbench

On your Desktop screen, place your cursor on the search bar and type “ workbench” and click open or double-click on the MySQL workbench to run the program.

Command prompt

STEP 2: Access the Vannimanufacturing Database

Now click the local instance bar at bottom-left of the page and type-in your Root Password (The password you created whilst installing the MySQL database)

MySQL workbench

Once the program has finished loading, your screen will display the MySQL Workbench like the image below:

MySQL workbench overview

STEP 3: Run SQL script

Click the file tab at the far left corner of the MySQL workbench and click Run SQL script.

Run SQL script

STEP 4: Locate the Database

Locate where you saved the Vannimanufacturing Database and click on it in the pop-up prompt that shows up on your screen and click open.

Pop-up prompt

STEP 5: Click Run

After you have opened the Vannimanufacturing Database, A Run SQL Script command prompt will pop-up on your screen. Click RUN.

Run SQL script

STEP 6: Refresh

Once the Script is done running. Click the close button , then place your cursor atop the refresh “icon” that is right-next to the SCHEMAS title and click it in order to view your Vannimanufacturing Database.

Schema

Yep…Now we are ready to get started on the good stuff..Let’s go.

Querying the database

In order to access a particular database on the MySQL server, we have the option of using either of the two ways mentioned below. We can access the Vannimanufacturing Database either by Double-clicking the name of the Database or by using the “ USE” Statement.

Let’s take a look at the merits of both options individually.

The USE statement

This is used to select a database and perform SQL operations in that database. The database remains the default until end of session or execution. The scenario for using the USE statement to activate a database is when you have multiple databases in your MySQL and you want to query a particular database for that period.

USE statement

Whilst the USE statement has its merit, the other option is far easier and user-friendly.

Double – Click

It is simply double-clicking on the name of the Database you want to perform SQL operations on. Even if you have multiple databases in your MySQL server, all you have to do is double-click on the Database you wish to query for that session.

Once either of the above mentioned methods of selecting a database has been applied, the database name format will be boldened by the MySQL server to serve as an indicator of its usage or activation.

Note: To execute any query in the MySQL workbench, you either use the keyboard shortcut “ctrl+shift+enter” or you click on the “yellow thunder icon” atop your scripting pane.

Yellow thunder-icon

The Select statement

The SELECT statement is used to retrieve or request data from MySQL.

The FROM clause is used to list the tables and any joins required for the SQL statement.

The semicolon (;) is the statement delimiter. It specifies the end of a statement.

In Addition, commas (,) are used to separate multiple columns. It is best practice to write your statements in capital letters (e.g. SELECT) while you write your columns (e.g. customername) or expressions (e.g. quantity*price) in small letters.

The Select Syntax

MySQL query

Select All -using asterisk (*)

In this case the asterisk character (*) means all columns. We’re selecting the Customers table, and there is no condition attached, which means we will be selecting all columns and all records (i.e. all the data) from that table.

To select all of the columns from our Customers table, we use the statement:

SELECT statement

SELECT query output

If we just want specific attributes or columns from the table, we can name those instead of using the asterisk.

For example:

SELECT specified attributes

SELECT columns query output

Select using Date

Let’s take a look how to query using dates :

WHERE clause using datesSelect Statement using date output

Using conditions

In most cases, we won’t want to select all the records in a table, but we will want to retrieve particular records that meet some condition. In these cases, we will need to use a WHERE clause.

The Where Clause

The WHERE clause allows us to set a condition.

For example, We will select all details from the employees table where the job title sales rep:

WHERE clause

WHERE clause query output

Note: The basic syntax for writing strings (text) and dates (2020-08-23) is to encapsulate them with single quotes (‘ ’). As can be seen from the above examples.

Do you know that you are not just limited to the equals to (=) operator?

Let’s take a look at other operators that we have available.

The SQL ORDER BY

The ORDER BY clause is used to sort the result-set in ascending or descending order. The ORDER BY clause sorts the records in ascending ASC order by default. To sort the records in descending order, use the DESC keyword.

Let’s get on with it and show some examples:

ORDER BY clause

ORDER BY clause ASC query output

We noted that the city column was sorted in ascending order, though we did not specify the preferred hierarchy, MySQL sorted it by its default setting which is the ascending order (ASC).

ORDER BY Descending (DESC )

Let us query the database for office locations and sort the result-set in descending order.

ORDER BY clause DESC

ORDER BY clause DESC query output

ORDER BY several columns example

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” and the “CustomerName” column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

ORDER BY several columns

ORDER BY several columns query output

ORDER BY several columns example 2

The following SQL statement selects all customers from the “Customers” table, sorted ascending by the “Country” and descending by the “CustomerName” column:

ORDER BY ASC and DESC

ORDER BY ASC and DESC query output

Comparison operators

It is also possible to use other comparison operators in our WHERE clauses. This is the full list of comparison operators we can use in MySQL:

Comparison operators

We have reached the climax for this introduction to MySQL. So far we have learnt the basics of Relational Database Management System, what it entails, how to install MySQL, how to access and retrieve the data and a few examples to quickly get you started.

We do hope that you found this blog exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thank you for learning with Us!