Operaciones para la proyección de información MySQL

Módulo 6

INTRODUCCIÓN

Con la módulo de esta semana el lector tendrá la oportunidad de conocer la utilidad de algunas funciones de MySQL para la proyección de la información principalmente. De igual manera, este capitulo inicia explicando algunos aspectos para la relación de información, a través del concepto clave foránea.

Otro aspecto interesante a tocar inicialmente será la generación del modelo relacional mediante PhpMyAdmin.

Este módulo enfocara sus esfuerzos en mostrar las diferentes funciones y utilidades con las que podemos contar con MySQL para relacionar información y realzar manipulación de información, para lograr trasformaciones muy puntuales sobre esta y de esta manera lograr solucionar problemas que en la vida real se nos presentan.

Claves foráneas y restricciones

Una clave foránea o clave externa es una columna o grupo de columnas en una tabla que se vincula a una columna o grupo de columnas en otra tabla. La clave foránea impone restricciones a los datos de las tablas relacionadas, lo que permite que MySQL mantenga la integridad referencial.

Echemos un vistazo a las siguientes tablas oficina y empleado de la base de datos empresa_confecciones_limitada (base de datos que estaremos trabajando durante este módulo).

En este diagrama, una oficina puede pertenecer a uno a muchos empleados y un empleado debe tener una oficina asignada.

La relación entre oficina y empleado es de uno a muchos. Y esta relación se establece mediante la clave foránea en la tabla empleado especificada por la columna codigOficina.

La columna codigOficina de la tabla empleado se vincula a la columna codigOficina, la cual es la clave principal de la tabla oficina.

La tabla oficina se denomina tabla principal, y la tabla empleado se conoce como tabla secundaria.

Por lo general, las columnas de clave externa de la tabla secundaria a menudo se refieren a las columnas de clave principal de la tabla principal.

Una tabla puede tener más de una clave externa donde cada clave externa hace referencia a una clave primaria de las diferentes tablas principales.

Una vez que se aplica una restricción de clave externa, las columnas de clave externa de la tabla secundaria deben tener la fila correspondiente en las columnas de clave principal de la tabla principal o los valores en estas columnas de clave externa deben ser NULL.

Clave foránea autorreferencial

A veces, las tablas primaria y secundaria pueden hacer referencia a la misma tabla. En este caso, la clave externa hace referencia a la clave principal dentro de la misma tabla.

La columna jefe es una clave externa que se refiere a la columna numeroEmpleado que es la clave principal de la tabla empleado.

Esta relación permite que la tabla empleado almacene la estructura de informes entre empleados y jefes. Cada empleado informa a cero o un empleado y un empleado puede tener cero o muchos subordinados.

La clave externa de la columna jefe se conoce como clave externa recursiva o autorreferencial.

Sintaxis de una clave foránea en MySQL

Para referenciar la restricción de una llave foránea en MySQL se utiliza la expresión FOREIGN KEY. Esta restricción no aplica en MyISAM como motor de almacenamiento, debido a que no admite relaciones en una base de datos. De los motores vistos y profundizados en estos módulos, solamente InnoDB soportan lleves foráneas.

Una llave foránea puede ser creada desde un CREATE TABLE o ALTER TABLE. La sintaxis para realizarlo por SQL es la siguiente:

[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (column_name, ...)
REFERENCES parent_table(colunm_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

En esta sintaxis:

Primero, especifique el nombre de la restricción de clave externa que desea crear después de la palabra clave CONSTRAINT. Si omite el nombre de la restricción, MySQL genera automáticamente un nombre para la restricción de clave externa.

En segundo lugar, especifique una lista de columnas de claves externas separadas por comas después de las palabras claves FOREIGN KEY. El nombre de la clave externa también es opcional y se genera automáticamente si lo omite.

En tercer lugar, especifique la tabla principal seguida de una lista de columnas separadas por comas a las que hacen referencia las columnas de clave externa.

Finalmente, especifique cómo la clave externa mantiene la integridad referencial entre las tablas secundaria y primaria mediante las cláusulas ON DELETE y ON UPDATE. La palabra clave reference_option determina la acción que llevará acabo MySQL cuando los valores de las columnas claves se eliminan (ON DELETE) o actualizados (ON UPDATE).

MySQL tiene cinco opciones de referencia: CASCADE, SET NULL, NO ACTION, RESTRICT y SET DEFAULT.

CASCADE: si una fila de la tabla principal se elimina o actualiza, los valores de las filas coincidentes en la tabla secundaria se eliminan o actualizan automáticamente.
SET NULL: si una fila de la tabla principal se elimina o actualiza, los valores de la columna (o columnas) de clave externa en la tabla secundaria se establecen en NULL.
RESTRICT: si una fila de la tabla principal tiene una fila coincidente en la tabla secundaria, MySQL rechaza eliminar o actualizar filas en la tabla principal.
NO ACTION: es lo mismo que RESTRICT.
SET DEFAULT: es reconocido por el analizador MySQL. Sin embargo, esta acción es rechazada por las tablas que tienen configuradas el sistema de almacenamiento de InnoDB y NDB.

Si no especifica la cláusula ON DELETE y ON UPDATE, la acción predeterminada es RESTRICT.

Creación de llave foránea con PhpMyAdmin

Suponiendo que ya se tiene creada la base de datos y las tablas de esta, se puede realizar estas relaciones mediante este cliente, veamos:

1. Vamos a la sección Estructura de la tabla, donde se desea crear la llave foránea.

2. Luego nos dirigimos a la sección Vista de relaciones.

3. En esta sección se debe diligenciar la información asociada a la llave foránea, tal como se muestra a continuación y guardamos la o las restricciones ingresadas.

4. Al final obtendremos el script SQL que se generó en MySQL.

Ejemplo en script MySQL

Veamos el siguiente ejemplo. Partamos de la siguiente relación y realizando la creación durante la creación de la tabla y después de haber creado la tabla.

Cuando se crea la tabla, el script a ejecutar es el siguiente:

CREATE TABLE `empleado` (
  `numeroEmpleado` int(11) NOT NULL,
  `apellido` varchar(50) NOT NULL,
  `nombre` varchar(50) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `correo` varchar(100) NOT NULL,
  `codigOficina` varchar(10) NOT NULL,
  `jefe` int(11) DEFAULT NULL,
  `titulo` varchar(50) NOT NULL,
  PRIMARY KEY (`numeroEmpleado`),
  KEY `jefe` (`jefe`),
  KEY `codigOficina` (`codigOficina`),
  CONSTRAINT `fk_oficina_empleado` 
  FOREIGN KEY (`codigOficina`) 
  REFERENCES `oficina` (`codigOficina`)
    ON DELETE RESTRICT 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Después de haber creado la tabla, se debe ejecutar el siguiente script:

ALTER TABLE empleado
ADD CONSTRAINT fk_oficina_empleado
FOREIGN KEY (codigOficina)
REFERENCES oficina(codigOficina)
ON DELETE RESTRICT 
ON UPDATE CASCADE

Restricción UNIQUE

A veces, desea asegurarse que los valores en una columna o un grupo de columnas sean únicos. Por ejemplo, las direcciones de correo electrónico de los usuarios de la tabla usuario o el número de teléfono de los clientes de la tabla cliente deben ser únicos. Para hacer cumplir esta regla, usa una restricción UNIQUE.

Una UNIQUE restricción es una restricción de integridad que asegura que los valores en una columna o grupo de columnas sean únicos. Una restricción UNIQUE puede ser una restricción de columna o una restricción de tabla.

Para definir una restricción UNIQUE para una columna cuando se crea una tabla , use esta sintaxis:

CREATE TABLE nombre_tabla(
    ...,
    nombre_columna tipo_dato UNIQUE,
    ...
);

Para este caso si inserta o actualiza un valor que causa duplicación en nomnbre_columna, MySQL rechaza el cambio y emite un error.

Para definir una restricción UNIQUE para dos o más columnas, utilice la siguiente sintaxis:

CREATE TABLE nombre_tabla(
   ...
   nombre_columna1 tipo_dato,
   nombre_columna2 tipo_dato,
   ...,
   UNIQUE(nombre_columna1,nombre_columna2)
);

En esta sintaxis, agrega una lista de columnas separadas por comas entre paréntesis después de la palabra clave UNIQUE. MySQL usa la combinación de valores en ambas columnas nombre_columna1 y nombre_columna2 para evaluar la unicidad.

Si define una restricción UNIQUE sin especificar un nombre, MySQL genera automáticamente un nombre para ella. Para definir una restricción UNIQUE con un nombre, use esta sintaxis:

[CONSTRAINT constraint_name]
UNIQUE(column_list)

En esta sintaxis, especifica el nombre de la restricción UNIQUE después de la palabra clave CONSTRAINT.

Veamos el siguiente ejemplo:

CREATE TABLE proveedor (
    proveedor_id INT AUTO_INCREMENT,
    nombre VARCHAR(255) NOT NULL,
    telefono VARCHAR(15) NOT NULL UNIQUE,
    direccion VARCHAR(255) NOT NULL,
    PRIMARY KEY (proveedor_id),
    CONSTRAINT uq_nombre_direccion UNIQUE (nombre, direccion)
);

En este ejemplo, la primera restricción UNIQUE se define para la columna telefono:

telefono VARCHAR(15) NOT NULL UNIQUE

Y la segunda restricción es la combinación de las columnas de nombre y direccion:

CONSTRAINT uq_nombre_direccion UNIQUE (nombre, direccion)

Generar modelo relacional desde PhpMyAdmin

Desde muchos clientes de MySQL es posible generar el modelo relacional de las bases de datos, esto con la finalidad de tener nuestro diagrama lo mas actualizado posible, sin necesidad de invertir mucho tiempo en los posibles cambios que a podido sufrir nuestra base de datos. En PhpMyAdmin contamos con un diseñador que nos permite crear la base de datos y obtener una vista de nuestro modelo relacional.

Para explicar esta generación de este modelo tomaremos como punto de referencia Clever-Cloud, aunque también puede aplicar en el cliente de PhpMyAdmin instalado en XAMPP (es posible que en local esta extensión presente dificultades). Veamos como se genera este modelo.

1. Ingrese a la base de datos creada en Clever Cloud y nos dirigimos a la opción de PhpMyAdmin (en caso de no recordar como ingresar a Clever Cloud, se puede remitir a el módulo 5 de base de datos).

2. Luego seleccionamos la base de datos que nos asigna Clever Cloud (partimos del hecho que ya se crearon las tablas de esta base de datos).

3. Una vez seleccionada la base de datos, nos dirigimos a la opción More > Designer.

4. Organice el modelo, de tal manera que quede lo mas visible las tablas y relaciones que hay entre las tablas.

5. Diríjase al menú lateral del diseñador y seleccione la opción Export schema.

6. Seleccione el método de exportación, para efectos de este ejemplo se exportará el diagrama como SVG.

7. Con esto ya tendremos nuestro modelo exportado. En el siguiente enlace se puede apreciar el modelo descargado: https://github.com/devoscarmesa/base_datos_semana_6/blob/master/modelo_relacional_colegio_la_trinidad.svg

Proyección de registros (SELECT)

La declaración SQL SELECT le permite leer datos de una o más tablas. Para escribir una declaración SELECT en MySQL, siga esta sintaxis:

SELECT lista_de_campos
FROM nombre_tabla;

Veamos cada parte de la declaración.

Primero, empiece con la palabra clave SELECT. La palabra clave tiene un significado especial en MySQL. En este caso, SELECT indica a MySQL que recupere datos.

Luego, tiene espacio y luego una lista de columnas o expresiones que desea mostrar en el resultado.

Luego, tienes la palabra clave FROM, el espacio y el nombre de la tabla desde donde se van a extraer los datos.

Finalmente, tiene un punto y coma ; al final de la declaración. El punto y coma ; es el delimitador de instrucciones. Especifica el final de una declaración. Si tiene dos o más declaraciones, use el punto y coma ; para separarlas para que MySQL ejecute cada declaración individualmente.

En la declaración SELECT, SELECT y FROM son palabras clave y están escritas en mayúsculas. Básicamente, se trata solo de formatear. Las letras mayúsculas hacen que las palabras clave se destaquen en gran parte de los editores.

Dado que SQL no es un lenguaje que distingue entre mayúsculas y minúsculas, puede escribir las palabras claves en minúsculas o mayúsculas.

También es importante tener en cuenta que la palabra clave FROM está en una nueva línea. MySQL no requiere esto. Sin embargo, colocar la palabra clave FROM en una nueva línea hará que la consulta sea más fácil de leer y más simple de mantener.

Al evaluar la declaración SELECT, MySQL evalúa primero la cláusula FROM y luego la cláusula SELECT:

Para el resto del módulo se usará la siguiente la base de datos empresa_confecciones_limitada, la cual cuenta con datos de prueba para realizar consultas y operaciones sobre estos. A continuación, se deja el modelo relacional y el link donde se puede descargar el script: https://github.com/devoscarmesa/base_datos_semana_6/blob/master/empresa_confecciones_limitada.sql

Uso de SELECT para recuperar una sola columna

El siguiente ejemplo usa la declaración SELECT para seleccionar el nombre de todos los clientes:

SELECT `nombreCliente` 
FROM `cliente`;

La salida de una declaración SELECT se llama resultados o conjunto de resultados, ya que es un conjunto de datos que resulta de una consulta.

Uso de SELECT para recuperar varias columnas

El siguiente ejemplo usa la declaración SELECT para obtener el nombre, apellido 1, apellido 2 y teléfono:

SELECT 
    `nombreCliente`,
    `contactoApellido`,
    `contactoApellido1`,
    `telefono`
FROM 
    `cliente`;

Aunque la tabla cliente tiene muchas columnas, la declaración SELECT solo devuelve datos de cuatro columnas.

Uso de SELECT para recuperar todas las columnas

Si desea recuperar datos de todas las columnas de la tabla cliente, puede especificar todos los nombres de columna en la cláusula SELECT o simplemente use la abreviatura de asterisco (*) como se muestra en la siguiente consulta:

SELECT *
FROM `cliente`;

La consulta devuelve datos de todas las columnas de la tabla cliente.

Nota: Seleccionar datos de esta manera no es muy recomendado cuando se esta integrando en un lenguaje como Node.js, PHP, Java, Python, etc.; debido a la alta latencia o tiempo que puede tardar la respuesta al cliente.

Uso de los Alias

Un alias es como su nombre lo dice, un apodo o forma alternativa de llamar a algo.

Estos alias en MySQL son usados para renombrar o para evitar conflictos con campos repetidos. La cláusula para un alias es AS y su sintaxis es:

SELECT nombre_columna_original AS nuevo_nombre_deseamos 
FROM nombre_tabla;

Veamos el siguiente ejemplo:

SELECT 
    `nombreCliente` AS nombre,
    `contactoApellido` AS apellido1,
    `contactoApellido1` apellido2
FROM 
    `cliente`;

También es posible solo dejar un espacio en blanco, seguido del nombre con el que queremos nombrar esta columna, tal como se observa en el ejemplo anterior.

    `contactoApellido1` apellido2

Sentencias de control de flujo

Las sentencias de control de flujo nos permiten intervenir la salida de lo que queremos proyectar, de acuerdo a una serie de condiciones. Estas sentencias las podemos ver reflejadas en varias partes de una consulta SQL, por ejemplo, un SELECT. Veamos algunas sentencias de este tipo.

CASE – WHEN

La instrucción CASE se usa para aplicar una construcción condicional compleja en MySQL. Tiene la funcionalidad de una instrucción IF-THEN-ELSE al permitirle evaluar condiciones y devolver un valor cuando se cumple la primera condición.

La sintaxis de la función CASE en MySQL es:

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Parámetros o argumentos

expression

Opcional. Es el valor que está comparando con la lista de condiciones. (es decir: condition_1, condition_2, ... condition_n)

condition_1, condition_2, ... condition_n

Evaluado en el orden indicado. Una vez que se determina que una condición es verdadera, la función CASE devolverá el resultado y no evaluará más las condiciones.

result_1, result_2, ... result_n El valor devuelto una vez que se determina que una condición es verdadera.

Nota

Si no se encuentra ninguna condición verdadera, la función CASE devolverá el valor de la cláusula ELSE.

Si se omite la cláusula ELSE y no se determina que ninguna condición sea verdadera, la instrucción CASE devolverá NULL.

Veamos el siguiente ejemplo:

Las para la empresa de confecciones es muy importante conocer la localización de las de los productos, el cual esta correlacionado en la siguiente tabla.

Localización numérica

Nombre de la localización

1:10

Estante de cuero

1:12

Estante telas

1:18

Estante materia prima

1:24

Estante de cuerina

1:32

Estante de cintas

1:50

Estante de material variado

1:700

Estante de estampados

1:72

Estante de material desconocido

Se requiere realizar una consulta que muestre el nombre de la localización del producto.

SELECT 
    nombreProducto, 
    CASE localizacionProducto 
        WHEN "1:10" THEN "Estante de cuero" 
        WHEN "1:12" THEN "Estante telas" 
        WHEN "1:18" THEN "Estante materia prima" 
        WHEN "1:24" THEN "Estante de cuerina" 
        WHEN "1:32" THEN "Estante de cintas" 
        WHEN "1:50" THEN "Estante de material variado" 
        WHEN "1:700" THEN "Estante de estampados" 
        ELSE "Estante de material desconocido"
    END AS nombreLocalizacion
FROM `producto`

IF

La sentencia de control IF () toma tres expresiones y si la primera expresión es verdadera, ni cero ni NULL, devuelve la segunda expresión. De lo contrario, devuelve la tercera expresión.

Dependiendo del contexto en el que se use, devuelve un valor numérico o de cadena.

Sintaxis:

IF (expression, expr_true, expr_false)

Dependiendo del contexto en el que se use, devuelve un valor numérico o de cadena.

Parámetros

Nombre

Descripción

Tipo de retorno

expression

Una expresión o condición.

expr_true

Devuelve cuando la condición es VERDADERA.

Una cadena cuando expr_true es una cadena, un valor de punto flotante cuando expr_true es un valor de punto flotante y un número entero cuando expr_true es un número entero.

expr_false

Devuelve cuando la condición es FALSA.

Una cadena cuando expr_false es una cadena, un valor de punto flotante cuando expr_false es un valor de punto flotante y un entero cuando expr_false es un número entero.

Veamos algunos ejemplos:

SELECT IF(1>3,'verdadero','falso');

En la fábrica de confecciones se necesita saber cuándo un producto está agotado en inventario. Para el dueño, tener menos de 1000 elementos de un producto es la alerta para indicar que se debe aumentar el inventario.

Por esto se requiere realizar una consulta que indique cuales productos están agotados o si cuentan con inventario suficiente.

SELECT 
    nombreProducto,
    IF(cantidadEnInventario < 1000,'Agotado','Inventario suficiente') AS existenciaEnInventario
FROM 
    producto;

IFNULL

La función IFNULL () toma dos expresiones y si la primera expresión no es NULL, devuelve la primera expresión. De lo contrario, devuelve la segunda expresión.

Dependiendo del contexto en el que se use, devuelve un valor numérico o de cadena.

Sintaxis:

IFNULL (expresión1, expresión2);

Veamos algunos ejemplos:

SELECT IFNULL(0,2);

SELECT IFNULL(1,2);

En la fábrica de confecciones, el dueño desea saber si un cliente no tiene asignado el estado donde vive, por lo cual se debe construir una consulta para tal fin.

SELECT 
    nombreCliente, 
    IFNULL(estado,'No tiene estado asignado') AS estadoAsignado
FROM 
    cliente;

Funciones para manipulación de cadenas

En MySQL se cuenta con algunas funciones para la manipulación de cadenas de texto, estas funciones tienen fines muy específicos y ayudan al lector en la solución de algunas problemáticas que son muy comunes en su trabajo diario. Veamos algunas de estas funciones.

LENGTH

Retorna la longitud de la cadena enviada como argumento. Veamos algunos ejemplos:

SELECT LENGTH('Hola');

Veamos un ejemplo que permita conocer el tamaño del texto descriptivo de la tabla de la línea de producto.

SELECT 
    lp.lineaProducto, 
    LENGTH(lp.textoDescriptivo)
FROM 
    linea_producto AS lp;

CONCAT

Devuelve una cadena de texto, encadenando o uniéndonos una serie de argumentos que le pasamos a la función. Veamos algunos ejemplos.

SELECT CONCAT('Hola,',' ','como esta?');

Usemos esta función para concatenar todo el nombre del empleado.

SELECT 
    numeroEmpleado,
    CONCAT(nombre, ' ', apellido) AS nombreCompleto 
FROM 
    empleado;

CONCAT_WS

Esta función es igual a la anterior, pero tiene “ws” al final de la función, iniciales de "with separator". La diferencia con la función CONCAT es que esta función espera el primer argumento es el separador de entre los otros argumentos que se pasan. Veamos algunos ejemplos.

SELECT CONCAT_WS(', ','Juan','Pedro','Luis');

Usemos esta función para concatenar todo el nombre del cliente.

SELECT 
    numeroCliente,
    CONCAT_WS(' ', nombreCliente, contactoApellido, contactoApellido1) AS nombreCliente
FROM 
    cliente;

UPPER Y UCASE

A estas funciones se le pasa una cadena de texto y devuelve una cadena de texto con todos sus caracteres en mayúsculas. Veamos los siguientes ejemplos:

SELECT UPPER('HoLA EsTUDIAnte');
SELECT UCASE('hola estudiantes, como ESTA?');

LOWER Y LCASE

A estas funciones se le pasa una cadena de texto y devuelve una cadena de texto con todos sus caracteres en minúscula. Veamos los siguientes ejemplos:

SELECT LOWER('HOLA ESTUDIAnte');
SELECT LCASE('HOLA ESTUDIANTE');

LTRIM

Esta función se le pasa una cadena de texto y esta se encarga de eliminar los espacios del inicio a la izquierda. Veamos el siguiente ejemplo:

SELECT LTRIM('          Hola            saludos        ');

REPLACE

Esta función se encarga de buscar un texto sobre una cadena de texto y remplazar el texto encontrado por otro texto.

Sintaxis:

REPLACE(cadena, cadena_a_buscar, cadena_a_reemplazar)

Veamos el siguiente ejemplo:

SELECT REPLACE('xxx.google.com','x','w');
SELECT REPLACE(' Hola estudiantes, saludos ',' ','-');

RVERSE

La función REVERSE () invierte una cadena y devuelve otra cadena de texto. Veamos el siguiente ejemplo:

SELECT REVERSE('Hola estudiantes, saludos');

Funciones matemáticas

En MySQL también se cuenta con funciones matemáticas para dar solución a múltiples problemas de la vida cotidiana. Es importante resaltar, que por ser funciones matemáticas, se puede presentar errores con facilidad, por lo cual, estas funciones retornan NULL. Veamos algunas de estas funciones.

ABS

Retorna el valor absoluto de un número X que se le pasa a la función. Veamos algunos ejemplos:

SELECT ABS(290);
SELECT ABS(-3892);

CEILING Y CEIL

Estas funciones devuelven el entero más pequeño cuyo valor es mayor que X (valor que se le pasa a la función). El alias CEIL() fue añadido en la versión 4.0.6. El valor de retorno se convierte a BIGINT. Veamos algunos ejemplos:

SELECT CEILING(1.2);

SELECT CEIL(1.8);

FLOOR

Esta función devuelve el entero más grande inferior o igual a X (valor que se le pasa a la función). El valor de retorno se convierte a BIGINT. Veamos algunos ejemplos:

SELECT FLOOR(1.2);

SELECT FLOOR(-1.8);

LN

Devuelve el logaritmo natural de X (valor que se le pasa a la función). Veamos algunos ejemplos:

SELECT LN(2);

SELECT LN(10);

MOD

Módulo (operador % en gran parte de los lenguajes). Devuelve el resto de la división de N entre (MOD(N, M)). También es posible utilizar el operador %. Veamos algunos ejemplos:

SELECT MOD(10, 2);

SELECT 234 % 10;

POW

Esta función retorna el valor de X elevado a la potencia Y (POW(X,Y)). Veamos algunos ejemplos:

SELECT POW(2, 3);

SELECT POW(8, 2);

SQRT

Esta función devuelve la raíz cuadrada no negativa de X (número que se le pasa a la función). Veamos algunos ejemplos:

SELECT SQRT(9);

SELECT SQRT(2);

FORMAT

Formatea el número X según la plantilla '#,###,###.##', redondeando a D decimales (FROMAT(X,D)), y devuelve el resultado como una cadena. Si D es 0, el resultado no tendrá punto decimal ni parte fraccionaría. Veamos algunos ejemplos:

SELECT FORMAT(25900670.98746,2);

SELECT FORMAT(300200.746,0);

Funciones para la manipulación de fechas

Al momento de desarrollar un sistema de información el trabajo con fechas es un dolor de cabeza para los integrantes de un equipo. En ese sentido MySQL incorpora algunas funciones para la manipulación de fechas, permitiendo transformarlas y realizar operaciones complejas con ellas. Veamos algunas de estas funciones.

NOW

La función NOW() devuelve la fecha y hora actual en la zona horaria configurada como una cadena o un número, en el formato 'YYYY-MM-DD HH:MM:DD' o 'YYYYMMDDHHMMSS.uuuuuu'. El formato retornado, depende del contexto en el que se invoque dicha función, veamos:

SELECT NOW();

En este contexto se retorna una cadena de texto formateada como fecha.

SELECT NOW() + 0;

Sin embargo, en el contexto numérico como el siguiente ejemplo, la función NOW() devuelve la fecha y hora actuales como un número.

La función NOW como valor por defecto para una columna.

Puede utilizar la función NOW() para proporcionar un valor predeterminado para una columna DATETIME o TIMESTAMP. Cuando omite el valor de fecha u hora en las declaraciones INSERT y UPDATE, MySQL inserta y actualiza la fecha y hora actuales en la columna cuyo valor predeterminado es NOW(). Esto también se puede conseguir con la instrucción CURRENT_TIMESTAMP.

Veamos el siguiente ejemplo, donde se debe realizar el registro de ingreso y salida de estudiantes en una sesión de clase:

DROP TABLE IF EXISTS registro_clase;
CREATE TABLE registro_clase(
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre_estudiante VARCHAR(255) NOT NULL,
    ingreso DATETIME NOT NULL DEFAULT NOW(), -- o CURRENT_TIMESTAMP
    observaciones TEXT NULL, 
    salida TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
);

Ahora insertemos un registro de un estudiante que ingresa.

INSERT INTO registro_clase(nombre_estudiante,observaciones) 
VALUES('Carlos David', 'Entra sin uniforme.');

Ahora generemos la salida del estudiante.

UPDATE registro_clase 
SET observaciones = CONCAT(observaciones,'\nSale con nota en el cuaderno disciplinario.') 
WHERE id = 1;

Observe que en ninguno de los scripts SQL (INSERT y UPDATE) se insertan o modifican las fechas, si no que MySQL lo hace automáticamente de acuerdo a lo declarado en la definición de la estructura.

DATE

Extrae la fecha de la expresión de fecha o fecha y hora. Veamos algunos ejemplos:

SELECT DATE('2003-12-31 01:02:03');

SELECT DATE(NOW());

DATEDIFF

La función DATEDIFF calcula el número de días entre dos valores DATE, DATETIME o TIMESTAMP.

La sintaxis de esta función es la siguiente:

DATEDIFF(date_expression_1,date_expression_2);

La función DATEDIFF acepta dos argumentos que pueden ser cualquier valor válido de fecha o fecha y hora. Si pasa valores DATETIME o TIMESTAMP, la función DATEDIFF solo toma las partes de fecha para el cálculo e ignora las partes de tiempo.

Veamos algunos ejemplos:

SELECT DATEDIFF('2020-09-18','2020-09-08');

SELECT DATEDIFF('2020-09-08','2020-09-18');

DATE_ADD

La función DATE_ADD agrega un intervalo a un valor DATE o DATETIME. A continuación, se ilustra la sintaxis de la función DATE_ADD.

DATE_ADD(start_date, INTERVAL expr unit);

La función DATE_ADD toma dos argumentos:

start_date es el valor, en el cual comienza DATE o DATETIME.
INTERVAL expr unit es un valor de intervalo que se agregará al valor de la fecha de inicio.

Veamos algunos ejemplos:

SELECT  DATE_ADD('1999-12-31 20:59:59', INTERVAL 1 SECOND) resultado;

SELECT  DATE_ADD('2020-12-31 00:00:01', INTERVAL 1 DAY) resultado;

SELECT DATE_ADD(NOW(), INTERVAL '-1 5' DAY_HOUR) resultado;

DATE_SUB

La función DATE_SUB() resta un valor de tiempo (o un intervalo) de un valor DATE o DATETIME. Lo siguiente ilustra la función DATE_SUB():

DATE_SUB(start_date,INTERVAL expr unit)

start_date es el valor, en el cual comienza DATE o DATETIME.
INTERVAL expr unit es un valor de intervalo que se restará al valor de la fecha de inicio.

Veamos algunos ejemplos:

SELECT DATE_SUB('2017-07-04',INTERVAL 3 HOUR) resultado;

SELECT DATE_SUB(NOW(),INTERVAL 2 DAY) resultado;

SELECT DATE_SUB('2027-07-03',INTERVAL -2 DAY) resultado;

En el caso que el intervalo sea negativo, la función DATE_SUB se comporta como DATE_ADD.

DATE_FORMAT

Esta función permite formatear una fecha que se envíe en un formato y pueda ser visualizada en otro de los formatos permitidos, vemos su sintaxis:

DATE_FORMAT(date,format)

La función DATE_FORMAT acepta dos argumentos:

date: es un valor de fecha válido que desea formatear.
format: es una cadena de formato que consta de especificadores predefinidos. Cada especificador está precedido por un carácter de porcentaje (%). Veamos esta tabla de especificadores:

Especificador

Sentido

Nombre del día de la semana abreviado de tres caracteres, p. Ej., lunes, martes, miércoles, etc.

Nombre del mes abreviado de tres caracteres, por ejemplo, enero, febrero, marzo, etc.

Mes en numérico, p. Ej., 1, 2, 3… 12

Día del mes con sufijo en inglés, por ejemplo, 0, 1, 2, etc.

Día del mes con cero a la izquierda si es 1 número, por ejemplo, 00, 01,02,… 31

Día del mes sin cero a la izquierda, p. Ej., 1,2,… 31

Microsegundos en el rango de 000000..999999

Hora en formato de 24 horas con cero a la izquierda, por ejemplo, 00..23

Hora en formato de 12 horas con cero a la izquierda, por ejemplo, 01, 02… 12

Igual que% h

Minutos con cero a la izquierda, por ejemplo, 00, 01,… 59

Día del año con cero a la izquierda, p. Ej., 001,002,… 366

Hora en formato de 24 horas sin cero a la izquierda, por ejemplo, 0,1,2… 23

Hora en formato de 12 horas sin cero a la izquierda, p. Ej., 1,2… 12

Nombre del mes completo, por ejemplo, enero, febrero,… diciembre

Nombre del mes con cero a la izquierda, por ejemplo, 00,01,02,… 12

AM o PM, dependiendo de otros especificadores de tiempo

Hora en formato de 12 horas hh: mm: ss AM o PM

Segundos con cero a la izquierda 00,01,… 59

Igual que% S

Hora en formato de 24 horas hh: mm: ss

Número de semana con cero a la izquierda cuando el primer día de la semana es domingo, por ejemplo, 00,01,02… 53

Número de semana con cero a la izquierda cuando el primer día de la semana es el lunes, por ejemplo, 00,01,02… 53

Lo mismo que tu; se usa con% X

Lo mismo que tu; se usa con% x

Nombre completo del día de la semana, p. Ej., Domingo, lunes,…, sábado

Día de la semana en número (0 = domingo, 1 = lunes, etc.)

Año de la semana en cuatro dígitos donde el primer día de la semana es el domingo; usado a menudo con% V

Año de la semana, donde el primer día de la semana es el lunes, cuatro dígitos; usado con% v

Año de cuatro dígitos, por ejemplo, 2000 y 2001.

Año de dos dígitos, por ejemplo, 10,11 y 12.

Agregar carácter de porcentaje (%) a la salida

Veamos algunos ejemplos:

Si desea lograr que los meses y días de la semana aparezcan en español, tenga en cuenta este ejemplo:

SET lc_time_names = 'es_ES';
SELECT DATE_FORMAT("2010-01-01",'%d - %b - %Y') AS fecha;

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS fecha;

Quiero saber más …

Ejercicios

Ejercicio 1

Tome como referencia la base de datos de la empresa de confecciones (tratada en este módulo). El supervisor de recurso requiere que se adicione una nueva tabla, que permita saber el estado de los empleados, los estados que se manejan son: Active, Inactive, Suspended, Disabled y Fired.

Cree una tabla de estados para los empleados.
Adicione la llave foránea (tenga en cuenta las restricciones) en la tabla de empleado, donde el valor por defecto es Active.
Entregue la evidencia de los scripts ejecutados y pantallazo de las tablas creadas en la base de datos.

Ejercicio 2

Genere los modelos relaciones mediante PhpMyAdmin de dos bases de datos de las cuales a trabajados en los módulos de base de datos (No se permite el modelo de la empresa de confecciones tratada en este módulo).

Ejercicio 3

Diseñe 2 consultar con CASE – WHEN y 2 consultas con IF, donde demuestre el uso de estas dos estructuras de control de flujo y sobre estas 4 consultas demuestre el uso de los ALIAS. Aplique dichas consultas en sobre tablas del PPI.

Ejercicio 4

Se tienen las siguientes cadenas de texto

"Lavan esa base naval"
“LAVAN ESAB ASE NAVAL”

Diseñe una consulta que indique si estas dos palabras son palíndromas o no son palíndromas.

Ejercicio 5

Desarrolle una consulta que me indique si un número (el número va dentro de la consulta, no necesita ser una variable) es palíndromo o no.

Ejercicio 6

En la base de datos de la empresa de confecciones (tratada en este módulo), existe la tabla de pedido; se requiere saber cuántos días han transcurrido entre la fecha límite de entrega (fechaLimitEntrega) y la fecha de envío (fechaEnvio), asegure que el pedido se encuentre en estado Shipped. Diseñe una consulta que permita obtener este dato.

Ejercicio 7

La tabla abordada en el ejercicio anterior (pedido), se requiere ver las fechas en los siguientes formatos.

fechaOrden

'%Y-%m-%d'

fechaLimitEntrega

'%a %D %b %Y'

fechaEnvio

'%W %D %M %Y'

Ejercicio 8

Investigue una función diferente a cada uno de los temas tratados en este módulo:

Función para para manipular cadenas.
Función matemática.
Función para manipulación de fechas.

PreviousFunciones y operaciones en MySQL NextRelación entre tablas y manejo avanzado de los datos mediante algunas cláusulas SQL avanzadas

Last updated 4 years ago

Was this helpful?