Table of Contents
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:
- The table’s name
- Names of fields
- 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:
- ALTER TABLE table_name
- DROP COLUMN column_name;
Example:
- ALTER TABLE Persons
- 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;
0 Comments