Aprenda MySQL/MariaDB para principiantes - Parte 1


En este artículo, mostraremos cómo crear una base de datos (también conocida como esquema), tablas (con tipos de datos) y explicaremos cómo realizar operaciones de lenguaje de manipulación de datos (DML) con datos en un servidor MySQL/MariaDB.

Se asume que previamente 1) instaló los paquetes necesarios en su sistema Linux, y 2) ejecutó mysql_secure_installation para mejorar la seguridad del servidor de la base de datos. De lo contrario, siga las guías a continuación para instalar el servidor MySQL/MariaDB.

  1. Instale la base de datos MySQL más reciente en sistemas Linux
  2. Instale la base de datos MariaDB más reciente en sistemas Linux

En aras de la brevedad, nos referiremos a MariaDB exclusivamente a lo largo de este artículo, pero los conceptos y comandos descritos aquí también se aplican a MySQL.

Creación de bases de datos, tablas y usuarios autorizados

Como sabe, una base de datos se puede definir en términos simples como una colección organizada de información. En particular, MariaDB es un sistema de gestión de bases de datos relacionales (RDBMS) y utiliza el lenguaje de consulta de estructura para realizar operaciones en bases de datos. Además, tenga en cuenta que MariaDB usa los términos base de datos y esquema de manera intercambiable.

Para almacenar información persistente en una base de datos, usaremos tablas que almacenan filas de datos. A menudo, dos o más tablas estarán relacionadas entre sí de alguna manera. Eso es parte de la organización que caracteriza el uso de bases de datos relacionales.

Para crear una nueva base de datos llamada BooksDB , ingrese el indicador de MariaDB con el siguiente comando (se le pedirá que ingrese la contraseña del usuario raíz de MariaDB):

[[email protected] ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 

Una vez que se ha creado la base de datos, necesitamos crear al menos dos tablas en ella. Pero primero exploremos el concepto de tipos de datos.

Introducción a los tipos de datos MariaDB

Como explicamos anteriormente, las tablas son objetos de base de datos donde guardaremos información persistente. Cada tabla consta de dos o más campos (también conocidos como columnas) de un tipo de datos determinado (el tipo de información) que dicho campo puede almacenar.

Los tipos de datos más comunes en MariaDB son los siguientes (puede consultar la lista completa en la documentación oficial en línea de MariaDB):

  1. BOOLEAN considera 0 como falso y cualquier otro valor como verdadero.
  2. TINYINT, si se usa con SIGNED, cubre el rango de -128 a 127, mientras que el rango UNSIGNED es de 0 a 255.
  3. SMALLINT, si se usa con SIGNED, cubre el rango de -32768 a 32767. El rango de UNSIGNED es de 0 a 65535.
  4. INT, si se usa con UNSIGNED, cubre el rango de 0 a 4294967295 y de -2147483648 a 2147483647 en caso contrario.

Nota: En TINYINT, SMALLINT e INT, se asume el valor predeterminado FIRMADO.

DOBLE (M, D), donde M es el número total de dígitos y D es el número de dígitos después del punto decimal, representa un número de punto flotante de doble precisión. Si se especifica UNSIGNED, no se permiten valores negativos.

  1. VARCHAR (M) representa una cadena de longitud variable donde M es la longitud máxima de columna permitida en bytes (65.535 en teoría). En la mayoría de los casos, el número de bytes es idéntico al número de caracteres, excepto por algunos caracteres que pueden ocupar hasta 3 bytes. Por ejemplo, la letra española ñ representa un carácter pero ocupa 2 bytes.
  2. TEXTO (M) representa una columna con una longitud máxima de 65 535 caracteres. Sin embargo, como sucede con VARCHAR (M), la longitud máxima real se reduce si se almacenan caracteres multibyte. Si se especifica M, la columna se crea como el tipo más pequeño que puede almacenar tal número de caracteres.
  3. MEDIUMTEXT (M) y LONGTEXT (M) son similares a TEXT (M), solo que las longitudes máximas permitidas son 16.777.215 y 4.294.967.295 caracteres, respectivamente.

  1. DATE representa la fecha en formato AAAA-MM-DD.
  2. TIME representa la hora en formato HH: MM: SS.sss (hora, minutos, segundos y milisegundos).
  3. DATETIME es la combinación de DATE y TIME en formato AAAA-MM-DD HH: MM: SS.
  4. TIMESTAMP se usa para definir el momento en que se agregó o actualizó una fila.

Después de haber revisado estos tipos de datos, estará en una mejor posición para determinar qué tipo de datos necesita asignar a una columna determinada en una tabla.

Por ejemplo, el nombre de una persona puede caber fácilmente en un VARCHAR (50), mientras que una publicación de blog necesitará un tipo de TEXTO (elija M según sus necesidades específicas).

Antes de sumergirnos en la creación de tablas, hay dos conceptos fundamentales sobre las bases de datos relacionales que debemos revisar: claves primarias y externas.

Una clave principal contiene un valor que identifica de forma única cada fila o registro de la tabla. Por otro lado, se utiliza una clave externa para crear un vínculo entre los datos en dos tablas y para controlar los datos que se pueden almacenar en la tabla donde se encuentra la clave externa. Tanto las claves primarias como externas son generalmente INT.

Para ilustrar, usemos BookstoreDB y creemos dos tablas llamadas AuthorsTBL y BooksTBL de la siguiente manera. La restricción NOT NULL indica que el campo asociado requiere un valor distinto de NULL.

Además, AUTO_INCREMENT se utiliza para aumentar en uno el valor de las columnas de clave primaria INT cuando se inserta un nuevo registro en la tabla.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);
MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> 

