MySQL CREATE Table

Introduction

In this chapter, we will learn how to create tables in MySQL. Tables are essential for storing and organizing data within a database. We will cover the syntax, examples, and important considerations when creating tables.

Creating a Table

To create a new table, we use the CREATE TABLE statement. This command defines the table's structure, including its columns, data types, and constraints.

Syntax

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

• table_name: The name of the table you want to create.
• column1, column2, ..., columnN: The names of the columns in the table.
• datatype: The data type for the column (e.g., INT, VARCHAR(50), DATE).
• constraints: Optional constraints for the column (e.g., PRIMARY KEY, NOT NULL, AUTO_INCREMENT).

Example

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

This example creates a table named employees with five columns: id, first_name, last_name, email, and hire_date.

Viewing the Table Structure

After creating a table, you might want to view its structure to verify that it has been created correctly. You can use the DESCRIBE statement to display the structure of a table.

Syntax

DESCRIBE table_name;

• table_name: The name of the table whose structure you want to view.

Example

DESCRIBE employees;

Output

Field	Type	Null	Key	Default	Extra
id	INT	NO	PRI	NULL	auto_increment
first_name	VARCHAR(50)	NO		NULL
last_name	VARCHAR(50)	NO		NULL
email	VARCHAR(100)	YES	UNI	NULL
hire_date	DATE	YES		NULL

This output shows the structure of the employees table, including each column's name, data type, whether it can be null, key information, default values, and any extra information like auto_increment.

Full Example

Let's go through a full example where we create a database, create a table, and view the table's structure.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. View the Table Structure:

DESCRIBE employees;

Output

Field	Type	Null	Key	Default	Extra
id	INT	NO	PRI	NULL	auto_increment
first_name	VARCHAR(50)	NO		NULL
last_name	VARCHAR(50)	NO		NULL
email	VARCHAR(100)	YES	UNI	NULL
hire_date	DATE	YES		NULL

Important Considerations

• Naming Conventions: Use meaningful names for your tables and columns to make your database easier to understand.
• Data Types: Choose appropriate data types for each column to ensure data integrity and optimal storage.
• Constraints: Use constraints such as PRIMARY KEY, UNIQUE, NOT NULL, and AUTO_INCREMENT to enforce data integrity.
• Indexes: Consider adding indexes to columns that are frequently used in search conditions to improve query performance.

Conclusion

Creating tables in MySQL is a fundamental task for organizing and storing data in a database. This chapter covered the CREATE TABLE statement, provided examples, and explained how to view a table's structure using the DESCRIBE statement.