Запрос данных PostgreSQL с помощью SELECT

Linux | Amber | 1 Comment

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

В данном руководстве рассматривается процесс создания запросов данных PostgreSQL.

Примечание: Предполагается, что СУБД PostgreSQL предварительно установлена на сервер. В руководстве используется система Ubuntu 12.04, однако инструкции подойдут и для более современных дистрибутивов Linux.

Вход в PostgreSQL

Для работы можно загрузить тестовую БД.

Откройте сессию стандартного пользователя PostgreSQL:

sudo su - postgres

Чтобы получить файл тестовой БД, введите:

wget http://pgfoundry.org/frs/download.php/527/world-1.0.tar.gz

Распакуйте архив и перейдите в полученный каталог:

tar xzvf world-1.0.tar.gz
cd dbsamples-0.1/world

Создайте БД:

createdb -T template0 worlddb

Добавьте в новую БД файл .sql:

psql worlddb < world.sql

Войдите в новое окружение:

psql worlddb

Просмотр данных в PostgreSQL

Для начала нужно ознакомиться с тестовой БД. Чтобы просмотреть список таблиц, введите:

\d+
List of relations
Schema |      Name       | Type  |  Owner   |  Size  | Description
-------+-----------------+-------+----------+--------+-------------
public | city            | table | postgres | 264 kB |
public | country         | table | postgres | 48 kB  |
public | countrylanguage | table | postgres | 56 kB  |
(3 rows)

Эта БД содержит три таблицы. Чтобы просмотреть столбцы одной из таблиц (например, city), выполните команду:

\d city
Table "public.city"
Column      |     Type     | Modifiers
------------+--------------+-----------
id          | integer      | not null
name        | text         | not null
countrycode | character(3) | not null
district    | text         | not null
population  | integer      | not null
Indexes:
"city_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)

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

Запрос данных при помощи select

Для запроса информации в PostgreSQL используется оператор select. Базовый синтаксис выглядит так:

SELECT columns_to_return FROM table_name;

К примеру, если запросить столбцы таблицы country (команда \d country), на экране появится объёмный вывод. Его можно отфильтровать, запросив только необходимые вам данные. Для примера запросите название страны и континент, на котором она находится.

SELECT name,continent FROM country;
name                  |   continent
----------------------+---------------
Afghanistan           | Asia
Netherlands           | Europe
Netherlands Antilles  | North America
Albania               | Europe
Algeria               | Africa
American Samoa        | Oceania
Andorra               | Europe
. . .

Чтобы просмотреть все столбцы конкретной таблицы, используйте символ звёздочки (*).

SELECT * FROM city;
id|               name                | countrycode |           district            | population
--+-----------------------------------+-------------+-------------------------------+------------
1 | Kabul                             | AFG         | Kabol                         |    1780000
2 | Qandahar                          | AFG         | Qandahar                      |     237500
3 | Herat                             | AFG         | Herat                         |     186800
4 | Mazar-e-Sharif                    | AFG         | Balkh                         |     127800
5 | Amsterdam                         | NLD         | Noord-Holland                 |     731200
6 | Rotterdam                         | NLD         | Zuid-Holland                  |     593321
7 | Haag                              | NLD         | Zuid-Holland                  |     440900
. . .

Определение порядка записей

PostgreSQL позволяет определить порядок вывода запрошенных данных; для этого используется конструкция ORDER BY, идущая после оператора SELECT. Базовый синтаксис выглядит так:

SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];

К примеру, чтобы запросить название стран и континент из таблицы country, а потом упорядочить вывод по континентам, нужно ввести следующую команду:

SELECT name,continent FROM country ORDER BY continent;
name                     |   continent
-------------------------+---------------
Algeria                  | Africa
Western Sahara           | Africa
Madagascar               | Africa
Uganda                   | Africa
Malawi                   | Africa
Mali                     | Africa
Morocco                  | Africa
Côte d\u0092Ivoire       | Africa
. . .

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

Чтобы изменить порядок вывода, нужно добавить в конструкцию ORDER BY оператор DESC. Например:

SELECT name,continent FROM country ORDER BY continent DESC;
name                     |   continent
-------------------------+---------------
Paraguay                 | South America
Bolivia                  | South America
Brazil                   | South America
Falkland Islands         | South America
Argentina                | South America
Venezuela                | South America
Guyana                   | South America
Chile                    | South America
. . .

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

К примеру, можно отсортировать вывод по континенту, а затем по названию страны, и в результате получить алфавитный список стран для каждого континента:

SELECT name,continent FROM country ORDER BY continent,name;
name                     |   continent
-------------------------+---------------
Angola                   | Africa
Burundi                  | Africa
Benin                    | Africa
Burkina Faso             | Africa
Botswana                 | Africa
Central African Republic | Africa
Côte d\u0092Ivoire       | Africa
Cameroon                 | Africa
Congo       | Africa
. . .

Фильтрация записей PostgreSQL

PostgreSQL предоставляет механизмы для более детальной фильтрации вывода.

Один из таких механизмов – предложение where, которое позволяет задавать условия поиска.

К примеру, можно запросить все города Соединённых Штатов, добавив в конец команды конструкцию WHERE countrycode = ‘USA’.

SELECT name FROM city WHERE countrycode = 'USA';
name
-------------------------
New York
Los Angeles
Chicago
Houston
Philadelphia
Phoenix
San Diego
Dallas
San Antonio
. . .

Примечание: Условия поиска для предложения WHERE нужно помещать в одинарные кавычки.

Реляционный оператор like делает поиск ещё более гибким. Этот оператор использует шаблоны:

• “_” – поиск совпадения по одному символу.
• “%” – поиск 0 или больше значений.
• and и or – объединение параметров фильтрации.

Для примера попробуйте запросить названия городов США, которые начинаются на N.

SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%';
name
--------------------
New York
Nashville-Davidson
New Orleans
Newark
Norfolk
Newport News
Naperville
New Haven
North Las Vegas
Norwalk
New Bedford
Norman
(12 rows)

Этот вывод также можно получить в алфавитном порядке:

SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%' ORDER BY name;
name
--------------------
Naperville
Nashville-Davidson
Newark
New Bedford
New Haven
New Orleans
Newport News
New York
Norfolk
Norman
North Las Vegas
Norwalk
(12 rows)

Продвинутое использование select

Теперь попробуйте создать более сложный запрос. В качестве примера можно рассмотреть следующий запрос:

SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
country                |              capital              |   continent
------------------------+-----------------------------------+---------------
Algeria                 | Alger                             | Africa
Angola                  | Luanda                            | Africa
Benin                   | Porto-Novo                        | Africa
Botswana                | Gaborone                          | Africa
Burkina Faso            | Ouagadougou                       | Africa
Burundi                 | Bujumbura                         | Africa
Cameroon                | Yaoundé                           | Africa
Cape Verde              | Praia                             | Africa
Central African Republic| Bangui                            | Africa
Chad                    | N´Djaména                         | Africa
. . .

Этот запрос состоит из нескольких частей.

В конце этого запроса находится знакомый оператор ORDER BY continent,country, который сортирует вывод по континенту, а затем по названию страны.

Чтобы понять остальные компоненты запроса, нужно ознакомиться с объединением таблиц.

Соединенные таблицы

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

Примечание: Таблицы считаются связанными, если они имеют столбец, который относится к одним и тем же данным.

К примеру, в тестовой БД таблицы country и city связаны, поскольку совместно используют некоторые данные. Чтобы увидеть, что таблица country ссылается на таблицу city, введите:

\d country
. . .
. . .
Foreign-key constraints:
"country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
. . .
. . .

Этот вывод говорит о том, что столбец capital таблицы country ссылается на столбец id таблицы city. По сути, можно воспринимать эти две таблицы как одну большую таблицу.

В предложенном выше запросе есть часть:

FROM country JOIN city ON country.capital = city.id

Это выражение значит, что Postgres вернёт данные из обеих таблиц. Предложение JOIN здесь является стандартным оператором соединения (также известен как inner join).

INNER JOIN выводит данные, присутствующие в обеих таблицах. Например, если соединить таблицы, которые не относятся друг к другу как внешние ключи, программа вернёт ошибку, поскольку таблицы содержат не связанные между собой данные. Для такого вывода стандартное предложение join не подходит.

Часть команды после ключевого слова on указывает общий столбец таблиц в таком формате:

table_name.column_name

В данном случае запрашиваются записи с общими значениями в обеих таблицах.

Заключение

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