Створення запитів на прикладі бази даних

ДЕРЖАВНИЙ  ПРОФЕСІЙНО- ТЕХНІЧНИЙ НАВЧАЛЬНИЙ ЗАКЛАД

«ВІННИЦЬКЕ МІЖРЕГІОНАЛЬНЕ ВИЩЕ ПРОФЕСІЙНЕ УЧИЛИЩЕ»

КУРСОВА РОБОТА

на  тему:

Створення запитів на прикладі бази даних

«Агенство нерухомості»

Виконала:                                                          Татаринцева Софія  Максимівна

учениця  I курсу, групи № 16

професія : 41 12 (ІІ категорія)

«Оператор комп’ютерного набору»

Керівник                                                                        
                        

Викладач:                                                          Фурман  А.В.

Вінниця    -  2016

Зміст

Вступ

1. Створення запитів  на прикладі бази даних на прикладі бази даних «Агенство не рухомості».

1.1. Поняття   запиту

1.1.1. Загальні відомості про запити

1.2. Способи створення   запитів  в MS  Access

1.3.  Запити  вибірку

1.3.1. Загальні відомості про запити на вибірку

1.3.2.  Створення запитів на вибірку

1.4. Конструктор запитів

1.4.1.   Проектування  запиту

1.4.2. Вікно запиту.

1.4.3.  Основні  операції в запиті.

1.4.5.  Виконання  запиту.

1.4.6.  Збереження    запиту.

1.4.7  Редагування  запиту.

1.5. Перехресний запит.

1.6. Параметричний запит.

1.7. Запити дії.

1.7.1. Запит на додавання.

1.7.2. Запит на видалення.

1.7.3. Запит на оновлення.

1.7.4. Запит створення таблиць.

Висновок

Список використаної літератури

Додатки

1.1.Поняття запиту.

Для вибору записів із таблиць бази даних, що задовольняють певним умовам, можна скористатися запитами. Наприклад, за допомогою запиту можна одержати інформацію про країни, в яких проживає понад 5 млн жителів; або дані про заповідники, створені після 1950 р., тощо. Запити нагадують розглянуті фільтри, але вони надають значно більше можливостей.

Запит — це об’єкт системи управління базами, призначений для відбору даних із пов’язаних таблиць, які задовольняють заданим умовам. Узагалі запит є одним з основних інструментів відбору та опрацювання даних у СУБД. Саме за допомогою запитів можна легко знайти потрібні дані, виконати обчислення над полями таблиць, згрупувати записи, що мають однакові значення в окремих полях тощо. У СУБД Access можна створити такі типи запитів: запит на вибирання, за допомогою якого із таблиць бази даних вибираються дані, які задовольняють уведеним умовам. Результати виконання цього запиту відображаються у вигляді таблиці. Таблиця з результатами запиту може бути використана при подальшій роботі з базою даних, наприклад як джерело даних при побудові іншого запиту, форми чи звіту; запит на перетворення таблиць передбачає вибір із таблиць бази даних тих записів, що задовольняють уведеній умові з метою подальшого редагування цих записів або іншої таблиці. На відміну від попереднього типу, в результаті виконання запиту на перетворення змінюється вміст початкових таблиць. У СУБД Access можна виконати такі перетворення: створення й поновлення таблиць, доповнення їх новими записами та видалення записів. Тип запиту вибирають за допомогою пункту меню Запит у режимі конструктора.

1.2. Способи створення запитів в MS Access.

Запит можна створити за допомогою майстра або в режимі конструктора. Як завжди, найбільш простий спосіб побудови запиту передбачає використання майстра.Режим конструктора (Query Design) дозволяє задавати не лише умови вибору даних, але і порядок сортування. Запити зберігаються як окремі об"єкти і відображуються у вікні бази даних.

Створення простого запиту за допомогою майстра

