SQLite Interrogazione: Seleziona, Dove, LIMITE, OFFSET, Conteggio, Raggruppa per
Marco Allen
Per scrivere query SQL in un file 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,
- Aprire Risorse del computer e andare alla seguente directory “C:\sqlite" e
- Quindi apri “sqlite3.exe"
Passo 2) Apri la banca dati”TutorialSampleDB.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:
Tieni presente che il nome della colonna è ancora "Nome dello studente“; la colonna StudentName è sempre la stessa, non cambia con l'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 utilizzare il nome completo della tabella, abbiamo utilizzato i loro alias per fare riferimento ad esse.
- INNER JOIN unisce due o più tabelle insieme utilizzando una condizione. Nel nostro esempio, abbiamo unito la tabella Studenti alla tabella Dipartimenti con la colonna DepartmentId. C'è anche una spiegazione approfondita per l'INNER JOIN nella sezione "SQLite Unisci” tutorial.
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 da 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 si concatenerà in un nuovo alias “StudentIdConNome"
- Il valore stringa letterale “Id con nome: "
- con il valore del “StudentId"colonna e
- con il valore da "Nome dello studente"colonna
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 hai un valore numerico memorizzato come valore stringa come questo " '12.5' " e vuoi convertirlo in un valore numerico puoi usare l'operatore CAST per farlo in questo modo "CAST('12.5' COME REALE)“. Oppure se hai un valore decimale come 12.5 e devi ottenere solo la parte intera, puoi convertirlo in un numero intero come questo "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:
|
Esempio:
Nell'esempio seguente, proveremo i cinque operatori aritmetici con valori numerici letterali nello stesso
seleziona la clausola:
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:
|
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
"COME” – viene utilizzato per la corrispondenza dei modelli. Utilizzando “Come“, è possibile cercare 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 modello. Per esempio, NomeStudente MI PIACE '%a%' – cercherà i nomi degli studenti che contengono la lettera “a” in qualsiasi posizione nella colonna NomeStudente.
- Inizia con il modello. Per esempio, "NomeStudente MI PIACE 'a%'” – cerca i nomi degli studenti che iniziano con la lettera “a”.
- Termina con il modello. Per esempio, "NomeStudente MI PIACE '%a'” – Cerca i nomi degli studenti che terminano con la lettera “a”.
- Corrispondenza di qualsiasi singolo carattere in una stringa utilizzando la lettera di sottolineatura "_". Per esempio, "NomeStudente COME '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 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 è sensibile alle maiuscole e alle 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
BETWEEN viene utilizzato per selezionare i valori compresi in un intervallo di due valori. Per esempio, "X TRA Y E Z" restituirà true (1) se il valore X è compreso tra i due valori Y e Z. Altrimenti, restituirà false (0). "X TRA Y E Z" è equivalente a "X >= Y E 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 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).
Come questo: "colIN(x, y, z)“. Ciò 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 a utilizzare più operatori OR. IL "DOVE IDStudente IN(2, 4, 6, 8)" è equivalente a " DOVE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);"
Mi Piace
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 degli operandi non contiene il primo operando. Come questo: "col NON IN(x, y, z)“. Ciò equivale 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à:
Solo i tre dipartimenti”Informatica, fisica e arte" verrà restituito. E il nome del dipartimento”Matematica" non verrà restituito perché non ci sono studenti in quel dipartimento, quindi l'ID dipartimento non esiste nella tabella degli studenti. Ecco perché l'operatore EXISTS ha ignorato il "Matematica" Dipartimento.
SQLite NON
Reverse il risultato dell'operatore precedente che viene dopo. 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);
Uscita:
Solo il dipartimento”Matematica " verrà restituito. Perché il "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. Per esempio, "SPOSTAMENTO LIMITE 4 4" ignorerà le prime 4 righe e restituirà 4 righe a partire dalla quinta riga, quindi otterrai le righe 5,6,7 e 8.
Tieni presente che la clausola OFFSET è facoltativa, puoi scriverla come "LIMITE 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:
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 tua query SQL restituisce valori duplicati, puoi utilizzare il comando "DISTINCT" parola chiave per rimuovere tali duplicati e restituire valori distinti. È possibile specificare più di una colonna dopo il tasto 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 punteggio più alto e il punteggio più basso da “Marks" tavolo:
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 query seguente useremo SUM e total per ottenere la somma di tutti i voti nella “Marks"tabelle:
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 "NULL" Il valore è un valore speciale in SQLite. Viene utilizzato per rappresentare un valore sconosciuto o mancante. Tieni presente che il valore null è totalmente diverso da "0" o un valore "" vuoto. Poiché 0 e il valore vuoto sono valori noti, 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 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, per far funzionare la query, è necessario utilizzare il file "È ZERO" operatore per cercare 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 vuoi ottenere quei valori che non sono nulli, allora devi usare il comando "NON È NULL” operatore 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 il CASI espressione con NULL valore nella colonna ID dipartimento nella 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 "Nome CTE" è qualsiasi nome tu possa dare al CTE, puoi usarlo per farvi riferimento in seguito. Nota che puoi definire istruzioni SELECT, UPDATE, INSERT o DELETE sui 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 un CTE e gli abbiamo dato il nome “Tutti i dipartimenti“. Questo CTE è stato definito 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 a ISCRIVITI clausola per ottenere DepartmentName dalla tabella Departments. 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 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:
Sintesi
Questa è stata un'introduzione alla scrittura SQLite query e nozioni di base sull'esecuzione di query sul database e su come filtrare i dati restituiti. Ora puoi scrivere il tuo SQLite interrogazioni.
