MySQL CREATE TABLE: How to Create Tables with Examples

Tutorial

MYSQL

MySQL Tutorial

MySQL Introduction

MySQL COUNT, AVG, SUM

MySQL Database

MySQL Create Table

❮ Previous

Next ❯

MySQL CREATE TABLE Statement

The CREATE TABLE statement in MySQL is used to define a new table within a database, specifying column names, data types, and constraints.

Syntax

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

table_name: Name of the table.
column1, column2, ...: Column names.
datatype: Defines the type of data (e.g., INT, VARCHAR, DATE).
constraints: Rules like PRIMARY KEY, NOT NULL, UNIQUE, etc.

Example: Creating a Students Table

CREATE TABLE students (

student_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

age INT CHECK (age >= 18),

email VARCHAR(100) UNIQUE,

enrollment_date DATE DEFAULT CURRENT_DATE

);

✅ student_id is an AUTO_INCREMENT PRIMARY KEY (unique ID for each student).

✅ name cannot be NULL (NOT NULL).

✅ age must be 18 or older (CHECK (age >= 18)).

✅ email must be unique (UNIQUE).

✅ enrollment_date defaults to current date (DEFAULT CURRENT_DATE).

Check All Tables in a Database

SHOW TABLES;

View Table Structure

DESC students;

MySQL CREATE TABLE Using Another Table

In MySQL, you can create a new table using the structure or data from an existing table.

1. Create a Table with the Same Structure (Without Data)

To create a new table with the same structure as an existing table without copying data, use:

CREATE TABLE new_table LIKE existing_table;

Example

CREATE TABLE students_backup LIKE students;

This creates students_backup with the same columns and data types as students but without any data.

2. Create a Table and Copy Data from Another Table

To create a table and copy data from an existing table:

Syntax:

CREATE TABLE new_table AS SELECT * FROM existing_table;

Example

CREATE TABLE students_copy AS SELECT * FROM students;

This creates students_copy with the same structure and all data from students.

3. Copy Specific Columns from Another Table

To copy only specific columns into the new table:

Example

CREATE TABLE new_students AS SELECT student_id, name FROM students;

This creates new_students with only student_id and name from students.

4, Copy Data with a Condition

To copy only specific rows into the new table:

Example

CREATE TABLE adult_students AS SELECT * FROM students WHERE age >= 18;

This creates adult_students containing only students 18 years or older.

Key Differences Between LIKE & AS SELECT

Method	Copies Structure	Copies Data	Copies Constraints (e.g., PRIMARY KEY, AUTO_INCREMENT)
CREATE TABLE new_table LIKE existing_table;	✅ Yes	❌ No	✅ Yes
CREATE TABLE new_table AS SELECT * FROM existing_table;	✅ Yes	✅ Yes	❌ No

Note

✔ Use LIKE when you only want the structure.

✔ Use AS SELECT when you need both structure and data but without constraints.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.