MySQL
Creazione della tabella

Sintassi

• CREATE TABLE table_name (column_name1 data_type (size), column_name2 data_type (size), column_name3 data_type (size), ....); // Creazione della tabella di base

• CREATE TABLE nome_tabella [IF NOT EXISTS] (column_name1 data_type (size), column_name2 data_type (size), column_name3 data_type (size), ....); // Controllo creazione tabelle esistente

• CREATE [TEMPORARY] TABLE nome_tabella [IF NOT EXISTS] (column_name1 data_type (size), column_name2 data_type (size), column_name3 data_type (size), ....); // Creazione di tabelle temporanee

• CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; // Creazione di tabelle da SELECT

Osservazioni

CREATE TABLE table_name ( column_definitions ) ENGINE=engine;

Creazione di una tabella di base

L'istruzione CREATE TABLE viene utilizzata per creare una tabella in un database MySQL.

CREATE TABLE Person (
    `PersonID`      INTEGER NOT NULL PRIMARY KEY,
    `LastName`      VARCHAR(80),
    `FirstName`     VARCHAR(80),
    `Address`       TEXT,
    `City`          VARCHAR(100)
) Engine=InnoDB;

Ogni definizione di campo deve avere:

1. Nome campo: un nome campo valido. Assicurati di codificare i nomi in `-chars. Ciò garantisce che è possibile utilizzare ad esempio caratteri di spazio nel campo nomecampo.
2. Tipo di dati [Lunghezza]: se il campo è CHAR o VARCHAR , è obbligatorio specificare una lunghezza del campo.
3. Attributi NULL | NOT NULL : se viene specificato NOT NULL , qualsiasi tentativo di memorizzare un valore NULL in quel campo avrà esito negativo.
4. Vedi di più sui tipi di dati e i loro attributi qui .

Engine=... è un parametro facoltativo utilizzato per specificare il motore di archiviazione della tabella. Se non viene specificato alcun motore di archiviazione, la tabella verrà creata utilizzando il motore di memorizzazione della tabella predefinito del server (solitamente InnoDB o MyISAM).

Impostazione delle impostazioni predefinite

Inoltre, se ha senso, è possibile impostare un valore predefinito per ogni campo utilizzando DEFAULT :

CREATE TABLE Address (
    `AddressID`   INTEGER NOT NULL PRIMARY KEY,
    `Street`      VARCHAR(80),
    `City`        VARCHAR(80),
    `Country`     VARCHAR(80) DEFAULT "United States",
    `Active`      BOOLEAN DEFAULT 1,
) Engine=InnoDB;

Se durante gli inserimenti non è specificato Street , quel campo sarà NULL quando recuperato. Quando non viene specificato alcun Country momento dell'introduzione, per impostazione predefinita verrà impostato "Stati Uniti".

È possibile impostare valori predefiniti per tutti i tipi di colonna, ad eccezione dei campi BLOB , TEXT , GEOMETRY e JSON .

Creazione di tabelle con chiave primaria

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66),
    PRIMARY KEY (PersonID)
);

Una chiave primaria è un singolo NOT NULL o un identificatore a più colonne che identifica in modo univoco una riga di una tabella. Viene creato un indice e, se non dichiarato esplicitamente come NOT NULL , MySQL lo dichiarerà in modo così silenzioso e implicito.

Una tabella può avere solo una PRIMARY KEY e ad ogni tabella si consiglia di averne una. InnoDB ne creerà automaticamente uno in sua assenza (come visto nella documentazione di MySQL ) anche se questo è meno desiderabile.

Spesso, un AUTO_INCREMENT INT noto anche come "chiave surrogata", viene utilizzato per l'ottimizzazione dell'indice sottile e le relazioni con altre tabelle. Questo valore aumenterà (normalmente) di 1 ogni volta che viene aggiunto un nuovo record, a partire da un valore predefinito di 1.

Tuttavia, nonostante il suo nome, non è suo scopo garantire che i valori siano incrementali, ma semplicemente che siano sequenziali e unici.

Un valore INT autoincremento non verrà ripristinato al valore iniziale predefinito se tutte le righe della tabella vengono eliminate, a meno che la tabella non venga troncata utilizzando l'istruzione TRUNCATE TABLE .

Definizione di una colonna come chiave primaria (definizione in linea)

Se la chiave primaria è costituita da una singola colonna, la clausola PRIMARY KEY può essere posizionata in linea con la definizione della colonna:

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL PRIMARY KEY,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66)
);

Questa forma del comando è più breve e più facile da leggere.

Definizione di una chiave primaria a più colonne

È anche possibile definire una chiave primaria comprendente più di una colonna. Questo potrebbe essere fatto ad esempio sulla tabella figlio di una relazione di chiave esterna. Una chiave primaria a più colonne viene definita elencando le colonne partecipanti in una clausola PRIMARY KEY separata. La sintassi inline non è consentita qui, poiché solo una colonna può essere dichiarata PRIMARY KEY linea. Per esempio:

CREATE TABLE invoice_line_items (
    LineNum      SMALLINT UNSIGNED NOT NULL,
    InvoiceNum   INT UNSIGNED NOT NULL,
    -- Other columns go here
    PRIMARY KEY (InvoiceNum, LineNum),
    FOREIGN KEY (InvoiceNum) REFERENCES -- references to an attribute of a table
);

Si noti che le colonne della chiave primaria devono essere specificate nell'ordine di ordinamento logico, che potrebbe essere diverso dall'ordine in cui sono state definite le colonne, come nell'esempio sopra riportato.

Gli indici più grandi richiedono più spazio su disco, memoria e I / O. Pertanto le chiavi dovrebbero essere il più piccole possibile (specialmente per quanto riguarda le chiavi composte). In InnoDB, ogni "indice secondario" include una copia delle colonne del PRIMARY KEY .

Creazione di tabelle con chiave esterna

CREATE TABLE Account (
    AccountID     INT UNSIGNED NOT NULL,
    AccountNo     INT UNSIGNED NOT NULL,
    PersonID    INT UNSIGNED,
    PRIMARY KEY (AccountID),
    FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
) ENGINE=InnoDB;

Chiave esterna: una chiave esterna ( FK ) è una singola colonna o un composto composto da più colonne di una tabella di riferimento . Questo FK è confermato per esistere nella tabella di riferimento . Si consiglia vivamente che la chiave della tabella di riferimento che conferma l' FK sia una chiave primaria, ma non viene applicata. È usato come ricerca rapida nel riferimento dove non ha bisogno di essere unico, e in effetti può essere l'indice più a sinistra lì.

Le relazioni con le chiavi esterne coinvolgono una tabella padre che contiene i valori dei dati centrali e una tabella figlio con valori identici che risalgono al suo genitore. La clausola FOREIGN KEY è specificata nella tabella figlio. Le tabelle padre e figlio devono utilizzare lo stesso motore di archiviazione. Non devono essere tavoli TEMPORANEA .

Le colonne corrispondenti nella chiave esterna e la chiave di riferimento devono avere tipi di dati simili. La dimensione e il segno dei tipi interi devono essere uguali. La lunghezza dei tipi di stringa non deve essere la stessa. Per le colonne di stringa non binarie (carattere), il set di caratteri e le regole di confronto devono essere uguali.

Nota: i vincoli di chiave esterna sono supportati dal motore di archiviazione InnoDB (non da MyISAM o MEMORY). Le configurazioni DB che utilizzano altri motori accettano questa istruzione CREATE TABLE ma non rispetteranno i vincoli di chiave esterna. (Anche se le versioni più recenti di MySQL sono predefinite in InnoDB , ma è buona prassi essere esplicite.)

Clonazione di una tabella esistente

Una tabella può essere replicata come segue:

CREATE TABLE ClonedPersons LIKE Persons;

La nuova tabella avrà esattamente la stessa struttura della tabella originale, inclusi gli indici e gli attributi di colonna.

Oltre a creare manualmente una tabella, è anche possibile creare una tabella selezionando i dati da un'altra tabella:

CREATE TABLE ClonedPersons SELECT * FROM Persons;

È possibile utilizzare una qualsiasi delle normali funzionalità di un'istruzione SELECT per modificare i dati man mano che si procede:

CREATE TABLE ModifiedPersons
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

Le chiavi e gli indici primari non verranno conservati durante la creazione di tabelle da SELECT . È necessario ridichiarli:

CREATE TABLE ModifiedPersons (PRIMARY KEY (PersonID))
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

CREA TABELLA DA SELEZIONA

È possibile creare una tabella da un'altra aggiungendo un'istruzione SELECT alla fine CREATE TABLE :

CREATE TABLE stack (
    id_user INT,
    username VARCHAR(30),
    password VARCHAR(30)
);

Crea una tabella nello stesso database:

-- create a table from another table in the same database with all attributes
CREATE TABLE stack2 AS SELECT * FROM stack;

-- create a table from another table in the same database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM stack;

Crea tabelle da diversi database:

-- create a table from another table from another database with all attributes
CREATE TABLE stack2 AS SELECT * FROM second_db.stack;

-- create a table from another table from another database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM second_db.stack;

NB

Per creare una tabella uguale a un'altra tabella esistente in un altro database, è necessario specificare il nome del database in questo modo:

FROM NAME_DATABASE.name_table

Mostra struttura tabella

Se si desidera visualizzare le informazioni sullo schema della tabella, è possibile utilizzare uno dei seguenti:

SHOW CREATE TABLE child; -- Option 1

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(100) NOT NULL,
  `myParent` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mommy_daddy` (`myParent`),
  CONSTRAINT `mommy_daddy` FOREIGN KEY (`myParent`) REFERENCES `parent` (`id`) 
      ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Se usato dallo strumento della riga di comando mysql, questo è meno dettagliato:

SHOW CREATE TABLE child \G

Un modo meno descrittivo di mostrare la struttura della tabella:

mysql> CREATE TABLE Tab1(id int, name varchar(30));
Query OK, 0 rows affected (0.03 sec)

mysql> DESCRIBE Tab1; -- Option 2 

+-------+-------------+------+-----+---------+-------+ 
| Field | Type        | Null | Key | Default | Extra |  
+-------+-------------+------+-----+---------+-------+ 
| id    | int(11)     | YES  |     | NULL    |       | 
| name  | varchar(30) | YES  |     | NULL    |       |  
+-------+-------------+------+-----+---------+-------+ 

Sia DESCRIBE che DESC danno lo stesso risultato.

Per vedere DESCRIBE eseguito su tutte le tabelle di un database contemporaneamente, vedere questo esempio .

Tabella Crea con colonna TimeStamp per mostrare l'ultimo aggiornamento

La colonna TIMESTAMP mostrerà quando la riga è stata aggiornata l'ultima volta.

CREATE TABLE `TestLastUpdate` (
    `ID` INT NULL,
    `Name` VARCHAR(50) NULL,
    `Address` VARCHAR(50) NULL,
    `LastUpdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COMMENT='Last Update'
;