Запрос и фильтрация данных в SQLite

Введение

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

Подключение к SQLite и просмотр данных

На этом первом шаге вы подключитесь к базе данных SQLite под названием employees.db и просмотрите данные в таблице staff. Это позволит вам понять, с какими данными вы будете работать.

Откройте терминал в среде LabEx VM (виртуальной машины). Каталог по умолчанию: /home/labex/project.
Подключитесь к базе данных employees.db с помощью утилиты командной строки sqlite3:
```
sqlite3 employees.db
```
Эта команда открывает оболочку SQLite, обозначенную приглашением sqlite>.
Просмотрите данные в таблице staff. Выполните следующую SQL-команду:
```
SELECT * FROM staff;
```
Эта команда выбирает все столбцы (*) из таблицы staff и отображает результаты. Вы должны увидеть следующий вывод:
```
1|Alice|HR
2|Bob|IT
3|Charlie|HR
4|David|IT
5|Eve|Marketing
6|Frank|IT
```
Этот вывод показывает id, name (имя) и department (отдел) для каждого сотрудника в таблице staff.
Чтобы выйти из оболочки SQLite, введите:
```
.quit
```
ВАЖНО: Убедитесь, что вы выполнили команду .quit перед тем, как нажать кнопку "Continue" ("Продолжить"). В противном случае этот шаг не может быть проверен, поскольку история sqlite не будет записана.

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

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

Подключитесь к базе данных employees.db:
```
sqlite3 employees.db
```
Чтобы получить только сотрудников из отдела HR, используйте следующую SQL-команду:
```
SELECT * FROM staff WHERE department = 'HR';
```
Эта команда выбирает все столбцы из таблицы staff, но включает только строки, в которых столбец department равен HR. Результат будет следующим:
```
1|Alice|HR
3|Charlie|HR
```
Теперь давайте найдем сотрудников с id больше 3:
```
SELECT * FROM staff WHERE id > 3;
```
Эта команда выбирает все столбцы из таблицы staff, но включает только строки, в которых столбец id больше 3. Результат будет следующим:
```
4|David|IT
5|Eve|Marketing
6|Frank|IT
```
Выйдите из оболочки SQLite:
```
.quit
```
ВАЖНО: Убедитесь, что вы выполнили команду .quit перед тем, как нажать кнопку "Continue" ("Продолжить"). В противном случае этот шаг не может быть проверен, поскольку история sqlite не будет записана.

Сопоставление с образцом с помощью оператора LIKE

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

Подключитесь к базе данных employees.db:
```
sqlite3 employees.db
```
Чтобы найти сотрудников, чьи имена начинаются с A, используйте следующую команду:
```
SELECT * FROM staff WHERE name LIKE 'A%';
```
Эта команда выбирает все столбцы из таблицы staff, но включает только строки, в которых столбец name начинается с A. Подстановочный знак % соответствует нулю или более символам. Результат будет следующим:
```
1|Alice|HR
```
Чтобы найти сотрудников, чьи имена содержат букву i, используйте:
```
SELECT * FROM staff WHERE name LIKE '%i%';
```
Эта команда выбирает все столбцы из таблицы staff, но включает только строки, в которых столбец name содержит букву i. Результат будет следующим:
```
1|Alice|HR
3|Charlie|HR
4|David|IT
```
Выйдите из оболочки SQLite:
```
.quit
```

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

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

Подключитесь к базе данных employees.db:
```
sqlite3 employees.db
```
Чтобы отсортировать данные в алфавитном порядке по name по возрастанию, используйте:
```
SELECT * FROM staff ORDER BY name;
```
Эта команда выбирает все столбцы из таблицы staff и сортирует результаты по столбцу name по возрастанию (от A до Z). Результат будет следующим:
```
1|Alice|HR
2|Bob|IT
3|Charlie|HR
4|David|IT
5|Eve|Marketing
6|Frank|IT
```
Чтобы отсортировать по name по убыванию, используйте:
```
SELECT * FROM staff ORDER BY name DESC;
```
Эта команда выбирает все столбцы из таблицы staff и сортирует результаты по столбцу name по убыванию (от Z до A). Результат будет следующим:
```
6|Frank|IT
5|Eve|Marketing
4|David|IT
3|Charlie|HR
2|Bob|IT
1|Alice|HR
```
Выйдите из оболочки SQLite:
```
.quit
```

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

Предложение LIMIT ограничивает количество строк, возвращаемых запросом. Это полезно, когда вам нужна только часть данных (subset of the data).

Подключитесь к базе данных employees.db:
```
sqlite3 employees.db
```
Чтобы получить только первые 3 строки из таблицы staff, используйте:
```
SELECT * FROM staff LIMIT 3;
```
Эта команда выбирает все столбцы из таблицы staff и ограничивает результаты первыми 3 строками. Результат будет следующим:
```
1|Alice|HR
2|Bob|IT
3|Charlie|HR
```
Объедините LIMIT с ORDER BY, чтобы получить первых 2 сотрудников, отсортированных по name в порядке убывания:
```
SELECT * FROM staff ORDER BY name DESC LIMIT 2;
```
Эта команда выбирает все столбцы из таблицы staff, сортирует результаты по столбцу name в порядке убывания и ограничивает результаты первыми 2 строками. Результат будет следующим:
```
6|Frank|IT
5|Eve|Marketing
```
Выйдите из оболочки SQLite:
```
.quit
```

Резюме

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