Creazione e gestione di database e tabelle

Con questa lezione iniziamo ad utilizzare il linguaggio SQL per manipolare e leggere i nostri dati. Siccome questa guida non è un corso di SQL, la trattazione sarà piuttosto sintetica, senza analizzare a fondo il linguaggio.

Quando mostreremo codice di esempio, useremo (come già anche in lezioni precedenti) le stesse convenzioni applicate dal manuale MySQL, quindi:

  • parentesi quadre per indicare codice opzionale
  • pipe (|) per indicare opzioni alternative (racchiuse fra parentesi graffe quando è obbligatorio indicarne una)
  • puntini (…) per indicare codice omesso o codice ripetibile

Gestione dei database

Cominciamo con la creazione di un database:

CREATE DATABASE [IF NOT EXISTS] nome_db
[CHARACTER SET charset] [COLLATE collation]

Con l’opzione IF NOT EXISTS possiamo evitare la segnalazione di errore nel caso esista già un database con lo stesso nome (nel qual caso ovviamente la creazione non avviene); abbiamo già visto nella lezione 10 il significato delle opzioni relative a character set e collation. Per poter eseguire questa istruzione è necessario avere il privilegio CREATE sul database.

Per MySQL un database non è altro che una sottodirectory nella directory dei dati. Quindi se create “manualmente” una directory lì dentro vedrete che MySQL la riconoscerà come database.

ALTER DATABASE nome_db
[CHARACTER SET charset] [COLLATE collation]

Con questa istruzione possiamo modificare le impostazioni di default del database relative a character set e collation.

DROP DATABASE [IF EXISTS] nome_db

Questa è un’istruzione tanto semplice quanto potenzialmente devastante: la sua esecuzione infatti provoca la cancellazione di tutti i dati contenuti nel database. Va da sè che è necessario usare estrema attenzione prima di utilizzarla, visto che il linguaggio SQL non prevede in nessun caso la richiesta di conferme. Per eseguirla è necessario il privilegio DROP sul database.

Gestione delle tabelle

L’istruzione base per creare una tabella è la seguente:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nome_tabella
[(definizione,...)]
[opzione, ...] [select]

La tabella viene creata nel database in uso; è possibile indicare espressamente in quale database deve essere creata, indicando nome_db.nome_tabella.

La parola chiave TEMPORARY fa sì che la tabella creata sia valida e visibile solo per la presente connessione. Un’eventuale tabella omonima esistente sul database rimane nascosta dalla presenza di quella temporanea.

IF NOT EXISTS si usa, come già visto per i db, per evitare messaggi di errore nel caso la tabella esista già.

Le definizioni possono essere definizioni di colonna oppure definizioni relative alla tabella. Vediamo una definizione di colonna:

nome_colonna tipo [NOT NULL | NULL] [DEFAULT valore]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'commento'] [reference_definition]

Il tipo di colonna è uno di quelli visti nella lezione 9. Se non indicato, si considera che una colonna possa contenere valori NULL.

Il default deve essere una costante (con l’unica eccezione del valore CURRENT_TIMESTAMP ammesso per i campi di tipo TIMESTAMP) e, se non indicato, viene considerato NULL se la colonna può contenere valori NULL. In caso contrario non sarà previsto alcun default.

Attenzione: questo comportamento è stato introdotto con la versione 5.0.2 di MySQL, prima della quale veniva sempre previsto un valore di default (il default implicito per tipo di colonna quando non indicato). Questa è una differenza importante, perchè in fase di inserimento dati, se non viene indicato un valore per una colonna che non ha default esplicito ed è attivo lo strict_mode, viene generato un errore.

AUTO_INCREMENT può essere usato con una colonna di tipo intero per avere un valore sequenziale generato automaticamente dal server per ogni riga inserita in tabella. Per ogni tabella può esserci un solo AUTO_INCREMENT; la colonna deve essere indicizzata e non può avere default. In fase di inserimento si utilizza NULL per far sì che il valore venga generato da MySQL.

UNIQUE rappresenta un indice che non può contenere valori duplicati; PRIMARY KEY è la chiave primaria della tabella e, oltre a non ammettere duplicati, non può contenere valori NULL.

La “reference_definition” è una chiave esterna su un’altra tabella, e questa è la sua sintassi:

REFERENCES nome_tabella [(colonna_indice,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

MySQL permette l’uso di foreign key solo sulle tabelle InnoDB (vedere lezione 8), ma la sintassi è ammessa anche sugli altri tipi di tabelle (ovviamente senza conseguenze).

Vediamo ora le altre definizioni possibili (relative all’intera tabella):

[CONSTRAINT [simbolo]] PRIMARY KEY [tipo_indice] (colonna_indice,…)
| KEY [nome_indice] [tipo_indice] (colonna_indice,…)
| INDEX [nome_indice] [tipo_indice] (colonna_indice,…)
| [CONSTRAINT [simbolo]] UNIQUE [INDEX]
[nome_indice] [tipo_indice] (colonna_indice,…)
| [FULLTEXT|SPATIAL] [INDEX] [nome_indice] (colonna_indice,…)
| [CONSTRAINT [simbolo]] FOREIGN KEY
[nome_indice] (colonna_indice,…) [reference_definition]

Queste opzioni permettono di definire indici o chiavi esterne. Se si vuole definire un indice su più di una colonna, l’unico modo per farlo è di definirlo in questa parte della CREATE TABLE. Da notare che KEY in questo caso è sinonimo di INDEX, mentre se usato su una definizione di colonna è sinonimo di PRIMARY KEY.

Il nome dell’indice è sempre PRIMARY per le PRIMARY KEY; per gli altri indici, se non lo indicate, verrà utilizzato il nome della prima colonna che compone l’indice (con eventuali aggiustamenti per rendere i nomi unici).

Per tipo_indice e colonna_indice consultate la lezione 12 sugli indici.

Abbiamo ancora la parte relativa alle opzioni della tabella, fra le quali selezioniamo le principali:

{ENGINE|TYPE} = tipo_tabella
| AUTO_INCREMENT = valore
| AVG_ROW_LENGTH = valore
| [DEFAULT] CHARACTER SET nome_charset [COLLATE collation]
| CHECKSUM = {0 | 1}
| COMMENT = ‘stringa’
| MAX_ROWS = valore

ENGINE o TYPE: vedere lezione 8; AUTO_INCREMENT: valore iniziale per la colonna; AVG_ROW_LENGTH: lunghezza media di una riga (indicativo, utile per tabelle molto grandi); CHARACTER_SET e COLLATE: vedere lezione 10; CHECKSUM: utile per trovare più facilmente tabelle corrotte, anche se rallenta leggermente gli update; COMMENT: massimo 60 caratteri; MAX_ROWS: massimo numero indicativo di righe che si prevede di memorizzare (utile per tabelle grandi).

Abbiamo poi la possibilità di caricare dati nella tabella al momento della creazione, con una SELECT:

[IGNORE | REPLACE] [AS] SELECT …

L’eventuale valore IGNORE o REPLACE specifica come comportarsi nel caso in cui la SELECT porti a creare sulla nuova tabella situazioni di chiavi doppie. Tenete presente che una tabella copiata in questo modo riporta solo i dati, non gli indici e tantomeno l’eventuale AUTO_INCREMENT. In caso di errore nella copia la nuova tabella non verrà creata.

Abbiamo anche un modo veloce di creare una tabella vuota copiando la struttura da una tabella già esistente:

CREATE TABLE nuova_tabella LIKE tabella_originale;

È possibile modificare la struttura di una tabella con il comando ALTER TABLE:

ALTER [IGNORE] TABLE nome_tabella
alter_specification [, alter_specification] …
//Ecco i possibili valori per alter_specification:
ADD [COLUMN] definizione_colonna [FIRST | AFTER nome_colonna ]
| ADD [COLUMN] (definizione_colonna,…)
| ADD INDEX [nome_indice] [tipo_indice] (colonna_indice,…)
| ADD [CONSTRAINT [simbolo]]
PRIMARY KEY [tipo_indice] (colonna_indice,…)
| ADD [CONSTRAINT [simbolo]]
UNIQUE [INDEX] [tipo_indice] [tipo_indice] (colonna_indice,…)
| ADD [FULLTEXT|SPATIAL] [INDEX] [nome_indice] (colonna_indice,…)
| ADD [CONSTRAINT [simbolo]]
FOREIGN KEY [nome_indice] (colonna_indice,…)
[reference_definition]
| ALTER [COLUMN] nome_colonna {SET DEFAULT costante | DROP DEFAULT}
| CHANGE [COLUMN] vecchia_colonna definizione_colonna
[FIRST|AFTER nome_colonna]
| MODIFY [COLUMN] definizione_colonna [FIRST | AFTER nome_colonna]
| DROP [COLUMN] nome_colonna
| DROP PRIMARY KEY
| DROP INDEX nome_indice
| DROP FOREIGN KEY simbolo
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] nuovo_nome
| ORDER BY nome_colonna
| CONVERT TO CHARACTER SET charset [COLLATE collation]
| [DEFAULT] CHARACTER SET charset [COLLATE collation]
| opzioni

Alcune spiegazioni: la clausola IGNORE consente di proseguire in caso di chiavi duplicate, eliminando i record successivi al primo; per cambiare nome a una colonna bisogna utilizzare CHANGE, mentre per cambiare solo il tipo di dato è sufficiente MODIFY.

DISABLE KEYS consente di sospendere l’aggiornamento degli indici in fase di inserimento: utile se si devono eseguire inserimenti massivi; gli indici andranno poi ricreati conENABLE KEYS.

CONVERT TO converte i valori delle colonne al charset indicato, mentre DEFAULT CHARACTER SET si limita a modificare il charset di default della tabella.

Se si vuole solo rinominare la tabella, è possibile farlo con il comando:

RENAME TABLE nome_tabella TO nuovo_nome
[, nome_tabella2 TO nuovo_nome2] …

che consente, come vedete, di rinominare anche più tabelle contemporaneamente.

Una tabella si elimina con il comando DROP TABLE:

DROP [TEMPORARY] TABLE [IF EXISTS] nome_tabella [, nome_tabella] …

Anche qui la clasola IF EXISTS permette di evitare errori.

Per creare una tabella è necessario il permesso CREATE; per creare una tabella temporanea è richiesto lo specifico permesso CREATE TEMPORARY TABLE; per usare ALTER TABLE sono necessari i permessi ALTER, INSERT, CREATE, nonchè il permesso INDEX per eseguire DISABLE KEYS ed ENABLE KEYS. Infine il permesso DROP è richiesto per eliminare la tabella.

I nomi

Bisogna fare attenzione quando si utilizzano nomi che sono parole riservate di MySQL: supponiamo ad esempio di volere dare ad una tabella il nome select. In questo caso è necessario fare riferimento, ogni volta, al nome della tabella mettendolo fra ‘backtick’, così: CREATE TABLE `select`… Questa regola vale per tutti gli identificatori, cioè database, tabelle, colonne e indici.

I backtick si ottengono digitando sulla tastiera alt+96 (tastierino numerico).

Both comments and pings are currently closed.

Comments are closed.