MySQL use Tables to store, and Manage Data. MySQL Table is a combination of Rows and Columns. We have to use Create Table Statement to create a new table in MySQL. In this article we will show you, How to Create Table in MySQL with example. Here, we will use both MySQL command prompt, and Workbench for MySQL Create Table statement
MySQL Create Table Syntax
The basic syntax to create table in MySQL is as shown below:
CREATE TABLE 'Table Name' ( Column_Name1 Data_Type(Size) [NULL | NOT NULL], Column_Name2 Data_Type(Size) [NULL | NOT NULL], … Column_NameN Data_Type(Size) [NULL | NOT NULL] );
Let us see the individual items from the syntax:
- Table Name: Please provide Unique table name. It will throw an error, if you use the already existing table name.
- Column_Names: Please specify the Unique Column Names required for this table
- Data Type: Please specify the valid Data type for each and every individual column. For example, Int, Varchar, float etc
- Size: Please provide valid number here
- NULL or NOT NULL: If you select NULL option then column will accept both normal values and NULL values otherwise, it will throw an error saying Column should not be empty
MySQL Create Table example – Using Command Prompt
Before we start adding new table to company Database, Let us see the list of existing tables inside the database. In order to get this information you have to use SHOW TABLES command
Below screenshot will show you the available databases.
As you can see, company database had no existing tables. For the demonstration purpose, we are going to create new table called sales.
CREATE TABLE sales ( SaleID INT NOT NULL AUTO_INCREMENT, Product_Name VARCHAR(100) NULL, Product_Description TEXT );
From the below screenshot you can observe that, Command is executed successfully.
Now, let me show you the list of tables available in company database. And you can see the sales table name in the list
MySQL Create Table Example – Workbench
We are going to use the below shown code to create table called Customers inside the company Database
USE company; CREATE TABLE customers ( CustID INT NOT NULL AUTO_INCREMENT, First_Name VARCHAR(50) NULL, Last_Name VARCHAR(50) NULL, Education VARCHAR(50) NULL, Profession VARCHAR(50) NULL, Yearly_Income INT NULL, Sales FLOAT(10, 2) NULL, PRIMARY KEY (CustID) )
NOTE: Before you start creating a TABLE, It is always advisable to check if a Table exists or not.
From the above code you can observe that, We declared 7 Columns:
- Our first column is CustID of Integer data type and it will not allow NULL values. We also defined this column as Auto Incremental. (This is optional, If you want You can remove Auto_Increment portion completely)
- FirstName, LastName, Education, Profession columns belongs to Varchar data type and it will allow NULL values. We also assigned the size to 50, it means these columns will accept up to 50 characters
- Sixth column is Yearly Income of INT data type, and it will allow NULL values.
- Sales columns belongs to float data type, and it will allow NULL values.
From the below screenshot you can observe that, Command is executed successfully and you can see the Newly created table under the Tables folder.
TIP: If you didn’t find the newly created table, Please click on the refresh button beside the SCHEMAS
How to find MySQL Table Definition
If you want to find the existing table definition then, right-click on the table name and select the Table Inspector option from the context menu.
Once you select the Table Inspector option, a new tab will opened with table Information. Please go to the DDL tab to see the table definition.
Check whether MySQL Table name exists or not?
Let us see what will happen, if we create a new table with existing name. From the below screenshot you can observe that, it is throwing error saying: Table Sales already exists.
USE company; CREATE TABLE sales ( Id INT NOT NULL, Name VARCHAR(50) NULL, Country VARCHAR(50) )
NOTE: It is always advisable to check whether the table name already exists or not
If you have the privilege to see the list of tables available in a databases then you can use the SHOW TABLES command. If it is not the case then use below option.
Following statement will only execute Create Table Statement, if the sales in not available in company database
USE company; CREATE TABLE IF NOT EXISTS sales ( Id INT NOT NULL, Name VARCHAR(50) NULL, Country VARCHAR(50) );
Try different table name
USE company; CREATE TABLE IF NOT EXISTS salesDetails ( Id INT NOT NULL, Name VARCHAR(50) NULL, Country VARCHAR(50) );
MySQL Create Table Using Workbench GUI
Within the SCHEMAS, Expand the Database folder on which you want to create table. Right click on the Tables folder it will open the context menu. Please select Create Table… option as shown below
Once you select the Create Table… option, following window will be opened to design table. Please change the Table_Name from new_table to Employees
You can use the Collation drop down box to change the Schema Default collation.
You can change the Default server Engine using Engine drop down list
For now, we are leaving all the default configurations. Next, click on the arrow to get more space for designing the table.
Click on the Empty row below the Column Name will automatically create a new column for you. Let me rename it as Empid
Please change the Data Type as per your requirement. And also, use check boxes too
From the below screenshot you can see, there are four column. Click the apply button to create new table employees.
TIP : Please use the check boxes beside the Column Names, or under Storage section to make the column as: Primary, Unique, Not Null, Binary, Unsigned, Auto Incremental, Zero Fill, and Generated.
Click Apply button
Next, click Finish button
From the below screenshot you can observe that, you can see the employees table
TIP: You can use INSERT Statement to insert data into this newly created table
Thank You for Visiting Our Blog