ПЕРЕЛІК ДИСЦИПЛІН:
  • Адміністративне право
  • Арбітражний процес
  • Архітектура
  • Астрологія
  • Астрономія
  • Банківська справа
  • Безпека життєдіяльності
  • Біографії
  • Біологія
  • Біологія і хімія
  • Ботаніка та сільське гос-во
  • Бухгалтерський облік і аудит
  • Валютні відносини
  • Ветеринарія
  • Військова кафедра
  • Географія
  • Геодезія
  • Геологія
  • Етика
  • Держава і право
  • Цивільне право і процес
  • Діловодство
  • Гроші та кредит
  • Природничі науки
  • Журналістика
  • Екологія
  • Видавнича справа та поліграфія
  • Інвестиції
  • Іноземна мова
  • Інформатика
  • Інформатика, програмування
  • Юрист по наследству
  • Історичні особистості
  • Історія
  • Історія техніки
  • Кибернетика
  • Комунікації і зв'язок
  • Комп'ютерні науки
  • Косметологія
  • Короткий зміст творів
  • Криміналістика
  • Кримінологія
  • Криптология
  • Кулінарія
  • Культура і мистецтво
  • Культурологія
  • Російська література
  • Література і російська мова
  • Логіка
  • Логістика
  • Маркетинг
  • Математика
  • Медицина, здоров'я
  • Медичні науки
  • Міжнародне публічне право
  • Міжнародне приватне право
  • Міжнародні відносини
  • Менеджмент
  • Металургія
  • Москвоведение
  • Мовознавство
  • Музика
  • Муніципальне право
  • Податки, оподаткування
  •  
    Бесплатные рефераты
     

     

     

     

     

     

         
     
    Математична логіка. Мова SQL
         

     

    Інформатика, програмування

    Математична логіка. Мова 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

         
     
         
    Реферат Банк
     
    Рефераты
     
    Бесплатные рефераты
     

     

     

     

     

     

     

     
     
     
      Все права защищены. Reff.net.ua - українські реферати ! DMCA.com Protection Status