Corso base SQLite. Copyright (c) 2015 Antonio Gallo

(1)

Corso base SQLite

Il seguente documento è rilasciato sotto licenza Creative Commons 2.5 – Attribution – Share

alike. Per leggere una copia della licenza visita il sito http://creativecommons.org/licenses/by-sa/2.5/it/

(2)

Indice generale

1 Introduzione...2

2 Creiamo la tabella articoli in SQlite...3

2.1 Altri tipi di dato...5

2.1.1 Dati numerici...5

2.1.2 Stringhe...6

3 Il valore NOT NULL...6

4 l'istruzione INSERT: inserire dati in una tabella...6

4.1 Nel caso si inseriscano valori in un campo numerico...7

4.2 Nel caso si inserisca il valore '...8

5 L'istruzione SELECT: estrarre dati da una tabella...8

5.1 Indicare anche la tabella di appartenenza di un campo...9

5.2 Affinare la selezione con la clausola WHERE...10

5.2.1 Esempio 1...10

5.2.2 Esempio 2...10

5.2.3 Esempio 3...11

5.3 SUM, AVG, MIN, MAX, COUNT...11

5.4 GROUP BY: raggruppare i risultati...12

5.5 ORDER BY: ordinare i risultati...13

5.5.1 Esempio 1...13

5.5.2 Esempio 2...14

5.5.3 Esempio 3...15

5.6 Limitare il numero di record estratti tramite la clausa LIMIT...15

5.6.1 Esempio 1...15

5.6.2 Esempio 2...16

5.7 Mescolare la clausole WHERE, GROUP BY, ORDER BY, LIMIT...17

6 L'istruzione DELETE: cancellare record da una tabella...17

6.1 Esempio 1...17

7 L'istruzione UPDATE: modificare dei record esistenti...18

7.1 Esempio 1...18

7.2 Esempio 2...19

1 Introduzione

Un database SQlite è formato da tabelle ognuna contenente dei dati.

Ogni tabella è formata da righe e colonne. Le colonne vengono chiamate campi. Le righe vengono chiamate record e rappresentano i dati inseriti. Quindi in SQlite (e in generale in tutti i database relazionali) le tabelle sono formate da campi (colonne) e da record (righe). Ogni tabella ha un nome che viene usato per richiamare, inserire, cancellare dati.

(3)

La tabella seguente, che chiameremo articoli, ha 4 campi e 3 record.

I campi sono i seguenti:

• id: un identificativo numerico, univoco e incrementale (consigliato per ogni tabella)

• titolo: contiene il titolo dell'articolo

• categoria: contiene la categoria dell'articolo

• autore: contiene l'autore dell'articolo

• testo: contiene il testo dell'articolo tabella articoli:

id titolo categoria autore testo

1 SQlite è facile informatica Antonio Etc etc

2 SQlite for

dummies informatica Alberto Etc etc

3 Lo sport è salutare sport Luigi Etc etc

Tabella 1:

Come si vede i campi servono a dire a che tipo di dato ci stiamo riferendo (al titolo dell'articolo, alla categoria, all'autore, etc) mentre i record rappresentano gli articoli veri e propri.

2 Installazione shell di prova

E' possibile scaricarlo al seguente indirizzo:

https://www.sqlite.org/2015/sqlite-shell-win32-x86-3080803.zip

Decompattarlo e premere due volte su sqlite.exe.

Si aprirà una schermata nera.

Per creare un database:

.open nome_database.db

Verrà aperto se esiste oppure creato se non esiste.

3 Creiamo la tabella articoli in SQlite

Creiamo la nostra tabella chiamata articoli. Ecco la sintassi SQlite:

CREATE TABLE articoli (

id INTEGER PRIMARY KEY AUTOINCREMENT, titolo CHAR(200) NOT NULL,

categoria VARCHAR(200) NOT NULL,

(4)

autore VARCHAR(200) NOT NULL, testo TEXT NOT NULL,

visite INTEGER NOT NULL );

Come si vede abbiamo definito tutti i campi descritti precedentemente. Verranno analizzati nel dettagli qui di seguito:

il campo id

