АЛМАТИНСКИЙ УНИВЕРСИТЕТ ЭНЕРГЕТИКИ И СВЯЗИ

Кафедра компьютерных технологий

СИСТЕМЫ БАЗ ДАННЫХ

Методические указания к выполнению лабораторных работ

для студентов всех форм обучения специальностей

5В070300 – “Информационные системы”

5В070400 – “Вычислительная техника и программное обеспечение”

Алматы 2012

Составители: А. А. Аманбаев и Е.Г. Сатимова. Системы баз данных. Методические указания к выполнению лабораторных работ для студентов всех форм обучения специальностей 5В070300 – “Информационные системы”; 5В070400 –“ Вычислительная техника и программное обеспечение”. – Алматы: АУЭС, 2012. – ___ с.

Методические указания содержат указания по подготовке к проведению пяти лабораторных работ по установке, изучению, настройке и работе в в Microsoft SQL Server 2008; приведены описания каждой лабораторной работы, дана методика проведения и ход выполнения, оговорен перечень рекомендуемой литературы и контрольные вопросы в конце каждой темы.

Выполнение лабораторных работ определяет фундаментальную основу освоения инструментальной среды Microsoft SQL Server 2008 по курсу “ Системы баз данных ”, “Проектирование и защита баз данных”. Предметом изучения дисциплины “ Системы баз данных ”, “Проектирование и защита баз данных” является освоение основных принципов проектирования и создания баз данных различной степени сложности и назначения в среде Microsoft SQL Server 2008. Студенту важно понять как работать в данной серверной СУБД, а также создавать базы данных, таблицы, запросы и объекты средствами Microsoft SQL Server 2008. Поскольку бакалавр вычислительной техники занимается созданием, внедрением, анализом и сопровождением, его профессиональная подготовка требует умения грамотно работать с различными СУБД. Предметом изучения в рассматриваемой дисциплине являются СУБД Microsoft SQL Server 2008.

Методические указания предназначены для студентов всех форм обучения специальности 5В070300. Они могут быть использованы и для организации лабораторных занятий по идентичным темам аналогичных дисциплин, запланированных для других специальностей.

Ил. 15, библиогр. – 10 назв.

Рецензент: д-р. физ-мат. наук, проф. З.К. Куралбаев.

Печатается по плану издания некоммерческого акционерного общества “Алматинский Университет энергетики и связи” на 2011 г.

Сводный план 2011 г., поз. 252

Содержание

Введение	4
1 Лабораторная работа. Проектирования и реализация модели базы данных в среде СУБД MS SQL Server	5
1.1 Задание на лабораторную работу	5
1.2 Методические указания к выполнению лабораторной работы	6
1.2.1 Установка и настройка Microsoft SQL SERVER 2008	6
1.2.2 Общие сведения о среде СУБД MS SQL Server	7
1.2.3 Создание базы данных и таблиц	8
1.2.4 Изменение структуры и удаление таблиц	12
2 Лабораторная работа. Манипуляция над данными	12
2.1 Задание на лабораторную работу	13
2.2 Методические указания к выполнению лабораторной работы 2.3	13
2.2.1 Команды манипуляции данными	13
3 Лабораторная работа. Простые запросы к базе данных.	17
3.1 Задание на лабораторную работу	17
3.2 Методические указания к выполнению лабораторной работы	18
3.2.1 Извлечение информации из таблиц	18
3.2.2 Использования стандартные функций	28
4 Лабораторная работа. Создание сложных и динамических запросов	32
4.1 Задание на лабораторную работу	33
4.2 Методические указания к выполнению лабораторной работы	33
4.2.1 Объединение таблиц в запросах	33
4.2.2 Использование подзапросов. Связанные подзапросы	34
4.2.3 Создание динамических запросов при помощи хранимых процедур	38
5 Лабораторная работа. Представления	41
5.1 Задание на лабораторную работу	41
5.2 Методические указания к выполнению лабораторной работы	42
5.2.1 Общие сведения о представлениях	42
5.2.2 Представление на базе нескольких таблиц	43
5.2.3 Представление на базе представлений	45
6 Лабораторная работа. Функции и триггеры	46
6.1 Задание на лабораторную работу	46
6.2 Методические указания к выполнению лабораторной работы	47
6.2.1 Функции	47
6.2.2 Триггеры	48
Список литературы	52

Введение

Задачей курса "Системы баз данных" является не изучение особенностей той или иной СУБД, а освоение технологий создания и работы с базами данных. Проектирование информационных систем всегда начинается с определения цели проекта.

Microsoft SQL Server 2008 ориентирован на создание и ведение БД на уровне предприятия. Основное назначение – работа с крупными корпоративными базами.

В настоящий сборник включены лабораторные работы, целью которых является освоение студентом основных принципов проектирования и создания баз данных различной степени сложности и назначения в среде Microsoft SQL Server 2008 при изучении курса «Системы баз данных».

Этапы выполнения лабораторной работы: проработка теоретической части, выполнение рабочего задания, создание отчета и защита его у преподавателя. Рабочее задание содержит работы по выполнению того или иного задания по рассматриваемой теме. Выполнение практических заданий дает возможность выработки навыков и знаний у студентов. В методических указаниях представлены лабораторные задания по базам данных, выполненным на Microsoft SQL Server 2008. Студентам предлагается параллельно выполнить одну и ту же лабораторную работу. В отчете студент должен продемонстрировать выполнение лабораторных работ на своем, выбранном им дистрибутиве.

Выполнение каждой лабораторной работы должно завершаться оформлением отчета. Отчеты должны быть оформлены по фирменному стандарту АУЭС и содержать не менее 20 принскринов, отчет о проделанной работе должен содержать:

- титульный лист;

- цель и задание работы;

- оглавление или содержание;

- введение;

- описание лабораторной работы;

- задание на выполнение работы;

- ответы на контрольные вопросы (итоги теоретической подготовки);

- результаты проделанной работы (принскрины по каждому пункту рабочего задания с пояснением);

- выводы своими словами (что получилось или не получилось, что оказалось в задании непонятно, какие навыки приобретены);

- заключение;

- список литературы.

1 Лабораторная работа. Проектирования и реализация модели базы данных в среде СУБД MS SQL Server

Цель работы: получение навыков:

- в установке и настройке Microsoft SQL SERVER 2008;

- в создании базы данных в среде СУБД MS SQL Server 2008;

- в создании таблиц баз данных в среде СУБД MS SQL Server 2008.

1.1 Задание на лабораторную работу

Реализуйте часть спроектированной модели данных учебного процесса в среде СУБД MS SQL Server 2008:

Создайте таблицы «Преподаватели», «Кафедры», «Группы» и «Студенты».

В лабораторной работе для объектов рассматриваемой базы данных применяются названия на английском языке. Это связано с требованиями используемого программного обеспечения. Поэтому в приведенных ниже примерах база данных называется “Education”. Для создания любого объекта SQL Server существует несколько способов, базирующихся на выполнении определенной команды.

Размещение пользовательских баз может меняться в зависимости от версии SQL и размещения Program Files. Выяснить место расположения пользовательских баз можно из основного окна программы, выбрав из контекстного меню Свойства (Properties) любой базы в списке Databases. Также в окне свойств на вкладке TransactionLog просмотрите место расположения журнала транзакций. Команды резервного копирования и восстановления базы данных тоже выбираются в контекстном меню: строка Все задачи (All Tasks), команды Backup Databases и Restore Databases.

1. Создать в MS SQL Server 2008 базу данных lab_study.

2. Создать в базе данных lab_study таблицы по следующему плану:

a. таблицы Кафедры, Преподаватели – графическим способом в SQL Server Management Studio

b. таблицы Группы, Студенты - в Database Diagrams SQL Server Management Studio

c. таблицы Предметы, Учебный план, Успеваемость - скриптами в Object Explorer SQL Server Management Studio

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

1. Что означает аббревиатура SQL?

2. Каковы главные отличия технологии клиент/сервер от технологии, использующей мэйнфрейм?

3. В рамках технологии клиент/сервер персональный компьютер является клиентом или сервером?

4. Какие типы данных допустимы при создании таблицы?

5. Как выполнить создание таблицы средствами меню программы MS SQL Server 2008?

6. Как выполнить создание таблицы средствами языка SQL?

7. Каким образом выполнить простейшие операции вставки строк данных в таблицу средствами SQL?

8. Каким образом выполнить простейшие операции модификации строк таблицы средствами SQL?

1.2 Методические указания к выполнению лабораторной работы

1.2.1 Установка и настройка Microsoft SQL SERVER 2008

История создания

Родоначальником серии SQL Server и его основой является язык запросов SQL. Данный язык был создан компанией IBM в начале 1970г. Изначально он назывался SEQVEL (Structured English Query Language) В основу языка SQL, используемого в SQL Server, легла разновидность языка T-SQL (Transact – SQL).

В начале 80 г. фирма IBM и ее подрядчики Microsoft и Sybase создают первую версияю сетевой СУБД, которая называлась SQL Server версия 1.0, для операционной системы IBM OS/2. После этого под эту операционную систему было выпущено еще 3 версии SQL Server. В середине 80-х г. компания Microsoft и Sybase отделяются от фирмы IBM, и Microsoft начинает работу над своей операционной системой Windows и вместе с компанией Sybase начинает развитие SQL Server.

В середине 90-х г. (в частности в 1995г) Microsoft создала операционную систему Windows NT и вместе с компанией Sybase выпускает первую версию SQL Server для Windows версии 4.1.

После этого компания Sybase разрывает свои отношения с Microsoft и Microsoft создает Microsoft SQL Server 6.0. Данная версия была предназначена для работы в операционной системе Windows NT, 95 и 98. В 1999г. выходит версия Microsoft SQL Server 7.0, которая стала одной из самых популярных серверных СУБД в мире. В 2000г. выходит 8-я версия Micrsoft SQL Server 2000. В 2005 году выходит новая версия сервера, основанная на новой технологии NET, а в 2008 году выходит её улучшенная версия Microsoft SQL Server 2008.

Требования к аппаратному обеспечению

Минимальные:

- процессор: Intel (или совместимый) Pentium III 1000 МГц или выше;

- память: 512 МБ или более;

- жесткий диск: 20 ГБ или более.

Рекомендуемые:

- процессор: Intel Pentium4 3000 МГц или выше;

- память: 2 ГБ или более;

