Доступний до Інтернету словник Merriam-Webster визначає базу данихяк великий набір даних, організований спеціальним чином для забезпечення швидкого пошукуі вилучення даних(наприклад, за допомогою комп'ютера).
Система управління базами даних (СУБД), як правило, є комплект бібліотек, додатків та утиліт, що звільняють розробника програми від вантажу турбот, що стосуються деталей зберігання та управління даними. СУБД також надає засоби пошуку та оновлення записів.
За багато років для вирішення різних видів проблем зберігання даних було створено безліч СУБД.
Типи баз даних
У 1960-70-х роках розроблялися бази даних, які тим чи іншим способом вирішували проблему груп, що повторюються. Ці методи сприяли створенню моделей систем управління базами даних. Основою для таких моделей, що використовуються і досі, стали дослідження, що проводяться в компанії IBM.
Одним із основних факторів проектування ранніх СУБД була ефективність. Набагато легше маніпулювати записами бази даних, що мають фіксовану довжину або, принаймні, фіксовану кількість елементів запису (стовпчиків у рядку). Так вдається уникнути проблеми повторюваних груп. Той, хто програмував якоюсь процедурною мовою, легко зрозуміє, що в цьому випадку можна прочитати кожен запис бази даних у просту структуру C. Однак у реальному житті такі вдалі ситуації зустрічаються рідко, тому програмістам доводиться обробляти не так зручно структуровані дані.
База даних із мережевою структурою
Мережева модель вводить до баз даних покажчики - записи, що містять посилання на інші записи. Так можна зберігати запис для кожного замовника. Кожен замовник протягом деякого часу розмістив у нас багато замовлень. Дані розташовані так, що запис замовника містить покажчик рівно на один запис замовлення. Кожен запис замовлення містить дані за цим конкретним замовленням, так і вказівник на інший запис замовлення. Тоді в додатку-конвертері валют, яким ми займалися раніше, можна було б використати структуру, яка б виглядала приблизно так (рис. 1.):
Рис. 1. Структура записів конвертеру валют
Дані завантажуються та виходить пов'язаний (звідси і назва моделі – мережна) список для мов (мал. 2):
Рис. 2. Пов'язаний список
Два різних типи записів, представлені малюнку, зберігатимуться окремо, кожен - у своїй власній таблиці.
Звичайно, було б доцільніше, якби назви мов не повторювалися в основі знову і знову. Ймовірно, краще ввести третю таблицю, в якій містилися б мови та ідентифікатор (часто в цій якості використовується ціле число), який використовувався для посилання на запис таблиці мов із записів іншого типу. Такий ідентифікатор називається ключем.
Мережа моделі бази даних має кілька важливих переваг. Якщо потрібно знайти всі записи одного типу, що відносяться до певного запису іншого типу (наприклад, мови, якими розмовляють в одній із країн), то можна зробити це дуже швидко, слідуючи за вказівниками, починаючи з зазначеного запису.
Є, проте, недоліки. Якщо нам потрібен перелік країн, у яких розмовляють французькою, доведеться пройти за посиланнями всіх записів країн, і для великих баз даних така операція виконуватиметься дуже повільно. Це можна виправити, створивши інші зв'язані списки вказівників спеціально для мов, але таке рішення швидко стає занадто складним і, звичайно ж, не є універсальним, оскільки необхідно вирішити, як будуть організовані посилання.
До того ж, писати додаток, що використовує мережеву модель бази даних, досить втомлює, тому що зазвичай відповідальність за створення та підтримку покажчиків у міру оновлення та видалення записів лежить на додатку.
Ієрархічна модель бази даних
Наприкінці 1960-х років IBM використовувала в СУБД IMS ієрархічну модель побудови бази. У цій моделі проблема повторюваних груп вирішувалася за рахунок представлення одних записів як складаються з багатьох інших.
Це можна як «специфікацію матеріалів», яка застосовується описи складових складного продукту. Наприклад, машина складається (скажімо) з шасі, кузова, двигуна та чотирьох коліс. Кожен із цих основних компонентів у свою чергу складається з деяких інших. Двигун включає кілька циліндрів, головку циліндра і колінчастий вал. Ці компоненти знов-таки складаються з дрібніших; так ми доходимо до гайок та болтів, якими комплектуються будь-які складові автомобіля.
Ієрархічна модель бази даних застосовується досі. Ієрархічна СУБД здатна оптимізувати зберігання даних у тому, що стосується деяких окремих питань, наприклад можна легко визначити, в якому автомобілі використовується якась конкретна деталь.
Реляційна модель бази даних
Величезний стрибок у розвитку теорії систем управління базами даних стався в 1970 році, коли була опублікована доповідь Є. Ф. Кодда (E. F. Codd) «Реляційна модель для великих банків даних» (A Relational Model of Data for Large Shared Data Banks »), Див. це посилання. У цьому воістину революційній праці вводилося поняття відносин і було показано, як використовувати таблиці для уявлення фактів, які встановлюють відносини з об'єктами «реального світу» і, отже, зберігають дані про них.
На той час вже стало очевидно, що ефективність, досягнення якої спочатку було основним при проектуванні бази, не така важлива, як цілісність даних. Реляційна модель надає набагато більше значення цілісності даних, ніж будь-яка інша модель, що раніше застосовувалася.
Реляційну систему управління базами даних визначає набір правил. По-перше, запис таблиці зветься «кортеж», і саме цей термін використовується у частині документації на PostgreSQL. Кортеж - це впорядкована група компонентів (чи атрибутів), кожен із яких належить певному типу. Всі кортежі побудовані за одним шаблоном, у всіх однакова кількість компонентів однакових типів. Наведемо приклад набору кортежів:
("France", "FRF", 6.56) ("Belgium", "BEF", 40.1)
Кожен із цих кортежів складається з трьох атрибутів: назви країни (рядковий тип), валюти (рядковий тип) та валютного курсу (тип із плаваючою точкою). У реляційній базі даних всі записи, що додаються до цієї множини (або таблицю), повинні слідувати цій же формі, тому записи, представлені нижче, не можуть бути додані:
Понад те, у жодній таблиці може бути повторення кортежів. Тобто в будь-якій таблиці реляційної бази даних рядки або записи, що повторюються, не дозволені.
Такий захід може виглядати як драконівський, оскільки може здатися, що для системи, яка зберігає замовлення, розміщені клієнтами, це означає, що один клієнт не зможе замовити якийсь продукт двічі.
Кожен атрибут запису має бути «атомарним», тобто являти собою просту порцію інформації, а не інший запис або список інших аргументів. Крім того, типи відповідних атрибутів у кожному записі повинні збігатися, як було показано вище. Технічно це означає, що вони повинні бути отримані з одного набору значень або домену. Практично всі вони повинні бути або рядками, або цілими числами, або числами з плаваючою точкою, або ж належати якомусь іншому типу, що підтримується СУБД.
Атрибут, яким відрізняють записи, у всьому іншому ідентичні, називається ключем. У деяких випадках як ключ може виступати комбінація з декількох атрибутів.
Атрибут (або атрибути), призначений для того, щоб відрізнити деякий запис таблиці від інших записів цієї таблиці (або, іншими словами, зробити запис унікальним), називається первинним ключем. У реляційної базі даних кожне відношення (таблиця) повинно мати первинний ключ, тобто щось, що робило б кожний запис відмінним від інших у цій таблиці.
Останнє правило, що визначає структуру реляційної бази даних, - це цілісність посилань. Така вимога пояснюється тим, що будь-якої миті часу всі записи бази даних повинні мати сенс. Розробник додатка, що взаємодіє з базою даних, повинен бути уважним, він повинен переконатися, що його код не порушує цілісності бази. Уявіть, що відбувається при видаленні клієнта. Якщо клієнт видаляється з відношення CUSTOMER, необхідно видалити всі його замовлення з таблиці ORDERS. В іншому випадку залишаться записи про замовлення, яким не можна порівняти клієнт.
У наступних моїх блогах буде представлена докладніша теоретична та практична інформація про реляційні бази даних. Поки що ж запам'ятайте, що реляційна модель побудована таких математичних поняттях, як множини і відносини, і що з створенні систем слід дотримуватися певних правил.
Мови запитів SQL та інші
Реляційні системи управління базами даних, звичайно ж, надають способи додавання та оновлення даних, але це не головне, сила таких систем полягає в тому, що вони надають користувачеві можливість ставити питання про збережені дані спеціальною мовою запитів. На відміну від більш ранніх баз даних, які спеціально проектувалися так, щоб відповідати на певні типи питань, що стосуються інформації, що містяться в них, реляційні бази даних є набагато більш гнучкими і відповідають на питання, які ще не були відомі при створенні бази.
Запропонована Коддом реляційна модель використовує той факт, що відносини визначають множини, а множини можна обробляти математично. Кодд припустив, що у запитах міг би застосовуватися такий розділ теоретичної логіки, як обчислення предикатів, на його основі та побудовані мови запитів. Такий підхід забезпечує безпрецедентну продуктивність пошуку та вибірки множин даних.
Одним із перших був реалізований мову запитів QUEL, він використовувався у створеній наприкінці 1970-х років базі даних Ingres. Ще один мову запитів, у якому застосовувався інший метод, називався QBE (Query By Example - запит на приклад). Приблизно в той же час група, що працює в дослідному центрі IBM, розробила мову структурованих запитів SQL (Structured Query Language), ця назва зазвичай вимовляється як "Сіквел".
SQL- це стандартна мова запитівНайбільш поширеним його визначенням є стандарт ISO/IEC 9075:1992, «Information Technology - Database Languages - SQL» (або, простіше кажучи, SQL92) та його американський аналог ANSI X3.135-1992, який відрізняється від першого лише кількома сторінками обкладинки. Ці стандарти замінили раніше існуючий SQL89. Насправді є і пізніший стандарт, SQL99, але він ще не отримав поширення, до того ж більшість оновлень не торкається ядра мови SQL.
Існують три рівні відповідності SQL92: Entry SQL, Intermediate SQL та Full SQL. Найпоширенішим є рівень "Entry", і PostgreSQL дуже близький до такої відповідності, хоча є й невеликі відмінності. Розробники займаються виправленням незначних упущень, і з кожною новою версією PostgreSQL стає дедалі ближче до стандарту.
У мові SQL три типи команд:
- Data Manipulation Language (DML)- мова маніпулювання даними. Це частина SQL, яка використовується в 90% випадків. Вона складається з команд додавання, видалення, оновлення та, що найважливіше, вибірки даних з бази даних.
- Data Definition Language (DDL)- Мова визначення даних. Це команди для створення таблиць та управління іншими аспектами бази даних, структурованими на вищому рівні, ніж дані, що відносяться до них.
- Data Control Language (DCL)- мова управління даними
Це набір команд, які контролюють права доступу до даних. Багато користувачів баз даних ніколи не застосовують такі команди, оскільки працюють у великих компаніях, де є спеціальний адміністратор бази даних (або навіть кілька), який займається управлінням базою даних, до його функцій входить і контроль за правами доступу.
SQL
SQL практично повсюдно визнаний стандартною мовою запитів і, як згадувалося, описаний у багатьох міжнародних стандартах. У наші дні майже кожна СУБД тією чи іншою мірою підтримує SQL. Це сприяє уніфікації, тому що додаток, написаний із застосуванням SQL як інтерфейс до бази даних, може бути перенесено і використовуватися на іншій базі, при цьому вартість такого перенесення в термінах витраченого часу і зусиль, що додаються, буде невелика.
Однак під тиском ринку виробники баз даних змушені створювати продукти, що відрізняються один від одного. Так з'явилося кілька діалектів SQL, чому сприяло і те, що в стандарті, що описує мову, не визначено команди для багатьох завдань адміністрування бази даних, які є необхідною і дуже важливою складовою при використанні бази в реальному світі. Тому існують різницю між діалектами SQL, прийнятими (наприклад) в Oracle, SQL Server і PostgreSQL.
SQL буде описуватися протягом усієї книги, поки наведемо кілька прикладів, щоб показати, на що ця мова схожа. Виявляється, щоб почати працювати з SQL, не обов'язково вивчати його формальні правила.
Створимо за допомогою SQL нову таблицю у базі даних. У цьому прикладі створюється таблиця для товарів, що пропонуються на продаж, які увійдуть на замовлення:
CREATE TABLE item (item_id serial, description char(64) not null, cost_price numeric(7,2), sell_price numeric(7,2));
Тут ми визначили, що таблиці необхідний ідентифікатор, який діяв як первинний ключ, і він повинен автоматично генеруватися системою управління базою даних. Ідентифікатор має тип serial, а це означає, що кожного разу при додаванні нового елемента item у послідовності буде створено новий, унікальний item_id. Опис (description) – це текстовий атрибут, що складається із 64 символів. Собівартість (cost_price) та ціна продажу (sell_price) визначаються як числа з плаваючою точкою, з двома знаками після коми.
Тепер використовуємо SQL для заповнення щойно створеної таблиці. У цьому немає нічого складного:
INSERT INTO item(description, cost_price, sell_price) values("Fan Small", 9.23, 15.75); INSERT INTO item(description, cost_price, sell_price) values("Fan Large", 13.36, 19.95); INSERT INTO item(description, cost_price, sell_price) values("Toothbrush", 0.75, 1.45);
Основа SQL - це оператор SELECT. Він застосовується для створення результуючих множин - груп записів (або атрибутів записів), які відповідають певному критерію. Ці критерії можуть бути складними. Результуючі множини можуть використовуватися як цільові об'єкти для змін, що здійснюються оператором UPDATE, або видалень, що виконуються DELETE.
Ось кілька прикладів використання оператора SELECT:
SELECT * FROM customer, orderinfo WHERE orderinfo.customer_id = customer.customer_id GROUP BY customer_id SELECT customer.title, customer.fname, customer.lname, COUNT(orderinfo.orderinfo_id) AS "Number of orders" = orderinfo.customer_id GROUP BY customer.title, customer.fname, customer.lname
Ці оператори SELECT перераховують усі замовлення клієнтів у зазначеному порядку та підраховують кількість замовлень, зроблених кожним клієнтом.
Наприклад, база даних PostgreSQL надає кілька способів доступу до даних, зокрема:
- Використовувати консольну програму для виконання операторів SQL
- Безпосередньо вбудувати SQL у програму
- Використовувати виклики функцій API (Application Programming Interfaces, інтерфейсів прикладного програмування) для підготовки та виконання операторів SQL, перегляду результуючих множин та оновлення даних з різних мов програмування
- Вжити опосередкованого доступу до даних бази PostgreSQL із застосуванням драйвера ODBC (Open Database Connection - відкритого інтерфейсу доступу до баз даних) або JDBC (Java Database Connectivity - інтерфейсу доступу Java-додатків до баз даних) або стандартної бібліотеки, такої як DBI для мови Perl
Системи управління базами даних
СУБД, як говорилося раніше, - це набір програм, уможливлюють побудова баз даних та його використання. До обов'язків СУБД входить:
- Створення бази даних.Деякі системи керують одним великим файлом і створюють одну або кілька баз даних усередині нього, інші можуть задіяти кілька файлів операційної системи або безпосередньо реалізовувати низькорівневий доступ до розділів диска. Користувачі та розробники не повинні дбати про низькорівневу структуру таких файлів, тому що весь необхідний доступ забезпечує СУБД.
- Надання засобів для виконання запитів та оновлень.СУБД повинна забезпечувати можливість запиту даних, що задовольняють деякому критерію, наприклад, можливість вибору всіх замовлень, зроблених деяким клієнтом, але ще не доставлених. Перш ніж SQL отримав широке поширення як стандартного мови, способи вираження таких запитів змінювалися від системи до системи.
- Багатозадачність.Якщо з базою даних працюють кілька додатків або до неї одночасно здійснюють доступ до декількох користувачів, то СУБД повинна гарантувати, що обробка запиту кожного користувача не впливає на роботу інших. Тобто користувачам доводиться чекати тільки якщо хтось інший записує дані саме тоді, коли їм потрібно прочитати (або записати) дані в якийсь елемент. Одночасно може відбуватися кілька зчитувань даних. На перевірку виявляється, що різні бази даних підтримують різні рівні багатозадачності і що ці рівні можуть бути навіть настроюваними.
- Веде журнал.СУБД має вести журнал всіх змін даних за певний період. Він може використовуватися для відстеження помилок, а також (можливо, це навіть важливіше) для відновлення даних у разі збою системи, наприклад, позапланового вимкнення живлення. Зазвичай виконується резервне копіювання даних і ведеться журнал транзакцій, тому що резервна копія може бути корисною для відновлення бази даних у разі пошкодження диска.
- Забезпечення безпеки бази даних.СУБД повинна забезпечувати контроль за доступом, щоб тільки зареєстровані користувачі могли маніпулювати даними, що зберігаються в базі, і структурою бази даних (атрибутами, таблицями та індексами). Зазвичай для кожної бази визначається ієрархія користувачів, на чолі цієї структури стоїть «суперкористувач», який може змінювати все, що завгодно, далі йдуть користувачі, які можуть додавати та видаляти дані, а в самому низу знаходяться ті, хто має право тільки на читання. СУБД повинен мати засоби, що дозволяють додавати та видаляти користувачів, а також вказувати, до яких можливостей бази даних вони можуть отримати доступ.
- Підтримка цілісності посилань.Багато СУБД мають властивості, що сприяють підтримці цілісності посилання, тобто коректності даних. Зазвичай якщо запит або оновлення порушує правила реляційної моделі, СУБД видає повідомлення про помилку.
|
|
У попередніх двох статтях цього циклу, опублікованих у № 6 та 7 нашого журналу, ми розглянули різні механізми доступу до даних, включаючи ADO, BDE та їх альтернативи. Тепер ми знаємо, як вибрати технологію доступу до даних для тієї чи іншої пари «СУБД – засіб розробки».
Маючи в своєму розпорядженні технологію доступу до даних, можна нарешті подумати і про те, яким чином слід маніпулювати самими даними та метаданими. Способи маніпуляції можуть бути специфічними для СУБД (наприклад, використання об'єктів клієнтської частини цієї СУБД для доступу до об'єктів баз даних) або для даного механізму доступу до даних. Тим не менш, існує більш-менш універсальний спосіб маніпуляції даними, підтримуваний майже всіма серверними реляційними СУБД і більшістю універсальних механізмів доступу до даних (у тому числі при використанні їх спільно з настільними СУБД). Цей спосіб – застосування мови SQL (Structured Query Language – мова структурованих запитів). Нижче ми розглянемо призначення та особливості цієї мови, а також вивчимо, як з його допомогою вилучати та підсумовувати дані, додавати, видаляти та модифікувати записи, захищати дані від несанкціонованого доступу, створювати бази даних. Для докладнішого вивчення SQL ми можемо порекомендувати книги Мартіна Грабера «Вступ до SQL» (М., Лорі, 1996) і «SQL. Довідкове керівництво» (М., Лорі, 1997).
Вступ
Structured Query Language є непроцедурною мовою, що використовується для управління даними реляційних СУБД. Термін "непроцедурний" означає, що цією мовою можна сформулювати, що потрібно зробити з даними, але не можна проінструктувати, як саме це слід зробити. Іншими словами, у цій мові відсутні алгоритмічні конструкції, такі як мітки, оператори циклу, умовні переходи та ін.
Мова SQL була створена на початку 70-х років у результаті дослідницького проекту IBM, метою якого було створення мови маніпуляції реляційними даними. Спочатку він називався SEQUEL (Structured English Query Language), потім – SEQUEL/2, а потім – просто SQL. Офіційний стандарт SQL був опублікований ANSI (American National Standards Institute - Національний інститут стандартизації, США) у 1986 році (це найчастіше реалізація SQL, що нині використовується). Цей стандарт було розширено в 1989 і 1992 роках, тому останній стандарт SQL називається SQL92. В даний час ведеться робота над стандартом SQL3, що містить деякі об'єктно-орієнтовані розширення.
Існує три рівні відповідності стандарту ANSI – початковий, проміжний та повний. Багато виробників серверних СУБД, такі як IBM, Informix, Microsoft, Oracle і Sybase, застосовують власні реалізації SQL, засновані на стандарті ANSI (що відповідають як мінімум початковому рівню відповідності стандарту) і містять деякі розширення, специфічні для цієї СУБД.
Більш детальну інформацію про відповідність стандарту версії SQL, яка використовується в конкретній СУБД, можна знайти в документації, що постачається з цією СУБД.
Як працює SQL
Давайте розглянемо, як працює SQL. Припустимо, що є база даних, керована з допомогою будь-якої СУБД. Для отримання даних використовується запит, сформульований на мові SQL. СУБД обробляє цей запит, витягує запитані дані та повертає їх. Цей процес схематично зображено на рис. 1 .
Як ми побачимо пізніше, SQL дозволяє не тільки вилучати дані, але й визначати структуру даних, додавати та видаляти дані, обмежувати або надавати доступ до даних, підтримувати цілісність посилань.
SQL сам по собі не є ні СУБД, ні окремим продуктом. Це мова, застосовуваний взаємодії з СУБД і що у певному сенсі її невід'ємною частиною.
Data Definition Language (DDL)
Data Definition Language містить оператори, що дозволяють створювати, змінювати та знищувати бази даних та об'єкти всередині них (таблиці, уявлення та ін.). Ці оператори перераховані у табл. 1.
Таблиця 1
|
Оператор |
Опис |
|---|---|
|
Застосовується для додавання нової таблиці до бази даних |
|
|
Застосовується для видалення таблиці з бази даних |
|
|
Застосовується для зміни структури наявної таблиці |
|
|
Застосовується для додавання нового уявлення до бази даних |
|
|
Застосовується для видалення подання з бази даних |
|
|
Застосовується для створення індексу для даного поля |
|
|
Застосовується для видалення існуючого індексу |
|
|
Застосовується для створення нової схеми у базі даних |
|
|
Застосовується для видалення схеми з бази даних |
|
|
Застосовується для створення нового домену |
|
|
Застосовується для перевизначення домену |
|
|
Застосовується для видалення домену з бази даних |
Data Manipulation Language (DML)
Data Manipulation Language містить оператори, що дозволяють вибирати, додавати, видаляти та модифікувати дані. Зверніть увагу, що ці оператори не зобов'язані завершувати транзакцію, всередині якої вони викликані. Оператори DML представлені у табл. 2.
Таблиця 2
Іноді оператор SELECT відносять до окремої категорії, яка називається Data Query Language (DQL).
Cursor Control Language (CCL)
Оператори Cursor Control Language використовуються для визначення курсору, підготовки SQL-пропозицій для виконання, а також деяких інших операторів. Оператори CCL представлені у табл. 5.
Таблиця 5
|
Оператор |
Опис |
|---|---|
|
Застосовується для визначення курсору для запиту |
|
|
Використовується для опису плану запиту. Цей оператор є розширенням SQL для Microsoft SQL Server 7.0. Він повинен виконуватися за іншими СУБД. Наприклад, у випадку Oracle слід використовувати оператор EXPLAIN PLAN |
|
|
Застосовується для відкриття курсору при отриманні результатів запиту |
|
|
Застосовується для отримання рядка результатів запиту |
|
|
Застосовується для закриття курсору |
|
|
Застосовується для підготовки оператора SQL до виконання |
|
|
Застосовується для виконання оператора SQL |
|
|
Застосовується для опису підготовленого запиту |
Усі оператори SQL мають вигляд, показаний на рис. 2 .
Кожен оператор SQL починається з дієслова, що є ключовим словом, що визначає, що саме робить цей оператор (SELECT, INSERT, DELETE ...). В операторі містяться також пропозиції, що містять відомості про те, над якими даними здійснюються операції. Кожна пропозиція починається з ключового слова, такого як FROM, WHERE та ін. Структура речення залежить від його типу - ряд речень містить імена таблиць або полів, деякі можуть містити додаткові ключові слова, константи або вирази.
За допомогою чого можна виконати SQL-оператори
Всі сучасні серверні СУБД (а також багато популярних настільних СУБД) містять у своєму складі утиліти, що дозволяють виконати SQL-пропозицію та ознайомитися з її результатом. Зокрема, клієнтська частина Oracle містить у своєму складі утиліту SQL Plus, а Microsoft SQL Server – утиліту SQL Query Analyzer. Саме цією утилітою ми скористаємося для демонстрації можливостей SQL, а як базу даних, над якою ми будемо «експериментувати», візьмемо базу даних NorthWind, що входить до комплекту постачання Microsoft SQL Server 7.0. В принципі, можна використовувати іншу базу даних та будь-яку іншу утиліту, здатну виконувати в цій базі даних SQL-пропозиції та відображати результати (або навіть написати свою, використовуючи будь-який засіб розробки – Visual Basic, Delphi, C++Builder та ін.). ). Однак про всяк випадок рекомендується зробити резервну копію цієї бази даних.
SQL часто називають мовою есперанто для систем управління базами даних (СУБД). Справді, у світі немає іншої мови для роботи з базами даних (БД), яка настільки широко використовувалася б у програмах. Перший стандарт sol з'явився в 1986 р. і на сьогодні завоював загальне визнання. Його можна використовувати навіть при роботі з нереляційними СУБД. На відміну від інших програмних засобів, таких, як мови Сі та Кобол, що є прерогативою програмістів-професіоналів, sql застосовується фахівцями з різних областей. Програмісти, адміністратори СУБД, бізнес-аналітики всі вони з успіхом обробляють дані за допомогою sql. Знання цієї мови корисне всім, кому доводиться мати справу із БД.
У статті ми розглянемо основні поняття sql. Розповімо його передісторію (і розвіємо попутно кілька міфів). Ви познайомитеся з реляційною моделлю і зможете набути перших навичок роботи з sql, що допоможе в подальшому освоєнні мови.
Чи важко вивчити SQL? Це залежить від того, наскільки глибоко ви збираєтеся вникати у суть. Для того, щоб стати професіоналом, доведеться вивчити дуже багато. Мова sql виникла в 1974 р. як предмет маленької дослідницької роботи, що складалася з 23 сторінок, і з того часу пройшов довгий шлях розвитку. Текст чинного нині стандарту - офіційного документа "the international standard database language sql" (зазвичай званого sql-92) - містить понад шість сотень сторінок, проте в ньому нічого не йдеться про конкретні особливості версій sol, реалізовані в СУБД фірм microsoft, oracle, sybase та ін. Мова настільки розвинений і різноманітний, що лише просте перерахування його можливостей вимагатиме кількох журнальних статей, а якщо зібрати все, що написано на тему sol, то вийде багатотомна бібліотека.
Однак для звичайного користувача зовсім не обов'язково знати SQL повністю і повністю. Як туристу, який опинився в країні, де розмовляють незрозумілою мовою, достатньо вивчити лише кілька вживаних виразів і правил граматики, так і в SQL - знаючи небагато, можна отримувати безліч корисних результатів. У цій статті ми розглянемо основні команди SQL, правила завдання критеріїв для відбору даних та покажемо, як отримувати результати. У результаті ви зможете самостійно створювати таблиці та вводити в них інформацію, складати запити та працювати зі звітами. Ці знання можуть стати базою для подальшого самостійного освоєння SQL.
Що таке SQL?
sql - це спеціалізована непроцедурна мова, що дозволяє описувати дані, здійснювати вибірку та обробку інформації з реляційних СУБД. Спеціалізованість означає, що sol призначений лише для роботи з БД; не можна створити повноцінну прикладну систему лише засобами цієї мови – для цього потрібно використовувати інші мови, в які можна вбудовувати sql-команди. Тому SQL ще називають допоміжним мовним засобом для обробки даних. Допоміжна мова використовується лише у комплексі з іншими мовами.
У прикладній мові загального призначення зазвичай є засоби створення процедур, а sql їх немає. З його допомогою не можна вказати, яким чином має виконуватись деяке завдання, а можна лише визначити, у чому саме воно полягає. Інакше кажучи, під час роботи з sql нас цікавлять результати, а чи не процедури їхнього отримання.
Найбільш істотною властивістю SQL є можливість доступу до реляційних БД. Багато хто навіть вважає, що вирази "БД, що обробляється засобами sql" та "реляційна БД" - синоніми. Однак незабаром ви переконаєтеся, що між ними є різниця. У стандарті sql-92 навіть немає терміну відношення (relation).
Що таке реляційна СУБД?
Якщо не вдаватися до подробиць, то реляційна СУБД — це система, заснована на реляційній моделі управління даними.
Поняття реляційної моделі було вперше запропоновано в роботі д-ра Е. Ф. Кодда, опублікованої в 1970 р. У ній було описано математичний апарат для структуризації даних та управління ними, а також запропоновано абстрактну модель для представлення будь-якої реальної інформації. Доти з використанням БД потрібно враховувати конкретні особливості зберігання у ній інформації. Якщо внутрішня структура БД змінювалася (наприклад, з метою підвищення швидкодії), доводилося переробляти прикладні програми, навіть якщо логічно жодних змін не відбувалося. Реляційна модель дозволила відокремити приватні особливості зберігання даних рівня прикладної програми. Насправді модель ніяк не визначає способи зберігання інформації та доступу до неї. Враховується лише те, як ця інформація сприймається користувачем. Завдяки появі реляційної моделі якісно змінився підхід до управління даними: з мистецтва воно перетворилося на науку, що призвело до революційного розвитку галузі.
Основні поняття реляційної моделі
Згідно з реляційною моделлю, відношення (relation) - це деяка таблиця з даними. Ставлення може мати один або кілька атрибутів (ознак), відповідних стовпцям цієї таблиці, і деяке безліч (можливо, порожнє) даних, що є наборами цих атрибутів (їх називають n-арними кортежами, або записами) і відповідних рядків таблиці.
Для будь-якого кортежу значення атрибутів мають належати так званим доменам. Фактично доменом є деякий набір даних, який визначає безліч всіх допустимих значень.
Давайте розглянемо приклад. Нехай є домен Дні Тижня, що містить значення від Понеділок до Неділі. Якщо відношення має атрибут День Тижня, що відповідає цьому домену, то в будь-якому кортежі відносини в стовпці День Тижня має бути одне з перерахованих значень. Поява значень Січень чи Кішка не допускається.
Зверніть увагу: атрибут обов'язково повинен мати одне із допустимих значень. Завдання одразу кількох значень заборонено. Таким чином, крім вимог належності значень атрибуту деякому домену, повинна дотримуватися умова його атомарності. Це означає, що з цих значень неприпустима декомпозиція, т. е. не можна розбити в більш дрібні частини, не втративши основного сенсу. Наприклад, якби значення атрибута одночасно містило Понеділок та Вівторок, то можна було б виділити дві частини, зберігши первісний сенс — День Тижня; отже, це значення атрибута перестав бути атомарным. Однак, якщо спробувати розбити значення "Понеділок" на частини, то вийде набір з окремих літер - від "П" до "К"; вихідний сенс втрачено, тому значення "Понеділок" є атомарним.
Відносини мають інші властивості. Найбільш значуще їх — математичне властивість замкнутості операцій. Це означає, що в результаті будь-якої операції над ставленням має з'являтися нове ставлення. Ця властивість дозволяє у виконанні математичних операцій над відносинами отримувати передбачувані результати. Крім того, з'являється можливість представляти операції у вигляді абстрактних виразів із різними рівнями вкладеності.
У своїй вихідній роботі д-р Кодд визначив набір із восьми операторів, який отримав назву реляційної алгебри. Чотири оператори - об'єднання, логічне множення, різницю і Декартів твір - були перенесені з традиційної теорії множин; Інші оператори були створені спеціально для обробки відносин. У подальших роботах д-ра Кодда, Кріса Дейта та інших дослідників було запропоновано додаткових операторів. Далі в цій статті будуть розглянуті три реляційні оператори — продукція (project), обмеження (select, або restrict) та злиття (join).
sql та реляційна модель
Тепер, коли ви познайомилися з реляційною моделлю, забудемо про неї. Звичайно, не назавжди, а лише для того, щоб пояснити наступне: хоча саме запропонована д-ром Коддом реляційна модель була використана при розробці SQL, між ними немає повної або буквальної відповідності (це одна з причин, чому в стандарті SQL-92 відсутня термін ставлення). Наприклад, поняття таблиця sql і ставлення є рівнозначними, оскільки у таблицях може бути відразу кілька однакових рядків, тоді як у відносинах поява ідентичних кортежів не дозволено. До того ж у sql не передбачено використання реляційних доменів, хоча певною мірою їхню роль грають типи даних (деякі впливові прихильники реляційної моделі роблять зараз спробу домогтися включення в майбутній стандарт sql реляційних доменів).
На жаль, невідповідність між sql та реляційною моделлю породила безліч непорозумінь та суперечок за минулі роки. Але оскільки основна тема статті – вивчення SQL, а не реляційної моделі, ці проблеми тут не розглядаються. Просто слід запам'ятати, що між термінами, що застосовуються в SQL і в реляційній моделі, є відмінності. Далі у статті використовуватимуться лише терміни, прийняті в sql. Замість відносин, атрибутів та кортежів будемо застосовувати їх SQL-аналоги: таблиці, стовпці та рядки.
Статичний та динамічний sql
Можливо, вам вже знайомі такі терміни як статичний і динамічний sql. sql-запит є статичним, якщо він компілюється та оптимізується на стадії, що передує виконанню програми. Ми вже згадували одну з форм статичного sql, коли говорили про вбудовування sql-команд у програми на Сі або Кобол (для таких виразів існує ще інша назва - вбудований sql). Як ви, напевно, здогадуєтеся, динамічний sql-запит компілюється та оптимізується під час виконання програми. Як правило, звичайні користувачі застосовують саме динамічний sql, що дозволяє створювати запити відповідно до нагальних потреб. Один з варіантів використання динамічних sql-запитів - їх інтерактивний або безпосередній виклик (існує навіть спеціальний термін - directsql), коли запити, що відправляються на обробку, вводяться в інтерактивному режимі з терміналу. Між статичним і динамічним sql є певні відмінності в синтаксисі конструкцій, що застосовуються, і особливостях виконання, проте ці питання виходять за рамки статті. Зазначимо лише, що з ясності розуміння приклади даються у вигляді direct sql-запросов, оскільки це дозволяє навчитися використовувати sql як програмістам, а й більшості кінцевих користувачів.
Як вивчати sql
Тепер ви готові до написання перших sql-запитів. Якщо у вас є доступ до БД через sql і ви захочете скористатися нашими прикладами на практиці, то врахуйте наступне: ви повинні входити в систему як користувач з необмеженими повноваженнями і вам потрібні програмні засоби інтерактивної обробки sql-запитів (якщо йдеться про мережевий БД, слід переговорити з адміністратором БД про надання відповідних прав). Якщо доступу до БД через SQL немає - не засмучуйтеся: всі приклади дуже прості і в них можна розібратися "всуху", без виходу на машину.
Щоб виконати будь-які дії в sql, слід виконати вираз мовою sql. Зустрічається кілька типів виразів, проте серед них можна виділити три основні групи: ddl-команди (data definition language – мова опису даних), dml-команди (data manipulation language – мова маніпуляцій з даними) та засоби контролю за даними. Таким чином, в SQL в якомусь сенсі об'єднані три різні мови.
Команди мови опису даних
Почнемо з однієї з основних ddl-команд – create table (Створити таблицю). У SQL бувають таблиці декількох типів, основними є два типи: базові (base) і вибіркові (views). Базовими є таблиці, які стосуються реально існуючим даним; вибіркові - це "віртуальні" таблиці, що створюються на основі інформації, що отримується з базових таблиць; але для користувачів форми виглядають як стандартні таблиці. Команда create table призначена для створення базових таблиць.
У команді create table слід задати назву таблиці, вказати список стовпців і типи даних, що містяться в них. Як параметри можуть бути також інші необов'язкові елементи, проте спочатку давайте розглянемо лише основні параметри. Покажемо найпростішу синтаксичну форму для цієї команди:
create table Ім'яТаблиці (Стовпчик ТипДаних) ;
create та table – це ключові слова sql; Ім'яТаблиці, Стовпець і ТипДаних - це формальні параметри, замість яких користувач щоразу вводить фактичні значення. Параметри Стовпець та ТипДаних укладені у круглі дужки. У SQL круглі дужки зазвичай використовуються для групування окремих елементів. У разі вони дозволяють об'єднати визначення для стовпця. Знак "точка з комою", що стоїть в кінці, є роздільником команд. Він повинен завершувати будь-який вираз мовою sql.
Розглянемо приклад. Нехай необхідно створити таблицю для зберігання даних про всі зустрічі (appointments). Для цього в SQL слід ввести команду:
create table appointments (appointment_date date);
Після виконання цієї команди буде створено таблицю з ім'ям appointments, де є один стовпець appointment_date, у якому можуть записуватися дані типу date. Оскільки на даний момент дані ще не вводилися, кількість рядків у таблиці дорівнює нулю (за допомогою команди create table тільки дається визначення таблиці; реальні значення вводяться командою insert, що розглядається далі).
Параметри appointments і appointment_date називаються ідентифікаторами, оскільки задають імена для конкретних об'єктів БД, у разі — імена для таблиці і стовпця відповідно. У SQL зустрічаються ідентифікатори двох типів: звичайні (regular) і виділені (delimited). Виділені ідентифікатори полягають у подвійні лапки, і в них враховується регістр символів, що використовуються. Звичайні ідентифікатори не виділяються жодними обмеженими символами, у тому написанні регістр не враховується. У статті застосовуються лише звичайні ідентифікатори.
Символи, які використовуються для побудови ідентифікаторів, повинні відповідати певним правилам. У звичайних ідентифікаторах можуть використовуватися лише літери (не обов'язково латинські, а й інших алфавітів), цифри та символ підкреслення. Ідентифікатор не повинен містити знаків пунктуації, прогалин або спеціальних символів (#, @, % або!); крім того, він не може починатися з цифри або символу підкреслення. Для ідентифікаторів можна використовувати окремі ключові слова SQL, але робити це не рекомендується. Ідентифікатор призначений для позначення деякого об'єкта, тому має бути унікальне (у межах певного контексту) ім'я: не можна створити таблицю з ім'ям, яке вже зустрічається в БД; в одній таблиці не можна мати стовпчики з однаковими іменами. До речі, майте на увазі, що appointments та appointments – це однакові імена для sql. Однією лише зміною регістру літер створити новий ідентифікатор не можна.
Хоча таблиця може мати лише один стовпець, практично зазвичай потрібні таблиці з кількома стовпцями. Команда для створення такої таблиці у вигляді виглядає так:
create table Ім'яТаблиці (Стовпець ТипДаних [ ( , Стовпець ТипДаних ] )) ;
Квадратні дужки використані для позначення необов'язкових елементів, фігурні містять елементи, які можуть бути переліком одноколійних конструкцій (при введенні реальної sql-команди ні ті ні інші дужки не ставляться). Такий синтаксис дозволяє задати будь-яку кількість стовпців. Зверніть увагу, що перед другим елементом стоїть кома. Якщо у списку є кілька параметрів, то вони відокремлюються один від одного комами.
create table appointments2 (appointment_date date , appointment_time time , description varchar (256)) ;
Ця команда створює таблицю appointments2 (нова таблиця повинна мати інше ім'я, оскільки таблиця appointments вже є у БД). Як і першій таблиці, у ній є стовпець appointment_date для запису дати зустрічей; крім того, з'явився стовпець appointment_time для запису цих зустрічей. Параметр description (опис) є текстовим рядком, де може бути до 256 символів. Для цього параметра вказано тип varchar (скорочення від character varying), оскільки заздалегідь не відомо, скільки місця знадобиться для запису, але зрозуміло, що опис займе трохи більше 256 символів. При описі параметра типу символьний рядок (і деяких інших типів) вказується довжина параметра. Її значення задається у круглих дужках праворуч від назви типу.
Можливо, ви звернули увагу, що у двох розглянутих прикладах запис команди оформлено по-різному. Якщо в першому випадку команда повністю розміщена в одному рядку, то в другому після першої відкритої круглої дужки запис продовжено з нового рядка, і визначення кожного наступного стовпця починається з нового рядка. У SQL немає спеціальних вимог до оформлення запису. Розбиття запису на рядки робить його читання зручнішим. Мова sql дозволяє при написанні команд не лише розбивати команду по рядках, але й вставляти відступи на початку рядків та пробіли між елементами запису.
Тепер, коли ви знаєте основні правила, розглянемо складніший приклад створення таблиці з декількома стовпцями. На початку статті було показано таблицю employees (Співробітники). У ній містяться такі стовпці: прізвище, ім'я, дата прийому на роботу, підрозділ, категорія та зарплата за рік. Для визначення цієї таблиці використовується така команда SQL:
create table employees (last_name character (13) not null, first_name character (10) not null, hire_date date , branch_office character (15) , grade_level smallint , salary decimal (9 , 2) ;
У команді зустрічається кілька нових елементів. Насамперед, це вираз not null, що стоїть наприкінці визначення стовпців last_name та first_name. За допомогою подібних конструкцій задаються вимоги, що підлягають обов'язковому дотриманню. У цьому випадку зазначено, що поля last_name та first_name повинні обов'язково заповнюватися при введенні; залишати ці стовпці порожніми не можна (це логічно: як можна ідентифікувати співробітника, не знаючи його імені?).
Крім того, у прикладі присутні три нових типи даних: character, smallint та decimal. Досі ми майже не говорили про типи. Хоча в SQL немає реляційних доменів, однак є набір основних типів даних. Ця інформація використовується при виділенні пам'яті та порівнянні величин; певною мірою звужує список можливих значень при введенні, проте контроль типів у SQL менш суворий, ніж в інших мовах.
Усі наявні в SQL типи даних можна розбити на шість груп: символьні рядки, точні числові значення, наближені числові значення, бітові рядки, датовчас і інтервали. Ми перерахували всі різновиди, однак у цій статті докладно розглядатимуться лише окремі з них (бітові рядки, наприклад, не становлять особливого інтересу для звичайних користувачів).
До речі, якщо ви подумали, що дати час — це помилка, то помилилися. До цієї групи (datetime) відноситься більшість використовуваних в SQL типів даних, пов'язаних з часом (такі параметри, як тимчасові інтервали, виділені в окрему групу). У попередньому прикладі вже зустрічалися два типи даних із групи дати часу - date і time.
Наступний тип даних, з яким ви вже знайомі - character varying (або просто varchar); він належить до групи символьних рядків. Якщо varchar служить для зберігання рядків змінної довжини, то тип char, що зустрівся в третьому прикладі, призначений для запису рядків, що мають фіксоване число символів. Наприклад, у стовпці last_name будуть записуватися рядки з 13 символів незалежно від реально введених прізвищ, будь то poe або penworth-chickering (у випадку з poe 10 символів, що залишилися, заповняються пробілами).
З погляду користувача, varchar та char мають однаковий сенс. Навіщо треба було вводити два типи? Справа в тому, що на практиці зазвичай доводиться шукати компроміс між швидкодією та економією простору на диску. Як правило, застосування рядків з фіксованою довжиною дає деякий виграш у швидкості доступу, проте при надто великій довжині рядків простір на диску витрачається неекономно. Якщо appointments2 для кожного рядка коментаря резервувати по 256 символів, це може бути нераціонально; найчастіше рядки будуть значно коротшими. З іншого боку, прізвища мають різну довжину, але для них, як правило, потрібно близько 13 символів; у разі втрати будуть мінімальними. Існує хороше правило: якщо відомо, що довжина рядка змінюється незначно або порівняно невелика, то використовуйте char; в інших випадках – varchar.
Наступні два нових типи даних – smallint та decimal – відносяться до групи точних числових значень. smallint – це скорочена назва від small integer (мале ціле). У SQL також передбачений тип даних integer. Наявність двох подібних типів у разі пояснюється міркуванням економії простору. У прикладі значення параметра grade_level можуть бути представлені за допомогою двозначного числа, тому використаний тип smallint; однак на практиці не завжди відомо, які максимальні значення можуть бути параметрами. Якщо такої інформації немає, застосовуйте integer. Реальний обсяг, що виділяється для зберігання параметрів типу smallint і integer, і відповідний діапазон значень цих параметрів індивідуальні кожної платформи.
Тип даних decimal, який зазвичай використовується для обліку фінансових показників, дозволяє задати шаблон з необхідною кількістю десяткових знаків. Оскільки цей тип служить для точного числового запису, він гарантує точність у виконанні математичних операцій над десятковими даними. Якщо для десяткових значень використовувати типи даних із групи наближеного числового запису, наприклад float (floating point number — число з плаваючою точкою), це призведе до похибок округлення, тому для фінансових розрахунків цей варіант не підходить. Для визначення параметрів типу decimal використовується така форма запису:
де p – це число десяткових знаків, d – кількість розрядів після коми. Замість p слід записувати загальну кількість значущих цифр у значеннях, а замість d — кількість цифр після коми.
У врізці "Створення таблиці" показано повний варіант узагальненого запису команди create table. У ньому є нові елементи і показаний формат для всіх розглянутих типів даних (В принципі зустрічаються й інші типи даних, але ми їх не розглядаємо).
Спочатку може здатися, що синтаксис sql-команд занадто складний. Але ви легко в ньому розберетеся, якщо уважно вивчили наведені вище приклади. На схемі з'явився додатковий елемент – вертикальна характеристика; він служить розмежування альтернативних конструкцій. Іншими словами, при визначенні кожного стовпця потрібно вибрати відповідний тип даних (як ви пам'ятаєте, у квадратні дужки полягають необов'язкові параметри, а у фігурні дужки - конструкції, які можуть повторюватися багаторазово; у реальних sql-командах ці спеціальні символи не пишуться). У першій частині схеми наведені повні назви для типів даних, у другій їх скорочені назви; практично можна використовувати будь-які їх.
Першу частину статті завершено. Друга буде присвячена вивченню dml-команд insert, select, update та delete. Також будуть розглянуті умови вибірки даних, оператори порівняння та логічні оператори, використання null-значень та трійкова логіка.
Створення таблиці. Синтаксис команди create table: у квадратних дужках вказані необов'язкові параметри, у фігурних — конструкції, що повторюються.
create table table (column character (length) [ constraint ] | character varying (length) [ constraint ] | date [ constraint ] | time [ constraint ] | integer [ constraint ] | smallint [ constraint ] | decimal (precision, decimal places) [ constraint ] | float (precision) [ constraint ] [( , column char (length) [ constraint ] | varchar (length) [ constraint ] | date [ constraint ] | time [ constraint ] | int [ constraint ] | smallint [ constraint ] | dec (precision, decimal places) [constraint] | float (precision) [constraint])]);
Секрет назви sql
На початку 1970-х років. в ibm приступили до практичного втілення моделі реляційних БД, запропонованої доктором Коддом. Дональд Чамберлін та група інших співробітників підрозділу перспективних досліджень створили прототип мови, який отримав назву structured english query language (мова структурованих англомовних запитів), або просто sequel. Надалі він був розширений і підданий доопрацюванню. Новий варіант, запропонований ibm, отримав назву sequel/2. Його використовували як програмний інтерфейс для проектування першої реляційної системи БД фірми ibm - system/r. З міркувань, пов'язаних із правовими нюансами, в ibm вирішили змінити назву: замість sequel/2 використовувати sql (structured query language). Цю абревіатуру часто вимовляють як "сі-ку-ел".
Для отримання даних з бази даних використовується мова SQL. SQL - це мова програмування, яка дуже нагадує англійську, але призначена для програм управління базами даних. SQL використовується у кожному запиті в Access.
Розуміння принципів роботи SQL допомагає створювати точніші запити і спрощує виправлення запитів, які повертають неправильні результати.
Це стаття з циклу статей про мову SQL для Access. У ній описані основи використання SQL для вибірки даних та наведено приклади синтаксису SQL.
У цій статті
Що таке SQL?
SQL - це мова програмування, призначений для роботи з наборами фактів та відносин між ними. У програмах керування реляційними базами даних, як-от Microsoft Office Access, мова SQL використовується для роботи з даними. На відміну від багатьох мов програмування, SQL легко читаємо і зрозумілий навіть новачкам. Як і багато мов програмування, SQL є міжнародним стандартом, визнаним такими комітетами зі стандартизації, як ISO та ANSI.
На мові SQL описуються набори даних, які допомагають отримувати відповіді питання. При використанні SQL необхідно використовувати правильний синтаксис. Синтаксис – це набір правил, що дозволяють правильно поєднувати елементи мови. Синтаксис SQL заснований на синтаксисі англійської мови та має багато спільних елементів із синтаксисом мови Visual Basic для програм (VBA).
Наприклад, проста інструкція SQL, яка отримує список прізвищ контактів з ім'ям Mary, може виглядати так:
SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";
Примітка:Мова SQL використовується не тільки для виконання операцій над даними, але ще й для створення та зміни структури об'єктів бази даних, наприклад, таблиць. Частина SQL, яка використовується для створення та зміни об'єктів бази даних, називається мовою опису даних DDL. Мова DDL не розглядається у цій статті. Для отримання додаткових відомостей див . у статті Створення та зміна таблиць або індексів за допомогою запиту визначення даних .
Інструкції SELECT
Інструкція SELECT використовується для опису набору даних на мові SQL. Вона містить повний опис набору даних, які необхідно отримати з бази даних, включаючи:
таблиці, у яких містяться дані;
зв'язки між даними із різних джерел;
поля чи обчислення, на основі яких відбираються дані;
умови відбору, яким повинні відповідати дані, що включаються до результату запиту;
необхідність та спосіб сортування.
Пропозиції SQL
Інструкція SQL складається з кількох частин, які називаються пропозиціями. Кожна пропозиція в інструкції SQL має призначення. Деякі пропозиції є обов'язковими. У наведеній нижче таблиці вказані пропозиції SQL, які найчастіше використовуються.
Пропозиція SQL | Опис | Обов'язкове |
|---|---|---|
|
Визначає поля, які містять необхідні дані. |
||
|
Визначає таблиці, які містять поля, зазначені у реченні SELECT. |
||
|
Визначає умови відбору полів, яким мають відповідати всі записи, що включаються до результатів. |
||
|
Визначає порядок сортування результатів. |
||
|
В інструкції SQL, яка містить статистичні функції, визначає поля, для яких у реченні SELECT не обчислюється зведене значення. |
Тільки за наявності таких полів |
|
|
В інструкції SQL, яка містить статистичні функції, визначає умови, що застосовуються до полів, для яких у пропозиції SELECT обчислюється зведене значення. |
Терміни SQL
Кожна пропозиція SQL складається з термінів, які можна порівняти з частинами мови. У наведеній нижче таблиці наведено типи термінів SQL.
Термін SQL | Порівняна частина мови | Визначення | Приклад |
|---|---|---|---|
|
ідентифікатор |
іменник |
Ім'я, яке використовується для ідентифікації об'єкта бази даних, наприклад, ім'я поля. |
Клієнти. [Номер Телефону] |
|
оператор |
дієслово чи прислівник |
Ключове слово, яке репрезентує дію або змінює його. |
|
|
константа |
іменник |
Значення, яке не змінюється, наприклад, число або NULL. |
|
|
вираз |
прикметник |
Поєднання ідентифікаторів, операторів, констант та функцій, призначене для обчислення одного значення. |
>= Товари.[Ціна] |
Основні пропозиції SQL: SELECT, FROM та WHERE
Загальний формат інструкцій SQL:
SELECT field_1
FROM table_1
WHERE criterion_1
;
Примітки:
Access не враховує розрив рядків в інструкції SQL. Незважаючи на це, кожна пропозиція рекомендується починати з нового рядка, щоб інструкцію SQL було зручно читати як тому, хто її написав, так і всім іншим.
Кожна інструкція SELECT закінчується крапкою з комою (;). Точка з комою може стояти як наприкінці останньої пропозиції, так і на окремому рядку наприкінці інструкції SQL.
Приклад у Access
У наведеному нижче прикладі показано, як Access може виглядати інструкція SQL для простого запиту на вибірку.
1. Пропозиція SELECT
2. Пропозиція FROM
3. Пропозиція WHERE
Розберемо приклад за пропозиціями, щоб зрозуміти, як працює синтаксис SQL.
Пропозиція SELECT
SELECT, Company
Ця пропозиція SELECT. Воно містить оператор (SELECT), за яким слідують два ідентифікатори ("[Адреса електронної пошти]" та "Компанія").
Якщо ідентифікатор містить пробіли або спеціальні знаки (наприклад, "Адреса електронної пошти"), він повинен бути укладений у прямокутні дужки.
У пропозиції SELECT не потрібно вказувати таблиці, в яких містяться поля, і не можна задати умови відбору, яким повинні відповідати дані, що включаються до результатів.
В інструкції SELECT пропозиція SELECT завжди стоїть перед пропозицією FROM.
Пропозиція FROM
FROM Contacts
Це пропозиція FROM. Воно містить оператор (FROM), за яким слідує ідентифікатор (Контакти).
Пропозиція FROM не вказує поля для вибірки.
Пропозиція WHERE
WHERE City="Seattle"
Ця пропозиція WHERE. Воно містить оператор (WHERE), за яким слідує вираз (Місто = "Ростов").
За допомогою пропозицій SELECT, FROM та WHERE можна виконувати безліч дій. Для отримання додаткових відомостей про використання цих пропозицій див. наступні статті:
Сортування результатів: ORDER BY
Як і Microsoft Excel, в Access можна сортувати результати запиту в таблиці. Використовуючи пропозицію ORDER BY, ви також можете вказати спосіб сортування результатів під час виконання запиту. Якщо використовується пропозиція ORDER BY, вона повинна знаходитись наприкінці інструкції SQL.
Пропозиція ORDER BY містить список полів, для яких потрібно виконати сортування, у тому самому порядку, в якому будуть застосовані сортування.
Припустимо, наприклад, що результати спочатку потрібно відсортувати по полю "Компанія" в порядку зменшення, а потім, якщо присутні записи з однаковим значенням поля "Компанія", - відсортувати їх по полю "Адреса електронної пошти" в порядку зростання. Пропозиція ORDER BY буде виглядати так:
ORDER BY Company DESC,
Примітка:За замовчуванням Access сортує значення за зростанням (від А до Я, від найменшого до найбільшого). Щоб замість цього виконати сортування значень за спаданням, необхідно вказати ключове слово DESC.
Додаткові відомості про пропозицію ORDER BY див. у статті Пропозиція ORDER BY.
Робота зі зведеними даними: пропозиції GROUP BY та HAVING
Іноді виникає необхідність роботи зі зведеними даними, такими як підсумковий продаж за місяць або найдорожчі товари на складі. Для цього у реченні SELECT до поля застосовується агрегатна функція. Наприклад, якщо в результаті виконання запиту потрібно отримати кількість адрес електронної пошти кожної компанії, пропозиція SELECT може виглядати так:
Можливість використання тієї чи іншої агрегатної функції залежить від типу даних у полі та потрібного виразу. Для отримання додаткових відомостей про доступні функції функцій див . статті Статистичні функції SQL .
Завдання полів, що не використовуються в агрегатній функції: пропозиція GROUP BY
При використанні агрегатних функцій зазвичай потрібно створити пропозицію GROUP BY. У пропозиції GROUP BY вказуються всі поля, яких не застосовується агрегатна функція. Якщо агрегатні функції застосовуються до всіх полів у запиті, GROUP BY пропозицію створювати не потрібно.
Пропозиція GROUP BY повинна слідувати відразу за пропозицією WHERE або FROM, якщо пропозиція WHERE відсутня. У реченні GROUP BY поля вказуються у тому порядку, як і у реченні SELECT.
Продовжимо попередній приклад. Нехай у реченні SELECT агрегатна функція застосовується тільки до поля [Адреса електронної пошти], тоді пропозиція GROUP BY буде виглядати наступним чином:
GROUP BY Company
Додаткові відомості про пропозицію GROUP BY див. у статті Пропозиція GROUP BY.
Обмеження агрегованих значень за умов групування: пропозиція HAVING
Якщо потрібно вказати умови для обмеження результатів, але поле, до якого їх потрібно застосувати, використовується в агрегованій функції, пропозицію WHERE не можна використовувати. Замість нього слід використовувати пропозицію HAVING. Пропозиція HAVING працює так само, як і WHERE, але використовується для агрегованих даних.
Припустимо, наприклад, що до першого полю у реченні SELECT застосовується функція AVG (яка обчислює середнє значення):
SELECT COUNT(), Company
Якщо ви хочете обмежити результати запиту на основі значення COUNT, до цього поля не можна застосувати умову відбору в пропозиції WHERE. Замість умови слід помістити в пропозицію HAVING. Наприклад, якщо потрібно, щоб запит повертав рядки тільки в тому випадку, якщо компанія має кілька адрес електронної пошти, можна використовувати таку пропозицію HAVING:
HAVING COUNT()>1
Примітка:Запит може включати і пропозицію WHERE, і пропозицію HAVING, умови відбору для полів, які не використовуються в статистичних функціях, вказуються в пропозиції WHERE, а умови для полів, які використовуються в статистичних функціях, - у пропозиції HAVING.
Додаткові відомості про пропозицію HAVING див. у статті Пропозиція HAVING.
Об'єднання результатів запиту: оператор UNION
Оператор UNION використовується для одночасного перегляду всіх даних, що повертаються кількома подібними запитами на вибірку, як об'єднаний набор.
Оператор UNION дозволяє об'єднати дві вказівки SELECT в одну. Інструкції SELECT, що об'єднуються, повинні мати однакове число і порядок вихідних полів з такими ж або сумісними типами даних. При виконанні запиту дані кожного набору відповідних полів об'єднуються в одне вихідне поле, тому вихідні дані запиту мають стільки ж полів, скільки і кожна інструкція SELECT окремо.
Примітка:У запитах на об'єднання числовий та текстовий типи даних сумісні.
Використовуючи оператор UNION, можна вказати, чи повинні до результатів запиту включатися рядки, що повторюються, якщо такі є. Для цього слід використати ключове слово ALL.
Запит на об'єднання двох інструкцій SELECT має наступний базовий синтаксис:
SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;
Припустимо, наприклад, що є дві таблиці, які називаються "Товари" та "Послуги". Обидві таблиці містять поля з назвою товару або послуги, ціною та відомостями про гарантію, а також поле, в якому вказується ексклюзивність пропонованого товару чи послуги. Незважаючи на те, що в таблицях "Продукти" та "Послуги" передбачені різні типи гарантій, основна інформація та сама (надається на окремі продукти або послуги гарантія якості). Для об'єднання чотирьох полів із двох таблиць можна використовувати наступний запит на об'єднання:
SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;
Для отримання додаткових відомостей про об'єднання інструкцій SELECT за допомогою оператора UNION див.
Мова запитів до баз даних SQL виникла 70-ті роки. Його прототип був розроблений фірмою IBM та відомий під назвою SEQUEL (Structured English QUEry Language). SQL увібрав у собі переваги реляційної моделі, зокрема те, що в її основі лежить математичний апарат реляційної алгебри та реляційного обчислення, використовуючи при цьому порівняно невелику кількість операторів та простий синтаксис.
Завдяки своїм якостям мова SQL стала спочатку «де-факто», а потім і офіційно затвердженим як стандарт мови для роботи з реляційними базами даних, що підтримується всіма провідними світовими фірмами, що діють у галузі технології баз даних. Використання виразної та ефективної стандартної мови дозволило в даний час забезпечити високий ступінь незалежності прикладних програмних систем, що розробляються, від конкретного типу використовуваної СУБД, істотно підняти рівень і уніфікацію інструментальних засобів розробки додатків, що працюють з реляційними базами даних.
Говорячи про стандарт мови SQL, слід зазначити, що більшість його комерційних реалізацій мають більші або менші відступи від стандарту. Це, звичайно, погіршує сумісність систем, які використовують різні діалекти SQL. З іншого боку, корисні розширення реалізацій мови щодо стандарту є засобом розвитку мови та згодом включаються до нових редакцій стандарту.
Мовою SQL присвячено велику кількість книг, у тому числі й навчального призначення, деякі з них наведені у списку літератури цього посібника, зокрема, навчальний посібник спеціально присвячений питанням практичного вивчення мови SQL. У зв'язку з цим у цьому посібнику ми розглянемо лише важливі загальні особливості цієї мови, що мають значення для подальшого викладу матеріалу.
8.1. Відмінність SQL від процедурних мов програмування
Мова SQL - відноситься до класу непроцедурних мов програмування. На відміну від універсальних процедурних мов, які також можуть бути використані для роботи з базами даних, мова SQL орієнтована не на записи, а на множини. Це означає таке. Як вхідна інформація для формулованого на мові SQL запиту до бази даних використовується безліч кортежів-записіводнієї чи кількох таблиць відносин. В результаті виконання запиту також утворюється безліч кортежіврезультуючої таблиці-відносини. Тобто в SQL результатом будь-якої операції над відносинами також є ставлення. Запит SQL ставить не процедуру, тобто. послідовність дій, необхідні отримання результату, а умови, яким мають задовольняти кортежі результуючого відносини, сформульовані у термінах вхідного (чи вхідних) відносин.
8.2. Форми та складові частини SQL
Існують і використовуються дві форми мови SQL: інтерактивний SQL
та вбудований SQL.
Інтерактивний SQLвикористовується для безпосереднього введення та отримання результату SQL запитів користувачем в інтерактивному режимі.
Вбудований SQL складається з команд SQL, вбудованих всередину програм, які зазвичай написані деякою іншою мовою (Паскаль, С, С++ та ін). Це робить програми, написані на таких мовах, більш потужними та ефективними, забезпечуючи можливість роботи за допомогою них з даними, що зберігаються в реляційних базах даних, вимагаючи, однак, введення додаткових засобів, що забезпечують інтерфейс SQL з мовою, на яку він вбудовується.
І інтерактивний, і вбудований SQL зазвичай поділяють такі складові.
Мова Визначення Даних- DDL (Data Definition Language), дає можливість створення, зміни та видалення різних об'єктів бази даних (таблиць, індексів, користувачів, привілеїв тощо).
До додаткових функцій мови визначення даних DDL можуть бути включені також засоби визначення обмежень цілісності даних,
визначення порядку у структурах зберігання даних, опис елементів фізичного рівня зберігання даних.
Мова обробки даних- DML (Data Manipulation Language),
надає можливість вибірки інформації з бази даних і перетворення даних, що зберігається в ній.
Тим не менш, це не дві різні мови, а компоненти єдиного SQL.
8.3. Умови та термінологія
Ключові слова – це слова, що використовуються у виразах, які мають спеціальне призначення. Наприклад, вони можуть означати конкретні команди SQL. Ключові слова не можна використовувати для інших цілей, наприклад як імена об'єктів бази даних.
Оператори SQL є інструкціями, за допомогою яких здійснюється звернення SQL до бази даних. Оператори складаються з однієї чи більше окремих логічних частин, які називаються пропозиціями. Пропозиції починаються відповідним ключовим словом і складаються з ключових слів та аргументів.
Слід звернути увагу на те, що терміни, які використовуються в мові SQL, дещо відрізняються від термінів, прийнятих в описі реляційної моделі. Зокрема, замість терміну відношення в ньому використовується термін таблиця , замість термінів кортеж і атрибут відповідно рядок і стовпець .
8.4. Вибір даних. Оператор SELECT
Найпростіші SELECT- запити
Оператор SELECT (ВИБРАТИ ) мови SQL є найважливішим і найчастіше використовуваним оператором. Він призначений вибірки інформації з таблиць бази даних. Спрощений синтаксис оператора SELECT має такий вигляд.
SELECT< список атрибутів>
FROM< список таблиц>
У квадратних дужках вказані елементи, які можуть бути відсутніми.
Ключове слово SELECT повідомляє СУБД, що ця пропозиція є запитом на отримання інформації. Після слова SELECT через кому перераховуються найменування полів (список атрибутів), вміст яких запитується.
Обов'язковим ключовим словом у реченні-запиті SELECT є слово FROM (з). За ключовим словом FROM вказується список розділених ком імен таблиць, з яких витягується інформація.
Наприклад,
SELECT NAME, SURNAME FROM STUDENT;
SQL-запит повинен закінчуватися символом "точка з комою". Наведений запит здійснює вибірку всіх значень полів NAME та
SURNAME із таблиці STUDENT.
Його результатом є таблиця наступного виду
Порядок прямування стовпців у цій таблиці відповідає порядку полів NAME та SURNAME , вказаному у запиті, а не їх порядку у вхідній таблиці
STUDENT.
Звернімо увагу, що отримані в результаті SQL-запиту таблиці не повною мірою відповідають визначенню реляційного відношення. В
зокрема в них можуть виявитися дублікати кортежів з однаковими значеннями атрибутів.
Наприклад, запит: “Отримати список назв міст, в яких живуть студенти, відомості про які знаходяться у таблиці STUDENT ”, можна записати у такому вигляді
SELECT CITY FROM STUDENT;
Його результатом буде таблиця
Білгород
Можна бачити, що у цій таблиці можуть зустрічатися однакові рядки. Вони виділені жирним шрифтом.
Для виключення результату SELECT -запиту повторюваних записів використовується ключове слово DISTINCT (відмінний). Якщо запит SELECT витягує безліч полів, DISTINCT виключає дублікати рядків, у яких значення всіх вибраних полів ідентичні.
Введення у вираз оператора SELECT , речення, що визначається ключовим словом WHERE (де), дозволяє вводити вираз умови (предикат), що набуває значення істина або брехня для значень полів рядків таблиць, до яких звертається оператор SELECT . Пропозиція WHERE визначає, які рядки вказаних таблиць мають бути вибрані. У таблицю, що є результатом запиту, включаються тільки рядки, для яких умова (предикат), зазначена в реченні WHERE , приймає значення істина.
Написати запит, який виконує вибірку імен (NAME ) всіх студентів з прізвищем (SURNAME ) Петров, відомості про які знаходяться в таблиці
SELECT SURNAME, NAME
FROM STUDENT
WHERE SURNAME = 'Петров';
У умовах, що задаються в пропозиції WHERE, можуть використовуватися операції порівняння, що задаються наступними операторами: = (рівно), > (більше),< (меньше), >= (більше або одно),<= (меньше или равно), <>(Не дорівнює), а також логічні оператори AND, OR та NOT.
Наприклад, запит для отримання імен та прізвищ студентів, які навчаються на третьому курсі та отримують стипендію (розмір стипендії більший за нуль) буде виглядати таким чином
SELECT NAME, SURNAME FROM STUDENT
WHERE KURS = 3 AND STIPEND > 0;
8.5. Реалізація операцій реляційної алгебри засобами мови SQL. Реляційна повнота SQL
В У попередніх розділах, присвячених розгляду реляційної алгебри, говорилося, що однією з важливих сторін наявності в реляційній моделі такого математичного апарату є можливість оцінки та доказуреляційної повнотиПрактично використовуються мови запитів до баз даних, зокрема мови SQL. Для того, щоб показати, що мова SQL є реляційно повною, потрібно показати, що будь-який оператор реляційної алгебри може бути виражений засобами SQL. Насправді досить показати, що засобами SQL можна висловити будь-який із примітивних реляційних операторів. Нижче наведено приклади реалізації реляційних операторів за допомогою мови SQL.
Оператор об'єднання
Реляційна алгебра: A UNION B Оператор SQL:
SELECT * FROM A
SELECT * FROM B;
Оператор перетину
Реляційна алгебра: A INTERSECT B
Оператор SQL:
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …,
FROM A, B
WHERE A. ПОЛЕ1=B. ПОЛЕ1 AND A. ПОЛЕ2=B. ПОЛЕ2 AND …;
SELECT A.* FROM A, B
WHERE A.pk = B.pk;
Оператор віднімання
Реляційна алгебра: A MINUS B Оператор SQL:
SELECT * FROM A
WHERE A.pk NOT IN (SELECT pk FROM B);
де A.pk та B.pk первинні ключі таблиць A та B
Оператор декартового твору
Реляційна алгебра: A TIMES B Оператор SQL:
FROM A, B;
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …
FROM A CROSS JOIN B;
Оператор проекції
Реляційна алгебра: A Оператор SQL:
SELECT DISTINCT X, Y, …, Z FROM A;
Оператор вибірки
Реляційна алгебра: A WHERE θ Оператор SQL:
SELECT * FROM A
WHERE θ;
Оператор θ-з'єднання
Реляційна алгебра: (A TIMES B) WHERE θ Оператор SQL:
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …
FROM A, B
WHERE θ;
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …
FROM A CROSS JOIN B WHERE θ;
Оператор поділу
Реляційна алгебра: A(X,Y) DEVIDE BY B(Y) Оператор SQL:
SELECT DISTINCT A. X FROM A
(SELECT *
(SELECT * FROM A A1
A1. X=A. X AND A1. Y=B. Y));
Таким чином, наведені вирази доводять, що мова SQL, як реляційна алгебра, є реляційно повним.
Слід звернути увагу, якщо у наведених запитах у таблицях будуть присутні NULL -значення (див. нижче розділ 9.1), всі вищеперелічені запити можуть відпрацювати неправильно, т.к. NULL< >NULL та NULL = NULL – є брехня.
Це, однак, не спростовує зробленого висновку про реляційну повноту SQL, тому що NULL - значення реляційної моделі не підтримуються.
