How to create a table in MySql

MySQL is a widely used relational database management system. It is used by developers, database administrators, and data analysts to store and manage data. In this tutorial, we will discuss how to create a table in MySQL using the CREATE TABLE statement.

The CREATE TABLE statement is used to create a new table in a database. It consists of the following parts:

  1. Table name: This is the name of the table you want to create.
  2. Column names and data types: This defines the columns in the table and their data types.
  3. Constraints: These are rules you can apply to the columns in the table to ensure data integrity.

create table mySql syntax

  
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Here’s what each of the parts of this statement mean:

  • CREATE TABLE is the statement used to create a new table.
  • table_name is the name of the table you want to create.
  • column1, column2, etc. are the names of the columns in the table.
  • datatype is the data type of each column, such as INT, VARCHAR, DATE, etc.
  • constraint is an optional constraint that you can add to a column, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, etc.

For example,

  create table student(id int,
                   first_name varchar(30),last_name varchar(30),
                   semester varchar(20),address varchar(80));

if the table is created without any error then it will show the following statement

Above MySQL create statement create a student table with primary key id,
with auto-increment field value and two unique keys mobile_no and email_id,
at the time of creating the user add_date is added CURRENT_TIMESTAMP as the default value.

This table also uses mysql create table auto_increment

Result:

    Query OK, 0 rows affected (1.44 sec)

This query will result in a table with the above fields. The first column will hold numbers while the other columns are types of varchar.

Here is an another example of a basic CREATE TABLE statement:

CREATE TABLE employees (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT(11) NOT NULL,
  salary DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id)
);

In this example, we are creating a new table called “employees”. The table has four columns:

  • “id”: This is an integer column with a maximum length of 11 digits. It is set to auto-increment, which means that MySQL will automatically assign a unique value to this column for each new row that is inserted.
  • “name”: This is a varchar column with a maximum length of 50 characters. It cannot be null (i.e., it must contain a value).
  • “age”: This is an integer column with a maximum length of 11 digits. It cannot be null.
  • “salary”: This is a decimal column with a precision of 10 digits and a scale of 2 digits. It cannot be null.

The final line of the statement specifies that the “id” column is the primary key of the table.

Here are some additional tips and examples to help you create tables in MySQL:

  1. Use backticks () around table and column names to avoid conflicts with reserved words. For example, name is a better column name than name.
  2. Use data types that are appropriate for your data. For example, use INT for integer values, VARCHAR for character strings, and DECIMAL for numbers with decimal places.
  3. Add constraints to your columns to ensure data integrity. For example, you can use the UNIQUE constraint to ensure that a column contains only unique values.
  4. Specify default values for columns if appropriate. For example, you can set a default value for a column to be used when no value is specified during insert.
  5. Use the COMMENT clause to add comments to your table and column definitions for documentation purposes.

Here is another example of a CREATE TABLE statement that includes some of these tips:

CREATE TABLE customers (
  id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Customer ID',
  first_name VARCHAR(50) NOT NULL COMMENT 'First name of customer',
  last_name VARCHAR(50) NOT NULL COMMENT 'Last name of customer',
  email VARCHAR(100) NOT NULL COMMENT 'Email address of customer',
  phone VARCHAR(20) DEFAULT NULL COMMENT 'Phone number of customer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the record was created',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Date and time the record was last updated',
  PRIMARY KEY (id),
  UNIQUE KEY email (email`)
);

Create Table in Mysql with primary key

create table student(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
fname varchar(100),
lname varchar(30),
address varchar(80),
mobile_no varchar(12),
email_id varchar(100),
city varchar(50),
add_date timestamp DEFAULT CURRENT_TIMESTAMP,
primary key(id),
unique key(mobile_no),
unique key(email_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySql create table if not exists

Syntax of create table if not exist is
CREATE TABLE IF NOT EXISTS table_name(............);

To modity the student table with CREATE TABLE IF NOT EXISTS the query is as below

CREATE TABLE IF NOT EXISTS student(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
fname varchar(100),
lname varchar(30),
address varchar(80),
mobile_no varchar(12),
email_id varchar(100),
city varchar(50),
add_date timestamp DEFAULT CURRENT_TIMESTAMP,
primary key(id),
unique key(mobile_no),
unique key(email_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

Create table ... Like statement with syntax and examples

Syntax

CREATE TABLE new_table_name
LIKE existing_table_name;

Suppose you have an existing table called users that has the following structure:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

If you want to create a new table called customers that has the same structure as the users table, you can use the CREATE TABLE ... LIKE statement like this:

CREATE TABLE customers
LIKE users;

The new table, however, does not contain any data from the users table.

Additional columns or data 

CREATE TABLE customers
LIKE users
    COMMENT 'Customers table'
    ENGINE=InnoDB
    ROW_FORMAT=DYNAMIC
    INDEX (name)
    ALTER COLUMN age SET DEFAULT 18
    RENAME COLUMN name TO customer_name;

 CREATE TABLE ... SELECT Statement

The CREATE TABLE ... SELECT statement in MySQL allows you to create a new table based on the results of a SELECT statement.

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

Suppose you have an existing table called orders that has the following structure:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    order_date DATE,
    order_amount DECIMAL(10,2)
);

If you want to create a new table called big_orders that contains only the orders with an order amount greater than 1000, you can use the CREATE TABLE ... SELECT a statement like this:

CREATE TABLE big_orders
AS
SELECT *
FROM orders
WHERE order_amount > 1000;

You can also specify the columns you want to include in the new table instead of using the wildcard *

CREATE TABLE big_orders
AS
SELECT order_id, customer_name, order_amount
FROM orders
WHERE order_amount > 1000;

Create table with auto increment

CREATE TABLE table_name (
    column1 datatype AUTO_INCREMENT PRIMARY KEY,
    column2 datatype,
    column3 datatype,
    ...
)AUTO_INCREMENT = starting_value;


CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50),
    email VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50),
    email VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) AUTO_INCREMENT = 1001;


INSERT INTO users (username, password, email) VALUES ('Ram', 'password123', '[email protected]');

how To create a table with a FOREIGN KEY constraint

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name)
);


CREATE TABLE customer(
 customer_id INT PRIMARY KEY,
 customer_name VARCHAR(50),
 customer_address VARCHAR(50),
 customer_contact VARCHAR(50),
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

You can also create a foreign key constraint that references a composite key, which is a primary key made up of multiple columns. Here's an example of creating a table order_items with a foreign key constraint that references a composite key in the orders table:

CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    item_name VARCHAR(50),
    item_quantity INT,
    PRIMARY KEY (order_id, item_id),
    FOREIGN KEY (order_id) REFERENCES orders (order_id)
);

Create Temporary Table Statement in MySql

create a temporary table that exists only for the duration of the current session. Once the session ends, the temporary table is automatically dropped and its data is lost.

The syntax for the Create Temporary Table statement:

CREATE TEMPORARY TABLE temp_table_name (
   column1 datatype constraint,
   column2 datatype constraint,
   ...
);

Here's what each of the parts of this statement mean:

  • CREATE TEMPORARY TABLE is the statement used to create a new temporary table.
  • temp_table_name is the name of the temporary table you want to create.
  • column1, column2, etc. are the names of the columns in the table.
  • datatype is the data type of each column, such as INT, VARCHAR, DATE, etc.
  • constraint is an optional constraint that you can add to a column, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, etc.
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO temp_users (id, name) VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');

mySql create table current_timestamp

Person table in MySql

CREATE TABLE person (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  mobile_no VARCHAR(20),
  aadhar_no VARCHAR(20),
  email_id VARCHAR(50),
  PAN VARCHAR(20),
  driving_licence VARCHAR(20),
  gender ENUM('Male', 'Female', 'Other'),
  dob DATE,
  add_date TIMESTAMP,
  update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Student table in MySql

CREATE TABLE student (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE,
  phone VARCHAR(15),
  address VARCHAR(100),
  dob DATE,
  gender ENUM('Male', 'Female', 'Other'),
  registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Reference

MySql