Sql

SELECT запити - Розширені можливості

Розширені оператори SELECT - IN, BETWEEN, LIKE з wildcards, функції дат та рядків, складні умови фільтрації

SELECT запити - Розширені можливості

Від базових до складних запитів

У попередній темі ми вивчили основи SELECT. Тепер розглянемо розширені можливості для більш гнучкої та потужної роботи з даними.

Loading diagram...

Оператор IN: Перевірка на список значень

IN дозволяє перевірити, чи value міститься в списку значень.

Базовий синтаксис

WHERE column IN (value1, value2, value3, ...)

Приклади

-- ❌ Довгий спосіб (з OR)
SELECT * FROM Students
WHERE YEAR(BirthDate) = 1997
   OR YEAR(BirthDate) = 1998
   OR YEAR(BirthDate) = 1999;

-- ✅ Коротший спосіб (з IN)
SELECT * FROM Students
WHERE YEAR(BirthDate) IN (1997, 1998, 1999);

SELECT * FROM Students
WHERE LastName IN ('Петренко', 'Коваленко', 'Шевченко');

-- Студенти НЕ народжені в 1997 та 1999
SELECT * FROM Students
WHERE YEAR(BirthDate) NOT IN (1997, 1999);

IN з підзапитами

IN може використовувати результат іншого SELECT:

-- Студенти зі стипендією вище середньої
SELECT * FROM Students
WHERE Grants IN (
    SELECT Grants
    FROM Students
    WHERE Grants > (SELECT AVG(Grants) FROM Students)
);

Performance: IN з невеликим списком констант (до 10-20 значень) - швидко. Для великих списків або складних підзапитів розгляньте альтернативи (JOIN, EXISTS).

Оператор BETWEEN: Діапазони значень

BETWEEN перевіряє, чи значення знаходиться в діапазоні (включно з межами).

Базовий синтаксис

WHERE column BETWEEN value1 AND value2
-- Еквівалентно: column >= value1 AND column <= value2

Приклади з числами

-- Студенти зі стипендією від 1300 до 1500
SELECT FirstName, LastName, Grants
FROM Students
WHERE Grants BETWEEN 1300 AND 1500;

-- Еквівалентно:
WHERE Grants >= 1300 AND Grants <= 1500;

BETWEEN включає обидві межі:

BETWEEN 1300 AND 1500 означає >= 1300 AND <= 1500
Включає як 1300, так і 1500

BETWEEN з датами

-- Студенти народжені в 1998 році
SELECT * FROM Students
WHERE BirthDate BETWEEN '1998-01-01' AND '1998-12-31';

-- Студенти народжені в першому півріччі 1998
SELECT * FROM Students
WHERE BirthDate BETWEEN '1998-01-01' AND '1998-06-30';

NOT BETWEEN

-- Студенти зі стипендією НЕ в діапазоні 1300-1500
SELECT * FROM Students
WHERE Grants NOT BETWEEN 1300 AND 1500;

-- Еквівалентно:
WHERE Grants < 1300 OR Grants > 1500;

BETWEEN vs операторів порівняння

WHERE Age BETWEEN 18 AND 25
WHERE Price BETWEEN 100 AND 500
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31'

Переваги: Коротше, зрозуміліше

WHERE Age >= 18 AND Age < 25         -- Виключити 25!
WHERE Price > 100 AND Price <= 500   -- Виключити 100!

Переваги: Більше контролю над межами

LIKE: Pattern Matching (пошук за шаблоном)

LIKE дозволяє шукати текст за шаблоном з використанням wildcards.

Wildcards (спеціальні символи)

Wildcard	Значення	Приклад	Знайде
`%`	Будь-яка кількість символів (0+)	`'%енко'`	Петренко, Коваленко
`_`	Рівно один символ	`'А___'`	Анна, Алла (4 букви)
`[]`	Один символ зі списку	`'[МА]%'`	Марія, Анна
`[^]`	Один символ НЕ зі списку	`'[^М]%'`	Іван, Анна (не М)

Приклади з % (будь-яка кількість)

-- Прізвища що починаються з "Пет"
SELECT * FROM Students
WHERE LastName LIKE 'Пет%';
-- Знайде: Петренко, Петров, ...

-- Прізвища що закінчуються на "енко"
SELECT * FROM Students
WHERE LastName LIKE '%енко';
-- Знайде: Петренко, Коваленко, Ткаченко, ...

