WHERE đào sâu ② — BETWEEN và LIKE cho lọc theo phạm vi và mẫu

Dữ liệu sẽ dùng — bảng staff

Bài thứ 2 của loạt đào sâu WHERE đề cập lọc theo phạm vi (BETWEEN) và khớp mẫu (LIKE). Khi kết hợp các điều kiện ghép đã dựng bằng AND / OR / NOT ở bài trước với BETWEEN / LIKE của bài này, bạn có thể diễn đạt phần lớn các yêu cầu lọc thường gặp trong thực tế.

Trước các bài tập, hãy xác nhận định nghĩa cột và một mẫu dữ liệu của bảng staff.

① Chạy PRAGMA table_info(staff); để kiểm tra tên cột, kiểu, và khóa chính.

② Chạy SELECT * FROM staff LIMIT 5; để xem trước 5 hàng đầu.

BETWEEN — lọc theo phạm vi giá trị

Khi bạn muốn kiểm tra liệu một giá trị có nằm trong một phạm vi — "lương giữa 5.000.000 và 6.000.000," "sinh nhật giữa Tháng 4 2020 và Tháng 3 2025" — BETWEEN là toán tử.

Viết column BETWEEN value1 AND value2 trả về các hàng mà cột ít nhất là value1 và nhiều nhất là value2. Lưu ý rằng cả hai đầu mút đều được bao gồm. Nó nghĩa giống như column >= value1 AND column <= value2; mọi người chọn BETWEEN thường chỉ vì khả năng đọc.

Thêm NOT BETWEEN trả về các hàng ngoài phạm vi (nhỏ hơn value1 hoặc lớn hơn value2).

-- Staff có lương giữa 5.000.000 và 6.000.000 (bao gồm các đầu mút)
SELECT name, salary FROM staff
WHERE salary BETWEEN 5000000 AND 6000000;

-- Cùng nghĩa, viết với >= và <= nối bằng AND
SELECT name, salary FROM staff
WHERE salary >= 5000000 AND salary <= 6000000;

-- Ngoài phạm vi (dưới 5.000.000 HOẶC trên 6.000.000)
SELECT name, salary FROM staff
WHERE salary NOT BETWEEN 5000000 AND 6000000;

BETWEEN bao gồm cả hai đầu mút

BETWEEN 5000000 AND 6000000 bao gồm đúng 5.000.000 và đúng 6.000.000. Nếu bạn muốn loại trừ các đầu mút, viết > 5000000 AND < 6000000.

Với ngày tháng, bạn có thể viết BETWEEN '2020-04-01' AND '2025-03-31' — các literal được so sánh như chuỗi. Để bao gồm "toàn bộ Tháng 3 2025," định nghĩa cả hai đầu mút bằng ngày cuối tháng thực tế là cách an toàn. BETWEEN '2020-04-01' AND '2025-04-01' cũng sẽ bao gồm cả `2025-04-01`, thường không phải điều bạn muốn.

Hãy hình dung cuộc họp lãnh đạo cần số lượng "người thu nhập trung tầng (lương 5.000.000 – 6.000.000)." (Khi bạn chạy đúng, phần giải thích sẽ hiện ra.)

① Từ staff, lấy các cột name và salary.

② Lọc đến các hàng mà `salary` ít nhất là 5.000.000 và nhiều nhất là 6.000.000 (bao gồm các đầu mút).

③ Xác nhận kết quả là 3 hàng (Bob 5.200.000 / Grace 5.500.000 / Jack 5.900.000).

Hãy hình dung yêu cầu "xem các staff ngoài trung tầng (4.500.000 – 6.000.000), sắp theo lương cao nhất trước."

① Từ staff, lấy các cột name và salary.

② Lọc đến các hàng mà `salary` nằm ngoài phạm vi 4.500.000 – 6.000.000 (tức là dưới 4.500.000 hoặc trên 6.000.000).

③ Sắp theo `salary` giảm dần.

④ Xác nhận kết quả là 6 hàng, bắt đầu với Frank 7.200.000 và kết thúc với Emi 3.800.000.

LIKE — lọc theo khớp mẫu

Khi bạn muốn lọc theo một mẫu chuỗi — "các tên kết thúc bằng Tanaka," "các email chứa @example.com" — LIKE là toán tử. Viết column LIKE 'pattern'. Bên trong mẫu bạn có thể dùng hai wildcard sau.