Il campo id è stato definito di tipo INTEGER, cioè intero. Questo tipo di dato viene utilizzato quando è necessario immagazzinare dei dati di tipo numerico come ad esempio l'età di una persona o, come in questo caso, un indice incrementale e univoco.

Inoltre il campo id è stato definito come AUTOINCREMENT. Questo serve a fare in modo che non sia necessario inserire il valore del campo id ogni volta che si inserisce un nuovo articolo, ci pensa SQlite incrementando di 1 il valore del campo id dell'articolo precedente.

PRIMARY KEY significa che il campo id è una chiave primaria, cioè il suo valore non può essere duplicato (ogni record deve avere il campo id diverso da tutti gli altri record)

il campo titolo

Il campo titolo è stato definito di tipo CHAR, cioè una stringa di testo. Il valore 200 all'interno delle parentesi serve a dire a SQlite che la stringa non sarà più lunga di 200 caratteri. Nel caso si cercasse di inserire un articolo avente un titolo più lungo di 200 caratteri SQlite taglierebbe automaticamente la parte eccedente i 200 caratteri (cioè l'inserimento avviene, ma il titolo viene troncato). Il valore 200 serve a dire a SQlite di riservare in memoria lo spazio di 200 caratteri per il campo titolo.

La tringa NOT NULL indica che nel campo titolo non è possibile inserire valori nulli (vedi in seguito).

il campo categoria

id INTEGER PRIMARY KEY AUTOINCREMENT

titolo CHAR(200) NOT NULL

(5)

Per il campo categoria vale quanto detto per il campo titolo, ma con la seguente differenza. Al posto di CHAR(200) si è scritto VARCHAR(200). Il tipo di dato VARCHAR viene anch'esso utilizzato per memorizzare le stringhe e il significato del numero tra parentesi (200) è lo stesso di prima. La differenza è che VARCHAR, al contrario di CHAR, ottimizza lo spazio utilizzato per memorizzazione del dato. VARCHAR va bene se si prevedono stringhe lunghe.

il campo autore

Per il campo autore vale quanto detto per il campo categoria

il campo testo

Il campo testo è stato definito di tipo TEXT. Il tipo TEXT viene utilizzato quando è necessario memorizzare molto teso, ad esempio migliaia di caratteri.

3.1 Altri tipi di dato

Come il PHP, anche SQlite permette di definire molti tipi di dati.

3.1.1 Dati numerici

Nel caso di dati numerici potete usare i seguenti dati:

INTEGER → serve per i numeri interi REAL → serve per i numeri con la virgola

3.1.2 Stringhe

CHAR → per stringhe di breve lunghezza

categoria VARCHAR(200) NOT NULL

autore VARCHAR(200) NOT NULL

testo TEXT NOT NULL

(6)

VARCHAR → per stringhe lunghe (oltre i 50 caratteri) TEXT → per testi lunghi (oltre i 200 caratteri)

4 Il valore NOT NULL

Quando si definisce un campo come NOT NULL SQlite non accetta che in un quel campo non si inserisca alcun valore (si deve per forza inserire qualcosa!).

5 l'istruzione INSERT: inserire dati in una tabella

Per inserire dati in una tabella esistente è necessario usare l'istruzione INSERT. La sintassi è la seguente:

INSERT INTO <nome_tabella> ( <lista_campi_da_inserire> ) VALUES (<lista_dei_valori>);

Dove <lista_campi_da_inserire> rappresenta una lista di tutti i campi che si vogliono riempire, separati da una virgola. <lista_dei_valori> rappresenta invece la lista dei valori da inserire (corrispondenti ai campi)

Ad esempio, proviamo a riempire la tabella articoli con i dati mostrati nella Tabella 1 (aggiungendo anche il valore del campo visite appena aggiunto)

Inseriamo la prima riga:

INSERT INTO articoli (titolo,categoria,autore,testo,visite) VALUES ('SQlite è facile','informatica','Antonio','Etc Etc','10');

Osserviamo che:

• La lista dei campi va scritta senza apici → (titolo, categoria, autore, testo, visite)

• La lista dei valori va scritta con gli apici all'inizio e alla fine di ogni valore ('SQlite è facile','SQlite for dummies','Antonio','Etc Etc','10')

