Фильтрация данных и простые запросы в PostgreSQL

Введение

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

Затем лабораторная работа проведет вас через использование предложения WHERE для фильтрации данных на основе определенных условий, применение LIKE для сопоставления с шаблоном, сортировку результатов с помощью ORDER BY и ограничение количества возвращаемых строк с использованием LIMIT и OFFSET.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/data_sort("Sort Query Results") subgraph Lab Skills postgresql/db_access -.-> lab-550898{{"Фильтрация данных и простые запросы в PostgreSQL"}} postgresql/table_init -.-> lab-550898{{"Фильтрация данных и простые запросы в PostgreSQL"}} postgresql/data_where -.-> lab-550898{{"Фильтрация данных и простые запросы в PostgreSQL"}} postgresql/data_sort -.-> lab-550898{{"Фильтрация данных и простые запросы в PostgreSQL"}} end

Фильтрация данных с помощью предложения WHERE

В этом шаге мы узнаем, как использовать предложение WHERE в PostgreSQL для фильтрации данных на основе определенных условий. Предложение WHERE — это мощный инструмент, который позволяет извлекать только те строки, которые соответствуют вашим критериям.

Прежде чем мы начнем, давайте подключимся к базе данных PostgreSQL. Откройте терминал в вашей виртуальной машине LabEx. Вы можете использовать терминал Xfce по умолчанию.

Сначала подключитесь к базе данных PostgreSQL с помощью команды psql. Мы подключимся к базе данных postgres с пользователем postgres. Возможно, вам потребуется использовать sudo для выполнения команды.

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL (postgres=#).

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

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INTEGER
);

INSERT INTO employees (name, department, salary) VALUES
('Alice Smith', 'Sales', 60000),
('Bob Johnson', 'Marketing', 75000),
('Charlie Brown', 'Sales', 55000),
('David Lee', 'Engineering', 90000),
('Eve Wilson', 'Marketing', 80000),
('Frank Miller', 'Engineering', 95000);

Чтобы убедиться, что таблица была создана и заполнена правильно, выполните следующий SQL-запрос:

SELECT * FROM employees;

Вы должны увидеть вывод, подобный этому:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  6 | Frank Miller    | Engineering |  95000
(6 rows)

Теперь давайте используем предложение WHERE для фильтрации данных. Предположим, мы хотим получить только тех сотрудников, которые работают в отделе 'Sales' (продаж). Мы можем использовать следующий запрос:

SELECT * FROM employees WHERE department = 'Sales';

Этот запрос вернет только те строки, где столбец department равен 'Sales'. Вывод должен быть:

 id |     name      | department | salary
----+-----------------+------------+--------
  1 | Alice Smith     | Sales      |  60000
  3 | Charlie Brown   | Sales      |  55000
(2 rows)

Вы также можете использовать другие операторы сравнения в предложении WHERE, такие как >, <, >=, <=, и <>. Например, чтобы получить сотрудников с зарплатой больше 70000, вы можете использовать следующий запрос:

SELECT * FROM employees WHERE salary > 70000;

Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  2 | Bob Johnson     | Marketing   |  75000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  6 | Frank Miller    | Engineering |  95000
(4 rows)

Вы также можете объединять несколько условий, используя логические операторы, такие как AND и OR. Например, чтобы получить сотрудников, которые работают в отделе 'Sales' и имеют зарплату больше 58000, вы можете использовать следующий запрос:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 58000;

Вывод должен быть:

 id |    name     | department | salary
----+---------------+------------+--------
  1 | Alice Smith | Sales      |  60000
(1 row)

Наконец, выйдите из оболочки psql:

\q

Это вернет вас в терминал пользователя labex.

Использование LIKE для сопоставления с шаблоном

В этом шаге мы узнаем, как использовать оператор LIKE в PostgreSQL для сопоставления с шаблоном (pattern matching). Оператор LIKE позволяет искать данные, соответствующие определенному шаблону, что особенно полезно, когда вы не знаете точное значение, которое ищете.

