CREATE TABLE

Curso de Bases de Datos con MySQL y MariaDB

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

No tienes acceso a esta clase

¡Continúa aprendiendo! Únete y comienza a potenciar tu carrera

Si ya tienes una cuenta,

Iniciar sesión

Recursos

Aportes 34

Preguntas 6

Ordenar por:

¿Quieres ver más aportes, preguntas y respuestas de la comunidad?

o inicia sesión.

Valentín Jorge Begnis

hace un año

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;

Miguel Osuna

hace un año

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

Kenneth Angulo L

hace un año

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.

Juan David Sánchez

hace un año

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/

Diana M. Camargo

hace un año

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;

Toshiro Kuramura

hace un año

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

dgpar

hace un año

NICOLAS RUBIO

hace 7 meses

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

Osvaldo Arzate Santiago

hace 8 meses

El poder de CHAT-GPT

Héctor Alejandro Toro Bernal

hace 3 meses

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

hace 2 meses

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

argenisnieves

hace 7 meses

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

davidortiz2354

hace un año

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

juanesguzyan634

hace 10 meses

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.

GRUPO OET

hace 25 días

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;

Jaison Rodriguez

hace 2 meses

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 "

Jorge Campero

hace 3 meses

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

América Madahi Guzman Nava

hace 3 meses

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

Julia Gabriela Nieva Paredes

hace 6 meses

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;

Mauro Benito Montoya Arenas

hace 7 meses

“lines” es una palabra reservada. Tendran problemas si lo declaran como nombre de una tabla.

argenisnieves

hace 7 meses

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

Keren Pingo Mezones

hace 8 meses

Esto hice para la tabla “trenes” 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;

Mario Alexander Vargas Celis

hace 8 meses

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;

Alejandro Ivan Mendieta

hace 9 meses

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;

alvcm

hace 9 meses

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

Juan Pablo Castiblanco Contreras

hace 10 meses

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.

Luis Ángel Pérez Meléndez

hace un año

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;

Luis Valencia

hace un año

Juan Camilo Vargas Lopera

hace un año

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

JORGE ALEXANDER MÚNERA CANABAL

hace un año

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;

Carlos S. Aldazosa

hace un año

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;

mil0rd

hace un año

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;

Juan David Sánchez

hace un año

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)

Ivan Martinez

hace un año

Para los que tienen la duda de por qué quedo así la tabla “stations”, precisamente en el siguiente vídeo se usara el comando “ALTER TABLE” para agregar los valores que le faltan al campo “id”.

Estos son:

USE metro_cdmx;

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