Create, Delete & Alter a Table Using MySQL Workbench

Welcome to our tutorial on creating a table in a MySQL database using MySQL Workbench. In this step-by-step guide, we'll explore how to define a table structure, insert data, and execute queries to work with your database. To illustrate the process, we'll use a dataset of employees from a company called ZygnovaTech as shown below.

To create a new table in our database, which we'll name "zygnovadb" and the table itself will be called "employee," follow the steps provided below.

1. Launch MySQL Workbench on your computer.

2. Click on the "+" icon in the "MySQL Connections" tab. This will open the "Set up a New Connection" dialog box.

3. In the "Connection Name" field, enter a name for your connection (e.g., "MyDatabase").

4. Enter the necessary connection details in the following fields:

5. Hostname: Enter the hostname or IP address of the MySQL server.

6. Port: Specify the port number on which the MySQL server is running (default is usually 3306).

7. Username: Provide the username for accessing the MySQL server.

8. Password: Enter the password associated with the provided username.

9. Click the "Test Connection" button to check if the connection is successful. If everything is set up correctly, you should see a success message.

10. Click the "OK" button to save the connection settings.

11. Once the connection is established, you can proceed to create a database by executing SQL statements or using the visual tools provided by MySQL Workbench.

12. After creating the connection and connecting to the MySQL server, you can start creating a database by executing the necessary SQL statements. In your case, to create a database and insert the data you provided, you can use the following SQL statements:

CREATE DATABASE zygnovadb;

USE zygnovadb;

CREATE TABLE zygnova_employee (
  Firstname VARCHAR(255),
  Lastname VARCHAR(255),
  Salary INT,
  DateHired DATE,
  Status VARCHAR(255)
);

INSERT INTO zygnova_employee (Firstname, Lastname, Salary, DateHired, Status)
VALUES ('John', 'Smith', 5000, '2020-01-15', 'Active'),
       ('Emily', 'Johnson', 6000, '2019-05-20', 'Active'),
       ('Michael', 'Davis', 4500, '2021-02-10', 'Active'),
       ('Sophia', 'Martinez', 5500, '2022-03-05', 'Active'),
       ('Daniel', 'Thompson', 4000, '2023-01-02', 'Inactive');

13. Execute these SQL statements in the SQL editor of MySQL Workbench to create the database and insert the provided data into the table.

Assuming we have already created the table named “employee” in the database “zygnovadb”, and now we want to delete this table. Here is how you can do it:

1. Launch MySQL Workbench and establish a connection to your MySQL server.

2. Once connected, navigate to the SQL Editor by clicking on the "SQL" icon on the top toolbar or selecting "SQL Editor" from the "Database" menu.

3. In the SQL Editor, make sure that the correct database is selected by using the USE statement. For example, if you want to delete a table in the "zygnovadb" database, you can use the following command:

USE zygnovadb;

4. Once you have selected the correct database, you can delete the table using the DROP TABLE statement. The syntax for dropping a table is as follows:

DROP TABLE employee;

5. After writing the DROP TABLE statement, click the "Execute" button in the SQL Editor toolbar or press Ctrl+Enter to execute the SQL statement.

6. The table will be deleted from the database, and a message indicating the success of the operation will be displayed in the output area.

Using the SQL Editor in MySQL Workbench allows you to execute SQL statements directly, providing more control and flexibility when performing database operations. Just be cautious when using the DROP TABLE statement, as it permanently deletes the table and all its data. Make sure to double-check that you are deleting the correct table to avoid accidental data loss.

Add New Column

Assuming we want to add a new column called “phone_number” to the existing table “employee” in the database “zygnovadb”, you can follow these steps:

1. Open MySQL Workbench and establish a connection to the MySQL server.

2. Select the 'zygnovadb' database from the schema list.

3. In the SQL Editor, enter the following SQL statement to add the new column:

ALTER TABLE employee
ADD COLUMN phone_number VARCHAR(255);

4. This statement adds a new column named 'phone_number' to the 'employee' table with a data type of VARCHAR and a maximum length of 255 characters. You can modify the data type and length based on your specific requirements.

5. Execute the SQL statement by clicking the 'Execute' button or pressing Ctrl+Enter.

6. The table 'employee' will be altered, and the new column 'phone_number' will be added.

7. You can verify the changes by viewing the structure of the 'employee' table in the MySQL Workbench or by executing a SELECT query to retrieve the table's information.

By following these steps, you can successfully add a new column to an existing table in the 'zygnovadb' database using MySQL Workbench.

---

Changing/Modifying a Column

To change a column, let’s say in this example, we want to change the column from "phone_number" to "email" in an existing table named "employee" within the "zygnovadb" database, you can use the following SQL statement:

ALTER TABLE employee
CHANGE COLUMN phone_number email <column_definition>;

Replace <column_definition> with the original definition of the column, including the data type and any additional constraints.

Here's an example of how the SQL statement would look:

ALTER TABLE employee
CHANGE COLUMN phone_number email VARCHAR(15) NOT NULL;

This command will modify the column name from "phone_number" to "contact" and update its definition accordingly.

Please note that altering a column may involve other considerations, such as data integrity and the impact on existing data. Make sure to back up your database before making any structural changes and review the documentation for your specific database management system for further guidance.

---

Renaming a Column

1. Open MySQL Workbench and connect to your MySQL server.

2. In the SQL editor, make sure you have selected the appropriate database by executing the following command:

USE zygnovadb;

3. Replace zygnovadb with the name of your database.

4. To rename a column in an existing table, you can use the ALTER TABLE statement with the RENAME COLUMN clause. The syntax is as follows:

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

5. Replace table_name with the name of your table, old_column_name with the current name of the column you want to rename, and new_column_name with the desired new name for the column.

6. For example, if you want to rename the column "phone_number" to "contact" in the "employee" table, you would execute the following command:

ALTER TABLE employee
RENAME COLUMN phone_number TO contact;

7. This command will rename the column from "phone_number" to "contact" in the "employee" table.

8. Execute the ALTER TABLE statement by clicking the "Execute" button or pressing Ctrl+Enter.

9. MySQL Workbench will execute the statement and rename the specified column in the table. Make sure to verify the changes by checking the table structure or querying the table to see the updated column name.

Using the RENAME COLUMN syntax provides a direct and concise way to rename a column in MySQL.

---

RENAME vs CHANGE

RENAME: The RENAME statement is used specifically for renaming database objects such as tables or columns. With RENAME, you can rename a table or a specific column within a table. For example, you can rename a table from "table1" to "table2" or rename a column from "column1" to "column2".

Syntax for renaming a table:

RENAME TABLE old_table_name TO new_table_name;

Syntax for renaming a column:

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

CHANGE: The CHANGE statement is used within an ALTER TABLE statement and is primarily used to modify the definition of a column in terms of its name, data type, and other attributes. It allows you to change the name, data type, or other properties of an existing column. Syntax for changing a column using CHANGE:

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_data_type;

The key difference is that RENAME is specifically for renaming objects (tables or columns), while CHANGE is used to modify the definition of a column within an ALTER TABLE statement. Depending on your requirement, you would choose the appropriate statement to achieve the desired result.

We hope this tutorial has been helpful in understanding how to create tables in a MySQL database using MySQL Workbench. By considering the appropriate data types for your columns, you can ensure accurate data storage and retrieval in your database. Happy database management!