Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMJunior27 phút đọc

Database: ERD, khoá ngoại, JOIN và GROUP BY

Ôn 17 thẻ
Trong bài này

Cụm bài Database: 1. Bảng và SQL cơ bản · 2. ERD, JOIN, GROUP BY · 3. Chuẩn hoá · 4. Bài tập thư viện · 5. Index và hiệu năng truy vấn · 6. Transaction và locking

Bài 1 làm việc với từng bảng riêng lẻ, còn dữ liệu thật nằm ở nhiều bảng có quan hệ: khoá học có chương, học viên đăng ký nhiều khoá. Bài này đi từ thiết kế (ERD, quan hệ 1-1/1-n/n-n, khoá ngoại, ON DELETE) đến truy vấn (JOIN, GROUP BY/HAVING, thứ tự thực thi logic của SELECT), kèm bốn bài tập trên hệ thống khoá học online. Nên đọc bài 1 trước nếu chưa quen CREATE TABLE, khoá chính và NULL; dialect là MySQL 8 (InnoDB), chỗ PostgreSQL khác đáng kể có ghi chú.

1. Mô hình hoá: ERD

Ý chính: ERD là bản vẽ thực thể, thuộc tính và quan hệ, làm trước khi viết CREATE TABLE. Mỗi quan hệ trên ERD sau đó thành một khoá ngoại hoặc một bảng trung gian.

ERD (Entity-Relationship Diagram) có ba thành phần:

  • Thực thể (entity): đối tượng cần lưu: Student, Course, Teacher…
  • Thuộc tính (attribute): đặc điểm của thực thể: id, tên, ngày sinh…
  • Quan hệ (relationship): cách các thực thể gắn với nhau: 1-1, 1-n, n-n.

Ví dụ trên slide là hệ thống khoá học online, với các thực thể Teacher, Student, Course, Chapter, Lesson, Admin. Bài 1 ở phần Bài tập vẽ đầy đủ.

1.1. Ba loại quan hệ

