SQLite Interrogazione: Seleziona, Dove, LIMITE, OFFSET, Conteggio, Raggruppa per

Le sezioni seguenti illustrano la lettura dei dati con SELECT e FROM, il filtraggio delle righe con WHERE e i relativi operatori, l'ordinamento e la limitazione dei risultati, le funzioni di aggregazione, GROUP BY e HAVING, le subquery, le operazioni sugli insiemi, la gestione dei valori NULL, le espressioni CASE e le espressioni di tabella comuni.

Scrivere query SQL Organizza una SQLite database, è necessario sapere come funzionano le clausole SELECT, FROM, WHERE, GROUP BY, ORDER BY e LIMIT e come utilizzarle.

Durante questo tutorial imparerai come utilizzare queste clausole e come scrivere SQLite clausole.

Lettura dei dati con Seleziona

La clausola SELECT è l'istruzione principale che usi per interrogare un SQLite Banca dati. Nella clausola SELECT, dichiari cosa selezionare. Ma prima della clausola select, vediamo da dove possiamo selezionare i dati utilizzando la clausola FROM.

La clausola FROM viene utilizzata per specificare dove si desidera selezionare i dati. Nella clausola from, è possibile specificare una o più tabelle o sottoquery da cui selezionare i dati, come vedremo più avanti nei tutorial.

Si noti che, per tutti gli esempi seguenti, è necessario eseguire sqlite3.exe e aprire una connessione al database di esempio come segue:

Passo 1) In questa fase,

Apri Risorse del computer e vai alla seguente directory "C:\sqlite", quindi apri "sqlite3.exe":

Passo 2) Aprire il database “TutorialsSampleDB.db” con il seguente comando:

Ora sei pronto per eseguire qualsiasi tipo di query sul database.

Nella clausola SELECT, puoi selezionare non solo un nome di colonna, ma hai molte altre opzioni per specificare cosa selezionare. Come segue:

SELECT *

Questo comando selezionerà tutte le colonne da tutte le tabelle (o sottoquery) a cui si fa riferimento nella clausola FROM. Per esempio:

SELECT *
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Questo selezionerà tutte le colonne sia dalle tabelle studenti che dalle tabelle dipartimenti:

SELEZIONA nometabella.*

Ciò selezionerà tutte le colonne solo dalla tabella "nometabella". Per esempio:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Questo selezionerà tutte le colonne solo dalla tabella degli studenti:

Un valore letterale

Un valore letterale è un valore costante che può essere specificato nell'istruzione select. È possibile utilizzare normalmente valori letterali nello stesso modo in cui si utilizzano i nomi delle colonne nella clausola SELECT. Questi valori letterali verranno visualizzati per ogni riga tra le righe restituite dalla query SQL.

Ecco alcuni esempi di diversi valori letterali che puoi selezionare:

• Letterale numerico: numeri in qualsiasi formato come 1, 2.55, ... ecc.
• Stringhe letterali: qualsiasi stringa "USA", "questo è un testo di esempio", ... ecc.
• NULL – Valore NULL.
• Current_TIME – Ti darà l'ora corrente.
• CURRENT_DATE – questo ti darà la data corrente.

Questo può essere utile in alcune situazioni in cui devi selezionare un valore costante per tutte le righe restituite. Ad esempio, se vuoi selezionare tutti gli studenti dalla tabella Studenti, con una nuova colonna chiamata Paese che contiene il valore "USA", puoi farlo:

SELECT *, 'USA' AS Country FROM Students;

Questo ti darà tutte le colonne degli studenti, più una nuova colonna "Paese" come questa:

Tieni presente che questa nuova colonna Paese non è effettivamente una nuova colonna aggiunta alla tabella. È una colonna virtuale, creata nella query per visualizzare i risultati e non verrà creata nella tabella.

Nomi e alias

L'alias è un nuovo nome per la colonna che consente di selezionare la colonna con un nuovo nome. Gli alias delle colonne vengono specificati utilizzando la parola chiave "AS".

Ad esempio, se desideri selezionare la colonna StudentName da restituire con "Student Name" anziché "StudentName" puoi assegnarle un alias come questo:

SELECT StudentName AS 'Student Name' FROM Students;

Questo ti darà i nomi degli studenti con il nome "Nome studente" invece di "Nome studente" in questo modo:

Si noti che il nome della colonna rimane "StudentName"; la colonna StudentName è sempre la stessa e non cambia a causa dell'alias.

L'alias non modificherà il nome della colonna; cambierà semplicemente il nome visualizzato nella clausola SELECT.

Inoltre, tieni presente che la parola chiave "AS" è facoltativa, puoi inserire il nome dell'alias senza di essa, qualcosa del genere:

SELECT StudentName 'Student Name' FROM Students;

E ti darà esattamente lo stesso risultato della query precedente:

Puoi anche dare alias alle tabelle, non solo alle colonne. Con la stessa parola chiave “AS”. Ad esempio, puoi fare questo:

