Cómo utilizar funciones en SQL

Introducción

Al trabajar con bases de datos relacionales y lenguaje de consulta estructurado (SQL), es posible almacenar, administrar y recuperar datos de un sistema de gestión de bases de datos relacionales. SQL puede recuperar los datos intactos, tal como estaban almacenados en la base de datos.

SQL también permite realizar cálculos y manipular datos mediante funciones. Por ejemplo, se pueden usar funciones para recuperar precios de productos redondeados al dólar más cercano, calcular el promedio de compras de productos o determinar los días restantes hasta el vencimiento de la garantía de una compra.

En este tutorial, utilizará varias funciones SQL para realizar cálculos matemáticos, manipular cadenas y fechas, y calcular resúmenes utilizando funciones de agregación.

Requisitos previos

Para seguir esta guía, necesita un ordenador con un sistema de gestión de bases de datos relacionales (SGBDR) basado en SQL. Las instrucciones y ejemplos de esta guía se han validado en el siguiente entorno:

• Un servidor que ejecuta Ubuntu 20.04 con un usuario no root con privilegios administrativos y un firewall configurado con UFW, como se describe en la Guía de configuración inicial del servidor para Ubuntu 20.04.
• MySQL está instalado y protegido en el servidor, como se describe en la guía "Cómo instalar MySQL en Ubuntu 20.04". Esta guía asume el uso de un usuario MySQL no root creado mediante el proceso descrito en el paso 3.
• Una introducción básica a la ejecución de consultas SELECT para seleccionar datos de una base de datos, como se explica en la guía "Cómo seleccionar filas de tablas en SQL".

Nota: Muchos RDBMS utilizan su propia implementación de SQL. Aunque los comandos descritos en este tutorial funcionan en la mayoría de los RDBMS, la sintaxis SQL estándar solo especifica un número limitado de funciones. Además, la compatibilidad con la sintaxis estándar varía entre los distintos motores de bases de datos. La sintaxis o el resultado exactos pueden variar si se prueban en un sistema distinto de MySQL.

También necesitará una base de datos con tablas cargadas con datos de ejemplo para poder usar las funciones. Le recomendamos consultar la sección "Conexión a MySQL y configuración de una base de datos de ejemplo" para obtener más información sobre cómo conectarse a un servidor MySQL y crear la base de datos de prueba utilizada en los ejemplos de esta guía.

Conexión a MySQL y configuración de una base de datos de muestra

En esta sección, se conectará a un servidor MySQL y creará una base de datos de muestra para que pueda seguir los ejemplos de esta guía.

Si su sistema de base de datos SQL se ejecuta en un servidor remoto, inicie sesión en su servidor a través de SSH:

ssh sammy@your_server_ip

A continuación, abra el entorno del servidor MySQL, en lugar de Sammy Ingrese su nombre de usuario de MySQL:

mysql -u sammy -p

Crea una base de datos llamada librería:

CREATE DATABASE bookstore;

Si la base de datos se creó correctamente, recibirá un resultado similar al siguiente:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos de la librería, ejecute el comando USE:

USE bookstore;

Verá el siguiente resultado:

Output
Database changed

Una vez seleccionada una base de datos, puede crear tablas de ejemplo en ella. Para esta guía, usaremos una biblioteca ficticia que vende diversos libros de diferentes autores.

La tabla de inventario contiene información sobre los libros disponibles en la librería. Contiene las siguientes columnas:

• book_id: Esta columna contiene el ID de cada libro representado por el tipo de dato int. Esta columna se convierte en la clave principal de la tabla y cada valor se convierte en un identificador único para la fila correspondiente.
• Autor: Esta columna contiene el nombre del autor del libro, expresado mediante el tipo de datos varchar con un máximo de 50 caracteres.
• Título: Esta columna contiene el título del libro adquirido, expresado mediante el tipo de datos varchar con un máximo de 200 caracteres.
• Introduction_date: Esta columna utiliza el tipo de datos de fecha para registrar la fecha en que la librería presentó cada libro.
• Stock: Esta columna contiene la cantidad de libros que la librería tiene en stock utilizando el tipo de datos entero.
• Precio: Esta columna almacena el precio de venta al público del libro utilizando el tipo de datos decimal con un máximo de 5 valores antes del punto decimal y 2 valores después del mismo.

Crea la tabla de ejemplo con el siguiente comando:

