Consultas MySQL con funciones

SELECT se usa para recibir registros seleccionados desde una o más tablas. MySQL 5.0 incluye soporte para comandos UNION y subconsultas. Consulte Sección 13.2.7.2, “Sintaxis de UNION” y Sección 13.2.8, “Sintaxis de subconsultas”.

• Cada select_expr indicata una columna que quiere recibir.
• table_references indicata la tabla o tablas desde la que recibir registros. Su sintaxis se describe en Sección 13.2.7.1, “Sintaxis de JOIN”.
• where_definition consiste en la palabra clave WHERE seguida por una expresión que indica la condición o condiciones que deben satisfacer los registros para ser seleccionados.

SELECT también puede usarse para recuperar registros computados sin referencia a ninguna tabla.
Por ejemplo:


Consultas con funciones de Fecha

De cada jugador, queremos saber su nombre, fecha de nacimiento (en el formato correcto) y la edad actual. El resultado lo queremos ordenado por la edad.

SELECT NJugador,DATE_FORMAT(FechaNac,'%d/%m/%Y') AS Fecha_Nacimiento,CAST(  (DATEDIFF(Now(),FechaNac) / 365) AS UNSIGNED) AS Edad FROM JugadorORDER BY Edad;

En una tienda, queremos conocer el nombre de un vendedor, nombre del producto, fecha de venta, kilos vendidos, precio del producto y total de la venta de las ventas realizadas en el año 2002.

SELECT NombreVendedor,NomProducto,Fecha,Kilos,Precio,Kilos*Precio AS Total FROM Ventas,Producto,Vendedor WHERE IdProducto=CodProducto AND CodVendedor=IdVendedor AND Year(Fecha)=2002;

Total de los kilos vendidos por año de cada uno de los productos.

SELECT NomProducto,SUM(Kilos) AS Total_Kilos,Year(Fecha) AS Annio FROM Ventas,Producto WHERE IdProducto=CodProducto GROUP BY Annio,NomProducto;

Resumen de ventas anuales por vendedor

SELECT NombreVendedor,Year(Fecha) AS Annio,Sum(Kilos*Precio) AS Ventas FROM Vendedor,Ventas,Producto WHERE IdProducto=CodProducto AND CodVendedor=IdVendedor GROUP BY NombreVendedor,Annio;

Funciones de fecha, hora y datetime de MySQL

Comenzaré con estas funciones porque son las que tengo más recientes en mi trabajo reciente y creo que son de las más útiles. Las fechas, para mi gusto, es uno de los problemas más pesados de todos los lenguajes de programación.
Dado que en estoy acostumbrado a trabajar con llamadas, no voy a complicarme mucho la existencia y usaré un ejemplo de varias llamadas. Como puedes ver un poco más abajo, la mayoría de los campos son fechas con el formato YYYY-MM-DD (HH:M:SS).

+---------+---------+---------------------+---------------------+---------------------+
| origen  | destino | fecha               | respondida          | cuelgue             |
+---------+---------+---------------------+---------------------+---------------------+
| Piccolo | Vegeta  | 2012-09-26 00:00:00 | 2012-09-26 00:01:00 | 2012-09-26 00:04:50 |
| Goku    | Yamcha  | 2012-09-26 06:00:00 | 2012-09-26 06:02:50 | 2012-09-26 06:54:51 |
| Gohan   | Trunk   | 2012-09-26 10:43:21 | 2012-09-26 10:44:21 | 2012-09-26 11:24:59 |
+---------+---------+---------------------+---------------------+---------------------+

¡Esas fechas son horribles! Para estos casos, la función DATE_FORMAT() nos viene como anillo al dedo, ya que funciona de forma muy similar a la función date de PHP. Lo que pasa, es que con PHP tendríamos que hacer uso de la función strtotime para convertirla primero a formato Unix.

Para darle formato a la fecha con la función DATE_FORMAT, tendremos que hacer uso del caracter '%'.

Veamos cómo podríamos quién hizo la llamada y cuando, pero con un formato más bonito. Pero antes de que podamos apreciar lo que va a solucionarnos la vida, veamos cómo lo haríamos en PHP.
La consulta sería

1	SELECT origen, fechaLlamada FROM llamadas;

Sin embargo, ajustando mínimamente nuestra consulta:

1 2 3

SELECT origen, DATE_FORMAT(fechaLlamada,'%d/%m/%Y a las %H:%i') AS fechaLlamada FROM llamadas

