MySQL Keywords

Keyword Categories

SELECT, FROM, WHERE

INSERT, UPDATE, DELETE

CREATE, ALTER, DROP

GRANT, REVOKE, DENY

🔹 SELECT Keywords

SELECT keywords retrieve data from tables. Use SELECT to specify columns, FROM to identify tables, WHERE to filter rows, ORDER BY to sort results, and LIMIT to restrict the number of returned records. These form the foundation of data querying in MySQL.

-- Basic SELECT
SELECT * FROM users;

-- SELECT specific columns
SELECT name, email FROM users;

-- SELECT with WHERE clause
SELECT * FROM users WHERE age > 18;

-- SELECT with ORDER BY
SELECT name, age FROM users ORDER BY age DESC;

-- SELECT with LIMIT
SELECT * FROM users LIMIT 10;

-- SELECT DISTINCT (unique values)
SELECT DISTINCT country FROM users;

-- SELECT with multiple conditions
SELECT * FROM products 
WHERE price > 100 AND category = 'electronics'
ORDER BY price ASC
LIMIT 5;

🔹 JOIN Keywords

JOIN keywords combine rows from multiple tables based on related columns. INNER JOIN returns matching rows, LEFT JOIN includes all left table rows, RIGHT JOIN includes all right table rows, and CROSS JOIN creates all possible combinations. Joins are essential for relational database queries.

-- INNER JOIN (matching rows only)
SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN (all left table rows)
SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN (all right table rows)
SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- CROSS JOIN (all combinations)
SELECT products.name, colors.color
FROM products
CROSS JOIN colors;

-- Multiple JOINs
SELECT u.name, o.order_id, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id;

🔹 INSERT, UPDATE, DELETE Keywords

These keywords modify data in tables. INSERT adds new records, UPDATE changes existing records, and DELETE removes records. Always use WHERE clauses with UPDATE and DELETE to avoid affecting all rows. These are the core data manipulation commands in MySQL.

-- INSERT single row
INSERT INTO users (name, email, age)
VALUES ('John Doe', '[email protected]', 25);

-- INSERT multiple rows
INSERT INTO users (name, email, age)
VALUES 
    ('Alice', '[email protected]', 30),
    ('Bob', '[email protected]', 28);

-- UPDATE records
UPDATE users
SET age = 26, email = '[email protected]'
WHERE id = 1;

-- DELETE records
DELETE FROM users WHERE id = 5;

-- DELETE with condition
DELETE FROM orders WHERE order_date < '2023-01-01';

-- UPDATE with calculation
UPDATE products
SET price = price * 1.1
WHERE category = 'electronics';

🔹 CREATE, ALTER, DROP Keywords

These Data Definition Language keywords manage database structure. CREATE builds new tables and databases, ALTER modifies existing structures by adding or changing columns, and DROP permanently removes tables or databases. Use these carefully as they affect your database schema and can cause data loss.

-- CREATE DATABASE
CREATE DATABASE company_db;

-- CREATE TABLE
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2),
    hire_date DATE
);

-- ALTER TABLE (add column)
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- ALTER TABLE (modify column)
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2);

-- ALTER TABLE (drop column)
ALTER TABLE employees
DROP COLUMN phone;

-- DROP TABLE
DROP TABLE IF EXISTS old_table;

-- DROP DATABASE
DROP DATABASE IF EXISTS test_db;

🔹 Aggregate and GROUP BY Keywords

Aggregate keywords perform calculations on multiple rows. COUNT tallies rows, SUM adds values, AVG calculates averages, MAX and MIN find extremes. GROUP BY organizes results into groups, and HAVING filters grouped results. These enable powerful data analysis and reporting capabilities.

-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;

-- SUM
SELECT SUM(salary) FROM employees;

-- AVG
SELECT AVG(age) FROM users;

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

-- GROUP BY
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country;

-- GROUP BY with HAVING
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

-- Multiple aggregates
SELECT category, 
       COUNT(*) as total_products,
       AVG(price) as avg_price,
       MAX(price) as max_price
FROM products
GROUP BY category;

🔹 Constraint Keywords

Constraint keywords enforce data integrity rules. PRIMARY KEY uniquely identifies rows, FOREIGN KEY links tables, UNIQUE prevents duplicates, NOT NULL requires values, DEFAULT sets automatic values, and CHECK validates data. Constraints maintain database consistency and prevent invalid data entry.

-- PRIMARY KEY
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- FOREIGN KEY
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- UNIQUE constraint
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

-- NOT NULL constraint
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- DEFAULT constraint
CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- CHECK constraint
CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18),
    salary DECIMAL(10,2) CHECK (salary > 0)
);

🔹 Transaction Keywords

Transaction keywords ensure data consistency across multiple operations. START TRANSACTION begins a transaction block, COMMIT saves all changes permanently, and ROLLBACK undoes changes if errors occur. Transactions are critical for maintaining data integrity in complex operations like financial transfers or multi-step updates.

-- Start a transaction
START TRANSACTION;

-- Perform operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit if successful
COMMIT;

-- Or rollback if error
ROLLBACK;

-- Transaction with error handling
START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 250.00);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;

-- If everything is okay
COMMIT;

-- If something went wrong
-- ROLLBACK;

-- Savepoint example
START TRANSACTION;
INSERT INTO logs (message) VALUES ('Step 1');
SAVEPOINT step1;
INSERT INTO logs (message) VALUES ('Step 2');
ROLLBACK TO step1;  -- Undo only step 2
COMMIT;