Quan hệ Ví dụ Cách cài đặt
1-1 Một sinh viên có đúng một thẻ sinh viên FK ở một bên kèm UNIQUE
1-n Một khoá học có nhiều chương FK đặt ở bảng phía nhiều (chapter.course_id)
n-n Sinh viên học nhiều môn, môn có nhiều sinh viên Bảng trung gian chứa hai FK
erDiagram
  STUDENT ||--|| STUDENT_CARD : "has"
  COURSE  ||--o{ CHAPTER      : "contains"
  STUDENT ||--o{ STUDENT_SUBJECT : "enrolls"
  SUBJECT ||--o{ STUDENT_SUBJECT : "is taken in"

1.2. Relational database, khoá chính và khoá ngoại

  • Relational database: dữ liệu chia thành nhiều bảng, mỗi bảng là một thực thể, các bảng nối với nhau bằng khoá.
  • Khoá chính (PK): định danh duy nhất mỗi dòng trong bảng.
  • Khoá ngoại (FK): cột ở bảng này trỏ tới PK (hoặc cột UNIQUE) của bảng khác, là “điểm chung” để nối hai bảng. DBMS chặn FK trỏ tới dòng không tồn tại; đó là referential integrity (toàn vẹn tham chiếu).
CREATE TABLE student (
  id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- 1-1: UNIQUE trên student_id là thứ biến 1-n thành 1-1
CREATE TABLE student_card (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT NOT NULL UNIQUE,
  issued_on  DATE   NOT NULL,
  CONSTRAINT fk_card_student FOREIGN KEY (student_id) REFERENCES student(id)
);

-- 1-n: FK ở phía "nhiều"
CREATE TABLE book (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  borrower_id BIGINT NULL,
  CONSTRAINT fk_book_student FOREIGN KEY (borrower_id) REFERENCES student(id)
);

-- n-n: bảng trung gian, PK ghép để không đăng ký trùng
CREATE TABLE subject (
  id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
CREATE TABLE student_subject (
  student_id  BIGINT   NOT NULL,
  subject_id  BIGINT   NOT NULL,
  enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, subject_id),
  KEY idx_ss_subject (subject_id),
  CONSTRAINT fk_ss_student FOREIGN KEY (student_id) REFERENCES student(id),
  CONSTRAINT fk_ss_subject FOREIGN KEY (subject_id) REFERENCES subject(id)
);

Những chỗ slide chưa chuẩn, cần sửa khi tự làm:

  • Bảng trung gian trên slide dùng ID tự tăng làm PK nhưng không có UNIQUE(student_id, subject_id), nên một sinh viên đăng ký được cùng một môn hai lần. Dùng PK ghép như trên, hoặc giữ surrogate id (id tự sinh, không mang nghĩa nghiệp vụ) và thêm unique constraint.
  • Thứ tự cột trong PK ghép quan trọng: (student_id, subject_id) phục vụ tốt “môn của sinh viên X”. Tra ngược “sinh viên của môn Y” cần thêm index (subject_id) như idx_ss_subject. MySQL tự tạo index cho FK nếu chưa có, PostgreSQL thì không.
Lỗi gõ DDL và quan hệ Student → Address trên slide
  • Một số DDL trên slide có lỗi gõ (create table chaper, dấu phẩy thừa trước ), bảng Course đặt tên cột SUBJECT_ID, Chapter đặt BOOK_ID). Chạy y nguyên sẽ báo lỗi cú pháp.
  • Một biến thể slide cài 1-1 Student → Address bằng address_id trong bảng student, cũng không có UNIQUE, nên thực chất là n-1. Dữ liệu mẫu còn ghi quận Hà Đông thuộc HCM: lỗi nhỏ, nhưng dữ liệu mẫu sai dễ làm test “đúng” trên dữ liệu vô nghĩa.

1.3. ON DELETE và ON UPDATE

Slide không nhắc, nhưng phỏng vấn hay hỏi: khi xoá dòng cha thì dòng con ra sao?

Tuỳ chọn Hành vi
RESTRICT / NO ACTION (mặc định) Chặn xoá cha nếu còn con
CASCADE Xoá luôn con
SET NULL Đặt FK của con về NULL

1.4. Hai cách vẽ ERD: Chen và crow’s foot

Slide vẽ ERD theo ký hiệu Chen (chữ nhật, elip, hình thoi). Mermaid trong blog dùng crow’s foot (chân chim), kiểu mặc định của MySQL Workbench hay dbdiagram.io. Cùng mô hình, khác cách vẽ.

Bảng đối chiếu ký hiệu Chen và crow’s foot
Ý nghĩa Chen (slide) Crow’s foot (mermaid trong blog)
Thực thể Hình chữ nhật Hộp có tên bảng
Thuộc tính Hình elip nối vào thực thể; thuộc tính khoá được gạch chân Một dòng trong hộp, đánh dấu PK/FK
Quan hệ Hình thoi ghi động từ: study, teach, have, borrow Đường nối, động từ là nhãn trên đường
Bản số Ghi 1, N, M cạnh đường nối Ký hiệu ở đầu đường: || đúng một, |o không hoặc một, }| một hoặc nhiều, }o không hoặc nhiều

Ví dụ thư viện trên slide (sinh viên có một thẻ, mượn nhiều sách, học nhiều môn), vẽ lại kiểu Chen:

flowchart LR
  SUB[Subject] ---|N| STUDY{study}
  STUDY ---|N| STU[Student]
  STU ---|1| BORROW{borrow}
  BORROW ---|N| BOOK[Book]
  STU ---|1| HAVE{have}
  HAVE ---|1| CARD[Card]
  STU --- SID([student_id - khoá])
  BOOK --- BID([book_id - khoá])

Và theo crow’s foot, rõ hơn phần bắt buộc/tuỳ chọn: sách có thể chưa ai mượn (|o), sinh viên có thể chưa có thẻ (o|):

erDiagram
  SUBJECT }o--o{ STUDENT : study
  STUDENT |o--o{ BOOK : borrows
  STUDENT ||--o| CARD : has

2. JOIN: lấy dữ liệu từ nhiều bảng

Ý chính: JOIN ghép dòng của hai bảng theo điều kiện trong ON. Chọn loại JOIN theo câu hỏi “dòng không khớp có cần giữ lại không”.

flowchart LR
  subgraph INNER["INNER JOIN"]
    i1["Chỉ dòng khớp ở cả hai bảng"]
  end
  subgraph LEFT["LEFT JOIN"]
    l1["Mọi dòng bảng trái + dòng khớp bên phải (không khớp thì NULL)"]
  end
  subgraph RIGHT["RIGHT JOIN"]
    r1["Mọi dòng bảng phải + dòng khớp bên trái (không khớp thì NULL)"]
  end
-- INNER JOIN: sinh viên và sách họ đang mượn (chỉ ai có mượn)
SELECT s.name, b.title
FROM student s
INNER JOIN book b ON b.borrower_id = s.id;

-- LEFT JOIN: mọi sinh viên, ai không mượn thì title = NULL
SELECT s.name, b.title
FROM student s
LEFT JOIN book b ON b.borrower_id = s.id;

-- Mọi cuốn sách, sách chưa ai mượn thì name = NULL.
-- Có thể viết RIGHT JOIN, nhưng đảo bảng rồi dùng LEFT JOIN thì dễ đọc hơn.
SELECT b.title, s.name
FROM book b
LEFT JOIN student s ON s.id = b.borrower_id;

Kết quả với dữ liệu mẫu (An mượn 2 cuốn, Binh và Chi không mượn, cuốn SICP chưa ai mượn):

Câu Kết quả
INNER (An, Clean Code), (An, DDIA)
LEFT từ student (An, Clean Code), (An, DDIA), (Binh, NULL), (Chi, NULL)
LEFT từ book (Clean Code, An), (DDIA, An), (SICP, NULL)

Một bản slide định nghĩa RIGHT JOIN là “trả về các hàng từ bảng bên trái và các hàng khớp từ bảng phải”. Câu đó sai: RIGHT JOIN giữ mọi dòng bảng phải. Code ví dụ trên slide thì đúng.

Những điểm hay vấp:

  • FULL OUTER JOIN: PostgreSQL có, MySQL không. Trong MySQL, giả lập bằng LEFT JOIN ... UNION ... RIGHT JOIN.
  • CROSS JOIN (tích Descartes, mọi cặp dòng): N × M dòng. Quên điều kiện join là ra cái này.
  • Self join: bảng join với chính nó, ví dụ employee e JOIN employee m ON e.manager_id = m.id.

MySQL thực thi JOIN chủ yếu bằng nested loop join: với mỗi dòng bảng ngoài, tra bảng trong; có index trên cột join thì mỗi lần tra là một lần đi B+tree. Từ 8.0.18 MySQL có thêm hash join cho join không có index, PostgreSQL có thêm merge join. Đây là lý do cột FK gần như luôn cần index.

Ba thuật toán này chạy ra sao, cần index hay bộ nhớ thế nào và planner chọn cái nào khi nào, có plan thật trên PostgreSQL và mô phỏng bằng Java, ở bài JOIN chạy thế nào: nested loop, hash join, merge join.

3. GROUP BY và HAVING

Ý chính: GROUP BY gom các dòng cùng giá trị thành nhóm rồi tính hàm tổng hợp (COUNT, SUM, AVG, MAX, MIN) cho từng nhóm. WHERE lọc dòng trước khi gom, HAVING lọc nhóm theo kết quả tổng hợp.

-- Đếm số sách theo nơi xuất bản
SELECT publisher_city, COUNT(*) AS book_count
FROM book
GROUP BY publisher_city;

-- Môn học mà mọi bài thi đều trên 7 điểm, kèm số lượt thi
SELECT subject_name, COUNT(*) AS attempts
FROM exam_result
GROUP BY subject_name
HAVING MIN(score) > 7;

-- WHERE và HAVING cùng lúc: chỉ tính đơn đã thanh toán,
-- rồi chỉ giữ khách có tổng chi tiêu trên 10 triệu
SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id
HAVING SUM(total) > 10000000;

Mẹo: HAVING MIN(score) > 7 nghĩa là “mọi dòng trong nhóm đều thoả”, còn HAVING MAX(score) > 7 là “ít nhất một dòng thoả”.

Quy tắc vàng của GROUP BY: mỗi cột trong SELECT phải nằm trong GROUP BY, trong hàm tổng hợp, hoặc phụ thuộc hàm vào cột đã group (giá trị được xác định duy nhất bởi cột đó). Ví dụ group theo PK teacher.id thì được select teacher.name. MySQL 8 bật ONLY_FULL_GROUP_BY mặc định để ép quy tắc này; PostgreSQL cũng chấp nhận trường hợp phụ thuộc vào PK.

4. Thứ tự thực thi logic của một câu SELECT

Ý chính: ta viết SELECT trước, nhưng DBMS xử lý logic FROM trước và SELECT gần cuối. Thứ tự này quyết định mệnh đề nào thấy được alias và hàm tổng hợp.

flowchart LR
  A["1. FROM + JOIN"] --> B["2. WHERE"] --> C["3. GROUP BY"] --> D["4. HAVING"] --> E["5. SELECT<br/>(tính biểu thức, alias, window function)"] --> F["6. DISTINCT"] --> G["7. ORDER BY"] --> H["8. LIMIT / OFFSET"]
  • WHERE không dùng được alias của SELECT và không dùng được hàm tổng hợp, vì lúc đó SELECT chưa chạy và chưa có nhóm. Lọc theo tổng hợp thì dùng HAVING.
  • ORDER BY dùng được alias vì chạy sau SELECT.
  • LIMIT chạy cuối: LIMIT 10 không làm phần group/sort bớt nặng nếu thiếu index phù hợp.
  • Dialect: MySQL cho dùng alias trong GROUP BY và HAVING. PostgreSQL cho trong GROUP BY/ORDER BY nhưng không cho trong HAVING/WHERE. Lặp lại biểu thức trong HAVING để chạy được trên cả hai.

Đây là thứ tự logic. Optimizer được đổi thứ tự vật lý (đẩy điều kiện xuống sớm, dùng index để khỏi sort) miễn kết quả không đổi.

Bài tập

Các bài dưới đây diễn giải lại đề bài từ khoá học, lời giải do mình tự viết và đã chạy thử trên MySQL 8.4.

Bài 1: ERD hệ thống khoá học online

Đề: thiết kế database cho hệ thống khoá học online: học viên đăng ký khoá học, đánh giá và chấm sao; quản lý giáo viên và khoá học. Làm theo bốn bước: xác định thực thể, thêm thuộc tính, xác định quan hệ, tạo bảng.

Bước 1, thực thể: Teacher, Learner (học viên), Course, Chapter, Lesson, Admin. Thêm hai thực thể “sinh ra từ quan hệ”: Enrollment (học viên đăng ký khoá, kèm rating/review) và LessonProgress (tiến độ từng bài).

Bước 2 và 3, thuộc tính và quan hệ:

erDiagram
  TEACHER ||--o{ COURSE : teaches
  COURSE  ||--o{ CHAPTER : has
  CHAPTER ||--o{ LESSON : has
  LEARNER ||--o{ ENROLLMENT : makes
  COURSE  ||--o{ ENROLLMENT : receives
  LEARNER ||--o{ LESSON_PROGRESS : tracks
  LESSON  ||--o{ LESSON_PROGRESS : "is tracked in"
  TEACHER {
    int id PK
    string name
  }
  COURSE {
    int id PK
    string title
    int teacher_id FK
  }
  CHAPTER {
    int id PK
    int course_id FK
    string title
    int position
  }
  LESSON {
    int id PK
    int chapter_id FK
    string title
    int position
  }
  LEARNER {
    int id PK
    string name
    string status
  }
  ENROLLMENT {
    int learner_id PK
    int course_id PK
    int rating
    datetime enrolled_at
  }
  LESSON_PROGRESS {
    int learner_id PK
    int lesson_id PK
    string status
    datetime updated_at
  }

Admin là vai trò quản trị, không có quan hệ dữ liệu với khoá học, nên để ngoài sơ đồ. Thực tế thường gộp Admin/Teacher/Learner vào một bảng user + bảng role.

Teacher và Course: 1-n hay n-n? Sơ đồ bước 3 trên slide vẽ Teacher n-n Course, nhưng các bài luyện bên dưới và thiết kế E-Learning ở phần ORM dùng course.teacher_id (1-n). Mình chọn 1-n vì quy tắc “mỗi khoá có một giảng viên phụ trách” là phổ biến.

Lời giải Bài 1, bước 4: DDL đầy đủ và các quyết định thiết kế
CREATE TABLE teacher (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE learner (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  name   VARCHAR(100) NOT NULL,
  status VARCHAR(10)  NOT NULL DEFAULT 'ACTIVE'
);

CREATE TABLE course (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  teacher_id INT NULL,                         -- khoá học có thể chưa được phân công
  FOREIGN KEY (teacher_id) REFERENCES teacher(id)
);

CREATE TABLE enrollment (
  learner_id  INT      NOT NULL,
  course_id   INT      NOT NULL,
  rating      TINYINT  NULL CHECK (rating BETWEEN 1 AND 5),
  enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (learner_id, course_id),
  KEY idx_enroll_course (course_id),
  FOREIGN KEY (learner_id) REFERENCES learner(id),
  FOREIGN KEY (course_id)  REFERENCES course(id)
);

CREATE TABLE chapter (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  course_id INT NOT NULL,
  title     VARCHAR(200) NOT NULL,
  position  INT NOT NULL,
  FOREIGN KEY (course_id) REFERENCES course(id)
);

CREATE TABLE lesson (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  chapter_id INT NOT NULL,
  title      VARCHAR(200) NOT NULL,
  position   INT NOT NULL,
  FOREIGN KEY (chapter_id) REFERENCES chapter(id)
);

CREATE TABLE lesson_progress (
  learner_id INT NOT NULL,
  lesson_id  INT NOT NULL,
  status     ENUM('IN_PROGRESS', 'COMPLETED') NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (learner_id, lesson_id),
  KEY idx_progress_lesson (lesson_id),
  FOREIGN KEY (learner_id) REFERENCES learner(id),
  FOREIGN KEY (lesson_id)  REFERENCES lesson(id)
);

Quyết định thiết kế đáng nói:

  • “Chưa bắt đầu” không lưu thành dòng. Không có dòng trong lesson_progress nghĩa là chưa học, nên không phải insert hàng nghìn dòng mỗi khi học viên đăng ký một khoá.
  • Cột position để sắp thứ tự chương/bài. Đừng dựa vào id vì giáo viên sẽ muốn đổi thứ tự.
  • Slide có hai câu truy vấn mẫu cho bước này. “Danh sách tên học viên đang hoạt động”: SELECT name FROM learner WHERE status = 'ACTIVE';. “Khoá học của học viên id = 1”: SELECT c.* FROM enrollment e JOIN course c ON c.id = e.course_id WHERE e.learner_id = 1;.

Bài 2: Ba câu truy vấn trên mô hình sinh viên, thẻ, môn học

Dùng các bảng student, student_card, subject, student_subject ở mục 1.2.

  • (a) Thông tin sinh viên và số môn mỗi người đã học.
  • (b) Sinh viên và thông tin thẻ tương ứng.
  • (c) Môn học và số sinh viên đã học môn đó.
Lời giải Bài 2

(a)

SELECT s.id, s.name, COUNT(ss.subject_id) AS subject_count
FROM student s
LEFT JOIN student_subject ss ON ss.student_id = s.id
GROUP BY s.id, s.name;

Phải dùng LEFT JOIN và COUNT(ss.subject_id) (không phải COUNT(*)), để sinh viên chưa học môn nào vẫn xuất hiện với số 0. COUNT(*) đếm cả dòng NULL do LEFT JOIN tạo ra và trả về 1, sai.

(b)

SELECT s.id, s.name, c.id AS card_id, c.issued_on
FROM student s
LEFT JOIN student_card c ON c.student_id = s.id;

Nhờ UNIQUE(student_id), mỗi sinh viên ra tối đa một dòng. Dùng INNER JOIN nếu chỉ muốn sinh viên đã có thẻ.

(c)

SELECT sub.id, sub.name, COUNT(ss.student_id) AS student_count
FROM subject sub
LEFT JOIN student_subject ss ON ss.subject_id = sub.id
GROUP BY sub.id, sub.name
ORDER BY student_count DESC;

Index idx_ss_subject (subject_id) giúp câu này không phải quét toàn bảng trung gian.

Bài 3: Chín bài luyện GROUP BY/HAVING trên hệ thống khoá học

Dùng schema ở Bài 1. Dữ liệu mẫu: 4 giáo viên (Quang chưa dạy khoá nào), 6 khoá (Kafka chưa có giáo viên), 3 học viên.

  1. Số khoá học mỗi giáo viên được phân công.
  2. Như trên nhưng có id, tên giáo viên; chỉ lấy giáo viên có từ 1 khoá trở lên.
  3. Như 3.2 nhưng phân trang, mỗi trang tối đa 2 dòng, lấy trang 2.
  4. Như 3.2 nhưng gồm cả giáo viên chưa dạy khoá nào.
  5. Học viên và mọi khoá học họ đã đăng ký.
  6. Điểm đánh giá trung bình mỗi khoá, chỉ lấy khoá có trung bình ≥ 4.0.
  7. Học viên, khoá đã đăng ký và giáo viên của khoá đó.
  8. Học viên, khoá đã đăng ký, các chương/bài của khoá và trạng thái học của từng bài.
  9. Mỗi bài học: thông tin khoá/chương/bài, số người đã hoàn thành, đang học, chưa bắt đầu. (Slide không có lời giải.)
Lời giải Bài 3.1–3.5: đếm, phân trang, LEFT JOIN

3.1.

SELECT teacher_id, COUNT(*) AS course_count
FROM course
WHERE teacher_id IS NOT NULL
GROUP BY teacher_id;

Thiếu WHERE teacher_id IS NOT NULL thì sẽ có một nhóm NULL chứa các khoá chưa phân công.

3.2.

SELECT t.id, t.name, COUNT(c.id) AS course_count
FROM teacher t
JOIN course c ON c.teacher_id = t.id
GROUP BY t.id, t.name;

INNER JOIN đã loại giáo viên không có khoá nào, nên điều kiện “≥ 1” thoả sẵn. Nếu ngưỡng là 2 thì thêm HAVING COUNT(c.id) >= 2.

3.3.

SELECT t.id, t.name, COUNT(c.id) AS course_count
FROM teacher t
JOIN course c ON c.teacher_id = t.id
GROUP BY t.id, t.name
ORDER BY t.id
LIMIT 2 OFFSET 2;            -- OFFSET = (page - 1) * size

Lời giải gốc trên slide thiếu ORDER BY. Không có nó, “trang 2” không ổn định giữa các lần gọi. Với bảng lớn, OFFSET còn chậm dần theo số trang. Cách thay thế (keyset pagination) ở bài index và hiệu năng truy vấn.

3.4.

SELECT t.id, t.name, COUNT(c.id) AS course_count
FROM teacher t
LEFT JOIN course c ON c.teacher_id = t.id
GROUP BY t.id, t.name;

Quang xuất hiện với course_count = 0. Đảo bảng gốc thành teacher rồi LEFT JOIN là mấu chốt.

3.5.

SELECT l.id, l.name, c.title
FROM learner l
JOIN enrollment e ON e.learner_id = l.id
JOIN course c     ON c.id = e.course_id
ORDER BY l.id, c.id;
Lời giải Bài 3.6–3.9: rating, JOIN nhiều tầng, conditional aggregation

3.6.

SELECT c.id, c.title,
       ROUND(AVG(e.rating), 2) AS avg_rating,
       COUNT(e.rating)         AS rating_count
FROM course c
JOIN enrollment e ON e.course_id = c.id
GROUP BY c.id, c.title
HAVING AVG(e.rating) >= 4.0;

Mình thêm rating_count: trung bình 5.0 từ 1 lượt đánh giá không đáng tin bằng 4.6 từ 2.000 lượt. Hệ thống thật thường cần ngưỡng tối thiểu (HAVING COUNT(e.rating) >= 10) hoặc trung bình có trọng số (Bayesian average). Slide gõ thiếu chữ S ở SELECT trong lời giải.

3.7.

SELECT l.name AS learner, c.title AS course, t.name AS teacher
FROM learner l
JOIN enrollment e ON e.learner_id = l.id
JOIN course c     ON c.id = e.course_id
LEFT JOIN teacher t ON t.id = c.teacher_id
ORDER BY l.id, c.id;

LEFT JOIN teacher để khoá chưa có giáo viên không làm mất dòng đăng ký.

3.8.

SELECT l.name  AS learner,
       c.title AS course,
       ch.title AS chapter,
       ls.title AS lesson,
       COALESCE(p.status, 'NOT_STARTED') AS status
FROM enrollment e
JOIN learner l  ON l.id = e.learner_id
JOIN course c   ON c.id = e.course_id
JOIN chapter ch ON ch.course_id = c.id
JOIN lesson ls  ON ls.chapter_id = ch.id
LEFT JOIN lesson_progress p
       ON p.lesson_id = ls.id
      AND p.learner_id = e.learner_id
ORDER BY l.id, c.id, ch.position, ls.position;

Lời giải trên slide có hai vấn đề đáng học:

  1. Sau LEFT JOIN bảng tiến độ lại INNER JOIN bảng học viên theo cột của bảng tiến độ, nên mọi bài “chưa học” (cột NULL) bị loại, LEFT JOIN mất tác dụng.
  2. Có GROUP BY mà không có hàm tổng hợp nào, thực chất chỉ là DISTINCT trá hình.

Cách đúng: bắt đầu từ enrollment (ai học khoá nào) để sinh ra mọi cặp (học viên, bài), rồi LEFT JOIN tiến độ với cả hai điều kiện lesson_id và learner_id trong ON.

3.9.

SELECT c.title  AS course,
       ch.title AS chapter,
       ls.title AS lesson,
       COUNT(e.learner_id)                                AS enrolled,
       COUNT(CASE WHEN p.status = 'COMPLETED'   THEN 1 END) AS completed,
       COUNT(CASE WHEN p.status = 'IN_PROGRESS' THEN 1 END) AS in_progress,
       COUNT(e.learner_id) - COUNT(p.learner_id)          AS not_started
FROM course c
JOIN chapter ch ON ch.course_id = c.id
JOIN lesson ls  ON ls.chapter_id = ch.id
LEFT JOIN enrollment e      ON e.course_id = c.id
LEFT JOIN lesson_progress p ON p.lesson_id = ls.id AND p.learner_id = e.learner_id
GROUP BY c.id, c.title, ch.id, ch.title, ch.position, ls.id, ls.title, ls.position
ORDER BY c.id, ch.position, ls.position;

Kỹ thuật ở đây là conditional aggregation: COUNT(CASE WHEN ... THEN 1 END) đếm có điều kiện trong một lần quét, thay vì ba subquery. “Chưa bắt đầu” = số người đăng ký trừ số người có dòng tiến độ. Kết quả mẫu:

course chapter lesson enrolled completed in_progress not_started
Java Core Basics Variables 2 2 0 0
Java Core Basics Loops 2 0 1 1
Java Core OOP Classes 2 0 0 2
SQL Select WHERE 2 1 0 1

MySQL cho viết gọn SUM(p.status = 'COMPLETED'), nhưng hàm này trả NULL (không phải 0) khi mọi dòng là NULL, và không chạy trên PostgreSQL (Postgres có cú pháp riêng COUNT(*) FILTER (WHERE ...)). Dạng COUNT(CASE ...) chạy được ở cả hai.

Ghi nhớ

  • Giải thích được vì sao 1-1 cần UNIQUE trên FK, và vì sao bảng trung gian n-n cần PK ghép cộng index cho FK thứ hai.
  • Biết khi nào dùng ON DELETE CASCADE và khi nào để RESTRICT.
  • Viết được LEFT JOIN + COUNT(cột bảng phải) để giữ cả dòng có số 0, và nói được vì sao điều kiện bảng phải thuộc về ON.
  • Kể được thứ tự logic của SELECT và suy ra alias dùng được ở mệnh đề nào.
  • Nhận ra fan-out khi join hai bảng con 1-n, và sửa bằng cách gom từng bảng con trước.

Câu hỏi phỏng vấn

Junior

WHERE khác HAVING thế nào?

WHERE lọc từng dòng trước khi gom nhóm; HAVING lọc các nhóm sau GROUP BY.

WHERE không dùng được hàm tổng hợp, HAVING dùng được COUNT, SUM… Điều kiện nào lọc được bằng WHERE thì nên đặt ở WHERE, vì giảm số dòng phải gom.

INNER JOIN và LEFT JOIN khác nhau ra sao?

INNER chỉ giữ dòng khớp ở cả hai bảng; LEFT giữ mọi dòng bảng trái, cột bảng phải là NULL nếu không khớp.

Ví dụ “liệt kê mọi khách hàng kèm số đơn, kể cả khách chưa mua” phải dùng LEFT JOIN + COUNT(o.id).

Cài đặt quan hệ nhiều-nhiều như thế nào?

Bằng một bảng trung gian chứa hai FK trỏ về hai bảng, có PK ghép (hoặc UNIQUE) trên cặp FK.

PK ghép chặn đăng ký trùng; thêm index cho FK thứ hai để tra ngược. Bảng trung gian thường mang thêm thuộc tính của chính quan hệ như ngày đăng ký, rating.

Đọc ký hiệu TEACHER ||--o{ COURSE trong ERD crow’s foot thế nào? Nó khác ký hiệu Chen ra sao?

Mỗi khoá học có đúng một giảng viên, một giảng viên dạy từ 0 tới nhiều khoá; thành bảng thì teacher_id là FK ở course.

Crow’s foot đọc ký hiệu ở mỗi đầu đường: phía TEACHER là || (đúng một), phía COURSE là o{ (không hoặc nhiều). Chen vẽ cùng ý đó bằng hai hình chữ nhật nối qua hình thoi “teach”, ghi 1 và N trên đường, thuộc tính là các hình elip. Crow’s foot gần với bảng thật hơn nên hay dùng khi thiết kế schema.

Mid

Vì sao COUNT(*) và COUNT(column) cho kết quả khác nhau sau LEFT JOIN?

COUNT(*) đếm mọi dòng, kể cả dòng LEFT JOIN điền NULL; COUNT(col) bỏ qua NULL.

Đếm “số đơn mỗi khách” bằng COUNT(*) sẽ ra 1 cho khách không có đơn nào. Phải đếm một cột của bảng bên phải, thường là PK của nó.

Thứ tự thực thi logic của SELECT? Vì sao không dùng alias trong WHERE được?

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT; alias sinh ra ở bước SELECT, sau WHERE.

Nên WHERE chưa “thấy” alias, còn ORDER BY chạy sau nên dùng được. MySQL nới lỏng cho phép alias trong HAVING, PostgreSQL thì không.

LEFT JOIN rồi đặt điều kiện bảng phải trong WHERE thì sao?

Kết quả giống hệt INNER JOIN: các dòng không khớp bị loại.

Dòng không khớp có cột bên phải là NULL, điều kiện như WHERE r.status = 'X' cho UNKNOWN nên loại chúng. Muốn giữ dòng bảng trái thì đặt điều kiện trong ON.

Khi nào dùng ON DELETE CASCADE?

Khi bảng con là một phần không tách rời của bảng cha và không có giá trị nghiệp vụ riêng, ví dụ dòng chi tiết của giỏ hàng tạm.

Với dữ liệu tài chính, lịch sử, audit thì tránh: một lệnh xoá có thể lan rộng ngoài ý muốn. Nên dùng RESTRICT và xoá/lưu trữ có chủ đích.

Senior

JOIN gây fan-out làm SUM sai, xử lý thế nào?

Tổng hợp từng bảng con theo khoá cha trước (subquery/CTE), rồi mới join các kết quả đã gom.

Join một bảng cha với hai bảng con 1-n cùng lúc thì số dòng là tích hai bên, nên tổng bị nhân. Sau khi gom, mỗi bảng con còn 1 dòng/cha. Cách khác là subquery tương quan trong SELECT. Kiểm tra bằng cách so COUNT(*) trước và sau join.

Database thực thi JOIN thế nào và tại sao cột FK cần index?

Chủ yếu bằng nested loop join: với mỗi dòng bảng ngoài, tra bảng trong. Có index thì mỗi lần tra là O(log n), không có thì quét toàn bảng, thành O(n × m).

MySQL 8.0.18+ có hash join cho trường hợp không có index; PostgreSQL có thêm merge join. Index cột FK còn giúp xoá/sửa dòng cha nhanh, vì DB phải kiểm tra bảng con. Postgres không tự tạo index cho FK nên đây là lỗi hiệu năng hay gặp.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
entity /ˈentɪti/ thực thể “I started by listing the core entities: user, course, and enrollment.”
attribute /ˈætrɪbjuːt/ thuộc tính “Each attribute becomes a column with a concrete data type.”
relationship /rɪˈleɪʃnʃɪp/ quan hệ “It’s a many-to-many relationship, so I added a junction table.”
foreign key /ˈfɒrən kiː/ khoá ngoại “The foreign key lives on the ‘many’ side of a one-to-many relationship.”
junction table /ˈdʒʌŋkʃn ˈteɪbl/ bảng trung gian “The junction table has a composite primary key on both foreign keys.”
referential integrity /ˌrefəˈrenʃl ɪnˈteɡrəti/ toàn vẹn tham chiếu “Referential integrity guarantees that every order points to a real customer.”
aggregate function /ˈæɡrɪɡət ˈfʌŋkʃn/ hàm tổng hợp “Aggregate functions like COUNT and AVG ignore NULL values.”
clause /klɔːz/ mệnh đề “The HAVING clause filters groups, while WHERE filters rows.”
cardinality /ˌkɑːdɪˈnæləti/ lực lượng quan hệ / số giá trị khác nhau “The cardinality between student and card is one-to-one.”
fan-out /ˈfæn aʊt/ nhân dòng khi join “Joining two child tables caused a fan-out, so the totals were doubled.”
anti-join /ˈænti dʒɔɪn/ lấy dòng bên trái không có dòng khớp bên phải “I’d write it as an anti-join with NOT EXISTS instead of NOT IN.”
conditional aggregation /kənˈdɪʃənl ˌæɡrɪˈɡeɪʃn/ tổng hợp có điều kiện trong một lần quét “With conditional aggregation I get all three counts in a single pass.”

Ghi chép dựa trên khoá Fundamentals of Backend (Ronin Engineer), viết lại theo cách hiểu của mình.