Математична логіка. Мова SQL
Виконав Романов А.Н.
Російський державний гуманітарний університет
Москва 2007
Мова запитів SQL
Першими
спробами піти від побудови баз даних (БД) на основі фізичної структури їх
розміщення на носіях були індексні файли. Вони забезпечували доступ до
збережені в них інформації за допомогою індексних ключів, тобто для пошуку
якихось конкретних записів у файлі використовувалася сукупність покажчиків. До
недоліків такого підходу можна віднести, зокрема, Неоптимальне зберігання
інформації (дублювання, недостатнє структурування) і значний час
пошуку у великих файлах, не кажучи вже про істотні вимоги,
що пред'являються до апаратного забезпечення.
Одним
з рішень згаданих проблем стали ієрархічні БД. У таких базах елементи
строго впорядковані, причому так, що дані одного рівня підпорядковуються (є
підмножиною) даними іншого, вищого рівня. У такій моделі зв'язку можуть
бути відображені у вигляді дерева, причому допускаються тільки односторонні - від
старших рівнів до молодших. Подібна структура все ще нагадує деревоподібну
файлову систему, де директорії є верхніми рівнями, а файли - нижніми.
Незважаючи на те що в порівнянні з індексними файлами це був суттєвий крок
вперед, ієрархічні БД наслідували численні недоліки попередніх
систем, які полягали в складності алгоритмів доступу до даних нижніх рівнів і
підвищених апаратних вимогах.
Ієрархічні
БД не отримали широкого розповсюдження, поступившись місцем нової концепції зберігання
даних (реляційні БД). Вона полягала у використанні табличного методу
зберігання та доступу до конкретних записів, що використовуються і в даний
час.
Прогрес
в галузі мережевих технологій сприяв виникненню проблем організації
доступу до даних, розташованих на серверах, з віддалених комп'ютерів --
учасників мережі. Для оптимізації цього процесу і зниження мережевого трафіку
Міжнародною організацією зі стандартизації (ISO) був розроблений і впроваджений
структурований мова запитів SQL.
З
розвитком Глобальної мережі і зростанням обсягів що звертається в ній інформації завдання
надання доступу до останньої практично повсюдно стали вирішуватися з
допомогою технологій БД загалом і SQL зокрема. В даний час рядовий
користувач, сам того не знаючи, активно працює з базами даних, навіть просто
переглядаючи веб-сторінки і форуми, або користуючись сервісами електронної пошти
та ICQ.
Незважаючи
на те, що ієрархічні БД ще не канули в лету остаточно, вони, як
згадувалося вище, використовуються вкрай рідко - в основному при вирішенні якихось
спеціалізованих завдань, внаслідок чого не має сенсу докладно
зупинятися на їх пристрої. Набагато цікавіше розглянути реляційні
БД.
Отже,
дані в таких базах розміщені в взаємопов'язаних таблицях, рядки яких
називаються записами, а стовпці - полями. При цьому дані в комірках одного поля
повинні бути однакового типу. У кожній
таблиці, як правило, є спеціальні поля, які дозволяють однозначно
ідентифікувати той чи інший запис - вони називаються первинними ключами або
первинними індексами. Такі поля допомагають відрізняти один запис від іншої, навіть
якщо всі інші поля декількох таких записів абсолютно ідентичні. Наприклад,
уявіть, що ви розробляєте довідник співробітників своєї організації, і
при цьому кожен запис зберігає дані про одного працівника, а їх вибірка
здійснюється по полю «Прізвище». Може виявитися так, що в організації
працює кілька осіб з однаковим прізвищем. Щоб відрізнити ці записи
один від одного, застосовуються первинні індексні поля. Найчастіше за тип даних
первинного ключа береться цілочисельне значення лічильника - в такому випадку при
додавання нового запису в таблицю значення цього поля заповнюються
автоматично. Однак не забороняється використовувати як первинний ключ
поле, що має, приміром, символьний тип даних, хоча подібні ситуації
виникають вкрай рідко.
Крім
задачі ідентифікації записів первинні індекси також часто використовуються для
зв'язування між собою даних з різних таблиць. Крім первинних індексів
існують і вторинні індекси, які забезпечують механізм швидкого пошуку і
доступу до даних таблиці. Щоб отримати відповідь на запит до таблиці, що не має
індексного поля, SQL-серверу доведеться сканувати повністю всю таблицю,
зчитуючи рядки цілком. Очевидно, що такий підхід при великих обсягах
інформації занадто марнотратний з точки зору витрат апаратних ресурсів. Тим
не менш необхідно пам'ятати, що зі збільшенням кількості індексів зростає і
обсяг бази даних.
За
ознакою методу доступу БД поділяються на локальні, мережеві та розподілені.
До
локальних баз доступ можливий тільки з того комп'ютера, на якому вони розташовані.
Мережеві бази покликані забезпечити роботу з даними з інших комп'ютерів
за допомогою локальної мережі або Інтернету. Розподілені БД - це, по суті,
підвид мережевих баз з тією лише різницею, що різні частини інформації
знаходяться на безлічі різних комп'ютерів.
На
сьогоднішній день основним видом є другий тип БД - мережеві бази. Саме
вони застосовуються в Інтернеті для організації доступу користувачів до інформації
сайтів, форумів, гостьових книг і каталогів товарів.
Такі
БД поділяються на файл-серверні і клієнт-серверні. У файл-серверної моделі при
СУБД підключенні клієнта до віддаленої базі всі дані завантажуються на локальний
комп'ютер, а після їх обробки або зміни знову завантажуються на сервер для
поновлення у повному складі. Таким чином, відбувається досить інтенсивний
інформаційний обмін з сервером, що надзвичайно навантажує мережу. Крім того, в
такої моделі дуже складно організувати одночасну роботу декількох
користувачів, тому в даний час вона використовується рідко і лише для простих
баз. Клієнт-серверна модель організована зовсім інакше. Система управління
такою базою складається з двох частин - клієнта і сервера. Клієнтська частина
програми посилає запити за допомогою мови запитів SQL,
серверна
частина обробляє їх і відправляє назад тільки ті дані, які були потрібні
користувачеві. Інформація про зміну цих даних повертається на сер-
вір,
який обробляє її і фіксує в загальній базі. Навантаження на мережу при такій
організації роботи мінімальна, а організацію одночасної роботи декількох
користувачів бере на себе сервер.
Метою
будь-який СУБД бути надання користувачу простих механізмів доступу і
маніпулювання даними. Існує багато різних методів її досягнення,
одним з яких є мова SQL. Розшифрувати і перекладена на російську
мовою ця абревіатура буде виглядати як структурованої мови запитів.
Стандарт
мови SQL. визначається Американським національним інститутом стандартів (ANSI)
та Міжнародною організацією зі стандартизації (ISO). Однак деякі
виробники БД вносять у мову свої зміни та доповнення. Наприклад, компанія
Огас1е створила мову PL/SQL, який є процедурним розширенням
оригінальному SQL.
Однак
і стандартний SQL може використовуватися в двох різних варіантах --
інтерактивному і вкладеному. Перший представляється собою окремий програмний
модуль на SQL, який сам виконує запити і відображає результати роботи.
Другий - це впровадження елементів SQL в іншу мову високого рівня, наприклад С
або Delphi. У цьому випадку основна програма самостійно формує запити
для сервера SQL, а потім використовує результати вибірок з бази, не пред'являючи
їх користувачеві в чистому вигляді.
В
Загалом же SQL - це мова, орієнтований на роботу з реляційними базами даних. Його використання дозволяє на
порядок скоротити обсяг робіт, який знадобився б у разі створення
додатків, що використовують БД на універсальною мовою програмування, наприклад
на тому ж С.
Дійсно,
щоб сформувати реляційну базу даних на С, потрібно було б описати як
мінімум один об'єкт (двовимірний масив), званий в SQL. таблицею, який
повинен мати можливість міняти розмір для вміщення будь-якого необхідного числа
рядків. Потім довелося б створювати процедури для приміщення значень в таку таблицю,
а також пошуку та вилучення цих значень. Це непросто навіть на перший погляд.
Так, якби ви захотіли знайти всі рядки в таблиці «TAB», в яких значення
якогось поля «num» дорівнює 5, то необхідно було б виконати по кроках вельми
складну процедуру.
Грамотний
лістинг програми, що реалізує такий алгоритм на С і що дотримує всі належні
перевірки займе, мабуть, всю цю сторінку. У випадку ж використання SQL
досить написати ось такий запит:
SELECT *
FROM tab
WHERE num = 5
Справа
в тому, що команди SQL можуть працювати з усіма записами таблиці як з єдиним
об'єктом. Необхідність організації циклів і перевірок значень вручну
відпадає.
Згаданий
у прикладі оператор SELECT є основним і найбільш часто використовуваних для
вибірки даних з таблиць. Всі звернення до таблиць відбуваються у вигляді запитів,
які складаються з таких самих операторів, - змінюються тільки умови пошуку.
В
даний час важко знайти мова програмування високого рівня, який би
не підтримував роботу з SQL. Для цих цілей розробниками різних
компіляторів передбачені спеціальні бібліотеки компонентів, які
забезпечують зв'язок між додатками, написаними на їх мові, і серверами
SQL, в результаті чого дані, які будуть вибиратися з бази, можна
використовувати в клієнтської програмі обробки на свій розсуд.
Відразу
попередимо, що SQL Server 2000 можливо встановити тільки на серверні версії
операційної системи Windows - 2000 Server і 2003 Server. Спроби інсталяції
її на власні версії ОС, такі як Windows ХР Professional, якщо
тільки мова не йде про клієнтської частини, не увінчуються успіхом. Якщо ж ви
неодмінно хочете користуватися мовою запитів, працюючи під користувача
ОС, існує MSDE - це урізана версія SQL Server, що входить до
дистрибутив МS Office 2003.
Після
запуску інсталятора з компакт-диска в перших двох вікнах майстра установки
необхідно послідовно вибрати пункти «Components» і «Install Database
Server ». Результатом цих дій стане поява вікна з пропозицією вказати
розташування майбутнього сервера. Тут можливі такі варіанти: «Local
Computer »- локальний комп'ютер,« Remote computer »- віддалений комп'ютер у
вашої мережі, «Virtual Server» - віртуальний сервер мережі. Цей вибір залежить
тільки від конкретного завдання, яку ви вирішуєте в даний момент.
Якщо
ви вибрали комп'ютер, де до цього вже існувала якась версія SQL Server, то
у наступному вікні буде доступний пункт «Upgrade, remove or add components to an
existing instance of SQL Server »- додавання, зміна та видалення
існуючих компонентів сервера. Інакше тут можна буде вибрати лише «Create
a new instance of SQL Server or install Client Tolls »- створити новий екземпляр
SQL-сервера або встановити клієнтську частину. До речі, це по-
останню
«Або» сприяє появі ще одного розгалуження в дереві варіантів установки.
Наступне вікно «Installation Definition» надає на вибір пункти: «Client
Tools Only »- поставити клієнтську частину, яка використовується для організації
доступу до сервера з клієнтських машин; «Server and Client Tools» - серверну і
клієнтську частини, а також «Connectivity only» - тільки драйвери для
приєднання до бази. Останній тип установки застосовується виключно на
клієнтських машинах, які повинні працювати з базами даних, тобто утиліти
для роботи з сервером, подібні Enterprise Manadger, інсталюватися не будуть.
Оскільки ми встановлюємо саме сервер - тут слід вибрати другий
варіант.
Введення
імені ( «Instance Name») в наступному вікні не повинен викликати труднощів. За
замовчуванням воно буде ідентичне NETBIOS-імені вашої машини. Якщо потрібно його
змінити, доведеться зняти галочку в пункті «Default», після чого можна буде
ввести іншу назву.
З
далі запропонованих варіантів установки «Typical», «Minimum» і «Custom» корисно
використовувати саме останній варіант. При цьому вибирати щось зі списку
запропонованих компонентів не доведеться - пунктів, зазначених тут за замовчуванням,
більш ніж достатньо для початку роботи сервера.
Однак
параметрами запуску сервісів SQL Server в наступному вікні варто приділити більше
пильну увагу: «Auto start SQL Service» - всі сервіси стартують
автоматично; «Customize the setting for each Service» - кожному сервісу
призначити свої параметри запуску. Якщо ви виберете другий варіант, то в лівій частині
вікна на панелі «Services» стануть доступні елементи «SQL Server» і «SQL Server
Agent ». При виборі будь-якого з них в правій частині вікна на панелі "Service
Setting »можна буде налаштувати параметри запуску, вибравши один з трьох
стандартних варіантів: вимкнено, авто або вручну.
На
це настройки самого сервера фактично завершені, залишилось тільки вказати
параметри облікових записів, авторизації та режиму ліцензування. Отже, «Use the
Local System account »- використовувати обліковий запис локальної системи,« Use a
Domain User account »- використовувати доменну обліковий запис. Вибір цілком
залежить від ваших уподобань.
Для
вказівки методу авторизації (Authentication Mode) необхідно вибрати: «Windows
Authentication Mode »- використовувати авторизацію Windows NT або« Mixed Mode (Windows Authentication and SQL
Server Authentication) »- використовувати змішану авторизацію Windows NT і SQL-сервера). При
використанні другого методу не забудьте вказати пароль облікового запису
адміністратора «SА», що створюється за замовчуванням.
І,
нарешті, останнє вікно майстра «Choose Licensing Mode» присвячено вибору режиму
ліцензування, який залежить від умов, на яких ви придбали дану
версію SQL Server 2000.
Основні інструменти
Найбільш
часто використовується утилітою для роботи з SQL Server є Enterprise
Manager. Цей інструмент створювався з метою полегшення виконання найбільш
складних адміністративних завдань, поєднуючи простоту роботи з високою
функціональністю. Серед них такі як управління системою безпеки,
створення баз даних та її об'єктів, створення та відновлення резервних копій,
запуск та встановлення служб, а також конфігурування пов'язаних і віддалених
серверів.
Наприклад,
створення нової бази даних за допомогою Enterprise Manager зводиться до кількох
кліків миші та вводу імені БД. У лівій частині екрана утиліти потрібно вибрати
той SQL Server, на якому буде схвалено, і натиснути правою кнопкою мишки на
папці Database, після чого в контекстному меню вибрати пункт «New Database». У
вікні, що з'явилося «Database Properties» в полі «Name» необхідно вписати
назва бази і натиснути кнопку «ОК». Опис цього процесу зайняло більше
часу, ніж він тривав би на ділі.
Створення
таблиць відбувається абсолютно аналогічно: клік спочатку правою кнопкою миші на
значку «Table», потім лівою - на пункті «New Table» в контекстному меню. Вище
був наведений приклад алгоритму вибірки з бази даних для класичної мови
високого рівня. Просто уявіть, скільки часу і сил зайняло б опис
на ньому такої структури як БД з декількома взаємозалежними таблицями.
Як
б добре не була налаштована база даних, завжди існує ймовірність втрати
інформації з незалежних від адміністратора причин. До відома подібних
втрат до розумного мінімуму Enterprise Manager містить інструмент резервного копіювання
БД.
Щоб
виконати резервне копіювання бази вручну, потрібно клацнути правою кнопкою
миші за назвою бази та обрати «Усі завдання •> Backup Database». Далі в
вікні треба натиснути кнопку «АDD '», після чого вибрати каталог для
збереження і в полі «File name» ввести ім'я файлу, в якому буде міститися
база. Ім'я цього файлу з назвою бази може бути ніяк не пов'язано. Після
підтвердження наміру залишається тільки чекати кінця процесу копіювання.
Для
того, щоб відновити дані з раніше збереженого файлу, необхідно мати на
SQL Server базу даних з назвою, ідентичним імені батьківського бази. Іншими
словами, якщо ви зберігали БД як Data_Base, то для відновлення даних
необхідно створити на сервері базу з такою ж назвою (не плутати з ім'ям
файлу резервної копії).
Для
відновлення інформації з файлу потрібно натиснути правою кнопкою мишки на базу,
призначену для прийому даних і вибрати «Усі завдання - Restore Database».
Далі в
послідовно
змінюють один одного вікнах потрібно вибрати пункти «FromDevise», «Select Devise» і
«АDD» і вказати каталог, в якому розташований резервний файл.
Друга
за важливістю і частоті використання утиліта після Enterprise Manager це Query
Analyzer. Вона предна?? начена для виконання, налагодження і аналізу запитів. Вікно
«Query Analizer» розділено на три частини. Зліва знаходиться браузер об'єктів
(Object Browser), за допомогою якого можна переглянути список всіх об'єктів,
розташованих в будь-якій базі даних сервера, а також перелік усіх функцій і
типів даних. Права частина розділена на верхню й нижню, при цьому верхня
половина є полем для введення запитів, а нижня використовується для виведення
результатів їх роботи та налагоджувальної інформації.
При
налагодження збережених процедур досить зручна можливість трасування їх виконання:
для цього необхідно буде клікнути на потрібній процедурі правою кнопкою миші і в
що з'явилися контекстному меню вибрати пункт «Debug».
Крім
виконання процедур і запитів в Query Analyzer передбачена можливість
оцінки швидкості роботи. Цю функцію можна включити, якщо відкрити меню "Query" і
вибрати в ньому, відповідно, пункти «Display Estimated», «Execution Plan» або
«Display Execution Plan».
Оператор
SELECT
Основний
оператор мови SQL, призначений для вибірки даних, - SELECT:
SELECT
* FROM Table 1
Зірочка
означає всі стовпці, а Table1 - ім'я таблиці, з якої ми ці стовпці хочемо
витягнути. Практичну цінність оператору SELECT надає ключове слово WHERE,
дозволяє виводити виключно ті рядки таблиці, які відповідають
умові. Припустимо, у нас є таблиця з інформацією про персонал (Employees),
де вказано ім'я працівника (Name) і його заробітна плата (Salary). Якщо нам
потрібно побачити дані про всіх працівників, які отримують заробітну плату більше 30
000 рублів, ми формулюємо запит:
SELECT Name FROM Employees WHERE
Salary> 30000
На
практиці існує необхідність запитувати інформацію одночасно з
декількох таблиць. Припустимо, що в нас є таблиця Agents з інформацією про
торгових агентів: ідентифікаційний номер (Agent_id), ім'я (Name) і дата
народження (Birth_Date). Є ще одна таблиця - Contacts, де містяться дані
про контрактах, укладених агентами: ідентифікаційний номер клієнта
(Client_id), номер агента (Agent_id), дата укладення контракту (CDate) і сума
угоди (Gross_Income).
Припустимо,
необхідно преміювати агентів, що укладали з початку року контракти на суму
більше 500 000 рублів. Запит до бази даних буде виглядати так:
SELECT Name, Bitth_Date FROM Agents,
Contacts WHERE Agents.Agent_id = Contracts.Agent_id AND Contracts.CDate
> `31.12.2004` AND CONTRACTS.Gross_Income> 500000
Умови
WHERE пов'язує між собою дві таблиці через номер агента, відкидає
старі досягнення і вибирає значні контракти. Логічний оператор AND
дозволяє задавати кілька умов. Запис <Назва
таблиці>. <Назва стовпця> застосовується для того, щоб розрізняти
стовпчики з однією і тією ж назвою з різних таблиць.
Оскільки
один агент може укласти кілька великих контрактів, його дані можуть бути декілька
раз продубльовані. Щоб уникнути цього, необхідно використовувати ключове
слово DISTINCT:
SELECT
DISTINCT Name, Birth_Day From ...
Використання
імен і вкладених запитів
В
SQL-конструкціях призначення нових імен застосовується, щоб зберегти для подальших
операцій результати, що повертаються запитами і вбудованими функціями, і зробити
текст запиту більш компактним за рахунок скорочень. Для демонстрації ефектів
перепризначення імен, візьмемо, наприклад, таблицю Rooms з інформацією про житлові
кімнатах у багатоквартирному будинку з наступними стовпцями: ідентифікатор кімнати
(Room_id), тип (Room_type), довжина (Length) і ширина (Width). Припустимо, ми
хочемо отримати інформацію про житлової площі всіх спалень і віталень в будинку. Для цього формулюємо запит:
SELECT Room_Type, Length * Width AS
Living_Space
FROM Rooms
WHERE Room_Type = `Вітальня OR Room_Type =` Спальня `
В
результуючої таблиці не буде даних про довжині і ширині, зате з'явиться стовпець
з інформацією про площу, яка була обчислена безпосередньо при виконанні
запиту
Другий
варіант застосування ключового слова AS можна проілюструвати на прикладі про
торгових агентів з попередньої замітки, який тепер буде виглядати так:
SELECT DISTINCT Name, Birth_Date
FROM Agents AS A1, Contracts AS C1
WHERE A1.Agent_id = C1.Agent_id AND
C1.CDate
> `31.12.2004`
AND C1.Gross_Income> 500000
Давши
за допомогою ключового слова АS таблиць Agents і Contacts скорочені імена, ми
зробили текст більш компактним. Запит можна зробити багатоступінчастим, тоді
результат вкладеного запиту стане вихідними даними. Той же самий приклад з
агентами можна виконати у вигляді вкладеного запиту:
SELECT Name, Birth_Day
FROM Agents
WHERE Agent_id IN (SELECT Agent_id
FROM Contracts
WHERE CDate> `31.12.2004` AND
Gross_Income> 500000
В
даному випадку предикат IN послідовно перевіряє, чи є серед
результатів вкладеного запиту по базі контрактів ідентифікатор кожного з
агентів. Якщо він є, то в результуючу таблицю головного запиту
додаються його дані (для протилежного результату можна використовувати
предикат NOT IN). У більшості випадків переважно замість вкладених
запитів застосовувати підключення таблиць за загальними стовпцях (... WHERE Agents.Ag_Num
= Contracts.Ag_Num ...), однак іноді буває, що все-таки без вкладень не
обійтися.
Об'єднання
і зовнішні з'єднання
Вище
ми скористалися оператором OR для вибірки даних про спальнях і віталень.
Можна піти іншим шляхом і використовувати оператор UNION для об'єднання двох
запитів:
(SELECT Room_Type, Length1 * Width1
AS
Living_Space FROM Rooms WHERE
Room_Type =
`Вітальня`) UNION
(SELECT Room_Type, Length1 *
Width1 AS Living_Space FROM Rooms
WHERE
Room_Type
= `Спальня`)
Оператор
UNION будує на основі двох таблиць третє, куди потрапляють рядки, які є
або в першій вихідної, або на другий, або в обох разом; рядки-дублікати
при цьому віддаляються. Іноді для подібних цілей зручніше користуватися оператором
OR, проте якщо умови об'єднуються підзапитів складні, UNION для їх
складання підходить більше. Суть такого інструменту як зовнішнє з'єднання
можна пояснити на такому прикладі. Припустимо, нам необхідно зробити вибірку
за контрактами, укладеними агентами в червні 2005 року. Ми можемо скористатися
для цього таким запитом:
SELECT
Name, CDate, Gross_Income
FROM Agents AS A1, Contracts AS C1
WHERE A1.Agent_id = C1.Agent_id AND
C1.CDate
BETWEEN
`01.06.2005` AND `30.06.2005`
Він,
зрозуміло, видасть правильні результати, однак наявність імені агента після
обробки запиту залежить від того, чи він уклав угоду в цей період. Якщо
необхідно, щоб у результуючої таблиці завжди були присутні всі агенти,
необхідно використовувати так зване ліве зовнішнє з'єднання (LEFT OUTER
JOIN). Його зміст полягає в тому, що всі рядки таблиці, зазначеної ліворуч від
оператора LEFT OUTER JOIN, потрапляють в таблицю-результат, а з таблиці праворуч
беруться лише дані, які відповідають умові:
SELECT Name, CDate, Gross_Income
FROM Agents LEFT OUTER JOIN
Contracts ON
Agents.Agent_id = Contracts.Agent_id
AND Contracts.CDate BETWEEN
`01.06.2005` AND
`30.06.2005`
Кожен
агент з таблиці Agents збережені ліворуч від LEFT OUTER JOIN, потрапить до
результат запиту, навіть якщо йому не можна буде підібрати відповідних рядків
з правої таблиці (оскільки не всі агенти укладали контракти в червні 2005
року). Необхідно звернути увагу, що замість ключового слова WHERE тут
використовується слово ON. Якщо використовувати слово WHERE, результат буде той же
самий, що і з звичайним запитом. Слід також пам'ятати, що синтаксис лівого
зовнішнього з'єднання може сильно відрізнятися в різних системах.
Математичні
функції і засоби роботи з датами
Оскільки
SQL орієнтований на вибірку даних, а не на управління обчисленнями, його
математичний інструментарій досить обмежений. Втім, перелік доступних
функцій у продукти різних розробників може варіюватися. Як правило, в
більшості реалізацій наявні такі функції: POWER (зведення у
ступінь), SQRT (квадратний корінь), АВS (модуль), LN і LOG10 (натуральний і
десятковий логарифми), ехр (Експоненціальна функція). Функція ROUND (х, р)
округлює число х до р десяткових знаків, TRUNCATE (х, р) - усікаємо. Опції
FLOOR (х) і CEILING (х) повертають найближчі до нецілим х цілі числа знизу і
зверху відповідно. Припустимо, нам для чогось знадобилося знайти не площа,
а діагональ кожній спальні з таблиці Rooms і округлити її до двох знаків після
комою. Запит буде мати такий вигляд:
SELECT Room_id,
ROUND (SQRT (POWER (Length, 2) +
POWER (Width, 2)), 2) AS Bias
FROM Rooms
WHERE Room_Type = `Спальня`
На
виході у нас вийде таблиця з двох стовпців, що містять інформацію про
ідентифікаторі кімнати і її довжині по діагоналі.
В
більшості реалізацій SQL присутній предикат BETWEEN, який кілька
полегшує роботу з інтервалами чисел, зокрема з тимчасовими і календарними
інтервалами (ми зіткнулися в попередньому розділі в прикладі з вибіркою контактів
за червень). У загальному випадку синтаксис предиката такий:
Val1
BETWEEN Low AND High
Предикат
поверне TRUE, якщо значення Val1 буде перебувати всередині діапазону,
обмеженого значеннями Low і High, або в іншому випадку False. Для простого
формування дат в діалектах SQL багатьох сучасних СУБД присутня
відповідна функція MAKEDATE, яка викликається за такими аргументами:
MAKEDATE (Year,
Month, Day)
Якщо
необхідно виконати зворотну задачу - виокремити рік, місяць або день з дати,
застосовують оператор EXTRACT. Зокрема, коли потрібно визначити поточний рік,
користуються таким виразом:
EXTRACT (YEAR FROM CURRENT_DATE)
Для
того щоб зміститися щодо якоїсь дати на задану кількість днів,
місяців або років, використовується ключове слово INTERVAL. Наприклад, наступне
вираз повертає дату, зміщений на п'ятнадцять днів вперед щодо
дати MyDate:
MyDate
+ INTERVAL 15 DAYS
Групи
і агрегатні функції
Іноді
виникають ситуації, коли необхідно провести групування даних, відкинувши
непотрібну необхідну інформацію, зате додавши кількісні оцінки груп.
Для цього в SQL є оператор GROUP BY.
Припустимо,
що нам необхідно отримати з таблиці Contracts дані щодо кількості
контрактів і загального обсягу продажів, що припадають на одного агента. Кожен запис
в таблиці Contracts описує один контракт. Одному агенту може
відповідати декілька таких записів. Отже, щоб отримати потрібний
результат, треба згрупувати таблицю по полю «Ag_Num», що містить індекс
агента:
SELECT Agent_id, SUM (Gross_Income)
AS
Gr_Income, COUNT (*) AS Contracts_Num
FROM Contracts
GROUP BY Agent_id
В
результуючої таблиці буде три стовпці: у першому - номер агента, у другому --
сума всіх укладених ним контрактів, у третьому - кількість цих контрактів.
Опції SUM, COUNT (а також AVG, MIN та МАХ) називаються агрегатними. Їх відмінність
від математичних функцій полягає в тому, що аргументом може бути довільне
безліч чисел. У нашому випадку функція SUM складає всі значення
Gross_Income для кожної окремої групи, а COUNT (*) підраховує кількість
записів у ній. Ускладнимо приклад, щоб побачити використання функції AVG
(обчислення середнього арифметичного).
Начальнику
відділу продажів потрібно уважно стежити за відстаючими - тими, чий обсяг продажів
нижче середнього рівня. Для цього йому слід написати такий запит:
SELECT
Name, Gr_Income
FROM Agents AS A1, (SELECT Agent_id,
SUM (Gross_Income)
FROM Contracts
GROUP BY Agent_id) AS T1 (Agent_id,
Gr_Income)
WHERE A1.Agent_id = T1.Agent_id,
Gr_Income <
AVG
(T1.Gr_Income)
В
розділ FROM вкладено вже знайомий нам (але трохи скорочений) запит,
займається компонуванням. За допомогою ключового слова АS ми даємо тимчасової
таблиці його результатів і стовпцях цієї таблиці символьні імена, щоб
послатися на них в основному запиті. Цікавить нас стовпець «Обсяг контрактів
на одного працівника »називається тепер Gr_Income. А далі в розділі WHERE
основного запиту ми відбираємо тих агентів, у яких це значення нижче
середнього.
Оператор
CASE
Іноді
буває необхідно прямо в ході виконання запиту перетворювати символьні
дані в числові, і навпаки. У попередній замітці ми розглянули найпростіший
випадок компонування, коли для обчислення загальних параметрів достатньо було
підсумувати значення, що містилися в группіруемих записах. Але не всі
значення можна підсумувати. Припустимо, що ми маємо справу зі шкільної
відомістю School_Sheet, в якій міститься інформація щодо
ідентифікатора учня (Pupil_id), його ім'я (Name), статі (Gender) і класу, в
якому він навчається (Group_id). Якщо тепер виникне завдання згрупувати дітей
по класах і визначити, скільки людина вчиться в тому чи іншому класі, то ми
вже знаємо, як це робити:
SELECT Group_id, COUNT (*) AS Total
FROM School_Sheet
GROUP BY Group_id
Однак
куди бігти і за що хапатися, якщо потрібно підрахувати, скільки в кожному класі
хлопчиків і дівчаток? Тут до нас і прийде на допомогу оператор CASE:
SELECT
Group_id,
SUM (CASE WHEN Gender = `M` THEN 1
ELSE
0) AS Boys,
SUM (CASE WHEN Gender = `F` THEN 1
ELSE
0) AS Girls,
Boys + Girls AS Total
FROM School_Sheet
GROUP BY Group_id
На
кожного запису оператор CASE повертає 0 або 1 залежно від вмісту
поля Gender - отже, на одиницю збільшується лічильник хлопчиків або
лічильник дівчаток. Можлива й інша нотація для запису оператора CASE. Припустимо,
нам необхідно перевести літерні оцінки знань учнів у цифрові для
знаходження середнього балу. Відповідний оператор перекладу буде записана так:
CASE
Mark WHEN A THEN 5
WHEN B THEN 4
WHEN C THEN 3
WHEN D THEN 2
WHEN E THEN 1
Деякі
системи не підтримують оператор CASE. Обійти цю проблему можна за допомогою
таблиць відповідності. У нашому прикладі це буде таблиця Convert_Table з полями
«NMark» і «LMark», що містять цифровий та літерний варіанти. Якщо у вихідній
таблиці з оцінками School_Marks значення прописані в символьної формі, то
конверсію можна здійснити так:
SELECT Name, Discipline, NMark
FROM School_Marks AS S1,
Convert_Table AS C1
WHERE
S1.Mark = C1.LMark
Створення
таблиць і маніпуляції з даними
Можливості
SQL виходять за межі одного лише складання запитів. З його допомогою можна
створювати нові таблиці, додавати, оновлювати та видаляти дані. Перевага
перед ручним редагуванням таблиць за допомогою оболонки СУБД очевидно:
редагування здійснюється автоматично за заданими правилами при
мінімальної участі оператора - а значить, дуже швидко і без помилок. Дуже
важлива область застосування автоматичних маніпуляцій даними - побудова
проміжних таблиць. На комп'ютері, не повною мірою підтримують SQL-92, часто
виникають ситуації, коли результат проміжного запиту необхідно зберегти
в новій таблиці. Таблиця створюється за допомогою оператора CREATE TABLE, після чого
в дужках зазначаються найменування і типи полів:
CREATE TABLE Table1
(Field1 INTEGER NOT NULL,
Field2
VARCHAR (20) NOT NULL,
...)
Для
кожного стовпця можна задати додаткові Опції/обмеження, наприклад NOT
NULL (це означає, що в цьому стовпці не може бути «порожніх» значень) або
UNIQUE (означає, що у стовпці не може бути повторюваних значень). Нарешті,
за допомогою оператора CONSTRAINT можна накладати складні обмеження на
вміст таблиці із застосуванням повноцінних запитів. Наприклад, уявімо,
що в нашій таблиці Agents доданий ще один стовпець - «Work_Start_Date», в
якому зазначено, коли агент влаштувався на роботу. Тому можна задати
обмеження Contract_Date, що щоб уникнути помилок оператора буде
контролювати, щоб дата контракту, укладеного агентом, не була більш
ранньою, ніж дата його надходження на роботу:
CREATE TABLE Contracts
(Agent_id INTEGER NOT NULL,
Client_id INTEGER NOT NULL,
CDate DATE NOT NULL,
Gross_Income Number NOT NULL,
CONSTRAINT Contract_Date
CHECK (EXISTS
(SELECT Agent_id, Work_Start_Date
FROM Agents AS A1
WHERE A1.Agent_id =
Contracts.Agent_id AND
Contracts.CDate>
A1.Work_Start_Date )))
В
даному випадку комбінація СНЕСК (ЕХISTS (SELECT перевіряє, чи існує взагалі
агент, на ідентифікатор якого посилається додається в таблицю запис, і якщо
він існує - є дата укладення контракту новіша, ніж дата
надходження агента на роботу. Якщо обидві умови виконуються, SELECT повертає
непусті результати запиту, оператор EXISTS, відповідно, приймає значення
TRUE, і СНЕСК виявляється задоволений. Крім того, за допомогою ключового слова
DEFAULT можна задати значення, які зберігаються в даному стовпці за замовчуванням.
Оператор INSERT INTO дозволяє автоматично додавати в таблицю дані,
отримані в результаті запиту. Приміром, якщо створена проміжна таблиця
Т1, де повинна зберігатися інформація щодо обсягу продажів кожного агента,
то заповнюватися вона буде за допомогою знайомого нам запиту, що видає
згруповано результати:
CREATE TABLE T1
(Agent_id INTEGER,
Gr_Income NUMBER);
INSERT INTO T1
SELECT Agent_id, SUM (Gross_Income)
FROM
Contracts
GROUP BY Agent_id
Існує
версія цього оператора, яка дозволяє додавати в таблицю заздалегідь
певні значення, наприклад:
INSERT INTO Table1 VALUES ( `John
Smith `, 2, 34, 15)
Видалення
рядків з таблиці здійснюється за допомогою оператора DELETE FROM, якому
надає гнучкість ключове слово WHERE. Припустимо, що ми хочемо видалити з
таблиці Contacts дані про всіх контрактах, укладених до 2005 року. Для
цього нам знадобиться наступне вираз:
DELETE
FROM Contracts
WHERE
Cdate < `01.01.2005`
умовах аие
пошуку видаляються записів можна зробити настільки складним, наскільки необхідно:
з використанням даних з інших таблиць і застосуванням повноцінних запитів.
Щоб досягти цього, необхідно скористатися комбінацією WHERE (EXISTS
(SELECT, яка аналогічна щойно розглянутої. Якщо запит, що йде після
ключового слова SELECT повертає непусті результати, EXISTS і WHERE повертають
TRUE, і запис видаляється. Для коригування даних у таблиці застосовується
оператор UPDATE. Наприклад, наступне вираз збільшує в півтора рази
значення в полі Field1 в тих рядках, де це значення більше двох:
UPDATE Table1
SET Field1 = 1.5 * Field1
WHERE Field> 2
Ключове
слово WHERE тут діє так само, як у випадку з оператором DELETE.
Цілком
таблицю можна знищити за допомогою оператора DROP TABLE. Коли наша
проміжна таблиця T1, яку ми обговорювали вище, буде вже не потрібна, її
необхідно видалити за допомогою такої команди:
DROP
TABLE T1
Список літератури
Перегудов
Ф.І., Тарасенко Ф.П. Введення в системний аналіз. Навчальний посібник для для
ВУЗів. - М.: Вища школа, 1989. - 367 с.
Девід
А. Марка, Клемент Ман Гоуен. Методологія структурного аналізу і проектування /
Пер. з англ. - М.: Метатехнологія, 1993, 240 с.
Маклаков
С. В. Bpwin, Erwin: Case-засоби розробки інформаційних систем. М.:
Діалог-МИФИ, 2000, 254с.
Леоненко
Ф. В. Самоучитель UML. СПб.: BHV-Петербург, 2001, 304с.
Для
підготовки даної роботи були використані матеріали з сайту http://referat.ru
