Презентація на тему:
Основи мови запитів

Спецкурс “Основи баз даних” Розділ 9. Основи мови запитів За підручником І. О. Завадського

Повторення Що таке мова маніпулювання даними? Яка мова маніпулювання даними найбільш поширена в реляційній моделі? Який вигляд має вікно конструктора запитів? Що таке віртуальна таблиця?

Основи мови запитів Практично в усіх реляційних СКБД для маніпулювання даними застосовують мову SQL (англ. Structured Query Language — мова структурованих запитів), а такі автоматизовані засоби створення запитів, як ми розглядали в попередньому розділі, представлені лише в поодиноких системах керування базами даних. Основний спосіб конструювання запитів до баз даних — це їх запис у текстовому вигляді, чимось подібний до написання невеличких програм. Однак мова SQL не є мовою програмування, вона декларативна, тобто дозволяє користувачу описати, що він хоче отримати, не описуючи, як саме комп'ютер має обчислити потрібний результат. Тому писати запити мовою SQL значно легше, ніж програми будь-якою мовою програмування. Загалом є кілька різновидів запитів: на вибирання даних, їх додавання, видалення, оновлення та деякі інші. Навчимося описувати мовою SQL нескладні запити на вибирання даних.

Загальна структура SQL-запиту Ви вже вмієте створювати запити на вибирання даних, або вибіркові запити за допомогою конструктора запитів MS Access. Відмінною рисою цього типу запитів є те, що вони не змінюють дані в базі, а лише вибирають їх з таблиць за певними умовами. Результатом виконання будь-якого вибіркового запиту є віртуальна таблиця, що існує нетривалий час, поки ви її не закриєте.

Вправа 9.1 Зрозуміти структуру вибіркового SQL-запиту найлегше на конкретному прикладі. Запишіть мовою SQL і виконайте запит визначити прізвища та імена учнів-хлопців. Перейти до виконання вправи 9.1

Вирази, які починаються з ключових слів, часто називають фразами, наприклад фраза SELECT. Регістр ключових слів неважливий, але їх прийнято записувати великими літерами. Мова SQL англізована, тобто її вирази дещо нагадують англійські речення. Слово SELECT перекладається як «вибрати», слово FROM — «з», a WHERE — «де», або «для яких».

Схема простого SQL-запиту У цілому простий SQL-запит потрібно читати за такою схемою. Запит select х from у where z слід інтерпретувати так: “вибрати поля х тих записів таблиці у, які задовольняють умову z”.

