Skip to content

lesson03.md

Latest commit

 

History

History
440 lines (322 loc) · 20.2 KB

lesson03.md

File metadata and controls

440 lines (322 loc) · 20.2 KB

Урок 3. Простые выборки

SELECT и FROM — основа любого запроса

Любое обращение к данным в SQL начинается с двух ключевых слов: SELECT и FROM.

SELECT что_выбрать
FROM откуда_выбрать;
  • SELECT — описывает что мы хотим получить: конкретные столбцы или все сразу
  • FROM — описывает откуда: из какой таблицы

Выбор всех столбцов

Символ * означает "все столбцы":

SELECT * FROM products;

Результат — все строки таблицы products со всеми столбцами:

┌────┬──────────────────────────┬──────────┬───────┬─────────────┐
│ id │ name                     │ price    │ stock │ category_id │
├────┼──────────────────────────┼──────────┼───────┼─────────────┤
│  1 │ Ноутбук Lenovo IdeaPad   │ 75000.00 │    12 │           1 │
│  2 │ Ноутбук ASUS VivoBook    │ 68000.00 │     8 │           1 │
│  3 │ Смартфон Samsung Galaxy  │ 45000.00 │    25 │           1 │
│ .. │ ...                      │ ...      │   ... │         ... │
└────┴──────────────────────────┴──────────┴───────┴─────────────┘

Выбор конкретных столбцов

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

SELECT name, price FROM products;
┌──────────────────────────┬──────────┐
│ name                     │ price    │
├──────────────────────────┼──────────┤
│ Ноутбук Lenovo IdeaPad   │ 75000.00 │
│ Ноутбук ASUS VivoBook    │ 68000.00 │
│ Смартфон Samsung Galaxy  │ 45000.00 │
│ ...                      │ ...      │
└──────────────────────────┴──────────┘

Использовать SELECT * удобно при исследовании данных. В реальных приложениях лучше указывать конкретные столбцы — это делает код понятнее и быстрее при больших таблицах.

Порядок столбцов в SELECT

Столбцы выводятся в том порядке, в котором вы их перечислили — не в том, в котором они объявлены в таблице:

SELECT price, name, id FROM products;
┌──────────┬──────────────────────────┬────┐
│ price    │ name                     │ id │
├──────────┼──────────────────────────┼────┤
│ 75000.00 │ Ноутбук Lenovo IdeaPad   │  1 │
│ 68000.00 │ Ноутбук ASUS VivoBook    │  2 │
│ ...      │ ...                      │ .. │
└──────────┴──────────────────────────┴────┘

WHERE — фильтрация строк

SELECT * FROM products возвращает все строки. Но чаще нужно получить только те строки, которые соответствуют условию. Для этого используется WHERE:

SELECT что_выбрать
FROM таблица
WHERE условие;

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

Операторы сравнения

-- Товары дороже 10 000 рублей
SELECT name, price FROM products
WHERE price > 10000;
-- Товары с остатком ровно 0
SELECT name, stock FROM products
WHERE stock = 0;

Все доступные операторы:

Оператор Значение Пример
= Равно city = 'Москва'
!= или <> Не равно status != 'cancelled'
> Больше price > 10000
< Меньше price < 5000
>= Больше или равно stock >= 10
<= Меньше или равно price <= 30000

Пример с текстом

Строки в SQL всегда пишутся в одинарных кавычках. Двойные кавычки в SQL зарезервированы для идентификаторов — имён таблиц и столбцов, если они содержат пробелы или совпадают с ключевыми словами. SQLite допускает двойные кавычки для строк, но это нестандартное поведение:

-- Пользователи из Москвы
SELECT name, email FROM users
WHERE city = 'Москва';
┌──────────────────┬────────────────────┐
│ name             │ email              │
├──────────────────┼────────────────────┤
│ Алексей Смирнов  │ alexey@gmail.com   │
│ Дмитрий Козлов   │ dmitry@mail.ru     │
│ Анна Волкова     │ anna@gmail.com     │
│ Иван Попов       │ ivan@gmail.com     │
│ Андрей Борисов   │ andrey@gmail.com   │
└──────────────────┴────────────────────┘

AND, OR, NOT — составные условия

Условия в WHERE можно комбинировать с помощью логических операторов.

AND — оба условия должны быть истинны

-- Товары дешевле 10 000 и с остатком больше 20
SELECT name, price, stock FROM products
WHERE price < 10000 AND stock > 20;
┌──────────────────────────────┬─────────┬───────┐
│ name                         │ price   │ stock │
├──────────────────────────────┼─────────┼───────┤
│ Мышь Logitech MX Master      │ 5500.00 │    50 │
│ Игровой коврик SteelSeries   │ 2200.00 │    60 │
│ Чистый код. Мартин           │ 1200.00 │   100 │
│ Паттерны проектирования      │ 1400.00 │    80 │
│ Изучаем Python. Лутц         │ 1800.00 │    60 │
│ Грокаем алгоритмы            │ 1100.00 │    90 │
│ Футболка хлопок M            │  800.00 │   200 │
│ Футболка хлопок L            │  800.00 │   180 │
└──────────────────────────────┴─────────┴───────┘

OR — хотя бы одно условие должно быть истинно

-- Заказы со статусом shipped или pending
SELECT id, user_id, status FROM orders
WHERE status = 'shipped' OR status = 'pending';
┌────┬─────────┬─────────┐
│ id │ user_id │ status  │
├────┼─────────┼─────────┤
│ 10 │       7 │ shipped │
│ 12 │       9 │ shipped │
│ 13 │      10 │ pending │
│ 16 │      13 │ pending │
│ 18 │      15 │ shipped │
└────┴─────────┴─────────┘

NOT — отрицание условия

-- Пользователи не из Москвы
SELECT name, city FROM users
WHERE NOT city = 'Москва';

Это равнозначно записи WHERE city != 'Москва'. NOT чаще используется с более сложными конструкциями — например, NOT IN или NOT LIKE, которые разберём ниже.

Скобки для приоритета

Когда вы смешиваете AND и OR, используйте скобки чтобы явно указать порядок вычисления. Без скобок AND всегда выполняется раньше OR, что может дать неожиданный результат:

-- Без скобок: сначала вычислится AND, потом OR
-- Читается как: (price < 2000 AND stock > 50) OR category_id = 3
SELECT name, price, stock FROM products
WHERE price < 2000 AND stock > 50 OR category_id = 3;

-- Со скобками: явно задаём порядок
SELECT name, price, stock FROM products
WHERE price < 2000 AND (stock > 50 OR category_id = 3);

Эти два запроса вернут разные результаты. Всегда расставляйте скобки когда смешиваете AND и OR.

LIKE — поиск по шаблону

Оператор LIKE позволяет искать строки по шаблону. В шаблоне используются два специальных символа:

Символ Значение
% Любое количество любых символов (в том числе ноль)
_ Ровно один любой символ 
-- Товары, в названии которых есть слово "Ноутбук"
SELECT name, price FROM products
WHERE name LIKE 'Ноутбук%';
┌──────────────────────────┬──────────┐
│ name                     │ price    │
├──────────────────────────┼──────────┤
│ Ноутбук Lenovo IdeaPad   │ 75000.00 │
│ Ноутбук ASUS VivoBook    │ 68000.00 │
└──────────────────────────┴──────────┘

-- Пользователи с email на gmail.com
SELECT name, email FROM users
WHERE email LIKE '%@gmail.com';
┌──────────────────┬──────────────────────┐
│ name             │ email                │
├──────────────────┼──────────────────────┤
│ Алексей Смирнов  │ alexey@gmail.com     │
│ Елена Новикова   │ elena@gmail.com      │
│ Анна Волкова     │ anna@gmail.com       │
│ Иван Попов       │ ivan@gmail.com       │
│ Андрей Борисов   │ andrey@gmail.com     │
│ Светлана Орлова  │ svetlana@gmail.com   │
└──────────────────┴──────────────────────┘

-- Товары, в названии которых есть "фон" в любом месте
SELECT name FROM products
WHERE name LIKE '%фон%';
┌─────────────────────────┐
│ name                    │
├─────────────────────────┤
│ Смартфон Samsung Galaxy │
│ Смартфон Xiaomi Redmi   │
└─────────────────────────┘

LIKE в SQLite нечувствителен к регистру для латинских символов: 'logitech%' найдёт 'Logitech MX Master'. Для кириллицы поведение зависит от настроек — в учебных целях пишите в том регистре, в котором хранятся данные.

NOT LIKE — противоположность: строки которые не соответствуют шаблону:

-- Пользователи, email которых не на yandex.ru
SELECT name, email FROM users
WHERE email NOT LIKE '%@yandex.ru';

IN — проверка на вхождение в список

IN позволяет проверить принадлежит ли значение заданному списку. Это компактная замена нескольким условиям через OR:

-- Заказы со статусом shipped или pending
-- Длинный способ:
SELECT id, status FROM orders
WHERE status = 'shipped' OR status = 'pending';

-- Короткий способ через IN:
SELECT id, status FROM orders
WHERE status IN ('shipped', 'pending');

Оба запроса дают одинаковый результат. IN предпочтительнее когда вариантов больше двух.

-- Пользователи из нескольких городов
SELECT name, city FROM users
WHERE city IN ('Москва', 'Казань', 'Новосибирск');
┌──────────────────┬─────────────┐
│ name             │ city        │
├──────────────────┼─────────────┤
│ Алексей Смирнов  │ Москва      │
│ Дмитрий Козлов   │ Москва      │
│ Анна Волкова     │ Москва      │
│ Павел Лебедев    │ Казань      │
│ Иван Попов       │ Москва      │
│ Андрей Борисов   │ Москва      │
│ Михаил Федоров   │ Новосибирск │
│ Николай Петров   │ Казань      │
└──────────────────┴─────────────┘

NOT IN — строки, значение которых не входит в список:

-- Товары не из категорий 1 и 2
SELECT name, category_id FROM products
WHERE category_id NOT IN (1, 2);

IS NULL / IS NOT NULL — проверка на пустое значение

NULL в SQL — это отсутствие значения. Это не ноль и не пустая строка, это именно "значения нет". Сравнивать с NULL через = нельзя — это не работает:

-- Так НЕ работает:
SELECT * FROM products WHERE stock = NULL;    -- всегда пустой результат

-- Так правильно:
SELECT * FROM products WHERE stock IS NULL;

В нашей базе данных все поля заполнены, поэтому добавим тестовый случай чтобы увидеть NULL в действии:

-- Добавим товар без указания остатка (stock не обязательный по умолчанию)
-- Пока не делаем этого - просто запомните синтаксис:

-- Найти строки где значение есть:
SELECT name, stock FROM products
WHERE stock IS NOT NULL;

-- Найти строки где значение отсутствует:
SELECT name, stock FROM products
WHERE stock IS NULL;

Правило: для проверки на NULL всегда используйте IS NULL и IS NOT NULL. Оператор = с NULL никогда не работает как ожидается — это особенность SQL.

Вопросы

  1. Чем отличается SELECT * от SELECT name, price?
  2. Что произойдёт если написать WHERE price = NULL вместо WHERE price IS NULL?
  3. Какой результат вернёт запрос: WHERE price < 5000 AND stock > 100 OR category_id = 4?
  4. Чем NOT IN ('shipped', 'pending') отличается от IN ('delivered', 'cancelled')? Когда результаты будут одинаковы?
  5. Напишите шаблон LIKE который найдёт все строки, содержащие слово "книга" в любом месте строки.
  6. Почему в SQL строки пишутся в одинарных кавычках, а не в двойных?
  7. Как найти всех пользователей, у которых email заканчивается на .ru (любой домен)?
  8. Чем OR отличается от IN при проверке одного столбца на несколько значений? Когда лучше использовать IN?
  9. Что вернёт запрос SELECT name FROM products WHERE category_id NOT IN (1, 2, 3, 4, 5)?
  10. Можно ли использовать WHERE без SELECT? Есть ли другие команды SQL которые используют WHERE?

Задачи

Задача 1.

Выведите только названия и цены всех товаров из таблицы products.

Задача 2.

Найдите всех пользователей из Санкт-Петербурга. Выведите имя, email и город.

Задача 3.

Найдите все товары с ценой от 5000 до 30000 рублей включительно. Выведите название и цену.

Задача 4.

Выведите все заказы со статусом delivered. Выведите id заказа, user_id и дату создания.

Задача 5.

Найдите все товары, в названии которых встречается слово "Смартфон". Выведите название и цену.

Задача 6.

Найдите пользователей, зарегистрированных в марте или апреле 2024 года. Выведите имя и дату регистрации. Даты хранятся как текст в формате YYYY-MM-DD.

Задача 7.

Выведите товары из категорий с category_id равным 3 или 4 (мебель и книги), у которых на складе больше 10 штук. Выведите название, цену и остаток.

Задача 8.

Найдите всех пользователей, email которых не зарегистрирован на gmail.com. Выведите имя и email.

Задача 9.

Найдите товары с ценой выше 50 000 рублей или с остатком на складе меньше 10 штук. Выведите название, цену и остаток.

Задача 10.

Найдите все заказы, которые не были ни доставлены (delivered), ни отменены (cancelled). Выведите id, user_id и статус. Решите задачу двумя способами: через NOT IN и через операторы сравнения с AND.

Предыдущий урок | Следующий урок