SQL

Cosa è un database? Un database è un posto dove le informazioni sono archiviate in maniera organizzata.

SQL è l’acronimo di Structured Query Language.

I database SQL sono spesso definiti database relazionali perché sono fatti di relazioni tra le tabelle che li compongono.

Database Management Systems (DBMS)

Un Database Management System (DBMS) è il software che viene utilizzato per lavorare con un database.

Un Relational Database Management System (RDBMS) è un software specifico per database relazionali.

Ogni RDBMS ha un’implementazione differente di SQL, ciò significa che la sintassi può variare lievemente tra i vari RDBMS.

Principali RDBMS

CRUD

CRUD è l’acronimo delle quattro maggiori operazioni che si effettuano su un database:

Create	crea
Read	leggi
Update	aggiorna
Delete	elimina

Coloro che hanno accesso in lettura al database possono eseguire soltanto Read. Coloro che possono eseguire query SQL, di solito sono data analyst o data scientist.

Esempio di comando CREATE

CREATE TABLE Tabella_di_test (id int, Nome varchar(255));

Crea una tabella di nome Tabella_di_test con due campi: id (di tipo numero), e Nome (di tipo stringa).

Altro esempio di comando CREATE

INSERT INTO Tabella_di_test (id, Nome) VALUES (1, 'Mario')

Inserisci un record nella tabella Tabella_di_test, id = 1 e Nome = ‘Mario’

Esempio di comando READ

SELECT * from Tabella_di_test

Seleziona tutti i campi della tabella Tabella_di_test

Esempio di comando UPDATE

UPDATE Tabella_di_test SET nome = 'Mario' where id = 1

Aggiorna il contenuto di Tabella_di_test impostando il campo nome come “Mario” dove l’id del record è uguale 1

Esempio di comando DELETE

DELETE FROM Tabella_di_test WHERE id = 1

Elimina da Tabella_di_test i record che hanno il campo id uguale a 1

Il comando SELECT

La Query basilare è:

SELECT * FROM NomeTabella

questo comando significa: mostra tutti i dati contenuti nella tabella NomeTabella.

Anche se SQL è case-insensitive, per ragioni di leggibilità è meglio mettere in maiuscolo solo le keyword SQL e lasciare in minuscolo il resto.

Ordine di combinazione delle clausole

SELECT	quali colonne mostrare
FROM	tabelle da cui prendere i dati
WHERE	filtra le righe
GROUP BY	suddividi le righe in gruppi
HAVING	filtra i gruppi
ORDER BY	ordinamento delle colonne

Commentare il codice

– commenta una riga

Principali RDBMS

SQLite

SQLite è free (rilasciato sotto public domain) ed è il più leggero da installare. In SQLite il database si trova archiviato in un unico file con estensione .db.

MySQL

È free anche se ora è di proprietà della Oracle.

SQL Server

SQL Server è software proprietario della Microsoft e lavora sia su Windows che su Linux. La versione gratuita è SQL Server Express, che utilizza un solo core del processore.

Porte utilizzate da SQL

MSSQL porta: 1443 MySql porta: 3306

Per collegarsi a un database è necessario sapere dove il database si trova, la porta a cui connettersi, lo username e la password.

Fa eccezione SQLite, per collegarsi a esso, basta aprire il file .db

Tabelle

Un database relazionale contiene delle tabelle; ogni tabella contiene informazioni correlate tra loro.

Tutte le tabelle mantengono informazioni ordinate per riga e per colonna.

Caratteristiche delle tabelle:

hanno nomi univoci;
i nomi delle tabelle andrebbero utilizzati al singolare;
ogni riga descrive un insieme di informazioni correlate su un oggetto;
ogni colonna descrive una singola caratteristica di quell’insieme di informazioni;
colonne e righe possono essere in qualsiasi ordine.

Restrizioni sulle tabelle

Le restrizione possono essere di tipo logico o di tipo fisico.

Restrizione logica: Univocità di una riga all’interno di una tabella. Essa viene definita da una o più colonne specifiche, chiemate chiave primaria. Due righe non possono avere il medesimo valone in quella colonna.

Restrizione fisica: i nomi delle tabelle devono essere univoci all’interno di un database; i nomi delle colonne devono essere univoci all’interno di una tabella.

Normalizzazione di una tabella

Normalizzare una tabella consiste nello scomporre le informazioni tra colonne fino a che non possono essere ulteriormente divise.

Esempio di tabella non normalizzata, questa tabella contiene 3 colonne (o campi), si pò ulteriormente scomporre…

nome_e_cognome	telefono	indirizzo
Mario Rossi	06 123456	Via delle rose, 1 - 00126 Roma (RM)
Luigi Bianchi	06 654321	Via delle mimose, 10 - 02100 Rieti (RI)

Esempio di tabella normalizzata

nome	cognome	tel_prefisso	tel_numero	via	civico	cap	città	provincia
Mario	Rossi	06	123456	Via delle rose	1	00126	Roma	RM
Luigi	Bianchi	06	654321	Via delle mimose	10	02100	Rieti	RI

Le tabelle normalizzate offrono dei vantaggi:

le colonne sono più facili da aggiornare;
le colonne sono più facili da interrogare;
è più facile mantenere l’integrità dei dati.

Restrizioni sulle colonne

Si possono impostare diverse restrizioni sui valori permessi in una colonna di una tabella.

Esempio: not null, No duplicates, No changes.

Not Null (NN): richiede che venga immesso un valore nella colonna; Il valore NULL permette la distinzione tra un’immissione di dato voluta e una mancata immissione di dati;
No Duplicates (ND): assicura che ogni record della tabella sia univoco;
No Changes (ND): impedisce che vengano cambiati i valori di una colonna

Chiave primaria (PK - Primary Key)

La chiave primaria (PK - Primary Key) è una o più colonne che identificano un record di una tabella in modo univoco. Le PK assicurano l’univocità dei record di una tabella.

Buone pratiche:

ogni tabella dovrebbe avere una chiave primaria logica;
è consentita una sola chiava primara per tabella;
la chiave primaria non deve ammettere valori NULL o duplicati. Si consiglia di utilizzare anche il No Changes.

Tali restrizioni si possono impostare nelle definizione delle tabelle di SQL SERVER utilizzando i vincoli di colonna UNIQUE o PRIMARY KEY.

Foreign Key (FK)

Una Foreign Key è una o più colonne i cui valori corrispondono a quelli della chiave primaria della tabella correlata.

Vantaggi derivanti dalla creazione di Foreign key:

La FK permette un collegamente tra le due tabelle;
La FK permette l’integrità referenziale, assicurando che ogni valore della colonna FK sia una PK valida definita come chiave primaria.

Database di sistema

SQL SERVER viene installato con quattro database di sistema:

master;
model;
tempdb;
msdb.

Le tabelle di sistema contengono le informazioni di SQL SERVER e in ogni database utente. Le stored procedure di sistema vengono utilizzate per richiedere informazioni sui dati contenuti nelle tabelle.

Esistono due tipi di database in SQL SERVER: database di sisteme e database utente. Entrambi immagazzinano dati. I database di sistema memorizzano i cambiamenti di SQL Server nel suo insieme. In pratica, SQL SERVER utilizza i database di sistema per gestire sé stesso. I database creati dall’utente sono database utente.

Il database master

Il database master contiene le tabelle di sistema. A nessun utente è permesso di modificare il master direttamente.

NOTA: È importante avere sempre un backup aggiornato del database master.

Cosa contiene il database master?

database e device di backup disponibili;
database caricati sul server;
la quantità di memoria allocata ad ogni database;
i processi attivi (log delle transazioni);
user account;
i lock attivi;
i messaggi di errore di sistema;
le stored procedure di sistema.

Il database model

Il database model contiene le tabelle di sistema che appartengono a ogni database utente. Alla creazione di un nuovo database, SQL SERVER utilizza model come modello di riferimento.

Esso può essere utilizzato per personalizzare la struttura dei database appena creati.

Il database tempdb

Il database tempdb fornisce un’area di memmoria per le tabelle temporanee. Per lavorare su questo database non è richiesto un particolare permesso.

tempdb è uno spazio di lavoro comune per tutti i database di SQL SERVER. Quando un utente si scollega da SQL SERVER, il contenuto di tempdb di quell’utente viene cancellato.

Quando il sistema si ferma, tutto il contenuto di tempdb viene cancellato.

Il database msdb

Questo database viene utilizzato nell’identificazione dei malfunzionamenti delle attività pianificate.

Stored procedure di sistema

Si tratta di un insieme di istruzioni SQL messe a disposizione da SQL Server, per gestire SQL Server stesso e visualizzare infomrazioni sui database utente.

Si trovano nel database master, possono essere eseguite da qualsiasi database, i loro nomi iniziano tutti con _sp__

Molte stored procedure danno informazioni su un particolare oggetto o un insieme di oggetti.

sp_help (objname)
sp_helpdb (dbname)
sp_helpindex (tabname)
sp_spaceused

Query Transact-SQL

Una query è una richiesta per richiamare (SELECT), inserire (INSERT), modificare (UPDATE), cancellare (DELETE) i dati. Le query permettono di ottenere i dati dalle tabelle. Le query inviata a un database SQL SERVER vengono processate da un ottimizzatore che le analizza e determina come meglio eseguire le istruzioni contenute.

SELECT

L’istruzione SELECT è lo strumento principale per richiamare dati. Tramite SELECT è possibile ottenere dati dal sistema o da una o più tabelle. È possibile specificare quali colonne o righe richiamare.

Tramite SELECT si può anche definire il formato dell’insieme dei risultati.

Utilizzo di SELECT per:

selezioni: richieamare un sottoinsieme di righe in una o più tabelle;
proiezioni: richiamare un sottoinsieme di colonne in una o più tabelle;
unioni: collegare righe in due o più tabelle, per trovare dati su più tabelle.

Si può utilizzare SELECT per richiamare dati che non sono presenti in una tabella.

SELECT 'Testo di prova'

--------------
Testo di prova

(1 row affected)


SELECT @@VERSION

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 
…

I nomi di colonna che vengono elencati dopo SELECT rappresentano la select list, determinano quali colonne vengono selezionate per apparire nell’insieme dei risultati

I nomi delle tabelle riportati dopo FROM determinano quali tabelle saranno interrogate dall’istruzione SELECT

I nomi delle colonne possono essere specificati come nome_colonna o come nome_tabella.nome_colonna. La seconda opzione si utilizza quando vengono fatti riferimenti a colonne con lo stesso nome presenti in più tabelle.

Se si specifica nome_database.owner.nome_tabella è possibile eseguire query che fanno riferimento a un’altra tabella in un altro database.

L’ordine in cui vengono elencate le colonne dopo l’istruzione SELECT determina l’ordine in cui appariranno le colonne.

SELECT [EmployeeID], [LastName], [FirstName], [Title] FROM [NorthWind].[dbo].[Employees]

EmployeeID  LastName             FirstName  Title
----------- -------------------- ---------- ------------------------------
1           Davolio              Nancy      Sales Representative
2           Fuller               Andrew     Vice President, Sales
3           Leverling            Janet      Sales Representative
…

Per selezionare tutte le colonne di una tabella è possibile utilizzare la SELECT seguita da un asterisco *.

Questa è una query inefficiente, dato che carica le risorse di sistema, è preferibile richiamare solo le colonne strettamente necessarie nella select list. È sempre bene restringere il campo dei risultati per ottenere solo quelli di cui si ha bisogno

Per specificare quali righe richiamare da una o più tabelle si utilizza la clausola WHERE

SELECT select_list FROM _nome_tabella WHERE condizioni_di_ricerca

Le _condizioni di ricerca incluse nella clausola WHERE possono fare riferimento a colonne non incluse nella select_list.

SELECT [LastName], [FirstName], [Title] FROM [NorthWind].[dbo].[Employees] WHERE EmployeeID = 1

LastName             FirstName  Title
-------------------- ---------- ------------------------------
Davolio              Nancy      Sales Representative

(1 row affected)

Operatori di confronto: = > <= <= <>
Intervalli: BETWEEN, NOT BETWEEN
Liste: IN, NOT IN
ricerca in stringhe: LIKE, NOT LIKE
Valori non noti: IS NULL, IS NOT NULL
Operatori boooleani: AND, OR, NOT.

Bibliografia:

Microsoft SQL Server Training: hands-on self-paced training kit for version 6.5; ISBN 88-7131-836-6

bozzettidigitali.it