====== 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. 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 [[http://www.tutorialspoint.com/mysql/mysql-regexps.htm|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; 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 -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 ===== * [[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'': 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.