Capítulo 4 Realización de consultas · PDF fileConsultas cuya condición depende del resultado de otra/s consulta/s Se diferencian dos tipos: • No correlacionadas: ... INNER JOIN:

Capítulo 4

Realización de consultas

ÍNDICE CAPÍTULO 4

Introducción sentencia SELECTBase de datos de ejemploConsultas básicas

• Cláusula ORDER BY• Cláusula DISTINCT• Cláusula LIMIT• Expresiones• Funciones propias de MySQL• Cláusula WHERE• Predicados en SQL• Funciones de agregado• Cláusula GROUP BY• Cláusula HAVING

SubconsultasConsultas sobre varias tablas

• Reunión (JOIN)• Unión/Intersección/Diferencia

Sentencia SELECT

Formato básico sentencia SELECT

SELECT [ALL | DISTINCT | DISTINCTROW]expresion_select,...FROM referencias_de_tablasWHERE condiciones[GROUP BY

[ASC | DESC], ... [WITH ROLLUP]][HAVING condiciones][ORDER BY

[ASC | DESC] ,...][LIMIT ]

Base de datos ejemplo I

Diseño

equipo(id_equipo, nombre_equipo, ciudad, web_oficial, puntos)

jugador(id_jugador, nombre, apellido, id_capitan, posicion, fecha_alta, salario_bruto, equipo)

partido(elocal, evisitante, resultado, fecha, arbitro)

Base de datos ejemplo II

Datos tabla jugadores

Base de datos ejemplo III

Datos tabla equipos

Base de datos ejemplo IV

Datos tabla partidos

Consultas básicas

Ejemplo

-Obtener todos los datos de todos los equipos

SELECT * FROM equipo;

Claúsula ORDER BY

Permite mostrar resultados ordenados por uno o varios campos

Ordenación por un campo:

-Seleccionar los nombres, apellido y posición de todos los jugadores ordenados por posición

SELECT nombre, apellido, posición FROM jugador ORDER BY posición;

Ordenación por varios campos:

-Seleccionar el nombre, equipo y posición de los jugadores ordenados por equipo y posición

SELECT nombre, equipo, posicionFROM jugadorORDER BY equipo, posición;

Cláusula DISTINCT

Muestra únicamente filas con valoresdistintos

-Seleccionar los distintos equipos que existen en la tabla jugador

SELECT DISTINCT equipoFROM jugador;

Cláusula LIMIT

Limita el número de filas en el resultado

-Obtener los 5 primeros registros de la tabla jugador

SELECT * FROM jugador LIMIT 5;

-Obtener los datos de los tres últimos equipos clasificados (son 6 equipos en total)

SELECT * FROM equipo ORDER BY puesto DESC LIMIT 3,6;

Expresiones

Uso de operadores sobre las columnas a mostrar

Ejemplo

-Calcular el salario neto anual a percibir por cada jugador suponiendo que el IRPF es un 18%.

SELECT nombre, apellido, salario * 0,82 AS “salario neto anual”FROM jugador;

Funciones propias de MySQL

Funciones de control de flujo

CASE, IF, IFNULL, NULLIF

Funciones de cadena

-Funciones de comparación: LIKE, NOT LIKE y STRCMP.

-Funciones de expresiones regulares: NOT REGEXP y REGEXP=RLIKE.Funciones matemáticas

Funciones de fecha y hora

Funciones de búsqueda tipo full-text

Funciones XML

Funciones de compresión y codificación

Cláusula WHERE y predicados I

Predicados en claúsulas WHERE

x = y x es igual a yx <> y ó x!=y x no es igual a yx < y x es menor que yx > y x es mayor que yx <= y x es menor o igual que yx >= y x es mayor o igual que y

-Seleccionar datos de jugadores que no pertenezcan al equipo 3:

SELECT * FROM jugador WHERE equipo <> 3;

Cláusula WHERE y predicados II

Predicado para comprobar valor nulo

nom_columna IS [NOT] NULL

-Seleccionar aquellos datos de equipos cuya web es nula

SELECT * FROM equipo WHERE web IS NULL;

Predicado IN: pertenencia a un conjunto

expresión [NOT] IN (valor1, valor2,…)

-Obtener los datos de los equipos menos los de Valencia y Madrid

SELECT * FROM equipoWHERE ciudad NOT IN (‘Valencia’, ‘Madrid’);

Cláusula WHERE y predicados III

Predicado [NOT] BETWEEN exp1– AND exp2

expresion1 [NOT] BETWEEN expr2 AND expr3

-Obtener los datos de partidos de marzo de 2010

SELECT * FROM partidoWHERE fecha BETWEEN 01-03-2010 AND 31-03-2010;

Predicado LIKE

nom_columna [NOT] LIKE cte_alfanumérica

-Obtener los datos de los equipos cuyo nombre empiece por E

SELECT * FROM equipoWHERE nombre_equipo LIKE ‘E%’;

Cláusula WHERE y predicados IV

Predicado REGEXP y expresiones regulares

