Consultas Avanzadas en MySQL

Trabajaremos con el siguiente diseño de base de datos:

Iniciaremos con el denominado CRUD

C ==> Create

R ==> Read

U ==> Update

D ==> Delete

Create

Create hace referencia a lo que tiene que ver con insertar registros a una tabla determinada.

Tomaremos como ejemplo a la tabla clientes

insert into clientes(
nombre,
direccion,
fecha_nacimiento,
email) values (
"Jaider",
"Kra 80 No. 10-40",
"1974-09-05",
"jq@gmail.com"
);

Nótese que a pesar que existe el campo Id, no se coloca ni en la definición de los campos ni en valor respectivo y esto obedece a que el ca'Jaider'mpo Id es auto incremental.

Por otro lado también se debe tener en cuenta que la asignación de los valores depende del tipo del campo.

Para el caso de los campos tipo string, es decir, char, varchar o text se usan comillas o doble comillas.

Ejemplo:

'Jaider' o "Jaider"

Para el caso de los campos tipo Date también se usan comillas o doble comillas.

Ejemplo:

'2023-10-01' o "2023-10-01"

Para el caso de los campos tipo numéricos los valores se escriben tal cual.

Ejemplo:

2500

Read

Read hace referencia a lo que tiene que ver con mostrar los registros de una o varias tablas.

Consultar datos de una o varias tablas

Para mostrar datos de una tabla se sigue la siguiente estructura:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [STRAIGHT_JOIN]
    [FROM table_references]
    [WHERE where_condition]
    [GROUP BY col_name]
    [HAVING where_condition]
    [ORDER BY col_name1, col_name1 ....ASC | DESC]
    [LIMIT number]

Ejemplo 1:

SELECT * 
FROM clientes;

Para este caso, muestra todos los registros, incluyendo todos los capos de la tabla cliente.

En el caso que desees mostrar ciertos campos seria:

SELECT nombre, fecha_nacimiento, email
FROM clientes;

Cuando consideres varias tablas en una consulta, debes verificar las relaciones dado que estas deben tener una linea continua en dicha relación.

Existen dos formas de considerar esta relación, para ello utilizaremos tres tablas:

telefonos,

clientes y

ventas.

Nótese que si existe una linea continua en las relaciones de las tres tablas.

Forma 1: Las relaciones se contemplan en la clausula Where, teniendo en cuenta los campos llaves y foráneos de cada relación entre dichas tablas.

SELECT *
FROM telefonos, clientes, ventas
where
clientes.id = telefonos.clientes_id and
clientes.id = ventas.clientes_id;

para ayudar a la consulta, podemos utilizar alias en los nombres de las tablas de la siguiente manera.

SELECT *
FROM telefonos as T, clientes as C, ventas as V
where
C.id = T.clientes_id and
C.id = V.clientes_id;

Tambien podemos seleccionar los campos que se desee mostrar de cada tabla: utilice el nombre de la tabla.nombreCampo o alias.nombreCampo y en caso que desee mostrar todos los campos de una tabla utilice nombreTabla.*

SELECT C.nombre, C.email, T.numero, V.*
FROM telefonos as T, clientes as C, ventas as V
where
C.id = T.clientes_id and
C.id = V.clientes_id

Forma 2: Las relaciones se contemplan en el estamento Join, teniendo en cuenta los campos llaves y foráneos de cada relación entre dichas tablas.

SELECT C.nombre, C.email, T.numero, V.*
FROM telefonos as T
join clientes as C on(
C.id = T.clientes_id
)
join ventas as V on(
C.id = V.clientes_id
);

Condiciones en las Consultas

Para las condiciones se utiliza la clausula Where de la siguiente manera:

Quiero realizar la misma consulta anterior de cualquiera de las dos formas, teniendo en cuenta la condición que presente las ventas de una fecha especifica.

Forma 1:

SELECT C.nombre, C.email, T.numero, V.*
FROM telefonos as T, clientes as C, ventas as V
where
C.id = T.clientes_id and
C.id = V.clientes_id and
fecha = "2023-11-12";

Forma 2:

SELECT C.nombre, C.email, T.numero, V.*
FROM telefonos as T
join clientes as C on(
C.id = T.clientes_id
)
join ventas as V on(
C.id = V.clientes_id
)
where 
fecha = "2023-11-12";

y el resultado para ambas formas es:

Operadores de las Condiciones

Operador	Significado
=	Igual
\<>	Diferente
>	Mayor Que
>=	Mayor o igual Que
\<	Menor Que
\<=	Menor o igual Que
Like	Es un operador lógico de SQL Server que determina si una cadena de caracteres coincide con un patrón especificado

Ejemplo: Mostrar todo los email de los contactos que comiencen con la letra m.

