Агапов И.Е. Лабораторный практикум. Базы данных

Подождите немного. Документ загружается.

<список сортировки> =

{Колонка | Номер}

[ASC | DESC]

[, <список сортировки> ...]

Некоторые параметры, входящие в этот оператор, описаны в табл. 4.

Таблица 4

Описание параметров оператора SELECT

Параметр Описание

DISTINCT | ALL

DISTINCT – предотвращает дублирование

данных, которые будут извлечены.

ALL (по умолчанию) – приведет к извле-

чению всех данных

{* | <величина> [,

<величина> ...]}

Звездочка (*) означает, что надо извлекать

все колонки из указанных таблиц.

<величина> [, <величина> ...] –

извлекает список указанных колонок, пе-

ременных или выражений

INTO :Переменная [,

:Переменная ...]

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

мых процедурах для операторов SELECT,

возвращающих не более одной строки.

Указывается список переменных, в кото-

рые извлекаются величины

FROM <tableref> [,

<tableref> ...]

Указывает список таблиц, просмотров и

хранимых процедур, из которых извлека-

ются данные. Список может включать со-

единения и соединения могут быть вло-

женными

table

Имя существующей в базе данных табли-

цы

view

Имя существующего базе данных про-

смотра

procedure

Имя существующей хранимой процедуры,

предназначенной для использования в

операторе SELECT

Псевдоним

Короткое альтернативное имя для табли-

цы, просмотра или колонки. После описа-

ния в <tableref>, псевдоним может ис-

пользоваться для ссылок на таблицу или

просмотр

join_type

Задает тип соединения, которое может

быть внутренним или внешним

Описание параметров оператора SELECT

Параметр Описание

WHERE <условие поиска>

Указывает условие, которое ограничивает

количество извлекаемых строк

GROUP BY Колонка [,

Колонка ...]

Разбивает результат запроса на группы,

содержащие все строки с идентичными

значениями указанными в списке колонок

HAVING

<условие поиска>

Использует совместно с GROUP BY. Зада-

ет условие, которое ограничивает количе-

ство возвращаемых групп

UNION [ALL]

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

сов. Все запросы должны извлекать оди-

наковое количество столбцов, тип данных

каждого столба первого запроса должен

совпадать с типом данных других запро-

сов, имена столбцов в разных запросах мо-

гут отличаться. Необязательный параметр

ALL указывает, что надо выводить одина-

ковые строки

ORDER BY

<список сортировки>

Указывает колонки, по которым будет

производиться сортировка извлекаемых

строк. Можно указывать либо имена коло-

нок, либо их порядковые номера в списке

извлекаемых колонок. Если указать ASC,

то строки будут выдаваться в порядке воз-

растания значений сортируемых полей,

если DESC – в порядке убывания значений

Как видно из синтаксиса оператора SELECT, обязательными являются

только предложение SELECT с перечнем выдаваемых колонок и предложе-

ние FROM.

Пример простейшего оператора SELECT:

-- Выдать перечень всех служащих:

SELECT * FROM Employee;

Ниже приведено несколько упрощенных вариантов синтаксиса опера-

тора SELECT, помогающих научиться составлять простые запросы.

Упрощенный синтаксис внутреннего соединения (стандарт SQL-92):

SELECT Колонка [, Колонка ...] | *

FROM <tableref_left> [INNER] JOIN <tableref_right>

[ON <условие поиска>]

[WHERE <условие поиска>];

Упрощенный синтаксис внешнего соединения:

SELECT Колонка [, Колонка ...] | *

FROM <tableref_left>

{LEFT | RIGHT | FULL} [OUTER] JOIN

<tableref_right>

[ON <условие поиска>]

[WHERE <условие поиска>];

Упрощенный синтаксис использования подзапроса:

SELECT [DISTINCT] Колонка [, Колонка ...]

FROM <tableref> [, <tableref> ...]

WHERE

{expression {[NOT] IN | <оператор сравнения>}

| [NOT] EXISTS

}

(SELECT [DISTINCT] Колонка [, Колонка ...]

FROM <tableref> [, <tableref> ...]

WHERE <условие поиска>

);

7.5. Задание

Лабораторную работу следует выполнять в следующем порядке:

1. Изучить синтаксис оператора SELECT и примеры запросов к базе данных

"Employee.fdb".

2. Зарегистрировать базу данных "Employee.fdb" и подключиться к ней в

программе "IB Expert".

3. Выполнить в окне "SQL Editor" двадцать запросов к базе данных

"Employee.fdb" и сохранить их в файле "Employee_20.sql" в рабочей папке.

Каждый запрос должен иметь комментарии с

описанием, а файл в целом

должен иметь комментарии со сведениями об авторе и дате создания.

4. Создать аналогично предыдущим лабораторным работам рабочую папку и

назвать ее "ЛР7".

5. Скопировать в эту папку файл сценария, созданный при выполнении ла-

бораторной работы №6.

6. Открыть в приложении "IB Expert" этот сценарий, исправить комментарии

сделать, чтобы база данных теперь создавалась в папке "ЛР7".

7. Выполнить сценарий и сохранить его в папке "ЛР7".

