Giới thiệu về Truy vấn trong PostgreSQL

Database là thành phần quan trọng của nhiều trang web và ứng dụng, đồng thời là cốt lõi của cách dữ liệu được lưu trữ và trao đổi trên internet. Một trong những khía cạnh quan trọng nhất của quản lý database là thực hành truy xuất dữ liệu từ database , cho dù đó là trên cơ sở đặc biệt hay một phần của quy trình được mã hóa thành một ứng dụng. Có một số cách để lấy thông tin từ database , nhưng một trong những phương pháp được sử dụng phổ biến nhất là thực hiện thông qua việc gửi truy vấn thông qua dòng lệnh.

---

Trong hệ quản trị database quan hệ, truy vấn là lệnh bất kỳ được sử dụng để truy xuất dữ liệu từ bảng. Trong Ngôn ngữ truy vấn có cấu trúc (SQL), các truy vấn hầu như luôn được thực hiện bằng cách sử dụng SELECT .

Trong hướng dẫn này, ta sẽ thảo luận về cú pháp cơ bản của các truy vấn SQL cũng như một số hàm và toán tử được sử dụng phổ biến hơn. Ta cũng sẽ thực hành tạo các truy vấn SQL bằng cách sử dụng một số dữ liệu mẫu trong database PostgreSQL.

PostgreSQL , thường được viết tắt là “Postgres”, là một hệ quản trị database quan hệ với cách tiếp cận hướng đối tượng, nghĩa là thông tin có thể được biểu diễn dưới dạng các đối tượng hoặc lớp trong các schemas PostgreSQL. PostgreSQL phù hợp chặt chẽ với SQL tiêu chuẩn, mặc dù nó cũng bao gồm một số tính năng không có trong các hệ thống database quan hệ khác.

Yêu cầu

Nói chung, các lệnh và khái niệm được trình bày trong hướng dẫn này được dùng trên bất kỳ hệ điều hành dựa trên Linux nào đang chạy bất kỳ phần mềm database SQL nào. Tuy nhiên, nó được viết riêng với server Ubuntu 18.04 chạy PostgreSQL. Để cài đặt điều này, bạn cần những thứ sau:

• Máy Ubuntu 18.04 với user không phải root có quyền sudo. Điều này có thể được cài đặt bằng cách sử dụng hướng dẫn Cài đặt Server Ban đầu của ta cho Ubuntu 18.04 .
• PostgreSQL đã được cài đặt trên máy. Để được trợ giúp cài đặt điều này, hãy làm theo phần “Cài đặt PostgreSQL” trong hướng dẫn của ta về Cách cài đặt và sử dụng PostgreSQL trên Ubuntu 18.04 .

Với cài đặt này, ta có thể bắt đầu hướng dẫn.

Tạo database mẫu

Trước khi có thể bắt đầu thực hiện các truy vấn trong SQL, trước tiên ta sẽ tạo một database và một vài bảng, sau đó điền vào các bảng này một số dữ liệu mẫu. Điều này sẽ cho phép bạn có được một số kinh nghiệm thực tế khi bắt đầu thực hiện các truy vấn sau này.

Đối với database mẫu mà ta sẽ sử dụng trong suốt hướng dẫn này, hãy tưởng tượng tình huống sau:

Bạn và một số bạn bè của bạn đều tổ chức sinh nhật với nhau. Vào mỗi dịp, các thành viên của group đến playground bowling ở local , tham gia một giải đấu giao hữu, và sau đó mọi người đến nơi của bạn, nơi bạn chuẩn bị bữa ăn sinh nhật yêu thích của người đó.

Bây giờ truyền thống này đã diễn ra được một thời gian, bạn quyết định bắt đầu theo dõi các kỷ lục từ các giải đấu này. Ngoài ra, để lập kế hoạch bữa tối dễ dàng hơn, bạn quyết định tạo một bản ghi về sinh nhật của bạn bè và các món khai vị, món ăn kèm và món tráng miệng yêu thích của họ. Thay vì giữ thông tin này trong một sổ cái vật lý, bạn quyết định thực hiện các kỹ năng database của bạn bằng cách ghi lại nó trong database PostgreSQL.

Để bắt đầu, hãy mở dấu nhắc PostgreSQL với quyền là superuser postgres của bạn:

sudo -u postgres psql 

Lưu ý: Nếu bạn đã làm theo tất cả các bước của hướng dẫn tiên quyết về Cài đặt PostgreSQL trên Ubuntu 18.04 , bạn có thể đã cấu hình một role mới cho cài đặt PostgreSQL của bạn . Trong trường hợp này, bạn có thể kết nối với dấu nhắc Postgres bằng lệnh sau, thay thế sammy bằng tên user của bạn :

