Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
posibnyk_informat.doc
Скачиваний:
0
Добавлен:
01.04.2025
Размер:
8 Мб
Скачать
☆

Лабораторна робота № 30

Тема. MS Access. Мова SQL.

Мета. Навчитися створювати запити мовою SQL.

План

1. Призначення мови SQL.

2. Створення таблиць за допомогою мови SQL.

3. Зміна структури таблиці.

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

4. Пошук даних у базі даних.

5. Застосування команди SELECT.

Теоретичні відомості

1. Призначення мови SQL. За допомогою команд мови можна створити об’єкти бази даних, наприклад таблиці, модифікувати структуру раніше створених об’єктів, побудувати запити до бази даних на вибірку записів, у тому числі параметричні, перехресні, на створення нової таблиці, на обчислення в таблицях тощо.

Щоб перейти в режим SQL, потрібно відкрити будь-яку базу даних або під час створення нової бази даних перейти на закладку Запити й виконати команду Створити запит за допомогою конструктора. Після цього переходимо в режим SQL за допомогою команд із головного меню Вигляд / Мова SQL. Отримаємо вікно для побудови запиту засобами мови, куди вводять речення мови.

2. Створення таблиць за допомогою мови SQL. Для створення нової таблиці слід увести команду CREATE TABLE (поле 1 тип 1, поле 2 тип 2, ...).

Назву таблиці й полям дає користувач. Типи полів такі: integer – цілі числові дані; char або text(n) – текстові дані, де п – довжина даного; real – короткі дійсні числові дані; float – довгі дійсні числові дані; binary(n) – логічні дані зі значеннями true, false зі зазначенням довжини даного; bit – логічні дані зі значеннями 1, 0; counter – лічильник; datetime – дата й час; money – грошові дані; image – картинка.

Одне з полів можна зробити ключовим, дописавши після назви його типу службові слова PRIMARY KEY.

Якщо назва таблиці чи поля містить пропуск чи інший розділовий символ, то її беруть у квадратні дужки, наприклад, CREATE TABLE Склад1 ([Номер товару] integer, [Назва товару] text(30), Ціна real, Кількість integer, Імпорт bit, Картинка image);

3. Зміна структури таблиці. Модифікувати структуру таблиці можна такою командою: ALTER TABLE таблиця дія COLOMN поле тип.

Над полем визначені такі дії: ADD – додати, DROP – вилучити, ALTER – змінити тип поля.

Щоб вилучити всю таблицю з БД, виконують команду DROP TABLE таблиця.

Додавати запис у кінець створеної таблиці можна так: INSERT INTO таблиця (поле 1, поле 2,...) VALUES (значення 1, значення 2, ...).

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

UPDATE таблиця SET поле = вираз WHERE умова.

Приклад 1. Внести зміни в таблицю Склад 1, якщо ціни телевізорів зменшилася на 10 %.

Розв’язок:

UPDATE Склад1 SET Ціна = Ціна *0.9

WHERE Назва = “телевізор”.

Записи вилучають із таблиці за допомогою команди:

DELETE FROM таблиця WHERE умова; значення поля можна вилучати матодом створення запиту на оновлення поля значенням Null.

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

Вибірку записів створюють за допомогою конструкції SELECT, що має такий загальний вигляд:

SELECT предикат список полів;

FROM таблиця {IN адреса зовнішньої БД на диску};

{WHERE умова пошуку};

{GROUP BY поле для групування записів};

{HAVING додаткова умова пошуку в групі};

{ORDER BY список полів для впорядкування).

Команди, записані у фігурних дужках, є необов’язковими.

Предикатом може бути службове слово ALL (показати всі незнай­дені записи), TOP n – показати перші п записів, TOP n PTRCENT – показати перші n % записів, DISTINCT – показати лише різні записи.

Список полів містить назви полів, які відображатимуться у вибірці. Елементи списків відокремлюються комами. Усі поля тaблиці позначають так: таблиця.* або *, якщо розглядається одна таблиця.

Джерелом даних може бути одна або декілька таблиць із поточної чи зовнішньої бази даних.

Назви полів, якщо вони трапляються в різних таблицях, мають бути доповнені назвами таблиць. Між двома назвами має cтояти крапка, наприклад, Склад1. Ціна, Товари. Ціна.

Якщо таблиць декілька, то їх оголошують списком у команді FROM. Якщо між двома таблицями є внутрішній зв’язок по деякому полю, наприклад [Номер товару], то команду МІОМ потрібно писати так:

FROM таблиця1 INNER JOIN таблиця2

ON таблиця1. [Номер товару]=таблиця2. [Номер товару].

Команда ORDER BY призначена для впорядкування отриманих записів у алфавітному чи зворотному порядку. Відповідний напрямок упорядкування задається предикатами ASC чи DESC, що записуються після назви поля.

Приклад 2. Із таблиці Склад 1 вибрати перші три записи з усіма полями про товари, ціна яких менша від 100 грн.

Розв’язок:

SELECT TOP 3 *

FROM Склад1

WHERE Ціна < 100.

5. Пошук даних у базі даних. Команда WHERE є найважливішою в конструкції SELECT. Власне вона призначена для відбору потрібних записів згідно з деяким критерієм (умови пошуку). Умова пошуку – це простий або складений логічний вираз. Простий логічний вираз – це два вирази, з’єднані oneрацією відношення: =, <, >, <=, >=. Відношення “не рівно” позначається <> або !=.

Вирази можуть містити назви полів і сталі величини, з’єднані допустимими операціями. Складені логічні вирази утворюють із простих за допомогою логічних операцій NOT, AND, OR, XOR та інших так, як це прийнято в мовах програмування Visunl Basic чи Qbasic. Розглянемо приклади умов пошуку:

1) [Назва товару]=“телефон”;

2) [Дата виготовлення]=#5/21/05#;

3) NOT Продавець=“Афоня” або Продавець != “Афоня”;

4) Мат > 3 AND Фіз > 3 AND Інформ > 3;

5) [Ціна товару] > 20.5 AND [Ціна товару] < 85.

Для останнього прикладу є зручніша конструкція, компактніша, якщо назва поля довга:

[Ціна товару] BETWEEN 20.5 AND 85.

Усі дати за останні 30 днів:

Дата BETWEEN day() - 30 AND day().

Дати слід писати в американському стилі місяць / день / рік.

Для пошуку даних у текстових полях корисною є операція порівняння Like.

Приклад 3. Із деякої таблиці вибрати всі записи про фірми, назви яких починаються літерою “Д” і які є в містах, назви яких починаються від літери “А” до літери “Ж”, погрупувавши фірми за містами, а міста впорядкувавши за алфавітом.

Розв’язок:

SELECT; *

FROM [Якась таблиця];

WHERE Фірма Like “Д*” AND Місто Like [А-Ж];

GROUP BY Місто;

ORDER BY Місто.

Умови конструюють так само, як і в QBE, з тією відмінністю, що в логічних виразах SQL є назви полів.

Наведену вище конструкцію SELECT призначено для створення запитів на вибірку записів із БД.

6. Застосування команди SELECT. Розглянемо такі застосування команди SELECT: 1) для статистичних обчислень у вибірці; 2) для побудови параметричних запитів; 3) для створення нового обчислю­вального поля; 4) запиту на створення нової таблиці; 5) запиту на додавання записів.

1. Статистичні функції. Команду SELECT використовують для обчислення й виведення на екран значень статистичних функцій у полях вибірки. Перше речення в конструкції має бути таким:

SELECT список функцій.

Розглянемо деякі статистичні функції:

Соunt(поле) дає кількість записів, де це поле не порожнє (тобто є NOT Null, де Null – ознака порожнього поля);

Sum(поле) обчислює суму всіх числових значень поля;

First:(поле), Last(пoлe) – значення полів у першому й останньому записах таблиці;

Міп(поле), Мах(поле) – мінімальне та максимальне значення в цих полях;

Аvg(поле) – середнє арифметичне значення поля;

Stdev(ПОЛЕ), Stdevp(ПОЛЕ) – середньоквадратичні відхилення в полі.

Приклад 4. Визначити середню ціну товарів, які є на складі:

SELECT Аvg(Ціна) AS t

FROM Склад1;

Результат t потрібно переписати з екрана.

2. Запит із параметром.

Щоб вивести на екран вибірку всіх товарів, ціна яких менша від середньої, потрібно виконати такий триметричний запит:

SELECT *

FROM Склад1

WHERE Ціна <z.

Якщо деяка назва в команді WHERE не збігається з назвою поля чи поточної бази даних, то її розглядають як назву Параметра в параметричному запиті. Відкриється вікно, куди слід увести значення параметра (у нашому випадку це значення беремо з попереднього прикладу – t). Зверніть увагу: у команді WHERE застосовувати статистичні функції не можна.

3. Запит на створення нового обчислювального поля. Наступне застосування команди SELECT – це створення нового Обчислювального поля, наприклад Сума, так:

SELECT Ціна, Кількість, Ціна*Кількість AS Сума

FROM Склад1.

Якщо обчислення містить розгалуження, то використовують функцію IF – “якщо”. Загальний вигляд функції IF такий: ІІF(умова, значення 1, значення 2), де значення 1 надається полю, якщо умова істинна, а значення 2, якщо – хибна. Значення в полі може змінюва­тися залежно від деякої умови. Наприклад, якщо деяке числове поле Кількість у вибірці не містить жодного значення, то його можна продублювати полем Нова Кількість так, щоб на місці порожніх значень поля було число 0:

SELECT Ціна, Кількість, IIF(Кількість IS NULL,O, Кількість) AS

НоваКількість

FROM Склад1.

4. Запит на створення нової таблиці. Нову таблицю можна створити за допомогою такого запиту:

SELECT список полів таблиці-джерела

INTO нова таблиця {IN зовнішня БД}

FROM таблиця-джерело {WHERE умова}.

5. Запит на додавання записів.

Запит на додавання записів у таблицю 1 з іншої таблиці 2 має такий вигляд:

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]