В этой лабораторной работе вы узнаете, как выполнять фильтрацию данных и простые запросы в PostgreSQL. Вы начнете с подключения к базе данных PostgreSQL и создания образца таблицы employees с данными.
Затем лабораторная работа проведет вас через использование предложения WHERE для фильтрации данных на основе определенных условий, применение LIKE для сопоставления с шаблоном, сортировку результатов с помощью ORDER BY и ограничение количества возвращаемых строк с использованием LIMIT и OFFSET.
Skills Graph
%%%%{init: {'theme':'neutral'}}%%%%
flowchart RL
postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"])
postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table")
postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row")
postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row")
postgresql/PostgreSQLGroup -.-> postgresql/func_call("Call Stored Function")
subgraph Lab Skills
postgresql/table_init -.-> lab-550947{{"Фильтрация данных и простые запросы в PostgreSQL"}}
postgresql/row_add -.-> lab-550947{{"Фильтрация данных и простые запросы в PostgreSQL"}}
postgresql/row_drop -.-> lab-550947{{"Фильтрация данных и простые запросы в PostgreSQL"}}
postgresql/func_call -.-> lab-550947{{"Фильтрация данных и простые запросы в PostgreSQL"}}
end
Фильтрация данных с помощью предложения WHERE
В этом шаге вы узнаете, как использовать предложение WHERE в PostgreSQL для фильтрации данных на основе определенных условий. Предложение WHERE — это мощный инструмент, который позволяет извлекать только те строки, которые соответствуют вашим критериям.
Сначала откройте терминал в вашей LabEx VM (виртуальной машине). Вы можете использовать терминал Xfce по умолчанию.
Подключитесь к базе данных PostgreSQL, используя команду psql:
sudo -u postgres psql
Теперь вы должны увидеть приглашение PostgreSQL (postgres=#).
Теперь давайте создадим образец таблицы с именем employees и вставим в нее некоторые данные. Эта таблица будет хранить информацию о сотрудниках, включая их ID, имя, отдел и зарплату. Выполните следующие SQL-запросы:
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)
Использование LIKE для сопоставления с шаблоном
В этом шаге вы узнаете, как использовать оператор LIKE в PostgreSQL для сопоставления с шаблоном (pattern matching). Оператор LIKE позволяет искать данные, соответствующие определенному шаблону, что особенно полезно, когда вы не знаете точное значение, которое ищете.
Мы продолжим использовать таблицу 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'.
Сортировка данных с помощью ORDER BY
В этом шаге вы узнаете, как использовать предложение ORDER BY в PostgreSQL для сортировки данных. Предложение ORDER BY позволяет сортировать результирующий набор (result set) запроса на основе одного или нескольких столбцов, как в порядке возрастания, так и в порядке убывания.
Мы продолжим использовать таблицу 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)
Ограничение результатов с помощью LIMIT и OFFSET
В этом шаге вы узнаете, как использовать предложения LIMIT и OFFSET в PostgreSQL для ограничения количества строк, возвращаемых запросом, и для пропуска определенного количества строк, соответственно. Эти предложения полезны для реализации пагинации (pagination) или извлечения определенного подмножества данных.
Мы продолжим использовать таблицу 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)
Итог
В этой лабораторной работе вы научились выполнять фильтрацию данных и простые запросы в PostgreSQL. Вы начали с подключения к базе данных PostgreSQL с помощью команды psql и создали пример таблицы employees.
Затем вы узнали, как использовать предложение WHERE для фильтрации данных на основе определенных условий, оператор LIKE для сопоставления с шаблоном (pattern matching), предложение ORDER BY для сортировки данных и предложения LIMIT и OFFSET для управления размером результирующего набора (result set size). Это фундаментальные навыки для работы с реляционными базами данных, такими как PostgreSQL.
