Per scrivere query SQL in un database SQLite, è 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 clausole SQLite.
In questo tutorial imparerai-
- Lettura dei dati con Seleziona
- Nomi e pseudonimi
- DOVE
- Limitazione e ordinamento
- Rimozione dei duplicati
- Aggregato
- Raggruppare per
- Query e sottoquery
- Set Operations -UNION, Intersect
- Gestione NULL
- Risultati condizionali
- Espressione di tabella comune
- Query avanzate
Lettura dei dati con Seleziona
La clausola SELECT è l'istruzione principale utilizzata per interrogare un database SQLite. 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 i seguenti esempi, è necessario eseguire sqlite3.exe e aprire una connessione al database di esempio mentre scorre:
Passaggio 1) In questo passaggio,
- Apri Risorse del computer e vai alla seguente directory " C: \ sqlite " e
- Quindi apri " sqlite3.exe ":
Passaggio 2) Apri 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 il nome di una colonna ma hai molte altre opzioni per specificare cosa selezionare. Come segue:
SELEZIONARE *
Questo comando selezionerà tutte le colonne da tutte le tabelle di riferimento (o sottoquery) nella clausola FROM. Per esempio:
SELEZIONARE *DA StudentiINNER JOIN Dipartimenti ON Students.DepartmentId = Departments.DepartmentId;
Questo selezionerà tutte le colonne sia dalle tabelle studenti che dalle tabelle dei dipartimenti:
SELEZIONA tablename. *
Questo selezionerà tutte le colonne solo dalla tabella "tablename". Per esempio:
SELEZIONA Studenti. *DA StudentiINNER JOIN Dipartimenti 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 valori letterali normalmente nello stesso modo in cui si utilizzano i nomi di colonna nella clausola SELECT. Questi valori letterali verranno visualizzati per ogni riga dalle righe restituite dalla query SQL.
Di seguito sono riportati alcuni esempi di diversi valori letterali che è possibile selezionare:
- Letterale numerico: numeri in qualsiasi formato come 1, 2,55, ... ecc.
- Valori letterali stringa: 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.
Ciò può essere utile in alcune situazioni in cui è necessario selezionare un valore costante per tutte le righe restituite. Ad esempio, se desideri selezionare tutti gli studenti dalla tabella Studenti, con una nuova colonna chiamata paese che contiene il valore "USA", puoi farlo:
SELEZIONA *, "USA" COME Nazione DA Studenti;
Questo ti darà tutte le colonne degli studenti, più una nuova colonna "Paese" come questa:
Notare che questa nuova colonna Paese non è in realtà una nuova colonna aggiunta alla tabella. È una colonna virtuale, creata nella query per visualizzare i risultati e non verrà creata nella tabella.
Nomi e pseudonimi
L'alias è un nuovo nome per la colonna che ti consente di selezionare la colonna con un nuovo nome. Gli alias di colonna vengono specificati utilizzando la parola chiave "AS".
Ad esempio, se desideri selezionare la colonna StudentName da restituire con "Student Name" invece di "StudentName" puoi dargli un alias come questo:
SELEZIONA StudentName COME "Nome studente" DA Studenti;
Questo ti darà i nomi degli studenti con il nome "Student Name" invece di "StudentName" in questo modo:
Notare che il nome della colonna è ancora " StudentName "; la colonna StudentName è sempre la stessa, non cambia per l'alias.
L'alias non cambierà il nome della colonna; cambierà solo il nome visualizzato nella clausola SELECT.
Inoltre, tieni presente che, la parola chiave "AS" è facoltativa, puoi inserire il nome alias senza di essa, qualcosa del genere:
SELEZIONA StudentName "Student Name" FROM Students;
E ti darà lo stesso identico output della query precedente:
Puoi anche fornire alias di tabelle, non solo 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 stai partecipando a più di un tavolo; invece di ripetere il nome completo della tabella nella query, è possibile assegnare a ciascuna tabella un breve nome alias. Ad esempio, nella seguente query:
SELEZIONA Students.StudentName, Department.DepartmentNameDA StudentiINNER JOIN Dipartimenti ON Students.DepartmentId = Departments.DepartmentId;
Questa query selezionerà il nome di ogni studente dalla tabella "Studenti" con il nome del dipartimento dalla tabella "Dipartimenti":
Tuttavia, la stessa query può essere scritta in questo modo:
SELEZIONA s.StudentName, d.DepartmentNameDA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentId;
- Abbiamo assegnato alla tabella Studenti un alias "s" e alla tabella dei dipartimenti un alias "d".
- Quindi, invece di utilizzare il nome completo della tabella, abbiamo utilizzato i loro alias per fare riferimento a loro.
- INNER JOIN unisce due o più tabelle utilizzando una condizione. Nel nostro esempio, abbiamo unito la tabella Studenti con la tabella Reparti con la colonna DepartmentId. C'è anche una spiegazione approfondita per INNER JOIN nel tutorial "SQLite Joins".
Questo ti darà l'output esatto della query precedente:
DOVE
Scrivere query SQL utilizzando la clausola SELECT da sola con la clausola FROM come abbiamo visto nella sezione precedente, ti darà tutte le righe delle tabelle. Tuttavia, se si desidera filtrare i dati restituiti, è necessario 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".
- Quell'espressione verrà valutata per ogni riga restituita dalla tabella o dalle tabelle specificate nella clausola FROM.
- L'espressione verrà valutata come un'espressione booleana, con un risultato true, false o null.
- 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 utilizzarli
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.
Si noti che è possibile utilizzare 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 si comportano.
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 letterali o colonne concatenati. Per esempio:
SELEZIONA "Id con nome: || StudentId || StudentName AS StudentIdWithNameDA Studenti;
Questo si concatenerà in un nuovo alias " StudentIdWithName ":
- Il valore stringa letterale " Id con nome: "
- con il valore della colonna " StudentId " e
- con il valore dalla colonna " StudentName "
Operatore SQLite CAST:
L'operatore CAST viene utilizzato per convertire un valore da un tipo di dati a un altro tipo di dati.
Ad esempio, se hai un valore numerico memorizzato come valore stringa come questo " '12 .5 ' " e desideri convertirlo in un valore numerico, puoi utilizzare l'operatore CAST per farlo in questo modo " CAST ('12 .5' AS REAL) ". Oppure, se hai un valore decimale come 12.5, e devi ottenere solo la parte intera, puoi convertirla in un numero intero come questo "CAST (12.5 AS INTEGER)".
Esempio
Nel seguente comando proveremo a convertire diversi valori in altri tipi di dati:
SELEZIONA CAST ('12 .5 'COME REALE) ToReal, CAST (12.5 COME INTERO) COME ToInteger;
Questo ti darà:
Il risultato è il seguente:
- CAST ('12 .5 'COME REAL) - il valore '12 .5' è un valore stringa, verrà convertito in un valore REALE.
- 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.
Operatori aritmetici SQLite:
Prendi due o più valori letterali numerici o colonne numeriche e restituisci un valore numerico. Gli operatori aritmetici supportati in SQLite sono:
|
Esempio:
Nell'esempio seguente, proveremo i cinque operatori aritmetici con valori numerici letterali nello stesso
clausola di selezione:
SELEZIONA 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Questo ti darà:
Notare come abbiamo usato un'istruzione SELECT senza una clausola FROM qui. E questo è consentito in SQLite fintanto che selezioniamo valori letterali.
Operatori di confronto SQLite
Confronta due operandi tra loro e restituisci vero o falso come segue:
|
Nota che SQLite esprime il valore vero con 1 e il valore falso con 0.
Esempio:
SELEZIONARE10 <6 COME '<', 10 <= 6 COME '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 COME '=', 10 == 6 COME '==',10! = 6 COME '! =', 10 <> 6 COME '<>';
Questo darà qualcosa del genere:
Operatori di corrispondenza dei modelli SQLite
" MI PIACE " - viene utilizzato per la corrispondenza dei modelli. Utilizzando " Mi piace ", puoi cercare i valori che corrispondono a un modello specificato utilizzando 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 pattern. Ad esempio, StudentName LIKE '% a%' - questo cercherà i nomi degli studenti che contengono la lettera "a" in qualsiasi posizione nella colonna StudentName.
- Inizia con lo schema. Ad esempio, " StudentName LIKE 'a%' ": cerca i nomi degli studenti che iniziano con la lettera "a".
- Finisce con lo schema. Ad esempio, " StudentName LIKE '% a' " - Cerca i nomi degli studenti che terminano con la lettera "a".
- Corrispondenza di un singolo carattere in una stringa utilizzando la lettera di sottolineatura "_". Ad esempio, " StudentName LIKE 'J___' ": cerca i nomi degli studenti lunghi 4 caratteri. Deve iniziare con la lettera "J" e può contenere altri tre caratteri dopo la lettera "J".
Esempi di corrispondenza dei modelli:
- Ottieni i nomi degli studenti che iniziano con la lettera "j":
SELEZIONA StudentName FROM Students WHERE StudentName LIKE 'j%';
Risultato:
- Fai in modo che i nomi degli studenti finiscano con la lettera "y":
SELEZIONA StudentName FROM Students WHERE StudentName LIKE '% y';
Risultato:
- Ottieni i nomi degli studenti che contengono la lettera "n":
SELEZIONA StudentName FROM Students WHERE StudentName LIKE '% n%';
Risultato:
"GLOB" - è equivalente all'operatore LIKE, ma GLOB distingue tra maiuscole e minuscole, a differenza dell'operatore LIKE. Ad esempio, i due comandi seguenti restituiranno risultati diversi:
SELEZIONA 'Jack' GLOB 'j%';SELEZIONA "Jack" COME "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 (true) 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 seguente query cercherà gli studenti che hanno StudentId> 5 e StudentName inizia con la lettera N, gli studenti restituiti devono soddisfare le due condizioni:
SELEZIONARE *DA StudentiWHERE (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 true, restituirà true. Tuttavia, se tutte le espressioni restituiscono false, restituirà false.
Esempio:
La seguente query cercherà gli studenti che hanno StudentId> 5 o StudentName inizia con la lettera N, gli studenti restituiti devono soddisfare almeno una delle condizioni:
SELEZIONARE *DA StudentiWHERE (StudentId> 5) OR (StudentName LIKE 'N%');
Questo ti darà:
Come output, nello screenshot qui sopra, questo ti darà il nome di uno studente con la lettera "n" nel loro nome più l'id dello studente con valore> 5.
Come puoi vedere il risultato è diverso dalla query con l'operatore AND.
SQLite TRA
TRA è utilizzato per selezionare quei valori che sono all'interno di un intervallo di due valori. Ad esempio, " X TRA Y AND Z " restituirà true (1) se il valore X è compreso tra i due valori Y e Z. In caso contrario, restituirà false (0). " X TRA Y AND Z " è equivalente a " X> = Y AND X <= Z ", 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 studenti con valore Id compreso tra 5 e 8:
SELEZIONARE *DA StudentiDOVE StudentId TRA 5 E 8;
Questo darà solo gli studenti con ID 5, 6, 7 e 8:
SQLite IN
Accetta un operando e un elenco di operandi. Restituirà vero se il valore del primo operando è uguale a uno dei valori degli operandi dall'elenco. L'operatore IN restituisce true (1) se l'elenco di operandi contiene il primo valore dell'operando all'interno dei suoi valori. In caso contrario, restituirà false (0).
In questo modo: " col IN (x, y, z) ". Questo è equivalente a " (col = x) o (col = y) o (col = z) ".
Esempio:
La seguente query selezionerà solo studenti con ID 2, 4, 6, 8:
SELEZIONARE *DA StudentiWHERE StudentId IN (2, 4, 6, 8);
Come questo:
La query precedente darà il risultato esatto come la seguente query perché sono equivalenti:
SELEZIONARE *DA StudentiWHERE (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 la prima query è stata utilizzata l'operatore "IN". Nella seconda query, abbiamo utilizzato più operatori "OR".
L'operatore IN equivale a utilizzare più operatori OR. " WHERE StudentId IN (2, 4, 6, 8) " è equivalente a " WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); "
Come questo:
SQLite NON IN
L'operando "NOT IN" è l'opposto dell'operatore IN. Ma con la stessa sintassi; richiede un operando e un elenco di operandi. Restituirà vero se il valore del primo operando non è uguale a uno dei valori degli operandi dall'elenco. cioè, restituirà vero (0) se l'elenco di operandi non contiene il primo operando. In questo modo: " col NOT IN (x, y, z) ". Ciò è 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:
SELEZIONARE *DA StudentiDOVE StudentId NON IN (2, 4, 6, 8);
Come questo
La query precedente forniamo il risultato esatto come la seguente query perché sono equivalenti:
SELEZIONARE *DA StudentiWHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Come questo:
Nello screenshot qui sopra,
Abbiamo utilizzato più operatori non uguali "<>" 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 questo elenco di ID.
SQLite ESISTE
Gli operatori EXISTS non accettano operandi; ci vuole solo una clausola SELECT dopo di essa. L'operatore EXISTS restituirà true (1) se sono presenti righe restituite dalla clausola SELECT e restituirà false (0) se non ci sono righe restituite dalla clausola SELECT.
Esempio:
Nell'esempio seguente, selezioneremo il nome del dipartimento, se l'ID del dipartimento esiste nella tabella degli studenti:
SELEZIONA DepartmentNameDA Dipartimenti AS dDOVE ESISTE (SELEZIONARE DepartmentId DA STUDENTI COME s WHERE d.DepartmentId = s.DepartmentId);
Questo ti darà:
Verranno restituiti solo i tre dipartimenti " IT, Physics and Arts ". E il nome del dipartimento " Matematica " non verrà restituito perché non ci sono studenti in 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
Inverte il risultato dell'operatore precedente che viene dopo di esso. Per esempio:
- NOT BETWEEN - Restituirà true se BETWEEN restituirà false e viceversa.
- NOT LIKE - Restituirà true se LIKE restituisce false e viceversa.
- NOT GLOB - Restituirà true se GLOB restituirà false e viceversa.
- NOT EXISTS - Restituirà true se EXISTS restituirà false e viceversa.
Esempio:
Nell'esempio seguente, utilizzeremo l'operatore NOT con l'operatore EXISTS per ottenere i nomi dei reparti che non esistono nella tabella Studenti, che è il risultato inverso dell'operatore EXISTS. Quindi, la ricerca verrà eseguita tramite DepartmentId che non esiste nella tabella del dipartimento.
SELEZIONA DepartmentNameDA Dipartimenti AS dDOVE NON ESISTE (SELEZIONA DepartmentIdDA Studenti AS sWHERE d.DepartmentId = s.DepartmentId);
Uscita :
Verrà restituito solo il dipartimento " Matematica ". Perché il dipartimento " Matematica " è l'unico dipartimento che non esiste nella tabella degli studenti.
Limitazione e ordinamento
Ordine SQLite
L'ordine SQLite 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; solo la clausola LIMIT può essere specificata dopo di essa.
- Specificare l'espressione con cui ordinare i dati, questa espressione può essere un nome di colonna o un'espressione.
- Dopo l'espressione, puoi 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 hai specificato nessuno, i dati verranno ordinati in ordine crescente.
- È possibile specificare più espressioni utilizzando tra loro ",".
Esempio
Nell'esempio seguente, selezioneremo tutti gli studenti ordinati per nome ma in ordine decrescente, quindi per nome del dipartimento in ordine crescente:
SELEZIONA s.StudentName, d.DepartmentNameDA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentIdORDINA PER d.DepartmentName ASC, s.StudentName DESC;
Questo ti darà:
- SQLite ordinerà prima tutti gli studenti in base al nome del dipartimento in ordine crescente
- Quindi, per ogni nome di dipartimento, tutti gli studenti sotto quel nome di dipartimento verranno visualizzati in ordine decrescente in base ai loro nomi
Limite SQLite:
È possibile limitare il numero di righe restituite dalla 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 riga, quindi otterrai le righe 5,6,7 e 8.
Nota che la clausola OFFSET è facoltativa, puoi scriverla come " LIMIT 4, 4 " e ti darà i risultati esatti.
Esempio :
Nell'esempio seguente, restituiremo solo 3 studenti a partire dall'ID studente 5 utilizzando la query:
SELEZIONA * DA STUDENTI LIMITE 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 tua query SQL restituisce valori duplicati, puoi utilizzare la parola chiave " DISTINCT " per rimuovere quei duplicati e restituire valori distinti. È possibile specificare più di una colonna dopo il lavoro con il tasto DISTINCT.
Esempio:
La seguente query restituirà "valori del nome del reparto" duplicati: Qui abbiamo valori duplicati con nomi IT, Physics and Arts.
SELEZIONA d.DepartmentNameDA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentId;
Questo ti darà valori duplicati per il nome del dipartimento:
Notare come esistono valori duplicati per il nome del reparto. Ora useremo la parola chiave DISTINCT con la stessa query per rimuovere quei duplicati e ottenere solo valori univoci. Come questo:
SELEZIONA DISTINTO d.DepartmentNameDA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentId;
Questo ti darà solo tre valori univoci per la colonna del nome del dipartimento:
Aggregato
Gli aggregati SQLite sono funzioni integrate definite in SQLite che raggrupperanno più valori di più righe in un unico valore.
Ecco gli aggregati supportati da SQLite:
SQLite AVG ()
Ha restituito la media per tutti i valori x.
Esempio:
Nell'esempio seguente otterremo il voto medio che gli studenti ottengono da tutti gli esami:
SELEZIONA 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 di tutte le colonne.
- COUNT (DISTINCT x): è possibile specificare una parola chiave DISTINCT prima della 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 come sono diversi:
SELEZIONA COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FROM Students;
Questo ti darà:
Come segue:
- COUNT (DepartmentId) ti fornirà il conteggio di tutti gli ID reparto e ignorerà i valori nulli.
- COUNT (DISTINCT DepartmentId) fornisce valori distinti di DepartmentId, che sono solo 3. Che sono i tre diversi valori del nome del reparto. Notare che ci sono 8 valori del nome del dipartimento nel nome dello studente. Ma solo i diversi tre valori che sono matematica, informatica e fisica.
- COUNT (*) conta il numero di righe nella tabella degli studenti che sono 10 righe per 10 studenti.
GROUP_CONCAT () - GROUP_CONCAT (X) o GROUP_CONCAT (X, Y)
La funzione di aggregazione GROUP_CONCAT concatena i valori multipli in un valore con una virgola per separarli. Ha le seguenti opzioni:
- GROUP_CONCAT (X): concatenerà tutto il valore di x in una stringa, con la virgola "," utilizzata come separatore tra i valori. I valori NULL verranno ignorati.
- GROUP_CONCAT (X, Y): questo concatenerà i valori di x in una stringa, con il valore di y usato come separatore tra ogni valore invece del separatore predefinito ','. Anche i valori NULL verranno ignorati.
- GROUP_CONCAT (DISTINCT X): questo concatenerà tutti i valori distinti di x in una stringa, con la virgola "," utilizzata come separatore tra i valori. I valori NULL verranno ignorati.
GROUP_CONCAT (DepartmentName) Esempio
La seguente query concatenerà tutti i valori del nome del dipartimento dagli studenti e dalla tabella dei dipartimenti in una stringa separata da virgole. Quindi, invece di restituire un elenco di valori, un valore su ogni riga. Restituirà un solo valore su una riga, con tutti i valori separati da virgola:
SELEZIONA GROUP_CONCAT (d.DepartmentName)DA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentId;
Questo ti darà:
Questo ti darà l'elenco degli 8 valori dei nomi dei dipartimenti concatenati in una stringa separata da virgole.
GROUP_CONCAT (DISTINCT DepartmentName) Esempio
La seguente query concatenerà i valori distinti del nome del dipartimento dalla tabella studenti e dipartimenti in una stringa separata da virgole:
SELEZIONA GROUP_CONCAT (DISTINCT d.DepartmentName)DA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentId;
Questo ti darà:
Notare come il risultato sia diverso dal risultato precedente; sono stati restituiti solo tre valori che sono i nomi dei reparti distinti e i valori duplicati sono stati rimossi.
GROUP_CONCAT (DepartmentName, '&') Esempio
La seguente query concatenerà tutti i valori della colonna del nome del dipartimento dalla tabella studenti e dipartimenti in una stringa, ma con il carattere "&" invece di una virgola come separatore:
SELEZIONA GROUP_CONCAT (d.DepartmentName, '&')DA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentId;
Questo ti darà:
Notare come il carattere "&" viene utilizzato al posto del carattere predefinito "," per separare i valori.
SQLite MAX () e MIN ()
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 punteggio più alto e il punteggio più basso dalla tabella " Marks ":
SELEZIONA MAX (Mark), MIN (Mark) FROM Marks;
Questo ti darà:
SOMMA SQLite (x), Totale (x)
Entrambi restituiranno la somma di tutti i valori x. Ma sono diversi nei seguenti:
- SUM restituirà null se tutti i valori sono null, ma Total restituirà 0.
- TOTALE restituisce sempre valori in virgola mobile. SOMMA restituisce un valore intero se tutti i valori x sono un numero intero. Tuttavia, se i valori non sono un numero intero, restituirà un valore in virgola mobile.
Esempio
Nella seguente query utilizzeremo SUM e total per ottenere la somma di tutti i punteggi nelle tabelle " Marks ":
SELEZIONA SUM (Mark), TOTAL (Mark) FROM Marks;
Questo ti darà:
Come puoi vedere, TOTALE restituisce sempre un punto mobile. Ma SOMMA restituisce un valore intero perché i valori nella colonna "Segna" potrebbero essere in numeri interi.
Differenza tra SOMMA e TOTALE esempio:
Nella seguente query mostreremo la differenza tra SUM e TOTAL quando ottengono la SUM dei valori NULL:
SELEZIONA SUM (Mark), TOTAL (Mark) FROM Marks WHERE TestId = 4;
Questo ti darà:
Nota che non ci sono segni per TestId = 4, quindi ci sono valori nulli per quel test. SOMMA restituisce un valore nullo come vuoto, mentre TOTALE restituisce 0.
Raggruppare 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 (disposte) insieme in gruppi.
Per qualsiasi altra colonna che non è inclusa nel raggruppamento per colonne, è possibile utilizzare una funzione di aggregazione per essa.
Esempio:
La seguente query ti darà il numero totale di studenti presenti in ogni dipartimento.
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentIdGROUP BY d. Nome Dipartimento;
Questo ti darà:
La clausola GROUPBY DepartmentName raggrupperà tutti gli studenti in gruppi uno per ogni nome di dipartimento. Per ogni gruppo di "dipartimento", conterà gli studenti su di esso.
Clausola HAVING
Se si desidera filtrare i gruppi restituiti dalla clausola GROUP BY, è possibile specificare una clausola "HAVING" con un'espressione dopo GROUP BY. L'espressione verrà utilizzata per filtrare questi gruppi.
Esempio
Nella seguente query, selezioneremo quei dipartimenti che hanno solo due studenti:
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDA Studenti AS sINNER JOIN Dipartimenti AS d ON s.DepartmentId = d.DepartmentIdGROUP BY d. Nome DipartimentoHAVING 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.
Query e sottoquery SQLite
All'interno di qualsiasi query, è possibile utilizzare un'altra query in SELECT, INSERT, DELETE, UPDATE o all'interno di un'altra sottoquery.
Questa query annidata è chiamata sottoquery. Vedremo ora alcuni esempi di utilizzo di sottoquery nella clausola SELECT. Tuttavia, nell'esercitazione sulla modifica dei dati, vedremo come utilizzare le sottoquery con le istruzioni INSERT, DELETE e UPDATE.
Utilizzo di una sottoquery nell'esempio della clausola FROM
Nella seguente query includeremo una sottoquery all'interno della clausola FROM:
SELEZIONAREs.StudentName, t.MarkDA Studenti AS sINNER JOIN(SELEZIONA StudentId, MarkFROM Test AS tINNER JOIN Contrassegna AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
La domanda:
SELEZIONA StudentId, MarkFROM Test AS tINNER JOIN Contrassegna AS m ON t.TestId = m.TestId
La query precedente è chiamata sottoquery qui 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 di una sottoquery nell'esempio della clausola WHERE
Nella seguente query includeremo una sottoquery nella clausola WHERE:
SELEZIONA DepartmentNameDA Dipartimenti AS dDOVE NON ESISTE (SELEZIONA DepartmentIdDA Studenti AS sWHERE d.DepartmentId = s.DepartmentId);
La domanda:
SELEZIONA DepartmentIdDA Studenti AS sDOVE d.DepartmentId = s.DepartmentId
La query precedente è chiamata sottoquery qui perché è nidificata nella clausola WHERE. La sottoquery restituirà i valori DepartmentId che verranno utilizzati dall'operatore NOT EXISTS.
Questa query ti darà:
Nella query sopra, abbiamo selezionato il dipartimento in cui non sono iscritti studenti. Qual è il dipartimento di "matematica" qui.
Operazioni sugli insiemi - UNION, Intersect
SQLite supporta le seguenti operazioni SET:
UNION & UNION ALL
Combina uno o più set di risultati (un gruppo di righe) restituiti da più istruzioni SELECT in un set di risultati.
UNION restituirà valori distinti. Tuttavia, UNION ALL non includerà e includerà duplicati.
Notare che il nome della colonna sarà il nome della colonna specificato nella prima istruzione SELECT.
UNION Esempio
Nell'esempio seguente, otterremo l'elenco di DepartmentId dalla tabella studenti e l'elenco di DepartmentId dalla tabella di dipartimenti nella stessa colonna:
SELEZIONA DepartmentId AS DepartmentIdUnioned DA StudentiUNIONESELEZIONA DepartmentId FROM Dipartimenti;
Questo ti darà:
La query restituisce solo 5 righe che sono i valori di ID reparto distinti. Notare il primo valore che è il valore nullo.
SQLite UNION ALL Esempio
Nell'esempio seguente, otterremo l'elenco di DepartmentId dalla tabella studenti e l'elenco di DepartmentId dalla tabella di dipartimenti nella stessa colonna:
SELEZIONA DepartmentId AS DepartmentIdUnioned DA StudentiUNION ALLSELEZIONA DepartmentId FROM Dipartimenti;
Questo ti darà:
La query restituirà 14 righe, 10 righe dalla tabella degli studenti e 4 dalla tabella dei dipartimenti. Notare che ci sono duplicati nei valori restituiti. Inoltre, si noti 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 INTERSECT
Restituisce i valori esistenti in entrambi i set di risultati combinati. I valori presenti in uno dei set di risultati combinati verranno ignorati.
Esempio
Nella seguente query, selezioneremo i valori DepartmentId presenti in entrambe le tabelle Studenti e Dipartimenti nella colonna DepartmentId:
SELEZIONA DepartmentId FROM StudentsIntersecaSELEZIONA DepartmentId FROM Dipartimenti;
Questo ti darà:
La query restituisce solo tre valori 1, 2 e 3. Che 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.
TRANNE
Supponi di avere due elenchi di righe, list1 e list2, e desideri solo le righe di list1 che non esiste in list2, puoi utilizzare 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 seguente query, selezioneremo i valori DepartmentId che esistono nella tabella dei dipartimenti e non esistono nella tabella degli studenti:
SELEZIONA DepartmentId FROM DipartimentiTRANNESELEZIONA DepartmentId DA Studenti;
Questo ti darà:
La query restituisce solo il valore 4. Che è l'unico valore che esiste nella tabella dei dipartimenti e non esiste nella tabella degli studenti.
Gestione NULL
Il valore " NULL " è un valore speciale in SQLite. Viene utilizzato per rappresentare un valore sconosciuto o mancante. Notare che il valore nullo è completamente diverso dal valore " 0 " o vuoto "". Poiché 0 e il valore vuoto è un valore noto, tuttavia, il valore null è sconosciuto.
I valori NULL richiedono una gestione speciale in SQLite, vedremo ora come gestire i valori NULL.
Cerca valori NULL
Non è possibile utilizzare il normale operatore di uguaglianza (=) per cercare i valori nulli. Ad esempio, la query seguente cerca gli studenti che hanno un valore DepartmentId nullo:
SELEZIONA * DA Studenti DOVE DepartmentId = NULL;
Questa query non darà alcun risultato:
Poiché il valore NULL non è uguale a nessun altro valore incluso un valore null stesso, è per questo che non ha restituito alcun risultato.
- Tuttavia, per far funzionare la query, è necessario utilizzare l' operatore "IS NULL" per cercare valori null come segue:
SELEZIONA * DA Studenti DOVE DepartmentId IS NULL;
Questo ti darà:
La query restituirà quegli studenti che hanno un valore DepartmentId nullo.
- Se vuoi ottenere quei valori che non sono nulli, devi usare l' operatore " IS NOT NULL " in questo modo:
SELEZIONA * DA Studenti DOVE DepartmentId NON È NULLO;
Questo ti darà:
La query restituirà quegli studenti che non hanno un valore DepartmentId NULL.
Risultati condizionali
Se si dispone di un elenco di valori e si desidera selezionarne uno qualsiasi in base ad alcune condizioni. Per questo, la condizione per quel particolare valore dovrebbe essere vera per essere selezionato.
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 desideri selezionare un valore di testo basato sul 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 a seconda di determinate condizioni, come l'istruzione if, ad esempio.
L'operatore CASE può essere definito con diverse sintassi come segue:
- Puoi utilizzare diverse condizioni:
ASTUCCIOWHEN condizione1 THEN risultato1WHEN condizione2 THEN risultato2WHEN condizione3 THEN risultato3… ELSE risultato nFINE
- In alternativa, puoi utilizzare una sola espressione e inserire diversi valori possibili tra cui scegliere:
Espressione CASEWHEN valore1 THEN risultato1QUANDO valore2 ALLORA risultato2WHEN value3 THEN result3… ELSE restulnFINE
Notare che la clausola ELSE è facoltativa.
Esempio
Nell'esempio seguente, utilizzeremo l' espressione CASE con valore NULL nella colonna ID dipartimento nella tabella Studenti per visualizzare il testo "Nessun dipartimento" come segue:
SELEZIONARENome dello studente,ASTUCCIOQUANDO DepartmentId È NULL, POI "No Department"ELSE DepartmentIdFINE COME DepartmentIdDA Studenti;
- L'operatore CASE verificherà il valore di DepartmentId se è nullo o meno.
- Se è un valore NULL, selezionerà il valore letterale "Nessun dipartimento" 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é è definito dalle istruzioni SQL e renderà le query più facili da leggere, mantenere e comprendere.
Un'espressione di tabella comune può essere definita inserendo la clausola WITH davanti a un'istruzione SELECT come segue:
CON CTEnameCOME(Istruzione SELECT)SELECT, UPDATE, INSERT o update statement qui FROM CTE
Il " CTEname " è qualsiasi nome che puoi dare per il CTE, puoi usarlo per farvi riferimento in seguito. Si noti che è possibile definire l'istruzione SELECT, UPDATE, INSERT o DELETE su CTE
Vediamo ora un esempio di come utilizzare CTE nella clausola SELECT.
Esempio
Nell'esempio seguente, definiremo un CTE da un'istruzione SELECT, quindi lo utilizzeremo in seguito su un'altra query:
CON AllDepartmentsCOME(SELEZIONA DepartmentId, DepartmentNameDA Dipartimenti)SELEZIONAREs.StudentId,s.StudentName,a.DepartmentNameDA Studenti AS sINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
In questa query, abbiamo definito un CTE e gli abbiamo assegnato il nome " AllDepartments ". Questa CTE è stata definita da una query SELECT:
SELEZIONA DepartmentId, DepartmentNameDA Dipartimenti
Quindi, dopo aver definito il CTE, lo abbiamo utilizzato nella query SELECT che viene dopo.
Tieni presente che le espressioni di tabella comuni non influiscono sull'output della query. È un modo per definire una vista logica o una sottoquery in modo da riutilizzarli 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 che contengono join complessi, sottoquery e alcune aggregazioni. Nella sezione seguente vedremo un esempio di una query avanzata:
Dove otteniamo il,
- I nomi dei dipartimenti con tutti gli studenti per ogni dipartimento
- I nomi degli studenti separati da virgola e
- Mostra il dipartimento con almeno tre studenti
SELEZIONAREd.DepartmentName,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS StudentiDA Dipartimenti AS dINNER JOIN Students AS s ON s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameHAVING COUNT (s.StudentId)> = 3;
Abbiamo aggiunto una clausola JOIN per ottenere il DepartmentName dalla tabella dei dipartimenti. Successivamente abbiamo aggiunto una clausola GROUP BY con due funzioni aggregate:
- "COUNT" per contare gli studenti per ogni gruppo di dipartimento.
- GROUP_CONCAT per concatenare gli studenti per ogni gruppo separati da virgole in una stringa.
- Dopo il GROUP BY, abbiamo utilizzato la clausola HAVING per filtrare i dipartimenti e selezionare solo quei dipartimenti che hanno almeno 3 studenti.
Il risultato sarà il seguente:
Sommario:
Questa è stata un'introduzione alla scrittura di query SQLite e le basi per eseguire query sul database e come filtrare i dati restituiti. Ora puoi scrivere le tue query SQLite.