sudo -u sammy psql 

Tiếp theo, tạo database bằng lệnh:

CREATE DATABASE birthdays; 

Sau đó chọn database này bằng lệnh :

\c birthdays 

Tiếp theo, tạo hai bảng trong database này. Ta sẽ sử dụng bảng đầu tiên để theo dõi profile của bạn bè bạn tại playground bowling. Lệnh sau sẽ tạo một bảng có tên là tourneys với các cột cho name của từng người bạn của bạn, số giải đấu mà họ đã giành được ( wins ), điểm số best mọi thời đại của họ và loại giày bowling mà họ mang ( size ):

CREATE TABLE tourneys (  
name varchar(30),  
wins real,  
best real,  
size real  
); 

Khi bạn chạy CREATE TABLE và điền nó với các tiêu đề cột, bạn sẽ nhận được kết quả sau:

Output
CREATE TABLE 

Điền vào bảng tourneys với một số dữ liệu mẫu:

INSERT INTO tourneys (name, wins, best, size)  
VALUES ('Dolly', '7', '245', '8.5'),  
('Etta', '4', '283', '9'),  
('Irma', '9', '266', '7'),  
('Barbara', '2', '197', '7.5'),  
('Gladys', '13', '273', '8'); 

Bạn sẽ nhận được kết quả sau:

Output
INSERT 0 5 

Sau đó, tạo một bảng khác trong cùng một database mà ta sẽ sử dụng để lưu trữ thông tin về bữa ăn sinh nhật yêu thích của bạn bè bạn. Các lệnh sau đây tạo ra một bảng tên là dinners với các cột cho name của mỗi bạn bè, họ birthdate , yêu thích của họ entree , ưa thích của họ side món ăn, và họ yêu thích dessert :

CREATE TABLE dinners (  
name varchar(30),  
birthdate date,  
entree varchar(30),  
side varchar(30),  
dessert varchar(30)  
); 

Tương tự với bảng này, bạn sẽ nhận được phản hồi xác minh bảng đã được tạo:

Output
CREATE TABLE 

Cũng điền vào bảng này một số dữ liệu mẫu:

INSERT INTO dinners (name, birthdate, entree, side, dessert)  
VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'),  
('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'),  
('Irma', '1941-02-18', 'tofu', 'fries', 'cake'),  
('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'),  
('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream'); 

Output
INSERT 0 5 

Khi lệnh đó hoàn tất thành công, bạn đã hoàn tất việc cài đặt database của bạn . Tiếp theo, ta sẽ xem xét cấu trúc lệnh cơ bản của các truy vấn SELECT .

Hiểu câu lệnh SELECT

Như đã đề cập trong phần giới thiệu, các truy vấn SQL hầu như luôn bắt đầu bằng SELECT . SELECT được sử dụng trong các truy vấn để chỉ định cột nào từ bảng sẽ được trả về trong tập kết quả. Các truy vấn hầu như luôn bao gồm FROM , được sử dụng để chỉ định bảng mà câu lệnh sẽ truy vấn.

Nói chung, các truy vấn SQL tuân theo cú pháp sau:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply; 

Ví dụ, câu lệnh sau sẽ trả về toàn bộ cột name từ bảng dinners :

SELECT name FROM dinners; 

Output
  name    ---------  Dolly  Etta  Irma  Barbara  Gladys (5 rows) 

Bạn có thể chọn nhiều cột từ cùng một bảng bằng cách tách tên của chúng bằng dấu phẩy, như sau:

SELECT name, birthdate FROM dinners; 

Output
  name   | birthdate   ---------+------------  Dolly   | 1946-01-19  Etta    | 1938-01-25  Irma    | 1941-02-18  Barbara | 1948-12-25  Gladys  | 1944-05-28 (5 rows) 

Thay vì đặt tên cho một cột hoặc tập hợp các cột cụ thể, bạn có thể tuân theo toán tử SELECT với dấu hoa thị ( * ) đóng role là trình giữ chỗ đại diện cho tất cả các cột trong bảng. Lệnh sau trả về mọi cột từ bảng tourneys :

SELECT * FROM tourneys; 

Output
  name   | wins | best | size  ---------+------+------+------  Dolly   |    7 |  245 |  8.5  Etta    |    4 |  283 |    9  Irma    |    9 |  266 |    7  Barbara |    2 |  197 |  7.5  Gladys  |   13 |  273 |    8 (5 rows) 

