Gestión de Bases de Datos

Esta página es de solo lectura. Puedes ver la fuente pero no puedes cambiarla. Pregunta a tu administrador si crees que esto es incorrecto.
====== Tratamiento de Datos y Consultas ======
===== Sentencias DML ====

En el lenguaje SQL existen 4 sentencias que forman el DML (Data Manipulation Language, Lenguaje de Manipulación de Datos). Son aquellas sentencias que nos permiten manipular la información que almacenamos en las Bases de Datos.

Existen 4 instrucciones que nos permitirán insertar datos en una tabla (''INSERT''), modificar esos datos (''UPDATE''), eliminarlos (''DELETE'') y consultarlos (''SELECT'').
===== Base de Datos de prueba ====

Todos los ejemplos de este bloque se han escrito tomando como referencia la Base de Datos ''reservas'' que se proporciona junto con este documento. También pueden aparecer algunos ejemplos tomando como referencia las Bases de Datos de [[http://dev.mysql.com/doc/index-other.html|ejemplos oficiales de MySQL]]

A continuación se muestran el modelo entidad-relación y relacional de esta Base de Datos de prueba:

{{ reservas.png | Modelo E-R. Base de Datos de prueba (reservas)}}

<code>
pistas (#id, codigo, tipo, precio, veces_reservada, -id_polideportivo)
pistas_abiertas(#-id_pista, operativa, precio, fecha_ultima_reserva, proxima_revision)
pistas_cerradas (#-id_pista, fecha_clausura, motivo)
polideportivos (#id, nombre, direccion, ciudad, extension)
servicios_polideportivo(#id, servicio, -id_polideportivo)
reservas (#id, fecha_reserva, fecha_uso, precio, -id_pista)
usuarios (#id, dni, nombre, apellidos, email, ciudad, fecha_nacimiento, descuento, fecha_alta)
usuario_usuario (#(-id_usuario, -id_amigo))
usuario_reserva (#(-id_usuario, -id_reserva), asiste)
</code>

La base de datos puede descargarse desde {{:extra:bd-reservas.zip |este enlace}}
===== Modificar Información =====

==== Inserción de registros ====
La inserción de nuevos registros a una tabla se efectúa con la sentencia ''INSERT'', que tiene el siguiente formato:

<code sql>
INSERT INTO nombre_tabla [ '('columnas')' ] 
{ VALUES '(' { valores } ')',} | consulta
</code>

Indicar las columnas en las que insertas datos es opcional. Si no se indican se debe dar valor a todas las columnas en el órden en que están en la tabla. Si se indican las columnas, se da solamente valor a esos campos.

Veamos algunos ejemplos:

  * Para insertar una fila con todos los campos de la tabla
<code sql>
-- La tabla pistas tiene los campos: (id, codigo, tipo, precio, id_polideportivo)
INSERT INTO pistas
VALUES (1 , 'A34565', 'tenis', '7.34', '12');
</code>

  * Para insertar una fila en una tabla dando valor a ciertas columnas

<code sql>
INSERT INTO usuarios (dni, nombre, apellidos, email, fecha_nacimiento)
VALUES ('123456789A', 'Antonio', 'García', 'agarcia@gmail.com', '1990-12-12');

INSERT INTO usuarios
VALUES (200, '123456789A', 'Antonio', 'García', 'agarcia@gmail.com', 'Zaragoza', '1990-12-12', 0.3, '2003-02-01');

INSERT INTO usuarios (id, dni, nombre, apellidos, email, fecha_nacimiento)
VALUES (45, '123456789A', 'Antonio', 'García', 'agarcia@gmail.com', '1990-12-12');
</code>

  * Para insertar varias filas en una tabla

<code sql>
INSERT INTO usuarios (dni, nombre, apellidos, email, fecha_nacimiento)
VALUES ('123456789A', 'Pepe', 'Sanz', 'psanz@gmail.com', '1990-12-12'),
       ('987654321Z', 'Luis', 'Peréz', 'lperez@gmail.com', '1988-01-03');
</code>

  * Para insertar el resultado de una consulta en una tabla (suponiendo que la tabla ''otros_usuarios'' existe y tiene al menos los campos indicados del mismo tipo que en la tabla ''usuarios'')

<code sql>
INSERT INTO usuario (dni, nombre, apellidos, email, fecha_nacimiento)
SELECT dni, nombre, apellidos, email, fecha_nacimiento 
FROM otros_usuarios;
</code>

==== Modificación de registros ====

La modificación de registros ya insertados en la tabla se realiza con la sentencia ''UPDATE'', que tiene el siguiente formato:

<code sql>
UPDATE nombre_tabla
SET columna = valor [ {, columna = valor} ]
[ WHERE condiciones ]
</code>

Veamos algunos ejemplos:

  * Actualizar una columna de una fila

<code sql>
UPDATE usuarios
SET nombre = 'Felipe'
WHERE id = 12;
</code>

  * Actualizar varias columnas de una fila

<code sql>
UPDATE usuarios
SET nombre = 'Felipe', dni = '123654789H'
WHERE id = 15;
</code>

  * Actualizar una columna de varias filas

<code sql>
UPDATE pistas
SET precio = precio + precio * 0.10
WHERE precio < 20 AND tipo = 'tenis';
</code>

  * Actualizar una columna utilizando una subconsulta:

<code sql>
-- Reduce el precio de las pistas que no se han reservado todavía
UPDATE pistas
SET precio = precio - precio * 0.1
WHERE id NOT IN (SELECT id_pista FROM reservas);
</code>

Cabe destacar que la ausencia de condiciones (WHERE) en una sentencia de actualización, ejecutaría dicha sentencia sobre todas las filas de la tabla.

==== Eliminación de registros ====

El borrado de filas de una tabla se efectúa con la sentencia ''DELETE'', que tiene el siguiente formato:

<code sql>
DELETE FROM nombre_tabla
[ WHERE condiciones ]
</code>

Veamos algunos ejemplos:

  * Elimina todos los usuarios 

<code sql>
DELETE FROM usuarios;
</code>

  * Borrar una fila estableciendo una condición

<code sql>
DELETE FROM pistas
WHERE id = 10;
</code>

  * Borrar varias filas estableciendo varias condiciones

<code sql>
DELETE FROM pistas
WHERE tipo = 'baloncesto' OR codigo = 'BAL001';
</code>

  * Borrar filas relacionando varias tablas

<code sql>
-- Elimina los usuarios que se dieron de alta antes de 2014
-- y aún no han reservado ninguna pista
DELETE FROM usuarios
WHERE id NOT IN (SELECT id_usuario FROM usuario_reserva)
AND fecha_alta < '2014-01-01';
</code>

Cabe destacar que la ausencia de condiciones en una sentencia de borrado, eliminaría todas las filas de la tabla.




===== Consulta de registros =====

==== La sentencia SELECT ====

La consulta de registros es la operación mas compleja, y también la más ejecutada, de una Base de Datos. Se lleva a cabo con la sentencia ''SELECT'', que tiene el siguiente formato:

<code sql>
SELECT columnas
FROM tablas
[ WHERE condiciones ]
[ GROUP BY columnas ]
[ HAVING condiciones_de_grupo ]
[ ORDER BY columnas_a_ordenar [ASC|DESC] ]
</code>

Veamos para que sirve cada una de las cláusulas de esta sentencia:

=== SELECT . . . FROM . . . ===

La cláusula ''SELECT'' se utiliza para selecciona las columnas que se quieren visualizar como resultado de la consulta. Se puede seleccionar cualquier columna de las tablas afectadas por la consulta (cláusula ''FROM''), valores constantes establecidos a la hora de ejecutar la consulta, o bien el comodín '*' para indicar que se quieren visualizar todas las columnas afectadas.

La cláusula ''FROM'' permite indicar con qué tablas se trabajará en la consulta. No siempre serán tablas de las que se visualicen columnas, puesto que muchas veces sólo se utilizarán para relacionar unas tablas con otras. En cualquier caso, se usen para que se visualicen sus campos o bien para relacionar otras tablas (que no están directamente relacionadas), se deben indicar en esta cláusula.

Veamos algunos ejemplos:
  * Consulta de una columna de una tabla
            
<code sql>
--  Nombre de todos los usuarios (incluye repeticiones)
SELECT nombre
FROM usuarios;
</code>

  * Consulta de dos columnas de una tabla

<code sql>
-- Nombre y apellidos de todos los usuarios
SELECT nombre, apellidos
FROM usuarios;
</code>

  * Consulta de todas las columnas de una tabla

<code sql>
-- Toda la información de todos los usuarios
SELECT *
FROM usuarios;
</code>
            
=== WHERE ===

La cláusula ''WHERE'' permite establecer condiciones sobre que filas se mostrarán en una sentencia de consulta. En ausencia de esta cláusula se muestran todos los registros de la tabla (aunque sólo las columnas establecidas en la cláusula ''SELECT''). Si se indican condiciones mediante la cláusula ''WHERE'' sólo se mostrarán aquellas filas que las cumplan.

Veamos algunos ejemplos:

<code sql>
-- Nombre y dirección de los polideportivos de Zaragoza
SELECT nombre, direccion
FROM polideportivos
WHERE ciudad = 'Zaragoza';
</code>

Además, nos permitirá establecer condiciones para establecer lo que se conoce como un ''INNER JOIN'' (implícito) entre dos o más tablas:

<code sql>
-- Código y tipo de las pistas de tenis que están operativas
SELECT pistas.codigo, pistas.tipo
FROM pistas, pistas_abiertas
WHERE pistas.id = pistas_abiertas.id_pista AND
      pistas_abiertas.operativa = TRUE AND pistas.tipo = 'tenis';
</code>

<code sql>
-- Código y tipo de las pistas de los polideportivos
-- de Zaragoza
SELECT pistas.codigo, pistas.tipo
FROM pistas, polideportivos
WHERE pistas.id_polideportivo = polideportivos.id AND polideportivos.ciudad = 'Zaragoza'
</code>

De manera que si utilizamos //alias// para los nombres de las tablas, podemos escribir la misma consulta algo más rápido:

<code sql>
-- Código y tipo de las pistas de los polideportivos
-- de Zaragoza
SELECT P.codigo, P.tipo
FROM pistas P, polideportivos PP
WHERE P.id_polideportivo = PP.id AND PP.ciudad = 'Zaragoza'
</code>

{{ youtube>hX1EZ-5mCIk }}
\\

=== GROUP BY / HAVING ===

Las cláusulas ''GROUP BY'' y ''HAVING'' permiten crear agrupaciones de datos y establecer condiciones sobre dichas agrupaciones, respectivamente. **Solo tiene sentido agrupar cuando quiero utilizar una función de agregación**.

<code sql>
-- Número de polideportivos en cada ciudad
SELECT ciudad, COUNT(*) AS cantidad
FROM polideportivos
GROUP BY ciudad;
</code>

<code sql>
-- Número de polideportivos hay en cada ciudad, solamente de aquellas
-- ciudades donde hay más de 10.000 polideportivos
SELECT ciudad, COUNT(*) AS cantidad
FROM polideportivos
GROUP BY ciudad
HAVING COUNT(*) > 10000;
</code>

<code sql>
-- Dinero recaudado en reservas cada año
SELECT SUM(precio) AS dinero, YEAR(fecha_reserva)
FROM usuarios
GROUP BY YEAR(fecha_reserva);
</code>

También es posible añadir claúsulas ''WHERE'' para filtrar registros y agrupar el resultado final

<code sql>
-- Precio medio, por tipo de pista, de las pistas que no están operativas
SELECT P.tipo, AVG(P.precio) AS precio_medio
FROM pistas P, pistas_abiertas PA
WHERE P.id = PA.id_pista AND PA.operativa = FALSE
GROUP BY P.tipo;
</code>

En los casos en los que usemos las cláusulas ''WHERE'' y ''HAVING'' junto con ''GROUP BY'', debemos tener en cuenta que:
  * ''WHERE'': filtra los registros antes de agrupar
  * ''HAVING'': filtra los resultados después de agrupar 

Además, hay que tener en cuenta que en ocasiones nos puede interesar agrupar por algún campo que no va a ser mostrado como resultado de la consulta en la claúsula ''SELECT''. En este caso, agruparíamos por ''id'' pero mostraríamos el ''nombre'' del polideportivo, ya que puede ser un dato más interesante para el usuario. Además, en casos en que el campo que queramos mostrar pueda repetirse, no conviene agrupar por dicho campo puesto que agruparía registros que en principio no tienen nada que ver. Además, en los dos siguientes ejemplos, unimos dos tablas para realizar la consulta y trabajar con los datos relacionados entre ambas (utilizando la claúsula ''WHERE'' para realizar la unión o //JOIN// que más adelante se explicará con más detalle):

<code sql>
-- Cantidad de pistas que hay en cada polideportivo
SELECT PP.nombre, COUNT(*) AS numero_pistas
FROM polideportivos PP, pistas P
WHERE PP.id = P.id_polideportivo
GROUP BY PP.id;
</code>

<code sql>
-- Número de reservas que ha hecho cada usuario
SELECT U.apellidos, U.nombre, U.ciudad, COUNT(*) AS numero_reservas
FROM usuarios U, usuario_reserva UR
WHERE U.id = UR.id_usuario
GROUP BY U.id
ORDER BY U.apellidos;
</code>

<code sql>
-- Número de pistas que hay de cada tipo en el polideportivo 'ACTUR 1'
SELECT P.tipo, COUNT(*) AS numero_pistas
FROM pistas P, polideportivos PP
WHERE P.id_polideportivo = PP.id AND PP.nombre = 'ACTUR 1'
GROUP BY P.tipo;
</code>

{{ youtube>XhYHvqigNiE }}
\\

=== ORDER BY ===

La cláusula ''ORDER BY'' permite ordenar el resultado de cualquier consulta atendiendo al campo o campos especificados en esta cláusula, ya sea en orden ascendente (''ASC'') o descendente (''DESC'')

<code sql>
-- Nombre y apellidos de los usuarios, ordenados por nombre
SELECT nombre, apellidos
FROM usuarios
ORDER BY nombre ASC;
</code>

{{ youtube>Nk6JnS4egC0 }}
\\

=== LIMIT ===
La cláusula ''LIMIT'' restringe el número máximo de registros devueltos por una consulta ''SELECT'' al número indicado. Se puede aplicar a casi todas las consultas, y genera un menor tiempo de respuesta ya que la ejecución de la consulta termina en el momento en que se completa el cupo de registros solicitados:

<code sql>
-- Muestra los 3 primeros registros que cumplan la consulta
SELECT nombre, apellidos
FROM usuarios
ORDER BY nombre ASC
LIMIT 3;
</code>
==== Operadores ====

A la hora de establecer condiciones en una sentencia de consulta, podremos utilizar los siguientes operadores:

  * ''='': Igual
  * ''<'': Menor
  * ''>'': Mayor
  * ''<nowiki><=</nowiki>'': Menor o igual
  * ''>='': Mayor o igual
  * ''<>'': Distinto
  * ''NOT'': Operador lógico para la negación de condiciones
  * ''AND'': Operador lógico para la conjunción de condiciones
  * ''OR'': Operador lógico para la disyunción de condiciones
  * ''DISTINCT'': Se utiliza para indicar a la cláusula SELECT que no se muestren valor de columnas repetidos

<code sql>   
-- Nombres distintos de los usuarios 	             
SELECT DISTINCT nombre, ciudad 	
FROM usuarios; 	
</code>

  * ''LIKE'': Permite comprobar si una cadena de caracteres cumple algún patrón determinado:
            
Permite la expresión de patrones a través de dos caracteres comodín:
    * El carácter '_' para expresar un único carácter
    * El carácter '%' para expresar cualquier secuencia de caracteres o incluso la secuencia vacía  
       
Veamos un par de ejemplos:

<code sql>
-- Nombre de los polideportivos que están en una ciudad
-- cuyo nombre empieza por Z y tiene 8 caracteres
SELECT nombre
FROM polideportivos
WHERE ciudad LIKE 'Z_______';

-- Nombre de los polideportivos que están en una ciudad
-- cuyo nombre empieza por Z
SELECT nombre
FROM polideportivos
WHERE ciudad LIKE 'Z%';
</code>
  * ''IN'' | ''NOT IN'': Permite comprobar si un valor coincide (o no) con algún valor especificado como un conjunto.

<code sql>
-- Nombre y extensión de los polideportivos de 
-- Zaragoza, Huesca y Teruel
SELECT nombre, extension
FROM polideportivos 
WHERE ciudad IN ('Zaragoza', 'Huesca', 'Teruel');
</code>
  * ''IS NULL'' | ''IS NOT NULL'': Se utiliza para comprobar si un valor es igual (o no) a ''NULL''. Dicha comprobación no debe realizar con ningún otro operador.

<code sql>
-- Nombre y apellidos de los usuarios que no indicaron su fecha de nacimiento
SELECT nombre, apellidos
FROM usuarios
WHERE fecha_nacimiento IS NULL;
</code>

  * ''BETWEEN'': Permite comprobar si el valor de una columna está comprendido entre dos valores determinados:

<code sql>
-- Nombre y apellidos de los usuarios que tienen un descuento
-- entre 10 y 20 %
SELECT nombre, apellidos
FROM usuarios
WHERE descuento BETWEEN 0.1 AND 0.2;
</code>

{{ youtube>V_h6u6cLpHg }}
\\

=== Operadores Aritméticos ===
Por otra parte, también tenemos los operadores aritméticos habituales de los lenguajes de programación:

^Operador^Operación^
|+|Suma|
|-|Resta|
|*|Producto|
|/|División decimal|
|div|División entera|
|mod ó %| Módulo: resto entero de división.|

<code sql>
-- Calcula el 10% de las ganancias por reservas del año pasado
SELECT (SUM(precio) * 0.1) AS 'impuestos'
FROM reservas
WHERE YEAR(fecha_reserva) = YEAR(CURDATE()) - 1;
</code>
==== Funciones agregadas ====

Son funciones que proporciona el lenguaje SQL, que permiten realizar operaciones sobre los datos de una base de datos:

  * ''COUNT'': Devuelve el número de filas seleccionadas. Entre los parentesis podemos indicar ''*'' ó un campo de la tabla.
    * (*): cuenta la cantidad de registros
    * (columna): cuenta la cantidad de valores de esa columna (no cuenta valores ''null''). También puedo usarlo para contar valores distintos.
<code sql>
-- Número de pistas 
SELECT COUNT(*)
FROM pistas;

-- Número de polideportivos en Zaragoza
SELECT COUNT(*)
FROM polideportivos
WHERE ciudad = 'Zaragoza';

-- Número de ciudades distintas
SELECT COUNT(DISTINCT ciudad)
FROM usuarios;
</code>

  * ''SUM'': Devuelve la suma de todos los valores de una columna
<code sql>
-- Cuánto dinero costaría alquilar todas las pistas del
-- polideportivo cuyo id es 23
SELECT SUM(precio)
FROM pistas
WHERE id_polideportivo = 23;
</code>

  * ''MIN'': Devuelve el valor mínimo de una columna
<code sql>
-- Cuánto vale la pista más barata
SELECT MIN(precio)
FROM pistas;
</code>

  * ''MAX'': Devuelve el valor máximo de una columna
<code sql>
-- Cuánto vale la pista más cara
SELECT MAX(precio)
FROM pistas;
</code>
  * ''AVG'': Devuelve el valor medio de los valores de una columna
<code sql>
-- Valor medio de las pistas
SELECT AVG(precio)
FROM pistas;
</code>

Hay que tener en cuenta que, excepto la función ''COUNT'', todas las demás devolverán el valor ''NULL'' si no hay columnas sobre las que puedan operar. La función ''COUNT'', sin embargo, devolverá el valor 0 en ese caso.

{{ youtube>TKpCt-gOxFQ }}
\\
==== Subconsultas ====

La creación de subconsultas permite utilizar el resultado de una consulta como valor de entrada para la condición de otra consulta principal.

Veamos unos ejemplos
<code sql>
-- Código y tipo de la pista más barata
SELECT codigo, tipo
FROM pistas
WHERE precio = (SELECT MIN(precio) 
                FROM pistas);

-- Codigo y tipo de las pistas cuyo precio está por encima de la media
SELECT codigo, tipo
FROM pistas
WHERE precio > (SELECT AVG(precio) 
                FROM pistas)

-- Nombre y apellidos de los usuarios que aún no han realizado 
-- ninguna reserva
SELECT nombre, apellidos
FROM usuarios 
WHERE id NOT IN (SELECT id_usuario 
                 FROM usuario_reserva)

-- Mostrar codigo y tipo de las pistas que se han reservado este año
SELECT codigo, tipo
FROM pistas
WHERE id IN (SELECT id_pista 
             FROM reservas 
             WHERE YEAR(fecha_reserva) = YEAR(CURDATE()))
</code>




==== Consultas con varias tablas ====

Como se ha visto anteriormente, aplicando la cláusula ''WHERE'', introducíamos una posibilidad más a la hora de realizar consultas sobre los datos de nuestra base de datos, lo que se conoce como una consulta de varias tablas o combinación de tablas (en inglés ''JOIN'').

Veamos varios ejemplos:

<code sql>
-- Mostrar, para cada polideportivo, el código y tipo de las pistas 
-- que tiene
SELECT PP.id, PP.nombre, P.codigo, P.tipo
FROM polideportivos PP, pistas P
WHERE PP.id = P.id_polideportivo

-- Consulta equivalente
SELECT PP.id, PP.nombre, P.codigo, P.tipo
FROM polideportivos PP INNER JOIN pistas P ON PP.id = P.id_polideportivo
</code>
       
De esta forma, al incluir a más de una tabla en la cláusula ''FROM'' estamos realizando lo que se conoce como una combinación interna (''INNER JOIN''), de forma que cabe la posibilidad de que sólo se muestren algunos datos de alguna de las tablas, puesto que la combinación interna sólo se queda con aquellos registros que están relacionadas con algún registro de la otra tabla.

{{ inner_join.gif }}

{{ youtube>ywbpcn0ogGI }}
\\

Si ahora tenemos en cuenta que algún cliente puede no haber realizado pedido alguno, veremos como no aparecen en el resultado de la consulta anterior. En algunos casos eso será lo que queramos, pero quizás en otros casos nos interesa que su nombre aparezca aunque no esté vinculado con ninguno de los pedidos. En este caso nos interesa lo que se conoce como ''OUTER JOIN''. En resumen, si alguna fila de cualquier tabla de la consulta puede no estar relacionado con alguna de las otras, puede ser interesante utilizar un ''OUTER JOIN''. Decidir si utilizar un ''LEFT OUTER JOIN'' o bien un ''RIGHT OUTER JOIN'' depende de si el dato que puede no tener relación con la otra tabla está a la izquierda o la derecha, respectivamente, según el sentido en que se escribe el código //SQL//.

{{ left_join.png }}

{{ right_join.gif }}

<code sql>
-- Mostrar, para cada pista, el codigo de reserva que ha tenido
-- Si nunca se ha reservado, se mostrarán sólo sus datos
-- (En este caso puede pasar que una pista no esté relacionada con 
-- ninguna reserva, como se puede ver en el modelo E-R)
SELECT P.id, P.codigo, P.tipo, R.id AS codigo
FROM pistas P LEFT OUTER JOIN reservas R ON P.id = R.id_pista;
ORDER BY P.codigo
</code>

De esta forma mostraremos también los datos de los pistas que no estén relacionados con ninguna reserva. Hay que tener en cuenta que, sólo en este caso, es relevante el orden en el que se especifican las tablas a la hora de definir el ''JOIN'' puesto que se incluirán //aquellas filas de la tabla del lado izquierdo que no tengan relación con las de la tabla del lado derecho//. Es por ello que en los ''INNER JOIN'' no se tiene que indicar el sentido de la unión.

<code sql>
-- Mostrar cuántas veces se ha reservado cada pista
SELECT P.id, P.codigo, P.tipo, COUNT(R.id) AS reservas
FROM pistas P LEFT OUTER JOIN reservas R ON P.id = R.id_pista
GROUP BY P.id
ORDER BY P.codigo;
</code>

<code sql>
-- Mostrar cuántas reservas ha hecho cada usuario
-- (Es posible que algún usuario no haya hecho reservas. Ver E-R)
SELECT U.dni, U.nombre, U.apellidos, COUNT(R.id) AS numero_reservas
FROM usuarios U LEFT OUTER JOIN usuario_reserva UR ON U.id = UR.id_usuario
                LEFT OUTER JOIN reservas R ON UR.id_reservas = R.id
                LEFT OUTER JOIN pistas P ON R.id_pista = P.id;
</code>

{{ youtube>rP91QeY9P6Y }}
\\

En definitiva, a la hora de construir una consulta //SQL// hay que añadir en la claúsula ''FROM'' todas aquellas tablas que estén involucradas en la consulta, bien porque se muestre alguna de sus columnas en la claúsula ''SELECT'', porque se establezca alguna condición con ''WHERE'', se agrupe por alguno de sus campos o incluso simplemente dicha tabla haga de //puente// entre dos tablas que deban estar involucradas en dicha consulta.

Con respecto al número de tablas que pueden ser incluidas en un //JOIN//, hay que tener en cuenta que el límite en //MySQL// es de 61.

{{ joins.jpeg }}

{{ youtube>iLH8fqBlPBQ }}
\\
==== Unión e intersección de consultas ====

La unión de consultas permite unir los resultados de dos consultas totalmente diferentes como si fuera el de una sola. Se realiza mediante la instrucción ''UNION'' y muestra los resultados sin repeticiones.

<code sql>
-- Código y tipo de las pistas abiertas y cerradas, indicando 
-- el estado actual
SELECT 'abierta', codigo, tipo
FROM pistas
WHERE id IN (SELECT id_pista FROM pistas_abiertas)
UNION
SELECT 'cerrada', codigo, tipo
FROM pistas
WHERE id IN (SELECT id_pista FROM pistas_cerradas);
</code>
        
La intersección de consultas muestra sólo los valores que aparecen en las dos consultas que se intersectan. Se realiza mediante la instrucción ''INTERSECT'':

<code sql>
-- Ciudades con polideportivos que cuentan con usuarios registrados 
SELECT ciudad
FROM usuarios
INTERSECT
SELECT ciudad
FROM polideportivos;
</code>

==== Consultas complejas====
A continuación se indican algunos métodos para resolver tipos de consultas que no podemos resolver con los visto anteriormente.

=== Consultas usando Vistas ===
La creación de vistas permite almacenar consultas como si se trataran de nuevas tablas con la finalidad de utilizar el resultado de las mismas en otras consultas más complejas. Cuando se crea una vista se genera lo que se conoce como una 'tabla lógica' que permite asignar un nombre al resultado de una consulta y utilizar ésta más adelante y siempre actualizada.

Hay que tener en cuenta que realmente la consulta que se ha creado como vista no se encuentra almacenada sino que tiene que ser generada cada vez que se deba utilizar.

Veamos un ejemplo:

<code sql>
-- Vista que almacena la consulta que mostraría el número de 
-- pistas que hay en cada polideportivo
CREATE VIEW pistas_por_polideportivo
AS
SELECT PP.id, PP.nombre, COUNT(*) AS cantidad
FROM polideportivos PP, pistas P
WHERE PP.id = P.id_polideportivo
GROUP BY PP.id
</code>

Si ahora suponemos que nos pidieran conocer el polideportivo o polideportivos que más pistas tiene, sólo tendríamos que realizar una consulta utilizando la vista creada anteriormente.

<code sql>
-- Nombre del polideportivo que más pistas tiene
SELECT nombre
FROM pistas_por_polideportivo
WHERE cantidad = (SELECT (MAX(cantidad) 
                  FROM pistas_por_polideportivos)
</code>

{{ youtube>EoQdJkhrnZw }}
\\

=== Subconsultas en cláusula FROM ===
El supuesto anterior se puede resolver también con otro tipo de subconsultas. Existe la posibilidad de incluir subconsultas en la clausula ''FROM'':

<code sql>
-- Nombre del polideportivo que más pistas tiene
SELECT pistas_polideportivo.nombre
FROM (SELECT PP.id, PP.nombre, COUNT(*) AS cantidad
      FROM polideportivos PP, pistas P
      WHERE PP.id = P.id_polideportivo
      GROUP BY PP.id) AS pistas_polideportivo
WHERE cantidad = (SELECT MAX(cantidad) 
                  FROM (SELECT COUNT(*) AS cantidad
                        FROM polideportivos PP, pistas P
			WHERE PP.id = P.id_polideportivo
			GROUP BY PP.id) AS cantidad_pistas);
</code>

Viendo los dos enfoques, resulta más sencillo guardar una consulta como una //Vista// para luego hacer una consulta más sencilla sobre ella.

===== Funciones de MySQL =====

==== Funciones para cadenas de caracteres ====

  * ''CHAR_LENGTH(str)'': Devuelve la longitud, en caracteres, de una cadena de texto
                
<code sql>
 > SELECT CHAR_LENGTH('Esto es una cadena');
-> 18
</code>

  * ''CONCAT(str1, str2, . . .)'': Concatena las cadenas de texto que se pasan como parámetros

<code sql>
 > SELECT CONCAT('Esto', ' forma ', 'una cadena');
-> 'Esto forma una cadena'
</code>

  * ''LOWER(str)'': Devuelve la cadena convertida a minúsculas

<code sql>
 > SELECT LOWER('Bases de Datos Relacionales');
-> 'bases de datos relacionales'
</code>

  * ''LTRIM(str)'': Elimina los espacios en blanco al comienzo de la cadena

<code sql>
 > SELECT LTRIM('    MySQL');
-> 'MySQL'
</code>

  * ''REPLACE(str, from_str, to_str)'': Reemplaza todas las ocurrencias de 'from_str' por 'to_str' que aparezcan en la cadena 'str'

<code sql>
 > SELECT REPLACE('Access es un buen SGBD', 'Access', 'MySQL');
-> 'MySQL es un buen SGBD'
</code>

  * ''RTRIM(str)'': Elimina los espacios en blanco al final de la cadena

<code sql>
 > SELECT RTRIM('MySQL    ');
-> 'MySQL'
</code>

  * ''STRCMP(expr1, expr2)'': Devuelve 0 si las cadenas son idénticas, -1 si la primera es menor,                y 1 en cualquier otro caso

<code sql>
 > SELECT STRCMP('MySQL', 'Access');
-> -1
</code>

  * ''SUBSTRING(str, pos), SUBSTRING(str, pos, len)'': Devuelve una subcadena de 'str' comenzando                en la posición 'pos'. En el segundo caso tomará caracteres hasta completar una subcadena de tamaño 'len'

<code sql>
 > SELECT SUBSTRING('MySQL es un buen SGBD', 7);
-> 'es un buen SGBD'

 > SELECT SUBSTRING('MySQL es un buen SGBD', 7, 2);
-> 'es'
</code>

  * ''TRIM(str)'': Elimina los espacios en blanco al comienzo y al final de la cadena

<code sql>
 > SELECT TRIM('   MySQL    ');
-> 'MySQL'
</code>

  * ''UPPER(str)'': Devuelve la cadena convertida a mayúsculas

<code sql>
 > SELECT UPPER('Esto es una cadena');
-> 'ESTO ES UNA CADENA'
</code>
==== Funciones numéricas ====

  * ''RAND(), RAND(semilla)'': Devuelve un número aleatorio del rango de 0 a 1.0. Es posible                pasar una semilla como parámetro para generar secuencias repetibles

<code sql>
 > SELECT RAND();
-> 0.838373287632872

 > SELECT RAND(20);
-> 0.1588498478438734

 > SELECT RAND(20);
-> 0.1588498478438734
</code>

  * ''ROUND(X), ROUND(X, D)'': Redondea el valor de X al número más cercano. Si se especifica el                parámetro 'D' se pueden indicar los decimales con los que se quiere aproximar

<code sql>
 > SELECT ROUND(0.67);
-> 1

 > SELECT ROUND(1.235);
-> 1

 > SELECT ROUND(1.235, 1);
-> 1.2
</code>
==== Funciones de fecha ====

  * ''CURDATE()'': Devuelve la fecha actual

<code>
 > SELECT CURDATE();
-> '2012-01-09'
</code>

  * ''CURTIME()'': Devuelve la hora actual

<code sql>
 > SELECT CURTIME();
-> '19:23:02'
</code>

  * ''CURRENT_TIMESTAMP()'': Devuelve la fecha y hora actual
                
<code sql>
 > SELECT CURRENT_TIMESTAMP();
-> '2012-01-09 19:23:02'
</code>


  * ''MONTH(fecha)'': Devuelve el número de mes para la fecha especificada (1-12)
      
<code sql>
 > SELECT MONTH('1998-01-01 23:59:59');
-> 1
</code>

  * ''MONTHNAME(fecha)'': Devuelve el nombre del mes para la fecha especificada

<code sql>
 > SELECT MONTHNAME('1998-01-01 23:59:59');
-> January
</code>

  * ''WEEK(fecha)'': Devuelve el número de la semana para la fecha especificada (0-52)

<code sql>
 > SELECT WEEK('1998-01-01 23:59:59');
-> 0
</code>

  * ''WEEKDAY(fecha)'': Devuelve el día de la semana para la fecha especificada (0-6)

<code sql>
 > SELECT WEEKDAY('1998-01-01 23:59:59');
-> 3
</code>

  * ''YEAR(fecha)'': Devuelve el número de año para la fecha especificada

<code sql>
 > SELECT YEAR('1998-01-01 23:59:59');
-> 1998
</code>

  * ''DAY(fecha)'': Devuelve el día del mes de la fecha especificada (1-31)

<code sql>
 > SELECT DAY('1998-01-01 23:59:59');
-> 1
</code>

  * ''DAYNAME(fecha)'': Devuelve el nombre del día de la semana de la fecha especificada

<code sql>
 > SELECT DAYNAME('1998-01-01 23:59:59');
-> Thursday
</code>

  * ''DAYOFYEAR(fecha)'': Devuelve el día del año para la fecha especificada (1-366)

<code sql>
 > SELECT DAYOFYEAR('1998-01-01 23:59:59');
-> 1
</code>

  * ''DATE(expr)'': Extrae la fecha de una expresión de fecha o de fecha y hora

<code sql>
 > SELECT DATE('2012-01-09 19:23:02');
-> '2012-01-09'
</code>

  * ''DATEDIFF(expr, expr2)'': Devuelve el número de días entre la fecha inicial 'expr' y la fecha                final 'expr2'

<code sql>
 > SELECT DATEDIFF('2011-01-09', CURDATE());
-> -365
</code>

  * ''DATE_ADD(fecha, INTERVAL expr1 type), DATE_SUB(fecha, INTERVAL expr type)'': Realizan operaciones aritméticas con fechas

Como valor de 'type' se pueden especificar los partes de fecha con los que se quiera realizar el                cálculo. Estos son algunos:
    * ''SECOND'': Segundos
    * ''MINUTE'': Minutos
    * ''HOUR'': Horas
    * ''DAY'': Días
    * ''WEEK'': Semanas
    * ''MONTH'': Meses
    * ''YEAR'': Años
    * ''DAY_HOUR'': Días Horas
    * ''YEAR_MONTH'': Años-Meses

<code sql>
 > SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 DAY):
-> '1998-01-01 23:59:59'

 > SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'

 > SELECT DATE_ADD('1998-01-02', INTERVAL 1.1 YEAR_MONTH);
-> '1999-02-02'
</code>

  * ''TIMESTAMPDIFF(INTERVAL, timestamp1, timestamp2)'': Devuelve la diferencia en el tipo de periodo elegido, entre la fecha 1 y la fecha 2
<code sql>
 > SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2000-01-01');
 > -2
 > SELECT TIMESTAMPDIFF(YEAR,'2000-05-01','2002-01-01');
 > 1
</code>

  * ''DATE_FORMAT(fecha, formato)'': Formatea el valor de fecha a través de estos especificadores:
    * ''%c'' Mes (1-12)
    * ''%e'' Día del mes (1-31)
    * ''%h'' Hora (01-12)
    * ''%H'' Hora (00-23)
    * ''%i'' Minutos (00-59)
    * ''%M'' Nombre del mes
    * ''%m'' Mes (01-12)
    * ''%s'' Segundos (00-59)
    * ''%Y'' Año (cuatro dígitos)
    * ''%y'' Año (dos dígitos)

<code sql>
 > SELECT DATE_FORMAT('1998-01-01 23:59:59', '%e/%m/%y');
-> '1/01/98'

 > SELECT DATE_FORMAT('1998-01-01 23:59:59', '%e/%M/%Y %H:%i');
-> '1/January/1998 23:59'
</code>

----

===== Ejercicios =====

{{ ejercicio.png}}

  - Con las siguientes tablas, realizar las consultas que se enumeran a continuación<code>
Empleados (#id, nombre, apellidos, oficio, fecha_alta, salario, 
          comision, -id_departamento)
Departamentos (#id, nombre, ubicacion)
</code>
    * Nombre y apellidos de los empleados cuyo nombre empieza por 'A', ordenado por apellido
    * Nombre, apellidos y departamento de los empleados que trabajan como 'Analista'
    * Mostrar el salario medio de los empleados que trabajan como 'Programador'
    * Nombre y apellidos de los empleados que no han conseguido ninguna comisión, ordenador por apellido
    * Nombre, apellidos y oficio de los empleados que ganan menos de 1000 euros
    * Oficio y fecha de alta de los empleados que trabajan en el departamento de 'Ventas', ordenado por fecha de alta
    * Nombre, apellidos y antigüedad de todos los empleados
    * ¿Cuántos empleados se han contratado este año?
  - Sobre el modelo relacional del Ejercicio 1 del tema anterior, realiza las siguientes consultas
    * ¿Cuántos equipos se han apuntado al campeonato?
    * ¿Cuántos jugadores juegan en el equipo 'Colegio Montessori'?
    * Nombre y categoría de los equipos que juegan de blanco como primera equipación
    * Nombre y categoría de los equipos que juegan de blanco como primera equipación y de azul como                segunda
    * ¿Cuántas empresas patrocinan el campeonato?
    * Nombre de los equipos de aquellos en los que han habido incidencias en sus partidos
    * ¿Cuántos árbitros hay en el campeonato?
  - Sobre el modelo relacional del Ejercicio 8 del tema anterior, realiza las siguientes consultas:
    * Ingresos totales en lo que va de año
    * Login, nombre y email de los usuarios que tienen carritos sin validar, ordenado por email
    * Nombre de los productos que no se han vendido nunca, ordenado por nombre
    * Nombre y precio de los productos cuyo nombre empieza por 'A', ordenado por precio
    * Nombre y precio de los productos cuyo precio está entre 100 y 200 Euros, ordenado por precio
    * Nombre de las secciones visitadas por el usuario cuyo login es 'cgomez'
    * ¿Cuál fue la primera sección visitada por el usuario cuyo login es 'cgomez'?
    * ¿Cuántas secciones tiene la página web?
  - Dadas estas tablas, realiza las siguientes consultas<code>
Empleados (#id, dni, nombre, salario)
Vendedores (#id, nro_vendedor, zona, -id_empleado)
Polizas (#id, nro_poliza, importe, beneficiario, -id_vendedor, fecha, 
         fecha_vencimiento)
Empleado_Jefe (#(-id_empleado, -id_jefe))
</code>
    * Nombre de los vendedores de la zona Norte
    * Nombre del jefe del vendedor con número de vendedor 123456
    * ¿Cuántos vendedores hay en la zona norte?
    * Número de vendedores cuyo nombre empieza por 'A'
    * ¿Cuántos vendedores tienen jefe?
    * ¿Cuántos vendedores no tienen jefe?
    * Nombre de los empleados cuyo jefe es José Pérez
    * Importe total de las pólizas vendidas por vendedores de la zona Norte
    * Nº de póliza e importe de las pólizas vendidas por los vendedores cuyo jefe es Pablo Collado
    * ¿Cuántas pólizas se vendieron cada mes del año pasado?
    * Número e importe de las pólizas que vencen mañana
  - Dadas estas tablas, realiza las siguientes consultas<code>
Comunidades (#id, nombre, nro_habitantes)
Ciudades (#id, nombre, nro_habitantes, -id_comunidad)
Rios (#id, nombre, caudal, longitud)
Comunidad_Rio (#(-id_comunidad, -id_rio), nro_kilometros)
Rio_Ciudad (#(-id_rio, -id_ciudad))
</code>
    * Nombre y número de habitantes de las ciudad de La Rioja
    * Nombre y longitud de los ríos que pasan por la Comunidad de Aragón
    * Nombre de los ríos que pasan por Castilla la Mancha y Andalucía (por ambas)
    * Caudal total de los ríos que pasan por Navarra
    * ¿Cuál es el río más largo de España?
    * ¿Por cuántas ciudades pasa el río Ebro?
    * ¿Cuál es el río que más ciudades atraviesa?
    * Nombre de los ríos que sólo cruzan una ciudad
    * Número de ciudades que atraviesa cada río  
  - Realiza las siguientes consultas sobre estas tablas<code>
Autores (#id, nombre, fecha_nacimiento, fecha_fallecimiento, 
         nacionalidad)
Obras (#id, titulo, fecha, -id_museo)
Museos (#id, nombre, direccion, ciudad, pais)
Obra_Autor (#(-id_obra, -id_autor))
</code>
    * ¿Cuántas obras hay en el museo del Prado?
    * ¿Cuántas obras ha creado Pablo Picasso?
    * Título de las obras que hay en el museo Reina Sofía, ordenado por título
    * Nombre y dirección de los museos de España, ordenado por nombre
    * Título de la obra y autor de aquellas obras de autores extranjeros que se encuentran en España, ordenado por autor
    * ¿Cuántas obras las han realizado autores españoles?
    * ¿Cuántos museos hay?
    * ¿Cuántas obras hay en cada museo?
    * Nombre y fecha de creación de las obras creadas antes del año 2000, ordenado por fecha
    * Nombre y edad de los autores que siguen vivos en la actualidad
    * Nombre y edad a la que murieron los autores ya fallecidos
    * ¿En qué mes nació Picasso?
    * Para cada obra de arte del museo del Prado, mostrar el título y el mes y año de creación

----

(c) {{date> %Y}} Santi Faci y Fernando Valdeón