CREATE TABLE inventory (
book_id int,
author varchar(50),
title varchar(200),
introduction_date date,
stock int,
price decimal(5, 2),
PRIMARY KEY (book_id)
);

Si se imprime el siguiente resultado, la tabla se ha creado correctamente:

Output
Query OK, 0 rows affected (0.00 sec)

Luego, cargue la tabla de compras con algunos datos de muestra ejecutando la siguiente operación INSERT INTO:

INSERT INTO inventory
VALUES
(1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83),
(2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13),
(3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99),
(4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43),
(5, 'Mark Twain', 'The Adventures of Huckleberry Finn', '2022-10-01', 14, 23.15);

La operación INSERT INTO añade cinco libros con los valores especificados a la tabla de inventario. El siguiente resultado muestra que se han añadido las cinco filas:

Output
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

Con esto, estás listo para seguir el resto de la guía y comenzar a usar funciones en SQL.

Comprensión de las funciones SQL

Las funciones son expresiones con nombre que toman uno o más valores, realizan cálculos o transformaciones sobre los datos y devuelven un nuevo valor como resultado. Las funciones SQL se pueden considerar como funciones matemáticas. Por ejemplo, la función log(x) toma un valor x y devuelve el logaritmo de x.

Por ejemplo, si quisiera recuperar todos los títulos de libros con sus precios, ordenados del más caro al más barato, podría ejecutar el siguiente comando:

SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;

Obtendrá un resultado similar al siguiente:

Output
+------------------------------------+-------+-------------------+
| title | price | introduction_date |
+------------------------------------+-------+-------------------+
| Pride and Prejudice | 42.13 | 2022-10-04 |
| The Adventures of Huckleberry Finn | 23.15 | 2022-10-01 |
| The Time Machine | 21.99 | 2022-09-23 |
| The Picture of Dorian Gray | 20.83 | 2022-10-01 |
| Frankenstein | 17.43 | 2022-07-23 |
+------------------------------------+-------+-------------------+
5 rows in set (0.000 sec)

En este comando, título, precio y fecha de introducción son los nombres de las columnas y, en la salida, la base de datos muestra los valores intactos recuperados de esas columnas para cada libro: el título completo del libro, el precio y la fecha en que el libro llegó a la biblioteca.

Sin embargo, es posible que desee recuperar valores de la base de datos después de algún tipo de procesamiento o manipulación. Podría interesarle ver precios de libros redondeados al dólar más cercano, títulos de libros en mayúsculas o el año de introducción, excluyendo el mes y el día. En este caso, se utiliza una función.

Las funciones SQL se pueden clasificar en varios grupos, según el tipo de datos con los que operan. Estas son las funciones más utilizadas:

• Funciones matemáticas: Funciones que operan sobre valores numéricos y realizan cálculos, como redondeo, logaritmos, raíces cuadradas o potencias.
• Funciones de manipulación de cadenas: funciones que operan en cadenas y campos de texto que realizan transformaciones de texto, como convertir texto a mayúsculas, truncar o reemplazar palabras en valores.
• Funciones de fecha y hora: Funciones que operan en campos de fecha. Estas funciones realizan cálculos y conversiones, como sumar días a una fecha determinada o restar un año a una fecha completa.
• Funciones agregadas: Un caso especial de funciones matemáticas que operan sobre valores obtenidos de múltiples filas, como calcular el precio promedio de todas las filas.

Nota: La mayoría de las bases de datos relacionales, incluyendo MySQL, amplían el conjunto estándar de funciones definidas por el estándar SQL con operaciones adicionales específicas del motor de base de datos. Muchas funciones fuera del conjunto estándar de funciones SQL funcionan de forma similar en varias bases de datos, mientras que otras son específicas de un único RDBMS y sus características únicas. Puede consultar la documentación de la base de datos elegida para obtener más información sobre las funciones que ofrece. En el caso de MySQL, puede obtener más información sobre las funciones integradas y las referencias de operadores.

El siguiente ejemplo muestra la sintaxis general para utilizar una función ficticia, inexistente, llamada EXAMPLE para modificar los resultados de los valores de precios en una base de datos de inventario de una librería mediante una consulta SELECT:

SELECT EXAMPLE(price) AS new_price FROM inventory;

