Введение
В этом практическом занятии (лабораторной работе) мы рассмотрим основные методы фильтрации и сортировки данных в MySQL. Вы узнаете, как использовать предложения WHERE для фильтрации данных на основе определенных условий, сортировать результаты с помощью предложения ORDER BY и ограничить вывод с использованием предложения LIMIT. Эти навыки являются фундаментальными для любого пользователя базы данных, позволяя вам получать именно те данные, которые вам нужны, в том порядке, в котором вы хотите. С помощью практических упражнений вы получите практический опыт в выполнении этих важных операций с базами данных и поймете, как эффективно запрашивать и организовывать данные в MySQL.
Базовая фильтрация данных с использованием WHERE
На этом этапе мы узнаем, как использовать предложение WHERE для фильтрации данных на основе определенных условий. Предложение WHERE является одной из наиболее важных функций в SQL, позволяющей вам получать только те данные, которые соответствуют вашим критериям.
Сначала подключимся к MySQL и выберем нашу базу данных:
sudo mysql -u root
После подключения:
USE bookstore;
Простые операции сравнения
Начнем с базовых операторов сравнения:
-- Книги стоимостью более 40 долларов
SELECT title, price
FROM books
WHERE price > 40;
Вы должны увидеть такой вывод:
+-------------------------+-------+
| title | price |
+-------------------------+-------+
| The MySQL Guide | 45.99 |
| Advanced Database Concepts | 54.99 |
| Database Administration | 49.99 |
| The Perfect Index | 42.99 |
+-------------------------+-------+
Общие операторы сравнения включают:
=(равно)<>или!=(не равно)>(больше)<(меньше)>=(больше или равно)<=(меньше или равно)
Попробуем другой пример: найдем книги, опубликованные в определенном году:
SELECT title, author, publication_year
FROM books
WHERE publication_year = 2023;
Использование AND и OR
Мы можем комбинировать несколько условий с помощью операторов AND и OR:
-- Технические книги, опубликованные в 2023 году
SELECT title, genre, publication_year, price
FROM books
WHERE genre = 'Technical'
AND publication_year = 2023;
Попробуем использовать OR, чтобы найти книги, которые либо очень новые, либо очень дорогие:
SELECT title, publication_year, price
FROM books
WHERE publication_year = 2023
OR price >= 50;
Работа с NULL и булевыми значениями
Чтобы найти книги, которых нет в наличии:
SELECT title, price, in_stock
FROM books
WHERE in_stock = FALSE;
Поиск по шаблону с использованием LIKE
На этом этапе мы рассмотрим поиск по шаблону с использованием оператора LIKE, который позволяет искать шаблоны в текстовых данных. Это особенно полезно, когда вы не знаете точный текст, который ищете.
Оператор LIKE использует два специальных символа:
%представляет ноль или более символов_представляет ровно один символ
Попробуем несколько примеров:
-- Найти книги, в названии которых встречается "SQL"
SELECT title, author
FROM books
WHERE title LIKE '%SQL%';
Этот запрос находит все книги, в названии которых "SQL" встречается в любом месте. Вы должны увидеть такой вывод:
+------------------+----------------+
| title | author |
+------------------+----------------+
| SQL for Beginners| Sarah Johnson |
| SQL Mystery Tales| Jennifer White |
+------------------+----------------+
Попробуем еще несколько примеров поиска по шаблону:
-- Найти книги, названия которых начинаются с "The"
SELECT title, author
FROM books
WHERE title LIKE 'The%';
-- Найти книги, в названии которых встречается "Database"
SELECT title, author, price
FROM books
WHERE title LIKE '%Database%';
Обратите внимание, что по умолчанию в MySQL оператор LIKE нечувствителен к регистру. Если вам нужен поиск с учетом регистра, вы можете использовать ключевое слово BINARY:
-- Поиск с учетом регистра
SELECT title, author
FROM books
WHERE title LIKE BINARY 'The%';
Сортировка результатов с использованием ORDER BY
На этом этапе мы узнаем, как сортировать результаты запросов с помощью предложения ORDER BY. Это позволяет нам упорядочивать данные в осмысленном порядке, как по возрастанию (ASC), так и по убыванию (DESC).
Сортировка по одной колонке
Начнем с базовой сортировки по цене:
-- Сортировка книг по цене (по умолчанию по возрастанию)
SELECT title, price
FROM books
ORDER BY price;
Вы увидите, что книги будут перечислены от самой дешевой до самой дорогой. Чтобы изменить порядок на обратный:
-- Сортировка книг по цене в порядке убывания
SELECT title, price
FROM books
ORDER BY price DESC;
В выводе сначала будут показаны самые дорогие книги:
+---------------------------+-------+
| title | price |
+---------------------------+-------+
| Advanced Database Concepts| 54.99 |
| Database Administration | 49.99 |
| The MySQL Guide | 45.99 |
| The Perfect Index | 42.99 |
...
+---------------------------+-------+
Сортировка по нескольким колонкам
Мы можем сортировать по нескольким колонкам, что полезно, когда в первой колонке для сортировки есть повторяющиеся значения:
-- Сортировка по жанру, а затем по цене внутри каждого жанра
SELECT title, genre, price
FROM books
ORDER BY genre, price DESC;
Вы можете комбинировать сортировку по возрастанию и убыванию:
-- Сортировка по жанру (по возрастанию) и цене (по убыванию)
SELECT title, genre, price
FROM books
ORDER BY genre ASC, price DESC;
Ограничение количества результатов с использованием LIMIT
На этом этапе мы узнаем, как использовать предложение LIMIT для ограничения количества строк, возвращаемых запросом. Это особенно полезно при работе с большими наборами данных или когда вам нужно просмотреть только несколько примеров.
Базовое использование LIMIT
Чтобы увидеть только 3 самые дорогие книги:
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;
Вы должны увидеть только три строки:
+---------------------------+-------+
| title | price |
+---------------------------+-------+
| Advanced Database Concepts| 54.99 |
| Database Administration | 49.99 |
| The MySQL Guide | 45.99 |
+---------------------------+-------+
Использование LIMIT с смещением (offset)
LIMIT может принимать два числа: смещение (сколько строк пропустить) и количество (сколько строк вернуть):
-- Пропустить первые 3 книги и показать следующие 3
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3, 3;
Это покажет 4-ю, 5-ю и 6-ю по стоимости книги.
Распространенный случай использования - пагинация. Например, чтобы показать "страницу 2" с 5 элементами на странице:
SELECT title, author, price
FROM books
ORDER BY title
LIMIT 5, 5;
Это пропускает первые 5 результатов (страница 1) и показывает следующие 5 (страница 2).
Комбинирование всех элементов
Соберем все, что мы узнали, чтобы создать более сложный запрос:
SELECT title, author, price
FROM books
WHERE genre = 'Technical'
AND price > 30
ORDER BY price DESC
LIMIT 3;
Этот запрос:
- Фильтрует технические книги стоимостью более 30 долларов.
- Сортирует их по убыванию цены.
- Показывает только 3 лучших результата.
Резюме
В этом практическом занятии (лабораторной работе) мы рассмотрели основные методы фильтрации и сортировки данных в MySQL:
- Использование предложений WHERE с операторами сравнения и логическими операторами для фильтрации данных.
- Поиск по шаблону с использованием оператора LIKE для текстовых запросов.
- Сортировка результатов с помощью предложения ORDER BY по одной или нескольким колонкам.
- Ограничение наборов результатов с использованием LIMIT и OFFSET.
Эти навыки являются основой для эффективного извлечения и организации данных в MySQL.
