MySQL CREATE TABLE

A MySQL table is a structured data container that holds rows and columns. It is very similar to a typical spreadsheet. While creating a table in MySQL we need to specify the columns’ names and their data types, constraints, and optional attributes.

Let’s look at these different methods to create table in MySQL.

Different Ways to CREATE TABLE in MySQL

MySQL provides multiple methods for creating tables. The two primary methods include using the Command Line Interface(CLI) and the Graphical User Interface(GUI) provided by MySQL Workbench.

CREATE TABLE Statement in MySQL CLI

To create a table in MySQL CLI, use the CREATE TABLE statement.

Create Table Syntax

The basic syntax To Create Table in MySQL is given below:

CREATE TABLE table_name
(
    column1_name datatype1 [optional_constraints],
    column2_name datatype2 [optional_constraints],
    ...
    PRIMARY KEY (one_or_more_columns)
);

Parameters

• column_name: Specify the name of the columns of tables.
• datatype: Specify the datatype of the data to be entered in the column.

MySQL CREATE TABLE EXAMPLE

In this example, we will create table in MySQL with Primary Key.

So let’s create a table called employee_information which hold the some columns in it. It holds the employee_id,first name,last name, and email, position or role of employee. Also, we apply the PRIMARY KEY Constraint on the employee_id Column.

CREATE TABLE employee_information
(
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10, 2)

Let’s verify the table is created or not with the help of DESCRIBE command.

Query:

DESCRIBE employee_information;

Output:

Information Table

Explanation:This output displays the structure of the employee_information table, providing information about each column as specified in the CREATE TABLE command.

By following these steps, you have successfully created a table in the MySQL command-line interface to store information about employees. The created table is now ready to be populated with data and used in your MySQL database.

Create Table Using Another Table

We can also create a new table from an existing table. Using this method you can create a table with same column definitions as the existing table. It will also take the same data as the previous table.

Syntax

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

Example

CREATE TABLE new_employee_table AS
SELECT employee_id, first_name, last_name, salary
FROM employee_information; 

Create Table in MySQL Workbench

For those who prefer a more visual approach, MySQL Workbench is a powerful tool. Navigate through the intuitive interface, utilizing drag and drop functionalities to design your table. Explore the graphical representation of your table’s structure and relationships.

To create the Table follow below given steps.

Step 1: Open & Create

• You first all open MySql workbench then after connecting to the server,
• Create a Database/ Schema (Ignore if already created).

Step 2: Select The Database

• Click on the Schemas Button on the left hand side of the screen.
• Then a list of Database will appear select one of them.

Select database

Step 3: Creating Table

1. Create the new table using new table button given in the above toolbar.
2. Then fill the required details like name of table, column names, constraint , datatypes etc.
3. For example we have take the example of information table only.

Create Table

Hence the Information table is successfully created using the MYSQL Workbench.

Lets Verify it using Describe

Verification in COMMAND Line

Describe Table

Conclusion

Table in MYSQL view the record in structured format. It is a very fundamental concept in MySQL. To create a table in MySQL there are two methods- CLI and Workbench.

In this article, we have covered both these methods to create table in MySQL. We saw the use of CREATE TABLE statement in MySQL CLI to create a table and also saw the steps to create table in MySQL workbench. With these techniques you can easily create MySQL tables.