La función (EXAMPLE) toma el nombre de la columna (precio) como argumento entre paréntesis. Esta parte de la consulta indica a la base de datos que ejecute la función EXAMPLE con los valores de precio de la columna y devuelva los resultados. AS new_price indica a la base de datos que asigne un nombre temporal (new_price) a los valores calculados durante la consulta. Con esta función, se pueden identificar los resultados de la función en la salida y hacer referencia a los valores calculados mediante las cláusulas WHERE y ORDER BY.

En la siguiente sección, utilizará funciones matemáticas para realizar cálculos comunes.

Uso de funciones matemáticas

Las funciones matemáticas operan con valores numéricos, como el precio de un libro o la cantidad de libros en existencia en la base de datos de muestra. Permiten realizar cálculos en la base de datos y adaptar los resultados a sus necesidades.

El redondeo es uno de los usos más comunes de las funciones matemáticas en SQL. Supongamos que necesita recuperar los precios de todos los libros, pero solo desea redondear los valores al dólar más cercano. Para ello, puede usar la función REDONDO Uso que realiza la operación de redondeo.

Ejecute el siguiente comando:

SELECT title, price, ROUND(price) AS rounded_price FROM inventory;

Verá un resultado similar al siguiente:

Output
+------------------------------------+-------+---------------+
| title | price | rounded_price |
+------------------------------------+-------+---------------+
| The Picture of Dorian Gray | 20.83 | 21 |
| Pride and Prejudice | 42.13 | 42 |
| The Time Machine | 21.99 | 22 |
| Frankenstein | 17.43 | 17 |
| The Adventures of Huckleberry Finn | 23.15 | 23 |
+------------------------------------+-------+---------------+
5 rows in set (0.000 sec)

El comando anterior devuelve los valores de las columnas. título y precio con una columna temporal precio redondeado Que resulta de la función REDONDO(precio) Esta función toma un argumento, que es el nombre de la columna (aquí, precio) y redondea los valores de esa columna en la tabla al entero más cercano.

La función ROUND también acepta argumentos adicionales que especifican el número de decimales a los que se debe redondear, así como operaciones aritméticas en lugar del nombre de la columna. Por ejemplo, ejecute la siguiente consulta:

SELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;

Obtendrás el siguiente resultado:

Output
+------------------------------------+-------+-------+-------------+
| title | stock | price | stock_price |
+------------------------------------+-------+-------+-------------+
| The Picture of Dorian Gray | 4 | 20.83 | 83.3 |
| Pride and Prejudice | 12 | 42.13 | 505.6 |
| The Time Machine | 7 | 21.99 | 153.9 |
| Frankenstein | 9 | 17.43 | 156.9 |
| The Adventures of Huckleberry Finn | 14 | 23.15 | 324.1 |
+------------------------------------+-------+-------+-------------+
5 rows in set (0.000 sec)

Ejecución REDONDEAR(Precio * Stock, 1) Primero multiplica el precio de un libro por la cantidad de libros disponibles y luego redondea el precio resultante al primer decimal. El resultado se mostrará en la columna temporal Stock_Price.

Otras funciones matemáticas integradas en MySQL incluyen funciones trigonométricas, raíces cuadradas, potencias, logaritmos y exponenciales. Puede obtener más información sobre el uso de funciones matemáticas en SQL en el tutorial "Cómo usar expresiones matemáticas y funciones de agregación en SQL".

En la siguiente sección, manipulará texto de la base de datos utilizando funciones SQL.

Uso de funciones de texto

Las funciones de texto en SQL permiten manipular y modificar valores de cadena. Por ejemplo, podría querer recuperar los nombres de los libros de forma más ordenada eliminando los espacios al principio y al final de sus títulos.

Para ello, puedes utilizar la función RECORTAR Utilice esta función para eliminar todos los espacios y caracteres adicionales al principio y al final de una cadena.

Consideremos el siguiente ejemplo:

SELECT LOWER(title) AS title_lowercase FROM inventory;

La siguiente salida se imprime en la pantalla:

Output
+------------------------------------+
| title_lowercase |
+------------------------------------+
| the picture of dorian gray |
| pride and prejudice |
| the time machine |
| frankenstein |
| the adventures of huckleberry finn |
+------------------------------------+
5 rows in set (0.001 sec)

La función SQL LOWER toma un solo argumento y convierte su contenido a minúsculas. Mediante la columna alias AS title_downcase, los datos resultantes se presentan en una columna temporal denominada title_downcase.

Ahora recupera todos los autores, esta vez convertidos a mayúsculas. Intenta ejecutar la siguiente consulta SQL:

