Using MySQL

In this chapter, we will cover the following recipes:

Connecting to MySQL using the command-line client
Creating databases
Creating tables
Inserting, updating, and deleting rows
Loading sample data
Selecting data
Sorting results
Grouping results (aggregate functions)
Creating users
Granting and revoking access to users
Selecting data into a file and table
Loading data into a table
Joining tables
Stored procedures
Functions
Triggers
Views
Events
Getting information about databases and tables

Introduction

We are going to learn a lot of things in the following recipes. Let's take a look at each one in detail.

Connecting to MySQL using the command-line client

So far, you have learned how to install MySQL 8.0 on various platforms. Along with the installation, you will get the command-line client utility called mysql, which we use to connect to any MySQL server.

Getting ready

First you need to know to which server you need to connect. If you have the MySQL server installed on one host and you are trying to connect to the server from a different host (usually called client), you should specify the hostname or IP address of the server and the mysql-client package should be installed on the client. In the previous chapter, you installed both MySQL server and client packages. If you are already on the server (through SSH), you can...

Creating databases

Well, you have installed MySQL 8.0 and connected to it. Now it is time to store some data in it, that's what the database is meant for, after all. In any relational database management system (RDBMS), data is stored in rows, which is the basic building block of the database. Rows contain columns in which we can store several set of values.

For example, if you want to store information about your customers in a database.

Here is the dataset:

customer id=1, first_name=Mike, last_name=Christensen country=USA
customer id=2, first_name=Andy, last_name=Hollands, country=Australia
customer id=3, first_name=Ravi, last_name=Vedantam, country=India
customer id=4, first_name= Rajiv, last_name=Perera, country=Sri Lanka

