Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ
НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ "ЛЬВІВСЬКА ПОЛІТЕХНІКА"
Інститут комп’ютерних наук та інформаційних технологій
Кафедра систем автоматизованого проектування
ПОЯСНЮВАЛЬНА ЗАПИСКА
до курсового проекту
з дисципліни "Технологія розробки великих комп’ютерних систем"
на тему: "Основи SQL "
Допущено до захисту:
Дата: _______________
Виконала:
студентка групи ІТП-12м
Бідак Н.В.
Прийняв:
Стех Ю.В
Оцінка __________
Залікова книжка № 0808553
Дата ____________
Львів 2012
Національний Університет «Львівська Політехніка»
Інститут комп’ютерних наук та інформаційних технологій
Кафедра САПР
Дисципліна: « Технологія розробки великих комп’ютерних систем»
Спеціальність: Комп’ютерні науки
Курс: п’ятий Група: ІТП-12м Семестр: дев’ятий
на курсовий проект студента
Бідак Н.В.
|
№ п/п |
Назва етапів курсової роботи |
Термін виконання етапів роботи |
Примітки |
|
1 |
Отримання індивідуального завдання |
||
|
2 |
Пошук необхідної літератури |
||
|
3 |
Обробка відповідної літератури |
||
|
4 |
Написання програми |
||
|
5 |
Написання теоретичної частини курсової роботи |
||
|
6 |
Висновки по курсовій роботі |
||
|
7 |
Остаточна перевірка, закінчення курсової роботи |
Студент: ___________Бідак Н.В.
Керівник:___________ Стех Ю.В.
____________2012р.
Студент: Бідак Н.В.
“Основи SQL”. Курсова робота. – НУ „Львівська політехніка”, каф.: САПР, дисципліна: “ Технологія розробки великих комп’ютерних систем ”, 2012.
Дана курсова робота містить в собі 50 сторінок, 10 таблиць, 1 малюнку.
Виконання курсової роботи має за мету:
ЗМІСТ
|
[0.0.0.1] Інститут комп’ютерних наук та інформаційних технологій [0.0.0.2] ПОЯСНЮВАЛЬНА ЗАПИСКА [0.0.0.3] Львів 2012 [1] Завдання
[2] [3] Анотація [4] Основи SQL [4.0.1] Основні поняття [4.0.2] Стандарт і реалізація мови SQL [4.0.3] Введення в технологію клієнт-сервер [4.0.4] Типи команд SQL [4.0.5] Переваги мови SQL [4.0.6] Запис SQL –операторів [4.0.7] Опис учбової бази даних [5] Створення бази даних і проектування таблиць [5.0.1] База даних [5.0.2] Індекси [6] Ефективне виконання запитів для витягання даних [6.0.1] Пропозиція SELECT [6.0.2] Пропозиція FROM [6.0.3] Пропозиція WHERE [6.0.4] Пропозиція ORDER BY [7] Обчислення і підведення підсумків в запитах [7.0.1] Побудова обчислюваних полів [7.0.2] Використання підсумкових функцій [7.0.3] Пропозиція GROUP BY [7.0.4] Пропозиція HAVING [8] Запити модифікації даних [8.0.1] Запит додавання [8.0.2] Запит видалення [8.0.3] Запит оновлення [8.0.4] Введення в поняття "Цілісність даних" [9] Функції користувача [9.0.1] Поняття функції користувача [9.0.2] Функції Scalar [9.0.3] Функції Inline [9.0.4] Функції Multi-statement [9.0.5] Функції для роботи з датою і часом [10] Збережені процедури [10.0.1] Поняття збереженої процедури [10.0.2] Збережені процедури, в середовищі MS SQL Server [11] Висновок [12] Список літератури |
Всяка професійна діяльність так чи інакше пов'язана з інформацією, з організацією її збору, зберігання, вибірки. Можна сказати, що невід'ємною частиною повсякденного життя стали бази даних, для підтримки яких потрібно деякий організаційний метод, або механізм. Такий механізм називається системою управління базами даних ( СУБД ). Отже, введемо основні поняття.
База даних (БД) - спільно використовуваний набір логічно пов'язаних даних (і їх опис), призначений для задоволення інформаційних потреб організації.
СУБД (система управління базами даних ) - програмне забезпечення, за допомогою якого користувачі можуть визначати, створювати і підтримувати базу даних, а також діставати до неї контрольований доступ.
Системи управління базами даних існують вже багато років, багато хто з них зобов'язаний своїм походженням системам з неструктурованими файлами на великих ЕОМ. Разом із загальноприйнятими сучасними технологіями в області систем управління базами даних починають з'являтися нові напрями, що обумовлено вимогами зростаючого бізнесу, об'ємами корпоративних даних, що все збільшуються, і, звичайно ж, впливом технологій Internet.
Реляційні бази даних
Управління основними потоками інформації здійснюється за допомогою так званих систем управління реляційними базами даних, які беруть свій початок в традиційних системах управління базами даних. Саме об'єднання реляційних баз даних і клієнт-серверних технологій дозволяє сучасному підприємству успішно управляти власними даними, залишаючись конкурентоздатним на ринку товарів і послуг.
Реляційні БД мають потужний теоретичний фундамент, заснований на математичній теорії відношень. Поява теорії реляційних баз даних дала поштовх до розробки ряду мов запитів, які можна віднести до двох класів :
• мови алгебри, що дозволяють виражати запити засобами спеціалізованих операторів, що застосовуються до відношень;
• мови числення предикатів, що є набором правив для запису вираження, що визначає нове відношення із заданої сукупності існуючих відношень. Отже, числення предикатів є метод визначення того відношення, яке бажано отримати як відповідь на запит із відношень, вже наявних у базі даних.
У реляційній моделі об'єкти реального світу і взаємозв'язку між ними представляються за допомогою сукупності пов'язаних між собою таблиць (відношень).
Навіть у тому випадку, коли функції СУБД використовуються для вибору інформації з однієї або декількох таблиць (тобто виконується запит ), результат також представляється в табличному вигляді. Більше того, можна виконати запит із застосуванням результатів іншого запиту.
Кожна таблиця БД представляється як сукупність рядків і стовпців, де рядки (записи) відповідають екземпляру об'єкту, конкретній події або явищу, а стовпці(поля) - атрибутам (ознакам, характеристикам, параметрам) об'єкту, події, явища.
У кожній таблиці БД потрібна наявність первинного ключа - так іменують поле або набір полів, що однозначно ідентифікує кожен екземпляр об'єкту або запис. Значення первинного ключа в таблиці БД має бути унікальним, тобто в таблиці не допускається наявність двох і більше записів з однаковими значеннями первинного ключа. Він має бути мінімально достатнім, а значить, не містити полів, видалення яких не відіб'ється на його унікальності.
Реляційні зв'язки між таблицями баз даних
Зв'язки між об'єктами реального світу можуть знаходити своє віддзеркалення в структурі даних, а можуть і матися на увазі, тобто бути присутнім на неформальному рівні.
Між двома або більше таблицями бази даних можуть існувати відношення підлеглості, які визначають, що для кожного запису головної таблиці (званою ще батьківською) можлива наявність однієї або декількох записів в підпорядкованій таблиці (званою ще дочірньою).
Виділяють три різновиди зв'язку між таблицями бази даних :
• ""один-до-багатьох";
• ""один-до-одного";
• ""багато-до-багатьох".
Відношення "один-до-багатьох"
Відношення "один-до-багатьох" має місце, коли одному запису батьківської таблиці може відповідати декілька записів дочірньої. Зв'язок "один-до-багатьох" іноді називають зв'язком "багато-до-одного". І у тому, і в іншому випадку суть зв'язку між таблицями залишається незмінною. Зв'язок "один-до-багатьох" є найпоширенішим для реляційних баз даних. Він дозволяє моделювати також ієрархічні структури даних.
Відношення "один-до-одного"
Відношення "один-до-одного" має місце, коли одному запису у батьківській таблиці відповідає один запис в дочірній. Це відношення зустрічається набагато рідше, ніж відношення "один-до-багатьох". Його використовують, якщо не хочуть, щоб таблиця БД "розпухала" від другорядної інформації, проте для читання пов'язаної інформації в декількох таблицях доводиться робити ряд операцій читання замість однієї, коли дані зберігаються в одній таблиці.
Відношення "багато-до-багатьох"
Відношення "багато-до-багатьох" застосовується в наступних випадках:
• одному запису у батьківській таблиці відповідає більше за один запис в дочірній;
• одному запису в дочірній таблиці відповідає більше за один запис у батьківській.
Всякий зв'язок "багато-до-багатьох" в реляційній базі даних необхідно замінити на зв'язок "один-до-багатьох" (одну або більше) за допомогою введення додаткових таблиць.
Ріст кількості даних, необхідність їх зберігання і обробки привели до того, що виникла потреба в створенні стандартної мови баз даних, яка могла би функціонувати в численних комп'ютерних системах різних видів. Дійсно, з її допомогою користувачі можуть маніпулювати даними незалежно від того, чи працюють вони на персональному комп'ютері, мережевій робочій станції або універсальній ЕОМ.
Однією з мов, що з'явилися в результаті розробки реляційної моделі даних, є мова SQL (Structured Query Language), яка нині отримала дуже широке поширення і фактично перетворилася на стандартну мову реляційних баз даних. Стандарт на мову SQL був випущений Американським національним інститутом стандартів (ANSI) в 1986 р., а в 1987 р. Міжнародна організація стандартів (ISO) прийняла його як міжнародного. Нинішній стандарт SQL відомий під назвою SQL/92.
З використанням будь-яких стандартів пов'язані не лише численні і цілком очевидні переваги, але і певні недоліки. Передусім, стандарти направляють в певне русло розвиток відповідної індустрії; у разі мови SQL наявність твердих засадничих принципів приводить, кінець кінцем, до сумісності його різних реалізацій і сприяє як підвищенню переносимості програмного забезпечення і баз даних в цілому, так і універсальності роботи адміністраторів баз даних. З іншого боку, стандарти обмежують гнучкість і функціональні можливості конкретної реалізації. Під реалізацією мови SQL розуміється програмний продукт SQL відповідного виробника. Для розширення функціональних можливостей багато розробників, що дотримуються прийнятих стандартів, додають до стандартної мови SQL різні розширення. Слід зазначити, що стандарти вимагають від будь-якої закінченої реалізації мови SQL наявності певних характеристик і у загальних рисах відбивають основні тенденції, які не лише призводять до сумісності між усіма конкуруючими реалізаціями, але і сприяють підвищенню значущості програмістів SQL і користувачів реляційних баз даних на сучасному ринку програмного забезпечення.
Усі конкретні реалізації мови дещо відрізняються одна від одної. В інтересах самих же виробників гарантувати, щоб їх реалізація відповідала сучасним стандартам ANSI в частині переносимості і зручності роботи користувачів. Проте кожна реалізація SQL містить удосконалення, що відповідають вимогам того або іншого сервера баз даних. Ці удосконалення або розширення мови SQL є додатковими командами і опціями, що є додаваннями до стандартного пакету і доступні в цій конкретній реалізації.
Нині мова SQL підтримується багатьма десятками СУБД різних типів, розроблених для найрізноманітніших обчислювальних платформ, починаючи від персональних комп'ютерів і закінчуючи мейнфреймами.
Усі мови маніпулювання даними, створені для багатьох СУБД до появи реляційних баз даних, були орієнтовані на операції з даними, представленими у вигляді логічних записів файлів. Зрозуміло, це вимагало від користувача детального знання організації зберігання цих і серйозних зусиль для вказівки тієї, які дані потрібні, де вони розміщуються і як їх отримати.
Дана мова SQL орієнтована на операції з даними, представленими у вигляді логічно взаємозв'язаних сукупностей таблиць -відношень. Найважливіша особливість його структур - орієнтація на кінцевий результат обробки даних, а не на процедуру цієї обробки. Мова SQL сам визначає, де знаходяться дані, індекси і навіть які найбільш ефективні послідовності операцій слід використовувати для отримання результату, а тому вказувати ці деталі в запиті до бази даних не вимагається.
У зв'язку з розширенням ринку інформаційних послуг виробники програмного забезпечення стали випускати усе більш інтелектуальні, а значить, і об'ємні програмні комплекси. Багато організацій і окремі користувачі часто не могли розмістити придбані продукти на власних ЕОМ. Для обміну інформацією і її поширення були створені мережі ЕОМ, а узагальнюючі програми і дані стали встановлювати на спеціальних файлових серверах.
Завдяки працюючим з файловими серверами СУБД, безліч користувачів отримують доступ до одних і тих же баз даних. Спрощується розробка різних автоматизованих систем управління організаціями. Проте при такому підході уся обробка запитів з програм або з терміналів призначених для користувача ЕОМ на них і виконується, тому для реалізації навіть простого запиту необхідно прочитувати з файлового сервера або записувати на нього цілі файли, а це веде до конфліктних ситуацій і перевантаження мережі. Для виключення вказаних недоліків була запропонована технологія клієнт-сервер, але при цьому знадобилася єдина мова спілкування з сервером - вибір ліг на SQL.
Технологія клієнт-сервер означає такий спосіб взаємодії програмних компонентів, при якій вони утворюють єдину систему. Як видно з самої назви, існує деякий клієнтський процес, що вимагає певних ресурсів, а також серверний процес, який ці ресурси надає. Зовсім необов'язково, щоб вони знаходилися на одному комп'ютері. Зазвичай прийнято розміщувати сервер на одному вузлі локальної мережі, а клієнтів - на інших вузлах.
У контексті бази даних клієнт управляє призначеним для користувача інтерфейсом і логікою додатка, діючи як робоча станція, на якій виконуються додатки баз даних. Клієнт приймає від користувача запит, перевіряє синтаксис і генерує запит до бази даних на мові SQL або іншій мові бази даних, відповідному логіку додатка. Потім передає повідомлення сервера, чекає вступу відповіді і форматує отримані дані для представлення їх користувачеві. Сервер приймає і обробляє запити до бази даних, після чого відправляє отримані результати назад клієнтові. Така обробка включає перевірку повноважень клієнта, забезпечення вимог цілісності, а також виконання запиту і оновлення даних. Окрім цього підтримується управління паралельністю і відновленням.
Архітектура клієнт-сервер має ряд переваг :
• забезпечується ширший доступ до існуючих баз даних ;
• підвищується загальна продуктивність системи : оскільки клієнти і сервер знаходяться на різних комп'ютерах, їх процесори здатні виконувати додатки паралельно. Налаштування продуктивності комп'ютера з сервером спрощується, якщо на нім виконується тільки робота з базою даних ;
• знижується вартість апаратного забезпечення; досить потужний комп'ютер з великим пристроєм зберігання потрібний тільки серверу - для зберігання і управління базою даних ;
• скорочуються комунікаційні витрати. Додатки виконують частину операцій на клієнтських комп'ютерах і посилають через мережу тільки запити до баз даних, що дозволяє значно скоротити об'єм що пересилаються по мережі даних;
• підвищується рівень несуперечності даних. Сервер може самостійно управляти перевіркою цілісності даних, оскільки лише на нім визначаються і перевіряються усі обмеження. При цьому кожному застосуванню не доведеться виконувати власну перевірку;
• архітектура клієнт-сервер природно відображається на архітектуру відкритих систем.
Подальше розширення дворівневої архітектури клієнт-сервер припускає розподіл функціональної частини колишнього, "товстого" (інтелектуального) клієнта на дві частини. У трирівневій архітектурі клієнт-сервер "тонкий" (неінтелектуальний) клієнт на робочій станції управляє тільки призначеним для користувача інтерфейсом, тоді як середній рівень обробки даних управляє усією іншою логікою додатка. Третій рівень - сервер бази даних. Ця трирівнева архітектура виявилася більше відповідною для деяких середовищ - наприклад, для мереж Internet і intranet, де в якості клієнта може виступати звичайний Web -браузер.
Реалізація в SQL концепції операцій, орієнтованих на табличне представлення даних, дозволила створити компактну мову з невеликим набором пропозицій. Мова SQL може використовуватися як для виконання запитів до даних, так і для побудови прикладних програм.
Основні категорії команд мови SQL призначені для виконання різних функцій, включаючи побудову об'єктів бази даних і маніпулювання ними, початкове завантаження даних в таблиці, оновлення і видалення існуючої інформації, виконання запитів до бази даних, управління доступом до неї і її загальне адміністрування.
Основні категорії команд мови SQL :
• DDL - мова визначення даних;
• DML - мова маніпулювання даними;
• DQL - мова запитів ;
• DCL - мова управління даними;
• команди адміністрування даних;
• команди управління транзакціями
Визначення структур бази даних (DDL)
Мова визначення даних (Data Definition Language, DDL) дозволяє створювати і змінювати структуру об'єктів бази даних, наприклад, створювати і видаляти таблиці. Основними командами мови DDL є наступні: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX.
Маніпулювання даними (DML)
Мова маніпулювання даними (Data Manipulation Language, DML) використовується для маніпулювання інформацією усередині об'єктів реляційної бази даних за допомогою трьох основних команд: INSERT, UPDATE, DELETE.
Вибірка даних (DQL)
Мова запитів DQL найбільш відома користувачам реляційної бази даних, не дивлячись на те, що він включає всього одну команду SELECT. Ця команда разом зі своїми численними опціями і пропозиціями використовується для формування запитів до реляційної бази даних.
Мова управління даними (DCL - Data Control Language)
Команди управління даними дозволяють управляти доступом до інформації, що знаходиться усередині бази даних. Як правило, вони використовуються для створення об'єктів, пов'язаних з доступом до даних, а також служать для контролю над розподілом привілеїв між користувачами. Команди управління даними наступні: GRANT, REVOKE.
Команди адміністрування даних
За допомогою команд адміністрування даних користувач здійснює контроль за виконуваними діями і аналізує операції бази даних ; вони також можуть виявитися корисними при аналізі продуктивності системи. Не слід плутати адміністрування даних з адмініструванням бази даних, яке є загальним управлінням базою даних і має на увазі використання команд усіх рівнів.
Команди управління транзакціями
Існують наступні команди, що дозволяють управляти транзакціями бази даних : COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.
Мова SQL є основою багатьох СУБД, оскільки відповідає за фізичну структуризацію і запис даних на диск, а також за читання даних з диска, дозволяє приймати SQL -запити від інших компонентів СУБД і призначених для користувача застосувань. Таким чином, SQL - потужний інструмент, який забезпечує користувачам, програмам і обчислювальним системам доступ до інформації, що міститься в реляційних базах даних.
Основні достоїнства мови SQL полягають в наступному:
• стандартність - як вже було сказано, використання мови SQL в програмах стандартизоване міжнародними організаціями;
• незалежність від конкретних СУБД - усі поширені СУБД використовують SQL, оскільки реляційну базу даних можна перенести з однією СУБД на іншу з мінімальними доопрацюваннями;
• можливість перенесення з однієї обчислювальної системи на іншу - СУБД може бути орієнтована на різні обчислювальні системи, проте додатки, створені за допомогою SQL, допускають використання як для локальних БД, так і для великих розрахованих на багато користувачів систем;
• реляційна основа мови - SQL є мовою реляційних БД, тому він став популярним тоді, коли отримала широке поширення реляційна модель представлення даних. Таблична структура реляційної БД добре зрозуміла, а тому мова SQL проста для вивчення;
• можливість створення інтерактивних запитів - SQL забезпечує користувачам негайний доступ до даних, при цьому в інтерактивному режимі можна отримати результат запиту за дуже короткий час без написання складної програми;
• можливість програмного доступу до БД - мову SQL легко використовувати в додатках, яким необхідно звертатися до баз даних. Одні і ті ж оператори SQL вживаються як для інтерактивного, так і програмного доступу, тому частини програм, що містять звернення до БД, можна спочатку перевірити в інтерактивному режимі, а потім вбудовувати в програму;
• забезпечення різного представлення даних - за допомогою SQL можна представити таку структуру даних, що той або інший користувач бачитиме різні їх представлення. Крім того, дані з різних частин БД можуть бути скомбіновані і представлені у вигляді однієї простої таблиці, а значить, представлення придатні для посилення захисту БД і її налаштування під конкретні вимоги окремих користувачів;
• можливість динамічної зміни і розширення структури БД - мова SQL дозволяє маніпулювати структурою БД, тим самим забезпечуючи гнучкість з точки зору пристосованості БД до вимог предметної області, що змінюються;
• підтримка архітектури клієнт-сервер - SQL - одно з кращих засобів для реалізації додатків на платформі клієнт-сервер. SQL служить сполучною ланкою між клієнтською системою, що взаємодіє з користувачем, і серверною системою, що управляє БД, дозволяючи кожній з них зосередитися на виконанні своїх функцій.
Будь-яка мова роботи з базами даних повинна надавати користувачеві наступні можливості:
• створювати бази даних і таблиці з повним описом їх структури;
• виконувати основні операції маніпулювання даними, зокрема, вставку, модифікацію і видалення даних з таблиць ;
• виконувати прості і складні запити, що здійснюють перетворення даних.
Крім того, мова роботи з базами даних повинна вирішувати усі вказані вище завдання при мінімальних зусиллях з боку користувача, а структура і синтаксис його команд - досить прості і доступні для вивчення. І нарешті, він має бути універсальним, тобто відповідати деякому визнаному стандарту, що дозволить використовувати один і той же синтаксис і структуру команд при переході від однієї СУБД до іншої. Мова SQL задовольняє практично усім цим вимогам.
Мова SQL є прикладом мови з орієнтацією, що трансформується, або ж мови, призначеної для роботи з таблицями з метою перетворення вхідних даних до необхідного вихідного виду. Він включає тільки команди визначення і маніпулювання даними і не містить яких-небудь команд управління ходом обчислень. Подібні завдання повинні вирішуватися або за допомогою мов програмування або управління завданнями, або інтерактивно, в результаті дій, що виконуються самим користувачем. Унаслідок подібної незавершеності в плані організації обчислювального процесу мова SQL може використовуватися двома способами. Перший передбачає інтерактивну роботу, що полягає у введенні користувачем з терміналу окремих SQL -операторів. Другий полягає у впровадженні SQL -операторів в програми на процедурних мовах. Мова SQL відносно проста у вивченні. Оскільки це не процедурна мова, в нім необхідно вказувати, яка інформація має бути отримана, а не як її можна отримати. Інакше кажучи, SQL не вимагає вказівки методів доступу до даних. Як і більшість сучасних мов, він підтримує вільний формат запису операторів. Це означає, що при введенні окремі елементи операторів не пов'язані з фіксованими позиціями екрану. Мова SQL може використовуватися широким кругом фахівців, включаючи адміністраторів баз цих, прикладних програмістів і безліч інших кінцевих користувачів.
Мова SQL - перша і доки єдина стандартна мова для роботи з базами даних, який отримав досить широке поширення. Практично усі видатні розробники СУБД нині створюють свої продукти з використанням мови SQL або з SQL -інтерфейсом. У нього зроблені величезні інвестиції як з боку розробників, так і з боку користувачів. Він став частиною архітектури додатків, є стратегічним вибором багатьох великих і впливових організацій.
Мова SQL використовується в інших стандартах і навіть чинить вплив на розробку інших стандартів як інструмент визначення (наприклад, стандарт Remote Data Access, RDA). Створення мови сприяло не лише виробленню необхідних теоретичних основ, але і підготовці успішно реалізованих технічних рішень. Це особливо справедливо відносно оптимізації запитів, методів розподілу даних і реалізації засобів захисту. Почали з'являтися спеціалізовані реалізації мови, призначені для нових ринків : системи управління обробкою транзакцій (OnLine Transaction Processing, OLTP ) і системи оперативної аналітичної обробки або системи підтримки ухвалення рішень (OnLine Analytical Processing, OLAP ). Вже відомі плани подальших розширень стандарту, що включають підтримку розподіленої обробки, об'єктно-орієнтованого програмування, розширень користувачів і мультимедіа.
Для успішного вивчення мови SQL необхідно привести короткий опис структури SQL-операторів і нотації, які використовуються для визначення формату різноманітних конструкцій мови. Оператор SQL складається із зарезервованих слів, а також із слів, визначуваних користувачем. Зарезервовані слова є постійною частиною мови SQL і мають фіксоване значення. Їх слід записувати в точності так, як це встановлено, не можна розбивати на частини для перенесення з одного рядка на іншу. Слова, визначувані користувачем, задаються ним самим (відповідно до синтаксичних правил) і є ідентифікаторами або іменами різних об'єктів бази даних. Слова в операторові розміщуються також відповідно до встановлених синтаксичних правил.
Ідентифікатори мови SQL призначені для позначення об'єктів у базі даних і є іменами таблиць, представлень, стовпців і інших об'єктів бази даних. Символи, які можуть використовуватися в створюваних користувачем ідентифікаторах мови SQL, мають бути визначені як набір символів. Стандарт SQL задає набір символів, який використовується за умовчанням, - він включає рядкові і прописні букви латинського алфавіту ( A - Z, a - z ), цифри ( 0-9 ) і символ підкреслення ( _ ). На формат ідентифікатора накладаються наступні обмеження:
• ідентифікатор може мати довжину до 128 символів;
• ідентифікатор повинен починатися з букви;
• ідентифікатор не може містити пропуски.
<ідентифікатор>::=<буква>
{<буква>|<цифра>}[,..n]
Більшість компонентів мови не чутлива до регістра. Оскільки у мови SQL вільний формат, окремі SQL -операторі і їх послідовності матимуть більше читаний вигляд при використанні відступів і вирівнювання.
Мова, в термінах якої дається опис мови SQL, називається метамовою . Синтаксичні визначення зазвичай задають за допомогою спеціальної металінгвістичної символіки, званою Бекуса-Науера формулами (БНФ). Прописні букви використовуються для запису зарезервованих слів і повинні вказуватися в операторах точно так, як це буде показано. Рядкові букви вживаються для запису слів, визначуваних користувачем. Вживані в нотації БНФ символи і їх позначення показані в таблиці.
|
Таблиця 1.1. |
|
|
Символ |
Позначення |
|
::= |
Рівно за визначенням |
|
| |
Необхідність вибору одного з декількох приведених значень |
|
<.> |
Описана за допомогою метамови структура мови |
|
{.} |
Обов'язковий вибір деякої конструкції із списку |
|
[.] |
Необов'язковий вибір деякої конструкції із списку |
|
[,.n] |
Необов'язкова можливість повторення конструкції від нуля до декількох разів |
У подальшому викладі в якості прикладу використовуватиметься невелика база даних, що відбиває процес постачання або продажу деякого товару постійним клієнтам.
Виходячи з аналізу предметної області, можна виділити два типи сутностей - ТОВАР і КЛІЄНТ, які пов'язані між собою відношенням "багато-до-багатьох", оскільки кожен покупець може купити багато найменувань товару, а кожен товар може бути куплений багатьма покупцями. Проте реляційна модель даних вимагає замінити відношення "багато-до-багатьох" на декілька відношень "один-до-багатьох". Додамо ще один тип сутностей, процес продажу товарів, що відображає, - УГОДА.
Встановимо зв'язки між об'єктами. Один покупець може неодноразово купувати товари, тому між об'єктами КЛІЄНТ і УГОДА є зв'язок "один-до-багатьох". Кожне найменування товару може неодноразово брати участь в угодах, в результаті між об'єктами ТОВАР і УГОДА є зв'язок "один-до-багатьох".
Визначимо атрибути і зв'яжемо їх з сутностями і зв'язками. До об'єкту ТОВАР відносяться такі характеристики, як назва, тип, ціна, сорт. До об'єкту КЛІЄНТ - ім'я, по батькові, прізвище, фірма, місто, телефон. Тип суті УГОДА може бути охарактеризована такими ознаками, як дата і кількість проданого товару.
Важливим етапом в створенні бази даних є визначення атрибутів, які однозначно визначають кожен екземпляр суті, тобто виявлення первинних ключів.
Для таблиці ТОВАР назва не може служити первинним ключем, оскільки товари різних типів можуть мати однакові назви, тому введемо первинний ключ КодТовару, під яким можна розуміти, наприклад, артикул товару. Так само ні Ім'я, ні Фірма, ні Місто не можуть служити первинним ключем в таблиці КЛІЄНТ. Введемо первинний ключ КодКлієнта, під яким можна розуміти номер паспорта, ідентифікаційний номер платника податків або будь-який інший атрибут, що однозначно визначає кожного клієнта. Для таблиці УГОДА первинним ключем є поле КодУгоди, оскільки воно однозначно визначає дату, покупця і інші елементи даних. В якості первинного ключа можна було б вибрати не одно поле, а деяку сукупність полів, але для ілюстрації конструкцій мови обмежимося простими первинними ключами.
Встановимо зв'язки між таблицями. Один покупець може неодноразово купувати товари. Тому між таблицями КЛІЄНТ і УГОДА є зв'язок "один-до-багатьох" по полю КодКлієнта.
Кожен покупець може придбати декілька різних товарів. Тому між таблицями ТОВАР і УГОДА є зв'язок "один-до-багатьох" по полю КодТовару.
Тепер треба створити зв'язки між таблицями бази даних. Для цього помістимо копії первинних ключів з батьківської таблиці ( таблиці з боку "один") в дочірню таблицю ( таблицю з боку "багато"). Для організації зв'язку між таблицями ТОВАР і УГОДА помістимо копію поля КодТовару з таблиці ТОВАР в таблицю УГОДА. Для організації зв'язку між таблицями КЛІЄНТ і УГОДА помістимо копію поля КодКлієнта з таблиці КЛІЄНТ в таблицю УГОДА. Для таблиці УГОДА поля КодКлієнта і КодТовару є зовнішніми (чужими) ключами. В результаті отримаємо наступну структуру бази даних.
Мал. 1.1. Приклад структури бази даних.
Створення бази даних
У різних СУБД процедура створення баз даних зазвичай закріплюється тільки за адміністратором баз даних. У розрахованих на одного користувача системах що приймається по замовчуванню база даних може бути сформована безпосередньо в процесі установки і налаштування самої СУБД. Стандарт SQL не визначає, як повинні створюватися бази даних, тому в кожному з діалектів мови SQL зазвичай використовується свій підхід. Відповідно до стандарту SQL, таблиці і інші об'єкти бази даних існують в деякому середовищі. Окрім усього іншого, кожне середовище складається з одного або більше за каталоги, а кожен каталог - з набору схем. Схема є пойменованою колекцією об'єктів бази даних, деяким чином пов'язаних один з одним (усі об'єкти у базі даних мають бути описані в тій або іншій схемі ). Об'єктами схеми можуть бути таблиці, представлення, домени, твердження, зіставлення, тлумачення і набори символів. Усі вони мають одного і того ж власника і безліч загальних значень, що приймаються за умовчанням.
Стандарт SQL залишає за розробниками СУБД право вибору конкретного механізму створення і знищення каталогів, проте механізм створення і видалення схем регламентується за допомогою операторів CREATE SCHEMA і DROP SCHEMA. У стандарті також вказано, що у рамках оператора створення схеми повинна існувати можливість визначення діапазону привілеїв, доступних користувачам створюваної схеми. Проте конкретні чини визначення подібних привілеїв в різних СУБД розрізняються.
Нині оператори CREATE SCHEMA і DROP SCHEMA реалізовані в дуже небагатьох СУБД. У інших реалізаціях, наприклад, в СУБД MS SQL Server, використовується оператор CREATE DATABASE.
Створення бази даних в середовищі MS SQL Server
Процес створення бази даних в системі SQL -сервера складається з двох етапів: спочатку організовується сама база даних, а потім журнал транзакцій, що належить їй. Інформація розміщується у відповідних файлах, що мають розширення *.mdf (для бази даних ) і *.ldf. (для журналу транзакцій ). У файлі бази даних записуються відомості про основні об'єкти ( таблицях, індексах, представленнях і так далі), а у файлі журналу транзакцій - про процес роботи з транзакціями (контроль цілісності даних, стани бази даних до і після виконання транзакцій).
Створення бази даних в системі SQL -сервер здійснюється командою CREATE DATABASE. Слід зазначити, що процедура створення бази даних в SQL -сервері вимагає наявності прав адміністратора сервера.
<визначення_бази_даних> ::=
CREATE DATABASE ім'я_бази_даних
[ON [PRIMARY]
[ <визначення_файлу> [,..n] ]
[,<визначення_групи> [,..n] ] ]
[ LOG ON {<визначення_файлу>[,..n] } ]
[ FOR LOAD | FOR ATTACH ]
Розглянемо основні параметри представленого оператора.
При виборі імені бази даних слід керуватися загальними правилами іменування об'єктів. Якщо ім'я бази даних містить пропуски або будь-які інші неприпустимі символи, воно полягає в обмежувачі (подвійні лапки або квадратні дужки). Ім'я бази даних має бути унікальним в межах сервера і не може перевищувати 128 символів.
При створенні і зміні бази даних можна вказати ім'я файлу, який буде для неї створений, змінити ім'я, шлях і початковий розмір цього файлу. Якщо в процесі використання бази даних планується її розміщення на декількох дисках, то можна створити так звані вторинні файли бази даних з розширенням *.ndf. В цьому випадку основна інформація про базу даних розташовується в первинному ( PRIMARY ) файлі, а при нестачі для нього вільного місця інформація, що додається, розміщуватиметься у вторинному файлі. Підхід, використовуваний в SQL -сервері, дозволяє розподіляти вміст бази даних по декількох дискових томах.
Параметр ON визначає список файлів на диску для розміщення інформації, що зберігається у базі даних.
Параметр PRIMARY визначає первинний файл. Якщо він опущений, то первинним є перший файл в списку.
Параметр LOG ON визначає список файлів на диску для розміщення журналу транзакцій. Ім'я файлу для журналу транзакцій генерується на основі імені бази даних, і у кінці до нього додаються символи _log.
При створенні бази даних можна визначити набір файлів, з яких вона полягатиме. Файл визначається за допомогою наступної конструкції:
<визначення_файлу>::=
( ([ NAME=логічне_ім'я_файлу,]
FILENAME='фізичне_ім'я_файлу'
[SIZE=розмір_файлу ]
[MAXSIZE={max_розмір_файлу |UNLIMITED } ]
[FILEGROWTH=величина_приросту ] )[,..n]
Тут логічне ім'я файлу - це ім'я файлу, під яким він пізнаватиметься при виконанні різних SQL -команд.
Фізичне ім'я файлу призначене для вказівки повного шляху і назви відповідного фізичного файлу, який буде створений на жорсткому диску. Це ім'я залишиться за файлом на рівні операційної системи.
Параметр SIZE визначає первинний розмір файлу; мінімальний розмір параметра - 512 Кб, якщо він не вказаний, за умовчанням приймається 1 Мб.
Параметр MAXSIZE визначає максимальний розмір файлу бази даних. При значенні параметра UNLIMITED максимальний розмір бази даних обмежується вільним місцем на диску.
При створенні бази даних можна дозволити або заборонити автоматичний ріст її розміру (це визначається параметром FILEGROWTH ) і вказати приріст за допомогою абсолютної величини в Мб або процентним співвідношенням. Значення може бути вказане в кілобайтах, мегабайтах, гігабайтах, терабайтах або відсотках (%). Якщо вказано число без суфікса МБ, КБ або %, то за умовчанням використовується значення MБ. Якщо розмір кроку росту вказаний у відсотках (%), розмір збільшується на задану частину у відсотках від розміру файлу. Вказаний розмір округляється до найближчих 64 КБ.
Додаткові файли можуть бути включені в групу:
<визначення_групи>::=FILEGROUP ім'я_групи_файлів
<визначення_файлу>[,..n]
Приклад 2.1. Створити базу даних, причому для даних визначити три файли на диску C, для журналу транзакцій - два файли на диску C.
CREATE DATABASE Archive
ON PRIMARY ( NAME=Arch1
FILENAME='c:\user\data\archdat1.mdf
SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)
((NAME=Arch2,
FILENAME='c:\user\data\archdat2.mdf
SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)
((NAME=Arch3,
FILENAME='c:\user\data\archdat3.mdf
SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)
LOG ON
((NAME=Archlog1
FILENAME='c:\user\data\archlog1.ldf
SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)
((NAME=Archlog2
FILENAME='c:\user\data\archlog2.ldf
SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)
Приклад 2.1. Створення бази даних.
Зміна бази даних
Більшість дій зі зміни конфігурації бази даних виконуються за допомогою наступної конструкції:
<зміна_бази_даних> ::=
ALTER DATABASE ім'я_бази_даних
{ ADD FILE <визначення_файлу>[,..n]
[TO FILEGROUP ім'я_групи_файлів ]
| ADD LOG FILE <визначення_файлу>[,..n]
| REMOVE FILE логічне_ім'я_файлу
| ADD FILEGROUP ім'я_групи_файлів
| REMOVE FILEGROUP ім'я_групи_файлів
| MODIFY FILE <визначення_файлу>
| MODIFY FILEGROUP ім'я_групи_файлів
<властивості_групи_файлів>}
Як видно з синтаксису, за один виклик команди може бути змінено не більше за один параметр конфігурації бази даних. Якщо необхідно виконати декілька змін, доведеться розбити процес на ряд окремих кроків.
У базу даних можна додати ( ADD ) нові файли даних (у вказану групу файлів або в групу, прийняту за умовчанням) або файли журналу транзакцій.
Параметри файлів і груп файлів можна змінювати ( MODIFY ).
Для видалення з бази цих файлів або груп файлів використовується параметр REMOVE. Проте видалення файлу можливе лише за умови його звільнення від даних. Інакше сервер не дозволить видалення.
В якості властивостей групи файлів використовуються наступні:
READONLY - група файлів використовується тільки для читання; READWRITE - в групі файлів дозволяються зміни; DEFAULT - вказана група файлів приймається за умовчанням.
Видалення бази даних
Видалення бази даних здійснюється командою:
DROP DATABASE ім'я_бази_даних [,..n]
Віддаляються усі ті, що містяться у базі даних об'єкти, а також файли, в яких вона розміщується. Для виконання операції видалення бази даних користувач повинен мати відповідні права.
Створення таблиці
Після створення загальної структури бази даних можна приступити до створення таблиць, які є стосунками, що входять до складу проекту бази даних.
Таблиця - основний об'єкт для зберігання інформації в реляційній базі даних. Вона складається з тих, що містять дані рядків і стовпців, займає у базі даних фізичний простір і може бути постійною або тимчасовою.
Поле, також зване в реляційній базі даних стовпцем, є частиною таблиці, за якою закріплений певний тип даних. Кожна таблиця бази даних повинна містити хоч би один стовпець. Рядок даних - це запис в таблиці бази даних, вона включає поля, що містять дані з одного запису таблиці.
Приступаючи до створення таблиці, необхідно мати відповіді на низку запитань :
• Як називатиметься таблиця?
• Як називатимуться стовпці (поля) таблиці?
• Які типи даних будуть закріплені за кожним стовпцем?
• Який розмір пам'яті має бути виділений для зберігання кожного стовпця?
• Які стовпці таблиці вимагають обов'язкового введення?
• З яких стовпців складатиметься первинний ключ?
Базовий синтаксис оператора створення таблиці має наступний вигляд:
<визначення_таблиці> ::=
CREATE TABLE ім'я_таблиці
( (ім'я_стовпця тип_даних
[NULL | NOT NULL ] [,..n])
Приведений стандарт співпадає з реалізацією оператора створення таблиці в середовищі MS SQL Server.
Головне в команді створення таблиці - визначення імені таблиці і опис набору імен полів, які вказуються у відповідному порядку. Крім того, цією командою обмовляються типи даних і розміри полів таблиці.
Ключове слово NULL використовується для вказівки тієї, що в цьому стовпці можуть міститися значення NULL. Значення NULL відрізняється від пропуску або нуля - до нього прибігають, коли необхідно вказати, що дані недоступні, опущені або недопустимі. Якщо вказано ключове слово NOT NULL, то будуть відхилені будь-які спроби помістити значення NULL в цей стовпець. Якщо вказаний параметр NULL, приміщення значень NULL в стовпець дозволене. За умовчанням стандарт SQL припускає наявність ключового слова NULL.
Ми використовували спрощену версію оператора CREATE TABLE стандарту SQL. Його повна версія приводиться при обговоренні питань забезпечення цілісності даних.
Приклад 2.2. Створити таблицю для зберігання даних про товари, що надходять у продаж в деякій торговій фірмі. Необхідно врахувати такі відомості, як назва і тип товару, його ціна, сорт і місто, де товар робиться.
CREATE TABLE Товар
((Назва VARCHAR(50) NOT NULL
Ціна MONEY NOT NULL
Тип VARCHAR(50) NOT NULL
Сорт VARCHAR(50)
МістоТовару VARCHAR(50))
Приклад 2.2. Створення таблиці для зберігання даних про товари, що надходять у продаж в деякій торговій фірмі.
Приклад 2.3.Створити таблицю для збереження відомостей про постійних клієнтів з вказівкою назв міста і фірми, прізвища, імені і по батькові клієнта, номера його телефону.
CREATE TABLE Клієнт
( (Фірма VARCHAR(50) NOT NULL
Прізвище VARCHAR(50) NOT NULL
Ім'я VARCHAR(50) NOT NULL
По батькові VARCHAR(50)
МістоКлієнта VARCHAR(50)
Телефон CHAR(10) NOT NULL)
Приклад 2.3. Створення таблиці для збереження відомостей про постійних клієнтів.
Зміна таблиці
Структура існуючої таблиці може бути модифікована за допомогою команди ALTER TABLE, спрощений синтаксис якої представлений нижче :
ALTER TABLE ім'я_таблиці
{[ADD [COLUMN] ім'я_стовпця тип_даних [
NULL | NOT NULL ]]
| [DROP [COLUMN] ім'я_стовпця]}
У середовищі MS SQL Server спрощений синтаксис команди модифікації таблиці має вигляд:
ALTER TABLE ім'я_таблиці
{[ALTER COLUMN ім'я_стовпця
{новий_тип_даних [(точність[,масштаб])]
[ NULL | NOT NULL ]}]
| ADD { [ім'я_стовпця тип_даних]
| ім'я_стовпця AS вираження } [,..n]
| DROP {COLUMN ім'я_стовпця}[,..n]
}
Команда дозволяє додавати і видаляти стовпці, змінювати їх визначення.
Одно з основних правил при додаванні стовпців в існуючу таблицю свідчить: коли в таблиці вже містяться дані, стовпець, що додається, не може бути визначений з атрибутом NOT NULL. Цей атрибут означає, що для кожного рядка даних відповідний стовпець повинен містити деяке значення, тому додавання стовпця з атрибутом NOT NULL призводить до появи протиріччя - вже існуючі рядки даних таблиці не матимуть в новому стовпці ненульових значень.
Проте існує спосіб додавання обов'язкових полів в існуючу таблицю. Для цього необхідно:
• додати в таблицю новий стовпець, визначивши його з атрибутом NULL (тобто стовпець не зобов'язаний містити яких-небудь значень);
• ввести в новий стовпець які-небудь значення для кожного рядка даних таблиці ;
• переконавшись, що новий стовпець містить ненульові значення для кожного рядка даних, змінити структуру таблиці, замінивши атрибут цього стовпця на NOT NULL.
При зміні визначень стовпців слід брати до уваги деякі загальноприйняті правила:
• розмір стовпця може бути збільшений до максимального значення, що допускається відповідним типом даних;
• розмір стовпця може бути зменшений тільки у тому випадку, якщо найбільше значення, що міститься в нім, не перевершуватиме його нового розміру;
• кількість розрядів числового типу даних завжди може бути збільшена;
• кількість розрядів числового типу даних може бути зменшена тільки у тому випадку, якщо кількість розрядів найбільшого значення у відповідному стовпці не перевершуватиме нового числа розрядів, визначеного для цього стовпця ;
• кількість десяткових знаків числового типу даних може бути зменшена або збільшена;
• тип даних стовпця, як правило, може бути змінений.
Деякі реалізації фактично можуть обмежити розробника у використанні деяких опцій команди ALTER TABLE. Наприклад, може виявитися неприпустимим видалення стовпців з існуючої таблиці. Щоб добитися цього, спочатку потрібно буде видалити саму таблицю і тільки потім наново її побудувати з потрібними стовпцями. Причому вже внесені в таблицю дані будуть втрачені.
Можливі труднощі, пов'язані з видаленням з таблиці стовпця, який залежить від деякого стовпця іншої таблиці. У такому разі спочатку доведеться видалити обмеження стовпця, а потім сам стовпець.
Приклад 2.4. Додати в таблицю Клієнт поле для номера розрахункового рахунку.
ALTER TABLE Клієнт ADD Рас_рахунок CHAR(20)
Приклад 2.4. Додавання в таблицю Клієнт поля для номера розрахункового рахунку.
Видалення таблиці
З часом структура бази даних міняється: створюються нові таблиці, а колишні стають непотрібними і видаляються з бази даних за допомогою оператора:
DROP TABLE ім'я_таблиці [RESTRICT | CASCADE]
Слід зазначити, що ця команда видалить не лише вказану таблицю, але і усі рядки даних, що входять в неї. Якщо вимагається видалити з таблиці лише дані, зберігши структуру таблиці, слід скористатися командою DELETE.
Оператор DROP TABLE додатково дозволяє вказувати, чи слід операцію видалення виконувати каскадний. Якщо в операторові вказано ключове слово RESTRICT, то за наявності у базі даних хоч би одного об'єкту, існування якого залежить від таблиці, що видаляється, виконання оператора DROP TABLE буде скасовано. Якщо вказано ключове слово CASCADE, автоматично віддаляються і усі інші об'єкти бази даних, чиє існування залежить від таблиці, що видаляється, а також інші об'єкти, залежні від об'єктів, що видаляються. Загальний ефект від виконання оператора DROP TABLE з ключовим словом CASCADE може виявитися дуже відчутним, тому подібні оператори слід використовувати з максимальною обережністю.
Найчастіше оператор DROP TABLE використовується для виправлення помилок, допущених при створенні таблиці. Якщо таблиця була створена з некоректною структурою, можна скористатися оператором DROP TABLE для її видалення, після чого створити таблицю наново.
Індекси в стандарті мови
Індекси є структурою, що дозволяє виконувати прискорений доступ до рядків таблиці на основі значень одного або більше за її стовпці . Наявність індексу може істотно підвищити швидкість виконання деяких запитів і скоротити час пошуку необхідних даних за рахунок фізичного або логічного їх впорядковування. Індекс - це набір посилань, впорядкованих по певному стовпцю таблиці, який в даному випадку називатиметься індексованим стовпцем . Хоча індекс і пов'язаний з конкретним стовпцем (чи стовпцями ) таблиці, все ж він є самостійним об'єктом бази даних.
Фізично індекс - усього лише впорядкований набір значень з індексованого стовпця з покажчиками на місця фізичного розміщення початкових рядків в структурі бази даних. Коли користувач виконує запит, що звертається до індексованого стовпця, СУБД автоматично аналізує індекс для пошуку необхідних значень.
Проте, оскільки індекси повинні оновлюватися системою при кожному внесенні змін до їх базової таблиці, вони створюють додаткове навантаження на систему.
Індекси зазвичай створюються з метою задоволення певних критеріїв пошуку після того, як таблиця вже знаходилася деякий час в роботі і збільшилася в розмірах. Створення індексів не передбачене стандартом SQL, проте більшість діалектів підтримують як мінімум наступний оператор:
CREATE [ UNIQUE ] INDEX ім'я_індексу
ON ім'я_таблиці(ім'я_стовпця[ASC|DESC][,..n])
Вказані в операторові стовпці складають ключ індексу. Індекси можуть створюватися тільки для базових таблиць, але не для представлень. Якщо в операторові вказано ключове слово UNIQUE, унікальність значень ключа індексу автоматично підтримуватиметься системою. Вимога унікальності значень обов'язкова для первинних ключів, а також можливо і для інших стовпців таблиці (наприклад, для альтернативних ключів). Хоча створення індексу допускається у будь-який момент, при його побудові для вже заповненою даними таблиці можуть виникнути проблеми, пов'язані з дублюванням даних в різних рядках. Отже, унікальні індекси (принаймні, для первинного ключа) має сенс створювати безпосередньо при формуванні таблиці. В результаті система відразу візьме на себе контроль за унікальністю значень даних у відповідних стовпцях.
Якщо створений індекс згодом виявиться непотрібним, його можна видалити за допомогою оператора
DROP INDEX ім'я_індексу
Індекси в середовищі MS SQL Server
Індекс є засобом, що допомагає прискорити пошук необхідних даних за рахунок фізичного або логічного їх впорядковування. Індекс є набором посилань, впорядкованих по певному стовпцю таблиці, який в даному випадку називатиметься індексованим стовпцем. Індекси - це набори унікальних значень для деякої таблиці з відповідними посиланнями на дані. Вони розташовані в самій таблиці і є зручним внутрішнім механізмом системи SQL -сервера, за допомогою якого здійснюється доступ до даних найбільш оптимальним способом. У середовищі SQL Server реалізовані ефективні алгоритми пошуку потрібного значення в строго певній послідовності даних. Прискорення пошуку досягається саме за рахунок того, що дані представляються впорядкованими (хоча фізично, залежно від типу індексу, вони можуть зберігатися відповідно до черговості їх додавання в таблицю ). До теперішнього часу розроблені ефективні математичні алгоритми пошуку даних у впорядкованій послідовності. Найбільш ефективною структурою для пошуку даних в машинному представленні являються B -дерева - багаторівнева ієрархічна структура зі змінною кількістю елементів в кожному вузлі.
Створення індексу
Якщо вибірка даних з таблиці вимагає значного часу, це означає, що для неї необхідно створити індекс. Індекси можуть істотно підвищити продуктивність виконання операцій пошуку і вибірки даних. При виборі стовпця для індексу слід проаналізувати, які типи запитів найчастіше виконуються користувачами і які стовпці є ключовими, тобто задаючими критерії вибірки даних, наприклад, порядок сортування.
У середовищі SQL Server реалізовано декілька типів індексів :
•кластерні індекси ;
•некластерні індекси ;
•унікальні індекси.
Некластерний індекс
Некластерні індекси - найбільш типові представники сімейства індексів. На відміну від кластерних, вони не перебудовують фізичну структуру таблиці, а лише організовують посилання на відповідні рядки .
Для ідентифікації потрібного рядка в таблиці некластерний індекс організовує спеціальні покажчики, що включають :
• інформацію про ідентифікаційний номер файлу, в якому зберігається рядок ;
• ідентифікаційний номер сторінки відповідних даних;
• номер шуканого рядка на відповідній сторінці;
• вміст стовпця.
У більшості випадків слід обмежуватися 4-5 індексами.
Кластерний індекс
Принциповою відмінністю кластерного індексу від індексів інших типів є те, що при його визначенні в таблиці фізичне розташування даних перебудовується відповідно до структури індексу . Логічна структура таблиці в цьому випадку є швидше словником, ніж індекс. Дані в словнику фізично впорядковані, наприклад за абеткою.
Кластерні індекси можуть дати істотне збільшення продуктивності пошуку даних навіть в порівнянні із звичайними індексами. Збільшення продуктивності особливе помітно при роботі з послідовними даними. Якщо в таблиці визначений некластерний індекс, то сервер повинен спочатку звернутися до індексу, а потім знайти потрібний рядок в таблиці. При використанні кластерних індексів наступна порція даних розташовується відразу після знайдених раніше за даних. Завдяки цьому відпадають зайві операції, пов'язані із зверненням до індексу і новим пошуком потрібного рядка в таблиці.
Природно, в таблиці може бути визначений тільки один кластерний індекс. В якості такого слід вибирати найбільш часто використовувані стовпці. При цьому варто наслідувати загальні рекомендації створення індексів і не індексувати занадто довгі стовпці.
Кластерний індекс може включати декілька стовпців. Проте кількість таких стовпців рекомендується по можливості звести до мінімуму.
Необхідно уникати створення кластерного індексу для часто змінюваних стовпців, оскільки сервер повинен буде виконувати фізичне переміщення усіх даних в таблиці, щоб вони знаходилися у впорядкованому стані, як того вимагає кластерний індекс. Для інтенсивно змінюваних стовпців краще підходить некластерний індекс.
При створенні в таблиці первинного ключа ( PRIMARY KEY ) сервер автоматично створює для нього кластерний індекс, якщо його не існувало раніше або якщо при визначенні ключа не був явно вказаний інший тип індексу.
Коли ж в таблиці визначений ще і некластерний індекс, то його покажчик посилається не на фізичне положення рядка у базі даних, а на того, що відповідає елементу кластерного індексу, що описує цей рядок, що дозволяє не перебудовувати структуру некластерних індексів всякий раз, коли кластерний індекс міняє фізичний порядок рядків в таблиці.
Унікальний індекс
Унікальність значень в індексованому стовпці гарантують унікальні індекси. При їх наявності сервер не дозволить вставити нове або змінити існуюче значення так, щоб в результаті цієї операції в стовпці з'явилися два однакові значення.
Унікальний індекс є своєрідною надбудовою і може бути реалізований як для кластерного, так і для некластерного індексу . В одній таблиці може існувати один унікальний кластерний і безліч унікальних некластерних індексів.
Унікальні індекси слід визначати тільки тоді, коли це дійсно необхідно. Для забезпечення цілісності даних в стовпці можна визначити обмеження цілісності UNIQUE або PRIMARY KEY, а не прибігати до унікальних індексів. Їх використання тільки для забезпечення цілісності даних є невиправданою витратою простору у базі даних. Крім того, на їх підтримку витрачається і процесорний час.
Засоби мови SQL пропонують декілька способів визначення індексу :
• автоматичне створення індексу при створенні первинного ключа;
• автоматичне створення індексу при визначенні обмеження цілісності UNIQUE ;
• створення індексу за допомогою команди CREATE INDEX.
Остання команда має наступний формат:
<створення_індексу>::=
CREATE [ UNIQUE ]
[ CLUSTERED | NONCLUSTERED ]
INDEX ім'я_індексу ON ім'я_таблиці(ім'я_стовпця
[ASC|DESC][,..n])
[WITH [PAD_INDEX]
[[,] FILLFACTOR=чинник_заповнення]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE] ]
[ON ім'я_групи_файлів ]
Розглянемо деякі параметри приведеної команди.
Ім'я індексу має бути унікальним в межах таблиці, а сам індекс створюється виключно для таблиці поточної бази даних.
Параметр UNIQUE використовується при необхідності введення в певне поле тільки унікальних значень. При вказівці цього ключового слова буде створений унікальний індекс. У індексованому стовпці бажано заборонити зберігання значень NULL, щоб уникнути проблем, пов'язаних з унікальністю значень. Після того, як для стовпця появиться унікальний індекс, сервер не дозволить виконання команд INSERT і UPDATE, які приведуть до появи дублюючих значень.
Параметр CLUSTERED використовує можливість фізичного індексування даних і дозволяє зробити так зване кластерне індексування, внаслідок чого будуть відсортовані дані в самій таблиці згідно з порядком цього індексу, а уся інформація, що додається, стане призводити до зміни фізичного порядку даних. Кластерним може бути тільки один індекс в таблиці.
Параметр NONCLUSTERED дозволяє створювати некластерні індекси.
Параметр FILLFACTOR здійснює налаштування розбиття індексу на сторінки і помітно оптимізує роботу SQL -сервера. Коефіцієнт FILLFACTOR визначає в процентному співвідношенні розмір створюваних індексних сторінок. При цьому є обернено пропорційна залежність частоти роботи з таблицею і коефіцієнту FILLFACTOR.
Параметр PAD_INDEX визначає заповнення внутрішнього простору індексу і застосовується спільно з FILLFACTOR.
Параметр DROP_EXISTING при використанні кластерного індексу визначає його повторне створення, що дозволяє запобігти небажаному оновленню кластерних індексів.
Параметр STATISTICS_NORECOMPUTE визначає функції автоматичного оновлення статистики для таблиці.
Параметр ім'я_групи_файлів дозволяє здійснити вибір файлової групи, в якій знаходитиметься створюваний індекс. Використання індексу з іншої файлової групи підвищує продуктивність некластерних індексів у зв'язку з паралельністю виконання процесів введення/виводу і роботи з самим індексом.
Видалення індексу
Видалення індексу виконується командою
DROP INDEX 'ім'я_індексу'[,..n]
Приклад 2.5. Створити унікальний кластерний індекс для таблиці Клієнт по стовпцю Прізвище в первинній групі файлів.
CREATE UNIQUE CLUSTERED INDEX index_klient1
ON Клієнт (Прізвище)
WITH DROP_EXISTING
ON PRIMARY
Приклад 2.5. Створення унікального кластерного індексу.
Приклад 2.6.Створити унікальний некластерний індекс для таблиці Клієнт по стовпцях Прізвище і Ім'я в первинній групі файлів. Крім того, елементи індексу будуть впорядковані по убуванню. Також заборонимо автоматичне оновлення статистики при зміні даних в таблиці і встановимо чинник заповнення індексних сторінок на рівні 30%.
CREATE UNIQUE NONCLUSTERED INDEX index_klient2
ON Клієнт (Прізвище DESC, Ім'я DESC)
WITH FILLFACTOR=30
STATISTICS_NORECOMPUTE
ON PRIMARY
Приклад 2.6. Створення унікального некластерного індексу.
Оператор SELECT - один з найбільш важливих і найпоширеніших операторів SQL. Він дозволяє робити вибірки даних з таблиць і перетворювати до потрібного виду отримані результати. Будучи дуже потужним, він здатний виконувати дії, еквівалентні операторам реляційної алгебри, причому в межах єдиної виконуваної команди. При його допомозі можна реалізувати складні і громіздкі умови відбору даних з різних таблиць.
Оператор SELECT - засіб, який повністю абстрагований від питань представлення даних, що допомагає сконцентрувати увага на проблемах доступу до даних. Приклади його використання наочно демонструють один із засадничих принципів великих (промислових) СУБД : засоби зберігання даних і доступу до них відокремлені від засобів представлення даних. Операції над даними робляться в масштабі наборів цих, а не окремих записів.
Оператор SELECT має наступний формат:
SELECT [ALL | DISTINCT ] {*|[ім'я_стовпця
[AS нове_ім'я]]} [,..n]
FROM ім'я_таблиці [[AS] псевдонім] [,..n]
[WHERE <умова_пошуку>]
[GROUP BY ім'я_стовпця [,..n]]
[HAVING <критерії вибору груп>]
[ORDER BY ім'я_стовпця [,..n]]
Оператор SELECT визначає поля (стовпці), які входитимуть в результат виконання запиту. У списку вони розділяються комами і приводяться в такій черговості, в якій мають бути представлені в результаті запиту. Якщо використовується ім'я поля, що містить пропуски або роздільники, його слід взяти в квадратних дужок. Символом* можна вибрати усі поля, а замість імені поля застосувати вираження з декількох імен.
Якщо обробляється ряд таблиць, то (за наявності однойменних полів в різних таблицях) в списку полів використовується повна специфікація поля, тобто. Ім'я_таблиці.Ім'я_поля.
Пропозиція FROM задає імена таблиць і представлень, які містять поля, перераховані в операторові SELECT. Необов'язковий параметр псевдоніма - це скорочення, що встановлюється для імені таблиці.
Обробка елементів оператора SELECT виконується в наступній послідовності:
• FROM - визначаються імена використовуваних таблиць;
• WHERE - виконується фільтрація рядків об'єкту відповідно до заданих умов;
• GROUP BY - утворюються групи рядків, що мають одно і те ж значення у вказаному стовпці;
• HAVING - фільтруються групи рядків об'єкту відповідно до вказаної умови;
• SELECT - встановлюється, які стовпці мають бути присутніми у вихідних даних;
• ORDER BY - визначається впорядкованість результатів виконання операторів.
Порядок пропозицій і фраз в операторові SELECT не може бути змінений. Тільки дві пропозиції SELECT і FROM є обов'язковими, усі інші можуть бути опущені. SELECT - закрита операція: результат запиту до таблиці є іншою таблицею. Існує безліч варіантів запису цього оператора, що ілюструється наведеними нижче прикладами.
Приклад 3.1. Скласти список відомостей про усіх клієнтів.
SELECT * FROM Клієнт
Приклад 3.1. Список відомостей про усіх клієнтів.
Параметр WHERE визначає критерій відбору записів з вхідного набору. Але в таблиці можуть бути присутніми записи (дублікати), що повторюються. Предикат ALL задає включення у вихідний набір усіх дублікатів, відібраних за критерієм WHERE. Немає необхідності вказувати ALL явно, оскільки це значення діє за умовчанням.
Приклад 3.2. Скласти список усіх фірм.
SELECT ALL Клієнт.Фірма FROM Клієнт
Чи (що еквівалентно)
SELECT Клієнт.Фірма FROM Клієнт
Приклад 3.2. Список усіх фірм.
Результат виконання запиту може містити значення, що дублюються, оскільки на відміну від операцій реляційної алгебри оператор SELECT не виключає значень, що повторюються, при виконанні вибірки даних.
Предикат DISTINCT слід застосовувати в тих випадках, коли вимагається відкинути блоки даних, що містять дублюючі записи у вибраних полях. Значення для кожного з приведених в інструкції SELECT полів мають бути унікальними, щоб запис, що утримує їх, зміг увійти до вихідного набору.
Причиною обмеження в застосуванні DISTINCT є та обставина, що її використання може різко уповільнити виконання запитів.
Відкоригований приклад 4.2 виглядає таким чином:
SELECT DISTINCT Клієнт.Фірма
FROM Клієнт
За допомогою WHERE -параметра користувач визначає, які блоки даних з приведених в списку FROM таблиць з'являться в результаті запиту. За ключовим словом WHERE слідує перелік умов пошуку, що визначають ті рядки, які мають бути вибрані при виконанні запиту. Існує п'ять основних типів умов пошуку (чи предикатів) :
• Порівняння: порівнюються результати обчислення одного вираження з результатами обчислення іншого.
• Діапазон: перевіряється, чи потрапляє результат обчислення вираження в заданий діапазон значень.
• Приналежність множині: перевіряється, чи належить результат обчислень вираження заданій безлічі значень.
• Відповідність шаблону: перевіряється, чи відповідає деяке строкове значення заданому шаблону.
• Значення NULL : перевіряється, чи містить цей стовпець визначник NULL (невідоме значення).
Порівняння
У мові SQL можна використовувати наступні оператори порівняння : = - рівність; < - менше; > - більше; <= - менше або рівно; >= - більше або рівно; <> - не рівно.
Приклад 4.3. Показати усі операції відпустки товарів об'ємом більше 20.
SELECT * FROM Угода
WHERE Кількість>20
Приклад 3.3. Операції відпустки товарів об'ємом більше 20.
SELECT * FROM Угода
WHERE Кількість>20
Приклад 3.3. Операції відпустки товарів об'ємом більше 20.
Складніші предикати можуть бути побудовані за допомогою логічних операторів AND, OR або NOT, а також дужок, використовуваних для визначення порядку обчислення вираження. Обчислення вираження в умовах виконується за наступними правилами:
• Вираження обчислюється зліва направо.
• Першими обчислюються підвирази в дужках.
• Оператори NOT виконуються до виконання операторів AND і OR.
• Оператори AND виконуються до виконання операторів OR.
Для усунення будь-якої можливої неоднозначності рекомендується використовувати дужки.
Приклад 4.4. Вивести список товарів, ціна яких більше або рівна 100 і менше або рівна 150.
SELECT Назва, Ціна
FROM Товар
WHERE Ціна>=100 And Ціна<=150
Приклад 3.4. Список товарів, ціна яких більше або рівна 100 і менше або рівна 150.
Приклад 3.5. Вивести список клієнтів з Києва або з Чернігова.
SELECT Прізвище, МістоКлієнта
FROM Клієнт
WHERE МістоКлієнта="Київ" Or
МістоКлієнта="Чернігів"
Приклад 3.5. Список клієнтів з Києва або з Чернігова.
Діапазон
Оператор BETWEEN використовується для пошуку значення усередині деякого інтервалу, визначуваного своїми мінімальним і максимальним значеннями. При цьому вказані значення включаються в умову пошуку.
Приклад 3.6. Вивести список товарів, ціна яких лежить в діапазоні від 100 до 150 (запит еквівалентний прикладу 4.4).
SELECT Назва, Ціна
FROM Товар
WHERE Ціна BETWEEN 100 And 150
Приклад 3.6. Список товарів, ціна яких лежить в діапазоні від 100 до 150.
При використанні заперечення NOT BETWEEN потрібно, щоб значення, що перевіряється, лежало поза межами заданого діапазону.
Приклад 3.7. Вивести список товарів, ціна яких не лежить в діапазоні від 100 до 150.
SELECT Товар.Назва, Товар.Ціна
FROM Товар
WHERE Товар.Ціна NOT BETWEEN 100 And 150
Чи (що еквівалентно)
SELECT Товар.Назва, Товар.Ціна
FROM Товар
WHERE (Товар.Ціна<100) OR (Товар.Ціна>150)
Приклад 3.7. Список товарів, ціна яких не лежить в діапазоні від 100 до 150.
Приналежність множині
Оператор IN використовується для порівняння деякого значення із списком заданих значень, при цьому перевіряється, чи відповідає результат обчислення вираження одному зі значень в наданому списку. За допомогою оператора IN може бути досягнутий той же результат, що і у разі застосування оператора OR, проте оператор IN виконується швидше.
Приклад 3.8. Вивести список клієнтів з Києва або з Чернігова (запит еквівалентний прикладу 3.5).
SELECT Прізвище, МістоКлієнта
FROM Клієнт
WHERE МістоКлієнта IN ("Київ", "Чернігів")
Приклад 3.8. Список клієнтів з Києва або з Чернігова
NOT IN використовується для відбору будь-яких значень, окрім тих, які вказані в представленому списку.
Приклад 3.9. Вивести список клієнтів, що проживають не в Києві і не в Чернігові.
SELECT Прізвище, МістоКлієнта
FROM Клієнт
WHERE МістоКлієнта
NOT IN ("Київ", "Чернігів")
Приклад 3.9. Список клієнтів, що проживають не в Києві і не в Чернігові.
Відповідність шаблону
За допомогою оператора LIKE можна виконувати порівняння вираження із заданим шаблоном, в якому допускається використання символів-замінників :
• Символ % - замість цього символу може бути підставлена будь-яка кількість довільних символів.
• Символ _ замінює один символ рядка.
• [] - замість символу рядка буде підставлений один з можливих символів, вказаний в цих обмежувачах.
• [^] - замість відповідного символу рядка будуть підставлені усі символи, окрім вказаних в обмежувачах.
Приклад 3.10. Знайти клієнтів, у яких в номері телефону друга цифра, - 4.
SELECT Клієнт.Прізвище, Клієнт.Телефон
FROM Клієнт
WHERE Клієнт.Телефон LIKE '_4%'
Приклад 3.10. Вибірка клієнтів, у яких в номері телефону друга цифра, - 4.
Приклад 3.11. Знайти клієнтів, у яких в номері телефону друга цифра, - 2 або 4.
SELECT Клієнт.Прізвище, Клієнт.Телефон
FROM Клієнт
WHERE Клієнт.Телефон LIKE '_[2,4]%'
Приклад 3.11. Вибірка клієнтів, у яких в номері телефону друга цифра, - 2 або 4.
Приклад 3.12. Знайти клієнтів, у яких в номері телефону друга цифра 2, 3 або 4.
SELECT Клієнт.Прізвище, Клієнт.Телефон
FROM Клієнт
WHERE Клієнт.Телефон LIKE '_[2-4]%'
Приклад 3.12. Вибірка клієнтів, у яких в номері телефону друга цифра 2, 3 або 4.
Приклад 3.13. Знайти клієнтів, у яких в прізвищі зустрічається склад "ро".
SELECT Клієнт.Прізвище
FROM Клієнт
WHERE Клієнт.Прізвище LIKE "%ро%"
Приклад 3.13. Вибірка клієнтів, у яких в прізвищі зустрічається склад "ро".
Значення NULL
Оператор IS NULL використовується для порівняння поточного значення зі значенням NULL - спеціальним значенням, що вказує на відсутність будь-якого значення. NULL - це не те ж саме, що знак пропуску (пропуск - допустимий символ) або нуль (0 - допустиме число). NULL відрізняється і від рядка нульової довжини (порожнього рядка).
Приклад 3.14. Знайти співробітників, у яких немає телефону (поле Телефон не містить ніякого значення).
SELECT Прізвище, Телефон
FROM Клієнт
WHERE Телефон IS NULL
Приклад 3.14. Вибірка співробітників, у яких немає телефону (поле Телефон не містить ніякого значення).
IS NOT NULL використовується для перевірки присутності значення в полі.
Приклад 3.15. Вибірка клієнтів, у яких є телефон (поле Телефон містить яке-небудь значення).
SELECT Клієнт.Прізвище, Клієнт.Телефон
FROM Клієнт
WHERE Клієнт.Телефон Is Not Null
Приклад 3.15. Знайти клієнтів, у яких є телефон (поле Телефон містить яке-небудь значення).
У загальному випадку рядка в результуючій таблиці SQL -запиту ніяк не впорядковані. Проте їх можна необхідним чином відсортувати, для чого в оператор SELECT вміщується фраза ORDER BY, яка сортує дані вихідного набору в заданій послідовності. Сортування може виконуватися по декількох полях, в цьому випадку вони перераховуються за ключовим словом ORDER BY через кому. Спосіб сортування задається ключовим словом, що вказується у рамках параметра ORDER BY слідом за назвою поля, по якій виконується сортування. За умовчанням реалізується сортування за збільшенням. Явно вона задається ключовим словом ASC. Для виконання сортування в зворотній послідовності необхідно після імені поля, по якому вона виконується, вказати ключове слово DESC. Фраза ORDER BY дозволяє упорядкувати вибрані записи в порядку зростання або убування значень будь-якого стовпця або комбінації стовпців, незалежно від того, є присутніми ці стовпці в таблиці результату або ні. Фраза ORDER BY завжди має бути останнім елементом в операторові SELECT.
У фразі ORDER BY може бути вказане і більше одного елементу. Головний (перший) ключ сортування визначає загальну впорядкованість рядків результуючої таблиці. Якщо в усіх рядках результуючої таблиці значення головного ключа сортування є унікальними, немає необхідності використовувати додаткові ключі сортування. Проте, якщо значення головного ключа не унікальні, в результуючій таблиці буде присутніми декілька рядків з одним і тим же значенням старшого ключа сортування. В цьому випадку, можливо, доведеться упорядкувати рядки з одним і тим же значенням головного ключа по якому-небудь додатковому ключу сортування.
У загальному випадку для створення обчислюваного (похідного) поля в списку SELECT слід вказати деяке вираження мови SQL. У цих виразах застосовуються арифметичні операції складання, віднімання, множення і ділення, а також вбудовані функції мови SQL. Можна вказати ім'я будь-якого стовпця (поля) таблиці або запиту, але використовувати ім'я стовпця тільки тієї таблиці або запиту, які вказані в списку пропозиції FROM відповідної інструкції. При побудові складних виразів можуть знадобитися дужки.
Стандарти SQL дозволяють явним чином задавати імена стовпців результуючої таблиці, для чого застосовується фраза AS.
Приклад 4.1. Розрахувати загальну вартість для кожної угоди. Цей запит використовує розрахунок результуючих стовпців на основі арифметичних виразів.
SELECT Товар.Назва, Товар.Ціна,
Угода.Кількість
Товар.Ціна*Угода.Кількість AS Вартість
FROM Товар INNER JOIN Угода
ON Товар.КодТовару=Угода.КодТовару
Приклад 4.1. Розрахунок загальної вартості для кожної угоди.
Приклад 4.2. Отримати список фірм з вказівкою прізвища і ініціалів клієнтів.
SELECT Фірма, Прізвище+" "+
Left(Ім'я, 1)+"" .+Left(По батькові, 1)+"".AS ПІБ
FROM Клієнт
Приклад 4.2. Отримання списку фірм з вказівкою прізвища і ініціалів клієнтів.
У запиті використана вбудована функція Left, що дозволяє вирізувати в текстовій змінній один символ ліворуч в даному випадку.
Приклад 4.3. Отримати список товарів з вказівкою року і місяця продажу.
SELECT Товар.Назва, Year(Угода.Дата)
AS Рік, Month(Угода.Дата) AS Місяць
FROM Товар INNER JOIN Угода
ON Товар.КодТовару=Угода.КодТовару
Приклад 6.3. Отримання списку товарів з вказівкою року і місяця продажу.
У запиті використані вбудовані функції Year і Month для виділення року і місяця з дати.
За допомогою підсумкових (агрегатних) функцій у рамках SQL -запиту можна отримати ряд узагальнювальних статистичних відомостей про безліч відібраних значень вихідного набору.
Користувачеві доступні наступні основні підсумкові функції:
• Count (Вираження) - визначає кількість записів у вихідному наборі SQL -запиту;
• Min/Max (Вираження) - визначають найменше і найбільше з безлічі значень в деякому полі запиту;
• Avg (Вираження) - ця функція дозволяє розрахувати середнє значення безлічі значень, що зберігаються в певному полі відібраних запитом записів. Воно є арифметичним середнім значенням, тобто сумою значень, що ділиться на їх кількість.
• Sum (Вираження) - обчислює суму безлічі значень, що містяться в певному полі відібраних запитом записів.
Найчастіше вираженням виступають імена стовпців. Вираження може обчислюватися і по значеннях декількох таблиць.
Усі ці функції оперують зі значеннями в єдиному стовпці таблиці або з арифметичним вираженням і повертають єдине значення. Функції COUNT, MIN і MAX застосовані як до числових, так і до нечислових полів, тоді як функції SUM і AVG можуть використовуватися тільки у разі числових полів, за винятком COUNT(*). При обчисленні результатів будь-яких функцій спочатку виключаються усі порожні значення, після чого необхідна операція застосовується тільки до конкретних значень стовпця, що залишилися. Варіант COUNT(*) - особливий випадок використання функції COUNT, його призначення полягає в підрахунку усіх рядків в результуючій таблиці, незалежно від того, містяться там порожні, такі, що дублюються або будь-які інші значення.
Якщо до застосування узагальнювальної функції необхідно виключити значення, що дублюються, слід перед ім'ям стовпця у визначенні функції помістити ключове слово DISTINCT. Воно не має сенсу для функцій MIN і MAX, проте його використання може вплинути на результати виконання функцій SUM і AVG, тому необхідно заздалегідь обдумати, чи повинне воно бути присутнім у кожному конкретному випадку. Крім того, ключове слово DISTINCT може бути вказане у будь-якому запиті не більше одного разу.
Дуже важливо відмітити, що підсумкові функції можуть використовуватися тільки в списку пропозиції SELECT і у складі пропозиції HAVING. У усіх інших випадках це неприпустимо. Якщо список в пропозиції SELECT містить підсумкові функції, а в тексті запиту відсутня фраза GROUP BY, що забезпечує об'єднання даних в групи, то жоден з елементів списку пропозиції SELECT не може включати яких-небудь посилань на поля, за винятком ситуації, коли поля виступають аргументами підсумкових функцій.
Часто в запитах вимагається формувати проміжні підсумки, що зазвичай відображається появою в запиті фрази "для кожного.". Для цієї мети в операторові SELECT використовується пропозиція GROUP BY. Запит, в якому є присутнім GROUP BY, називається групуючим запитом, оскільки в нім групуються дані, отримані в результаті виконання операції SELECT, після чого для кожної окремої групи створюється єдиний сумарний рядок. Стандарт SQL вимагає, щоб пропозиція SELECT і фраза GROUP BY були тісно пов'язані між собою. За наявності в операторові SELECT фрази GROUP BY кожен елемент списку в пропозиції SELECT повинен мати єдине значення для усієї групи. Більше того, пропозиція SELECT може включати тільки наступні типи елементів : імена полів, підсумкові функції, константи і вирази, що включають комбінації перелічених вище елементів.
Усі імена полів, приведені в списку пропозиції SELECT, мають бути присутніми і у фразі GROUP BY - за винятком випадків, коли ім'я стовпця використовується в підсумковій функції. Зворотне правило не є справедливим - у фразі GROUP BY можуть бути імена стовпців, відсутні в списку пропозиції SELECT.
Якщо спільно з GROUP BY використовується пропозиція WHERE, то воно обробляється першим, а групуванню піддаються тільки ті рядки, які задовольняють умові пошуку.
Стандартом SQL визначено, що при проведенні групування усі відсутні значення розглядаються як рівні. Якщо два рядки таблиці в одному і тому ж групованому стовпці містять значення NULL і ідентичні значення в усіх інших непорожніх групованих стовпцях, вони поміщаються в одну і ту ж групу.
За допомогою HAVING відбиваються усі попередньо згруповані за допомогою GROUP BY блоки даних, задовольняючі заданим в HAVING умовам. Це додаткова можливість "профільтрувати" вихідний набір.
Умови в HAVING відрізняються від умов в WHERE:
• HAVING виключає з результуючого набору даних групи з результатами агрегованих значень;
• WHERE виключає з розрахунку агрегатних значень по угрупуванню записи, що не задовольняють умові;
• у умові пошуку WHERE не можна задавати агрегатні функції.
Мова SQL орієнтована на виконання операцій над групами записів, хоча в деяких випадках їх можна проводити і над окремим записом.
Запити дії є досить потужним засобом, оскільки дозволяють оперувати не лише окремими рядками, але і набором рядків. За допомогою запитів дії користувач може додати, видалити або відновити блоки даних. Існує три види запитів дії:
• INSERT INTO - запит додавання ;
• DELETE - запит видалення ;
• UPDATE - запит оновлення.
Оператор INSERT застосовується для додавання записів в таблицю. Формат оператора:
<оператор_вставки>::=INSERT INTO <ім'я_таблиці>
[(ім'я_стовпця [,..n])]
{VALUES (значення[,..n])|
<SELECT_оператор>}
Тут параметром ім'я_таблиці є або ім'я таблиці бази даних, або ім'я оновлюваного представлення.
Перша форма оператора INSERT з параметром VALUES призначена для вставки єдиного рядка у вказану таблицю. Список стовпців вказує стовпці, яким будуть присвоєні значення в записах, що додаються. Список може бути опущений, тоді маються на увазі усі стовпці таблиці (окрім оголошених як лічильник), причому в певному порядку, встановленому при створенні таблиці. Якщо в операторові INSERT вказується конкретний список імен полів, то будь-які пропущені в нім стовпці мають бути оголошені при створенні таблиці як що допускають значення NULL, за винятком тих випадків, коли при описі стовпця використовувався параметр DEFAULT. Список значень повинен таким чином відповідати списку стовпців :
• кількість елементів в обох списках має бути однаковою;
• повинна існувати пряма відповідність між позицією одного і того ж елементу в обох списках, тому перший елемент списку значень повинен відноситися до першого стовпця в списку стовпців, другий - до другого стовпця і так далі
• типи цих елементів в списку значень мають бути сумісні з типами цих відповідних стовпців таблиці.
Приклад 4.1. Додати в таблицю ТОВАР новий запис.
INSERT INTO Товар (Назва, Тип, Ціна)
VALUES(" Слов'янський ", " шоколад ", 12)
Приклад 4.1. Додавання в таблицю ТОВАР нового запису.
Якщо стовпці таблиці ТОВАР вказані у повному складі і в тому порядку, в якому вони перераховані при створенні таблиці ТОВАР, оператор можна спростити.
INSERT INTO Товар VALUES (" Слов'янський
" " шоколад ", 12)
Друга форма оператора INSERT з параметром SELECT дозволяє скопіювати безліч рядків з однієї таблиці в іншу. Пропозиція SELECT може бути будь-який допустимий оператор SELECT. Рядки, що вставляються у вказану таблицю, в точності повинні відповідати рядкам результуючої таблиці, створеної при виконанні вкладеного запиту. Усі обмеження, вказані вище для першої форми оператора SELECT, застосовні і в цьому випадку.
Оскільки оператор SELECT в загальному випадку повертає безліч записів, то оператор INSERT в такій формі призводить до додавання в таблицю аналогічного числа нових записів.
Приклад 4.2. Додати в підсумкову таблицю зведення про загальну суму щомісячних продажів кожного найменування товару.
INSERT INTO Підсумок
( (Назва, Місяць, Вартість )
SELECT Товар.Назва, Month(Угода.Дата)
AS Місяць, Sum(Товар.Ціна*Угода.Кількість)
AS Вартість
FROM Товар INNER JOIN Угода
ON Товар.КодТовару= Угода.КодТовару
GROUP BY Товар.Назва, Month(Угода.Дата)
Приклад 4.2. Додавання в підсумкову таблицю зведення про загальну суму щомісячних продажів кожного найменування товару.
Оператор DELETE призначений для видалення групи записів з таблиці.
Формат оператора :
<оператор_видалення> ::=DELETE
FROM <ім'я_таблиці>[WHERE <умова_відбору>]
Тут параметром ім'я_таблиці є або ім'я таблиці бази даних, або ім'я оновлюваного представлення.
Якщо пропозиція WHERE є присутньою, віддаляються записи з таблиці, що задовольняють умові відбору. Якщо опустити пропозицію WHERE, з таблиці будуть видалені усі записи, проте сама таблиця збережеться.
Приклад 4.3. Видалити усі торішні угоди.
DELETE
FROM Угода
WHERE Year(Угода.Дата)=Year(GETDATE()) – 1
Приклад 4.3. Видалення усіх торішніх угод.
У наведеному прикладі умова відбору формується з урахуванням року (функція Year ) від поточної дати (функція GETDATE() ).
Оператор UPDATE застосовується для зміни значень в групі записів або в одному записі вказаної таблиці.
Формат оператора :
<оператор_зміни> ::=
UPDATE ім'я_таблиці SET ім'я_стовпця=
<вираження>[,..n]
[WHERE <умова_відбору>]
Параметр ім'я_таблиці - це або ім'я таблиці бази даних, або ім'я оновлюваного представлення. У пропозиції SET вказуються імена одного і більше за стовпці, дані в яких необхідно змінити. Пропозиція WHERE є необов'язковою. Якщо воно опущене, значення вказаних стовпців будуть змінені в усіх рядках таблиці. Якщо пропозиція WHERE є присутньою, то оновлені будуть тільки ті рядки, які задовольняють умові відбору. Вираження є новим значенням відповідного стовпця і має бути сумісне з ним за типом даних.
Приклад 4.4. Для товарів першого сорту встановити ціну в значення 140 і залишок - в значення 20 одиниць.
UPDATE Товар SET Товар.Ціна=140, Товар.Залишок=20
WHERE Товар.Сорт=" Перший "
Приклад 4.4. Оновлення вибраних записів.
Приклад 4.5. Збільшити ціну товарів першого сорту на 25%.
UPDATE Товар SET Товар.Ціна=Товар.Ціна*1.25
WHERE Товар.Сорт=" Перший "
Приклад 4.5. Оновлення вибраних записів.
Приклад 4.6. У угоді з максимальною кількістю товару збільшити число товарів на 10%.
UPDATE Угода SET Угода.Кількість=
Угода.Кількість*1.1
WHERE Угода.Кількість=
( (SELECT Max(Угода.Кількість) FROM Угода)
Приклад 4.6. Оновлення вибраних записів.
Виконання операторів модифікації даних в таблицях бази даних INSERT, DELETE і UPDATE може привести до порушення цілісності даних і їх коректності, тобто до втрати їх достовірності і несуперечності.
Щоб інформація, що зберігається у базі даних, була однозначною і несуперечливою, в реляційній моделі встановлюються деякі обмежувальні умови - правила, що визначають можливі значення даних і таких, що забезпечують логічну основу для підтримки коректних значень. Обмеження цілісності дозволяють звести до мінімуму помилки, що виникають при оновленні і обробці даних.
У базі даних, побудованій на реляційній моделі, задається ряд правил цілісності, які, по суті, є обмеженнями для усіх допустимих станів бази даних і гарантують коректність даних. Розглянемо наступні типи обмежень цілісності даних :
• обов'язкові дані;
• обмеження для доменів полів;
• корпоративні обмеження;
• цілісність сутностей;
• посилальна цілісність.
Обов'язкові дані
Деякі поля завжди повинні містити одно з допустимих значень, іншими словами, ці поля не можуть мати порожнього значення.
Обмеження для доменів полів
Кожне поле має свій домен, що є набором його допустимих значень.
Корпоративні обмеження цілісності
Існує поняття "корпоративні обмеження цілісності " як додаткові правила підтримки цілісності даних, визначувані користувачами, прийняті на підприємстві або адміністраторами баз даних. Обмеження підприємства називаються бізнес-правилами.
Цілісність сутностей
Це обмеження цілісності торкається первинних ключів базових таблиць. За визначенням, первинний ключ - мінімальний ідентифікатор (одно або декілька полів), який використовується для унікальної ідентифікації записів в таблиці. Таким чином, ніяка підмножина первісного ключа не може бути достатньою для унікальної ідентифікації записів.
Цілісність сутностей визначає, що у базовій таблиці жодне поле первинного ключа не може містити відсутніх значень, позначених NULL.
Якщо допустити присутність визначника NULL у будь-якій частині первинного ключа, це рівносильно твердженню, що не усі його поля потрібні для унікальної ідентифікації записів, і суперечить визначенню первинного ключа.
Цілісність посилання
Вказане обмеження цілісності торкається зовнішніх ключів. Зовнішній ключ - це поле (чи безліч полів) однієї таблиці, що є ключем іншої (чи тій же самій) таблиці. Зовнішні ключі використовуються для встановлення логічних зв'язків між таблицями. Зв'язок встановлюється шляхом привласнення значень зовнішнього ключа однієї таблиці значенням ключа інший.
Між двома або більше таблицями бази даних можуть існувати стосунки підлеглості, які визначають, що для кожного запису головної таблиці (званою ще батьківською ) може існувати одна або декілька записів в підпорядкованій таблиці (званою так же дочірньої ).
Існує три різновиди зв'язку між таблицями бази даних :
• ""один-до-багатьох";
• ""один-до-одного";
• ""багато-до-багатьох".
Відношення "один-до-одного" має місце, коли одному запису батьківської таблиці може відповідати декілька записів дочірньої. Зв'язок "один-до-багатьох" іноді називають зв'язком "багато-до-одного". І у тому, і в іншому випадку суть зв'язку між таблицями залишається незмінною.
Зв'язок "один-до-багатьох" найбільш поширений для реляційних баз даних. Вона дозволяє моделювати також ієрархічні структури даних.
Відношення "один-до-одного" має місце, коли одному запису у батьківській таблиці відповідає один запис в дочірній. Це відношення зустрічається набагато рідше, ніж відношення "один-до-багатьох". Його використовують, якщо не хочуть, щоб таблиця БД "розпухала" від другорядної інформації. Використання зв'язку "один-до-одного" призводить до того, що для читання пов'язаної інформації в декількох таблицях доводиться робити декілька операцій читання замість однієї, коли дані зберігаються в одній таблиці.
Відношення "багато-до-багатьох" має місце в наступних випадках:
• одному запису у батьківській таблиці відповідає більше за один запис в дочірній таблиці ;
• одному запису в дочірній таблиці відповідає більше за один запис у батьківській таблиці.
Вважається, що всякий зв'язок "багато-до-багатьох" може бути замінений на зв'язок "один-до-багатьох" (один або декілька).
Часто зв'язок між таблицями встановлюється по первинному ключу, тобто значення зовнішнього ключа однієї таблиці привласнюються значенню первісного ключа іншої. Проте це не є обов'язковим - в загальному випадку зв'язок може встановлюватися і за допомогою вторинних ключів. Крім того, при встановленні зв'язків між таблицями не потрібно неодмінну унікальність ключа, що забезпечує встановлення зв'язку. Поля зовнішнього ключа не зобов'язані мати тих же імен, що і імена ключів, яким вони відповідають. Зовнішній ключ може посилатися на свою власну таблицю - у такому разі зовнішній ключ називається рекурсивним.
Посилальна цілісність визначає: якщо в таблиці існує зовнішній ключ, то його значення повинне або відповідати значенню первісного ключа деякого запису у базовій таблиці, або задаватися визначником NULL.
Існує декілька важливих моментів, пов'язаних із зовнішніми ключами. По-перше, слід проаналізувати, чи допустиме використання в зовнішніх ключах порожніх значень. У загальному випадку, якщо участь дочірньої таблиці в зв'язку є обов'язковою, то рекомендується забороняти застосування порожніх значень у відповідному зовнішньому ключі. В той же час, якщо має місце часткова участь дочірньої таблиці в зв'язку, то приміщення порожніх значень в поле зовнішнього ключа має бути дозволене. Наприклад, якщо в операції фіксації угод деякої торгової фірми необхідно вказати покупця, то поле КодКлієнта повинне мати атрибут NOT NULL. Якщо допускається продаж або купівля товару без вказівки клієнта, то для поля КодКлієнта можна вказати атрибут NULL.
Наступна проблема пов'язана з організацією підтримки посилальної цілісності при виконанні операцій модифікації даних у базі. Тут можливі наступні ситуації:
1. Вставка нового рядка в дочірню таблицю. Для забезпечення посилальної цілісності необхідно переконатися, що значення зовнішнього ключа нового рядка дочірньої таблиці рівно порожньому значенню або деякому конкретному значенню, присутньому в поле первісного ключа одного з рядків батьківської таблиці.
2. Видалення рядка з дочірньої таблиці. Ніяких порушень посилальної цілісності не відбувається.
3. Оновлення зовнішнього ключа в рядку дочірньої таблиці. Цей випадок подібний до описаної вище першої ситуації. Для збереження цілісності посилання необхідно переконатися, що значення зовнішнього ключа в оновленому рядку дочірньої таблиці рівно порожньому значенню або деякому конкретному значенню, присутньому в полі первинного ключа одного з рядків батьківської таблиці.
4. Вставка рядка у батьківську таблицю. Така вставка не може викликати порушення посилальної цілісності. Доданий рядок просто стає батьківським об'єктом, що не має дочірніх об'єктів.
5. Видалення рядка з батьківської таблиці. Посилальна цілісність виявиться порушеною, якщо в дочірній таблиці існуватимуть рядки, що посилаються на видалений рядок батьківської таблиці. В цьому випадку може використовуватися одна з наступних стратегій :
o NO ACTION. Видалення рядка з батьківської таблиці забороняється, якщо в дочірній таблиці існує хоч би один рядок, що посилається на неї.
o CASCADE. При видаленні рядка з батьківської таблиці автоматично видаляються усі рядки дочірньої таблиці, що посилаються на неї. Якщо будь-який з рядків дочірньої таблиці, що видаляються, виступає батьківською стороною в якому-небудь іншому зв'язку, то операція видалення застосовується до усіх рядків дочірньої таблиці цього зв'язку і так далі. Іншими словами, видалення рядка батьківської таблиці автоматично поширюється на будь-які дочірні таблиці.
o SET NULL. При видаленні рядка з батьківської таблиці в усіх рядках дочірнього відношення, що посилаються на неї, в поле зовнішнього ключа, що відповідає первинному ключу видаленого рядка, записується порожнє значення. Отже, видалення рядків з батьківської таблиці викличе занесення порожнього значення у відповідне поле дочірньої таблиці. Ця стратегія може використовуватися, тільки коли в полі зовнішнього ключа дочірньої таблиці дозволяється поміщати порожні значення.
o SET DEFAULT. При видаленні рядка з батьківської таблиці в поле зовнішнього ключа усіх рядків дочірньої таблиці, що посилаються на неї, автоматично поміщається значення, вказане для цього поля як значення за умовчанням. Таким чином, видалення рядка з батьківської таблиці викликає приміщення значення, що набуває за умовчанням, в поле зовнішнього ключа усіх рядків дочірньої таблиці, що посилаються на видалений рядок. Ця стратегія застосовна лише в тих випадках, коли полю зовнішнього ключа дочірньої таблиці призначено деяке значення, що приймається за умовчанням.
o NO CHECK. При видаленні рядка з батьківської таблиці ніяких дій зі збереження посилальної цілісності даних не робиться.
6. Оновлення первинного ключа в рядку батьківської таблиці. Якщо значення первинного ключа деякого рядка батьківської таблиці буде оновлено, порушення посилальної цілісності станеться за тієї умови, що в дочірньому відношенні існують рядки, що посилаються на початкове значення первинного ключа. Для збереження посилальної цілісності може застосовуватися будь-яка з описаних вище стратегій. При використанні стратегії CASCADE оновлення значення первинного ключа в рядку батьківської таблиці буде відображене у будь-якому рядку дочірньої таблиці, що посилається на цей рядок.
Існує і інший вид цілісності - смислова (семантична) цілісність бази даних. Вимога смислової цілісності визначає, що дані у базі даних повинні змінюватися так, щоб не порушувався смисловий зв'язок, що склався між ними.
Рівень підтримки цілісності даних в різних системах істотно варіюється.
Ідеологія архітектури клієнт-сервер вимагає перенесення максимально можливого числа правил цілісності даних на сервер. До переваг такого підходу відносяться:
• гарантія цілісності бази даних, оскільки усі правила зосереджені в одному місці (у базі даних);
• автоматичне застосування визначених на сервері обмежень цілісності для будь-яких застосувань;
• відсутність різних реалізацій обмежень в різних клієнтських застосуваннях, працюючих з базою даних;
• швидке спрацьовування обмежень, оскільки вони реалізовані на сервері і, отже, немає необхідності посилати дані клієнтові, збільшуючи при цьому мережевий трафік;
• доступність внесених в обмеження на сервері змін для усіх клієнтських застосувань, працюючих з базою даних, і відсутність необхідності повторного поширення змінених додатків клієнтів серед користувачів.
До недоліків зберігання обмежень цілісності на сервері можна віднести:
• відсутність у клієнтського додатка можливості реагувати на деякі помилкові ситуації, що виникають на сервері при реалізації тих або інших правил (наприклад, помилок при виконанні процедур, що зберігаються, на сервері);
• обмеженість можливостей мови SQL і мови процедур, що зберігаються, і тригерів для реалізації усіх виникаючих потреб визначення цілісності даних.
На практиці в клієнтських застосуваннях реалізують лише такі правила, які важко або неможливо реалізувати із застосуванням засобів сервера. Усі інші обмеження цілісності даних переносяться на сервер.
При реалізації на мові SQL складних алгоритмів, які можуть знадобитися більше одного разу, відразу постає питання про збереження розробленого коду для подальшого застосування. Цю задачу можна було б реалізувати за допомогою збережених процедур , однак їх архітектура не дозволяє використовувати процедури безпосередньо у виразах, тому вони вимагають проміжного присвоєння повернутого значення змінної, яка потім і вказується в вираженні. Природно, подібний метод застосування програмного коду не дуже зручний. Багато розробники вже давно хотіли мати можливість виклику розроблених алгоритмів безпосередньо в виразах.
Можливість створення призначених для користувача функцій була надана в середовищі MS SQL Server 2000. В інших реалізаціях SQL в розпорядженні користувача є тільки вбудовані функції , які забезпечують виконання найбільш поширених алгоритмів: пошук максимального чи мінімального значення і ін
Функції користувача являють собою самостійні об'єкти бази даних, такі, наприклад, як збережені процедури або тригери. Функція користувача розташовується в певній базі даних і доступна тільки в її контексті.
У SQL Server є наступні класи функцій користувача :
• Scalar - функції повертають звичайне скалярний значення, кожна може включати безліч команд, що об'єднуються в один блок за допомогою конструкції BEGIN ... END;
• Inline - функції містять всього одну команду SELECT і повертають користувачеві набір даних у вигляді значення типу даних TABLE ;
• Multi-statement - функції також повертають користувачеві значення типу даних TABLE , що містить набір даних, проте в тілі функції знаходиться безліч команд SQL ( INSERT , UPDATE і т.д.). Саме з їх допомогою і формується набір даних, який має бути повернуто після виконання функції .
Користувальницькі функції схожі з збереженими процедурами , але, на відміну від них, можуть застосовуватися в запитах так само, як і системні вбудовані функції . Користувальницькі функції , що повертають таблиці, можуть стати альтернативою переглядам. Перегляди обмежені одним виразом SELECT , а користувальницькі функції здатні включати додаткові вирази, що дозволяє створювати більш складні і потужні конструкції.
Створення та зміна функції даного типу виконується за допомогою команди:
<Визначення_скаляр_функціі> :: =
{CREATE | ALTER} FUNCTION [власник.]
імя_функції
([{@ імя_параметра скаляр_тип_даних
[= default]} [, ... n]])
RETURNS скаляр_тип_даних
[WITH {ENCRYPTION | SCHEMABINDING}
[, .. . n]]
[AS]
BEGIN
<тіло_функціі>
RETURN скаляр_вираз
END
Розглянемо призначення параметрів команди.
Функція може містити один або декілька вхідних параметрів або не містити жодного. Кожен параметр повинен мати унікальне в межах створюваної функції ім'я і починатися з символу " @ ". Після імені вказується тип даних параметра. Додатково можна вказати значення, яке буде автоматично присвоюватися параметру ( DEFAULT ), якщо користувач явно не вказав значення відповідного параметра при виклику функції .
За допомогою конструкції RETURNS скаляр_тип_даних вказується, який тип даних буде мати повертане функцією значення.
Додаткові параметри, з якими повинна бути створена функція , можуть бути зазначені за допомогою ключового слова WITH . Завдяки ключовим словом ENCRYPTION код команди, використовуваний для створення функції , буде зашифровано, і ніхто не зможе переглянути його. Ця можливість дозволяє приховати логіку роботи функції . Крім того, в тілі функції може виконуватися звернення до різних об'єктів бази даних, а тому зміна або видалення відповідних об'єктів може призвести до порушення роботи функції . Щоб уникнути цього, потрібно заборонити внесення змін, вказавши при створенні цієї функції ключове слово SCHEMABINDING .
Між ключовими словами BEGIN ... END вказується набір команд, вони й будуть тілом функції .
Коли в ході виконання коду функції зустрічається ключове слово RETURN , виконання функції завершується і як результат її обчислення повертається значення, вказане безпосередньо після слова RETURN . Відзначимо, що в тілі функції дозволяється використання безлічі команд RETURN , які можуть повертати різні значення. В якості значення, що повертається допускаються як звичайні константи, так і складні вирази. Єдина умова - тип даних значення, що повертається повинен збігатися з типом даних, зазначених після ключового слова RETURNS .
Приклад 6.1 . Створити і застосувати функцію скалярного типу для обчислення сумарної кількості товару, що надійшов за певну дату. Власник функції - користувач з ім'ям user1 .
CREATE FUNCTION
user1.sales (@ data DATETIME)
Returns INT
AS
BEGIN
DECLARE @ C INT
SET @ c = (SELECT SUM (кількість)
FROM Угода
WHERE дата = @ data)
RETURN (@ c)
END
Приклад 6.1. Створення функції скалярного типу для обчислення сумарної кількості товару, що надійшов за певну дату.
В якості вхідного параметра використовується дата. Функція повертає значення цілого типу, отримане з оператора SELECT шляхом підсумовування кількості товару з таблиці Угода . Умовою відбору записів для підсумовування є рівність дати угоди значенню вхідного параметра функції .
Проілюструємо звернення до функції користувача : визначимо кількість товару, що надійшов за 02.11.01:
DECLARE @ Kіl INT
SET @ kіl = user1.sales ('02 .11.01 ')
SELECT @ kіl
Створення та зміна функції цього типу виконується за допомогою команди:
<Визначен_табл_функціі> :: =
{CREATE | ALTER} FUNCTION [власник.]
імя_функції
([{@ імя_параметра скаляр_тип_даних
[= default]} [, ... n]])
RETURNS TABLE
[WITH {ENCRYPTION | SCHEMABINDING}
[, .. . n]]
[AS]
RETURN [(] SELECT_оператор [)]
Основна частина параметрів, використовуваних при створенні табличних функцій , аналогічна параметрам скалярної функції . Проте створення табличних функцій має свою специфіку.
Після ключового слова RETURNS завжди повинно вказуватися ключове слово TABLE . Таким чином, функція даного типу повинна строго повертати значення типу даних TABLE . Структура значення, що повертається типу TABLE не вказується явно при описі власне типу даних. Замість цього сервер буде автоматично використовувати для значення, що повертається TABLE структуру, повертану запитом SELECT , який є єдиною командою функції .
Особливість функції даного типу полягає в тому, що структура значення TABLE створюється автоматично в ході виконання запиту, а не вказується явно при визначенні типу після ключового слова RETURNS .
Повертане функцією значення типу TABLE може бути використано безпосередньо у запиті, тобто в розділі FROM .
Приклад 6.2 . Створити і застосувати функцію табличного типу для визначення двох найменувань товару з найбільшим залишком.
CREATE FUNCTION user1.itog ()
Returns TABLE
AS
RETURN (SELECT TOP 2 Товар.Назва
FROM Товар INNER JOIN Склад
ON Товар.КодТовару = Склад.КодТовару
ORDER BY Склад.Залишок DESC)
Приклад 6.2. Створення функції табличного типу для визначення двох найменувань товару з найбільшим залишком.
Використовувати функцію для отримання двох найменувань товару з найбільшим залишком можна наступним чином:
SELECT Назва
FROM user1.itog ()
Створення та зміна функцій типу Multi-statement виконується за допомогою наступної команди:
<Визначення_мульти_функціі> :: =
{CREATE | ALTER} FUNCTION [власник.]
імя_функції
([{@ імя_параметра скаляр_тип_даних
[= default]} [, ... n]])
RETURNS @ імя_параметра TABLE
<визначення_таблиці>
[WITH {ENCRYPTION | SCHEMABINDING }
[, ... n]]
[AS]
BEGIN
<тіло_функціі>
RETURN
END
Використання більшої частини параметрів розглядалося при описі попередніх функцій .
Відзначимо, що функції даного типу, як і табличні , повертають значення типу TABLE . Однак, на відміну від табличних функцій , при створенні функцій Multi-statement необхідно явно задати структуру значення, що повертається. Вона зазначається безпосередньо після ключового слова TABLE і, таким чином, є частиною визначення повертаного типу даних. Синтаксис конструкції <визначен_таблиці> повністю відповідає однойменним структурам, використовуваним при створенні звичайних таблиць за допомогою команди CREATE TABLE .
Набір повертаних даних повинен формуватися за допомогою команд INSERT , виконуваних в тілі функції . Крім того, в тілі функції допускається використання різних конструкцій мови SQL, які можуть контролювати значення, що розміщуються у вихідному наборі рядків. При роботі з командою INSERT потрібно явно вказати ім'я того об'єкта, куди необхідно вставити рядки. Тому в функціях типу Multi-statement , на відміну від табличних , необхідно присвоїти якесь ім'я об'єкту з типом даних TABLE - воно і вказується як повернене значення.
Завершення роботи функції відбувається у двох випадках: якщо виникають помилки виконання і якщо з'являється ключове слово RETURN . На відміну від функцій скалярного типу , при використанні команди RETURN не потрібно вказувати повернене значення. Сервер автоматично поверне набір даних типу TABLE , ім'я та структура якого була вказана після ключового слова RETURNS . У тілі функції може бути вказано більше однієї команди RETURN .
Необхідно відзначити, що робота функції завершується тільки при наявності команди RETURN . Це твердження вірне і в тому випадку, коли мова йде про досягнення кінця тіла функції - самої останньої командою повинна бути команда RETURN .
Строкові функції
Короткий огляд строкових функцій представлений в таблиці.
|
Таблиця 6.2. |
|
|
ASCII |
повертає код ASCII лівого символу рядка |
|
CHAR |
за кодом ASCII повертає символ |
|
CHARINDEX |
визначає порядковий номер символу, з якого починається входження підрядка в рядок |
|
DIFFERENCE |
повертає показник збігу рядків |
|
LEFT |
повертає вказану кількість символів з початку рядка |
|
LEN |
повертає довжину рядка |
|
LOWER |
переводить всі символи рядка в нижній регістр |
|
LTRIM |
видаляє пробіли на початку рядка |
|
NCHAR |
повертає за кодом символ Unicode |
|
PATINDEX |
виконує пошук підрядка в рядку за вказаною шаблоном |
|
REPLACE |
замінює входження підрядка на вказане значення |
|
QUOTENAME |
конвертує рядок в формат Unicode |
|
REPLICATE |
виконує тиражування рядки певне число разів |
|
REVERSE |
повертає рядок, символи якої записані у зворотному порядку |
|
RIGHT |
повертає вказану кількість символів з кінця рядка |
|
RTRIM |
видаляє пробіли в кінці рядка |
|
SOUNDEX |
повертає код звучання рядка |
|
SPACE |
повертає вказану кількість пробілів |
|
STR |
виконує конвертування значення числового типу в символьний формат |
|
STUFF |
видаляє вказане число символів, замінюючи нової підрядком |
|
SUBSTRING |
повертає для рядка підрядок зазначеної довжини з заданого символу |
|
UNICODE |
повертає Unicode-код лівого символу рядка |
|
UPPER |
переводить всі символи рядка в верхній регістр |
SELECT Фірма, [Прізвище] + ""
+ Left ([Ім'я], 1) + "."
+ Left ([По батькові], 1)
+ "." AS ПІБ
FROM Клієнт
Приклад 6.5. Використання функції LEFT для отримання ініціалів клієнтів.
Короткий огляд основних функцій для роботи з датою і часом представлений в таблиці.
|
Таблиця 6.3. |
|
|
DATEADD |
додає до дати зазначена значення днів, місяців, годин і т.д. |
|
DATEDIFF |
повертає різницю між зазначеними частинами двох дат |
|
DATENAME |
виділяє з дати вказану частину і повертає її в символьному форматі |
|
DATEPART |
виділяє з дати вказану частину і повертає її в числовому форматі |
|
DAY |
повертає число з вказаної дати |
|
GETDATE |
повертає поточний системний час |
|
ISDATE |
перевіряє правильність виразу на відповідність одному з можливих форматів вводу дати |
|
MONTH |
повертає значення місяця з вказаної дати |
|
YEAR |
повертає значення року з вказаної дати |
SELECT Year (Дата) AS Рік, Month (Дата)
AS Місяць,
Sum (Кількість) AS Заг_Кількість
FROM Угода
GROUP BY Year (Дата), Month (Дата)
Приклад 6.6. Використання функцій YEAR і MONTH для визначення загальної кількості товару, проданого за кожний місяць кожного року.
DECLARE @ D DATETIME
DECLARE @ Y INT
SET @ d = '29 .10.03 '
SET @ y = DATEPART (yy, @ d)
SELECT @ y
Приклад 6.7. Приклад виділення з дати значення року.
Збережені процедури, є групами пов'язаних між собою операторів SQL, застосування яких робить роботу програміста легшою і гнучкішою, оскільки виконати процедуру, що зберігається, часто виявляється набагато простіше, ніж послідовність окремих операторів SQL. Збережені процедури, є набором команд, що складається з одного або декількох операторів SQL або функцій і зберігається у базі даних у виді, що відкомпілювався. Виконання у базі даних процедур замість окремих операторів SQL, що зберігаються, дає користувачеві наступні переваги:
• необхідні оператори вже містяться у базі даних;
• усі вони пройшли етап синтаксичного аналізу і знаходяться у виконуваному форматі; перед виконанням збереженої процедури, SQL Server генерує для неї план виконання, виконує її оптимізацію і компіляцію;
• Збережені процедури, підтримують модульне програмування, оскільки дозволяють розбивати великі завдання на самостійні, дрібніші і зручніші в управлінні частини;
• Збережені процедури, можуть викликати інші Збережені процедури, і функції;
• Збережені процедури, можуть бути викликані з прикладних програм інших типів;
• як правило, Збережені процедури, виконуються швидше, ніж послідовність окремих операторів;
• Збережені процедури, простіше використовувати: вони можуть складатися з десятків і сотень команд, але для їх запуску досить вказати усього лише ім'я потрібної збереженої процедури. Це дозволяє зменшити розмір запиту, що посилається від клієнта на сервер, а значить, і навантаження на мережу.
Зберігання процедур в тому ж місці, де вони виконуються, забезпечує зменшення об'єму передаваних по мережі даних і підвищує загальну продуктивність системи. Застосування процедур, що зберігаються, спрощує супровід програмних комплексів і внесення змін в них. Зазвичай усі обмеження цілісності у вигляді правил і алгоритмів обробки даних реалізуються на сервері баз даних і доступні кінцевому застосуванню у вигляді набору процедур, що зберігаються, які і представляють інтерфейс обробки даних. Для забезпечення цілісності даних, а також в цілях безпеки, додаток зазвичай не дістає прямого доступу до даних - уся робота з ними ведеться шляхом виклику тих або інших процедур, що зберігаються.
Подібний підхід робить дуже просту модифікацію алгоритмів обробки даних, що негайно ж стають доступними для усіх користувачів мережі, і забезпечує можливість розширення системи без внесення змін до самозастосування: досить змінити процедуру, що зберігається, на сервері баз даних. Розробникові не треба перекомпілювати додаток, створювати його копії, а також інструктувати користувачів про необхідність роботи з новою версією. Користувачі взагалі можуть не підозрювати про те, що в систему внесені зміни.
Збережені процедури, існують незалежно від таблиць або яких-небудь інших об'єктів баз даних. Вони викликаються клієнтською програмою, іншою процедурою, що зберігається, або тригером. Розробник може управляти правами доступу до збереженої процедури, дозволяючи або забороняючи її виконання. Змінювати код збереженої процедури, дозволяється тільки її власникові або членові фіксованої ролі бази даних. При необхідності можна передати права володіння нею від одного користувача до іншого.
При роботі з SQL Server користувачі можуть створювати власні процедури, що реалізовують ті або інші дії. Збережені процедури, є повноцінними об'єктами бази даних, а тому кожна з них зберігається в конкретній базі даних. Безпосередній виклик збереженої процедури, можливий, тільки якщо він здійснюється в контексті тієї бази даних, де знаходиться процедура.
Типи процедур, що зберігаються
У SQL Server є декілька типів процедур, що зберігаються.
• Системні збережені процедури, призначені для виконання різних адміністративних дій. Практично усі дії з адміністрування сервера виконуються з їх допомогою. Можна сказати, що системні Збережені процедури, є інтерфейсом, що забезпечує роботу з системними таблицями, яка, кінець кінцем, зводиться до зміни, додавання, видалення і вибірки даних з системних таблиць як призначених для користувача, так і системних баз даних. Системні збережені процедури мають префікс sp_, зберігаються в системній базі даних і можуть бути викликані в контексті будь-якої іншої бази даних.
• Призначені для користувача Збережені процедури, реалізують ті або інші дії. Збережені процедури, - повноцінний об'єкт бази даних. Внаслідок цього кожна процедура, що зберігається, розташовується в конкретній базі даних, де і виконується.
• Тимчасові збережені процедури, існують лише деякий час, після чого автоматично знищуються сервером. Вони діляться на локальні і глобальні. Локальні тимчасові Збережені процедури, можуть бути викликані тільки з того з'єднання, в якому створені. При створенні такої процедури їй необхідно дати ім'я, що починається з одного символу #. Як і усі тимчасові об'єкти, процедури цього типу, що зберігаються, автоматично віддаляються при відключенні користувача, перезапуску або зупинці сервера. Глобальні тимчасові Збережені процедури, доступні для будь-яких з'єднань сервера, на якому є така ж процедура. Для її визначення досить дати їй ім'я, що починається з символів ##. Віддаляються ці процедури при перезапуску або зупинці сервера, а також при закритті з'єднання, в контексті якого вони були створені.
Створення, зміна і видалення процедур, що зберігаються
Створення збереженої процедури, припускає рішення наступних завдань :
• визначення типу створюваної збереженої процедури : тимчасова або призначена для користувача. Окрім цього, можна створити свою власну системну процедуру, що зберігається, призначивши їй ім'я з префіксом sp_ і помістивши її в системну базу даних. Така процедура буде доступна в контексті будь-якої бази даних локального сервера;
• планування прав доступу. При створенні збереженої процедури, слід враховувати, що вона матиме ті ж права доступу до об'єктів бази даних, що і користувач, що створив її;
• визначення параметрів збереженої процедури. Подібно до процедур, що входять до складу більшості мов програмування, Збережені процедури, можуть бути наділені вхідними і вихідними параметрами ;
• розробка коду збереженої процедури. Код процедури може містити послідовність будь-яких команд SQL, включаючи виклик інших процедур, що зберігаються.
Створення нової і зміна наявної збереженої процедури, здійснюється за допомогою наступної команди:
<визначення_процедури>::=
{CREATE | ALTER } [PROCEDURE] ім'я_процедури
[;номер]
[{@ім'я_параметра тип_даних } [VARYING ]
[=default][OUTPUT] ][,..n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_оператор [..n]
Розглянемо параметри цієї команди.
Використовуючи префікси sp_, #, ##, створювану процедуру можна визначити в якості системної або тимчасової. Як видно з синтаксису команди, не допускається вказувати ім'я власника, якому належатиме створювана процедура, а також ім'я бази даних, де вона має бути розміщена. Так, щоб розмістити утворюючу збережену процедуру в конкретній базі даних, необхідно виконати команду CREATE PROCEDURE в контексті цієї бази даних. При зверненні з тіла збереженої процедури, до об'єктів тієї ж бази даних можна використовувати укорочені імена, т. е. без вказівки імені бази даних. Коли ж вимагається звернутися до об'єктів, розташованих в інших базах даних, вказівка імені бази даних обов'язково.
Номер в імені - це ідентифікаційний номер збереженої процедури, що однозначно визначає її в групі процедур. Для зручності управління процедурами логічно однотипні Збережені процедури, можна групувати, привласнюючи їм однакові імена, але різні ідентифікаційні номери.
Для передачі вхідних і вихідних даних в створюваній процедурі, що зберігається, можуть використовуватися параметри, імена яких, як і імена локальних змінних, повинні починатися з символу @. В одній процедурі, що зберігається, можна задати безліч параметрів, розділених комами. У тілі процедури не повинні застосовуватися локальні змінні, чиї імена співпадають з іменами параметрів цієї процедури.
Для визначення типу даних, який матиме відповідний параметр збереженої процедури, годяться будь-які типи даних SQL, включаючи визначені користувачем. Проте тип даних CURSOR може бути використаний тільки як вихідний параметр збереженої процедури, тобто з вказівкою ключового слова OUTPUT.
Наявність ключового слова OUTPUT означає, що відповідний параметр призначений для повернення даних з збереженої процедури. Проте це зовсім не означає, що параметр не підходить для передачі значень в процедуру, що зберігається. Вказівка ключового слова OUTPUT наказує серверу при виході з збереженої процедури, присвоїти поточне значення параметра локальної змінної, яка була вказана при виклику процедури в якості значення параметра. Відмітимо, що при вказівці ключового слова OUTPUT значення відповідного параметра при виклику процедури може бути задане тільки за допомогою локальної змінної. Не дозволяється використання будь-яких виразів або констант, допустиме для звичайних параметрів.
Ключове слово VARYING застосовується спільно з параметром OUTPUT, що має тип CURSOR. Воно визначає, що вихідним параметром буде результуюча множина.
Ключове слово DEFAULT є значенням, яке прийматиме відповідний параметр за умовчанням. Таким чином, при виклику процедури можна не вказувати явно значення відповідного параметра.
Оскільки сервер кешує план виконання запиту і код, що компілює, при наступному виклику процедури використовуватимуться вже готові значення. Проте в деяких випадках все ж вимагається виконувати перекомпіляцію коду процедури. Вказівка ключового слова RECOMPILE наказує системі створювати план виконання збереженої процедури, при кожному її виклику.
Параметр FOR REPLICATION затребуваний при реплікації даних і включенні створюваної збереженої процедури, в якості статті в публікацію.
Ключове слово ENCRYPTION наказує серверу виконати шифрування коду збереженої процедури, що може забезпечити захист від використання авторських алгоритмів, що реалізовують роботу збереженої процедури.
Ключове слово AS розміщується на початку власне тіла збереженої процедури, тобто набору команд SQL, за допомогою яких і реалізовуватиметься те або інша дія. У тілі процедури можуть застосовуватися практично усі команди SQL, оголошуватися транзакції, встановлюватися блокування і викликатися інші Збережені процедури. Вихід зі збереженої процедури можна здійснити за допомогою команди RETURN.
Видалення збереженої процедури, здійснюється командою:
DROP PROCEDURE {ім'я_процедури} [,..n]
Виконання збереженої процедури
Для виконання збереженої процедури, використовується команда:
[[ EXEC [ UTE] ім'я_процедури [;номер]
[[@ім'я_параметра=]{значення | @ім'я_змінної}
[OUTPUT ]|[DEFAULT ]][,..n]
Якщо виклик збереженої процедури, не є єдиною командою в пакеті, то присутність команди EXECUTE обов'язкова. Більше того, цю команду потрібно для виклику процедури з тіла іншої процедури або тригера.
Використання ключового слова OUTPUT при виклику процедури дозволяється тільки для параметрів, які були оголошені при створенні процедури з ключовим словом OUTPUT.
Коли ж при виклику процедури для параметра вказується ключове слово DEFAULT, то буде використано значення за умовчанням. Природно, вказане слово DEFAULT дозволяється тільки для тих параметрів, для яких визначено значення за умовчанням.
З синтаксису команди EXECUTE видно, що імена параметрів можуть бути опущені при виклику процедури. Проте в цьому випадку користувач повинен вказувати значення для параметрів в тому ж порядку, в якому вони перераховувалися при створенні процедури. Присвоїти параметру значення за умовчанням, просто пропустивши його при перерахуванні не можна. Якщо ж вимагається опустити параметри, для яких визначено значення за умовчанням, досить явної вказівки імен параметрів при виклику збереженої процедури. Більше того, у такий спосіб можна перераховувати параметри і їх значення в довільному порядку.
Відмітимо, що при виклику процедури вказуються або імена параметрів зі значеннями, або тільки значення без імені параметра. Їх комбінування не допускається.
На сьогоднішній день (і в осяжному майбутньому) мова SQL є єдиним визнаним стандартом мови баз даних, підтримуваним всіма основними постачальниками СУБД. З роками (а історія SQL налічує вже близько 30 років) мова розвивається і ускладнюється. Добре володіння мовою SQL є обов'язковим для професійних розробників додатків баз даних та їх адміністраторів. У курсову роботу включені теми, що є, по-перше, найбільш важливими для користувачів і розробників додатків і, по-друге, найбільш проробленими в поточній версії стандарту. У вступній частині курсової роботи обговорюються історія мови, її основні концепції, типи даних, допустимі в SQL. Наводиться огляд підходів SQL до визначення даних і маніпулювання ними. В основній частині послідовно обговорюються наступні теми: значення, базові функції і вирази SQL; арифметичні вирази з перемикачами і перетворенням типу, вирази зі рядковими значеннями; види предикатів, допустимих в логічних виразах; з'єднання та теоретико-множинні операції; види виразів запитів; обмеження цілісності і посилальна цілісність; тригери; вбудована SQL і мова модулів; принципи роботи з курсорами; привілеї, користувачі і безпека; управління транзакціями; управління з'єднаннями і віддалений доступ до баз даних; збережені процедури і функції; динамічний SQL; інтерфейс рівня викликів; діагностика та управління помилковими ситуаціями; питання інтернаціоналізації та локалізації; інформаційна схема.
http://www.intuit.ru/department/database/sql/
PAGE