1. Basic SQL Statements

Query (SELECT)

Retrieve data using SELECT, with support for wildcrads (*), column names, arithmetic operations, or aggregate functions. Use AS for aliases (optional):

SELECT name, (math + english)/2 AS avg_score 
FROM students;

Insert (INSERT)

Add new records (single/multiple rows) or import from another table:

-- Single/multiple rows
INSERT INTO users (username, role) 
VALUES ('alice', 'admin'), ('bob', 'user');

-- From another table
INSERT INTO new_users (username, pwd) 
SELECT username, pwd FROM old_users;

Delete (DELETE)

Remove records (with a cnodition) or truncate a table (rebuilds structure):

DELETE FROM users WHERE id = 1;
TRUNCATE TABLE users;

Udpate (UPDATE)

Modify existing data:

UPDATE users SET role = 'editor' WHERE id = 2;

2. Filter Conditions & Execution Order

The SELECT syntax order: SELECT [DISTINCT] ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

Execution order (internal processing): FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT → DISTINCT.

• WHERE: Filters rows (supports =, >, IN, BETWEEN, LIKE). Example: LIKE 'a%' (starts with 'a'), LIKE '_b' (single char before 'b').
• GROUP BY: Aggregate data (e.g., SUM(), AVG(), GROUP_CONCAT()). Requires ONLY_FULL_GROUP_BY in MySQL 5.7+ for strict mode.
• ORDER BY: Sort (ASC/DESC). Multi-column: ORDER BY salary DESC, id ASC.
• LIMIT: Control output: LIMIT 5 (first 5 rows) or LIMIT 2, 3 (rows 3–5).

3. Multi-Table Joins

Cartesian Product (Cross Join)

Combine all rows from two tables (use WHERE to filter):

SELECT * FROM employees, departments 
WHERE employees.dept_id = departments.id;

Inner Join

Retrieve matching rows from both tables:

SELECT * FROM departments 
JOIN employees ON employees.dept_id = departments.id;

Outer Joins

• Left Join: Include all left table rows (right table may have NULL):

  SELECT * FROM departments 
  LEFT JOIN employees ON employees.dept_id = departments.id;
  

• Full Join (MySQL Workaround): Use UNION of left and right joins:

  SELECT * FROM departments LEFT JOIN employees ... 
  UNION 
  SELECT * FROM departments RIGHT JOIN employees ...;
  

4. Subqueries

A query nested in another (e.g., WHERE clause):

SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'Asia');

Use EXISTS to check for results:

SELECT * FROM products 
WHERE EXISTS (SELECT * FROM orders WHERE orders.product_id = products.id);

5. User & Privilege Management

• Create User:

  CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
  

• Grant Privileges:

  GRANT ALL PRIVILEGES ON db_name.* TO 'new_user'@'%' WITH GRANT OPTION;
  

• Revoke/Delete:

  REVOKE SELECT ON db_name.* FROM 'user'@'host';
  DROP USER 'user'@'host';
  

• Refresh Privileges:

  FLUSH PRIVILEGES;
  

6. pymysql in Python

Example with parameterized queries (prevents SQL injection) and error handling:

import pymysql

# Database configuration
DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '',
    'database': 'db_example'
}

try:
    # Establish connection
    conn = pymysql.connect(**DB_CONFIG)
    print("Connection successful!")
    
    # Create cursor (DictCursor for dictionary results)
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # Execute a query
    query = "SELECT * FROM employees"
    row_count = cursor.execute(query)
    print(f"Rows returned: {row_count}")
    
    # Fetch results
    print(cursor.fetchall())  # All rows
    cursor.scroll(0, 'absolute')  # Reset cursor
    print(cursor.fetchone())     # First row
    
    # Parameterized insert (safe from injection)
    insert_query = "INSERT INTO users (name, role) VALUES (%s, %s)"
    cursor.executemany(insert_query, [('Alice', 'admin'), ('Bob', 'user')])
    conn.commit()  # Commit changes
    
except pymysql.Error as e:
    print(f"Database error: {e}")
finally:
    # Close resources
    cursor.close()
    conn.close()
    print("Connection closed.")

7. SQL Injection Prevention

Vulnerable Code (direct string formatting allows injection):

user_input = "admin' OR '1'='1"
risky_sql = f"SELECT * FROM users WHERE username = '{user_input}'"

Safe Code (parameterized queries):

safe_sql = "SELECT * FROM users WHERE username = %s"
cursor.execute(safe_sql, (user_input,))  # Input is sanitized