Inseriamo ora le altre due righe:

(7)

INSERT INTO articoli (titolo,categoria,autore,testo,visite) VALUES ('SQlite for dummies','informatica','Alberto','Etc Etc','24');

INSERT INTO articoli (titolo,categoria,autore,testo,visite) VALUES ('Lo sport è salutare','sport','Luigi','Etc Etc','2');

5.1 Nel caso si inseriscano valori in un campo numerico

Nel caso si fossero inseriti valori in un campo numerico (INT o FLOAT) bisognerebbe evitare di usare gli apici, altrimenti il valore viene passato non come numero ma come stringa. Se non li si mette (come abbiamo fatto nelle due istruzioni INSERT mostrate sopra) SQlite trasformerà comunque quel numero in stringa.

Inseriamo una quarta riga evitando di usare gli apici per il valore numerico del campo visite:

INSERT INTO articoli (titolo,categoria,autore,testo,visite) VALUES ('Mangiare salutare','alimentazione','Luigi','Etc Etc',5);

Come si vede nell'istruzione INSERT precedente il valore del campo visite, essendo numerico (definito come INT), può assumere un valore ( 5 ) senza essere costretti ad inserire gli apici.

5.2 Nel caso si inserisca il valore '

Nel caso si debba inserire l'apice ' in un campo, ad esempio perché una certa parola contiene un apostrofo, è necessario precedere tale apostrofo con il carattere backslash \. Questo affinché l'apostrofo non venga confuso con gli apici che racchiudono il valore.

Esempio:

INSERT INTO articoli (titolo,categoria,autore,testo,visite) VALUES ('L\'aria pulita','ambiente','Antonio','Etc Etc',11);

Come si vede nel campo titolo era presente un apice che è stato preceduto con il carattere \.

(8)

6 L'istruzione SELECT: estrarre dati da una tabella

Ora che abbiamo inserito dei valori all'interno della tabella vogliamo provare ad estrarli. Per farlo dobbiamo usare l'istruzione SELECT. La sintassi è la seguente:

SELECT <lista_campi> FROM <nome_tabella>;

Ad esempio:

SELECT id,autore,categoria,titolo,visite FROM articoli;

che restituisce il seguente output:

+----+---+---+---+---+

| 5 | Antonio | ambiente | L'aria pulita | 11 | +----+---+---+---+---+

5 rows in set (0.00 sec)

Come si vede SQlite ha restituito i valori richiesti, con i valori incolonnati. Si noti che il campo id, benché non sia mai stato riempito, contiene un valore numerico incrementale. Infatto il suo valore è stato aggiunto in automatico da SQlite dato che il campo id era stato definito come

AUTO_INCREMENT.

Per selezionare tutti i campi è possibile usare il caratteri asterisco *, come mostrato di seguito:

SELECT * FROM articoli;

+----+---+---+---+---+---+

(9)

| 5 | L'aria pulita | ambiente | Antonio | Etc Etc | 11 | +----+---+---+---+---+---+

Come si vede in questo caso sono stati restituiti tutti i campi della tabella articoli.

6.1 Indicare anche la tabella di appartenenza di un campo

È possibile indicare, nella SELECT query, anche la tabella dalla quale si vuole estrarre un determinato campo. Esempio:

SELECT articoli.autore FROM articoli;

che è equivalente a:

SELECT autore FROM articoli;

Nella prima query abbiamo specificato che il campo autore deve essere estratto dalla tabella articoli.

L'utilità di questo si vedrà in seguito quando impareremo ad estrarre dati da più valori contemporaneamente.

6.2 Affinare la selezione con la clausola WHERE

È possibile affinare la ricerca usando la clausola WHERE, come mostrato nei seguenti esempi:

6.2.1 Esempio 1

