Create Tables in MySQL

Introduction

After creating a database, the next important step is to create tables.

Tables are used to store data in a structured format using rows and columns. Each table represents a specific type of data, such as students, employees, or products.

In MySQL, tables are created using the CREATE TABLE statement.

What is CREATE TABLE

The CREATE TABLE statement is used to define a new table in a database.

When creating a table, you specify:

• Column names

• Data types

• Constraints (like PRIMARY KEY, NOT NULL, etc.)

Basic Syntax

CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype ); 

Example

CREATE TABLE Students ( id INT, name VARCHAR(50), age INT ); 

This creates a table named Students with three columns:

• id → integer value

• name → text (up to 50 characters)

• age → integer value

Creating a Table with Constraints

You can also add constraints while creating a table.

Example:

CREATE TABLE Students ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT ); 

Here:

• PRIMARY KEY ensures unique values

• NOT NULL ensures the column cannot be empty

Common Data Types

Some commonly used MySQL data types:

• INT → numbers

• VARCHAR → text

• DATE → date values

• FLOAT → decimal numbers

Create a Table in a Specific Database

Before creating a table, make sure a database is selected:

USE school_db; 

Then create the table:

CREATE TABLE Students ( id INT, name VARCHAR(50) ); 

Example Scenario

For a student management system, you may create:

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

This table stores student details.

Common Mistakes

• Forgetting to select a database

• Using incorrect data types

• Missing commas between columns

• Not defining a primary key

Key Points to Remember

• Tables store data in rows and columns

• Use CREATE TABLE to create a table

• Always define appropriate data types

• Constraints help maintain data integrity

• Select a database before creating tables