Consultas_sql.txt

Mostrar registros de una tabla
//Mostrar todos los emails, nombres y apellidos de la tabla usuarios
SELECT email, nombre, apellidos FROM usuarios;

//Mostrar todos los registros de la tabla usuarios cuyo email contenga admin@admin.com
//WHERE se traduce como "cuyo".
SELECT * FROM usuarios WHERE email='admin@admin.com';

//Mostrar todos los nombres y apellidos de la tabla usuarios que se registraron en 2019 o en 2020
SELECT nombre, apellidos FROM usuarios WHERE YEAR(fecha) = 2019 OR YEAR(fecha) = 2020;

//Mostrar todos los emails de la tabla usuarios cuyo apellido contenga la letra A y que la contraseña sea 1234
//LIKE se traduce como "que contenga.."
//Los % son comodines. Quiere decir cualquier cantidad de caracteres. Hay otros tipos de comodines.
SELECT email FROM usuarios WHERE apellido LIKE '%a%' AND password = '1234';

//Mostrar todos los registros de la tabla usuarios cuya fecha de registro sea par.
SELECT * FROM usuarios WHERE YEAR(fecha)%2=0;

//Mostar toos los registros de la tabla usuarios cuyo nombre tenga más de 5 letras, se hayan registrado antes de 2020 y además mostrar el nombre en mayúsculas
//AS se usa para crear un alias y que aparezca Nombre en vez de UPPER(nombre)
SELECT UPPER(nombre) AS 'Nombre', apellidos FROM usuarios WHERE LENGTH(nombre) > 5 AND YEAR(fecha) < 2020;

//Mostrar todos los registros de la tabla usuarios ordenador por el id de forma ascendente.
SELECT * FROM usuarios ORDER BY id ASC;

//Mostrar un solo registro usando LIMIT
SELECT * FROM usuarios LIMIT 1;

//Mostrar del registro 0 al 5 registro usando LIMIT
SELECT * FROM usuarios LIMIT 0,5;





-----------------------------------------
Consultas de agrupamiento
//Mostrar un titulo y un id de cada categoría de la tabla entradas
SELECT titulo, id FROM entradas GROUP BY category_id;

//Mostrar cuantos títulos (el número) por categoría hay en la tabla entradas
//AS se usa para crear un alias y que aparezca Título en vez de COUNT(titulo)
SELECT COUNT(titulo) AS 'Numero de entradas' FROM entradas GROUP BY category_id;

//Consulta de agrupamiento con condiciones (HAVING)
//Se muestra lo mismo que en el caso anterior pero solo muestra los agrupamientos mayores o iguales a 4
//Esto se hace con la condición HAVING, que en los agrupamientos sustituye a WHERE
SELECT COUNT(titulo) AS 'Numero de entradas' FROM entradas
FROM entradas GROUP BY categoria_id HAVING COUNT(titulo) >=4;

//Me muestra de forma agrupada la media de los id de las entradas
SELECT AVG(id) AS 'Media de entradas' FROM entradas;

//Extrae el id más grande y muestra su título
SELECT MAX(id) AS 'Máximo id', titulo FROM entradas;




-----------------------------------------
Subconsultas
//Muestra todos los usuarios cuyo id exista en la columna usuario_id de la tabla entradas
//Dicho de otro modo, muestra los usuarios que hayan escrito una entrada
//IN se usa cuando hay multiples resultados, no solo uno.
SELECT * FROM usuarios WHERE id IN (SELECT usuario_id FROM entradas);

//Muestra el nombre y apellidos de la tabla usuarios cuyo id exista en la tabla de entradas cuyo titulo contenga la palabra tomates.
//Mostrar usuarios que tengan alguna entrada que hablen de tomates
SELECT nombre, apellidos FROM usuarios WHERE id
IN (SELECT usuario_id FROM entradas WHERE titulo LIKE "%tomates%");

//Mostrar todas las entradas de la categoría Acción
SELECT titulo FROM entradas WHERE 
categoria_id IN (SELECT id FROM categorias WHERE nombre ='Acción');

//Mostrar las categorías con más de 3 entradas
SELECT nombre FROM categorias WHERE 
(SELECT COUNT(categoria_id) FROM entradas GROUP BY categoria_id) >= 3;

//Mostrar los usuarios que crearon una entrada un martes
// DAYOFWEEK(fecha) = 3 es una función que saca el día de la semana que es igual a 3, teniendo en cuanta que e domingo es el día 1.
SELECT * FROM usuarios WHERE id IN (SELECT usuarios_id FROM entradas WHERE DAYOFWEEK(fecha) = 3); 

//Mostrar el nombre de el usuario que tenga más entradas
//En este caso sería:
//Muestra los nombres de usuario cuyo id está contenido en usuarios_id de la tabla entradas
//y agrupando los usuarios_id y ordenándolos por la suma de los id de forma descendente y limitando el número de items a 1.
SELECT nombre FROM usuarios WHERE id = 
(SELECT usuario_id FROM entradas GROUP BY usuario_id ORDER BY COUNT(id) DESC LIMIT 1);