nom_columna [NOT] REGEXP cte_alfanumérica

-Obtener los datos de los equipos cuyo nombre empiece por vocal

SELECT * FROM equipoWHERE nombre_equipo REGEXP ‘[aeiou]+’

Predicados compuestos

Combinación de condiciones con OR y AND

-Seleccionar el nombre de jugadores de los equipos 1 y 2 que jueguen como pivot.SELECT nombre FROM jugadorWHERE posicion = “base” AND (equipo=1 OR equipo=2);

Funciones de agregado I

Funciones cuyos valores de entrada son varias filas en una consulta

AVG: devuelve la media de los valores de la colección.

MAX: devuelve el valor máximo de la colección.

MIN: devuelve el valor mínimo.

SUM: devuelve la suma.

COUNT: devuelve el número de elementos que tiene la colección.-

Funciones de agregado II

Formato 1

nom_función ( [DISTINCT] nom_columna )

Formato 2

nom_función (expresión)

Formato 3

COUNT (*)

Funciones de agregado III

-Calcular el número de jugadores que miden más de dos metros

SELECT COUNT(comision) FROM jugador WHERE altura > 2,00;

-Calcular el salario medio de todos los jugadores

SELECT AVG(salario) AS “Salario Medio” FROM jugador;

-Encontrar el salario más alto, el más bajo y la diferencia entre ambos

SELECT MAX(salario), MIN(salario), MAX(salario) -MIN(salario) “Diferencia salarios” FROM jugador;

-Hallar el número de ciudades en las que hay equipos registrados

SELECT COUNT (DISTINCT ciudad) FROM equipo;

Cláusula GROUP BYConsultas con agrupamiento de filas

Formato

GROUP BY col1 [, col2]...

-Seleccionar el número de jugadores por equipo

SELECT COUNT(*) GROUP BY equipo;

-Seleccionar el salario, mínimo y máximo de los jugadores, agrupados por equipo

SELECT equipo, MIN(salario), MAX(salario)FROM jugadorGROUP BY equipo;

Cláusula HAVING

Permite imponer condiciones sobre el resultado de funciones de agregado

Formato

HAVING condición

-Seleccionar el salario medio de cada equipo, pero solo para aquellos cuya media sea superior a 50000

SELECT AVG(salario) FROM jugadorGROUP BY equipoHAVING AVG(salario) > 50000;

Subconsultas

Consultas cuya condición depende del resultado de otra/s consulta/s

Se diferencian dos tipos:

• No correlacionadas:la consulta interna puede realizarse independientemente de la externa.

• Correlacionadas: la consulta interna debe tener en cuenta algun campo de la consulta externa para realizarse.

Ejemplo subconsulta no correlacionada:

-Obtener los datos de los jugadores cuyo salarios sea mayor que el de Gasol.

SELECT * FROM jugadores WHERE salario> (SELECT salario FROM jugadores WHERE apellido=’Gasol’);

Predicados en subconsultasCuantificadores (ALL, SOME, ANY)

Cuantificador ALL: establece una condición sobre todos los valores devueltos por la subconsulta.

-Obtener el nombre de los jugadores que ganen más que todos los del equipo 2.

SELECT nombre FROM jugador WHERE salario > ALL (SELECT salario FROM jugador WHERE equipo= 2);

Cuantificador ANY o SOME: condición se cumple si alguno de los valores devueltos por la subconsulta coincide con el criterio de búsqueda.

-Seleccionar los jugadores que ganen más que alguno de los del equipo 5.

SELECT nombre FROM jugador WHERE salario > ANY (SELECT salario FROM jugador WHERE equipo = 5);

Predicados en subconsultasCuantificadores (IN y EXISTS)

Predicado IN: implica considerar la subconsulta como un conjunto de valores a los que aplicar el predicado

-Datos de los jugadores que jueguen en Zaragoza:

SELECT * FROM jugadorWHERE equipo IN (SELECT id_equipo FROM equipo WHERE ciudad=’Zaragoza’);

Predicado EXISTS: Devuelve “Verdadero” si la subconsulta subsiguiente es no vacía y “Falso” en caso contrario

-Obtener los datos de los jugadores pero solo si hay más de 10 equipos

SELECT * FROM jugador WHERE EXISTS (SELECT COUNT(*) FROM equipo HAVING COUNT(*)>10);

Consultas correlacionadas I

Consulta en la que se requieren campos de la consulta externa para resolver la subconsulta o consulta interna.

Procesamiento de consultas correlacionadas:

1. La consulta externa pasa los valores de cada fila de la consulta a la consulta interna o subconsulta.

2. La consulta interna usa los valores que le pasa la consulta externa para evaluarlos.

3. La consulta interna devuelve los valores que cumplan las condiciones a la consulta externa.

4. Se repite el proceso para todas las filas de la consulta externa.

-Obtener los datos de jugadores que miden más que la media de su equipoSELECT * FROM jugador j1 WHERE altura >(SELECT AVG(altura) FROM jugador j2 WHERE

j1.equipo=j2.equipo);

