CREATE TABLE syntax in MySQL

Introduction

We use CREATE TABLE to make a new table in a database. Tables store data in rows and columns, like a spreadsheet.

When starting a new project and you need to save information like users or products.

When you want to organize data into categories with specific details.

When you need to prepare a place to store data before adding any records.

When designing a database to keep track of things like orders, employees, or events.

Syntax

MySQL

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

table_name is the name you give your table.

Each column has a name and a type, like numbers or text.

Examples

This creates a table named users with three columns: id, name, and email.

MySQL

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

This table products stores product details with a price that has two decimal places.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  price DECIMAL(10,2)
);

The orders table has an auto-incrementing order_id and stores the date of the order.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  order_date DATE
);

Sample Program

This query creates a table called employees with columns for ID, first and last names, and hire date.

MySQL

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hire_date DATE
);

Important Notes

You must choose the right data type for each column to store the correct kind of data.

Primary keys help uniquely identify each row in the table.

Some columns can have constraints like NOT NULL to make sure they always have a value.

Summary

CREATE TABLE makes a new table to store data.

Define columns with names and data types inside parentheses.

Use constraints like PRIMARY KEY to organize and protect your data.