Ricerca nel sito web

Impara MySQL/MariaDB per principianti - Parte 1


In questo articolo mostreremo come creare un database (noto anche come schema), tabelle (con tipi di dati) e spiegheremo come eseguire il Data Manipulation Language (DML ) operazioni con dati su un server MySQL/MariaDB.

Si presuppone che tu abbia precedentemente 1) installato i pacchetti necessari sul tuo sistema Linux e 2) eseguito mysql_secure_installation per migliorare la sicurezza del server database . In caso contrario, seguire le guide seguenti per installare il server MySQL/MariaDB.

  1. Installa l'ultimo database MySQL nei sistemi Linux
  2. Installa l'ultimo database MariaDB nei sistemi Linux

Per brevità, in questo articolo faremo riferimento esclusivamente a MariaDB, ma i concetti e i comandi qui delineati si applicano anche a MySQL.

Creazione di database, tabelle e utenti autorizzati

Come sapete, un database può essere definito in termini semplici come una raccolta organizzata di informazioni. In particolare, MariaDB è un sistema di gestione di database relazionali (RDBMS) e utilizza lo Structure Query Language per eseguire operazioni sui database. Inoltre, tieni presente che MariaDB utilizza i termini database e schema in modo intercambiabile.

Per memorizzare informazioni persistenti in un database, utilizzeremo tabelle che memorizzano righe di dati. Spesso due o più tabelle saranno correlate tra loro in qualche modo. Ciò fa parte dell'organizzazione che caratterizza l'utilizzo dei database relazionali.

Creazione di un nuovo database

Per creare un nuovo database denominato BooksDB, inserisci il prompt di MariaDB con il seguente comando (ti verrà richiesto di inserire la password per l'utente root 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 volta creato il database, dobbiamo creare almeno due tabelle al suo interno. Ma prima esploriamo il concetto di tipi di dati.

Presentazione dei tipi di dati MariaDB

Come abbiamo spiegato in precedenza, le tabelle sono oggetti di database in cui conserveremo informazioni persistenti. Ogni tabella è composta da due o più campi (noti anche come colonne) di un determinato tipo di dati (il tipo di informazioni) che tale campo può memorizzare.

I tipi di dati più comuni in MariaDB sono i seguenti (puoi consultare l'elenco completo nella documentazione online ufficiale di MariaDB):

Numerico:
  1. BOOLEAN considera 0 come falso e qualsiasi altro valore come vero.
  2. TINYINT, se utilizzato con SIGNED, copre l'intervallo da -128 a 127, mentre l'intervallo UNSIGNED va da 0 a 255.
  3. SMALLINT, se utilizzato con SIGNED, copre l'intervallo da -32768 a 32767. L'intervallo UNSIGNED va da 0 a 65535.
  4. INT, se utilizzato con UNSIGNED, copre l'intervallo da 0 a 4294967295, altrimenti da -2147483648 a 2147483647.

Nota: in TINYINT, SMALLINT e INT, si presuppone il valore predefinito SIGNED.

DOUBLE(M, D), dove M è il numero totale di cifre e D è il numero di cifre dopo il punto decimale, rappresenta un numero in virgola mobile a doppia precisione. Se viene specificato UNSIGNED, non sono consentiti valori negativi.

Corda :
  1. VARCHAR(M) rappresenta una stringa di lunghezza variabile dove M è la lunghezza massima consentita della colonna in byte (65.535 in teoria). Nella maggior parte dei casi, il numero di byte è identico al numero di caratteri, ad eccezione di alcuni caratteri che possono occupare fino a 3 byte. Ad esempio, la lettera spagnola ñ rappresenta un carattere ma occupa 2 byte.
  2. TEXT(M) rappresenta una colonna con una lunghezza massima di 65.535 caratteri. Tuttavia, come accade con VARCHAR(M), la lunghezza massima effettiva viene ridotta se vengono memorizzati caratteri multibyte. Se viene specificato M, la colonna viene creata come il tipo più piccolo in grado di memorizzare tale numero di caratteri.
  3. MEDIUMTEXT(M) e LONGTEXT(M) sono simili a TEXT(M), solo che le lunghezze massime consentite sono 16.777.215 e 4.294.967.295 caratteri, rispettivamente.
Data e ora:
  1. DATA rappresenta la data nel formato AAAA-MM-GG.
  2. TIME rappresenta l'ora nel formato HH:MM:SS.sss (ora, minuti, secondi e millisecondi).
  3. DATETIME è la combinazione di DATE e TIME nel formato AAAA-MM-GG HH:MM:SS.
  4. TIMESTAMP viene utilizzato per definire il momento in cui una riga è stata aggiunta o aggiornata.

Dopo aver esaminato questi tipi di dati, sarai in una posizione migliore per determinare quale tipo di dati devi assegnare a una determinata colonna in una tabella.

Ad esempio, il nome di una persona può facilmente rientrare in VARCHAR(50), mentre un post di blog avrà bisogno di un tipo TEXT (scegli M come in base alle vostre esigenze specifiche).

Creazione di tabelle con chiavi primarie ed esterne

Prima di immergerci nella creazione delle tabelle, ci sono due concetti fondamentali sui database relazionali che dobbiamo rivedere: chiavi primarie ed estranee.

Una chiave primaria contiene un valore che identifica in modo univoco ogni riga o record nella tabella. D'altra parte, una chiave esterna viene utilizzata per creare un collegamento tra i dati in due tabelle e per controllare i dati che possono essere archiviati nella tabella in cui si trova la chiave esterna. Sia le chiavi primarie che quelle esterne sono generalmente INT.

Per illustrare, utilizziamo BookstoreDB e creiamo due tabelle denominate AuthorsTBL e BooksTBL come segue. Il vincolo NOT NULL indica che il campo associato richiede un valore diverso da NULL.

Inoltre, AUTO_INCREMENT viene utilizzato per aumentare di uno il valore delle colonne chiave primaria INT quando un nuovo record viene inserito nella tabella.

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

Ora possiamo andare avanti e iniziare a inserire i record in AuthorsTBL e BooksTBL.

Selezione, inserimento, aggiornamento ed eliminazione di righe

Per prima cosa popoleremo la tabella AuthorsTBL. Perché? Perché dobbiamo avere valori per AuthorID prima di inserire record in BooksTBL.

Esegui la seguente query dal prompt di MariaDB:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Successivamente, selezioneremo tutti i record da AuthorsTBL. Ricorda che avremo bisogno dell'AuthorID per ciascun record per creare la query INSERT per BooksTBL.

Se desideri recuperare un record alla volta, puoi utilizzare una clausola WHERE per indicare una condizione che una riga deve soddisfare per essere restituita. Per esempio,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

In alternativa è possibile selezionare tutti i record contemporaneamente:

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

Ora creiamo la query INSERT per BooksTBL, utilizzando l'AuthorID corrispondente per abbinare l'autore di ciascun libro. Un valore 1 in BookIsAvailable indica che il libro è disponibile, 0 altrimenti:

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

A questo punto faremo una SELECT per vedere i record in BooksTBL. Quindi AGGIORNA il prezzo di “The Alchemist” di Paulo Coelho e SELEZIONA nuovamente quel disco specifico.

Nota come il campo BookLastUpdated ora mostra un valore diverso. Come abbiamo spiegato in precedenza, un campo TIMESTAMP mostra il valore quando il record è stato inserito o modificato l'ultima volta.

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

Anche se non lo faremo qui, puoi anche eliminare un record se non viene più utilizzato. Ad esempio, supponiamo di voler eliminare “The Alchemist” da BooksTBL.

Per fare ciò, utilizzeremo l'istruzione DELETE come segue:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Come nel caso di UPDATE, è una buona idea fare prima un SELECT per visualizzare i record che potrebbero essere potenzialmente interessati dal ELIMINA.

Inoltre, non dimenticare di aggiungere la clausola WHERE e una condizione (BookID=6) per selezionare il record specifico da rimuovere. Altrimenti corri il rischio di cancellare tutte le righe della tabella!

Se desideri concatenare due (o più) campi, puoi utilizzare l'istruzione CONCAT. Ad esempio, supponiamo di voler restituire un set di risultati costituito da un campo con il nome e l'autore del libro sotto forma di "L'Alchimista (Paulo Coelho)" e un'altra colonna con il prezzo.

Ciò richiederà un JOIN tra AuthorsTBL e BooksTBL sul campo comune condiviso da entrambe le tabelle (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Come possiamo vedere, CONCAT ci consente di unire più espressioni stringa separate da virgole. Noterai anche che abbiamo scelto l'alias Descrizione per rappresentare il set di risultati della concatenazione.

L'output della query precedente è mostrato nell'immagine seguente:

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)

Crea utente per accedere al database BookstoreDB

Usare root per eseguire tutte le operazioni DML in un database è una cattiva idea. Per evitare ciò, possiamo creare un nuovo account utente MariaDB (lo chiameremo bookstoreuser) e assegnare tutte le autorizzazioni necessarie per 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)

Avere un utente dedicato e separato per ciascun database eviterà danni all'intero database nel caso in cui un singolo account venga compromesso.

Suggerimenti aggiuntivi su MySQL

Per cancellare il prompt MariaDB, digita il seguente comando e premi Invio:

MariaDB [BookstoreDB]> \! clear

Per controllare la configurazione di una determinata tabella, eseguire:

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

Per esempio,

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 rapida ispezione rivela che il campo BookIsAvailable ammette valori NULL. Dato che non vogliamo permetterlo, ALTEREREMO la tabella come segue:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Sentiti libero di mostrare nuovamente le colonne: il evidenziato nell'immagine sopra ora dovrebbe essere un NO).

Infine, per visualizzare tutti i database sul tuo server, esegui:

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)

L'immagine seguente mostra il risultato del comando precedente dopo aver effettuato l'accesso al prompt di MariaDB come utentebookstore (nota come questo account non può "vedere" alcun database diverso da BookstoreDB e information_schema (disponibile per tutti gli utenti):

Riepilogo

In questo articolo abbiamo spiegato come eseguire operazioni DML e come creare un database, tabelle e utenti dedicati su un database MariaDB. Inoltre, abbiamo condiviso alcuni suggerimenti che potrebbero semplificarti la vita come amministratore di sistema/database.

  1. Parte sull'amministrazione del database MySQL – 1
  2. Parte amministrazione del database MySQL – 2
  3. Regolazione e ottimizzazione delle prestazioni MySQL – Parte 3

Se hai domande su questo articolo, non esitare a farcelo sapere! Sentiti libero di utilizzare il modulo di commento qui sotto per contattarci.