Utilizzo di MySQL con comandi SQL di base

Breve: Impara ad usare i comandi SQL di base nel sistema di gestione di database open source MySQL.

MySQL è il gestore di database relazionale più comunemente usato. La sua facilità d'uso e la licenza open source hanno notevolmente contribuito a questa popolarità.

La settimana scorsa, ho coperto l'installazione e la configurazione di MySQL in Ubuntu. In questo articolo ti presenterò diverse funzionalità MySQL, per lo più attinenti alle nozioni di base .

Nota: le query MySQL (comandi) non fanno distinzione tra maiuscole e minuscole ; tuttavia, è prassi comune utilizzare TUTTI I MAIUSCOLI per le parole chiave di comando effettive e minuscole per il resto .

Comandi Sql di base

Connessione e disconnessione dal server MySQL

Per poter inserire le query, devi prima connetterti al server usando MySQL e utilizzare il prompt MySQL. Il comando per fare questo è:

mysql -h host_name -u user -p 

-h è usato per specificare un nome host (se il server si trova su un'altra macchina, se non lo è, basta ometterlo), -u menziona l' utente e -p specifica che si desidera inserire una password .

Sebbene non raccomandato (per motivi di sicurezza), è possibile inserire la password direttamente nel comando digitandola subito dopo -p . Ad esempio, se la password per test_user è 1234 e si sta tentando di connettersi alla macchina che si sta utilizzando, è possibile utilizzare:

 mysql -u test_user -p1234 

Se hai immesso correttamente i parametri richiesti, verrai accolto dal prompt della shell MySQL ( mysql> ):

Per disconnettersi dal server e lasciare il prompt mysql, digitare:

 QUIT 

Digitando quit (MySQL è case insensitive) o \ q funzionerà anche. Premi Invio per uscire.

Puoi anche generare informazioni sulla versione con un semplice comando:

 sudo mysqladmin -u root version -p 

Nota: assicurati di essere connesso al server prima di inserire una delle query che coprirò.

Se vuoi vedere un elenco di opzioni, usa:

 mysql --help 

Utilizzo di query in MySQL

MySQL memorizza i dati nelle tabelle e utilizza comandi chiamati query ( SQL = linguaggio di query strutturato). Prima di passare alla memorizzazione, all'accesso e alla modifica dei dati, vado a occuparmi delle query di base in modo da farcela.

Poiché MySQL utilizza le tabelle, anche l' output delle query verrà visualizzato nelle tabelle . Tutte le istruzioni SQL devono essere seguite da un punto e virgola ( ; ), sebbene esistano delle eccezioni (in particolare: QUIT). Puoi separare le colonne con una virgola ( , ). Ecco alcuni esempi di base:

 mysql> SELECT VERSION(); mysql> SELECT CURRENT_DATE; mysql> SELECT VERSION(), CURRENT_DATE; 

Ad esempio, la terza query dovrebbe stampare qualcosa di simile a questo:

 +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 8.0.15 | 2019-04-13 | +-----------+--------------+ 1 row in set (0, 41 sec) 

Poiché il punto e virgola ( ; ) segna la fine di un'istruzione, è anche possibile scrivere più istruzioni su una singola riga.

Ad esempio, invece di:

 mysql> SELECT VERSION(); mysql> SELECT CURRENT_DATE; 

Potresti anche scrivere:

 mysql> SELECT VERSION(); SELECT CURRENT_DATE; 

Puoi anche inserire query su più righe (se non includi un punto e virgola alla fine della riga prima di premere Invio ). In tal caso, MySQL inserirà semplicemente un prompt diverso per consentire di continuare il comando. Per esempio:

 mysql> SELECT -> VERSION() ->, -> CURRENT_DATE; 

Le query multilinea si verificano anche se non si termina una stringa su una riga (una parola circondata da " o " ).

Se si desidera annullare una query, digitare \ c e premere Invio .

Ci sono suggerimenti con significati diversi:

  • mysql> = pronto per una nuova query
  • -> = in attesa della riga successiva della query su più righe
  • '> = in attesa della riga successiva, in attesa del completamento di una stringa iniziata con una virgoletta singola ( ' )
  • "> = In attesa della riga successiva, in attesa del completamento di una stringa iniziata con una virgoletta doppia ( " )
  • `> = in attesa della riga successiva, in attesa del completamento di un identificatore iniziato con un apice ( ` )
  • / *> = in attesa della riga successiva, in attesa del completamento di un commento iniziato con /*

È anche possibile stampare l' ora corrente ( hh: mm: ss ) insieme alla data corrente con NOW (), così come l' utente che si è connesso come usando USER () :

 mysql> SELECT NOW(); mysql> SELECT USER(); 

Questo produrrà qualcosa di simile a questo:

 +---------------------+ | NOW() | +---------------------+ | 2019-04-13 23:53:48 | +---------------------+ 1 row in set (0, 00 sec) +----------------+ | USER() | +----------------+ | [email protected] | +----------------+ 1 row in set (0, 00 sec) 

MySQL ti consente anche di calcolare calcoli matematici :

 mysql> SELECT COS(PI()/3), (10-2+4)/3; 

Emissione:

 +--------------------+------------+ | COS(PI()/3) | (10-2+4)/3 | +--------------------+------------+ | 0.5000000000000001 | 4.0000 | +--------------------+------------+ 

Utilizzo di database in MySQL

1. Ottenere informazioni sui database

Prima di tutto, puoi elencare i database disponibili con:

 mysql> SHOW DATABASES; 

Puoi anche vedere il database selezionato con:

 mysql> SELECT DATABASE(); 

Questo produrrà NULL se nessun database è selezionato. Ecco un esempio di output per le due affermazioni citate:

 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ +------------+ | DATABASE() | +------------+ | NULL | +------------+ 

2. Creazione di database

Questo viene fatto semplicemente inserendo un comando:

 mysql> CREATE DATABASE example_db; 

Nota: in Ubuntu 18.04 (o qualsiasi altro sistema basato su Unix) i nomi delle tabelle e dei nomi sono sensibili al maiuscolo / minuscolo .

3. Selezione dei database

Per selezionare un database, devi menzionare che vuoi usarlo :

 mysql> USE example_db; 

In caso di successo, riceverai il messaggio:

 Database changed 

In caso contrario, riceverai un errore che ti dice che MySQL non riesce a trovare il database specificato.

È anche possibile selezionare un database quando ci si connette al server citando il nome di un database esistente alla fine del comando connect:

 mysql -h host_name -u user_name -p example_table 

Per esempio:

 mysql -u root -p example_table 

Utilizzo di tabelle in SQL

1. Ottenere informazioni sulle tabelle

Per elencare le tabelle nel database corrente, utilizzare:

 mysql> SHOW TABLES; 

Nota: assicurarsi di aver selezionato un database.

Se il database è vuoto (ad esempio un nuovo creato), l'output sarà:

 Empty set (0, 00 sec) 

Dopo aver creato le tabelle, l'output sarà lungo la linea di:

 +----------------------+ | Tables_in_example_db | +----------------------+ | table_1 | | table_2 | +----------------------+ 1 row in set (0, 00 sec) 

2. Creazione di tabelle

Per creare tabelle, devi specificare il layout: le colonne e il tipo di dati che devono essere archiviati.

Nel mio esempio, memorizzerò le informazioni su un gruppo di persone: nome, data di nascita, sesso, paese. Ecco come posso creare quella tabella:

 mysql> CREATE TABLE table_1 (name VARCHAR(30), birth_date DATE, sex CHAR(1), country VARCHAR(40)); 

Nota: è anche possibile scrivere il comando su più righe .

Puoi vedere che ho menzionato il nome della tabella ( tabella_1 ) e il nome delle colonne ( nome, data di nascita, sesso, paese ). Dopo i nomi delle colonne, ho specificato il tipo di dati che memorizzano. VARCHAR (n) sono stringhe di un massimo di n caratteri di lunghezza, DATE è auto-esplicativo (formato CCYY-MM-DD ) e CHAR (1) significa un singolo carattere (in particolare, intendo usare 'm' e 'f ' per maschio e femmina ). Altri tipi comuni includono INT (interi), BOOL (booleani), TIME (hh: mm: ss), Ci sono molti tipi di dati disponibili per l'uso in MySQL (numerico, stringa, data e ora). Puoi anche utilizzare tipi di dati più complessi, come AUTO_INCREMENT .

La tabella verrà ora visualizzata se MOSTRA TAVOLI .

Se lo desideri, puoi modificare il layout di una tabella usando ALTER TABLE :

 mysql> ALTER TABLE table_1 ADD email VARCHAR(50); mysql> ALTER TABLE table_1 DROP birth_date; 

Questi esempi hanno aggiunto una colonna (primo esempio) e cancellato una colonna (secondo esempio). Puoi controllare ulteriori informazioni su ALTER TABLE qui, poiché ha anche usi più avanzati.

3. Descrizione delle tabelle

Puoi vedere la struttura di un tavolo in qualsiasi momento con:

 mysql> DESCRIBLE table_name; 

Per esempio:

 mysql> DESCRIBE table_1; 

sta per uscire:

 +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | name | varchar(30) | YES | | NULL | | | birth_date | date | YES | | NULL | | | sex | char(1) | YES | | NULL | | | country | varchar(40) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 4 rows in set (0, 00 sec) 

4. Eliminazione di tabelle

La dichiarazione per l'eliminazione delle tabelle è:

 DROP TABLE table_name; 

5. Inserimento di dati nelle tabelle

Per inserire dati, devi specificare i valori da introdurre in ogni colonna (nello stesso ordine della definizione della tabella). Per valori vuoti o sconosciuti dovresti usare NULL . Assicurati che i valori non numerici siano tra virgolette ( ' ) o doppie virgolette ( " ). I valori dovrebbero essere separati da virgole ( , ).

Ecco alcuni esempi per Smith ed Emily:

 mysql> INSERT INTO table_1 VALUES ('Smith', '1980-04-24', 'm', 'Argentina'); mysql> INSERT INTO table_1 VALUES ('Emily', '1994-07-19', 'f', NULL); 

6. Svuotare le tabelle

Se vuoi svuotare una tabella (cancella tutte le voci), usa:

 DELETE FROM table_name; 

Utilizzando WHERE, puoi eliminare righe specifiche :

 DELETE FROM table_name WHERE col_name = value 

Andrò più in profondità nelle sezioni seguenti.

7. Aggiornamento delle voci di tabella

La sintassi per l' aggiornamento di una voce è:

 UPDATE table_name SET col = 'value' WHERE conditions 

Per esempio:

 UPDATE table_1 SET country = 'France' WHERE name = 'Emily' 

Se non si specificano le condizioni, tutte le voci verranno modificate.

8. Recupero dei dati dalle tabelle

Il comando MySQL utilizzato per estrarre i dati dalle tabelle è SELECT . La struttura di una tale affermazione è:

 SELECT what FROM where WHERE conditions; 

Vedrò alcune applicazioni comuni, in modo che tu possa capire come estrarre esattamente quello che vuoi dal database.

un. Selezione di tutti i dati

Prima di tutto, l'uso più semplice è quello di visualizzare tutti i dati da una tabella . Per esempio:

 mysql> SELECT * FROM table_1; 

Il carattere jolly ( * ) sta per tutto, table_1 è la tabella da cui sto estraendo. Puoi vedere che ho omesso la parte WHERE ; è facoltativo avere condizioni per i dati selezionati.

b. Selezione di dati particolari

Prima di tutto andrò a selezionare le righe .

Per selezionare righe particolari, devi specificare le condizioni che restringono i dati:

 mysql> SELECT * FROM table_1 WHERE name = 'Smith'; mysql> SELECT * FROM table_1 WHERE sex = 'm'; mysql> SELECT * FROM table_1 WHERE birth_date SELECT * FROM table_1 WHERE sex = 'f' AND birth_date > '1991-1-1'; mysql> SELECT * FROM table_1 WHERE sex = 'm' OR country = 'France'; mysql> SELECT * FROM table_1 WHERE country IS NOT NULL; 

Nei primi due esempi, ho semplicemente confrontato stringhe ( maiuscole e minuscole ). Puoi anche confrontare valori come date e numeri interi con operatori di confronto ( >, =, <=, = ). è usato per denotare " non uguale ". È possibile specificare più condizioni utilizzando operatori logici ( AND, OR ). E ha precedenza più alta di OR . È meglio usare le parentesi quando si hanno condizioni più complesse.

IS NOT NULL è un modo per visualizzare solo le righe che non hanno un valore per la colonna specificata. Non è possibile utilizzare gli operatori di confronto aritmetico con NULL, poiché rappresenta un valore mancante (anche il risultato sarà NULL ). È necessario utilizzare IS NULL e IS NOT NULL .

Sia NULL che sono trattati come FALSE, il resto come VERO .

Ora parlerò della visualizzazione di colonne specifiche .

Per fare ciò, devi specificare le colonne che desideri visualizzare, separate da virgole. Per esempio:

 mysql> SELECT name, birth_date FROM table_1; 

Puoi anche sbarazzarti dei dati ripetuti . Ad esempio, se desidero ottenere tutte le date di nascita (senza ottenere lo stesso valore più volte se più persone sono nate in quella data), userò:

 mysql> SELECT DISTINCT birth_date FROM table_1; 

Questo mostrerà solo i risultati DISTINCT .

Per essere ancora più specifici, puoi combinare la visualizzazione di particolari colonne con condizioni ( WHERE ):

 mysql> SELECT name, sex FROM table_1 WHERE country = 'France' AND birth_date < '1991-1-1'; 

c. Ordinamento dei dati

Per ordinare i dati, utilizza ORDER_BY :

 mysql> SELECT name FROM table_1 ORDER BY birth_date; 

Puoi vedere che ho combinato questo con la selezione di dati specifici. Il comando sopra mostrerà i nomi di tutte le voci, ordinate in ordine crescente per data di nascita.

Puoi anche ordinare in ordine decrescente :

 mysql> SELECT name FROM table_1 ORDER BY birth_date DESC; 

L'ordinamento può essere applicato su più colonne . Ad esempio, per ordinare in ordine decrescente per data di nascita, e le persone nate alla stessa data in ordine crescente per nome, vorrei usare:

 mysql> SELECT name FROM table_1 ORDER BY birth_date DESC, name; 

d. Manipolazione delle date

Puoi ottenere la data corrente usando CURDATE () . Usando questo e un altro anno, è possibile calcolare una differenza (ad esempio per ottenere l' età di una persona) con TIMESTAMPDIFF () :

 mysql> SELECT name, birth_date, CURDATE(), -> TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age -> FROM table_1 ORDER BY age; 

Ecco l'output per un esempio di tabella_1 :

 +--------+------------+------------+------+ | name | birth_date | CURDATE() | age | +--------+------------+------------+------+ | Emily | 1994-07-19 | 2019-04-13 | 24 | | Danny | 1992-08-04 | 2019-04-13 | 26 | | Joanna | 1992-08-04 | 2019-04-13 | 26 | | Joe | 1985-03-11 | 2019-04-13 | 34 | | Smith | 1980-04-24 | 2019-04-13 | 38 | +--------+------------+------------+------+ 

TIMESTAMPDIFF () accetta come argomenti l'unità da utilizzare per il risultato (ANNO) e due date (data di nascita, CURDATE ()) per cui calcolare una differenza. La parola chiave AS ( alias) nomina la colonna risultante e facilita il lavoro con (in questo esempio: ordinamento per età).

Per fare riferimento a parti specifiche delle date, è possibile utilizzare YEAR (), MONTH () e DAYOFMONTH (), utilizzando la data come argomento . Per esempio:

 mysql> SELECT name, birth_date, MONTH(birth_date) FROM table_1; 

È possibile confrontare i risultati (mesi, anni, giorni) proprio come i numeri normali. Tuttavia, per confrontarli con cose come il mese successivo, non puoi semplicemente aggiungere a CURDATE (), poiché ciò potrebbe farti controllare per il mese 13 o altro non senso. La soluzione alternativa è INTERVAL e DATE_ADD () :

 mysql> SELECT name, birth_date FROM table_1 WHERE MONTH(birth_date) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)); 

Puoi anche usare l' operatore modulo ( MOD ):

 mysql> SELECT name, birth_date FROM pet WHERE MONTH(birth_date) = MOD(MONTH(CURDATE()), 12) + 1; 

L'utilizzo di date non valide restituirà NULL e produrrà WARNINGS, visto con:

 mysql> SHOW WARNINGS; 

e. Usando la corrispondenza del modello

In MySQL, _ rappresenta qualsiasi carattere singolo e % per caratteri 0+ e i modelli sono (per impostazione predefinita) senza distinzione tra maiuscole e minuscole . Invece di = e , per usare i pattern devi usare LIKE e NOT LIKE :

 mysql> SELECT birth_date FROM table_1 WHERE name LIKE '%a%'; mysql> SELECT birth_date FROM table_1 WHERE name LIKE '%b'; mysql> SELECT birth_date FROM table_1 WHERE name LIKE 'c%'; mysql> SELECT * FROM table_1 WHERE name LIKE '___'; 

Questi esempi selezionano le date di nascita delle voci in cui il nome contiene ' a ' (primo esempio), termina con ' b ' (secondo esempio) o inizia con ' c ' (terzo esempio). L'ultimo esempio seleziona le righe in cui il nome ha esattamente tre caratteri (tre istanze di " _ ").

Puoi anche utilizzare le espressioni regolari estese, con REGEXP_LIKE () (anche gli operatori REGEXP e RLIKE ). Le espressioni regolari vanno oltre lo scopo di questa guida, ma puoi consultare ulteriori informazioni qui.

f. Conteggio dei risultati

Il conteggio dei dati è importante e ha molti usi nel mondo reale. MySQL usa COUNT () per tali compiti. L'esempio più semplice è il conteggio delle voci di una tabella :

 mysql> SELECT COUNT(*) FROM table_1; 

È anche possibile dividere il conteggio tra i gruppi . Ad esempio, potrei GROUP BY paese e visualizzare quante voci sono in ogni paese:

 mysql> SELECT country, COUNT(*) FROM table_1 GROUP BY country; 

Puoi menzionare anche gruppi più specifici inserendo più colonne per raggruppare . Per esempio:

 mysql> SELECT country, sex, COUNT(*) FROM table_1 GROUP BY country, sex; 

Questi risultati potrebbero anche essere utilizzati insieme a WHERE per restringere l'output.

Fai attenzione quando contati. Se specifichi qualcos'altro da stampare oltre a COUNT () e non menzioni quelle colonne dopo GROUP BY, potresti ottenere un errore o risultati imprevisti (vedi ONLY_FULL_GROUP_BY ).

g. Utilizzando più tabelle

Questo è qualcosa che potresti voler fare in un contesto leggermente più complesso.

Ad esempio, immagina che ci sia un'altra tabella ( table_2 ) che memorizza la data ( data ) presso le persone streghe ( nome ) che hanno partecipato alle riunioni ( riunione ).

Puoi mostrare quanti anni hanno le persone in questi tavoli quando hanno partecipato alle riunioni:

 mysql> SELECT table_1.name, TIMESTAMPDIFF(YEAR, birth_date, date) AS age, meeting FROM table_1 INNER JOIN table_2 ON table_1.name = table_2.name; 

Questo produrrà qualcosa come:

 +-------+------+-----------------+ | name | age | meeting | +-------+------+-----------------+ | Emily | 21 | Dog Lovers Club | | Emily | 22 | Dog Lovers Club | | Emily | 23 | Hackathon | | Smith | 36 | TED Talk | | Smith | 38 | Footbal Match | +-------+------+-----------------+ 

Proverò a spiegare la sintassi . Per nome, abbiamo dovuto menzionare table_1.name e table_2.name, poiché la colonna è presente in entrambe le tabelle (per birth_date, date e meeting non è stato necessario specificare la tabella, poiché sono uniche per una di esse). Questo usa le affermazioni che ho già trattato.

La parte interessante è questa:

  FROM table_1 INNER JOIN table_2 ON table_1.name = table_2.name; 

INNER JOIN mette insieme le tabelle e prende la riga che ha qualcosa in comune, una condizione specificata dalla parola chiave ON ; in questo caso, dove i nomi corrispondono.

Nota: puoi anche mettere insieme la stessa tabella con se stessa, magari per confrontare due risultati SELECT.

Utilizzo della modalità batch in MySQL

Un'altra caratteristica utile è la modalità batch . Invece della shell interattiva, puoi mettere le istruzioni in un file ed eseguirle :

 mysql -h host_name -u user_name -p < batch_file 

Ti verrà quindi richiesto di inserire una password (se richiesta per l'utente). Se vuoi continuare a correre invece di fermarti agli errori, usa -force .

È anche possibile reindirizzare l'output su un altro programma o su un file:

 mysql -h host_name -u user_name -p < batch_file | less mysql -h host_name -u user_name -p output_file 

È anche possibile ottenere output interattivo con output -t o echo con parametri -v .

Se si è nella shell MySQL interattiva e si desidera eseguire uno script, utilizzare uno di questi due:

 mysql> source batch_file; mysql> \. batch_file; 

Avvolgendo

In questo articolo, ho trattato diversi modi in cui è possibile utilizzare MySQL per gestire database tabulari, anche approfondendo funzionalità più avanzate .

Sarei felice se questa guida aiuti utenti in difficoltà e principianti. Anche se non sei un principiante, spero che tu legga qualcosa che non sapevi. Se ti ha aiutato, faccelo sapere nei commenti!

Raccomandato

CPod: un'app Podcast open source e multipiattaforma
2019
5 libri di Ubuntu gratuiti per principianti
2019
Come risolvere: No Unity, No Launcher, No Dash In Ubuntu Linux
2019