9 Lenguaje SQL: Selección de datos
Ya disponemos de bases de datos, y sabemos cómo añadir y modificar datos. Ahora aprenderemos a extraer datos de una base de datos. Para ello volveremos a usar la sentencia SELECT.
La sintaxis de SELECT es compleja, pero en este capítulo no explicaremos todas sus opciones. Una forma más general consiste en la siguiente sintaxis:
SELECT [ALL | DISTINCT | DISTINCTROW]
expresion_select,...
FROM referencias_de_tablas
WHERE condiciones
[GROUP BY {nombre_col | expresion | posicion}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING condiciones]
[ORDER BY {nombre_col | expresion | posicion}
[ASC | DESC] ,...]
[LIMIT {[desplazamiento,] contador | contador OFFSET desplazamiento}]
Forma incondicional
La forma más sencilla es la que hemos usado hasta ahora, consiste en pedir todas las columnas y no especificar condiciones.
mysql> SELECT * FROM gente; +---------+------------+ | nombre | fecha | +---------+------------+ | Fulano | 1985-04-12 | | Mengano | 1978-06-15 | | Tulano | 2001-12-02 | | Pegano | 1993-02-10 | +---------+------------+ 4 rows in set (0.00 sec) mysql>
Limitar las columnas: proyección
Recordemos que una de las operaciones del álgebra relacional era la proyección, que consitía en seleccionar determinados atributos de una relación.
Mediante la sentencia SELECT es posible hacer una proyección de una tabla, seleccionando las columas de las que queremos obtener datos. En la sintaxis que hemos mostrado, la selección de columnas corresponde con la parte "expresion_select". En el ejemplo anterior hemos usado '*', que quiere decir que se muestran todas las columnas.
Pero podemos usar una lista de columnas, y de ese modo sólo se mostrarán esas columnas:
mysql> SELECT nombre FROM gente; +---------+ | nombre | +---------+ | Fulano | | Mengano | | Tulano | | Pegano | +---------+ 4 rows in set (0.00 sec) mysql> SELECT clave,poblacion FROM ciudad5; Empty set (0.00 sec) mysql>
Las expresiones_select no se limitan a nombres de columnas de tablas, pueden ser otras expresiones, incluso aunque no correspondan a ninguna tabla:
mysql> SELECT SIN(3.1416/2), 3+5, 7*4; +------------------+-----+-----+ | SIN(3.1416/2) | 3+5 | 7*4 | +------------------+-----+-----+ | 0.99999999999325 | 8 | 28 | +------------------+-----+-----+ 1 row in set (0.00 sec) mysql>
Vemos que podemos usar funciones, en este ejemplo hemos usando la función SIN para calcular el seno de π/2. En próximos capítulos veremos muchas de las funciones de las que disponemos en MySQL.
También podemos aplicar funciones sobre columnas de tablas, y usar esas columnas en expresiones para generar nuevas columnas:
mysql> SELECT nombre, fecha, DATEDIFF(CURRENT_DATE(),fecha)/365 FROM gente; +---------+------------+------------------------------------+ | nombre | fecha | DATEDIFF(CURRENT_DATE(),fecha)/365 | +---------+------------+------------------------------------+ | Fulano | 1985-04-12 | 19.91 | | Mengano | 1978-06-15 | 26.74 | | Tulano | 2001-12-02 | 3.26 | | Pegano | 1993-02-10 | 12.07 | +---------+------------+------------------------------------+ 4 rows in set (0.00 sec) mysql>
Alias
Aprovechemos la ocasión para mencionar que también es posible asignar un alias a cualquiera de las expresiones select. Esto se puede hacer usando la palabra AS, aunque esta palabra es opcional:
mysql> SELECT nombre, fecha, DATEDIFF(CURRENT_DATE(),fecha)/365 AS edad
-> FROM gente;
+---------+------------+-------+
| nombre | fecha | edad |
+---------+------------+-------+
| Fulano | 1985-04-12 | 19.91 |
| Mengano | 1978-06-15 | 26.74 |
| Tulano | 2001-12-02 | 3.26 |
| Pegano | 1993-02-10 | 12.07 |
+---------+------------+-------+
4 rows in set (0.00 sec)
mysql>
Podemos hacer "bromas" como:
mysql> SELECT 2+3 "2+2"; +-----+ | 2+2 | +-----+ | 5 | +-----+ 1 row in set (0.00 sec) mysql>
En este caso vemos que podemos omitir la palabra AS. Pero no es aconsejable, ya que en ocasiones puede ser difícil distinguir entre un olvido de una coma o de una palabra AS.
Posteriormente veremos que podemos usar los alias en otras cláusulas, como WHERE, HAVING o GROUP BY.
Mostrar filas repetidas
Ya que podemos elegir sólo algunas de las columnas de una tabla, es posible que se produzcan filas repetidas, debido a que hayamos excluido las columnas únicas.
Por ejemplo, añadamos las siguientes filas a nuestra tabla:
mysql> INSERT INTO gente VALUES ('Pimplano', '1978-06-15'),
-> ('Frutano', '1985-04-12');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT fecha FROM gente;
+------------+
| fecha |
+------------+
| 1985-04-12 |
| 1978-06-15 |
| 2001-12-02 |
| 1993-02-10 |
| 1978-06-15 |
| 1985-04-12 |
+------------+
6 rows in set (0.00 sec)
mysql>
Vemos que existen dos valores de filas repetidos, para la fecha "1985-04-12" y para "1978-06-15". La sentencia que hemos usado asume el valor por defecto (ALL) para el grupo de opciones ALL, DISTINCT y DISTINCTROW. En realidad sólo existen dos opciones, ya que las dos últimas: DISTINCT y DISTINCTROW son sinónimos.
La otra alternativa es usar DISTINCT, que hará que sólo se muestren las filas diferentes:
mysql> SELECT DISTINCT fecha FROM gente; +------------+ | fecha | +------------+ | 1985-04-12 | | 1978-06-15 | | 2001-12-02 | | 1993-02-10 | +------------+ 4 rows in set (0.00 sec) mysql>
Limitar las filas: selección
Otra de las operaciones del álgebra relacional era la selección, que consitía en seleccionar filas de una realación que cumplieran determinadas condiciones.
Lo que es más útil de una base de datos es la posibilidad de hacer consultas en función de ciertas condiciones. Generalmente nos interesará saber qué filas se ajustan a determinados parámetros. Por supuesto, SELECT permite usar condiciones como parte de su sintaxis, es decir, para hacer selecciones. Concretamente mediante la cláusula WHERE, veamos algunos ejemplos:
mysql> SELECT * FROM gente WHERE nombre="Mengano";
+---------+------------+
| nombre | fecha |
+---------+------------+
| Mengano | 1978-06-15 |
+---------+------------+
1 row in set (0.03 sec)
mysql> SELECT * FROM gente WHERE fecha>="1986-01-01";
+--------+------------+
| nombre | fecha |
+--------+------------+
| Tulano | 2001-12-02 |
| Pegano | 1993-02-10 |
+--------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM gente
-> WHERE fecha>="1986-01-01" AND fecha < "2000-01-01";
+--------+------------+
| nombre | fecha |
+--------+------------+
| Pegano | 1993-02-10 |
+--------+------------+
1 row in set (0.00 sec)
mysql>
En una cláusula WHERE se puede usar cualquier función disponible en MySQL, excluyendo sólo las de resumen o reunión, que veremos en el siguiente punto. Esas funciones están diseñadas específicamente para usarse en cláusulas GROUP BY.
También se puede aplicar lógica booleana para crear expresiones complejas. Disponemos de los operadores AND, OR, XOR y NOT.
En próximos capítulos veremos los operadores de los que dispone MySQL.
Agrupar filas
Es posible agrupar filas en la salida de una sentencia SELECT según los distintos valores de una columna, usando la cláusula GROUP BY. Esto, en principio, puede parecer redundante, ya que podíamos hacer lo mismo usando la opción DISTINCT. Sin embargo, la cláusula GROUP BY es más potente:
mysql> SELECT fecha FROM gente GROUP BY fecha; +------------+ | fecha | +------------+ | 1978-06-15 | | 1985-04-12 | | 1993-02-10 | | 2001-12-02 | +------------+ 4 rows in set (0.00 sec) mysql>
La primera diferencia que observamos es que si se usa GROUP BY la salida se ordena según los valores de la columna indicada. En este caso, las columnas aparecen ordenadas por fechas.
Otra diferencia es que se eliminan los valores duplicados aún si la proyección no contiene filas duplicadas, por ejemplo:
mysql> SELECT nombre,fecha FROM gente GROUP BY fecha; +---------+------------+ | nombre | fecha | +---------+------------+ | Mengano | 1978-06-15 | | Fulano | 1985-04-12 | | Pegano | 1993-02-10 | | Tulano | 2001-12-02 | +---------+------------+ 4 rows in set (0.00 sec) mysql>
Pero la diferencia principal es que el uso de la cláusula GROUP BY permite usar funciones de resumen o reunión. Por ejemplo, la función COUNT(), que sirve para contar las filas de cada grupo:
mysql> SELECT fecha, COUNT(*) AS cuenta FROM gente GROUP BY fecha; +------------+--------+ | fecha | cuenta | +------------+--------+ | 1978-06-15 | 2 | | 1985-04-12 | 2 | | 1993-02-10 | 1 | | 2001-12-02 | 1 | +------------+--------+ 4 rows in set (0.00 sec) mysql>
Esta sentencia muestra todas las fechas diferentes y el número de filas para cada fecha.
Existen otras funciones de resumen o reunión, como MAX(), MIN(), SUM(), AVG(), STD(), VARIANCE()...
Estas funciones también se pueden usar sin la cláusula GROUP BY siempre que no se proyecten otras columnas:
mysql> SELECT MAX(nombre) FROM gente; +-------------+ | max(nombre) | +-------------+ | Tulano | +-------------+ 1 row in set (0.00 sec) mysql>
Esta sentencia muestra el valor más grande de 'nombre' de la tabla 'gente', es decir, el último por orden alfabético.
Cláusula HAVING
La cláusula HAVING permite hacer selecciones en situaciones en las que no es posible usar WHERE. Veamos un ejemplo completo:
mysql> CREATE TABLE muestras (
-> ciudad VARCHAR(40),
-> fecha DATE,
-> temperatura TINYINT);
Query OK, 0 rows affected (0.25 sec)
mysql> mysql> INSERT INTO muestras (ciudad,fecha,temperatura) VALUES
-> ('Madrid', '2005-03-17', 23),
-> ('París', '2005-03-17', 16),
-> ('Berlín', '2005-03-17', 15),
-> ('Madrid', '2005-03-18', 25),
-> ('Madrid', '2005-03-19', 24),
-> ('Berlín', '2005-03-19', 18);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT ciudad, MAX(temperatura) FROM muestras
-> GROUP BY ciudad HAVING MAX(temperatura)>16;
+--------+------------------+
| ciudad | MAX(temperatura) |
+--------+------------------+
| Berlín | 18 |
| Madrid | 25 |
+--------+------------------+
2 rows in set (0.00 sec)
mysql>
La cláusula WHERE no se puede aplicar a columnas calculadas mediante funciones de reunión, como en este ejemplo.
Ordenar resultados
Además, podemos añadir una cláusula de orden ORDER BY para obtener resultados ordenados por la columna que queramos:
mysql> SELECT * FROM gente ORDER BY fecha; +----------+------------+ | nombre | fecha | +----------+------------+ | Mengano | 1978-06-15 | | Pimplano | 1978-06-15 | | Fulano | 1985-04-12 | | Frutano | 1985-04-12 | | Pegano | 1993-02-10 | | Tulano | 2001-12-02 | +----------+------------+ 6 rows in set (0.02 sec) mysql>
Existe una opción para esta cláusula para elegir el orden, ascendente o descendente. Se puede añadir a continuación ASC o DESC, respectivamente. Por defecto se usa el orden ascendente, de modo que el modificador ASC es opcional.
mysql> SELECT * FROM gente ORDER BY fecha DESC; +----------+------------+ | nombre | fecha | +----------+------------+ | Tulano | 2001-12-02 | | Pegano | 1993-02-10 | | Fulano | 1985-04-12 | | Frutano | 1985-04-12 | | Mengano | 1978-06-15 | | Pimplano | 1978-06-15 | +----------+------------+ 6 rows in set (0.00 sec) mysql>
Limitar el número de filas de salida
Por último, la cláusula LIMIT permite limitar el número de filas devueltas:
mysql> SELECT * FROM gente LIMIT 3; +---------+------------+ | nombre | fecha | +---------+------------+ | Fulano | 1985-04-12 | | Mengano | 1978-06-15 | | Tulano | 2001-12-02 | +---------+------------+ 3 rows in set (0.19 sec) mysql>
Esta cláusula se suele usar para obtener filas por grupos, y no sobrecargar demasiado al servidor, o a la aplicación que recibe los resultados. Para poder hacer esto la clásula LIMIT admite dos parámetros. Cuando se usan los dos, el primero indica el número de la primera fila a recuperar, y el segundo el número de filas a recuperar. Podemos, por ejemplo, recuperar las filas de dos en dos:
mysql> Select * from gente limit 0,2; +---------+------------+ | nombre | fecha | +---------+------------+ | Fulano | 1985-04-12 | | Mengano | 1978-06-15 | +---------+------------+ 2 rows in set (0.00 sec) mysql> Select * from gente limit 2,2; +--------+------------+ | nombre | fecha | +--------+------------+ | Tulano | 2001-12-02 | | Pegano | 1993-02-10 | +--------+------------+ 2 rows in set (0.02 sec) mysql> Select * from gente limit 4,2; +----------+------------+ | nombre | fecha | +----------+------------+ | Pimplano | 1978-06-15 | | Frutano | 1985-04-12 | +----------+------------+ 2 rows in set (0.00 sec) mysql> Select * from gente limit 6,2; Empty set (0.00 sec) mysql>