Hello there, friends! In this post, we will learn about MySQL Tables in Hindi (What is a Table in MySQL?) and how to create and modify them. This will be seen as well. I've written it in simple language. You will easily understand it if you read it completely. So let's get started:

MySQL Table After you've created the database, you can start creating tables in it. In MySQL, a table is made up of rows and columns. Tuples refer to rows, and fields refer to columns.

MySQL table properties

A MySQL table has four fundamental properties. These characteristics are also known as options. These properties are specified when the table is created. The ALTER command can be used to change the value of some properties after they have been defined. These characteristics are listed below.

1:- ENGINE - This property specifies the storage engine that the table will use. MySQL includes four storage engines. You can put them to use as you see fit. MySQL provides the following storage engines.

MyISAM
InnoDB
memory
MERGE

2:- COMMENT - With this property, you can define the table's description. It is used for documentation purposes. This property's value can be up to 60 characters long. The text is enclosed in single quotes in this case.

3:- CHARACTER SET - This property defines the character set.

4:- COLLATE - This property defines a table's collation.

Making a MySQL table
The most important unit in a database is the table. The database structure is defined solely by tables. You can get information about tables from any database using the SHOW TABLES statement before creating them. Below is an example of this.

When this statement is executed, it displays a list of all the tables that are already present in the database. This knowledge can be used in a variety of ways.
The following is the basic syntax for creating a table in MySQL.

CREATE TABLE <table-name>
(
<column-name> <column-type> <options>,
<column-name> <column-type> <options),
<constraints>
) <table-options>

This is referred to as the create table statement. Let us now attempt to comprehend this syntax.

In MySQL, the CREATE TABLE statement is used to create tables. Following this statement, the table's unique name is given.

<column-name> is the name of the column in the table that you want to create. This name should be unique across the board.

<column-type> specifies the type of data that will be stored in a specific column. For example, character, integer, etc.

Column options
<options> are defined for individual columns.These options define any column's additional behavior. For example, if you want the column value to be increased automatically, you can use the AUTO INCREMENT option. Some column options are provided below.

NULL - This option specifies that a NULL value may be stored in a specific column.

NOT NULL - This option specifies that NULL values are not permitted in a specific column.

DEFAULT - This option allows you to set the column's default value. If a column's value is not specified at the time of insertion, the default value is saved.

AUTO INCREMENT - As previously stated, this option defines the column values that will be automatically increased. This option is only available for integer values.

Constraints
There are <constraints> rules that define the restrictions that will be applied to table rows. Columns are subjected to constraints. You can prevent any type of change in the table by using Constraints. Constraints ensure database consistency.

For example, if a new user is given full access to a critical table, he may corrupt the data. In such a case, the constraints prevent him from taking an action that would jeopardize the database's consistency. The most common constraints in MySQL are listed below.

Primary Key – The primary key constraint states that a column's value cannot be NULL. Furthermore, the values in this column will be unique throughout the table. Every row in the table can be uniquely identified using the primary key. The primary key is typically a column of integer values such as ID, etc.

Unique Key – The unique key constraint is similar to the primary key constraint, but it allows for the storage of NULL values. The column on which this constraint is applied, as well as the values in that column, must be unique throughout the table. A unique constraint requires the user to enter distinct values in a column. This constraint can uniquely identify rows if the value of any column is not NULL.

Foreign Key – A foreign key constraint refers to a table's primary key. A primary key in another table is referred to as a foreign key. This constraint explains the connection between two tables. This constraint indicates that this table's columns are related to the table with the foreign key.
You should now understand the fundamental syntax for creating a table in MySQL. Let us now try to explain it with an example.

mysql > create table Employee
>(
> Id INT NOT NULL AUTO_INCREMENT,
> Name CHAR (20) NOT NULL,
> Salary INT,
> primary key (Id)
>);

In the preceding example, a table called Employee was created. This table's first column is Id, which is an integer. This column has the NOT NULL and AUTO INCREMENT options set. The second name is of the CHAR format. It has a maximum size of 20. This column has the NOT NULL option set. The third column, salary, is of the INT type. Following that, the primary key constraint defines the Id column as the primary key.

Altering Table in MySQL
When a table is created, it is not uncommon for the structure of that table to be incorrect. In this case, you can change the structure of the table and add or remove columns as needed. You can also change the column definitions (type, size etc.).

Let us now investigate how the structure of existing tables is altered.

Adding columns
The ALTER TABLE statement is used to add columns to any existing table. The basic syntax is shown below.

mysql > ALTER TABLE <table_name> ADD COLUMN <column_name> <type(size)> <options)
Let us now use an example to try to understand it. Assume you want to add a column called Address to the employee table you created earlier. You can do it this way.

mysql > alter table Employee add column Address varchar(20) NOT NULL;
When you add columns in this manner, they are added at the end. However, you can add columns anywhere you want. You can accomplish this by utilizing the FIRST and AFTER clauses. Below is an example of this.

mysql > alter table Employee add column Address varchar(20) NOT NULL after Name;

Removing Columns
You can use the DROP clause with the ALTER TABLE statement to remove any column. For example, if you want to remove the salary column from the table above, you can do so as follows.

mysql > alter table Employee drop column salary;
Changing columns definition
If you want to change the type of a column or its options, you can use the CHANGE clause with the ALTER TABLE statement. You can use this clause to write back the definition of any column.

For example, if you want to change the type of the Id column from int to bigint in the table above, you can do so as follows.

mysql > alter table employee change Id bigint not null auto_increment;