Добавил:
Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Скачиваний:
54
Добавлен:
31.12.2017
Размер:
175.88 Кб
Скачать

Лабораторная работа № 8

Использование объединяющих и вложенных запросов и корректирующих операторов языка SQL

Цель работы: изучить возможности оператора UNION и вложенных за-

просов и получить практические навыки использования операторов языка SQL,

изменяющих данные, хранящиеся в БД.

Продолжительность работы - 4 ч.

Теоретические сведения

Наряду с использованием многотабличных запросов язык SQL позволяет формировать таблицу результатов на основе данных, содержащихся в несколь-

ких исходных таблицах, посредством объединения запросов или применения вложенных запросов..

Объединение результатов нескольких запросов

Язык SQL предусматривает возможность объединения результатов двух или более запросов в одну таблицу с помощью оператора UNION. Например,

можно объединить результаты, выполнив следующий сложный оператор, и по-

лучить список сотрудников и клиентов из Москвы:

SELECT Фамилия + Имя, Должность FROM Сотрудники WHERE Город = 'Москва'

UNION

SELECT ОбращатьсяК, Должность FROM Клиенты WHERE Город = 'Москва'

Чтобы таблицы результатов запроса можно было объединить с помощью оператора UNION, они должны удовлетворять следующим требованиям:

1)таблицы должны содержать одинаковое число столбцов;

2)тип данных каждого столбца первой таблицы должен совпадать с типом данных соответствующего столбца во второй таблице;

3)ни одна из таблиц не может быть отсортирована с помощью предложе-

ния ORDER BY, однако объединенные результаты запроса можно отсортиро-

вать.

Имена столбцов в объединяемых таблицах не обязательно должны быть одинаковыми.

Поскольку оператор UNION объединяет строки из двух таблиц результа-

тов, то вполне вероятно, что в объединенной таблице будут содержаться повто-

ряющиеся строки. По умолчанию оператор UNION в процессе своего выполне-

ния удаляет повторяющиеся строки.

Если в результирующей объединенной таблице необходимо сохранить по-

вторяющиеся строки, сразу за ключевым словом UNION следует указать ключе-

вое слово ALL.

Хотя использование предложения ORDER BY в составляющих операторах

SELECT, оператор UNION, запрещено, но результирующую объединенную таб-

лицу можно отсортировать, записав предложение ORDER BY за последним опе-

ратором SELECT. Поскольку столбцы в таблице результатов запроса на объеди-

нение не имеют имен, то в предложении ORDER BY следует указывать номера столбцов.

Оператор UNION можно использовать многократно, чтобы объединить ре-

зультаты трех или более запросов, указывая с помощью круглых скобок порядок выполнения операторов:

SELECT * FROM A

UNION ALL (SELECT*FROM B UNION SELECT*FROM C)

Вложенные запросы на чтение

В языке SQL существует понятие вложенного запроса. Механизм вложен-

ных запросов позволяет использовать результат одного запроса в качестве со-

ставной части другого запроса. Возможность применения одного запроса внутри другого и была причиной появления слова “структурированный” в названии язы-

ка SQL.

Вложенным, или подчиненным, запросом (подзапросом), называется за-

прос, содержащийся в списке возвращаемых столбцов или предложениях

WHERE или HAVING другого оператора. Вложенные запросы позволяют есте-

ственным образом формулировать запросы, которые используют результаты дру-

гих запросов.

Например, из учебной базы данных “Торговая компания” можно опреде-

лить, кто из сотрудников является самым молодым по возрасту:

SELECT Фамилия, ДатаРождения FROM Сотрудники

WHERE ДатаРождения = (SELECT MAX(ДатаРождения) FROM Сотрудники)

2

Вложенный (внутренний) запрос, указанный в условии поиска предложе-

ния WHERE, вычисляет максимальную дату рождения сотрудника, а главный

(внешний) запрос сравнивает дату рождения каждого сотрудника с вычисленной датой и, в зависимости от результата сравнения, либо добавляет сотрудника в таблицу результатов запроса, либо нет. Совокупно главный и вложенный запро-

сы выражают исходный запрос и извлекают из БД требуемую информацию.

Вложенный запрос, или подзапрос, - это оператор SELECT, заключенный в круглые скобки. Между вложенным запросом и оператором SELECT имеется ряд отличий.

1.Таблица результатов вложенного запроса всегда состоит из одного столбца, т.е. список возвращаемых столбцов должен иметь только один элемент.

2.Во вложенный запрос не может входить предложение ORDER BY.

3.Вложенный запрос не может быть запросом на объединение нескольких различных операторов SELECT; допускается использование только одного опе-

ратора SELECT.

