3 Вивчення методів оформлення наукових результатів

ТА СТВОРЕННЯ ЗВІТІВ ЗАСОБАМИ МYSQL / MS ACCESS

РОЗДІЛ 6 СТВОРЕННЯ СКЛАДНИХ ЗАПИТІВ НА ОСНОВІ

ЛОГІЧНИХ ОПЕРАТОРІВ ТА ВКЛАДЕНИХ ЗАПИТІВ SQL

6.1 Мета роботи

Вивчити синтаксис SQL-команд SELECT при використанні логічних операторів і dкладених запитів на вибірку даних. Використовуючи пропозиції WHERE або HAVING задати умови, для яких параметри добору невідомі. Вивчити організацію вкладених запитів на вибірки даних.

6.2 Обладнання для виконання роботи

Робота виконується на такому обладнанні:

 персональний комп'ютер Pentium з мікропроцесором частотою понад 160 МГц, накопичувачем на гнучких дисках високої щільності, жорстким диском та накопичувачем на компакт-дисках;

 пам'ять комп'ютера: оперативна пам'ять понад 32 Мбайт, на жорсткому диску понад 1,5 Гбайт вільного простору;

 операційна система Windows ХР.

6.3 Підготовка до виконання роботи

Під час підготовки до лабораторної роботи студентам пропонується:

 повторити вiдповiдний лекцiйний матерiал i вправи, котрі виконувалися на практичних заняттях;

 засвоїти порядок виконання лабораторної роботи вiдповiдно до цих методичних вказiвок, пiдготувати на робочому місці необхiдне обладнання: комп'ютер, дискету, компакт-диск, олівець, аркуш паперу.

6.4 Методичні поради щодо виконання лабораторної роботи

Процес проектування компонентів застосування бази даних передбачає створення таких об’єктів:

- теки (каталога, папки) на жорсткому диску ПК для збереження всiх компонентів бази даних застосування;

- нових таблиць зазначеним методом;

- створення схеми даних – сукупності взаємозв’язаних таблиць.

Завдання 1. Пригадайте формат команди SELECT при використанні логічних операторів і вкладених запитів на вибірку даних. Використовуючи пропозиції WHERE задати умови, для яких параметри відбору невідомі.

1. Порядок виконання завдання 3 з логічними операціями. У тому випадку, коли необхідно отримати не всі записи, а тільки ті, що задовольняють певній умові, цю умову можна вказати після ключового слова WHERE. Допустимо, що треба знайти всі публікації (БД publications) за інтервал 1995 - 1997 рр. Цю умову можна записати з використанням логічної операції BETWEEN – перевірки на входження в інтервал:

SELECT title FROM titles WHERE yearpub BETWEEN 1995 AND 1997;

Існує ще один варіант цієї команди з використанням логічної операції IN – перевірки на входження в список:

SELECT title FROM titles WHERE yearpub IN (1995, 1996, 1997);

2. Порядок виконання завдання 3 по неповному значенню. Деякі задачі не можна розв’язати з використанням тільки операторів порівняння. Наприклад, коли необхідно знайти web-site видавництва "Wiley", але невідомо його точного найменування. Для вирішення цієї задачі вико-ристовується ключове слово LIKE, його синтаксис має такий вигляд:

WHERE <имя_столбца> LIKE <образец>;

Зразок беретьсяє в лапки і повинен містити шаблон підрядка для пошуку. Зазвичай в шаблонах використовуються три символи:

* (зірочка) – замінює будь-яку кількість символів;

? (знак питання) – замінює одиночний символ;

# (ґратка) – замінює одиночну цифру.

Знайти потрібний web-site можна командою:

SELECT publiser, url FROM publishers WHERE publisher LIKE '*Wiley*';

Знайти книги, назва яких починається словом "SQL", можна командою:

SELECT title FROM titles WHERE title LIKE 'SQL*';

3. Порядок виконання завдання 3 при групуванні даних. Групування даних в операторі SELECT здійснюється ключовим словом GROUP BY і ключовим словом HAVING, за допомогою якого задаються умови розбиття записів на групи.

Ключове слово GROUP BY нерозривно пов'язано з агрегуючими функціями, без яких воно практично не вживається. GROUP BY розділяє таблицю на групи, а агрегуюча функція обчислює для кожної з них підсумкове значення. Наприклад, визначити кількість книг кожного видавництва в БД publications можна такою командою:

SELECT publishers.publisher, COUNT(titles.title)

FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id

GROUP BY publisher;

Ключове слово HAVING працює таким чином: спочатку GROUP BY розбиває рядки на групи, потім на отримані набори накладаються умови HAVING. Наприклад, усунемо з попереднього запиту ті видавництва, які мають тільки одну книгу:

SELECT publishers.publisher, COUNT(titles.title)

FROM titles INNER JOIN publishers ON titles.pub_id=publishers.pub_id

GROUP BY publisher

HAVING COUNT(titles.title)>1;

Інший варіант використання HAVING – включити в результат тільки ті видавництва, назва яких закінчується на підрядок "Press":

SELECT publishers.publisher, COUNT(titles.title)

FROM titles INNER JOIN publishers ON titles.pub_id=publishers.pub_id

GROUP BY publisher

HAVING publisher LIKE '*Press';

Відмінність між цими двома варіантами полягає у використанні слова HAVING. У другому варіанті умову відбору записів можна помістити в розділ пропозиції WHERE, в першому ж варіанті цього зробити не вдасться, оскільки WHERE не допускає використання агрегуючих функцій.

4. Порядок виконання завдання 3 при сортуванні даних. Для сортування даних в операторові SELECT використовується ключове слово ORDER BY. За його допомогою можна сортувати результати по будь-якому стовпцю або виразу, вказаному в <списке_выбора>. Дані можуть бути упорядковані як за збільшенням, так і по зменшенням. Приклад: відсортувати список авторів за абеткою:

SELECT author FROM authors ORDER BY author;

Складніший приклад дозволяє отримати список авторів, відсортований за абеткою, і список їх публікацій, причому для кожного автора список книг сортується за датою видання в зворотному порядку (тобто спочатку "свіжіші" книги, потім "старовиніші"):

SELECT authors.author,titles.title,titles.yearpub, publishers.publisher

FROM ((titleauthors INNER JOIN authors ON

titleauthors.au_id=authors.au_id)

INNER JOIN titles ON

titleauthors.title_id=titles.title_id)

INNER JOIN publishers ON

titles.pub_id=publishers.pub_id

ORDER BY authors.author ASC, titles.yearpub DESC;

Ключове слово DESC задає зворотний порядок сортування по полю yearpub, ключове слово ASC (його можна опускати, воно використовується за замовчуванням), – прямий порядок сортування по полю author.

5. Порядок виконання завдання 3 з вкладеними запитами. Переваги ключового слова IN виявляються у вкладених запитах. Припустимо, потрібно знайти всі видання, випущені компанією "Oracle Press". Найменування видавничих компаній містяться в таблиці publishers, назви книг в таблиці titles. Ключове слово IN дозволяє об'єднати обидві таблиці (без отримання загальної таблиці) і вибрати потрібну інформацію:

SELECT title FROM titles WHERE pub_id IN

(SELECT pub_id FROM publishers WHERE publisher='Oracle Press');

При виконанні цієї команди СКБД спочатку обробляє вкладений запит по таблиці publishers, а потім його результат передає на вхід основного запиту по таблиці titles. Наступний приклад дозволяє вибирати дані по параметру, обчисленому вкладеним запитом. Наприклад, знайти назву і рік видання книг автора W.J. Gordon.

SELECT titles.title, titles.yearpub

FROM titles

WHERE title_id = (SELECT title_id FROM titleauthors

WHERE au_id = (SELECT au_id FROM authors

WHERE author = ‘W.J. Gordon’));

При выполнении работы вычисляемые поля заменять их синонимами:

SELECT COUNT(*) AS Количество_строк FROM titles;

6.5 Контрольні питання:

1. Чим відрізняються пропозиції WHERE і HAVING?

2. Що обчислює функція COUNT?

3. Як використовується пропозиція GROUP BY?

4. Що задає пропозицію ORDER BY?

6.6 Завдання для самостійного виконання:

1. Отримати список замовлень у кількості більше 3.

2. Отримати список виробів і відсортувати їх за збільшенням цін.

3. Знайти замовлення з певною назвою вулиці в полі «Адреса».

4. Знайти замовлення, для яких сумарна ціна перевищує 20 одиниць, і відсортувати в алфавітному порядку.

6.7 Літературні джерела:

1. Велинг, Люк, Томсон, Лора. MySQL. Учебное пособие. – М.: Вильямс, 2005. – 304 с.

2. Уилтон, Пол, Колби, Джон. SQL для начинающих. – М.: Вильямс, 2006. – 496 с.

3. Моркес Д. Access 2003. Эффективный самоучитель. – СПб.: Наука и техника, 2006. – 352 с.

4. Золотова С.В. Практикум по Access. – М.: Финансы и статистика, 2004. – 144 с.

Навчальне видання

МЕТОДИЧНІ ВКАЗІВКИ

до лабораторних робіт з дисципліни

"МЕТОДИ НАУКОВИХ ДОСЛІДЖЕНЬ" напряму підготовки