CREATE TABLE
Contenido del curso
Valentín Jorge Begnis
Ivan Martinez
Esteban Gutierrez Peña
Miguel Osuna
Kenneth Angulo L
Alan Lapierre
Juan David Sánchez
NICOLAS RUBIO
Diana M. Camargo
Keren Pingo Mezones
Toshiro Kuramura
Rodrigo Angel Zucchini
JAMES WILLIAM MONTENEGRO DAZA
Samuel Ricardo Duncan Vides
Daniel G. Parrilla
Osvaldo Arzate Santiago
Carlos Santafe
Facundo Peralta
Luis Ángel Pérez Meléndez
Marco Antonio Alvarado López
Manuel Espitia
juan andres fuentes hunda
Jaison Rodriguez
Héctor Alejandro Toro Bernal
juanpedrazarte67
Argenis Daniel Nieves Berroeta
Argenis Daniel Nieves Berroeta
David Julian Ortiz
Juan Esteban Guzmán Yance
Francisco Alonso Gutierrez Lazo
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;
👍🏻
Muy bien crack!!!
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.
Buen aporte, si, es algo que puede complicarte y no me imagino que lo pienses en una primera instancia...
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/
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';
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;
Solo falta agregar la FK a la tabla trenes
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
~
The '<' operator is reserved for future use. + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : RedirectionNotSupported "
Ayudaaaaaaaaaa
ESTOY IGUAL, LO SOLUCIONASTE?
Usa el siguientes comando, a mi me funciono asi:
mysql -u "nombreUsuario" -p <2-create-tables.sql
¿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;
El poder de CHAT-GPT
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?
Este profe es uno de los mejores de platzi sin lugar a dudas! Un capo!
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;
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"
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.
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)
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 "
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;
En la terminal de Windows hay que usar el siguiente comando
Get-Content 2-create-tables.sql | mysql -u root -p
-- 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.
Podrías detallarlo? aun no logro entenderlo