SELECT s.* FROM Students AS s;

Questo ti darà tutte le colonne nella tabella Studenti:

Questo può essere molto utile se si stanno unendo più tabelle; invece di ripetere il nome completo della tabella nella query, è possibile assegnare a ogni tabella un nome alias breve. Ad esempio, nella query seguente:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Questa query selezionerà il nome di ciascun studente dalla tabella "Studenti" con il nome del dipartimento dalla tabella "Dipartimenti":

Tuttavia, la stessa query può essere scritta in questo modo:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Abbiamo assegnato alla tabella Studenti un alias "s" e alla tabella Dipartimenti un alias "d". Quindi, invece di usare il nome completo della tabella, abbiamo usato i loro alias per farvi riferimento. INNER JOIN unisce due o più tabelle utilizzando una condizione. Nel nostro esempio, abbiamo unito la tabella Studenti con la tabella Dipartimenti tramite la colonna DepartmentId. C'è anche una spiegazione approfondita per INNER JOIN nel "SQLite Entra a far partetutorial.

Questo ti darà l'output esatto della query precedente:

DOVE

Scrivere query SQL utilizzando la sola clausola SELECT con la clausola FROM, come abbiamo visto nella sezione precedente, ti darà tutte le righe delle tabelle. Tuttavia, se desideri filtrare i dati restituiti, devi aggiungere una clausola "WHERE".

La clausola WHERE viene utilizzata per filtrare il set di risultati restituito dalla query SQL. Ecco come funziona la clausola WHERE:

• Nella clausola WHERE è possibile specificare una "espressione".
• Tale espressione verrà valutata per ogni riga restituita dalle tabelle specificate nella clausola FROM.
• L'espressione verrà valutata come espressione booleana, con un risultato vero, falso o nullo.
• Quindi verranno restituite solo le righe per le quali l'espressione è stata valutata con un valore vero e quelle con risultati falsi o nulli verranno ignorate e non incluse nel set di risultati.

Per filtrare i risultati impostati utilizzando la clausola WHERE, è necessario utilizzare espressioni e operatori.

Elenco degli operatori in SQLite e come usarli

Nella sezione seguente spiegheremo come filtrare utilizzando espressioni e operatori.

L'espressione è uno o più valori letterali o colonne combinati tra loro con un operatore.

Tieni presente che puoi utilizzare le espressioni sia nella clausola SELECT che nella clausola WHERE.

Negli esempi seguenti, proveremo le espressioni e gli operatori sia nella clausola select che nella clausola WHERE. Per mostrarti come funzionano.

Esistono diversi tipi di espressioni e operatori che è possibile specificare come segue:

SQLite l'operatore di concatenazione “||”

Questo operatore viene utilizzato per concatenare uno o più valori letterali o colonne tra loro. Produrrà una stringa di risultati da tutti i valori o colonne letterali concatenati. Per esempio:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

Questo verrà concatenato in un nuovo alias "StudentIdWithName":

• Il valore letterale della stringa "ID con nome: "
• con il valore della colonna “StudentId” e
• con il valore della colonna “StudentName”

SQLite Operatore CAST:

L'operatore CAST viene utilizzato per convertire un valore da un tipo di dati a un altro tipo di dati.

Ad esempio, se si dispone di un valore numerico memorizzato come stringa, ad esempio "12.5", e si desidera convertirlo in un valore numerico, è possibile utilizzare l'operatore CAST in questo modo: "CAST('12.5' ​​AS REAL)". Oppure, se si dispone di un valore decimale come 12.5 e si desidera ottenere solo la parte intera, è possibile effettuare un cast a intero in questo modo: "CAST(12.5 AS INTEGER)".

Esempio

Nel seguente comando proveremo a convertire valori diversi in altri tipi di dati:

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Questo ti darà:

Il risultato è il seguente:

• CAST('12.5' ​​AS REAL) – il valore '12.5' ​​è un valore stringa, verrà convertito in un valore REAL.
• CAST(12.5 AS INTEGER) – il valore 12.5 è un valore decimale, verrà convertito in un valore intero. La parte decimale verrà troncata e diventerà 12.

SQLite Aritmetica Operatori:

Prendi due o più valori letterali numerici o colonne numeriche e restituisci un valore numerico. Gli operatori aritmetici supportati in SQLite siamo:

• L'addizione “+” restituisce la somma dei due operandi.
• Subtraczione “–” – subtraccalcola i due operandi e il risultato è la differenza.
• Moltiplicazione “*” – il prodotto dei due operandi.
• Resto (modulo) “%” – indica il resto della divisione di un operando per il secondo operando.
• Divisione “/” – restituisce il quoziente risultante dalla divisione dell'operando sinistro per l'operando destro.

Esempio:

Nell'esempio seguente, proveremo i cinque operatori aritmetici con valori numerici letterali nella stessa clausola select:

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Questo ti darà:

Nota come qui abbiamo utilizzato un'istruzione SELECT senza una clausola FROM. E questo è consentito SQLite purché selezioniamo valori letterali.

SQLite Operatori di confronto

Confronta due operandi tra loro e restituisci un vero o un falso come segue:

• “<” – restituisce vero se l’operando sinistro è minore dell’operando destro.
• “<=” – restituisce vero se l’operando sinistro è minore o uguale all’operando destro.
• “>” – restituisce true se l’operando sinistro è maggiore dell’operando destro.
• “>=” – restituisce true se l’operando sinistro è maggiore o uguale all’operando destro.
• “=” e “==” – restituiscono true se i due operandi sono uguali. Si noti che entrambi gli operatori sono identici e non c'è alcuna differenza tra di loro.
• “!=” e “<>” – restituiscono true se i due operandi non sono uguali. Si noti che entrambi gli operatori sono equivalenti e non presentano alcuna differenza.

Notare che, SQLite esprime il valore vero con 1 e il valore falso con 0.

Esempio:

SELECT
  10<6 AS '<', 10<=6 AS '<=',
  10>6 AS '>', 10>=6 AS '>=',
  10=6 AS '=', 10==6 AS '==',
  10!=6 AS '!=', 10<>6 AS '<>';

Questo darà qualcosa del genere:

SQLite Operatori di corrispondenza di pattern

“LIKE” viene utilizzato per la corrispondenza di modelli. Utilizzando “LIKE”, è possibile cercare valori che corrispondono a un modello specificato tramite un carattere jolly.

L'operando a sinistra può essere un valore letterale stringa o una colonna stringa. Il modello può essere specificato come segue:

• Contiene un pattern. Ad esempio, StudentName LIKE '%a%' – questo cercherà i nomi degli studenti che contengono la lettera "a" in qualsiasi posizione nella colonna StudentName.
• Si parte dallo schema. Ad esempio, "NomeStudente COME 'a%'" – cerca i nomi degli studenti che iniziano con la lettera "a".
• Termina con il modello. Ad esempio, "NomeStudente COME '%a'" - Cerca i nomi degli studenti che terminano con la lettera "a".
• Corrispondenza di un singolo carattere in una stringa utilizzando il carattere di sottolineatura "_". Ad esempio, "NomeStudente COME 'J___'" - Cerca i nomi degli studenti che hanno una lunghezza di 4 caratteri. Devono iniziare con la lettera "J" e possono avere altri tre caratteri qualsiasi dopo la "J".

Esempi di corrispondenza dei modelli:

Ottieni nomi di studenti che iniziano con la lettera "j":

SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

Risultato:

Ottieni che i nomi degli studenti finiscano con la lettera "y":

SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

Risultato:

Ottieni i nomi degli studenti che contengono la lettera "n":

SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

Risultato:

“GLOB” è equivalente all’operatore LIKE, ma GLOB fa distinzione tra maiuscole e minuscole, a differenza dell’operatore LIKE. Ad esempio, i due comandi seguenti restituiranno risultati diversi:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Questo ti darà:

La prima istruzione restituisce 0 (falso) perché l'operatore GLOB distingue tra maiuscole e minuscole, quindi "j" non è uguale a "J". Tuttavia, la seconda istruzione restituirà 1 (vero) perché l'operatore LIKE non fa distinzione tra maiuscole e minuscole, quindi "j" è uguale a "J".

Altri operatori:

SQLite and

Un operatore logico che combina una o più espressioni. Restituirà vero, solo se tutte le espressioni restituiscono un valore "vero". Tuttavia, restituirà false solo se tutte le espressioni restituiscono un valore “false”.

Esempio:

La query seguente cercherà gli studenti che hanno StudentId > 5 e StudentName che inizia con la lettera N; gli studenti restituiti devono soddisfare le due condizioni:

SELECT *
FROM Students
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

Come output, nello screenshot qui sopra, questo ti darà solo "Nancy". Nancy è l'unica studentessa che soddisfa entrambe le condizioni.

SQLite OR

Un operatore logico che combina una o più espressioni, in modo che se uno degli operatori combinati restituisce vero, restituirà vero. Tuttavia, se tutte le espressioni restituiscono false, verrà restituito false.

Esempio:

La seguente query cercherà gli studenti che hanno StudentId > 5 o StudentName che inizia con la lettera N; gli studenti restituiti devono soddisfare almeno una delle condizioni:

SELECT *
FROM Students
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Questo ti darà:

Come output, nello screenshot sopra, questo ti darà il nome di uno studente con la lettera "n" nel nome più l'ID studente con valore>5.

Come puoi vedere il risultato è diverso dalla query con l'operatore AND.

SQLite TRA

L'operatore BETWEEN viene utilizzato per selezionare i valori compresi tra due valori. Ad esempio, "X BETWEEN Y AND Z" restituirà vero (1) se il valore X è compreso tra i due valori Y e Z. Altrimenti, restituirà falso (0). "X BETWEEN Y AND Z" è equivalente a "X >= Y AND X <= Z", dove X deve essere maggiore o uguale a Y e X è minore o uguale a Z.

Esempio:

Nella seguente query di esempio, scriveremo una query per ottenere gli studenti con valore Id compreso tra 5 e 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Questo darà solo agli studenti con ID 5, 6, 7 e 8:

SQLite IN

Accetta un operando e un elenco di operandi. Restituirà true se il valore del primo operando è uguale al valore di uno degli operandi dall'elenco. L'operatore IN restituisce true (1) se l'elenco di operandi contiene il valore del primo operando nei suoi valori. Altrimenti, restituirà false (0).

In questo modo: “col IN(x, y, z)”. Questo equivale a “ (col=x) o (col=y) o (col=z) ”.

Esempio:

La seguente query selezionerà solo gli studenti con ID 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Mi Piace

La query precedente fornirà lo stesso risultato della query seguente perché sono equivalenti:

SELECT *
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Entrambe le query forniscono l'output esatto. Tuttavia, la differenza tra le due query è che nella prima query abbiamo utilizzato l'operatore "IN". Nella seconda query abbiamo utilizzato più operatori “OR”.

L'operatore IN equivale all'utilizzo di più operatori OR. La clausola "WHERE StudentId IN(2, 4, 6, 8)" è equivalente a "WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);"

Mi Piace

SQLite NON IN

L'operatore “NOT IN” è l'opposto dell'operatore IN. Ha la stessa sintassi: accetta un operando e un elenco di operandi. Restituisce true se il valore del primo operando non è uguale a uno degli operandi presenti nell'elenco. In altre parole, restituisce true (0) se l'elenco di operandi non contiene il primo operando. Ad esempio: “col NOT IN(x, y, z)”. Questo è equivalente a “(col<>x) AND (col<>y) AND (col<>z)”.

Esempio:

La seguente query selezionerà gli studenti con ID diversi da uno di questi ID 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Ti piace questa

La query precedente fornisce lo stesso risultato esatto della query seguente perché sono equivalenti:

SELECT *
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Mi Piace

Nello screenshot sopra,

Abbiamo utilizzato più operatori diversi da "<>" per ottenere un elenco di studenti che non sono uguali a nessuno dei seguenti ID: 2, 4, 6 o 8. Questa query restituirà tutti gli altri studenti diversi da questi elenchi di ID.

SQLite ESISTE

Gli operatori EXISTS non accettano operandi; dopo è necessaria solo una clausola SELECT. L'operatore EXISTS restituirà vero (1) se sono presenti righe restituite dalla clausola SELECT e restituirà falso (0) se non sono presenti righe restituite dalla clausola SELECT.

Esempio:

Nell'esempio seguente, selezioneremo il nome del dipartimento, se l'ID del dipartimento esiste nella tabella studenti:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Questo ti darà:

Verranno restituiti solo i tre dipartimenti "IT, Fisica e Arte". Il dipartimento "Matematica" non verrà restituito perché non ci sono studenti iscritti a quel dipartimento, quindi l'ID del dipartimento non esiste nella tabella degli studenti. Ecco perché l'operatore EXISTS ha ignorato il dipartimento "Matematica".

SQLite NON

Reverses è il risultato dell'operatore precedente che lo segue. Ad esempio:

• NOT BETWEEN – Restituirà vero se BETWEEN restituisce falso e viceversa.
• NOT LIKE – Restituirà vero se LIKE restituisce falso e viceversa.
• NOT GLOB – Restituirà vero se GLOB restituisce falso e viceversa.
• NON ESISTE – Restituirà vero se EXISTS restituisce falso e viceversa.

Esempio:

Nell'esempio seguente, useremo l'operatore NOT con l'operatore EXISTS per ottenere i nomi dei dipartimenti che non esistono nella tabella Students, che è il risultato inverso dell'operatore EXISTS. Quindi, la ricerca verrà eseguita tramite DepartmentId che non esiste nella tabella department.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Produzione:

Verrà restituito solo il dipartimento "Matematica". Questo perché il dipartimento "Matematica" è l'unico dipartimento che non esiste nella tabella degli studenti.

Limitare e ordinare

SQLite Ordine

SQLite L'ordine consiste nell'ordinare il risultato in base a una o più espressioni. Per ordinare il set di risultati, è necessario utilizzare la clausola ORDER BY come segue:

• Innanzitutto, devi specificare la clausola ORDER BY.
• La clausola ORDER BY deve essere specificata alla fine della query; dopo di essa è possibile specificare solo la clausola LIMIT.
• Specificare l'espressione con cui ordinare i dati, questa espressione può essere un nome di colonna o un'espressione.
• Dopo l'espressione è possibile specificare una direzione di ordinamento facoltativa. O DESC, per ordinare i dati in ordine decrescente o ASC per ordinare i dati in ordine crescente. Se non ne specificassi nessuno, i dati verrebbero ordinati in modo crescente.
• È possibile specificare più espressioni utilizzando "," tra loro.

Esempio

Nell'esempio seguente selezioneremo tutti gli studenti ordinati in base al loro nome ma in ordine decrescente, quindi in base al nome del dipartimento in ordine crescente:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

Questo ti darà:

• SQLite ordinerà innanzitutto tutti gli studenti in base al nome del dipartimento in ordine crescente
• Quindi, per ciascun nome di dipartimento, tutti gli studenti sotto quel nome di dipartimento verranno visualizzati in ordine decrescente in base ai loro nomi

SQLite Limite:

Puoi limitare il numero di righe restituite dalla tua query SQL, utilizzando la clausola LIMIT. Ad esempio, LIMIT 10 ti darà solo 10 righe e ignorerà tutte le altre righe.

Nella clausola LIMIT, è possibile selezionare un numero specifico di righe a partire da una posizione specifica utilizzando la clausola OFFSET. Ad esempio, "LIMIT 4 OFFSET 4" ignorerà le prime 4 righe e restituirà 4 righe a partire dalla quinta, quindi si otterranno le righe 5, 6, 7 e 8.

Tieni presente che la clausola OFFSET è facoltativa; puoi scriverla come "LIMIT 4, 4" e otterrai comunque i risultati esatti.

Esempio:

Nell'esempio seguente, restituiremo solo 3 studenti a partire dall'ID studente 5 utilizzando la query:

SELECT * FROM Students LIMIT 4,3;

Questo ti darà solo tre studenti a partire dalla riga 5. Quindi ti darà le righe con StudentId 5, 6 e 7:

Rimozione dei duplicati

Se la query SQL restituisce valori duplicati, è possibile utilizzare la parola chiave "DISTINCT" per rimuovere i duplicati e restituire solo valori distinti. È possibile specificare più di una colonna dopo l'operatore DISTINCT.

Esempio:

La seguente query restituirà valori duplicati dei "nomi dipartimento": qui abbiamo valori duplicati con i nomi IT, Fisica e Arti.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Questo ti darà valori duplicati per il nome del dipartimento:

Notare come sono presenti valori duplicati per il nome del dipartimento. Ora utilizzeremo la parola chiave DISTINCT con la stessa query per rimuovere tali duplicati e ottenere solo valori univoci. Come questo:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Questo ti darà solo tre valori univoci per la colonna del nome del dipartimento:

Aggregato

SQLite Gli aggregati sono funzioni integrate definite in SQLite che raggrupperà più valori di più righe in un unico valore.

Ecco gli aggregati supportati da SQLite:

SQLite AVG()

Restituita la media per tutti i valori x.

Esempio:

Nell'esempio seguente otterremo il voto medio che gli studenti devono ottenere da tutti gli esami:

SELECT AVG(Mark) FROM Marks;

Questo ti darà il valore “18.375”:

Questi risultati derivano dalla somma di tutti i valori dei voti divisi per il loro conteggio.

COUNT() – COUNT(X) o COUNT(*)

Restituisce il conteggio totale del numero di volte in cui è apparso il valore x. Ed ecco alcune opzioni che puoi utilizzare con COUNT:

• COUNT(x): conta solo i valori x, dove x è il nome di una colonna. Ignorerà i valori NULL.
• COUNT(*): conta tutte le righe da tutte le colonne.
• COUNT (DISTINCT x): è possibile specificare una parola chiave DISTINCT prima di x che otterrà il conteggio dei valori distinti di x.

Esempio

Nell'esempio seguente, otterremo il conteggio totale dei dipartimenti con COUNT(DepartmentId), COUNT(*) e COUNT(DISTINCT DepartmentId) e le loro differenze:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Questo ti darà:

Come segue:

• COUNT(DepartmentId) ti fornirà il conteggio di tutti gli ID del dipartimento e ignorerà i valori null.
• COUNT(DISTINCT DepartmentId) fornisce valori distinti di DepartmentId, che sono solo 3. Quali sono i tre diversi valori del nome del dipartimento. Si noti che ci sono 8 valori del nome del dipartimento nel nome dello studente. Ma solo i tre diversi valori che sono matematica, informatica e fisica.
• COUNT(*) conta il numero di righe nella tabella studenti che corrispondono a 10 righe per 10 studenti.

GRUPPO_CONCAT() – GRUPPO_CONCAT(X) o GRUPPO_CONCAT(X,Y)

La funzione di aggregazione GROUP_CONCAT concatena più valori in un unico valore, separandoli con una virgola. Ha le seguenti opzioni:

• GRUPPO_CONCAT(X): concatena tutto il valore di x in un'unica stringa, con la virgola "", utilizzata come separatore tra i valori. I valori NULL verranno ignorati.
• GROUP_CONCAT(X, Y): concatenerà i valori di x in un'unica stringa, con il valore di y utilizzato come separatore tra ciascun valore invece del separatore predefinito ','. Anche i valori NULL verranno ignorati.
• GROUP_CONCAT(DISTINCT X): concatenerà tutti i valori distinti di x in un'unica stringa, con la virgola "", utilizzata come separatore tra i valori. I valori NULL verranno ignorati.

GRUPPO_CONCAT(NomeDipartimento) Esempio

La query seguente concatenerà tutti i valori del nome del dipartimento dalla tabella studenti e dipartimenti in una stringa separata da virgole. Quindi, invece di restituire un elenco di valori, un valore su ogni riga. Restituirà solo un valore su una riga, con tutti i valori separati da virgole:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Questo ti darà:

Questo ti darà l'elenco dei valori dei nomi di 8 dipartimenti concatenati in una stringa separata da virgole.

GRUPPO_CONCAT(DISTINCTNomeDipartimento) Esempio

La seguente query concatenerà i valori distinti del nome del dipartimento dalle tabelle studenti e dipartimenti in un'unica stringa separata da virgole:

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Questo ti darà:

Nota come il risultato è diverso dal risultato precedente; sono stati restituiti solo tre valori che sono i nomi dei dipartimenti distinti e i valori duplicati sono stati rimossi.

GRUPPO_CONCAT(NomeDipartimento ,'&') Esempio

La seguente query concatenerà tutti i valori della colonna del nome del dipartimento dalle tabelle studenti e dipartimenti in un'unica stringa, ma con il carattere '&' al posto della virgola come separatore:

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Questo ti darà:

Notare come viene utilizzato il carattere "&" al posto del carattere predefinito "," per separare i valori.

SQLite MASSIMO() e MINIMO()

MAX(X) restituisce il valore più alto tra i valori X. MAX restituirà un valore NULL se tutti i valori di x sono nulli. Mentre MIN(X) restituisce il valore più piccolo tra i valori X. MIN restituirà un valore NULL se tutti i valori di X sono nulli.

Esempio

Nella seguente query, utilizzeremo le funzioni MIN e MAX per ottenere il voto più alto e il voto più basso dalla tabella "Voti":

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Questo ti darà:

SQLite SOMMA(x), Totale(x)

Entrambi restituiranno la somma di tutti i valori x. Ma sono diversi in quanto segue:

• SUM restituirà null se tutti i valori sono null, ma Total restituirà 0.
• TOTAL restituisce sempre valori in virgola mobile. SOMMA restituisce un valore intero se tutti i valori x sono interi. Tuttavia, se i valori non sono interi, verrà restituito un valore in virgola mobile.

Esempio

Nella seguente query utilizzeremo SUM e TOTAL per ottenere la somma di tutti i voti presenti nella tabella "Voti":

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Questo ti darà:

Come puoi vedere, TOTAL restituisce sempre un punto mobile. Ma SUM restituisce un valore intero perché i valori nella colonna "Mark" potrebbero essere numeri interi.

Differenza tra SUM e TOTALE esempio:

Nella seguente query mostreremo la differenza tra SUM e TOTAL quando ottengono la SOMMA dei valori NULL:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Questo ti darà:

Tieni presente che non ci sono segni per TestId = 4, quindi ci sono valori null per quel test. SUM restituisce un valore nullo come spazio vuoto, mentre TOTAL restituisce 0.

Raggruppa per

La clausola GROUP BY viene utilizzata per specificare una o più colonne che verranno utilizzate per raggruppare le righe in gruppi. Le righe con gli stessi valori verranno raccolte (organizzate) insieme in gruppi.

Per qualsiasi altra colonna non inclusa nel raggruppamento di colonne, è possibile utilizzare una funzione di aggregazione.

Esempio:

La seguente query ti fornirà il numero totale di studenti presenti in ciascun dipartimento.

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

Questo ti darà:

La clausola GROUPBY DepartmentName raggrupperà tutti gli studenti in gruppi, uno per ciascun nome di dipartimento. Per ogni gruppo di “dipartimento”, conterà gli studenti presenti.

Clausola AVENTE

Se desideri filtrare i gruppi restituiti dalla clausola GROUP BY, puoi specificare una clausola "HAVING" con l'espressione dopo la clausola GROUP BY. L'espressione verrà utilizzata per filtrare questi gruppi.

Esempio

Nella seguente query selezioneremo i dipartimenti che hanno solo due studenti:

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(s.StudentId) = 2;

Questo ti darà:

La clausola HAVING COUNT(S.StudentId) = 2 filtrerà i gruppi restituiti e restituirà solo quei gruppi che contengono esattamente due studenti. Nel nostro caso, il dipartimento artistico ha 2 studenti, quindi viene visualizzato nell'output.

SQLite Query e sottoquery

All'interno di qualsiasi query, puoi utilizzare un'altra query in SELECT, INSERT, DELETE, UPDATE o all'interno di un'altra sottoquery.

Questa query nidificata è chiamata sottoquery. Vedremo ora alcuni esempi di utilizzo delle sottoquery nella clausola SELECT. Tuttavia, nel tutorial sulla modifica dei dati, vedremo come utilizzare le sottoquery con le istruzioni INSERT, DELETE e UPDATE.

Utilizzo della sottoquery nell'esempio della clausola FROM

Nella query seguente includeremo una sottoquery all'interno della clausola FROM:

SELECT
  s.StudentName, t.Mark
FROM Students AS s
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

La domanda:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

La query precedente è chiamata qui sottoquery perché è nidificata all'interno della clausola FROM. Si noti che gli abbiamo assegnato un nome alias "t" in modo da poter fare riferimento alle colonne restituite da esso nella query.

Questa query ti darà:

Quindi nel nostro caso,

• s.StudentName viene selezionato dalla query principale che fornisce il nome degli studenti e
• t.Mark è selezionato dalla sottoquery; che dà i voti ottenuti da ciascuno di questi studenti

Utilizzo della sottoquery nell'esempio della clausola WHERE

Nella seguente query includeremo una sottoquery nella clausola WHERE:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

La domanda:

SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId

La query precedente è chiamata qui sottoquery perché è nidificata nella clausola WHERE. La sottoquery restituirà i valori DepartmentId che verranno utilizzati dall'operatore NOT EXISTS.

Questa query ti darà:

Nella query precedente, abbiamo selezionato il dipartimento in cui non sono iscritti studenti. Che è il dipartimento di "Matematica" qui.

Impostato Operazioni – UNIONE,Intersezione

SQLite supporta le seguenti operazioni SET:

UNIONE E UNIONE TUTTI

Combina uno o più set di risultati (un gruppo di righe) restituiti da più istruzioni SELECT in un unico set di risultati.

UNION restituirà valori distinti. Tuttavia, UNION ALL non includerà e includerà duplicati.

Tieni presente che il nome della colonna sarà il nome della colonna specificato nella prima istruzione SELECT.

UNIONE Esempio

Nell'esempio seguente, otterremo l'elenco di DepartmentId dalla tabella studenti e l'elenco di DepartmentId dalla tabella dipartimenti nella stessa colonna:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Questo ti darà:

La query restituisce solo 5 righe che rappresentano i valori ID del dipartimento distinti. Notare il primo valore che è il valore null.

SQLite UNIONE TUTTI Esempio

Nell'esempio seguente, otterremo l'elenco di DepartmentId dalla tabella studenti e l'elenco di DepartmentId dalla tabella dipartimenti nella stessa colonna:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Questo ti darà:

La query restituirà 14 righe, 10 righe dalla tabella studenti e 4 dalla tabella dipartimenti. Tieni presente che sono presenti duplicati nei valori restituiti. Inoltre, tieni presente che il nome della colonna era quello specificato nella prima istruzione SELECT.

Ora vediamo come UNION all darà risultati diversi se sostituiamo UNION ALL con UNION:

SQLite INTERSEZIONE

Restituisce i valori presenti in entrambi i set di risultati combinati. I valori presenti in uno dei set di risultati combinati verranno ignorati.

Esempio

Nella query seguente selezioneremo i valori DepartmentId presenti in entrambe le tabelle Students e Departments nella colonna DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Questo ti darà:

La query restituisce solo tre valori 1, 2 e 3. Quali sono i valori presenti in entrambe le tabelle.

Tuttavia, i valori null e 4 non sono stati inclusi perché il valore null esiste solo nella tabella degli studenti e non nella tabella dei dipartimenti. E il valore 4 esiste nella tabella dei dipartimenti e non nella tabella degli studenti.

Ecco perché entrambi i valori NULL e 4 sono stati ignorati e non inclusi nei valori restituiti.

SALVO

Supponiamo di avere due elenchi di righe, list1 e list2, e di volere solo le righe di list1 che non esistono in list2, puoi usare la clausola "EXCEPT". La clausola EXCEPT confronta i due elenchi e restituisce quelle righe che esistono in list1 e non esistono in list2.

Esempio

Nella query seguente selezioneremo i valori DepartmentId presenti nella tabella departments e non presenti nella tabella students:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Questo ti darà:

La query restituisce solo il valore 4. Che è l'unico valore esistente nella tabella dei dipartimenti e non esiste nella tabella degli studenti.

Gestione NULL

Il valore “NULL” è un valore speciale in SQLiteViene utilizzato per rappresentare un valore sconosciuto o mancante. Si noti che il valore nullo è completamente diverso da "0" o da un valore vuoto. Mentre 0 e il valore vuoto sono valori noti, il valore nullo è sconosciuto.

I valori NULL richiedono una gestione speciale in SQLite, vedremo ora come gestire i valori NULL.

Cerca valori NULL

Non puoi usare il normale operatore di uguaglianza (=) per cercare i valori nulli. Ad esempio, la query seguente cerca gli studenti che hanno un valore DepartmentId nullo:

SELECT * FROM Students WHERE DepartmentId = NULL;

Questa query non darà alcun risultato:

Poiché il valore NULL non è uguale a nessun altro valore incluso un valore null stesso, ecco perché non ha restituito alcun risultato.

Tuttavia, affinché la query funzioni, è necessario utilizzare l'operatore "IS NULL" per cercare i valori nulli come segue:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Questo ti darà:

La query restituirà gli studenti che hanno un valore DepartmentId null.

Se si desidera ottenere i valori non nulli, è necessario utilizzare l'operatore "IS NOT NULL" in questo modo:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Questo ti darà:

La query restituirà gli studenti che non hanno un valore DepartmentId NULL.

Risultati condizionali

Se disponi di un elenco di valori e desideri selezionarne uno in base ad alcune condizioni. Per questo, la condizione per quel particolare valore dovrebbe essere vera per essere selezionata.

L'espressione CASE valuterà questo elenco di condizioni per tutti i valori. Se la condizione è vera, restituirà quel valore.

Ad esempio, se hai una colonna "Voto" e vuoi selezionare un valore di testo in base al valore del voto come segue:

– “Eccellente” se il voto è superiore a 85.

– “Molto buono” se il voto è compreso tra 70 e 85.

– “Buono” se il voto è compreso tra 60 e 70.

Quindi puoi usare l'espressione CASE per farlo.

Questo può essere utilizzato per definire una logica nella clausola SELECT in modo da poter selezionare determinati risultati in base a determinate condizioni, come ad esempio l'istruzione if.

L'operatore CASE può essere definito con diverse sintassi come segue:

Puoi utilizzare diverse condizioni:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

Oppure puoi utilizzare una sola espressione e inserire diversi valori possibili tra cui scegliere:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln
END

Tieni presente che la clausola ELSE è facoltativa.

Esempio

Nell'esempio seguente, utilizzeremo l'espressione CASE con valore NULL nella colonna ID dipartimento della tabella Studenti per visualizzare il testo 'Nessun dipartimento' come segue:

SELECT
  StudentName,
  CASE
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId
  END AS DepartmentId
FROM Students;

• L'operatore CASE controllerà il valore di DepartmentId se è nullo o meno.
• Se è un valore NULL, selezionerà il valore letterale "No Department" invece del valore DepartmentId.
• Se non è un valore null, selezionerà il valore della colonna DepartmentId.

Questo ti darà l'output come mostrato di seguito:

Espressione di tabella comune

Le espressioni di tabella comuni (CTE) sono sottoquery definite all'interno dell'istruzione SQL con un determinato nome.

Ha un vantaggio rispetto alle sottoquery perché è definita dalle istruzioni SQL e renderà le query più facili da leggere, gestire e comprendere.

Un'espressione di tabella comune può essere definita inserendo la clausola WITH davanti alle istruzioni SELECT come segue:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

Il campo "CTEname" è un nome qualsiasi che puoi assegnare alla CTE e che potrai utilizzare in seguito per farvi riferimento. Tieni presente che puoi definire istruzioni SELECT, UPDATE, INSERT o DELETE sulle CTE.

Vediamo ora un esempio di come utilizzare CTE nella clausola SELECT.

Esempio

Nell'esempio seguente, definiremo un CTE da un'istruzione SELECT e lo utilizzeremo in seguito in un'altra query:

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

In questa query, abbiamo definito una CTE e le abbiamo assegnato il nome "AllDepartments". Questa CTE è stata definita da una query SELECT:

SELECT DepartmentId, DepartmentName
  FROM Departments

Quindi, dopo aver definito il CTE, lo abbiamo utilizzato nella query SELECT che segue.

Tieni presente che le espressioni di tabella comuni non influiscono sull'output della query. È un modo per definire una vista logica o una sottoquery per riutilizzarle nella stessa query. Le espressioni di tabella comuni sono come una variabile che dichiari e la riutilizzi come sottoquery. Solo l'istruzione SELECT influisce sull'output della query.

Questa query ti darà:

Query avanzate

Le query avanzate sono quelle query che contengono join complessi, sottoquery e alcuni aggregati. Nella sezione seguente vedremo un esempio di query avanzata:

Dove otteniamo il,

• Nomi dei dipartimenti con tutti gli studenti per ciascun dipartimento
• Nomi degli studenti separati da virgola e
• Mostrare che il dipartimento ha almeno tre studenti al suo interno

SELECT
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

Abbiamo aggiunto una clausola JOIN per ottenere il nome del dipartimento dalla tabella Departments. Dopodiché abbiamo aggiunto una clausola GROUP BY con due funzioni di aggregazione:

• “COUNT” per contare gli studenti per ogni gruppo di dipartimento.
• GROUP_CONCAT per concatenare gli studenti per ciascun gruppo con virgole separate in un'unica stringa.

Dopo il GRUPPO BY, abbiamo utilizzato la clausola HAVING per filtrare i dipartimenti e selezionare solo quelli che hanno almeno 3 studenti.

Il risultato sarà il seguente:

DOMANDE FREQUENTI