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

Cơ sở dữ liệu là một thành phần quan trọng của nhiều trang web và các ứng dụng, và có lúc 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 cơ sở dữ liệu quản lý là thực hành lấy dữ liệu từ cơ sở dữ liệu, cho dù đó là trên cơ sở đặc biệt hoặc một phần của một quá trình mà được mã hóa vào ứng dụng. Có một số cách để truy xuất thông tin từ cơ sở dữ liệu, nhưng một trong những phương pháp sử dụng phổ biến nhất được thực hiện thông qua đệ trình truy vấn thông qua dòng lệnh.

Trong các hệ thống quản lý cơ sở dữ liệu quan hệ, một truy vấn là bất kỳ lệnh được sử dụng để lấy dữ liệu từ một bảng. Trong cấu trúc ngôn ngữ truy vấn đi (SQL), truy vấn được hầu như luôn luôn được thực hiện bằng cách sử dụng các tuyên bố SELECT .

Trong hướng dẫn này, chúng tôi sẽ thảo luận về các cú pháp cơ bản của các truy vấn SQL cũng như một số các chức năng thường được thêm sử dụng và vận hành. Chúng tôi cũng sẽ thực hành làm cho các truy vấn SQL bằng cách sử dụng một số dữ liệu mẫu trong cơ sở dữ liệu PostgreSQL.

PostgreSQL, thường được rút ngắn để “Postgres,” là một hệ thống quản lý cơ sở dữ liệu quan hệ với một cách tiếp cận hướng đối tượng, có nghĩa là thông tin có thể được biểu diễn như là các đối tượng hay các lớp học trong lược đồ PostgreSQL. PostgreSQL gắn chặt chẽ với tiêu chuẩn SQL, 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 cơ sở dữ liệu quan hệ khác.

Điều kiện tiên quyết
Nói chung, các lệnh và các khái niệm trình bày trong hướng dẫn này có thể được sử dụng trên bất kỳ Linux dựa trên hệ điều hành đang chạy bất kỳ phần mềm cơ sở dữ liệu SQL. Tuy nhiên, nó đã được viết đặc biệt với một máy chủ Ubuntu 18,04 chạy PostgreSQL trong tâm trí. Để thiết lập này, bạn sẽ cần sau đây:

Một máy Ubuntu 18,04 với người dùng-gốc với sudo quyền. Điều này có thể được thiết lập bằng cách sử dụng của chúng tôi hướng dẫn ban đầu thiết lập máy chủ cho Ubuntu 18,04.
PostgreSQL được cài đặt trên máy tính. Để được trợ giúp thiết lập này lên, theo phần “Cài đặt PostgreSQL” của chúng tôi hướng dẫn trên làm thế nào để cài đặt và sử dụng PostgreSQL trên Ubuntu 18,04.
Điều này thiết lập tại chỗ, chúng tôi có thể bắt đầu hướng dẫn.

Tạo một cơ sở dữ liệu mẫu
Trước khi chúng tôi có thể bắt đầu thực hiện truy vấn trong SQL, chúng tôi sẽ lần đầu tiên tạo ra một cơ sở dữ liệu và một vài bảng, sau đó điền các bảng với một số dữ liệu mẫu. Điều này sẽ cho phép bạn để đạt được một số kinh nghiệm thực hành khi bạn bắt đầu thực hiện truy vấn sau này.

Đối với cơ sở dữ liệu mẫu chúng tôi sẽ sử dụng trong suốt hướng dẫn này, hãy tưởng tượng kịch bản sau đây:

Bạn và một số bạn bè của bạn tất cả kỷ niệm sinh nhật của bạn với nhau. Mỗi dịp đặc biệt, các thành viên của nhóm, đến hẻm bowling địa phương, tham gia vào một giải đấu thân thiện, và sau đó tất cả mọi người đứng đầu đến nơi của bạn nơi bạn chuẩn bị bữa ăn yêu thích của sinh nhật-người.

Bây giờ mà truyền thống này đã xảy ra trong một thời gian, bạn đã quyết định để bắt đầu theo dõi các bản ghi từ các giải đấu. Ngoài ra, để thực hiện kế hoạch bữa ăn tối khách sạn dễ dàng hơn, bạn quyết định để tạo một bản ghi ngày sinh nhật của bạn bè của họ yêu thích entrees, hai bên và món tráng miệng. Chứ không phải là giữ cho thông tin này trong sổ kế toán vật lý, bạn quyết định để thực hiện các kỹ năng cơ sở dữ liệu của bạn bằng cách ghi lại nó trong cơ sở dữ liệu PostgreSQL.

