MySQL

Motores

Se utilizan para las tablas.

Los más utilizados:

MyISAM
InnoDB

Tipos de datos

Números enteros

Tipo	Tamaño	Rango
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT	4 bytes
BIGINT	8 bytes
BIT	1 byte (1 bit)

Números reales

Tipo	Tamaño	Rango
FLOAT
DOUBLE
DECIMAL

DECIMAL indica cuántos dígitos tendrá en total y cuántos de esos corresponderá a la parte decimal.

Texto

Tipo	Tamaño	Rango
CHAR
VARCHAR
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT	4 GB
JSON

CHAR se utiliza cuando se sabe el tamaño que va a tener el dato. Esto hace que este tipo de dato sea más eficiente que VARCHAR.

Binario

Tipo	Tamaño	Rango
BINARY
VARBINARY
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

Temporal

Tipo	Tamaño	Rango
DATE
TIME
YEAR
DATETIME
TIMESTAMP

Los tipos de fecha (DATE o DATETIME) en MySQL/MariaDB se almacenan como AAAA-MM-DD. Esto facilita las ordenaciones.

El tipo de dato TIMESTAMP es similar a DATETIME.

Otros

Tipo	Tamaño	Rango
ENUM
SET

Los campos de tipo ENUM son útiles si solo permitimos ciertos valores en un campo. Podemos crear una lista de valores posibles (letras o números). Si intentamos introducir otro valor, dará error.

SET es como ENUM, pero permite valores vacíos.

Comandos

SELECT

Permite recuperar información de una base de datos.

SELECT campo1, campo2, campo3
FROM tabla

Si queremos recuperar todos los campos de una tabla, podemos usar el asterisco *:

SELECT *
FROM tabla

JOIN

Cláusulas

WHERE

Filtra los resultados obtenidos por SELECT:

SELECT campo1, campo2, campo3
FROM tabla
WHERE campo1 > 100

Operadores que se pueden usar con WHERE:

Operador	Descripción
`=`	Igualdad
`!=`	Distinto
`>`	Mayor que
`<`	Menor que
`>=`	Mayor o igual que
`<=`	Menor o igual que
`LIKE`	Compara un patrón

Si vamos a usar LIKE con múltiples valores, es mejor usar el operador REGEXP y una expresión regular. Por ejemplo:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

Sería:

WHERE interests REGEXP 'sports|pub'

GROUP BY

HAVING

LIMIT

Subconsultas

Sentencias SELECT dentro de otras sentencias.

SELECT campo1,
       campo2,
       (
           SELECT MAX(fecha)
           FROM tabla2
           WHERE campo4 = 123
       ) AS ultima_fecha
FROM tabla 1

Esto se usa mucho para obtener datos calculados en una única fila.

Otro ejemplo donde se usa una subconsulta, pero en la cláusula WHERE:

SELECT campo1,
       campo2
FROM tabla1
WHERE campo1 > 0
  AND (
          SELECT permiso 
          FROM permisos
      ) = 1

Orden de procesamiento de consultas

Al lanzar una consulta, el motor de base de datos la procesa en el siguiente orden:

FROM y JOIN (con todas las condiciones del ON)
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT / OFFSET

Es necesario tenerlo en cuenta para evitar errores a la hora de construir consultas. Por ejemplo, no podemos usar WHERE para filtrar información que proceda de un GROUP BY sino que debemos usar HAVING

Base de datos

Creación

Tablas

Creación

CREATE TABLE mitabla (
    campo1 INT NOT NULL AUTO_INCREMENT,
    campo2 VARCHAR(50) NULL DEFAULT NULL,
    campo3 VARCHAR(150) NOT NULL DEFAULT '',
    PRIMARY KEY (campo1)
)

Funciones

MIN

Obtiene el valor mínimo de un campo.

MAX

Obtiene el valor máximo de un campo.

SUBSTR

Coge una porción de un campo de texto.

Por ejemplo, para coger los 5 primeros caracteres del campo nombre:

SELECT SUBSTR(nombre, 1, 5)
FROM tabla

CURRENT_DATE

Devuelve la fecha actual en formato YYYY-MM-DD:

SELECT CURRENT_DATE

CURRENT_TIME

Devuelve la hora actual:

SELECT CURRENT_TIME

CURRENT_TIMESTAMP