SELECT UPPER(author) AS author_uppercase FROM inventory;

Obtendrás el siguiente resultado:

Output
+----------------------+
| author_uppercase |
+----------------------+
| OSCAR WILDE |
| JANE AUSTEN |
| HERBERT GEORGE WELLS |
| MARY SHELLEY |
| MARK TWAIN |
+----------------------+
5 rows in set (0.000 sec)

En lugar de la función LOWER, se utilizó la función UPPER, que funciona de forma similar, pero convierte el texto a mayúsculas. Ambas funciones se pueden usar para garantizar la coherencia entre mayúsculas y minúsculas al recuperar datos.

Otra función útil para manipular cadenas es CONCAT, que toma múltiples argumentos que contienen valores de texto y los concatena. Intente recuperar autores y títulos de libros en una sola columna. Para ello, ejecute la siguiente instrucción:

SELECT CONCAT(author, ': ', title) AS full_title FROM inventory;

Este comando devuelve la siguiente salida:

Output
+------------------------------------------------+
| full_title |
+------------------------------------------------+
| Oscar Wilde: The Picture of Dorian Gray |
| Jane Austen: Pride and Prejudice |
| Herbert George Wells: The Time Machine |
| Mary Shelley: Frankenstein |
| Mark Twain: The Adventures of Huckleberry Finn |
+------------------------------------------------+
5 rows in set (0.001 sec)

La función CONCAT concatena varias cadenas y se ejecuta con tres argumentos. El primero, "autor", se refiere a la columna de autor que contiene el nombre del autor. El segundo, :, es un valor de cadena arbitrario para delimitar autores y títulos de libros con dos puntos. El último, "título", se refiere a la columna que contiene los títulos de los libros.

Como resultado de esta consulta, los autores y títulos se devuelven en una columna temporal llamada full_title, que es concatenada directamente por el motor de base de datos.

Otras funciones de cadena integradas en MySQL incluyen funciones para buscar y reemplazar cadenas, recuperar subcadenas, rellenar y recortar valores de cadenas, y aplicar expresiones regulares, entre otras. Puede obtener más información sobre el uso de funciones SQL para concatenar varios valores en el tutorial "Cómo manipular datos con funciones CAST y expresiones de concatenación en SQL". También puede consultar "Funciones y operadores de cadena" en la documentación de MySQL.

En la siguiente sección, utilizará funciones SQL para manipular fechas de la base de datos.

Uso de funciones de fecha y hora

Las funciones de fecha y hora en SQL permiten manipular valores almacenados en columnas que contienen fechas y marcas de tiempo al procesar consultas SQL. Permiten extraer información de fechas, realizar cálculos de fechas o dar formato a fechas y marcas de tiempo en los formatos de salida requeridos.

Supongamos que necesita dividir la fecha de publicación del libro en año, mes y día por separado en lugar de tener una sola columna de fecha en la salida.

Intente ejecutar el siguiente comando:

SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;

Verá un resultado similar al siguiente:

Output
+-------------------+------+-------+------+
| introduction_date | year | month | day |
+-------------------+------+-------+------+
| 2022-10-01 | 2022 | 10 | 1 |
| 2022-10-04 | 2022 | 10 | 4 |
| 2022-09-23 | 2022 | 9 | 23 |
| 2022-07-23 | 2022 | 7 | 23 |
| 2022-10-01 | 2022 | 10 | 1 |
+-------------------+------+-------+------+
5 rows in set (0.000 sec)

Esta sentencia SQL utiliza tres funciones independientes: AÑO, MES y DÍA. Cada función toma como argumento el nombre de la columna donde se almacenan las fechas y extrae solo una parte de la fecha completa: un año, un mes o un día, respectivamente. Con estas funciones, se puede acceder a partes individuales de una fecha en consultas SQL.

Otra función útil para manipular fechas es DATEDIFF, que permite recuperar el número de días entre dos fechas. Ahora, compruebe cuántos días han transcurrido entre la fecha de presentación de cada libro y la fecha actual.

Ejecute la siguiente consulta:

SELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;

La siguiente salida se imprime en la pantalla:

Output
+-------------------+------------+
| introduction_date | days_since |
+-------------------+------------+
| 2022-10-01 | -30 |
| 2022-10-04 | -27 |
| 2022-09-23 | -38 |
| 2022-07-23 | -100 |
| 2022-10-01 | -30 |
+-------------------+------------+
5 rows in set (0.000 sec)

