Hướng dẫn SQL cho người mới bắt đầu

Vậy là “SELECT * WHERE a=b FROM c” hay là “SELECT WHERE a=b FROM c ON *” ?

SQL là một ngôn ngữ có cú pháp thoạt nhìn thì khá đơn giản (quả thật thế, đọc nó cũng tương tự như đọc tiếng anh thông thường vậy), nhưng vì một số lý do củ chuối nào đó mà bạn chẳng thể làm gì khác ngoài việc google-sama cú pháp đúng cho từng câu truy vấn ngớ ngẩn.

Kế đó, bạn bắt đầu join chúng, sử dụng các phép tính aggregation, sử dụng các subqueries và rồi bạn thắc mắc tại sao chúng chẳng có ý nghĩa quái gì cả! Vâng, chúng từa tựa như thế này:

SELECT members.firstname || ' ' || members.lastname
AS "Full Name"
FROM borrowings
JOIN members
ON members.memberid=borrowings.memberid
JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
  FROM books
  WHERE stock>(SELECT avg(stock)
    FROM books))
GROUP BY members.firstname, members.lastname;

Bối rối ***! Cái đống này có thể làm bất cứ một gã dev mới vào nghề nào cũng phát hoảng, thậm chí là cả những tay dev tầm trung mới lần đầu nhìn mã SQL.

Thông qua hướng dẫn này, tôi muốn làm cho con đường đến với SQL của những người mới bớt chông gai, kể cả với những người đã làm việc lâu năm với SQL nhưng muốn tìm kiếm một tương lai xán lạn hơn.

Tất cả những câu truy xuất SQL trong bài viết đều viết trên PostgreSQL, mặc dù SQL có cú pháp tương tự nhau trên các cơ sở dữ liệu khác nhau. Do đó, một số cú pháp có thể vẫn hoạt động tốt trên MySQL hay các cơ sở dữ liệu khác.

Nội dung

1. Ba từ nhiệm màu ( The three magic words )
2. Cơ sở dữ liệu của chúng ta
3. Các câu truy vấn đơn giản
   1. FROM - Chúng ta lấy dữ liệu ở đâu?
   2. WHERE - Chúng ta lấy dữ liệu ấy qua đâu?
   3. SELECT - Chúng ta sẽ biểu diễn những gì?
4. Joins
5. Aggregations
6. Subqueries
   1. Bảng hai chiều
   2. Mảng một chiều
   3. Giá trị đơn (Single value)
7. Write Operations
   1. Update
   2. Delete
   3. Insert
8. Feedback

1. Ba từ nhiệm màu

Mặc dừ có rất nhiều từ khóa được sử dụng trong SQL, SELECT, FROM, WHERE vẫn là 3 từ khóa được sử dụng nhiều nhất. Sau khi đọc tiếp, bạn sẽ dần hình dung được SELECT, FROM, WHERE thể hiện những khía cạnh cơ bản nhất của công việc truy vấn một cơ sở dữ liệu. Bạn cũng sẽ nhận ra rằng những câu truy vấn phức tạp cũng chỉ là SELECT, FROM, WHERE được mở rộng.

2. Cơ sở dữ liệu của chúng ta

Tên cơ sở dữ liệu là Library với bảng books cùng các bảng khác là members, borrowing.

• Bảng books có các cột title, author, published (ngày phát hành), stock - số bản còn lại được sắp xếp khá dễ hiểu.
• Bảng members chỉ có duy nhất tên của các thành viên đã đăng kí.
• Bảng borrowing chứa thông tin những cuốn sách đã được mượn bởi thành viên. Cột bookid chính là id của những cuốn sách đã được mượn trong bảng books và cột memberid tương ứng là những thành viên trong bảng “members” đã mượn sách. Chúng ta cũng có cột ngày mượn sách.

3. Câu truy vấn đơn giản

Hãy bắt đầu với câu truy vấn đầu tiên: ta cần names và ids của tất cả các cuốn sách của tác giả Dan Brown. Câu truy vấn:

SELECT bookid AS "id", title
FROM books
WHERE author='Dan Brown';

Kết quả trả về như sau:  

Ez money! Tuy nhiên, chúng ta cần phân tích cú pháp câu truy vấn trên để hiểu rõ vấn đề:

3.1 FROM - Chúng ta lấy dữ liệu ở đâu?

Với những câu truy vấn đơn giản như trên thì khá rõ ràng, ta sẽ chọn bảng books để lấy dữ liệu. Tuy nhiên khi đi sâu vào các truy vấn JOIN và các subquery thì vấn đề trở nên khó khăn hơn. Định nghĩa của khóa FROM là nó sẽ chỉ ra bảng nơi mà các câu truy vấn cần tìm kiếm dữ liệu phù hợp. Bảng có thể đã tồn tại rồi, hoặc là bảng tạo bởi câu lệnh JOIN hay từ các subqueries. 

3.2 WHERE - Chúng ta lấy dữ liệu ấy qua đâu?

Từ khóa này dùng điều kiện và lọc ra hàng phù hợp với điều kiện ấy. Trong trường hợp của chúng ta thì những hàng trả về sẽ nằm ở cột author và có giá trị “Dan Brown”.

3.3 SELECT - Chúng ta sẽ biểu diễn những gì?

Giả sử chúng ta đã có đầy đủ những bảng dữ liệu phù hợp. Vấn đề là lấy những dữ liệu cần thiết như thế nào? Ta cần id và name của những cuốn sách, vậy ta sẽ SELECT chúng. Chúng ta cũng có thể đổi tên hiện thị của cột bằng từ khóa AS (tên gốc trong bảng không thay đổi).

Tóm lại, câu truy vấn dữ liệu ban đầu của chúng ta có thể được biểu diễn như trên hình:

4. Joins

Bây giờ nếu cần tất cả tên của các cuốn sách đã được mượn của tác giả Dan Brown cùng với ngày mượn, ta sử dụng câu truy vấn sau (lưu ý rằng có thể có sự lặp lại):

SELECT books.title AS "Title", borrowings.returndate AS "Return Date"
FROM borrowings JOIN books ON borrowings.bookid=books.bookid
WHERE books.author='Dan Brown';

Kết quả trả về:

Hẳn các bạn đã quen thuộc với cú pháp trên, ngoại trừ sau phần FROM: bảng chúng ta truy vấn đến đã thay đổi. Bây giờ bảng mới không phải borrowing hay books mà là 1 bảng mới, kết hợp cả 2 bảng trên.

Cú pháp borrowings JOIN books ON borrowings.bookid=books.bookid được xem như 1 bảng mới, tổng hợp tất cả các mục của 2 bảng borrowing và books, miễn là các mục có cùng bookid ở mỗi bảng. Bảng tổng hợp như sau:

Chúng ta chỉ cần truy vấn đến dữ liệu trong bảng mới này tương tự như truy vấn ở những bảng đơn. Vấn đề đã trở nên đơn giản hơn: chúng ta chỉ cần quan tâm rằng nên JOIN các bảng như thế nào. Tùy thuộc vào cách chúng ta JOIN bảng mà mức độ phức tạp của các câu truy vấn cũng sẽ giảm.

Bây giờ hãy thử 1 ví dụ khó hơn một chút:

• Ta cần biết tên của những người đã mượn sách của Dan Brown.

Lần này chúng ta sẽ thử phương pháp suy luận ngược để giải quyết bài toán:

Bước 1: ta sẽ lấy dữ liệu từ đâu? Cần phải JOIN bảng members, bảng books và bảng borrowing với nhau. Câu truy vấn:

borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid

Bảng kết quả trả về:

Bước 2: Biểu diễn dữ liệu gì? Cần tập trung vào những dữ liệu có yếu tố author là “Dan Brown”.

WHERE books.author='Dan Brown'

Bước 3: Biểu diễn dữ liệu lấy được như thế nào? Bây giờ dữ liệu đã đầy đủ,ta cần biểu diễn tên của những thành viên mượn sách.

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"

Công việc bây giờ chỉ đơn giản là kết hợp 3 bước để hoàn thiện truy vấn. Cú pháp đầy đủ như sau:

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown';

Bảng kết quả trả về:

Tuy nhiên có những tên bị trùng. Kế tiếp ta sẽ xử lí chúng.

5. Aggregations

Nói 1 cách ngắn gọn, aggregation (phép gộp) được sử dụng để chuyển nhiều hàng thành 1 hàng. Aggregation sẽ chỉ làm thay đổi logic của các cột sử dụng gộp dữ liệu.

Hãy tiếp tục với ví dụ trước, khi kết quả “Ellen Horton”  bị lặp. Ta sẽ viết truy vấn khác:

SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown'
GROUP BY members.firstname, members.lastname;

Kết quả trả về: 

Hầu hết trong mọi tình huống dùng phép gộp ta đều sử dụng khóa GROUP BY.  Khóa này dùng để xếp các dữ liệu giống nhau thành nhóm. Mỗi nhóm tương ứng với 1 nhóm các giá trị duy nhất có đặc điểm nêu sau khóa GROUP BY. Cụ thể, trong ví dụ trên ta đã chuyển dữ liệu thu được vào nhóm các hàng. Ta cũng đã sử dụng hàm gộp count. Hàm count sẽ chuyển nhiều hàng vào 1 giá trị đơn (trong trường hợp này là số hàng).

