MySQL database tutorial

In this article we will work with MySQL databases - creation, adding records, etc..
To connect to a MySQL /MariaDB instance:

# mariadb -u user -p'user_password'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 11.7.2-MariaDB-ubu2404 mariadb.org binary distribution

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

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

To show current user’s rights/grants:

MariaDB [(none)]> show grants;
+-----------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user`@`%` IDENTIFIED BY PASSWORD '*xxx' |
| GRANT ALL PRIVILEGES ON `my\_database`.* TO `user`@`%`                                              |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.008 sec)

To show databases:

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_database        |
+--------------------+
2 rows in set (0.009 sec)

To create a database:

MariaDB [(none)]> create database publications;
Query OK, 1 row affected (0.011 sec)

To create a user:

MariaDB [(none)]> CREATE USER 'george'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.018 sec)

Let’s grant the new user full rights for the publications database which we created earlier.

MariaDB [(none)]> GRANT ALL ON publications.* TO 'george'@'localhost';

Query OK, 0 rows affected (0.010 sec)

We can now log in to mariadb with the new user:

root@mariadb-host:/# mariadb -u george -p'password'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 11.7.2-MariaDB-ubu2404 mariadb.org binary distribution

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

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

MariaDB [(none)]>

You can enter the password manually without specifying it in the -p parameter:

root@mariadb-host:/# mariadb -u george -p
Enter password:

Let’s load the database publications:

MariaDB [(none)]> USE publications;
Database changed

Let’s create a table, using a multi-line command and tabs for the columns:

CREATE TABLE classics (
	author VARCHAR(128),
	title VARCHAR(128),
	type VARCHAR(16),
	year CHAR(4));

The result below:

MariaDB [publications]> CREATE TABLE classics (
    -> author VARCHAR(128),
    -> title VARCHAR(128),
    -> type VARCHAR(16),
    -> year CHAR(4));
Query OK, 0 rows affected (0.037 sec)

Let’s take a look at the newly created table with the DESCRIBE command:

MariaDB [publications]> DESCRIBE classics;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| author | varchar(128) | YES  |     | NULL    |       |
| title  | varchar(128) | YES  |     | NULL    |       |
| type   | varchar(16)  | YES  |     | NULL    |       |
| year   | char(4)      | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.011 sec)

Let’s use ALTER TABLE to add an auto-incrementing column id, so that every row in the database has an unique identifier.
Unsigned means that there are 4 billion possible unique rows.

MariaDB [publications]> ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;
Query OK, 0 rows affected (0.037 sec)
Records: 0  Duplicates: 0  Warnings: 0

The table looks like this:

MariaDB [publications]> DESCRIBE classics;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| author | varchar(128)     | YES  |     | NULL    |                |
| title  | varchar(128)     | YES  |     | NULL    |                |
| type   | varchar(16)      | YES  |     | NULL    |                |
| year   | char(4)          | YES  |     | NULL    |                |
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.011 sec)

We will add some records:

INSERT INTO classics(author, title, type, year)
VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');
INSERT INTO classics(author, title, type, year)
VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');
INSERT INTO classics(author, title, type, year)
VALUES('Charles Darwin','The Origin of Species','Nonfiction','1856');
INSERT INTO classics(author, title, type, year)
VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841');
INSERT INTO classics(author, title, type, year)
VALUES('William Shakespeare','Romeo and Juliet','Play','1594');

With the SELECT * statement, we list all records in the table.
Notice that each has its unique id, as per the last row:

MariaDB [publications]> SELECT * FROM classics;
+---------------------+------------------------------+------------+------+----+
| author              | title                        | type       | year | id |
+---------------------+------------------------------+------------+------+----+
| Mark Twain          | The Adventures of Tom Sawyer | Fiction    | 1876 |  1 |
| Jane Austen         | Pride and Prejudice          | Fiction    | 1811 |  2 |
| Charles Darwin      | The Origin of Species        | Nonfiction | 1856 |  3 |
| Charles Dickens     | The Old Curiosity Shop       | Fiction    | 1841 |  4 |
| William Shakespeare | Romeo and Juliet             | Play       | 1594 |  5 |
+---------------------+------------------------------+------------+------+----+
5 rows in set (0.009 sec)

Let’s modify one the colum year to SMALLINT with takes only 2 bytes.

MariaDB [publications]> ALTER TABLE classics MODIFY year SMALLINT;
Query OK, 5 rows affected (0.050 sec)
Records: 5  Duplicates: 0  Warnings: 0

Data will automatically be modified:

MariaDB [publications]> SELECT * FROM classics;
+---------------------+------------------------------+------------+------+----+
| author              | title                        | type       | year | id |
+---------------------+------------------------------+------------+------+----+
| Mark Twain          | The Adventures of Tom Sawyer | Fiction    | 1876 |  1 |
| Jane Austen         | Pride and Prejudice          | Fiction    | 1811 |  2 |
| Charles Darwin      | The Origin of Species        | Nonfiction | 1856 |  3 |
| Charles Dickens     | The Old Curiosity Shop       | Fiction    | 1841 |  4 |
| William Shakespeare | Romeo and Juliet             | Play       | 1594 |  5 |
+---------------------+------------------------------+------------+------+----+
5 rows in set (0.003 sec)

Let’s add another column, called pages, to the table. It is SMALLINT, it can hold a value up to 65535.

MariaDB [publications]> ALTER TABLE classics ADD pages SMALLINT UNSIGNED;
Query OK, 0 rows affected (0.027 sec)
Records: 0  Duplicates: 0  Warnings: 0

If we take a look at the table:

MariaDB [publications]> DESCRIBE classics;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| author | varchar(128)         | YES  |     | NULL    |                |
| title  | varchar(128)         | YES  |     | NULL    |                |
| type   | varchar(16)          | YES  |     | NULL    |                |
| year   | smallint(6)          | YES  |     | NULL    |                |
| id     | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| pages  | smallint(5) unsigned | YES  |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
6 rows in set (0.009 sec)

Let’s change the column type to classics, to avoid using an SQL keyword:

MariaDB [publications]> ALTER TABLE classics CHANGE type category VARCHAR(16);
Query OK, 0 rows affected (0.027 sec)
Records: 0  Duplicates: 0  Warnings: 0

We will also remove the column pages. We will use the DROP statement.
This change cannot be undone:

MariaDB [publications]> ALTER TABLE classics DROP pages;
Query OK, 0 rows affected (0.032 sec)
Records: 0  Duplicates: 0  Warnings: 0

To delete a table, we will use DROP TABLE:

MariaDB [publications]> CREATE TABLE disposable(trash INT);
Query OK, 0 rows affected (0.021 sec)

MariaDB [publications]> DESCRIBE disposable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| trash | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.002 sec)

MariaDB [publications]> DROP TABLE disposable;
Query OK, 0 rows affected (0.009 sec)

MariaDB [publications]> SHOW tables;
+------------------------+
| Tables_in_publications |
+------------------------+
| classics               |
+------------------------+
1 row in set (0.001 sec)

We will create indexes for the table classics; there are a few types: INDEX, a PRIMARY KEY, or a FULLTEXT.
We use ADD INDEX and limit the author and title columns to the first 20 characters.

MariaDB [publications]> ALTER TABLE classics ADD INDEX(author(20));
Query OK, 0 rows affected (0.041 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [publications]> ALTER TABLE classics ADD INDEX(title(20));
Query OK, 0 rows affected (0.023 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [publications]> ALTER TABLE classics ADD INDEX(category(4));
Query OK, 0 rows affected (0.027 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [publications]> ALTER TABLE classics ADD INDEX(year);
Query OK, 0 rows affected (0.027 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [publications]> DESCRIBE classics;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| author   | varchar(128)     | YES  | MUL | NULL    |                |
| title    | varchar(128)     | YES  | MUL | NULL    |                |
| category | varchar(16)      | YES  | MUL | NULL    |                |
| year     | smallint(6)      | YES  | MUL | NULL    |                |
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+----------+------------------+------+-----+---------+----------------+

We could also specify the INDEX option at table creation:

CREATE TABLE classics (
	author VARCHAR(128),
	title VARCHAR(128),
	category VARCHAR(16),
	year SMALLINT,
	INDEX(author(20)),
	INDEX(title(20)),
	INDEX(category(4)),
	INDEX(year));

We will add an isbn column, which will be the new primary key, instead of id.
We will also be deleting the id column via the DROP statement:

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
UPDATE classics SET isbn='9780582506206' WHERE year='1811';
UPDATE classics SET isbn='9780517123201' WHERE year='1856';
UPDATE classics SET isbn='9780099533474' WHERE year='1841';
UPDATE classics SET isbn='9780192814968' WHERE year='1594';
ALTER TABLE classics ADD PRIMARY KEY(isbn);
ALTER TABLE classics DROP id;

We will also modify the isbn column to prevent it from having no values (NULL), as it is a primary key (it uniquely identifies each record).

MariaDB [publications]> ALTER TABLE classics MODIFY COLUMN isbn CHAR(13) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [publications]> DESCRIBE classics;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| author   | varchar(128) | YES  | MUL | NULL    |       |
| title    | varchar(128) | YES  | MUL | NULL    |       |
| category | varchar(16)  | YES  | MUL | NULL    |       |
| year     | smallint(6)  | YES  | MUL | NULL    |       |
| isbn     | char(13)     | NO   | PRI |         |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.002 sec)

Let’s insert another record:

  MariaDB [publications]> INSERT INTO classics(author, title, category, year, isbn)
    -> VALUES('Frank Herbert','Dune','Fiction','1975','9780425027066');
Query OK, 1 row affected (0.010 sec)

MariaDB [publications]> SELECT * FROM classics;
+---------------------+------------------------------+------------+------+---------------+
| author              | title                        | category   | year | isbn          |
+---------------------+------------------------------+------------+------+---------------+
| Charles Dickens     | The Old Curiosity Shop       | Fiction    | 1841 | 9780099533474 |
| William Shakespeare | Romeo and Juliet             | Play       | 1594 | 9780192814968 |
| Frank Herbert       | Dune                         | Fiction    | 1975 | 9780425027066 |
| Charles Darwin      | The Origin of Species        | Nonfiction | 1856 | 9780517123201 |
| Jane Austen         | Pride and Prejudice          | Fiction    | 1811 | 9780582506206 |
| Mark Twain          | The Adventures of Tom Sawyer | Fiction    | 1876 | 9781598184891 |
+---------------------+------------------------------+------------+------+---------------+
6 rows in set (0.000 sec)

We can delete a record with the statement below, using WHERE to filter records:

DELETE FROM classics WHERE author='Frank Herbert';

The LIKE statement searches for strings that start with the word Frank because of % at the end of the word - Frank%.

MariaDB [publications]> SELECT author,title FROM classics WHERE author LIKE'Frank%';
+---------------+-------+
| author        | title |
+---------------+-------+
| Frank Herbert | Dune  |
+---------------+-------+
1 row in set (0.011 sec)

We search for the word any in any position, because of % surrounding the word - %and%.

MariaDB [publications]> SELECT author,title FROM classics WHERE title LIKE'%and%';
+---------------------+---------------------+
| author              | title               |
+---------------------+---------------------+
| William Shakespeare | Romeo and Juliet    |
| Jane Austen         | Pride and Prejudice |
+---------------------+---------------------+
2 rows in set (0.001 sec)

Let’s insert another record:

MariaDB [publications]> INSERT INTO classics(author, title, category, year, isbn)
    -> VALUES('William Shakespeare','Titus Andronicus','Play','1990','9780710813046');
Query OK, 1 row affected (0.012 sec)

MariaDB [publications]> SELECT * FROM classics;
+---------------------+------------------------------+------------+------+---------------+
| author              | title                        | category   | year | isbn          |
+---------------------+------------------------------+------------+------+---------------+
| Charles Dickens     | The Old Curiosity Shop       | Fiction    | 1841 | 9780099533474 |
| William Shakespeare | Romeo and Juliet             | Play       | 1594 | 9780192814968 |
| Frank Herbert       | Dune                         | Fiction    | 1975 | 9780425027066 |
| Charles Darwin      | The Origin of Species        | Nonfiction | 1856 | 9780517123201 |
| Jane Austen         | Pride and Prejudice          | Fiction    | 1811 | 9780582506206 |
| William Shakespeare | Titus Andronicus             | Play       | 1990 | 9780710813046 |
| Mark Twain          | The Adventures of Tom Sawyer | Fiction    | 1876 | 9781598184891 |
+---------------------+------------------------------+------------+------+---------------+
7 rows in set (0.000 sec)

Now we have two books by the same author - William Shakespeare.

MariaDB [publications]> SELECT author FROM classics;
+---------------------+
| author              |
+---------------------+
| Charles Dickens     |
| William Shakespeare |
| Frank Herbert       |
| Charles Darwin      |
| Jane Austen         |
| William Shakespeare |
| Mark Twain          |
+---------------------+
7 rows in set (0.001 sec)

If we want to select only unique records, no duplicates, we use SELECT DISTINCT:

MariaDB [publications]> SELECT DISTINCT author FROM classics;
+---------------------+
| author              |
+---------------------+
| Charles Dickens     |
| William Shakespeare |
| Frank Herbert       |
| Charles Darwin      |
| Jane Austen         |
| Mark Twain          |
+---------------------+
6 rows in set (0.000 sec)

Let’s do a search for a specific author:

MariaDB [publications]> SELECT author,title FROM classics WHERE author="William Shakespeare";
+---------------------+------------------+
| author              | title            |
+---------------------+------------------+
| William Shakespeare | Romeo and Juliet |
| William Shakespeare | Titus Andronicus |
+---------------------+------------------+
2 rows in set (0.014 sec)

We can limit the number of records with the LIMIT statement; it shows only the first three records:

MariaDB [publications]> SELECT author,title FROM classics LIMIT 3;
+---------------------+------------------------+
| author              | title                  |
+---------------------+------------------------+
| Charles Dickens     | The Old Curiosity Shop |
| William Shakespeare | Romeo and Juliet       |
| Frank Herbert       | Dune                   |
+---------------------+------------------------+
3 rows in set (0.008 sec)

We can skip records - LIMIT 1,2 means skip 1 record and show the next 2.

MariaDB [publications]> SELECT author,title FROM classics LIMIT 1,2;
+---------------------+------------------+
| author              | title            |
+---------------------+------------------+
| William Shakespeare | Romeo and Juliet |
| Frank Herbert       | Dune             |
+---------------------+------------------+
2 rows in set (0.008 sec)

Let’s add full text index to the columns author, title.
We can then search

MariaDB [publications]> ALTER TABLE classics ADD FULLTEXT(author,title);
Query OK, 0 rows affected (0.148 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [publications]> SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('and');
+---------------------+---------------------+
| author              | title               |
+---------------------+---------------------+
| William Shakespeare | Romeo and Juliet    |
| Jane Austen         | Pride and Prejudice |
+---------------------+---------------------+
2 rows in set (0.013 sec)

We can use multiple words, and the search is not case sensitive (will find Curiosity Shop even if we search for curiosity shop):

MariaDB [publications]> SELECT author,title FROM classics
    -> WHERE MATCH(author,title) AGAINST('curiosity shop');
+-----------------+------------------------+
| author          | title                  |
+-----------------+------------------------+
| Charles Dickens | The Old Curiosity Shop |
+-----------------+------------------------+
1 row in set (0.000 sec)

We can use boolean mode; we use the operators -(exclude term from search), + (include term in search).
The second is an exact search for the words between quotes:

MariaDB [publications]> SELECT author,title FROM classics
    -> WHERE MATCH(author,title)
    -> AGAINST('+charles -species' IN BOOLEAN MODE);
+-----------------+------------------------+
| author          | title                  |
+-----------------+------------------------+
| Charles Dickens | The Old Curiosity Shop |
+-----------------+------------------------+
1 row in set (0.001 sec)

MariaDB [publications]> SELECT author,title FROM classics
    -> WHERE MATCH(author,title)
    -> AGAINST('"origin of"' IN BOOLEAN MODE);
+----------------+-----------------------+
| author         | title                 |
+----------------+-----------------------+
| Charles Darwin | The Origin of Species |
+----------------+-----------------------+
1 row in set (0.001 sec)

We can use UPDATE...SET to change an existing record:

MariaDB [publications]> UPDATE classics SET category='Classic Fiction'
    -> WHERE category='Fiction';
Query OK, 4 rows affected (0.008 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [publications]> SELECT * from classics;
+---------------------+------------------------------+-----------------+------+---------------+
| author              | title                        | category        | year | isbn          |
+---------------------+------------------------------+-----------------+------+---------------+
| Charles Dickens     | The Old Curiosity Shop       | Classic Fiction | 1841 | 9780099533474 |
| William Shakespeare | Romeo and Juliet             | Play            | 1594 | 9780192814968 |
| Frank Herbert       | Dune                         | Classic Fiction | 1975 | 9780425027066 |
| Charles Darwin      | The Origin of Species        | Nonfiction      | 1856 | 9780517123201 |
| Jane Austen         | Pride and Prejudice          | Classic Fiction | 1811 | 9780582506206 |
| William Shakespeare | Titus Andronicus             | Play            | 1990 | 9780710813046 |
| Mark Twain          | The Adventures of Tom Sawyer | Classic Fiction | 1876 | 9781598184891 |
+---------------------+------------------------------+-----------------+------+---------------+
7 rows in set (0.009 sec)

Let’s use ORDER BY and DESC to order records in descending order:

MariaDB [publications]> SELECT author,title FROM classics ORDER BY title DESC;
+---------------------+------------------------------+
| author              | title                        |
+---------------------+------------------------------+
| William Shakespeare | Titus Andronicus             |
| Charles Darwin      | The Origin of Species        |
| Charles Dickens     | The Old Curiosity Shop       |
| Mark Twain          | The Adventures of Tom Sawyer |
| William Shakespeare | Romeo and Juliet             |
| Jane Austen         | Pride and Prejudice          |
| Frank Herbert       | Dune                         |
+---------------------+------------------------------+
7 rows in set (0.009 sec)

We will do a listing for authors in ascending order (A to Z), and for the publishing date in descending order (newest to older publication year).

MariaDB [publications]> SELECT author,title,year FROM classics ORDER BY author ASC,year DESC;
+---------------------+------------------------------+------+
| author              | title                        | year |
+---------------------+------------------------------+------+
| Charles Darwin      | The Origin of Species        | 1856 |
| Charles Dickens     | The Old Curiosity Shop       | 1841 |
| Frank Herbert       | Dune                         | 1975 |
| Jane Austen         | Pride and Prejudice          | 1811 |
| Mark Twain          | The Adventures of Tom Sawyer | 1876 |
| William Shakespeare | Titus Andronicus             | 1990 |
| William Shakespeare | Romeo and Juliet             | 1594 |
+---------------------+------------------------------+------+
7 rows in set (0.000 sec)

We use GROUP BY to group the results by category in this case:

MariaDB [publications]> SELECT category,COUNT(author) FROM classics GROUP BY category;
+-----------------+---------------+
| category        | COUNT(author) |
+-----------------+---------------+
| Classic Fiction |             4 |
| Nonfiction      |             1 |
| Play            |             2 |
+-----------------+---------------+
3 rows in set (0.010 sec)

Let’s create a new customers table, with names of customers and their purchases (name and isbn fields):

CREATE TABLE customers (
name VARCHAR(128),
isbn VARCHAR(13),
PRIMARY KEY (isbn)) ENGINE InnoDB;
INSERT INTO customers(name,isbn)
VALUES('Eusebia Aguilera','9780425027066');
INSERT INTO customers(name,isbn)
VALUES('Rosa Wiedemann','9780582506206');
INSERT INTO customers(name,isbn)
VALUES('Marinella Molinaro','9780710813046');
SELECT * FROM customers;

MariaDB [publications]> SELECT * FROM customers;
+--------------------+---------------+
| name               | isbn          |
+--------------------+---------------+
| Eusebia Aguilera   | 9780425027066 |
| Rosa Wiedemann     | 9780582506206 |
| Marinella Molinaro | 9780710813046 |
+--------------------+---------------+
3 rows in set (0.012 sec)

We have added three purchases above, and listed the customers table, with the INSERT INTO statement.
The statement can also be used once, with a list of values:

INSERT INTO customers(name,isbn) VALUES
	('Silvia Pop','9780517123201'),
	('Beatrice Roman','9781598184891');

MariaDB [publications]> INSERT INTO customers(name,isbn) VALUES
    -> ('Silvia Pop','9780517123201'),
    -> ('Beatrice Roman','9781598184891');
Query OK, 2 rows affected (0.012 sec)
Records: 2  Duplicates: 0  Warnings: 0

We can join the two tables (classics and customers) by using SELECT:

SELECT name,author,title FROM customers,classics
	WHERE customers.isbn=classics.isbn;

MariaDB [publications]> SELECT name,author,title FROM customers,classics
    -> WHERE customers.isbn=classics.isbn;
+--------------------+---------------------+------------------------------+
| name               | author              | title                        |
+--------------------+---------------------+------------------------------+
| Eusebia Aguilera   | Frank Herbert       | Dune                         |
| Silvia Pop         | Charles Darwin      | The Origin of Species        |
| Rosa Wiedemann     | Jane Austen         | Pride and Prejudice          |
| Marinella Molinaro | William Shakespeare | Titus Andronicus             |
| Beatrice Roman     | Mark Twain          | The Adventures of Tom Sawyer |
+--------------------+---------------------+------------------------------+
5 rows in set (0.011 sec)

We can also use NATURAL JOIN to do achieve the same result:

SELECT name,author,title FROM customers NATURAL JOIN classics;

MariaDB [publications]> SELECT name,author,title FROM customers NATURAL JOIN classics;
+--------------------+---------------------+------------------------------+
| name               | author              | title                        |
+--------------------+---------------------+------------------------------+
| Eusebia Aguilera   | Frank Herbert       | Dune                         |
| Silvia Pop         | Charles Darwin      | The Origin of Species        |
| Rosa Wiedemann     | Jane Austen         | Pride and Prejudice          |
| Marinella Molinaro | William Shakespeare | Titus Andronicus             |
| Beatrice Roman     | Mark Twain          | The Adventures of Tom Sawyer |
+--------------------+---------------------+------------------------------+
5 rows in set (0.009 sec)

We can use JOIN...ON:

SELECT name,author,title FROM customers
	JOIN classics ON customers.isbn=classics.isbn;

MariaDB [publications]> SELECT name,author,title FROM customers
    -> JOIN classics ON customers.isbn=classics.isbn;
+--------------------+---------------------+------------------------------+
| name               | author              | title                        |
+--------------------+---------------------+------------------------------+
| Eusebia Aguilera   | Frank Herbert       | Dune                         |
| Silvia Pop         | Charles Darwin      | The Origin of Species        |
| Rosa Wiedemann     | Jane Austen         | Pride and Prejudice          |
| Marinella Molinaro | William Shakespeare | Titus Andronicus             |
| Beatrice Roman     | Mark Twain          | The Adventures of Tom Sawyer |
+--------------------+---------------------+------------------------------+
5 rows in set (0.001 sec)

We can use aliases, cust instead of customers, class instead of classics:

SELECT name,author,title FROM customers 
	AS cust, classics AS class WHERE cust.isbn=class.isbn;

MariaDB [publications]> SELECT name,author,title FROM customers
    -> AS cust, classics AS class WHERE cust.isbn=class.isbn;
+--------------------+---------------------+------------------------------+
| name               | author              | title                        |
+--------------------+---------------------+------------------------------+
| Eusebia Aguilera   | Frank Herbert       | Dune                         |
| Silvia Pop         | Charles Darwin      | The Origin of Species        |
| Rosa Wiedemann     | Jane Austen         | Pride and Prejudice          |
| Marinella Molinaro | William Shakespeare | Titus Andronicus             |
| Beatrice Roman     | Mark Twain          | The Adventures of Tom Sawyer |
+--------------------+---------------------+------------------------------+
5 rows in set (0.009 sec)

We could use AS to rename a column; in this case, the name column is renamed to New customers:

MariaDB [publications]> SELECT name AS 'New customers' FROM customers ORDER BY 'New customers';
+--------------------+
| New customers      |
+--------------------+
| Eusebia Aguilera   |
| Silvia Pop         |
| Rosa Wiedemann     |
| Marinella Molinaro |
| Beatrice Roman     |
+--------------------+
5 rows in set (0.001 sec)

We can use logical operators such as AND, OR, NOT, to narrow query results:

SELECT author,title FROM classics 
	WHERE author LIKE "Charles%" AND author NOT LIKE "%Darwin";

MariaDB [publications]> SELECT author,title FROM classics
    -> WHERE author LIKE "Charles%" AND author NOT LIKE "%Darwin";
+-----------------+------------------------+
| author          | title                  |
+-----------------+------------------------+
| Charles Dickens | The Old Curiosity Shop |
+-----------------+------------------------+
1 row in set (0.044 sec)

Exporting of a database can be done with mysqldump. For MariaDB, we have mariadb-dump.
The syntax is below; the result is a text file with sql statements that can be executed to recreate and populate the database.

mariadb-dump -u username -ppassword database > database.sql
mariadb-dump -u george -ppassword publications

The resulting file:

/*M!999999\- enable the sandbox mode */
-- MariaDB dump 10.19-11.7.2-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: publications
-- ------------------------------------------------------
-- Server version       11.7.2-MariaDB-ubu2404

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
[..]

--
-- Table structure for table `classics`
--

DROP TABLE IF EXISTS `classics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `classics` (
  `author` varchar(128) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `category` varchar(16) DEFAULT NULL,
  `year` smallint(6) DEFAULT NULL,
  `isbn` char(13) NOT NULL DEFAULT '',
  PRIMARY KEY (`isbn`),
  KEY `author` (`author`(20)),
  KEY `title` (`title`(20)),
  KEY `category` (`category`(4)),
  KEY `year` (`year`),
  FULLTEXT KEY `author_2` (`author`,`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `classics`
--

LOCK TABLES `classics` WRITE;
/*!40000 ALTER TABLE `classics` DISABLE KEYS */;
INSERT INTO `classics` VALUES
('Charles Dickens','The Old Curiosity Shop','Classic Fiction',1841,'9780099533474'),
('William Shakespeare','Romeo and Juliet','Play',1594,'9780192814968'),
('Frank Herbert','Dune','Classic Fiction',1975,'9780425027066'),
('Charles Darwin','The Origin of Species','Nonfiction',1856,'9780517123201'),
('Jane Austen','Pride and Prejudice','Classic Fiction',1811,'9780582506206'),
('William Shakespeare','Titus Andronicus','Play',1990,'9780710813046'),
('Mark Twain','The Adventures of Tom Sawyer','Classic Fiction',1876,'9781598184891');
/*!40000 ALTER TABLE `classics` ENABLE KEYS */;
UNLOCK TABLES;

To restore a database:

% mysql -u user -p testdb < filename.sql

To restore a table in the database:

% mysql -u user -p testdb -D table < filename.sql

To export the tables as comma separated values, which can be imported into Excel:

% mariadb-dump -u root -proot_password --no-create-info --tab=/tmp \
	--fields-terminated-by="," publications

We can list the customers.txt file, which is an export of the customers table:

root@mariadb-host:/# cat /tmp/customers.txt
Eusebia Aguilera,9780425027066
Silvia Pop,9780517123201
Rosa Wiedemann,9780582506206
Marinella Molinaro,9780710813046
Beatrice Roman,9781598184891