MySQL: Formateando fechas con DATE_FORMAT()

La función DATE_FORMAT() de MySQL, que fue incorporada en la versió 4.0 del conocido SGBD, nos permite dentro de un select definir el formato de salida de una fecha. La función recibe dos parámetros: una fecha o campo de fecha y una cadena de texto con el formato.

Los siguientes valores pueden ser usados en el formateo de fecha:

%aNombre del día abreviado (Sun a Sat)
%bNombre de mes abreviado (Jan a Dec)
%cMes en formato número (0 a 12)
%DDía del mes en formato numérico seguido de sufijo numeral (1st, 2nd, 3rd, …)
%dDía del mes en formato numérico, forzando dos dígitos  siempre ( de 01 a 31)
%eDía del mes en formato numérico, sin forzar dos dígitos (1 a 31)
%fMicrosegundos (000000 to 999999)
%HHora en formato 24 horas, con dos dígitos
%hHora en formato 12 horas, dos dígitos
%IHora en formato 12 horas, dos dígitos
%iMinutos
%jDía del año (001 a 366)
%kHora en formato 24 horas, sin forzar los dos dígitos
%lHora en formato 12 horas, sin forzar los dos dígitos
%MNombre del mes (January a December)
%mMes en formato numérico, forzando dos dígitos.
%pAM o PM
%rHora en formato 12 horas AM o PM (hh:mm:ss AM/PM)
%SSegundos (00 a 59)
%sSegundos (00 a 59)
%THora en formato 24 horas (hh:mm:ss)
%USemana, tomando el domingo como primer día (00 a 53)
%uSemana, tomando el lunes como primer día (00 to 53)
%VSemana, tomando el domingo como primer día (01 a 53). Usado con %X
%vSemana, tomando el lunes como primer día (01 a 53). Usado con %X
%WNombre del día completo (Sunday to Saturday)
%wNúmero del día en la semana, siendo el domindo 0 y el sábado 6.
%XAño para la semana, tomando el domingo como primer día. Usado con %V
%xAño para la semana, tomando el lunes como primer día. Usado con %V
%YAño en formato de cuatro dígitos
%yAño en formato de dos dígitos

Veamos unos ejemplos si quieres probarlo en casa:

Select DATE_FORMAT("2018-11-20", "%d/%m/%Y"); 
#Pintaría 20/11/2018, el formato europeo
Select DATE_FORMAT("2018-11-20", "%V %X"); 
#Pintaría 46 2018
Select DATE_FORMAT("2018-11-20", "%j-%y"); 
#Pintaría 324-2018
Select DATE_FORMAT("2018-11-20", "%W %d %M"); 
#Pintaría Tuesday 20 November

En el ejemplo le hemos pasado una cadena con una fecha como primer parámetro, pero puedes probar una consulta sobre un campo de una tabla que almacena alguna fecha.

Anuncios

MySQL: Consulta para saber cuántas tablas o vistas hay en una base de datos

El otro día veíamos cómo hacerlo con SQL-Server ¿y con MySQL? Más o menos lo mismo, con el pequeño cambio que implica que MySQL obligue a meter siempre en la consulta el nombre del esquema que vamos a usar, mientra que en SQL-Server cogerá por defecto el que tengamos seleccionado.

Entonces en MySQL la cosa quedaría así:

#Contar Tablas
SELECT COUNT(*) from Information_Schema.Tables where TABLE_TYPE = 'BASE TABLE' and table_schema = 'LaBaseDeDatosQueSea';

#Contar Vistas
SELECT COUNT(*) from Information_Schema.Tables where TABLE_TYPE = 'VIEW' and table_schema = 'LaBaseDeDatosQueSea';

#Contar Tablas y vistas
SELECT COUNT(*) from Information_Schema.Tables where table_schema = 'LaBaseDeDatosQueSea';

SQL: Formas Normales

Recuperados ya del fin de semana de rock and roll vamos a volver al código con algo de SQL teórico: la normalización de datos. El diseño lógico de las tablas y sus relaciones es fundamental para la optimización de una base de datos. Existen cinco reglas de normalización que debemos cumplir para aseverar que nuestra base de datos SQL está normalizada, aunque es cierto que hay quien considera que la cuarta y la quinta son rizar el rizo y que con cumplir la tres primeras generalmente ya tenemos una base de datos normalizada. Estas son las cinco formas normales:

  1. Primera Forma Normal: La primera forma requiere la eliminación de todas las columnas duplicadas de una tabla, la separación en otras tablas de esos datos que se duplicarían y la identificación de cada tabla con una atributo de clave primaria. Por ejemplo, si tenemos una tabla donde registramos ventas no guardamos el nombre y el precio del producto vendido en ella varias veces, sino que tendremos una tabla de productos con esos datos separado de la tabla de ventas, y ambas tendrían claves primarias que identifican a cada fila.
  2. Segunda forma Normal: Implica que se cumpla lo dicho en la primera forma normal y que, además, se creen relaciones entre tablas a través de claves externas. Es decir, la tabla Ventas del ejemplo anterior incluye como Clave Externa un valor único que lo relaciona con la tabla Productos, generalmente su clave primaria.
  3. Tercera Forma Normal: Esta norma implica que se cumplan las dos anteriores y que, además, todas las columnas de un registro deben hacer referencia únicamente a la clave primaria, y además elimina todos los atributos derivados. Volvemos al ejemplo: la tabla Productos nos dará el nombre del producto, su precio, en qué almacén se guarda y su fecha de caducidad. No tendremos, por ejemplo, un registro para decir en qué piso del almacén se guarda, ya que ese dato sería de la tabla Almacén. Tampoco tendríamos una columna con los días que faltan hasta que caduque, ya que ese sería un atributo derivado que podemos calcular con la fecha de caducidad.
  4. Cuarta Forma Normal: Agrega un requisito adicional, que es la eliminación de cualquier dependencia multivaluada en las relaciones. Una tabla con una dependencia multivaluada es una donde la existencia de dos o más relaciones independientes muchos a muchos causa redundancia.
  5. Quinta Forma Normal: Rizando el rizo, vendría a decir que sólo se podrían realizar relaciones entre tablas utilizando claves candidatas, con la idea de reducir la redundancia de datos entre múltiples tablas.

A la hora de normalizar bases de datos hay una frase en inglés que lo resume todo: “The key, the whole key, and nothing but the key.” En la propia web de Microsoft la primera recomendación que dan para mejorar el rendimiento de SQL-Server y tener un diseño eficiente de la base de datos es usar un índice autonumérico como clave primaria de cada tabla, identificando así de forma unívoca cada registro y facilitando la relación entre ellos.

MySQL y MariaDB: Consulta para saber en qué tabla está una columna

El otro día lo vimos en SQL-Server, ayer en Oracle, vamos hoy con MySQL y su fork MariaDB, seguramente los gestores de bases de datos SQL libres más populares de la actualidad ¿Cómo puedo saber a qué tabla pertenece una columna sabiendo sólo el nombre de columna? Como en los casos anteriores, basta una consulta:

SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'Nombre_Columna'
        AND TABLE_SCHEMA='Nombre_BaseDatos';

Ya sabéis, os toca cambiar el nombre de columna y el nombre del esquema por los que correspondan en vuestro caso.

MySQL: Calcular una edad a partir de una fecha.

En fin, seguimos con el cálculo de edades a partir de fechas en diferentes SGBD. Ya hablamos de SQL-Server y de PostgreSQL, vamos ahora a ver cómo iría la cosa en MySQL/MariaDB.

Vamos a asumir que los datos están bien guardados, en una columna del tipo DATETIME. Como en el caso de Postgres, en MySQL dispones de una función que nos ayudará a hacer esto directamente, llamada TIMESTAMPDIFF(). Esta función recibe como argumentos la unidad de tiempo en la que queremos recibir la respuesta y las dos fechas entre las que queremos obtener la diferencia. Veamos el ejemplo para obtener la edad de alguien con esta función, sirviéndonos de una tabla de ejemplo llamada clientes que contendrá una columna FechaNac con la fecha de nacimiento de los mismos:

SELECT TIMESTAMPDIFF(YEAR,FechaNac,CURDATE()) AS edad
     FROM clientes;

Borrar un evento de MySQL

Hoy me preguntaba en la entrada sobre Eventos en MySQL cómo se borran, así que en vez de contestar en los comentarios voy a dejarlo por aquí en una minientrada general. ¿Cómo se borra un evento? Pues con Drop Event, cuya sintaxis es DROP EVENT [IF EXISTS] event_name. En el viejo ejemplo creábamos uno llamado e_ActualizaSaldoDiario, así que si quisiéramos borrarlo nos bastaría con hacer lo siguiente:

DROP EVENT e_ActualizaSaldoDiario

En caso de que el evento que intentamos borrar no existiera (por haberse borrado previamente) nos devolverá un error. Si queremos evitar esto podemos añadir IF EXISTS:

DROP EVENT IF EXISTS e_ActualizaSaldoDiario

Recordad que para borrarlos, igual que para crearlos, tenéis que tener permisos sobre los eventos en el schema.

Seguridad en WordPress ¿cómo cambiar el prefijo de la base de datos?

Si alguna vez has hecho una instalación de WordPress verás que te solicita, para crear el sitio, el nombre de una base de datos, el usuario con el que se conectará a ella, la contraseña de ese usuario y un prefijo. ¿Por qué un prefijo? Simplemente porque así puedes tener varias instalaciones de WordPress (o de otros CMS que usen nombres de tabla muy genéricos tipo “users”, “posts”, etc.) en una misma base de datos.

Si no cambiamos esta configuración por defecto el sistema pondrá el prefijo “wp_” a las tablas. El no poner un prefijo personalizado, en si, constituye un error. Porque dejando el prefijo por defecto estás provocando: a)Que si un atacante logra ver el nombre de una tabla descubra que el CMS que estás utilizando es WordPress y b) Que dicho atacante, entonces, pueda conocer los nombres de las tablas de tu base de datos. La solución es simple, durante la instalación define un prefijo personalizado.

¿Ya lo tienes instalado? Don’t worry, be nécora. No está todo perdido, tienes todavía varias opciones. La más rápida y simple, instalas este plugin que te permitará cambiarla cómodamente desde la interfaz gráfica. ¿La versión más larga y compleja y élite? Pues los siguientes pasos te lo explican:

Bueno, como paso previo, o paso 0 del proceso HAZ UN BACKUP DE TU BASE DE DATOS POR SI ALGO FALLA Y TIENES QUE RECUPERARLA. Consejo que debes tener siempre en mente cuando te pongas a tocar tablas de una instalación de cualquier cosa.

El primer paso: ir a wp-config.php y cambiar ahí el prefijo (en nuestro caso pondremos como prefijo personalizado my_b457Bch33s_ ).

$table_prefix  = 'my_b457Bch33s_';

El siguiente paso es renombrar todas las tablas de tu instalación de wordpress:

/*Básicamente vas haciendo esto con todas las tablas*/
RENAME table `wp_comments` TO `my_b457Bch33s_comments`;

Tras renombrar las tablas haces un update sobre la tabla options buscando todas las líneas que hagan referencia a tablas con el prefijo viejo para actualizarlas:

UPDATE `my_b457Bch33s_options` SET `option_name`=REPLACE(`option_name`,'wp_','my_b457Bch33s_') WHERE `option_name` LIKE '%wp_%';

Y con la tabla usermeta tres cuartos de lo mismo, update que te crió.

UPDATE `my_b457Bch33s_usermeta` SET `meta_key`=REPLACE(`meta_key`,'wp_','my_b457Bch33s_') WHERE `meta_key` LIKE '%wp_%';

Y tras esto deberías tener todo funcionando de nuevo, pero con el nuevo prefijo, más seguro contra potenciales atacantes.