Để bắt đầu, mở một dấu nhắc PostgreSQL là siêu người dùng postgres của bạn:

sudo -u postgres psql
Lưu ý: Nếu bạn theo tất cả các bước của hướng dẫn, điều kiện tiên quyết về PostgreSQL cài đặt trên Ubuntu 18,04, bạn có thể đã cấu hình một vai trò mới cho cài đặt PostgreSQL. Trong trường hợp này, bạn có thể kết nối với prompt Postgres với lệnh sau, thay thế các sammy với tên người dùng của riêng bạn:

sudo -u sammy psql
Tiếp theo, tạo cơ sở dữ liệu bằng cách chạy:

CREATE DATABASE birthdays;
Sau đó chọn cơ sở dữ liệu này bằng cách gõ:

\c birthdays
Tiếp theo, tạo ra hai bảng trong cơ sở dữ liệu này. Chúng tôi sẽ sử dụng bảng đầu tiên để theo dõi các bản ghi của bạn bè ở chơi bowling. Lệnh sau sẽ tạo ra một bảng gọi là tourneys với cột cho name của mỗi người trong số bạn bè của bạn, số lượng các giải đấu, họ đã giành được (wins), điểm-thời gian best của họ, và những gì kích thước bowling Đánh giày họ mang (size):

CREATE TABLE tourneys (
name varchar(30),
wins real,
best real,
size real
);
Khi bạn chạy lệnh CREATE TABLE và điền nó với tiêu đề cột, bạn sẽ nhận được đầu ra sau đây:

Output
CREATE TABLE
Cư tourneys bàn 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 đầu ra sau đây:

Output
INSERT 0 5
Sau đó, tạo một bảng trong cơ sở dữ liệu tương tự mà chúng tôi sẽ sử dụng để lưu trữ thông tin về bạn bè của bạn sinh nhật yêu thích bữa ăn. Lệnh sau 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è của bạn, của birthdate, yêu thích của họ entree, món ăn ưa thích side của họ và yêu thích của họ dessert:

CREATE TABLE dinners (
name varchar(30),
birthdate date,
entree varchar(30),
side varchar(30),
dessert varchar(30)
);
Tương tự như vậy cho bảng này, bạn sẽ nhận được thông tin phản hồi để xác minh rằng các bảng đã được tạo ra:

Output
CREATE TABLE
Đưa bảng này với một số dữ liệu của 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
Một khi lệnh hoàn thành thành công, bạn đã hoàn tất việc thiết lập cơ sở dữ liệu của bạn. Tiếp theo, chúng tôi sẽ đi qua các cấu trúc cơ bản lệnh truy vấn SELECT .

Sự hiểu biết chọn câu
Như đã đề cập trong phần giới thiệu, truy vấn SQL hầu như luôn luôn bắt đầu với tuyên bố SELECT . SELECT được sử dụng trong các truy vấn để xác định những cột bảng nên được trả lại trong thiết lập kết quả. Truy vấn cũng hầu như luôn luôn bao gồm các FROM, mà được sử dụng để xác định bảng mà tuyên bố sẽ truy vấn.

Nói chung, truy vấn SQL theo cú pháp này:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;
Ví dụ, các tuyên bố sau sẽ trả lại toàn bộ name cột từ dinners bàn:

SELECT name FROM dinners;
Output
name
———
Dolly
Etta
Irma
Barbara
Gladys
(5 rows)
Bạn có thể chọn nhiều cột cùng một bảng bằng cách tách tên bằng dấu phẩy, như thế này:

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 một cột cụ thể hoặc tập hợp các cột, bạn có thể làm theo các nhà điều hành SELECT với một dấu sao (**) phục vụ như một giữ chỗ đại diện cho tất cả các cột trong một bảng. Lệnh 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 hồ sơ đáp ứng các điều kiện quy định, và bất kỳ hàng nào không đáp ứng các điều kiện đó được loại bỏ từ kết quả. Một điều khoản WHERE thường sau cú pháp này:

. . . WHERE column_name comparison_operator value
Các nhà điều hành so sánh ở một WHERE điều khoản định nghĩa như thế nào so sánh các cột được chỉ định chống lại các giá trị. Dưới đây là một số nhà khai thác phổ biến so sánh SQL:

Nhà điều hành Những gì nó làm
= Các xét nghiệm cho sự bình đẳng
!= Các xét nghiệm cho bất đẳng thức
< thử nghiệm cho ít hơn-hơn > xét nghiệm cho lớn hơn-hơn
<= Các xét nghiệm cho ít hơn-hơn hoặc bình đẳng để >= xét nghiệm cho lớn hơn-hơn hoặc bình đẳng để
BETWEEN kiểm tra xem một giá trị nằm trong một phạm vi nhất định
IN thử nghiệm cho dù giá trị của một hàng được chứa trong một tập hợp các giá trị được chỉ định
EXISTS thử nghiệm cho dù hàng tồn tại, được đưa ra các điều kiện quy định
LIKE kiểm tra xem một giá trị phù hợp với chuỗi chỉ định
IS NULL Các xét nghiệm cho các giá trị NULL
IS NOT NULL thử nghiệm cho tất cả các giá trị khác hơn là 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 đây:

SELECT size FROM tourneys WHERE name = ‘Irma’;
Output
size
——
7
(1 row)
SQL cho phép sử dụng ký tự đại diện, và đây là những đặc biệt tiện dụng khi sử dụng ở WHERE mệnh đề. Dấu hiệu tỷ lệ phần trăm (%) đại diện cho số không hay nhiều ký tự không rõ, và dấu gạch dưới (_) đại diện cho một ký tự duy nhất không biết. Đây là hữu ích nếu bạn đang cố gắng để tìm thấy một mục cụ thể trong bảng, nhưng không chắc chắn về điều gì đó là chính xác. Để minh họa, giả sử rằng bạn đã quên đi vào yêu thích của một vài trong số bạn bè của bạn, nhưng bạn chắc chắn đi vào cụ thể này bắt đầu với một “t”. Bạn có thể tìm thấy tên của mình bằng cách chạy truy vấn sau đây:

SELECT entree FROM dinners WHERE entree LIKE ‘t%’;
Output
entree
——-
tofu
tofu
(2 rows)
Dựa trên đầu ra ở trên, chúng ta thấy rằng đi vào chúng ta đã quên tofu.

Có thể có lần khi bạn đang làm việc với cơ sở dữ liệu có cột hoặc bảng với những cái tên khá 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 dễ đọc hơn bằng cách tạo ra một bí danh AS từ khoá. Bí danh tạo với AS là tạm thời và chỉ tồn tại trong suốt thời gian của các truy vấn mà họ đang tạo ra:

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, chúng tôi đã nói với SQL để hiển thị các cột name là n, cột birthdate như bvà cột dessert là d.

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

Tổng hợp các chức năng
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 riêng của mình. Thay vào đó, bạn muốn thông tin về dữ liệu. Cú pháp SQL bao gồm một số chức năng cho phép bạn giải thích hoặc chạy tính toán trên dữ liệu của bạn chỉ bằng cách phát hành một truy vấn SELECT . Chúng được gọi là chức năng tổng hợp.

Hàm COUNT đếm và trả lại 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 làm thế nào nhiều bạn bè của bạn thích đậu phụ cho đi vào ngày sinh nhật của họ, bạn có thể phát hành các truy vấn này:

SELECT COUNT(entree) FROM dinners WHERE entree = ‘tofu’;
Output
count
——-
2
(1 row)
AVG hàm 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 chúng tôi, bạn có thể tìm thấy tốt nhất điểm trung bình giữa các 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 thấy số tiền của cột được đưa ra. Ví dụ, nếu bạn muốn xem bao nhiêu trò chơi bạn và bạn bè đã bowled hơn những năm qua, bạn có thể chạy các truy vấn này:

SELECT SUM(wins) FROM tourneys;
Output
sum
—–
35
(1 row)
Lưu ý rằng AVG và SUM các chức năng chỉ làm việc một cách chính xác khi sử dụng với các dữ liệu số. Nếu bạn cố gắng sử dụng chúng trên không số dữ liệu, nó sẽ gây ra một lỗi hoặc chỉ 0, tùy thuộc vào RDBMS mà 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 các truy vấn để xem những gì tồi tệ nhất tổng thể bowling ghi là cho đến nay (về số 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 được đưa ra. Truy vấn sau đây sẽ hiển thị tốt nhất tổng thể bowling ghi lại:

SELECT MAX(wins) FROM tourneys;
Output
max
—–
13
(1 row)
Không giống như SUM và AVG, hàm MIN và MAX có thể được sử dụng cho cả hai loại dữ liệu số và chữ. Khi chạy trên một cột có chứa giá trị chuỗi, hàm MIN sẽ hiển thị các giá trị đầu tiên theo thứ tự ABC:

SELECT MIN(name) FROM dinners;
Output
min
———
Barbara
(1 row)
Tương tự như vậy, khi chạy trên một cột có chứa giá trị chuỗi, hàm MAX sẽ hiển thị giá trị cuối theo thứ tự ABC:

SELECT MAX(name) FROM dinners;
Output
max
——
Irma
(1 row)
Tổng hợp các chức năng có nhiều công dụng vượt ra ngoài những gì đã được mô tả trong phần này. Họ là đặc biệt hữu ích khi sử dụng với các GROUP BY mệnh đề, mà bao gồm trong phần tiếp theo cùng với một số khác mệnh đề truy vấn mà ảnh hưởng đến cách bộ kết quả đều được sắp xếp.

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

Một trong những khoản truy vấn thường xuyên nhất sử dụng 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 chức năng tổng hợp trên một cột, nhưng liên quan đến kết hợp các giá trị khác.

Ví dụ, giả sử bạn muốn biết làm thế nào nhiều bạn bè của bạn thích mỗi của các ba các entrees 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 đây:

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 dùng để sắp xếp các kết quả truy vấn. Theo mặc định, giá trị số được sắp xếp thứ tự tăng dần, và giá trị văn bản được sắp xếp theo thứ tự chữ cái. Để minh họa, truy vấn sau đây 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)
Nhận thấy rằng hành vi mặc định của ORDER BY sắp xếp các kết quả thiết lập thứ tự tăng dần. Ngược lại điều này và có thiết lập kết quả được sắp xếp theo thứ tự giảm dần, đóng các truy vấn với 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 đó, WHERE các khoản được sử dụng để lọc kết quả dựa trên điều kiện cụ thể. Tuy nhiên, nếu bạn sử dụng điều khoản WHERE với một chức năng tổng hợp, nó sẽ trả về một lỗi, như trường hợp với nỗ lực sau đây tìm thấy bên đó là yêu thích của ít nhất ba trong số bạn bè 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…
HAVING khoản đã được thêm vào SQL để cung cấp chức năng tương tự như WHERE các khoản trong khi đang tương thích với các chức năng tổng hợp. Nó là hữu ích để suy nghĩ về sự khác biệt giữa những mệnh đề hai như là WHERE áp dụng cho hồ sơ cá nhân, trong khi HAVING áp dụng cho nhóm hồ sơ. Để kết thúc này, bất kỳ thời điểm nào bạn vấn đề một khoản HAVING , GROUP BY khoản phải cũng có mặt.

Ví dụ sau là một nỗ lực để tìm thấy những món ăn phụ là yêu thích của ít nhất ba trong số bạn bè của bạn, mặc dù điều này sẽ trả về một kết quả mà 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)
Tổng hợp các chức năng có ích đối với tổng kết các kết quả của một cột cụ thể trong một bảng được đưa ra. Tuy nhiên, có rất nhiều trường hợp nó là cần thiết để truy vấn các nội dung của nhiều hơn một bảng. Chúng tôi sẽ đi qua một số cách bạn có thể làm điều này trong phần tiếp theo.

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

Điều khoản JOIN có thể được sử dụng để kết hợp hàng từ hai hoặc nhiều bảng trong 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 có liên quan giữa các bảng và sắp xếp các kết quả phù hợp đầu ra.

