MySQL Básico (2)

(Nociones básicas de SQL usando MySQL, bajo Windows.
Un repaso a lo fundamental en 2 lecciones.
-Parte 2-
Si quieres sabes más, echa un vistazo en nachocabanes.com/sql/curso)

Hemos visto cómo introducir datos, cómo borrarlos y modificarlos, y también cómo hacer consultas básicas para acceder a la información que contiene nuestra base de datos. Ahora vamos a ver algunas consultas ligeramente más avanzadas...

10. Funciones de agregación

Podemos aplicar ciertas funciones matemáticas a todo un conjunto de datos de una tabla. Por ejemplo, podemos saber cual es la edad más baja de entre las personas que tenemos en nuestra base de datos, haríamos:

select min(edad) from personas;
 

Las funciones de agregación más habituales son:

• min = mínimo valor
• max = máximo valor
• sum = suma de los valores
• avg = media de los valores
• count = cantidad de valores

La forma más habitual de usar "count" es pidiendo con "count(*)" que se nos muestren todos los datos que cumplen una condición. Por ejemplo, podríamos saber cuantas personas tienen una dirección que comience por la letra "s", así:

select count(*) from personas where direccion like 's%';

11. Cero y valor nulo

En ocasiones querremos dejar un campo totalmente vacío, sin valor. Para las cadenas de texto, existe una forma "parecida" de conseguirlo, que es con una cadena vacía, indicada con dos comillas que no contengan ningún texto entre ellas (ni siquiera espacios en blanco): '' ; En cambio, para los números, no basta con guardar un 0 para indicar que no se sabe el valor: no es lo mismo un importe de 0 euros que un importe no detallado. Por eso, existe un símbolo especial para indicar cuando no existe valor en un campo.

Este símbolo especial es la palabra NULL. Por ejemplo, añadiríamos datos parcialmente en blanco a una tabla haciendo

insert into personas
  (nombre, direccion, edad)
values (
 'pedro', NULL, NULL
);
 

Para saber si algún campo está vacío, compararíamos su valor con NULL, pero de una forma un tanto especial: no con el símbolo "igual" (=), sino con la palabra IS. Por ejemplo, sabríamos cuales de las personas de nuestra bases de datos tienen dirección usando

select * from personas
  where direccion is not null;
 

Y, de forma similar, sabríamos quien no tiene dirección, así:

select * from personas
  where direccion is null;
 

12. Resultados agrupados

Puede ocurrir que no nos interese un único valor agrupado (el total, la media, la cantidad de datos), sino el resultado para un grupo de datos. Por ejemplo: saber no sólo la cantidad de clientes que hay registrados en nuestra base de datos, sino también la cantidad de clientes que viven en cada ciudad.

La forma de obtener subtotales es creando grupos con la orden "group by", y entonces pidiendo una valor agrupado (count, sum, avg, ...) para cada uno de esos grupos. Por ejemplo, en nuestra tabla "personas", podríamos saber cuantas personas aparecen de cada edad, con:

select count(*), edad from personas group by edad;



13. Cómo filtrar los datos agrupados

Podemos llegar más allá: podemos no trabajar con todos los grupos posibles, sino sólo con los que cumplen alguna condición.

La condición que se aplica a los grupos no se indica con "where", sino con "having" (que se podría traducir como "los que tengan..."). Por ejemplo, podríamos saber la cantidad de personas que tenemos con cada edad, pero considerando sólo las mayores de 24 años:

select count(*), edad from personas group by edad having edad > 24;

14. Subconsultas

A veces tenemos que realizar operaciones más complejas con los datos, operaciones en las que nos interesaría ayudarnos de una primera consulta auxiliar que extrajera la información en la que nos queremos basar. Esta consulta auxiliar recibe el nombre de "subconsulta" o "subquery".

Por ejemplo, si queremos saber qué clientes tenemos en la ciudad que más habitantes tenga, la forma "razonable" de conseguirlo sería saber en primer lugar cual es la ciudad que más habitantes tenga, y entonces lanzar una segunda consulta para ver qué clientes hay en esa ciudad.

Como la estructura de nuestra base de datos de ejemplo es muy sencilla, no podemos hacer grandes cosas, pero un caso parecido al anterior (aunque claramente más inútil) podría ser saber qué personas tenemos almacenadas que vivan en la última ciudad de nuestra lista.

Para ello, la primera consulta (la "subconsulta") sería saber cual es la última ciudad de nuestra lista. Si lo hacemos tomando la que tenga el último código, la consulta podría ser

SELECT MAX(codigo) FROM ciudades;
 

Vamos a imaginar que pudiéramos hacerlo en dos pasos. Si llamamos "maxCodigo" a ese código obtenido, la "segunda" consulta podría ser:

SELECT * FROM personas WHERE codciudad= maxCodigo;
 

Pero estos dos pasos se pueden dar en uno: al final de la "segunda" consulta (la "grande") incluimos la primera consulta (la "subconsulta"), entre paréntesis, así

SELECT * FROM personas WHERE codciudad= (
SELECT MAX(codigo) FROM ciudades
);
 

Si la subconsulta no devuelve un único dato, sino un conjunto de datos, la forma de trabajar será básicamente la misma, pero para comprobar si el valor coincide con uno de la lista, no usaremos el símbolo "=", sino la palabra "in".

Por ejemplo, vamos a hacer una consulta que nos muestre las personas que viven en ciudades cuyo nombre tiene una "a" en segundo lugar (por ejemplo, serían ciudades válidas Madrid o Barcelona, pero no Alicante).

Para consultar qué letras hay en ciertas posiciones de una cadena, podemos usar SUBSTRING (en el próximo apartado veremos las funciones más importantes de manipulación de cadenas). Así, una forma de saber qué ciudades tienen una letra A en su segunda posición sería:

SELECT codigo FROM ciudades 
WHERE SUBSTRING(nombre,2,1)='a';
 

Como esta subconsulta puede tener más de un resultado, deberemos usar IN para incluirla en la consulta principal, que quedaría de esta forma:

SELECT * FROM personas 
WHERE codciudad IN 
(
SELECT codigo FROM ciudades WHERE SUBSTRING(nombre,2,1)='a'
);

15. Funciones de cadena

En MySQL tenemos muchas funciones para manipular cadenas: calcular su longitud, extraer un fragmento situado a la derecha, a la izquierda o en cualquier posición, eliminar espacios finales o iniciales, convertir a hexadecimal y a binario, etc. Vamos a comentar sólo las más habituales. Los ejemplos estarán aplicados directamente sobre cadenas, pero (por supuesto) también se pueden aplicar a campos de una tabla:

Funciones de conversión a mayúsculas/minúsculas

• LOWER o LCASE convierte una cadena a minúsculas: SELECT LOWER('Hola'); -> hola
• UPPER o UCASE convierte una cadena a mayúsculas: SELECT UPPER('Hola'); -> HOLA

Funciones de extracción de parte de la cadena

• LEFT(cadena, longitud) extrae varios caracteres del comienzo (la parte izquierda) de la cadena: SELECT LEFT('Hola',2); -> Ho
• RIGHT(cadena, longitud) extrae varios caracteres del final (la parte derecha) de la cadena: SELECT RIGHT('Hola',2); -> la
• MID(cadena, posición, longitud), SUBSTR(cadena, posición, longitud) o SUBSTRING(cadena, posición, longitud) extrae varios caracteres de cualquier posición de una cadena, tantos como se indique en "longitud": SELECT SUBSTRING('Hola',2,2); -> ol (Nota: a partir MySQL 5 se permite un valor negativo en la posición, y entonces se comienza a contar desde la derecha -el final de la cadena-)
• CONCAT une (concatena) varias cadenas para formar una nueva: SELECT CONCAT('Ho', 'la'); -> Hola
• TRIM devuelve la cadena sin los espacios en blanco que pudiera contener al principio ni al final: SELECT TRIM(' Hola '); -> Hola (Nota: realmente, TRIM puede eliminar cualquier prefijo, no sólo espacios; mira el manual de MySQL para más detalles)

Otras funciones de modificación de la cadena

• INSERT(cadena,posición,longitud,nuevaCadena) inserta en la cadena otra cadena: SELECT INSERT('Hola', 2, 2, 'ADIOS'); -> HADIOSa
• REPLACE(cadena,de,a) devuelve la cadena pero cambiando ciertas secuencias de caracteres por otras: SELECT REPLACE('Hola', 'l', 'LLL'); -> HoLLLa
• REPEAT(cadena,numero) devuelve la cadena repetida varias veces: SELECT REPEAT(' Hola',3); -> HolaHolaHola

Funciones de información sobre la cadena

• CHAR_LENGTH o CHARACTER_LENGTH devuelve la longitud de la cadena en caracteres
• INSTR(cadena,subcadena) o LOCATE(subcadena,cadena,posInicial) devuelve la posición de una subcadena dentro de la cadena: SELECT INSTR('Hola','ol'); -> 2

16. Enlazar tablas con "join"

Sabemos enlazar varias tablas para mostrar datos que estén relacionados. Por ejemplo, podríamos mostrar nombres de deportistas, junto con los nombres de los deportes que practican. Pero todavía hay un detalle que se nos escapa: ¿cómo hacemos si queremos mostrar todos los deportes que hay en nuestra base de datos, incluso aunque no haya deportistas que los practiquen?

Vamos a crear una base de datos sencilla para ver un ejemplo de cual es este problema y de cómo solucionarlo.

Nuestra base de datos se llamará "ejemploJoins":

create database ejemploJoins;
use ejemploJoins;

En ella vamos a crear una primera tabla en la que guardaremos "capacidades" de personas (cosas que saben hacer):

create table capacidad(
codigo varchar(4),
nombre varchar(20),
primary key(codigo) );

También crearemos una segunda tabla con datos básicos de personas:

create table persona(
codigo varchar(4),
nombre varchar(20),
codcapac varchar(4),
primary key(codigo) );

Vamos a introducir datos de ejemplo:

insert into capacidad values
('c','Progr.C'),
('pas','Progr.Pascal'),
('j','Progr.Java'),
('sql','Bases datos SQL');

insert into persona values
('ju','Juan','c'),
('ja','Javier','pas'),
('jo','Jose','perl'),
('je','Jesus','html');

Como se puede observar, hay dos capacidades en nuestra base de datos para las que no conocemos a ninguna persona; de igual modo, existen dos personas que tienen capacidades sobre las que no tenemos ningún detalle.

Por eso, si mostramos las personas con sus capacidades de la forma que sabemos, sólo aparecerán las parejas de persona y capacidad para las que todo está claro (existe persona y existe capacidad), es decir:

select * from capacidad, persona where persona.codcapac = capacidad.codigo;

(Hay que recordar que la orden "where" es obligatoria: si no indicamos esa condición, se mostraría el "producto cartesiano" de las dos tablas: todos los parejas (persona, capacidad), aunque no estén relacionados en nuestra base de datos).

Pues bien, con órdenes "join" podemos afinar cómo queremos enlazar (en inglés, "join", unir) las tablas. Por ejemplo, si queremos ver los datos que coinciden en ambas tablas, lo que antes conseguíamos comparando los códigos con un "where", también podemos usar un "inner join" (unión interior; se puede abreviar simplemente "join"):

select persona.nombre, capacidad.nombre
from persona inner join capacidad
on persona.codcapac = capacidad.codigo;



Pero aquí llega la novedad: si queremos ver todas las personas y sus capacidades, incluso para aquellas personas cuya capacidad no está detallada en la otra tabla, usaríamos un "left join" (unión por la izquierda, también se puede escribir "left outer join", unión exterior por la izquierda, para dejar claro que se van a incluir datos que están sólo en una de las dos tablas):

select persona.nombre, capacidad.nombre
from persona left outer join capacidad
on persona.codcapac = capacidad.codigo;



De igual modo, si queremos ver todas las capacidades, incluso aquellas para las que no hay detalles sobre personas, podemos escribir el orden de las tablas al revés en la consulta anterior, o bien usar "right join" (o "right outer join"):

Otros gestores de bases de datos permiten combinar el "right join" y el "left join" en una única consulta, usando "full outer join", algo que no permite MySQL en su versión actual.

17. Uniendo dos consultas en una

En el apartado anterior comentábamos que la versión actual de MySQL no permite usar "full outer join" para mostrar todos los datos que hay en dos tablas enlazadas, aunque alguno de esos datos no tenga equivalencia en la otra tabla.

También decíamos que se podría imitar haciendo a la vez un "right join" y un "left join".

En general, tenemos la posibilidad de unir dos consultas en una usando "union", así:

select persona.nombre, capacidad.nombre
from persona right outer join capacidad
on persona.codcapac = capacidad.codigo
union
select persona.nombre, capacidad.nombre
from persona left outer join capacidad
on persona.codcapac = capacidad.codigo;
 

Nota: en algunos gestores de bases de datos, podemos no sólo crear "uniones" entre dos tablas, sino también realizar otras operaciones habituales entre conjuntos, como calcular su intersección ("intersection") o ver qué elementos hay en la primera pero no en la segunda (diferencia, "difference"). Estas posibilidades no están disponibles en la versión actual de MySQL.