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

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.

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...

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...

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):

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

You can create many tables inside a database...

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).

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.

The...

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.

1. Download the zipped file:

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

2. Unzip the file:

shell> unzip master.zip

3. 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...

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.

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...

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.

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...