SELECT báo cáo bao gồm một điều khoản JOIN thường thực hiện theo cú pháp này:

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.related_column=table2.related_column;
Lưu ý rằng bởi vì JOIN mệnh đề so sánh nội dung của nhiều hơn một bảng, ví dụ trước chỉ định mà bảng để chọn mỗi cột từ ngay trước tên các cột với tên của các bảng và một khoảng thời gian. Bạn có thể chỉ định mà bảng một cột nên được lựa chọn từ như thế này cho bất kỳ truy vấn, mặc dù nó không phải là cần thiết khi lựa chọn từ một bảng duy nhất, như chúng tôi đã 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 chúng tôi.

Hãy tưởng tượng rằng bạn muốn mua cho mỗi người trong số bạn bè của bạn một đôi giày bowling như một món quà sinh nhật. Bởi vì các thông tin về bạn bè của bạn ngày và giày cỡ được tổ chức trong 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. Với một điều khoản JOIN , mặc dù, bạn có thể tìm thấy tất cả các thông tin mà bạn muốn với một truy vấn duy nhất:

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)
JOIN điều khoản được sử dụng trong ví dụ này, mà không có bất kỳ đối số khác, là một mệnh đề JOIN bên trong . Điều này có nghĩa rằng 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 cho việc thiết lập kết quả, trong khi bất kỳ hồ sơ không phù hợp được loại trừ. Để minh họa ý tưởng này, hãy thêm dòng mới mỗi bảng mà không có một mục nhập tương ứng khác:

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 những tuyên bố SELECT trước với các điều khoản 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)
Chú ý rằng, bởi vì bảng tourneys có không có mục nhập cho Lesley và dinners bàn có không có mục nhập cho Bettye, các hồ sơ này vắng mặt từ này ra.

Nó có thể, mặc dù, để trở về tất cả các bản ghi từ một bảng bằng cách sử dụng một mệnh đề JOIN bên ngoài . Ngoài JOIN mệnh đề được viết như là LEFT JOIN, RIGHT JOIN, hoặc FULL JOIN.

Một điều khoản LEFT JOIN trả lại tất cả các bản ghi từ bảng “trái” và chỉ có các hồ sơ phù hợp từ bảng bên phải. Trong bối cảnh bên ngoài tham gia, bảng bên trái là một tham chiếu của các FROM mệnh đề, và bảng bên phải là bất kỳ bảng tham chiếu sau khi tuyên bố JOIN .

Chạy truy vấn trước đó một lần nữa, nhưng thời gian này sử dụng một khoản 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ở lại mỗi bản ghi từ các bảng còn lại (trong trường hợp này, tourneys) ngay cả khi nó không có một bản ghi tương ứng trong bảng bên phải. Bất kỳ thời gian không có một hồ sơ phù hợp từ bảng bên phải, nó trở lại như là một 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 truy vấn một lần nữa, lần này với một khoản RIGHT JOIN :

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
RIGHT JOIN dinners ON tourneys.name=dinners.name;
Điều này sẽ trả về tất cả các bản ghi từ bảng bên phải (dinners). Bởi vì ngày sinh của Lesley được ghi lại 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 name và size cột sẽ trở lại như các giá trị trống ở 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 ý rằng trái và bên phải, tham gia có thể được viết dưới dạng LEFT OUTER JOIN hoặc RIGHT OUTER JOIN, mặc dù một phần OUTER của các mệnh đề là ngụ ý. Tương tự như vậy, chỉ định INNER JOIN sẽ cho ra kết quả tương tự như chỉ viết JOIN.

Đó là 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. Một FULL JOIN sẽ trở lại tất cả các bản ghi từ mỗi bảng, bao gồm bất kỳ giá trị null:

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 ý: Bài viết này, điều khoản FULL JOIN không được hỗ trợ bởi MySQL hoặc MariaDB.

Làm thế nào để 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 các khoản UNION .

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

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

SELECT name FROM tourneys UNION SELECT name FROM dinners;
Truy vấn này sẽ loại bỏ bất kỳ mục trùng lặp, mà là hành vi mặc định của các nhà điều hành UNION :