- жесткий диск: 100 ГБ или более.

Требования к программному обеспечению

Необходимо наличие установленных пакетов: Microsoft dotNET Framework 3.5 SP1, Windows Installer 4.5 и Windows PowerShell 1.0.

Операционная система: MS Windows 2003 Server SP2 (Standard Edition, Enterprise Edition, Data Center Edition), MS Windows 2003 Small Business Server SP2 (Standard Edition, Premium Edition), MS Windows 2008 Server (Standard Edition, Enterprise Edition, Data Center Edition, Web Edition).
Кроме того, некоторые редакции SQL Server 2008, в том числе бесплатную редакцию (Express Edition), можно устанавливать на следующие ОС: MS Windows XP SP2 (Home Edition, Professional Edition, Media Center Edition, Tablet Edition), MS Windows Vista (Home Basic Edition, Home Premium Edition, Business Edition, Enterprise Edition, Ultimate Edition).

Примечание: 64-разрядные редакции SQL Server 2008 предъявляют другие требования к аппаратно-программному обеспечению.

Установка MS SQL Server 2008

Запустить программу-установщик (в бесплатной версии Express Edition обычно называется SQLEXPRADV_x86_RUS.exe) с правами администратора на данном компьютере. На экране появляется меню для установк. Используя режимы меню, настроить и установить MS SQL Server 2008.

1.2.2 Общие сведения о среде СУБД MS SQL Server

Microsoft SQL Server – это реляционная система управления базой данных (СУБД). В реляционных базах данных данные хранятся в таблицах. Взаимосвязанные данные могут группироваться в таблицы, кроме того, могут быть установлены также и взаимоотношения между таблицами. Отсюда и произошло название реляционные – от английского слова relational (родственный, связанный отношениями, взаимозависимый). Пользователи получают доступ к данным на сервере через приложения, а администраторы, выполняя задачи конфигурирования, администрирования и поддержки базы данных, производят непосредственный доступ к серверу.

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

СУБД SQL Server появилась в 1989 году и с тех пор значительно изменилась. Огромные изменения претерпели масштабируемость продукта, его целостность, удобство администрирования, производительность и функциональные возможности.

Cистема SQL Server может быть реализована либо как клиент-серверная система, либо как автономная "настольная" система. Тип проектируемой вами системы зависит от количества пользователей, которые должны одновременно осуществлять доступ к базе данных, и от характера работ, которые должны выполняться.

1.2.3 Создание базы данных и таблиц

Физически база данных располагается в одном или нескольких файлах операционной системы. В одном файле операционной системы не может содержаться несколько баз данных. В этом файле хранятся такие объекты , как таблицы и индексы. Журнал транзакций – это рабочие области, которые SQL Server применяет для записи информации до и после выполнения транзакции. Эта информация может использоваться для отмены выполненной транзакции или для восстановления базы данных, если возникнет такая необходимость. В MS SQL Server 2008 журналы транзакций хранятся в отдельном файле, а не вместе с таблицами, как было в предыдущих версиях. Для создания базы данных с помощью Transact-SQL используется команда CREATE DATABASE.

CREATE DATABASE lab_study

ON PRIMARY

(NAME = education_data, FILENAME='C:\Program Files\Microsoft SQLServer\MSSQL\Data\education_data.mdf', size = 4, maxsize =25, filegrowth = 1 mb)

LOG ON

(NAME = education_log, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\education_log.ldf', size = 4, maxsize = 20,

filegrowth =1 mb);

Внимание: размещение базы и журнала транзакций - 'С:\…’ - может меняться в зависимости от версии SQL и размещения Program Files.

Здесь:

- education имя создаваемой базы данных.

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

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

- LOG ON определяет список файлов на диске, в которых будет храниться журнал транзакций. Если этот параметр не определен, то размер журнала транзакций будет составлять 25% от общего размера файлов данных.

- education_data определяет логическое имя, которое SQL Server будет использовать для обращения к файлу.

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

Определите текущую базу данных с помощью следующей команды:

USE lab_study;

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

Таблицу можно создать с помощью оператора CREATE TABLE языка SQL.

Синтаксис

CREATE TABLE table_name

( { < column_definition > | < table_constraint > } [ ,...n ] )

< column_definition > ::=

{ column_name data_type }

[ { DEFAULT constant_expression

| [ IDENTITY [ ( seed , increment ) ]

] } ]

[ ROWGUIDCOL ]

[ < column_constraint > [ ...n ] ]

< column_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ PRIMARY KEY | UNIQUE ]

| REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ] }

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

{ ( column [ ,...n ] ) } ]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}

Замечания:

1) Аргументы и ограничения рассматриваются в справке оператора CREATE TABLE;

2) Определения столбца: при создании таблицы необходимо указать, по крайней мере, одно определение столбца.

Правила допустимости нулевого значения в рамках определения таблицы: допустимость нулевого значения столбца определяет, будет ли нулевое значение (NULL) принято в столбец как данные. NULL — это не ноль и не пробел. Это значит, что запись не была сделана или что было добавлено явное значение NULL, что обычно обозначает, что значение либо не известно, либо не применимо.

Практическая часть лабораторной работы:

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

USE lab_study;

Создание таблицы Кафедры:

CREATE TABLE Chair (

Chair_ID int PRIMARY KEY,

Chair_NAME varchar(20) NOT NULL,

Chair_PHONE varchar (10) ,

Chair_CHIEF varchar (15)) ;

Создание таблицы Преподаватели:

CREATE TABLE Teacher (

Teach_ID bigint not null PRIMARY KEY ,

Teach_FAM varchar (20) NOT NULL,

Teach_IMYA varchar (10),

Teach_OTCH varchar (15),

Teach_POSITION varchar (18),

Teach_STEPEN varchar (12),

Chair_ID int NOT NULL) ;

Создание таблицы Группы:

CREATE TABLE Grup (

Grup_ID int identity (1,1) PRIMARY KEY,

Grup_NAME varchar (9) NOT NULL,

Grup_COURSE int NOT NULL ) ;

Так как слово GROUP является зарезервированным (часть конструкции GROUP BY), чтобы использовать его в качестве названия, необходимо всегда брать его в квадратные скобки [ ] либо не использовать это служебное слово в названиях элементов БД. Поэтому наша таблица называется GRUP.

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

CREATE TABLE People (

Peop_ID bigint,

Peop_FAM char(20) NOT NULL,

Peop_NAME char(10),

Peop_OTCH char(15),

Peop_DATE datetime,

Peop_ADDRESS char(25),

Group_ID int NOT NULL REFERENCES [Grup] (Grup_ID ),

Peop_MAN bigint,

CONSTRAINT PK_People PRIMARY KEY (Peop_ID),

CONSTRAINT FK_People_People FOREIGN KEY (Peop_MAN) REFERENCES People (Peop_ID)

);

Создание таблицы Предметы:

CREATE TABLE Subject (

Subj_ID int PRIMARY KEY ,

Subj_NAME varchar(20) NOT NULL,

Total_Hours int ,

Lection_Hours int,

Practice_Hours int,

Labor_Hours int ) ;

Создание таблицы Учебный план:

CREATE TABLE Study (

Grup_ID int NOT NULL REFERENCES Grup (Grup_ID ) ON DELETE CASCADE,

Subj_ID int NOT NULL REFERENCES Subject (Subj_ID) ,

Teach_ID bigint NOT NULL REFERENCES Teacher (Teach_ID),

Kredit_count int,

Lesson_Hours int not null,

CONSTRAINT PK_Study PRIMARY KEY (Grup_ID, Subj_ID, Teach_ID, )

) ;

Создание таблицы Успеваемость:

CREATE TABLE Evolution (

Peop_ID bigint not null FOREIGN KEY REFERENCES People (Peop_ID),

Grup_ID int not null,

Subj_ID int not null,

Teach_ID bigint not null,

Kredit_count int ,

Pr_DATE datetime null,

OCENKA integer CHECK ( OCENKA in (0,1,2,3,4,5,6,7,8,9)) DEFAULT(0),

CONSTRAINT FK_Evolution_Study FOREIGN KEY (Grup_ID, Subj_ID, Teach_ID ) REFERENCES Study (Grup_ID, Subj_ID, Teach_ID ),

CONSTRAINT PK_Evolution PRIMARY KEY (Peop_ID, Grup_ID, Subj_ID, Teach_ID )

);

1.2.4 Изменение структуры и удаление таблиц

Структуру таблиц можно изменять командой ALTER TABLE.

Добавление полей. Добавление в таблицу Chair поле Chair_Cab:

ALTER TABLE Chair

ADD Chair_Cab char(10) not null;

Удаление полей из таблицы. Удалите введенное поле из таблицы:

ALTER TABLE Chair

DROP COLUMN Chair_Cab ;

Добавьте в таблицу People поле, где будет храниться информация о стипендии студентов:

ALTER TABLE People ADD Peop_STIP int ;

Добавление ограничений. Если в таблице не были определены первичные или внешние ключи, это также можно исправить с помощью ALTER TABLE.

Предположим, что в таблице Chair не был определен первичный ключ: ALTER TABLE Chair

ADD CONSTRAINT PK_Chair PRIMARY KEY (Chair_ID)

В таблице Teacher (Преподаватели) не был описан один из внешних ключей, добавим его:

ALTER TABLE Teacher

ADD CONSTRAINT PK_Teacher_Chair FOREIGN KEY (Chair_ID) REFERENCES Chair (Chair_ID) ;

Для удаления таблиц предназначена команда DROP.

Например,

DROP Teacher

2 Лабораторная работа. Манипуляция над данными

Цель работы: получение навыков:

- научить студентов заполнять базу данных скриптом и графической средой СУБД MS SQL Server 2008;

- научить студентов удалять строки из таблицы базы данных скриптом в среде СУБД MS SQL Server 2008;

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

2.1 Задание на лабораторную работу

1. Заполните данными все таблицы вашей базы данных (команда INSERT).

Придерживайтесь следующих правил:

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

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

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

2. Заполните поле Peop_STIP таблицы People различными данными, учитывая (используйте команду UPDATE).

3. Выполните по своему усмотрению команды по изменению данных в таблицах базы данных.

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

1. Какие команды манипуляции данными вы знаете?

2. Дайте определение целостности данных.

3. Сформулируйте правило целостности на уровне ссылок.

4. Что означает определение поля NOT NULL?

