Creating Tables in MySQL

Introduction

In MySQL, a table is a collection of related data held in a structured format within a database. It consists of columns and rows where individual pieces of data are stored. In this tutorial, we will learn how to create tables in MySQL.

Prerequisites

Before we start, make sure that you have MySQL installed on your machine. If not, you can download it from the official MySQL website. You should also have a basic understanding of SQL commands.

Creating a Table

To create a table in MySQL, we use the CREATE TABLE statement. The basic syntax is as follows:

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

Here, table_name is the name of the table that you want to create. column1, column2, column3 are the names of the columns. datatype is the type of data that can be stored in the column (like integer, varchar, date, etc.).

Example

Let's create a table named students with four columns: id, name, age, and grade.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    age INT,
    grade CHAR(1)
);

In the above SQL query:

• students is the name of the table.
• id, name, age, grade are the names of the columns.
• INT, VARCHAR(100), CHAR(1) are the data types of the columns.

Primary Key

A primary key is a column (or a combination of columns) with a unique value for each row. Each table should have a primary key column (or columns). In our students table, the id column can be our primary key.

To define a column as a primary key, we use the PRIMARY KEY keyword.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade CHAR(1)
);

Auto Increment

If you want MySQL to automatically increase the value of a column each time a new record is added, you can use the AUTO_INCREMENT keyword. This is often used with primary key columns.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade CHAR(1)
);

Not Null

By default, a column can hold NULL. If you don't want a column to have a NULL value, you can use the NOT NULL keyword.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    grade CHAR(1)
);

Conclusion

In this tutorial, we've learned how to create tables in MySQL, define primary keys, and use the AUTO_INCREMENT and NOT NULL keywords. Practice creating your own tables and defining different types of columns to become more comfortable with these concepts.