Create Table in MySQL

To create a table in MySQL, you use the CREATE TABLE statement followed by the table name and a list of columns with their data types and optional constraints. Here's the basic syntax:

<span class="pln">
CREATE TABLE table_name </span><span class="pun">(</span><span class="pln">
    column1 datatype </span><span class="pun">[</span><span class="pln">constraints</span><span class="pun">],</span><span class="pln">
    column2 datatype </span><span class="pun">[</span><span class="pln">constraints</span><span class="pun">],</span><span class="pln">
    </span><span class="pun">...</span><span class="pln">
    columnN datatype </span><span class="pun">[</span><span class="pln">constraints</span><span class="pun">]</span><span class="pln">
</span><span class="pun">);</span><span class="pln">
</span>

Let's break down the components:

• CREATE TABLE: This is the SQL statement used to create a new table.

• table_name: This is the name of the table you want to create. Choose a descriptive name that reflects the purpose of the table.

• column1, column2, ..., columnN: These are the names of the columns in the table.

• datatype: This specifies the data type of each column. For example, INT, VARCHAR, DATE, etc.

• constraints: These are optional and define rules or limits for the data that can be stored in each column. Common constraints include PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, etc.

Here's an example of creating a simple table named students with three columns: student_id, name, and age:

<span class="pln">
CREATE TABLE students </span><span class="pun">(</span><span class="pln">
    student_id INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">
    name VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln">
    age INT
</span><span class="pun">);</span><span class="pln">
</span>

This statement creates a table named students with three columns:

1. student_id: An integer column serving as the primary key.
2. name: A variable character (string) column with a maximum length of 100 characters.
3. age: An integer column to store the age of the student.

After executing this SQL statement, the students table will be created in your MySQL database. You can then use it to store data by inserting rows into it using the INSERT INTO statement.

<span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> departments table
CREATE TABLE departments </span><span class="pun">(</span><span class="pln">
    department_id INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">
    department_name VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">)</span><span class="pln">
</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> sample data </span><span class="kwd">into</span><span class="pln"> departments table
INSERT INTO departments </span><span class="pun">(</span><span class="pln">department_id</span><span class="pun">,</span><span class="pln"> department_name</span><span class="pun">)</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'HR'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Finance'</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> employees table
CREATE TABLE employees </span><span class="pun">(</span><span class="pln">
    employee_id INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">
    name VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln">
    department_id INT</span><span class="pun">,</span><span class="pln">
    FOREIGN KEY </span><span class="pun">(</span><span class="pln">department_id</span><span class="pun">)</span><span class="pln"> REFERENCES departments</span><span class="pun">(</span><span class="pln">department_id</span><span class="pun">)</span><span class="pln">
</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> sample data </span><span class="kwd">into</span><span class="pln"> employees table
INSERT INTO employees </span><span class="pun">(</span><span class="pln">employee_id</span><span class="pun">,</span><span class="pln"> name</span><span class="pun">,</span><span class="pln"> department_id</span><span class="pun">)</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Alice'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Bob'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Charlie'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'David'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> customers table
CREATE TABLE customers </span><span class="pun">(</span><span class="pln">
    customer_id INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">
    customer_name VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">)</span><span class="pln">
</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> sample data </span><span class="kwd">into</span><span class="pln"> customers table
INSERT INTO customers </span><span class="pun">(</span><span class="pln">customer_id</span><span class="pun">,</span><span class="pln"> customer_name</span><span class="pun">)</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'John'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Jane'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Smith'</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> products table
CREATE TABLE products </span><span class="pun">(</span><span class="pln">
    product_id INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">
    product_name VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">)</span><span class="pln">
</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> sample data </span><span class="kwd">into</span><span class="pln"> products table
INSERT INTO products </span><span class="pun">(</span><span class="pln">product_id</span><span class="pun">,</span><span class="pln"> product_name</span><span class="pun">)</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">101</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Laptop'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">102</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Tablet'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">103</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Smartphone'</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> orders table
CREATE TABLE orders </span><span class="pun">(</span><span class="pln">
    order_id INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">
    customer_id INT</span><span class="pun">,</span><span class="pln">
    product_id INT</span><span class="pun">,</span><span class="pln">
    order_date DATE</span><span class="pun">,</span><span class="pln">
    FOREIGN KEY </span><span class="pun">(</span><span class="pln">customer_id</span><span class="pun">)</span><span class="pln"> REFERENCES customers</span><span class="pun">(</span><span class="pln">customer_id</span><span class="pun">),</span><span class="pln">
    FOREIGN KEY </span><span class="pun">(</span><span class="pln">product_id</span><span class="pun">)</span><span class="pln"> REFERENCES products</span><span class="pun">(</span><span class="pln">product_id</span><span class="pun">)</span><span class="pln">
</span><span class="pun">);</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> sample data </span><span class="kwd">into</span><span class="pln"> orders table
INSERT INTO orders </span><span class="pun">(</span><span class="pln">order_id</span><span class="pun">,</span><span class="pln"> customer_id</span><span class="pun">,</span><span class="pln"> product_id</span><span class="pun">,</span><span class="pln"> order_date</span><span class="pun">)</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">101</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2024-01-15'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="lit">102</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2024-01-16'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">103</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2024-01-17'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="lit">101</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2024-01-18'</span><span class="pun">);</span><span class="pln">
</span>