4. Имена столбцов, используемые во вложенном запросе, могут являться ссылками на столбцы таблиц главного (внешнего) запроса.

Чтобы проиллюстрировать последнее из перечисленных отличий, опреде-

лим, кто из поставщиков одних товаров одновременно является клиентом, зака-

зывающим другие товары. Таблица результатов формируется оператором

SELECT * FROM Поставщики

WHERE Название = (SELECT Название FROM Клиенты

WHERE Клиенты.Название = Поставщики.Название)

Столбец (или поле) Поставщики.Название во вложенном запросе является примером внешней ссылки. Внешняя ссылка представляет собой имя столбца,

принадлежащего таблице, указанной в предложении FROM главного запроса, и

не входящего ни в одну из таблиц, перечисленных в предложении FROM вло-

женного запроса.

Если во вложенном запросе имеется внешняя ссылка, то он называется связанным подзапросом. Особенностью связанного подзапроса является то, что он выполняется многократно, по одному разу для каждой строки таблицы, ука-

занной в главном запросе.

3

Процедура выполнения связанного подзапроса состоит из следующих ша-

гов:

1) выбрать строку из таблицы, имя которой указано в главном запросе; это текущая строка кандидат (в примере это строка таблицы ПОСТАВЩИКИ с по-

лем “Название”); 2) выполнить вложенный запрос с учѐтом значений, содержащихся в те-

кущей строке-кандидате (в примере это значение поля Поставщики.Название, в

соответствии с которым из таблицы КЛИЕНТЫ выбирается название клиента); 3) вычислить условие поиска главного запроса с учѐтом результатов вло-

женного запроса, выполненного на шаге 2; если вычислено значение TRUE, то текущая строка-кандидат включается в таблицу результатов;

4) повторять шаги 1-3 для следующей строки-кандидата, пока не будут проверены все строки таблицы, указанной в главном запросе.

Условия поиска с вложенным запросом

Вложенный запрос обычно является частью условия поиска в предложе-

нии WHERE или HAVING. Возможны следующие условия поиска с вложенным запросом:

1) сравнение с результатом вложенного запроса. Сравнивает значение вы-

ражения с одним значением, возвращѐнным вложенным запросом;

Проверяемое_выражение

 

 

 

=

 

 

Вложенный

 

 

 

 

 

_запрос

 

 

 

 

<>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<=

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

>=

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2) проверка на принадлежность результатам вложенного запроса. Прове-

ряет значение выражения на равенство с одним из значений множества, возвра-

щѐнного вложенным запросом.

Проверяемое

_выражение IN

NOT

Например:

Вложенный _запрос

4

SELECT * FROM Поставщики

WHERE Название NOT IN (SELECT Название FROM Клиенты

WHERE Клиенты.Название = Поставщики.Название)

3) проверка на существование. Проверяет наличие строк в таблице резуль-

татов вложенного запроса.

 

 

 

 

 

 

EXISTS

 

Вложенный

 

 

 

 

 

 

 

 

 

NOT

 

 

 

_запрос

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Например:

 

 

 

SELECT * FROM Поставщики

WHERE EXISTS (SELECT Название FROM Клиенты

WHERE Клиенты.Название = Поставщики.Название)

Обратите внимание на то, что условие поиска EXISTS не использует ре-

зультаты вложенного запроса. Проверяется только наличие результатов. По этой причине в SQL смягчается правило о единственности возвращаемого столбца во вложенном запросе и во вложенном запросе при проверке EXISTS допускается использование формы SELECT * ;

4) многократное сравнение. Сравнивает значение выражения с каждым из значений множества, возвращаемого вложенным запросом.

Проверяемое

 

 

=

 

 

 

 

ANY

 

 

Вложенный

_выражение

 

 

 

 

 

 

 

 

_запрос

 

 

<>

 

 

 

 

ALL

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<=

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

>=

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

При проверке ANY проверяемое значение поочерѐдно сравнивается с каж-

дым значением, содержащимся в столбце, который сформулирован вложенным запросом. Если любое из этих сравнений даѐт результат TRUE, то проверка ANY

возвращает значение TRUE.

При проверке ALL проверяемое значение поочерѐдно сравнивается с каж-

дым значением, содержащимся в столбце. Если все сравнения дают результат

TRUE, то проверка ALL возвращает значение TRUE.

Например, с использованием многократного сравнения можно определить сотрудников, которые оформили наибольшее число заказов:

SELECT Фамилия, Имя, "оформил", COUNT(КодЗаказа) , "заказов" FROM Сотрудники, Заказы

WHERE Сотрудники.КодСотрудника = Заказы.КодСотрудника GROUP BY Фамилия, Имя