5. Если поле определено как NULL, значит ли это, что в это поле обязательно должно быть что-нибудь введено?

6. Что означает определение поля identity?

2.2 Методические указания к выполнению лабораторной работы

2.2.1 Команды манипуляции данными

В SQL Server 2008 заполнение таблиц производится при помощи следующей команды:

INSERT [INTO]

table_name [ ( column_list ) ]

{ VALUES

( { DEFAULT | NULL | expression } [ ,...n] )

| derived_table}

или

INSERT <Имя таблицы> [(<Список полей>)]

VALUES (<Значения полей>),

где <Имя таблицы> - таблица, куда вводим данные, (<Список полей>) – список полей, куда вводим данные, если не указываем, то подразумевается заполнение всех полей, в списке полей поля указываются через запятую, (<Значения полей>) – значение полей через запятую.

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

Пример: добавление записи, имеющей следующие значения полей ФИО = Иванов, Адрес = Москва, Код специальности = 5, в таблицу «Студенты».

INSERT Студенты (Фамилия, Имя, Отчество, Адрес, [Код специальности])

VALUES (‘Иванов’, ‘Антон’, ‘Антонович’, ‘Астана’, 5)

Замечание: аргументы и ограничения рассматриваются в справке оператора INSERT (CTRL+ALT+F3)

Примечания:

- чтобы заменить данные в таблице, необходимо использовать инструкцию DELETE для очистки существующих данных перед загрузкой новых данных с помощью INSERT. Чтобы изменить значения столбца в существующей строке, воспользуйтесь инструкцией UPDATE;

- если вставка column_list пропущена, список вставки столбцов, в котором указываются все столбцы таблицы в возрастающем порядке в соответствии с их порядковыми номерами, становится неявным;

- в column_list столбец таблицы можно определить только один раз. Если в column_list столбец отсутствует, SQL Server должен предоставить значение на основании определения столбца; в противном случае строку загрузить не удастся. SQL Server автоматически предоставляет значение для столбца в столбце.

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

Если для указания значения столбца используется значение DEFAULT, то для этого столбца вставляется стандартное значение. Если стандартного значения для столбца не существует, и в столбце могут быть значения Null, то вставляется значение NULL. Значение DEFAULT недопустимо для столбца идентификаторов.

Примеры кода:

INSERT INTO Chair (Chair_ID,Chair_NAME,Chair_PHONE, Chair_CHIEF)

VALUES (50763,'Инж. Кибернетики','123456','Хисаров Б.’);

Создание записи «преподаватели»:

INSERT INTO Teacher (Teach_FAM, Teach_IMYA, Teach_OTCH,

Teach_POSITION, Teach_STEPEN, Chair_ID,Teach_ID)

VALUES ('Ахметова', 'Галия ', 'Сериковна', ' ', ' ',50763,77512);

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

DELETE <Имя таблицы>

[WHERE <Условие>],

где <Условие> - условие, которым удовлетворяют удаляемые записи, если условие не указаны, то удаляются все строки таблицы. Если условие указано, то удаляются записи поля, которых соответствуют условию.

Пример: удалить записи из таблицы «Студенты», у которых поле Адрес = Астана.

DELETE Студенты

WHERE Адрес = ‘Астана’

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

UPDATE <Имя таблицы>

SET

<Имя поля1> = <Выражение1>,

[<Имя поля2> = <Выражение2>,]

…

[WHERE <Условие>]

Здесь <Имя поля1>, <Имя поля2>- имена изменяемых полей, <Выражение1>, <Выражение 2> - либо конкретные значения, либо NULL, либо операторы SELECT. Здесь SELECT применяется как функция.

<Условие>- условие, которым должны соответствовать записи, поля которых изменяем.

Пример: в таблице «Студенты» у студента Иванова поменять адрес Астана на Тараз, а код специальности вместо 5 поставить 3.

UPDATE Студенты

SET

Адрес = ‘Тараз’,

[Код специальности] = 3

WHERE ФИО = ‘Иванов’

Замечание: в качестве выражения можно использовать математические формулы.

Например: SET [Средний балл]= (Оценка1+ Оценка2+ Оценка3)/3) вычисляет поле «Средний балл» как среднее полей «Оценка1», «Оценка2» и «Оценка3». При этом поля «Оценка1», «Оценка2» и «Оценка3» должны уже существовать, и тип данных поля «Средний балл» должен быть с плавающей запятой (Например Real).

Замечание: если необходимо из таблицы удалить все записи, но сохранить ее структуру, для этого используют команду TRUNCATE TABLE <Имя таблицы>, при этом все данные будут удалены, но сама таблица останется.

Изменение значения поля

Для изменения существующих значений в столбцах таблицы используется инструкция UPDATE.

Синтаксис

UPDATE table_name

[ WITH ( < table_hint > ) ]

SET

{ column_name = { expression | DEFAULT | NULL }} [ ,...n ]

[ WHERE < search_condition > ]

Замечание: аргументы и ограничения рассматриваются в справке оператора UPDATE

Примечания:

- столбцы идентификаторов обновлять нельзя;

- если не указано предложение WHERE, обновляются все строки в таблице;

- перед обновлением строк в предложении WHERE проводится оценка условий поиска для каждой строки в таблице;

- если обновление строки нарушает ограничение или правило, либо настройку значения NULL для столбца, а также если новое значение является несовместимым типом данных, происходит отмена инструкции и возврат ошибки без обновления записей;

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

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

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

UPDATE Teacher

SET Teach_Stepen = 'Профессор' WHERE Teach_ID = 2;

3 Лабораторная работа. Простые запросы к базе данных.

Цель работы: научить студентов создавать простые запросы к базе данных скриптом и в графической среде СУБД MS SQL Server 2008.

3.1 Задание на лабораторную работу

Выполните поиск информации в отдельных таблицах:

1) Список преподавателей с указанием их должностей в алфавитном порядке.

2) Названия кафедр с фамилиями заведующих.

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

4) Список студентов, у которых стипендия больше 2000.

5) Список студентов, проживающих в Астане и Караганде.

6) Список студентов второй группы, у которых нет стипендии.

7) Список студентов третьей группы, фамилии которых начинаются на букву «А».

8) Список студентов, которые родились в 1988 году.

9) Посчитайте суммарную стипендию студентов третьей группы.

10) Посчитайте среднее число лекционных часов по всем предметам.

11) Сколько студентов введено в базу данных?

12) Выведите всю информацию о предметах.

13) Список студентов, которые не проживают в Алматы.

14) Список студентов, чьи дни рождения в мае.

15) Номера студентов с минимальной оценкой из ведомости успеваемости.

16) Номера студентов с максимальной оценкой из ведомости успеваемости.

17) Выведите список студентов и их возраст.

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

1. Назовите обязательные составляющие оператора SELECT.

2. Для всех ли данных в выражении ключевого слова WHERE обязательно нужно использовать кавычки?

3. К какому разделу SQL относится оператор SELECT?

4. Можно ли в выражении для ключевого слова WHERE задать несколько условий?

5. Допустимы ли кавычки для значений числовых полей?

6. Играет ли роль тип данных при использовании функции COUNT.

7. Чтобы группировать данные по столбцу, должен ли этот столбец быть указан в списке ключевого слова SELECT?

При использовании ключевого слова ORDER BY в операторе SELECT обязательно ли использовать ключевое слово GROUP BY?

3.2 Методические указания к выполнению лабораторной работы

3.2.1 Извлечение информации из таблиц

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

Для реализации запросов используют специальный язык запросов SQL (Standard Query Language).

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

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

Все запросы делятся на:

1) статические;

2) динамические.

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

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

Хранимые процедуры – SQL запрос, хранимый на стороне сервера, и этот запрос имеет параметры, которые подставляются внутрь SQL кода. При вызове хранимой процедуры необходимо передавать внутрь ее значения параметра.

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

В случае связей между таблицами одна таблица всегда выступает первичной, а другая - вторичной, связь происходит при помощи полей связи. При связи сопоставляются записи с одинаковыми значениями полей связи. Первичная таблица всегда заполняется первой, а ее поле связи заполняется автоматически (тип данных – счетчик). Вторичная таблица всегда заполняется после заполнения первичной таблицы, значения ее поля связи подставляется из значений поля связи первичной таблицы. Поля связи должны иметь одинаковый тип данных.

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

Для выборки данных в Transact-SQL используется инструкция SELECT. Большинство реальных запросов SQL предназначено для выборки не всех, а определенных строк и столбцов таблиц.

Инструкция SELECT

Извлекает строки из базы данных и позволяет выбирать одну или несколько строк или столбцов в одной или нескольких таблицах. Это основной структурный компонент SQL, используемый для отправки запросов. Инструкция SELECT не изменяет, не вставляет и не удаляет данные.

Синтаксис

SELECT select_list

[ FROM table_source ]

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

Замечания:

Инструкция SELECT описывает запрос к системе. Выполнение запроса не обновляет данные. Результатом запроса является таблица идентично структурированных строк, в каждой из которых содержится одинаковый набор столбцов. Инструкция SELECT однозначно определяет, какие столбцы будут существовать в этой таблице результатов и какие строки ее заполнят. Инструкция SELECT не сообщает системе последовательность выполнения запроса; система сама выполняет запрос оптимальным в данном случае способом, используя внутренний модуль оптимизации на основе сведений о затратах. Результат должен гарантированно отвечать описанной ниже канонической стратегии выполнения. Единственное различие может заключаться в порядке расположения строк в таблице, хотя любой другой порядок может быть задан предложением ORDER BY.

Стратегия выполнения

1. Создайте объединение таблиц в предложении FROM. При использовании явного синтаксиса JOIN результат JOIN будет явным. Если в предложении FROM имеется список таблиц, разделенных запятыми, это неявное объединение таблиц векторным произведением.

2. При наличии предложения WHERE следует применять данные условия поиска к строкам, полученным после шага 1, и сохранять только те строки, которые удовлетворяют условию.

3. Если в предложении SELECT нет объединений, как нет и предложения GROUP BY, перейдите к шагу 7.

4. Если есть предложение GROUP BY, разделите строки, полученные после шага 2, на несколько групп так, чтобы у всех строк в каждой группе было одинаковое значение по всем столбцам группирования. Если предложения GROUP BY нет, поместите все строки в одну группу.

5. К каждой группе, полученной после шага 4, примените предложение HAVING, если таковое указано. Останутся только те группы, которые удовлетворят предложению HAVING.

