Create database mysql workbench how to#

In the next post we are going to learn how to insert the data in the SQL database using the python library → ← prev MySQL Workbench (Download and Installation) However, our data is big and it would be super tedious to fill the tables this way. So far, this database is set up but it is empty! Inserting values can be done manually by using: - to manually fill in a table insert into table_name (parameter1, parameter2 ) values( value1, value2 ) - Example: insert into cities(city_id, city_name, country) values( 2950159, 'Berlin', 'Germany') to retrieve all columns of a table select * from table_name - to drop a table drop table table_name - to add a column to a table : ALTER TABLE table_name DROP column_name INT - to drop a column from a table: ALTER TABLE table_name DROP COLUMN column_name In case something goes wrong and you want to start over, you can drop a table, drop/add a column. We are adding this condition because this table is a many to many relationship and hence it does not have a primary key that uniquely identifies its rows.īelow are some commands that might be needed during the process. In the table cities_airports the line PRIMARY KEY (city_id, airport_icao), is a restriction that makes sure the entities in this table are unique. Next, we create the weathers table with the following variables: CREATE TABLE IF NOT EXISTS weathers( weather_id INT AUTO_INCREMENT, city_id INT, rain_prob DECIMAL (3,2), forecast_time DATETIME, PRIMARY KEY (weather_id), FOREIGN KEY (city_id) REFERENCES cities (city_id) ) įollowing the same logic as which table comes next by looking at its keys, create the table airports: CREATE TABLE IF NOT EXISTS airports( airport_icao VARCHAR(4), airport_name VARCHAR (200), PRIMARY KEY (airport_icao) ) Īfter that comes either cities_airports or flights: CREATE TABLE IF NOT EXISTS cities_airports ( city_id INT, airport_icao VARCHAR(25), PRIMARY KEY (city_id, airport_icao), FOREIGN KEY (city_id) REFERENCES cities(city_id), FOREIGN KEY (airport_icao) REFERENCES airports(airport_icao) ) create table if not exists flights ( flight_id int auto_increment, flight_num varchar(200), departure_icao varchar(4), arrival_icao varchar(4), arrival_time datetime, primary key(flight_id), foreign key(arrival_icao) references airports(airport_icao) ) To avoid such an error in case by mistake you run the query, include the words IF NOT EXISTS as follows: CREATE TABLE IF NOT EXISTS cities( city_id INT, city_name VARCHAR(200), country VARCHAR (200), PRIMARY KEY (city_id) ) If you try to execute the above command again, there will be an error: Query Interrupted with the Message: Error Code: 1050. The query to create a table is executed only once. CREATE TABLE cities( city_id INT, city_name VARCHAR(200), country VARCHAR (200), PRIMARY KEY (city_id) )

The first table to create is cities because it does not have a foreign key. To let the SQL server know that we want to work on this database, we need to write: USE database_name The database is created by using the following query: CREATE DATABASE database_name

Considering our data that we collected in the previous post, we should have the following tables: Once you have the EER diagram in mind or drawn on paper, you are ready to go. Regarding the names of the tables and their columns, by convention the name of the tables is plural and the names of the columns are singular. So, a table that contains a foreign key can not be created if the table that has this foreign key as a primary key is not created. The primary key of one table is a foreign key in the table that is connected to. Creation of tables must be done sequentially because the tables are connected to each other through keys. The second step is to decide which table to create first. Also, there shouldn’t be redundant columns in the database. Each table should have an id that uniquely identifies the identity of a row, which is known as the primary key. Before creating the database right away, it is important to first think of a schema such that the data gets stored in an efficient way. In this post, we are going to set up a local database on MySQL workbench that will be used to store the data we collected. airports flights information of each city 3. In the previous post: Collect Data Using Web-scraping and APIs, we used web scraping and APIs to collect the following data:ġ. This is the second section of the project Automating an end-to-end Data Pipeline on AWS Cloud.

Create database mysql workbench