HAVING COUNT(КодЗаказа)

5

>= ALL

(SELECT COUNT(КодЗаказа)

FROM Сотрудники, Заказы

WHERE Сотрудники.КодСотрудника = Заказы.КодСотрудника GROUP BY Фамилия, Имя

)

Внесение изменений в базу данных

Язык SQL позволяет не только извлекать информацию из БД с помощью запросов на чтение, но и изменять содержащуюся в ней информацию с помощью корректирующих запросов на добавление, удаление и обновление.

Добавление новых данных. Наименьшей единицей информации, которую можно добавить в реляционную БД, является одна строка. Существует два спо-

соба добавления новых строк в БД:

1)однострочный оператор INSERT позволяет добавить в таблицу новую строку (рис. 1);

2)многострочный оператор INSERT обеспечивает извлечение строк из одной части БД и добавление их в другую таблицу (рис. 2).

INSERT INTO имя_таблицы

 

 

(

имя_столбца

)

 

,

 

VALUES (

константа

)

 

NULL

 

 

,

 

Рис. 1. Однострочный оператор INSERT

 

В предложении INTO указывается целевая таблица, в которую добавляется новая строка, а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет, какие значения в какой столбец заносятся.

Например, можно задать запросы для внесения в БД сведений о новых компаниях, осуществляющих доставку:

a) INSERT INTO Доставка

VALUES (1234, 'ООО Быструм', '(495) 124-3195')

b) INSERT INTO Доставка (Название, Телефон, КодДоставки)

VALUES ('Турбо', '(495) 529-3175', 1235) c) INSERT INTO Доставка (Телефон, Название)

VALUES ('(496) 924-3376', 'Скороход')

Если список столбцов опущен, то последовательность значений данных должна точно соответствовать порядку столбцов в таблице (см. a).

6

В списке столбцов их имена могут указываться в любой удобной последо-

вательности, определяющей затем порядок значений данных в предложении

VALUES (см. b).

При добавлении в таблицу новой строки всем столбцам, имена которых отсутствуют в списке столбцов оператора INSERT, СУБД автоматически при-

сваивает значение NULL либо значение, заданное по умолчанию при описании структуры таблицы (см. c, где отсутствует имя столбца КодДоставки).

Список столбцов в операторе INSERT служит, чтобы установить соответ-

ствие между значениями данных, содержащимися в предложении VALUES, и

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

INSERT INTO имя_таблицы

 

 

 

 

 

 

 

 

запрос

 

 

(

 

имя_столбца

 

)

 

 

 

 

 

 

 

,

Рис. 2. Многострочный оператор INSERT

Многострочный оператор INSERT добавляет в целевую таблицу одну или несколько строк. Источником новых строк служит запрос на чтение, содержа-

щийся в операторе INSERT.

Например, можно добавить в таблицу ДОСТАВКА сведения о новой ком-

пании, у которой название и телефон будет такими же, как клиента с кодом ANTON:

INSERT INTO Доставка (Телефон, Название)

SELECT Телефон, Название FROM Клиенты

WHERE КодКлиента = 'ANTON'

Обновление существующих данных. Наименьшей единицей информации,

которую можно обновить в реляционной БД является значение одного столбца в одной строке.

Для обновления значения одного или нескольких столбцов в выбранных строках одной таблицы предназначен оператор UPDATE (рис. 3).

UPDATE имя_таблицы SET

 

имя_столбца = выражение

 

 

 

 

 

 

 

 

,

 

 

 

 

 

 

 

 

 

WHERE условие_поиска Рис. 3. Синтаксическая диаграмма оператора UPDATE

7

В операторе указывается целевая таблица, которая должна быть модифи-

цирована. Предложение WHERE отбирает строки таблицы, подлежащие обнов-

лению. В предложении SET указывается, какие столбцы в выбранных строках таблицы должны быть обновлены, и для них задаются новые значения.

Следующий оператор UPDATE изменяет номер телефона компании “Рост-

ранс”, которая доставляет заказы:

UPDATE Доставка SET Телефон=’(496) 111-7700’ WHERE Название = ’Ространс’

Оператор UPDATE может одновременно обновить столбцы в нескольких строках, соответствующих условию поиска. Строки, для которых условие поиска имеет значение TRUE, обновляются, а строки, для которых условие поиска имеет значение FALSE или NULL, не обновляются.

Выражение в операции присваивания может быть любым правильным вы-

ражением языка SQL, результирующее значение которого имеет тип данных,

соответствующий целевому столбцу.

Значение выражения вычисляется на основе значений строки, которая в данный момент обновляется в целевой таблице. Выражение не может включать в себя какие-либо агрегатные функции или запросы.

