Ingestión de datos y consultas en MariaDB#
Última modificación: Mayo 26, 2022
Introducción#
MariaDB es un clon de MySQL.
Es desarrollada por el mismo equipo de MySQL con el fin de garantizar que siempre será open source.
Se fundamenta en los valores de desempeño, estabilidad y apertura.
Accesso al prompt de MariaDB desde la línea de comandos#
$ mysql -u //user_name// -p -h //ip_address// //db_name//
Creación de una tabla e insersión de valores#
Copie y pegue el código en el prompt de comandos.
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS books (
BookID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Title VARCHAR(100) NOT NULL,
SeriesID INT, AuthorID INT);
CREATE TABLE IF NOT EXISTS authors
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE IF NOT EXISTS series
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
INSERT INTO books (Title,SeriesID,AuthorID)
VALUES('The Fellowship of the Ring',1,1),
('The Two Towers',1,1), ('The Return of the King',1,1),
('The Sum of All Men',2,2), ('Brotherhood of the Wolf',2,2),
('Wizardborn',2,2), ('The Hobbbit',0,1);
Show#
MariaDB [test]> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| authors |
| books |
| series |
+----------------+
3 rows in set (0.001 sec)
Describe#
MariaDB [test]> DESCRIBE books;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| BookID | int(11) | NO | PRI | NULL | auto_increment |
| Title | varchar(100) | NO | | NULL | |
| SeriesID | int(11) | YES | | NULL | |
| AuthorID | int(11) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.007 sec)
Select#
MariaDB [test]> SELECT * FROM books;
+--------+----------------------------+----------+----------+
| BookID | Title | SeriesID | AuthorID |
+--------+----------------------------+----------+----------+
| 1 | The Fellowship of the Ring | 1 | 1 |
| 2 | The Two Towers | 1 | 1 |
| 3 | The Return of the King | 1 | 1 |
| 4 | The Sum of All Men | 2 | 2 |
| 5 | Brotherhood of the Wolf | 2 | 2 |
| 6 | Wizardborn | 2 | 2 |
| 7 | The Hobbbit | 0 | 1 |
+--------+----------------------------+----------+----------+
7 rows in set (0.001 sec)
Insersión de datos#
MariaDB [test]> INSERT INTO books (Title, SeriesID, AuthorID)
-> VALUES ("Lair of Bones", 2, 2);
Query OK, 1 row affected (0.004 sec)
MariaDB [test]> SELECT * FROM books;
+--------+----------------------------+----------+----------+
| BookID | Title | SeriesID | AuthorID |
+--------+----------------------------+----------+----------+
| 1 | The Fellowship of the Ring | 1 | 1 |
| 2 | The Two Towers | 1 | 1 |
| 3 | The Return of the King | 1 | 1 |
| 4 | The Sum of All Men | 2 | 2 |
| 5 | Brotherhood of the Wolf | 2 | 2 |
| 6 | Wizardborn | 2 | 2 |
| 7 | The Hobbbit | 0 | 1 |
| 8 | Lair of Bones | 2 | 2 |
+--------+----------------------------+----------+----------+
8 rows in set (0.001 sec)
Modificación de datos#
MariaDB [test]> UPDATE books
-> SET Title = "The Hobbit"
-> WHERE BookID = 7;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> SELECT * FROM books;
+--------+----------------------------+----------+----------+
| BookID | Title | SeriesID | AuthorID |
+--------+----------------------------+----------+----------+
| 1 | The Fellowship of the Ring | 1 | 1 |
| 2 | The Two Towers | 1 | 1 |
| 3 | The Return of the King | 1 | 1 |
| 4 | The Sum of All Men | 2 | 2 |
| 5 | Brotherhood of the Wolf | 2 | 2 |
| 6 | Wizardborn | 2 | 2 |
| 7 | The Hobbit | 0 | 1 |
| 8 | Lair of Bones | 2 | 2 |
+--------+----------------------------+----------+----------+
8 rows in set (0.001 sec)
Salida del terminal#
MariaDB [test]> exit
Bye
root@5f54c55f7926:/workspace#
Envio de comandos desde el terminal#
[1]:
!mariadb -u root -e "USE test; SELECT * FROM books;"
+--------+----------------------------+----------+----------+
| BookID | Title | SeriesID | AuthorID |
+--------+----------------------------+----------+----------+
| 1 | The Fellowship of the Ring | 1 | 1 |
| 2 | The Two Towers | 1 | 1 |
| 3 | The Return of the King | 1 | 1 |
| 4 | The Sum of All Men | 2 | 2 |
| 5 | Brotherhood of the Wolf | 2 | 2 |
| 6 | Wizardborn | 2 | 2 |
| 7 | The Hobbit | 0 | 1 |
| 8 | Lair of Bones | 2 | 2 |
+--------+----------------------------+----------+----------+
Uso de un archivo de comandos#
[2]:
%%writefile /tmp/myquery.sql
USE test;
SELECT * FROM books;
Overwriting /tmp/myquery.sql
[3]:
!mariadb -u root < /tmp/myquery.sql
BookID Title SeriesID AuthorID
1 The Fellowship of the Ring 1 1
2 The Two Towers 1 1
3 The Return of the King 1 1
4 The Sum of All Men 2 2
5 Brotherhood of the Wolf 2 2
6 Wizardborn 2 2
7 The Hobbit 0 1
8 Lair of Bones 2 2
[4]:
!mariadb -u root < /tmp/myquery.sql > /tmp/results.csv
!cat /tmp/results.csv
BookID Title SeriesID AuthorID
1 The Fellowship of the Ring 1 1
2 The Two Towers 1 1
3 The Return of the King 1 1
4 The Sum of All Men 2 2
5 Brotherhood of the Wolf 2 2
6 Wizardborn 2 2
7 The Hobbit 0 1
8 Lair of Bones 2 2