Chapter 2. Data Query Techniques in MySQL 8

In Chapter 1, Introduction to Big Data and MySQL 8, we saw an overview of big data and MySQL 8 along with new features of MySQL 8 followed by the installation of MySQL 8. In this chapter, we will explore data query techniques. This chapter assumes that you have gone through the previous chapter and are familiar with MySQL 8 and how to install MySQL 8.

In this chapter, we will cover the following important topics for MySQL 8 query techniques:

• Overview of SQL
• Database storage engines and types
• Select statements in MySQL
• Insert and update statements in SQL
• Transactions in MySQL
• Aggregating data in MySQL 8
• JSON Data Type and selecting and updating in JSON Data Type

Before we move on to the details, let's have an overview of SQL with respect to MySQL.

Overview of SQL

Structured Query Language (SQL) is used to manipulate, retrieve, insert, update, and delete data in relational database management system (RDBMS). To make it simpler, SQL tells the database what to do and exactly what it needs. SQL is a standard language that all RDBMS systems such as MySQL, MS Access, MS SQL, Oracle, Postgres, and others use.

Using SQL, the user can access, define and manipulate the data from MySQL. We can leverage SQL modules, libraries, and precompilers to embed with the other languages which help in creating/dropping databases and tables, manage view, stored procedures, function etc. in a database. Using SQL, we can also manage permissions on tables, procedures and different views.

The following are a few important SQL commands that we explain in detail later in the chapter:

• SELECT: Extracts data from a database
• UPDATE: Updates data from a database
• DELETE: Removes existing records from a database
• INSERT INTO: Add new information into a database
• CREATE DATABASE...

Database storage engines and types

Let's have a look at different storage engine of MySQL database. This is an important section to understand before we jump into data query techniques, as storage engines play an important role in data query techniques. MySQL stores data in the database as a subdirectory. In each database, data is stored as tables and each table definition information is being stored in a file with extension as .frm with the same name as the table name. Suppose if we create a new table as admin_user then it will store all table definition related information in admin_user.frm file.

We can see information related to a table with the use of SHOWTABLE STATUS command. Let's try to execute this command for admin_user table and pull the information.

mysql> SHOW TABLE STATUS LIKE 'admin_user' \G;
*************************** 1. row ***************************
 Name: admin_user
 Engine: InnoDB
 Version: 10
 Row_format: Dynamic
 Rows: 2
 Avg_row_length: 8192
 Data_length: 16384
...

Select statement in MySQL 8

The Select statement is used to retrieve data from single or multiple tables:

SELECT field 1, field 2, field 3 from table_name [WHERE Clause] [GROUPBY {col_name }] [HAVINGwhere_condition] [ORDERBY {col_name}  {ASC|DESC}, ...] [LIMIT{OFFSET M}{LIMIT N}]

This is the common syntax used to retrieve data from a single table:

• Fields one and two are the column names of the table. To fetch all columns from the table, the * expression can be used.
• table_name indicates the table name from where data needs to be retrieved.
• The WHERE clause can be used to specify any condition in a single and multiple column.
• The Group BY function is used with aggregate functions to group the result sets.
• The HAVING clause is needed after GROUP BY to filter based on conditions for a group of rows or aggregates. If we use the HAVING clause without GROUP BY, it would act similarly to the WHERE clause.
• The ORDER BY clause is used to sort the table result sets in ascending or descending order.
• LIMIT...

Insert, replace, and update statements in MySQL 8

Insert, replace, and update statements are used to insert or modify data in the database. Let's take a look at them one by one.

Insert

INSERT inserts new rows into an existing table; the following is the syntax to add new rows to a table:

INSERT[INTO] tbl_name [(field_name1, field_name2...)] {VALUES|VALUE} ({value of filed 1},value of filed 2,...),(...),...

The following inserts new users into the user table:

Insert INTO user (first_name,last_name,email,password) VALUES 
("Robert","Dotson","[e-mail]","17500917b5cd7cefdb57e87ce73485d3"),
("Ruth","Uselton","[e-mail]","17500917b5cd7cefdb57e87ce73485d3");

Update

UPDATE modifies data into an existing table; the following is the syntax to add new rows to a table:

UPDATEtable_referenceSETfield_name1="value"[,field_name2="value"]...
[WHEREwhere_condition]
[ORDERBY...]
[LIMITrow_count]

The following UPDATE statement is used to update the first name in the users table:

UPDATE...

Transactions in MySQL 8

Transaction is a logical unit of one or more Insert, Update, and Delete statements. Transaction is useful when you want to do multiple operations on the database. Either all the changes that are successful would be committed, unsuccessful ones would be undone, or errors generated during execution would perform transactions rolled back. The InnoDB storage engine supports the rollback of transactions. The goal of the InnoDB storage model is to combine the best properties of a multi-versioning database with two-phase locking. InnoDB performs locking at the row level and runs queries as non-locking for consistent reads by default for better performance and integrity of data.

Let's take an example to understand where transaction is useful. Consider a banking database. Suppose a bank customer want to transfers money from his account to another account. Generally, SQL statements will be divided into five parts using a transaction:

• Transaction start
• Debit from customer account...

Aggregating data in MySQL 8

Generally, it happens that data you want is not always available in the database. To generate data, such as getting a total count of orders or sum of price for all orders, MySQL provides many built-in aggregate functions that you can use to represent loaded data to perform calculations in MySQL statements. Data aggregation values can be sums, counting, averages, and so forth. Aggregate functions are all about fetching data from one column of a table, performing calculations on several rows, and returning the required value as an outcome.

As per the ISO standard, there are mainly the following listed aggregate functions:

• Minimum
• Maximum
• Average
• Count
• Sum

The importance of aggregate functions

For businesses, there would be several requirements at each level for each of the diversified functions to produce aggregated data to visualize. Top executives of companies would be concerned with having a holistic view of the whole organization; however, a manager would be limited...

JSON

In MySQL 5.7, the JSON functionality was introduced that allows you to get dataset results in JSON data format, virtual columns, and, tentatively, 15 SQL functions that allow you to search and use JSON data on the server side. In MySQL 8, there are additional aggregation functions that can be used in JSON objects/arrays to represent loaded data in a further optimized way. The following are the two JSON aggregation functions that were introduced in MySQL 8:

• JSON_OBJECTAGG()
• JSON_ARRAYAGG()

Let's take an example of an e-commerce platform where we will create a few tables and data to understand both these functions.

First, let's create a table for products as follows:

CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`pname` varchar(75) DEFAULT NULL,
 `pmanufacturer` varchar(75) DEFAULT NULL,
 `pprice` int(10) DEFAULT NULL,
 PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now let's create a table for attributes:

CREATE TABLE `attr` (
`id` int(11) NOT NULL AUTO_INCREMENT...

Summary

By now, you have learned the different database engines available in MySQL 8 and their applications. We established how to create tables, modify existing tables, and how to add/remove columns from an existing table. We also understood how to drop tables and databases. We learned in detail about different filter criteria available in MySQL. After that, we explored the different joins available in MySQL 8 and how to use joins in a query to fetch meaningful data from the database in a single query. We explored how database transactions work in MySQL and the importance of transactions in real-life scenarios. Lastly, we understood how to use aggregate functions in MySQL and learned the usage of different aggregate functions such as group by, having, min(), max(), avg(), sum(), and count(), and how to store JSON information in the database.

In the next chapter, you will learn how indexing works in MySQL 8, the new features introduced related to indexing, different types of indexing, and...