Menu Close

Create MySQL Table

create mysql table

In this MySQL tutorial, we are going to learn all about how to create MySQL Table in the MySQL database.
In the previous tutorial, we have seen how to create a new database in MySQL server using CREATE DATABASE statement.

Here we will see step by step guide to creating a MySQL table using CREATE TABLE statement.

There are two ways to create a new table in the MySQL database.

  • Using MySQL Client Tool
  • Using MySQL Workbench Application

Create MySQL Table using MySQL Client Tool

Here we will create a MySQL table with the help of the MySQL client tool.

MySQL Create Table Syntax

The CREATE TABLE statement allows us to create a MySQL table in the MySQL database.

The following syntax indicates the syntax for creating a new table.


CREATE TABLE [IF NOT EXISTS] table_name (
column1 definition,
column2 definition,
column3 definition,
.......,

) ENGINE=storage_engine;

Let’s break down all the above syntax into some smaller parts.

  1. CREATE TABLE is the MySQL predefined keyword that allow us to create new table.The Table name must be unique within a database.
  2. IF NOT EXIST is an optional keyword in MySQL CREATE TABLE statement, that allows to check table is already created or not.If table is already exists.it will ignore the comand other it will create the database.
  3. table_name specify the name of the database.
  4. Now, you have to specify the name of the columns with definition.All the columns are seperated by commas.
  5. Now, you can specify the storage engine for the table in the engine clause.You can use any storage engine from innodb and MyISAM.If you don;t want to specify storage engine explicitly.MySQL use Innodb storage engine default.

Since MySQL version 5.0, InnoDB became the default storage engine clause. The InnoDB storage engine supports various storage engines of relational database management systems like ACID property, referential integrity, etc.

Create MySQL Table

Let’s create a simple table in the previously created database. In the previous tutorial, we have created a new database that was testing. Now it’s time to create a new table inside the testing database.

  • Login into MySQL client tool using following command.
sudo mysql -u root -p 

MySQL asks root password to validate your credentials.

If you using the Windows operating system, Then you can directly open your MySQL clint tool application and enter the root password.

  • Display all existing database using following command.
SHOW DATABASES;

Output


+--------------------+
| Database           |
+--------------------+
| college            |
| demodb             |
| information_schema |
| mydb               |
| mysql              |
| mysqldjango        |
| performance_schema |
| sakila             |
| sys                |
| testdb             |
| testing            |
| world              |
+--------------------+
12 rows in set (0.15 sec)
  • Select database testing to create mysql table below command.
USE testing;
  • Here, you can see we have a database testing, which we have created in our create mysql database tutorial.
  • Now, we are going to create students table with following details.

CREATE TABLE students (

st_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100),
course VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

If you are got the following message, That means you have successfully created your MySQL table.

Query OK, 0 rows affected (0.72 sec)

Let’s understand all the information of the students tables.


  1. First, we have used the CREATE TABLE statement to create mysql table.
  2. Specify the name of the table.In our case students is the name of the table.
  3. st_id is integer based column.If is also auto increment column.if you are insert a new record into students table wihout specify the name of the st_id, Mysql will automatically generate a sequential integer id for the st_id.PRIMARY KEY ensure every st_id must be unique.
  4. first_name is character based column whose support only 100 characters and it should not be null.
  5. last_name is also character based column whose support maximum 100 characters and it can be null.
  6. course specify the course name of the every student it can be null.
  7. created_at store the time during the creation of new record.

  • MySQL provide a command DESC table_name.This command show all the description of table.
DESC students;

After executing the above command, You will get the following output.


+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| st_id      | int(11)      | NO   | PRI | NULL              | auto_increment    |
| first_name | varchar(100) | NO   |     | NULL              |                   |
| last_name  | varchar(100) | YES  |     | NULL              |                   |
| course     | varchar(100) | NO   |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.11 sec)

To display all the tables inside the currently selected database, you can use the following command.

SHOW TABLES;

When the above command is successfully executed, You will get the following output.


+-------------------+
| Tables_in_testing |
+-------------------+
| students          |
+-------------------+
1 row in set (0.00 sec)

Create MySQL table using MySQL Workbench

There is another way to create a MySQL database table using the MySQL Workbench GUI application.
MySQL Workbench is a MySQL database IDE that allows us to perform all the operations with help of the application.

Here we are going to create another table with the help of MySQL workbench.

  • Open your MySQL workbench and login into server with the help of password.
Create MySQL Database
  • If you are logged in successfully, you are able to see all the available databases in left side.
  • To create new table, click on Create Table in Tables under database name.
create mysql table
  • Provide the name of the table and all the colums with definition and click on Apply button.
Create MySQL Table
  • Now, MySQL will show you SQL script which you provided in above step.if everything is ok click on Apply.
Create MySQL Table
  • After executed above code successfully.MySQL shows you a successful message window.Now you are able to see all the available tables in Tables section.
Create MySQL Table

Conclusion

So, Today we have seen a total of two ways to create a MySQL Table. Both are the best options to create a new MySQL database table. If you don’t want to write a raw SQL query to create a new MySQL database table, you can go with the MySQL workbench application.

I hope this article will have helped you. To learn more about the MySQL databases, you can follow our MySQL database tutorial.

Reference:- Click Here

Previous Article

Was this post helpful?

Related Posts