Первая страница
Наша команда
Контакты
О нас

    Головна сторінка



Ярцев в. П. Створення та обробка баз даних на пеом

Ярцев в. П. Створення та обробка баз даних на пеом




Сторінка10/20
Дата конвертації10.03.2017
Розмір2.36 Mb.
ТипПротокол
1   ...   6   7   8   9   10   11   12   13   ...   20

7. Мова SQL




7.1. Загальні відомості

Запити – це основний інструмент вибірки й обробки даних у СУБД. Для створення і реалізації запитів розроблена спеціальна мова SQL (Structured Query Language – мова структурованих запитів).

Перший прототип мови SQL з'явився наприкінці 70-х років і одержав через якийсь час широке поширення. Він став застосовуватися у всіх комерційних СУБД і поступово став стандартом де-факто для мов маніпулювання даними в реляційних БД.

Перша версія стандарту називалася SQL-86 і була прийнята ANSI (Американським національним інститутом стандартів) і ISO (Міжнародним інститутом стандартів).Наприкінці 1992 р. був прийнятий новий міжнародний стандарт мови – це SQL-92.

Міжнародний стандарт - стандарт розроблений міжнародною організацією стандартизації. Найвідомішою з них є International Organization for Standardization. Міжнародний стандарт можна застосовувати прямо, чи модифікувати для кращої відповідності місцевим умовам.
Цей стандарт підтримується всіма сучасними СУБД, у тому числі і MS Access.

Останній стандарт з'явився в 1999 р., у якому були введені нові типи даних, і ряд інших нововведень, важливих для розроблювачів нових версій сучасних СУБД.

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

Всі оператори мови можна розділити на такі три категорії:


  1. Оператори контролю даних – використовуються для перевірки повноважень користувача при звертанні до БД. Наприклад, це такі оператори:

GRANT – надати права доступу до тих чи інших об’єктів БД чи функцій;

REVOKE – позбавити тих чи інших прав.



  1. Оператори визначення даних – використовуються для створення об'єктів БД і визначення їхньої структури. До них відносяться, наприклад, такі оператори:

CREATE TABLE – створити таблицю;

DROP TABLE – видалити таблицю;

ALTER TABLE – змінити структуру існуючій таблиці;

CREATE INDEX – створити інде, і інші оператори;



  1. Оператори керування даними – використовуються для пошуку, видалення, зміни і збереження даних. Це такі оператори:

SELECT – оператор, який реалізує усі операції реляційної алгебри і дозволяє створювати нові відношення згідно запиту, що був заданий;

UPDATE – відновлює значення одного чи декількох полів в однієї чи декількох рядках таблиці;

INSERT – утворює новий запис і додає його в таблицю;

DELETE – видаляє з таблиці один чи декілька записів, які відповідають умовам, що задані.

На початку вивчення мови SQL доцільно розглянути оператор SELECT, який найчастіше використовується на практиці. Його вивчення дозволить глибше зрозуміти внутрішні механізми функціювання БД.

7.2. Використання операторів DDL

Оператори DDL (Data definition language - мова опису даних) дозволяють створювати, модифікувати і видаляти в базі даних таблиці і індекси. За допомогою цих операторів багато які досить складні операції можна записати в одному рядку. Проте, є і деякі обмеження. Головне з них полягає в тому, що оператори DDL підтримуються тільки базами даних Jet. Нагадаємо, що об'єкти доступу до даних можуть використовуватися з будь-якими базами даних, що підтримуються ядром Jet. Інше обмеження підтримка тільки невеликої підмножини властивостей об'єктів таблиць, полів і індексів. Якщо необхідно працювати з властивостями поза цією підмножиною, доведеться скористатися методами, описаними раніше.

Для опису таблиць в базах даних використовуються три оператори DDL:

- CREATE TABLE. Визначає в базі даних нову таблицю.

- ALTER TABLE. Змінює структуру таблиці.

- DROP TABLE. Видаляє таблицю з бази даних.

