La wiki de TempWin

Esta página es de solo lectura. Puedes ver la fuente pero no puedes cambiarla. Pregunta a tu administrador si crees que esto es incorrecto.
====== MySQL ======

  * [[https://www.mysql.com/|Web oficial de MySQL]]
  * [[https://mariadb.org/|Web oficial de MariaDB]]
===== 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.

<code mysql>
SELECT campo1, campo2, campo3
FROM tabla
</code>

<WRAP center round tip 60%>
Si queremos recuperar todos los campos de una tabla, podemos usar el asterisco ''*'': 
<code mysql>
SELECT *
FROM tabla
</code>
</WRAP>



==== JOIN ====

===== Cláusulas =====

==== WHERE ====

Filtra los resultados obtenidos por ''SELECT'':

<code mysql>
SELECT campo1, campo2, campo3
FROM tabla
WHERE campo1 > 100
</code>

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 [[http://www.tutorialspoint.com/mysql/mysql-regexps.htm|expresión regular]]. Por ejemplo:

<code mysql>
WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'
</code>

Sería:

<code mysql>
WHERE interests REGEXP 'sports|pub'
</code>



==== GROUP BY ====

==== HAVING ====

==== LIMIT ====

===== Subconsultas =====

Sentencias ''SELECT'' dentro de otras sentencias.

<code mysql>
SELECT campo1,
       campo2,
       (
           SELECT MAX(fecha)
           FROM tabla2
           WHERE campo4 = 123
       ) AS ultima_fecha
FROM tabla 1
</code>

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'':

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


===== 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 ====

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

===== 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'':

<code sql>
SELECT SUBSTR(nombre, 1, 5)
FROM tabla
</code>

==== CURRENT_DATE ====

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

<code mysql>
SELECT CURRENT_DATE 
</code>

==== CURRENT_TIME ====

Devuelve la hora actual:

<code mysql>
SELECT CURRENT_TIME 
</code>

==== CURRENT_TIMESTAMP ====

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

<code mysql>
SELECT CURRENT_TIMESTAMP 
</code>

==== DATEDIFF ====

Calcula la diferencia en días entre fechas:

<code mysql>
SELECT DATEDIFF('2021-08-01', '2021-01-01')
</code>

==== DAYOFWEEK ====

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

<code mysql>
SELECT DAYOFWEEK(CURRENT_DATE)
</code>

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

==== ADDDATE ====

Añade días a una fecha.

<code sql>
SELECT fecha, 
       ADDDATE(fecha, 30) as fecha_limite
FROM tabla
</code>

==== DATEDIFF ====

Devuelve la diferencia entre fechas

<code sql>
SELECT DATEDIFF(NOW(), fecha)
FROM tabla
</code>

==== DATE_FORMAT ====

Dar un formato a una fecha

<code mysql>
SELECT DATE_FORMAT(fecha, "%d-%m-%Y") as nueva_fecha
FROM tabla
</code>

Si solo queremos traer el año:

<code mysql>
SELECT DATE_FORMAT(fecha, "%Y") as ano
FROM tabla
</code>
==== AES_ENCRYPT ====

Cifra datos utilizando el algoritmo AES.

<code sql>
SELECT AES_ENCRYPT(campo, 'palabraclaveparaencriptar') as cifrado
FROM tabla
</code>

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.

<code mysql>
SELECT AES_DECRYPT(campocifrado, 'palabra_clave_usada_en_el_cifrado') as descifrado
FROM tabla
</code>

==== LENGH ====

Devuelve el número de caracteres de un campo

<code mysql>
SELECT LENGTH(campo)
FROM tabla
</code>

==== CONCAT ====

Une datos

<code mysql>
SELECT CONCAT(campo1, campo2, campo3)
</code>

==== CONCAT_WS ====

Une datos indicando un separador

<code mysql>
SELECT CONCAT(" ", campo1, campo2, campo3)
</code>

==== CONVERT ====

Convierte entre tipos de datos.

<code mysql>
SELECT CONVERT("2021-01-01", DATE)
</code>

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.

<code mysql>
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
</code>

Para ejecutarlo:

<code mysql>
CALL borrar_alumno(4);
</code>

Podemos trabajar con variables:

<code mysql>
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
</code>

Ejemplo con cursores:

<code mysql>
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
</code>

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

<code mysql>
SELECT ganancia_ventas(2021, 1);
</code>
==== Manejo de errores ====

<code mysql>
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
</code>

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:

<code mysql>
CREATE TRIGGER `nombre_mayusculas` BEFORE INSERT ON `alumnos` FOR EACH ROW 

BEGIN
    SET NEW.nombre_alumno = UCASE(NEW.nombre_alumno);
END
</code>

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

<code mysql>
INSERT INTO alumnos (id_alumno, nombre_alumno)
VALUES (4, 'pepito');
</code>

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:

<code mysql>
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
</code>

<WRAP center round info 60%>
''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).
</WRAP>

===== Vistas =====

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

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

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.

<code mysql>
START TRANSACTION;

<OPERACIONES>

COMMIT TRANSACTION;
</code>

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:

<code mysql>
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
</code>

  * ''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'':

<code sql>
EXPLAIN
SELECT *
FROM tabla1 t1
INNER JOIN tabla2 t2 ON t1.id = t2.id_t1
</code>

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.

<WRAP center round info 60%>
''EXPLAIN'' funciona con sentencias ''SELECT'', ''DELETE'', ''INSERT'' y ''UPDATE''.
</WRAP>

===== Copias de seguridad =====

==== Exportar ====

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

  * ''-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:

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

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'':

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

==== Restaurar ====

Para poder restaurar a partir de una copia de seguridad:

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

<WRAP center round tip 60%>
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.
</WRAP>

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:

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

===== 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:

<code mysql>
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);
</code>

  * 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'':

<code mysql>
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);
</code>

==== Seleccionar campos ====

Si queremos insertar solo determinados campos:

<code mysql>
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;
</code>

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'':

<code mysql>
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);
</code>

==== 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'':

<code mysql>
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;
</code>
===== Bases de datos de ejemplo =====

  * [[https://dev.mysql.com/doc/sakila/en/|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'':

<code>
mysqlcheck -uUSUARIO -p --databases NOMBRE_BASE_DATOS --auto-repair
</code>

  * ''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.