Лабораторная работа по ACCESS №4. Создание SQL - запросов
для доступа к реляционной базе данных.
Использование языка запросов SQL. Основные операторы языка.
Общепринятый термин «язык запросов» не совсем точно отражает рассматриваемое понятие, поскольку слово «запрос» подразумевает лишь выборку, в то время как с помощью этого языка выполняются также операции создания, обновления, вставки и удаления.
К числу основных операторов SQL относятся следующие операторы:
-
CREATE TABLE – создание таблицы.
-
SELECT, UPDATE, INSERT, DELETE – выборка, изменение, вставка и удаление записей.
Использование языка запросов SQL в среде ACCESS.
Применение SQL-инструкций рассмотрим на примере базы данных «Кадры», включающей в себя одну таблицу RABOTNIKI - работники предприятия.
Поля таблицы:
TAB_NOM – табельный номер сотрудника - строка символов длиной 5,
NAME –имя сотрудника – строка символов длиной 30 символов,
OKLAD – оклад сотрудника – числовое данное,
ADRESS – адрес сотрудника – строка длиной 100 символов.
Создание таблиц
Оператор SQL для создания таблицы имеет следующий вид:
CREATE TABLE БАЗОВАЯ_таблица(Столбец_1 тип_данных, столбец_2 тип_данных, ….);
При создании таблиц для отдельных полей могут указываться некоторые дополнительные правила контроля вводимых в них значений. Конструкция NOT NULL - ( не пустое) служит именно таким целям и для столбца таблицы означает, что в этом столбце должно быть определено значение.
Пример_1: Создайте таблицу RABOTNIKI для базы данных «Кадры».
-
Создайте новую базу данных с названием Кадры.mdb.
-
Перейдите в режим создания запроса конструктором ЗАПРОСЫ/ СОЗДАТЬ/ КОНСТРУКТОР. Закройте окно добавления таблиц и затем, выполните команды меню Запрос – Запрос SQL – управление.
-
В открывшемся окне наберите соответствующую инструкцию на создание таблицы RABOTNIKI:
CREATE TABLE RABOTNIKI
(TAB_NOM CHAR(5) not null,
NAME CHAR(30),
OKLAD NUMERIC,
ADRESS CHAR (100),
PRIMARY KEY (TAB_NOM));
Примечание: Последнее предложение назначает ключевой параметр.
-
Сохраните запрос под именем Создать таблицу работников. Выполните созданный запрос и проверьте наличие таблицы «RABOTNIKI».
-
Заполните созданную таблицу данными (15-20 строк).
При построении запроса в окне конструктора система Access работает в фоновом режиме, записывая эквивалентные инструкции SQL. Для просмотра программы SQL в меню Вид выберите команду Режим SQL .
Запросы на выборку.
В упрощенном виде важнейший оператор SELECT имеет следующий формат:
SELECT [ ALL\DISTINCT] <список данных>
FROM <список таблиц >
[WHERE <условие отбора>]
[GROUP BY <имя столбца> [, <имя столбца>]...]
[HAVING <условие поиска>]
[ORDER BY <спецификация сортировки> [,<спецификация сортировки>]...]
Оператор SELECT позволяет выполнять выборку и вычисления над данными одной или нескольких таблиц. Результатом выполнения оператора является ответная таблица, которая может иметь (ALL) или не иметь (DISTINCT) повторяющиеся строки.
В списке данных можно задавать имена столбцов и выражения над ними, к примеру, арифметические. Если записи отбираются из нескольких таблиц, то используют составные имена <имя таблицы>.<имя столбца>.
Рассмотрим более подробно оператор выборки SELECT на конкретных примерах.
Пример_2. Получите упорядоченный список работников с окладом более 10000.
-
Перейдите в режим создания запроса конструктором ЗАПРОСЫ/ СОЗДАТЬ/ КОНСТРУКТОР. Закройте окно добавления таблиц и затем, выполните команды меню Запрос – Запрос SQL – управление.
-
В открывшемся окне наберите текст оператора создаваемого запроса на выборку:
SELECT RABOTNIKI.NAME, RABOTNIKI.OKLAD
FROM RABOTNIKI
WHERE (((RABOTNIKI.OKLAD)>=10000))
ORDER BY RABOTNIKI.NAME;
-
Проанализируйте инструкцию построенного запроса:
-
инструкции SELECT - определяет возвращаемую информацию как набор записей.
-
параметр FROM - указывает, какая таблица содержат поля, приведенные в инструкции SELECT.
-
параметр WHERE устанавливает условие отбора записей таблицы – предикат вида (RABOTNIKI.OKLAD)>=10000
-
Операция ORDER BY производит сортировку всех полей списка SELECT по полю NAME
-
Сохраните запрос под именем Высокооплачиваемые, и затем выполните запрос.
Пример_3. Получите упорядоченный список работников проживающих в Смоленске.
-
Перейдите в режим создания запроса конструктором ЗАПРОСЫ/ СОЗДАТЬ/ КОНСТРУКТОР. Закройте окно добавления таблиц и затем, выполните команды меню Запрос – Запрос SQL – управление.
-
В открывшемся окне наберите текст оператора создаваемого запроса на выборку с сортировкой:
SELECT RABOTNIKI.NAME, RABOTNIKI.ADRESS
FROM RABOTNIKI
WHERE (((RABOTNIKI.ADRESS) Like "*Смоленск*"))
ORDER BY RABOTNIKI.NAME;
-
Проанализируйте инструкцию построенного запроса:
-
инструкции SELECT - определяет возвращаемую информацию как набор записей.
-
параметр FROM - указывает, какая таблица содержат поля, приведенные в инструкции SELECT.
-
параметр WHERE содержит предикат Like, устанавливающий отбор записей таблицы, в которых поле ADRESS содержит подстроку СМОЛЕНСК.
-
Операция ORDER BY производит сортировку всех полей списка SELECT по полю NAME
-
Сохраните запрос под именем Смоляне, и затем выполните запрос.
ГРУППОВОЙ ЗАПРОС
Пример_4. Получите сумму окладов всех работников.
-
Перейдите в режим создания запроса конструктором ЗАПРОСЫ/ СОЗДАТЬ/ КОНСТРУКТОР. Закройте окно добавления таблиц и затем, выполните команды меню Запрос – Запрос SQL – управление.
-
В открывшемся окне наберите текст оператора создаваемого группового запроса на выборку:
SELECT Sum(RABOTNIKI.OKLAD) AS [Сумма всех окладов]
FROM RABOTNIKI;
В данном запросе выполняется операция Sum – суммирование всех значения поля OKLAD. Параметр AS определяет название колонки, соответствующей сумме.
-
Сохраните запрос под именем Итого по окладам, и затем выполните запрос.
ЗАПРОС НА ОБНОВЛЕНИЕ
Запросы на обновление изменяют значения полей на основании определенных критериев.
Пример_5. Увеличьте оклады всех работников на 20%.
-
Перейдите в режим создания запроса конструктором ЗАПРОСЫ/ СОЗДАТЬ/ КОНСТРУКТОР. Закройте окно добавления таблиц и затем, выполните команды меню Запрос – Запрос SQL – управление.
-
В открывшемся окне наберите текст оператора создаваемого запроса на обновление данных таблицы:
UPDATE RABOTNIKI SET RABOTNIKI.OKLAD = 1.2 * RABOTNIKI.OKLAD;
-
Сохраните запрос под именем Увеличить оклады, и затем выполните запрос и проверьте содержимое таблицы с увеличенным окладом.
Пример_6. Увеличьте оклады всех работников на 20%, увеличивая оклады только в том случае, если они не превышают 50000 рублей:
-
Внесите в предыдущий запрос условие отбора WHERE:
UPDATE RABOTNIKI SET RABOTNIKI.OKLAD = 1.2 * RABOTNIKI.OKLAD WHERE (RABOTNIKI.OKLAD <= 50000);
-
Испытайте видоизмененный запрос.
ЗАПРОС НА ДОБАВЛЕНИЕ
Запрос на добавление добавляет одну или более записей в конец таблицы.
Пример_7. Добавьте в таблицу RABOTNIKI сведения о новой работнице с табельным номером 777 по фамилии САМОЙЛОВА, окладом 20000 и адресом СМОЛЕНСК, ул. ТВАРДОВСКОГО, 1
-
Перейдите в режим создания запроса конструктором ЗАПРОСЫ/ СОЗДАТЬ/ КОНСТРУКТОР. Закройте окно добавления таблиц и затем, выполните команды меню Запрос – Запрос SQL – управление.
-
В открывшемся окне наберите текст оператора создаваемого запроса на обновление данных таблицы:
INSERT INTO RABOTNIKI VALUES ("777",
" Самойлова", 20000, "Смоленск, ул.Твардовского, 1");
-
Сохраните запрос под именем Добавить работника, и затем выполните запрос и проверьте содержимое таблицы с новым работником.
Задание для самостоятельной работы
-
Создайте новую, без таблиц БД в среде Access.
-
Используя только SQL- запросы:
-
Создайте таблицу сведений о песнях исполнителей или книгах авторов или марках автомобилей, содержащие ключевое поле, числовые и строковые поля.
-
Заполните таблицу данными.
-
Создайте 3-4 запроса на выборку данных, содержащие предикаты, сортировку данных и групповые операции.
-
Создайте запросы на обновление и добавление данных.
-
Создайте формы и отчеты для таблицы и всех запросов БД.