DDL and CRUD Operations

#Introduction

The previous chapter covered how MySQL works internally. Now we'll start writing SQL like a backend developer.

#SQL Command Categories

Every SQL command belongs to one of these categories:

Think of it like Java:

#DDL (Data Definition Language)

DDL changes the structure of the database:

CREATE DATABASE event_booking;
CREATE TABLE users (...);
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;

These modify the schema, not the data itself.

#CREATE TABLE

A production-style users table:

CREATE TABLE users (
    user_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(150) UNIQUE NOT NULL,
    phone      VARCHAR(15),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Why These Data Types?

Behind the Scenes

When MySQL receives CREATE TABLE users (...), it stores metadata — table name, columns, data types, constraints and indexes — in its data dictionary, and creates the table's storage (for InnoDB, a .ibd tablespace file by default).

#ALTER TABLE

Real projects evolve constantly. Suppose your manager says "We also need date of birth." No need to recreate the table:

-- Add a column
ALTER TABLE users
    ADD COLUMN date_of_birth DATE;

-- Rename a column
ALTER TABLE users
    RENAME COLUMN phone TO mobile_number;

-- Modify the data type
ALTER TABLE users
    MODIFY mobile_number VARCHAR(20);

-- Drop a column
ALTER TABLE users
    DROP COLUMN date_of_birth;

#DELETE vs TRUNCATE vs DROP

One of the most asked interview questions.

DELETE

DELETE FROM users;

Removes rows. ✅ Can use WHERE. ✅ Can be rolled back (inside a transaction with InnoDB). The AUTO_INCREMENT counter is not reset.

TRUNCATE

TRUNCATE TABLE users;

Deletes all rows, very fast, and resets the AUTO_INCREMENT counter. ❌ Can't filter rows. It behaves like a DDL operation — it causes an implicit commit, so it can't be rolled back, and it fails on a table referenced by a foreign key.

DROP

DROP TABLE users;

Deletes the data, the table structure, its indexes and its constraints. Everything is gone.

Comparison

#INSERT

-- Single row
INSERT INTO users (full_name, email)
VALUES ('Rahul', 'rahul@gmail.com');

-- Multiple rows
INSERT INTO users (full_name, email)
VALUES ('Raj',   'raj@gmail.com'),
       ('Ankit', 'ankit@gmail.com'),
       ('Amit',  'amit@gmail.com');

#UPDATE

UPDATE users
SET full_name = 'Rahul Sharma'
WHERE user_id = 1;

UPDATE users SET full_name = 'ABC';

#DELETE

-- Delete one record
DELETE FROM users
WHERE user_id = 10;

-- Delete inactive users
DELETE FROM users
WHERE status = 'INACTIVE';

Never run DELETE without confirming the WHERE clause, unless you intentionally want to remove all rows.

#SELECT

The most frequently used SQL statement.

-- All users
SELECT *
FROM users;

-- Selected columns
SELECT user_id, full_name, email
FROM users;

-- Alias
SELECT full_name AS customer_name
FROM users;

-- Distinct values
SELECT DISTINCT city
FROM users;

-- Pagination: skip the first 20 rows, return the next 10
SELECT *
FROM users
LIMIT 10 OFFSET 20;

#Real Spring Boot Example

User user = new User();
user.setFullName("Rahul");
user.setEmail("rahul@gmail.com");

userRepository.save(user);

Hibernate typically generates SQL similar to:

INSERT INTO users (full_name, email)
VALUES (?, ?);

Fetching a user with userRepository.findById(1L) becomes something like:

SELECT user_id, full_name, email, ...
FROM users
WHERE user_id = ?;

Changing a managed entity (user.setFullName("Rahul Sharma")) results in an UPDATE when the transaction is flushed. Understanding these statements makes it much easier to debug JPA/Hibernate issues.

#Hands-On Assignment

Using the event_booking database (users, events, venues, tickets):

1. Insert at least 10 records into each table.
2. Update the name of one event.
3. Delete one ticket.
4. Add a new column status to the users table.
5. Change the length of the phone column.
6. Retrieve all users; only names and emails; the first five users; and distinct cities (if you add a city column).

Mini Challenge

Design a movies table with movie ID, movie name, language, duration (minutes), release date, rating and created time. Then insert 5 movies, update one movie's rating, delete one movie, and retrieve all movies released after a chosen date.