Создание запросов к таблицам в PostgreSQL

Amber

4 года назад

Базы данных являются ключевым компонентом многих веб-сайтов и приложений и лежат в основе хранения и обмена данными в Интернете. Одним из наиболее важных аспектов управления базой данных является процедура извлечения данных из БД, будь то разовое обращение или часть процесса, закодированного в приложении. Существует несколько способов извлечения информации из базы данных, но один из наиболее часто используемых методов – это отправка запросов через командную строку.

В системах управления реляционными базами данных запрос – это любая команда, используемая для извлечения данных из таблицы. В языке структурированных запросов (SQL) запросы почти всегда выполняются с помощью оператора SELECT.

В этом мануале мы обсудим основной синтаксис SQL-запросов, а также некоторые из наиболее часто используемых функций и операторов. Мы также попрактикуемся в создании запросов SQL на тестовых данных в БД PostgreSQL.

PostgreSQL (часто просто «Postgres») – это систему управления реляционными базами данных с объектно-ориентированным подходом (это означает, что в схемах PostgreSQL информация может быть представлена в виде объектов или классов). PostgreSQL тесно связан со стандартным SQL, хотя он также включает некоторые функции, которых нет в других реляционных СУБД.

Требования

В общем, команды и понятия, представленные в этом мануале, могут использоваться в любой операционной системе на базе Linux и в любом программном обеспечении SQL. Однако мануал был написан специально для сервера Ubuntu 18.04 и PostgreSQL. Для работы вам понадобится:

Сервер Ubuntu 18.04 с пользователем sudo. Начальная настройка сервера описана здесь.
Предварительно установленная система PostgreSQL. Инструкции по установке можно найти в мануале Установка и использование PostgreSQL в Ubuntu 18.04.

Создание тестовой базы данных

Прежде чем начать работу с запросами в SQL, создайте тестовую базу данных и пару таблиц, а затем заполните эти таблицы условными данными. Это позволит вам получить практический опыт, выполняя предложенные в мануале примеры.

В качестве БД, которую мы будем использовать в этом руководстве, мы предлагаем следующий сценарий.

Предположим, вы и несколько ваших подруг всегда празднуете дни рождения вместе. Каждый раз все члены группы направляются в местный боулинг на дружеский турнир, а затем идут к вам, где вы готовите любимое блюдо для именинницы.

Теперь, когда это стало традицией, вы решили начать отслеживать результаты этих турниров. Кроме того, чтобы упростить планирование обедов, вы хотите создать запись о днях рождения ваших подруг и их любимых блюдах, гарнирах и десертах. Вместо того чтобы хранить эту информацию в физической книге, вы можете использовать свои навыки работы с СУБД PostgreSQL.

Для начала откройте командную строку PostgreSQL:

sudo -u postgres psql

Примечание: Если вы полностью выполнили мануал по установке PostgreSQL в Ubuntu 18.04, вероятно, вы настроили новую роль. В этом случае вы можете подключиться к командной строке Postgres с помощью следующей команды, заменив 8host своим именем пользователя:

sudo -u 8host psql

Создайте БД:

CREATE DATABASE birthdays;

Выберите эту БД:

\c birthdays

Затем создайте в этой базе данных две таблицы. Первую таблицу можно использовать, чтобы отслеживать результаты в боулинге. Следующая команда создаст таблицу под названием tourneys. В ней будут столбцы с именами подруг (name), количество выигранных ими турниров (wins), их лучшие результаты за все время (best) и размер ботинок для боулинга, которые они носят (size):

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

Запустив эту команду и заполнив ее заголовками столбцов, вы увидите такой вывод:

CREATE TABLE

Теперь добавьте в таблицу какие-нибудь данные:

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');

Команда вернет:

INSERT 0 5

После этого создайте еще одну таблицу в той же базе данных. Ее можно использовать для хранения информации о любимых блюдах подруг на день рождения. Следующая команда создает таблицу dinners, где будут столбцы с именами подруг, их датой рождения (birthdate), их любимым блюдом (entree), гарниром (side) и десертом (dessert):

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

