• Web oficial de MySQL
• Web oficial de MariaDB

Se utilizan para las tablas.

Los más utilizados:

• MyISAM
• InnoDB

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

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.

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.

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.

Permite recuperar información de una base de datos.

SELECT campo1, campo2, campo3
FROM tabla

SELECT *
FROM tabla

Filtra los resultados obtenidos por SELECT:

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

Operadores que se pueden usar con WHERE:

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'

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

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

1. FROM y JOIN (con todas las condiciones del ON)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. 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

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

Obtiene el valor mínimo de un campo.

Obtiene el valor máximo de un campo.

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

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

SELECT CURRENT_DATE 

Devuelve la hora actual:

SELECT CURRENT_TIME 

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

SELECT CURRENT_TIMESTAMP 

Calcula la diferencia en días entre fechas:

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

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)

Añade días a una fecha.

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

Devuelve la diferencia entre fechas

SELECT DATEDIFF(NOW(), fecha)
FROM tabla

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

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.

Descifra datos utilizando el algoritmo AES.

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

Devuelve el número de caracteres de un campo

SELECT LENGTH(campo)
FROM tabla

Une datos

SELECT CONCAT(campo1, campo2, campo3)

Une datos indicando un separador

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

Convierte entre tipos de datos.

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

Hemos convertido un texto en un tipo de dato DATE.

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.

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);

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.

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.

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.

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

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.

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.

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á.

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.

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

Para poder restaurar a partir de una copia de seguridad:

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

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

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.

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);

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.

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);

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;

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

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.