Estas son las filas devueltas:

+---------+------------------------+
| origen  | fechaLlamada           |
+---------+------------------------+
| Piccolo | 26/09/2012 a las 00:00 |
| Goku    | 26/09/2012 a las 06:00 |
| Gohan   | 26/09/2012 a las 10:43 |
+---------+------------------------+

-----

Consulta que nos indica las ventas totales, por cada año, de los que están contratados y
los que no. El resultado lo ordenaremos por año:

SELECT Contratado,Year(Fecha) AS Annio,Sum(Kilos) AS Kg_Vendidos FROM Vendedor,Ventas,Producto WHERE IdProducto=CodProducto AND CodVendedor=IdVendedor GROUP BY Contratado,Annio ORDER BY Annio;

 Resumen de las ventas en kilos por cada uno de los meses del año 2000.

SELECT Month(Fecha) AS Mes,Sum(Kilos) AS Kilos_Mes FROM Ventas WHERE Year(Fecha)=2000 GROUP BY Mes ORDER BY Kilos_Mes DESC;

Queremos tener los datos para ver la evolución de las ventas por meses en  toda la historia de mi frutería, por tanto, queremos tener la venta económica por cada mes a lo largo del tiempo.

SELECT Month(Fecha) AS Mes, Year(Fecha) AS Anio,Sum(Kilos*Precio) AS Cantidad_Vendida FROM Ventas,Producto WHERE IdProducto=CodProducto GROUP BY Mes,Anio ORDER BY Anio,Mes;

Obteniendo la fecha/hora actual

Si necesitas usar la fecha, la hora o ambos a la vez en alguna de tus consultas, no necesitas que PHP haga nada por ti. Usa una de estas funciones:

• NOW() nos devuelve la fecha actual en el formato YYYY-MM-DD (HH:M:SS).
• CURDATE() nos devuelve la fecha actual en el formato YYYY-MM-DD.
• CURTIME() nos devuelve la hora actual en el formato HH:M:SS.

Estas funciones son muy útiles por ejemplo para establecer la fecha de actualización de una fila. Por ejemplo,

1 2 3

UPDATE llamadas SET revisada = NOW() WHERE origen = 'Piccolo';

Vamos a mezclar un poco la función que vimos antes con esta para saber cuándo fue la última vez que llamó cada personaje.

1 2 3 4 5 6 7 8 9 10

SELECT a.origen, TIMESTAMPDIFF(DAY,a.fechaLlamada,NOW()) AS diasDesdeUltimaLlamada FROM llamadas a INNER JOIN (     SELECT id,     MAX(fechaLlamada) AS ultimaLlamada     FROM llamadas     GROUP BY origen ) b ON (b.id = a.id AND b.ultimaLlamada = a.fechaLlamada);

Esta consulta es un poco más compleja pero puede resultar de utilidad. Si tenéis duda con los JOINS, echadle un ojo a un artículo que publicamos con una explicación visual sobre los JOINs SQL.

+---------+------------------------+
| origen  | diasDesdeUltimaLlamada |
+---------+------------------------+
| Gohan   |                      2 |
| Goku    |                      2 |
| Piccolo |                      2 |
+---------+------------------------+

Para ver la eficiencia de los vendedores, queremos comprobar si es proporcional el número de ventas realizadas con la cantidad económica vendida. Para ello queremos saber, por cada vendedor, la cantidad de ventas realizadas y el total vendido por cada uno.
El resultado lo ordenaremos por este último dato.

SELECT IdVendedor,NombreVendedor AS Vendedor,Count(Ventas.CodVendedor) AS Ventas,Sum(Kilos*Precio) AS Total_Vendido From Vendedor,Producto,Ventas WHERE IdProducto=CodProducto AND IdVendedor=CodVendedor GROUP BY IdVendedor ORDER BY Total_Vendido;

Queremos expandir la frutería y para ello tenemos que decidir que grupo de productos voy a ampliar. Por eso, quiero saber, de forma ordenada, la cantidad económica total vendida de cada uno de los grupos de productos de mi frutería.

SELECT NombreGrupo, Sum(Kilos*Precio) AS Total_Vendido FROM Ventas,Producto,Grupo WHERE IdProducto=CodProducto AND Grupo.IdGrupo=Producto.IdGrupo GROUP BY NombreGrupo;

