Національний технічний університет "Харківський Політехнічній інститут"
Факультет "Компьютерні та Інформаційні технології"
Кафедра "Обчіслювальна техніка та програмування"
Реферат
З курсу "Організація баз даних"
Тема: Можливості SQL-Запитів у Microsoft Access
Виконала: ст. гр. xxxxxxxxxxxxxxxxx.
Перевірів: xxxxx
Харків
2006
Зміст:
1. Введення
Як відомо, в основному системи управління реляційними базами даних (СУРБД) делат на два класи - серверні та користувацькі. Microsoft Access, що є предметом розгляду даного реферату, належить до другого класу систем, тому що з-за опрацьованою системи спільного доступу до даних не може виступати в ролі повноцінної серверної СУРБД (хоча відповідні драйвери ODBC існують і працюють). Також на користь того, що Access належить до призначених для користувача систем, говорить факт інтегрованості сховища даних (таблиць) і засобів створення користувальницького інтерфейсу (збережені запити, форми, звіти, програми)
Проте за іншими своїми можливостями, зокрема, за можливостями виконання складних запитів SQL, Access набагато перевершує інші програмні продукти свого класу. До того ж, легкість об'єднання з іншим ПО, що поставляється в комплекті Office Professional - SQL Server Desktop Engine - робить Access взагалі недосяжною для конкурентів системою.
Даний реферат присвячено проблематиці, неминуче виникає при проектуванні БД - проектуванні системи пошуку та модифікації існуючих даних. Показано, як виконувати ці операції, використовуючи для цього один лише вбудований інтерпретатор мови SQL - такий шлях є найбільш універсальним, і, через деякий час - найлегшим способом організувати будь-яке потрібне подання наявних даних.
2. Запити на вибірку
Даний тип запитів є одним з найбільш часто застосовуваних не тільки в MS Access, а й взагалі у всіх СУРБД.
Основні завдання таких запитів такі:
а. Вибрати деякі поля з таблиці
б. Відфільтрувати вміст таблиці по деяких критеріях
в. Об'єднати декілька різнорідних таблиць, використовуючи зв'язки типу "один-до-одного», «один-до-багатьох»
р. Сформувати з декількох однорідних таблиць одне джерело записів.
д. Згрупувати дані таблиці і / або обчислити деякі характеристики цих даних.
2.1. Особливості використання операторів SELECT
Запити на вибірку завжди починаються з пропозиції SQL SELECT (список полів) FROM (список таблиць), наприклад:
SLECT Назва, Місто FROM замовники
Проте навіть така конструкція має розгалуження:
- SELECT DISTINCT - використовується для відбору унікальних записів по полях, що містяться в запиті
- SELECT DISTINCTROW - те ж саме, що і DISTINCT, але для визначення унікальності використовуються
- SELECT TOP [число та кількість відсотків]
У SQL-запитах можна об'єднувати декілька таблиць, навіть якщо вони містять різну кількість записів (хоча, як правило, таке об'єднання буде безглуздо). Для вибору всіх полів з таблиці застосовується символ *.
Приклади найпростіших SQL-запитів:
SELECT * From Замовники - вибирає всі записи і поля за таблиці «Замовники».
SELECT DISTINCT Місто FROM Замовники - вибирає по 1 разу кожне місто, в якому знаходиться один або більше замовник.
SELECT TOP 10 PERCENT * FROM Замовники - вибирає перші 10% записів у таблиці.
Якщо ж у запиті присутні кілька таблиць, то в частині FROM їх необхідно перерахувати через кому. Якщо з таблиць в такому запиті вибираються не всі поля, то обирані потрібно вводити в форматі [ім'я_таблиці]. [Ім'я_поля], наприклад:
SELECT Table1.Field1, Table2.Field1 FROM Table1, Table2;
Крім того, імена полів або таблиць мовою, що відрізняється від англійської, рекомендується брати в квадратні дужки. Імена ж, що містять пробіли, підлягають обов'язковому узяття в квадратні дужки.
2.2. Використання критеріїв відбору
Припустимо, у нас є таблиця «Замовники» з наступними полями:
Якщо необхідно вибрати з неї не всі записи, а тільки ті, які задовольняють деяким умовам. Наприклад, тільки тих з них, які розміщені у Харкові. Для цього використовується інструкція WHERE. Умов може бути кілька, тоді вони об'єднуються логічними операціями І, АБО, виключає АБО (XOR). Для інвертування частини або всього умови (тобто відбору тих записів, які даній умові не задовольняють) використовується операція NOT.
Загальний синтаксис простого запиту з умовою такий:
SELECT Спісок_полей FROM Спісок_Табліц
WHERE (условіе1) лог_оп (условіе2) лог_оп (условіе3);
Приклад:
SELECT * FROM Замовники WHERE (Місто = 'Харків');
SELECT * FROM Замовники WHERE (Місто = 'Харків') AND (К_во_заказов> 5);
2.3. Об'єднання декількох джерел даних
Іноді буває необхідно об'єднати різнорідні таблиці, щоб зайнятися аналізом даних, уникаючи непотрібного дублювання даних і зайвих операцій з пошуку.
Припустимо, до нашої бази даних приєднана зовнішня таблиця «Реєстр», в якій зберігаються відомості про всіх суб'єктів підприємницької діяльності в Україну. А нам треба переглянути всі відомості тільки про наших замовників. Знову-таки, всіх. Для таких випадках існує інструкція JOIN - об'єднання таблиць по одному полю. Розрізняють внутрішні (INNER) і зовнішні (LEFT, RIGHT) об'єднання. Ми розглянемо лише внутрішні - це найбільш життєва ситуація.
Загальний вигляд об'єднання такий:
SELECT (список_полей_главной_таблицы) FROM (Главная_табліца) <Від_об'едіненія> JOIN (Подчіненная_табліца) ON (Главная.Поле1 = Подчіненная.Поле1)
Отже, таблиця «Реєстр» має наступну структуру:
Тепер приєднаємо її до таблиці «Замовники»:
SELECT * FROM Замовники INNER JOIN Реєстр ON (Заказчікі.Названіе = Реестр.Названіе)
У результаті ми будемо мати джерело записів з кількістю записів, рівним розміром таблиці «Замовники», і що містить всі поля таблиць «Замовники» та «Реєстр». Так само, як і в конструкції WHERE, об'єднання може бути по декількох полях, з використанням різних умов, проте слід мати на увазі, що використання нестандартних (непідтримуваних конструктором запитів) дій може призвести до непередбачуваних результатів.
Щоб уникнути нерозуміння варто зауважити, що використання однієї конструкції SQL практично ніколи не накладає заборону на використання іншої. Тобто запит типу
SELECT * FROM Замовники INNER JOIN Реєстр ON (Заказчікі.Названіе = Реестр.Названіе)
WHERE (Місто = 'Харків') AND (К_во_заказов> 5);
буде сприйнятий MS Access цілком нормально.
Можлива й інша ситуація.
Припустимо, у нас є таблиця «Сотруднікі_офіса» наступної структури:
Також є таблиця «Сотруднікі_філіала» з точно такою ж структурою. Необхідно в якому-небудь підсумковому звіті представити ці таблиці разом. Для цього використовується конструкція UNION. При її використанні можна склеїти скільки завгодно таблиць. Точніше, таблиці необов'язково повинні мати однакову структуру. Необхідно лише, щоб з усіх таблиць вибиралося однакову кількість полів, і щоб ці поля були повністю еквівалентні.
Загальна структура такого запиту така:
SELECT Табліца1.Поле1, Табліца1.Поле N FROM Таблиця1
UNION SELECT Табліца2.Поле1, Табліца2.Поле N FROM Таблиця2
UNION SELECT Таблиця K. Поле1, Таблиця K. Поле N FROM Таблиця K;
У нашому випадку це буде виглядати так:
SELECT Сотруднікі_офіса .* FROM Сотруднікі_офіса
UNION SELECT Сотруднікі_філіала .* FROM Сотруднікі_філіала
У результаті ми отримаємо джерело записів, який містить відомості про всіх співробітників, які працюють і в офісі, і у філії.
Іноді буває необхідно об'єднати кілька таблиць (більше 2) за допомогою операції JOIN. Зробити це в рамках одного запиту дуже проблематично, проте не слід забувати, що MS Access в змозі використовувати запити як джерело записів. Тобто об'єднайте дві таблиці в одному запиті, а потім обхедініте третю таблицю і запит у другому.
2.4. Групові операції і обчислювані поля
Припустимо, є у нас таблиця «Постоянние_кліенти». І нам, в залежності від кількості покупок треба дати їм знижки - наприклад, ті, хто зробив більше 5 покупок, отримує знижку 6%. Вихідна таблиця має такий вигляд:
Щоб виконувати подібні операції, не вдаючись до мистецтва програмування, у запитах Microsoft Jet (саме так називається ядро баз даних Access) реалізована можливість реалізації обчислюваних полів.
Оголошуються ці поля точно так само, як і звичайні, в конструкції SELECT:
SELECT (спісок_обичних_полей), (операція_с_полямі_ілі_ числа-ми) AS вичісляемое_поле FROM (спісок_табліц);
Що стосується нашої ситуації має сенс використовувати операцію IIf - Якщо. Її формат такий: IIf (умова; действіе_еслі_істіна; действіе_ еслі_ложь). Можна також використовувати вкладені умови - їх потрібно вставляти замість дій - IIf (умова; IIf (вложенное_условіе; действіе_еслі_істіна; действіе_еслі_ложь); действіе_еслі_ложь)
Наш запит буде виглядати так:
SELECT *, (IIf (Купівлі> 5; 0.06; 0)) AS Знижка From Постоянние_кліенти;
Він дасть нам джерело записів, який містить усі поля і записи таблиці «Постоянние_кліенти», а на додачу до них - вже розраховані знижки у відповідному полі.
Більш докладно про підтримуваних обчислювальних операціях можна прочитати у відповідному розділі довідки Microsoft Access.
Access підтримує також групові (підсумкові) операції. Це означає, що можна розрахувати, виходячи з набору даних, деякі статистичні та інші характеристики, такі як сума, дисперсія, математичне сподівання та інші. У запитах цим користуються не дуже часто, але користуються. Поля з груповими обчисленнями оголошуються так само, як і звичайні обчислювані поля. Наприклад, запит
SELECT Sum (Купівлі) AS Кількість FROM Постоянние_кліенти;
Поверне нам одну-єдину запис із загальним числом покупок, зроблених всіма постійними клієнтами.
Іноді для зручності аналізу даних їх необхідно групувати. Тобто якщо у джерелі записів є записи, що повторюються з яких-небудь критеріями, часом краще привести їх до однієї і вивести їх кількість.
Отже, є дві таблиці: «Асортимент», що містить список товарів, якими торгує гіпотетична фірма «Рога_і_копита», і безграмотно складена таблиця «Склад», в яку при знаходженні кожного примірника товару додавали запис. Треба зробити так, щоб дані з цих таблиць отобразалісь наочно.
Таблиця «Асортимент» містить поля «ID_товара» і «Найменування», а таблиця до «Склад» - тільки «ID_товара».
Спочатку створимо запит «Склад_гр», який згрупує повторювані записи в таблиці «Склад» і виведе кількість повторень для кожного запису. Для цього застосуємо групову операцію Count і конструкцію GROUP BY.
У загальному вигляді угруповання виглядає так:
SELECT (спісок_полей) FROM (спісок_табліц) GROUP BY (спісок_полей);
У нашому випадку ми отримаємо такий запит:
SELECT ID _товара, Count (ID _товара) AS Кількість FROM Склад
GROUP BY ID _товара;
Цей запит дасть нам вже згруповану таблицю, і ми легко зможемо побачити, скільки примірників кожного товару лежить на складі. Але тепер бажано було б переглянути найменування товарів, що лежать на складі. Для цього скористаємося вже знайомої операцією INNER JOIN.
SELECT Кількість FROM Склад_гр INNER JOIN Асортимент ON (Склад_гр. ID _товара = Асортимент. ID _товара);
Повернений таким запитом джерело записів буде мати наступну структуру:
Якраз те, що потрібно.
2.5. Сортування отриманих результатів
Для того, щоб зробити висновок впорядкованим, мова SQL пропонує використання конструкції ORDER [ASCEND | DESCEND] BY (полі). ASCEND / DESCEND - це напрямок сортування - за зростанням або спаданням, відповідно. Модернізуємо наш попередній запит таким чином, щоб він при виведенні сортував дані по полю «Найменування»:
SELECT Кількість FROM Склад_гр INNER JOIN Асортимент ON (Склад_гр. ID _товара = Асортимент. ID _товара)
ORDER ASCEND BY Найменування;
На цьому закінчимо розгляд (кілька поверхневе) пропозицій SELECT.
3. Запити на додавання
Надалі ми будемо розглядати запити, які керують безпосередньо даними в таблицях, а то й самими таблицями. При їх розробці та виконанні слід дотримуватися обережності, тому що зміни, що вносяться такими запитами в дані, часто незворотні. Природно, що джерелами даних для таких запитів можуть бути лише таблиці.
Іноді, найчастіше в серверних базах даних або при використанні елементів програмування в Microsoft Access потрібно динамічно додати запис в таблицю. Мова SQL пропонує використовувати для цих цілей пропозицію INSERT INTO.
Загальна структура цього типу запитів така:
INSERT INTO Ім'я_таблиці VALUES (список значень);
Список значень завжди повинен бути рівний за кількістю елементів кількості полів в змінною таблиці. Якщо ж треба ввести тільки деякі поля, для решти 0 або NULL для строкових полів. Також можна використовувати вирази.
Наприклад, є таблиця «товари»
Припустимо, нам треба додати запис в цю таблицю, але біда в тому, що поле ID_товара - ключова і іммет тип «Лічильник». Порушувати його не можна, тому скористаємося груповий операцією Max ().
INSERT INTO Товари VALUES
(MAX (ID _Товара) +1, "Який-то товар ', 10);
Таким чином, ми зберегли порядок відліку.
Слід зауважити, що пропозиція INSERT є одним з найпростіших в SQL. Ніяких додаткових конструкцій у ньому не використовується.
4. Запити на оновлення
Іноді, знову-таки, найчастіше при використанні елементів програмування, може знадобитися динамічна зміна всіх або частини записів у таблиці. Для цього застосовується пропозицію UPDATE.
Припустимо, зі складу раз на місяць здійснюється відвантаження. Відвантажують по одному артикулу кожного товару. Якщо якийсь товар на складі відсутня, його, відповідно, не відвантажують.
Загальна структура запитів на оновлення:
UPDATE ім'я_таблиці SET
(Список значень або список виразів «Поле = значення»);
За замовчуванням UPDATE накладає зміни на всі записи таблиці. Щоб обмежити кількість уражених записів, слід використовувати конструкцію WHERE так само, як і в запитах на вибірку.
Наш запит на відвантаження буде виглядати так:
UPDATE Товари SET (Кількість = Кількість - 1)
WHERE (Кількість> 0);
Він-то і виконає всі необхідні дії по обробці результатів відвантаження.
5. Запити на видалення
Трапляються ситуації, коли дані в таблицях застарівають. Припустимо, є база фінансової звітності підприємства. І документи, які були введені туди до 1 січня 2004 року, треба видалити.
Для цього використовується пропозицію DELETE.
Структура таблиці «Документи»
Запити на видалення мають наступну структуру:
DELETE * FROM ім'я_таблиці WHERE (список умов);
У нашому випадку запит буде мати наступний вигляд:
DELETE * FROM Документи
WHERE (Дата_регістраціі <01.01.2004);
Як бачимо, діалект мови SQL, використовуваний в Microsoft Access, досить демократичний.
6. Параметричні запити
Access дозволяє зробити запити більш гнучкими - тобто зробити так, щоб при зміні критеріїв у запиті не доводилося правити його в конструкторі. Для цього використовується механізм параметрів.
Наприклад, у нас є таблиця «Спісок_жільцов», і нам потрібно відобразити тих з них, вік яких перевищує деяку заздалегідь невідому величину. Для цього використовуємо запит
SELECT * FROM Спісок_жільцов WHERE (Вік> Крітерій_ віку);
При спробі відкриття такого запиту Access в спеціальному діалоговому вікні попросить ввести значення параметра «Крітерій_возраста»
Параметрів може бути скільки завгодно, але майте на увазі, що користувачеві для відкриття такого запиту доведеться відповісти на відповідну кількість діалогових вікон.
7. Керуючі запити ядра Microsoft Jet
Ця невелика група запитів дозволяє робити операції на рівні як окремої таблиці, так і бази даних в цілому.
а. Запит DROP
Цей запит дозволяє видалити з бази даних таблицю або індекс. Його структура -
DROP TABLE ім'я_таблиці
DROP INDEX ім'я_індексу
б. Запит ALTER Table
Цей запит дозволяє редагувати структуру таблиці.
Його структура:
Alter table <ім'я таблиці>; <опис зміни>
У випадку, коли треба зробити декілька змін, вони вказуються через кому. Перерахую типи змін:
ADD <опис колонки>
Дозволяє додати нову колонку в таблицю. Опис колонки таке ж, як і в Create table. Після слова ADD можна вказати слово COLUMN, яке дозволяє зробити текст запиту більш читабельним. Після опису колонки можна вказати слово FIRST, в цьому випадку колонка буде першою в таблиці, або слово AFTER, після якого вказується назва колонки, в цьому випадку навая колонка буде додана після вказаної.
Можна також вказати кілька описів колонки через кому і усередині круглих дужок. У цьому разі зазначити розташування колонки не можна.
ADD INDEX (<ім'я колонки >,...)
Дозволяє додати новий індекс. У дужках вказуються назви колонок, що входять в індекс. Між словом INDEX і дужкою можна вказати назву індексу.
ADD PRIMARY KEY (<ім'я колонки >,...)
Дозволяє додати новий первинний ключ (або змінити його). У дужках вказуються назви колонок, які входять у ключ.
ADD UNIQUE (<ім'я колонки >,...)
Дозволяє додати новий індекс з унікальними значеннями. У дужках вказуються назви колонок, які входять до індексу. Між словом UNIQUE і дужкою можна вказати назву індексу.
ADD FULLTEXT (<ім'я колонки> ...)
Дозволяє додати новий індекс із повнотекстових пошуком. У дужках вказуються назви колонок, які входять до індексу. Між словом FULLTEXT і дужкою можна вказати назву індексу.
ALTER COLUMN <ім'я колонки> SET DEFAULT <значення>
ALTER COLUMN <ім'я колонки> DROP DEFAULT
Запит дозволяє створити або видалити значення за замовчуванням для колонки. При цьому слово COLUMN можна опустити.
CHANGE COLUMN <ім'я колонки> <опис колонки>
Запит змінює зазначену колонку на нову, опис таке ж, як і в CREATE TABLE. Слово COLUMN можна опустити. В описі колонки вказується ім'я колонки, так що ім'я може бути змінено.
MODIFY COLUMN <опис колонки>
Змінює колонку на нову, опис таке ж, як і в CREATE TABLE. Слово COLUMN моно опустити.
DROP COLUMN <ім'я> - Видаляє колонку.
DROP PRIMARY KEY - Видаляє первинний ключ
DROP INDEX <ім'я індексу> - Видаляє індекс
RENAME TO <нове ім'я таблиці> - Перейменовує таблицю
в. Запит CREATE TABLE
Цей запит дозволяє динамічно створити нову таблицю в базі даних. Наприклад:
У наступному керуючому запиті за допомогою інструкції CREATE TABLE створюється таблиця «Друзі». Наведена інструкція визначає імена і типи даних для полів таблиці і створює для поля «Код» індекс, хто чинить таке поле ключовим.
8. Формування і виконання запитів у реальному часі (інструментарій Visual Basic For Applications)
Для того, щоб запрограмувати автоматизацію дій з запитами, Microsoft Access пропонує два варіанти:
а. Використання об'єктів доступу до даних (DAO)
б. Використання об'єкта-дії DoCmd.
Перший шлях дозволяє нам створювати збережені запити в базі даних (вони будуть з'являтися у вкладці «Запити») або ж працювати з їх результатами безпосередньо. Об'єкт DoCmd, а точніше, його метод RunSQL використовується виключно для запуску запитів на зміну даних або керуючих запитів MS Jet.
Покажемо загальні прийоми створення і виконання запитів за допомогою DAO. Для цього будемо використовувати об'єкт Application.CurrentDB і, для створення / видалення збережених запитів - колекцію Queries, в цьому об'єкті зберігається. Для роботи з даними в реальному часі застосуємо об'єкт Recordset.
За основу візьмемо запити на вибірку, показані в розділі 2 даного реферату.
Option Compare Database
Option Explicit
Public Sub QRY_Example1 ()
Dim s As String
S = "SELECT Кількість FROM Склад _ гр INNER JOIN" + _ "Асортимент ON (Склад _ гр. ID _товара = Асортимент. ID_ товару)" + _
"ORDER ASCEND BY Найменування;"
Application.CurrentDb.CreateQueryDef ("Query1", s)
End Sub
Така процедура створить в базі даних новий запит з ім'ям Query1 і текстом, що зберігається у змінній S. А наступна - видалить його:
Public Sub QRY_Example2 ()
Application.CurrentDb.QueryDefs.Delete ("Query1")
End Sub
Тепер покажемо, як працювати з отриманими із запиту даними безпосередньо (ця процедура виведе повідомлення, в яких перерахує кількість і найменування всіх товарів):
Public Sub QRY_Example3 ()
Dim s As String
Dim rst As New Recordset
S = "SELECT Кількість FROM Склад _ гр INNER JOIN" + _ "Асортимент ON (Склад _ гр. ID _товара = Асортимент. ID_ товару)" + _
"ORDER ASCEND BY Найменування;"
set rst = Application.CurrentDb.OpenRecordset (s, dbOpenDynaset, dbReadOnly)
rst.MoveFirst
for I = 1 to rst.RecordCount do
MsqBox "На складі зберігається" + str (rst. Fields ("Кількість")) + "" + rst. Fields ("найменування")
Rst. MoveNext
Next
End Sub
З приводу властивостей і методів об'єктів доступу до даних докладні відомості можна отримати у вбудованій довідці VBA.
З об'єктом DoCmd все набагато простіше:
Public Sub QRY_Example4 ()
Dim s As String
Dim s2 As String
Dim rst As New Recordset
S = InputBox "Введіть найменування товару", "Наша_БД"
S 2 = InputBox "Введіть кількість цього товару", "Наша_БД"
S = "INSERT INTO Товари VALUES" + _
"(MAX (ID_ Товару) +1," + "'" + s + "'," + s2 + ");"
DoCmd. RunSQL s
End Sub
Ця процедура в запросить в діалоговому вікні ввести найменування товару і його кількість, а потім додасть ці значення у вигляді нового запису в таблицю «Товари».
На цьому демонстрацію можливостей ядра Microsoft Jet в області виконання SQL-запитів можна вважати закінченою.
9. Висновки
У даному рефераті були розглянуті основні типи запитів, які підтримуються Microsoft Access - запити на вибірку, додавання, видалення і оновлення записів і керуючі запити. Також висвітлена тема динамічного формування і виконання запитів SQL засобами Visual Basic for Applications.
10. Список літератури:
1. Microsoft Access 2002 Bible - Cary Prague and Michael Irwin - NY: Hungry Minds, 2002.
2. Mirosoft Access 2002 Help system © 1991-2002 Microsoft Corp.
3. Alter table - лекція © RSC-Team 2001-2005 (http://rsc-team.ru/bk/index.pl?rzd=2&group=lection&ind=103)
Факультет "Компьютерні та Інформаційні технології"
Кафедра "Обчіслювальна техніка та програмування"
Реферат
З курсу "Організація баз даних"
Тема: Можливості SQL-Запитів у Microsoft Access
Виконала: ст. гр. xxxxxxxxxxxxxxxxx.
Перевірів: xxxxx
Харків
2006
Зміст:
| 1. Введення | 3 |
| 2. Запити на вибірку | 4 |
| Особливості використання операторів SELECT | 4 |
| Завдання критеріїв відбору (WHERE) | 5 |
| Об'єднання декількох джерел даних | 6 |
| Групові операції і обчислювані поля | 8 |
| Сортування результатів | 11 |
| 3. Запити на додавання | 11 |
| 4. Запити на оновлення | 12 |
| 5. Запити на видалення | 13 |
| 6. Параметричні запити | 14 |
| 7. Керуючі запити ядра Microsoft Jet | 14 |
| 8. Формування і виконання запитів у реальному часі (інструментарій Visual Basic For Applications) | 17 |
| 9. Висновки | 20 |
| 10. Список використаної літератури | 21 |
1. Введення
Як відомо, в основному системи управління реляційними базами даних (СУРБД) делат на два класи - серверні та користувацькі. Microsoft Access, що є предметом розгляду даного реферату, належить до другого класу систем, тому що з-за опрацьованою системи спільного доступу до даних не може виступати в ролі повноцінної серверної СУРБД (хоча відповідні драйвери ODBC існують і працюють). Також на користь того, що Access належить до призначених для користувача систем, говорить факт інтегрованості сховища даних (таблиць) і засобів створення користувальницького інтерфейсу (збережені запити, форми, звіти, програми)
Проте за іншими своїми можливостями, зокрема, за можливостями виконання складних запитів SQL, Access набагато перевершує інші програмні продукти свого класу. До того ж, легкість об'єднання з іншим ПО, що поставляється в комплекті Office Professional - SQL Server Desktop Engine - робить Access взагалі недосяжною для конкурентів системою.
Даний реферат присвячено проблематиці, неминуче виникає при проектуванні БД - проектуванні системи пошуку та модифікації існуючих даних. Показано, як виконувати ці операції, використовуючи для цього один лише вбудований інтерпретатор мови SQL - такий шлях є найбільш універсальним, і, через деякий час - найлегшим способом організувати будь-яке потрібне подання наявних даних.
2. Запити на вибірку
Даний тип запитів є одним з найбільш часто застосовуваних не тільки в MS Access, а й взагалі у всіх СУРБД.
Основні завдання таких запитів такі:
а. Вибрати деякі поля з таблиці
б. Відфільтрувати вміст таблиці по деяких критеріях
в. Об'єднати декілька різнорідних таблиць, використовуючи зв'язки типу "один-до-одного», «один-до-багатьох»
р. Сформувати з декількох однорідних таблиць одне джерело записів.
д. Згрупувати дані таблиці і / або обчислити деякі характеристики цих даних.
2.1. Особливості використання операторів SELECT
Запити на вибірку завжди починаються з пропозиції SQL SELECT (список полів) FROM (список таблиць), наприклад:
SLECT Назва, Місто FROM замовники
Проте навіть така конструкція має розгалуження:
- SELECT DISTINCT - використовується для відбору унікальних записів по полях, що містяться в запиті
- SELECT DISTINCTROW - те ж саме, що і DISTINCT, але для визначення унікальності використовуються
- SELECT TOP [число та кількість відсотків]
У SQL-запитах можна об'єднувати декілька таблиць, навіть якщо вони містять різну кількість записів (хоча, як правило, таке об'єднання буде безглуздо). Для вибору всіх полів з таблиці застосовується символ *.
Приклади найпростіших SQL-запитів:
SELECT * From Замовники - вибирає всі записи і поля за таблиці «Замовники».
SELECT DISTINCT Місто FROM Замовники - вибирає по 1 разу кожне місто, в якому знаходиться один або більше замовник.
SELECT TOP 10 PERCENT * FROM Замовники - вибирає перші 10% записів у таблиці.
Якщо ж у запиті присутні кілька таблиць, то в частині FROM їх необхідно перерахувати через кому. Якщо з таблиць в такому запиті вибираються не всі поля, то обирані потрібно вводити в форматі [ім'я_таблиці]. [Ім'я_поля], наприклад:
SELECT Table1.Field1, Table2.Field1 FROM Table1, Table2;
Крім того, імена полів або таблиць мовою, що відрізняється від англійської, рекомендується брати в квадратні дужки. Імена ж, що містять пробіли, підлягають обов'язковому узяття в квадратні дужки.
2.2. Використання критеріїв відбору
Припустимо, у нас є таблиця «Замовники» з наступними полями:
| № п / п | Назва | Місто | Адреса | К_во_заказов |
Загальний синтаксис простого запиту з умовою такий:
SELECT Спісок_полей FROM Спісок_Табліц
WHERE (условіе1) лог_оп (условіе2) лог_оп (условіе3);
Приклад:
SELECT * FROM Замовники WHERE (Місто = 'Харків');
SELECT * FROM Замовники WHERE (Місто = 'Харків') AND (К_во_заказов> 5);
2.3. Об'єднання декількох джерел даних
Іноді буває необхідно об'єднати різнорідні таблиці, щоб зайнятися аналізом даних, уникаючи непотрібного дублювання даних і зайвих операцій з пошуку.
Припустимо, до нашої бази даних приєднана зовнішня таблиця «Реєстр», в якій зберігаються відомості про всіх суб'єктів підприємницької діяльності в Україну. А нам треба переглянути всі відомості тільки про наших замовників. Знову-таки, всіх. Для таких випадках існує інструкція JOIN - об'єднання таблиць по одному полю. Розрізняють внутрішні (INNER) і зовнішні (LEFT, RIGHT) об'єднання. Ми розглянемо лише внутрішні - це найбільш життєва ситуація.
Загальний вигляд об'єднання такий:
SELECT (список_полей_главной_таблицы) FROM (Главная_табліца) <Від_об'едіненія> JOIN (Подчіненная_табліца) ON (Главная.Поле1 = Подчіненная.Поле1)
Отже, таблиця «Реєстр» має наступну структуру:
| Назва | Код_ЕДРПОУ | Расч_счет | ІПН |
SELECT * FROM Замовники INNER JOIN Реєстр ON (Заказчікі.Названіе = Реестр.Названіе)
У результаті ми будемо мати джерело записів з кількістю записів, рівним розміром таблиці «Замовники», і що містить всі поля таблиць «Замовники» та «Реєстр». Так само, як і в конструкції WHERE, об'єднання може бути по декількох полях, з використанням різних умов, проте слід мати на увазі, що використання нестандартних (непідтримуваних конструктором запитів) дій може призвести до непередбачуваних результатів.
Щоб уникнути нерозуміння варто зауважити, що використання однієї конструкції SQL практично ніколи не накладає заборону на використання іншої. Тобто запит типу
SELECT * FROM Замовники INNER JOIN Реєстр ON (Заказчікі.Названіе = Реестр.Названіе)
WHERE (Місто = 'Харків') AND (К_во_заказов> 5);
буде сприйнятий MS Access цілком нормально.
Можлива й інша ситуація.
Припустимо, у нас є таблиця «Сотруднікі_офіса» наступної структури:
| Таб_номер | ПІБ | Телефон |
Загальна структура такого запиту така:
SELECT Табліца1.Поле1, Табліца1.Поле N FROM Таблиця1
UNION SELECT Табліца2.Поле1, Табліца2.Поле N FROM Таблиця2
UNION SELECT Таблиця K. Поле1, Таблиця K. Поле N FROM Таблиця K;
У нашому випадку це буде виглядати так:
SELECT Сотруднікі_офіса .* FROM Сотруднікі_офіса
UNION SELECT Сотруднікі_філіала .* FROM Сотруднікі_філіала
У результаті ми отримаємо джерело записів, який містить відомості про всіх співробітників, які працюють і в офісі, і у філії.
Іноді буває необхідно об'єднати кілька таблиць (більше 2) за допомогою операції JOIN. Зробити це в рамках одного запиту дуже проблематично, проте не слід забувати, що MS Access в змозі використовувати запити як джерело записів. Тобто об'єднайте дві таблиці в одному запиті, а потім обхедініте третю таблицю і запит у другому.
2.4. Групові операції і обчислювані поля
Припустимо, є у нас таблиця «Постоянние_кліенти». І нам, в залежності від кількості покупок треба дати їм знижки - наприклад, ті, хто зробив більше 5 покупок, отримує знижку 6%. Вихідна таблиця має такий вигляд:
| Номер | ПІБ | Купівлі |
Оголошуються ці поля точно так само, як і звичайні, в конструкції SELECT:
SELECT (спісок_обичних_полей), (операція_с_полямі_ілі_ числа-ми) AS вичісляемое_поле FROM (спісок_табліц);
Що стосується нашої ситуації має сенс використовувати операцію IIf - Якщо. Її формат такий: IIf (умова; действіе_еслі_істіна; действіе_ еслі_ложь). Можна також використовувати вкладені умови - їх потрібно вставляти замість дій - IIf (умова; IIf (вложенное_условіе; действіе_еслі_істіна; действіе_еслі_ложь); действіе_еслі_ложь)
Наш запит буде виглядати так:
SELECT *, (IIf (Купівлі> 5; 0.06; 0)) AS Знижка From Постоянние_кліенти;
Він дасть нам джерело записів, який містить усі поля і записи таблиці «Постоянние_кліенти», а на додачу до них - вже розраховані знижки у відповідному полі.
Більш докладно про підтримуваних обчислювальних операціях можна прочитати у відповідному розділі довідки Microsoft Access.
Access підтримує також групові (підсумкові) операції. Це означає, що можна розрахувати, виходячи з набору даних, деякі статистичні та інші характеристики, такі як сума, дисперсія, математичне сподівання та інші. У запитах цим користуються не дуже часто, але користуються. Поля з груповими обчисленнями оголошуються так само, як і звичайні обчислювані поля. Наприклад, запит
SELECT Sum (Купівлі) AS Кількість FROM Постоянние_кліенти;
Поверне нам одну-єдину запис із загальним числом покупок, зроблених всіма постійними клієнтами.
Іноді для зручності аналізу даних їх необхідно групувати. Тобто якщо у джерелі записів є записи, що повторюються з яких-небудь критеріями, часом краще привести їх до однієї і вивести їх кількість.
Отже, є дві таблиці: «Асортимент», що містить список товарів, якими торгує гіпотетична фірма «Рога_і_копита», і безграмотно складена таблиця «Склад», в яку при знаходженні кожного примірника товару додавали запис. Треба зробити так, щоб дані з цих таблиць отобразалісь наочно.
Таблиця «Асортимент» містить поля «ID_товара» і «Найменування», а таблиця до «Склад» - тільки «ID_товара».
Спочатку створимо запит «Склад_гр», який згрупує повторювані записи в таблиці «Склад» і виведе кількість повторень для кожного запису. Для цього застосуємо групову операцію Count і конструкцію GROUP BY.
У загальному вигляді угруповання виглядає так:
SELECT (спісок_полей) FROM (спісок_табліц) GROUP BY (спісок_полей);
У нашому випадку ми отримаємо такий запит:
SELECT ID _товара, Count (ID _товара) AS Кількість FROM Склад
GROUP BY ID _товара;
Цей запит дасть нам вже згруповану таблицю, і ми легко зможемо побачити, скільки примірників кожного товару лежить на складі. Але тепер бажано було б переглянути найменування товарів, що лежать на складі. Для цього скористаємося вже знайомої операцією INNER JOIN.
SELECT Кількість FROM Склад_гр INNER JOIN Асортимент ON (Склад_гр. ID _товара = Асортимент. ID _товара);
Повернений таким запитом джерело записів буде мати наступну структуру:
| Кількість | ID_товара | Найменування |
2.5. Сортування отриманих результатів
Для того, щоб зробити висновок впорядкованим, мова SQL пропонує використання конструкції ORDER [ASCEND | DESCEND] BY (полі). ASCEND / DESCEND - це напрямок сортування - за зростанням або спаданням, відповідно. Модернізуємо наш попередній запит таким чином, щоб він при виведенні сортував дані по полю «Найменування»:
SELECT Кількість FROM Склад_гр INNER JOIN Асортимент ON (Склад_гр. ID _товара = Асортимент. ID _товара)
ORDER ASCEND BY Найменування;
На цьому закінчимо розгляд (кілька поверхневе) пропозицій SELECT.
3. Запити на додавання
Надалі ми будемо розглядати запити, які керують безпосередньо даними в таблицях, а то й самими таблицями. При їх розробці та виконанні слід дотримуватися обережності, тому що зміни, що вносяться такими запитами в дані, часто незворотні. Природно, що джерелами даних для таких запитів можуть бути лише таблиці.
Іноді, найчастіше в серверних базах даних або при використанні елементів програмування в Microsoft Access потрібно динамічно додати запис в таблицю. Мова SQL пропонує використовувати для цих цілей пропозицію INSERT INTO.
Загальна структура цього типу запитів така:
INSERT INTO Ім'я_таблиці VALUES (список значень);
Список значень завжди повинен бути рівний за кількістю елементів кількості полів в змінною таблиці. Якщо ж треба ввести тільки деякі поля, для решти 0 або NULL для строкових полів. Також можна використовувати вирази.
Наприклад, є таблиця «товари»
| ID_товара | Найменування | Кількість |
INSERT INTO Товари VALUES
(MAX (ID _Товара) +1, "Який-то товар ', 10);
Таким чином, ми зберегли порядок відліку.
Слід зауважити, що пропозиція INSERT є одним з найпростіших в SQL. Ніяких додаткових конструкцій у ньому не використовується.
4. Запити на оновлення
Іноді, знову-таки, найчастіше при використанні елементів програмування, може знадобитися динамічна зміна всіх або частини записів у таблиці. Для цього застосовується пропозицію UPDATE.
Припустимо, зі складу раз на місяць здійснюється відвантаження. Відвантажують по одному артикулу кожного товару. Якщо якийсь товар на складі відсутня, його, відповідно, не відвантажують.
Загальна структура запитів на оновлення:
UPDATE ім'я_таблиці SET
(Список значень або список виразів «Поле = значення»);
За замовчуванням UPDATE накладає зміни на всі записи таблиці. Щоб обмежити кількість уражених записів, слід використовувати конструкцію WHERE так само, як і в запитах на вибірку.
Наш запит на відвантаження буде виглядати так:
UPDATE Товари SET (Кількість = Кількість - 1)
WHERE (Кількість> 0);
Він-то і виконає всі необхідні дії по обробці результатів відвантаження.
5. Запити на видалення
Трапляються ситуації, коли дані в таблицях застарівають. Припустимо, є база фінансової звітності підприємства. І документи, які були введені туди до 1 січня 2004 року, треба видалити.
Для цього використовується пропозицію DELETE.
Структура таблиці «Документи»
| Номер | Назва | Текст | Дата_регістраціі |
DELETE * FROM ім'я_таблиці WHERE (список умов);
У нашому випадку запит буде мати наступний вигляд:
DELETE * FROM Документи
WHERE (Дата_регістраціі <01.01.2004);
Як бачимо, діалект мови SQL, використовуваний в Microsoft Access, досить демократичний.
6. Параметричні запити
Access дозволяє зробити запити більш гнучкими - тобто зробити так, щоб при зміні критеріїв у запиті не доводилося правити його в конструкторі. Для цього використовується механізм параметрів.
Наприклад, у нас є таблиця «Спісок_жільцов», і нам потрібно відобразити тих з них, вік яких перевищує деяку заздалегідь невідому величину. Для цього використовуємо запит
SELECT * FROM Спісок_жільцов WHERE (Вік> Крітерій_ віку);
При спробі відкриття такого запиту Access в спеціальному діалоговому вікні попросить ввести значення параметра «Крітерій_возраста»
Параметрів може бути скільки завгодно, але майте на увазі, що користувачеві для відкриття такого запиту доведеться відповісти на відповідну кількість діалогових вікон.
7. Керуючі запити ядра Microsoft Jet
Ця невелика група запитів дозволяє робити операції на рівні як окремої таблиці, так і бази даних в цілому.
а. Запит DROP
Цей запит дозволяє видалити з бази даних таблицю або індекс. Його структура -
DROP TABLE ім'я_таблиці
DROP INDEX ім'я_індексу
б. Запит ALTER Table
Цей запит дозволяє редагувати структуру таблиці.
Його структура:
Alter table <ім'я таблиці>; <опис зміни>
У випадку, коли треба зробити декілька змін, вони вказуються через кому. Перерахую типи змін:
ADD <опис колонки>
Дозволяє додати нову колонку в таблицю. Опис колонки таке ж, як і в Create table. Після слова ADD можна вказати слово COLUMN, яке дозволяє зробити текст запиту більш читабельним. Після опису колонки можна вказати слово FIRST, в цьому випадку колонка буде першою в таблиці, або слово AFTER, після якого вказується назва колонки, в цьому випадку навая колонка буде додана після вказаної.
Можна також вказати кілька описів колонки через кому і усередині круглих дужок. У цьому разі зазначити розташування колонки не можна.
ADD INDEX (<ім'я колонки >,...)
Дозволяє додати новий індекс. У дужках вказуються назви колонок, що входять в індекс. Між словом INDEX і дужкою можна вказати назву індексу.
ADD PRIMARY KEY (<ім'я колонки >,...)
Дозволяє додати новий первинний ключ (або змінити його). У дужках вказуються назви колонок, які входять у ключ.
ADD UNIQUE (<ім'я колонки >,...)
Дозволяє додати новий індекс з унікальними значеннями. У дужках вказуються назви колонок, які входять до індексу. Між словом UNIQUE і дужкою можна вказати назву індексу.
ADD FULLTEXT (<ім'я колонки> ...)
Дозволяє додати новий індекс із повнотекстових пошуком. У дужках вказуються назви колонок, які входять до індексу. Між словом FULLTEXT і дужкою можна вказати назву індексу.
ALTER COLUMN <ім'я колонки> SET DEFAULT <значення>
ALTER COLUMN <ім'я колонки> DROP DEFAULT
Запит дозволяє створити або видалити значення за замовчуванням для колонки. При цьому слово COLUMN можна опустити.
CHANGE COLUMN <ім'я колонки> <опис колонки>
Запит змінює зазначену колонку на нову, опис таке ж, як і в CREATE TABLE. Слово COLUMN можна опустити. В описі колонки вказується ім'я колонки, так що ім'я може бути змінено.
MODIFY COLUMN <опис колонки>
Змінює колонку на нову, опис таке ж, як і в CREATE TABLE. Слово COLUMN моно опустити.
DROP COLUMN <ім'я> - Видаляє колонку.
DROP PRIMARY KEY - Видаляє первинний ключ
DROP INDEX <ім'я індексу> - Видаляє індекс
RENAME TO <нове ім'я таблиці> - Перейменовує таблицю
в. Запит CREATE TABLE
Цей запит дозволяє динамічно створити нову таблицю в базі даних. Наприклад:
У наступному керуючому запиті за допомогою інструкції CREATE TABLE створюється таблиця «Друзі». Наведена інструкція визначає імена і типи даних для полів таблиці і створює для поля «Код» індекс, хто чинить таке поле ключовим.
CREATE TABLE Друзья ([Код] integer, [Фамилия] text, [Имя] text, [ДатаРождения] date, [Телефон] text, [Заметки] memo, CONSTRAINT [Индекс1] PRIMARY KEY ([Код])); 8. Формування і виконання запитів у реальному часі (інструментарій Visual Basic For Applications)
Для того, щоб запрограмувати автоматизацію дій з запитами, Microsoft Access пропонує два варіанти:
а. Використання об'єктів доступу до даних (DAO)
б. Використання об'єкта-дії DoCmd.
Перший шлях дозволяє нам створювати збережені запити в базі даних (вони будуть з'являтися у вкладці «Запити») або ж працювати з їх результатами безпосередньо. Об'єкт DoCmd, а точніше, його метод RunSQL використовується виключно для запуску запитів на зміну даних або керуючих запитів MS Jet.
Покажемо загальні прийоми створення і виконання запитів за допомогою DAO. Для цього будемо використовувати об'єкт Application.CurrentDB і, для створення / видалення збережених запитів - колекцію Queries, в цьому об'єкті зберігається. Для роботи з даними в реальному часі застосуємо об'єкт Recordset.
За основу візьмемо запити на вибірку, показані в розділі 2 даного реферату.
Option Compare Database
Option Explicit
Public Sub QRY_Example1 ()
Dim s As String
S = "SELECT Кількість FROM Склад _ гр INNER JOIN" + _ "Асортимент ON (Склад _ гр. ID _товара = Асортимент. ID_ товару)" + _
"ORDER ASCEND BY Найменування;"
Application.CurrentDb.CreateQueryDef ("Query1", s)
End Sub
Така процедура створить в базі даних новий запит з ім'ям Query1 і текстом, що зберігається у змінній S. А наступна - видалить його:
Public Sub QRY_Example2 ()
Application.CurrentDb.QueryDefs.Delete ("Query1")
End Sub
Тепер покажемо, як працювати з отриманими із запиту даними безпосередньо (ця процедура виведе повідомлення, в яких перерахує кількість і найменування всіх товарів):
Public Sub QRY_Example3 ()
Dim s As String
Dim rst As New Recordset
S = "SELECT Кількість FROM Склад _ гр INNER JOIN" + _ "Асортимент ON (Склад _ гр. ID _товара = Асортимент. ID_ товару)" + _
"ORDER ASCEND BY Найменування;"
set rst = Application.CurrentDb.OpenRecordset (s, dbOpenDynaset, dbReadOnly)
rst.MoveFirst
for I = 1 to rst.RecordCount do
MsqBox "На складі зберігається" + str (rst. Fields ("Кількість")) + "" + rst. Fields ("найменування")
Rst. MoveNext
Next
End Sub
З приводу властивостей і методів об'єктів доступу до даних докладні відомості можна отримати у вбудованій довідці VBA.
З об'єктом DoCmd все набагато простіше:
Public Sub QRY_Example4 ()
Dim s As String
Dim s2 As String
Dim rst As New Recordset
S = InputBox "Введіть найменування товару", "Наша_БД"
S 2 = InputBox "Введіть кількість цього товару", "Наша_БД"
S = "INSERT INTO Товари VALUES" + _
"(MAX (ID_ Товару) +1," + "'" + s + "'," + s2 + ");"
DoCmd. RunSQL s
End Sub
Ця процедура в запросить в діалоговому вікні ввести найменування товару і його кількість, а потім додасть ці значення у вигляді нового запису в таблицю «Товари».
На цьому демонстрацію можливостей ядра Microsoft Jet в області виконання SQL-запитів можна вважати закінченою.
9. Висновки
У даному рефераті були розглянуті основні типи запитів, які підтримуються Microsoft Access - запити на вибірку, додавання, видалення і оновлення записів і керуючі запити. Також висвітлена тема динамічного формування і виконання запитів SQL засобами Visual Basic for Applications.
10. Список літератури:
1. Microsoft Access 2002 Bible - Cary Prague and Michael Irwin - NY: Hungry Minds, 2002.
2. Mirosoft Access 2002 Help system © 1991-2002 Microsoft Corp.
3. Alter table - лекція © RSC-Team 2001-2005 (http://rsc-team.ru/bk/index.pl?rzd=2&group=lection&ind=103)