· У лівому полі головного вікна вибрати елемент «Запити»
· У поле «Завдання» вибрати елемент «Створити запит в режимі дизайну»
· Вибрати у вікні таблицю «Студент», з якої будемо вибирати дані
· Натиснути кнопки «Додати», «Закрити».
Відкривається конструктор таблиць. У верхньому полі відображаються таблиці, з яких буде здійснюватися вибірка даних і зв'язку між ними. У нижньому полі налаштовуються виведені колонки таблиць.
· Подвійним клацанням на потрібних полях додаємо колонки в нижнє поле.
· Для виконання запиту натиснути кнопку «Виконати запит» на панелі інструментів. У верхній частині конструктора запитів проявляється область виведення результатів, в яку виводяться всі записи таблиці «Студент»
Щоб відсортувати прізвища студентів за алфавітом треба підвести покажчик миші до поля «Сортування» у колонці «ПІБ», двічі клацнути лівою кнопкою миші і вибрати з списку, що розкрився «по зростанню». Зрозуміло, треба заново виконати запит.
Якщо додати сортування по групах отримаємо список, відсортований по групах, причому в межах групи студенти відсортовані по ПІБ. Завжди при сортуванні ліва колонка має пріоритет.
Щоб поміняти порядок колонок, потрібно натиснути ліву кнопку миші в заголовку (сірої рядку) таблиці колонок, не відпускаючи її, перемістити покажчик миші в область заголовка інший колонки і відпустити кнопку миші.
Якщо нас цікавлять студенти тільки однієї групи в полі «Критерій» колонки «Група» вводимо шифр потрібної групи. Виконайте запит. Зверніть увагу, що конструктор запитів автоматично уклав задану рядок в апострофи 'АК1-11'.
Щоб відібрати студентів груп, що починаються на подстроку «АК» слід задати в поле критерій для колонки «група» рядок like 'АК *'
На місці символу «*» може стояти 0 або більше будь-яких символів.
Щоб знайти студентів з ім'ям «Іван» скористаємося наступним виразом
like '* Іван *' Зверніть увагу на прогалини між «*» і ім'ям. Якщо цього не зробити під умову потраплять всі «Іван ови» і «Іван ович».
Видаліть всі умови.
Задамо умова на дату народження> = 11.11.1981. Виконайте запит. Зверніть увагу, що конструктор запитів автоматично додав справа і зліва від дати символ «#».
Додамо умова в поле «АБО». Виконайте запит.
Отримали студентів з групи 'АК1-11' з датою народження> = # 11.11.1981 # а так само всіх студентів групи 'АК3-11'. Як зрозуміти, що вийде в результаті запиту?
Натисніть на панелі інструментів кнопку «включити / вимкнути вид дизайну».
Дві нижніх області замінюються текстовим полем із запитом на мові SQL.
SELECT "Група", "№зачеткі", "ПІБ", "Дата народження" FROM "Студент" AS "Студент" WHERE ( "Група" = 'АК1-11' AND "Дата народження"> = OR "Група" = ' АК3-11 ') ORDER BY "Група" ASC, "ПІБ" ASC
Коли ви натискаєте кнопку «виконати» конструктор запитів формує такий рядок, і передає її драйверу СУБД. Драйвер передає її на сервер СУБД, (якщо треба, по мережі). Результати запиту передаються назад через драйвер СУБД конструктору, який відображає їх. Саме SQL є стандартною мовою для всіх СУБД. Таким чином, ви можете легко перейти на іншу СУБД іншого виробника. На жаль, деякі конструкції нечітко прописані в стандарті, в результаті існують діалекти мови SQL. Так, що при зміні СУБД зазвичай потрібно тестування і внесення деяких змін.
Але повернемося до нашого запиту. По-перше, можна помітити, що для критеріїв відбору, у яких не задана операція відносини, використовується відношення "дорівнює". По-друге, умови відбору, задані в різних колонках з'єднані логічною операцією «AND». По-третє, умови в різних рядках умови з'єднані логічною операцією «OR». Відбираються тільки записи, для яких виконується умова, заданий в секції «WHERE».
Якщо потрібно, відібрати студентів груп 'АК1-11' і 'АК3-11' з датою народження більше заданої, поправте умова
SELECT "група", "№зачеткі", "ПІБ", "Дата народження" FROM "студент" AS "студент" WHERE ( "Дата народження"> = AND ( "група" = 'АК1-11' OR "група" = 'АК3-11')) ORDER BY "група" ASC, "ПІБ" ASC
Якщо знову натиснути на кнопку «включити / вимкнути вид дизайну». Конструктор спробує перевести заданий умова в табличну форму. Але бувають випадки некоректного перетворення.
Видаліть всі умови на групу.
Відберемо тепер всіх студентів з датою народження в заданому діапазоні. Нам потрібно отримати умова
"Дата народження"> = AND "Дата народження"<
Щоб сформувати цю умову в конструкторі, додамо ще одне поле «Дата народження» і поставимо в ній друга умова.
Щоб в результатах запиту не з'явилася друга колонка колонку «Дата народження» зніміть позначку «Видимий» в дублюючої колонці.
Видаліть всі умови.
До значенням полів можна застосовувати різні функції, наприклад, якщо ми хочемо отримати рік народження кожного студента, треба ввести замість імені поля вираз:
YEAR ( "Дата народження"). Зверніть увагу, що ім'я поля полягає в лапки. Не забудьте поставити позначку «Видимий»
Щоб в результатах запиту в заголовку нашої нової колонки висновок не заданий вираз, а осмислена рядок, введіть потрібний текст в поле «псевдонім» нашої колонки. До речі, при використанні результатів одного запиту в іншому псевдоніми для обчислюваних колонок треба ставити обов'язково.
Місяць можна порахувати за допомогою функції MONTH ( "Дата народження")
Можна ставити і більш складні вирази. Виведемо вік наших студентів на даний момент часу. Це можна зробити двома способами:
YEAR (NOW ()) - YEAR ( "Дата народження") - беремо рік від поточної дати і віднімаємо з нього рік від дати народження.
DATEDIFF ( 'year', "Дата народження", NOW ()) - Скористаємося спеціальний пристрій. Ця ж функція дозволяє обчислювати тривалість інтервалу в днях: DATEDIFF ( 'day', "Дата народження", NOW ())
Повний список функцій наведено в додатку «Вбудовані функції і процедури Hsqldb».
При формуванні списку часто потрібно склеїти значення з кількох колонок таблиці. Наприклад, якщо є однофамільці, доведеться додати до ПІБ номер заліковки за допомогою оператора склеювання рядків «||»:
Отже, запам'ятайте правила:
· Строкові константи полягають в апострофи 'Іванов'
· Імена таблиць і колонок полягають в лапки "ПІБ" Уважно стежте за пропусками і капіталізації тексту.
· Дати укладаються між символами «#»: # 11.11.1981 #
· Числові константи записуються, без будь-яких знаків: 12345
Відкрийте таблицю «студент» і зітріть у одного зі студентів дату народження. Такі значення називаються «NULL значення». Збережіть запис.
Виконайте наш запит. Результатом виразу виявилася порожній рядок.
Спробуйте задати умову на дату. Запис з «NULL» не задовольняє ні умові «<= #01.01.1980#» ни «>= # 01.01.1980 # », ні, как не странно«<> # 01.01.1980 # ».
Для перевірки на NULL значення використовуйте умови «IS EMPTY» і «NOT IS EMPTY»
Складемо список груп, в яких є студенти 1980 року народження, причому, нас не цікавлять ПІБ студентів, тільки факт наявності студентів в групі.
Створіть новий запит в режимі дизайну. Додайте таблицю «Студент». Додайте колонки «Група» і «Дата народження». У другій колонці організуйте обчислення виразу «YEAR (" Дата народження ")», задайте критерій відбору 1980, зніміть позначку «Видимий».
Після виконання запиту отримаємо:
Чому група «CM1-11» потрапила в список 3 рази? Справа в тому, що в цій групі три студента 1980 голи народження. Щоб позбавиться від повторів? Натисніть на панелі інструментів кнопку «Однозначні значення» (крайня праворуч). Якщо кнопка втоплена, виключаються однакові рядки в вибірці.
Виконайте запит. Тепер повторів немає, а групи виявилися відсортовані за алфавітом.
Нехай нам потрібно вивести ПІБ викладачів і найменування предметів по всіх іспитах заданої групи.
Особливістю цього запиту є те, що прізвище викладача, найменування предмета і шифр групи лежать в різних таблицях.
Створіть новий запит в режимі дизайну. Додайте таблиці «Викладач», «Відомість» і «Предмет». Вибираємо таблицю «Відомість», оскільки в ній є шифр групи, але найважливіше, що саме поля цієї таблиці посилаються на записи в таблицях предмет і викладач.
Зверніть увагу, що конструктор запитів автоматично додав зв'язку між таблицями, користуючись зв'язками, які ми створили в конструкторі зв'язків. Якщо додані таблиці виявляться не пов'язаними, значить, ви не додали зв'язок між таблицями, або таблиці не пов'язані безпосередньо, тоді треба додати таблицю, за допомогою якої пов'язані наші таблиці, хоча з цієї таблиці нам і не потрібно жодного поля.
Але повернемося до нашого запиту. Додайте колонки:
· «ПІБ» з таблиці «Викладач»
· «Найменування» з таблиці «Предмет»
· «Група» з таблиці «Відомість»
Задайте шифр групи в полі «Критерій» колонки «Група» і виконайте запит.
Не забудьте попередньо ввести дані в таблиці.
1. Скласти список груп для заданого факультету.
2. Визначити список предметів (в алфавітному порядку), які приймалися заданим викладачем.
3. Вивести номери відомостей і дати іспитів, в весняному семестрі заданого року.
4. Створити список груп, які здавали іспити заданому викладачеві в заданому місяці.
5. Скласти список викладачів (в алфавітному порядку), які брали іспити в поточному році.
Нехай нам треба вивести оцінки всіх студентів з математики, але не для всіх студентів є записи в таблиці результат.
На жаль, в конструкторі «OpenOffice. org 3.0 »в один запит таке завдання вирішити не вдалося. Підготуємо допоміжний запит, який формує список оцінок з математики.
Створюємо новий запит в режимі дизайну. Додаємо таблиці «Результат» і «Відомість». Додаємо колонки «Студент» і «Оцінка» з таблиці «Результат». Додаємо колонку «Предмет» з таблиці «Відомість». Колонці «Оцінка» даємо псевдонім «Математика». Для колонки «Предмет» задаємо критерій відбору рівний коду математики (Подивіться в таблиці «Предмет»). Значення в колонці «Предмет» всі однакові їх ми і так знаємо, тому, знімемо позначку «Видимий».
Збережемо цей запит під ім'ям «Оцінки з Математики».
Створюємо новий запит в режимі дизайну. Додаємо таблицю «Студент». Потім вибираємо кнопку «Запити» і додаємо наш допоміжний запит «Оцінки з Математики». Часто складний запит зручно розбити на кілька простих допоміжних запитів, а потім використовувати їх в головному запиті. Зазвичай, час виконання від цього не змінюється. Перед виконанням, збирається загальний запит і передається СУБД, яка його оптимізує.
Увага! Таблиці треба зв'язати. Для цього треба підвести покажчик миші до поля «№ заліковки» з таблиці «Студент», натиснути ліву кнопку миші і, не відпускаючи її перемістити покажчик миші на поле «Студент» з допоміжного запиту, потім відпустити кнопку миші.
Додаємо колонки «№ заліковки» і «ПІБ» з таблиці «Студент». І колонку «Інформатика» з допоміжного запиту.
Якщо зараз виконати запит, отримаємо прізвища студентів, які здавали математику і їх оцінки. Справа в тому, що тип додається зв'язку за замовчуванням «Внутрішнє об'єднання». При такому з'єднанні виводяться тільки записи, в якому значення в заданих полях збігаються. Щоб отримати список всіх студентів, виконайте подвійне клацання миші на зв'язку між таблицями і виберіть зі списку «тип» «Правое об'єднання», натисніть «ОК».
Виконайте запит. Тепер отримаємо повний список студентів і оцінки з математики для тих студентів, які її здавали. У решти до колонки «Математика» порожньо. Якщо не вийшло, спробуйте «Ліве об'єднання». Яке об'єднання вибрати, залежить не від розташування таблиць в середньому вікні, а від порядку, в якому їх додавали. І ще одне зауваження, якщо підказка в нижній частині діалогу «Властивості зв'язку» не відповідає результату, не вір очам своїм.
Одним запитом цю задачу не вдалося вирішити не тільки тому, що конструктор запитів в «OpenOffice. org 3.0 »не переварює два правих / лівих об'єднання в одному запиті.
Збережіть отриманий запит під ім'ям «студент - математика» і зніміть з нього копію (Ctrl + C, Ctrl + V в головному вікні програми). Поекспериментуємо з ним.
Спробуйте задати умову на оцінку = 5 в колонці «Інформатика». Будь-яка умова в приєднаної таблиці відсіче заодно і все порожні клітинки. Нагадую що, NULL значення не задовольняє жодної логічної операції. Щоб в звіті залишилися студенти, які не здавали математику, можна задати «IS EMPTY» в поле «або». Але все одно, зі списку зі списку пропадуть студенти, які отримали інші оцінки. Це не помилка програми, а результат заданого нами критерію відбору. Не подобається, сміливо створюйте допоміжні запити. Зазвичай СУБД їх нормально оптимізує. До речі, іноді запит з правим або лівим об'єднанням виконуються навіть швидше, ніж запити з внутрішнім об'єднанням. Іноді СУБД вибирає неправильну послідовність виконання запиту. Використання правого об'єднання змушує СУБД виконувати запит в іншому порядку, в результаті, запит виконується в кілька разів швидше.
Тепер припустимо, що нам потрібно знайти студентів, які не здавали іспит з математики. Здавалося б, що простіше.
Але результатом такого запиту буде порожньою список. Справа в тому, що для студентів, які не здавали іспит, не просто значення в поле «Оцінка» порожньо, а взагалі немає запису. Важко знайти чорну кішку в темній кімнаті, особливо якщо її там немає.
всі кімнати і виключити зі списку кімнати де кішка є.
Для вирішення нашої задачі треба взяти повний список студентів з таблиці «Студент» і виключити з нього студентів, які мають оцінки. Беремо наш запит «студент - математика» і задаємо для колонки «Математика» критерій «IS EMPTY»
Не завжди для вирішення завдань вистачає можливостей конструктора запитів. В цьому випадку слід натиснути кнопку «включити / вимкнути вид дизайну» і більше, в режим конструктора запитів не увійти. Конструктор перебудує ваш запит, як йому завгодно. Відкривати такий запит слід командою «Редагувати в режимі SQL». Крім того, SQL запити, є основним способом звертання до бази даних з програм на мовах «С», «Pascal», «Java» ...
Розглянемо основні форми запитів: