No tienes acceso a esta clase

隆Contin煤a aprendiendo! 脷nete y comienza a potenciar tu carrera

Compra acceso a todo Platzi por 1 a帽o

Antes: $249

Currency
$209

Paga en 4 cuotas sin intereses

Paga en 4 cuotas sin intereses
Suscr铆bete

Termina en:

13D
8H
40M
15S
Curso de Bases de Datos con MySQL y MariaDB

Curso de Bases de Datos con MySQL y MariaDB

Carlos Eduardo G贸mez Garc铆a

Carlos Eduardo G贸mez Garc铆a

CREATE TABLE

12/34
Recursos

Aportes 34

Preguntas 6

Ordenar por:

驴Quieres ver m谩s aportes, preguntas y respuestas de la comunidad?

o inicia sesi贸n.

Asi me quedaron las tablas del reto propuesto 馃槂

CREATE TABLE `stations` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(`id`)
) 
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trains` (
  `serial_number` VARCHAR(10) NOT NULL, 
  `line_id` BIGINT(20) UNSIGNED NOT NULL,
  `type` TINYINT(4) NOT NULL,
  `year` INT(4) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(serial_number),
  CONSTRAINT `fk_line`
  FOREIGN KEY(`line_id`) REFERENCES `lines`(`id`)
)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

para hacer comillas invertidas en windows se hacen con alt+96

Importante el listado de caracteres (Charset) para que la base de datos pueda almacenar los caracteres latinos.
.
Hace mucho tiempo me paso en un proyecto, la aplicacion funcionaba (era .NET y usaba SQL) pero algunas busquedas fallaban.
.
Era el cotejado, el collate no se correspondia, luego de cambiarlo para caracteres latinos, todo funciono bien.

Me puse a averiguar los tipos de datos existentes en MySQL/MariaDB e hice un tutorial con esa informaci贸n.

https://platzi.com/tutoriales/4203-mysql-mariadb/20970-tipos-de-datos-en-mysqlmariadb/

USE metro_cdmx;

CREATE TABLE `lines`

(`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(10) NOT NULL,
`color` VARCHAR(15) NOT NULL,

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(id)
)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trains`
(`serial_number` VARCHAR(10) NOT NULL,

`line_id`BIGINT(20) UNSIGNED NOT NULL,
`type` TINYINT(4) NOT NULL,
`year` INT(4) NOT NULL,

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(serial_number)
) 
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stations`
(`id`BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(id)
)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Necesito ayuda , cuando trato de hacer los pasos dentro de la terminal no me deja crear el archivo鈥 me sale algo de que el operador 鈥<鈥 esta reservado para uso futuro鈥

"
At line:1 char:7

  • mysql < 2-create-tables.sql;
  •   ~
    

The 鈥<鈥 operator is reserved for future use.
+ CategoryInfo : ParserError: (馃槂 [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : RedirectionNotSupported
"

Ayudaaaaaaaaaa

para los que tienen problemas con el codigo y les aparecen errores como este en windows:

PS C:\Users\xxxxxx\Desktop\FirstSteps\mariadb> mysql < name_file.sql
At line:1 char:7
+mysql < 2.create-tables.sql
The 鈥<鈥 operator is reserved for future use.

To add the table before than log :

Get-Content name_file.sql | mysql -u user.example -p --database=database_name_ex;
  • eso resolveria el problema del 鈥<鈥

  • pero si quieres crear la tabla desde adentro de la terminal de mariadb te recomiendo ejecutar todo el codigo adentro

1.logiar
2.USE database_name;
3.CREATE TABLE `lines` (
    `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR (10) NOT NULL,
    `color` VARCHAR (15) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)

)
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

por otro lado me di cuenta que la primary key a mi no me servia sin las `` , recomiendo agregarlas si tienes el mismo problema**

Si sigues com probemas te recomiendo revisar tus permisos como administrador:

SHOW GRANTS FOR 'nombre_de_usuario'@'localhost';

El poder de CHAT-GPT

As铆 cree mis tables:

MariaDB [metro_cdmx]> show create table `lines`;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lines | CREATE TABLE `lines` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `color` varchar(15) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [metro_cdmx]> show create table `trains`;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                     |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trains | CREATE TABLE `trains` (
  `serial_number` varchar(10) NOT NULL,
  `line_id` bigint(20) unsigned NOT NULL,
  `type` tinyint(4) NOT NULL,
  `year` int(4) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [metro_cdmx]> show create table `stations`;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                     |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stations | CREATE TABLE `stations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)

MariaDB [metro_cdmx]> show create table `locations`;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| locations | CREATE TABLE `locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `station_id` bigint(20) unsigned NOT NULL,
  `location` point NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.006 sec)

MariaDB [metro_cdmx]> ALTER TABLE `locations` MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.035 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [metro_cdmx]> show create table `locations`;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                              |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| locations | CREATE TABLE `locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `station_id` bigint(20) unsigned NOT NULL,
  `location` point NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.011 sec)```
buenas tardes a alguien le salio este errorERROR 1072 (42000): Key column 'id' doesn't exist in table y alla encontrado solucion gracias
-- Crear las tablas

USE metro_cdmx;

CREATE TABLE IF NOT EXISTS `lines`(
    
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY(id)
)
DEFAULT CHARSET= utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stations`(
    
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY(id)
)

DEFAULT CHARSET= utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trains`(
    
    `serial_number` VARCHAR(10)  NOT NULL,

    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `type` TINYINT(4) NOT NULL,
    `year` INT(4) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY(serial_number)
)

DEFAULT CHARSET= utf8mb4
COLLATE=utf8mb4_unicode_ci;
-- Crear las tablas

USE metro;


-- ==========================================================================
-- Lines
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `lines` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Stations
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `stations` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Trains
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `trains` (
    
    `serial_number` VARCHAR(10) NOT NULL,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `type` TINYINT(4) UNSIGNED NOT NULL,
    `year` INT(4) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (serial_number),
    CONSTRAINT FK_trains_lines FOREIGN KEY (line_id) REFERENCES `lines`(`id`)
    
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Lines_stations
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `lines_stations` (
    
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `station_id` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT FK_lines_stations_lines FOREIGN KEY (line_id) REFERENCES `lines`(`id`),
    CONSTRAINT FK_lines_stations_stations FOREIGN KEY (station_id) REFERENCES `stations`(`id`)
    
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Locations
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `locations` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `station_id` BIGINT(20) UNSIGNED NOT NULL,
    `location` POINT NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT FK_locations_stations FOREIGN KEY (station_id) REFERENCES `stations`(`id`)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;



-- ==========================================================================
-- Drivers
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `drivers` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `status` BOOLEAN DEFAULT false,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Active Drivers
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `active_drivers` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `driver_id` BIGINT(20) UNSIGNED NOT NULL,
    `status` BOOLEAN DEFAULT false,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT FK_active_drivers_drivers FOREIGN KEY (driver_id) REFERENCES `drivers`(`id`)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Para los que no pueden ejecutar el c贸digo en la terminal usando 鈥<鈥 Yo lo que hice fue copiar y pegar el c贸digo tal como lo hicimos para crear la database, pero en este caso para lines con todo el c贸digo. M谩s sencillo porque no encontr茅 soluci贸n.

Aqui les dejo mi codigo del reto de la tabla stations. USE metro\_cdmx; CREATE TABLE stations ( id\_station BIGINT(20) UNSIGNED NOT NULL AUTO\_INCREMENT, nombre VARCHAR(50) NOT NULL, created\_at TIMESTAMP NOT NULL DEFAULT CURRENT\_TIMESTAMP, updated\_at TIMESTAMP NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP, PRIMARY KEY (id\_station) ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_unicode\_ci;
para los que utiliza vscode en windows, y tienen proble de crear las tablas por que sale este error: The '<' operator is reserved for future use. yo segui los siguientes pasos: 1.logiar: mysql -u miusuario -p 2.USE database\_name; 3\. SOURCE subdirectorio/2-CREATE-TABLES.sql; Aseg煤rse de cambiar `"subdirectorio"` al nombre real del subdirectorio en el que se encuentra el archivo SQL en su equipo, como por ejemplo : " C:/Users/Usuario/Documents/2-CREATE-TABLES.sql "
CREATE TABLE `trains` (
    `serial_number` BIGINT(20) UNSIGNED NOT NULL,

    `type` TINYINT(10) NOT NULL,
    `YEAR` INT(4) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (serial_number)
)
DEFAULT CHARSET=utf8mb4

CREATE TABLE `stations` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(50) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
)
DEFAULT CHARSET=utf8mb4

Dejo mi c贸digo con la Foreign Key de la tabla de trenes

S贸lo un detalle, no necesitamos entrar a nuestro servidor de base de datos y seleccionar la base si en el script agregamos la instrucci贸n para seleccionar la base, de la siguiente forma:

-- Crear las tablas

USE metro_cdmx;

CREATE TABLE `lines`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `color`VARCHAR(15) NOT NULL,
    `created_at`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id)
)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

鈥渓ines鈥 es una palabra reservada. Tendran problemas si lo declaran como nombre de una tabla.

Si est谩s trabajando en Windows y te sale este error:

At line:1 char:18
+ mysql -u root -p < 2-create-tables.sql
+                  ~
The '<' operator is reserved for future use.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

En Windows PowerShell no soporta el operador de entrada 鈥<鈥

En lugar de como indica el instructor en el video, usen:

Get-Content 2-create-tables.sql | mysql -u root -p

De aqu铆 obtuve la informaci贸n.

https://suay.site/?p=3223

Esto hice para la tabla 鈥渢renes鈥 que lleva una llave for谩nea

CREATE TABLE `trains` (
   `serial_number` VARCHAR(10) NOT NULL,
   `line_id` BIGINT(20) UNSIGNED NOT NULL,
   `type` TINYINT(4) NOT NULL,
   `year` INT NOT NULL,
   `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   CONSTRAINT trains_pkey PRIMARY KEY(serial_number),
   CONSTRAINT line_fkey FOREIGN KEY(line_id)
     REFERENCES `lines`(id)
) 
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

