Session 5.3 – DML Commands: INSERT, UPDATE, DELETE, SELECT

Module 5: PHP Database Connectivity | Duration: 1 hr

Learning Objectives

By the end of this session, students will be able to:

Understand Data Manipulation Language (DML) and its purpose
Insert data into tables using INSERT statements
Retrieve data using SELECT queries with various clauses
Update existing records using UPDATE statements
Delete records using DELETE statements
Use WHERE clauses for filtering data
Perform advanced queries with JOIN, GROUP BY, and aggregate functions

Introduction to DML

Data Manipulation Language (DML) is a subset of SQL used to manage data within database objects. Unlike DDL which defines structure, DML deals with the data itself.

Main DML Commands

INSERT: Adds new records to a table
SELECT: Retrieves data from one or more tables
UPDATE: Modifies existing records
DELETE: Removes records from a table

Note: DML operations can be rolled back (using transactions) unlike DDL commands.

INSERT Statement

The INSERT statement is used to add new records to a table.

Basic Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
                        

Single Row Insert

-- Insert with specified columns
INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- Insert all columns (in order)
INSERT INTO users
VALUES (1, 'jane_smith', 'jane@example.com', 'hashed_password', NOW());
                        

Multiple Row Insert

-- Insert multiple records at once
INSERT INTO products (product_name, price, stock_quantity)
VALUES
    ('Laptop', 999.99, 50),
    ('Mouse', 29.99, 200),
    ('Keyboard', 79.99, 150),
    ('Monitor', 299.99, 75);
                        

Insert from SELECT

-- Insert data from another table
INSERT INTO archived_orders (order_id, user_id, order_date, total_amount)
SELECT order_id, user_id, order_date, total_amount
FROM orders
WHERE order_date < '2023-01-01';
                        

Insert with Default Values

-- Use DEFAULT keyword for columns with default values
INSERT INTO products (product_name, price, stock_quantity, is_active)
VALUES ('Webcam', 89.99, 100, DEFAULT);

-- Omit columns with default values
INSERT INTO products (product_name, price)
VALUES ('Headphones', 59.99);
                        

SELECT Statement

The SELECT statement is used to query and retrieve data from database tables.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column]
[LIMIT number];
                        

Basic SELECT Queries

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT username, email FROM users;

-- Select with alias
SELECT username AS 'User Name', email AS 'Email Address'
FROM users;

-- Select distinct values
SELECT DISTINCT category_id FROM products;
                        

ORDER BY Clause

-- Sort ascending (default)
SELECT * FROM products
ORDER BY price ASC;

-- Sort descending
SELECT * FROM products
ORDER BY price DESC;

-- Sort by multiple columns
SELECT * FROM products
ORDER BY category_id ASC, price DESC;
                        

LIMIT Clause

-- Limit number of results
SELECT * FROM products
LIMIT 10;

-- Pagination: LIMIT with OFFSET
SELECT * FROM products
LIMIT 10 OFFSET 20;

-- Alternative syntax: LIMIT offset, count
SELECT * FROM products
LIMIT 20, 10;
                        

Aggregate Functions

-- COUNT: Count rows
SELECT COUNT(*) AS total_users FROM users;

-- SUM: Calculate total
SELECT SUM(total_amount) AS total_sales FROM orders;

-- AVG: Calculate average
SELECT AVG(price) AS avg_price FROM products;

-- MIN and MAX
SELECT MIN(price) AS lowest_price, MAX(price) AS highest_price
FROM products;
                        

WHERE Clause

The WHERE clause is used to filter records based on specific conditions.

Comparison Operators

-- Equal to
SELECT * FROM products WHERE category_id = 1;

-- Not equal to
SELECT * FROM products WHERE category_id != 1;
SELECT * FROM products WHERE category_id <> 1;

-- Greater than, Less than
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <= 50;

-- BETWEEN
SELECT * FROM products
WHERE price BETWEEN 50 AND 200;
                        

Logical Operators

-- AND operator
SELECT * FROM products
WHERE price > 100 AND stock_quantity > 0;

-- OR operator
SELECT * FROM products
WHERE category_id = 1 OR category_id = 2;

-- NOT operator
SELECT * FROM products
WHERE NOT is_active = 0;

-- Complex conditions with parentheses
SELECT * FROM products
WHERE (category_id = 1 OR category_id = 2)
  AND price < 100;
                        

Pattern Matching

