Creare e visualizzare tabelle

Creare un database

Per prima cosa usciamo dalla console e come abbiamo fatto in precedenza creiamo un nuovo database "sql_book":

Ora colleghiamoci a PostgreSQL specificando con l'opzione -d il nome del database:

Il prompt ci dice che stiamo lavorando sul database sql_book, ma se per curiosità volessimo vedere la lista di tutti i database potremmo farlo con il metacomando \list:

Per creare un database possiamo anche usare un'istruzione direttamente all'interno della console. Il comando createdb della shell non è altro che un contenitore che esegue questa istruzione:

Verifica con il metacomando \list che il database sia stato creato.

Nota che per convenzione i nomi dei database e delle tabelle vengono scritti in snake_case: cioè con parole minuscole separate da underscore.

Connettersi a un database

Per connetterci a un altro database usiamo il metacomando \connect o \c:

 

Cancellare un database

Usiamo l'istruzione SQL DROP:

Creare una tabella

Nel database sql_book vogliamo memorizzare una lista di utenti: per ognuno un id, un nome utente, e l'informazione se l'utente è abilitato o meno. Per ogni colonna dobbiamo scegliere un tipo di dati appropriato:

Nella tabella che segue troviamo alcuni tipi di dati usati comunemente per le colonne del database:

Nota: Dalla versione 10 è stato introdotto il tipo di dato IDENTITY che dovrebbe essere usato al posto di SERIAL.

Vincoli

I vincoli (constraints) sono regole che definiscono quali tipi di valori sono permessi in alcune colonne: permettono di mantenere l'integrità e la qualità dei dati memorizzati.

I vincoli si possono applicare a una specifica colonna, a un'intera tabella o a un intero schema.

Rivediamo l'istruzione con cui abbiamo creato la tabella degli utenti:

Osserviamo che sono stati inseriti alcuni vincoli:

• UNIQUE: la colonna id ha un vincolo per cui ogni riga ha un id unico, che non si può ripetere in altre righe;
• NOT NULL: il vincolo dice che l'id non può essere lasciato vuoto;
• DEFAULT: specifica un valore di default (in questo caso TRUE) che viene inserito nella colonna se non viene specificato un altro valore;

Vedere la tabella

Il metacomando \dt permette di elencare tutte le tabelle del database:

Per vedere informazioni più dettagliate su una certa tabella possiamo usare \d:

Osserviamo che il tipo di dato "serial" che abbiamo impostato per la colonna id è stato realizzato con una colonna di numeri interi (integer) e specificando un valore di default "nextval", che è una funzione che tiene traccia del valore più alto usato e lo incrementa in modo da fornire il successivo numero disponibile.

Esercizi

1. Crea un database chiamandolo encyclopedia e collegati ad esso con psql
2. Crea una tabella countries con le seguenti colonne:
   • id: di tipo serial
   • name: varchar(50), UNIQUE, NOT NULL
   • capital: varchar(50), NOT NULL
   • population: integer
3. Crea una tabella famous_people con le seguenti colonne:
   • id: per contenere dei valori autoincrementanti (1, 2, 3, ...)
   • name: per stringhe con un massimo di 100 caratteri
   • occupation: per stringhe fino a 150 caratteri
   • date_of_birth: data di nascita sotto forma di stringa con un massimo di 50 caratteri
   • deceased: se il personaggio è defunto: vero o falso
4. Crea una tabella animals che possa contenere i seguenti dati di esempio:
   • 
   • La tabella dovrà anche contenere una colonna id autoincrementante
   • ogni animale dovrebbe avere sempre sia un nome che un nome binomiale
   • i nomi variano in lunghezza, ma non vanno mai oltre i 100 caratteri
   • la colonna del peso massimo dovrà contenere dati nel range da 0,001 kg a 40.000 kg
   • lo stato di conservazione è denotato da una combinazione di due lettere (CR, EN, VU...)
5. Scrivi il comando per mostrare la lista delle tabelle contenute nel database encyclopedia
6. Mostra lo schema per la tabella animals
7. Crea un database ls_burger e connettiti ad esso
8. Crea una tabella orders nel database ls_burger; la tabella dovrà avere le seguenti colonne:
   • id: valore intero autoincrementante
   • customer_name: stringa fino a 100 caratteri
   • burger: stringa fino a 50 caratteri
   • side: stringa fino a 50 caratteri
   • drink: stringa fino a 50 caratteri
   • order_total: valore numerico in dollari e centesimi
   • le colonne customer_name e order_total devono sempre contenere un valore

• SQL