Фильтрация и сортировка данных в MySQL

Введение

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

Skills Graph

Базовая фильтрация данных с использованием 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;

Этот запрос:

1. Фильтрует технические книги стоимостью более 30 долларов.
2. Сортирует их по убыванию цены.
3. Показывает только 3 лучших результата.

Резюме

В этом практическом занятии (лабораторной работе) мы рассмотрели основные методы фильтрации и сортировки данных в MySQL:

1. Использование предложений WHERE с операторами сравнения и логическими операторами для фильтрации данных.
2. Поиск по шаблону с использованием оператора LIKE для текстовых запросов.
3. Сортировка результатов с помощью предложения ORDER BY по одной или нескольким колонкам.
4. Ограничение наборов результатов с использованием LIMIT и OFFSET.

Эти навыки являются основой для эффективного извлечения и организации данных в MySQL.