Per selezionare tutti gli articoli scritti da Antonio (si notino gli apici attorno ad Antonio nell'istruzione seguente):

SELECT titolo,categoria,autore FROM articoli WHERE autore = 'Antonio';

(10)

+---+---+---+

| titolo | categoria | autore | +---+---+---+

| SQlite è facile | informatica | Antonio |

| L'aria pulita | ambiente | Antonio | +---+---+---+

6.2.2 Esempio 2

Per selezionare tutti gli articoli scritti da Antonio della categoria informatica:

SELECT titolo,categoria,autore FROM articoli WHERE autore = 'Antonio' AND categoria = 'informatica';

+---+---+---+

| titolo | categoria | autore | +---+---+---+

| SQlite è facile | informatica | Antonio | +---+---+---+

1 row in set (0.00 sec)

6.2.3 Esempio 3

Selezionare tutti gli articoli che hanno più di 10 visite:

SELECT * FROM articoli WHERE visite > 10;

(11)

+----+---+---+---+---+---+

Si noti che il valore 10 è stato scritto senza apici dato che corrisponde ad un campo (visite) di tipo INT. In ogni caso l'istruzione valeva anche se si scriveva 10 tra apici (come detto, SQlite lo trasformava automaticamente da stringa a numero)

SELECT * FROM articoli WHERE visite > '10';

6.3 Case sensitive e insensitive.

Di default SQLite è case sensitive. Questo significa che le stringhe “Antonio” e “antonio” sono differenti. Se volete eseguire una selezione in modo che un campo sia considerato case insensitive (cioè senza considerare maiuscole e minuscole), dovete scrivere così:

SELECT * FROM articoli WHERE autore collate nocase = 'antonio';

In questo caso gli state dicendo che il campo autore deve essere cercato senza considerare maiuscole o minuscole.

6.4 SUM, AVG, MIN, MAX, COUNT

La funzione SUM calcola la somma dei valori raggruppati (vedi nella sezione sotto il significato).

La funzione AVG calcola la media, la funzione MIN calcola il minimo, la funzione MAX calcola il massimo e la funzione COUNT calcola il numero di record raggruppati.

Ad esempio calcoliamo la somma (SUM) di tutte le visite ricevute (di tutti gli articoli), il numero minimo (MIN) e massimo (MAX) di visite e il numero medio (AVG). Calcoliamo inoltre il numero di record nella tabella tramite la funzione COUNT.

SELECT SUM(visite), MIN(visite), MAX(visite), AVG(visite), COUNT(*) FROM articoli;

+---+---+---+---+---+

(12)

| 52 | 2 | 24 | 10.4000 | 5 | +---+---+---+---+---+

1 row in set (0.00 sec)

La funzione COUNT richiede l'asterisco tra parentesi e serve per contare tutti i record raggruppati (vedi sotto)

6.5 GROUP BY: raggruppare i risultati

È possibile aggregare i risultati che hanno qualcosa in comune, ad esempio gli articoli scritti dallo stesso autore o all'interno della stessa categoria. Questo può essere utile se non si vuole che i campi doppi vengano ripetuti più volte oppure per applicare funzioni di aggregazione (SUM, AVG, MIN, MAX) sui campi raggruppati.

Estraggo tutte le categorie, evitando di ripetere più di una volta il nome della singola categoria:

SELECT categoria FROM articoli GROUP BY categoria;

+---+

| categoria | +---+

| alimentazione |

| ambiente |

| informatica |

| sport | +---+

Come sappiamo gli articoli scritti sotto la categoria informatica sono due. Quindi SQlite ha aggregato i due articoli all'interno della categoria informatica come da noi chiesto attraverso la clausola GROUP BY, e ha mostrato una sola volta il nome della categoria.

Ora possiamo chiedere di applicare una delle funzioni viste sopra sui valori raggruppati da GROUP BY:

SELECT COUNT(*),categoria FROM articoli GROUP BY categoria;

(13)

+---+---+

| COUNT(*) | categoria | +---+---+

| 1 | alimentazione |

| 1 | ambiente |

| 2 | informatica |

| 1 | sport | +---+---+

Come si vede SQlite ha raggruppato gli articoli sotto la categoria informatica. Con la funzione COUNT(*) è possibile chiedergli quanti sono i record della tabella che ha raggruppato (quindi, in questo caso, quanti sono gli articoli aventi la categoria uguale ad informatica)

6.6 ORDER BY: ordinare i risultati

I risultati estratti possono essere ordinati tramite la clausola ORDER BY, come mostrato nei seguenti esempi:

6.6.1 Esempio 1

Ordiniamo i risultati in ordine alfabetico rispetto all'autore:

SELECT * FROM articoli ORDER BY autore;

+----+---+---+---+---+---+

(14)

| 4 | Mangiare salutare | alimentazione | Luigi | Etc Etc | 5 | +----+---+---+---+---+---+

Come si vede i risultati sono stati estratti in ordine alfabeto rispetto all'autore. Si poteva ordinarli rispetto a qualunque campo.

6.6.2 Esempio 2

Ordiniamo i risultati in ordine alfabetico decrescente rispetto all'autore:

SELECT * FROM articoli ORDER BY autore DESC;

+----+---+---+---+---+---+

| 2 | SQlite for dummies | informatica | Alberto | Etc Etc | 24 | +----+---+---+---+---+---+

6.6.3 Esempio 3

(15)

Ordinare in ordine decrescente rispetto all'id (l'id viene incrementato ad ogni inserimento e quindi indica l'ordine di inserimento degli articoli. L'articolo con l'id più grande sarà l'ultimo inserito).

