How to create the table in MySQL?

vishrut-singhal · 18 May 2021 06:45

MySQL CREATE TABLE

A table is used to organize data in the form of rows and columns and used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A table creation command requires three things :

Name of the table
Names of fields
Definitions for each field

MySQL allows us to create a table into the database mainly in two ways :

MySQL Command Line Client
MySQL Workbench

MySQL Command Line Client

MySQL allows us to create a table into the database by using the CREATE TABLE command. Following is a generic syntax for creating a MySQL table in the database.

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

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

Parameter	Description
database_name	It is the name of a new table. It should be unique in the MySQL database that we have selected. The IF NOT EXIST clause avoids an error when we create a table into the selected database that already exists.
column_definition	It specifies the name of the column along with data types for each column. The columns in table definition are separated by the comma operator. The syntax of column definition is as follows:
**column_name1 data_type(size) [NULL	NOT NULL]**
table_constraints	It specifies the table constraints such as PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK, etc.

Example

Let us understand how to create a table into the database with the help of an example. Open the MySQL console and write down the password, if we have set during installation. Now open the database in which you want to create a table. Here, we are going to create a table name “employee_table” in the database “employeedb” using the following statement:

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)
);

NOTE:

Here, NOT NULL is a field attribute, and it is used because we don’t want this field to be NULL. If we try to create a record with a NULL value, then MySQL will raise an error.
The field attribute AUTO_INCREMENT specifies MySQL to go ahead and add the next available number to the id field. PRIMARY KEY is used to define a column’s uniqueness. We can use multiple columns separated by a comma to define a primary key.

Visual representation of creating a MySQL table:

MySQL CREATE TABLE

We need to use the following command to see the newly created table:

mysql> SHOW TABLES;

It will look like the below output:

MySQL CREATE TABLE

See the table structure:

We can use the following command to see the information or structure of the newly created table:

mysql> DESCRIBE employee_table;

It will look like this:

MySQL CREATE TABLE

Create Table Using MySQL Workbench

It is a visual GUI tool used to create databases, tables, indexes, views, and stored procedures quickly and efficiently. To create a new database using this tool, we first need to launch the [MySQL Workbench] and log in using the username and password that you want. It will show the following screen:

Now do the following steps for table creation:

Go to the Navigation tab and click on the Schema menu . Here, we can see all the previously created databases. Now we are ready to select the database in which a table is created.
Select the database, double click on it, and we will get the sub-menu under the database. These sub-menus are Tables, Views, Functions, and Stored Procedures, as shown in the below screen.

MySQL CREATE TABLE

Select Tables sub-menu, right-click on it, and select Create Table option. We can also click on create a new table icon (shown in red rectangle) to create a table.
On the new table screen, we need to fill all the details to create a table. Here, we will enter the table name ( for example , employee_table) and use default collation and engine.
Click inside the middle window and fill the column details. Here, the column name contains many attributes such as Primary Key(PK), Not Null (NN), Unique Index (UI), Binary(B), Unsigned Data type(UN), Auto Incremental (AI), etc. The following screen explains it more clearly. After filling all the details, click on the Apply button.

As soon as you click on the Apply button, it will open the SQL statement window. Again, click on the Apply button to execute the statement and Finish button to save the changes.

MySQL CREATE TABLE

Now, go to the Schema menu and select the database which contains the newly created table, as shown in the screen below.

MySQL CREATE TABLE