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.
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:
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.
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.
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.
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.
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
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.
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.
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.
Step 9: Authentication method
Now, select the Authentication Method and click on Next. Here, I am going to select the first option.
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.
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.
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.
Step 13: Completion
Once the configuration has completed, you will get the screen below. Now, click on the Finish button to continue.
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.
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.
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.
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.
Step 18: Finish button
After completing the above step, we will get the following screen. Here, click on the Finish button.
Step 19: Installation completion
Now, the MySQL installation is complete. Click on the Finish button.
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.
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.
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)
Once the program has finished loading, your screen will display the MySQL Workbench like the image below:
STEP 3: Run SQL script
Click the file tab at the far left corner of the MySQL workbench and click 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.
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.
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.
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.
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.
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
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:
If we just want specific attributes or columns from the table, we can name those instead of using the asterisk.
For example:
Select using Date
Let’s take a look how to query using dates :
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:
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:
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 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 example 2
The following SQL statement selects all customers from the “Customers” table, sorted ascending by the “Country” and descending by the “CustomerName” column:
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:
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 appreciate our readers and learners and would love to get your feedback.
Don’t forget to like, share the article and subscribe for more exciting content.
Author
Geovanni Ubah. You can find me on LinkedIn.
Continue Reading
Exploratory Data Analysis using MySQL
Do you want to learn how to automate tasks with Excel?
Receive Fresh, Weekly Analytics Content
Join 1K+ subscribers to receive original, freshly curated content every week.
Related article – Visualizing WhatsApp Data using Python and Power BI
Video series – Building an Expenses Management Dashboard in Excel
