mario
Consultas a la base de datos
En este curso nos enfocaremos en las consultas a las bases de datos (Query).Una consulta bien hecha puede salvar un negocio. Un Query es lo que brinda información.
Estructura básica de un Query
Los Query son la forma en que estructuramos las preguntas a las bases de datos, nos ayuda a transportar una duda. El Query tiene básicamente 2 partes, la sentencia Select y la sentencia From, también suele estar Where.
SELECT, Nos permite traer los datos que queremos mostrar
FROM, de dónde estamos tomando los datos
WHERE, es una condición, que en activo solo nos traiga los True
GROUP BY, nos permite agrupar por un criterio
ORDER BY, nos dice cómo ordenar
DESC, de manera descendiente
HAVING, es una condición similar a Where, pero se utiliza luego de un Order By
Un ejemplo se vería así, donde le decimos que traiga todo de la tabla posts y muestre todos los campos.
SELECT *
FROM posts;
Otro ejemplo,
SELECT *
FROM posts
WHERE fecha>’2022’;
Select
En esta sección veremos la primera parte de la estructura de un Query, SELECT. Se encarga de proyectar o mostrar los datos.
SELECT *, (quiere decir traeme todo, pero debe si o si ir con la segunda sentencia)
FROM
Ahora también podemos traer algunos campos de la siguiente manera,
SELECT titulo, fecha_publicacion, estatus
FROM posts;
Hay otra propiedad que se usa en SELECT que es el nombre que se le da al campo de la siguiente forma, Alias, el cual nos permitirá referirnos posteriormente con su nuevo nombre
SELECT titulo AS encabezado, fecha_publicacion AS publicado, status AS estado;
FROM posts;
Para contar los registros lo realizamos de la siguiente manera,
SELECT COUNT (*) AS numero_posts
FROM posts;
From
FROM indica de donde vamos a tener que traer los datos. Junto a la sentencia FROM tenemos el comando JOIN el cual nos ayuda a unir tablas, estos los veremos de una forma gráfica con los diagramas de Venn.
Los primeros Join que veremos son la diferencia, que quiere decir lo que tenemos en un lado pero no está en el otro.
En el primero quiere decir que traerá todos los usuarios que estén en la tabla A, estén o no están en B, por ejemplo si yo tengo un usuario tenga o no tenga post lo traeré. Hay otra forma de Left Join donde dice que me traiga los usuarios, pero solo los que no tengan Post asociados. Y lo mismo para el otro lado Right Join treme todo los Post que tengan o no tengan usuario y por último el right join, solo tráeme los post que no tengan usuarios.
Ahora veremos los Join qué tienen que ver con intersección. El primero de ellos es Inner Join, va a traer los valores que tanto existen en A como en B los usuarios que tengan post y los post que tengan usuarios.
La unión va a traer todo, todos los usuarios y todos los post.
La diferencia asimétrica todo lo contrario, traerá solo los usuarios que no tengan post y los post que no tengan usuarios.
Utilizando la sentencia From
El primero que haremos será el que traiga todo,
SELECT *
FROM usuarios;
Ahora para unir los usuarios con los post haremos un left join, que lo que quiere decir es que tomara la tabla que es usuario (que está a la izquierda) la uniremos con la tabla post (derecha) y ON nos dirá cual es la clave que nos ayudará a unir las tablas, es decir tráeme todo de la tabla usuario unido a la tabla post unida a través de sus 2 llaves, la tabla usuarios con su id primario que sea igual a la tabla post con su usuario_id
SELECT *
FROM usuarios
LEFT JOIN posts ON usuarios.id = posts.usuarios_id;
Ahora haremos el left join que esta abajo, que deja afuera a los que tienen post
SELECT *
FROM usuarios
LEFT JOIN posts ON usuarios.id = posts.usuarios_id
WHERE post.usuario_id IS NULL;
Ahora haremos el otro lado, nos traerá todos los posts tengan o no tengan usuarios
SELECT *
FROM usuarios
RIGHT JOIN posts ON usuarios.id = posts.usuarios_id;
Y ahora traeremos todos los post que no tengan usuario.
SELECT *
FROM usuarios
RIGHT JOIN posts ON usuarios.id = posts.usuarios_id
WHERE post.usuario_id IS NULL;
La intersección, nos traerá los usuarios con post y los post con usuarios
SELECT *
FROM usuarios
INNER JOIN posts ON usuarios.id = posts.usuarios_id;
Para la unión hay un tipo de unión que tienen algunos manejadores de bases de datos,
SELECT *
FROM usuarios
FULL OUTER JOIN posts ON usuarios.id = posts.usuarios_id;
Pero en MySQL no existe por lo cual se hará de la siguiente manera, un Join compuesto, el cual si es estandar
SELECT *
FROM usuarios
LEFT JOIN posts ON usuarios.id = posts.usuarios_id
UNION
SELECT *
FROM usuarios
LEFT JOIN posts ON usuarios.id = posts.usuarios_id;
Ahora para hacer la diferencia simétrica, que es decir todo lo que está en A y no en B y todo lo que está en B y no en A se hace de la siguiente manera
SELECT *
FROM usuarios
LEFT JOIN posts ON usuarios.id = posts.usuarios_id
WHERE posts.usuario_id IS NULL
UNION
SELECT *
FROM usuarios
LEFT JOIN posts ON usuarios.id = posts.usuarios_id
WHERE posts.usuario_id IS NULL;
Where
WHERE, nos ayuda a filtrar no columnas, si no tuplas o registros, con criterios como una fecha o cantidad. Si ponemos,
SELEC *
FROM posts;
Nos traerá todos los datos de la tabla post
SELECT *
FROM post
WHERE id < 50;
Nos traerá los registros con el id hasta el 49
SELECT *
FROM post
WHERE estatus != “activo”;
Nos traera solo los que no son activos.
Ahora veremos la propiedad LIKE el cual nos ayuda a traer registros de los cuales conocemos sólo una parte de la información. Este se ocupa entre porcentajes y lo que quiere decir es traerme la cadena con lo que sea antes y lo que sea después
SELECT *
FROM posts
WHERE titulo LIKE “%escandalo%”;
Ahora si quitamos el primer porcentaje nos traerá las rows que empiecen por la palabra escándalo y después da lo mismo con que terminan. Lo mismo si quitamos el porcentaje del final, los que terminen con la palabra escándalo.
SELECT *
FROM posts
WHERE titulo LIKE “escandalo%”;
Ahora haremos otro tipo de WHERE donde podemos ocupar la fecha, por ejemplo que nos traiga las publicación que sean posteriores a la siguiente fecha
SELECT *
FROM posts
WHERE fecha_publicacion > “2025-01-01”;
Ahora en la sentencia WHERE ocuparemos BETWEEN, el cual nos sirve para arrojar registros que estén en el medio de ciertos parámetros.
SELECT *
FROM posts
WHERE fecha_publicacion BETWEEN “2023-01-01” AND “2025-01-01”;
O también entre el ID 50 y el 60
SELECT *
FROM posts
WHERE id BETWEEN “50” AND “60”;
Otra propiedad que podemos usar es la siguiente, donde tomamos solo el año y no la fecha completa, también podemos hacer lo mismo con meses
SELECT *
FROM posts
WHERE YEAR (fecha_publicacion) BETWEEN “2023” AND “2024”;
Ó escoger solo un mes
SELECT *
FROM posts
WHERE MONTH(fecha_publicacion) = “04”;
Utilizando la sentencia Where nulo y no nulo
El valor nulo en una tabla generalmente es su valor por defecto cuando nadie le asignó algo diferente. La sintaxis para hacer búsquedas de datos nulos es IS NULL. La sintaxis para buscar datos que no son nulos es IS NOT NULL.
El valor nulo funciona de una manera un poco diferente. Por ejemplo, para ver los post que no tengan un usuario asociado, donde la condición debe ser que el usuario_id tenga un valor nulo, se traduce de la siguiente manera
SELECT *
FROM posts
WHERE usuario_id IS NULL;
La mayoría de las veces nos interesa traer los que no son nulos, y se hace así. Todos los post menos los post huérfanos
SELECT *
FROM posts
WHERE usuario_id IS NOT NULL
Para hacer los WHERE hay una condición extra que se llama AND que nos une las condiciones. Filtro sobre filtro
SELECT *
FROM posts
WHERE usuario_id IS NOT NULL
AND estatus =”activo”
AND id <50
AND categoia_id = 2
AND YEAR(fecha_publicacion) = “2025”;
Group By
GROUP BY tiene que ver con agrupación. Indica a la base de datos qué criterios debe tener en cuenta para agrupar. Ahora haremos distintos ejemplos, que nos muestre el estatus, nos grupe para decir cuantos post pertenecen a ese estatus (al lado de count pongo como quiero que se llame la columna)
SELECT estatus, COUNT (*) post_quantity
FROM post
GROUP BY estatus;
También podemos sumar, para saber por ejemplo, cuánto se ha gastado en entretenimiento y en salud, podemos agrupar por categoría y por mes, entonces nos dará la cantidad de insumo por cada mes y dato.
SELECT estatus, SUM(id) suma_id
FROM post
GROUP BY estatus;
Otro ejemplo, vamos a tomar todos los post y los vamos a agrupar por año y vamos a mostrar cuantos post se hicieron ese año
SELECT YEAR(fecha_publicacion) AS post_year, COUNT (*) AS post_quantity
FROM post
GROUP BY post_year;
Otro ejemplo, lo mismo pero la cantidad de post que hay en ese mes, con el nombre de los meses
SELECT MONTHNAME(fecha_publicacion) AS post_month, COUNT (*) AS post_quantity
FROM post
GROUP BY post_month;
Un último ejemplo vamos agrupar tanto por el estatus como el mes del post y nos dira cuanto hay activo e inactivo en cada mes
SELECT estatus, MONTHNAME(fecha_publicacion) AS post_month, COUNT (*) AS post_quantity
FROM post
GROUP BY estatus, post_month;
Order By y Having
La sentencia ORDER BY tiene que ver con el ordenamiento de los datos dependiendo de los criterios que quieras usar.
- ASC sirve para ordenar de forma ascendente.
- DESC sirve para ordenar de forma descendente.
- LIMIT se usa para limitar la cantidad de resultados que arroja el query.
En este ejemplo nos ordenara los post según la fecha de publicación de forma ascendente
SELECT *
FROM posts
ORDER BY fecha_publicacion ASC;
También podemos ordenarlo por cadena, como por ejemplo ordenarlo por el título, y de esta manera si le ponemos ascendente lo ordena de forma alfabética
SELECT *
FROM posts
ORDER BY titulo ASC
Hay un complemento al ORDER BY que siempre va en conjunto y es la sentencia LIMIT, el cual nos ayudará atraer la cantidad que nosotros queramos de posts, por ejemplo los 5 primeros
SELECT *
FROM posts
ORDER BY fecha_publicacion ASC
LIMIT 5;
Por último tenemos la sentencia HAVING, tiene una similitud muy grande con WHERE, sin embargo el uso de ellos depende del orden. Cuando se quiere seleccionar tuplas agrupadas únicamente se puede hacer con HAVING.
SELECT MONTHNAME(fecha_publicacion) AS post_month, estatus, COUNT(*) AS post_q
FROM posts
GROUP BY estatus, post_month
ORDER BY post_month;
Lo que nos arroja esta sentencia es cuantos post hay en abril de cualquier año, que están activo o inactivo
Pero ahora lo que queremos hacer es ver los meses que tenemos más de 1 post, normalmente lo que pensaríamos es hacerlo con un WHERE
SELECT MONTHNAME(fecha_publicacion) AS post_month, estatus, COUNT(*) AS post_q
FROM posts
WHERE post_q > 1
GROUP BY estatus, post_month
ORDER BY post_month;
Pero nos daría un error, ya que WHERE nos ayuda a filtrar pero lo hace antes de la parte de hacer la agrupación y la agrupación fue la que nos permitió contar la cantidad de post basándose en el mes y estatus, por lo cual where todavía no conoce el valor de post_q
Cuando uno hace una selección de tuplas o rows se hace con WHERE pero cuando uno quiere hacer una selección de tuplas o rows agrupados no se puede, y se debe hacer con HAVING (debe ir después del GROUP BY)
SELECT MONTHNAME(fecha_publicacion) AS post_month, estatus, COUNT(*) AS post_q
FROM posts
GROUP BY estatus, post_month
HAVING post_q > 1
ORDER BY post_month;
El interminable agujero de conejo (Nested queries)
Los Nested queries significan que dentro de un query podemos hacer otro query. Esto sirve para hacer Join de tablas, estando una en memoria. También teniendo un query como condicional del otro.
Este proceso puede ser tan profundo como quieras, teniendo infinitos queries anidados.
Se le conoce como un producto cartesiano ya que se multiplican todos los registros de una tabla con todos los del nuevo query. Esto provoca que el query sea difícil de procesar por lo pesado que puede resultar. Por lo cual es bueno utilizarlos, pero hay que saber cuando no utilizarlos, saber que si lo metemos en un query en particular ese query no es escalable, por lo cual hay que hacerlo con querys que sepamos que no estarán creciendo constantemente en la tabla que se utilizaron.
Estos se ocupan cuando tengo un problema que no se puede solucionar consultando tu tabla. Haremos un ejemplo donde en la primera sentencia SELECT vamos a colocar el nombre de una nueva tabla que vamos a proyectar (la cual no existe actualmente, la vamos a crear con el subquery) y luego del punto le diremos que vamos a querer el campo date. Para la sentencia FROM no ocuparemos una tabla que está creada, si no que será una tabla que será dinámica, que estará en memoria y será creada al momento de ejecutar ese query.
SELECT new_table_proyection.date, COUNT (*) AS posts_count
FROM (
SELECT DATE(MIN(fecha_publicacion)) AS date, YEAR(fecha_publicacion) AS post_year
FROM pots
GROUP BY post_year
) AS new_table_proyection
GRUP BY new_table_proyection.date
ORDER BY new_table_proyection.date;
Entonces lo que tenemos primero es un query que se está creando luego del query que tenemos entre paréntesis y finalmente tenemos los criterios para ordenar el query que se creó primeramente. (Esto se da mucho cuando debemos traer valores máximos, mínimos que tenemos que traer algún tipo de agrupación). En el order by final le estoy diciendo que nos agrupe la tabla por el campo date.
Esto se lee desde adentro hacia afuera, es decir primero nos vamos al SELECT dentro del paréntesis el cual está diciendo que quiere la fecha mínima (solo un valor), como fecha y el año como post_year, desde la tabla posts, agrupados por el año. Todo esto lo dará como una tabla y para consultar de forma inmediata esa misma tabla es que sale el query de afuera donde dice que está consultando esa misma tabla, que quiere el campo fecha y que nos cuente los post, agrupados por fecha y ordenados por fecha de la tabla que creamos en el primer query.
Ahora veremos un segundo ejemplo,
SELECT *
FROM posts
WHERE fecha_publicacion = (
SELECT MAX(fecha_publicacion)
FROM posts
);
Acá le estamos diciendo que quiero seleccionar todo de la tabla post, pero la condición sea igual a la fecha máxima de publicación que nos va a traer el segundo query (El segundo query si lo ejecutamos por separado nos traerá la última fecha de publicación, y eso lo traerá como condición WHERE)
De hecho dándonos este resultado es lo mismo que lo escribiéramos de la siguiente forma
SELECT *
FROM posts
WHERE fecha_publicacion = “2030-04-05”;
Y lo que nos trajo es la noticia completa pero de la fecha que ha sido la fecha máxima que encontró en el subquery.
No se ocupa en lo más común del día a día, pero existen algunos casos de usos.
¿Cómo convertir una pregunta en un query?
De pregunta a Query
SELECT: Lo que quieres mostrar (columnas, campos dinámicos, etc)
FROM: De dónde voy a tomar los datos (de una tabla, dos tablas, etc)
WHERE: Los filtros de los datos que quieres mostrar
GROUP BY: Los rubros por los que me interesa agrupar la información
ORDER BY: El orden en que quiero presentar mi información
HAVING: Los filtros que quiero que mis datos agrupados tengan
LIMIT: La cantidad de registros que quiero
Preguntandole a la base de datos
En esta sección vamos a hacer algunas preguntas y vamos a ver cómo se transforman en un query.
- Cuantas etiquetas tiene un Blog post
SELECT post.titulos, COUNT(*) num_etiquetas
FROM post
INNER JOIN post_etiquetas ON posts.id = post_etiquetas.post_id
INNER JOIN etiquetas ON etiquetas.id = posts_etiquetas.etiqueta_id
GROUP BY posts.id;
2. Cuales son los post que tienen más etiquetas
SELECT post.titulos, COUNT(*) num_etiquetas
FROM post
INNER JOIN post_etiquetas ON posts.id = post_etiquetas.post_id
INNER JOIN etiquetas ON etiquetas.id = posts_etiquetas.etiqueta_id
GROUP BY posts.id
ORDER BY num_etiquetas DESC;
3. Cuales son los tag o etiquetas que pertenecen a cada post
GROUP_CONCAT toma el resultado del query y lo pone como campo separado por comas.
SELECT post.titulos, GROUP_CONCAT (nombre_etiqueta)
FROM post
INNER JOIN post_etiquetas ON posts.id = post_etiquetas.post_id
INNER JOIN etiquetas ON etiquetas.id = posts_etiquetas.etiqueta_id
GROUP BY posts.id;
4. Cuales son las etiquetas que no tienen ningún post
SELECT *
FROM etiquetas
LEFT JOIN posts_etioquetas ON etiquetas.id = posts_etiquetas.etiqueta_id
WHERE post_etiquetas.etiqueta.id IS NULL
Consultas a la Base de datos está basado en distintos cursos de Platzi y práctica personal.