Open In App

MySQL CREATE TABLE

Last Updated : 23 Apr, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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:

table information

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

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

Create Table

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

Lets Verify it using Describe

Verification in COMMAND Line

describe table

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.


Previous Article
Next Article

Similar Reads

Update One Table with Another Table's Values in MySQL
Sometimes we need to update a table data with the values from another table in MySQL. Doing this helps in efficiently updating tables while also maintaining the integrity of the database. This is mostly used for automated updates on tables. We can update values in one table with values in another table, using an ID match. ID match ensures that the
3 min read
Table 20 to 25 - Multiplication Table Chart and 20-25 Times Table
Multiplication Tables of 20, 21, 22, 23, 24, and 25 are provided here. Tables from 20 to 25 is a list of tables that contain multiplication of numbers 20 to 25 with integers 1 to 10. For eg. a multiplication table of 21 will show results when 21 is multiplied by numbers 1 to 10. Tables in Maths are also called Multiplication Tables. Table of Conten
6 min read
MySQL RENAME TABLE Statement
In MySQL, the RENAME TABLE statement is used to rename one or more tables in a database. We will discuss about MySQL RENAME TABLE statement. We will see how to rename one table, multiple tables, and a temporary table. We will also see the different ways we have to rename a table. Sometimes our table is non-meaningful, so it is required to rename or
5 min read
MySQL Temporary Table
Do you want to store the intermediate results of your query in some table but only for a given DB session and not persist for the lifetime? Is there some way to hold temporary data for a short time without making it permanent on the database forever? If you have ever thought about this and wondered how to do that, then you have arrived at the right
5 min read
MySQL Derived Table
Structured Query Language (SQL) is a powerful tool for managing and querying relational databases, and MySQL is one of the most widely used database management systems. In MySQL, derived tables offer a flexible and efficient way to manipulate and analyze data within a query. In this article, we will get inside the concept of MySQL-derived tables, e
4 min read
How to Find Records From One Table Which Don't Exist in Another MySQL
MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides a variety of rich features to create databases and tables, insert data in them, and further manipulate them as the system evolves. In this article, w
5 min read
MySQL DROP TABLE Statement
MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides a variety of rich features to create databases and tables, insert data in them, and further manipulate them as the system evolves. In this article, w
5 min read
MySQL Can't Specify Target Table For Update in FROM Clause
The "MySQL can't specify target table for update in FROM clause" error is a common challenge encountered when attempting to update a table based on a subquery that refers to the same table in the FROM clause. This restriction is in place to prevent ambiguous or unintended updates that might arise from self-referencing subqueries. So, in this articl
3 min read
How to Return Pivot Table Output in MySQL
Pivoting a table in MySQL involves transforming rows into columns, which can be particularly useful for data analysis and reporting. While MySQL does not have a native PIVOT function like some other database systems, you can achieve pivoting using conditional aggregation with the CASE statement. So, In this article, we will explore how can I return
5 min read
How to Get the Datatype of Table Columns in MySQL?
Sometimes we need to get the datatype of table columns in MySQL when working on a database. Knowing the data type of the column ensures the data integrity of the table. If we insert a string instead of an integer value that will affect the working of the database and will show an error. So, in this article, we will discuss how to check the data typ
3 min read