Create, Alter, Drop Table using MySQL Workbench
In this article, I am going to discuss How to Create Alter and Drop MySQL Database table using MySQL Workbench. Please read our previous article where we discussed how to create, alter and drop databases using MySQL Workbench.
How to Create Alter and Drop Database table using MySQL Workbench?
Firstly, I’ve opened MySQL Workbench and logged in to my MySQL database server using the ‘root’ username and password. Then I created one database called mytestdb by executing the following SQL Script.
CREATE DATABASE mytestdb;
Under the SCHEMAS menu, select the newly created database. In my case ‘mytestdb’, double click the database name, it will display the submenu under the database. The submenu contains tables, views, stored procedures, and functions as shown in the below image. At this moment we’ve just created the database so there are no tables or other information stored in it.
Select the ‘Tables’ submenu under the mytestdb database, and then right-click on it and select the ‘create table’ option as shown in the below image.
Once you click on the Create Table option, it will open the below create new table window.
On the above ‘new table’ screen, enter the table name. I am giving the name as students. Use default charset/collation and engine. Click inside the middle window and type column name. Let’s create the students table here. In column name, type StudentId, data type INT, select this ‘PK’ checkbox to make this column the ‘Primary Key’ column.
- PK: This will make the column Primary Key. So, the column that you want to make as the Primary key, you need to select this PK option for that column.
- NN: As the ‘Primary Key’ column does not store NULL values, so MySQL workbench will automatically check ‘NN’ means the Not Null option. So, whenever you want to make any column as NOT NULL, you need to check this NN Check box.
- UQ: Select the ‘UQ’ checkbox to store only unique values. So, whenever you want to store unique values for a column, then you need to select this check box.
- B: The ‘B’ checkbox is used to store only binary or bit values. That is the values containing only 1 or 0s. So, if your column is going to store 0 or 1 i.e. true or false, then you need to select this checkbox.
- UN: The UN checkbox means ‘Unsigned’ data type. This option is only used for a number that stores either positive or negative values. We will be storing all the positive values in our ‘StudentId’ column, so we will select the ‘unsinged’ option.
- ZF: ZF means ‘Zero Fill’ i.e. column with 0 values.
- AI: AI option is for Auto Increment. We are not going to manually enter each ‘StudentId’, so we will let the system auto-generate and increment the StudentIds.
- G: This ‘G’ option is for ‘Generated Columns’ for advanced use like expression caching, provide join index for nonrelational data, etc.
- Default/Expression: In the ‘Default/Expression’ option, we can store a default value for the column.
Enter the second column name FirstName, DataType VARCHAR(20), ‘NN’ for not null. Enter another column name i.e. LastName, DataType VARCHAR(20), ‘NN’ for not null, Enter another column name i.e. Class, DataType VARCHAR(20), ‘NN’ for not null, and finally Age column which will be an unsigned INTEGER. Select INT and UN for unsigned. Then click on the Apply button as shown in the below image to save the changes.
Once you click on the Apply button, it will open the below Apply SQL Script to Database window. Here, simply click on the Apply button as shown in the below image to execute the SQL Statements.
And from the next window, click on the Finish to save the changes as shown in the below image.
Now under the SCHEMAS menu, under the selected database i.e. mytestdb, the ‘Tables’ link will contain the newly created students table as shown in the below image.
How to Alter MySQL Database Table using MySQL Workbench?
In order to alter the table, right-click on the table name and then select the Alter Table option from the context menu as shown in the below image.
Once you click on the Alter Table option, it will open the below window and here you can change the table detail. You can modify the column name, data type, and other table settings from here.
Creating Table using SQL Script in MySQL Workbench:
As of now, we have seen the graphical way of creating the database table. It is also possible to create the database table using the raw SQL in MySQL Workbench. First, open a new query tab and then write and execute the below SQL Script which will create studentaddress table in mytestdb database.
CREATE TABLE `mytestdb`.`studentaddress` ( StudentIdAddress INT PRIMARY KEY, StudentId INT NOT NULL, AddressLine1 VARCHAR(200), AddressLine2 VARCHAR(200), MobileNo INT );
Once you execute the above SQL statement, then you can see the newly created studentaddress table in the mytestdb database as shown in the below image.
Note: If the newly created table is not visible to you, simply refresh the Table link and you will able to see the newly created table.
How to Drop a table in MySQL Server using MySQL Workbench?
To delete a table in MySQL using MySQL Workbench, simply select the table name, right-click on it, and select the ‘drop table’ option from the context menu as shown in the below image.
Once you click on the Drop Table option, it will open the below popup, and here simply select the ‘drop now’ option to instantly delete the table from the database as shown in the below image.
It is also possible to drop a table using the below SQL Statement.
DROP TABLE mytestdb.students;
Once you execute the above SQL Statement, it will delete the student’s table from the database.
In the next article, I am going to discuss How Insert, Read, Update, Delete Data Rows using MySQL Workbench. Here, in this article, I try to explain How to Create Alter and Drop Database table using MySQL Workbench and I hope you enjoy this Create Alter and Drop Database table using MySQL Workbench article.