View
227
Download
0
Category
Preview:
Citation preview
7/25/2019 Unidad_04_03_Consultas_III.pdf
1/32
Fundamentos Bases de Datos
Consultas
3 parte
7/25/2019 Unidad_04_03_Consultas_III.pdf
2/32
Fundamentos Bases de Datos
Consultas
Es aquella en la que se puede consultar informacin de ms
de una tabla.
Se aprovechan los campos relacionados para unirlas (JOIN).
Sintaxis:
SELECT [DISTINCT] select_expr [,select_expr] ...[FROM referencias_tablas]
[WHERE filtro]
[GROUP BY expr [, expr].... ]
[HAVING filtro_grupos][ORDER BY {nombre_columnas | expr | posicin} [ASC | DESC] , ...]
7/25/2019 Unidad_04_03_Consultas_III.pdf
3/32
Fundamentos Bases de Datos
Consultas
referencias_tablas:
referencia_tabla[, referencia_tabla] ...
| referencia_tabla [INNER | CROSS] JOIN referencia_tabla [ON condicin]
| referencia_tabla LEFT [OUTER] JOIN referencia_tabla ON condicin
| referencia_tabla RIGHT [OUTER] JOIN referencia_tabla ON condicin
referencia_tabla:
nombre_tabla [[AS] alias] SQL2
SQL1
7/25/2019 Unidad_04_03_Consultas_III.pdf
4/32
Fundamentos Bases de Datos
Consultas
referencias_tablas:
referencia_tabla [, referencia_tabla] ...
referencia_tabla:nombre_tabla [[AS] alias]
Consiste en el producto cartesiano de las tablas que es la
combinacin de las filas de una tabla unidas a las filas de lasotras.
7/25/2019 Unidad_04_03_Consultas_III.pdf
5/32
Fundamentos Bases de Datos
Consultas
Supongamos que una BD de mascotas tiene dos tablas (la deanimales y la de propietarios)SELECT * FROM propietarios;
+-----------+--------------------+
| dni | nombre |
+-----------+--------------------+
| 51993482Y | Jos Prez || 2883477X | Matas Fernndez |
| 37276317Z | Francisco Martnez |
+-----------+--------------------+
SELECT * FROM animales;
+--------+----------+-------+-------------+| codigo | nombre | tipo | propietario |
+--------+----------+-------+-------------+
| 1 | Cloncho | gato | 51993482Y |
| 2 | Yoda | gato | 51993482Y |
| 3 | Sprocket | perro | 37276317Z |
+--------+----------+-------+-------------+
A simple vista,
observamos que Matas
no tiene mascota
7/25/2019 Unidad_04_03_Consultas_III.pdf
6/32
Fundamentos Bases de Datos
Consultas
SELECT * FROM animales, propietarios;
+--------+----------+-------+-------------+-----------+--------------------+| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+
| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| 3 | Sprocket | perro | 37276317Z | 51993482Y | Jos Prez |
| 1 | Cloncho | gato | 51993482Y | 2883477X | Matas Fernndez |
| 2 | Yoda | gato | 51993482Y | 2883477X | Matas Fernndez || 3 | Sprocket | perro | 37276317Z | 2883477X | Matas Fernndez |
| 1 | Cloncho | gato | 51993482Y | 37276317Z | Francisco Martnez |
| 2 | Yoda | gato | 51993482Y | 37276317Z | Francisco Martnez |
| 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
+--------+----------+-------+-------------+-----------+--------------------+
Se genera un conjunto de resultados con todas las
combinaciones posibles entre las filas de las dos tablas. Esto no
tiene mucha utilidad a menos que se aplique un filtro (WHERE)
por el campo relacionado (Clave fornea vs clave primaria).
7/25/2019 Unidad_04_03_Consultas_III.pdf
7/32
Fundamentos Bases de Datos
Consultas
Join=Producto cartesiano + filtro
SELECT * FROM animales, propietarios
WHERE propietarios.dni = animales.propietario;
+--------+----------+-------+-------------+-----------+--------------------+
| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+
| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
+--------+----------+-------+-------------+-----------+--------------------+
7/25/2019 Unidad_04_03_Consultas_III.pdf
8/32
Fundamentos Bases de Datos
Consultas
7/25/2019 Unidad_04_03_Consultas_III.pdf
9/32
Fundamentos Bases de Datos
Consultas
Listado de pedidos gestionados por cada empleado:
SELECT Empleados.Nombre,Clientes.NombreCliente,Pedidos.CodigoPedido
FROM Clientes, Pedidos, Empleados
WHERE Clientes.CodigoCliente=Pedidos.CodigoCliente
AND
Empleados.CodigoEmpleado = Clientes.CodigoEmpleadoRepVentasORDER BY Empleados.Nombre;
+-----------------+--------------------------------+--------------+
| Nombre | NombreCliente | CodigoPedido |
+-----------------+--------------------------------+--------------+
| Emmanuel | Naturagua | 18 |
| Emmanuel | Beragua | 16 |.....
| Walter Santiago | DGPRODUCTIONS GARDEN | 65 |
| Walter Santiago | Gardening & Associates | 58 |
+-----------------+--------------------------------+--------------+
7/25/2019 Unidad_04_03_Consultas_III.pdf
10/32
Fundamentos Bases de Datos
Consultas
Obtener cuntos pedidos ha gestionado cada empleado:SELECT Empleados.Nombre, COUNT(Pedidos.CodigoPedido) as NumeroDePedidos
FROM Clientes, Pedidos, Empleados
WHERE
Clientes.CodigoCliente = Pedidos.CodigoCliente
AND
Empleados.CodigoEmpleado = Clientes.CodigoEmpleadoRepVentas
GROUP BY Empleados.Nombre
ORDER BY NumeroDePedidos;
+-----------------+-----------------+
| Nombre | NumeroDePedidos |
+-----------------+-----------------+| Michael | 5 |
| Lorena | 10 |
| ... | .. |
| Walter Santiago | 20 |
+-----------------+-----------------+
7/25/2019 Unidad_04_03_Consultas_III.pdf
11/32
Fundamentos Bases de Datos
Consultas
Join Interna
De equivalencia (INNER JOIN)
Natural (NATURAL JOIN)
Producto Cartesiano (CROSS JOIN)
Join Externa
De tabla izquierda (LEFT [OUTER] JOIN) De tabla derecha (RIGHT [OUTER] JOIN)
Completa (FULL [OUTER] JOIN)
7/25/2019 Unidad_04_03_Consultas_III.pdf
12/32
Fundamentos Bases de Datos
Consultas
Se calcula el producto cartesiano de todos los registros y
solo se seleccionan aquellos registros que satisfacen lascondiciones dadas, teniendo en cuenta que los valores
NULL no se combinan.
d d
7/25/2019 Unidad_04_03_Consultas_III.pdf
13/32
Fundamentos Bases de Datos
Consultas
Supongamos la siguiente situacin en la BD de mascotas:SELECT * FROM propietarios;
+-----------+--------------------+
| dni | nombre |
+-----------+--------------------+
| 51993482Y | Jos Prez |
| 2883477X | Matas Fernndez || 37276317Z | Francisco Martnez |
+-----------+--------------------+
SELECT * FROM animales;
+--------+----------+-------+-------------+
| codigo | nombre | tipo | propietario |+--------+----------+-------+-------------+
| 1 | Cloncho | gato | 51993482Y |
| 2 | Yoda | gato | 51993482Y |
| 3 | Sprocket | perro | 37276317Z |
| 4 | Arco | perro | NULL |
+--------+----------+-------+-------------+
Observamos que Arco an
no tiene propietario
F d B d D
7/25/2019 Unidad_04_03_Consultas_III.pdf
14/32
Fundamentos Bases de Datos
Consultas
SELECT * FROM animales INNER JOIN propietarios
ON animales.propietario = propietarios.dni;
+--------+----------+-------+-------------+-----------+--------------------+
| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+
| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
+--------+----------+-------+-------------+-----------+--------------------+
IMPORTANTE: Los animales sin propietarios no salen.
Es equivalente a:
SELECT * FROM animales , propietarios
where animales.propietario = propietarios.dni;
F d t B d D t
7/25/2019 Unidad_04_03_Consultas_III.pdf
15/32
Fundamentos Bases de Datos
Consultas
Especializacin de INNER JOIN.
Se comparan todas las columnas que tengan el mismo
nombre en ambas tablas. La tabla resultante contienesolo una columna por cada par de columnas con el mismo
nombre.
F d t B d D t
7/25/2019 Unidad_04_03_Consultas_III.pdf
16/32
Fundamentos Bases de Datos
Consultas
SELECT Empleados.CodigoEmpleado,
Empleados.Nombre,CodigoOficina, Oficinas.Ciudad
FROM Empleados NATURAL JOIN Oficinas;
+----------------+-----------------+---------------+----------------------+
| CodigoEmpleado | Nombre | CodigoOficina | Ciudad |
+----------------+-----------------+---------------+----------------------+
| 1 | Marcos | TAL-ES | Talavera de la Reina |
| 2 | Ruben | TAL-ES | Talavera de la Reina |
....
| 31 | Mariko | SYD-AU | Sydney |
+----------------+-----------------+---------------+----------------------+
Aunque CodigoOficina es un campo que est en las dos
tablas no debe precederse por ningn nombre de tabla
puesto que NATURAL JOIN devuelve un nico campo por
cada pareja de campos con el mismo nombre.
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
17/32
Fundamentos Bases de Datos
Consultas
Devuelve el producto cartesiano de las dos tablas
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
18/32
Fundamentos Bases de Datos
Consultas
Equivalente a SELECT * FROM animales, propietarios;SELECT * FROM animales CROSS JOIN propietarios;
+--------+----------+-------+-------------+-----------+--------------------+
| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+
| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 1 | Cloncho | gato | 51993482Y | 2883477X | Matas Fernndez |
| 1 | Cloncho | gato | 51993482Y | 37276317Z | Francisco Martnez || 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 2883477X | Matas Fernndez |
| 2 | Yoda | gato | 51993482Y | 37276317Z | Francisco Martnez |
| 3 | Sprocket | perro | 37276317Z | 51993482Y | Jos Prez |
| 3 | Sprocket | perro | 37276317Z | 2883477X | Matas Fernndez |
| 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
| 4 | Arco | perro | NULL | 51993482Y | Jos Prez || 4 | Arco | perro | NULL | 2883477X | Matas Fernndez |
| 4 | Arco | perro | NULL | 37276317Z | Francisco Martnez |
+--------+----------+-------+-------------+-----------+--------------------+
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
19/32
Fundamentos Bases de Datos
Consultas
Las tablas relacionadas no requieren que haya una
equivalencia.
El registro es seleccionado para ser mostrado aunque
no haya otro registro que le corresponda.OUTER JOIN se subdivide dependiendo de la tabla a la
cual se admitirn los registros que no tienen
correspondencia, ya sean de tabla izquierda, de tabla
derecha o combinacin completa.
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
20/32
Fundamentos Bases de Datos
Consultas
LEFT : Composicin de tabla izquierda
Incluir todos los registros de la tabla que aparece a la
IZQUIERDA (LEFT), aunque no tengan coincidencia con
los registros de la tabla derecha
RIGHT : Composicin de tabla derecha
Incluir todos los registros de la tabla que aparece a la
DERECHA (RIGHT) aunque no tenga coincidencia con
los registros de la tabla izquierda
FULL: Composicin externa completa
Incluye todos los registros sin coincidencias, tanto los
de la tabla IZQUIERDA como los de la DERECHA
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
21/32
Fundamentos Bases de Datos
Consultas
SELECT * FROM animales LEFT OUTER JOIN propietarios
ON animales.propietario = propietarios.dni;
+--------+----------+-------+-------------+-----------+--------------------+
| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+
| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
| 4 | Arco | perro | NULL | NULL | NULL |
+--------+----------+-------+-------------+-----------+--------------------+
animales est a la izquierda propietarios est a la derecha
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
22/32
Fundamentos Bases de Datos
Consultas
SELECT * FROM animales RIGHT OUTER JOIN propietarios
ON animales.propietario = propietarios.dni;
+--------+----------+-------+-------------+-----------+--------------------+
| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+
| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| NULL | NULL | NULL | NULL | 2883477X | Matas Fernndez || 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
+--------+----------+-------+-------------+-----------+--------------------+
animales est a la izquierda propietarios est a la derecha
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
23/32
Fundamentos Bases de Datos
Consultas
SELECT * FROM animales FULL OUTER JOIN propietarios
ON animales.propietario = propietarios.dni;
+--------+----------+-------+-------------+-----------+--------------------+
| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
| 4 | Arco | perro | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 2883477X | Matas Fernndez |
+--------+----------+-------+-------------+-----------+--------------------+
(Slo vlida para Oracle)
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
24/32
Fundamentos Bases de Datos
Consultas
Aade al conjunto de resultados de una SELECT losresultados de otra SELECT.
SELECT .... FROM ....
UNION [ALL]
SELECT .... FROM ....
El parmetro ALL incluye todos los registros de las dos
SELECT, incluyendo los iguales. Si no se indica ALL seexcluyen los duplicados.
Es til en MySQL para simular FULL OUTER JOIN.
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
25/32
Consultas
SELECT * FROM animales LEFT OUTER JOIN propietarios
ON animales.propietario = propietarios.dniUNION
SELECT * FROM animales RIGHT OUTER JOIN propietarios
ON animales.propietario = propietarios.dni;
+--------+----------+-------+-------------+-----------+--------------------+
| codigo | nombre | tipo | propietario | dni | nombre |
+--------+----------+-------+-------------+-----------+--------------------+
| 1 | Cloncho | gato | 51993482Y | 51993482Y | Jos Prez |
| 2 | Yoda | gato | 51993482Y | 51993482Y | Jos Prez |
| 3 | Sprocket | perro | 37276317Z | 37276317Z | Francisco Martnez |
| 4 | Arco | perro | NULL | NULL | NULL || NULL | NULL | NULL | NULL | 2883477X | Matas Fernndez |
+--------+----------+-------+-------------+-----------+--------------------+
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
26/32
Consultas
A veces, es necesario obtenerinformacin de relaciones reflexivas.
Por ejemplo, un informe de empleados
donde junto a su nombre y apellidos
apareciera el nombre y apellidos desus subordinados.
Para ello, es necesario hacer una JOIN
entre registros de la misma tabla.
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
27/32
Consultas
SELECT
concat(concat(emp.Nombre,' '),emp.Apellido1) as Empleado,
concat(concat(jefe.Nombre,' '),jefe.Apellido1) as EsJefeDe
FROM Empleados emp INNER JOIN Empleados jefe
ON emp.CodigoEmpleado=jefe.CodigoJefe;
+-------------------+-------------------------+| ElEmpleado | EsJefeDe |
+-------------------+-------------------------+
| Marcos Magaa | Ruben Lpez |
| Ruben Lpez | Alberto Soria |
....
| Alberto Soria | Kevin Fallmer |
| Kevin Fallmer | Julian Bellinelli |
| Kevin Fallmer | Mariko Kishi |
+-------------------+-------------------------+
Alias 1 Alias 1
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
28/32
Consultas
Si quisiramos que tambin apareciesen los empleados
sin subordinados:
SELECT
concat(concat(emp.Nombre,' '),emp.Apellido1) as Empleado,
concat(concat(jefe.Nombre,' '),jefe.Apellido1) as EsJefeDe
FROM Empleados emp LEFT OUTER INNER JOIN Empleados jefeON emp.CodigoEmpleado=jefe.CodigoJefe;
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
29/32
Consultas
Son aquellas que utilizan sentencias SELECT en la clusula
FROM en lugar de nombres de tablas.
La tabla derivada que figura en la clusula es una tabla
temporal.
Sirven para obtener informacin relacionada de forma ms
avanzada.
No tienen limitacin (se pueden unir a otras, filtrar, agrupar,).
Ejemplo:SELECT * FROM
(SELECT CodigoEmpleado, Nombre
FROM Empleados
WHERE CodigoOficina='TAL-ES') tabla_derivada;
Alias de la tabla temporal (noes obligatorio)
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
30/32
Consultas
Se desea sacar el importe del pedido de menor coste detodos los pedidos:
1: sacar el total de cada pedido
2: sacar el pedido con menor coste con la funcin de
columna MIN
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
31/32
Consultas
1) Calcular el total de cada pedidoSELECT SUM(Cantidad*PrecioUnidad) as total, CodigoPedido
FROM DetallePedidos
GROUP BY CodigoPedido;
+-------+--------------+| total | CodigoPedido |
+-------+--------------+
| 1567 | 1 |
| 7113 | 2 |
| 10850 | 3 |
....| 154 | 117 |
| 51 | 128 |
+-------+--------------+
Fundamentos Bases de Datos
7/25/2019 Unidad_04_03_Consultas_III.pdf
32/32
Consultas
2) Para calcular el menor pedido, se puede hacer unatabla derivada de la consulta anterior y con la funcin
MIN obtener el menor de ellos:SELECT MIN(total) FROM (
SELECT SUM(Cantidad*PrecioUnidad) as total,CodigoPedido
FROM DetallePedidos
GROUP BY CodigoPedido
) TotalPedidos;
+------------+
| MIN(total) |
+------------+
| 4 |
+------------+
TotalPedidos es la tabla derivada formada por el
resultado de la consulta entre parntesis
Recommended