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

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

  1. Install Latest MySQL Database in Linux Systems
  2. Install Latest MariaDB Database in Linux Systems

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

Parte 1 : Aprenda MySQL/MariaDB para principiantes

ninguna

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

Como usted sabe, una base de datos puede definirse en términos simples como una recopilación organizada de información. En particular, MariaDB es un sistema de administración de base de datos relacional ( 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 la solicitud de MariaDB con el siguiente comando (se le pedirá que ingrese la contraseña del usuario root 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, debemos crear al menos dos tablas en ella. Pero primero vamos a explorar el concepto de tipos de datos.

Introducción a los tipos de datos de MariaDB

Como explicamos anteriormente, las tablas son objetos de la base de datos donde mantendremos 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 en línea oficial de MariaDB):

  1. BOOLEAN considers 0 as false and any other values as true.
  2. TINYINT, if used with SIGNED, covers the range from -128 to 127, whereas the UNSIGNED range is 0 to 255.
  3. SMALLINT, if used with SIGNED, covers the range from -32768 to 32767. The UNSIGNED range is 0 to 65535.
  4. INT, if used with UNSIGNED, covers the range from 0 to 4294967295, and -2147483648 to 2147483647 otherwise.

rojo

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

  1. VARCHAR(M) represents a string of variable length where M is the maximum allowed column length in bytes (65,535 in theory). In most cases, the number of bytes is identical to the number of characters, except for some characters that can take up as much as 3 bytes. For example, the Spanish letter ñ represents one character but takes up 2 bytes.
  2. TEXT(M) represents a column with a maximum length of 65,535 characters. However, as it happens with VARCHAR(M), the actual maximum length is reduced if multi-byte characters are stored. If M is specified, the column is created as the smallest type that can store such number of characters.
  3. MEDIUMTEXT(M) and LONGTEXT(M) are similar to TEXT(M), only that the maximum allowed lengths are 16,777,215 and 4,294,967,295 characters, respectively.
  1. DATE represents the date in YYYY-MM-DD format.
  2. TIME represents the time in HH:MM:SS.sss format (hour, minutes, seconds, and milliseconds).
  3. DATETIME is the combination of DATE and TIME in YYYY-MM-DD HH:MM:SS format.
  4. TIMESTAMP is used to define the moment a row was added or updated.

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 dada en una tabla.

Por ejemplo, el nombre de una persona puede encajar fácilmente en un VARCHAR (50) , mientras que una publicación de blog necesitará un tipo de TEXTO (elija M como por sus necesidades especificas).

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

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

Para ilustrar, vamos a usar el BookstoreDB y crear 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 INT columnas de clave primaria 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)
);

Crear tablas MySQL con clave principal y externa

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 rellenaremos la tabla AuthorsTBL . ¿Por qué? Porque necesitamos tener valores para AuthorID antes de insertar registros en BooksTBL .

Ejecute la siguiente consulta desde su 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 ID de autor 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;

Seleccionar y consultar registro en MySQL

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 creamos la consulta INSERT para BooksTBL , utilizando el AuthorID correspondiente correspondiente para que coincida con el autor de cada libro. Un valor de 1 en BookIsAvailable indica que el libro está en stock, 0 de lo 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);

Insertar consulta en la tabla MySQL

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 SELECCIONAR para ver los registros en BooksTBL . Luego vamos a ACTUALIZAR el precio de " El Alquimista " por Paulo Coelho y SELECCIONAR ese registro específico nuevamente.

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

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;

Insertar consulta y actualizar la tabla en la base de datos MySQL

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 no se usa más. Por ejemplo, supongamos que queremos eliminar " The Alchemist " 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 ACTUALIZACIÓN , es una buena idea hacer una SELECCIONAR primero para ver los registros que pueden verse afectados por la ELIMINAR .

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 usar la declaración CONCAT . Por ejemplo, digamos que queremos devolver un conjunto de resultados que consiste en un campo con el nombre del libro y el autor en la forma de " El Alquimista (Paulo Coelho) " y otra columna con el precio.

Esto requerirá un UNIR 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 varias 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.

La salida de la consulta anterior se muestra en la siguiente imagen:

Consultar varios campos en la tabla MySQL

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)

Utilizar root para realizar todas las operaciones de 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 [email protected] IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email protected];
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;

Crear nuevo usuario de base de datos con privilegios

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 y separado para cada base de datos evitará daños en toda la base de datos en caso de que una sola cuenta se vea comprometida.

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

MariaDB [BookstoreDB]> \! clear

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

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

Por ejemplo,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;

Listar columnas en la tabla de base de datos

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 volver a mostrar las columnas: el 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;

Listar todas las bases de datos MySQL

[[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 bookstoreuser (observe cómo esta cuenta no puede "ver" ninguna base de datos que no sea BookstoreDB y < strong> information_schema (disponible para todos los usuarios):

Resumen

En este artículo, hemos explicado cómo ejecutar las operaciones de 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 hacer que su vida como administrador de sistemas/bases de datos sea más fácil.

  1. MySQL Database Administration Part – 1
  2. MySQL Database Administration Part – 2
  3. MySQL Performance Tunning and Optimization – Part 3

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