-- Прізвища що містять "ов"
SELECT * FROM Students
WHERE LastName LIKE '%ов%';
-- Знайде: Мороз [немає], Коваль [немає], але знайде якби був Петров

-- Email з Gmail
SELECT * FROM Students
WHERE Email LIKE '%@gmail.com';

Приклади з _ (один символ)

-- Імена з 4 літер
SELECT * FROM Students
WHERE FirstName LIKE '____';  -- 4 підкреслення
-- Знайде: Іван (4 букви), НЕ знайде: Марія (5 букв)

-- Другаа літера 'а'
SELECT * FROM Students
WHERE FirstName LIKE '_а%';
-- Знайде: Марія (М-а-...), NOT Іван

Приклади з (список символів)

-- Прізвища що починаються з М або К
SELECT * FROM Students
WHERE LastName LIKE '[МК]%';
-- Знайде: Мороз, Мельник, Коваленко, Коваль

-- Email з цифрами на початку
SELECT * FROM Students
WHERE Email LIKE '[0-9]%';
-- Знайде: 123test@..., 5user@..., НЕ знайде: user123@...

-- Імена що починаються з голосної
SELECT * FROM Students
WHERE FirstName LIKE '[АЕИІОУЯЮЄЇаеиоуяюєї]%';

Приклади з ^ (НЕ зі списку)

-- Прізвища що НЕ починаються з П або К
SELECT * FROM Students
WHERE LastName LIKE '[^ПК]%';
-- Знайде: Мороз, Шевченко, НЕ знайде: Петренко, Коваленко

-- Імена БЕЗ цифр
SELECT * FROM Students
WHERE FirstName NOT LIKE '%[0-9]%';

ESCAPE: Екранування спецсимволів

Що робити, якщо потрібно знайти сам символ % або _?

-- Знайти email що містить підкреслення
SELECT * FROM Students
WHERE Email LIKE '%!_%' ESCAPE '!';
--                    ^         ^^^
--              екранували _    символ escape

-- Знайти продукти з % у назві "50% discount"
SELECT * FROM Products
WHERE ProductName LIKE '%!%%' ESCAPE '!';

Case Sensitivity

За замовчуванням LIKE case-insensitive:

WHERE LastName LIKE 'петренко'  -- Знайде "Петренко", "ПЕТРЕНКО"

Для case-sensitive:

WHERE LastName COLLATE Latin1_General_CS_AS LIKE 'Петренко'

Performance попередження: LIKE з % на початку ('%value') НЕ використовує індекси і може бути повільним на великих таблицях. По можливості уникайте patterns типу '%value%'.

Робота з датами

SQL Server надає багато функцій для роботи з датами.

Функції витягування компонентів

SELECT
    FirstName,
    BirthDate,
    YEAR(BirthDate) AS BirthYear,
    MONTH(BirthDate) AS BirthMonth,
    DAY(BirthDate) AS BirthDay
FROM Students;

SELECT
    FirstName,
    BirthDate,
    DATENAME(MONTH, BirthDate) AS MonthName,
    DATENAME(WEEKDAY, BirthDate) AS DayOfWeek
FROM Students;
-- Результат: 'January', 'Monday'

SELECT
    FirstName,
    DATEPART(WEEKDAY, BirthDate) AS DayNumber
FROM Students;
-- Результат: 1 = Sunday, 2 = Monday, ...

Поточна дата і час

SELECT
    GETDATE() AS CurrentDateTime,          -- 2024-02-07 22:30:45.123
    SYSDATETIME() AS HighPrecision,        -- Більше точності
    GETUTCDATE() AS UTC;                   -- UTC час

DATEADD: Додавання до дати

-- Формат: DATEADD(datepart, number, date)

SELECT
    BirthDate,
    DATEADD(YEAR, 18, BirthDate) AS AdultDate,      -- +18 років
    DATEADD(MONTH, 6, BirthDate) AS HalfYearLater,  -- +6 місяців
    DATEADD(DAY, -30, GETDATE()) AS MonthAgo        -- -30 днів від сьогодні
FROM Students;

datepart може бути:

YEAR, MONTH, DAY
HOUR, MINUTE, SECOND
WEEK, QUARTER

DATEDIFF: Різниця між датами

-- Формат: DATEDIFF(datepart, startdate, enddate)

SELECT
    FirstName,
    BirthDate,
    DATEDIFF(YEAR, BirthDate, GETDATE()) AS ApproximateAge,
    DATEDIFF(DAY, BirthDate, GETDATE()) AS DaysOld,
    DATEDIFF(MONTH, BirthDate, GETDATE()) AS MonthsOld
