MySQL
Creación de tablas

Sintaxis

• CREATE TABLE table_name (column_name1 data_type (tamaño), column_name2 data_type (tamaño), column_name3 data_type (tamaño), ....); // Creación de tablas básicas

• CREATE TABLE table_name [SI NO EXISTE] (column_name1 data_type (tamaño), column_name2 data_type (tamaño), column_name3 data_type (tamaño), ....); // Comprobación de creación de tablas existente

• CREATE [TEMPORARY] TABLE table_name [IF NOT EXISTS] (column_name1 data_type (tamaño), column_name2 data_type (tamaño), column_name3 data_type (tamaño), ....); // Creación de tablas temporales

• CREAR TABLA new_tbl [AS] SELECT * FROM orig_tbl; // Creación de tablas desde SELECT

Observaciones

CREATE TABLE table_name ( column_definitions ) ENGINE=engine;

Creación básica de tablas

La CREATE TABLE se usa para crear una tabla en una base de datos MySQL.

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

Cada definición de campo debe tener:

1. Nombre de campo: Un nombre de campo válido. Asegúrate de incluir los nombres en `-chars. Esto asegura que puede usar, por ejemplo, espacios en el nombre de campo.
2. Tipo de datos [Longitud]: si el campo es CHAR o VARCHAR , es obligatorio especificar la longitud del campo.
3. Atributos NULL | NOT NULL : Si se especifica NOT NULL , cualquier intento de almacenar un valor NULL en ese campo fallará.
4. Vea más sobre los tipos de datos y sus atributos aquí .

Engine=... es un parámetro opcional que se usa para especificar el motor de almacenamiento de la tabla. Si no se especifica ningún motor de almacenamiento, la tabla se creará utilizando el motor de almacenamiento de tablas predeterminado del servidor (generalmente InnoDB o MyISAM).

Configuración de los valores predeterminados

Además, donde tenga sentido, puede establecer un valor predeterminado para cada campo usando 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;

Si durante las inserciones no se especifica la Street , ese campo será NULL cuando se recupere. Cuando no se especifica ningún Country al insertarlo, se establecerá de forma predeterminada en "Estados Unidos".

Puede establecer valores predeterminados para todos los tipos de columna, excepto para los campos BLOB , TEXT , GEOMETRY y JSON .

Creación de tablas con clave 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 clave principal es un identificador único o de varias columnas NOT NULL que identifica de forma única una fila de una tabla. Se crea un índice , y si no se declara explícitamente como NOT NULL , MySQL los declarará de manera silenciosa e implícita.

Una tabla solo puede tener una PRIMARY KEY , y se recomienda que cada tabla tenga una. InnoDB creará automáticamente uno en su ausencia (como se ve en la documentación de MySQL ), aunque esto es menos deseable.

A menudo, una INT AUTO_INCREMENT también conocida como "clave sustituta", se utiliza para la optimización de índice delgado y las relaciones con otras tablas. Este valor (normalmente) aumentará en 1 cada vez que se agregue un nuevo registro, a partir de un valor predeterminado de 1.

Sin embargo, a pesar de su nombre, no es su propósito garantizar que los valores sean incrementales, simplemente que sean secuenciales y únicos.

Un valor INT incremento automático no se restablecerá a su valor de inicio predeterminado si se eliminan todas las filas de la tabla, a menos que la tabla se trunca utilizando la instrucción TRUNCATE TABLE .

Definiendo una columna como Clave Primaria (definición en línea)

Si la clave principal consta de una sola columna, la cláusula PRIMARY KEY se puede colocar en línea con la definición de la columna:

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

Esta forma del comando es más corta y más fácil de leer.

Definir una clave primaria de varias columnas

También es posible definir una clave principal que comprenda más de una columna. Esto se puede hacer, por ejemplo, en la tabla secundaria de una relación de clave externa. Una clave primaria de varias columnas se define enumerando las columnas participantes en una cláusula PRIMARY KEY separada. La sintaxis en línea no está permitida aquí, ya que solo una columna puede declararse PRIMARY KEY línea. Por ejemplo:

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
);

Tenga en cuenta que las columnas de la clave principal deben especificarse en orden de clasificación lógica, que puede ser diferente del orden en que se definieron las columnas, como en el ejemplo anterior.

Los índices más grandes requieren más espacio en disco, memoria y E / S. Por lo tanto, las claves deben ser lo más pequeñas posible (especialmente con respecto a las claves compuestas). En InnoDB, cada 'índice secundario' incluye una copia de las columnas de la PRIMARY KEY .

Creación de tablas con clave externa

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;

Clave externa: una clave externa ( FK ) es una columna única o un compuesto de columnas de varias columnas en una tabla de referencia . Se confirma que este FK existe en la tabla referenciada . Se recomienda encarecidamente que la clave de la tabla a la que se hace referencia confirme que el FK sea ​​una clave principal, pero no se aplica. Se utiliza como una búsqueda rápida en la referencia en la que no es necesario que sea único y, de hecho, puede ser un índice más a la izquierda.

Las relaciones de clave externa implican una tabla principal que contiene los valores de datos centrales y una tabla secundaria con valores idénticos que apuntan a su principal. La cláusula FOREIGN KEY se especifica en la tabla secundaria. Las tablas padre e hijo deben usar el mismo motor de almacenamiento. No deben ser tablas TEMPORALES .

Las columnas correspondientes en la clave externa y la clave a la que se hace referencia deben tener tipos de datos similares. El tamaño y el signo de los tipos enteros deben ser iguales. La longitud de los tipos de cadena no tiene por qué ser la misma. Para las columnas de cadena no binaria (carácter), el conjunto de caracteres y la intercalación deben ser los mismos.

Nota: las restricciones de clave foránea son compatibles con el motor de almacenamiento InnoDB (no MyISAM o MEMORY). Las configuraciones de bases de datos que usan otros motores aceptarán esta declaración CREATE TABLE pero no respetarán las restricciones de clave externa. (Aunque las versiones más nuevas de MySQL están predeterminadas en InnoDB , pero es una buena práctica ser explícitas).

Clonando una tabla existente

Una tabla se puede replicar de la siguiente manera:

CREATE TABLE ClonedPersons LIKE Persons;

La nueva tabla tendrá exactamente la misma estructura que la tabla original, incluidos los índices y los atributos de columna.

Además de crear una tabla manualmente, también es posible crear una tabla seleccionando datos de otra tabla:

CREATE TABLE ClonedPersons SELECT * FROM Persons;

Puede usar cualquiera de las características normales de una declaración SELECT para modificar los datos a medida que avanza:

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

Las claves primarias y los índices no se conservarán al crear tablas desde SELECT . Debes redeclarlos:

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

CREAR TABLA DESDE SELECCIONAR

Puede crear una tabla a partir de otra agregando una instrucción SELECT al final de la instrucción CREATE TABLE :

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

Crea una tabla en la misma base de datos:

-- 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;

Crear tablas desde diferentes bases de datos:

-- 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;

nótese bien

Para crear una tabla de otra tabla que exista en otra base de datos, debe especificar el nombre de la base de datos de esta manera:

FROM NAME_DATABASE.name_table

Mostrar estructura de tabla

Si desea ver la información de esquema de su tabla, puede utilizar uno de los siguientes:

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;

Si se usa desde la herramienta de línea de comandos mysql, esto es menos detallado:

SHOW CREATE TABLE child \G

Una forma menos descriptiva de mostrar la estructura de la tabla:

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    |       |  
+-------+-------------+------+-----+---------+-------+ 

Tanto DESCRIBE como DESC dan el mismo resultado.

Para ver DESCRIBE ejecutado en todas las tablas en una base de datos a la vez, vea este Ejemplo .

Tabla Crear con la columna TimeStamp para mostrar la última actualización

La columna TIMESTAMP mostrará cuándo se actualizó por última vez la fila.

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'
;