Тема: "Мови запитів QBE і SQL. Використання оператора SELECT"
Мета: засвоєння учнями поняття запиту SQL.
Тип уроку: комбінований.
Форма проведення уроку: лекція.
Методичне забезпечення: ілюстративний матеріал.
План заняття
- мови запитів;
- мова запитів за зразком;
- структурована мова запитів;
- поняття запиту SQL.
- призначення оператора SELECT;
- формат оператора SELECT;
- визначення полів;
- визначення зв’язків між таблицями;
- агрегатні функції.
Структура уроку
1. ОРГАНІЗАЦІЙНА ЧАСТИНА
· перевірка присутності учнів
· перевірка готовності до уроку
2. АКТУАЛІЗАЦІЯ ОПОРНИХ ЗНАНЬ УЧНІВ
1. Для чого призначені запити?
2. Які бувають запити?
3. Які Ви знаєте засоби створення запитів?
4. Який запит можна створити за допомогою майстра Простой запрос?
5. Який алгоритм створення простого запиту?
6. Які запити можна створити за допомогою конструктора?
7. Який алгоритм створення запиту за допомогою конструктора?
8. Чи можна створити простий запит у конструкторі?
9. Як задати порядок сортування у запиті?
10. Як задати умови відбору в запиті?
3. ПОЯСНЕННЯ НОВОГО МАТЕРІАЛУ
Для створення запитів у різних системах управління базами даних, як правило, використовуються дві основні мови опису запитів:
QBE (Query By Example) — мова запитів за зразком;
SQL (Structured Query Language) — структурована мова запитів.
Можливості маніпулювання даними в процесі створення запитів у цих мовах практично еквівалентні. Головна відмінність між ними полягає в способі формування запитів:
QBE дозволяє візуальне формування запиту;
SQL потребує програмування запиту.
Мова QBE дозволяє створювати складні запити за допомогою конструктора запитів. Такий спосіб створення запитів забезпечує високу наочність і не вимагає визначення алгоритму виконання операції — досить описати зразок результату, що очікується.
Під час створення запитів за допомогою мови QBE припустимі такі операції:
· вибір, обчислення і модифікація даних;
· вставка і вилучення записів.
Результатом виконання запиту може бути нова таблиця або оновлена вихідна таблиця.
Вибір, вставка, вилучення і модифікація даних можуть виконуватися з використанням умов, що задаються за допомогою логічних виразів. Обчислення над даними задаються за допомогою арифметичних виразів і породжують у відповідних таблицях нові поля, що обчислюються.
SQL (Structured Query Language – мова структурованих запитів) є набором програмних команд.
Мова SQL використовується для створення запитів, а також для оновлення й управління реляційними базами даних, такими, як бази даних Microsoft Access. Коли користувач створює запит в режимі конструктора запиту, Microsoft Access автоматично створює еквівалентну інструкцію SQL. Користувач має нагоду переглядати і змінювати інструкції SQL в режимі SQL.
Не всі запити можуть бути визначені в бланку запиту. Це запити до серверу, управляючі запити і запити на об'єднання. Для створення таких запитів потрібно ввести інструкцію SQL безпосередньо у вікно запиту в режимі SQL.
Іноді автоматично створюється інструкція SQL. Наприклад, під час використання майстра для створення форми або звіту, що одержує дані з кількох таблиць, автоматично створюється інструкція SQL, що стає значенням властивості Джерело рядків (Источник строк) форми або звіту. Під час створення списку або поля із списком за допомогою майстра також створюється інструкція SQL, що стає значенням властивості Джерело рядків (Источник строк) списку або поля зі списком.
Крім того, за допомогою інструкцій SQL визначаються підлеглі запити в бланку запиту, аргументи макрокоманди ЗапускЗапитуSQL (Запуск Запроса SQL) і конструкції в програмах.
Запит SQL — це запит, створений за допомогою інструкцій SQL
Прикладами запитів SQL можуть служити запити на об'єднання, запити до серверу, управляючі та підлеглі запити.
Досвідчені користувачі можуть працювати безпосередньо з базовою інструкцією запиту у вікні запиту в режимі SQL.
Оператори мови SQL створюють запити, що обробляються ядром бази даних.
Запит визначає:
· поля, які потрібно обробити;
· таблиці, що містять ці поля;
· діапазон записів;
· порядок представлення записів.
SQL – оператор зазвичай повертає записи у вигляді динамічного набору записів.
SQL-оператор складається з трьох складових частин:
1. Оголошення параметрів. Необов’язкові параметри, що передаються в SQL-оператор програмою. В цій частині зазначаються параметри, необхідні для SQL – оператора.
PARAMETERS <�ім’я параметру > <�тип даних >;
2. Керуючий оператор. Ця частина оператора повідомляє ядру обробки запитів тип операції (наприклад SELECT або DELETE).
3. Опціональні оголошення. Ці оголошення можуть передавати ядру обробки запитів інформацію про умови фільтрації, групування або сортування, які необхідно застосовувати до даних, що оброблюються (WHERE, GROUP BY і ORDER BY).
Формат SQL – оператора:
[Оголошення параметрів;] Керуючий оператор [Опціональне оголошення]
Керуючі оператори
Оператор
|
Функція
|
CREATE TABLE
|
створення таблиці
|
DROP TABLE
|
вилучення таблиці
|
CREATE INDEX
|
створення індексу
|
DROP INDEX
|
вилучення індексу
|
ALTER TABLE
|
зміна структури таблиці
|
SHOW DATABASE
|
перегляд БД
|
START DATABASE
|
активізація БД
|
STOP DATABASE
|
закриття БД
|
DROP DATABASE
|
вилучення БД
|
SELECT
|
вибірка записів
|
SELECT…INTO
|
створює нову таблицю на основі існуючої
|
UPDATE
|
зміна записів
|
TRANSFORM
|
створює перехресну таблицю
|
INSERT
|
вставка записів
|
DELETE
|
вилучення записів
|
Використання оператора SELECT
Оператор SELECT повертає записи та розміщує інформацію в динамічному наборі або в таблиці для подальшої програмної обробки.
Оператор SELECT дозволяє виконувати вибірку й обчислення над даними однієї чи декількох таблиць.
Формат оператора:
SELECT [ALL|DISTINCT] <�список полів > FROM <�список таблиць >
[WHERE <�умова вибірки >]
[GROUP BY <�ім'я стовпця > [, <�ім'я стовпця >]...]
[HAVING <�умова пошуку >]
[ORDER BY <�специфікація сортування >[,<�специфікація сортування >]...];
SELECT – ключове слово, яке повідомляє базі даних, що ця команда є запитом, тобто всі запити починаються цим словом і закінчуються “;”.
“;” повідомляє, що команда готова до виконання.
Визначення необхідних полів
Частина оператора <�список полів > використовується для визначення полів, що включаються до результуючого набору записів:
· можна включити всі поля таблиці – для цього використовують шаблон “*”;
· можна включити конкретні поля таблиці – вказуються імена полів через “,” (кому); якщо ім’я поля містить пропуск, то воно заключається в “[ ]”;
· можна включити поля з різних таблиць – тоді використовують складені імена <�ім'я таблиці>.<�ім'я поля>.
Предикат ALL – використовується для виділення всіх записів, що задовольняють визначеній умові (використовується за замовчанням).
Предикат DISTINCT - використовується для повернення лише одного запису для кожного набору значень полів, що вибираються.
Наприклад для визначення студентів, які здавали іспити:
SELECT DISTINCT SNum FROM Uspichn;
Визначення зв’язку між таблицями
Для зазначення зв’язку між таблицями використовуються дві директиви:
JOIN. Комбінує дві таблиці на основі вмісту полів кожної з таблиць і типу JOIN.
WHERE. Зазвичай використовується для фільтрації записів, проте може виконувати функції директиви INNER JOIN.
(У разі використання директиви WHERE для об’єднання таблиць створюється набір записів, призначений лише для читання).
Формат директиви JOIN:
імя_таблиці1 {INNER|LEFT|RIGHT} JOIN імя_таблиці2 ON імя_таблиці1.ключ1 = імя_таблиці2.ключ2
Ядро виконання запитів, що використовується у Access, підтримує три різновиди директиви JOIN.
Записи, що повертаються різними типами директиви JOIN
Тип JOIN
|
Записи з лівої таблиці
|
Записи з правої таблиці
|
INNER
|
Лише записи, для яких є відповідність у правій таблиці
|
Лише записи, для яких є відповідність у лівій таблиці
|
LEFT
|
Всі записи
|
Лише записи, для яких є відповідність у лівій таблиці
|
RIGHT
|
Лише записи, для яких є відповідність у правій таблиці
|
Всі записи
|
Ліва таблиця – перша таблиця із вказаних таблиць (ліворуч від ключового слова JOIN).
Права таблиця – друга (праворуч від ключового слова JOIN).
Наприклад:
SELECT Studenty.SPrizv, Studenty.SIma, Uspishnist.Ocinka, Uspishnist.DtZdachi
FROM Studenty RIGHT JOIN Uspishnist ON Studenty.SNom = Uspishnist.SNom
WHERE (((Uspishnist.Ocinka)>7) AND ((Uspishnist.DtZdachi)<=#6/10/1999#));
Використання директиви FROM
Ключове слово FROM – використовується для зазначення джерела інформації (якщо використовуються кілька таблиць, імена перелічуються через кому).
Використання директиви WHERE
За допомогою директиви WHERE можна встановити зв’язок між таблицями.
Наприклад:
WHERE Vykladachi.VKod = Predmety.Vkod
WHERE <�умова вибірки > – дозволяє встановити умову відбору записів.
Директива WHERE використовує чотири типу предикатів (логічний оператор, що визначає умову):
Comparison – порівняння значення поля з заданим значенням;
LIKE - порівняння значення поля з шаблоном;
IN - порівняння значення поля зі списком припустимих величин;
BETWEEN - порівняння значення поля з діапазоном величин.
Предикат Comparison виконує порівняння двох значень за допомогою операторів порівняння:
=, <>, >, <, >=, <= .
У порівняннях рядкова величина повинна заключатися в одинарні лапки (‘Ігор’), дати – в символ # (# 01/01/89#)
Предикат LIKE використовується лише для порівняння рядків. За його допомогою можна порівняти вираз (значення поля) з заданим шаблоном. Для створення шаблонів використовуються символи * і ?
У предикаті LIKE можуть використовуватись символи шаблонів та списки - діапазони символів.
Предикат IN –дозволяє перевіряти чи співпадає значення виразу з однією з декількох величин.
Предикат BETWEEN перевіряє, чи співпадає значення виразу з величиною, що належить визначеному діапазону, включаючи його межі. Використовується для порівняння рядків, чисел і дат.
Наприклад:
...BETWEEN ‘M’ AND ‘P’
...BETWEEN 1 AND 2.5
...BETWEEN #08/01/94# AND #8/10/94#
...NOT BETWEEN ‘M’ AND ‘P’ - повертає записи, що не належать визначеному діапазону
У директиві WHERE можна комбінувати кілька умов за допомогою логічних операторів AND і OR, а також можна використовувати в умовах оператор NOT.
Використання директиви ORDER BY
ORDER BY - сортування записів.
ASC – ключове слово, що використовується для сортування за зростанням.
DESC – сортування за спаданням.
Наприклад:
... ORDER BY Styp DESC, Spizv ASC;
Використання агрегатних функцій
За допомогою агрегатних функцій оператор SELECT може бути використаним для виконання розрахунків даних у таблиці. Функції вказуються в операторі як звичайні поля, проте використовують імена полів як аргументи.
Агрегатні функції
Функція
|
Результат
|
Avg
|
Середньоарифметичне від значень поля
|
Count
|
Кількість записів
|
Min
|
Мінімальне значення поля
|
Max
|
Максимальне значення поля
|
Sum
|
Сума значень поля
|
First
|
Значення поля для першого запису в наборі
|
Last
|
Значення поля для останнього запису в наборі
|
Наприклад:
SELECT Sum(Styp) FROM Stydenty;
SELECT Count(*) FROM Stydenty;
SELECT Count(ALL Snum) FROM Uspichn;
SELECT Sum(Ocinka) FROM Uspichn;
SELECT Max(Styp*2) FROM Stydenty;
Якщо в запиті не використовувалась директива GROUP BY, то агрегатні функції повертають лише одне значення для всього набору записів.
Під час використання директиви GROUP BY значення функції обчислюється і повертається для кожної групи.
Використання директиви GROUP BY
Використання груп записів дозволяє створювати набори записів, що включають по одному запису для кожного з можливих значень зазначеного поля.
4. ЗАКРІПЛЕННЯ ВИВЧЕНОГО МАТЕРІАЛУ
1. Для чого призначені запити?
2. Які бувають запити?
3. Які Ви знаєте засоби створення запитів?
4. Що представляє собою мова SQL?
5. Що Ви розумієте під запитом SQL?
6. Які складові частини оператора SQL?
7. Який формат оператора SQL?
8. Яке призначення оператора SELECT?
9. Для чого використовується директива From?
10. Для чого використовується директива From?
11. Коли використовується предикат LIKE?
12. Які функції використовуються для отримання підсумкових даних?
13. Для чого використовується команда GROUP BY?
14. Як задати порядок сортування результатів запиту?
15. За допомогою яких директив можна задати параметри зв’язку між об’єктами бази даних?
5. ДОМАШНЄ ЗАВДАННЯ
1. Навчальний посібник: Гуревич Р.С., Жиліна Л.В., Кадемія М.Ю. "Організація баз даних у MS Access".
§ 3.1.1. Мови запитів QBE і SQL.
§ 3.1.2. Оператори мови SQL.
§ 3.1.3. Використання оператора SELECT.
|