Consultas correlacionadas II

Consultas correlacionadas con predicado EXISTS

Este predicado es frecuentemente usado en subconsultas correlacionadas para verificar cuando un valor recuperado por la consulta externa existe en el conjunto de resultados obtenidos por la consulta interna

-Obtener los datos de los capitanes de los equipos

SELECT * FROM jugador j1WHERE j1.id_jugador EXISTS (SELECT * FROM jugador j2WHERE j1.id_jugador = j2.capitan);

Otros usos Subconsultas

Subconsultas como una expresión (en la claúsula SELECT)

-Datos de los jugadores con el salario medio de su equipo y la diferencia de éste con el de cada jugador:

SELECT num_emp, sal, (SELECT AVG(sal) 'media' FROM emp) AS t, sal-(SELECT avg(sal) 'media' FROM emp) AS diferencia;

Consultas como tablas derivadas

-Obtener el máximo salario total de todos los equipos

SELECT max(tderivada.maxsal) FROM (SELECT sum(salario) 'maxsal' FROM jugador GROUP BY

equipo) AS tderivada;

CONSULTAS SOBRE VARIAS TABLAS

A veces se requiere el uso de más de una tabla

-Porque la condición implica otros campos que están en otras tablas

-Porque queremos mostrar información que está en más de una tabla

Para ello disponemos de la operación de reunión o JOIN que es equivalente al producto cartesiano de dos o más tablas

El proceso de diseño de una consulta de varias tablas se resume en:

1. Analizar la consulta para ver las tablas necesarias para resolverla2. Incluir dichas tablas en la cláusula FROM3. Filtrar filas espurias usando campos comunes o claves ajenas4. Añadir los filtros o cláusulas necesarias tal como si trabajásemos con una

única tabla

CONSULTAS SOBRE VARIAS TABLASOperaciones de reunión (JOIN) I

La operación JOIN incluye variantes

INNER JOIN: composición interna

-Obtener número de jugadores de equipos de Madrid:

SELECT COUNT(*) FROM jugador j, equipo eWHERE j.equipo = e.id_equipo AND ciudad= “Madrid”;

OUTER JOIN: composición externa

Se incluyen registros combinado aunque tengan valor nulo.Hay dos variantes:

-LEFT incluye solo los registros nulos de la tabla izquierda -RIGHT incluye solo los registros nulos de la tabla derecha

CONSULTAS SOBRE VARIAS TABLASOperaciones de reunión (JOIN) II

-Mostrar los datos de todos los jugadores incluyendo datos de sus equipos en caso de tener

SELECT COUNT(*) FROM jugador j LEFT JOIN equipo eON j.equipo = e.id_equipo;

STRAIGHT_JOIN: composición directa

Equivalente a la reunión interna salvo que así se obliga a procesar primero la tabla indicada en primer lugar (tendrá su importancia en optimización)

CONSULTAS SOBRE VARIAS TABLASOperaciones de Unión

Unión

Se muestra el resultado de dos consultas con el mismo número y tipo de campos

Sintaxis

SELECT ...UNION [ALL | DISTINCT]SELECT ...[UNION [ALL | DISTINCT]SELECT ...]

-Mostrar, ordenados por nombre, los nombres de jugadores de los equipos 1 y 2.

(SELECT nombre FROM jugador WHERE equipo=1)UNION(SELECT nombre FROM jugador WHERE equipo=2)ORDER BY 1 LIMIT 10;

CONSULTAS SOBRE VARIAS TABLASOperaciones de Intersección

Intersección

Operación entre dos conjuntos cuyo resultado es el conjunto de elementos comunes en ambos..

-Obtener el listado de equipos que han jugado como locales y visitantes.

Si lo hiciésemos con otro gestor como Postgresql:

SELECT local FROM equipoINTERSECTSELECT visitante FROM equipo;

CONSULTAS SOBRE VARIAS TABLAS

Operaciones de Diferencia

Diferencia

Operación entre dos conjuntos cuyo resultado es el conjunto de elementos que son distintos-Para obtener todos los nombres de jugadores del equipo 1 que no coincidan con ningún nombre del equipo 2

• En PostgreSQL:

SELECT nombre FROM equipo WHERE id_equipo=1EXCEPTSELECT nombre FROM equipo WHERE id_equipo=2;

• En MySQL tenemos dos posibilidades, una subconsulta y una combinación:

SELECT nombre FROM equipo WHERE id_equipo=1 AND nombre NOT IN(SELECT nombre FROM equipo WHERE id_equipo=2;

SELECT DISTINCT a.nombreFROM jugador a LEFT JOIN jugador b USING a.nombreWHERE (a.equipo=1 OR a.equipo=2)AND (b.equipo=1 OR b.equipo=2)AND b.nombre IS NULL;

FIN CAPÍTULO 4

Documents

Capítulo 4 Realización de consultas · PDF fileConsultas cuya condición depende del resultado de otra/s consulta/s Se diferencian dos tipos: • No correlacionadas: ... INNER JOIN: