Database SQL

PostgreSQL

Connessione
Connettersi a singularity:
ssh n.cognome@singularity.9dreams.it

Accedere alla console:
psql

Per uscire:
\q
oppure:
exit

Per avere informazioni sulla connessione:
\conninfo

Per vedere un elenco delle tabelle presenti nel database:
\dt

Schema
Possiamo immaginare gli "schema" come cartelle all'interno del database. Per crearne uno:

CREATE SCHEMA esercizi;

Per vedere l'elenco degli schema presenti:
\dn

o per maggiori informazioni:

\dn+

Eliminare uno schema:
DROP SCHEMA esercizi;

Eliminare uno schema con tutte le sue tabelle:
DROP SCHEMA esercizi CASCADE;

Usare un certo schema:
SET search_path TO esercizi;

Vedere le tabelle presenti in uno schema:
\dt

Tabelle
Per prima cosa entra nello schema in cui vuoi lavorare:
SET search_path TO esercizi;

Creare una tabella:
CREATE TABLE users (
       id serial UNIQUE NOT NULL,
       username char(25),
       enabled boolean DEFAULT TRUE
);

  • users è il nome della tabella
  • id, username e enabled sono le tre colonne
  • serial, char(25) e boolean i tipi di dati
  • UNIQUE e NOT NULL dei vincoli
  • DEFAULT TRUE il valore di default per la colonna

Eliminare una tabella:
DROP TABLE users;

Vedere un elenco delle tabelle:
\dt

o per maggiori informazioni:

\dt+


Descrivere la struttura di una tabella:
\d users

o per maggiori informazioni

\d+ users

Modificare una tabella

Cambiare il nome di una tabella:
ALTER TABLE users
RENAME TO utenti;

Cambiare il nome di una colonna:
ALTER TABLE utenti
RENAME COLUMN username TO full_name;

Cambiare il tipo di dati di una colonna:
ALTER TABLE utenti
ALTER COLUMN full_name TYPE varchar(25);

Aggiungere una colonna:
ALTER TABLE users
ADD COLUMN cognome varchar(30);

Eliminare una colonna:
ALTER TABLE users
DROP COLUMN cognome;

Tipi di dato




Inserire i dati 
INSERT INTO utenti
  (username, enabled)
VALUES
  ('Andrea Ros', true);

INSERT INTO utenti
  (username, enabled)
VALUES
  ('Andrea Ros', true),
  ('Paolo Rompi', false),
  ('Gaetana Figliuola', true);

Se una colonna ha un valore di default possiamo anche inserire delle righe omettendo il valore di quella colonna:
INSERT INTO utenti
  (username)
VALUES
 ('Marcella Proietti'),
 ('Alessandro Canucci');

Simbolo dell'apostrofo

Se la stringa che devi inserire contiene un apostrofo, inseriscilo doppio in modo che non venga interpretato come la chiusura della stringa... ad esempio:
VALUES ('Marco Dall''Acqua')

Selezionare i dati
Per vedere tutte le righe di una tabella:
SELECT *
FROM utenti;

Per selezionare solo alcune colonne:
SELECT username
FROM utenti;

Filtri

Per filtrare i dati in base al valore di una colonna:
SELECT *
FROM utenti
WHERE id > 3;

Operatori di confronto

È possibile usare gli operatori consueti (> < >= <= =) oltre a quello di disuguaglianza (<> oppure !=)

Per filtrare i dati in base a più colonne:
SELECT *
FROM utenti
WHERE id > 3 AND enabled = true;

Per cercare parte di una stringa:
SELECT *
FROM utenti
WHERE username LIKE 'A%';

Trova tutti gli utenti che iniziano per A:
 
SELECT *
FROM utenti
WHERE username LIKE '%A%';

Trova tutti gli utenti che contengono una A:

Ordinamento

Per ordinare i dati in base al valore di una colonna:
SELECT *
FROM utenti
WHERE id > 2
ORDER BY username;

Per selezionare i dati in ordine decrescente:
SELECT *
FROM utenti
WHERE id > 2
ORDER BY username desc;

Limiti

Per selezionare solo le prime tre righe:
SELECT *
FROM utenti
ORDER BY username
LIMIT 3;

Per prendere tre righe saltando le prime due:
SELECT *
FROM utenti
ORDER by username
LIMIT 3
OFFSET 2;

Modificare i dati 
UPDATE utenti
SET enabled = false
WHERE username = 'Andrea Ros';

Per modificare il valore di una colonna per tutte le righe:
UPDATE utenti
SET enabled = false;

Eliminazione dei dati

Per eliminare una riga:
DELETE FROM utenti
WHERE id = 3;

Per cancellare tutte le righe:
DELETE FROM utenti;

Relazione uno a molti 
CREATE TABLE indirizzi (
id serial UNIQUE NOT NULL,
destinatario varchar(50),
via varchar(25)
);

Usiamo id come chiave primaria:

ALTER TABLE indirizzi
ADD PRIMARY KEY(id);

Aggiungiamo una tabella per le città:

CREATE TABLE città (
id serial UNIQUE NOT NULL,
nome char(25),
cap char(5),
provincia char(2)
);

Inseriamo qualche dato:
INSERT INTO città
(nome,cap,provincia)
VALUES
('San Donà di Piave', '30027', 'VE'),
('Roncade', '31056', 'TV'),
('Silea', '31057', 'TV');

Una certa città è collegata a molti indirizzi: inseriamo una chiave esterna (città_id) che faccia riferimento alla tabella delle città:
ALTER TABLE indirizzi
ADD COLUMN città_id integer NOT NULL,
ADD FOREIGN KEY(città_id)
  REFERENCES città(id)
  ON DELETE CASCADE;

Inseriamo qualche indirizzo:
INSERT INTO indirizzi
(destinatario,via,città_id)
VALUES
('Andrea Ros','XIII Martiri, 86',1),
('Paola Pazzaglia','Canestrelli, 12',3),
('Esmeralda Cacioli','Archibugi, 47',3),
('Maurizia Palude','Carmelitani, 12b', 1);

Joins 
SELECT *
FROM indirizzi
JOIN città
ON indirizzi.città_id = città.id;


Selezioniamo solo le colonne che ci interessano:
SELECT indirizzi.destinatario, città.nome, città.cap, città.provincia
FROM indirizzi
JOIN città
ON indirizzi.città_id = città.id;


Se non c'è il rischio di fare confusione con nomi di colonna uguali nelle due tabelle, possiamo anche omettere i nomi delle tabelle (lasciamo città.nome perché la rende più leggibile):
SELECT destinatario, città.nome, cap, provincia
FROM indirizzi
JOIN città
ON indirizzi.città_id = città.id;

Aliasing

Con l'aliasing possiamo abbreviare le query
SELECT destinatario, c.nome, cap, provincia
FROM indirizzi AS i
JOIN città AS c
ON i.città_id = c.id;

Aliasing di colonne

Può far comodo cambiare il nome di una colonna nella tabella risultante:
SELECT destinatario, città.nome AS città, cap, provincia
FROM indirizzi
JOIN città
ON indirizzi.città_id = città.id;


Nota: la città di Roncade non compare nella tabella, perché nessun indirizzo fa riferimento ad essa.


Tipi di Join

  • INNER JOIN (default): intersezione, righe che hanno valori in comune nella condizione
  • LEFT JOIN: prende tutte le righe della tabella di sinistra e le collega a quelle di destra; se non c'è una corrispondenza le colonne mancanti vengono riempite con NULL
  • RIGHT JOIN: prende tutte le righe della tabella di destra, e se non c'è una corrispondenza a sinistra riempie con NULL
  • FULL JOIN: è una combinazione di LEFT e RIGHT, prende tutte le righe di entrambe le tabelle, e dove non c'è una corrispondenza riempie con NULL
  • CROSS JOIN: incrocia tutte le possibili combinazioni della tabella di sinistra con quella di destra (va omessa la clausola ON)


Backup dei dati

Per esportare tutto il database in un file SQL:

pg_dump > export.sql

Per ripristinare un backup:

psql -f export.sql


Corso Max

Introduzione

Basics