До уваги! Якщо запит із вправи 9.1 скласти в конструкторі, а потім перейти в режим SQL, то ми побачимо такий текст, автоматично сконструйований СКБД: SELECT Учні.прізвище, Учні, [ім'я] FROM Учні where (((Учні.стать)="ч")); За змістом він нічим не відрізняється від тексту, записаного нами в SQL-редакторі, але виглядає складнішим через те, що під час автоматичної побудови SQL-виразів MS Access вводить деякі зайві позначення. Так, після слова WHERE поставлено непотрібні дужки, а перед назвами атрибутів записано назву таблиці з символом крапки. Вираз Учні.прізвище можна читати як «поле прізвище таблиці Учні». Проте якщо у запиті всього одна таблиця, то поле прізвище ніякій іншій таблиці не може належати і тому специфікатор Учні. можна опустити.

Завдання 9.1 Запишіть мовою SQL запит визначити прізвища та номери паспортів учителів-математиків. Як перейти в режим уведення SQL-коду, описано у вправі 9.1.

З'єднання таблиць Розглянемо запит визначити прізвища та імена вчителів, що викладають в 11 класі, який ми вже реалізовували за допомогою конструктора запитів. Якщо створити цей запит у конструкторі, а потім відобразити його SQL-код та видалити зайві дужки і специфікатори, результат буде таким: SELECT прізвище,[ім'я] FROM Учителі INNER JOIN Викладання ON Учителі.паспорт = Викладання.учитель WHERE клас=“11А” У фразі FROM ми бачимо не назву таблиці і не список назв, а оператор INNER JOIN (у перекладі з англійської — внутрішнє з'єднання), призначений для з'єднання таблиць. Загальний синтаксис цього оператора такий: Таблиця1 INNER JOIN Таблиця2 ON умова

Умова зазвичай стосується обох таблиць, вказаних до і після слів INNER JOIN (у прикладі вище — таблиць Учителі та Викладання). Оператор «зчіплює» ті записи таблиці 1 і таблиці 2, які, разом узяті, відповідають умові. У результаті з таких зчіпок формується нова таблиця, до якої вже застосовується відбір рядків згідно з фразою WHERE, а потім — відбір стовпців згідно з фразою SELECT.

Алгоритм, за яким СКБД виконує запит

У загальному випадку SQL-запит виконується так: Виконується операція з'єднання таблиць із фрази FROM. До отриманої таблиці застосовується операція вибірки: відбираються ті записи, що задовольняють умову, вказану у фразі WHERE. Таблиця, отримана на попередньому кроці, проектується на поля, вказані у фразі SELECT, тобто ці поля залишаються, а всі інші відкидаються. Більшість вибіркових запитів до реляційних баз даних (але не всі) виконуються саме за цією схемою: з'єднання таблиць — вибірка записів — проекція на поля.

До уваги! З'єднання, вибірка і проекція — три з восьми операцій реляційноі алгебри Кодда. Реляційна алгебра — це мова маніпулювання даними, запропонована автором реляційноі моделі Е. Коддом. Багато її рис увібрала в себе мова SQL.

Завдання 9.2 Запишіть оператори INNER JOIN, які дозволять отримати в базі даних школа такі з'єднання таблиць, як показано на рис.9.4, ст. 143

Завдання 9.3 Реалізуйте мовою SQL запит визначити, у якому класі класним керівником є Сошко Катерина Миколаївна.

До уваги! З'єднувати таблиці можна і без оператора INNER JOIN. Для цього слід перелічити їхні назви через кому у фразі FROM, а у фразі WHERE записати умову з'єднання записів, долучивши її до інших умов сполучником AND. Наприклад, запит визначити прізвища та імена вчителів, що викладають в 11А класі можна реалізувати так: SELECT прізвище,[ім'я] FROM Учителі.Викладання WHERE клас="11А" AND Учителі.паспорт = Викладання.учитель

Підзапити Властивість реляційної замкненості мови SQL полягає в тому, що результатом будь-якого запиту є таблиця, а отже, одні запити можна підставляти в інші замість таблиць. Загалом є два способи підстановки одного запиту в інший: замість імені таблиці в запиті вказують ім'я іншого запиту; в одному запиті записують повний текст іншого запиту, взятий у круглі дужки.

Підзапити Підстановку можна здійснювати у фразах FROM та WHERE. Особливо важливий і цікавий той випадок, коли у фразі WHERE одного запиту записують повний текст іншого запиту. Розглянемо цей випадок детально, реалізувавши запит визначити прізвища та імена вчителів, що викладають в 11А класі, з використанням підзапиту. SQL-текст запиту буде таким: SELECT прізвище,[ім'я] FROM Учителі WHERE паспорт IN (SELECT учитель FROM Викладання WHERE клас="11А") У фразі WHERE цього запиту вжито оператор IN (англ. «в», «належить»). Його загальний формат такий: значення IN (підзапит) Результатом підзапиту має бути таблиця з одним стовпцем. Якщо вказане перед словом IN значення в цьому стовпці є, то весь вираз значення IN (підзапит) вважається істинним, інакше — хибним.

Принцип дії оператора IN Розглянемо крок за кроком виконання запиту визначити прізвища та імена вчителів, що викладають в 11А класі з точки зору СКБД. 1. Виконуємо фразу FROM. Оскільки в ній вказано лише одну таблицю Учителі, то на першому кроці ми просто «беремо» цю таблицю для подальших операцій. 2. Перебираємо всі записи таблиці Учителі, перевіряючи для кожного, чи виконується умова, вказана після слова where. Ця перевірка здійснюється в два етапи. 2а. Виконуємо підзапит. Його результат — це набір номерів паспортів учителів 11А класу (згадайте структуру таблиці Викладання). 26. Перевіряємо, чи належить значення поля паспорт з поточного запису таблиці Учителі набору значень, отриманому на кроці 2а. Якщо належить, то запис у таблиці Учителі залишаємо, інакше — відкидаємо. 3. Проектуємо отриману на кроці 2 таблицю за полями прізвище та ім'я, тобто ці поля залишаємо, а всі інші — відкидаємо.

Висновок Таким чином, оператор IN дає змогу перевірити, чи належить значення множині результатів підзапиту. Зазначимо, що у фразу WHERE підзапит можна вставляти лише за допомогою логічного оператора, формуючи вираз, значення якого істинне або хибне. Однак це не обов'язково має бути оператор IN; є ще оператори EXISTS (англ. «існує»), ANY (англ. «будь-який»), ALL (усі). Так, значення виразу EXISTS (підзапит) буде істинним, якщо результат підзапиту містить хоча б один запис, і хибним, якщо результат підзапиту порожній.

Завдання 9.4 Використовуючи підзапит в операторі IN, реалізуйте мовою SQL запит визначити класи, у яких викладають вчителі на ім'я Петро.

Віднімання множин записів Можливості мови SQL значно ширші за можливості конструктора запитів MS Access і тому далеко не кожен SQL-запит може бути створений у вікні конструктора. Розглянемо найпростіший різновид запитів, які не можуть бути створені за допомогою конструктора. Це запити з запереченням, наприклад визначити прізвища та імена вчителів, які не викладають в 11А класі. Цей запит відрізняється від того, який ми розглядали раніше, наявністю частки «не». Якби її не було, ми би просто з'єднали таблицю вчителів із таблицею викладання, відібрали ті записи, які відповідають 10А класу, та спроектували результат на поля прізвище та ім'я. Але в запиті із часткою «не» нам потрібні не ці, а якраз всі інші вчителі. Тобто від множини всіх учителів нам потрібно відняти тих, які викладають у 10А класі. Найлегше це зробити за допомогою оператора NOT IN (англ. «не належить»): SELECT прізвище,[ім'я] FROM Учителі WHERE паспорт NOT IN (SELECT учитель FROM Викладання WHERE клас="11А") Буквально запит читається так: «вибрати прізвища та імена тих учителів, які не належать множині вчителів, що викладають в 11А класі».

Завдання 9.5 Реалізуйте мовою SQL запит визначити назви класів, у яких не вчиться жодної дівчини.