6. Для каждой группы, полученной после шага 5, создайте одну строку результата путем оценки списка выбора в предложении SELECT в данной группе.

7. Если в предложении SELECT содержится ключевое слово DISTINCT, удалите все повторяющиеся строки, полученные в результате шага 6.

Если есть предложение ORDER BY, выполните сортировку результатов шага 7, как указано выражением порядкам.

SELECT _LIST

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

Синтаксис

SELECT [ ALL | DISTINCT ] TOP (expression) < select_list >

< select_list > ::=

{ *

| { table_name | table_alias }.*

| { column_name | expression } [ [ AS ] column_alias ]

} [ ,...n ]

Замечание: аргументы и ограничения рассматриваются в справке оператора SELECT.

Рассмотрим только аргумент column_alias - альтернативное имя для замены имени столбца в наборе результатов запроса. Например, для столбца «quantity» может быть указан псевдоним «Quantity», «Quantity to Date» или «Qty». Кроме того, псевдонимы используются для указания имен для результатов выражений, например:

SELECT AVG(Peop_STIP) AS Средняя_стипендия

FROM People

Примечание: column_alias можно использовать в предложении ORDER BY, но нельзя использовать в предложениях WHERE, GROUP BY и HAVING.

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

Примечание: возвращающие табличное значение функции не поддерживаются

Синтаксис и аргументы рассматриваются в справке команды.

[ FROM { < table_source > } [ ,...n ]

< table_source > ::=

table_name [ [ AS ] table_alias ]

| < joined_table >

| <derived_table> [ [ AS ] table_alias ]

< joined_table > ::=

< table_source > < join_type > < table_source > ON <search_condition >

| <table_source> CROSS JOIN <table_source>

| <left_table_source> { CROSS | OUTER } APPLY <right_table_source>

| ( < joined_table > )

< join_type > ::=

[ INNER | { { LEFT | RIGHT } [ OUTER ] } ] JOIN ]

left_table_source::= table_source

right_table_source::=table_source

Рассмотрим особенности применения аргумента JOIN:

CROSS JOIN указывает векторное произведение двух таблиц.

INNER указывает, что все совпадающие пары строк возвращены. Отмена несовпадающих строк из обеих таблиц. Задается по умолчанию, если тип объединения не указан.

LEFT [ OUTER ] указывает, что все строки таблицы слева, которые не соответствуют указанному условию, включаются в результирующий набор в дополнение ко всем строкам, которые возвращаются внутренним объединением. Для выходных столбцов таблицы слева указано значение NULL.

RIGHT [ OUTER ] указывает, что все строки таблицы справа, которые не соответствуют указанному условию, включаются в результирующий набор в дополнение ко всем строкам, которые возвращаются внутренним объединением. Для выходных столбцов таблицы справа указано значение NULL.

JOIN показывает, что указанные таблицы следует объединить.

ON < search_condition > указание условия, на котором основывается объединение. Условие может указать любой допустимый предикат, однако часто используются столбцы и операторы сравнения.

Замечания:

1) если тот же запрос может быть написан как с оператором JOIN, так и с ключевым словом APPLY, запрос с применением JOIN может оказаться быстрее;

2) если в right_table_source есть ссылки на таблицы, не привязанные к таблицам, перечисленным в right_table_source, то необходимо либо привести в соответствие имя или псевдоним таблицы в left_table_source, либо привести в соответствие имя или псевдоним таблицы во внешнем предложении FROM (если во вложенном запросе в предложении WHERE или списке SELECT указан оператор APPLY). Если и в первом случае, и во втором имеются совпадающие ссылки, первый имеет больший приоритет;

3) операторы APPLY имеют тот же приоритет, что и операторы JOIN. При отсутствии скобок последовательность операторов JOIN и APPLY будет вычисляться слева направо.

Дополнительные сведения см. в электронной документации по SQL Server в разделах «Использование предложения FROM» и «Использование APPLY».

Примеры:

Следующий пример предоставляет больше сведений об использовании предложения FROM

SELECT Teach_ID, Teach_FAM, Teach_IMYA

FROM Teacher

Использование простого предложения FROM

SELECT Teach_ID, Teach_FAM, Teach_IMYA

FROM Teacher

ORDER BY Teach_ID

Использование CROSS JOIN

В следующем примере возвращается перекрестное произведение двух таблиц, People и Group. Возвращается список всех возможных сочетаний строк Peop_ID и Group_ID:

SELECT S.Peop_ID, G.Grup_Id

FROM People S

CROSS JOIN Grup G

ORDER BY S.Peop_ID, G.Grup_ID

Использование LEFT OUTER JOIN

Следующий пример соединяет две таблицы по столбцу Group_ID и сохраняет строки из левой таблицы, не имеющие соответствий. Таблица People сравнивается с таблицей Group по столбцам Group_ID, которые имеются в обеих таблицах. В результирующем наборе отражаются все студенты (как входящие в группы, так и не входящие):

SELECT S.Peop_FAM ,S.Peop_NAME, G.GRUP_NAME

FROM People S

LEFT OUTER JOIN Grup G ON S.Grup_ID = G.Grup_ID

ORDER BY G.Grup_NAME

Использование INNER JOIN

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

SELECT S.Peop_FAM ,S.Peop_NAME, G.GRUP_NAME

FROM People S

INNER JOIN Grup G ON S.Grup_ID = G.Grup_ID

ORDER BY G.Grup_NAME

Использование RIGHT OUTER JOIN

Следующий пример соединяет две таблицы по столбцу Group_ID и сохраняет строки из левой таблицы, не имеющие соответствий. Таблица Group сравнивается с таблицей People по столбцам Group_ID, которые имеются в обеих таблицах. В результирующем наборе отражаются все группы (как со студентами, так и без):

SELECT S.Peop_FAM ,S.Peop_NAME, G.GRUP_NAME

FROM People S

RIGHT OUTER JOIN Group G ON S.Grup_ID = G.Grup_ID

ORDER BY G.Grup_NAME

DISTINCT - аргумент, который обеспечивает устранение двойных значений в команде SELECT. Предположим, необходимо узнать, какие студенты в настоящий момент имеют хотя бы по одной оценке (сами оценки пока не нужны, нужен только список кодов студентов):

SELECT Peop_ID

FROM Evolution

При выполнении этой команды, коды дублируются (некоторые студенты имеют по несколько оценок). Для получения списка без дубликатов надо ввести:

SELECT DISTINCT Peop_ID

FROM Evolution

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

Если вместо DISTINCT указать ALL, это будет иметь противоположный эффект, дублирование строк сохранится. Поскольку это тот же самый случай, когда не указывается никакой аргумент, то ALL, по существу, лишь пояснительный аргумент.

Ограничение строк таблицы. Таблицы имеют тенденцию становиться очень большими. Поскольку обычно только некоторые их них интересуют Вас в данное время, имеется возможность устанавливать критерии, чтобы определить, какие строки будут выбраны. Для этого используется ключевое слово WHERE - предложение команды SELECT, позволяющее задавать условие, которое может быть верным или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы, для которых это условие верно. Синтаксис предложения WHERE:

WHERE имя_столбца оператор_сравнения значение

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

Предположим, необходимо получить имена и фамилии всех студентов с отчеством “Николаевич”:

SELECT Peop_FAM , Stud_IMA

FROM People

WHERE Peop_OTCH = 'Николаевич'

Когда в запросе имеется предложение WHERE, SQL Server просматривает всю таблицу построчно и исследует каждую строку, чтобы определить верно ли утверждение.

Примечание: строковые константы типа 'Москва' в Transact-SQL ограничиваются либо апострофами ', либо кавычками ".

Теперь попробуем построить запрос с числовым полем в предложении WHERE. Выберем всех студентов со стипендией 1200:

SELECT *

FROM People

WHERE Peop_STIP = 1200

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

SELECT SUM(Peop_STIP) AS ' Сумма стипендии'

FROM People

WHERE Group_ID=5

Сортировка вывода. Обычно требуется, чтобы данные как-то были упорядочены. Упорядочение задается с помощью ключевого слова ORDER BY (по умолчанию упорядочение по возрастанию). Синтаксис предложения ORDER BY:

ORDER BY Столбец1 | Целое_значение [ACS | DESC]

Например, выведем список студентов в алфавитном порядке: SELECT Peop_ID, Peop_FAM

FROM People

ORDER BY Peop_FAM

Если после имени столбца указать имя еще одного столбца, то по значениям второго столбца будут упорядочены строки, содержащие одинаковые значения в первом столбце. Столбец, указанный в списке ключевого слова ORDER BY, можно заменить числом, соответствующим порядку столбца в списке (параметр Целое_значение ):

SELECT Grup_ID, Peop_ID, Peop_FAM

FROM People

ORDER BY 1, 3 DESC

Параметры ASC (ascending) – по возрастанию, DESC (descending) – по убыванию задают порядок сортировки.

Примечания:

- столбцы данных типа text и image нельзя использовать в предложении ORDER BY;

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

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

Замечание: в предложении ORDER BY нет ограничения по числу элементов.

При использовании предложения ORDER BY с инструкцией UNION сортируемые столбцы должны быть именами или псевдонимами столбцов, указанными в первой инструкции SELECT. Например, первая из следующих инструкций SELECT выполнится успешно, а во время выполнения второй произойдет сбой:

Create t1 (col1 int, col2 int);

Create t2 (col3 int, col4 int);

SELECT * from t1

UNION

SELECT * from t2

ORDER BY col1;

Данная инструкция успешно выполняется, поскольку имя «col1» принадлежит первой таблице (t1)

SELECT * from t1

UNION

SELECT * from t2

ORDER BY col3;

Данная инструкция дает сбой, поскольку имя «col3» не принадлежит первой таблице (t1)

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

Операторы отношения:

1) = Равно

2) > Больше чем

3) < Меньше чем

4) >= Больше или равно

5) <= Меньше или равно

6) <> Не равно

Они имеют стандартные значения для символьных значений и дат.

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

Предположим, что нам надо увидеть всех студентов со стипендией выше 1200:

SELECT *

FROM People

WHERE Peop_STIP > 1200

Логические операторы:

- AND логическое "И"

- OR логическое "ИЛИ"

- NOT логическое отрицание

