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 country), which are stored in a table. The number of columns that a table can hold should be defined at the time of the creation of the table, which is the major limitation of RDBMS. However, we can alter the definition of the table any time, but the full table should be rebuilt while doing so. In some cases, the table will be unavailable while doing an alter. Altering a table will be discussed in detail in Chapter 9, Table Maintenance.

A database is a collection of many tables, and a database server can hold many of these databases. The flow is as follows:

Database Server —> Databases —> Tables (defined by columns) —> Rows

Databases and tables are referred to as database objects. Any operation, such as creating, modifying, or deleting database objects, is called Data Definition Language (DDL).

The organization of data as a blueprint of how the database is constructed (divided into database and tables) is called a schema.