FROM Students;

DATEDIFF(YEAR, ...) НЕ дає точний вік!

DATEDIFF(YEAR, '1998-12-31', '1999-01-01') = 1
-- Але різниця лише 1 день!

-- Для точного віку:
DATEDIFF(YEAR, BirthDate, GETDATE()) -
    CASE
        WHEN MONTH(BirthDate) > MONTH(GETDATE()) OR
             (MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE()))
        THEN 1
        ELSE 0
    END AS ExactAge

Практичні приклади з датами

-- Студенти народжені в січні
SELECT * FROM Students
WHERE MONTH(BirthDate) = 1;

-- Студенти народжені в останні 25 років
SELECT * FROM Students
WHERE BirthDate >= DATEADD(YEAR, -25, GETDATE());

-- Студенти народжені у вівторок
SELECT * FROM Students
WHERE DATENAME(WEEKDAY, BirthDate) = 'Tuesday';

-- Студенти у яких день народження цього місяця
SELECT * FROM Students
WHERE MONTH(BirthDate) = MONTH(GETDATE());

Рядкові функції

SQL Server має багато функцій для обробки тексту.

LEN: Довжина рядка

SELECT
    FirstName,
    LEN(FirstName) AS NameLength,
    LEN(Email) AS EmailLength
FROM Students;

LEN()не рахує trailing пробіли: LEN(' text ') = 4, не 6!

Для підрахунку з пробілами: DATALENGTH(column)

SUBSTRING: Витягування підрядка

-- SUBSTRING(string, start, length)

SELECT
    FirstName,
    SUBSTRING(FirstName, 1, 3) AS First3Chars,
    SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS EmailUsername
FROM Students;

Приклади:

SUBSTRING('Іван', 1, 2) → 'Ів'
SUBSTRING('Петренко', 4, 3) → 'рен'

LEFT і RIGHT

SELECT
    FirstName,
    LEFT(FirstName, 2) AS First2,      -- Перші 2 символи
    RIGHT(FirstName, 2) AS Last2       -- Останні 2 символи
FROM Students;

UPPER, LOWER: Зміна регістру

SELECT
    FirstName,
    UPPER(FirstName) AS Uppercase,     -- ІВАН
    LOWER(FirstName) AS Lowercase,     -- іван
    UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, LEN(FirstName))) AS Capitalized
FROM Students;

TRIM, LTRIM, RTRIM: Видалення пробілів

SELECT
    LTRIM('  text  ') AS LeftTrimmed,    -- 'text  '
    RTRIM('  text  ') AS RightTrimmed,   -- '  text'
    TRIM('  text  ') AS BothTrimmed;     -- 'text'

REPLACE: Заміна підрядка

SELECT
    Email,
    REPLACE(Email, '@example.com', '@newdomain.com') AS NewEmail
FROM Students;

-- 'ivan@example.com' → 'ivan@newdomain.com'

CHARINDEX: Пошук підрядка

-- CHARINDEX(substring, string) - повертає позицію або 0

SELECT
    Email,
    CHARINDEX('@', Email) AS AtPosition,
    CASE
        WHEN CHARINDEX('@gmail', Email) > 0 THEN 'Gmail'
        WHEN CHARINDEX('@example', Email) > 0 THEN 'Example'
        ELSE 'Other'
    END AS EmailProvider
FROM Students;

CONCAT: Об'єднання рядків

-- CONCAT ігнорує NULL (на відміну від +)

SELECT
    CONCAT(FirstName, ' ', LastName) AS FullName,
    CONCAT('Email: ', Email, ' (verified)') AS EmailInfo
FROM Students;
-- Якщо Email = NULL, результат: 'Email:  (verified)' (не NULL!)

Практичні приклади з рядками

-- Email username (до @)
SELECT
    Email,
    SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS Username
FROM Students
WHERE Email IS NOT NULL;

-- Перша літера прізвища
SELECT
    FirstName,
    LEFT(LastName, 1) + '.' AS LastInitial
FROM Students;

-- Пошук студентів з довгими іменами
SELECT * FROM Students
WHERE LEN(FirstName) > 6;

-- Заміна домену email
SELECT
    FirstName,
    REPLACE(Email, 'example.com', 'university.edu') AS UniversityEmail
FROM Students;

Комбінування складних умов

Тепер, коли ми знаємо всі ці оператори та функції, можемо створювати дуже складні запити.

Приклад 1: Множинні умови

