SQL per sviluppatori Web: Un manuale completo

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:

1. Prova la versione interattiva di questo corso SQL su Boot.dev , completo di sfide e progetti di codifica
2. Guarda il video dettagliato di questo corso sul canale YouTube di FreeCodeCamp (incorporato di seguito):

Sommario

• Capitolo 1 introduzione
• Capitolo 2: Tabelle SQL
• Capitolo 3: Vincoli
• Capitolo 4: Operazioni CRUD
• Capitolo 5: Query SQL di base
• Capitolo 6: Come strutturare i dati restituiti in SQL
• Capitolo 7: Come eseguire aggregazioni in SQL
• Capitolo 8: Sottoquery SQL
• Capitolo 9: Normalizzazione del database
• Capitolo 10: Come unire tabelle in SQL
• Capitolo 11: Prestazioni del database

Capitolo 1 introduzione

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.

Cos'è un'istruzione SQL Select?

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;

Come selezionare un singolo campo

Un'istruzione SELECTinizia con la parola chiave SELECTseguita dai campi che desideri recuperare.

SELECT id from users;

Come selezionare più campi

Se desideri selezionare più di un campo, puoi specificare più campi separati da virgole in questo modo:

SELECT id, name from users;

Come selezionare tutti i campi

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:

• id
• name
• balance

Infine, tutte le istruzioni terminano con un punto e virgola ;.

Quali database utilizzano SQL?

SQL è solo un linguaggio di query. In genere lo usi per interagire con una tecnologia di database specifica. Per esempio:

• SQLite
• PostgreSQL
• MySQL
• Scarafaggio DB
• Oracolo

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.

Stiamo usando SQLite

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.

NoSQL contro SQL

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:

1. I database NoSQL sono generalmente non relazionali, i database SQL sono generalmente relazionali (parleremo più avanti di cosa significa).
2. I database SQL di solito hanno uno schema definito, i database NoSQL di solito hanno uno schema dinamico.
3. I database SQL sono basati su tabelle, i database NoSQL hanno una varietà di metodi di archiviazione diversi, come documenti, valori-chiave, grafici, colonne larghe e altro.

Tipi di database NoSQL

• Banca dati dei documenti
• Archivio valori-chiave
• Colonna larga
• Grafico

Alcuni dei database NoSQL più popolari sono:

• MongoDB
• Cassandra
• CouchDB
• DynamoDB
• Ricerca elastica

Confronto tra database SQL

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:

• PostgreSQL
• MySQL
• Microsoft SQL Server
• SQLite
• E molti altri

Fonte: db-engines.com

Sebbene tutti questi database utilizzino SQL, ciascun database definisce regole, pratiche e strategie specifiche che li differenziano dai concorrenti.

SQLite contro PostgreSQL

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.

Capitolo 2: Tabelle SQL

L' CREATE TABLEistruzione viene utilizzata per creare una nuova tabella in un database.

Come utilizzare la CREATE TABLEdichiarazione

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

Come modificare le tabelle

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.

Come usareALTER TABLE

Con SQLite un'istruzione ALTER TABLEti consente di:

1. Rinominare una tabella o una colonna, cosa che puoi fare in questo modo:

ALTER TABLE employees
RENAME TO contractors;

ALTER TABLE contractors
RENAME COLUMN salary TO invoice;

1. AGGIUNGI o ELIMINA una colonna, cosa che puoi fare in questo modo:

ALTER TABLE contractors
ADD COLUMN job_title TEXT;

ALTER TABLE contractors
DROP COLUMN is_manager;

Introduzione alle migrazioni

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.

Esempio di migrazione errata

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.

Tipi di dati SQL

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.

Tipi di dati SQLite

Esaminiamo i tipi di dati supportati da SQLite: e come vengono archiviati.

1. NULL- Valore nullo.
2. INTEGER- Un intero con segno memorizzato in 0,1,2,3,4,6 o 8 byte.
3. REAL- Valore in virgola mobile memorizzato come numero in virgola mobile IEEE a 64 bit .
4. TEXT- Stringa di testo memorizzata utilizzando la codifica del database come UTF-8
5. BLOB- Abbreviazione di oggetto binario di grandi dimensioni e generalmente utilizzato per immagini, audio o altri contenuti multimediali.

Per esempio:

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    age INTEGER,
    is_manager BOOLEAN,
    salary INTEGER
);

Valori booleani

È importante notare che SQLite non dispone di una BOOLEANclasse di archiviazione separata. Invece, i valori booleani vengono memorizzati come numeri interi:

• 0=false
• 1=true

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.

Capitolo 3: Vincoli

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.

Vincolo NOT NULL

Il NOT NULLvincolo può essere aggiunto direttamente alla CREATE TABLEdichiarazione.

CREATE TABLE employees(
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    title TEXT NOT NULL
);

Limitazione di SQLite

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.

Vincoli della chiave primaria

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.

La tua chiave primaria sarà quasi sempre la colonna "id".

È 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.

Vincoli di chiave esterna

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.

Creazione di una chiave esterna in SQLite

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.

Schema

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 .

Non esiste un modo perfetto per progettare uno schema di 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.

Come decidiamo su un'architettura di schema sana?

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:

• Tieni traccia del saldo attuale di un utente
• Visualizza il saldo storico in qualsiasi momento del passato
• Visualizza un registro di quali transazioni hanno modificato il saldo nel tempo

Esistono molti modi per affrontare questo problema. Per il nostro primo tentativo, proviamo lo schema più semplice che soddisfa le esigenze del nostro progetto.

Capitolo 4: Operazioni CRUD in SQL

Cos'è il CRUD?

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.

HTTP e CRUD

Le operazioni CRUD si correlano bene con i metodi HTTP che potresti aver già imparato:

• HTTP POST-CREATE
• HTTP GET-READ
• HTTP PUT-UPDATE
• HTTP DELETE-DELETE

Istruzione di inserimento SQL

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');

Ciclo di vita del database HTTP CRUD

È importante comprendere come i dati fluiscono attraverso una tipica applicazione Web.

1. Il front-end elabora alcuni dati dall'input dell'utente, forse viene inviato un modulo.
2. Il front-end invia i dati al server tramite una richiesta HTTP, magari un file POST.
3. Il server effettua una query SQL sul suo database per creare un record associato, probabilmente utilizzando un'istruzione INSERT.
4. Una volta che il server ha accertato che la query del database ha avuto esito positivo, risponde al front-end con un codice di stato! Si spera in un codice di livello 200 (successo)!

Inserimento manuale

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)

SQL Injection

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!

Contare

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 (*).

Ciclo di vita del database HTTP CRUD

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à:

1. Innanzitutto, viene caricata la pagina Web front-end.
2. Il front-end invia una GETrichiesta HTTP a un /usersendpoint sul server back-end.
3. Il server riceve la richiesta.
4. Il server utilizza SELECTun'istruzione per recuperare il record dell'utente dalla userstabella nel database.
5. Il server converte la riga di dati SQL in un JSONoggetto e lo rimanda al front-end.

Dove la clausola

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.

Utilizzando una clausola WHERE

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.

Trovare valori NULL

È possibile utilizzare una WHEREclausola per filtrare i valori in base al fatto che siano o meno NULL.

È ZERO

SELECT name FROM users WHERE first_name IS NULL;

NON È NULLO

SELECT name FROM users WHERE first_name IS NOT NULL;

ELIMINARE

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.

Dichiarazione DELETE

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!

Il pericolo di cancellare i dati

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.

Strategia 1 - Backup

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.

Strategia 2: eliminazioni soft

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.

Aggiorna la query in SQL

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.

Dichiarazione di aggiornamento

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;

Mappatura relazionale oggetto (ORM)

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.

Esempio: utilizzo di un ORM

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)

Esempio: utilizzo di SQL diretto

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)

Dovresti usare un ORM?

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.

Capitolo 5: Query SQL di base

Come utilizzare la ASclausola in SQL

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.

ASparola chiave

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.

Funzioni SQL

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.

Funzione IIF

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

Come utilizzare BETWEENconWHERE

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.

Come restituire valori distinti

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.

SELECT DISTINCT

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.

Operatori logici

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.

ANDoperatore

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.

Operatori di uguaglianza

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"

ORoperatore

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

L' INoperatore

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.

La LIKEparola chiave

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.

%Operatore

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%';

Operatore di sottolineatura

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:

• stivale
• radice
• piede

SELECT * FROM products
    WHERE product_name LIKE '__oot';

La query sopra corrisponde a prodotti come:

• sparare
• grande

Capitolo 6: Come strutturare i dati restituiti in SQL

La LIMITparola chiave

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.

La parola chiave SQLORDER BY

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.

Esempi

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;

Ordina per e limite

Quando si utilizzano sia ORDER BYe LIMIT, la ORDER BYclausola deve venire prima.

Capitolo 7: Come eseguire aggregazioni in SQL

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.

Perché utilizzare le aggregazioni?

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

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:

La MAXfunzione

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.

Una nota sullo schema

• Sarà sender_idpresente per tutte le transazioni in cui l'utente in questione ( user_id) riceve denaro (dal mittente).
• Sarà recipient_idpresente per tutte le transazioni in cui l'utente in questione ( user_id) invia denaro (al destinatario).

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

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.

La GROUP BYclausola

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.

Esempio diGROUP BY

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.

La AVG()funzione

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.

La HAVINGclausola

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.

HAVINGrispetto WHEREa SQL

È 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à:

• Una WHEREcondizione viene applicata a tutti i dati in una query prima che vengano raggruppati in una GROUP BYclausola.
• Una HAVINGcondizione viene applicata solo alle righe raggruppate restituite dopo GROUP BYl'applicazione di a.

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.

La ROUNDfunzione

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.

Capitolo 8: Sottoquery SQL

Sottoquery

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.

Come recuperare i dati da più tabelle

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

Sintassi della sottoquery

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%'
);

Non sono necessarie tabelle

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;

Capitolo 9: Normalizzazione del database

Relazioni tra tabelle

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 _

Tipi di relazioni

Esistono 3 tipi principali di relazioni in un database relazionale:

1. Uno a uno
2. Uno a molti
3. Molti-a-molti

Uno a uno

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.

Uno a molti

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!

Esempi di relazioni uno-a-molti

• Un customerstavolo e un orderstavolo. Ogni cliente ha 0, 1o molti ordini che ha effettuato.
• Un userstavolo e un transactionstavolo. Ciascuno userha 0, 1, o molte transazioni a cui hanno preso parte.

Molti a molti

Una relazione molti-a-molti si verifica quando più record di una tabella possono essere correlati a più record di un'altra tabella.

Esempi di relazioni molti-a-molti

• Una productstabella e una supplierstabella: i prodotti possono avere 0molti fornitori e i fornitori possono fornire 0molti prodotti.
• Una classestabella e una studentstabella: gli studenti possono frequentare potenzialmente molte classi e le classi possono avere molti studenti iscritti.

Unione di tabelle

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.

Vincoli univoci su 2 campi

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.

Normalizzazione del database

La normalizzazione del database è un metodo per strutturare lo schema del database in modo che aiuti:

• Migliorare l'integrità dei dati
• Ridurre la ridondanza dei dati

Cos'è l'integrità dei dati?

"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à.

Cos'è la ridondanza dei dati?

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.

Forme normali

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.

• Prima forma normale (1NF)
• Seconda forma normale (2NF)
• Terza forma normale (3NF)
• Forma normale di Boyce-Codd (BCNF)

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 delle forme normali, "chiave primaria" significa qualcosa di leggermente diverso

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

1a forma normale (1NF)

Per essere conforme alla prima forma normale , una tabella di database deve semplicemente seguire 2 regole:

• Deve avere una chiave primaria univoca.
• Una cella non può avere una tabella nidificata come valore (a seconda del database che stai utilizzando, ciò potrebbe non essere nemmeno possibile)

Esempio di NON 1a forma normale

Questa tabella non aderisce a 1NF. Ha due righe identiche, quindi non esiste una chiave primaria univoca per ogni riga.

Esempio della 1a forma normale

Il modo più semplice (ma non l'unico) per entrare nella prima forma normale è aggiungere una idcolonna univoca.

Vale la pena notare che se crei una "chiave primaria" assicurandoti che due colonne siano sempre "uniche insieme" anche questo funziona.

Non dovresti quasi mai progettare una tabella che non aderisce a 1NF

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.

2a forma normale (2NF)

Una tabella in seconda forma normale segue tutte le regole della prima forma normale e una regola aggiuntiva:

• Tutte le colonne che non fanno parte della chiave primaria dipendono dall'intera chiave primaria e non solo da una delle colonne nella chiave primaria.

Esempio di 1a NF, ma non di 2a NF

In questa tabella, la chiave primaria è una combinazione di first_name+ last_name.

Questa tabella non aderisce a 2NF. La first_initialcolonna dipende interamente dalla first_namecolonna, rendendola ridondante.

Esempio di 2a forma normale

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:

2NF è solitamente una buona idea

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.

3a forma normale (3NF)

Una tabella in 3a forma normale segue tutte le regole della 2a forma normale e una regola aggiuntiva:

• Tutte le colonne che non fanno parte della chiave primaria dipendono esclusivamente dalla chiave primaria.

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.

Esempio di 2a NF, ma non di 3a NF

In questa tabella la chiave primaria è semplicemente la idcolonna.

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.

Esempio di 3a 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.

3NF è solitamente una buona idea

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.

Forma normale di Boyce-Codd (BCNF)

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:

• Una colonna che fa parte di una chiave primaria non può dipendere interamente da una colonna che non fa parte di quella chiave primaria.

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.

Esempio di 3a NF, ma non Boyce-Codd NF

La cosa interessante qui è che ci sono 3 possibili chiavi primarie:

• release_year+sales
• release_date+sales
• name

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.

Esempio di forma normale di Boyce-Codd

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.

BCNF è solitamente una buona idea

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.

Revisione della normalizzazione

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.

Regole pratiche per la progettazione di database

1. Ogni tabella dovrebbe sempre avere un identificatore univoco (chiave primaria)
2. Nel 90% dei casi, l'identificatore univoco sarà costituito da una singola colonna denominataid
3. Evita dati duplicati
4. Evitare di archiviare dati che dipendono completamente da altri dati. Invece, calcolalo al volo quando ne hai bisogno.
5. Mantieni il tuo schema il più semplice possibile. Ottimizzare prima per un database normalizzato. Denormalizza solo per motivi di velocità quando inizi a riscontrare problemi di prestazioni.

Parleremo più approfonditamente dell'ottimizzazione della velocità in un capitolo successivo.

Capitolo 10: Come unire tabelle in SQL

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.

INNER JOIN

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.

La ONclausola

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.

Spaziatura dei nomi sulle tabelle

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.

LEFT JOIN

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.

RIGHT JOIN

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.

Restrizione SQLite

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.

FULL JOIN

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.

SQLite

Come RIGHT JOINs, SQLite non supporta FULL JOINs ma è comunque importante conoscerli.

Capitolo 11: Prestazioni del database

Indici SQL

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

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.

Revisione dell'indice

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

Non dovremmo indicizzare tutto? Possiamo rendere il database ultraveloce!

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.

Indici a più colonne

Gli indici a più colonne sono utili proprio per il motivo che potresti pensare: accelerano le ricerche che dipendono da più colonne.

CREATE INDEX

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.

Regola del pollice

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.

Denormalizzazione per la velocità

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 Injection

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!

Come ci proteggiamo dall'SQL injection?

È 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.

Congratulazioni per essere arrivato alla fine!

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