SELECT * FROM articoli ORDER BY id DESC;

+----+---+---+---+---+---+

| 1 | SQlite è facile | informatica | Antonio | Etc Etc | 10 | +----+---+---+---+---+---+

6.7 Limitare il numero di record estratti tramite la clausa LIMIT

È possibile specificare il numero di record che si vuole estrarre grazie alla clausola LIMIT

6.7.1 Esempio 1

Estraiamo solo due articoli:

SELECT * FROM articoli LIMIT 2;

+----+---+---+---+---+---+

(16)

| 2 | SQlite for dummies | informatica | Alberto | Etc Etc | 24 | +----+---+---+---+---+---+

6.7.2 Esempio 2

Estraiamo due articoli saltando i primi 2 record:

SELECT * FROM articoli LIMIT 2,2;

+----+---+---+---+---+---+

| 4 | Mangiare salutare | alimentazione | Luigi | Etc Etc | 5 | +----+---+---+---+---+---+

6.8 Mescolare la clausole WHERE, GROUP BY, ORDER BY, LIMIT

SELECT * FROM articoli WHERE visite > '10' ORDER BY autore LIMIT 2;

7 L'istruzione DELETE: cancellare record da una tabella

E' possibile cancellare dei record da una tabella tramite l'istruzione DELETE, la cui sintassi è la seguente:

(17)

DELETE FROM <nome_tabella> WHERE <clausola_where>;

7.1 Esempio 1

Esempio, immaginiamo di voler cancellare il record avente id=3:

DELETE FROM articoli WHERE id=3;

Ora possiamo provare ad estrarre tutti i record dalla tabella articoli:

Per ottenere il seguente output:

+----+---+---+---+---+---+

Come si vede il record avente id=3 è stato cancellato.

8 L'istruzione UPDATE: modificare dei record esistenti

È possibile modificare dei record esistenti di una tabella attraverso l'istruzione UPDATE, la cui sintassi è la seguente:

UPDATE <nome_tabella> SET <campo1>=<valore1>, <campo2>=<valore2> WHERE

(18)

<clausola_where>;

8.1 Esempio 1

Modifichiamo il titolo, l'autore, la categoria e il numero di visite del record avente id=2 della tabella articoli:

UPDATE articoli SET titolo='SQlite facile', autore='Marcello', categoria='database', visite=154 WHERE id=2;

+----+---+---+---+---+---+

Come si vede il record avente id=2 è stato modificato come richiesto.

8.2 Esempio 2

Proviamo a usare una clausola WHERE che non faccia uso del campo id. Ad esempio, modifichiamo tutte le categorie degli articoli scritti da Antonio.

(19)

UPDATE articoli SET categoria='computer' WHERE autore='Antonio';

Si noti come siano stati usati gli apici attorno alla stringa Antonio (è una stringa, non un numero).

+----+---+---+---+---+---+

| 5 | L'aria pulita | computer | Antonio | Etc Etc | 11 | +----+---+---+---+---+---+

Come si vede ora tutti gli articoli scritti da Antonio fanno parte della categoria computer.

NB: in questo secondo esempio abbiamo modificato più record contemporaneamente (due).