MySQL WorkBench: Create, alter & Drop

Table of Contents Introduction Prerequisites Task 1: Connect to the MySQL Workspace Task 2: Create a Database and a Table Step 1: Check Available Databases Step 2: Create a New Database Step 3: Create a Table Step 4: Verify Table Creation Task 3: Modify a Table Task 4: Delete a Table and Database Step 1: Drop a Table Step 2: Verify Table Deletion Step 3: Drop the Database Conclusion Introduction Managing relational databases effectively requires an understanding of fundamental operations such as creating, viewing, altering, and deleting databases and tables. This guide walks you through these essential operations using MySQL Workspace. By the end of this guide, you will be able to: Create databases and tables using the CREATE statement. View available databases and tables using the SHOW statement. Alter the structure of tables using the ALTER statement. Delete databases and tables using the DROP statement. Prerequisites Before you begin, ensure that: You have MySQL installed on your system. MySQL Workbench is installed for database management. You have the necessary user privileges to create and modify databases. Task 1: Connect to the MySQL Workspace Open MySQL Workbench. Click on Database in the menu bar and select Manage Connections. Click New and enter the following details: Connection Name: World_Workspace Hostname: localhost (or your server address) Username: root Click Test Connection to verify the setup. Once the connection is successful, click OK and then Connect. Task 2: Create a Database and a Table Step 1: Check Available Databases Run the following query to display all available databases: SHOW DATABASES; Step 2: Create a New Database To create a database named company, execute: CREATE DATABASE company; Verify the creation by running: SHOW DATABASES; Step 3: Create a Table To store employee data, create a table named employees with the following structure: CREATE TABLE company.employees ( `EmployeeID` INT AUTO_INCREMENT PRIMARY KEY, `FirstName` VARCHAR(50) NOT NULL, `LastName` VARCHAR(50) NOT NULL, `Department` VARCHAR(50) NOT NULL, `Salary` DECIMAL(10,2) NOT NULL, `HireDate` DATE NOT NULL ); Step 4: Verify Table Creation To verify that the employees table was created, specify the database and check its tables: USE company; SHOW TABLES; To list all columns in the employees table, run: SHOW COLUMNS FROM company.employees; Task 3: Modify a Table If you need to add a new column for email addresses, use the ALTER TABLE statement: ALTER TABLE company.employees ADD COLUMN Email VARCHAR(100) NOT NULL; Verify the change: SHOW COLUMNS FROM company.employees; Task 4: Delete a Table and Database Step 1: Drop a Table To delete the employees table: DROP TABLE company.employees; Step 2: Verify Table Deletion SHOW TABLES FROM company; Step 3: Drop the Database To delete the company database: DROP DATABASE company; Verify database deletion: SHOW DATABASES; Conclusion Congratulations! You have successfully performed key database table operations: Created databases and tables. Viewed databases and tables using SHOW commands. Altered a table column using ALTER TABLE. Deleted tables and databases using DROP.

Mar 8, 2025 - 20:38

Introduction
Prerequisites
Task 1: Connect to the MySQL Workspace
Task 2: Create a Database and a Table
- Step 1: Check Available Databases
- Step 2: Create a New Database
- Step 3: Create a Table
- Step 4: Verify Table Creation
Task 3: Modify a Table
Task 4: Delete a Table and Database
- Step 1: Drop a Table
- Step 2: Verify Table Deletion
- Step 3: Drop the Database
Conclusion

Introduction

Managing relational databases effectively requires an understanding of fundamental operations such as creating, viewing, altering, and deleting databases and tables. This guide walks you through these essential operations using MySQL Workspace.

By the end of this guide, you will be able to:

Create databases and tables using the CREATE statement.
View available databases and tables using the SHOW statement.
Alter the structure of tables using the ALTER statement.
Delete databases and tables using the DROP statement.

Prerequisites

Before you begin, ensure that:

You have MySQL installed on your system.
MySQL Workbench is installed for database management.
You have the necessary user privileges to create and modify databases.

Task 1: Connect to the MySQL Workspace

Open MySQL Workbench.
Click on Database in the menu bar and select Manage Connections.
Click New and enter the following details:
- Connection Name: World_Workspace
- Hostname: localhost (or your server address)
- Username: root

Click Test Connection to verify the setup.
Once the connection is successful, click OK and then Connect.

Task 2: Create a Database and a Table

Step 1: Check Available Databases

Run the following query to display all available databases:

SHOW DATABASES;

Step 2: Create a New Database

To create a database named company, execute:

CREATE DATABASE company;

Verify the creation by running:

SHOW DATABASES;

Step 3: Create a Table

To store employee data, create a table named employees with the following structure:

CREATE TABLE company.employees (
  `EmployeeID` INT AUTO_INCREMENT PRIMARY KEY,
  `FirstName` VARCHAR(50) NOT NULL,
  `LastName` VARCHAR(50) NOT NULL,
  `Department` VARCHAR(50) NOT NULL,
  `Salary` DECIMAL(10,2) NOT NULL,
  `HireDate` DATE NOT NULL
);

Step 4: Verify Table Creation

To verify that the employees table was created, specify the database and check its tables:

USE company;
SHOW TABLES;

To list all columns in the employees table, run:

SHOW COLUMNS FROM company.employees;

Task 3: Modify a Table

If you need to add a new column for email addresses, use the ALTER TABLE statement:

ALTER TABLE company.employees ADD COLUMN Email VARCHAR(100) NOT NULL;

Verify the change:

SHOW COLUMNS FROM company.employees;

Task 4: Delete a Table and Database

Step 1: Drop a Table

To delete the employees table:

DROP TABLE company.employees;

Step 2: Verify Table Deletion

SHOW TABLES FROM company;

Step 3: Drop the Database

To delete the company database:

DROP DATABASE company;

Verify database deletion:

SHOW DATABASES;

Conclusion

Congratulations! You have successfully performed key database table operations:

Created databases and tables.
Viewed databases and tables using SHOW commands.
Altered a table column using ALTER TABLE.
Deleted tables and databases using DROP.