MySQL Tables

A table is a structured collection of related data organized into rows and columns.

students
-------------------------
id | name  | course
1  | Rahul | Python
2  | Amit  | Java

A database can contain multiple tables.

school
   |
   ├── students
   ├── teachers
   ├── courses
   ├── attendance
   └── fees

Each table can store information related to a particular part of the application.

A row represents one complete record in a table.

id | name  | course
1  | Rahul | Python

The above row represents one student record.

A column represents a particular type of information stored for each record.

id
name
course

In the students table, id, name, and course are columns.

The CREATE TABLE statement is used to create a table.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    course VARCHAR(100)
);

The name immediately after CREATE TABLE is the table name.

CREATE TABLE students (
    id INT,
    name VARCHAR(100)
);

Here, students is the table name.

Each column definition normally contains a column name and a data type.

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

Every column normally has a data type that describes the kind of data it can store.

id      INT
name    VARCHAR(100)
salary  DECIMAL(10,2)
dob     DATE

A primary key uniquely identifies rows in a table.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

Here, id is the primary key.

Use INSERT INTO to add records to a table.

INSERT INTO students
(id, name, course)
VALUES
(1, 'Rahul', 'Python');

The SELECT statement is used to retrieve data.

SELECT * FROM students;

The asterisk means all columns.

You can select specific columns from a table.

SELECT name, course
FROM students;

Only the name and course columns are returned.

The DESCRIBE statement displays information about a table's columns.

DESCRIBE students;

You can also use:

DESC students;

The SHOW TABLES command displays tables in the currently selected database.

USE school;

SHOW TABLES;

Use clear and meaningful table names.

Examples:

students
teachers
courses
employees
products
orders

Good names make database structures easier to understand.

A table can contain many columns according to the requirements of the application.

CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    email VARCHAR(150),
    mobile VARCHAR(15),
    salary DECIMAL(10,2)
);

Multiple rows can be inserted in one INSERT statement.

INSERT INTO students
(id, name, course)
VALUES
(1, 'Rahul', 'Python'),
(2, 'Amit', 'Java'),
(3, 'Neha', 'SQL');

The UPDATE statement changes existing records.

UPDATE students
SET course = 'MySQL'
WHERE id = 1;

The WHERE condition identifies the row to update.

The DELETE statement removes rows from a table.

DELETE FROM students
WHERE id = 3;

This removes the student whose ID is 3.

The ALTER TABLE statement can be used to add a column.

ALTER TABLE students
ADD mobile VARCHAR(15);

This adds a mobile column to the students table.

Modern MySQL versions support changing a column name using ALTER TABLE.

ALTER TABLE students
RENAME COLUMN name TO student_name;

The exact ALTER TABLE syntax depends on the operation and MySQL version.

You can remove a column using ALTER TABLE.

ALTER TABLE students
DROP COLUMN mobile;

The mobile column is removed from the table structure.

The DROP TABLE statement removes a table and its stored data.

DROP TABLE students;

This is different from DELETE, which removes rows while keeping the table structure.

TRUNCATE TABLE removes all rows from a table while keeping the table structure.

TRUNCATE TABLE students;

The table remains available for future use.

Tables can be related using keys such as foreign keys.

students
---------
id
course_id
   |
   ↓
courses
-------
id
course_name

Relationships help organize related data across multiple tables.

Good table design usually includes:

• Meaningful table names
• Clear column names
• Appropriate data types
• Primary keys where appropriate
• Proper relationships between tables
• Suitable constraints

Common problems while working with tables include:

• Table does not exist
• Table already exists
• Wrong column name
• Incorrect data type
• Duplicate primary key
• No database selected
• Insufficient privileges

CREATE DATABASE school;

USE school;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    mobile VARCHAR(15),
    course VARCHAR(100)
);

INSERT INTO students
(id, name, mobile, course)
VALUES
(1, 'Rahul', '9876543210', 'Python'),
(2, 'Amit', '9876501234', 'Java');

SELECT * FROM students;

DESCRIBE students;

SHOW TABLES;

Suppose you are building a student management application. You can create a students table like this:

CREATE DATABASE IF NOT EXISTS student_management;

USE student_management;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15),
    course VARCHAR(100),
    fee DECIMAL(10,2)
);

INSERT INTO students
(id, name, mobile, course, fee)
VALUES
(1, 'Rahul Kumar', '9876543210', 'Python', 15000.00),
(2, 'Amit Kumar', '9876501234', 'Java', 18000.00);

SELECT * FROM students;

This table can store basic student information for a real-world application.