Sentencias útiles en MySQL

Vamos a ver varios ejemplos útiles para trabajar con MySQL, son cosas sencillas y muy básicas pero creo que interesantes y del día a día.

Empezaremos conectándonos al servidor de base de datos con usuario root:

   # mysql -u root -p 

CREAR USUARIOS, PRIVILEGIOS Y BASES DE DATOS

Para no utilizar el usuario root en labores de administración, lo ideal es crear un usuario diferente como por ejemplo adminUSER:

   > GRANT ALL PRIVILEGES ON *.* TO 'adminUSER'@'localhost' IDENTIFIED BY 'Pa$$w0rd';

Generar una base de datos es tan sencillo como lanzar la siguiente sentencia SQL especificando el nombre de la base de datos:

    CREATE DATABASE testDB;

Si vamos a crear un usuario específico para que sea administrador solo de esa base de datos podemos crear el usuario adminTEST:

   > GRANT ALL PRIVILEGES ON testDB.* TO 'adminTEST'@'localhost' IDENTIFIED BY 'Pa$$w0rd';

La próxima vez que nos conectemos al servidor de base de datos podremos iniciar sesión con este usuario, así solo tendremos privilegios sobre la nueva base de datos:

   # mysql -u adminTEST -p 

Muchas veces necesitamos un usuario que tenga privilegios de solo lectura sobre una base de datos, por ejemplo un usuario de servicio con el que vamos a monitorizar. Empecemos viendo como crear un usuario que tendrá acceso desde el mismo servidor de base de datos solo a la base de datos especificada:

   > GRANT SELECT ON testDB.* TO 'readTEST'@'localhost' IDENTIFIED BY 'Pa$$w0rd';

Si lo que deseamos es que se conecte desde cualquier equipo remoto:

   > GRANT SELECT ON testDB.* TO 'readTEST'@'%' IDENTIFIED BY 'Pa$$w0rd';

O podemos ser mas restrictivos especificando el nombre DNS o la dirección IP desde la que se conectará el usuario con acceso de lectura:

   > GRANT SELECT ON testDB.* TO 'readTEST'@'NombreServidorRemoto' IDENTIFIED BY 'Pa$$w0rd';

Siempre que cambiemos los privilegios de un usuario o lo creemos, hay que ejecutar:

   > FLUSH PRIVILEGES;

CREAR TABLAS

Cuando estamos conectados a un servidor con varias bases de datos, primero tendremos que ejecutar la sentencia para utilizar una base de datos concreta:

   > USE testDB;

Una forma rápida de generar tablas en MySQL es a través de un pequeño script, aunque siempre podemos utilizar un editor gráfico como podría ser DBeaver. Debemos especificar el nombre de la tabla, los campos que la componen y sus características. Veamos algunos ejemplos:

Creación de una tabla de usuarios: Crearemos UserID, un campo que será clave principal, autoincremental y no podrá ser null. Además crearemos una serie de campos de texto de tamaño variable para especificar los datos del usuario, alias, nombre, etcétera. Por último un campo de tipo booleano para establecer si el usuario está activo o no.

   > CREATE TABLE DBUsers (
         UserID int NOT NULL AUTO_INCREMENT,
         Alias varchar (50) NOT NULL,
         FirstName varchar(100),
         LastName varchar(100),
         Email varchar(150),
         Enabled boolean DEFAULT 1,
         PRIMARY KEY (UserID)
         );

Creación de una tabla de grupos de usuarios o roles: Crearemos GroupID, un campo que será clave principal, autoincremental y no podrá ser null. Además estableceremos el campo con el nombre del grupo y un campo booleano para establecer si el usuario está activo o no.

   > CREATE TABLE DBGroupsList (
         GroupID int NOT NULL AUTO_INCREMENT,
         GroupName varchar (50) NOT NULL,
         Enabled boolean DEFAULT 1,
         PRIMARY KEY (GroupID)
         );

Creación de una tabla en la que asignaremos usuarios a grupos especificando los identificadores de usuario y grupo. Además añadimos un campo que establezca si esa asignación está activa o no. Además estos campos tienen que estar conectados a las tablas que definen la lista de usuarios y la lista de grupos, para eso son las claves foráneas.

    > CREATE TABLE DBGroupsAssign (
         UserID int NOT NULL
         GroupID int NOT NULL,
         Enabled boolean DEFAULT 1,
         FOREIGN KEY (UserID) REFERENCES DBUsers(UserID),
         FOREIGN KEY (GroupID) REFERENCES DBGroupsList(GroupID)
         );

MODIFICAR TABLAS

Una labor muy común es modificar una tabla, vamos a ver el caso mas frecuente, por ejemplo que tenemos que añadir un campo.

En este ejemplo vamos a añadir la fecha de alta en la tabla de usuarios. Además como lo queremos en un sitio concreto, podemos especificar FIRST para ser el primer campo de la tabla o AFTER para especificar el campo detrás del que queremos insertar el nuevo.

   > ALTER TABLE DBUsers ADD StartDate DATETIME AFTER Alias;

Otro caso muy frecuente es modificar alguna característica de un campo ya existente. Por ejemplo vamos a modificar la longitud de un campo, imaginemos que el Email se nos ha quedado corto en la tabla de usuarios. Además vamos a hacer que sea un campo obligatorio, que no pueda estar vacio.

   > ALTER TABLE DBUsers MODIFY Email varchar(255) NOT NULL;

SaluDOS