После запуска команда выведет:

CREATE TABLE

Теперь заполните таблицу данными:

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'); INSERT 0 5

Теперь у вас есть данные, на которых можно потренироваться.

Оператор SELECT

Как упоминалось в начале статьи, SQL-запросы почти всегда начинаются с оператора SELECT. SELECT в запросах указывает, какие столбцы из таблицы должны нужно вернуть в наборе результатов. Запросы также почти всегда включают оператор FROM, который используется для указания таблицы, к которой нужно обратиться.

В общем SQL-запросы следуют такому синтаксису:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

Например, чтобы извлечь столбец name из таблицы dinners, нужен такой запрос:

SELECT name FROM dinners; name --------- Dolly Etta Irma Barbara Gladys (5 rows)

Вы также можете запрашивать несколько столбцов из одной таблицы, отделив их заголовки запятыми.

Вместо того чтобы называть конкретный столбец или набор столбцов, вы можете использовать оператор SELECT со звездочкой (*) – она служит заполнителем, представляющим все столбцы в таблице. Следующая команда отобразит все столбцы таблицы tourneys:

SELECT * FROM tourneys; 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 используется в запросах для фильтрации записей, которые удовлетворяют указанному условию. Все строки, которые не удовлетворяют этому условию, исключаются из результата. Оператор WHERE обычно использует следующий синтаксис:

. . . WHERE column_name comparison_operator value

Оператор сравнения в выражении WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:

Оператор	Действие
=	Равно
!=	Не равно
<	Меньше, чем
>	Больше, чем
<=	Меньше или равно
>=	Больше или равно
BETWEEN	проверяет, находится ли значение в заданном диапазоне
IN	проверяет, содержится ли значение строки в наборе указанных значений
EXISTS	проверяет, существуют ли строки при заданных условиях
LIKE	проверяет, соответствует ли значение указанной строке
IS NULL	Проверяет значения NULL
IS NOT NULL	Проверяет все значения, кроме NULL

Например, если вы хотите узнать размер обуви Ирмы, вы можете использовать следующий запрос:

SELECT size FROM tourneys WHERE name = 'Irma'; size ------ 7 (1 row)

SQL позволяет использовать подстановочных знаков, и это особенно удобно при работе с выражениями WHERE. Знак процента (%) представляют ноль или более неизвестных символов, а подчеркивания (_) представляют один неизвестный символ. Они полезны, если вы пытаетесь найти конкретную запись в таблице, но не знаете точно, что это за запись. Чтобы проиллюстрировать это, предположим, что вы забыли любимое блюдо нескольких своих подруг, но вы уверены, что это блюдо начинается на t. Вы можете найти его название с помощью запроса:

SELECT entree FROM dinners WHERE entree LIKE 't%'; entree ------- tofu tofu (2 rows)

Исходя из вышеприведенного вывода, это tofu.

Иногда приходится работать с базами данных, в которых есть столбцы или таблицы с относительно длинными или трудно читаемыми названиями. В этих случаях вы можете сделать эти имена более читабельными, создав псевдонимы – для этого есть ключевое слово AS. Псевдонимы, созданные с помощью AS, являются временными (они существуют только на время запроса, для которого они созданы):

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners; 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)

Как видите, теперь SQL отображает столбец name как n, столбец birthdate как b и dessert как d.

На данный момент мы рассмотрели некоторые наиболее часто используемые ключевые слова и предложения в запросах SQL. Они полезны для базовых запросов, но они не помогут, если вам нужно выполнить вычисление или получить скалярное значение (одно значение, а не набор из нескольких различных значений) на основе ваших данных. Здесь вам понадобятся агрегатные функции.

Агрегатные функции

Часто при работе с данными не нужно просматривать сами данные, а нужна информация о них. Синтаксис SQL включает в себя ряд функций, которые позволяют интерпретировать или выполнять вычисления на ваших данных через запрос SELECT. Такие функции называются агрегатными.

