A) Прості вибірки з фільтром
Наприклад, необхідно отримати результати тестування авто (див. Додаток А), які включають: або тестування несправності з кодом 3, тобто Код_несправності# =3, або результати лише автомобілів з кодом 5, тобто Код_автомобіля=5 , тоді фільтр буде записано так:
SELECT *
FROM [Результати тестування]
WHERE (([Результати тестування].[Код_несправності#])=3)) OR (([Результати тестування].Код_автомобіля)=5));
Пояснення:
· Відповідно до вимог базової мови імена таблиці включено у квадратні дужки (не плутати з квадратними дужками у синтаксисі SQL). При посиланні на поле таблиці, з тієї ж причини, вказується Таблиця та ім’я поля (у квадратних дужках), розділених крапкою.
· Знак “*” після SELECT вказує на те, що потрібно відібрати усі поля (без вертикального відбору) із таблиці, яка вказана у реченні FROM, та відібрати записи (горизонтальний відбір), які відповідають умовам, вказаним після WHERE.
· Предикат, який іде за WHERE, може включати оператори порівняння (=, <,= >, <, >=, <>),бульові оператори AND, NOT, OR, а також дужки для того, щоб зазначити потрібний порядк обчислення, тобто для однозначної інтерпретації умови відбору.
Речення WHERE може мати простіші умови, залежно від умов горизонтального відбору, наприклад:
SELECT *
FROM [Результати тестування]
WHERE ((([Результати тестування].[Код_несправності#])=3));
Коли користувач розроблює запит у режимі конструктора запитів, Microsoft Access автоматично створює еквівалентну інструкцію SQL.
Користувач має можливість переглядати та змінювати інструкції SQL у режимі SQL (команда меню Вид, Режм SQL). Зміни, що внесені у запит у режимі SQL, викликають відповідні зміни у бланку запита у режимі конструктора.
b) Прості вибірка з упорядкуванням
Наприклад, побудуємо однотабличний запит, у якому в алфавітному порядку по прізвищам контролерів буде створено перелік несправностей автомобілів, виявлених контролерами:
SELECT [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Назва_несправності, [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування]
FROM [Довідник несправностей]
ORDER BY [Довідник несправностей].Контролер;
Пояснення:
· ORDER BY вказує на упорядкування списку за значенням поля, яке вказано далі (за прізвищем контролера). DESC означає сортування за зменшенням , ASC- сортування за зростанням. Якщо опція DESC/ ASC не вказується, то по замовчуванню, записи результуючої таблиці упорядковуються за зростанням значень зазначеного після ORDER BY.
Зауваження. Якщо у режимі SQL замість переліку полів в інструкції SELECT поставити “*”, то у нашому прикладі на результат роботи це не вплине. Поясніть чому.
c) Запити з параметрами
Організувати оперативну довідку про результати тестування групи авто, код якої вводитися у процесі виконання запиту
SELECT [Результати тестування].Код_автомобіля, [Результати тестування].[Код_несправності#], [Результати тестування].[Код_системи_двигуна#]
FROM [Результати тестування]
WHERE ((([Результати тестування].Код_автомобіля)=[Введіть код автомобіля]))
ORDER BY [Результати тестування].[Код_несправності#];
5.1.2. Фільтрація та сортування у запитах
a) Наприклад, необхідно одержати перелік груп автомобілей та коди несправностей в кривошипно – шатунному механізмі (Код _частини _двигуна# =1)
SELECT [Результати тестування].[Код_автомобіля], [Результати тестування].[Код_несправності#]
FROM [Результати тестування]
WHERE ([Результати тестування].[Код_системи_двигуна#] =1)
ORDER BY [Результати тестування].Код_автомобіля;
Результуюча вибірка може включати дублюючі записи:
| Код_автомобіля | Код несправності# | |
b) Списки без дублюючих записів.
Щоб у список не включати повторення, вносимо зміни у SQL-інструкцію за допомогою ключового слова DISTINCT.
Вносимо зміни у SQL-інструкцію (у режимі SQL)
SELECT DISTINCT [Результати тестування].Код_автомобіля, [Результати тестування].[Код_несправності#]
FROM [Результати тестування]
WHERE ([Результати тестування].[Код_системи_двигуна#]=1)
ORDER BY [Результати тестування].[Код_автомобіля];
Запускаємо запит на виконання, отримуємо наступний список без дублюючих записів.
| Код_автомобіля | Код несправності# |
Пояснення та зауваження:
· Щоб упорядкувати записи результуючого набора, можна виконати сортування по будь-якому числу полів, але вони мають бути вказані у реченні SELECT.
· Речення ORDER BY <список полів> використовується для сортування і завжди має бути останнім в операторі SELECT.
· У списку полів речення ORDER BY можуть бути або імена полів, або їх порядкові позиції у списку речення SELECT.
· Якщо упорядковання проводиться за зростанням, то ключове слово ASC- може бути відсутнім (приймається по замовчуванню)
· Горизонтальну вибірку реалізує речення WHERE, яке завжди записують після речення FROM
· Предикати являють собою вирази, які можуть бути як простими виразами порівняння, так і являти собою комбінацію із будь-якого (скінченного) числа виразів, об’ єднаних логічними операторами AND, OR, NOT.
· У предикатах може використовуватись SQL-оператор IS та круглі дужки для зміни порядку виконання операцій.
· Предикат у мові SQL може приймати такі значення:
1. TRUE- истина –вірно, причому у числовому виразі- набуває значення 1;
2. FALSE- ложь- невірно, у числовому виразі набуває значення 0;
3. UNNOWN -неизвестно- невідомо у числовому виразі набуває значення 0,5.
Вкажемо правила комбінування:
· TRUE AND UNNOWN = UNNOWN,
· FALSE OR UNNOWN = UNNOWN,
· NOT UNNOWN= UNNOWN.
Для символьних виразів (у SQL тип даних CHARACTERE) може застосовуватись предикат порівняння. Дані типу Дата порівнюються у хронологічному порядку.
5.1.3. Проста вибірки з умовою на діапазон значень
Наприклад, необхідно створити перелік несправностей автомобіля, тестування яких коштує від 60 до 180 грошових одиниць. Перелік упорядкувати по спаданню ціни.
SELECT [Довідник несправностей].[Код_несправності#], [Довідник несправностей].[Назва_несправності], [Довідник несправностей].[Контролер], [Довідник несправностей].[Ціна тестування]
FROM [Довідник несправностей]
WHERE (([Довідник несправностей].[Ціна тестування]) Between 60 And 180)
ORDER BY [Довідник несправностей].[Ціна тестування] DESC;
Пояснення:
· Предикат Between перевіряє чи належать вказаному діапазону значення виразу, що перевіряється.
· Значення виразу, що перевіряється, та значення пограничних виразів повинні бути сумісними за типами даних.
5.1.4. Вибірки за зразком (з предикатом Like) та з використанням оператора IN
Розглянемо приклади:
a) Нехай необхідно вивести усі несправності, що вказують на гіперфункції роботи двигунів, що проходили тестування.
SELECT [Довідник несправностей].[Назва_несправності], [Довідник несправностей].[Контролер], [Довідник несправностей].[Ціна тестування]
FROM [Довідник несправностей]
WHERE ((([Довідник несправностей].Назва_несправності) Like "З*"));
Пояснення:
· Оператор Like призначено для порівняння рядкового виразу зі зразком, який потрібно задати після оператора Like у виразі SQL.
· Синтаксис оператора Like
выражение Like "образец"
· выражение – це вираз SQL, що використовується у реченні WHERE,
· образец це текст, з яким порівнююється вираз. Текст може включати маскуючі символи. Найбільш часто використовують наступні з них:
- символ “*” - замінює будь-які символи у будь-якій кількості символів, наприклад : а*, *а, *авс* , тощо;
- Символ “?”- замінює тільки один символ, , наприклад,: а??, ?арка, с???, a?с*, тощо.
Зауваження: У стандарті мови SQL замість знака “*” використовується знак “_”, а замість “?” використовують знак “%”.
b) Наведемо ще один приклад відбору даних за умовою:
SELECT [Системи двигуна].[Код_системи_двигуна#],
[Системи двигуна].Назва_системи
FROM [Системи двигуна]
WHERE (([Системи двигуна].Назва_системи) Not Like "Система*");
$ Рекомендується переглянути приклад та вказати призначення запиту самостійно.
Зауваження:
· У тих випадках, коли або вираз, що перевіряють, або зразок є порожніми величинами (мають значення NULL), то предикат приймає значення “ TRUE “.
· Якщо вираз і зразок мають одночасно довжину 0, то предикат приймає значення “TRUE”.
c) Оператор IN
Оператор IN використовують, у випадках необхідності перевірки співпадання значення виразу з одним із елементів вказанного списка.
Синтаксис оператора IN:
выражение [Not] In(значение_1, значение_2, . . .)
Аргументи оператора In:
· Выражение: Вираз, який визначає поле, значення якого потрібно знайти у списку;
· Значение_1, значение_2, …: Вираз або список виразів, з якими порів-нюється выражение.
Якщо выражение є у списку значень, оператор In повертає значення True; інакше - False. За допомогою логічного оператора Not можно перевірити обернену умову (выражение не належить списку значень).
Розглянемо приклад: Необхідно отримати перелік несправностей, тестування яких коштує 30, 34 та 50 одиниць.
SELECT [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Ціна тестування]
FROM [Довідник несправностей]
WHERE ((([Довідник несправностей].[Ціна тестування]) In (30,50,34)));
5.1.5. Вибірки з пошуком відсутніх даних
Для перевіки заповненості таблиці Результати тестування підготувати перелік незаповнених кодів. Зазначимо, що інколи для зручності користування знімається властивість Обязательное поле, тому важливі дані можуть бути не внесені у документ. Тому потрібно автоматизувати пошук відсутності даних у таких полях.
SELECT [Результати тестування].Код_автомобіля, [Результати тестування].[Код_несправності#], [Результати тестування].[Код_системи_двигуна#]
FROM [Результати тестування]
WHERE ((([Результати тестування].Код_автомобіля) Is Null)) OR ((([Результати тестування].Код_автомобіля) Is Null)) OR ((([Результати тестування].[Код_несправності#]) Is Null)) OR ((([Результати тестування].[Код_системи_двигуна#]) Is Null));
Пояснення:
· Якщо ім’я поля не є ключовим (а отже зустрічається лише в одній із таблиць бази даних), то таке ім’я поля можна не заключати у квадратні дужки.
· Предикат Is [Not] Null дозволяє перевірити наявність/відсутність даних у полях таблиці.
· Is [Not] Null може використовуватись для аналізу даних про виконання певних робіт (наприклад, при складанні переліка боржників).
·
5.1.6. Запити з агрегованими ми функціями
Стандартом передбачено наступні агрегованіі функції:
СOUNT(*), СOUNT(<поле>), AVG, MIN, MAX,
де AVG, MIN, MAX мають загальноприйняті значення.
Функція СOUNT(*) підраховує кількість значень, без враховання порожніх комірок.
СOUNT(<поле>)- підраховує кількість значень.
У простому запиті (без підзапитів) аргументом агрегованої функції не може бути інша агрегована функція, наприклад, не можна одержати максимальне значення серед середніх. Агреговані функції включаються у речення SELECT. Наведемо приклади розробки запитів з агрегованими функціями:
a) Вибрати із бази даних максимальну ціну тестування автомобіля і середню ціну для усіх тестувань, окрім тих, які проводять контролери, що мають прізвище, що не починається літерою “І”.
SELECT Max([Довідник несправностей].[Ціна тестування]) AS Найдорожче, Avg([Довідник несправностей].[Ціна тестування]) AS Середня_ціна
FROM [Довідник несправностей]
WHERE (([Довідник несправностей].Контролер) Not Like "І*");
Результати відбору даних:
| Найдорожче | Середня_ціна |
| 53,8 |
Пояснення:
· Якщо у задачі не вимагається виводити значення полів, а потрібно одержати лише єдине значення, розраховане за агрегованою функцію, то останню записують відразу після слова SELECT.
· Агреговані функції своїми аргументами мають імена відповідних полів.
· Результуючим полям можна задати нові імена, використавши ключове слово AS, яке записуємо відразу після даних, для яких вводитися псевдонім ( у нашому прикладі це – “Найдорожче” та “Середня_ціна”).
· Якщо нове ім’я складається з кількох слів, то останні записуються через підкреслювання “_”, наприклад Середня_ціна.
b) Використання агрегованих функцій при визначенні груп записів (групування).
Групи визначаються у реченні GROUP BY. Розглянемо приклад: Підготувати перелік кодів несправностей, визначити кількість протестованих несправностей та представити дані у спадаючому порядку по кількості.
SELECT [Результати тестування].[Код_несправності#], Count(*) AS [Кількість]
FROM [Результати тестування]
GROUP BY [Результати тестування].[Код_несправності#]
ORDER BY Count(*) DESC;
Пояснення:
· Агреговані функції, включені у речення SELECT.
· Інструкції без речення GROUP BY виконуються над усіма результуючими рядками цього запиту.
· Якщо у запиті є речення GROUP BY, то кожен набір рядків, який має однакові значення стовбчика чи групи стовбчиків, які задано у реченні GROUP BY, складають групу, агреговані функції виконуються для кожної групи.
Результат
| Код несправності# | Кількість |
· Count(*), повертає кількість записів у кожній сформованій у реченні GROUP BY групі. Сортування проводиться саме за вказаними значеннями коду групи автомобілей. Тобто у прикладі ми отримали відомості про кількістьвипробувань у кожній групі авто.
· Агреговані функції включаються у речення SELECT. При необхідності агрегованим функціям присвоюється псевдонім за допомогою AS.
Зауваження: Не лише теоретичні положення, але і ряд переглянутих нами запитів, вказують на те, що знак “ ; ” завжди повинен бути присутнім у кінці інструкції SQL.
c) Запити з підсумками та формуванням груп за змінною умовою
Розглянемо задачу: “Розробити запит для отримання оперативних даних про число проведених тестувань для даної групи автомобілів, номер якої задається у режимі виконання запиту”.
SELECT [Результати тестування].Код_автомобіля, Count([Результати тестування].[Код_несправності#]) AS [Кількість протестованих авто групи#]
FROM [Результати тестування]
GROUP BY [Результати тестування].Код_автомобіля
HAVING (([Результати тестування].Код_автомобіля)=[Введіть код автомобіля]);
Пояснення:
· Підрахувати кількість авто, що належать до певної групи (мають відповідний код), які пройшли тестування у групі, можна за допомогою функції Count, ця функція має аргумент – стовпчик, у якому ми підраховуємо кількість записів.
· Необхідно відібрати із стовпчика Код_несправності# (в таблиці Результати тестування ) за умовою, що коди авто мають певне значення, яке не є відомим заздалегідь.
· Із сказаного випливає, що потрібно сформувати групу, включивши у неї весь стовпчик Код_несправності#, а потім, залежно від введеного кода авто, відібрати із групи потрібні записи та підрахувати кількість таких записів.
Речення HAVING застосовуєтьсяпісля групування для визначення предиката, для фільтрування групи за вказаними у HAVING умовами відбору, наприклад
HAVING (([Результати тестування].Код_автомобіля)=[Введіть код автомобіля]);
Такий запит формує умови відбору по, введеному при виконанні запита, коду авто.
· Агрегована функція підраховує кількість записів у одержаній вибірці.
· У предикаті HAVING не можна використовувати псевдоніми для агрегованих функцій
· Предикат у HAVING можевикористовувати різні оператори порівняння, логічні опратори, тобто все, що відповідає побудові предикатів для речення WHERE, відноситься і до предиката речення HAVING.
Багатотабличні запити
Зазначимо, що на практиці більш часто вирішуються задачі, що потребують даних, які зберігаються у кількох Таблицях бази даних і пройшли певну обробку.
У синтаксисі інструкції SELECT (див. Розділ 3, стр. 18) у реченні FROM допускаєтьсявведення кількох таблиць, це означає, що можна створювати запити, що використовують дані кількох таблиць. Такі запити прийнято називати: “багатотабличними запитами”. Зазначимо, що у багатотабличних запитах, для таблиць бази даних можна використовувати псевдоніми.
5.2.1. Правило встановлення відповідності записів, що вибираються із різних Таблиць
Зазначимо, що просте перечислення Таблиць у реченні FROM відповідає реляційній операції декартового об’єднання, за допомогою якого число відібраних записів із вказаних Таблиць значно зростає і не відповідає вимогам користувача.
Тому потрібно вказувати правило встановлення відповідності об’єднання записів із різних таблиць.
Часто таким правилом може бути співпадання значень ключових полів. Вказане правило умови вибору відповідних даних з двох Таблиць (наприклад, імена таблиць будуть А та В) схематично можна представити наступним чином:
SELECT *
FROM А,В
WHERE a# =b#;
За таких умов об’єднуватись будуть тільки ті рядки, у яких співпадають значення ключових полів а# та b#. У процесі побудови логічної моделі умов доступа до даних можуть використовуватись і інші умови, але найчастіше використовується зазначена вище умова.
Розглянемо приклади:
Вибірка більше ніж з однієї таблиці
Приклад 1. Розв’яжемо задачу: “Підготувати дані про результати тестування перших трьох груп автомобілів, якщо тестування проводили контролери, прізвища яких починаються на літери від А до П, а коди частин двигуна, у яких виявлено несправності, можуть приймати значення 1, 2, 3”.
Для зручності викладу спочатку запишемо всю інструкцію, а потім розглянемо основні принципи побудови останньої.
SQL-інструкція для вирішення вказаної задачі може бути записана наступним чином:
SELECT [Результати тестування].[Код_автомобіля], [Довідник несправностей].Назва_несправності, [Результати тестування].[Код_несправності#], [Результати тестування].Довідка, [Результати тестування].[Код_системи_двигуна#], [Контролер].Пр
FROM [Контролер] INNER JOIN ([Довідник несправностей] INNER JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]) ON Контролер.[Код контролера] = [Довідник несправностей].Контролер
WHERE ((([Результати тестування].Код_автомобіля)<4) AND (([Результати тестування].[Код_системи_двигуна#])<4) AND ((Контролер.Пр)<"Р*"));
Результат відбору даних надано у наступній таблиці:
| Код_автомобіля | Назва_несправності | Код несправності# | Довідка | Код_системи_двигуна | Контролер |
| Перегрівання двигуна | 05.08.2010 | Іваненко А. А. | |||
| Перегрівання двигуна | 01.09.2010 | Іваненко А. А. | |||
| Перегрівання двигуна | 01.09.2010 | Іваненко А. А. | |||
| Перегрівання двигуна | 05.08.2010 | Іваненко А. А. | |||
| Збільшення витрат масла | 01.05.2010 | Іванчук К. І. | |||
| Збільшення витрат масла | 01.05.2010 | Іванчук К. І. |
Пояснення до задачі:
· В запиті дані відбираються із трьох таблиць [Довідник несправностей] , [Результати тестування], [Контролер].
· Відібрані записи використовують для формування результуючої таблиці.
· Для нашого прикладу відібрані записи із таблиць вважаються відповідними, якщо співпадають значення ключових полів, тобто виконується умова:
[Довідник несправностей].[Код_несправності#]=[Результати тестування].[Код_несправності#].
Як слідує із вимог задачі, записи, що задовольняють вказаній вище умові, можуть бути відібраними у результуючу таблицю, але не усі, а лише ті із них, які відповідають “додатковим” умовам. Звичайно назва “додаткові умови “ є досить умовною і вказує лише на той факт, що за цими умовами записи відбираються лише із записів, для яких виконануються вказані умови відповідності .
· Для нашого прикладу всі “додаткові” умови вибору виконуються одночасно і тому можуть бути записані наступним чином:
((([Результати тестування].Код_автомобіля)<=3) AND (([Довідник несправностей].Контролер)<"Р*") AND (([Результати тестування].[Код_системи_двигуна#])<4));
· Як зазначалось раніше, вказані умови повинні виконуватись одночасно з основними умовами вибору відповідних записів із таблиць.
Об’єднання основних та “додаткових” умов логічним AND утворює предикат, який записуємо у реченні WHERE.
Якщо дані вибираються із двох (і більше) таблиць і встановлено правило відповідності вибраних записів, то говорять, що проведено операції з’єднання таблиць, а про таблиці говорять, що таблиці – є з’язаними.
Способи операцій з’єднання двох таблиць передбачено у синтаксисі SELECT.