Học Cơ sở dữ liệu và SQL từ đầu
1Cơ sở dữ liệu quan hệ là gì
Một cơ sở dữ liệu quan hệ lưu dữ liệu trong các bảng. Một bảng giống bảng tính: mỗi hàng là một bản ghi, mỗi cột là một thuộc tính.
Một cửa hàng có thể có bảng customers với các cột id, name và city, và bảng orders với các cột id, customer_id và total.
Khóa chính là một cột, hoặc một tập các cột, xác định duy nhất từng hàng. Nó không bao giờ được lặp lại và không bao giờ để trống. Ở đây đó là id của mỗi bảng.
Khóa ngoại là một cột tham chiếu đến khóa chính của bảng khác. Trong orders, customer_id cho biết khách hàng nào đã đặt đơn.
Đây là cách các bảng được liên kết: một khách hàng có thể có nhiều đơn hàng, nhưng mỗi đơn hàng thuộc về một khách hàng. Đó là quan hệ một-nhiều.
SQL (Structured Query Language) là ngôn ngữ bạn dùng để tạo bảng, thay đổi dữ liệu và đặt câu hỏi về nó.
Tự kiểm tra Khóa chính là gì?
- Cột đầu tiên của mọi bảng
- Một chỉ mục làm cho việc tìm kiếm nhanh hơn
- Một cột hoặc một tập cột xác định duy nhất từng hàng
- Một cột tham chiếu đến một hàng trong bảng khác
Đáp số: Một cột hoặc một tập cột xác định duy nhất từng hàng. Khóa chính là duy nhất và không bao giờ NULL, nên nó chọn ra đúng một hàng. Một cột tham chiếu đến bảng khác là khóa ngoại.
2Đọc dữ liệu với SELECT
Hầu hết câu hỏi nói về câu lệnh SELECT. Nó được xây dựng từ các mệnh đề, luôn viết theo thứ tự này:
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …
- SELECT liệt kê các cột bạn muốn. DISTINCT loại bỏ các hàng trùng lặp.
- FROM nêu tên bảng, hoặc các bảng được nối với nhau.
- WHERE chỉ giữ lại các hàng thỏa một điều kiện, ví dụ WHERE total > 20.
- GROUP BY gộp các hàng có cùng giá trị vào một nhóm, để bạn có thể đếm hoặc cộng theo từng nhóm.
- HAVING chỉ giữ lại các nhóm thỏa một điều kiện. Nó có thể dùng các hàm tổng hợp, còn WHERE thì không.
- ORDER BY sắp xếp kết quả, mặc định tăng dần; thêm DESC để lớn nhất trước.
Các hàm tổng hợp tóm tắt nhiều hàng thành một giá trị: COUNT, SUM, AVG, MIN và MAX.
Cơ sở dữ liệu không chạy các mệnh đề theo thứ tự bạn viết. Nó chạy FROM trước, rồi WHERE, GROUP BY, HAVING, SELECT và cuối cùng là ORDER BY.
Các câu lệnh khác làm thay đổi dữ liệu: INSERT thêm hàng, UPDATE thay đổi hàng và DELETE xóa hàng. CREATE, ALTER và DROP thay đổi chính các bảng.
Tự kiểm tra Bạn chỉ muốn những khách hàng có hơn 5 đơn hàng. Mệnh đề nào lọc theo COUNT(*) > 5?
- DISTINCT
- HAVING
- ORDER BY
- WHERE
Đáp số: HAVING. Giá trị đếm chỉ tồn tại sau khi các hàng được gom nhóm, và HAVING lọc các nhóm. WHERE chạy trước khi gom nhóm, nên không thể dùng hàm tổng hợp như COUNT.
3Phép nối, chuẩn hóa và giao dịch
Một phép nối (join) kết hợp các hàng của hai bảng bằng một điều kiện, thường là khóa ngoại = khóa chính:
FROM customers c JOIN orders o ON o.customer_id = c.id
- INNER JOIN (hoặc JOIN) chỉ giữ các hàng có khớp ở cả hai bảng.
- LEFT JOIN giữ mọi hàng của bảng bên trái. Nơi không có khớp, các cột của bảng bên phải là NULL.
- NULL nghĩa là một giá trị bị thiếu hoặc không xác định. Kiểm tra nó bằng IS NULL, không bao giờ bằng = NULL.
Chuẩn hóa tách các bảng để mỗi sự kiện chỉ được lưu một lần. Điều này tránh phải cập nhật cùng một sự kiện ở nhiều nơi.
- 1NF: mỗi ô chứa một giá trị nguyên tử, không có nhóm lặp.
- 2NF: 1NF, và mọi cột không khóa phụ thuộc vào toàn bộ khóa, không phụ thuộc vào một phần của khóa phức hợp.
- 3NF: 2NF, và không cột không khóa nào phụ thuộc vào một cột không khóa khác.
Một giao dịch gom các câu lệnh thành một đơn vị. COMMIT làm cho nó vĩnh viễn và ROLLBACK hoàn tác nó. Giao dịch tuân theo ACID: tính nguyên tử (tất cả hoặc không gì cả), nhất quán, cô lập và bền vững.
Tự kiểm tra Một khách hàng chưa đặt đơn nào. Điều gì xảy ra với khách hàng đó trong customers LEFT JOIN orders?
- Khách hàng bị loại khỏi kết quả
- Khách hàng xuất hiện một lần cho mỗi đơn hàng trong bảng
- Khách hàng xuất hiện một lần, với NULL ở các cột đơn hàng
- Truy vấn thất bại với một lỗi
Đáp số: Khách hàng xuất hiện một lần, với NULL ở các cột đơn hàng. LEFT JOIN giữ mọi hàng của bảng bên trái và điền NULL vào phía bên phải bị thiếu. Loại khách hàng ra là điều INNER JOIN làm.
4Phương pháp từng bước
Để viết một truy vấn, hoặc để tìm một truy vấn cho trước trả về gì:
- Tìm các bảng chứa dữ liệu, và chúng được liên kết bằng khóa như thế nào.
- Viết FROM cùng các phép nối. Chọn LEFT JOIN khi các hàng không có khớp phải được giữ lại.
- Thêm WHERE cho các điều kiện trên từng hàng.
- Nếu câu hỏi nói “mỗi” hoặc “theo từng”, hãy thêm GROUP BY và hàm tổng hợp.
- Thêm HAVING cho các điều kiện trên hàm tổng hợp, rồi ORDER BY nếu thứ tự quan trọng.
- Kiểm tra kết quả trên một ví dụ nhỏ: liệt kê các hàng sau mỗi mệnh đề, theo thứ tự cơ sở dữ liệu chạy chúng.
Với câu hỏi lý thuyết, trước hết gọi tên khái niệm (một khóa, một loại phép nối, một dạng chuẩn, một tính chất ACID), rồi chọn phương án khớp với định nghĩa của nó.
5Một ví dụ có lời giải
Theo dõi từng bước. Hãy thử đoán bước tiếp theo trước khi mở nó.
Ví dụ có lời giảiBảng customers có các hàng (1, Ana), (2, Ben) và (3, Cas). Bảng orders có các hàng (id, customer_id) = (10, 1), (11, 1), (12, 2) và (13, 1). Truy vấn này trả về gì? SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name
1Mỗi khách hàng được ghép với các đơn hàng mang id của nó. Ana khớp với các đơn 10, 11 và 13. Ben khớp với đơn 12.
Cas không có đơn hàng nào. Vì đây là LEFT JOIN, Cas vẫn ở trong kết quả, với NULL cho o.id.
2- Ana, 10
- Ana, 11
- Ana, 13
- Ben, 12
- Cas, NULL
Vậy là 5 hàng.
3GROUP BY c.name tạo ba nhóm: Ana với 3 hàng, Ben với 1 hàng và Cas với 1 hàng.
4COUNT(o.id) chỉ đếm các giá trị không phải NULL. Ana được 3 và Ben được 1.
Cas có một hàng, nhưng o.id của nó là NULL, nên Cas được 0.
5Ana, Ben và Cas cộng lại có 3 + 1 + 0 = 4 đơn hàng, bằng số hàng trong orders. Nếu dùng COUNT(*) thay thế, Cas sẽ bị tính nhầm là 1, vì COUNT(*) đếm chính hàng đó.
Đáp số Ana 3, Ben 1, Cas 0.
6Lỗi thường gặp
- Dùng hàm tổng hợp trong WHERE. Các điều kiện trên COUNT, SUM hay AVG thuộc về HAVING.
- Viết = NULL. Mọi so sánh với NULL đều không xác định; hãy dùng IS NULL hoặc IS NOT NULL.
- Dùng INNER JOIN khi các hàng không có khớp phải được giữ lại. Hãy dùng LEFT JOIN.
- Quên điều kiện nối. Không có ON, mỗi hàng được ghép với mọi hàng (phép nối chéo).
- Chọn một cột vừa không có trong GROUP BY vừa không nằm trong một hàm tổng hợp.
- Nhầm DELETE (xóa các hàng) với DROP TABLE (xóa cả bảng).
- Chạy UPDATE hoặc DELETE mà không có WHERE. Nó thay đổi mọi hàng trong bảng.
Tự kiểm tra Một bảng có 10 hàng, và cột col là NULL ở mọi hàng. SELECT COUNT(col) trả về gì?
- 0
- 10
- 1
- NULL
Đáp số: 0. COUNT(col) chỉ đếm các giá trị không phải NULL, nên nó trả về 0. COUNT(*) đếm số hàng và sẽ trả về 10.
7Các câu hỏi thi trông như thế nào
- Dễ: thuật ngữ và sự kiện chính. SELECT, WHERE, ORDER BY hay DELETE làm gì, khóa chính và khóa ngoại, NULL nghĩa là gì.
- Trung bình: vận dụng khái niệm. Truy vấn đúng cho một tác vụ, WHERE so với HAVING, phép nối trong so với ngoài, các dạng chuẩn, ACID.
- Khó: ca bài tập và phân tích. Truy vấn con, các mức cô lập và các dị thường của chúng, BCNF, hàm cửa sổ, chỉ mục và hiệu năng truy vấn, SQL injection.
Khi cho một truy vấn, hãy lần theo nó trên vài hàng theo thứ tự FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Hầu hết bẫy xuất hiện trong ba hàng.
Học từng bước một trong ứng dụngHọc từng bước một trong ứng dụng