Mỗi hàng ở bảng sau biểu diễn kết quả sau khi gộp của từng nhóm.

Ngoài hàm count, chúng ta còn có hàm sum hay max. Ví dụ, nếu ta muốn tính tổng số sách của từng tác giả, ta viết:

SELECT author, sum(stock)
FROM books
GROUP BY author;

Kết quả trả về như sau:

Trên đây là hàm SUM, nó chỉ làm việc trên cột stock, nó dùng để tính tổng các giá trị trong mỗi nhóm.

Tại khóa học Lập trình PHP 7 qua dự án thực tế bạn có thể học được cách sử dụng SQL trong các dự án thực tế và các tương tác với cơ sở dữ liệu thông qua PHP 7

6. Subqueries

Subqueries – truy vấn con, là những câu truy vấn SQL bình thường, tuy nhiên nó được thêm vào bên trong những câu truy vấn lớn.

Có 3 loại subqueries khác nhau dựa trên kiểu chúng trả về.

6.1 Bảng hai chiều

Là những câu truy vấn trả về kết quả nhiều hơn 1 cột. Hãy xem lại các bảng kết quả mà chúng ta đã có.

Bây giờ thử viết truy vấn lấy dữ liệu từ bảng này, ta sẽ được một bảng mới có cấu trúc bé hơn ( ít hàng, ít cột hơn). Tuy nhiên, khi sử dụng subqueries, t chỉ cần viết truy vấn 1 lần và bỏ qua được bước tạo bảng trên, nhảy thẳng đến bảng kết quả cuối cùng:

SELECT *
FROM (SELECT author, sum(stock)
  FROM books
  GROUP BY author) AS results
WHERE author='Robin Sharma';

Kết quả:

6.2 Mảng một chiều

Những câu truy vấn trả về nhiều hàng của chỉ 1 cột, kết quả thu được có thể biểu diễn theo dạng mảng.

Ví dụ, chúng ta cần tiêu đề và id của tất cả các cuốn sách của 1 tác giả, với điều kiện số sách phải lớn hơn 3. Hãy chia nhỏ vấn đề để giải quyết:

Bước 1: Lấy dữ liệu tác giả có số sách lớn hơn 3

SELECT author
FROM (SELECT author, sum(stock)
  FROM books
  GROUP BY author) AS results
WHERE sum > 3;

Kết quả trả về: 

Kết quả này có thể viết: ['Robin Sharma', 'Dan Brown']

Bước 2: Bây giờ hãy dùng kết quả trên để truy vấn tiếp:

SELECT title, bookid
FROM books
WHERE author IN (SELECT author
  FROM (SELECT author, sum(stock)
  FROM books
  GROUP BY author) AS results
  WHERE sum > 3);

Trả về kết quả:

Viết gọn lại theo kiểu mảng:

SELECT title, bookid
FROM books
WHERE author IN ('Robin Sharma', 'Dan Brown');

6.3 Single Values

Có những câu truy vấn chỉ trả về kết quả là 1 hàng, 1 cột. Kết quả này có thể xem như là giá trị không đổi (hằng) và có thể được sử dụng trong các phép so sánh. Nó cũng được xem như mảng 1 chiều chỉ 1 phần tử, hay bảng hai chiều.

Hãy tiếp tục với các ví dụ, tìm cuốn sách có số lượng lớn hơn số lượng sách trung bình.

select avg(stock) from books;

Kết quả trả về:

Nó có thể được sử dụng như giá trị vô hướng 3.

Hãy đến với câu truy vấn tiếp theo:

SELECT *
FROM books
WHERE stock>(SELECT avg(stock) FROM books);

SELECT *
FROM books
WHERE stock>3.000

UPDATE books
SET stock=0
WHERE author='Dan Brown';

DELETE FROM books
WHERE author='Dan Brown';

INSERT INTO x
  (a,b,c)
VALUES
  (x, y, z);

INSERT INTO books
  (bookid,title,author,published,stock)
VALUES
  (1,'Scion of Ikshvaku','Amish Tripathi','06-22-2015',2),
  (2,'The Lost Symbol','Dan Brown','07-22-2010',3),
  (3,'Who Will Cry When You Die?','Robin Sharma','06-15-2006',4),
  (4,'Inferno','Dan Brown','05-05-2014',3),
  (5,'The Fault in our Stars','John Green','01-03-2015',3);

SELECT members.firstname || ' ' || members.lastname AS "Full Name"

FROM borrowings
JOIN members
ON members.memberid=borrowings.memberid
JOIN books
ON books.bookid=borrowings.bookid

WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>  (SELECT avg(stock) FROM books)  )

GROUP BY members.firstname, members.lastname;