MySQL - Create Tables

In the MySQL relational database system, SQL is used to store data in the form of structured tables. These tables consist of fields and records. A field represents a column that defines the type of data to be stored in a table, and a record is a row containing the actual data. MySQL provides various queries to interact with the data, allowing you to create tables, update them, delete them, etc.

MySQL Create Table Statement

To create a table in MySQL RDBMS in prompt, CREATE TABLE statement is used.

One can create any number of tables in an SQL Server database. However, a limit exists on the number of objects that can be present in a database. Including tables, views, indexes etc., a database cannot exceed 2,147,483,647 objects. Therefore, a single user-defined table can define a maximum of 1024 columns.

A MySQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column's data type. Note that each table must be uniquely named in a database.

To begin with, the table creation command requires the following details −

• Name of the table.
• Name of the columns.
• Definitions for each column.

Syntax

Following is the basic SQL syntax to create a MySQL table −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE Statement −

CREATE TABLE CUSTOMERS (
   ID INT 				AUTO_INCREMENT,
   NAME VARCHAR(20) 	NOT NULL,
   AGE INT 				NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, a few items need explanation −

• Field Attribute AUTO_INCREMENT in MySQL automatically increments the value in the ID column by one for each new record you add. It starts from the next available number.

• Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user tries to create a record with a NULL value in that field, then MySQL will raise an error.

• Keyword PRIMARY KEY is used to define a column as a primary key. It ensures that every record in that column is unique. You can also use it for multiple columns by separating them with commas.

Output

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.03 sec)

Verification

Once we have finished creating the table, we can check whether it has been created successfully or not using the following query −

DESC CUSTOMERS;

The above query displays the structure of the CUSTOMERS table: column names, their datatypes, etc.

Creating Tables from Command Prompt

We can create a MySQL table from the command prompt by defining its structure and columns.

Following are the steps to perform to create a MySQL table from Command Prompt:

• Firstly, open the command prompt and enter the following command: mysql -u root -p to access the MySQL database management system.

• After entering the command, enter the password to log into the MySQL server.

• Then, we can start creating a table using the respected SQL CREATE TABLE query.

Example

In the following example, we are creating a MySQL table named CUSTOMERS from command prompt.

CREATE TABLE CUSTOMERS (
   NAME VARCHAR(20) NOT NULL,
   ID INT AUTO_INCREMENT,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

NOTE − MySQL does not terminate a command until you give a semicolon (;) at the end of SQL command.

Output

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.03 sec)

Verification

We can verify if the table has been created successfully or not using the following query −

mysql> DESC CUSTOMERS;

The above query will show the structure and description of the CUSTOMERS table −

Creating a Table from an Existing Table

We can create a duplicate table of an existing table including its structure and data, by using the SQL CREATE TABLE and SELECT statements. The duplicate table has the same columns and their definitions, and it also gets filled with the same data as the original table.

Note − As it is a completely new table, any changes made in it would not be reflected in the original table.

Syntax

Following is the syntax for creating a table from another table −

CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2...columnN]
FROM EXISTING_TABLE_NAME
[WHERE CONDITION];

Here, column1, column2... are the fields of the existing table and the same would be used to create fields of the new table. And the WHERE clause is optional to use.

Example

Let us consider an existing table CUSTOMERS in the TUTORIALS database −

mysql> USE TUTORIALS;
Database changed
mysql> SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, Using the following query, we are creating a new table named SAMPLE with the same structure and records as CUSTOMERS.

CREATE TABLE SAMPLE AS
SELECT * FROM CUSTOMERS;

Output

As we can see in the output, the table SAMPLE is created successfully.

Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

Using the following SELECT statement, let us verify if the new table SAMPLE contains the records or not.

SELECT * FROM SAMPLE;

As we can in the output below, the SAMPLE table has been created with all the records from the CUSTOMERS table. −

The IF NOT EXISTS clause

If you try to create a table with an existing name an error will be generated −

CREATE TABLE Employee(Name VARCHAR(255));
ERROR 1050 (42S01): Table 'employee' already exists

If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new table will be created and if a table with the given name, already exists the query will be ignored.

CREATE TABLE Test(Name VARCHAR(255));
Query OK, 0 rows affected (0.69 sec)

Create table into MySQL Database Using a Client Program

In addition to Create a table into MySQL Database using the MySQL query, we can also perform the CREATE TABLE operation using a client program.

Syntax

Following are the syntaxes to Create a table in various programming languages −

$sql = "CREATE TABLE table_name (column_name column_type)";
$mysqli->query($sql);

sql = "CREATE TABLE table_name (column_name column_type)";
con.query(sql);

String sql = "CREATE TABLE table_name (column_name column_type)";
statement.execute(sql);

sql = "CREATE TABLE table_name (column_name column_type)";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf('Connected successfully.<br />');

$sql = "CREATE TABLE tutorials_tbl( ".
   "tutorial_id INT NOT NULL AUTO_INCREMENT, "
   "tutorial_title VARCHAR(100) NOT NULL, ".
   "tutorial_author VARCHAR(40) NOT NULL, ".
   "submission_date DATE, ".
   "PRIMARY KEY ( tutorial_id )); ";
if ($mysqli->query($sql)) {
   printf("Table tutorials_tbl created successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not create table: %s<br />", $mysqli->error);
}
$mysqli->close();

Connected successfully.
Table tutorials_tbl created successfully.

var mysql = require('mysql2');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "*****"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
  
  //Creating a table
  sql = "CREATE TABLE tutorials_tbl(tutorial_id INT NOT NULL AUTO_INCREMENT,tutorial_title VARCHAR(100) NOT NULL,tutorial_author VARCHAR(40) NOT NULL,submission_date DATE,PRIMARY KEY ( tutorial_id ));"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("The table tutorials_tbl is created successfully!");
  });
});

Connected!
--------------------------
The table tutorials_tbl is created successfully!

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CreateTable {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Create new table...!
            String sql = "CREATE TABLE tutorials_tbl (ID Int Auto_Increment not null, tutorial_title Varchar(50) Not Null, tutorial_author Varchar(30) Not Null, Primary Key(ID))";
            statement.executeUpdate(sql);
            System.out.println("Table created successfully...!");

            ResultSet resultSet = statement.executeQuery("DESCRIBE tutorials_tbl");
            while (resultSet.next()) {
                System.out.print(resultSet.getNString(1));
                System.out.println();
            }
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table created successfully...!
ID
tutorial_title
tutorial_author

import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='localhost', database='tut'
)
#Creating a cursor object 
cursor = conn.cursor()
#Creating a table
sql ='''CREATE TABLE tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
)'''
cursor.execute(sql)
print ("The table tutorials_tbl is created successfully!")
#Closing the connection
conn.close()

The table tutorials_tbl is created successfully!