WHERE được sử dụng trong các truy vấn để lọc các bản ghi đáp ứng một điều kiện cụ thể và bất kỳ hàng nào không đáp ứng điều kiện đó sẽ bị loại khỏi kết quả. WHERE thường tuân theo cú pháp sau:

. . . WHERE column_name comparison_operator value 

Toán tử so sánh trong WHERE xác định cách cột được chỉ định sẽ được so sánh với giá trị. Dưới đây là một số toán tử so sánh SQL phổ biến:

Nhà điều hành	Những gì nó làm
=	kiểm tra sự bình đẳng
!=	kiểm tra sự bất bình đẳng
<	kiểm tra cho ít hơn
>	kiểm tra lớn hơn
<=	kiểm tra nhỏ hơn hoặc bằng
>=	các bài kiểm tra lớn hơn hoặc bằng
BETWEEN	kiểm tra xem một giá trị có nằm trong một phạm vi nhất định hay không
IN	kiểm tra xem giá trị của một hàng có nằm trong tập hợp các giá trị được chỉ định hay không
EXISTS	kiểm tra xem các hàng có tồn tại hay không, với các điều kiện cụ thể
LIKE	kiểm tra xem một giá trị có trùng với một chuỗi được chỉ định hay không
IS NULL	kiểm tra giá trị NULL
IS NOT NULL	kiểm tra tất cả các giá trị khác với NULL

Ví dụ: nếu bạn muốn tìm cỡ giày của Irma, bạn có thể sử dụng truy vấn sau:

SELECT size FROM tourneys WHERE name = 'Irma'; 

Output
 size  ------     7 (1 row) 

SQL cho phép sử dụng các ký tự đại diện và những ký tự này đặc biệt tiện dụng khi được sử dụng trong WHERE . Dấu phần trăm ( % ) đại diện cho không hoặc nhiều ký tự không xác định và dấu gạch dưới ( _ ) đại diện cho một ký tự không xác định. Những điều này hữu ích nếu bạn đang cố gắng tìm một mục cụ thể trong một bảng, nhưng không chắc chắn về mục nhập đó chính xác là gì. Để minh họa, giả sử rằng bạn đã quên lời đề nghị yêu thích của một vài người bạn của bạn , nhưng bạn chắc chắn rằng lời đề nghị cụ thể này bắt đầu bằng chữ “t”. Bạn có thể tìm thấy tên của nó bằng cách chạy truy vấn sau:

SELECT entree FROM dinners WHERE entree LIKE 't%'; 

Output
 entree   -------  tofu  tofu (2 rows) 

Dựa trên kết quả ở trên, ta thấy rằng món ăn mà ta đã quên là tofu .

Có thể đôi khi bạn đang làm việc với database có các cột hoặc bảng có tên tương đối dài hoặc khó đọc. Trong những trường hợp này, bạn có thể làm cho những tên này dễ đọc hơn bằng cách tạo một alias với từ khóa AS . Các alias được tạo bằng AS là tạm thời và chỉ tồn tại trong thời gian truy vấn mà chúng được tạo:

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners; 

Output
    n    |     b      |     d      ---------+------------+-----------  Dolly   | 1946-01-19 | cake  Etta    | 1938-01-25 | ice cream  Irma    | 1941-02-18 | cake  Barbara | 1948-12-25 | ice cream  Gladys  | 1944-05-28 | ice cream (5 rows) 

Ở đây, ta đã yêu cầu SQL hiển thị cột name là n , cột birthdate là b và cột dessert là d .

Các ví dụ mà ta đã xem qua cho đến thời điểm này bao gồm một số từ khóa và mệnh đề được sử dụng thường xuyên hơn trong các truy vấn SQL. Những điều này hữu ích cho các truy vấn cơ bản, nhưng chúng không hữu ích nếu bạn đang cố gắng thực hiện một phép tính hoặc lấy một giá trị vô hướng (một giá trị duy nhất, trái ngược với một tập hợp nhiều giá trị khác nhau) dựa trên dữ liệu . Đây là lúc các hàm tổng hợp phát huy tác dụng.

Chức năng tổng hợp

Thông thường, khi làm việc với dữ liệu, bạn không nhất thiết muốn xem dữ liệu đó. Đúng hơn, bạn muốn thông tin về dữ liệu. Cú pháp SQL bao gồm một số hàm cho phép bạn diễn giải hoặc chạy các phép tính trên dữ liệu của bạn chỉ bằng cách đưa ra một truy vấn SELECT . Chúng được gọi là các hàm tổng hợp .

Hàm COUNT đếm và trả về số hàng phù hợp với một tiêu chí nhất định. Ví dụ: nếu bạn muốn biết có bao nhiêu người bạn của bạn thích đậu phụ cho bữa tiệc sinh nhật của họ, bạn có thể đưa ra truy vấn sau:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu'; 

Output
 count  -------      2 (1 row) 

Hàm AVG trả về giá trị trung bình (trung bình) của một cột. Sử dụng bảng ví dụ của ta , bạn có thể tìm thấy điểm tốt nhất trung bình giữa bạn bè của bạn với truy vấn này:

SELECT AVG(best) FROM tourneys; 

Output
  avg   -------  252.8 (1 row) 

SUM được sử dụng để tìm tổng cộng của một cột nhất định. Ví dụ: nếu bạn muốn xem bạn và bạn bè của bạn đã chơi bao nhiêu trận trong những năm qua, bạn có thể chạy truy vấn sau:

SELECT SUM(wins) FROM tourneys; 

Output
 sum  -----   35 (1 row) 

Lưu ý các hàm AVG và SUM sẽ chỉ hoạt động chính xác khi được sử dụng với dữ liệu số. Nếu bạn cố gắng sử dụng chúng trên dữ liệu không phải số, nó sẽ dẫn đến lỗi hoặc chỉ là 0 , tùy thuộc vào RDBMS bạn đang sử dụng:

SELECT SUM(entree) FROM dinners; 

Output
ERROR:  function sum(character varying) does not exist LINE 1: select sum(entree) from dinners;                ^ HINT:  No function matches the given name and argument types. You might need to add explicit type casts. 

MIN được sử dụng để tìm giá trị nhỏ nhất trong một cột được chỉ định. Bạn có thể sử dụng truy vấn này để xem kỷ lục chơi bowling tổng thể tồi tệ nhất cho đến nay là bao nhiêu (về số trận thắng):

SELECT MIN(wins) FROM tourneys; 

Output
 min  -----    2 (1 row) 

Tương tự, MAX được sử dụng để tìm giá trị số lớn nhất trong một cột nhất định. Truy vấn sau đây sẽ hiển thị thành tích bowling tổng thể tốt nhất:

SELECT MAX(wins) FROM tourneys; 

Output
 max  -----   13 (1 row) 

Không giống như SUM và AVG , hàm MIN và MAX được dùng cho cả kiểu dữ liệu số và chữ cái. Khi chạy trên cột chứa giá trị chuỗi, hàm MIN sẽ hiển thị giá trị đầu tiên theo thứ tự bảng chữ cái:

SELECT MIN(name) FROM dinners; 

Output
   min    ---------  Barbara (1 row) 

Tương tự như vậy, khi chạy trên cột chứa giá trị chuỗi, hàm MAX sẽ hiển thị giá trị cuối cùng theo thứ tự bảng chữ cái:

SELECT MAX(name) FROM dinners; 

Output
 max   ------  Irma (1 row) 

Hàm tổng hợp có nhiều công dụng ngoài những gì được mô tả trong phần này. Chúng đặc biệt hữu ích khi được sử dụng với mệnh đề GROUP BY , được đề cập trong phần tiếp theo cùng với một số mệnh đề truy vấn khác ảnh hưởng đến cách sắp xếp các tập kết quả.

Thao tác kết quả truy vấn

Ngoài WHERE đề FROM và WHERE , có một số mệnh đề khác được sử dụng để điều khiển kết quả của một truy vấn SELECT . Trong phần này, ta sẽ giải thích và cung cấp các ví dụ cho một số mệnh đề truy vấn thường được sử dụng hơn.

Một trong những mệnh đề truy vấn được sử dụng thường xuyên nhất, ngoài FROM và WHERE , là mệnh đề GROUP BY . Nó thường được sử dụng khi bạn đang thực hiện một hàm tổng hợp trên một cột, nhưng liên quan đến việc khớp các giá trị trong một cột khác.

Ví dụ: giả sử bạn muốn biết có bao nhiêu người bạn của bạn thích mỗi trong số ba lời mời mà bạn thực hiện. Bạn có thể tìm thấy thông tin này với truy vấn sau:

SELECT COUNT(name), entree FROM dinners GROUP BY entree; 

Output
 count | entree   -------+---------      1 | chicken      2 | steak      2 | tofu (3 rows) 

