Consultas SQL muy prácticas para programadores en MySql o MariaDB
Este es uno de esos artículos que me parecen tan valiosos para mi que me los suelo guardar en marcadores. Me gustaría pensar que también es útil para mucha más gente que como yo usa diariamente consultas SQL. !Tenemos sorpresa al final!
Tabla de contenidos - Consultas SQL
Introducción a las consultas SQL
Todo programador que trabaje con proyectos dinámicos ha necesitado alguna vez consultar información contra una base de datos. En este artículo he querido centralizar las consultas más habituales. Algunas no tanto pero muy útiles en ciertos casos.
En mi opinión creo que son una colección de consultas SQL de gran utilidad. Prácticas para programadores que como yo, usamos MySql o MariaDB. Aunque también cabe mencionar que muchas de estas consultas también te servirán para SQL Server y Oracle.
Descubre la sorpresa al final de este artículo.
Conceptos Básicos de SQL
Antes de empezar con las consultas, dediquemos un momento para aprender los conceptos básicos entorno a SQL.
¿Qué significa SQL?
Structured Query Language - Lenguaje de consultas estructurado
¿Qué sentencia se usa para extraer información de una base de datos?
SELECT
¿Qué sentencia se usa para actualizar información de una base de datos?
UPDATE
¿Qué sentencia se usa para borrar información de una base de datos?
DELETE
¿Qué sentencia se usa para insertar información en una base de datos?
INSERT INTO
Con SQL, ¿cómo se selecciona una columna llamada "Nombre" de una tabla llamada "Personas"?
SELECT FirstName FROM Personas
Con SQL, ¿cómo se seleccionan todas las columnas de una tabla llamada "Personas"?
SELECT * FROM Personas
Con SQL, ¿cómo se seleccionan todos los registros de una tabla llamada "Personas" donde el valor de la columna "Nombre" es "Peter"?
SELECT * FROM Personas WHERE FirstName='Peter'
Con SQL, ¿cómo selecciona todos los registros de una tabla llamada "Personas" donde el valor de la columna "Nombre" comienza con una "a"?
SELECT * FROM Personas WHERE Nombre LIKE 'a%'
El operador OR muestra un registro si CUALQUIER condición enumerada es verdadera. El operador AND muestra un registro si TODAS las condiciones enumeradas son verdaderas
True
Con SQL, ¿cómo selecciona todos los registros de una tabla llamada "Personas" donde el "Nombre" es "Peter" y el "Apellido" es "Jackson"?
SELECT * FROM Personas WHERE Nombre='Peter' AND Apellido='Jackson'
Con SQL, ¿cómo selecciona todos los registros de una tabla llamada "Personas" donde el "Apellido" se encuentra alfabéticamente entre (e incluye) "Hansen" y "Pettersen"?
SELECT * FROM Personas WHERE Apellido BETWEEN 'Hansen' AND 'Pettersen'
¿Qué instrucción SQL se usa para devolver solo valores diferentes?
SELECT DISTINCT
¿Qué palabra clave de SQL se utiliza para ordenar el conjunto de resultados?
ORDER BY
Con SQL, ¿cómo puede devolver todos los registros de una tabla llamada "Personas" ordenada descendentemente por "Nombre"?
SELECT * FROM Personas ORDER BY FirstName DESC
Con SQL, ¿cómo puede insertar un nuevo registro en la tabla "Personas"?
INSERT INTO Personas VALUES ('Jimmy', 'Jackson')
Con SQL, ¿cómo se puede insertar "Olsen" como el "Apellido" en la tabla "Personas"?
INSERT INTO Personas (LastName) VALUES ('Olsen')
¿Cómo puedes cambiar "Hansen" a "Nilsen" en la columna "Apellido" en la tabla Personas?
UPDATE Personas SET LastName='Nilsen' WHERE LastName='Hansen'
Con SQL, ¿cómo puede eliminar los registros donde "Nombre" es "Peter" en la Tabla personas?
DELETE FROM Personas WHERE FirstName = 'Peter'
Con SQL, ¿cómo puede devolver el número de registros en la tabla "Personas"?
SELECT COUNT(*) FROM Personas
¿Qué operador se usa para seleccionar valores dentro de un rango?
BETWEEN
La restricción NOT NULL impone una columna no aceptar valores nulos.
True
¿Qué operador se usa para buscar un patrón específico en una columna?
LIKE
¿Qué declaración SQL se usa para crear una tabla en una base de datos?
CREATE TABLE
Reemplazar cadena con SQL
Esta es una de las consultas más populares, sobre todo se usa para cambiar de dominio una web, o cambiar urls. Por ejemplo cuando pasamos una web de http a https.
Veamos como se usa.
Donde 'campo' es la columna de la base de datos donde queremos hacer el cambio. Si lo haces en Wordpress normalmente será wp_post. Quedando así:
UPDATE tabla set campo = replace(campo, 'http', 'https') WHERE campo like 'http%';
Contar caracteres con SQL
Esta consultas SQL son muy interesantes en los casos en los que nos surja la necesidad de contabilizar los caracteres de una campo o columna. Sobre todo a la hora de hacer migraciones a nuevos sistemas gestores que puedan tener alguna restricción de longitud en algunos campos.
Basta con poner la siguiente función para contabilizar los caracteres de un texto, también llamado cadena o string la siguiente función puede recibir una cadena o string y devolver el número de caracteres:
CHARACTER_LENGTH(cadena)
SELECT character_length( 'prueba de un texto' )
Esta sentencia devolvería el valor 18, que es el número de caracteres que tiene la cadena "prueba un texto".
Búsquedas avanzadas con SQL
En este caso vamos a ver cómo buscar una cadena en todo un campo, con expresiones regulares, para, por ejemplo, reemplazarla por otra si fuera necesario.
SELECT * FROM post WHERE content LIKE "http://%"
Muy útil para luego ejecutar:
UPDATE tabla set campo = replace(campo, 'http', 'https') WHERE campo like "http%";
Insertar y si existe actualizar
Esta consulta se puede considerar algo avanzada, pues hace una operación algo compleja y se debe utilizar con cuidado. Lo que se busca con esta consulta es no crear registros duplicados. En este caso se quiere crear un usuario, con nombre Miguel y apellidos Gómez, además de dejarlo activado. Con la peculiaridad de que si hay un registro con nombre Miguel, no va a crear uno nuevo, sino que va a actualizarlo, lo va a dejar activado.
INSERT INTO usuarios (usuarios_id, nombre, apellidos, activado) VALUES (usuarios_id, 'Miguel', 'Gómez', 1) ON DUPLICATE KEY UPDATE nombre='Miguel',activado=1;
Borrar registros duplicados
Es muy común tener registros duplicados, por ejemplo, usuarios. En este consulta veremos como deshacernos de ellos, si tenemos, por ejemplo, registros duplicados por email:
SELECT id,email,COUNT(*)
FROM messages
WHERE id NOT IN (SELECT MAX(id) FROM messages GROUP BY email HAVING COUNT(email) >1)
GROUP BY email
HAVING COUNT(email) > 1
ORDER BY id
SQL Injection
Si queremos asegurarnos de que nuestra aplicación es segura, al menos en parte. Podemos probar insertando algunas consultas como la que te dejo a continuación. Una consulta modelo que te servirá para ver si tu sistema está preparado para evitar una de las técnicas más famosas de crackeo. El SQL injection.
SELECT id, nombre FROM usuarios WHERE id = 4 OR 1=1;
!Ahora la sorpresa!
La sorpresa no es otra que conocimiento gratuito, !y de calidad! No te quedes aquí, amplía tus conocimientos sobre SQL con este magnífico libro SQL Notes For Professionals