Сначала давайте подключимся к базе данных PostgreSQL. Откройте терминал в вашей виртуальной машине LabEx.

Подключитесь к базе данных PostgreSQL с помощью команды psql:

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL (postgres=#).

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

Давайте просмотрим данные в таблице employees:

SELECT * FROM employees;

Вы должны увидеть данные о сотрудниках, как и раньше.

Оператор LIKE используется в предложении WHERE для поиска строк, соответствующих указанному шаблону. Шаблон может включать подстановочные знаки (wildcard characters):

%: Представляет ноль или более символов.
_: Представляет один символ.

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

SELECT * FROM employees WHERE name LIKE 'A%';

Этот запрос вернет все строки, где столбец name начинается с 'A'. Вывод должен быть:

 id |    name     | department | salary
----+---------------+------------+--------
  1 | Alice Smith | Sales      |  60000
(1 row)

Чтобы найти всех сотрудников, чьи имена содержат букву 'o', вы можете использовать следующий запрос:

SELECT * FROM employees WHERE name LIKE '%o%';

Этот запрос вернет все строки, где столбец name содержит букву 'o'. Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
  5 | Eve Wilson      | Marketing   |  80000
(3 rows)

Чтобы найти всех сотрудников, чьи имена заканчиваются на 'n', вы можете использовать следующий запрос:

SELECT * FROM employees WHERE name LIKE '%n';

Этот запрос вернет все строки, где столбец name заканчивается на 'n'. Вывод должен быть:

 id |     name      | department | salary
----+---------------+------------+--------
  2 | Bob Johnson   | Marketing  |  75000
  3 | Charlie Brown | Sales      |  55000
  5 | Eve Wilson    | Marketing  |  80000
(3 rows)

Вы также можете использовать подстановочный знак _ для сопоставления с одним символом. Например, чтобы найти всех сотрудников, чьи имена имеют 'e ' в качестве второго и третьего символов, вы можете использовать следующий запрос:

SELECT * FROM employees WHERE name LIKE '_e%';

Этот запрос вернет все строки, где столбец name имеет 'e' в качестве второго символа. Вывод должен быть:

 id | name | department | salary
----+------+------------+--------
(0 rows)

Оператор LIKE чувствителен к регистру. Если вы хотите выполнить поиск без учета регистра, вы можете использовать оператор ILIKE. Например:

SELECT * FROM employees WHERE name ILIKE 'a%';

Этот запрос вернет все строки, где столбец name начинается с 'a' или 'A'.

Наконец, выйдите из оболочки psql:

\q

Это вернет вас в терминал пользователя labex.

Сортировка данных с помощью ORDER BY

В этом шаге мы узнаем, как использовать предложение ORDER BY в PostgreSQL для сортировки данных. Предложение ORDER BY позволяет сортировать результирующий набор запроса на основе одного или нескольких столбцов, как в порядке возрастания, так и в порядке убывания.

Сначала давайте подключимся к базе данных PostgreSQL. Откройте терминал в вашей виртуальной машине LabEx.

Подключитесь к базе данных PostgreSQL с помощью команды psql:

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL (postgres=#).

Мы продолжим использовать таблицу employees, созданную на предыдущих шагах. Если вы не создали ее, обратитесь к предыдущим шагам, чтобы создать таблицу и вставить данные.

Давайте просмотрим данные в таблице employees:

SELECT * FROM employees;

Вы должны увидеть данные о сотрудниках, как и раньше.

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

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

SELECT * FROM employees ORDER BY salary;

Этот запрос вернет все строки из таблицы employees, отсортированные по столбцу salary в порядке возрастания. Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  3 | Charlie Brown   | Sales       |  55000
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  5 | Eve Wilson      | Marketing   |  80000
  4 | David Lee       | Engineering |  90000
  6 | Frank Miller    | Engineering |  95000
(6 rows)

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

SELECT * FROM employees ORDER BY salary DESC;

Этот запрос вернет все строки из таблицы employees, отсортированные по столбцу salary в порядке убывания. Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  6 | Frank Miller    | Engineering |  95000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
  2 | Bob Johnson     | Marketing   |  75000
  1 | Alice Smith     | Sales       |  60000
  3 | Charlie Brown   | Sales       |  55000
(6 rows)

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

SELECT * FROM employees ORDER BY department ASC, salary DESC;

Этот запрос сначала отсортирует данные по столбцу department в порядке возрастания. Внутри каждого отдела данные будут отсортированы по столбцу salary в порядке убывания. Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  4 | David Lee       | Engineering |  90000
  6 | Frank Miller    | Engineering |  95000
  2 | Bob Johnson     | Marketing   |  75000
  5 | Eve Wilson      | Marketing   |  80000
  1 | Alice Smith     | Sales       |  60000
  3 | Charlie Brown   | Sales       |  55000
(6 rows)

Illustration for sorting data in PostgreSQL

Наконец, выйдите из оболочки psql:

\q

Это вернет вас в терминал пользователя labex.

Ограничение результатов с помощью LIMIT и OFFSET

В этом шаге мы узнаем, как использовать предложения LIMIT и OFFSET в PostgreSQL, чтобы ограничить количество строк, возвращаемых запросом, и пропустить определенное количество строк, соответственно. Эти предложения полезны для реализации пагинации (pagination) или извлечения определенного подмножества данных.

Сначала давайте подключимся к базе данных PostgreSQL. Откройте терминал в вашей виртуальной машине LabEx.

Подключитесь к базе данных PostgreSQL с помощью команды psql:

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL (postgres=#).

Давайте просмотрим данные в таблице employees:

SELECT * FROM employees;

Вы должны увидеть данные о сотрудниках, как и раньше.

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

SELECT * FROM employees LIMIT 3;

Этот запрос вернет первые 3 строки из таблицы employees. Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  1 | Alice Smith     | Sales       |  60000
  2 | Bob Johnson     | Marketing   |  75000
  3 | Charlie Brown   | Sales       |  55000
(3 rows)

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

SELECT * FROM employees LIMIT 3 OFFSET 2;

Этот запрос пропустит первые 2 строки, а затем вернет следующие 3 строки из таблицы employees. Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  3 | Charlie Brown   | Sales       |  55000
  4 | David Lee       | Engineering |  90000
  5 | Eve Wilson      | Marketing   |  80000
(3 rows)

Вы также можете объединить LIMIT и OFFSET с предложением ORDER BY. Например, чтобы получить 2 самых высокооплачиваемых сотрудников, вы можете использовать следующий запрос:

SELECT * FROM employees ORDER BY salary DESC LIMIT 2;

Этот запрос сначала отсортирует сотрудников по зарплате в порядке убывания, а затем вернет первые 2 строки. Вывод должен быть:

 id |     name      | department  | salary
----+-----------------+-------------+--------
  6 | Frank Miller    | Engineering |  95000
  4 | David Lee       | Engineering |  90000
(2 rows)

Чтобы получить 3-го и 4-го самых высокооплачиваемых сотрудников, вы можете использовать следующий запрос:

SELECT * FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2;

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

 id |     name      | department  | salary
----+-----------------+-------------+--------
  5 | Eve Wilson      | Marketing   |  80000
  2 | Bob Johnson     | Marketing   |  75000
(2 rows)

Наконец, выйдите из оболочки psql:

\q

Это вернет вас в терминал пользователя labex.

Итог (Summary)

В этой лабораторной работе мы начали с подключения к базе данных PostgreSQL с использованием команды psql и создали пример таблицы employees со столбцами для ID, имени (name), отдела (department) и зарплаты (salary). Затем мы заполнили таблицу данными о сотрудниках и проверили ее содержимое с помощью запроса SELECT.

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