Consultas generales

Nombre y teléfono de los clientes en cuya dirección aparezca Baza.

SELECT Nombre,Telefono FROM Cliente WHERE Direccion LIKE '%Baza%';

Marca, modelo y precio de los coches que se hayan reservado a un precio inferior a 50€

SELECT Marca,Modelo,Precio_Coche FROM Coche,Incluye WHERE Coche.Matricula=Incluye.Matricula AND Precio_Coche<50;

Nombre y dirección de los clientes que hayan hecho alguna reserva con la
agencia Miauto.

SELECT Nombre,Direccion FROM Cliente,Reserva WHERE Codigo=Codigo_Cliente
AND Agencia='Miauto';

Nombre y teléfono de los clientes que no hayan entregado su reserva.

SELECT Nombre,Telefono FROM Cliente,Reserva WHERE Codigo=Codigo_Cliente
AND Entregado is false;

Listado completo de los coches ordenados por marca y modelo.

SELECT * FROM Coche ORDER BY Marca,Modelo;

Marca y modelo de los coches, nombre y DNI de los clientes que hayan hecho
una reserva de algún coche cuya capacidad del depósito sea mayor a 70 litros y el
precio del coche sea mayor a 100€.

SELECT Marca,Modelo,Nombre,DNI FROM Coche,Reserva,Incluye,Cliente WHERE
Codigo_Cliente=Cliente.Codigo AND Reserva.Codigo_Reserva=Incluye.Codigo_Reserva
AND Coche.Matricula=Incluye.Matricula AND Litros_Combustible>70 AND Precio_Coche>100;

Nombre y dirección de los clientes cuyo teléfono comience por 958 ó 950 y el precio total de las reservas que hayan hecho sea inferior o igual a 100€.

SELECT Nombre,Direccion FROM Cliente,Reserva WHERE Cliente.Codigo=Reserva.Codigo_Cliente AND
Precio_Total <= 100 AND (Telefono LIKE '34958%' OR Telefono LIKE '34950%');

Queremos saber cuantos clientes hay por cada nombre ordenado ascendentemente por la cantidad. El resultado debe ser del tipo:

SELECT Nombre,Count(*) AS Cantidad FROM Cliente GROUP BY Nombre ORDER BY Cantidad;

Añadiendo y restando fechas y horas

Las funciones DATE_ADD() y DATE_SUB() nos ofrecen una forma muy sencilla de añadir o restar períodos de una fecha. Como parámetros reciben una fecha y un intervalo. El intervalo es una representación de un período de tiempo como 1 DAY, 50 YEAR, etc.
Yo las uso a menudo para extraer registros del día anterior al que estoy ejecutando la consulta, para temas de informes. Veamos cómo podemos usarlo:

1	SELECT * FROM llamadas WHERE fechaLlamada = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY));

Fácil, sencillo y para toda la familia.




Queremos saber las reservas con precios mayores por cada una de las agencias que han hecho reserva ordenado por la agencia.

SELECT Agencia,max(Precio_Total) AS Cantidad_máxima FROM Reserva GROUP BY Agencia;

Listado la suma de las cantidades totales que ha facturado cada agencia ordenado por esa cantidad.

SELECT Agencia,Sum(Precio_Total) AS Total_facturado FROM Reserva GROUP BY Agencia ORDER BY Total_facturado DESC;

Queremos saber, para cada coche, el precio máximo, el mínimo y la media.
Matrícula PVP_min PVP_max PVP_medio

SELECT Matricula,min(Precio_Coche) AS PVP_min,max(Precio_Coche) AS PVP_max,avg(Precio_Coche) AS PVP_medio
FROM Incluye GROUP BY Matricula;

Queremos saber los registros que tiene la tabla coches.

SELECT Count(*) FROM Coche;

Queremos saber cuantas reservas ha hecho cada cliente, ordenado por el número de reservas.

SELECT Codigo,count(Codigo_Reserva) AS Número_reservas FROM Cliente,Reserva WHERE Codigo=Reserva.Codigo_Cliente GROUP BY Codigo ORDER BY Número_reservas DESC;

Queremos saber cuantas marcas distintas de coches hay en nuestra base de datos.

SELECT DISTINCT Count(*) AS Marcas FROM Coche;

                                                                   o subconsulta

SELECT count(*) FROM (SELECT DISTINCT Marca FROM Coche) AS Marcas

