Lezione

6. SQL: MariaDB

Vediamo, in chiave pratica, come funziona un database SQL. Usiamo la nostra installazione di MariaDB e interagiamo con il database da terminale tramite il client mariadb .

6.1. Premessa

All’avvento delle tecnologie dei database nacquero diverse soluzioni che declinavano la tecnologia in modo diverso e, soprattutto, reciprocamente incompatibile. Finché qualcuno pensò che fosse utile standardizzare un linguaggio comune, neutro, che funzionasse con ogni database: nacque SQL.

L’SQL (Structured Query Language) fu una fantastica idea, ma è una promessa parzialmente tradita. Quasi da subito l’SQL si divide in diversi dialetti. I vari DB implementano potenzialità che non sono usabili tramite l’SQL. Le specifiche SQL, di fatto, non sono così universali come era nelle intenzioni dei padri del linguaggio….

C’è anche da dire, per completezza, che la progressiva diffusione dell’informatica, con l’aumento verticale dell’uso dei DB in soluzioni grandi e complesse ha messo a nudo importanti limiti connessi alle tecnologie SQL. Questo ha dato rapidamente origine a database noti come “no SQL”: il loro scopo e la loro caratteristica è di essere veloci a scapito, però, della rinuncia della tipicizzazione dei dati e della perdita delle relazioni.

Detto tutto ciò al nostro scopo è utile capire la grammatica base dell’SQL e, alla luce di quanto sopra esposto, capire perché alcune applicazioni Usano MySQL, altre MariaDB, altre PostgreSQL, altre DB2, ecc… Non è solo per la specifica licenza d’uso e per i costi che comporta.

Aggiungiamo un altro elemento di teoria molto rilevante. Se qualcuno ha usato Access o un altro DB desktop fondamentalmente si crea un database, con un certo numero di tabelle, ma non abbiamo bisogni di autenticazioni, definizioni dell’utente che lo userà, ecc… in un server, invece, abbiamo bisogno che il software del database gestisca contemporaneamente molti database, molti utenti, permessi diversi, con la possibilità di lavorare su grandi quantità di dati, magari distribuiti su più server e con la necessità di garantire un funzionamento continuo.

Pertanto un software desktop non è adatto. Per questo sono stati progettati applicativi adatti per i server, identificati dal nome DBMS, ovvero “Data Base Management System”: fondamentalmente sono un’installazione monolitica. L’amministratore gestisce gli utenti creandoli e concedendo i permessi. L’amministratore, inoltre, crea i database, li associa agli utenti (se non hanno autorità per crearli e autogestirli), gestisce le configurazioni del server per velocizzarlo e tenerlo in sicurezza, ecc.…

Per i DBMS che incarnano la tecnologia relazionale abbiamo il sottogruppo RDMS in cui troviamo anche gli applicativi MySQL e MariaDB.

Alla luce di questa premessa di teoria per accedere a un database gestito da un RDBMS abbiamo bisogno 4 dati:

il server (=host) su cui si trova l’istanza DB;
l’utente (=user) che ha i permessi di accesso al database;
la password dell’utente del database;
il nome del database.

Passiamo ora alla parte pratica con lo scopo di conoscere l’alfabeto base SQL e degli RDBMS.

Ultima nota: la riga di istruzioni SQL passate all’RDBMS si chiama query.

Per un approfondimento e per l’autoapprendimento rimandiamo alla smisurata guida online della MariaDB e al Tutorial SQL:

Documentation – MariaDB.org ( https://mariadb.org/documentation );
MariaDB Server Documentation, 2024-05, file PDF

( https://mariadb.org/wp-content/uploads/2024/05/MariaDBServerKnowledgeBase.pdf );
GeeksforGeeks | A computer science portal for geeks. SQL Tutorial

( https://www.geeksforgeeks.org/sql-tutorial ).

6.2. Creare un DB

Sfruttiamo l’utente root che il primo amministratore del nostro RDBMS MariaDB. L’istruzione sudo fa in modo che i comandi siano impersonificati dall’utente root :

accediamo al nostro server remoto, creato nel manuale 1, tramite ssh con il nostro utente webadmin;
connettiamoci, ora, a MariaDB tramite il comando mariadb

sudo mariadb -h localhost -u root

dal monitor di MariaDB diamo la query per creare in DB esercizio9

CREATE DATABASE esercizio9;

verifichiamo la creazione del database

show databases;

a questo punto, senza uscire dal monitor di MariaDB passiamo a creare un utente con permessi globali sul database esercizio9 .

6.3. Creare un utente per il DB

Creiamo un utente, con relativa password, con permesso di collegarsi solo dall’interno del server (=localhost) e autorizzazioni globali su database.

L’utente avrà le seguenti impostazioni:

username: esercizio9_user
password: esercizio9_password
host: localhost

Username e password sono didattici. Mentre può essere una buona pratica creare username che hanno come prefisso il database a cui afferiscono, la password invece deve sempre essere seria, non come questa che è solo a scopo didattico.

Procediamo: dal monitor di MariaDB diamo le tre seguenti query:

CREATE USER esercizio9_user@localhost IDENTIFIED BY 'esercizio9_password';
GRANT ALL ON esercizio9.* TO esercizio9_user@localhost WITH GRANT OPTION;
FLUSH PRIVILEGES;

Alcune note esplicative:

la prima query crea l’utente con la password;
la seconda query concede tutti i permessi all’utente esercizio9_user sul database esercizio9;
l’ultima query rende esecutivi i nuovi permessi.

A questo punto usciamo. Procederemo usando il nuovo utente esercizio9_user . Diamo il comando di uscita:

quit;

6.4. Create la tabella “moto” e “colore”

Le query che iniziamo a usare da qui in poi cominciano a diventare un po’ corpose. Può essere una buona idea scriverle dentro Sublime Text e poi copiarle nel monitor di MariaDB.

Colleghiamoci a MariaDB con l’utente esercizio9_user

mariadb -h localhost -u esercizio9_user -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.6.16-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

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)]>

accedere al database esercizio9

NB: MariaDB e MySQL permettono che un utente abbia accesso a più database. Pertanto dopo l’accesso bisogna sempre indicare quale database vogliamo usare

use  esercizio9;

creare la tabelle “colore”

CREATE TABLE colore 
(
    id_colore INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    colore VARCHAR(50) NOT NULL,
    note TEXT
);

creare la tabelle “moto”

CREATE TABLE moto 
(
    id_moto INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    marca VARCHAR(15) NOT NULL,
    modello VARCHAR(50) NOT NULL,
    colore INT,
    note TEXT,
    FOREIGN KEY (colore) REFERENCES colore(id_colore)
);

A questo punto abbiamo pronte le strutture base per inserire dei dati.

6.5. Caricare N entry

Caricheremo per primi i colori, poi le moto che prenderanno il colore dalla tabella colore precedentemente popolata.

Sempre operando dal monitor di MariaDB:

diamo la query per popolare la tabella colore

INSERT INTO colore (colore, note) VALUES 
 ('Nero','Bello'),
 ('Rosso','Molto Mello'),
 ('Giallo','Bellissimo');

diamo la query per popolare la tabella colore

INSERT INTO moto (marca, modello, colore, note) VALUES 
 ('Aprilia', 'Tuono 1100', 1, 'Nota demo 1'),
 ('Aprilia', 'Tuono 660', 2, 'Nota demo 2'),
 ('Aprilia', 'Tuono 125', 3, 'Nota demo 3'),
 ('Aprilia', 'Tuareg 660', 1, 'Nota demo 4'),
 ('Aprilia', 'SXR 50', 2, 'Nota demo 5'),
 ('Aprilia', 'SX 125', 3, 'Nota demo 6'),
 ('Aprilia', 'SR GT 200', 1, 'Nota demo 7'),
 ('Aprilia', 'SR GT 125', 2, 'Nota demo 8'),
 ('Aprilia', 'RSV4', 3, 'Nota demo 9'),
 ('Aprilia', 'RX 125', 1, 'Nota demo 10'),
 ('Aprilia', 'RS 660', 2, 'Nota demo 11'),
 ('Aprilia', 'RS 125', 3, 'Nota demo 12');

A questo punto abbiamo un mini database popolato. Passiamo a fare una ricerca.

6.6. Cercare

Sempre operando dal monitor di MariaDB passiamo delle query di ricerca.

Prima di iniziare diamo note di teoria per comprendere meglio le query che seguiranno:

WHERE : sostanzialmente è l’istruzione cerca;
= : è l’operatore di ricerca. Vengono restituiti i record che hanno il valore identico alla chiave passata;
LIKE : è l’operatore di ricerca. Semplificando: vengono restituiti i record che hanno il valore simile alla chiave passata. Supporta i wildcard;
% : è un carattere wildcard. Sta per: qualsiasi carattere e qualsiasi lunghezza;
_ : è un carattere wildcard. Sta per: un solo carattere qualsiasi.

Per conoscenza è opportuno sapere che MariaDB supporta anche la ricerca full text.

I seguenti esempi di query di ricerca sono corredati anche del risultato che ottenete.

Ricerca con restituzione di tutte le colonne:

SELECT *
  FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
  WHERE moto.modello = "Tuono 660";
+---------+---------+-----------+--------+-------------+-----------+--------+-------------+
| id_moto | marca   | modello   | colore | note        | id_colore | colore | note        |
+---------+---------+-----------+--------+-------------+-----------+--------+-------------+
|       2 | Aprilia | Tuono 660 |      2 | Nota demo 2 |         2 | Rosso  | Molto Mello |
+---------+---------+-----------+--------+-------------+-----------+--------+-------------+
1 row in set (0.001 sec)

Ricerca con restituzione delle sole colonne d'interesse

SELECT moto.marca,moto.modello,colore.colore,moto.note
  FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
  WHERE moto.modello = "Tuono 660";
+---------+-----------+--------+-------------+
| marca   | modello   | colore | note        |
+---------+-----------+--------+-------------+
| Aprilia | Tuono 660 | Rosso  | Nota demo 2 |
+---------+-----------+--------+-------------+
1 row in set (0.001 sec)

Ricerca con criterio wildcard: tutti i modelli 660

SELECT moto.marca,moto.modello,colore.colore,moto.note
  FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
  WHERE moto.modello LIKE "%660";
+---------+------------+--------+--------------+
| marca   | modello    | colore | note         |
+---------+------------+--------+--------------+
| Aprilia | Tuono 660  | Rosso  | Nota demo 2  |
| Aprilia | Tuareg 660 | Nero   | Nota demo 4  |
| Aprilia | RS 660     | Rosso  | Nota demo 11 |
+---------+------------+--------+--------------+
3 rows in set (0.001 sec)

Ricerca con criterio wildcard: tutte le versioni dei modelli Tuono

SELECT moto.marca,moto.modello,colore.colore,moto.note
  FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
  WHERE moto.modello LIKE "Tuono%";
+---------+------------+--------+-------------+
| marca   | modello    | colore | note        |
+---------+------------+--------+-------------+
| Aprilia | Tuono 1100 | Nero   | Nota demo 1 |
| Aprilia | Tuono 660  | Rosso  | Nota demo 2 |
| Aprilia | Tuono 125  | Giallo | Nota demo 3 |
+---------+------------+--------+-------------+
3 rows in set (0.001 sec)

Ricerca con criterio wildcard: tutte le versioni doppi zero di ogni modelli

SELECT moto.marca,moto.modello,colore.colore,moto.note
  FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
  WHERE moto.modello LIKE "%00%";
+---------+------------+--------+-------------+
| marca   | modello    | colore | note        |
+---------+------------+--------+-------------+
| Aprilia | Tuono 1100 | Nero   | Nota demo 1 |
| Aprilia | SR GT 200  | Nero   | Nota demo 7 |
+---------+------------+--------+-------------+
2 rows in set (0.001 sec)

Ricerca con criterio wildcard doppio: tutte le versioni dei modelli S come seconda lettera

SELECT moto.marca,moto.modello,colore.colore,moto.note
  FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
  WHERE moto.modello LIKE "_S %";
+---------+---------+--------+--------------+
| marca   | modello | colore | note         |
+---------+---------+--------+--------------+
| Aprilia | RS 660  | Rosso  | Nota demo 11 |
| Aprilia | RS 125  | Giallo | Nota demo 12 |
+---------+---------+--------+--------------+
2 rows in set (0.001 sec)

6.7. Modificare un record

Nel linguaggio SQL si parla di update di un valore.

Procediamo con due esempi: nel primo rendiamo rosso (id = 2) il modello “Tuono 125”, Nel secondo cambiamo la nota al modello “RS 125”. Riportiamo la query per l’update e subito dopo la query con l’output per vedere l’aggiornamento fatto

cambiamo il colore al modello “Tuono 125”

UPDATE moto SET colore = '2' WHERE modello = 'Tuono 125';
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1  Changed: 0  Warnings: 0

SELECT moto.marca,moto.modello,colore.colore,moto.note
 FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
 WHERE moto.modello = 'Tuono 125';
+---------+---------+--------+--------------+
| marca   | modello | colore | note         |
+---------+---------+--------+--------------+
| Aprilia | RS 125  | Rosso  | Nota demo 12 |
+---------+---------+--------+--------------+
1 row in set (0.001 sec)

cambiamo il colore al modello “RS 125”

UPDATE moto SET note = 'Nuova nota per RS 125' WHERE modello = 'RS 125';
Query OK, 0 rows affected (0.016 sec)
Rows matched: 1  Changed: 0  Warnings: 0

SELECT moto.marca,moto.modello,colore.colore,moto.note
 FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore
 WHERE moto.modello = 'RS 125';
+---------+---------+--------+-----------------------+
| marca   | modello | colore | note                  |
+---------+---------+--------+-----------------------+
| Aprilia | RS 125  | Giallo | Nuova nota per RS 125 |
+---------+---------+--------+-----------------------+
1 row in set (0.001 sec)

6.8. Cancellare un record

L’ultima azione che ci serve vedere è la cancellazione di un record. L’istruzione SQL è delete .

Procediamo con la cancellazione del modello RSV4, poi di tutti i modelli Tuono e poi, con una sola azione, di tutti i modelli ancora presenti nella tabella moto . Operiamo sempre dal monitor di MariaDB.

Iniziamo chiedendo l’elenco completo delle nostre moto

SELECT moto.id_moto,moto.marca,moto.modello,colore.colore,moto.note 
 FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore;
+---------+---------+------------+--------+-----------------------+
| id_moto | marca   | modello    | colore | note                  |
+---------+---------+------------+--------+-----------------------+
|       1 | Aprilia | Tuono 1100 | Nero   | Nota demo 1           |
|       2 | Aprilia | Tuono 660  | Rosso  | Nota demo 2           |
|       3 | Aprilia | Tuono 125  | Rosso  | Nota demo 3           |
|       4 | Aprilia | Tuareg 660 | Nero   | Nota demo 4           |
|       5 | Aprilia | SXR 50     | Rosso  | Nota demo 5           |
|       6 | Aprilia | SX 125     | Giallo | Nota demo 6           |
|       7 | Aprilia | SR GT 200  | Nero   | Nota demo 7           |
|       8 | Aprilia | SR GT 125  | Rosso  | Nota demo 8           |
|       9 | Aprilia | RSV4       | Giallo | Nota demo 9           |
|      10 | Aprilia | RX 125     | Nero   | Nota demo 10          |
|      11 | Aprilia | RS 660     | Rosso  | Nota demo 11          |
|      12 | Aprilia | RS 125     | Giallo | Nuova nota per RS 125 |
+---------+---------+------------+--------+-----------------------+
12 rows in set (0.001 sec)

diamo il comando di cancellazione del modello RSV4

DELETE FROM moto WHERE modello = 'RS 125';
Query OK, 1 row affected (0.016 sec)

visualizziamo l’elenco aggiornato della moto

SELECT moto.id_moto,moto.marca,moto.modello,colore.colore,moto.note 
 FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore;
+---------+---------+------------+--------+--------------+
| id_moto | marca   | modello    | colore | note         |
+---------+---------+------------+--------+--------------+
|       1 | Aprilia | Tuono 1100 | Nero   | Nota demo 1  |
|       2 | Aprilia | Tuono 660  | Rosso  | Nota demo 2  |
|       3 | Aprilia | Tuono 125  | Rosso  | Nota demo 3  |
|       4 | Aprilia | Tuareg 660 | Nero   | Nota demo 4  |
|       5 | Aprilia | SXR 50     | Rosso  | Nota demo 5  |
|       6 | Aprilia | SX 125     | Giallo | Nota demo 6  |
|       7 | Aprilia | SR GT 200  | Nero   | Nota demo 7  |
|       8 | Aprilia | SR GT 125  | Rosso  | Nota demo 8  |
|       9 | Aprilia | RSV4       | Giallo | Nota demo 9  |
|      10 | Aprilia | RX 125     | Nero   | Nota demo 10 |
|      11 | Aprilia | RS 660     | Rosso  | Nota demo 11 |
+---------+---------+------------+--------+--------------+
11 rows in set (0.001 sec)

diamo il comando di cancellazione di tutti i modelli Tuono

DELETE FROM moto WHERE modello like 'Tuono %';
Query OK, 3 rows affected (0.017 sec)

visualizziamo l’elenco aggiornato della moto

SELECT moto.id_moto,moto.marca,moto.modello,colore.colore,moto.note 
 FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore;
+---------+---------+------------+--------+--------------+
| id_moto | marca   | modello    | colore | note         |
+---------+---------+------------+--------+--------------+
|       4 | Aprilia | Tuareg 660 | Nero   | Nota demo 4  |
|       5 | Aprilia | SXR 50     | Rosso  | Nota demo 5  |
|       6 | Aprilia | SX 125     | Giallo | Nota demo 6  |
|       7 | Aprilia | SR GT 200  | Nero   | Nota demo 7  |
|       8 | Aprilia | SR GT 125  | Rosso  | Nota demo 8  |
|       9 | Aprilia | RSV4       | Giallo | Nota demo 9  |
|      10 | Aprilia | RX 125     | Nero   | Nota demo 10 |
|      11 | Aprilia | RS 660     | Rosso  | Nota demo 11 |
+---------+---------+------------+--------+--------------+
8 rows in set (0.001 sec)

diamo il comando di cancellazione di tutte le moto rimaste nella tabella

DELETE FROM moto;
Query OK, 8 rows affected (0.015 sec)

visualizziamo l’elenco aggiornato della moto

SELECT moto.id_moto,moto.marca,moto.modello,colore.colore,moto.note 
 FROM moto LEFT JOIN colore ON moto.colore = colore.id_colore;
Empty set (0.001 sec)

Abbiamo svuotato tutta la tabella moto con una sola istruzione.

6.9. Uscita dal RDBMS

Ricordiamo che dobbiamo sempre dal monitor del nostro database, così da chiudere la connessione.

Il comando, lo abbiamo visto all’inizio, è il seguente: