Combinations are essential when we talk about MySQL, taking into account that thanks to them we will have the possibility of making all kinds of queries on tables of a previously generated database.
The best thing about them is that they allow us to consider several criteria to achieve a particular result, a key issue thinking that many of these queries are also carried out on the construction of an SQL statement, called predicate.
If you do not know what we are talking about or how to take advantage of these combinations, do not worry, since in the following lines we will show you all details that you should consider in these types of cases.
Use MySQL Combinations
These MySQL combinations, which are used for example in the case of programming universities, become useful once we unzip the downloaded files, when two named ones appear sakila-schema.sql and sakila-data.sql.
Within them is the structure of the database as well as the sample data. On this occasion we will rely for example on a database that aims to provide a testing environment for software development or SQL queries.
The first thing you should consider about it is that the structure Sakila is not at all simple, since it has about 1000 records in addition to a huge number of previous relationships, with examples of views, stored procedures, etc.
Let's assume, for example, that this conformation is like a chain stores of movie rental that has both several branches and a huge number of vendors for each of them.
Who wants rent a movieThen, you will first have to confirm that it is available or in stock, and then the seller also check that it is part of the database to assign us the DVD. And there are also pay stubs, outstanding balances, etc.
This time, for example, if we want to know if the client exists, the query is something like:
select customer.customer_id, customer.store_id, CONCAT (customer.first_name, ’customer, customer.last_name) as Customer
where customer.first_name like ‘Juan%
Now, from this we want to show you what they are the queries We have available combining all available data.
Inner Join SQL clauses
Well, as we say, an SQL JOIN clause is normally used to combine different rows from two or more tables, taking advantage of a common field that can exist between them. What the system does is return only those that meet the union condition.
We'll see An example following the ethnic of the movie rental store that we mentioned before, so that there are no doubts about the ways in which we can take advantage of this tool.
If by case we want to know what they are customers who rented movies, the first thing we have to do for that is to use the base tables that are Customer and Rental. The first is to know who are inside the second in this way:
select rental.rental_id, customer.first_name, customer.last_name, rental.rental_date, rental.return_date
from rentalINNER JOIN customer
ON rental.customer_id = customer.customer_id
On the screen we should observe, from this, a list of customers who have rented movies in the different stores.
Another option is to consult with a Where, although here the procedure may take a little longer:
select rental.rental_id, customer.first_name, customer.last_name, rental.rental_date, rental.return_datefrom rental, customer
where rental.customer_id = customer.customer_id
As you can see, after installing MySQL on your computer you will have a huge amount of options available to take advantage of this platform, and this is only one of the many that we have analyzed in See how to do it.
Have you been able to use MySQL combinations thanks to this tutorial?