Devuelve la fecha y hora actuales en formato YYYY-MM-DD HH:MM:SS:

SELECT CURRENT_TIMESTAMP

DATEDIFF

Calcula la diferencia en días entre fechas:

SELECT DATEDIFF('2021-08-01', '2021-01-01')

DAYOFWEEK

Devuelve el número de día de la semana de una fecha

SELECT DAYOFWEEK(CURRENT_DATE)

Los días empiezan a contar desde el domingo (1) hasta el sábado (7)

ADDDATE

Añade días a una fecha.

SELECT fecha, 
       ADDDATE(fecha, 30) AS fecha_limite
FROM tabla

DATEDIFF

Devuelve la diferencia entre fechas

SELECT DATEDIFF(NOW(), fecha)
FROM tabla

DATE_FORMAT

Dar un formato a una fecha

SELECT DATE_FORMAT(fecha, "%d-%m-%Y") as nueva_fecha
FROM tabla

Si solo queremos traer el año:

SELECT DATE_FORMAT(fecha, "%Y") as ano
FROM tabla

AES_ENCRYPT

Cifra datos utilizando el algoritmo AES.

SELECT AES_ENCRYPT(campo, 'palabraclaveparaencriptar') AS cifrado
FROM tabla

Para poder descifrar esta información, necesitamos saber la palabra que usamos para cifrar y la función AES_DECRYPT.

AES_DECRYPT

Descifra datos utilizando el algoritmo AES.

SELECT AES_DECRYPT(campocifrado, 'palabra_clave_usada_en_el_cifrado') as descifrado
FROM tabla

LENGH

Devuelve el número de caracteres de un campo

SELECT LENGTH(campo)
FROM tabla

CONCAT

Une datos

SELECT CONCAT(campo1, campo2, campo3)

CONCAT_WS

Une datos indicando un separador

SELECT CONCAT(" ", campo1, campo2, campo3)

CONVERT

Convierte entre tipos de datos.

SELECT CONVERT("2021-01-01", DATE)

Hemos convertido un texto en un tipo de dato DATE.

Cursores

Los cursores son tablas en memoria que crea el motor que nos permitirá recorrer sus registros y procesar sus datos.

Estas tablas son creadas a partir de sentencias SQL.

Se suelen utilizar en los procedimientos almacenados.

Procedimientos almacenados

Trozos de código que se graban en el motor de base de datos. Se suelen crear porque son más rápidas y también porque se ocultan las consultas, es decir, que no se tiene que dar información sobre la base de datos y sus tablas.

CREATE PROCEDURE borrar_alumno (
    _mes TINYINT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DELETE FROM alumnos WHERE id_alumno = _id_alumno;
END

Para ejecutarlo:

CALL borrar_alumno(4);

Podemos trabajar con variables:

CREATE PROCEDURE maxima_venta()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE valor_max DECIMAL(10,2);
 
    SET valor_max = 0;
 
    -- Buscamos el mayor valor y lo guardamos en la variable anterior
    SELECT MAX(ventas_total) INTO valor_max FROM ventas;
 
    IF valor_max > 1000 THEN
        SELECT "Se superaron los 1000 €" AS mensaje;
    ELSE
        SELECT "Aún no se superaron los 1000 €" AS mensaje;
    END IF;
END

Ejemplo con cursores:

CREATE FUNCTION ganancia_ventas(
    _ano SMALLINT,
    _mes TINYINT
)
RETURNS
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    -- Declaración de variables
    DECLARE ganancia DECIMAL(10,2);
    DECLARE _precio DECIMAL(10,2);
    DECLARE _cantidad MEDIUMINT;
    DECLARE _coste DECIMAL(10,2);
    DECLARE final TINYINT DEFAULT 0;
 
    -- Declaración del cursor:
    DECLARE CUR_V CURSOR FOR
    -- Hacemos la consulta que va a crear el cursor que luego
    -- vamos a usar:
        SELECT vd_precio,
               vd_coste,
               vd_cantidad
        FROM ventas_detalle
        JOIN ventas ON vd_id_ventas = id_ventas
        WHERE vd_coste > 0 
          AND YEAR(ventas_fecha) = _ano 
          AND MONTH(ventas_fecha) = _mes;
 
    -- Manejador de fin de archivo. Cuando hayamos llegado al
    -- último registro, cambiaremos el valor de una variable
    -- que usaremos para saber si hemos llegado al final
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET final = 1;
 
    SET ganancia = 0;
 
    -- Abrimos el cursor para poder utilizar el cursor
    OPEN CUR_V ;
 
    -- Recorremos el cursor y lo procesamos:
    WHILE final = 0 DO
        -- Nos posicionamos en el próximo registro del cursor
        -- y guardamos el resultado en las variables creadas
        -- en el orden que usásemos en la sentencia SELECT para
        -- crear el cursor
        FETCH CUR_V  INTO _precio, _coste, _cantidad;
        -- Si todavía hay registros por recorrer,
        -- vamos acumulando valores en la variable "ganancia"
        IF final = 0 THEN
            SET ganancia = ganancia + ((_precio - _coste) * _cantidad);
        END IF;
    END WHILE;
 
    CLOSE CUR_V ;
 
    -- Devolvemos el dato de ganancia, ya que este procedimiento
    -- almacenado es de tipo FUNCTION y debe devolver un valor
    RETURN ganancia;
END

Para usarla, como se trata de una función, hay que ejecutarla dentro de una sentencia SELECT:

SELECT ganancia_ventas(2021, 1);

Manejo de errores

CREATE PROCEDURE insertar_alumno(
    _id_alumno INT,
    _nombre_alumno VARCHAR(50)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
 
BEGIN
    DECLARE CONTINUE HANDLER FOR 1062 SELECT "Clave duplicada";
    INSERT INTO alumnos (id_alumno, nombre_alumno) VALUES (_id_alumno, _nombre_alumno);
END

Si obtenemos un error 1062 del motor de base de datos, mostraremos un mensaje.

Triggers

Los triggers o disparadores son objetos que se crean en las tablas y ejecutan ciertas operaciones cuando se han ejecutado algunas de las operaciones INSERT, UPDATE o DELETE.

INSERT

Si queremos controlar algo cuando se quiere realizar una inserción en una tabla.

Se queda guardado en el motor de base de datos.

Si queremos que se active el disparador antes de hacer la inserción, usaremos BEFORE, si es después, AFTER.

Si queremos que los valores del campo nombre_alumno siempre se inserten en mayúsculas:

CREATE TRIGGER `nombre_mayusculas` BEFORE INSERT ON `alumnos` FOR EACH ROW 
 
BEGIN
    SET NEW.nombre_alumno = UCASE(NEW.nombre_alumno);
END

Una vez guardado el trigger, si ejecutamos una sentencia de inserción:

INSERT INTO alumnos (id_alumno, nombre_alumno)
VALUES (4, 'pepito');

En la tabla de alumnos veremos PEPITO.

UPDATE

Si queremos controlar algo cuando se quiere realizar una modificación en una tabla.

Por ejemplo, si queremos mantener un histórico con los cambios de precio de unos productos y qué usuario de base de datos lo ha hecho:

CREATE TRIGGER `productos_after_update` AFTER UPDATE ON `productos` FOR EACH ROW 
 
BEGIN
    IF NEW.precio_producto <> OLD.precio_producto THEN
        INSERT INTO productos_historial (
            id_producto_hist,
            precio_anterior_hist,
            precio_nuevo_hist, 
            usuario_hist
        ) VALUES (
            NEW.id_producto,
            OLD.precio_producto,
            NEW.precio_producto,
            CURRENT_USER)
        );
    END IF;
END

NEW hace referencia al nuevo valor modificado (insertado o eliminado) y OLD al valor que tenía antes de realizar la modificación (inserción o eliminación).

Vistas

Sentencias SELECT que quedan grabadas en el motor de bases de datos para usar cuando se quiera. Son como tablas virtuales.

CREATE VIEW lista_productos AS
SELECT campo1, 
       campo2
WHERE campo1 > 100 
  AND campo1 < 1000

Ventajas de las vistas:

Privacidad de la información: podemos establecer acceso solo a la vista y así no poder ver la tabla original con todas sus filas y columnas
Optimización del rendimiento de base de datos: las consultas sobre vistas que proceden de consultas complejas, son más rápidas.

Inconvenientes:

No aceptan parámetros. Siempre se mostrará la misma información.
Dependen de los orígenes a partir de los cuales se crearon la vista. Si cambiamos algún campo o tabla que use esa vista, tendremos que modificar la vista de acuerdo a esos cambios.

Transacciones

Las transacciones son un conjunto de órdenes que se ejecutan formando una unidad de trabajo, es decir, en forma indivisible o atómica.

Las transacciones tienen un comportamiento del tipo todo o nada, es decir, o se realiza completamente o no debe tener ningún efecto.

START TRANSACTION;
 
<OPERACIONES>
 
COMMIT TRANSACTION;

Las operaciones de la transacción solo se harán efectivas si se llega al final (COMMIT) sin ningún error.

Si queremos revertir los cambios, utilizaríamos la sentencia ROLLBACK.

Las transacciones deben cumplir cuatro propiedades, denominadas ACID:

Atomicidad (Atomicity): es la propiedad que asegura que la operación se ha realizado o no, y por lo tanto ante un fallo del sistema no puede quedar a medias.
Consistencia (Consistency): es la propiedad que asegura que solo se empieza aquello que se puede acabar. Por lo tanto, se ejecutan aquellas operaciones que no van a romper la reglas y directrices de integridad de la base de datos.
Aislamiento (Isolation): es la propiedad que asegura que una operación no puede afectar a otras. Esto asegura que la realización de dos transacciones sobre la misma información nunca generará ningún tipo de error.
Durabilidad (Durability): es la propiedad que asegura que una vez realizada la operación, esta persistirá y no se podrá deshacer aunque falle el sistema.

Eventos

Tareas que podemos programar para que se realicen en determinados momentos.

Por ejemplo, si queremos que una vez al día desde el 25/05/2021 hasta el 25/07/2021 se haga un borrado de agentes inactivos:

CREATE EVENT `depurar_tabla_agentes`
  ON SCHEDULE EVERY 1 DAY STARTS '2021-05-25 00:00:00' ENDS '2021-07-01 00:00:00'
  ON COMPLETION NOT PRESERVE
DO BEGIN
    DELETE FROM tabla_agentes
    WHERE activo = 0;
END

ON COMPLETION NOT PRESERVE: cuando el evento termine, el motor de base de datos lo eliminará.

Explain

Si queremos saber el coste en rendimiento de las consultas, podemos precederlas de la sentencia EXPLAIN:

EXPLAIN
SELECT *
FROM tabla1 t1
INNER JOIN tabla2 t2 ON t1.id = t2.id_t1

Nos arrojará resultados sobre el plan de ejecución (explicación de cómo el motor de base de datos ejecutaría la consulta) y podemos saber si realizando ciertos cambios (añadir índices en campos que se suelen utilizar para filtrar búsquedas, por ejemplo), podemos ganar en optimización.

EXPLAIN funciona con sentencias SELECT, DELETE, INSERT y UPDATE.

Copias de seguridad

Exportar

mysqldump -u pepito -p -h localhost --databases base_dato1 base_dato2 > /ruta/backup.sql

-u: usuario de la base de datos
-p: contraseña del usuario de la base de datos. Si no se la indicamos por línea de comandos, nos la preguntará al ejecutar mysqldump
-h: nombre de host (o dirección IP) del servidor de base de datos

Hay que tener en cuenta que de la anterior manera, mysqldump no graba los disparadores ni los procedimientos almacenados. Si queremos que vuelque disparadores, procedimientos almacenados y eventos:

mysqldump -u pepito -p -h localhost --databases base_dato1 base_dato2 --routines --triggers --events > /ruta/backup.sql

Si solo queremos hacer un volcado de la estructura de la base de datos, sin sus datos, lo logramos con la opción --no-data:

mysqldump -u pepito -p -h localhost --databases base_dato1 --no-data > /ruta/backup.sql

Restaurar

Para poder restaurar a partir de una copia de seguridad:

mysql -u pepito -p -h localhost < /ruta/backup.sql

Para evitar desastres, es una buena práctica editar el fichero con el backup y se cambia el nombre de la base de datos que va a crear.

Si en el fichero .sql no se selecciona la base de datos, podemos indicar la base de datos que va a recibir la restauración de la siguiente manera:

mysql -u pepito -p <NOMBRE_BASE_DATOS> -h localhost < /ruta/backup.sql

Importar CSV

Dentro de una sesión MySQL, elegimos la base de datos donde se realizará la importación y ejecutamos la siguiente instrucción:

LOAD DATA LOCAL INFILE '/home/pepito/ficheros/database.csv' 
INTO TABLE mi_tabla 
FIELDS TERMINATED BY ';' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n' 
IGNORE 1 rows 
(campo1, campo2, campo3, campo4);

En la primera línea indicamos que vamos a cargar un fichero que está en el sistema de ficheros local. Si fuese un sistema Windows, sería, por ejemplo, C:\\ficheros\\database.csv
La segunda línea indica la tabla en la que se van a importar los datos.
La tercera línea especifica el separador de campos.
La cuarta línea indica el caracter usado para encerrar los campos.
La quintalínea indica el separador de líneas. En ficheros creados en Windows sería \r\n
La sexta línea ignora la primera línea del fichero CSV por si contiene el nombre de las columnas
La última línea indica el orden y el nombre de los campos de la tabla donde se van a insertar los campos del fichero CSV.

Ignorar duplicados

En caso de que quisiéramos ignorar duplicados (si hay coincidencias en campos que deban ser únicos), añadimos IGNORE:

LOAD DATA LOCAL INFILE '/home/pepito/ficheros/database.csv' 
IGNORE INTO TABLE mi_tabla 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
IGNORE 1 rows 
(campo1, campo2, campo3, campo4);

Seleccionar campos

Si queremos insertar solo determinados campos:

LOAD DATA LOCAL INFILE '/home/pepito/ficheros/database.csv' 
INTO TABLE mi_tabla 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
IGNORE 1 rows 
(@col1, @col2, @col3, @col4) SET campo3=@col3, campo4=@col4;

Solo insertaría los campos que ocupan las columnas 3 y 4.

Insertar null

Si algún campo del CSV es vacío, podemos indicar que se inserte NULL mediante la función NULLIF:

LOAD DATA LOCAL INFILE '/home/pepito/ficheros/database.csv' 
INTO TABLE mi_tabla 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
IGNORE 1 rows 
(@col1, @col2, @col3)
SET campo1=NULLIF(@col1, ''), campo2=@col2, campo3=NULLIF(@col3);

Formatear fecha

Si el dato de fecha viene en un formato distinto al esperado en MySQL (%Y-%m-%d %H:%i:%s), lo podemos fomatear con la función STR_TO_DATE:

LOAD DATA LOCAL INFILE '/home/pepito/ficheros/database.csv' 
INTO TABLE mi_tabla 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
IGNORE 1 rows 
(@col1, @col2, @col3)
SET campo1=STR_TO_DATE(@col1, '%d/%m/%Y %H:%i:%s'), campo2=@col2, campo3=@col3;

Bases de datos de ejemplo

Sakila: base de datos de alquiler de películas.

Reparar tablas

Si tenemos el error “Table is marked as crashed and should be repaired”, podemos solucionarlo con el programa mysqlcheck:

mysqlcheck -uUSUARIO -p --databases NOMBRE_BASE_DATOS --auto-repair

USUARIO: usuario con permisos sobre la base de datos
NOMBRE_BASE_DATOS: base de datos que contiene las tablas corruptas.

Se hará una revisión de las tablas de dicha base de datos y se intentará reparar automáticamente.

Tabla de Contenidos

MySQL

Motores

Tipos de datos

Números enteros

Números reales

Texto

Binario

Temporal

Otros

Comandos

SELECT

JOIN

Cláusulas

WHERE

GROUP BY

HAVING

LIMIT

Subconsultas

Orden de procesamiento de consultas

Base de datos

Creación

Tablas

Creación

Funciones

MIN

MAX

SUBSTR

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DATEDIFF

DAYOFWEEK

ADDDATE

DATEDIFF

DATE_FORMAT

AES_ENCRYPT

AES_DECRYPT

LENGH

CONCAT

CONCAT_WS

CONVERT

Cursores

Procedimientos almacenados

Manejo de errores

Triggers

INSERT

UPDATE

Vistas

Transacciones

Eventos

Explain

Copias de seguridad

Exportar

Restaurar

Importar CSV

Ignorar duplicados

Seleccionar campos

Insertar null

Formatear fecha

Bases de datos de ejemplo

Reparar tablas