Ahora podemos continuar y comenzar a insertar registros en AuthorsTBL y BooksTBL .

Primero llenaremos la tabla AuthorsTBL . ¿Por qué? Porque necesitamos tener valores para AuthorID antes de insertar registros en BooksTBL.

Ejecute la siguiente consulta desde su indicador MariaDB:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Después de eso, seleccionaremos todos los registros de AuthorsTBL. Recuerde que necesitaremos el AuthorID para cada registro para crear la consulta INSERT para BooksTBL.

Si desea recuperar un registro a la vez, puede usar una cláusula WHERE para indicar una condición que debe cumplir una fila para ser devuelta. Por ejemplo,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternativamente, puede seleccionar todos los registros simultáneamente:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

Ahora creemos la consulta INSERT para BooksTBL, usando el AuthorID correspondiente para que coincida con el autor de cada libro. Un valor de 1 en BookIsAvailable indica que el libro está en stock, 0 en caso contrario:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

En este punto haremos un SELECT para ver los registros en BooksTBL. Entonces, ACTUALIZEMOS el precio de "El alquimista" de Paulo Coelho y SELECCIONEMOS ese disco específico de nuevo.

Observe cómo el campo BookLastUpdated ahora muestra un valor diferente. Como explicamos anteriormente, un campo TIMESTAMP muestra el valor cuando el registro fue insertado o modificado por última vez.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> 

Aunque no lo haremos aquí, también puede eliminar un registro si ya no se usa. Por ejemplo, supongamos que queremos eliminar "The Alchemist" de BooksTBL.

Para hacerlo, usaremos la instrucción DELETE de la siguiente manera:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Como en el caso de ACTUALIZAR, es una buena idea hacer una SELECCIÓN primero para ver los registros que potencialmente pueden verse afectados por ELIMINAR.

Además, no olvide agregar la cláusula WHERE y una condición (BookID u003d 6) para seleccionar el registro específico que se eliminará. De lo contrario, corre el riesgo de eliminar todas las filas de la tabla.

Si desea concatenar dos (o más) campos, puede usar la instrucción CONCAT. Por ejemplo, supongamos que queremos devolver un conjunto de resultados que consta de un campo con el nombre del libro y el autor en forma de "El alquimista (Paulo Coelho)" y otra columna con el precio.

Esto requerirá un JOIN entre AuthorsTBL y BooksTBL en el campo común compartido por ambas tablas (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Como podemos ver, CONCAT nos permite unir múltiples expresiones de cadena separadas por comas. También notará que elegimos el alias Descripción para representar el conjunto de resultados de la concatenación.

El resultado de la consulta anterior se muestra en la siguiente imagen:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

Usar root para realizar todas las operaciones DML en una base de datos es una mala idea. Para evitar esto, podemos crear una nueva cuenta de usuario de MariaDB (la llamaremos usuario de librería) y asignar todos los permisos necesarios para BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER [email protected] IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email protected];
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
MariaDB [BookstoreDB]> CREATE USER [email protected] IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email protected];
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Tener un usuario dedicado e independiente para cada base de datos evitará daños a toda la base de datos en caso de que una sola cuenta se vea comprometida.

Para borrar el indicador de MariaDB, escriba el siguiente comando y presione Entrar:

MariaDB [BookstoreDB]> \! clear

Para inspeccionar la configuración de una tabla determinada, haga lo siguiente:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

Por ejemplo,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

Una inspección rápida revela que el campo BookIsAvailable admite valores NULL. Como no queremos permitir eso, ALTERAremos la tabla de la siguiente manera:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Siéntase libre de mostrar las columnas nuevamente; el SÍ resaltado en la imagen de arriba ahora debería ser un NO).

Finalmente, para ver todas las bases de datos en su servidor, haga:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
[[email protected] ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

La siguiente imagen muestra el resultado del comando anterior después de acceder al indicador de MariaDB como usuario de la librería (tenga en cuenta que esta cuenta no puede "ver" ninguna base de datos que no sea BookstoreDB e information_schema (disponible para todos los usuarios):

Resumen

En este artículo hemos explicado cómo ejecutar operaciones DML y cómo crear una base de datos, tablas y usuarios dedicados en una base de datos MariaDB. Además, compartimos algunos consejos que pueden facilitarle la vida como administrador de sistema/base de datos.

  1. Parte de administración de la base de datos MySQL - 1
  2. Parte de la administración de la base de datos MySQL - 2
  3. Ajuste y optimización del rendimiento de MySQL - Parte 3

Si tiene alguna pregunta sobre este artículo, no dude en hacérnoslo saber. No dude en utilizar el formulario de comentarios a continuación para comunicarse con nosotros.