Для відкриття вікна майстра запиту у вікні бази даних на панелі Об"єкти (Objects) виберіть значок Запити (Queries) і двічі клацніть значок Створення запиту за допомогою майстра (Create Query By Using Wizard) (див. мал. 24.1). Інший спосіб відображення майстра запиту описаний нижчим в розділі «Створення багатотабличних запитів».
У вікні Створення простих запитів (Simple Query Wizard) у списку, що розкривається Таблиці і запити (Table/Query) виберіть таблиці і запити, поля яких використовуватимуться в запиті (мал. 24.4). У списку Доступні поля (Available Fields) двічі клацніть імена використовуваних в запиті полів. Ці поля перемістяться в список Вибрані поля (Selected Fields) . Натискуйте кнопку Далі (Next) .

Мал. 24.4 Вікно майстра створення простих запитів

При створенні запиту по декількох таблицях в другому вікні положенням перемикача вам треба вибрати детальний (виведення кожного поля кожного запису) (Detail (shows every field of every record)) або підсумковий (Summary ) запит (мал. 24.5). Підсумкові запити містять поля, по яких групуються дані, і числові поля, по яких визначають, наприклад, сумарне, середнє, максимальне або мінімальне значення. У підсумковому запиті можна зробити підрахунок кількості записів в групі (Count records in). Для додавання обчислень в результати запиту натискуйте кнопку Підсумки (Summary Options) і виберіть потрібні поля або встановите прапорець Підрахунок числа записів в Підсумки (Count records in Quarterly Orders by Product).

Мал. 24.5 Вибір типа створюваного звіту

У наступному вікні майстра вам буде запропоновано дати ім"я запиту і положенням перемикача визначити подальші дії: Відкрити запит для перегляду даних (Open The Query To View Information) або Змінити макет запиту (Modify the query design). Після того, як ви натискуватимете кнопку Готово (Finish) відкриється запит в режимі таблиці.

Створення запиту в режимі конструктора

Створення запиту в режимі конструктора надає великі можливості в порівнянні з майстром простого запиту.
У діалоговому вікні Новий запит (New Query) виберіть в списку в правій частині вікна елемент Конструктор (Design View) і натискуйте кнопку OK (мал. 24.6). На екрані відображуватиметься вікно запиту в режимі конструктора і діалогове вікно Додавання таблиці (Show Table) (мал. 24.7)яке дозволяє вибрати, по яких таблицях і запитах або їх комбінації буде створений новий запит. Вікно запиту розділене посередині по горизонталі. У верхній частині вікна відображують списки доступних в запиті полів всіх таблиць, в нижней — специфікація запитів. Кожен стовпець відображує поле, використовуване для виділення тих записів, які будуть включені в запит.

Мал. 24.6 Вибір методу створення запиту

Мал. 24.7 Вибір таблиці, для якої буде складений запит

На вкладці Таблиці (Tables) діалогового вікна Додавання таблиці вкажіть назва вихідної таблиці. Натискуйте кнопку Додати (Add) аби додати список полів цієї таблиці у верхню частину вікна запитів. Для додавання полий таблиці в запит можна двічі клацнути її ім"я.
При складанні запиту по декількох таблицях виберіть необхідні таблиці, а потім натискуйте кнопку Закрити (Close). На екрані відображуватиметься вікно, що дозволяє виконати налаштування запиту в режимі конструктора (мал. 24.8). У нижній частині вікна відображує порожній бланк запиту, який призначений для визначення запиту або фільтру в режимі конструктора запиту або у вікні розширеного фільтру.

Мал. 24.8 Вікно структури запиту: 1 — список полів, 2 — бланк запиту

У тих випадках, коли вас цікавлять записи, що відповідають певній умові, наприклад, прізвища клієнтів, що зробили замовлення дорожче певної суми, застосовують запит за зразком. При створенні запиту ви даєте, як би зразок (QBE, query by example — запит за зразком ), по якому буде складена відповідь або виконані операції. Тому в більш версіях Access використовувався термін бланк запиту за зразком (QBE).
Запити складаються в інтерактивному режимі. Включите в запит таблиці і поля, які вас цікавлять, вкажіть умови вибору записів і які обчислення необхідно виробити. Включити поле в запит можна наступними способами:

§      у рядку Поле (Field) поставити курсор в потрібний стовпець і двічі клацнути ім"я поля в списку. Натискувати клавішу стрілка-управо або клавішу Tab, перемістити курсор в наступний стовпець і додати нове поле в запит;

