MySQL WorkBench: Create, alter & Drop

Table of Contents

1. Introduction
2. Prerequisites
3. Task 1: Connect to the MySQL Workspace
4. 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
5. Task 3: Modify a Table
6. Task 4: Delete a Table and Database
   • Step 1: Drop a Table
   • Step 2: Verify Table Deletion
   • Step 3: Drop the Database
7. 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

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

1. Click Test Connection to verify the setup.
2. 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.