Mệnh đề ORDER BY được sử dụng để sắp xếp kết quả truy vấn. Theo mặc định, các giá trị số được sắp xếp theo thứ tự tăng dần và các giá trị văn bản được sắp xếp theo thứ tự bảng chữ cái. Để minh họa, truy vấn sau liệt kê các cột name và birthdate , nhưng sắp xếp kết quả theo ngày sinh:

SELECT name, birthdate FROM dinners ORDER BY birthdate; 

Output
  name   | birthdate   ---------+------------  Etta    | 1938-01-25  Irma    | 1941-02-18  Gladys  | 1944-05-28  Dolly   | 1946-01-19  Barbara | 1948-12-25 (5 rows) 

Lưu ý hành vi mặc định của ORDER BY là sắp xếp tập hợp kết quả theo thứ tự tăng dần. Để đảo ngược điều này và sắp xếp tập hợp kết quả theo thứ tự giảm dần, hãy đóng truy vấn bằng DESC :

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC; 

Output
  name   | birthdate   ---------+------------  Barbara | 1948-12-25  Dolly   | 1946-01-19  Gladys  | 1944-05-28  Irma    | 1941-02-18  Etta    | 1938-01-25 (5 rows) 

Như đã đề cập trước đây, WHERE được sử dụng để lọc kết quả dựa trên các điều kiện cụ thể. Tuy nhiên, nếu bạn sử dụng WHERE với một hàm tổng hợp, nó sẽ trả về lỗi, như trường hợp với nỗ lực sau để tìm mặt nào là yêu thích của ít nhất ba người bạn của bạn:

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3; 

Output
ERROR:  aggregate functions are not allowed in WHERE LINE 1: SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3... 

Mệnh đề HAVING đã được thêm vào SQL để cung cấp chức năng tương tự như WHERE trong khi cũng tương thích với các hàm tổng hợp. Thật hữu ích khi nghĩ về sự khác biệt giữa hai mệnh đề này là WHERE áp dụng cho các bản ghi cá nhân, trong khi HAVING áp dụng cho các bản ghi group . Vì vậy, bất kỳ khi nào bạn đưa ra mệnh đề HAVING , mệnh đề GROUP BY cũng phải có mặt.

Ví dụ sau là một nỗ lực khác để tìm món ăn phụ nào là món yêu thích của ít nhất ba người bạn của bạn, mặc dù ví dụ này sẽ trả về kết quả không có lỗi:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3; 

Output
 count | side   -------+-------      3 | fries (1 row) 

Hàm tổng hợp rất hữu ích để tóm tắt kết quả của một cột cụ thể trong một bảng nhất định. Tuy nhiên, có nhiều trường hợp cần truy vấn nội dung của nhiều hơn một bảng. Ta sẽ xem xét một số cách bạn có thể thực hiện việc này trong phần tiếp theo.

Truy vấn nhiều bảng

Thường xuyên hơn không, một database chứa nhiều bảng, mỗi bảng chứa các tập dữ liệu khác nhau. SQL cung cấp một số cách khác nhau để chạy một truy vấn trên nhiều bảng.

Mệnh đề JOIN được dùng để kết hợp các hàng từ hai hoặc nhiều bảng trong một kết quả truy vấn. Nó thực hiện điều này bằng cách tìm một cột liên quan giữa các bảng và sắp xếp kết quả một cách thích hợp trong kết quả .

SELECT bao gồm một mệnh đề JOIN thường tuân theo cú pháp sau:

SELECT table1.column1, table2.column2 
FROM table1 
JOIN table2 ON table1.related_column=table2.related_column; 

Lưu ý vì mệnh đề JOIN so sánh nội dung của nhiều hơn một bảng, ví dụ trước chỉ định bảng để chọn từng cột bằng cách đặt trước tên của cột với tên của bảng và dấu chấm. Bạn có thể chỉ định bảng nào nên chọn cột như thế này cho bất kỳ truy vấn nào, mặc dù không cần thiết khi chọn từ một bảng, như ta đã thực hiện trong các phần trước. Hãy xem qua một ví dụ bằng cách sử dụng dữ liệu mẫu của ta .

Hãy tưởng tượng rằng bạn muốn mua cho mỗi người bạn của bạn một đôi giày bowling làm quà sinh nhật. Vì thông tin về ngày sinh và kích cỡ giày của bạn bè được lưu trữ trong các bảng riêng biệt, bạn có thể truy vấn cả hai bảng riêng biệt sau đó so sánh kết quả từ mỗi bảng. Tuy nhiên, với mệnh đề JOIN , bạn có thể tìm thấy tất cả thông tin mình muốn chỉ với một truy vấn:

SELECT tourneys.name, tourneys.size, dinners.birthdate  
FROM tourneys  
JOIN dinners ON tourneys.name=dinners.name; 

Output
  name   | size | birthdate   ---------+------+------------  Dolly   |  8.5 | 1946-01-19  Etta    |    9 | 1938-01-25  Irma    |    7 | 1941-02-18  Barbara |  7.5 | 1948-12-25  Gladys  |    8 | 1944-05-28 (5 rows) 

Mệnh đề JOIN được sử dụng trong ví dụ này, không có bất kỳ đối số nào khác, là một mệnh đề JOIN bên trong . Điều này nghĩa là nó chọn tất cả các bản ghi có giá trị phù hợp trong cả hai bảng và in chúng vào tập kết quả, trong khi bất kỳ bản ghi nào không phù hợp sẽ bị loại trừ. Để minh họa cho ý tưởng này, hãy thêm một hàng mới vào mỗi bảng không có mục nhập tương ứng trong bảng kia:

INSERT INTO tourneys (name, wins, best, size)  
VALUES ('Bettye', '0', '193', '9'); 

INSERT INTO dinners (name, birthdate, entree, side, dessert)  
VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream'); 

Sau đó, chạy lại SELECT trước đó với mệnh đề JOIN :

SELECT tourneys.name, tourneys.size, dinners.birthdate  
FROM tourneys  
JOIN dinners ON tourneys.name=dinners.name; 

Output
  name   | size | birthdate   ---------+------+------------  Dolly   |  8.5 | 1946-01-19  Etta    |    9 | 1938-01-25  Irma    |    7 | 1941-02-18  Barbara |  7.5 | 1948-12-25  Gladys  |    8 | 1944-05-28 (5 rows) 

Lưu ý , bởi vì bảng tourneys không có mục nhập cho Lesley và bảng dinners không có mục nhập cho Bettye, các bản ghi đó không có trong kết quả này.

Tuy nhiên, có thể trả về tất cả các bản ghi từ một trong các bảng bằng mệnh đề JOIN bên ngoài . Các mệnh đề Outer JOIN được viết dưới dạng LEFT JOIN , RIGHT JOIN hoặc FULL JOIN .

Mệnh đề LEFT JOIN trả về tất cả các bản ghi từ bảng “bên trái” và chỉ các bản ghi phù hợp từ bảng bên phải. Trong ngữ cảnh của các phép nối bên ngoài, bảng bên trái là bảng được tham chiếu bởi mệnh đề FROM và bảng bên phải là bất kỳ bảng nào khác được tham chiếu sau câu lệnh JOIN .

Chạy lại truy vấn trước đó, nhưng lần này sử dụng mệnh đề LEFT JOIN :

SELECT tourneys.name, tourneys.size, dinners.birthdate  
FROM tourneys  
LEFT JOIN dinners ON tourneys.name=dinners.name; 

Lệnh này sẽ trả về mọi bản ghi từ bảng bên trái (trong trường hợp này là tourneys ) ngay cả khi nó không có bản ghi tương ứng trong bảng bên phải. Bất kỳ lúc nào không có bản ghi phù hợp từ bảng bên phải, nó được trả về dưới dạng giá trị trống hoặc NULL , tùy thuộc vào RDBMS của bạn:

Output
  name   | size | birthdate   ---------+------+------------  Dolly   |  8.5 | 1946-01-19  Etta    |    9 | 1938-01-25  Irma    |    7 | 1941-02-18  Barbara |  7.5 | 1948-12-25  Gladys  |    8 | 1944-05-28  Bettye  |    9 |  (6 rows) 

Bây giờ chạy lại truy vấn, lần này với mệnh đề RIGHT JOIN :

SELECT tourneys.name, tourneys.size, dinners.birthdate  
FROM tourneys  
RIGHT JOIN dinners ON tourneys.name=dinners.name; 

Thao tác này sẽ trả về tất cả các bản ghi từ bảng bên phải ( dinners ). Vì ngày sinh của Lesley được ghi trong bảng bên phải, nhưng không có hàng tương ứng cho cô ấy trong bảng bên trái, các cột name và size sẽ trả về dưới dạng giá trị trống trong hàng đó:

Output
  name   | size | birthdate   ---------+------+------------  Dolly   |  8.5 | 1946-01-19  Etta    |    9 | 1938-01-25  Irma    |    7 | 1941-02-18  Barbara |  7.5 | 1948-12-25  Gladys  |    8 | 1944-05-28          |      | 1946-05-02 (6 rows) 