You should save them as rows: (1, 'Mike', 'Christensen', 'USA'), (2, 'Andy', 'Hollands', &apos...

Creating tables

While defining columns in a table, you should mention the name of the column, datatype (integer, floating point, string, and so on), and default value (if any). MySQL supports various datatypes. Refer to the MySQL documentation for more details (https://dev.mysql.com/doc/refman/8.0/en/data-types.html). Here is an overview of all datatypes. The JSON datatype is a new extension, which will be discussed in Chapter 3, Using MySQL (Advanced):

Numeric: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, and BIT.
Floating numbers: DECIMAL, FLOAT, and DOUBLE.
Strings: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
Spatial datatypes are also supported. Refer to https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html for more details.
The JSON datatype - discussed in detail in the next chapter.

You can create many tables inside a database...

Inserting, updating, and deleting rows

The INSERT, UPDATE, DELETE, and SELECT operations are called Data Manipulation Language (DML) statements. INSERT, UPDATE, and DELETE are also called write operations, or simply write(s). SELECT is a read operation and is simply called read(s).

How to do it...

Let's look at each of them in detail. I am sure you will enjoy learning this. I would suggest that you try a few things on your own as well, later. By the end of this recipe, we will also have gotten to grips with truncating tables.

Inserting

The...

Loading sample data

You have created the schema (databases and tables) and some data (through INSERT, UPDATE, and DELETE). To explain the further chapters, more data is needed. MySQL has provided a sample employee database and a lot of data to play around with. In this chapter, we will discuss how to get that data and store it in our database.

How to do it...

Download the zipped file:

shell> wget 'https://codeload.github.com/datacharmer/test_db/zip/master' -O master.zip

Unzip the file:

shell> unzip master.zip

Load the data:

shell> cd test_db-master

shell> mysql -u root -p < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE...

Selecting data

You have inserted and updated data in the tables. Now it is time to learn how to retrieve information from the database. In this section, we will discuss how to retrieve data from the sample employee database that we have created.

There are many things that you can do with SELECT. The most common use cases will be discussed in this section. For more details on syntax and other use cases, refer to https://dev.mysql.com/doc/refman/8.0/en/select.html.

How to do it...

Select all data from the departments table of the employee database. You can use an asterisk (*) to select all columns from a table. It is not recommended to use it, you should always select only the data you need:

mysql> SELECT...

Sorting results

You can order the result based on the column or aliased column. You can be specify DESC for descending order or ASC for ascending. By default, ordering will be ascending. You can combine the LIMIT clause with ORDER BY to limit the results.

How to do it...

Find the employee IDs of the first five top-paid employees.

mysql> SELECT emp_no,salary FROM salaries ORDER BY salary DESC LIMIT 5;
+--------+--------+
| emp_no | salary |
+--------+--------+
|  43624 | 158220 |
|  43624 | 157821 |
| 254466 | 156286 |
|  47978 | 155709 |
| 253939 | 155513 |
+--------+--------+
5 rows in set (0.74 sec)

Instead of specifying the column name, you can also mention the position of the column in the SELECT statement. For example...

Introduction

We are going to learn a lot of things in the following recipes. Let's take a look at each one in detail.

Connecting to MySQL using the command-line client

So far, you have learned how to install MySQL 8.0 on various platforms. Along with the installation, you will get the command-line client utility called mysql, which we use to connect to any MySQL server.

Getting ready

First you need to know to which server you need to connect. If you have the MySQL server installed on one host and you are trying to connect to the server from a different host (usually called client), you should specify the hostname or IP address of the server and the mysql-client package should be installed on the client. In the previous chapter, you installed both MySQL server and client packages. If you are already on the server (through SSH), you can specify localhost, 127.0.0.1, or ::1.

Second, since you are connected to the server, the next thing you need to specify is to which port you want to connect on the server. By default, MySQL runs on port 3306. So, you should specify 3306.

Now you know where to connect. The next...

Creating databases

Well, you have installed MySQL 8.0 and connected to it. Now it is time to store some data in it, that's what the database is meant for, after all. In any relational database management system (RDBMS), data is stored in rows, which is the basic building block of the database. Rows contain columns in which we can store several set of values.

For example, if you want to store information about your customers in a database.

Here is the dataset:

customer id=1, first_name=Mike, last_name=Christensen country=USA
customer id=2, first_name=Andy, last_name=Hollands, country=Australia
customer id=3, first_name=Ravi, last_name=Vedantam, country=India
customer id=4, first_name= Rajiv, last_name=Perera, country=Sri Lanka

You should save them as rows: (1, 'Mike', 'Christensen', 'USA'), (2, 'Andy', 'Hollands', 'Australia'), (3, 'Ravi', 'Vedantam', 'India'), (4, 'Rajiv', 'Perera', 'Sri Lanka'). For this dataset, there are four rows described by three columns (id, first_name, last_name and...

Creating tables

While defining columns in a table, you should mention the name of the column, datatype (integer, floating point, string, and so on), and default value (if any). MySQL supports various datatypes. Refer to the MySQL documentation for more details (https://dev.mysql.com/doc/refman/8.0/en/data-types.html). Here is an overview of all datatypes. The JSON datatype is a new extension, which will be discussed in Chapter 3, Using MySQL (Advanced):

Numeric: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, and BIT.
Floating numbers: DECIMAL, FLOAT, and DOUBLE.
Strings: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
Spatial datatypes are also supported. Refer to https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html for more details.
The JSON datatype - discussed in detail in the next chapter.

You can create many tables inside a database.

How to do it...

The table contains the column definition:

mysql> CREATE TABLE IF NOT EXISTS `company`.`customers` (
`id` int unsigned AUTO_INCREMENT...

Inserting, updating, and deleting rows

The INSERT, UPDATE, DELETE, and SELECT operations are called Data Manipulation Language (DML) statements. INSERT, UPDATE, and DELETE are also called write operations, or simply write(s). SELECT is a read operation and is simply called read(s).

How to do it...

Let's look at each of them in detail. I am sure you will enjoy learning this. I would suggest that you try a few things on your own as well, later. By the end of this recipe, we will also have gotten to grips with truncating tables.

Inserting

The INSERT statement is used to create new records in a table:

mysql> INSERT IGNORE INTO `company`.`customers`(first_name, last_name,country)
VALUES 
('Mike', 'Christensen', 'USA'),
('Andy', 'Hollands', 'Australia'),
('Ravi', 'Vedantam', 'India'),
('Rajiv', 'Perera', 'Sri Lanka');

Or you can explicitly mention the id column, if you want to insert the specific id:

mysql> INSERT IGNORE INTO `company`.`customers`(id, first_name, last_name,country)
VALUES 
(1,...

Loading sample data

You have created the schema (databases and tables) and some data (through INSERT, UPDATE, and DELETE). To explain the further chapters, more data is needed. MySQL has provided a sample employee database and a lot of data to play around with. In this chapter, we will discuss how to get that data and store it in our database.

How to do it...

Download the zipped file:

shell> wget 'https://codeload.github.com/datacharmer/test_db/zip/master'-O master.zip

Unzip the file:

shell> unzip master.zip

Load the data:

shell> cd test_db-master

shell> mysql -u root -p < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
NULL

Verify the data:

shell> mysql -u root -p  employees -A
mysql: [Warning] Using a password on the...

Selecting data

You have inserted and updated data in the tables. Now it is time to learn how to retrieve information from the database. In this section, we will discuss how to retrieve data from the sample employee database that we have created.

There are many things that you can do with SELECT. The most common use cases will be discussed in this section. For more details on syntax and other use cases, refer to https://dev.mysql.com/doc/refman/8.0/en/select.html.

How to do it...

Select all data from the departments table of the employee database. You can use an asterisk (*) to select all columns from a table. It is not recommended to use it, you should always select only the data you need:

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004...

Sorting results

You can order the result based on the column or aliased column. You can be specify DESC for descending order or ASC for ascending. By default, ordering will be ascending. You can combine the LIMIT clause with ORDER BY to limit the results.

How to do it...

Find the employee IDs of the first five top-paid employees.

mysql> SELECT emp_no,salary FROM salaries ORDER BY salary DESC LIMIT 5;
+--------+--------+
| emp_no | salary |
+--------+--------+
|  43624 | 158220 |
|  43624 | 157821 |
| 254466 | 156286 |
|  47978 | 155709 |
| 253939 | 155513 |
+--------+--------+
5 rows in set (0.74 sec)

Instead of specifying the column name, you can also mention the position of the column in the SELECT statement. For example, you are selecting the salary at the second position in the SELECT statement. So, you can specify ORDER BY 2:

mysql> SELECT emp_no,salary FROM salaries ORDER BY 2 DESC LIMIT 5;
+--------+--------+
| emp_no | salary |
+--------+--------+
|  43624 | 158220 |
|  43624 ...

Grouping results (aggregate functions)

You can group the results using the GROUP BY clause on a column and then use AGGREGATE functions, such as COUNT, MAX, MIN, and AVERAGE. You can also use the function on a column in a group by clause. See the SUM example where you will use the YEAR() function.

How to do it...

Each of the previously-mentioned aggregate functions will be introduced to you here in detail.

COUNT

Find the count of male and female employees:

mysql> SELECT gender, COUNT(*) AS count FROM employees GROUP BY gender;
+--------+--------+
| gender | count  |
+--------+--------+
| M      | 179973 |
| F      | 120051 |
+--------+--------+
2 rows in set (0.14 sec)

You want to find the 10 most common first names of the employees. You can use GROUP BY first_name to group all the first names, then COUNT(first_name) to find the count inside the group, and finally the ORDER BY count to sort the results. LIMIT these results to the top 10:

mysql> SELECT first_name, COUNT(first_name) AS count...

Creating users

So far, you have used only the root user to connect to MySQL and execute statements. The root user should never be used while accessing MySQL, except for administrative tasks from localhost. You should create users, restrict the access, restrict the resource usage, and so on. For creating new users, you should have the CREATE USER privilege that will be discussed in the next section. During the initial set up, you can use the root user to create other users.

How to do it...

Connect to mysql using the root user and execute CREATE USER command to create new users.

mysql> CREATE USER IF NOT EXISTS 'company_read_only'@'localhost' 
IDENTIFIED WITH mysql_native_password 
BY 'company_pass' 
WITH MAX_QUERIES_PER_HOUR 500 
MAX_UPDATES_PER_HOUR 100;

You might get the following error if the password is not strong.

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

The preceding statement will create users with:

* Username: company_read_only.
* access only from...

Granting and revoking access to users

You can restrict the user to access specific databases or tables and also only specific operations, such as SELECT, INSERT, and UPDATE. For granting privileges to other users, you should have the GRANT privilege.

How to do it...

During the initial setup, you can use the root user to grant privileges. You can also create an administrative account to manage the users.

Granting privileges

Grant the READ ONLY(SELECT) privileges to the company_read_only user:

mysql> GRANT SELECT ON company.* TO 'company_read_only'@'localhost';
Query OK, 0 rows affected (0.06 sec)

The asterisk (*) represents all tables inside the database.

Grant the INSERT privilege to the new company_insert_only user:

mysql> GRANT INSERT ON company.* TO 'company_insert_only'@'localhost' IDENTIFIED BY 'xxxx';
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: Using GRANT...

Selecting data into a file and table

You can save the output into a file using the SELECT INTO OUTFILE statement.

You can specify the column and line delimiters, and later you can import the data into other data platforms.

How to do it...

You can save the output destination as a file or a table.

Saving as a file

To save the output into a file, you need the FILE privilege. FILE is a global privilege, which means you cannot restrict it for a particular database. However, you can restrict what the user selects:

mysql> GRANT SELECT ON employees.* TO 'user_ro_file'@'%' IDENTIFIED  WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT FILE ON *.* TO 'user_ro_file'@'%' IDENTIFIED  WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18';
Query OK, 0 rows affected, 1 warning (0.00 sec)