Оператор AND сравнивает два логических значения и возвращает TRUE (истина), если оба значения истинны (т.е. равны TRUE), в остальных случаях - FALSE (ложь). Оператор OR возвращает TRUE, если хотя бы один из аргументов равен TRUE. Оператор NOT возвращает TRUE, если его аргумент равен FALSE и наоборот.

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

Логический оператор AND (логическое И) имеет смысл, если возвращаемые в результате выполнения запроса строки должны удовлетворять обоим условиям сравнения, заданным в предложении WHERE:

SELECT *

FROM People

WHERE Peop_NAME = 'Алексей' AND

Peop_ADDRESS = 'Таугуль'

Если в таблице имеется две строки, в которых значение в столбце Stud_IMA равно 'Алексей' (то есть имеются тезки), то логический оператор AND позволяет выбрать ту строку, в которой значение столбца Peop_ADDRESS равно «Таугуль» (то есть того Алексея, который живет в Таугуле).

C помощью оператора OR можно выбрать несколько значений из одного столбца:

SELECT *

FROM People

WHERE Peop_NAME = 'Алексей' OR

Peop_NAME = 'Николай'

Оператор NOT указывается перед столбцом в операциях сравнения:

SELECT *

FROM People

WHERE NOT Peop_ADDRESS = 'Таугуль'

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

Для группировки выражений Transact-SQL позволяет использовать круглые скобки ( ).

Например:

SELECT *

FROM People

WHERE NOT (Peop_NAME = 'Алексей' OR

Peop_NAME = 'Николай')

Специальные операторы: IN, BETWEEN, LIKE, IS NULL. Оператор IN определяет список значений, в который должно входить значение поля. Например, если необходимо найти всех студентов с именем 'Алексей' и 'Николай', можно использовать такой запрос:

SELECT *

FROM People

WHERE Peop_NAME IN ('Алексей' , 'Николай')

Набор значений для оператора IN заключается в круглые скобки, значения разделяются запятыми.

Оператор BETWEEN похож на оператор IN. В отличие от списка допустимых значений, BETWEEN определяет диапазон значений. В запросе необходимо указать слово BETWEEN, затем начальное значение, ключевое слово AND и конечное значение. Первое значение должно быть меньше второго. Следующий запрос выберет студентов с оценками между 3 и 5:

SELECT *

FROM Evolution

WHERE Ocenka BETWEEN 3 AND 5

Оператор LIKE применим только к символьным полям, с которыми он используется, чтобы находить подстроки, то есть, он ищет поле символа, чтобы видеть совпадает ли с условием часть его строки. В качестве условия он использует специальные символы:

- символ подчеркивания '_' замещает любой одиночный символ. Например, 'к_т' будет соответствовать 'кот' и 'кит', но не 'крот';

- знак процента % замещает последовательность любого числа символов. Например, '%м%р' будет соответствовать 'компьютер' и 'омар'.

Давайте выберем студентов, чьи имена начинаются с буквы 'О':

SELECT *

FROM People

WHERE Peop_NAME LIKE 'О%'

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

Часто необходимо различать строки, содержащие значения NULL в каком-либо столбце. Так как NULL указывает на отсутствие значения, для сравнненя существует специальный оператор - IS NULL. Выберем из нашей базы студентов с NULL значениями в столбце Peop_STIP:

SELECT *

FROM People

WHERE Peop_STIP IS NULL

3.2.2 Использования стандартные функций

Математические функции

Замечание: в качестве параметров функции будем указывать соответствующий им тип данных.

- ABC (numeric) – модуль числа;

- ACOS/ASIN/ATAN (Float) – арккосинус, арксинус, арктангенс в радианах;

- COS/SIN/TAN/COT (Float) – косинус, синус, тангенс, котангенс;

- CEILING (Numeric) – наименьшее целое, большее или равное параметру в скобках;

- DEGREES (Numeric) – преобразует радианы в градусы;

- EXP(Float) – экспонента, е^х;

- FLOOR(Numeric) – наименьшее целое меньшее или равное выражению numeric;

- LOG(Float) – натуральный логарифм ln;

- LOG10(Float) – десятичный логарифм log₁₀;

- PI () – число пи;

- POWER (Numeric,y) – возводит выражение Numeric в степень у;

- RADIANS (Numeric) – преобразует градусы в радианы;

- RAND () – генерирует случайное число типа данных Float, расположенное между нулем и единицей;

- ROUND (Numeric, Длина) – округляет выражение Numeric до заданной Длины (количество знаков после запятой);

- SIGN (Numeric) – выводит знак числа +/- или ноль;

- SQUARE (Float) – вычисляет квадрат числа Float;

- SQRT (Float) – вычисляет квадратный корень числа Float.

Примеры использования математических функций:

- SELECT ABC(-10) результат 10

- SELECT SQRT (16) результат 4

- SELECT ROUND (125.85,0) результат 126

- SELECT POWER (2,4) результат 16

Строковые функции

Строковые функции позволяют производить операции с одной или несколькими строками:

- 'Строка1'+ 'Строка2' присоединяет Строку1 к Строке2;

- ASCII(Char) возвращает ASCII код с самого левого символа выражения Char;

- CHAR(Int) выводит символ соответствующий ASCII коду в выражении Int;

- CHARINDEX(Образец, Выражение) выводит позицию Образца выражения, то есть где находится Образец в Выражении;

- DIFFERENCE(Выражение1, Выражение2) сравнивает два выражения, выводит числа от 0 до 4: 0 – выражения абсолютно различны; 4 – выражения абсолютно идентичны. Оба выражения типа данных Char;

- LEFT(Char, Int) выводит из строки Char Int символов слева;

- RIGHT(Char, Int) выводит из строки Char Int символов справа;

- LTRIM(Char) удаляет из строки Char пробелы слева;

- RTRIM(Char) удаляет из строки Char пробелы справа;

- WCHAR(Int) выводит выражение Int в формате Unicode;

- REPLACE(Строка1, Строка2, Строка3) меняет в Строке1 все элементы Строка2 на элементы Строка3;

- REPLICATE(Char, Int) повторяет строку Char Int раз;

- REVERSE(Сhar) производит инверсию строки Char, то есть располагает символы в обратном порядке;

- SPACE(Int) выводит Int пробелов;

- STR(Float) переводит число Float в строку;

- STUFF(Выражение1, Начало, Длина, Выражение2) удаляет из Выражения1, начиная с позиции символа Начало, количество символов, равное параметру Длина, вместо них подставляет Выражение2;

- SUBSTRING(Выражение, Начало, Длина) из Выражения выводится строка заданной Длины, начиная с позиции Начало;

- UNICODE(Char) выводит код в формате Unicode первого символа в строке Char;

- LOWERC(Char) переводит строку Char в маленькие буквы;

- UPPER(Char) переводит строку Char в заглавные буквы.

Примеры применения строковых функций:

- SELECT ASCII('G') - результат 71;

- SELECT LOWER(‘ABC’) - результат abc;

- SELECT Right(‘ABCDE,3’) - результат CDE;

- SELECT REVERCE('МИР') - результат РИМ.

Замечание. во всех строковых функциях значения выражения типа Char заключаются в одинарные кавычки.

Функции дат

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

- dd – число дат (от 1 до 31);

- dy – день года (число от 1 до 366);

- hh – значение часа (0-23);

- ms – значение секунд (от 0 до 999);

- mi – значение минут (0-59);

- qq – значение (1-4);

- mm – значение месяцев (1-12);

- ss – значение секунд (0-59);

- wk – значение номеров недель в году;

- dw – значение дней недели, неделя начинается с воскресенья (1-7);

- yy – значение лет (1753 -999).

Функции дат предназначены для работы с датами или времени. Существуют несколько следующие функции дат:

- DATEADD(часть, число, date) добавляет к дате date часть даты увеличенное на число;

- DATEDIFF(часть, date1, date2) выводит количество частей даты между date1 и date2;

- DATENAME(часть, date) выводит символьное значение частей даты к заданной дате (название дней недели);

- DATEPART(часть, date) выводит числовое значение части даты из заданной даты (номер месяца);

- DAY(date) выводит количество дней в заданной дате;

- MONTH (date) выводит количество месяцев в заданной дате;

- YEAR(date) выводит количество лет в заданной дате;

- GETDATE() выводит текущую дату установленную на компьютере;

Замечание: даты выводятся в Американском формате: месяц/день/год.

Примеры функции работ с датами:

- SELECT DATEADD(dd,5,11/20/07) - результат Nov/25/2007;

- SELECT DATEDIFF(dd,11/20/07, 11/25/07) – результат 5 дней;

- SELECT DATENAME(mm, 11/20/07) - результат November;

- SELECT DATEPART(mm, 11/20/07) - результат 11.

Замечание: в выражениях оператора SELECT можно использовать операции сравнения. В результате будет либо истина TRUE, либо ложь FALSE. Можно использовать следующие операторы: =, <, >, >=, <=, <>, !<(не меньше), !>(не больше), !=(не равно). Приоритет операции задается круглыми скобками.

Системные функции

Системные функции предназначены для получения информации о базе данных и ее содержимом. В SQL сервере существуют следующие системные функции:

- COL_LENGTH(таблица, поле) выводит ширину поля;

- DATALENGTH(выражение) выводит длину выражения;

- GETAHSINULL(имя БД) выводит допустимо или недопустимо использовать в БД значение NULL;

- IDENTINCR(таблица) выводит шаг увеличения поля счетчика в таблице;

- IDENT_SEED(таблица) выводит начальное значение счетчиков в таблице;

- ISDATE(выражение) выводит единицу, если выражение является датой и ноль, если не является;

- ISNUMERIC(выражение) выводит единицу, если выражение является числовым и ноль, если не числовым;

- NULIFF(выражение1, выражение2) выводит ноль если выражение1 равно выражению 2.

Агрегатные функции

Агрегатные функции позволяют вычислять итоговые значения по полям таблицы:

- AVG(поле) – выводит среднее значение поля;

- COUNT(*) – выводит количество записей в таблице;

- COUNT(поле) – выводит количество всех значений поля;

- MAX(поле) – выводит максимальное значение поля;

- MIN(поле) – выводит минимальное значение поля;

- STDEV(поле) – выводит средне квадратичное отклонение всех значений поля;

- STDEVP(поле) – выводит среднеквадратичное отклонение различных значений поля;

- SUM(поле) – суммирует все значений поля;

- TOP n [Percent] – выводит n первых записей из таблица, либо n% записей из таблицы;

- VAR(поле) – выводит дисперсию всех значений поля;

- VARP(поле) – выводит дисперсию всех различных значений поля.

Примеры использования агрегатных функций:

- SELECT AVG(возраст) FROM Студенты – выводит средний возраст студента из таблицы «Студенты».

- SELECT COUNT(ФИО) FROM Студенты – выводит количество различных ФИО из таблицы «Студенты».

- SELECT Top 100 * FROM Студенты – выводит первые 100 студентов из таблицы «Студенты».

4 Лабораторная работа. Создание сложных и динамических запросов

Цель работы:

- научить студентов создавать сложные запросы к базе данных скриптом и в графической среде СУБД MS SQL Server 2008;

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

4.1 Задание на лабораторную работу

Получите следующую информацию из базы данных:

1) Список преподавателей, ведущих дисциплины «Информатика» и «Физика».

2) Список студентов, имеющих неудовлетворительные оценки.

3) Список студентов, не сдавших экзамен по высшей математике.

4) Список преподавателей кафедры «Информатика».

5) Список кафедр с указанием фамилий заведующих кафедр.

6) Список названий групп с указанием фамилий старост этих групп.

7) Списки студентов каждой группы с их оценками по всем предметам.

8) В каких группах проводятся занятия по предмету Информатика»?

9) Какие виды занятий по Информатике проводятся в первой гуппе?

10)Сколько часов занятий по каждому предмету в каждой группе проводится в семестре?

Список студентов, среднюю оценку и оценку его обучения («отличник», «хорошист», «троечник»)

11) Создать процедуру, вычисляющую среднее трех чисел.

12) Создать хранимую процедуру для отбора студентов из таблицы студенты по их «ФИО».

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

1. Можно ли иметь несколько ключевых слов AND в выражении, заданном ключевым словом WHERE?

2. Что такое рекурсивное связывание?

3. При связывании таблиц должны ли они связываться в том же порядке, в каком они указаны в выражении ключевого слова FROM?

4. При использовании в операторе запроса таблицы-связки обязательно ли выбирать в запросе ее столбцы?

5. Можно ли связывать в запросе не один, а несколько столбцов таблиц?

6. Какая часть оператора SQL задает условия связывания таблиц? 7. Что будет, если в запросе указать выборку из двух таблиц, но не связать их?

8. Для чего используются псевдонимы таблиц? 9 . Что такое подзапрос? Как он работает?

4.2 Методические указания к выполнению лабораторной работы

4.2.1 Объединение таблиц в запросах

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

Пример:

SELECT A.Teach_ID, B.Chair_NAME

FROM Teacher A

INNER JOIN Chair B ON A.Chair_ID=B.Chair_ID

Рекурсивное связывание удобно использовать, когда все необходимые данные размещаются в одной таблице, но требуется каким-то образом сравнить одни записи таблицы с другими. В нашей базе в таблице People хранятся фамилии студентов и номера старост этих студентов. Получим список студентов с фамилиями их старост:

SELECT A.Peop_FAM , B.Peop_FAM

FROM People A

LEFT JOIN People B ON A.Peop_MAN = B.Peop_ID

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

SELECT People.Peop_FAM , Subject.Subj_NAME , Evolution.OCENKA

FROM People

JOIN Evolution ON Stud.Peop_ID = Evolution.Peop_ID

JOIN Subject ON Subject.subj_id = Evolution.subj_id

4.2.2 Использование подзапросов. Связанные подзапросы

Запросы могут управлять другими запросами. Это делается путем помещения запроса внутрь условия другого запроса и использования вывода внутреннего запроса в верном или неверном условии.

Обычно внутренний запрос генерирует значение, которое проверяется в условии внешнего запроса, определяющего верно оно или нет. Например, мы знаем фамилию студента - Сидоров, но не знаем его кода (Peop_ID), и хотим получить все его оценки из таблицы Evolution: SELECT *

FROM Evolution

WHERE Peop_ID = (

SELECT Peop_ID

FROM People

WHERE Peop_FAM = 'Сидоров' )

Чтобы выполнить внешний (основной запрос), сначала выполняется внутренний запрос (подзапрос) внутри предложения WHERE. При выполнении подзапроса просматривается таблица People, в которой выбираются строки, где поле Peop_FAM равно 'Сидоров', затем извлекается значение поля Peop_ID. Пусть единственной строкой будет Peop_ID = 30104. Далее полученное значение помещается в условие основного запроса, вместо самого подзапроса, так что условие примет вид:

WHERE Peop_ID = 301004

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

Если Вы хотите использовать подзапрос, который возвращает несколько строк, то необходимо использовать оператор IN. Например, если в базе несколько студентов с фамилией Сидоров и все имеют оценки:

SELECT *

FROM Evolution

WHERE Peop_ID IN (

SELECT Peop_ID

FROM People

WHERE Peop_FAM = 'Сидоров' )

Найдем все оценки для предмета ТЭЦ:

SELECT Evolution.OCENKA

FROM Evolution

JOIN subject ON Evolution.subj_id= subject.subj_id

WHERE Evolution.Subj_ID IN (

SELECT Subj_ID

FROM Subject

WHERE Subj_NAME = 'ТЭЦ')

Этот результат можно получить и с помощью объединения:

SELECT Evolution.OCENKA

FROM subject

JOIN Evolution ON subject.subj_id=Evolution.subj_id

WHERE Subj_NAME = 'ТЭЦ'

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

Все вышеприведенные подзапросы объединяет то, что все они выбирают один единственный столбец. Это обязательно, поскольку их результат сравнивается с одиночным значением. Команды типа SELECT * запрещены в подзапросах.

Подзапросы можно также использовать в предложении HAVING. Эти подзапросы могут использовать собственные предложения GROUP BY или HAVING. Следующий запрос является тому примером:

SELECT Ocenka, COUNT (DISTINCT Peop_ID )

FROM Evolution

GROUP BY Ocenka

HAVING Ocenka > (

SELECT AVG(ocenka )

FROM Evolution

WHERE Pr_DATE >01/09/05)

Эта команда подсчитывает студентов с оценкой выше средней, сдавших экзамен после 01.09.05.

Связанные подзапросы. При использовании подзапросов можно обратиться во вложенном подзапросе к таблицам из внешнего подзапроса. Например, как найти всех студентов, сдавших экзамены 1 марта :

SELECT *

FROM Stud C

WHERE '2008-03-01' IN (

SELECT Pr_DATE

FROM Evolution O

WHERE O.Peop_ID = C.Peop_ID )

Связанные запросы можно использовать для сравнения таблицы с собой. Например, можно найти всех студентов с баллом выше среднего:

SELECT Peop_ID

FROM Evolution O

WHERE ocenka > (

SELECT AVG(ocenka )

FROM Evolution O1

WHERE O1.Peop_ID = O.Peop_ID )

Использование оператора EXISTS. Оператор EXISTS берет подзапрос как аргумент и оценивает его как верный, если подзапрос возвращает какие-либо записи, и неверный, если тот не делает этого. Вот как выполняется поиск всех групп, в которых учится хотя бы один студент:

SELECT Grup_ID

FROM [Gruppa] G

WHERE EXISTS (

SELECT *

FROM People

WHERE Stud.Grup_ID = G.Grup_ID)

Внутренний подзапрос выбирает всех студентов, которые учатся в группе. Оператор EXISTS во внешнем условии отмечает, что подзапрос вернул некоторые данные, следовательно, условие верное. Подзапрос будет выполнен один раз для всего внешнего запроса и имеет одно значение во всех случаях. Поэтому EXISTS, когда используется таким образом, делает условие верным или неверным для всех строк сразу.

Использование операторов ANY, ALL. Рассмотрим новый способ поиска студента, сдавшего экзамен:

SELECT * FROM People

WHERE Peop_ID = ANY (

SELECT Peop_ID

FROM Evolution)

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

В приведенном выше запросе можно было бы использовать оператор IN. Однако оператор ANY можно применять не только с оператором равенства.

Оператор ALL считает условие верным, если каждое значение, выбранное подзапросом, удовлетворяет условию внешнего запроса. Выберем тех студентов, у которых стипендия выше 1500:

SELECT *

FROM People

WHERE Peop_STIP > ALL(

SELECT Peop_STIP

FROM People

WHERE Peop_STIP = 1500 )

Использование команды UNION. Команда UNION объединяет вывод нескольких запросов с исключением повторяющихся строк. Например, приведенный ниже запрос выводит всех студентов и преподавателей, чьи фамилии размещены между буквами К и С:

SELECT Peop_FAM

FROM People

WHERE Peop_FAM BETWEEN 'К' AND 'С'

UNION

SELECT Teach_FAM

FROM Teacher

WHERE Teach_FAM BETWEEN 'К' AND 'С'

Для применения команды UNION существует 2 правила:

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

- типы данных должны быть совместимы.

UNION автоматически исключает дубликаты строк из вывода. Если вы хотите, чтобы все строки из запросов попали в результат, используйте UNION ALL:

SELECT Peop_FAM

FROM People

UNION ALL

SELECT Teach_FAM

FROM Teacher

Использование оператора CASE. Функция CASE, наверное, одна из наиболее редко используемых в SQL. Чаще всего её используют для генерации вариантов значений поля в выборках, однако, у этой функции есть ещё много полезных применений. Например, приведенный ниже запрос выводит всех студентов и статус:

SELECT Peop_FAM as 'Фамилия' +' ' + Stud_Ima as 'Имя',

case

when Peop_STIP is NULL then 'Платник'

else 'Грантник'

end as 'Статус'

FROM People

Использование оператора COALESCE. Возвращает первое выражение из списка аргументов, не равное NULL. Данный запрос выводит всех студентов, их стипендию. Если поле со стипендией будет пустым, т.е. иметь значение NULL, то оно заменится на другой аргумент :

SELECT Peop_FAM as 'Фамилия', Stud_Ima as 'Имя',

COALESCE (cast(Peop_STIP as char(10)), 'Платник') as 'Стипендия'

FROM People

4.2.3 Создание динамических запросов при помощи хранимых процедур

Хранимая процедура – SQL запрос, который имеет параметры, то есть он выполняется как обычная процедура (мы задаем ее имя и передаем в хранимую процедуру значение параметров.) В зависимости от значения параметров хранимой процедуры мы получаем тот или иной результат запроса.

Замечание: в SQL сервере хранимые процедуры реализуют динамические запросы, выполняемые на стороне сервера.

Рассмотрим создание хранимых процедур при помощи команд языка SQL. Чтобы отобразить хранимые процедуры рабочей БД панели «Object Explorer», нужно выделить пункт «Stored Procedures». Чтобы создать новую процедуру при помощи команд языка SQL, нужно щелкнуть ЛКМ по кнопке на панели инструментов.

В рабочей области окна сервера появится вкладка SQLQuery1.sql, где нужно набрать код новой процедуры, который имеет следующий синтаксис:

CREATE PROCEDURE <Имя процедуры>

[@<Параметр1> <Тип1>[=<Значение1>],

@<Параметр2> <Тип2>[=<Значение2>], …]

[WITH ENCRYPTION]

AS <Команды SQL>

Здесь:

- Имя процедуры – имя создаваемой хранимой процедуры.

- Параметр1, Параметр2, … - параметры передаваемые в процедуру.

- Значение1, Значение2, … - значения параметров по умолчанию.

- Тип1, Тип2, … - типы данных параметров.

- WITH ENCRYPTION – включает шифрование данных.

- Команды SQL – SQL запрос, который выполняется при запуске процедур.

Замечание: SQL запрос включает в себя параметры, если параметры сравниваются с какими-то полями или выражениями, то они должны иметь точно такой же тип данных, как эти поля или выражения.

Замечание: после создания процедура помещается в раздел Stored Procedures текущей БД на панели «Object Explorer». Если дважды щелкнуть по процедуре ЛКМ, то она откроется для редактирования на вкладке «SQLQuery».

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

EXEC SP_HELPTEXT <Имя процедуры>

Хранимые процедуры могут быть запущены следующей командой

EXEC <Имя процедуры> [<Параметр1>, <Параметр2>, …]

Здесь:

- <Имя процедуры> - имя выполняемой процедуры;

- Параметр1, Параметр2, … - значение параметров.

Пример: создание хранимой процедуры, который выводит имя студентов, у которых средний балл больше заданной величины:

CREATE PROCEDURE СрБАЛЛ

@X Real

SELECT *

FROM Студенты

WHERE

(Оценка1+ Оценка2+ Оценка3)/3>@X

Команда вызова вышенабранной процедуры выглядит следующим образом:

EXEC СрБАЛЛ 4

Команда выводит всех студентов, у которых средний балл больше 4.

Хранимая процедура имеет следующую структуру:

1) область настройки параметров синтаксиса процедуры. Позволяет настраивать некоторые синтаксические правила, используемые при наборе кода процедуры. В нашем случае это:

- SET ANSI_NULLS ON включает использование значений NULL (Пусто) в кодировке ANSI:

- SET QUOTED_IDENTIFIER ON включает возможность использования двойных кавычек для определения идентификаторов;

2) область определения имени процедуры (Procedure_Name) и параметров передаваемых в процедуру (@Param1, @Param2).

Определение параметров имеет следующий синтаксис:

@<Имя параметра> <Тип данных> = <Значение по умолчанию>

Параметры разделяются между собой запятыми;

3) начало тела процедуры, обозначается служебным словом «BEGIN»;

4) тело процедуры содержит команды языка программирования запросов T-SQL;

5) конец тела процедуры обозначается служебным словом «END».

Замечание: в коде зеленым цветом выделяются комментарии. Они не обрабатываются сервером и выполняют функцию пояснений к коду. Строки комментариев начинаются с подстроки “–“. Далее в коде мы не будем отображать комментарии, они будут свернуты. Слева от раздела с комментариями будет стоять знак “+”, щелкнув по которому можно развернуть комментарий.

Наберем код процедуры вычисляющей среднее трех чисел, как это показано на следующем примере:

SET ANSI_NULLS ON

GO [Запуск команду]

SET QUOTED_IDENTIFIER ON

GO [Запуск команду]

CREATE PROCEDURE [Среднее трех величин]

@Value1 Real=0,

@Value2 Real = 0,

@Value3 Real = 0

BEGIN

SET NOCOUT ON;

SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3

END

GO [Запуск процедуру]

Рассмотрим код данной процедуры более подробно:

1) CREATE PROCEDURE [Среднее трех величин] определяет имя создаваемой процедуры как «Среднее трех величин»;

2) @Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;

3) SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 вычисляет среднее и выводит результат с подписью «Среднее значение».

5 Лабораторная работа. Представления

Цель работы: научить студентов создавать представления к базе данных скриптом и в графической среде СУБД MS SQL Server 2008.

5.1 Задание на лабораторную работу

Создайте следующие представления:

a) отображающее список групп и старост этих групп;

b) отображающее список студентов, не сдавших экзамен по предмету;

c) которое отображало бы содержимое таблицы «Успеваемость» в удобном для пользователя виде (без идентификаторов);

d) отображающее список студентов, живущих в общежитии;

e) отображающее форматированный список студентов в одном поле (формат: Сидоров А.С. (группа));

f) отображающее список преподавателей, предметы и группы, в которых ведутся эти предметы.

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

1. Что такое представление?

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

3. Что означают понятия «Вертикальный», «Горизонтальный» фильтры?

4. Что случится, если таблица, на основе которой строится представление, будет удалена?

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

6. Содержит ли данные представление?

7. Для достижения каких целей используют представления?

5.2 Методические указания к выполнению лабораторной работы

5.2.1 Общие сведения о представлениях

Представление (view) является своего рода виртуальной таблицей, обеспечивающей доступ пользователей к некоторому подмножеству данных, составленному из одной или более таблиц. Для пользователя представление как обычная таблица, но при этом само по себе оно не содержит данных. Физически представление реализовано в виде SQL-запроса, на основе которого производится выборка данных из одной или более таблиц или представлений. Таблицы, на основе которых создается представление, называются базовыми (base table). В самом простом случае можно создать представление на базе одной таблицы, которое будет содержать точно такой же набор данных, как и исходная таблица. На практике такие представления редко используются. Более сложные представления могут объединять столбцы из нескольких таблиц. При этом, задав условие для выборки, можно сделать доступным из представления только ограниченное множество строк из этих таблиц. Когда из представления исключается один или более столбцов базовой таблицы, говорят, что на таблицу наложен вертикальный фильтр. Если в определении представления установлено одно или более условий для выборки строк, говорят, что на таблицу наложен горизонтальный фильтр.

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

Представления создаются с помощью оператора CREATE VIEW. Аналогично оператору CREATE TABLE оператор CREATE VIEW можно использовать только для создания представления, которого до сих пор не существовало.

CREATE VIEW имя_представления

SELECT список_выбора

[ FROM таблицы_источники ]

[ WHERE условие_отбора ]

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

Представление на базе одной таблицы. Создадим представление StudAddress из таблицы People:

CREATE VIEW StudAddress

SELECT Peop_ID, Peop_FAM , Stud_IMA,Peop_ADDRESS

FROM People

Создадим представление StudStip из таблицы People:

CREATE VIEW StudStip

SELECT distinct Peop_FAM ,Peop_STIP

FROM stud

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

Для удаления представлений из базы данных используется команда DROP VIEW имя_представления. У этой команды есть две опции – RESTRICT и CASCADE. Если используется RESTRICT и в условиях имеются зависимые представления, то оператор DROP VIEW возвращает ошибку. При использовании опции CASCADE и наличии зависимых представлений оператор DROP VIEW завершается успешно, и все зависимые представления тоже удаляются.

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

Представления удаляются с помощью оператора DROP VIEW <имя_представления >:

DROP VIEW StudAddress

5.2.2 Представление на базе нескольких таблиц

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

Создадим представление PrepodChair на базе двух таблиц teacher и chair, которое бы содержало фамилии преподавателей, работающих на кафедрах:

CREATE VIEW PrepodChair

SELECT teacher.teach_fam, chair.chair_name

FROM teacher, chair

WHERE teacher.chair_id=chair.chair_id

Создадим представление Studoсenka из таблиц People, Subject и Evolution, в котором отражается список студентов с их оценками по предметам:

CREATE VIEW Studocenka

SELECT stud.Peop_FAM , subject.subj_name, Evolution.ocenka

FROM stud,subject,Evolution

WHERE (Evolution.Peop_ID=stud.Peop_ID) and (Evolution.subj_id=subject.subj_id)

Представления чрезвычайно полезны для упрощения иcпользования вычисляемых полей. Создадим представление Otli4n, в котором отражается количество отличников по предметам в группе:

CREATE VIEW Otli4n

SELECT g.grup_name as [Группа], s.subj_name as [Предмет],

count(p.ocenka) as[Колличество отличников]

FROM Evolution p join gruppa g on g.grup_id = p.grup_id

JOIN subject s on s.subj_id = p.subj_id where p.ocenka in (8,9)

GROUP BY g.grup_name, s.subj_name

Таким образом, представления могут значительно упростить сложные операторы SQL. Используя представления, можно один раз записать код SQL и затем повторно использовать его, если возникает такая необходимость.

Другим наиболее частым случаем использования представлений является переформатирование выбранных данных. Предположим, что результаты регулярно требуются в определенном формате. Вместо того, чтобы выполнять объединение каждый раз, когда в этом возникает необходимость, можно создать представление и использовать его вместо объединения.

Можно использовать представления для переформатирования данных. Создадим представление, отражающее преподавателей, работающих на кафедрах в определенном формате:

CREATE VIEW PrepodChair2

SELECT left(Teach_IMA,1) +'.'+left(Teach_OTCH,1) +'.'+ left(Teach_FAM,15)+'('+left(teach_position,10)+')' AS [Teacher] , chair_name AS [Chair]

FROM Teacher, chair

WHERE chair.chair_id=teacher.chair_id

5.2.3 Представление на базе представлений

Поскольку представлнения представляют собой виртуальные таблицы, то на их базе также можно создавать другие представления. Допустим, нам требуется создать представление, отображающее количество грантников и платников на специальностях. Данное представление, конечно, можно сделать на основе сложных запросов, однако, сделаем его на базе двух представлений Grantn (грантники) и Platn (платники).

Сначала необходимо создать и заполить таблицу Speciality (Специальности):

CREATE TABLE Speciality (

Spec_ID int not null PRIMARY KEY,

Spec_NAME char (20) not null,)

Далее необходимо добавить атрибут spec_id в таблицу People, создать внешний ключ на spec_id в таблице Spec:

ALTER TABLE stud

ADD spec_id int foreign key references Spec (spec_id)

Создадим представление Grantn, отражающее специальность и количество грантов на этой специальности:

CREATE VIEW Grantn

SELECT spec.spec_name, count (distinct Peop_ID) as 'grant'

FROM People, Spec

WHERE stud.spec_id=spec.spec_id and Peop_STIP is not null

GROUP BY spec_name

Создадим представление Plat, отражающее специальность и количество платников на этой специальности:

CREATE VIEW plat

SELECT spec.spec_name, count (distinct Peop_ID) as 'plat'

FROM stud, Spec

WHERE stud.spec_id=speciality.spec_id and Peop_STIP is null

GROUP BY spec_name

Теперь создадим собственно само представление StudSpecGP, отражающее специальность и количество платников, грантников на базе представлений Grantn и Plat:

CREATE VIEW plat

SELECT p.spec_name AS ' Speciality' ,

(SELECT [grant] FROM Grantn WHERE grantn.spec_name= g.spec_name) AS 'Gratniki',

(SELECT [plat] FROM plat WHERE plat.spec_name= p.spec_name) AS 'Platniki'

FROM grantn g, plat p

WHERE g.spec_name=p.spec_name

GROUP BY g.spec_name, p.spec_name

6 Лабораторная работа. Функции и триггеры

Цель работы: научить студентов создавать функции и триггеры к базе данных скриптом и в графической среде СУБД MS SQL Server 2008.

6.1 Задание на лабораторную работу

Создайте процедуры, позволяющие выполнить следующие действия:

1) Отчислить/Зачислить студента.

2) Увеличить суммы стипендий всех студентов на 15%.

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

4) Найти неуспевающих студентов.

5) Объединить две группы в одну.

6) Закрепление преподавателя по предмету за определенными группами, у которых преподаватель ведет предмет (Ввод информации в таблицу Study).

7) В вузы на очную форму обучения принимаются абитуриенты моложе 35 лет. Создайте триггер, позволяющий контролировать возраст студента при выполнении зачисления студента (ввода данных в таблицу People).

8) Теоретически в БД можно ошибочно внести оценку студенту по предмету, который он не изучает вовсе. Задача: разработать триггер, контролирующий (сумму) количество сданных предметов, которые не должны превышать количество предметов изучаемых группой студента (таблицы Evolution и Subject) с информацией о предметах изучаемых студентами(таблица Study).

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

10) Теоретически в БД можно ошибочно ввести стипендию студенту, который закрыл сессию с удовлетворительными оценками. Задача: разработать триггер, контролирующий оценки, полученные студентом, и наличие его стипендии.

11) Создать функцию, возвращающую количество студентов в конкретной группе.

12) Создать функцию, возвращающую количество грантников на конкретной специальности.

Контрольные вопросы В чем преимущества использования функций?

1. Каковы различия между процедурами и функциями?

2. Когда выполняются триггеры – до или после выполнения команд INSERT, UPDATE и DELETE?

4. Можно ли изменить триггер?

6.2 Методические указания к выполнению лабораторной работы

6.2.1 Функции

Для расширения возможностей Transact-SQL по обработке данных в SQL Server реализован ряд встроенных функций.

Существующее многообразие встроенных функций SQL Server можно условно разделить на несколько категорий:

- математические функции;

- строковые функции;

- функции для работы с датами;

- статистические функции;

- криптографические функции;

- ранжирующие функции;

- функции приведения типов;

- системные функции.

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

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

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

Для создания функции следует использовать оператор CREATE FUNCTION следующего формата:

CREATE FUNCTION имя_функции

([ < @имя_параметра> AS <тип_данных> [=<значение_по_умолчанию>]])

RETURNS <возвращаемый_тип_данных>

операторы SQL

Функция определения возраста студента (например, мы ходим узнать возраст студента по id_stud):

CREATE FUNCTION GetPeopleAge(@uid int)

RETURNS varchar(200)

BEGIN declare @age datetime

SELECT @age=Peop_DATE

FROM People WHERE Peop_ID=@uid

SET @age=GETDATE()-@age;

RETURN convert(varchar(20),YEAR(@age)-1900)+' лет, '+convert(varchar(20),MONTH(@age)-1)+' месяцев и '+convert(varchar(20),DAY(@age)-1)+' дней'

END

Данную пользовательскую функцию можно использовать в следующем запросе:

SELECT Peop_FAM as [Фамилия], Stud_IMA as [Имя], Peop_DATE as [Дата рождения], dbo.GetPeopleAge(Peop_ID) as [Возраст]

FROM People ORDER BY Peop_DATE ASC

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

6.2.2 Триггеры

Триггер – это откомпилированная процедура, используемая для выполнения действий, инициируемых происходящими в базе данных событиями. Триггер представляет собой сохраненную в базе данных процедуру, которая запускается тогда, когда в отношении таблицы выполняются определенные действия. Триггер может выполняться до или после операторов INSERT, UPDATE или DELETE. Наиболее распространенное применение триггеров – это проверка сложных критериев в базе данных. Триггер - особый инструмент SQL-сервера, поддерживающий целостность данных в БД .

Рассмотрим простой пример. Стипендия студента не может превышать 15000. Определим триггер, который не позволял бы вводить сумму стипендии больше 15000. Для создания триггера с помощью утилиты SQL Server Enterprise Manager необходимо выбрать таблицу Evolution в списке объектов базы данных, после чего выполнить команду контекстного меню Аll tasks (Все задачи) - Manage Triggers. Эти действия приведут к открытию диалогового окна свойств триггера.

CREATE TRIGGER tri_stip

ON stud FOR INSERT, UPDATE

/*описываются локальные переменные*/

DECLARE @stip smallmoney

/* определяется информация о вставляемых записях*/

SELECT @stip = U.Peop_STIP

FROM Inserted U

IF @stip >15000

BEGIN

/*Команда ROLLBACK используется для того, чтобы отменить модификацию данных в случае, если база заблокирована*/

ROLLBACK TRAN

RAISERROR ('Стипендия студента не может превышать 15000', 16,10)

END

Определим триггер tri_ins_Evolution для таблицы Evolution, который будет запускаться каждый раз, когда запись вставляется в таблицу Evolution или модифицируется. Если экзамен или зачет сданы не в срок (например, после 15-го числа месяца), то запись не принимается:

CREATE TRIGGER tri_ins_Evolution

ON Evolution FOR INSERT, UPDATE

/*описываются локальные переменные*/

DECLARE @nDayOfMonth TINYINT

/* определяется информация о вставляемых записях*/

SELECT @nDayOfMonth = DATEPART (Day, I.Pr_DATE)

FROM Evolution P, Inserted I

WHERE P.Peop_ID = I.Peop_ID AND P.Ocenka = I.Ocenka

/*проверяется условие вставки записи*/

/*и при необходимости сообщается об ошибке*/

IF @ nDayOfMonth >15

BEGIN

/*Команда ROLLBACK используется для того, чтобы отменить

модификацию данных в случае, если база заблокирована*/

ROLLBACK TRAN

RAISERROR ('Вводить оценки, полученные до 15-го числа', 16,10)

END

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

Триггер можно удалить следующим образом: DROP TRIGGER имя_триггера

или из контекстного меню Аll tasks (Все задачи) - Manage Triggers выбором имени триггера из списка.

Триггер на добавление студента:

CREATE TRIGGER tr_ins_stud

ON People FOR INSERT

DECLARE @grup integer

SELECT @grup=I.Grup_ID

FROM Inserted I UPDATE Gruppa

SET Grup_KOLSTUD = Grup_KOLSTUD+1

WHERE Gruppa.Grup_ID=@grup

Пусть нам необходимо знать, кто (пользователь) и когда производил изменения оценок студентов. Для этого необходимо создать таблицу журнала и собственно тригер.

Создайте таблицу для журналирования изменений

CREATE TABLE journ (

mod_oper CHAR(20), /* Тип производимой операции */

mod_datetime DATEtime, /*Дата изменеия */

mod_user VARCHAR(30), /*Пользователь БД */

mod_id INTEGEr, /* id студента чья оценка была изменена*/

mod_ocen integer /* Измененая оценка*/

old_ocen integer )

Тригер, регистрирующий изменения оценки:

CREATE TRIGGER treg

ON Evolution

FOR Update

DECLARE @id int, @ocen int, @old_ocen int

SELECT @old_ocen=P.ocenka,@id = P.Peop_ID , @ocen = U.ocenka

FROM Evolution P, Inserted U

INSERT INTO journ VALUES('Обновлена',Current_timestamp,Current_USER,@id, @ocen,@old_ocen);

Данный тригер будет вносить информацию о изменениях оценок студентов.

Список литературы

1. У. Роберт, К. Майкл, Р. Роберт, Ф. Фабио, Д. Фармер. SQL Server 2008. Ускоренный курс для профессионалов. Вильямс – Москва – Санкт Петербург – Киев, 2008 – 768 с.

2. Стивенс Р, Программирование баз данных. - М.: ООО «Бином-Пресс», 2007 – 384 с.

3. Б. Найт, К. Пэтел, В. Снайдер, Р. Лофорт, С. Уорт. Microsoft SQL Server 2008: руководство администратора для профессионалов. Вильямс – Москва – Санкт Петербург – Киев, 2008 – 855 c.

4. Жилинский А. Самоучитель Microsoft SQL Server 2008. БХВ-Петербург, 2009 – 240 с.

5. Хансен Г., Хансен Д. Базы данных: разработка и управление. – М.: ЗАО «Издательство БИНОМ», 1999.

6. Питер Роб, Карлос Коронел Системы баз данных: проектирование, реализация и управление, 5-е издание, – BHV Санкт-Петербург, 2004. -1040 с.

7. Кренке Д. Теория и практика построения баз данных. Изд.9 – Питер, 2005.

8. MICROSOFT SQL SERVER 2005. Реализация и обслуживание. Учебный курс Microsoft/ Пер. с английского – М.: «Русская редакция», Спб.: «Питер», 2007. – 768 стр. ил.

9. Мамаев Е. MS SQL Server 2000. Проектирование и реализация баз данных. Сертификационный экзамен. - BHV, СПб. 2004, 416 с.

10. Плю Р., Стефенс Р., Райан К. Освой самостоятельно SQL за 24 часа. – М.: Издательский дом «Вильямс», 2000.