8. Зарегистрировать созданную базу данных в программе "IB Expert" и под-

ключиться к ней.

9. Выполнить в окне "SQL Editor" десять запросов к своей базе данных и со-

хранить их, добавив комментарии, в рабочей папке в файле

"select_10.sql".

10. Создать в папке "ЛР7" резервную копию базы данных.

11. Создать и сохранить в папке "ЛР7" файл с отчетом о выполнении лабора-

торной работы.

7.6. Ход работы

Для выполнения запросов в программе "IB Expert" необходимо выпол-

нить следующие действия:

1. Подключиться к базе данных и выполнить команду "ToolsÖSQL Editor"

(F12). В результате откроется окно "SQL Editor" (рис. 23).

Рис. 23. Окно выполнения запросов

2. Ввести в поле на вкладке "Edit" текст запроса.

3. Нажать на панели инструментов кнопку

[Execute] (F9).

4. Если запрос правильный, то в результате произойдет его выполнение и ре-

зультат будет отображен на вкладке "Results" (рис. 24).

Рис. 24. Окно с результатом выполнения запроса

5. Количество извлеченных в результате выполнения запроса строк отобра-

жается над сеткой с данными справа. На рис. 24 там содержится строка

"7 records fetched". В данном примере извлечено столько строк, сколько

требуется, чтобы заполнить сетку (в ней помещается только 7 строк)

PT.

6. Чтобы узнать, сколько всего строк соответствуют выполненному операто-

ру, надо перейти в конец отображаемого набора данных.

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

менить содержимое редактора на новый запрос и повторить те же действия.

К тексту ранее выполнявшихся правильных запросов можно вернуться, если

перейти на вкладку "History", либо

находясь на вкладке "Edit" нажимать

кнопки

[Previous Query] и [Next Query].

В параметрах программы "IB Expert" можно задать, чтобы сразу извлекались все строки.

7.7. Отчет о выполнении работы

Отчет о выполнении лабораторной работы №7 необходимо оформить

на листах формата A4.

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

мые в следующей последовательности:

1. Перечень запросов к базе данных "Employee.fdb", выполненных при вы-

полнении этой лабораторной работы с краткими пояснениями и данными

о количестве строк, выданных при выполнении каждого запроса.

2. Словесное

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

бораторных работах базе данных.

3. Распечатка всех запросов с краткими комментариями и указанием количе-

ства возвращенных строк.

4. Перечень файлов, полученных при выполнении лабораторной работы с

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

(сценарий, база данных, резервная копия базы данных

, файл с запросами и

файл с отчетом).

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

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

2. Перечислить в правильном порядке все возможные разделы оператора

SELECT.

3. Как надо задавать числовые и текстовые константы?

4. Как надо проводить сравнение с пустым значением?

5. Какие есть способы задания дат в операторе SELECT?

6. Какие бывают виды соединений таблиц в запросах?

Какие есть два способа задать внутреннее соединение?

8. Какие бывают виды внешних соединений?

9. В чем отличие внутренних и внешних соединений?

10. Для чего используются псевдонимы?

11. Что такое самосоединение?

12. Что такое объединение?

13. Где и для чего применяется ключевое слово BETWEEN? Можно ли его за-

менить другими операторами?

14.

Где и для чего применяется ключевое слово LIKE?

15. Чем отличается оператор CONTAINING от оператора LIKE?

16. Перечислить встроенные агрегатные функции.

17. Какая встроенная функция используется для преобразования типов?

18. Как устранить дублирование строк?

19. Как изменить порядок выводимых строк?

20. Почему при просмотре результатов запроса (рис. 24) имена некоторых

столбцов выделены

жирным шрифтом?

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

Представления

8.1. Цель работы

Изучить используемые в реляционных СУБД операторы создания и

удаления представлений. Получить навыки работы с представлениями с по-

мощью команд SQL и с помощью программы "IB Expert".

Время выполнения: 2 часа.

8.2. Исходные данные

Исходными данными является индивидуальное задание и результат

предыдущих лабораторных работ.

8.3. Используемые программы

Программы "IB Expert" и "Microsoft Word".

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

8.4.1. Представления (Views)

Представление представляет собой запрос на выборку, которому при-

своили имя и сохранили в базе данных. Другими словами это виртуальная

таблица, которая реально не содержит данных, а всяких раз, когда требуется

его содержимое, происходит выполнение запроса.

Когда СУБД встречает в инструкции SQL ссылку на представление,

она находит его определение, сохраненное в базе данных

, преобразует поль-

зовательский запрос, ссылающийся на представление, в эквивалентный за-

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

образом, СУБД создает иллюзию существования представления в виде от-

дельной таблицы.

Причины использования представлений

1. Они изолируют реализацию хранения данных от пользователя. Если кли-

енты будут работать с данными через

представление, то у разработчика

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

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

2. С их помощью можно ограничить доступ к данным, разрешая определен-

ным пользователям иметь права на чтение/изменение данных в представ-

лении, но даже не иметь понятия о таблицах, лежащих в

основе этого

представления.

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

хранимых данных в наиболее подходящем для них виде.

