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
IDtự 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ộtSUBJECT_ID, Chapter đặtBOOK_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_idtrong 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ằngLEFT 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"]
WHEREkhông dùng được alias củaSELECTvà 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ùngHAVING.ORDER BYdùng được alias vì chạy sauSELECT.LIMITchạy cuối:LIMIT 10khô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 BYvàHAVING. PostgreSQL cho trongGROUP BY/ORDER BYnhưng không cho trongHAVING/WHERE. Lặp lại biểu thức trongHAVINGđể 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_progressnghĩ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àoidvì 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.
- Số khoá học mỗi giáo viên được phân công.
- 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.
- Như 3.2 nhưng phân trang, mỗi trang tối đa 2 dòng, lấy trang 2.
- Như 3.2 nhưng gồm cả giáo viên chưa dạy khoá nào.
- Học viên và mọi khoá học họ đã đăng ký.
- Điểm đánh giá trung bình mỗi khoá, chỉ lấy khoá có trung bình ≥ 4.0.
- Học viên, khoá đã đăng ký và giáo viên của khoá đó.
- 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.
- 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:
- Sau
LEFT JOINbảng tiến độ lạiINNER JOINbả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. - Có
GROUP BYmà không có hàm tổng hợp nào, thực chất chỉ làDISTINCTtrá 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
UNIQUEtrê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 CASCADEvà 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.