Table of Contents
Data Manipulation Language (DML)
DML statements allow you to modify data in your database. The three main DML operations are INSERT (add data), UPDATE (modify data), and DELETE (remove data).
INSERT Statement
The INSERT statement adds new records to a table.
Basic INSERT Syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Inserting Single Record
INSERT INTO customers (first_name, last_name, email, phone)
VALUES ('John', 'Doe', 'john.doe@email.com', '555-1234');
Inserting Multiple Records
INSERT INTO customers (first_name, last_name, email, phone)
VALUES
('Jane', 'Smith', 'jane.smith@email.com', '555-5678'),
('Bob', 'Johnson', 'bob.johnson@email.com', '555-9012'),
('Alice', 'Brown', 'alice.brown@email.com', '555-3456');
Inserting Without Specifying Columns
You can insert values for all columns without listing them, but you must provide values in the correct order.
-- Assuming customers table has: id, first_name, last_name, email, phone, created_at
INSERT INTO customers
VALUES (NULL, 'Mike', 'Wilson', 'mike.wilson@email.com', '555-7890', NOW());
Inserting with AUTO_INCREMENT
For AUTO_INCREMENT columns, you can use NULL or omit the column.
INSERT INTO products (product_name, description, price, stock_quantity)
VALUES ('Laptop', 'High-performance laptop', 999.99, 50);
UPDATE Statement
The UPDATE statement modifies existing records in a table.
Basic UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
Updating Single Record
UPDATE customers
SET phone = '555-0000'
WHERE customer_id = 1;
Updating Multiple Columns
UPDATE customers
SET first_name = 'Jonathan', phone = '555-1111'
WHERE customer_id = 1;
Updating Multiple Records
-- Update all products in category 1 to increase price by 10%
UPDATE products
SET price = price * 1.10
WHERE category_id = 1;
Conditional Updates
-- Give discount to expensive products
UPDATE products
SET price = price * 0.90
WHERE price > 500;
DELETE Statement
The DELETE statement removes records from a table.
Basic DELETE Syntax
DELETE FROM table_name
WHERE condition;
Deleting Specific Records
DELETE FROM customers
WHERE customer_id = 5;
Deleting Multiple Records
-- Delete all orders older than 2 years
DELETE FROM orders
WHERE order_date < DATE_SUB(NOW(), INTERVAL 2 YEAR);
Deleting All Records
-- BE CAREFUL! This deletes all records
DELETE FROM temporary_table;
Important Safety Considerations
Always Use WHERE Clauses
Without WHERE clauses, UPDATE and DELETE affect ALL records!
-- DANGEROUS! Updates all customers
UPDATE customers SET city = 'Unknown';
-- SAFE: Updates specific customer
UPDATE customers SET city = 'Unknown' WHERE customer_id = 1;
Test with SELECT First
Before running UPDATE or DELETE, test your WHERE clause with SELECT.
-- Test which records will be affected
SELECT * FROM customers WHERE last_name = 'Smith';
-- Then run the update
UPDATE customers SET city = 'New York' WHERE last_name = 'Smith';
Working with Dates and Times
-- Insert with current timestamp
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, NOW(), 'pending');
-- Update with specific date
UPDATE orders
SET shipped_date = '2024-03-15'
WHERE order_id = 100;
Handling NULL Values
-- Insert with NULL values
INSERT INTO customers (first_name, last_name, email, phone)
VALUES ('John', 'Doe', 'john@email.com', NULL);
-- Update to set NULL
UPDATE customers
SET phone = NULL
WHERE customer_id = 1;
-- Update only non-NULL values
UPDATE customers
SET phone = '555-0000'
WHERE phone IS NOT NULL;
Using Subqueries in DML
INSERT with Subquery
-- Copy customers from one table to another
INSERT INTO premium_customers (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE total_orders > 10;
UPDATE with Subquery
-- Update customer status based on order count
UPDATE customers
SET status = 'VIP'
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
);
Transaction Safety
For important operations, use transactions to ensure data consistency.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- If everything looks good
COMMIT;
-- If there's an error
-- ROLLBACK;
Practical Examples
-- E-commerce scenario: Process an order
START TRANSACTION;
-- Insert new order
INSERT INTO orders (customer_id, order_date, status, total_amount)
VALUES (123, NOW(), 'processing', 299.99);
-- Get the order ID
SET @order_id = LAST_INSERT_ID();
-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
(@order_id, 1, 2, 99.99),
(@order_id, 2, 1, 99.99);
-- Update product inventory
UPDATE products SET stock_quantity = stock_quantity - 2 WHERE product_id = 1;
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 2;
COMMIT;
Practice Exercises
- Insert a new product with name 'Smartphone', price 699.99, and stock 25
- Update all products in category 2 to increase their price by 5%
- Delete all customers who haven't placed any orders
- Create a transaction that transfers $50 between two customer accounts
Next Steps
In the next lesson, we'll explore aggregate functions and GROUP BY clauses to analyze and summarize data effectively.