Auto-Increment Configuration

The default behavior for auto-increment columns starts at 1 and increments by 1. However, specific scenarios may require customizing the starting point or the increment step.

Setting the Initial Value

ALTER TABLE records AUTO_INCREMENT = 100;

Adjusting the Increment Step

• Session Scope: Affects only the current connection. Other connections remain unaffected.

  SET SESSION auto_increment_increment = 5;
  

• Global Scope: Applies to all new connections thereafter. Use with caution in production.

  SET GLOBAL auto_increment_increment = 5;
  

Indexing Strategies

Indexes enforce uniqueness constraints and significantly improve lookup performance. A unique index ensures that all values in a column are distinct (allowing multiple NULLs).

Creating a Unique Index

CREATE UNIQUE INDEX idx_unique_email ON accounts(email);

Creating a Composite Unique Index Ensures that the combination of multiple columns is unique.

CREATE UNIQUE INDEX idx_composite ON orders(customer_id, product_id);

Dropping an Index

DROP INDEX idx_unique_email ON accounts;

Complex Foreign Key Relationships

One-to-One Relationship In scenarios like linking user profiles to login credentials, the foreign key column must have a unique constraint to ensure the relationship is one-to-one.

CREATE TABLE members (
    member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100),
    registration_date DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE credentials (
    cred_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    pass_hash VARCHAR(255) NOT NULL,
    member_ref INT NOT NULL,
    UNIQUE KEY uniq_member (member_ref),
    CONSTRAINT fk_member_cred FOREIGN KEY (member_ref) REFERENCES members(member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Many-to-Many Relationship When entities like students and courses intersect, a junction table is required to link them.

CREATE TABLE students (
    stu_id INT AUTO_INCREMENT PRIMARY KEY,
    stu_name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE courses (
    crs_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE enrollment (
    rec_id INT AUTO_INCREMENT PRIMARY KEY,
    student_ref INT NOT NULL,
    course_ref INT NOT NULL,
    UNIQUE KEY uniq_enroll (student_ref, course_ref),
    CONSTRAINT fk_student FOREIGN KEY (student_ref) REFERENCES students(stu_id),
    CONSTRAINT fk_course FOREIGN KEY (course_ref) REFERENCES courses(crs_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data Manipulation (CRUD) Details

Insert Operations

• Inserting multiple rows:

  INSERT INTO employees (name, department) VALUES ('Alice', 'HR'), ('Bob', 'IT');
  

• Inserting data from a query:

  INSERT INTO archive_log (id, username) SELECT id, name FROM active_users WHERE status = 'inactive';
  

Update and Delete Operations

• Conditional Update:

  UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';
  

• Conditional Delete:

  DELETE FROM logs WHERE created_at < '2023-01-01';
  

Query Operations

• Basic Selection and Aliasing:

  SELECT id, username AS user_login FROM accounts WHERE id > 50;
  

• Filtering with IN and BETWEEN:

  SELECT * FROM items WHERE price BETWEEN 10 AND 50;
  SELECT * FROM items WHERE sku IN ('A100', 'B200', 'C300');
  

• Pattern Matching:

  SELECT * FROM customers WHERE phone LIKE '555%';
  

• Pagination:

  SELECT * FROM large_table LIMIT 10 OFFSET 20; -- Skips 20 rows, returns next 10
  

• Sorting:

  SELECT * FROM inventory ORDER BY quantity ASC, item_name DESC;
  

Agggregation and Grouping

Use GROUP BY with aggregate functions like COUNT, SUM, AVG, MAX, and MIN. Use HAVING to filter grouped results.

SELECT department, COUNT(*) as employee_count, AVG(salary) as avg_salary
FROM staff
GROUP BY department
HAVING employee_count > 5;

Joins

• Inner Join: Returns matching records only.

  SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
  

• Left Join: Returns all records from the left table, and matched records from the right.

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

• Right Join: Returns all records from the right table, and matched records from the left.

  SELECT * FROM orders RIGHT JOIN shippers ON orders.shipper_id = shippers.id;