Функция COUNT считает и возвращает количество строк, соответствующих определенным критериям. Например, если вы хотите узнать, сколько ваших друзей предпочитают тофу, вы можете выполнить этот запрос:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu'; count ------- 2 (1 row)

Функция AVG возвращает среднее значение столбца. Например, вы можете найти средний лучший результат в турнире с помощью этого запроса:

SELECT AVG(best) FROM tourneys; avg ------- 252.8 (1 row)

SUM может найти общую сумму значений столбца. Например, если вы хотите посмотреть, сколько турниров вы провели за эти годы, вы можете выполнить этот запрос:

SELECT SUM(wins) FROM tourneys; sum ----- 35 (1 row)

Обратите внимание, функции AVG и SUM работают правильно только с числовыми данными. Если вы попытаетесь использовать их для нечисловых данных, это приведет к ошибке или к 0, в зависимости от того, какую СУБД вы используете.

SELECT SUM(entree) FROM dinners; 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 используется для поиска наименьшего значения в указанном столбце. Попробуйте использовать этот запрос, чтобы узнать наименьшее количество очков победителя в турнире за все годы:

SELECT MIN(wins) FROM tourneys; min ----- 2 (1 row)

Точно так же используется MAX – только эта функция ищет наибольшее числовое значение в данном столбце. Следующий запрос покажет лучший результат в турнирах за все годы:

SELECT MAX(wins) FROM tourneys; max ----- 13 (1 row)

В отличие от SUM и AVG, функции MIN и MAX могут использоваться как для числовых, так и для буквенных типов данных. При запуске в столбце со строковыми значениями функция MIN отображает первое значение в алфавитном порядке:

SELECT MIN(name) FROM dinners; min --------- Barbara (1 row)

Аналогично MAX покажет последнее значение в алфавитном порядке:

SELECT MAX(name) FROM dinners; max ------ Irma (1 row)

Агрегатные функции широко применяются в СУБД. Они особенно полезны в выражениях GROUP BY, которые мы рассмотрим в следующем разделе вместе с несколькими другими операторами сортировки наборов результатов.

Управление выводом запроса

В дополнение к FROM и WHERE есть несколько других операторов, которые используются для управления результатами запроса SELECT. В этом разделе мы объясним некоторые из наиболее часто используемых операторов и рассмотрим их на примерах.

Одним из наиболее часто используемых операторов, кроме FROM и WHERE, является GROUP BY. Обычно он используется, когда вы выполняете агрегатную функцию для одного столбца, но в отношении сопоставления значений в другом.

Например, вы хотите узнать, сколько друзей любит то или иное блюдо. Для этого вы можете использовать такой запрос:

SELECT COUNT(name), entree FROM dinners GROUP BY entree; count | entree ------+--------- 1 | chicken 2 | steak 2 | tofu (3 rows)

Оператор ORDER BY используется для сортировки результатов запроса. По умолчанию числовые значения сортируются в порядке возрастания, а текстовые значения сортируются в алфавитном порядке. Для примера в следующем запросе перечислены имена и даты рождения, результаты отсортированы по дате рождения:

По умолчанию ORDER BY сортирует результаты в порядке возрастания. Чтобы отсортировать их в обратном порядке, добавьте DESC:

Как упоминалось ранее, WHERE используется для фильтрации результатов на основе определенных условий. Однако если вы используете WHERE с агрегатной функцией, он вернет ошибку. Для примера попробуем выяснить, какой гарнир нравится как минимум трем подругам:

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

Оператор HAVING добавлен в SQL для выполнения функций, аналогичных WHERE, но совместимых с агрегатными функциями. Разница между этими двумя операторами в том, что WHERE применяется к отдельным записям, а HAVING – к групповым. Для этого при каждом выполнении HAVING также должен присутствовать оператор GROUP BY.

Следующий пример – еще одна попытка узнать, какой гарнир является любимым как минимум у трех из подруг. Этот запрос вернет результат без ошибок:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3; count | side ------+------- 3 | fries (1 row)