§      клацнути на кнопці розкриття списку біля правого кордону вічка в рядку Поле (Field) або натискувати клавішу F4, аби відкрити список імен полів і вибрати необхідне поле;

§      перетягнути ім"я поля мишею із списку полів у верхній частині вікна. Для введення декілька полів із списку можна утримувати клавішу Ctrl при виділенні потрібних полів в списку, а потім перетягнете виділені поля мишею. Access автоматично розподілить вибрані поля по вічках рядка

Поле. Якщо вибрати в першому рядку зірочку «*», то це означає, що відповідає мають бути представлені всі стовпці таблиці.
Для виконання сортування результатів запиту по якому-небудь полю перемістите курсор в рядок Сортування (Sort) цього поля і натискуйте клавішу F4, для відображення варіантів сортування: за збільшенням (Ascending), по убуванню (Descending) або відсутній (not sorted). Детальніше вибір умов сортування далі в цій главі.
Якщо ви в запиті використовуєте яке-небудь поле для вказівки умов відбору, але не хочете, аби воно відображувало відповідає на запит, то зніміть прапорець в цьому полі в рядку Вивід на екран (Show) .
Якщо ви хочете використовувати текстовою критерій відбору, то введіть необхідний текст в рядок Умова відбору (Criteria). Наприклад, введіть в цей рядок слово фрукти для з"ясування, на яку суму проданий саме цей товар. Після натиснення клавіші Enter слово буде поміщено в лапки. Для кожного поля, яке передбачається використовувати як параметр, введіть у вічко рядки Умова відбору (Criteria) текст запрошення, ув"язнений в квадратні дужки. Це запрошення виводитиметься при запуску запиту. Текст запрошення повинен відрізнятися від імені поля, але може включати його.
За бажання користувач може змінити або уточнити запит, побудувати за результатами запиту графіки. Детальніше вибір умов відбору записів в запиті розглянутий нижче.

1.3. Запити на вибірку.

Запити на вибірку використовуються для відбору потрібної користувачеві інформації, що міститься в таблицях. Вони створюються тільки для зв’язаних таблиць.

У запитах на вибірку можна задавати умови. Для цього у відповідному стовпці потрібно вказати в рядку Условие отбора потрібне значення. При завданні умови відбору можна використовувати оператори >, <, =, >= (більше або рівно),<= (менше або рівно), <>(не рівно).

Якщо потрібно задати кілька умов, то умови для оператора «И» записуються  в одному рядку, а для оператора «ИЛИ» в одному стовпці один під одним. Складні умови відбору також можна писати в одну чарунку, використовуючи оператори Or, And.

Оператор LIKE, дозволяє робити відбір по фрагменту рядка.

При створенні запитів часто виникає необхідність не тільки використовувати наявні поля таблиць, але й створювати на їхній основі інші поля, які обчислюються.

Наприклад, якщо в таблиці якого-небудь магазину є поле ціни на товар і поле кількості цього товару, то можна створити поле, що обчислюється, у якому буде підраховуватися загальна вартість для кожного товару шляхом перемножування значень ціни й кількості. Інакше кажучи, у полі, що обчислюється, можуть використовуватися арифметичні оператори.

При створенні такого поля на основі вмісту текстових полів, як правило, використовується операція об’єднання текстових значень, що називається конкатенацією.

1.3.2  Створення   запитів на вибірку.

  При роботі з базою даних дуже часто виникає необхідність вичленувати з усієї маси інформації, що зберігається, що тільки цікавить. У таких випадках створюється запит на вибірку. Цей засіб формуєрезультуючу таблицю, що містить тільки необхідні по умові запиту дані з вказаних базових таблиць. Його створення в Access, як і для усіх інших об"єктів, можливо в автоматичному режимі, в ручному режимі і за допомогою майстра. Ми скористаємося ручним режимом, для наочнішого вивчення внутрішньої структури запиту. Але спочатку невеликий відступ, з приводу використання мови запитів SQL.

Мова SQL (Structured Query Language - структурована мова запитів) - це стандартний набір слів англійської мови, вживаний для опису запиту до бази даних.  Кидатися вивчати його в даний момент нема необхідності.  Access автоматично транслює усі запити в послідовність символів свого власного діалекту мови SQL, іменованого Jet SQL. Але і його вивчення доки можна відкласти. Вся річ у тому, що для спрощення створення запитів є спеціальний засіб, що називається бланком запиту за зразком. З його допомогою можна в лічені хвилини побудувати запит, перетягуючи елементи запиту між відповідними вікнами. Робиться це таким чином:

·                     Переходимо на вкладку Запити головного вікна бази даних. Там доки немає жодного готового об"єкту, але є присутньою знайома нам вже кнопка Створити. Ось вона нам і потрібна.

·                     У вікні, що відкрилося Новий запит пропонуються на вибір усі можливі режими створення запиту. Вибираємо ручний режим за допомогою пункту Конструктор.

·                     Далі необхідно вибрати таблиці бази даних, на підставі яких буде побудований запит. Це робиться за допомогою вікна Додавання таблиці, що містить список усіх наявних у базі таблиць.Виділяєте потрібну таблицю і натискаєте кнопку Додати. Вікно з переліком полів відповідної таблиці з"явиться у верхній половині бланка запиту. Які саме таблиці додавати при створенні запиту, вирішує розробник бази даних. Головна вимога - вони повинні містити усі необхідні для роботи майбутнього запиту поля. Наприклад, для створення запиту Картотека мені потрібні таблиці Автор, Книги і Розділи. Кнопка Закрити дозволяє перервати процес додавання таблиць і взятися до роботи з бланком запиту.

·                     Бланк запиту за зразком розділений на дві панелі.У верхній частині розташовані списки полів доданих таблиць, в нижній частині знаходяться рядки, що визначають структуру результуючої таблиці.

§     Рядок Полі можна заповнити або за допомогою випадного списку, розташованого в полі вибору, або простим перетягуванням його назви з верхньої частини вікна. У обох випадках ім"я поля з"явиться в рядку Полі, і станеться автоматичне заповнення рядка Ім"я таблиці. Ці дії можна виконати в зворотному порядку.Спочатку вибравши з випадного списку Ім"я таблиці, а потім вибравши те, що відповідає Полі (у списку будуть перераховані тільки поля містяться у вибраній таблиці). Кожна колонка бланка відповідає одному полю результуючої таблиці. Розмістіть поля в порядку, зручному для роботи і візуального сприйняття.

§     Рядок Сортування дозволяє вибрати режим виведення значень в результуючій таблиці. Доступні три варіанти:

За збільшенням - запис з меншим значенням поля виводиться перед великим значенням. У алфавітному виді запису сортуються від А до Я і від A до Z.

По убуванню - запис з великим значенням поля виводиться перед меншим значенням. У алфавітному виді запису сортуються від Я до А і від Z до A.

Відсутній - сортування не робиться і записи виводяться в порядку їх дотримання у відповідній таблиці.

§     Прапор Висновок на екран дозволяє приховати від працюючого із запитом деякі поля.Це можливо, коли один і той же запит використовується і як запит і як джерело інформації для інших об"єктів, наприклад форм. Чи коли необхідно виконати сортування або відбір по конфіденційному полю, значення якого не можна відображати в цьому запиті. Тому для приховання поля просто зніміть відповідний прапорець.

§     Умова відбору дозволяє вказати той критерій, по якому записи вибиратимуться для включення в підсумкову таблицю.Для кожного поля можна створити свою умову відбору. Тоді запис вважатиметься таким, що задовольняє запиту, якщо усі значення відповідних полів з вказаною умовою відбору, задовольнятимуть цим умовам.

·                     Створіть усі поля запиту і вкажіть відповідні параметри сортування і умови відбору. Якщо необхідно, то методом перетягання меж, задайте ширину полів таблиці. За допомогою смуги прокрутки можна до вільних полів, якщо поточні вже заповнені.

·                      Для перевірки працездатності запиту, запустите його на виконання за допомогою кнопки Запуск панелі інструментів у верхній частині вікна програми. На екрані з"явиться результуюча таблиця, заповнена записами, що задовольняють цьому запиту. Для повернення назад до режиму конструктора скористайтеся кнопкою Вид.