Представления могут основываться на данных из нескольких таблиц и

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

лицы. Оператор SELECT, образующий представление, может делать то же,

что и обычный оператор SELECT, кроме включения предложения ORDER BY

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

Синтаксис оператора создания представления:

CREATE VIEW Имя_Представления [(Колонка [, Колонка …])]

AS <select> [WITH CHECK OPTION];

<select> – оператор SELECT, который выбирает данные включаемые в

представление

WITH CHECK OPTION – если указать этот необязательный параметр, то

при вставке или изменении строки представления будет проверяться усло-

вие "попадания" этой строки в представление. Если новая или измененная

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

ставление, то операция будет отменена и возникнет ошибка.

Пример создания представления:

CREATE VIEW ViewStudent(St_ID, LastName, FirstName) AS

SELECT Pr_ID, Pr_LastName, Pr_FirstName

FROM Person, Student;

WHERE Pr_ID = St_ID;

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

заново. При удалении представления необходимо также удалить все зависи-

мые от его объекты – триггеры, хранимые процедуры и другие представле-

ния. Программа "IB Expert" пересоздает зависимые объекты автоматически.

Пример удаления представления:

DROP VIEW ViewStudent;

8.4.2. Модифицируемые представления

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

ки можно преобразовать в эквивалентные операции по отношению к исход-

ным таблицам этого представления. Могут существовать представления, для

которых добавление, удаление или обновление строк не имеет смысла. Такие

представления можно только читать.

Содержимое представления может изменяться командами модифика-

ции, которые будут автоматически перенаправлены

к базовой таблице. Что-

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

нове одной таблицы (или другого модифицируемого представления), а за-

прос, на котором основано представление, не должен содержать подзапросов,

агрегатных функций, UDF, хранимых процедур, предложений DISTINCT,

GROUP BY и HAVING. Если выполняются все эти условия, то представление

автоматически становится модифицируемым, т.е. для него можно выполнять

запросы DELETE, INSERT и UPDATE, которые будут изменять данные в

таблице-источнике.

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

любое из вышеперечисленных условий, применяется механизм триггеров.

Триггеры для модификации представления должны срабатывать при событи-

ях BEFORE DELETE, BEFORE UPDATE и BEFORE INSERT. В теле тригге-

ров необходимо описать, что должно происходить

с данными при удалении,

изменении и вставке.

8.5. Задание

Лабораторную работу следует выполнять в следующем порядке:

1. Создать аналогично предыдущим лабораторным работам рабочую папку и

назвать ее "ЛР8".

2. Скопировать в эту папку файл сценария, созданный при выполнении пре-

дыдущей лабораторной работы.

3. Открыть в приложении "IB Expert" этот сценарий.

4. Исправить текст комментариев и сделать, чтобы база данных теперь соз-

давалась

в папке "ЛР8".

5. Добавить в конец сценария операторы создания не менее трех представ-

лений.

6. Выполнить сценарий и сохранить его в папке "ЛР8".

7. Зарегистрировать созданную базу данных в программе "IB Expert" и под-

ключиться к ней.

8. Создать в своей базе данных еще одно представление в диалоговом режи-

ме, которое

не должно быть модифицируемым. Оно должно называться

"TestView" и основываться, например, на нескольких таблицах или других

представлениях. Добавить в диалоговом режиме к этому просмотру триг-

геры для разрешения выполнения обновления. Перенести в сценарий опе-

раторы создания этого представления и триггеров, которые автоматически

сгенерирует "IB Expert".

9. Создать в папке "ЛР8" резервную копию базы

данных.

10. Создать и сохранить в папке "ЛР8" файл с отчетом о выполнении лабора-

торной работы.

8.6. Ход работы

Для создания представлений в диалоговом режиме программы "IB Ex-

pert" необходимо выполнить следующие действия:

1. Подключиться к базе данных и выполнить команду главного меню

"DatabaseÖNew View". В результате откроется окно "View", в котором

будет содержаться заготовка оператора создания представления (рис. 25).

Рис. 25. Окно ввода оператора создания представления

2. Изменить в поле на вкладке "SQL" текст оператора создания представле-

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

[Compile] (Ctrl+F9). В результате откроется диалоговое окно (рис. 26), в

котором надо подтвердить (кнопка [Commit]) или отменить (кнопка

[Rollback]) выполнение операции

PT.

Рис. 26. Диалог создания представления

3. Представление, команда создания которого приведена на рис. 26 не явля-

ется модифицируемым. Чтобы сделать его модифицируемым, создадим

для него триггер позволяющий изменять поле "Name" (изменять поле

"NameCount" не имеет смысла).

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

"View" перейти на вкладку "Triggers", нажать правую кнопку мыши

на ти-

пе события, при котором должен срабатывать триггер, и в контекстном

меню выбрать команду "New Trigger" (рис. 27). В результате откроется

окно "Trigger", в котором будет находиться заготовка оператора создания

триггера. Используя эту заготовку, следует ввести правильный оператор

создания триггера и нажать кнопку

[Compile Trigger] (Ctrl+F9).

Если в операторе будет ошибка, то будет доступна только кнопка [Rollback].