//Mostrar las categorías sin entradas
//He usado NOT IN para negar el IN
SELECT nombre FROM categorias WHERE id 
NOT IN (SELECT categoria_id FROM entradas);


-----------------------------------------
Consultas multitabla
//Mostrar las entradas con el nombre del autor y la categoría
//En este caso sustituimos los id del nombre y la categoría por texto, 
//cuyo contenido está en las tablas de usuarios y categorías
//En el FROM le pongo un alias a cada tabla para reducir el texto en SELECT
//Pseoudocodigo: Muestra el título, nombre y nombre de las tablas entradas, usuarios y categorías
//cuando el id del usuario de la entrada sea igual al de la categoría y cuando el id de la categoria de entrada sea igual al id de la categoria.
SELECT e.titulo, u.nombre, c.nombre
FROM entradas e, usuarios u, categorias c
WHERE e.usuario_id = u.id AND e.categoria_id = c.id;

//Mostrar el nombre de las categorías y cuantas entradas tienen
//El GROUP BY es necesario para que muestre correctamente las categorias
SELECT entradas.titulo, COUNT(categorias.id) FROM entradas, categorias 
WHERE entradas.categoria_id = categoria.id GROUP BY entradas.categoria_id;

//Mostrar el email de los usarios y cuantas entradas tienen
SELECT usuarios.email COUNT(entradas.titulo) FROM usuarios, entradas
WHERE entradas.usuario_id = usuarios.id GROUP BY entradas.usuario_id;



-----------------------------------------
Consultas multitablas más efectivas
Las consultas multitablas del anterior apartado recorren todas las tablas citadas, lo cual es muy inefectivo. Con INNER JOIN  , con LEFT JOIN o el RIGHT JOIN se soluciona el problema
//Mostrar las entradas con el nombre del autor y la categoría
//Para usarlo hay que poner uno por cada nueva tabla que se quiera consultar
//Lleva un ON que es como el WHERE
SELECT entradas.id, entradas.titulo, usuarios.nombre, categorias.nombre
FROM entradas
INNER JOIN usuarios ON entradas.usuario_id = usuarios.id
INNER JOIN categorias ON entradas.categoria_id = categorias.id;

//Mostrar el nombre de las categorías y cuantas entradas tienen
//LEFT JOIN se usa para mantener todos los registros de las tablas citadas anterioremente (en esta ocasión es 
//categorias) aunque no cumplan la condición que se escribe a continuación.
//De esta forma el resultado sería que pone el nombre de todas las categorías incluso las que no tienen entradas.
//En vez de LEFT JOIN se puede usar RIGHT JOIN, que conserva integros los registros a partir de donde se usa en vez de los anteriores..
SELECT entradas.titulo, COUNT(categorias.id) 
FROM categorias
LEFT JOIN entradas ON entradas.categoria_id = categorias.id
GROUP BY entradas.categoria_id;



-----------------------------------------
Modificar datos
//Modificar en la tabla usuarios, el registro fecha, con la fecha actual (CURDATE). Pero solo modificamos el registro con el id 4
//Se eligen los campos con el WHERE
UPDATE usuarios SET fecha=CURDATE() WHERE id=4;

//Modificar en la tabla usuarios, el registro fecha y el registro apellidos. Pero solo modificamos el registro con el id 4
UPDATE usuarios SET fecha='2019-07-09', apellidos='Echeverria Royo' WHERE id=4;



-----------------------------------------
Eliminar datos
//En la tabla usuarios, eliminar el registro que tenga el email admin@admin.com
DELETE FROM usuarios WHERE email = 'admin@admin.com';

//En la tabla usuarios, eliminar el registro que tenga el id 5
DELETE FROM usuarios WHERE id = '5';



-----------------------------------------
Insertar datos
//En la tabla categorias inserto una fila con 3 registros. 
//El primero es null porque es el id y se genera solo.
//El segundo es el nombre de la categoría.
//El tercero es la fecha de hoy.
INSERT INTO categorias VALUES(null, 'Acción', CURDATE());





----------------------------------------------
Vistas o Views
Son parecidas a funciones. Sirven para optimizar las consultas a la base de datos.

//Así se crea. Al ejecutarlo me crea una "tabla en la base de datos".
//No es una tabla sino más bien una consulta.
//Se puede orbservar como con "CREATE VIEW entradas_con_nombres AS" hemos creado un alias de lo que viene a continuación
CREATE VIEW entradas_con_nombres AS
SELECT entradas.id, entradas.titulo, usuarios.nombre, categorias.nombre
FROM entradas
INNER JOIN usuarios ON entradas.usuario_id = usuarios.id
INNER JOIN categorias ON entradas.categoria_id = categorias.id;

//Si ahora ejecuto un SELECT de entradas_con_nombres, ejecuta el comando completo
SELECT * FROM entradas_con_nombres;

//Incluso puedo ponerle condiciones
SELECT * FROM entradas_con_nombres WHERE usuarios.nombre = Paco;

//Para borrar una vista
DROP VIEW entradas_con_nombres;