lunes, 11 de febrero de 2013

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');




No hay comentarios:

Publicar un comentario en la entrada