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