Listado de todas las marcas distintas de coches.

SELECT DISTINCT Marca FROM Coche;

Queremos saber la suma de los precios totales facturados por cada una de las agencias el 7 de enero de 2013

SELECT Agencia,Sum(Precio_Total) AS Total_facturado FROM Reserva GROUP BY Agencia WHERE Fecha_Fin='2013-01-07';

Necesitamos saber cuantas reservas han sido entregadas y cuantas no durante el mes de enero de 2013.

SELECT Entregado,Count(*) AS Cantidad FROM Reserva WHERE Fecha_Inicio BETWEEN '2013-01-01' AND '2013-01-31' GROUP BY Entregado;

De cada cliente, queremos saber su reserva de precio máximo, la de precio mínimo y la media de las reservas realizadas, ordenadas por la media.

SELECT Codigo_Cliente,max(Precio_Total) AS Reserva_max,min(Precio_Total) AS Reserva_min,avg(Precio_Total) AS Reserva_media FROM Reserva GROUP BY Codigo_Cliente ORDER BY Reserva_media DESC;

Subconsultas

Decimos que la subconsulta está anidada dentro de la consulta exterior, y de hecho, es posible anidar subconsultas dentro de otras subconsultas hasta una profundidad considerable. Una subconsulta debe siempre aparecer entre paréntesis.
Las principales ventajas de subconsultas son:

• Permiten consultas estructuradas de forma que es posible aislar cada parte de un comando.
• Proporcionan un modo alternativo de realizar operaciones que de otro modo necesitarían joins y uniones complejos.
• Son, en la opinión de mucha gente, leíbles. De hecho, fue la innovación de las subconsultas lo que dio a la gente la idea original de llamar a SQL “Structured Query Language.”
  
  
  Subconsulta de Tipo 1 (FROM) 
  
  Se trata de un tipo de consulta que realiza una consulta sobre la tabla devuelta por otra consulta anterior.
  
  En este tipo de subconsulta es necesario dar un nombre a la consulta interior mediante la clausula "AS".
  
  Queremos conocer el número de rangos diferentes ocupados por los usuarios de un foro.
   SELECT Count(*) AS Rango
  FROM (SELECT DISTINCT Rango FROM Usuario) AS ListaRangos;
  
  
  
  
  Subconsulta de Tipo 2 (WHERE) 
  
  Este tipo de subconsulta, está ligado a la condición de la consulta exterior.
  
  La consulta interior devolverá registros (de un solo campo) del mismo tipo que el dato con el que compararemos en la condición de la consulta exterior.
  
  
  Queremos conocer el nombre de los usuarios que han adqiurido menos puntos que la media en el foro.
  
  SELECT DISTINCT NombreUsuario
  FROM Usuario,Envios
  WHERE IdUsuario=CodigoUsuario AND Puntos<(SELECT AVG(Envios.Puntos) FROM Envios);
  
  
  
  • Subconsulta ALL (Comparativa con todos los registros de la subconsulta)
    
    Este subtipo de subconsulta where se utiliza para comparar el resultado de una consulta con todos los registros obtenidos en otra.
    
    
    Mostrar el nombre de todos los usuarios con el rango "Kage" que hayan enviado más mensajes que TODOS los usuarios con rango "Genin".
    
    SELECT NombreUsuario,Count(Envios.CodEnvio) AS NEnvios FROM Usuario,Rango,Envios
    WHERE IdUsuario=CodigoUsuario AND CodigoRango=IdRango
    AND NombreRango='Kage' GROUP BY NombreUsuario
    HAVING Count(Envios.CodEnvio) > ALL
    (SELECT Count(Envios.CodEnvio) AS EnviosGenin FROM Envio,Usuario,Rango WHERE IdUsuario=CodigoUsuario AND CodigoRango=IdRango AND NombreRango='Genin' GROUP BY NomProducto);
  • Subconsulta ANY(Comparativa con cualquiera de los registros de la subconsulta)
    
    Este subtipo de subconsulta where se utiliza para comparar el resultado de una consulta con cualquiera de los registros obtenidos en otra.
    
    
    Mostrar el nombre de todos los usuarios con el rango "Kage" que hayan enviado más mensajes que CUALQUIER usuario con rango "Genin".
    
    SELECT NombreUsuario,Count(Envios.CodEnvio) AS NEnvios FROM Usuario,Rango,Envios
    WHERE IdUsuario=CodigoUsuario AND CodigoRango=IdRango
    AND NombreRango='Kage' GROUP BY NombreUsuario
    HAVING Count(Envios.CodEnvio) > ANY
    (SELECT Count(Envios.CodEnvio) AS EnviosGenin FROM Envio,Usuario,Rango WHERE IdUsuario=CodigoUsuario AND CodigoRango=IdRango AND NombreRango='Genin' GROUP BY NomProducto);
    
    
     
  • Subconsulta IN (Indica los registros si están presentes también en la subconsulta)
    
    Este subtipo de subconsulta where se utiliza para comparar el resultado de una consulta con los registros de la subconsulta y mostrar así únicamente los registros en común.
    
    
    Indica el nombre de los usuarios con rango "Genin" que TENGAN LA MISMA reputación que los usuarios con rango "Akatsuki".
    
    SELECT NombreUsuario FROM Usuario,Rango
    WHERE IdRango=CodigoRango AND NombreRango='Genin' AND
    Reputacion IN (SELECT Reputacion FROM Usuario,Rango
    WHERE IdRango=CodRango AND NombreRango='Akatsuki');
  
   
  
  Ejemplos de subconsultas
  Obtiene una lista con el
  nombre, cargo y salario de todos los agentes de ventas cuyo salario es mayor
  que el de todos los jefes y directores.
   
  
  SELECT DISTINCTROW NombreProducto, Precio_Unidad FROM Productos WHERE (Precio_Unidad = (SELECT Precio_Unidad FROM Productos WHERE Nombre_Producto = "Almíbar anisado");
  
  
  Obtiene una lista con el
  nombre y el precio unitario de todos los productos con el mismo precio que el
  almíbar anisado.
   
  

  SELECT DISTINCTROW Nombre_Contacto, Nombre_Compañia, Cargo_Contacto, Telefono FROM Clientes WHERE (ID_Cliente IN (SELECT DISTINCTROW ID_Cliente FROM Pedidos WHERE Fecha_Pedido >= #04/1/93# <#07/1/93#);
  

  
  Obtiene una lista de las
  compañías y los contactos de todos los clientes que han realizado
  un pedido en el segundo trimestre de 1993.
  

  
  SELECT Nombre, Apellidos FROM Empleados AS E WHERE EXISTS (SELECT * FROM Pedidos AS O WHERE O.ID_Empleado = E.ID_Empleado);
  
  
  Obtiene una lista con el nombre y la dirección de los vendedores que hayan hecho 
  ventas de mas kilos que la media.
  
  SELECT DISTINCT NombreVendedor,Direccion FROM
  Vendedor,Ventas WHERE Vendedor.IdVendedor=Ventas.CodVendedor AND Kilos>(SELECT AVG(Ventas.Kilos) FROM Ventas); Obtiene una lista con el nombre y la direcciónd de los vendedores que han hecho ventas mayores que la media. SELECT DISTINCT NombreVendedor,Direccion FROM Vendedor,Ventas,Producto WHERE IdVendedor=CodVendedor AND IdProducto=CodProducto AND Kilos*Precio> (SELECT AVG(Ventas.Kilos*Producto.Precio) FROM Ventas,Producto WHERE IdProducto=CodProducto);
  
  Obtiene el máximo de los precios medios de los productos de cada uno de los grupos.
  
  SELECT PrecioMedio.IdGrupo,MAX(PrecioMedio.Media)
  FROM (SELECT AVG(Precio) AS Media,IdGrupo FROM Producto GROUP BY IdGrupo) AS PrecioMedio;
  
  
  Obtiene el número de poblaciones distintas de las que son los vendedores.
  
  SELECT Count(*) AS N_Poblaciones
  FROM (SELECT DISTINCT Poblacion FROM Vendedor) AS Poblaciones;
  
  
  Obtiene el apellido de los jugadores con la misma posición que "Sánchez":
  
  SELECT APELLIDO FORM EMPLE WHERE POSICION = (SELECT OFICIO
  FROM EMPLE WHERE APELLIDO='Sanchez');
  
  
  Obtiene todos los datos de la tabla Jugadores cuya sede está en Madrid o Barcelona:
  
  SELECT *    FROM JUGADORES WHERE EQUIPO_NOM IN (SELECT EQUIPO_NOM  
  FROM SEDE WHERE LOC IN ('MADRID', 'BARCELONA');