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. Вибірки з пошуком відсутніх даних

Для перевіки заповненості таблиці Результати тестування підготувати перелік незаповнених кодів. Зазначимо, що інколи для зручності користування знімається властивість Обязательное поле, тому важливі дані можуть бути не внесені у документ. Тому потрібно автоматизувати пошук відсутності даних у таких полях.

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.

⇐ Назад