Агрегатные функции полезны для суммирования результатов определенного столбца в данной таблице. Однако во многих случаях необходимо запросить содержимое сразу нескольких таблиц. Мы рассмотрим несколько способов сделать это в следующем разделе.

Запрос данных из нескольких таблиц

Чаще всего база данных содержит несколько таблиц, каждая из которых содержит разные наборы данных. SQL предоставляет несколько способов выполнения одного запроса для нескольких таблиц.

Оператор JOIN можно использовать для объединения строк из двух или более таблиц в результате запроса. Это достигается путем нахождения одинакового столбца между таблицами и соответствующей сортировки результатов в выходных данных.

Выражение SELECT с оператором JOIN, как правило, работает по такому синтаксису:

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

Обратите внимание, поскольку оператор JOIN сравнивает содержимое нескольких таблиц, в предыдущем выражении указывается, из какой таблицы нужно выбрать каждый столбец: сначала указывается название таблицы, а через точку – название столбца. Вы можете указать, из какой таблицы должен быть выбран столбец, хотя это не обязательно при выборе из одной таблицы. Давайте рассмотрим пример.

Представьте, что вы хотите купить каждой подруге пару ботинок для боулинга в качестве подарка на день рождения. Поскольку информация о датах рождения и размерах обуви хранится в разных таблицах, вы можете запросить обе таблицы по отдельности, а затем сравнить результаты для каждой из них. Однако с помощью предложения JOIN вы можете найти всю необходимую информацию в рамках одного запроса:

Оператор JOIN, используемый в этом примере без каких-либо других аргументов, является внутренним JOIN. Это означает, что он выбирает в обеих таблицах все записи, у которых есть общее значение (в данном случае – имя), и выводит их в наборе результатов, в то время как все остальные записи исключаются. Чтобы проиллюстрировать эту функцию, давайте добавим в каждую таблицу новые строки, у которых не будет общего значения:

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');

А теперь повторите запрос:

Обратите внимание, что, поскольку в таблице tourneys нет записи для Lesley, а в таблице dinners нет записи для Bettye, эти записи отсутствуют в выходных данных.

Однако возможно вернуть все записи из одной из таблиц, используя внешнее соединение JOIN. Внешние JOIN записываются как LEFT JOIN, RIGHT JOIN или FULL JOIN.

Предложение LEFT JOIN возвращает все записи из левой таблицы и только совпадающие записи из правой таблицы. В контексте внешнего соединения левая таблица – это таблица, на которую ссылается FROM, а правая – любая другая таблица, на которую есть ссылка после оператора JOIN.

Выполните предыдущий запрос еще раз, но на этот раз используйте LEFT JOIN:

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

Эта команда выведет каждую запись из левой таблицы (в данном случае tourneys), даже если в правой таблице нет совпадающей записи. Каждый раз, когда в правой таблице нет подходящей записи, она возвращается как пустое значение или NULL, в зависимости от СУБД.

Повторите запрос с оператором RIGHT JOIN:

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

Это вернет все записи из правой таблицы (dinners). Поскольку дата рождения Лесли записана в правой таблице, но для нее нет соответствующей строки в левой таблице, в столбцах name и size в этой строке будут пустые значения:

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)

Обратите внимание, что левые и правые соединения могут быть записаны как LEFT OUTER JOIN или RIGHT OUTER JOIN, хотя OUTER и так подразумевается. Аналогично, INNER JOIN дает тот же результат, что и простой JOIN.

Для некоторых дистрибутивов СУБД, включая PostgreSQL, существует четвертое условие соединения, называемое FULL JOIN. FULL JOIN вернет все записи из каждой таблицы, включая все нулевые значения:

SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys FULL JOIN dinners ON tourneys.name=dinners.name; 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)

Примечание: На момент написания MySQL и MariaDB не поддерживают FULL JOIN.

В качестве альтернативы FULL JOIN для запроса всех записей из нескольких таблиц, вы можете использовать оператор UNION.