select *
from clientes as C
where C.email like 'm%'

Mostrar todos los email de los contactos que contengan el dominio gmail

SELECT * FROM clientes 
where email like concat('%','gmail','%');

Mostrar todas las ventas en un rango de fecha, incluyendo sus respectivos clientes y productos. Ordenarla ascendentemente por fecha.

En esta consulta tiene una característica muy especial y que se debe prestar mucha atención y es el hecho que en el enunciado observamos las tablas de clientes, ventas y productos.

si vemos el diseño observamos que no existe una linea continua de tablas, por cuanto es necesario incluir la tabla ventas_productos que aunque no se menciones es necesario incluirlo para culminar la linea continua en la consulta de la base de datos.

Forma 1:

select C.*, V.*, VP.*, P.*
from
clientes as C, ventas as V, ventas_productos as VP, productos as P
where
C.id = V.clientes_id and
V.id = VP.ventas_id and
P.id = VP.productos_id and
V.fecha between "2023-01-01" and "2023-06-30"
order by V.fecha asc;

Forma 2:

select *
from clientes as C
join ventas as V on (C.id = V.clientes_id)
join ventas_productos as VP on (V.id = VP.ventas_id)
join productos as P on (P.id = VP.productos_id)
where 
V.fecha between "2023-01-01" and "2023-06-30"
order by V.fecha asc;

Consultas de Agrupamiento

Se consideran este tipo de consultas cuando tenemos valores que se repiten en los registros.

Si observamos la siguiente consulta:

SELECT C.nombre, C.email, T.numero, V.*
FROM telefonos as T
join clientes as C on(
C.id = T.clientes_id
)
join ventas as V on(
C.id = V.clientes_id
);

Las columnas como fecha y clientes_id tenemos valores que se repiten por ende podemos agrupar en esas consultas haciendo operaciones de Contar (Count), Sumar (Sum) y Promedio (Avg).

Ejemplaricemos este caso:

Mostrar la suma del total de las ventas realizadas por cada cliente, adicionalmente muestre la cantidad y el promedio de ventas por cada cliente, en un rango de fecha especifico.

Como se puede apreciar la columna o campo que se desea agrupar, que en este caso se repite es el de clientes_id de la tabla de ventas.

Forma 1:

SELECT C.id, C.nombre, sum(V.total) as TotalSuma, count(V.clientes_id) as CuentaTotal, avg(V.total) as Promedio 
FROM clientes as C, ventas as V 
where 
C.id = V.clientes_id and
V.fecha between "2023-01-01" and "2023-06-30"
group by C.id
order by TotalSuma desc;

Forma 2:

SELECT C.id, C.nombre, sum(V.total) as TotalSuma, count(V.clientes_id) as CuentaTotal, avg(V.total) as Promedio 
FROM clientes as C
join ventas as V on(
C.id = V.clientes_id
)
where V.fecha between "2023-01-01" and "2023-06-30"
group by C.id
order by TotalSuma desc;

Resultado para ambas formas:

También se le pueden colocar condiciones a las consultas agrupadas. Para este caso utilice la clausula Having., e la siguiente manera:

Forma 1:

SELECT C.id, C.nombre, sum(V.total) as TotalSuma, count(V.clientes_id) as CuentaTotal, avg(V.total) as Promedio 
FROM clientes as C, ventas as V 
where 
C.id = V.clientes_id and
V.fecha between "2023-01-01" and "2023-06-30"
group by C.id
having CuentaTotal >=2
order by TotalSuma desc;

Forma 2:

SELECT C.id, C.nombre, sum(V.total) as TotalSuma, count(V.clientes_id) as CuentaTotal, avg(V.total) as Promedio 
FROM clientes as C
join ventas as V on(
C.id = V.clientes_id
)
where V.fecha between "2023-01-01" and "2023-06-30"
group by C.id
having CuentaTotal >=2
order by TotalSuma desc;

Resultado:

Sub Consultas

En las Sub Consultas podemos realizar la teoría de conjuntos aplicadas a las bases de datos:

la mas conocida es el siguiente caso:

Teniendo en cuenta las mismas tablas anteriores entre clientes y ventas, muestre los clientes que no le han realizado ventas en una fecha especifica.

Si analizamos la consulta en la teoría de conjunto, seria A - B, donde A seria Clientes y B Ventas.

Clientes - Ventas

Forma 1:

select *
from clientes as C
where C.id Not In(
select V.clientes_id from ventas as V
where V.fecha between "2023-01-01" and "2023-06-30"
)

Forma 2:

select *
from clientes as C
left join ventas as V on(
C.id = V.clientes_id and
V.fecha between "2023-01-01" and "2023-06-30"
)
where 
V.clientes_id is null 

Resultado para ambas formas