Если выражение содержит ссылку на один из столбцов целевой таблицы,

то для вычисления выражения используется значение этого столбца в текущей строке, которое было перед обновлением. То же самое справедливо для ссылок на столбцы в предложении WHERE.

Если предложение WHERE отсутствует в записи оператора UPDATE, то обновляются все строки целевой таблицы, например:

UPDATE Заказано SET Скидка = Скидка + 0.01

Предложение WHERE может содержать вложенный запрос, если необхо-

димо отбирать строки на обновление, опираясь на информацию из других таб-

лиц. Например, можно повысить в должности сотрудников, оформивших более

100 заказов:

UPDATE Сотрудники SET Должность = 'Топ-менеджер'

WHERE 100 < (SELECT COUNT (*) FROM Заказы

WHERE Сотрудники.КодСотрудника = Заказы.КодСотрудника)

Удаление существующих данных. Наименьшей единицей информации,

которую можно удалить из реляционной БД, является строка.

8

Для удаления выбранных строк из одной таблицы используется оператор

DELETE, в предложении FROM которого указывается таблица, содержащая уда-

ляемые строки, а в предложении WHERE указывается условие, которому должны соответствовать удаляемые строки, например:

DELETE FROM Доставка

WHERE КодДоставки=1235

Если предложение WHERE отсутствует в записи оператора DELETE, то такой оператор удаляет из таблицы все строки. Например, оператор

DELETE FROM Доставка

“опустошит” таблицу ДОСТАВКА, удалив из неѐ все строки.

В предложении WHERE условие поиска может содержать вложенный за-

прос. Например, прежде чем удалять информацию из таблицы ДОСТАВКА, не-

обходимо из таблицы ЗАКАЗЫ удалить все строки, связанные с таблицы ДОС-

ТАВКА:

DELETE FROM Заказы

WHERE Заказы.Доставка IN (SELECT КодДоставки FROM Доставка)

Лабораторное задание

Изучить правила записи объединяющих и вложенных запросов и коррек-

тирующих операторов, подготовить и выполнить SQL-запросы с использованием операции объединения (UNION) и операторов SELECT, INSERT, DELETE, UPDATE для учебной базы данных “Торговая компания” и базы данных для своего варианта.

Порядок выполнения лабораторной работы

1. При домашней подготовке к лабораторной работе изучить ее описание,

ответить на контрольные вопросы и для базы данных, созданной для своего ва-

рианта, сформулировать и записать на языке SQL 10 запросов, использующих объединение и вложенные запросы, для предоставления всесторонней информа-

ции о предметной области с использованием всех видов условий поиска и 10

корректирующих запросов.

2. Запустить СУБД Access на Терминале 4100, активизировать окно справки и ознакомиться со справочной информацией об операции объединения

(UNION) и операторах (инструкциях) INSERT, DELETE, UPDATE.

9

3. Для учебной базы “Торговая компания” выполнить запросы, приведен-

ные в качестве примеров в описании лабораторной работы.

4. Для базы данных своего варианта выполнить запросы, сформулирован-

ные при домашней подготовке.

5. Показать результаты преподавателю и защитить лабораторную работу..

Требования к отчету

Рукописная часть отчета должна содержать:

1)название и цель лабораторной работы;

2)письменные ответы на контрольные вопросы;

3)формулировку запросов и их запись на языке SQL;

Файловая часть отчета должна содержать результаты выполнения запро-

сов.

Контрольные вопросы

1.Как объединить результаты выполнения нескольких запросов?

2.Какие ограничения существуют при использовании оператора UNION?

3.Какие запросы называются вложенными?

4.В чем отличие подзапроса от оператора SELECT?

5.Что называется внешней ссылкой?

6.Какой подзапрос называется связанным?

7.В чем особенность выполнения связанного подзапроса?

8.Какие виды условий поиска с подзапросами предусмотрены в языке

SQL? Приведите примеры.

9.Какие операторы языка SQL относятся к корректирующим?

10.Приведите правила записи и перечислите особенности выполнения

корректирующих операторов.

 

Содержание

 

Теоретические сведения........................................................................................................................................

1

Объединение результатов нескольких запросов .............................................................................................

1

Вложенные запросы на чтение .........................................................................................................................

2

Условия поиска с вложенным запросом..........................................................................................................

4

Внесение изменений в базу данных .................................................................................................................

6

Лабораторное задание ...........................................................................................................................................

9

Порядок выполнения лабораторной работы .......................................................................................................

9

Требования к отчету............................................................................................................................................

10

Контрольные вопросы.........................................................................................................................................

10

10

Соседние файлы в папке МП3-045