Construcción de Base de Datos en MySQL: Terminal, Tablas y Datos Simulados

Efficient database management is a fundamental skill for any technology professional. MySQL, as one of the most popular database management systems, offers powerful tools that go beyond basic queries. In this content, we will explore how to create and populate a database from scratch, using techniques that optimize both development and performance, preparing us to implement advanced practices that will transform the way we work with data.

How to create and configure our database from scratch?

We will start by creating a new database for this course. Unlike the previous course which was more basic and focused on SQL as such, this one will focus on best practices and advanced MySQL functionality.

To create our database, we will use commands from both the MySQL terminal and the operating system command line:

CREATE DATABASE IF NOT EXISTS platzi_sql;USE platzi_sql;

The CREATE DATABASE IF NOT EXISTS command is particularly useful because it avoids errors when trying to create a database that already exists. If we execute this command without the IF NOT EXISTS clause and the database already exists, MySQL will show us an error: "Error at line 1: Can't create database; database exists".

From the operating system command line, we can execute SQL scripts using:

mysql -u root < schema.sql

This command opens a connection to MySQL with the root user, executes the contents of the schema.sql file and automatically closes the connection when finished.

How to populate our database with initial data?

Once the database structure is created, we need to populate it with data to be able to work. For this, we will use SQL files containing INSERT statements.

Analyzing the data files

Before importing the data, it is useful to analyze the files to understand their structure and size:

wc -l *

This command shows us the number of lines in each file:

• build_products: 1,000 lines
• builds: 20 lines
• clients: 100,001 lines
• products: 3,000 lines

To see the content of a file we can use:

more products

Files contain INSERT INTO statements with multiple values. For example:

INSERT IGNORE INTO clients (name, email, phone_number) VALUES('Client1', '[email protected]', '123456789'),('Client2', '[email protected]', '987654321'),...

Importing data

To import the data into our database, we can use two approaches:

1. Modify the file to include the database selection:

   USE platzi_sql;INSERT INTO....

2. Specify the database in the import command:

   mysql -u root -b platzi_sql < clients.sql

It is important to note that we are using INSERT IGNORE, which means that if there are duplicate records (for example, emails that should be unique), MySQL will simply ignore them instead of generating an error. This explains why after importing, we have fewer records than there were in the original files:

SELECT COUNT(*) FROM clients;-- Result: 98,023 (instead of 100,000).

The import speed is impressive: MySQL can process files of several megabytes in a matter of seconds, which proves its efficiency for massive operations.

What are "super queries" and why are they important?

The instructor mentions a concept he calls "super queries," a term he coined to refer to queries that add intelligence not only to WHERE conditions, but also to columns and associations with other tables.

These "super queries" offer two main advantages:

1. Faster development: they reduce development time for complex queries from "days to hours".
2. More efficient execution: They improve performance, reducing execution time significantly.

The ultimate goal is to create databases that are:

• Clear: Easy to understand and maintain.
• Competent: Capable of solving complex problems
• Efficient: Both for development and production performance.

Practice is essential to master these techniques. As the instructor emphasizes, the best way to learn is to do the exercises yourself, experimenting and making mistakes in the process.

Throughout this course, we will be modifying tables and structures to adapt them to realistic business scenarios, learning how to optimize both the development and performance of our databases.

The path to MySQL mastery not only involves knowing the syntax, but also understanding how to structure our data and queries to obtain maximum performance with minimum effort. Have you experimented with mass data import in MySQL? Share your experiences and doubts in the comments.