Lưu ý phép nối trái và phải có thể được viết dưới dạng LEFT OUTER JOIN hoặc RIGHT OUTER JOIN , mặc dù phần OUTER của mệnh đề được ngụ ý. Tương tự như vậy, chỉ định INNER JOIN sẽ tạo ra kết quả tương tự như chỉ viết JOIN .

Có một điều khoản tham gia thứ tư được gọi là FULL JOIN có sẵn cho một số bản phân phối RDBMS, bao gồm cả PostgreSQL. FULL JOIN sẽ trả về tất cả các bản ghi từ mỗi bảng, bao gồm mọi giá trị rỗng:

SELECT tourneys.name, tourneys.size, dinners.birthdate  
FROM tourneys  
FULL JOIN dinners ON tourneys.name=dinners.name; 

Output
  name   | size | birthdate   ---------+------+------------  Dolly   |  8.5 | 1946-01-19  Etta    |    9 | 1938-01-25  Irma    |    7 | 1941-02-18  Barbara |  7.5 | 1948-12-25  Gladys  |    8 | 1944-05-28  Bettye  |    9 |           |      | 1946-05-02 (7 rows) 

Lưu ý: Khi viết bài này, mệnh đề FULL JOIN không được hỗ trợ bởi MySQL hoặc MariaDB.

Để thay thế cho việc sử dụng FULL JOIN để truy vấn tất cả các bản ghi từ nhiều bảng, bạn có thể sử dụng mệnh đề UNION .

Toán tử UNION hoạt động hơi khác so với mệnh đề JOIN : thay vì in kết quả từ nhiều bảng dưới dạng cột duy nhất bằng cách sử dụng một SELECT , UNION kết hợp kết quả của hai SELECT thành một cột duy nhất.

Để minh họa, hãy chạy truy vấn sau:

SELECT name FROM tourneys UNION SELECT name FROM dinners; 

Truy vấn này sẽ xóa mọi mục nhập trùng lặp, đây là hành vi mặc định của UNION tử UNION :

Output
  name    ---------  Irma  Etta  Bettye  Gladys  Barbara  Lesley  Dolly (7 rows) 

Để trả lại tất cả các mục nhập (bao gồm cả các mục trùng lặp), hãy sử dụng UNION ALL :

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners; 

Output
  name    ---------  Dolly  Etta  Irma  Barbara  Gladys  Bettye  Dolly  Etta  Irma  Barbara  Gladys  Lesley (12 rows) 

Tên và số cột trong bảng kết quả phản ánh tên và số cột được truy vấn bởi SELECT đầu tiên. Lưu ý khi sử dụng UNION để truy vấn nhiều cột từ nhiều bảng, mỗi SELECT phải truy vấn cùng một số cột, các cột tương ứng phải có kiểu dữ liệu tương tự và các cột trong mỗi SELECT phải theo cùng một thứ tự. Ví dụ sau cho thấy điều gì có thể dẫn đến nếu bạn sử dụng mệnh đề UNION trên hai SELECT truy vấn một số cột khác nhau:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys; 

Output
ERROR:  each UNION query must have the same number of columns LINE 1: SELECT name FROM dinners UNION SELECT name, wins FROM tourne... 

Một cách khác để truy vấn nhiều bảng là sử dụng các truy vấn con . Truy vấn con ( còn gọi là truy vấn nội bộ hoặc truy vấn lồng nhau ) là các truy vấn nằm trong một truy vấn khác. Những điều này hữu ích trong trường hợp bạn đang cố gắng lọc kết quả của một truy vấn so với kết quả của một hàm tổng hợp riêng biệt.

Để minh họa cho ý tưởng này, giả sử bạn muốn biết ai trong số những người bạn của bạn đã thắng nhiều trận hơn Barbara. Thay vì truy vấn xem Barbara đã thắng bao nhiêu trận rồi chạy một truy vấn khác để xem ai đã thắng nhiều trận hơn thế, bạn có thể tính toán cả hai chỉ với một truy vấn:

SELECT name, wins FROM tourneys  
WHERE wins > ( 
SELECT wins FROM tourneys WHERE name = 'Barbara' 
); 

Output
  name  | wins  --------+------  Dolly  |    7  Etta   |    4  Irma   |    9  Gladys |   13 (4 rows) 