Для створення таблиці необхідно побудувати SQL-оператор, що містить її ім'я і імена, типи і розміри всіх полів.

У наступному прикладі створюється таблиця Orders:

CREATE TABLE Orders (

Orderno LONG,

Custno LONG,

SalesID TEXT(6),

OrderDate DATE,

Totcost SINGLE)

Зверніть увагу, що при вказівці імен таблиці і полів, їх не треба брати в лапки. Імена, що містять пропуски, треба брати в квадратні дужки (наприклад, [Last name]).

При створенні таблиці вказуються тільки імена, типи і розміри полів. Не можна указати додаткові параметри, такі як значення, задані за умовчанням, перевірочні правила або перевірочні повідомлення про помилки. Але навіть з цими обмеженнями оператор DDL CREATE TABLE виявляється засобом створення таблиць.

Скориставшись оператором ALTER TABLE, можна додати в існуючу таблицю нове поле або видалити з неї старе. При доданні поля необхідно указати його ім'я, а також його тип і розмір (при необхідності). Для додання полів застосовується директива ADD COLUMN оператора ALTER TABLE. Для видалення поля досить указати його ім'я в директиві DROP COLUMN оператора ALTER TABLE. Як і при використанні інших методів зміни структури таблиць, в цьому випадку не можна видалити поле, що застосовується в індексі або відношенні.

У лістинг демонструється додання і видалення полів в таблиці Orders, яку ми створили в попередньому розділі.

Додаємо в таблицю "Orders" поле для вартості доставки :

ALTER TABLE Orders ADD COLUMN Shipping SINGLE

Видаляємо поле для вартості доставки :

ALTER TABLE Orders DROP COLUMN Shipping

За допомогою оператора DROP TABLE з бази даних можна видаляти цілі таблиці. У наступному фрагменті коду віддаляється таблиця Orders. При видаленні цілих таблиць вимагайте підтвердження операції від користувача; відразу після виконання цього оператора таблиця і дані, що все містяться в ній будуть безповоротно видалені:

DROP TABLE Orders

Роботу з індексами забезпечують два оператори DDL:

- CREATE INDEX. Створює новий індекс для таблиці.

- DROP INDEX. Видаляє існуючий індекс.

За допомогою оператора CREATE INDEX можна створювати індекси на основі одного або декількох полів. При цьому необхідно указати ім'я індексу, ім'я таблиці, що індексується і, принаймні, одне поле, на основі якого будується індекс. Можна також указати порядок індексації (висхідний або низхідний) для кожного поля. Крім того, індекс можна зробити первинним. У лістинг створюється первинний індекс по номеру замовника і ще один індекс по двох полях із заданим порядком сортування. Індекси створюються для таблиці Customers.

Створення первинного індексу по номеру замовника:

CREATE INDEX Custno ON Customers (Custno) WITH PRIMARY

Створення індексу по двох полях: по LastName у висхідному і по FirstName в низхідному порядку:

CREATE INDEX Name2 ON Customers (Lastname ASC, FirstName DESC)

Позбутися індексу також просто, як і створити його. Досить скористатися оператором DROP INDEX, як показано в наступному прикладі. Приведені оператори видаляють обидва індекси, створені в попередньому лістинг. Зверніть увагу, що при видаленні індексів необхідно вказувати ім'я відповідної таблиці:

DROP INDEX Custno ON Customers DROP INDEX Name2 ON Customers


7.3. Оператор SELECT. Відбір записів з однієї таблиці

Загальний формат оператора SELECT наступний:

SELECT [DISTINCT] {* | Список полів}

FROM Список таблиць

[WHERE Умова добору записів]

[GROUP BY Список полів для групування]

[HAVING Умови добору для груп]

[ORDER BY Список полів для сортування]


У наведеному форматі дужки [] позначають, що даний елемент є необов’язковим. Дужки {} позначають множину елементів. Дужки  позначають елемент, зміст якого пояснюється текстом в дужках.

Результатом виконання оператора SELECT є набір даних, який складається з записів, що відповідають заданим умовам відбору. В операторі обов'язково повинні бути присутніми інструкції SELECT і FROM. Інші інструкції (вказані у квадратних дужках) можуть бути відсутніми. Розглянемо більш докладно кожну з інструкцій, з яких складається оператор SELECT.



Інструкція SELECT повідомляє СУБД, що це початок оператору. Одночасно в інструкції SELECT указується список полів, які будуть включатися в записи, що відбираються. У списку полів повинне бути задане хоча б одне поле. Якщо в список полів потрібно включити всі поля з таблиці (таблиць), то замість перерахування полів можна вказати символ *. Якщо в список полів включаються поля з різних таблиць, то для вказівки приналежності поля до тієї чи іншої таблиці використовують складене ім'я поля, що складається з імені таблиці й імені поля, розділених крапкою. Необов'язкова інструкція DISTINCT забороняє включення в результуючий набір даних повторюваних записів.

В інструкції FROM вказуються імена таблиць, з яких відбираються записи. Список повинний містити хоча б одну таблицю. Якщо кількість таблиць дві і більше, між таблицями повинне встановлюватися з'єднання. Синтаксис інструкції у випадку з'єднання двох чи декількох таблиць буде розглянуто пізніше.

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

= , < , >, <>, <= , >= – операції порівняння;

Between – предикат, що перевіряє приналежність значення поля заданому діапазону значень. Предикат – це логічна функція, яка приймає значення True чи False в залежності від значень параметрів функції-предиката;

In – предикат, що перевіряє приналежність значення поля заданій множині;

Like – предикат, що перевіряє відповідність значення поля заданому шаблону;

And, Or, Not – логічні операції.



Інструкція GROUP BY призначена для вказівки полів, по яких визначаються групи записів. В одну групу включаються записи з однаковими значеннями в полях, перерахованих в інструкції GROUP BY. Для груп записів можна застосовувати групові операції (їх ще називають агрегатними функціями). У мові SQL визначені такі групові операції:

Max( ) – вибирає максимальне значення поля;

Min( ) – вибирає мінімальне значення поля;

Count( ) – визначає число значень у групі;

Avg( ) – обчислює середнє значення;

Sum( ) – обчислює суму значень полів у групі.

В дужках вказується ім'я поля, к даним якого застосовується дана функція.

Якщо застосовується інструкція GROUP BY, то у результуючому наборі даних кожний запис представляє одну з груп, що визначені в інструкції.

Інструкція HAVING застосовується разом з інструкцією GROUP BY і використовується для завдання умов добору для згрупованих даних. Правила запису умов добору аналогічні правилам завдання умов в інструкції WHERE.

В інструкції ORDER BY указується список полів, по яких потрібно сортування записів у результуючому наборі даних. За замовчуванням сортування по кожному полю виконується в порядку зростання значень. Якщо необхідно зробити сортування по спаданню, то після імені відповідного поля потрібно записати покажчик DESC.

Розглянемо приклади оператору SELECT для відбору записів з однієї таблиці:

SELECT ПІБ, РікНародж, Адреса

FROM СТУДЕНТИ

Цей оператор у результуючу таблицю відбирає поля ПІБ (прізвище, і'мя, по батькові), РікНародж і Адреса з таблиці СТУДЕНТИ.

SELECT *

FROM СТУДЕНТИ


Цим оператором відбираються всі поля з таблиці СТУДЕНТИ.

SELECT DISTINCT ПІБ

FROM СТУДЕНТИ

Необов'язкове ключове слово DISTINCT виключає відбір однакових записів.

Ключове́ сло́во - слово, або сталий вислів природної мови, яке використовують для вираження деякого аспекту змісту документа (або запиту); слово, яке має істотне смислове навантаження. Воно може служити ключем під час пошуку інформації в інтернеті чи на сторінці сайту.

SELECT DISTINCT *

FROM СТУДЕНТИ

Тут слово DISTINCT не має змісту, тому що виводиться ключове поле, значення в котрому не можуть повторюватися.

SELECT ПІБ

FROM СТУДЕНТИ

WHERE РікНародж >= 1980

Цим оператором відбираються записи з прізвищами усіх студентів тільки 1980 року народження і молодше.

SELECT ПІБ

FROM СТУДЕНТИ

WHERE (РікНародж = Between 1980 AND 1984) AND

(Місто = “Київ”)

Відбираються записи, що містять поле ПІБ, для всіх студентів, рік народження яких належить діапазону від 1980 до 1984 років, і проживаючих у м. Києві.

SELECT ПІБ

FROM СТУДЕНТИ

WHERE ПІБ Like “A*”
Відбираються записи, що містять поле ПІБ, для всіх студентів, прізвища яких починаються з букви “А”. Тут в умові добору використовується шаблон *, що заміняє довільне число символів. Застосовуються також такі елементи шаблонів: ? – один будь-який символ; # - одна будь-яка цифра.

7.4. Оператор SELECT. Відбір даних з декількох таблиць

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



7.4.1. Внутрішнє з'єднання таблиць

Нехай, наприклад, для відбору даних про оцінки студентів записаний такий оператор:

SELECT СТУДЕНТИ.ПІБ, УСПІШНІСТЬ.ОЦІНКА

FROM СТУДЕНТИ, УСПІШНІСТЬ

WHERE СТУДЕНТИ.КодСтуд = УСПІШНІСТЬ.КодСтуд
Цей оператор установлює внутрішнє з'єднання двох таблиць. Результуючий набір даних при внутрішнім з'єднанні таблиць А і В формується згідно такому правилу:

Зі стовпців (полів), що зазначені в інструкції SELECT, складається проміжний набір даних шляхом зчеплення результуючих стовпців кожного запису з таблиці А и всіх результуючих стовпців з таблиці В;

З отриманого набору даних відкидаються всі записи, що не задовольняють умовам відбору в інструкції WHERE.

Припустимо, що в таблицях СТУДЕНТИ й УСПІШНІСТЬ міститься така інформація:


СТУДЕНТИ (таблиця А) УСПІШНІСТЬ (таблиця В)

КодСтуд

ПІБ

Адреса




КодСтуд

КодПр

Оцінка

1

Іванов






1

1

5

2

Петров






1

2

3













2

1

4













2

1

3

Тоді приведений вище оператор SELECT у процесі його виконання формує проміжний і результуючий набори в такий спосіб:

Проміжний НД Результуючий НД


С.ПІБ

У.Оцінка




С.ПІБ

У.Оцінка

Іванов

5




Іванов

5

Іванов

3




Іванов

3

Іванов

4




Петров

4

Іванов

3




Петров

3

Петров

5










Петров

3










Петров

4










Петров

3









Для скорочення тексту оператора рекомендується використовувати так звані “псевдоніми” таблиць, тобто їхні короткі найменування. Наприклад, з використанням псевдонімів запис попереднього оператора може мати такий вигляд:

SELECT С.ПІБ, У.ОЦІНКА

FROM СТУДЕНТИ as С, УСПІШНІСТЬ as У

WHERE С.КодСтуд = У.КодСтуд

У цієї записі оператора в інструкції FROM задані псевдоніми С і У: для таблиці СТУДЕНТИ – псевдонім С, для таблиці УСПІШНІСТЬ – У.

Для створення внутрішнього з'єднання таблиць частіше використовується така форма інструкції FROM:

SELECT С.ПІБ, У.ОЦІНКА

FROM СТУДЕНТИ as С INNER JOIN УСПІШНІСТЬ as У

ON С.КодСтуд = У.КодСтуд

Тут службові слова INNER JOIN указують на внутрішнє з'єднання таблиць.

Службові слова, неповнозначні (систематичні слова - вільні морфеми, що інтонаційно об'єднуються зі словом, словосполученням чи реченням і виражають, подібно до словотвірних і словозмінних (флексійних)) зв'язаних морфем, різні стосунки між повнозначними словами й реченнями, об'єднуючи їх або надаючи їм різних значеннєвих відтінків.
За ключовим словом ON вказується умова внутрішнього з'єднання. Поля С.КодСтуд і У.КодСтуд, які вказані за службовим словом ON, виявляються ключами зв’язку, за якими створене з'єднання двох таблиць.

При внутрішньому з'єднанні у результуючу таблицю включаються тільки ті записи, які складаються з існуючих даних обох таблиць.



7.4.2. Зовнішні з'єднання таблиць

Крім розглянутого вище внутрішнього з'єднання існують ще і зовнішні з'єднання. Зовнішнє з'єднання відрізняється від внутрішнього тім, що в результуючий НД включаються всі записи стовпців з ведучої таблиці, що поєднуються з можливо порожніми записами з стовпцями другої (відомої) таблиці.

Формат оператора SELECT при зовнішнім з'єднанні такої ж, як і при внутрішньому. Відмінність полягає в тому, що замість слова INNER, що вказує на внутрішнє з'єднання, записується одне з таких слів:

LEFT – ліве зовнішнє з'єднання, при якому ведучої є таблиця, що розташована “ліворуч” від конструкції JOIN;

RIGHT – праве зовнішнє з'єднання, коли ведучої є таблиця, розташована “праворуч”;

FULL – повне зовнішнє з'єднання, коли ведучими є обидві таблиці. У цьому випадку в результуючий НД записі включаються за наступним правилом. Якщо для запису в таблиці А маються записи в таблиці В, що задовольняють умові з'єднання, у результуючий НД включаються всі комбінації з'єднань стовпців з таких записів. У противному випадку в результуючий НД будуть включені стовпці з таблиці А, з'єднані з порожніми стовпцями. Те ж відноситься і до стовпців з таблиці В.

Розглянемо наступний приклад. Нехай є такі таблиці:
СТУДЕНТИ УСПІШНІСТЬ


КодСтуд

ПІБ

Адр





КодСтуд

КодПр

Оцінка

1

Іванов






1

1

5

2

Петров






1

2

3

3

Сидоров






2

1

4













2

1

3













4

3

5

Оператор лівого зовнішнього з'єднання


SELECT ПІБ, Оцінка

FROM СТУДЕНТИ С LEFT JOIN УСПІШНІСТЬ У

ON С.КодСтуд = У.КодСтуд

створить такий результуючий НД:



ПІБ

Оцінка

Іванов


5

Іванов

3

Петров

4

Петров

3

Сидоров



Оператор правого зовнішнього з'єднання


SELECT ПІБ, Оцінка

FROM СТУДЕНТИ С RIGHT JOIN УСПІШНІСТЬ У

ON С.КодСтуд = У.КодСтуд
створить такий результуючий НД:


ПІБ

Оцінка

Іванов


5

Іванов

3

Петров

4

Петров

3




5

Оператор повного зовнішнього з'єднання таблиць С и У

SELECT ПІБ, Оцінка

FROM СТУДЕНТИ С FULL JOIN УСПІШНІСТЬ У

ON С.КодСтуд = У.КодСтуд


створить такий результуючий НД:

ПІБ

Оцінка

Іванов


5

Іванов

3

Петров

4

Петров

3

Сидоров







5



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


  1. Як в операторі SELECT створити поле, що обчислюється? Навести приклад.

  2. Для чого потрібні групові функції і як вони застосовуються в операторі SELECT? Навести приклади.

  3. Що таке “вкладені запити”, для чого вони потрібні? Навести приклад.

  4. Як в СУБД Access створити запит на відбір даних за допомогою Конструктора?

  5. Як задати складні умови відбору при створенні запиту за допомогою Конструктора у СУБД Access?



1   ...   6   7   8   9   10   11   12   13   ...   20



  • 7.2. Використання операторів DDL
  • 7.3. Оператор SELECT. Відбір записів з однієї таблиці
  • 7.4. Оператор SELECT. Відбір даних з декількох таблиць
  • 7.4.1. Внутрішнє зєднання таблиць
  • 7.4.2. Зовнішні зєднання таблиць