Table of Contents
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:
- The table’s name
- Names of fields
- Each field’s definitions
There are two ways to create tables in MySQL:
1. MySQL Command Line Client
CREATE TABLE [IF NOT EXISTS] table_name( column_definition1, column_definition2, ........, table_constraints );
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.
The ALTER statement is always used with the “ADD,” “DROP,” and “MODIFY” commands, depending on the context.
1) Add a column in the table
ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];
ALTER TABLE Persons ADD Email varchar(255);
2) Drop the column in the table
- ALTER TABLE table_name
- DROP COLUMN column_name;
- ALTER TABLE Persons
- DROP COLUMN age;
The TRUNCATE command removes data from a table.
TRUNCATE [TABLE] table_name;
mysql> TRUNCATE TABLE customer;
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.
mysql> DROP TABLE table_name;
mysql> DROP TABLE schema_name.table_name;
mysql> DROP TABLE Persons;