SELECT
    FirstName,
    LastName,
    BirthDate,
    Grants,
    Email
FROM Students
WHERE
    (
        -- Група 1: Народжені в 1998 зі стипендією
        (YEAR(BirthDate) = 1998 AND Grants > 1300)
        OR
        -- Група 2: Прізвище на "енко" з Email
        (LastName LIKE '%енко' AND Email IS NOT NULL)
    )
    AND
    -- Додаткова умова для всіх
    MONTH(BirthDate) NOT IN (1, 12)  -- Не січень і не грудень
ORDER BY Grants DESC, LastName ASC;

Приклад 2: Пошук з pattern matching

-- Знайти студентів:
-- - З Gmail email
-- - АБО прізвище містить "ов" або "ев"
-- - АБО ім'я починається з голосної
SELECT * FROM Students
WHERE
    Email LIKE '%@gmail.com'
    OR LastName LIKE '%[ое]в%'
    OR FirstName LIKE '[АЕИІОУаеиоу]%';

Приклад 3: Складна робота з датами

-- Студенти у яких:
-- - День народження в наступні 30 днів
SELECT
    FirstName,
    LastName,
    BirthDate,
    DATENAME(MONTH, BirthDate) + ' ' + CAST(DAY(BirthDate) AS NVARCHAR) AS Birthday
FROM Students
WHERE
    (
        -- Цього року ще не було ДН
        MONTH(BirthDate) > MONTH(GETDATE())
        OR (MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) >= DAY(GETDATE()))
    )
    AND
    -- В наступні 30 днів
    DATEADD(YEAR, YEAR(GETDATE()) - YEAR(BirthDate), BirthDate)
        BETWEEN GETDATE() AND DATEADD(DAY, 30, GETDATE());

Практичні завдання

Знайдіть студентів які:

Народилися в лютому, березні або квітні (використайте IN)
АБО стипендія в діапазоні 1400-1600

💡 Розв'язок

SELECT FirstName, LastName, BirthDate, Grants
FROM Students
WHERE MONTH(BirthDate) IN (2, 3, 4)
   OR Grants BETWEEN 1400 AND 1600;

Знайдіть студентів у яких:

Прізвище закінчується на "енко"
І Email НЕ містить цифр
І ім'я містить літеру 'а'

💡 Розв'язок

SELECT FirstName, LastName, Email
FROM Students
WHERE LastName LIKE '%енко'
  AND Email NOT LIKE '%[0-9]%'
  AND FirstName LIKE '%а%';

Знайдіть студентів старших за 25 років, відсортуйте від старшого до молодшого.

💡 Розв'язок

SELECT
    FirstName,
    LastName,
    BirthDate,
    DATEDIFF(YEAR, BirthDate, GETDATE()) AS ApproximateAge
FROM Students
WHERE DATEDIFF(YEAR, BirthDate, GETDATE()) > 25
ORDER BY BirthDate ASC;  -- Раніше народження = старший

Створіть звіт який показує:

Повне ім'я (Прізвище І.)
Email username (частина до @)
Довжину імені

💡 Розв'язок

SELECT
    LastName + ' ' + LEFT(FirstName, 1) + '.' AS ShortName,
    SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS EmailUsername,
    LEN(FirstName) AS NameLength
FROM Students
WHERE Email IS NOT NULL;

Резюме

Ключові моменти розширених SELECT:

IN — перевірка на список значень (замість множинних OR)
BETWEEN — діапазони (включає обидві межі)
LIKE з wildcards:
- % — будь-яка кількість символів
- _ — рівно 1 символ
- [] — символ зі списку
- [^] — символ НЕ зі списку
Функції дат:
- YEAR, MONTH, DAY — витягування компонентів
- GETDATE() — поточна дата/час
- DATEADD — додавання до дати
- DATEDIFF — різниця між датами
Рядкові функції:
- LEN, SUBSTRING, LEFT, RIGHT
- UPPER, LOWER, TRIM
- REPLACE, CHARINDEX, CONCAT
Комбінування — використовуйте дужки для складних умов

Наступний крок: Навчіться додавати дані за допомогою INSERT запитів.

Пов'язані теми:

Edit this pageorReport an issue

SELECT запити - Основи

Детальна анатомія SELECT запитів - вибірка даних, фільтрація WHERE, сортування ORDER BY, обмеження результатів TOP/DISTINCT

INSERT запити - Додавання даних

Детальний розбір INSERT запитів - додавання одного рядка, множинних рядків, INSERT SELECT, робота з IDENTITY та DEFAULT