Impara SQL, il linguaggio essenziale per interagire con i database relazionali, con questo manuale SQL completo per sviluppatori web.
SQL è ovunque in questi giorni. Che tu stia imparando lo sviluppo backend , l'ingegneria dei dati, DevOps o la scienza dei dati , SQL è una competenza che non avrai bisogno di avere nella tua cintura degli strumenti.
Questo è un manuale basato su testo gratuito e aperto. Se vuoi iniziare, scorri verso il basso e inizia a leggere. Detto questo, ci sono altre due opzioni per seguirlo:
Structured Query Language, o SQL , è il linguaggio di programmazione principale utilizzato per gestire e interagire con i database relazionali . SQL può eseguire varie operazioni come la creazione, l'aggiornamento, la lettura e l'eliminazione di record all'interno di un database.
Scriviamo la nostra istruzione SQL da zero. Un'istruzione SELECTè l'operazione più comune in SQL, spesso chiamata "query". SELECTrecupera i dati da una o più tabelle. SELECTLe istruzioni standard non alterano lo stato del database.
SELECT id from users;
Un'istruzione SELECTinizia con la parola chiave SELECTseguita dai campi che desideri recuperare.
SELECT id from users;
Se desideri selezionare più di un campo, puoi specificare più campi separati da virgole in questo modo:
SELECT id, name from users;
Se desideri selezionare tutti i campi di un record, puoi utilizzare la *sintassi abbreviata.
SELECT * from users;
Dopo aver specificato i campi, è necessario indicare da quale tabella si desidera estrarre i record utilizzando l' fromistruzione seguita dal nome della tabella.
Parleremo più approfonditamente delle tabelle in seguito, ma per ora puoi considerarle come strutture o oggetti. Ad esempio, la userstabella potrebbe avere 3 campi:
Infine, tutte le istruzioni terminano con un punto e virgola ;.
SQL è solo un linguaggio di query. In genere lo usi per interagire con una tecnologia di database specifica. Per esempio:
E altri.
Sebbene molti database diversi utilizzino il linguaggio SQL , la maggior parte di essi avrà il proprio dialetto . È fondamentale comprendere che non tutti i database sono uguali. Solo perché un database compatibile con SQL fa le cose in un certo modo, non significa che ogni database compatibile con SQL seguirà esattamente gli stessi schemi.
In questo corso utilizzeremo specificamente SQLite . SQLite è ottimo per progetti incorporati, browser Web e progetti giocattolo. È leggero, ma ha funzionalità limitate rispetto a PostgreSQL o MySQL, due delle tecnologie SQL di produzione più comuni.
E mi assicurerò di segnalarti ogni volta che alcune funzionalità con cui stiamo lavorando sono esclusive di SQLite.
Quando si parla di database SQL, dobbiamo menzionare anche l'elefante nella stanza: NoSQL .
Per dirla semplicemente, un database NoSQL è un database che non utilizza SQL (Structured Query Language). Ogni NoSQL ha in genere il proprio modo di scrivere ed eseguire le query. Ad esempio, MongoDB utilizza MQL (MongoDB Query Language) ed ElasticSearch ha semplicemente un'API JSON.
Sebbene la maggior parte dei database relazionali siano abbastanza simili, i database NoSQL tendono ad essere piuttosto unici e vengono utilizzati per scopi più di nicchia. Alcune delle principali differenze tra un database SQL e NoSQL sono:
Alcuni dei database NoSQL più popolari sono:
Andiamo più a fondo e parliamo di alcuni dei database SQL più diffusi e di cosa li rende diversi l'uno dall'altro. Alcuni dei database SQL più popolari al momento sono:
Fonte: db-engines.com
Sebbene tutti questi database utilizzino SQL, ciascun database definisce regole, pratiche e strategie specifiche che li differenziano dai concorrenti.
Personalmente, SQLite e PostgreSQL sono i miei preferiti dall'elenco sopra. Postgres è un database SQL molto potente, open source e pronto per la produzione. SQLite è un database open source leggero, integrabile. Di solito scelgo una di queste tecnologie se sto eseguendo lavori SQL.
SQLite è un sistema di gestione di database serverless (DBMS) che ha la capacità di essere eseguito all'interno delle applicazioni, mentre PostgreSQL utilizza un modello client-server e richiede che un server sia installato e in ascolto su una rete, simile a un server HTTP.
Vedi un confronto completo qui .
Ancora una volta, in questo corso lavoreremo con SQLite, un database leggero e semplice. Per la maggior parte dei server Web backend , PostgreSQL è un'opzione più pronta per la produzione, ma SQLite è ottimo per l'apprendimento e per i sistemi di piccole dimensioni.
L' CREATE TABLEistruzione viene utilizzata per creare una nuova tabella in un database.
Per creare una tabella, utilizzare l' CREATE TABLEistruzione seguita dal nome della tabella e dai campi desiderati nella tabella.
CREATE TABLE employees (id INTEGER, name TEXT, age INTEGER, is_manager BOOLEAN, salary INTEGER);
Ogni nome di campo è seguito dal relativo tipo di dati. Arriveremo ai tipi di dati tra un minuto.
È anche accettabile e comune interrompere l' CREATE TABLEistruzione con alcuni spazi bianchi come questo:
CREATE TABLE employees(
id INTEGER,
name TEXT,
age INTEGER,
is_manager BOOLEAN,
salary INTEGER
);
Spesso abbiamo bisogno di modificare lo schema del nostro database senza eliminarlo e ricrearlo. Immagina se Twitter cancellasse il suo database ogni volta che avesse bisogno di aggiungere una funzionalità, sarebbe un disastro! Il tuo account e tutti i tuoi tweet verrebbero cancellati quotidianamente.
Invece, possiamo utilizzare l' ALTER TABLEistruzione per apportare modifiche senza eliminare alcun dato.
Con SQLite un'istruzione ALTER TABLEti consente di:
ALTER TABLE employees
RENAME TO contractors;
ALTER TABLE contractors
RENAME COLUMN salary TO invoice;
ALTER TABLE contractors
ADD COLUMN job_title TEXT;
ALTER TABLE contractors
DROP COLUMN is_manager;
Una migrazione del database è un insieme di modifiche a un database relazionale. In effetti, le ALTER TABLEaffermazioni che abbiamo fatto nell’ultimo esercizio erano esempi di migrazioni.
Le migrazioni sono utili quando si passa da uno stato a un altro, si correggono errori o si adatta un database ai cambiamenti.
Le migrazioni valide sono modifiche piccole, incrementali e idealmente reversibili a un database. Come puoi immaginare, quando si lavora con database di grandi dimensioni, apportare modifiche può essere spaventoso. Dobbiamo fare attenzione quando scriviamo le migrazioni del database in modo da non danneggiare i sistemi che dipendono dal vecchio schema del database.
Se un server backend esegue periodicamente una query come SELECT * FROM people, ed eseguiamo una migrazione del database che altera il nome della tabella da peoplea users senza aggiornare il codice , l'applicazione si bloccherà. Tenterà di acquisire dati da una tabella che non esiste più.
Una soluzione semplice a questo problema sarebbe quella di distribuire un nuovo codice che utilizzi una nuova query:
SELECT * FROM users;
E distribuiremo quel codice alla produzione immediatamente dopo la migrazione.
SQL come linguaggio può supportare molti tipi di dati diversi. Ma i tipi di dati supportati dal tuo sistema di gestione del database ( DBMS ) varieranno a seconda del database specifico che stai utilizzando.
SQLite supporta solo i tipi più elementari e in questo corso utilizzeremo SQLite.
Esaminiamo i tipi di dati supportati da SQLite: e come vengono archiviati.
Per esempio:
CREATE TABLE employees (
id INTEGER,
name TEXT,
age INTEGER,
is_manager BOOLEAN,
salary INTEGER
);
È importante notare che SQLite non dispone di una BOOLEANclasse di archiviazione separata. Invece, i valori booleani vengono memorizzati come numeri interi:
In realtà non è poi così strano: dopotutto i valori booleani sono solo bit binari!
SQLite ti consentirà comunque di scrivere le tue query utilizzando booleanespressioni e true/ falseparole chiave, ma convertirà i booleani in numeri interi automaticamente.
A constraintè una regola che creiamo su un database che impone alcuni comportamenti specifici. Ad esempio, l'impostazione di un NOT NULLvincolo su una colonna garantisce che la colonna non accetterà NULLvalori.
Se proviamo a inserire un NULLvalore in una colonna con il NOT NULLvincolo, l'inserimento fallirà con un messaggio di errore. I vincoli sono estremamente utili quando dobbiamo garantire che determinati tipi di dati esistano all'interno del nostro database.
Il NOT NULLvincolo può essere aggiunto direttamente alla CREATE TABLEdichiarazione.
CREATE TABLE employees(
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
title TEXT NOT NULL
);
In altri dialetti SQL è possibile farlo ADD CONSTRAINTall'interno di ALTER TABLEun'istruzione. SQLite non supporta questa funzionalità, quindi quando creiamo le nostre tabelle dobbiamo assicurarci di specificare tutti i vincoli che desideriamo.
Ecco un elenco delle funzionalità SQL che SQLite non implementa nel caso tu sia curioso.
Una chiave definisce e protegge le relazioni tra le tabelle. A primary keyè una colonna speciale che identifica in modo univoco i record all'interno di una tabella. Ogni tabella può avere una e una sola chiave primaria.
È molto comune avere una colonna denominata idsu ciascuna tabella in un database e questa idè la chiave primaria per quella tabella. Non esistono due righe in quella tabella che possano condividere un file id.
PRIMARY KEYÈ possibile specificare esplicitamente un vincolo su una colonna per garantire l'univocità, rifiutando qualsiasi inserimento in cui si tenta di creare un ID duplicato.
Le chiavi esterne sono ciò che rende relazionali i database relazionali! Le chiavi esterne definiscono le relazioni tra le tabelle. In poche parole, a FOREIGN KEYè un campo in una tabella che fa riferimento a un campo di un'altra tabella PRIMARY KEY.
La creazione di un FOREIGN KEYin SQLite avviene durante la creazione della tabella! Dopo aver definito i campi e i vincoli della tabella, ne aggiungiamo uno aggiuntivo CONSTRAINTin cui definiamo the FOREIGN KEYe its REFERENCES.
Ecco un esempio:
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
department_name TEXT NOT NULL
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER,
CONSTRAINT fk_departments
FOREIGN KEY (department_id)
REFERENCES departments(id)
);
In questo esempio, an employeeha un file department_id. Deve department_idessere uguale al idcampo di un record della departmentstabella.
Abbiamo usato la parola schema alcune volte, parliamo di cosa significa quella parola. Lo schema di un database descrive come sono organizzati i dati al suo interno.
I tipi di dati, i nomi delle tabelle, i nomi dei campi, i vincoli e le relazioni tra tutte queste entità fanno parte dello schema di un database .
Quando si progetta uno schema di database in genere non esiste una soluzione "corretta". Facciamo del nostro meglio per scegliere un insieme corretto di tabelle, campi, vincoli, ecc. che consenta di raggiungere gli obiettivi del nostro progetto. Come molte cose nella programmazione, progetti di schemi diversi comportano compromessi diversi.
Una decisione molto importante da prendere è decidere quale tabella memorizzerà il saldo di un utente! Come puoi immaginare, garantire che i nostri dati siano accurati quando si tratta di denaro è estremamente importante. Vogliamo essere in grado di:
Esistono molti modi per affrontare questo problema. Per il nostro primo tentativo, proviamo lo schema più semplice che soddisfa le esigenze del nostro progetto.
CRUD è un acronimo che sta per CREATE, READ, UPDATE, e DELETE. Queste quattro operazioni sono il pane quotidiano di quasi tutti i database che creerai.
Le operazioni CRUD si correlano bene con i metodi HTTP che potresti aver già imparato:
Le tabelle sono piuttosto inutili senza dati al loro interno. In SQL possiamo aggiungere record a una tabella utilizzando INSERT INTOun'istruzione. Quando utilizziamo INSERTun'istruzione dobbiamo prima specificare il punto tablein cui stiamo inserendo il record, seguito dal punto fieldsall'interno della tabella a cui vogliamo aggiungere VALUES.
Ecco un esempio di INSERT INTOdichiarazione:
INSERT INTO employees(id, name, title)
VALUES (1, 'Allan', 'Engineer');
È importante comprendere come i dati fluiscono attraverso una tipica applicazione Web.
Inserire manualmente INSERTogni singolo record in un database sarebbe un compito estremamente dispendioso in termini di tempo! Lavorare con SQL grezzo come lo siamo ora non è molto comune quando si progettano sistemi backend .
Quando lavori con SQL all'interno di un sistema software, come un'applicazione web backend, in genere avrai accesso a un linguaggio di programmazione come Go o Python .
Ad esempio, un server backend scritto in Go può utilizzare la concatenazione di stringhe per creare dinamicamente istruzioni SQL, e di solito è così.
sqlQuery := fmt.Sprintf(`
INSERT INTO users(name, age, country_code)
VALUES ('%s', %v, %s);
`, user.Name, user.Age, user.CountryCode)
L'esempio sopra è una semplificazione eccessiva di ciò che realmente accade quando si accede a un database utilizzando il codice Go. In sostanza è corretto. L'interpolazione delle stringhe è il modo in cui i sistemi di produzione accedono ai database. Detto questo, deve essere fatto con attenzione per non costituire una vulnerabilità della sicurezza . Ne parleremo più avanti!
Possiamo usare un'istruzione SELECTper ottenere il conteggio dei record all'interno di una tabella. Questo può essere molto utile quando abbiamo bisogno di sapere quanti record ci sono, ma non ci interessa particolarmente cosa contengano.
Ecco un esempio in SQLite:
SELECT count(*) from employees;
In questo caso si riferisce al *nome di una colonna. Non ci interessa il conteggio di una colonna specifica: vogliamo conoscere il numero di record totali in modo da poter utilizzare il carattere jolly (*).
Abbiamo parlato di come un'operazione di "creazione" scorre attraverso un'applicazione web. Parliamo di una "lettura".
Parliamo attraverso un esempio. Il nostro product manager desidera mostrare i dati del profilo nella pagina delle impostazioni di un utente. Ecco come potremmo progettare tale richiesta di funzionalità:
Per continuare ad apprendere le operazioni CRUD in SQL, dobbiamo imparare come rendere più specifiche le istruzioni che inviamo al database. SQL accetta WHEREun'istruzione all'interno di una query che ci consente di essere molto specifici con le nostre istruzioni.
Se non fossimo in grado di specificare il record specifico che desideriamo READ, UPDATEo DELETEeseguire query su un database sarebbe molto frustrante e molto inefficiente.
Supponiamo che nella nostra tabella siano presenti oltre 9000 record users. Spesso vogliamo esaminare dati utente specifici all'interno di quella tabella senza recuperare tutti gli altri record nella tabella. Possiamo usare SELECTun'istruzione seguita da una WHEREclausola per specificare quali record recuperare. L' SELECTistruzione rimane la stessa, aggiungiamo semplicemente la WHEREclausola alla fine del file SELECT.
Ecco un esempio:
SELECT name FROM users WHERE power_level >= 9000;
Ciò selezionerà solo il namecampo di qualsiasi utente all'interno della userstabella WHEREil cui power_levelcampo è maggiore o uguale a 9000.
È possibile utilizzare una WHEREclausola per filtrare i valori in base al fatto che siano o meno NULL.
SELECT name FROM users WHERE first_name IS NULL;
SELECT name FROM users WHERE first_name IS NOT NULL;
Quando un utente elimina il proprio account su Twitter o elimina un commento su un video di YouTube, i dati devono essere rimossi dal rispettivo database.
Un'istruzione DELETErimuove un record da una tabella che corrisponde alla WHEREclausola. Come esempio:
DELETE from employees
WHERE id = 251;
Questa DELETEistruzione rimuove tutti i record dalla employeestabella che hanno un ID 251!
L'eliminazione dei dati può essere un'operazione pericolosa. Una volta rimossi, i dati possono essere davvero difficili se non impossibili da ripristinare! Parliamo di un paio di modi comuni con cui gli ingegneri back-end proteggono dalla perdita di preziosi dati dei clienti.
Se utilizzi un servizio cloud come Cloud SQL di GCP o RDS di AWS dovresti sempre attivare i backup automatizzati. Eseguono uno snapshot automatico dell'intero database a un certo intervallo e lo mantengono per un certo periodo di tempo.
Ad esempio, il database Boot.dev dispone di uno snapshot di backup eseguito quotidianamente e conserviamo tali backup per 30 giorni. Se mai eseguo accidentalmente una query che elimina dati importanti, posso ripristinarli dal backup.
Dovresti avere una strategia di backup per i database di produzione.
Una "eliminazione temporanea" avviene quando non si eliminano effettivamente i dati dal database, ma si limitano a "contrassegnare" i dati come eliminati.
Ad esempio, potresti impostare una deleted_atdata sulla riga che desideri eliminare. Quindi, nelle tue query ignori tutto ciò che ha una deleted_atdata impostata. L'idea è che ciò consenta alla tua applicazione di comportarsi come se stesse eliminando dati, ma puoi sempre tornare indietro e ripristinare tutti i dati che sono stati rimossi.
Probabilmente dovresti eseguire l'eliminazione temporanea solo se hai un motivo specifico per farlo. I backup automatizzati dovrebbero essere "abbastanza buoni" per la maggior parte delle applicazioni interessate solo alla protezione dagli errori degli sviluppatori.
Ogni volta che aggiorni l'immagine del tuo profilo o modifichi la password online, stai modificando i dati in un campo su una tabella in un database. Immagina se ogni volta che incasinassi accidentalmente un Tweet su Twitter dovessi eliminare l'intero tweet e pubblicarne uno nuovo invece di limitarti a modificarlo...
...Beh, questo è un cattivo esempio.
L' UPDATEistruzione in SQL ci consente di aggiornare i campi di un record. Possiamo anche aggiornare molti record a seconda di come scriviamo la dichiarazione.
Un'istruzione UPDATEspecifica la tabella che deve essere aggiornata, seguita dai campi e dai relativi nuovi valori utilizzando la SETparola chiave. Infine una WHEREclausola indica il/i record da aggiornare.
UPDATE employees
SET job_title = 'Backend Engineer', salary = 150000
WHERE id = 251;
Un Object-Relational Mapping o in breve ORM , è uno strumento che consente di eseguire operazioni CRUD su un database utilizzando un linguaggio di programmazione tradizionale. Questi in genere si presentano sotto forma di libreria o framework da utilizzare nel codice backend.
Il vantaggio principale offerto da un ORM è che mappa i record del database su oggetti in memoria. Ad esempio, in Go potremmo avere una struttura che utilizziamo nel nostro codice:
type User struct {
ID int
Name string
IsAdmin bool
}
Questa definizione di struttura rappresenta convenientemente una tabella di database chiamata userse un'istanza della struttura rappresenta una riga nella tabella.
Utilizzando un ORM potremmo essere in grado di scrivere codice semplice come questo:
user := User{
ID: 10,
Name: "Lane",
IsAdmin: false,
}
// generates a SQL statement and runs it,
// creating a new record in the users table
db.Create(user)
Utilizzando SQL diretto potremmo dover fare qualcosa di un po' più manuale:
user := User{
ID: 10,
Name: "Lane",
IsAdmin: false,
}
db.Exec("INSERT INTO users (id, name, is_admin) VALUES (?, ?, ?);",
user.ID, user.Name, user.IsAdmin)
Dipende: un ORM in genere baratta la semplicità con il controllo.
Utilizzando SQL diretto puoi sfruttare appieno la potenza del linguaggio SQL. Utilizzando un ORM, sei limitato dalla funzionalità dell'ORM.
Se riscontri problemi con una query specifica, può essere più difficile eseguire il debug con un ORM perché devi analizzare il codice e la documentazione del framework per capire come vengono generate le query sottostanti.
Consiglio di realizzare progetti in entrambi i modi in modo da poter conoscere i compromessi. Alla fine, quando lavori in un team di sviluppatori, sarà una decisione di squadra.
A volte abbiamo bisogno di strutturare i dati che restituiamo dalle nostre query in un modo specifico. Una ASclausola ci consente di "alias" un dato nella nostra query. L'alias esiste solo per la durata della query.
Le seguenti query restituiscono gli stessi dati:
SELECT employee_id AS id, employee_name AS name
FROM employees;
E:
SELECT employee_id, employee_name
FROM employees;
La differenza è che i risultati della query con alias avrebbero nomi di colonna ide nameinvece di employee_ide employee_name.
In fin dei conti, SQL è un linguaggio di programmazione ed è uno che supporta le funzioni. Possiamo utilizzare funzioni e alias per calcolare nuove colonne in una query. Questo è simile a come potresti usare le formule in Excel.
In SQLite, la IIFfunzione funziona come un ternario . Per esempio:
IIF(carA > carB, "Car a is bigger", "Car b is bigger")
Se aè maggiore di b, questa istruzione restituisce la stringa "Car a is bigger". Altrimenti, vale "Car b is bigger".
Ecco come possiamo utilizzare IIF()un directivealias per aggiungere una nuova colonna calcolata al nostro set di risultati:
SELECT quantity,
IIF(quantity < 10, "Order more", "In Stock") AS directive
from products
Possiamo verificare se determinati valori sono betweendue numeri utilizzando la WHEREclausola in modo intuitivo. La WHEREclausola non deve sempre essere utilizzata per specificare ID o valori specifici. Possiamo anche usarlo per restringere il nostro set di risultati. Ecco un esempio:
SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 and 60000;
Questa query restituisce tutti i dipendenti namee salaryi campi per qualsiasi riga in cui salaryè BETWEEN30.000 e 60.000. Possiamo anche interrogare i risultati che sono NOT BETWEENdue valori specificati.
SELECT product_name, quantity
FROM products
WHERE quantity NOT BETWEEN 20 and 100;
Questa query restituisce tutti i nomi di prodotto la cui quantità non è compresa tra 20e 100. Possiamo utilizzare i condizionali per rendere i risultati della nostra query tanto specifici quanto ne abbiamo bisogno.
A volte vogliamo recuperare i record da una tabella senza recuperare alcun duplicato.
Ad esempio, potremmo voler conoscere tutte le diverse aziende in cui i nostri dipendenti hanno lavorato in precedenza, ma non vogliamo vedere la stessa azienda più volte nel report.
SQL ci offre la DISTINCTparola chiave che rimuove i record duplicati dalla query risultante.
SELECT DISTINCT previous_company
FROM employees;
Ciò restituisce solo una riga per ogni previous_companyvalore univoco.
Spesso abbiamo bisogno di utilizzare più condizioni per recuperare le informazioni esatte che desideriamo. Possiamo iniziare a strutturare query molto più complesse utilizzando più condizioni insieme per restringere i risultati di ricerca della nostra query.
L'operatore logico ANDpuò essere utilizzato per restringere ulteriormente i nostri set di risultati.
SELECT product_name, quantity, shipment_status
FROM products
WHERE shipment_status = 'pending'
AND quantity BETWEEN 0 and 10;
Ciò recupera solo i record in cui sia shipment_status"in sospeso" che quantityè compreso tra 0e 10.
Tutti i seguenti operatori sono supportati in SQL. Questa =è la lingua principale a cui prestare attenzione, non è ==come in molte altre lingue.
Ad esempio, in Python potresti confrontare due valori come questo:
if name == "age"
Mentre in SQL faresti:
WHERE name = "age"
Come probabilmente avrai intuito, se l' ANDoperatore logico è supportato, ORprobabilmente lo sarà anche l'operatore.
SELECT product_name, quantity, shipment_status
FROM products
WHERE shipment_status = 'out of stock'
OR quantity BETWEEN 10 and 100;
Questa query recupera i record in cui sono soddisfatti shipping_status conditionOPPURE la condizione.quantity
L'ordine delle operazioni è importante quando si utilizzano questi operatori.
È possibile raggruppare le operazioni logiche con parentesi per specificare l' ordine delle operazioni .
(this AND that) OR the_other
Un'altra variazione alla WHEREclausola che possiamo utilizzare è l' INoperatore. INrestituisce trueo falsese il primo operando corrisponde a uno qualsiasi dei valori nel secondo operando. L' INoperatore è una scorciatoia per più ORcondizioni.
Queste due query sono equivalenti:
SELECT product_name, shipment_status
FROM products
WHERE shipment_status IN ('shipped', 'preparing', 'out of stock');
SELECT product_name, shipment_status
FROM products
WHERE shipment_status = 'shipped'
OR shipment_status = 'preparing'
OR shipment_status = 'out of stock';
Si spera che tu stia iniziando a vedere come l'esecuzione di query su dati specifici utilizzando clausole SQL ottimizzate aiuta a rivelare informazioni importanti. Quanto più grande diventa una tabella, tanto più difficile diventa analizzarla senza query adeguate.
A volte non possiamo permetterci il lusso di sapere esattamente cosa dobbiamo interrogare. Ti è mai capitato di voler cercare una canzone o un video ma ricordi solo parte del nome? SQL ci fornisce un'opzione per quando ci troviamo in situazioni LIKEcome questa.
La LIKEparola chiave consente l'uso degli operatori %e _dei caratteri jolly. Concentriamoci %prima su.
L' %operatore corrisponderà a zero o più caratteri. Possiamo utilizzare questo operatore all'interno della nostra stringa di query per trovare qualcosa di più delle semplici corrispondenze esatte a seconda di dove lo posizioniamo.
Ecco alcuni esempi che mostrano come funzionano:
Il prodotto inizia con "banana":
SELECT * FROM products
WHERE product_name LIKE 'banana%';
Il prodotto termina con "banana":
SELECT * from products
WHERE product_name LIKE '%banana';
Il prodotto contiene "banana":
SELECT * from products
WHERE product_name LIKE '%banana%';
Come discusso, l' %operatore jolly corrisponde a zero o più caratteri. Nel frattempo, l' _operatore jolly corrisponde a un solo carattere.
SELECT * FROM products
WHERE product_name LIKE '_oot';
La query sopra corrisponde a prodotti come:
SELECT * FROM products
WHERE product_name LIKE '__oot';
La query sopra corrisponde a prodotti come:
A volte non vogliamo recuperare tutti i record da una tabella. Ad esempio, è normale che una tabella di database di produzione abbia milioni di righe e SELECTl'esecuzione di tutte potrebbe causare l'arresto anomalo del sistema. È qui che la LIMITparola chiave entra nella chat.
La LIMITparola chiave può essere utilizzata alla fine di un'istruzione select per ridurre il numero di record restituiti.
SELECT * FROM products
WHERE product_name LIKE '%berry%'
LIMIT 50;
La query precedente recupera tutti i record dalla productstabella in cui il nome contiene la parola bacca. Se eseguissimo questa query sul database di Facebook, quasi sicuramente restituirebbe molti record.
L' LIMITistruzione consente al database di restituire solo fino a 50 record che corrispondono alla query. Ciò significa che se non sono presenti molti record che corrispondono alla query, l' LIMITistruzione non avrà effetto.
SQL ci offre anche la possibilità di ordinare i risultati di una query utilizzando i file ORDER BY. Per impostazione predefinita, la ORDER BYparola chiave ordina i record in base al campo specificato in ordine crescente o ASCin breve. Tuttavia, ORDER BYsupporta anche l'ordine discendente con la parola chiave DESC.
Questa query restituisce i campi name, pricee quantitydella productstabella ordinati pricein ordine crescente:
SELECT name, price, quantity FROM products
ORDER BY price;
Questa query restituisce i namevalori , pricee quantitydei prodotti ordinati in base alla quantità in ordine decrescente:
SELECT name, price, quantity FROM products
ORDER BY quantity desc;
Quando si utilizzano sia ORDER BYe LIMIT, la ORDER BYclausola deve venire prima.
Una "aggregazione" è un singolo valore derivato combinando diversi altri valori. Abbiamo eseguito un'aggregazione in precedenza quando abbiamo utilizzato l' countistruzione per contare il numero di record in una tabella.
I dati archiviati in un database dovrebbero generalmente essere archiviati grezzi . Quando dobbiamo calcolare alcuni dati aggiuntivi dai dati grezzi, possiamo utilizzare un'aggregazione.
Prendiamo countcome esempio la seguente aggregazione:
SELECT COUNT(*)
FROM products
WHERE quantity = 0;
Questa query restituisce il numero di prodotti quantitycon 0. Potremmo memorizzare un conteggio dei prodotti in una tabella di database separata e incrementarlo/diminuirlo ogni volta che apportiamo modifiche alla productstabella, ma ciò sarebbe ridondante.
È molto più semplice archiviare i prodotti in un unico posto (lo chiamiamo un'unica fonte di verità ) ed eseguire un'aggregazione quando dobbiamo ricavare informazioni aggiuntive dai dati grezzi.
La sumfunzione di aggregazione restituisce la somma di un insieme di valori.
Ad esempio, la query seguente restituisce un singolo record contenente un singolo campo. Il valore restituito è uguale allo stipendio totale riscosso da tutti i dipendenti employeesnella employeestabella.
SELECT sum(salary)
FROM employees;
Che restituisce:
SOMMA(STIpendio) |
---|
2483 |
Come ci si potrebbe aspettare, la maxfunzione recupera il valore più grande da un insieme di valori. Per esempio:
SELECT max(price)
FROM products
Questa query esamina tutti i prezzi nella productstabella e restituisce il prezzo con il valore di prezzo maggiore. Ricorda che restituisce solo il file price, non il resto del record. È sempre necessario specificare ciascun campo che si desidera venga restituito da una query.
In altre parole, una transazione può avere solo uno sender_ido un recipient_id- non entrambi. La presenza dell'uno o dell'altro indica se il denaro entra o esce dal conto dell'utente.
Questo schema che abbiamo progettato è solo un modo per progettare un database user_iddelle transazioni: esistono altri modi validi per farlo. È quello che stiamo utilizzando e in seguito parleremo più approfonditamente dei compromessi nelle diverse opzioni di progettazione del database.recipient_idsender_id
La minfunzione funziona allo stesso modo della maxfunzione ma trova il valore più basso anziché il valore più alto.
SELECT product_name, min(price)
from products;
Questa query restituisce product_namee i pricecampi del record con il valore più basso price.
A volte è necessario raggruppare i dati in base a valori specifici.
SQL offre la GROUP BYclausola che può raggruppare righe con valori simili in righe di "riepilogo". Restituisce una riga per ogni gruppo. La parte interessante è che a ciascun gruppo può essere applicata una funzione aggregata che opera solo sui dati raggruppati.
Immagina di avere un database con brani e album e di voler vedere quante canzoni sono presenti in ciascun album. Possiamo usare una query come questa:
SELECT album_id, count(song_id)
FROM songs
GROUP BY album_id;
Questa query recupera il conteggio di tutti i brani di ciascun album. Viene restituito un record per album e ciascuno ha il proprio file count.
Proprio come potremmo voler trovare i valori minimi o massimi all'interno di un set di dati, a volte abbiamo bisogno di conoscere la media !
SQL ci offre la AVG()funzione. Simile a MAX(), AVG()calcola la media di tutti i valori diversi da NULL.
select song_name, avg(song_length)
from songs
Questa query restituisce la media song_lengthnella songstabella.
Quando dobbiamo filtrare GROUP BYulteriormente i risultati di una query, possiamo utilizzare la HAVINGclausola. La HAVINGclausola specifica una condizione di ricerca per un gruppo.
La HAVINGclausola è simile alla WHEREclausola, ma opera sui gruppi dopo che sono stati raggruppati, anziché sulle righe prima che siano state raggruppate.
SELECT album_id, count(id) as count
FROM songs
GROUP BY album_id
HAVING count > 5;
Questa query restituisce il album_idnumero e il conteggio dei brani, ma solo per gli album con più di 5brani.
È abbastanza comune che gli sviluppatori si confondano sulla differenza tra le clausole HAVINGe le WHERE- dopo tutto sono abbastanza simili.
La differenza è abbastanza semplice in realtà:
Ciò significa che se desideri filtrare il risultato di un'aggregazione, devi utilizzare HAVING. Se desideri filtrare un valore presente nei dati grezzi, dovresti utilizzare una WHEREclausola semplice.
A volte è necessario arrotondare alcuni numeri, in particolare quando si lavora con i risultati di un'aggregazione. Possiamo usare la ROUND()funzione per portare a termine il lavoro.
La round()funzione SQL ti consente di specificare sia il valore che desideri arrotondare sia la precisione con cui desideri arrotondarlo:
round(value, precision)
Se non viene fornita alcuna precisione, SQL arrotonderà il valore al valore intero più vicino:
select song_name, round(avg(song_length), 1)
from songs
Questa query restituisce la media song_lengthdella songstabella, arrotondata a un singolo punto decimale.
A volte una singola query non è sufficiente per recuperare i record specifici di cui abbiamo bisogno.
È possibile eseguire una query sul set di risultati di un'altra query: una query all'interno di una query! Questa si chiama "query-ception"... ehm... intendo una "sottoquery".
Le sottoquery possono essere molto utili in diverse situazioni quando si tenta di recuperare dati specifici che non sarebbero accessibili semplicemente interrogando una singola tabella.
Ecco un esempio di sottoquery:
SELECT id, song_name, artist_id
FROM songs
WHERE artist_id IN (
SELECT id
FROM artists
WHERE artist_name LIKE 'Rick%'
);
In questo ipotetico database, la query precedente seleziona dalla tabella tutte le song_ids, song_names e s scritte da artisti il cui nome inizia con "Rick". Si noti che la sottoquery ci consente di utilizzare le informazioni di una tabella diversa, in questo caso la tabella.artist_idsongsartists
L'unica sintassi univoca per una sottoquery sono le parentesi che racchiudono la query nidificata. L' INoperatore potrebbe essere diverso, ad esempio potremmo utilizzare l' =operatore se ci aspettiamo che venga restituito un singolo valore.
Ecco un esempio:
SELECT id, song_name, artist_id
FROM songs
WHERE artist_id IN (
SELECT id
FROM artists
WHERE artist_name LIKE 'Rick%'
);
Quando si lavora su un'applicazione back-end, questo non si presenta spesso, ma è importante ricordare che SQL è un linguaggio di programmazione completo . Di solito lo usiamo per interagire con i dati archiviati nelle tabelle, ma è abbastanza flessibile e potente.
Ad esempio, puoi visualizzare SELECTinformazioni semplicemente calcolate, senza la necessità di tabelle.
SELECT 5 + 10 as sum;
I database relazionali sono potenti grazie alle relazioni tra le tabelle. Queste relazioni ci aiutano a mantenere i nostri database puliti ed efficienti.
Una relazione tra tabelle presuppone che una di queste tabelle abbia un foreign keyche fa riferimento a primary keyun'altra tabella.
@youtube _
Esistono 3 tipi principali di relazioni in un database relazionale:
Una one-to-onerelazione si manifesta molto spesso come un campo o un insieme di campi su una riga di una tabella. Ad esempio, a ne useravrà esattamente uno password.
I campi delle impostazioni potrebbero essere un altro esempio di relazione uno a uno. Un utente ne avrà esattamente uno email_preferenceed esattamente uno birthday.
Quando si parla di relazioni tra tabelle, la relazione uno-a-molti è probabilmente la relazione più comunemente utilizzata.
Una relazione uno-a-molti si verifica quando un singolo record in una tabella è correlato a potenzialmente molti record in un'altra tabella.
Nota che la relazione uno->molti va solo in una direzione, un record nella seconda tabella non può essere correlato a più record nella prima tabella!
Una relazione molti-a-molti si verifica quando più record di una tabella possono essere correlati a più record di un'altra tabella.
L'unione di tabelle aiuta a definire relazioni molti-a-molti tra i dati in un database. Ad esempio, quando si definisce la relazione di cui sopra tra prodotti e fornitori, definiremo una tabella di unione chiamata products_suppliersche contiene le chiavi primarie delle tabelle da unire.
Quindi, quando vogliamo vedere se un fornitore fornisce un prodotto specifico, possiamo guardare nella tabella di unione per vedere se gli ID condividono una riga.
Quando si applicano vincoli di schema specifici, potrebbe essere necessario applicare il UNIQUEvincolo su due campi diversi.
CREATE TABLE product_suppliers (
product_id INTEGER,
supplier_id INTEGER,
UNIQUE(product_id, supplier_id)
);
Ciò garantisce che possiamo avere più righe con lo stesso product_ido supplier_id, ma non possiamo avere due righe in cui sia product_ide supplier_idsono uguali.
La normalizzazione del database è un metodo per strutturare lo schema del database in modo che aiuti:
"Integrità dei dati" si riferisce all'accuratezza e alla coerenza dei dati. Ad esempio, se in un database viene memorizzata l'età di un utente anziché la sua data di nascita, i dati diventano automaticamente errati con il passare del tempo.
Sarebbe meglio memorizzare un compleanno e calcolare l'età secondo necessità.
La "ridondanza dei dati" si verifica quando lo stesso dato viene archiviato in più posti. Ad esempio: salvare lo stesso file più volte su dischi rigidi diversi.
La ridondanza dei dati può essere problematica, soprattutto quando i dati in un unico posto vengono modificati in modo tale che i dati non siano più coerenti tra tutte le copie dei dati.
Il creatore della "normalizzazione del database", Edgar F. Codd , ha descritto diverse "forme normali" a cui un database può aderire. Parleremo di quelli più comuni.
In breve, la prima forma normale è la forma meno "normalizzata" e Boyce-Codd è la forma più "normalizzata".
Più un database è normalizzato, migliore sarà l'integrità dei dati e meno dati duplicati avrai.
Nel contesto della normalizzazione del database, utilizzeremo il termine "chiave primaria" in modo leggermente diverso. Quando parliamo di SQLite, una "chiave primaria" è una singola colonna che identifica in modo univoco una riga.
Quando parliamo più in generale di normalizzazione dei dati, con il termine "chiave primaria" si intende l'insieme di colonne che identificano in modo univoco una riga. Può trattarsi di una singola colonna, ma in realtà può essere un numero qualsiasi di colonne. Una chiave primaria è il numero minimo di colonne necessarie per identificare in modo univoco una riga in una tabella.
Se ripensi alla tabella di unione molti-a-molti product_suppliers, la "chiave primaria" di quella tabella era in realtà una combinazione dei 2 ID product_ide supplier_id:
CREATE TABLE product_suppliers (
product_id INTEGER,
supplier_id INTEGER,
UNIQUE(product_id, supplier_id)
);
Per essere conforme alla prima forma normale , una tabella di database deve semplicemente seguire 2 regole:
NOME | ETÀ | |
---|---|---|
Sentiero | 27 | corsia@boot.dev |
Sentiero | 27 | corsia@boot.dev |
Allan | 27 | allan@boot.dev |
Questa tabella non aderisce a 1NF. Ha due righe identiche, quindi non esiste una chiave primaria univoca per ogni riga.
Il modo più semplice (ma non l'unico) per entrare nella prima forma normale è aggiungere una idcolonna univoca.
ID | NOME | ETÀ | |
---|---|---|---|
Primo | Sentiero | 27 | corsia@boot.dev |
2 | Sentiero | 27 | corsia@boot.dev |
3 | Allan | 27 | allan@boot.dev |
Vale la pena notare che se crei una "chiave primaria" assicurandoti che due colonne siano sempre "uniche insieme" anche questo funziona.
La prima forma normale è semplicemente una buona idea. Non ho mai creato uno schema di database in cui ogni tabella non sia almeno nella prima forma normale.
Una tabella in seconda forma normale segue tutte le regole della prima forma normale e una regola aggiuntiva:
In questa tabella, la chiave primaria è una combinazione di first_name+ last_name.
NOME DI BATTESIMO | COGNOME | PRIMA INIZIALE |
---|---|---|
Sentiero | Wagner | l |
Sentiero | Piccolo | l |
Allan | Wagner | UN |
Questa tabella non aderisce a 2NF. La first_initialcolonna dipende interamente dalla first_namecolonna, rendendola ridondante.
Un modo per convertire la tabella precedente in 2NF è aggiungere una nuova tabella che associ first_namedirettamente a al suo file first_initial. Questo rimuove eventuali duplicati:
NOME DI BATTESIMO | COGNOME |
---|---|
Sentiero | Wagner |
Sentiero | Piccolo |
Allan | Wagner |
NOME DI BATTESIMO | PRIMA INIZIALE |
---|---|
Sentiero | l |
Allan | UN |
Probabilmente dovresti mantenere le tue tabelle nella seconda forma normale per impostazione predefinita. Detto questo, ci sono buone ragioni per discostarsi da esso, in particolare per ragioni di prestazioni. Il motivo è che quando si esegue una query su una seconda tabella per ottenere dati aggiuntivi può essere necessario un po' più di tempo.
La mia regola pratica è:
Ottimizza innanzitutto l'integrità e la deduplicazione dei dati. Se hai problemi di velocità, denormalizza di conseguenza.
Una tabella in 3a forma normale segue tutte le regole della 2a forma normale e una regola aggiuntiva:
Si noti che questa è solo leggermente diversa dalla seconda forma normale. Nella seconda forma normale non possiamo avere una colonna completamente dipendente da una parte della chiave primaria, e nella terza forma normale non possiamo avere una colonna che dipenda interamente da qualcosa che non sia l'intera chiave primaria.
In questa tabella la chiave primaria è semplicemente la idcolonna.
ID | NOME | PRIMA INIZIALE | |
---|---|---|---|
Primo | Sentiero | l | corsia.works@esempio.com |
2 | Brenna | B | Breanna@esempio.com |
3 | Sentiero | l | corsia.destra@esempio.com |
Questa tabella è nella seconda forma normale perché first_initialnon dipende da una parte della chiave primaria. Tuttavia, poiché dipende dalla namecolonna, non aderisce alla terza forma normale.
Il modo per convertire la tabella precedente in 3NF è aggiungere una nuova tabella che associ namedirettamente a al suo file first_initial. Nota quanto questa soluzione è simile a 2NF.
ID | NOME | |
---|---|---|
Primo | Sentiero | corsia.works@esempio.com |
2 | Brenna | Breanna@esempio.com |
3 | Sentiero | corsia.destra@esempio.com |
NOME | PRIMA INIZIALE |
---|---|
Sentiero | l |
Brenna | B |
La stessa regola pratica si applica alla seconda e alla terza forma normale.
Ottimizza innanzitutto l'integrità e la deduplicazione dei dati aderendo a 3NF. Se hai problemi di velocità, denormalizza di conseguenza.
Ricorda la funzione IIF e la ASclausola.
Una tabella in forma normale di Boyce-Codd (creata da Raymond F Boyce e Edgar F Codd ) segue tutte le regole della 3a forma normale, più una regola aggiuntiva:
Questo entra in gioco solo quando ci sono più possibili combinazioni di chiavi primarie che si sovrappongono. Un altro nome per questo è "chiavi candidate sovrapposte".
Solo in rari casi una tabella in terza forma normale non soddisfa i requisiti della forma normale di Boyce-Codd.
ANNO DI PUBBLICAZIONE | DATA DI RILASCIO | SALDI | NOME |
---|---|---|---|
2001 | 2001-01-02 | 100 | Baciami teneramente |
2001 | 2001-02-04 | 200 | Bloody Mary |
2002 | 2002-04-14 | 100 | Voglio essere loro |
2002 | 24-06-2002 | 200 | Mi ha preso |
La cosa interessante qui è che ci sono 3 possibili chiavi primarie:
Ciò significa che per definizione questa tabella è nella 2a e 3a forma normale perché tali forme limitano solo la dipendenza di una colonna che non fa parte di una chiave primaria.
Questa tabella non è nella forma normale di Boyce-Codd perché release_yeardipende interamente da release_date.
Il modo più semplice per correggere la tabella nel nostro esempio è semplicemente rimuovere i dati duplicati da release_date. Facciamo quella colonna release_day_and_month.
ANNO DI PUBBLICAZIONE | RILASCIO_DAY_AND_MONTH | SALDI | NOME |
---|---|---|---|
2001 | 01-02 | 100 | Baciami teneramente |
2001 | 02-04 | 200 | Bloody Mary |
2002 | 04-14 | 100 | Voglio essere loro |
2002 | 06-24 | 200 | Mi ha preso |
La stessa regola pratica si applica alle forme normali 2a, 3a e Boyce-Codd. Detto questo, è improbabile che nella pratica si verifichino problemi specifici della BCNF.
Ottimizza innanzitutto l'integrità e la deduplicazione dei dati aderendo alla forma normale di Boyce-Codd. Se hai problemi di velocità, denormalizza di conseguenza.
A mio parere, le definizioni esatte delle forme normali 1a, 2a, 3a e Boyce-Codd semplicemente non sono poi così importanti nel tuo lavoro di sviluppatore back-end.
Tuttavia, ciò che è importante è comprendere i principi di base dell'integrità e della ridondanza dei dati che i moduli normali ci insegnano.
Esaminiamo alcune regole pratiche che dovresti memorizzare: ti saranno utili quando progetterai database e anche solo durante la codifica delle interviste.
Parleremo più approfonditamente dell'ottimizzazione della velocità in un capitolo successivo.
I join sono una delle funzionalità più importanti offerte da SQL. I join ci consentono di utilizzare le relazioni che abbiamo impostato tra le nostre tabelle. In breve, i join ci consentono di interrogare più tabelle contemporaneamente.
Il tipo di join più semplice e comune in SQL è il INNER JOIN. Per impostazione predefinita, un JOINcomando è un file INNER JOIN.
An INNER JOINrestituisce tutti i record in table_ache hanno record corrispondenti in table_b, come dimostrato dal seguente diagramma di Venn.
Per eseguire un'unione, dobbiamo dire al database quali campi devono essere "abbinati". La ONclausola viene utilizzata per specificare queste colonne da unire.
SELECT *
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
La query precedente restituisce tutti i campi di entrambe le tabelle. La INNERparola chiave non ha nulla a che fare con il numero di colonne restituite, influisce solo sul numero di righe restituite.
Quando si lavora con più tabelle, è possibile specificare su quale tabella esiste un campo utilizzando un file .. Per esempio:
table_name.column_name
SELECT students.name, classes.name
FROM students
INNER JOIN classes on classes.class_id = students.class_id;
La query precedente restituisce il namecampo dalla studentstabella e il namecampo dalla classestabella.
A LEFT JOINrestituirà ogni record da table_aindipendentemente dal fatto che qualcuno di questi record abbia o meno una corrispondenza in table_b. Un left join restituirà anche tutti i record corrispondenti da table_b.
Ecco un diagramma di Venn per aiutare a visualizzare l'effetto di un file LEFT JOIN.
Un piccolo trucco che puoi fare per semplificare la scrittura della query SQL è definire un alias per ogni tabella. Ecco un esempio:
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;
Notare le semplici dichiarazioni di alias ee rispettivamente dfor employeese .departments
Alcuni sviluppatori lo fanno per rendere le loro query meno dettagliate. Detto questo, personalmente lo odio perché è più difficile comprendere il significato delle variabili composte da una sola lettera.
A RIGHT JOINè, come puoi aspettarti, l'opposto di a LEFT JOIN. Restituisce tutti i record table_bindipendentemente dalle corrispondenze e tutti i record corrispondenti tra le due tabelle.
SQLite non supporta i right join, ma molti dialetti SQL lo fanno. Se ci pensi, a RIGHT JOINè solo a LEFT JOINcon l'ordine delle tabelle cambiato, quindi non è un grosso problema che SQLite non supporti la sintassi.
A FULL JOINcombina il set di risultati dei comandi LEFT JOINe RIGHT JOIN. Restituisce tutti i record da table_ae table_bindipendentemente dal fatto che abbiano o meno corrispondenze.
Come RIGHT JOINs, SQLite non supporta FULL JOINs ma è comunque importante conoscerli.
Un indice è una struttura in memoria che garantisce che le query che eseguiamo su un database siano performanti, vale a dire che vengano eseguite rapidamente.
Se hai imparato a conoscere le strutture dati, la maggior parte degli indici di database sono solo alberi binari . L'albero binario può essere memorizzato sia nella ram che sul disco e semplifica la ricerca della posizione di un'intera riga.
PRIMARY KEYle colonne sono indicizzate per impostazione predefinita, garantendo la possibilità di cercare una riga idmolto rapidamente. Ma se disponi di altre colonne su cui vuoi poter eseguire ricerche rapide, dovrai indicizzarle.
CREATE INDEX index_name on table_name (column_name);
È abbastanza comune denominare un indice dopo la colonna su cui è stato creato con il suffisso _idx.
Come abbiamo discusso, un indice è una struttura dati in grado di eseguire ricerche rapide. Indicizzando una colonna, creiamo una nuova struttura in memoria, solitamente un albero binario, in cui i valori nella colonna indicizzata vengono ordinati nell'albero per mantenere veloci le ricerche.
In termini di complessità Big-O, un indice ad albero binario garantisce che le ricerche siano O(log(n)) .
Sebbene gli indici rendano molto più rapidi tipi specifici di ricerche, aggiungono anche un sovraccarico in termini di prestazioni e possono rallentare un database in altri modi.
Pensaci: se indicizzi ogni colonna, potresti avere centinaia di alberi binari in memoria. Ciò gonfia inutilmente l'utilizzo della memoria del database. Significa anche che ogni volta che inserisci un record, quel record deve essere aggiunto a molti alberi, rallentando la velocità di inserimento.
La regola pratica è semplice:
Aggiungi un indice alle colonne su cui sai che effettuerai ricerche frequenti. Lascia tutto il resto non indicizzato. Puoi sempre aggiungere indici in un secondo momento.
Gli indici a più colonne sono utili proprio per il motivo che potresti pensare: accelerano le ricerche che dipendono da più colonne.
CREATE INDEX first_name_last_name_age_idx
ON users (first_name, last_name, age);
Un indice a più colonne viene ordinato prima in base alla prima colonna, poi alla seconda e così via. Una ricerca solo sulla prima colonna in un indice a più colonne ottiene quasi tutti i miglioramenti delle prestazioni che otterrebbe dal proprio indice a colonna singola. Ma le ricerche solo sulla seconda o terza colonna avranno prestazioni molto ridotte.
A meno che tu non abbia motivi specifici per fare qualcosa di speciale, aggiungi indici a più colonne solo se effettui ricerche frequenti su una combinazione specifica di colonne.
Vi ho lasciato con un cliffhanger nel capitolo "normalizzazione". A quanto pare, l’integrità dei dati e la deduplicazione hanno un costo, e tale costo è solitamente legato alla velocità.
L'unione di tabelle, l'utilizzo di sottoquery, l'esecuzione di aggregazioni e l'esecuzione di calcoli post-hoc richiedono tempo. Su scala molto ampia, queste tecniche avanzate possono effettivamente comportare un enorme impatto sulle prestazioni di un'applicazione, a volte bloccando il server del database.
L'archiviazione di informazioni duplicate può velocizzare drasticamente un'applicazione che deve cercarle in diversi modi. Ad esempio, se memorizzi le informazioni sul paese di un utente direttamente nel suo record utente, non è necessaria alcuna iscrizione costosa per caricare la pagina del suo profilo.
Detto questo, denormalizza a tuo rischio e pericolo. La denormalizzazione di un database comporta un grande rischio di dati imprecisi e contenenti errori.
A mio avviso, dovrebbe essere utilizzato come una sorta di "ultima risorsa" in nome della velocità.
SQL è un modo molto comune con cui gli hacker tentano di causare danni o violare un database. Uno dei miei fumetti XKCD preferiti di tutti i tempi dimostra il problema:
Lo scherzo qui è che se qualcuno stesse usando questa query:
INSERT INTO students(name) VALUES (?);
E il "nome" di uno studente sarebbe 'Robert'); DROP TABLE students;--quindi la query SQL risultante simile a questa:
INSERT INTO students(name) VALUES ('Robert'); DROP TABLE students;--)
Come puoi vedere, si tratta in realtà di 2 query! Il primo inserisce "Robert" nel database e il secondo cancella la tabella degli studenti!
È necessario essere consapevoli degli attacchi SQL injection, ma a dire il vero la soluzione al giorno d'oggi è semplicemente utilizzare una moderna libreria SQL che disinfetta gli input SQL. Spesso non abbiamo più bisogno di disinfettare manualmente gli ingressi a livello di applicazione.
Ad esempio, i pacchetti SQL della libreria Go standard proteggono automaticamente i tuoi input dagli attacchi SQL se li usi correttamente . In breve, non interpolare tu stesso l'input dell'utente in stringhe non elaborate: assicurati che la libreria del tuo database abbia un modo per disinfettare gli input e passargli quei valori non elaborati.
Se sei interessato a svolgere i compiti e i quiz di codifica interattivi per questo corso, puoi consultare il corso Impara SQL su Boot.dev
Questo corso fa parte del mio percorso completo di carriera da sviluppatore back-end, composto da altri corsi e progetti se sei interessato a verificarli.
Fonte: https://www.freecodecamp.org
#sql