·                      Для збереження запиту натисніть кнопку Зберегти, на панелі інструментів. У вікні Збереження вкажіть ім"я запиту, під яким він буде збережений на вкладці Запити, головного вікна бази даних. Потім закрийте вікно запиту і запустіть його на виконання з головного вікна. Перший запит готовий.

1.4.  Конструктор запитів

Конструктор запитів можна використовувати, як для створення нових запитів, так і для модифікації наявних. При створенні нових запитів, потрібно натиснути кнопку Создать панели инструментів вікна бази даних та у діалоговому вікні Новый запрос обрати пункт Конструктор. Щоб відкрити в режимі конструктора вже існуючий запит, оберіть його в списку та натисніть кнопку Конструктор панелі інструментів вікна бази даних.

Вікно конструктора запитів розбите на 2 частини. Верхня частина призначена для розміщення зображень таблиць або запитів, які включаються в запит. Там же у вигляді ліній відображаються встановлені між ними зв’язки. Умовно цю часть вікна конструктора запитів можна назвати зоною таблиць.

Нижню частину вікна конструктора запитів звичайно називають бланк запитів. В бланку запиту вказуються поля таблиць або запитів, які будуть брати участь в його представленні (поля вихідних таблиць або запитів, які будуть видимі при перегляді результатів запиту; поля, необхідні для задання умов вибору; поля, значення яких обчислюються на основі значень інших полів); найменування вихідних таблиць та запитів, з яких береться поле; порядок сортування; групові операції (при необхідності); умови, що накладаються на значення полів при вибірці записів.

Схема дій при створенні нового запиту. Спочатку потрібно додати до запиту необхідні таблиці або запити та встановити між ними потрібні зв’язки. Після цього в бланк запиту додають потрібні поля, встановлюють порядок сортування, вводять необхідні умови. Результати виконання запиту можна продивитись, навіть не зберігаючи її в базі даних. До речі запити та таблиці поділяють один й той самий простір імен. Це означає, що не можна зберегти запит під іменм наявної таблиці та навпаки.

Для того, щоб додати таблицю або запит в макет запиту потрібно обрати пункт Добавление таблицы меню Запрос або натиснути одноіменну кнопку панелі інструментів. В цьому вікні можна обрати одну або кілька таблиць чи запитів, і після натиснення кнопки Добавить, вони будуть додані в запит, а їх зображення з’являться в зоні таблиць. Після закриття вікна Добавление таблицы можна перейти до наступного етапу проектування запиту – встановленню зв’язків.

Зв’язки між таблицями встановлюється так само, як у вікні Схема данных. Ці зв’язки є тимчасовими і діють тільки на час виконання запиту. Якщо в запит будуть додані таблиці, між якими встановлені зв’язки на рівні баз даних, то конструктор автоматично відображатиме їх без вашої участі.

Наступний етап проектування – заповнення бланку запиту. Додати поле в бланк запиту можна двома засобами: перетягаючи при натисненій лівій кнопці миші потрібне поле з таблиці в зоні таблиць на порожнє поле бланку запиту або, обираючи з комбінованого списку найменування таблиці та поля ( в рядках Поле та Имя таблицы. Для кожного поля, вміщеного у бланк запиту, можна вказати умови, які будуть застосовуватись до значень цього поля при вибірці записів з вихідних таблиць. Ці умови задаються, починаючи з рядка Условия отбора. Умови, задані для різних полів, що знаходяться в одному рядку (Строка условий), об’єднуються в один логічний вираз за допомогою логічної операції И. Різноманітні рядки умов об’єднуються між собою із застосуванням логічної операції Или. Рядків умов можна задати скільки завгодно. В якості умов можна використовувати константи, вирази, навіть інші запити.

Після того, як розробку запиту закінчено, можна продивитись результати його дії в режимі Таблицы, або перейти до режиму SQL.,щоб вивчити отриманийSQL-оператор та, при необхідності, трохи його підредагувати.

Крім полів таблиць (запитів), добавлених в розроблений запит, можна також використовувати обчислювані поля. Для створення обчислюваних полів зручно скористатись послугами Побудівника виразів. Для його виклику потрібно в обраному полі клацнути правою кнопкою миші та з контекстного меню обрати пункт Построить …

1.4.1  Проектування запиту.

Рисунок 9.4 - Вікно конструктора запитів

Вікно конструктора запиту поділено на дві частини. У верхній частині міститься вікно таблиць і запитів, які використовуються у створюваному запиті, а нижня частина містить бланк запиту QBE, в якому задаються поля, ім’я таблиці (чи таблиць), порядок сортування, умови відбору.

Для додавання полів у запит переміщуємо їх мишкою з вікна таблиць у рядок Поле. Потім у рядку Условия отбора задаємо умови відбору у вигляді виразів. У поляхСортировка задаємо порядок сортування По возрастанию або По убыванию. В рядку Вывод на экран можна управляти виведенням на екран полів, тобто вказувати, чи виводити дане поле чи ні.

Після введення всіх параметрів необхідно закрити вікно конструктора запиту, підтвердити необхідність збереження та надати ім’я створеному запиту. На вкладці Запросыз’явиться значок нового запиту.

Серед умов, які можна вводити в запити, можна виділити:

· текстові умови;

· числові умови;

· умови для дат;

· умови з логічними операціями И та/або ИЛИ.

В текстових умовах допускається використання різних способів запису текстових виразів: Петро, «Петро», =Петро, =«Петро». Access автоматично візьме текстовий фрагмент у лапки і поставить перед ним знак рівності. Можна також використовувати оператор Like разом із символами «*» або «?», при цьому символ «*» використовується для заміни невизначеної кількості символів, а символ «?» — для заміни одного символу. Наприклад, якщо в якомусь полі умови вказати Like «Д*», то будуть відібрані всі записи, в яких значення відповідного поля починаються з літери Д; якщо вказати Like «??а*», то будуть вибрані всі записи, в яких у значенні відповідного поля третя літера буде а (наприклад, Іван, Іванов, Прага, Агафонов і т. д.).

В числових умовах допускається використання операторів відношень =, <, >, <=, >=, <>, а також службових слів between … and … Прикладами числових умов можуть бути =223,70; <=120, >=535, between 10 and 30 (від 10 до 30).

В умовах для дат дати можна вводити в різних форматах: 21/3/2008, #21/3/2008#, Март 21 2008, 21-Март-2008. Можна також вживати оператор between … and …

При використанні логічного оператора AND(И) не менше двох умов задаються у різних стовпчиках, а при використанні логічного оператора OR(ИЛИ) -не менше двох умов задаються для одного поля.

Якщо запит уже створено і він збережений на диску, його можна використовувати багаторазово, навіть якщо у відповідній таблиці будуть введені нові записи або видалені деякі зі старих записів, або внесені інші зміни. Щоб скористатися запитом достатньо у вікні бази даних перейти на вкладинку Запросы, виділити потрібний запит і клацнути мишею на кнопці Открыть. У результаті СУБД Access відобразить результати запиту в режимі таблиці.

При необхідності можна внести зміни в структуру запиту, наприклад, додати в запит або видалити з нього які-небудь поля, змінити умову добору і т.д. Для цього необхідно, насамперед, перейти в режим Конструктор, тобто викликати на екран діалогове вікно Запрос на выборку. Далі можна змінити критерії добору або засіб сортування; показати або не показати поля; додати, видалити або перенести поля. При цьому для додавання поля досить перетягнути його зі списку у верхній частині вікна в ту колонку таблиці специфікацій, у котру його необхідно вставити. Поле, що знаходилося там раніше, а також усі наступні поля перемістяться праворуч на одну колонку.

Для вилучення поля достатньо його виділити клацнувши мишкою у верхній частині поля і натиснути клавішу Delete.

Для переносу поля необхідно його виділити, а потім за допомогою мишки перенести в потрібне місце. Для зберігання відредагованого запиту достатньо закрити вікно проектування запиту і підтвердити необхідність збереження макета запиту.

Якщо запит припинив задовольняти користувача, його можна видалити. Для цього достатньо у вікні бази даних виділити потрібний запит, відпрацювати підпункт Удалить пункту горизонтального меню Правка і клацнути мишкою на кнопці Да у вікні запиту на підтвердження видалення. Зауважимо, що ніякі дані при цьому не знищуються; просто скасовується умова, що використовувалась для пошуку даних у базі даних.