Лабораторная работа № 11
Язык SQL. Работа с представлениями
Часа
Цель работы:
1) получение навыков создания простых запросов на выборку данных;
2) научиться создавать представления в СУБД MS SQL Server 2008. Реализовать необходимые представления в базе данных разрабатываемой информационной системы.
Оператор SELECT. Простые запросы на выборку.
После того, как таблица создана и в нее внесены данные, можно приступать к их использованию. В большинстве случаев используется упрощенный вариант команды SELECT, имеющий следующий синтаксис:
SELECT select_list
[INTO new_table]
FROM table_source
[WHERE search_conditions]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
Раздел SELECT имеет следующий синтаксис:
SELECT [ ALL | DISTINCT ]
TOP n [PERCENT]
<select_list>
Рассмотрим более подробно назначение аргументов:
ALL - при указании этого ключевого слова в результат запроса разрешается включение дублирующихся строк. Параметр ALL используется по умолчанию.
DISTINCT - это ключевое слово запрещает появление в результате дублирующихся строк.
TOP n PERCENT - использование этой конструкции предписывает серверу выбирать не все строки, а только n первых. Можно также выбирать не фиксированное количество строк, а определенный процент от всех строк, удовлетворяющих условию.
Конструкция <select_list> определяет список и происхождение колонок, которые будут включены в результат.
Аргумент table_name должен содержать имя таблицы, из которой необходимо выбрать все колонки. Имя таблицы необходимо указывать, если в запрос включено несколько таблиц. Если же в запросе участвует только одна таблица, то проще применить символ * без указания имени таблицы.
Аргумент view_name указывает имя представления, из которого необходимо выбрать все колонки.
Аргумент table_alias указывает псевдоним таблицы, из которой необходимо выбрать все колонки.
Аргумент column_name указывает имя колонки, которое необходимо включить в результат. Колонка должна принадлежать одной из таблиц, указанных в конструкции FROM.
Синтаксис команды SELECT позволяет изменять имена колонок, которые они будут иметь в результате. Для этого используются псевдонимы. Псевдоним задается с помощью аргумента column_alias, он должен быть уникален в пределах результата. Использование псевдонимов обязательно, если исходные таблицы имеют колонки с одинаковыми именами и эти колонки необходимо включить в результат.
Раздел INTO предназначен для сохранения результата, выполнения запроса в заданной таблице.
Аргумент new_table определяет имя таблицы, в которой будет размещен результат. Сервер автоматически создает таблицу с указанной структурой.
Синтаксис table_source:
Рассмотрим использование каждого из параметров:
table_name [ [AS] table_alias ]. Имя связанной таблицы. При необходимости для этой таблицы можно указать псевдоним. Использование псевдонимов необходимо, если в конструкции FROM указано имя изменяемой таблицы. Это требуется, чтобы сервер различал, когда имя таблицы нужно рассматривать как имя изменяемой таблицы, а когда — как имя связанной таблицы.
view_name [ [AS] table_alias]. Имя представления, определяющего критерии выполнения команды UPDATE. При необходимости для представления можно указать псевдоним, который будет использоваться в дальнейшем для ссылки на это представление.
rowset_function [ [AS] table_alias]. Этот блок предполагает использование функций ROWSET (CONTAINSTABLE, FREETEXTTABLE, OPENQUERY и OPENROWSET). С помощью этих функций можно подготовить набор данных, который может быть использован вместо таблицы или представления. Дополнительно можно указать псевдоним, позволяющий обращаться к данным.
derived_table [AS] table_alias [(column_alias [..n]). Определяет имя таблицы, которая может быть использована для построения подзапросов при выполнении обновления. Дополнительно можно указать псевдоним, как для всей таблицы, так и для каждой конкретной колонки. При указании псевдонима для колонок необходимо указывать их в соответствии с физическим порядком колонок в исходной таблице. Кроме того, требуется указание псевдонима для каждой колонки, созданной в исходной таблице.
joined_table. Эта конструкция является наиболее сложным методом задания критериев обновления таблицы и используется для связывания при выполнении обновления нескольких таблиц.
С помощью конструкции WHERE можно сузить количество обрабатываемых строк данных, определив одно или несколько логических условий. В результат будут включены только те строки, которые соответствуют наложенным условиям. Условие может включать константы, переменные и любые выражения, возвращающие булево значение (TRUE или FALSE). Можно указать несколько условий, объединив их с помощью логических операндов OR, AND и NOT. Синтаксис раздела WHERE следующий:
WHERE <search_condition> | column_name { *= | =* } column_name
В конструкции <search_condition> можно определить любое логическое условие, при выполнении которого строка будет включена в результат. Логическое условие может быть произвольным, в том числе и не связанным с данными.
ALL. Если в запросе было определено условие, сужающее диапазон группировки выбираемых строк, то при указании в разделе GROUP BY ключевого слова ALL сервер будет выводить список всех групп, но не будет выполнять для них функции агрегирования.
Раздел HAVING имеет следующую структуру:
HAVING <search_condition>.
Этот раздел в основном используется для указания условий поиска при выполнении группирования данных с помощью раздела GROUP BY. Конструкция <search_condition> содержит логические условия, определяющие диапазон строк, обрабатываемых запросом. Правила работы с этой конструкцией были рассмотрены при описании раздела WHERE.
Создание представлений
Представление (View) для конечных пользователей выглядит как таблица, но при этом само не содержит данных, а лишь представляет данные, расположенные в таблице. Физически представление реализовано в виде SQL-запроса, на основе которого производится выборка данных из одной или нескольких таблиц или представлений.
Представление может выбирать данные из других представлений, которые, в свою очередь, могут также основываться на представлениях или таблицах. Вложенность представлений не должна превышать 32. Представление часто применяется для ограничения доступа пользователей к конфиденциальным данным в таблице.
При обращении к представлению сервер проверяет правильность всех ссылок в запросе. Проверяется, существуют ли объекты, требующиеся для выполнения запроса, определяющего представление. Если одна из таблиц, на которые ссылается запрос, была уничтожена, то представление будет нельзя использовать и при попытке обратиться к нему пользователи получат сообщение об ошибке.
Создание представлений с использованием Transact-SQL
CREATE VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
select_statement
[WITH CHECK OPTION]
view_name — имя представления. При указании имени необходимо придерживаться тех же правил и ограничений, что и при создании таблицы.
column — имя колонки, которое будет использоваться в представлении. Максимальная длина имени составляет 128 символов. По умолчанию имена колонок в представлении соответствуют именам колонок в исходных таблицах. Явное указание имени колонки требуется при использовании вычисляемых колонок или при объединении нескольких таблиц, имеющих колонки с одинаковыми именами. Имена колонок перечисляются через запятую в соответствии с их порядком в представлении. Имена колонок можно указывать в команде SELECT, определяющей представление.
WITH ENCRYPTION - использование этого параметра предписывает серверу шифровать код SQL-запроса. Это гарантирует, что пользователи не смогут просмотреть код запроса и использовать его. Если при определении представления необходимо скрыть имена исходных таблиц и колонок, а также алгоритм объединения данных, то необходимо использовать эту опцию.
select_statement — код запроса SELECT, выполняющий выборку, объединение и фильтрацию строк из исходных таблиц и представлений. Можно использовать команду SELECT любой сложности со следующими ограничениями:
1) Нельзя создавать новую таблицу на основе результатов, полученных в ходе выполнения запроса, то есть запрещается использование параметра INTO.
2) Нельзя проводить выборку данных из временных таблиц, то есть нельзя использовать имена таблиц, начинающихся на # или ##.
WITH CHECK OPTION – обеспечивает соответствие всех выполняемых для представления инструкций модификации данных критериям, заданным при помощи аргумента select_statement. Если строка изменяется посредством представления, предложение WITH CHECK OPTION гарантирует, что после фиксации изменений доступ к данным из представления сохранится.
В запрос нельзя включать операции вычисления и группировки, то есть запрещается указание параметров ORDER BY, COMPUTE и COMPUTE BY.
Для удаления представления используется команда Transact SQL DROP VIEW{view […n]}. За один раз можно удалить несколько представлений.
Примеры
1.Вывод ограниченной информации о студентах с фамилией, оканчивающейся на «ов».
CREATE VIEW infoStudent /*Указываем имя представления*/
AS
SELECT /*Указываем, какие поля будут выведены*/
Студенты.Имя, Студенты.Фамилия, Студенты.факультет, Студенты.специальность
FROM Студенты /*Из какой таблицы*/
WHERE Студенты.Фамилия LIKE ‘[]ов’
2. Информация о замене экземпляров по программированию на С++.
CREATE VIEW infoZamena
AS
SELECT /*Указываем, какие поля, из каких таблиц будут выведены*/
Книга.Автор, Книга.Название, Экземпляр.Шифр, Замена_экземпляров.Но-мер_акта_замены
FROM /*Указываем таблицу, и связанные с ней при помощи оператора INNER JOIN таблицы, из которых выбираются связанные данные. После операнда ON указываем, по каким полям связаны две таблицы*/
(Книга INNER JOIN Экземпляр ON Книга.ISBN =Экземпляр.ISBN)
INNER JOIN Замена_экземпляров ON Экземпляр.Шифр = Замена_экземпляров.Шифр
WHERE Книга.Название LIKE '%C++' /*Выбираются только те книги, в названии которых присутствует “C++” */
3. Информация о списанных экземплярах по программированию на Delphi.
CREATE VIEW infoCpicanie
AS
SELECT /*Указываем, какие поля, из каких таблиц будут выведены*/
Книга.Автор, Книга.Название, Экземпляр.Шифр, Списанные_книги.причина_списания, Списанные_книги.номер_протокола_списания, Списанные_книги.Табельный_номер_списавшего
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
Книга INNER JOIN (Экземпляр INNER JOIN Списанные_книги ON Экземпляр.Шифр = Списанные_книги.Шифр) ON Книга.ISBN = Экземпляр.ISBN
WHERE Книга.Название LIKE '%Delphi' /*Выбираются толькоте книги, в названии которых присутствует “ Delphi */
4. Информация о преподавателях и их заказах на литературу, количество книг должно быть от 10 до 20 или от 25 до 30.
CREATE VIEW infoZakazi /*Объявляем имя представления*/
AS
SELECT /* Указываем, какие поля таблиц будут выведены*/
Преподаватели.Фамилия,Преподаватели.Имя, Преподаватели.Отчество, Книга.Автор, Книга.Название,Заказы.Количество
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
Книга INNER JOIN (Преподаватели INNER JOIN Заказы ON Преподаватели.Читательский_номер = Заказы.Читательский_номер)
ON Книга.ISBN = Заказы.ISBN
WHERE (Заказы.Количество BETWEEN 10 AND 20) OR (Заказы.Количество BETWEEN 25 AND 30) /* 10<количество заказов<20 или 25<количество заказов<30 */
5. Информация о книгах, которыми пользуется студент, и табельный номер сотрудника отдела обслуживания выдавшего книги. Номер отдела обслуживания 11.
CREATE VIEW infoOPolzovaniiStudentov
AS
SELECT /* Указываем, какие поля таблиц будут выведены*/
Студенты.Имя, Студенты.Фамилия, Книга.Автор, Книга.Название, Сотрудники_библиотеки.табельный_номер
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
(((Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN) INNER JOIN
Пользование_библиотекой2 ON Экземпляр.Шифр = Пользование_библиотекой2.Шифр) INNER JOIN
Студенты ON Пользование_библиотекой2.Читательский_номер = Студенты.Читательский_номер) INNER JOIN
Сотрудники_библиотеки ON Пользование_библиотекой2.Табельный_номер = Сотрудники_библиотеки.табельный_номер
WHERE Сотрудники_библиотеки.табельный_номер LIKE '11[]' /*Табельный номер сотрудников должен начинаться с «11»*/
6. Информация о книгах, которыми пользуется преподаватель и табельный номер сотрудника отдела обслуживания выдавшего книги. Номер отдела обслуживания 11.
CREATE VIEW infoOPolzovaniiPrepodovatelej
AS
SELECT /* Указываем, какие поля таблиц будут выведены*/
Преподаватели.Имя, Преподаватели.Фамилия, Книга.Автор, Книга.Название,Сотрудники_библиотеки.табельный_номер
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
(((Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN) INNER JOIN
Пользование_библиотекой3 ON Экземпляр.Шифр = Пользование_библиотекой3.Шифр) INNER JOIN
Преподаватели ON Пользование_библиотекой3.Читательский_номер = Преподаватели.Читательский_номер) INNER JOIN
Сотрудники_библиотеки ON Пользование_библиоте-кой3.Табельный_номер = Сотрудники_библиотеки.табельный_номер
WHERE Сотрудники_библиотеки.табельный_номер LIKE '11[]' /*Табельный номер сотрудников должен начинаться с «11»*/
7.Информация о отделах 11 и 12 и работающих в них сотрудниках.
CREATE VIEW Otdeli
AS
SELECT /*Указываем какие поля будут выбраны*/
Отделы.номер_отдела, Отделы.название_отдела, Сотрудники_библиотеки.фамилия,
Сотрудники_библиотеки.имя,Сотрудники_библиотеки.отчество
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
Отделы INNER JOIN Сотрудники_библиотеки ON Отделы.Номер_отдела = Сотрудники_библиотеки.Номер_отдела
GROUP BY номер.отдела /*По какому полю будут сгруппированы поля*/
WHERE Отделы.номер_отдела BETWEEN 10 AND 13 /*Выбираются отделы, номер которых находится между 10 и 13*/
8.Информация о месте хранения периодических изданий название которых содержит в себе слово «вестник», но при этом д.б. исключены периодические издания, первое слово которых начинается на «Э», например «Экономический вестник».
CREATE VIEW INFOPeriodika
AS
SELECT /*Указываем какие поля будут выбраны*/
Периодические_издания.Название, Периодические_издания.Дата_издания, Отделы.Название_отдела, Отделы.Номер_отдела
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
Отделы INNER JOIN Периодические_издания ON Отделы.Номер_отдела = Периодические_издания.Номер_отдела
WHERE Периодические_издания.Название LIKE '[^Э] %вестник%'
/*[^Э] запрещает начинаться первому слову на «Э», % вестник% – указывает на то, что оставшаяся часть названия должна содержать слово «вестник»*/
9. Полная информация об экземпляре, который был издан с 2000 до текущего года
CREATE VIEW FullInfoEkzemplar
AS
SELECT /*Указываем какие поля будут выбраны*/
Экземпляр.шифр,Книга.Автор,Книга.Название,Книга.Год_издания,Книга.Издательство,Экземпляр.Предметная_область, Экземпляр.Номер_отдела, Экземпляр.Отметка_о_списании, Экземпляр.Отметка_о_замене
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN
WHERE Книга.Год_издания BETWEEN 2000 AND YEAR (GETDATE())
/*GETDATE() возвращает текущую дату, YEAR (<дата>) – год <даты>*/
10. Информация о наличии книги в библиотеке
CREATE VIEW InfoAboutBook
AS
SELECT /*Указываем какие поля будут выбраны*/
Книга.ISBN, Экземпляр.Шифр, Экземпляр.Номер_отдела,
Пользование_библиотекой2.Дата_выдачи,Пользование_библиотекой2.Дата_приема
FROM /*Указываем таблицу, и связанные с ней таблицы, из которых выбираются связанные данные.*/
(Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN) INNER JOIN
Пользование_библиотекой2 ON Экземпляр.Шифр = Пользование_библиотекой2.Шифр
WHERE Пользование_библиотекой2.Дата_приема BETWEEN Пользование_библиотекой2.Дата_выдачи AND GETDATE() /*Если книгу сдали,т.е. она в наличии в библиотеке, то дата приема будет между датой выдачи и текущей датой*/
Группы основных функций
SQL Server содержит множество встроенных функций, перечисленных ниже, а также поддерживает создание определяемых пользователем функций.
| Функция | Описание |
| Функции работы с наборами строк | Возвращают объект, который можно использовать так же, как табличные ссылки в SQL-инструкции. |
| Статистические функции | Обрабатывают коллекцию значений и возвращают одно результирующее значение. |
| Ранжирующие функции | Возвращают ранжирующее значение для каждой строки в секции. |
| Скалярные функции (описаны ниже) | Обрабатывают и возвращают одиночное значение. Скалярные функции можно применять везде, где выражение допустимо. |
Скалярные функции
| Категория функции | Описание |
| Конфигурационные функции | Возвращают сведения о текущей конфигурации. |
| Функции работы с курсорами | Возвращают сведения о курсорах. |
| Функции и типы данных даты и времени | Выполняют операции над исходными значениями даты и времени, возвращают строковые и числовые значения, а также значения даты и времени. |
| Математические функции | Выполняют вычисления, основанные на числовых значениях, переданных функции в виде аргументов, и возвращают числовые значения. |
| Функции метаданных | Возвращают сведения о базах данных и объектах баз данных. |
| Функции безопасности | Возвращают данные о пользователях и ролях. |
| Строковые функции | Выполняют операции со строковым (char или varchar) исходным значением и возвращают строковое или числовое значение. |
| Системные функции | Выполняют операции над значениями, объектами и параметрами экземпляра SQL Server и возвращают сведения о них. |
| Системные статистические функции | Возвращают статистические сведения о системе. |
| Функции обработки текста и изображений | Выполняют операции над текстовыми или графическими исходными значениями или столбцами и возвращают сведения о значении. |
Задание
В разрабатываемой базе данных необходимо реализовать 15 представлений с использованием стандартных функций SQL.
Контрольные вопросы
1. Дайте определение представлению.
2. Поясните, в каких случаях целесообразно использовать представление.
3. Перечислите операторы SQL, с помощью которых представления создаются, удаляются и изменяются.
4. Перечислите способы создания представлений.
5. Перечислите существующие ограничения при создании представлений.