Оператор UNION работает немного иначе, чем JOIN: вместо вывода результатов из нескольких таблиц в виде уникальных столбцов с помощью одного оператора SELECT он объединяет результаты двух операторов SELECT в один столбец.

Рассмотрим такой запрос:

SELECT name FROM tourneys UNION SELECT name FROM dinners;

Он удалит все дублируемые записи, так как это поведение UNION по умолчанию.

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

Чтобы вывести все записи, включая повторы, используйте UNION ALL.

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners; name --------- Dolly Etta Irma Barbara Gladys Bettye Dolly Etta Irma Barbara Gladys Lesley (12 rows)

Значения и количество столбцов в результате отражают имя и количество столбцов, запрошенных первым оператором SELECT. Обратите внимание, что при использовании UNION для запроса нескольких столбцов из более, чем одной таблицы каждый оператор SELECT должен запрашивать одинаковое количество столбцов, соответствующие столбцы должны иметь одинаковые типы данных, а также идти в одном порядке в каждом операторе SELECT. В следующем примере показано, что может произойти, если вы используете предложение UNION для двух операторов SELECT, которые запрашивают разное количество столбцов:

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

Другой способ сделать запрос к нескольким таблицам – это подзапросы. Подзапросы (также называемые внутренними или вложенными запросами) – это запросы, заключенные в другой запрос. Они полезны в тех случаях, когда вы пытаетесь отфильтровать результаты запроса по сравнению с результатами отдельной агрегатной функции.

Чтобы проиллюстрировать это, предположим, что вам нужно узнать, кто из ваших подруг выиграл больше матчей, чем Барбара. Вместо того чтобы посмотреть, сколько матчей выиграла Барбара, а затем выполнить другой запрос, чтобы узнать, кто выиграл больше игр, вы можете вычислить результат с помощью одного запроса:

Подзапрос в этом операторе был выполнен только один раз; нужно только найти значение из столбца wins в той строке, где в столбце name указано значение Barbara; данные, возвращаемые подзапросом и внешним запросом, не зависят друг от друга. Однако существуют случаи, когда внешний запрос должен сначала прочитать каждую строку в таблице и сравнить эти значения с данными, возвращенными подзапросом, чтобы получить требуемые данные. В этом случае подзапрос будет как коррелированным.

Следующее выражение является примером коррелированного подзапроса. Этот запрос пытается выяснить, кто из подруг выиграл игр больше среднего среди участниц с одинаковым размером обуви:

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

Чтобы обработать запрос, СУБД должна сначала собрать столбцы name и size из внешнего запроса. Затем она сравнивает каждую строку из этого набора результатов с результатами внутреннего запроса, который определяет среднее количество побед для людей с одинаковым размером обуви. Поскольку у вас есть только две подруги с одинаковым размером обуви, в наборе результатов получится только одна строка:

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

Как упоминалось ранее, подзапросы могут использоваться для запроса результатов из нескольких таблиц. Чтобы проиллюстрировать это, давайте представим, что вы хотите устроить сюрприз – ужин для лучшего игрока в группе. Вы можете узнать, кто из подруг показал лучший результат, а также посмотреть любимое блюдо в рамках одного запроса:

Обратите внимание: этот запрос не только содержит подзапрос, но также еще один подзапрос внутри него.

Заключение

Запросы являются одной из наиболее часто выполняемых задач в области управления базами данных. Существует ряд инструментов администрирования баз данных (таких как phpMyAdmin и pgAdmin), которые позволяют выполнять запросы и визуализировать результаты, но выдача операторов SELECT из командной строки по-прежнему является широко распространенным рабочим процессом, который также может предоставить вам больший контроль над своими данными.

Если вы новичок в SQL, мы рекомендуем вам ознакомиться с нашим мануалом Краткий справочник по управлению базой данных SQL и с официальной документацией PostgreSQL. Кроме того, если вы хотите больше узнать о SQL и реляционных базах данных, вам могут быть интересны следующие мануалы: