Лабораторная работа № 5 «Создание sql-запросов»
Тема: Составление и исполнение запросов на языке SQL в среде СУБД MS Access.
Цель работы: создать SQL-запросы на создание таблицы, на выборку с параметрами, на обновление записей, на удаление записей, на добавление данных, на удаление таблицы, на создание индексов.
Продолжительность: 4 часа для студентов специальности 220200 и 8 часов для студентов специальности 061000.
Основы SQL
Запрос SQL — это запрос, создаваемый при помощи инструкций SQL. Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными БД.
В среде MS Access, когда пользователь создает запрос в режиме конструктора запроса (с помощью построителя запросов), MS Access автоматически создает эквивалентную инструкцию SQL. Фактически, для большинства свойств запроса, доступных в окне свойств в режиме конструктора, имеются эквивалентные предложения или параметры языка SQL, доступные в режиме SQL. При необходимости, пользователь имеет возможность просматривать и редактировать инструкции SQL в режиме SQL. После внесения изменений в запрос в режиме SQL его вид в режиме конструктора может измениться.
Некоторые запросы, которые называют запросами SQL, невозможно создать в бланке запроса. Для запросов к серверу, управляющих запросов и запросов на объединение необходимо создавать инструкции SQL непосредственно в окно запроса в режиме SQL. Для подчиненного запроса пользователь должен ввести инструкцию SQL в строку Поле или Условие отбора в бланке запроса.
Синтаксиса написания SQL-предложений:
в описании команд слова, написанные прописными латинскими буквами, являются зарезервированными словами SQL;
фрагменты SQL-предложений, заключенные в фигурные скобки и разделенные символом «», являются альтернативными. При формировании соответствующей команды для конкретного случая необходимо выбрать одну из них;
фрагмент описываемого SQL-предложения, заключенный в квадратные скобки [ ], имеет необязательный характер и может не использоваться;
многоточие …, стоящее перед закрывающейся скобкой, говорит о том, что фрагмент, указанный в этих скобках, может быть повторен;
Описание команд sql
Выборка записей
Инструкция SELECT. При выполнении инструкции SELECT СУБД находит указанную таблицу или таблицы, извлекает заданные столбцы, выделяет строки, соответствующие условию отбора, и сортирует или группирует результирующие строки в указанном порядке в виде набора записей.
Синтаксис команды:
SELECT [предикат] { * | таблица.* | [таблица.]поле_1
[AS псевдоним_2] [, [таблица.]поле_2[AS псевдоним_2] [, ...]]}
FROM выражение [, ...]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
где предикат — один из следующих предикатов отбора: ALL, DISTINCT, DISTINCTROW, TOP. Данные ключевые слова используются для ограничения числа возвращаемых записей. Если они отсутствуют, по умолчанию используется предикат ALL;
* указывает, что результирующий набор записей будет содержать все поля заданной таблицы или таблиц. Следующая инструкция отбирает все поля из таблицы «Студенты»: SELECT * FROM Студенты;
таблица — имя таблицы, из которой должны быть выбраны записи;
поле_1, поле_2 – имена полей, из которых должны быть отобраны данные;
псевдоним_1, псевдоним_2 — ассоциации, которые станут заголовками столбцов вместо исходных названий полей в таблице;
выражение — имена одной или нескольких таблиц, которые содержат необходимые для отбора записи;
предложение GROUP BY в SQL-предложении объединяет записи с одинаковыми значениями в указанном списке полей в одну запись. Если инструкция SELECT содержит статистическую функцию SQL, например, Sum или Count, то для каждой записи будет вычислено итоговое значение;
предложение HAVING определяет, какие сгруппированные записи, выданные в результате выполнения запроса, отображаются при использовании инструкции SELECT с предложением GROUP BY. После того как записи результирующего набора будут сгруппированы с помощью предложения GROUP BY, предложение HAVING отберет те из них, которые удовлетворяют условиям отбора, указанным в предложении HAVING;
предложение ORDER BY позволяет отсортировать записи, полученные в результате запроса, в порядке возрастания или убывания на основе значений указанного поля или полей.
Следует отметить, что инструкции SELECT не изменяют данные в базе данных.
Ниже приведен минимальный синтаксис инструкции SELECT:
SELECT поля FROM таблица
Если несколько таблиц, включенных в предложение FROM, содержат одноименные поля, перед именем такого поля следует ввести имя таблицы и оператор « . » (точка). Предположим, что поле «Номер_группы» содержится в таблицах «Студенты» и «Группы». Следующая инструкция SQL отберет поле «Номер_группы» и «ФИО_студента» из таблицы «Студенты» и «ФИО_куратора» из таблицы «Группы»:
SELECT Группы.Номер_группы, Группы.ФИО_куратора, Студенты.ФИО_студента
FROM Группы, Студенты
WHERE Группы.Номер_группы = Студенты.Номер_группы;
Внутреннее соединение
Операция INNER JOIN объединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения. Синтаксис операции:
FROM таблица_1 INNER JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2
где таблица_1, таблица_2 — имена таблиц, записи которых подлежат объединению;
поле_1, поле_2 — имена объединяемых полей. Поля должны иметь одинаковый тип данных и содержать данные одного рода, однако эти поля могут иметь разные имена;
оператор — любой оператор сравнения: "=," "<," ">," "<=," ">=," или "<>".
Операцию INNER JOIN можно использовать в любом предложении FROM. Это самые обычные типы связывания. Они объединяют записи двух таблиц, если связующие поля обеих таблиц содержат одинаковые значения. Предыдущий пример использования команды SELECT можно записать с использованием конструкции INNER JOIN следующим образом:
SELECT Группы.Номер_группы, Группы.ФИО_куратора, Студенты.ФИО_студента
FROM Группы INNER JOIN Студенты
ON Группы.Номер_группы = Студенты.Номер_группы;
Внешнее соединение
Операции LEFT JOIN, RIGHT JOIN объединяют записи исходных таблиц при использовании в любом предложении FROM.
Операция LEFT JOIN используется для создания внешнего соединения, при котором все записи из первой (левой) таблицы включаются в результирующий набор, даже если во второй (правой) таблице нет соответствующих им записей.
Операция RIGHT JOIN используется для создания внешнего объединения, при котором все записи из второй (правой) таблицы включаются в результирующий набор, даже если в первой (левой) таблице нет соответствующих им записей.
Синтаксис операции:
FROM таблица_1 [ LEFT | RIGHT ] JOIN таблица_2
ON таблица_1.поле_1 оператор таблица_2.поле_2
Например, операцию LEFT JOIN можно использовать с таблицами «Студенты» (левая) и «Задолженность_за_обучение» (правая) для отбора всех студентов, в том числе тех, которые не являются задолжниками:
SELECT Студенты.ФИО_студента,
Задолженность_за_обучение.Сумма_задолженности
FROM Студенты LEFT JOIN Задолженность_за_обучение
ON Студенты.Номер_зачетной_книжки =
Задолженность_за_обучение.Номер_зачетной_книжки;
В этом примере поле «Номер_зачетной_книжки» используется для объединения таблиц, однако, не включается в результат выполнения запроса, поскольку не включено в инструкцию SELECT. Чтобы включить связующее поле (в данном случае поле «Номер_зачетной_книжки») в результат выполнения запроса, его имя необходимо включить в инструкцию SELECT.
Важно отметить, что операции LEFT JOIN или RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.
Создание новой таблицы
Инструкция CREATE TABLE создает новую таблицу и используется для описания ее полей и индексов. Если для поля добавлено ограничение NOT NULL, то при добавлении новых записей это поле должно содержать допустимые данные. Синтаксис
CREATE TABLE таблица (поле_1 тип [(размер)]
[NOT NULL] [индекс_1] [, поле_2 тип [(размер)]
[NOT NULL] [индекс_2] [, ...]] [, CONSTRAINT составной Индекс [, ...]]),
где таблица — имя создаваемой таблицы;
поле_1, поле_2 — имена одного или нескольких полей, создаваемых в новой таблице. Таблица должна содержать хотя бы одно поле;
тип — тип данных поля в новой таблице;
размер — размер поля в символах (только для текстовых и двоичных полей);
индекс_1, индекс_2 — предложение CONSTRAINT, предназначенное для создания простого индекса;
составной Индекс — предложение CONSTRAINT, предназначенное для создания составного индекса.
В следующем примере создается новая таблица с двумя полями:
CREATE TABLE Студенты (ФИО TEXT, Дата рождения DATE);
Предложение CONSTRAINT
Предложение CONSTRAINT используется в инструкциях ALTER TABLE и CREATE TABLE для создания или удаления индексов. Существуют два типа предложений CONSTRAINT: для создания простого индекса (по одному полю) и для создания составного индекса (по нескольким полям). Синтаксис:
Простой индекс:
CONSTRAINT имя {PRIMARY KEY|UNIQUE | NOT NULL]}
Составной индекс:
CONSTRAINT имя
{PRIMARY KEY (ключевое_1[, ключевое_2 [, ...]]) |
UNIQUE (уникальное_1[, уникальное_2 [, ...]]) |
NOT NULL (непустое_1[, непустое_2 [, ...]]) |
FOREIGN KEY (ссылка_1[, ссылка_2 [, ...]])
REFERENCES внешняя Таблица [(внешнее Поле_1 [, внешнее Поле_2 [, ...]])]},
где имя — имя индекса, который следует создать;
ключевое_1, ключевое_2 — имена одного или нескольких полей, которые следует назначить ключевыми;
уникальное_1, уникальное_2 — имена одного или нескольких полей, которые следует включить в уникальный индекс;
непустое_1, непустое_2 — имена одного или нескольких полей, в которых запрещаются значения Null;
ссылка_1, ссылка_2 — имена одного или нескольких полей, включенных во внешний ключ, которые содержат ссылки на поля в другой таблице;
внешняя Таблица — имя внешней таблицы, которая содержит поля, указанные с помощью аргумента внешнееПоле;
внешнее Поле_1, внешнее Поле_2 — имена одного или нескольких полей во внешней Таблице, на которые ссылаются поля, указанные с помощью аргумента ссылка_1, ссылка_2. Это предложение можно опустить, если данное поле является ключом внешней Таблицы.
Предложение CONSTRAINT позволяет создать для поля индекс одного из двух описанных ниже типов:
для создания уникального индекса используется зарезервированное слово UNIQUE. Это означает, что в таблице не может быть двух записей, имеющих одно и то же значение в этом поле. Уникальный индекс создается для любого поля или любой группы полей. Если в таблице определен составной уникальный индекс, то комбинация значений включенных в него полей должна быть уникальной для каждой записи таблицы, хотя отдельные поля и могут иметь совпадающие значения;
для создания ключа таблицы, состоящего из одного или нескольких полей, используются зарезервированные слова PRIMARY KEY. Все значения ключа таблицы должны быть уникальными и не значениями Null. Кроме того, в таблице может быть только один ключ.
Для создания внешнего ключа можно использовать зарезервированную конструкцию FOREIGN KEY. Если ключ внешней таблицы состоит из нескольких полей, необходимо использовать предложение CONSTRAINT. При этом следует перечислить все поля, содержащие ссылки на поля во внешней таблице, а также указать имя внешней таблицы и имена полей внешней таблицы, на которые ссылаются поля, перечисленные выше, причем в том же порядке. Однако, если последние поля являются ключом внешней таблицы, то указывать их необязательно, поскольку ядро базы данных считает, что в качестве этих полей следует использовать поля, составляющие ключ внешней таблицы.
В следующем примере создается таблица Задолжен-ность_за_обучение с единственным полем Номер_зачет-ной_книжки и внешним ключом f1_i, связанным с полем Номер_зачетной_книжки в таблице Студенты:
CREATE TABLE Задолженность_за_обучение
(Номер_зачетной_книжки integer, constraint f1_i foreign key (Номер_зачетной_книжки) references Студенты (Номер_зачетной_книжки));
Создание индекса с помощью инструкции CREATE INDEX
CREATE INDEX создает новый индекс для существующей таблицы. Синтаксис команды:
CREATE [UNIQUE] INDEX индекс
ON таблица (поле [ASC|DESC][, поле [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]
где индекс — имя создаваемого индекса;
таблица — имя существующей таблицы, для которой создается индекс;
поле — имена одного или нескольких полей, включаемых в индекс. Для создания простого индекса, состоящего из одного поля, вводится имя поля в круглых скобках сразу после имени таблицы. Для создания составного индекса, состоящего из нескольких полей, перечисляются имена всех этих полей. Для расположения элементов индекса в убывающем порядке используется зарезервированное слово DESC; в противном случае будет принят порядок по возрастанию.
Чтобы запретить совпадение значений индексированных полей в разных записях, используется зарезервированное слово UNIQUE. Необязательное предложение WITH позволяет задать условия на значения. Например:
с помощью параметра DISALLOW NULL можно запретить значения Null в индексированных полях новых записей;
параметр IGNORE NULL позволяет запретить включение в индекс записей, имеющих значения Null в индексированных полях;
зарезервированное слово PRIMARY позволяет назначить индексированные поля ключом. Такой индекс по умолчанию является уникальным, следовательно, зарезервированное слово UNIQUE можно опустить.
Удаление таблицы/индекса
Инструкция DROP удаляет существующую таблицу из базы данных или удаляет существующий индекс из таблицы. Синтаксис:
DROP {TABLE таблица | INDEX индекс ON таблица}
где таблица — имя таблицы, которую следует удалить или из которой следует удалить индекс;
индекс — имя индекса, удаляемого из таблицы.
Прежде чем удалить таблицу или удалить из нее индекс, необходимо ее закрыть.
Удаление записей
Инструкция DELETE создает запрос на удаление записей из одной или нескольких таблиц, перечисленных в предложении FROM, которые удовлетворяют предложению WHERE. Синтаксис команды:
DELETE [Таблица.*]
FROM таблица
WHERE условие Отбора
где Таблица — необязательное имя таблицы, из которой удаляются записи;
таблица — имя таблицы, из которой удаляются записи;
условие Отбора — выражение, определяющее удаляемые записи.
С помощью инструкция DELETE можно осуществлять удаление большого количества записей. Данные из таблицы также можно удалить и с помощью инструкции DROP, однако при таком удалении теряется структура таблицы. Если же применить инструкцию DELETE, удаляются только данные. При этом сохраняются структура таблицы и все остальные ее свойства, такие, как атрибуты полей и индексы.
Запрос на удаление удаляет записи целиком, а не только содержимое указанных полей. Нельзя восстановить записи, удаленные с помощью запроса на удаление. Чтобы узнать, какие записи будут удалены, необходимо посмотреть результаты запроса на выборку, использующего те же самые условие отбора, а затем выполнить запрос на удаление.
Добавление записей
Инструкция INSERT INTO добавляет запись или записи в таблицу. Синтаксис команды:
а) запрос на добавление нескольких записей:
INSERT INTO назначение [(поле_1[, поле_2[, ...]])]
SELECT [источник.]поле_1[, поле_2[, ...]
FROM выражение
б) запрос на добавление одной записи:
INSERT INTO назначение [(поле_1[, поле_2[, ...]])]
VALUES (значение_1[, значение_2[, ...])
где назначение — имя таблицы или запроса, в который добавляются записи;
источник — имя таблицы или запроса, откуда копируются записи;
поле_1, поле_2 — имена полей для добавления данных, если они следуют за аргументом «Назначение»; имена полей, из которых берутся данные, если они следуют за аргументом источник;
выражение — имена таблицы или таблиц, откуда вставляются данные. Это выражение может быть именем отдельной таблицы или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN, а также сохраненным запросом;
значение_1, значение_2 — значения, добавляемые в указанные поля новой записи. Каждое значение будет вставлено в поле, занимающее то же положение в списке: значение_1 вставляется в поле_1 в новой записи, значение_2 — в поле_2 и т.д. Каждое значение текстового поля следует заключать в кавычки (' '), для разделения значений используются запятые.
Инструкцию INSERT INTO можно использовать для добавления одной записи в таблицу с помощью запроса на добавление одной записи, описанного выше. В этом случае инструкция должна содержать имя и значение каждого поля записи. Нужно определить все поля записи, в которые будет помещено значение, и значения для этих полей. Если поля не определены, в недостающие столбцы будет вставлено значение по умолчанию или значение Null. Записи добавляются в конец таблицы.
Инструкцию INSERT INTO можно также использовать для добавления набора записей из другой таблицы или запроса с помощью предложения SELECT ... FROM, как показано выше в запросе на добавление нескольких записей. В этом случае предложение SELECT определяет поля, добавляемые в указанную таблицу «Назначение».
Инструкция INSERT INTO является необязательной, однако, если она присутствует, то должна находиться перед инструкцией SELECT.
Запрос на добавление записей копирует записи из одной или нескольких таблиц в другую таблицу. Таблицы, которые содержат добавляемые записи, не изменяются.
Вместо добавления существующих записей из другой таблицы, можно указать значения полей одной новой записи с помощью предложения VALUES. Если список полей опущен, предложение VALUES должно содержать значение для каждого поля таблицы; в противном случае инструкция INSERT не будет выполнена. Можно использовать дополнительную инструкцию INSERT INTO с предложением VALUES для каждой добавляемой новой записи.
Обновление данных
Инструкция UPDATE создает запрос на обновление, который изменяет значения полей указанной таблицы на основе заданного условия отбора. Синтаксис команды:
UPDATE таблица
SET новое Значение
WHERE условие Отбора;
где таблица — имя таблицы, данные в которой следует изменить;
новое Значение — выражение, определяющее значение, которое должно быть вставлено в указанное поле обновленных записей;
условие Отбора — выражение, отбирающее записи, которые должны быть изменены.
При выполнении этой инструкции будут изменены только записи, удовлетворяющие указанному условию. Инструкцию UPDATE особенно удобно использовать для изменения сразу нескольких записей или в том случае, если записи, подлежащие изменению, находятся в разных таблицах. Одновременно можно изменить значения нескольких полей. Следующая инструкция SQL увеличивает стипендию студентов группы 422-1 на 10 процентов:
UPDATE Студенты
SET Стипендия = стипендия * 1.1
WHERE Номер_группы = '422-1';
Порядок выполнения работы
Все запросы, создаваемые в рамках данной лабораторной работы, должны быть реализованы на языке SQL без помощи построителя запросов.
Для создания нового
SQL-запроса
в окне базы данных нажмите кнопку
Создание запроса в режиме конструктора
и не выбирая таблицы для запроса нажмите
кнопку Вид
на панели инструментов.
Используя инструкцию CREATE TABLE, создайте запрос на создание новой таблицы для выбранной ранее предметной области, содержащую пять полей, различных типов данных, определив в запросе первичный ключ и проиндексировав соответствующие поля, используя предложение CONSTRAINT. Для запуска запроса нажмите кнопку Запуск на панели инструментов. После чего создайте запрос на создание еще одной таблицы, содержащей внешний ключ по отношению к первичному ключу предыдущей таблицы. Запустите запрос, после чего проверьте, отразились ли изменения в схеме данных.
Используя команду SELECT, создайте запрос на выборку записей из двух (или более) таблиц, используя правила внешнего и внутреннего соединения, а также различные условия отбора и сортировки.
Используя команду UPDATE, создайте запрос на обновление данных в созданных ранее таблицах.
Используя команду INSERT INTO, создайте запросы на добавление группы записей и одной записи в существующую таблицу.
Используя команду CREATE INDEX, создайте запрос на создание нового индекса, используя различные условия на значения индексов (IGNORE NULL, DISSALLOW NULL, PRIMARY), а также типы сортировки.
Используя команду DROP TABLE, создайте запрос на удаление таблицы БД.
Сохраните все созданные запросы в базе данных.