Introduction

In the structural format, a table is used to organize data in the form of rows and columns and is used for both storing and presenting records. Worksheets in a spreadsheet application are like this. Three components are required for a table formation:

1. The table’s name
2. Names of fields
3. Each field’s definitions

CREATE Table

There are two ways to create tables in MySQL:

1. MySQL Command Line Client

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name(
column_definition1,
column_definition2,
........,
table_constraints
);

Example:

mysql> CREATE TABLE employee_table(
id int NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
age int NOT NULL,
PRIMARY KEY (id)
);

2. MySQL Workbench

It’s a graphical user interface tool for rapidly and easily creating databases, tables, indexes, views, and stored procedures. To use MySQL Workbench to create a new database, open it and log in using the chosen username and password. The following screen will appear:

Select the database, double-click it, and the database’s sub-menu will appear. Tables, Views, Functions, and Stored Procedures are the sub-menus seen in the screenshot below.

Right-click on the Tables sub-menu and select the Create Table option. To construct a table, we can alternatively click the create a new table icon (shown in red rectangle).

To construct a table, we must fill in all of the details on the new table screen. We’ll use the default collation and engine and enter the table name (for example, employee table).

Fill in the column details by clicking inside the main pane. The column name includes terms like Primary Key (PK), Not Null (NN), Unique Index (UI), Binary (B), Unsigned Data Type (UN), Auto Incremental (AI), and so on. The following screen goes through it in further depth. After you’ve completed all the fields, click the Apply button.

Click the Apply button once again to execute the statement, and then the Finish button to save the changes.

ALTER Table

The ALTER statement is always used with the “ADD,” “DROP,” and “MODIFY” commands, depending on the context.

1) Add a column in the table

Syntax:

ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ];

Example:

ALTER TABLE Persons 
ADD Email varchar(255);

2) Drop the column in the table

Syntax:

1. ALTER TABLE table_name
2. DROP COLUMN column_name;

Example:

1. ALTER TABLE Persons
2. DROP COLUMN age;

TRUNCATE Table

The TRUNCATE command removes data from a table.

Syntax:

TRUNCATE [TABLE] table_name;

Example:

mysql> TRUNCATE TABLE customer;

DROP Table

To delete an existing table in MYSQL, use the Drop Table statement. This statement deletes a table’s entire contents as well as its whole structure or definition from the database. As a result, you must exercise extreme caution while deleting the table, as we will be unable to restore any data that has been deleted.

Syntax:

mysql> DROP TABLE  table_name;

OR,

mysql> DROP TABLE  schema_name.table_name;

Example:

mysql> DROP TABLE  Persons;