Búsqueda de sitios web

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 utilizar el lenguaje de manipulación de datos (DML ) operaciones con datos en un servidor MySQL/MariaDB.

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

  1. Instale la última base de datos MySQL en sistemas Linux
  2. Instale la última base de datos MariaDB en sistemas Linux

Para abreviar, 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 sabes, 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 estructural para realizar operaciones en bases de datos. Además, tenga en cuenta que MariaDB utiliza los términos base de datos y esquema indistintamente.

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.

Creando una nueva base de datos

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

[root@TecMint ~]# 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 creada la base de datos, debemos crear al menos dos tablas en ella. Pero primero exploremos el concepto de tipos de datos.

Presentamos los tipos de datos de 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 (puedes consultar la lista completa en la documentación oficial online de MariaDB):

Numérico :
  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 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 SIGNED.

DOUBLE(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 una número de coma flotante de doble precisión. Si se especifica UNSIGNED, no se permiten valores negativos.

Cadena :
  1. VARCHAR(M) representa una cadena de longitud variable donde M es la longitud máxima permitida de la columna 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 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 ocurre con VARCHAR(M), la longitud máxima real se reduce si se almacenan caracteres de varios bytes. Si se especifica M, la columna se crea como el tipo más pequeño que puede almacenar tal cantidad 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.
Fecha y hora:
  1. FECHA 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 utiliza 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 de una tabla.

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

Crear tablas con claves primarias y externas

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, una clave externa se utiliza para crear un vínculo entre los datos de 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 las 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 principal 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.

Seleccionar, insertar, actualizar y eliminar filas

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

Ejecute la siguiente consulta desde el indicador de 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 de cada registro para crear la consulta INSERT para BooksTBL.

Si desea recuperar un registro a la vez, puede utilizar 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 ACTUALIZAMOS el precio de “El Alquimista” de Paulo Coelho y SELECCIONAMOS ese disco específico nuevamente.

Observe cómo el campo BookLastUpdated ahora muestra un valor diferente. Como explicamos anteriormente, un campo TIMESTAMP muestra el valor cuando el registro se insertó o se modificó 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 puedes eliminar un registro si ya no lo utilizas. Por ejemplo, supongamos que queremos eliminar “El Alquimista” de BooksTBL.

Para hacerlo, usaremos la declaració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 SELECCIONAR primero para ver los registros que potencialmente pueden verse afectados por el BORRAR.

Además, no olvide agregar la cláusula WHERE y una condición (BookID=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 utilizar la instrucción CONCAT. Por ejemplo, digamos que queremos devolver un conjunto de resultados que consta de un campo con el nombre del libro y el autor en el formato "El Alquimista (Paulo Coelho)" y otra columna con el precio.

Esto requerirá una 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 vemos, CONCAT nos permite unir múltiples expresiones de cadena separadas por comas. También notarás 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)

Crear usuario para acceder a la base de datos BookstoreDB

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 MariaDB (la llamaremos bookstoreuser) y asignar todos los permisos necesarios para BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

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

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

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

Consejos adicionales para MySQL

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

MariaDB [BookstoreDB]> \! clear

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

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;

(No dude en 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;

[root@TecMint ~]# 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 y 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 sistemas/bases de datos.

  1. Administración de bases de datos MySQL Parte – 1
  2. Administración de bases de datos MySQL Parte – 2
  3. Ajuste y optimización del rendimiento de MySQL – Parte 3

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