-- LIKE operator (% = any characters, _ = single character)
SELECT * FROM users
WHERE username LIKE 'john%';

-- Find emails ending with gmail.com
SELECT * FROM users
WHERE email LIKE '%@gmail.com';

-- Find products containing 'phone'
SELECT * FROM products
WHERE product_name LIKE '%phone%';

-- IN operator
SELECT * FROM products
WHERE category_id IN (1, 2, 3, 5);

-- IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;
                        

UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Warning: Always use WHERE clause with UPDATE to avoid updating all records!

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
                        

Update Examples

-- Update single column
UPDATE users
SET email = 'newemail@example.com'
WHERE user_id = 1;

-- Update multiple columns
UPDATE products
SET price = 899.99,
    stock_quantity = 100,
    updated_at = NOW()
WHERE product_id = 5;

-- Update with calculation
UPDATE products
SET price = price * 1.1
WHERE category_id = 1;

-- Update based on condition
UPDATE orders
SET status = 'completed'
WHERE order_date < DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND status = 'processing';
                        

Update with JOIN

-- Update using data from another table
UPDATE orders o
JOIN users u ON o.user_id = u.user_id
SET o.discount = 10
WHERE u.membership_level = 'premium';
                        

DELETE Statement

The DELETE statement is used to remove records from a table.

Warning: Always use WHERE clause with DELETE to avoid deleting all records! Consider backing up data before deletion.

Syntax

DELETE FROM table_name
WHERE condition;
                        

Delete Examples

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

-- Delete multiple records
DELETE FROM products
WHERE stock_quantity = 0 AND is_active = 0;

-- Delete old records
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Delete with LIMIT
DELETE FROM temp_data
ORDER BY created_at ASC
LIMIT 1000;
                        

Delete with JOIN

-- Delete using data from another table
DELETE o
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.status = 'deleted';
                        

TRUNCATE vs DELETE

-- DELETE: Can use WHERE, slower, can rollback
DELETE FROM temp_table WHERE status = 'expired';

-- TRUNCATE: Removes all rows, faster, cannot rollback, resets AUTO_INCREMENT
TRUNCATE TABLE temp_table;
                        

Advanced Queries

JOIN Operations

-- INNER JOIN: Returns matching records from both tables
SELECT u.username, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

-- LEFT JOIN: Returns all records from left table
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

-- RIGHT JOIN: Returns all records from right table
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;

-- Multiple JOINs
SELECT o.order_id, u.username, p.product_name, oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;
                        

GROUP BY and HAVING

-- Group by with aggregate
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id;

-- Group by with multiple columns
SELECT user_id, DATE(order_date) AS order_day, SUM(total_amount) AS daily_total
FROM orders
GROUP BY user_id, DATE(order_date);

-- HAVING clause (filter after grouping)
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING order_count > 5;

-- Complete example
SELECT category_id, AVG(price) AS avg_price
FROM products
WHERE is_active = 1
GROUP BY category_id
HAVING avg_price > 100
ORDER BY avg_price DESC;
                        

Subqueries

-- Subquery in WHERE clause
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery with IN
SELECT * FROM users
WHERE user_id IN (
    SELECT user_id FROM orders
    WHERE total_amount > 1000
);

-- Subquery in FROM clause
SELECT category, avg_price
FROM (
    SELECT category_id AS category, AVG(price) AS avg_price
    FROM products
    GROUP BY category_id
) AS category_stats
WHERE avg_price > 100;
                        

UNION and UNION ALL

-- UNION: Combines results, removes duplicates
SELECT username FROM active_users
UNION
SELECT username FROM inactive_users;

-- UNION ALL: Combines results, keeps duplicates
SELECT product_name, price FROM electronics
UNION ALL
SELECT product_name, price FROM accessories;
                        

Session Summary

Key Points

DML commands manipulate data within database tables
INSERT adds new records (single, multiple, or from SELECT)
SELECT retrieves data with filtering, sorting, and limiting options
WHERE clause filters records using comparison and logical operators
UPDATE modifies existing records (always use WHERE clause)
DELETE removes records (always use WHERE clause and backup data)
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) perform calculations
JOIN operations combine data from multiple tables
GROUP BY groups rows with common values
HAVING filters grouped results
Subqueries enable complex nested queries

Next Session Preview

In the next session, we will explore DCL (Data Control Language) and Transaction Control commands for managing user permissions and ensuring data consistency.

Previous Session Course Home Next Session