CREATE TABLE

Clase 12 de 34 • Curso de Bases de Datos con MySQL y MariaDB

Contenido del curso

Introducción

Diagramas Entidad Relación

Instalando nuestras herramientas

Sentencias básicas

Consultas anidadas

Geolocalización

Stored Procedures

Triggers

Bonus

33
Confesión personal: phpMyAdmin es superior
08:45 min

Próximos pasos

34
Cómo seguir aprendiendo SQL
03:02 min

Tomar examen

Comentarios

Valentín Jorge Begnis

student•

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;

Ivan Martinez

student•

👍🏻

Esteban Gutierrez Peña

student•

Muy bien crack!!!

Miguel Osuna

student•

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

Kenneth Angulo L

student•

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.

Alan Lapierre

student•

Buen aporte, si, es algo que puede complicarte y no me imagino que lo pienses en una primera instancia...

Juan David Sánchez

student•

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/

NICOLAS RUBIO

student•

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

Diana M. Camargo

student•

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;

Keren Pingo Mezones

student•

Solo falta agregar la FK a la tabla trenes

Toshiro Kuramura

student•

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

Rodrigo Angel Zucchini

student•

ESTOY IGUAL, LO SOLUCIONASTE?

JAMES WILLIAM MONTENEGRO DAZA

student•

Usa el siguientes comando, a mi me funciono asi:

mysql -u "nombreUsuario" -p <2-create-tables.sql

Samuel Ricardo Duncan Vides

student•

¿Por qué en el campo id de la tabla lines se declara 'UNSIGNED' y 'NOT NULL' si al declararlo 'AUTO_INCREMENT' automáticamente de le dice que no estará vacío ni retrocederá en el valor (1,2,3...)? . A mí no me funcionó de la manera en que lo escribió el profesor, me devolvía error de sintaxis. Hice los siguientes cambios y me funcionó:

CREATE TABLE metro_cdmx.`lines` (
    `id` BIGINT(20) PRIMARY KEY 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
)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Daniel G. Parrilla

student•

Osvaldo Arzate Santiago

student•

El poder de CHAT-GPT

Carlos Santafe

student•

Hola, tengo problemas al ejecutar este comando wn VsCode para windows.

mysql < 2.createdTables.sql

En línea: 1 Carácter: 7

+ mysql < 2.createdTables.sql

+ ~

El operador '<' está reservado para uso futuro.

+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException

+ FullyQualifiedErrorId : RedirectionNotSupported

Que debo hacer?

Facundo Peralta

student•

Este profe es uno de los mejores de platzi sin lugar a dudas! Un capo!

Luis Ángel Pérez Meléndez

student•

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;

Marco Antonio Alvarado López

student•

Depende el sistema operativo sobre el que estés trabajando, sobre Linux o Windows, desde la terminal de Windows el comando para crear la tabla en mysql sería:

& cmd.exe /c "mysql -u root -p < 2-create-tables.sql"

Manuel Espitia

student•

Luego de usar la solución que compartiste:

& cmd.exe /c mysql -u root -p < 2-create-tables.sql

Me salió este error:

En línea: 1 Carácter: 31
+ & cmd.exe /c mysql -u root -p < 2-create-tables.sql
+                               ~
El operador '<' está reservado para uso futuro.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordExcepti  
   on
    + FullyQualifiedErrorId : RedirectionNotSupported

Sabes cómo lo puedo solucionar? Tengo una laptop windows y estoy usando la terminal de vscode.

juan andres fuentes hunda

student•

a mi no me cargaba el archivo como lo decia el profesor por tal razon me toco hacerlo de otra manera persona lo necesita

mysql -u "retaxmaster" -p (entramos al mariadb)| use metro_cdmx;(entramos a la base de datos| source 2-create-tables.sql(cargamos el archivo) | SHOW TABLES (para comprobar)

Jaison Rodriguez

student•

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 "

Héctor Alejandro Toro Bernal

student•

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

juanpedrazarte67

student•

buenas tardes a alguien le salio este errorERROR 1072 (42000): Key column 'id' doesn't exist in table

y alla encontrado solucion gracias

Argenis Daniel Nieves Berroeta

student•

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

Argenis Daniel Nieves Berroeta

student•

En la terminal de Windows hay que usar el siguiente comando

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

David Julian Ortiz

student•

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

Juan Esteban Guzmán Yance

student•

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.

Francisco Alonso Gutierrez Lazo

student•

Podrías detallarlo? aun no logro entenderlo

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;

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

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;

CREATE TABLE metro_cdmx.`lines` (
    `id` BIGINT(20) PRIMARY KEY 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
)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

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

En línea: 1 Carácter: 31
+ & cmd.exe /c mysql -u root -p < 2-create-tables.sql
+                               ~
El operador '<' está reservado para uso futuro.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordExcepti  
   on
    + FullyQualifiedErrorId : RedirectionNotSupported

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

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

CREATE TABLE

Introducción

¿Cuál es la diferencia entre MySQL y MariaDB?

Proyecto del curso: metro de CDMX

Diagramas Entidad Relación

Diagramas conceptuales vs. diagramas físicos

Diagrama conceptual: estableciendo relaciones

Diagrama físico: detalles en las tablas

Reto: relaciones en el diagrama físico

Instalando nuestras herramientas

Instalación de MariaDB en Windows

Instalación de MariaDB en Linux

Instalación de MariaDB en macOS

Sentencias básicas

Archivos SQL: CREATE DATABASE

Manejo de usuarios para la base de datos

CREATE TABLE

Modificando tablas con ALTER TABLE

Playground: Manejo de Tablas con SQL

Insertando información en la base de datos

Actualizando información: UPDATE

Borrando datos: DELETE

Playground: INSERT, UPDATE y DELETE en SQL

Listando datos: SELECT

Playground: Metro de CDMX en SQL

Consultas anidadas

¿Qué son las consultas anidadas?

Creando nuestra primer consulta anidada

Playground: Metro de Bogotá en SQL

Geolocalización

¿Cómo funciona la geolocalización?

Creando nuestras consultas de geolocalización

Reto: geolocalización con consultas anidadas

Stored Procedures

¿Qué son los Stored Procedures?

Crea tus primeras Stored Procedures

Prepared statements

Triggers

¿Qué son los triggers?

Crea tus primeros triggers

Triggers compuestos

Bonus

Confesión personal: phpMyAdmin es superior

Próximos pasos

Cómo seguir aprendiendo SQL