Create MySQL tables in a database
1. Create a table in MySQL using a CREATE TABLE statement, where the table will be used to store data in column and row format.
The CREATE TABLE statement allows you to create a new table in a database. Use this syntax: CREATE TABLE [IF NOT EXISTS] table_name( column_1_definition, column_2_definition, ..., table_constraints ) ENGINE=storage_engine; For example: CREATE TABLE IF NOT EXISTS tasks ( task_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, start_date DATE, due_date DATE, status TINYINT NOT NULL, priority TINYINT NOT NULL, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=INNODB;
2. Use CREATE TABLE with a foreign key and primary key to connect two tables with a common column. Use the primary key to represent a unique row in the table, and the foreign key to connect to a related column in another table.
For example, you might want to create a checklist with associated tasks you can tick off. Each checklist is essentially a collection of tasks. On the database side, first create a table called tasks with a primary key of task_id. To store checklists of tasks, create a new table named checklists, with primary key of todo_id and foreign key of task_id to connect the tasks table. CREATE TABLE IF NOT EXISTS checklists ( todo_id INT AUTO_INCREMENT, task_id INT, todo VARCHAR(255) NOT NULL, is_completed BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (todo_id , task_id), FOREIGN KEY (task_id) REFERENCES tasks (task_id) ON UPDATE RESTRICT ON DELETE CASCADE );
3. Create a table using MySQL Workbench visual editor to avoid writing a query from scratch.
Right-click on [database name] > Tables and select Create Table…. Enter a name for the table, and define the columns needed. Note: AI means auto increment. Tick AI if you would like the value to be automatically incremented when a new row is created in the table. Click Apply, review the SQL, and click Apply again.
4. Right-click on a table and select Alter Table... to add more columns to it. You might need to do this when the application usage grows and you want to store more data and attributes in the same table.
Alter allows you to add or remove columns from the table. Add the new columns and click Apply. Review the SQL syntax and click on Apply to add the new columns to the table.
5. Import data from a CSV file into a table using MySQL Workbench's import feature, which is intuitive and simple.
Check before importing that the CSV file columns match the columns in the table you are importing to, and the data types also match. Select the records from the table using a select statement: select * from [table_name]. Click on the import icon, and choose a CSV file to import.