mi c贸digo

USE metro_cdmx;

CREATE TABLE `estaci贸n` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)

)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trenes` (

    `serial_number` varchar(10) NOT NULL ,
    `line_id` BIGINT(20) SIN FIRMAR NO NULL,
    `tiny` INT(4) NOT NULL,
    `a帽o` INT(4) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(serial_number)

)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

aqu铆 los retos

USE metro_cdmx

CREATE TABLE `stations`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `trains`(
    `serial_number` BIGINT(20) NOT NULL,
    `type` TINYINT(4) NOT NULL,
    `year` INT(4) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (serial_number)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Para el campo updated_at en mysql tendria que ser:

`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

para que se registre automaticamente la fecha y hora cada vez que se modifique el registro

Este es un aporte de un error, muy comun pero me paso, jajaja. Por favor guarden el documento antes de ejecutar, las comillas deben ser las back ` y no ', a mi me dio error.

NO SE POR QUE NO MEDEJA HACER LAS TABLAS CON EL ERCHIVO, pero asi queda mi codigo

-- crear las tablas
USE metro_cdmx;

CREATE table `lines` (
    -- columnas de la tabla 
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `stations` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(`id`)
) 
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trains` (
  `serial_number` VARCHAR(20) NOT NULL,
  `line_id` BIGINT UNSIGNED NOT NULL,
  `type` ENUM('moderno', 'viejo') NOT NULL,
  `year` YEAR NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`serial_number`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
USE metro_cdmx

DROP TABLE IF EXISTS `trains`;
DROP TABLE IF EXISTS `stations`;

CREATE TABLE `trains`(

    `serial_number` VARCHAR(10) NOT NULL,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `type` TINYINT(4) NOT NULL,
    `year` INT(4) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (serial_number),
    CONSTRAINT `fk_train-line`
        FOREIGN KEY (line_id) REFERENCES `lines`(`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE `stations`(

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
);

Soluci贸n al reto 馃榾

--Reto crear tablas de tations y drivers

USE metro_cdmx;

CREATE TABLE `stations`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
)
DEFAULT CHARSET =utf8mb4
COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `drivers` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
    `status` BOOLEAN NOT NULL DEFAULT FALSE,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
)
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

Reto en un archivo:

-- Crear las tablas stations y drivers

USE metro_cdmx;

CREATE TABLE `stations` (

  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(10) NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id)
)
DEFAULT CHARSET=utf8mb4;

CREATE TABLE `drivers` (

  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(10) NOT NULL,
  `status` BOOLEAN NOT NULL DEFAULT false,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id)
)
DEFAULT CHARSET=utf8mb4;

Estoy practicando con un proyecto que tengo en mente, as铆 qued贸 mi tabla:

-- Crear la tabla

USE plantelesmx

CREATE TABLE `cctv2021` (

    `id_plantel` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `nombre_centro` CHAR(100) NOT NULL,

    `id_tipo` TINYINT(1) NOT NULL,

    `tipo_centro` CHAR(45) NOT NULL,

    `id_administrativa` TINYINT(2) NOT NULL,

    `nombre_estado` VARCHAR(8) NOT NULL,

    `bol_status` TINYINT(1) NOT NULL,

    `status` VARCHAR(8) NOT NULL,

    `fundacion` INT(5) NOT NULL,

    `alta_sis` VARCHAR(30) NOT NULL,

    `cambio_sis` VARCHAR(30) NOT NULL,

    `clausura` VARCHAR(30) NOT NULL,

    `reapertura` VARCHAR(30) NOT NULL,

    `reapertura_cambio` VARCHAR(30) NOT NULL,

    `vialidad_principal` TEXT NOT NULL,

    `vialidad_derecha` TEXT NOT NULL,

    `vialidad_izquierda` TEXT NOT NULL,

    `vialidad_posterior` TEXT NOT NULL,

    `num_exterior` INT(7) NOT NULL,

    `letra_exterior` VARCHAR(5) NOT NULL,

    `num_interior` INT(5) NOT NULL,

    `letra_interior` VARCHAR(5) NOT NULL,

    `id_estado` TINYINT(2) NOT NULL,

    `entidad_federativa` VARCHAR(35) NOT NULL,

    `id_mnpo` SMALLINT(3) NOT NULL,

    `municipio` VARCHAR(100) NOT NULL,

    `id_localidad` INT(4) NOT NULL,

    `localidad` VARCHAR(100) NOT NULL,

    `id_ambito` TINYINT(1) NOT NULL,

    `ambito` CHAR(6) NOT NULL,

    `id_asentamiento` INT(4) NOT NULL,

    `asentamiento` VARCHAR(200) NOT NULL,

    `id_tipo_asentamiento` INT(2) NOT NULL,

    `tipo_asentamiento` VARCHAR(25) NOT NULL,

    `cp` INT(5) NOT NULL,

    `ubicacion` TEXT NOT NULL,

    `latitud` FLOAT(15) NOT NULL,

    `longitud` FLOAT(15) NOT NULL,

    `id_sostenimiento` SMALLINT(3) NOT NULL,

    `sostenimiento` INT(7) NOT NULL,

    `id_subsostenimiento` TINYINT(2) NOT NULL,

    `subsostenimiento` VARCHAR(100) NOT NULL,

    `id_dependencia1` TINYINT(2) NOT NULL,

    `dependencia1` VARCHAR(100) NOT NULL,

    `id_dependencia2` TINYINT(2) NOT NULL,

    `dependencia2` VARCHAR(100) NOT NULL,

    `id_dependencia3` TINYINT(2) NOT NULL,

    `dependencia3` VARCHAR(80) NOT NULL,

    `id_dependencia4` TINYINT(2) NOT NULL,

    `dependencia4` VARCHAR(90) NOT NULL,

    `id_dependencia5` TINYINT(1) NOT NULL,

    `dependencia5` VARCHAR(60) NOT NULL,

    `id_servicio` VARCHAR(5) NOT NULL,

    `servicio` VARCHAR(115) NOT NULL,

    `id_opera_dependencia1` TINYINT(1) NOT NULL,

    `opera_dependencia1` VARCHAR(35) NOT NULL,

    `id_opera_dependencia2` TINYINT(1) NOT NULL,

    `opera_dependencia2` VARCHAR(95) NOT NULL,

    `id_opera_dependencia3` TINYINT(2) NOT NULL,

    `opera_dependencia3` VARCHAR(90) NOT NULL,

    `id_opera_dependencia4` TINYINT(2) NOT NULL,

    `opera_dependencia4` VARCHAR(90) NOT NULL,

    `id_opera_dependencia5` TINYINT(1) NOT NULL,

    `opera_dependencia5` VARCHAR(75) NOT NULL,

    `supervision` VARCHAR(10) NOT NULL,

    `curp` VARCHAR(18) NOT NULL,

    `rfc` VARCHAR(13) NOT NULL,

    `institucion` VARCHAR(10) NOT NULL,

    `turno1` TINYINT(20) NOT NULL,

    `turno2` TINYINT(20) NOT NULL,

    `turno3` TINYINT(20) NOT NULL,

    `id_nivel_servicio1` TINYINT(1) NOT NULL,

    `nivel_servicio1` VARCHAR(15) NOT NULL,

    `id_nivel_servicio2` TINYINT(1) NOT NULL,

    `nivel_servicio2`VARCHAR(25) NOT NULL,

    `id_nivel_servicio3` TINYINT(1) NOT NULL,

    `servicio_cam` TEXT NOT NULL,

    `id_caracteristica1` TINYINT(1) NOT NULL,

    `caracteristica1` VARCHAR(10) NOT NULL,

    `id_caracteristica2` TINYINT(2) NOT NULL,

    `caraceristica2` VARCHAR(10) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id_plantel)

)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

As铆 me quedaron las tablas de esta clase:

-- Creation of the lines table
CREATE TABLE `lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  `color` VARCHAR(15) NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`id`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Creation of the stations table
CREATE TABLE `stations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`id`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Creation of the trains table
CREATE TABLE `trains` (
  `serial_number` VARCHAR(20) NOT NULL,
  `line_id` BIGINT UNSIGNED NOT NULL,
  `type` ENUM('moderno', 'viejo') NOT NULL,
  `year` YEAR NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`serial_number`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
MariaDB [metro_cdmx]> DESC `lines`;
+------------+---------------------+------+-----+---------------------+----------------+
| Field      | Type                | Null | Key | Default             | Extra          |
+------------+---------------------+------+-----+---------------------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| name       | varchar(10)         | NO   |     | NULL                |                |
| color      | varchar(15)         | NO   |     | NULL                |                |
| created_at | timestamp           | NO   |     | current_timestamp() |                |
| updated_at | timestamp           | NO   |     | current_timestamp() |                |
+------------+---------------------+------+-----+---------------------+----------------+
5 rows in set (0.001 sec)

MariaDB [metro_cdmx]> DESC `stations`;
+------------+---------------------+------+-----+---------------------+----------------+
| Field      | Type                | Null | Key | Default             | Extra          |
+------------+---------------------+------+-----+---------------------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| name       | varchar(50)         | NO   |     | NULL                |                |
| created_at | timestamp           | NO   |     | current_timestamp() |                |
| updated_at | timestamp           | NO   |     | current_timestamp() |                |
+------------+---------------------+------+-----+---------------------+----------------+
4 rows in set (0.001 sec)

MariaDB [metro_cdmx]> DESC `trains`;
+---------------+-------------------------+------+-----+---------------------+-------+
| Field         | Type                    | Null | Key | Default             | Extra |
+---------------+-------------------------+------+-----+---------------------+-------+
| serial_number | varchar(20)             | NO   | PRI | NULL                |       |
| line_id       | bigint(20) unsigned     | NO   |     | NULL                |       |
| type          | enum('moderno','viejo') | NO   |     | NULL                |       |
| year          | year(4)                 | NO   |     | NULL                |       |
| created_at    | timestamp               | NO   |     | current_timestamp() |       |
| updated_at    | timestamp               | NO   |     | current_timestamp() |       |
+---------------+-------------------------+------+-----+---------------------+-------+
6 rows in set (0.001 sec)

Para los que tienen la duda de por qu茅 quedo as铆 la tabla 鈥渟tations鈥, precisamente en el siguiente v铆deo se usara el comando 鈥淎LTER TABLE鈥 para agregar los valores que le faltan al campo 鈥渋d鈥.

Estos son:

USE metro_cdmx;

ALTER TABLE `stations`
MODIFY `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(id);