'A%' là bắt đầu với A, '%a' là kết thúc với a, '%a%' là chứa a, 'A___%' là bắt đầu với A và có ít nhất ba ký tự nữa — kết hợp chúng theo nhu cầu. Thêm NOT LIKE trả về các hàng không khớp mẫu.

-- 1) Khớp tiền tố: name bắt đầu với A
SELECT name FROM staff WHERE name LIKE 'A%';

-- 2) Khớp hậu tố: họ Tanaka
SELECT name FROM staff WHERE name LIKE '%Tanaka';

-- 3) Chứa: name chứa 'a' ở bất cứ đâu
SELECT name FROM staff WHERE name LIKE '%a%';

-- 4) Một ký tự: ký tự thứ 2 là 'a', phần còn lại bất kỳ
SELECT name FROM staff WHERE name LIKE '_a%';

-- 5) Phủ định: không kết thúc với Tanaka
SELECT name FROM staff WHERE name NOT LIKE '%Tanaka';

LIKE và `=` tuân theo quy tắc phân biệt hoa thường khác nhau

Trong console của khóa học này (SQLite), trong phạm vi ASCII, LIKE không phân biệt hoa thường. name LIKE '%a%' và name LIKE '%A%' trả về cùng các hàng. Để làm cho nó phân biệt hoa thường, chạy PRAGMA case_sensitive_like = 1; hoặc chuẩn hóa cả hai vế bằng LOWER(...).

Trong khi đó, so sánh chuỗi với `=` (đề cập trong bài SELECT) phân biệt hoa thường — chú ý sự khác biệt hành vi giữa = và LIKE.

Tìm kiếm `%` hoặc `_` theo nghĩa đen — mệnh đề `ESCAPE`

Nếu chuỗi bạn đang tìm thực sự chứa một ký tự nghĩa đen % hoặc _ (chẳng hạn, một mã sản phẩm như 'A_001'), thì mặc định _ sẽ được hiểu là wildcard. Mệnh đề ESCAPE cho phép bạn chỉ định một ký tự escape, sau đó % / _ được coi là ký tự nghĩa đen.

Ví dụ: WHERE code LIKE 'A\_001' ESCAPE '\'; (khai báo \ là ký tự escape, nên \_ được coi là dấu gạch dưới nghĩa đen). Ký tự escape không nhất thiết là \ — ESCAPE '#' làm # là escape, ESCAPE '!' làm nó là !, v.v. Bạn sẽ dùng đến cái này khi tìm kiếm các cột mã chứa _ hoặc %.

Chạy ba mẫu — tiền tố, hậu tố, chứa-qua-_ — trong một console duy nhất và so sánh kết quả.

① Cho mỗi truy vấn, lấy name, city, và salary từ staff.

② Lấy các hàng mà `name` bắt đầu với `A` (khớp tiền tố 'A%').

③ Sau đó lấy các hàng mà `name` kết thúc với `Tanaka` (khớp hậu tố '%Tanaka').

④ Sau đó lấy các hàng mà ký tự thứ 2 của `name` là `a` ('_a%', trong đó _ là một ký tự bất kỳ).

⑤ Xác nhận số hàng là 1 / 4 / 3.

Kết hợp WHERE, ORDER BY, và LIMIT

Truy vấn thực tế thường kết hợp lọc (WHERE) → sắp xếp (ORDER BY) → giới hạn hàng (LIMIT) theo đúng thứ tự đó. Thứ tự mệnh đề là cố định, và đảo chúng là lỗi cú pháp.

Thứ tự thực thi của DB đại khái là FROM → WHERE → SELECT → ORDER BY → LIMIT — đầu tiên thu hẹp các hàng, chọn các cột bạn cần, sắp chúng, rồi cắt số lượng bạn muốn.

Hãy hình dung một dashboard hiển thị xếp hạng lương cho "staff có a trong tên."

① Từ staff, lấy các cột name và salary.

② Lọc đến các hàng mà `name` chứa `a` (khớp chứa).

③ Sắp theo `salary` giảm dần, và chỉ lấy 3 hàng đầu.

④ Xác nhận kết quả là 3 hàng: Frank Tanaka 7.200.000 / David Sato 6.800.000 / Henry Sato 6.100.000.