La función DATEDIFF acepta dos argumentos: una fecha de inicio y una fecha de finalización. Calcula el número de días que separan estos dos puntos. Si la fecha de finalización es anterior, el resultado puede ser negativo. En este ejemplo, el primer argumento es el nombre de la columna "fecha_de_introducción", que contiene las fechas de la tabla de inventario. El segundo argumento es otra función llamada "FECHA_CURRENTE", que representa la fecha actual del sistema. Al ejecutar esta consulta, se recupera el número de días transcurridos entre estos dos puntos y se guardan los resultados en una columna temporal llamada "días_desde".

Nota: DATEDIFF no forma parte del conjunto oficial de funciones estándar de SQL. Si bien muchas bases de datos admiten esta función, la sintaxis suele variar entre los distintos motores de bases de datos. Este ejemplo sigue la sintaxis nativa de MySQL.

Otras funciones de manipulación de fechas integradas en MySQL incluyen la suma y resta de intervalos de tiempo y fechas, el formato de fechas para diferentes idiomas, la recuperación de nombres de días y meses, o la creación de nuevos valores de fecha. Puede obtener más información sobre cómo trabajar con fechas en SQL en el tutorial "Cómo trabajar con fechas y horas en SQL". También puede consultar las funciones de fecha y hora en la documentación de MySQL.

En la siguiente sección, aprenderá a utilizar funciones de agregación.

Uso de funciones agregadas

En todos los ejemplos anteriores, se utilizaron funciones SQL para aplicar transformaciones o cálculos a valores de columnas individuales en una fila que representa un libro en una librería. SQL permite realizar cálculos matemáticos en varias filas para obtener información agregada sobre todo el conjunto de datos.

• Las funciones de agregación básicas en SQL incluyen:
• AVG para el promedio de los valores sobre los que se realizan los cálculos.
• CONTAR para el número de valores sobre los que se realizan cálculos.
• MAX para valor máximo.
• MIN para valor mínimo.
• SUMA para la suma de todos los valores.

Puede incluir varias funciones de agregación en su consulta SELECT. Imagine que desea consultar la cantidad de libros disponibles en una librería, el precio máximo disponible de cada libro y el precio promedio de todo el catálogo. Para ello, ejecute la siguiente instrucción:

SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;

Este comando devuelve la siguiente salida:

Output
+-------+-----------+-----------+
| count | max_price | avg_price |
+-------+-----------+-----------+
| 5 | 42.13 | 25.106000 |
+-------+-----------+-----------+
1 row in set (0.001 sec)

La consulta anterior utiliza tres funciones de agregación simultáneamente. La función COUNT cuenta las filas que busca la consulta. En este ejemplo, el título se pasa como argumento, pero como el número de filas será el mismo para cada columna marcada, puede usar cualquier otro nombre de columna como argumento. La función MAX calcula el valor máximo de la columna de precio: el nombre de la columna es importante, ya que el cálculo se realiza sobre los valores de esa columna. La última función es AVG, que calcula el promedio de todos los precios de la columna de precio.

El uso de funciones de agregación de este modo genera una fila en la base de datos con columnas temporales que representan los valores de los cálculos de agregación. Las filas de origen se utilizan internamente para el cálculo, pero no se devuelven mediante la consulta. En este ejemplo, se utilizaron funciones de agregación para calcular valores estadísticos de toda la tabla de inventario a la vez, considerando todas las filas para el resumen.

Con SQL, también puede dividir las filas de una tabla en grupos y luego calcular la suma de esos grupos por separado. Por ejemplo, puede calcular el precio promedio de libros de diferentes autores para averiguar qué autor ha publicado los títulos más caros. Puede obtener más información sobre la agrupación de filas para estos cálculos en el tutorial "Cómo usar GROUP BY y ORDER BY en SQL". También puede obtener más información sobre el uso de agregados en el tutorial "Cómo usar expresiones matemáticas y funciones de agregación en SQL".

Resultado

En esta guía, aprendió a usar funciones en SQL para realizar cálculos con datos y crear consultas más complejas. Estas funciones le ayudan a manipular los datos de diferentes maneras y a obtener mejores resultados. Tenga en cuenta que la sintaxis exacta de SQL puede variar según el tipo de sistema de gestión de bases de datos relacionales (SGBDR).