Output
name
———
Irma
Etta
Bettye
Gladys
Barbara
Lesley
Dolly
(7 rows)
Để trở về tất cả các mục (bao gồm bản sao) sử dụng các nhà điều hành 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ố lượng các cột trong bảng kết quả phản ánh tên và số lượng các cột truy vấn bằng tuyên bố SELECT đầu tiên. Lưu ý rằng khi sử dụng các UNION để truy vấn nhiều cột từ nhiều hơn một bảng, từng tuyên bố SELECT phải truy vấn cùng số lượng cột, cột tương ứng phải có tương tự như các loại dữ liệu, và các cột trong mỗi SELECT báo cáo phải theo thứ tự. Ví dụ sau cho thấy những gì có thể gây ra nếu bạn sử dụng một mệnh đề UNION trên hai SELECT câu lệnh 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à bằng cách sử dụng subqueries. Subqueries (còn được gọi là bên trong hoặc truy vấn lồng nhau) đang truy vấn kèm theo trong một truy vấn. Đây là những hữu ích trong trường hợp mà bạn đang cố gắng để lọc các kết quả của một truy vấn đối với kết quả của một chức năng tổng hợp riêng biệt.

Để minh họa ý tưởng này, nói rằng bạn muốn biết mà bạn bè của bạn đã chiến thắng các trận đấu nhiều hơn so với Barbara. Chứ không phải là truy vấn bao nhiêu phù hợp với Barbara đã giành được sau đó chạy một truy vấn để xem những người đã chiến thắng các trò chơi nhiều hơn, bạn có thể tính toán cả hai với một truy vấn duy nhất:

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)
Câu trong tuyên bố này được chạy chỉ một lần; nó chỉ dùng để tìm giá trị từ cột wins trong cùng hàng như Barbara trong cột name , và các dữ liệu trả về bởi các câu và truy vấn bên ngoài là độc lập với nhau. Có những trường hợp, mặc dù, nơi các truy vấn bên ngoài phải lần đầu tiên đọc mỗi hàng trong một bảng và so sánh các giá trị so với dữ liệu trả về bởi câu để trả lại dữ liệu mong muốn. Trong trường hợp này, câu này được gọi là một tương quan câu.

Các câu sau đây là một ví dụ về một câu tương quan. Truy vấn này tìm kiếm để tìm kiếm mà bạn bè của bạn đã chiến thắng trò chơi nhiều hơn so với mức trung bình cho những người có cùng một kích cỡ giày:

SELECT name, size FROM tourneys AS t
WHERE wins > (
SELECT AVG(wins) FROM tourneys WHERE size = t.size
);
Để cho các truy vấn để hoàn thành, nó trước tiên phải thu thập các cột name và size từ các truy vấn bên ngoài. Sau đó, nó so sánh mỗi hàng từ đó kết quả thiết lập chống lại các kết quả của các truy vấn bên trong, mà quyết định số thắng, Trung bình cho các cá nhân với kích thước giày giống hệt nhau. Bởi vì bạn chỉ có hai người bạn có cùng kích thước giày, có thể chỉ có một hàng trong thiết lập kết quả:

Output
name | size
——+——
Etta | 9
(1 row)
Như đã đề cập trước đó, subqueries có thể được sử dụng để truy vấn các kết quả từ nhiều bảng. Để minh họa điều này với một ví dụ cuối cùng, nói rằng bạn muốn ném một bữa ăn tối bất ngờ cho bowler tốt nhất mọi thời đại của nhóm. Bạn có thể tìm thấy bạn bè của bạn có tốt nhất bowling kỷ lục và trở về bữa ăn yêu thích của họ với truy vấn sau đây:

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)
Thông báo rằng tuyên bố này không chỉ bao gồm một câu, nhưng cũng chứa một câu trong câu đó.

Kết luận
Phát hành các truy vấn là một trong những công việc thường được thực hiện trong lĩnh vực quản lý cơ sở dữ liệu. Một số cơ sở dữ liệu công cụ quản lý, ví dụ như phpMyAdmin hoặc pgAdmin, cho phép bạn thực hiện các truy vấn và hình dung kết quả, nhưng vẫn phát hành báo cáo SELECT từ dòng lệnh là một-thực hành rộng rãi quy trình làm việc có thể cũng cung cấp cho bạn kiểm soát tốt hơn.

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

Tìm hiểu về SQL và cơ sở dữ liệu NoSQL và các mô hình cơ sở dữ liệu khác nhau
Làm thế nào để thiết lập các bản sao hợp lý với PostgreSQL 10 trên Ubuntu 18,04
Làm thế nào để Secure PostgreSQL chống lại cuộc tấn công tự động