Truy vấn con trong câu lệnh này chỉ được chạy một lần; nó chỉ cần tìm giá trị từ cột wins ở cùng hàng với Barbara trong cột name và dữ liệu được trả về bởi truy vấn con và truy vấn bên ngoài là độc lập với nhau. Tuy nhiên, có những trường hợp, trong đó truy vấn bên ngoài trước tiên phải đọc mọi hàng trong bảng và so sánh các giá trị đó với dữ liệu do truy vấn con trả về để trả về dữ liệu mong muốn. Trong trường hợp này, truy vấn con được gọi là truy vấn con tương quan .

Câu lệnh sau đây là một ví dụ về một truy vấn con tương quan. Truy vấn này nhằm tìm ra người bạn nào của bạn đã thắng nhiều trò chơi hơn mức trung bình của những người có cùng cỡ giày:

SELECT name, size FROM tourneys AS t  
WHERE wins > ( 
SELECT AVG(wins) FROM tourneys WHERE size = t.size 
); 

Để truy vấn hoàn thành, trước tiên nó phải thu thập các cột name và size từ truy vấn bên ngoài. Sau đó, nó so sánh từng hàng từ tập hợp kết quả đó với kết quả của truy vấn bên trong, xác định số trận thắng trung bình cho các cá nhân có cỡ giày giống nhau. Vì bạn chỉ có hai người bạn có cùng cỡ giày nên chỉ có thể có một hàng trong tập kết quả:

Output
 name | size  ------+------  Etta |    9 (1 row) 

Như đã đề cập trước đó, truy vấn con được dùng để truy vấn kết quả từ nhiều bảng. Để minh họa điều này bằng một ví dụ cuối cùng, giả sử bạn muốn tổ chức một bữa tối bất ngờ cho người ném bóng giỏi nhất mọi thời đại của group . Bạn có thể tìm thấy bạn bè nào của bạn có thành tích chơi bowling tốt nhất và trả lại bữa ăn yêu thích của họ bằng truy vấn sau:

SELECT name, entree, side, dessert  
FROM dinners  
WHERE name = (SELECT name FROM tourneys  
WHERE wins = (SELECT MAX(wins) FROM tourneys)); 

Output
  name  | entree | side  |  dessert   --------+--------+-------+-----------  Gladys | steak  | fries | ice cream (1 row) 

Lưu ý câu lệnh này không chỉ bao gồm một truy vấn con mà còn chứa một truy vấn con bên trong truy vấn con đó.

Kết luận

Phát hành truy vấn là một trong những tác vụ thường được thực hiện nhất trong lĩnh vực quản lý database . Có một số công cụ quản trị database , chẳng hạn như phpMyAdmin hoặc pgAdmin , cho phép bạn thực hiện các truy vấn và trực quan hóa kết quả, nhưng việc phát hành các SELECT từ dòng lệnh vẫn là một quy trình làm việc được thực hành rộng rãi cũng có thể cung cấp cho bạn khả năng kiểm soát tốt hơn.

Nếu bạn mới làm việc với SQL, ta khuyến khích bạn sử dụng Trang Cheat SQL của ta làm tài liệu tham khảo và để xem lại tài liệu PostgreSQL chính thức . Ngoài ra, nếu bạn muốn tìm hiểu thêm về SQL và database quan hệ, các hướng dẫn sau đây có thể bạn quan tâm:

• Hiểu database SQL và NoSQL và các mô hình database khác nhau
• Cách cài đặt bản sao lôgic với PostgreSQL 10 trên Ubuntu 18.04
• Cách bảo mật PostgreSQL chống lại các cuộc tấn công tự động

---

---

Các tin liên quan

Cách thiết lập bản sao lôgic với PostgreSQL 10 trên Ubuntu 18.04
2018-08-31
Cách di chuyển thư mục dữ liệu PostgreSQL đến vị trí mới trên Ubuntu 18.04
2018-07-13
Cách cài đặt và sử dụng PostgreSQL trên Ubuntu 18.04
2018-05-04
Cách sử dụng tìm kiếm toàn văn bản trong PostgreSQL trên Ubuntu 16.04
2017-06-15
Cách bảo mật PostgreSQL chống lại các cuộc tấn công tự động
2017-01-24
Cách sử dụng Postgresql với Ứng dụng Django của bạn trên Debian 8
2016-12-22
Cách di chuyển thư mục dữ liệu PostgreSQL đến vị trí mới trên Ubuntu 16.04
2016-07-27
Cách sử dụng PostgreSQL với Ứng dụng Django của bạn trên Ubuntu 16.04
2016-05-18
Cách cài đặt và sử dụng PostgreSQL trên Ubuntu 16.04
2016-05-04
Cách backup, khôi phục và di chuyển database PostgreSQL với Barman trên CentOS 7
2016-01-20