Модель данных Учет оплаты Реализация операций реляционной

Модель данных «Учет оплаты»

Реализация операций реляционной алгебры на SQL • выборка (горизонтальный срез) 1. SELECT * FROM студенты WHERE курс<3 AND дата поступления <’ 01/09/2009’ Вывести информацию из всех столбцов таблицы «студенты» , для строк удовлетворяющих условию: курс<3 и дата поступления <’ 01/09/2009’ 2. SELECT * FROM студенты WHERE MONTH (дата поступления) = 8 ORDER BY ФИО Вывести информацию из всех столбцов таблицы «студенты» , для строк удовлетворяющих условию: месяц поступления равен 8 Отсортировать результаты выборки по алфавиту, по возрастанию

Реализация операций реляционной алгебры на SQL • проекция (вертикальный срез) SELECT ALL ФИО, курс FROM студенты Вывести информацию из всех строк и столбцов «ФИО» и «курс» таблицы «студенты» , с дубликатами • объединение Таблицы должны быть совместимыми по типу SELECT учебный _ шифр, сумма FROM оплата _1_ семестр UNION SELECT учебный _ шифр, сумма FROM оплата _2_ семестр

Реализация операций реляционной алгебры на SQL • декартово произведение Операция, как правило является промежуточной, для выполнения операции соединения. SELECT студенты. учебный _ шифр, ФИО, курс, сумма FROM студенты, оплата_1_семестр • естественное соединение (2 варианта) Соединение реализуется на основе механизма внешних ключей 1. SELECT студенты. учебный _ шифр, ФИО, курс, сумма FROM студенты, оплата _1_ семестр WHERE студенты. учебный _ шифр = оплата _1_ семестр. учебный _ шифр 2. SELECT студенты. учебный _ шифр, ФИО, курс, сумма FROM студенты INNER JOIN оплата _1_ семестр ON студенты. учебный _ шифр = оплата _1_ семестр. учебный _ шифр

Реализация операций реляционной алгебры на SQL • пересечение Таблицы должны быть совместимыми по типу SELECT оплата_1_семестр. учебный шифр, оплата_1_семестр. сумма FROM оплата_1_семестр, оплата _2_ семестр WHERE оплата_1_семестр. учебный шифр = оплата _2_ семестр. учебный шифр AND оплата_1_семестр. сумма = оплата _2_ семестр. сумма • разность (с использованием вложенного запроса) Таблицы должны быть совместимыми по типу. Результатом операции будут строки, содержащие столбец «учебный шифр» , которые присутствуют в таблице «оплата_1_семестр» и отсутствуют в таблице «оплата_2_семестр» SELECT оплата_1_семестр. учебный шифр FROM оплата_1_семестр, оплата _2_ семестр WHERE NOT EXISTS (SELECT учебный шифр FROM оплата _2_ семестр WHERE оплата_1_семестр. учебный шифр = оплата _2_ семестр. учебный шифр )

Логические операции на запросами Логическая операция Истина Ложь EXISTS Запрос выдает хотя бы одну строку таблицы Запрос не выдает ни одной строки таблицы Запрос выдает хотя бы одну строку таблицы IN NOT EXISTS NOT IN

Запросы с группировкой • запрос с группировкой Запросы с группировкой предполагают использование агрегатных функций SELECT COUNT(*) AS ‘количество студентов’, курс FROM студенты GROUP BY курс Вывести количество студентов в разрезе курсов • запрос группировкой и условием отбора групп SELECT COUNT(*) AS ‘количество студентов’, курс FROM студенты GROUP BY курс HAVING курс>2 Вывести количество студентов в разрезе курсов, если курс старше 2

Вложенные запросы (подзапросы) 1. SELECT учебный шифр, сумма FROM WHERE сумма> (SELECT AVG(сумма) FROM оплата _1_ семестр) Вывести учебный шифр и сумму оплаты студентов, у которых оплата превышает среднюю оплату среди всех студентов 2. SELECT студенты. учебный _ шифр, курс, дата поступления, сумма FROM студенты, оплата _1_ семестр WHERE студенты. учебный _ шифр = оплата _1_ семестр. учебный _ шифр AND YEAR(дата поступления) = (SELECT MIN (YEAR (дата поступления) FROM студенты)

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

Процедуры и триггеры Триггер представляет собой специальный тип хранимых процедур, запускаемых сервером автоматически при попытке изменения данных в таблицах, с которыми триггеры связаны. Каждый триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна транзакция. При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции. Тем самым внесение изменений запрещается. Отменяются также все изменения, уже сделанные триггером. Триггер не может иметь параметров (аргументов) Триггерные события состоят из вставки, удаления и обновления строк в таблице

Команда создания процедуры Создание процедуры CREATE PROC[EDURE] <имя_процедуры> [{@имя_параметра <тип_параметра> }] AS sql_оператор [. . . n] Выполнение процедуры EXEC [ UTE] <имя_процедуры> [[@имя_параметра=]значение ]

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

Команды для работы с курсором Объявление курсора DECLARE @имя_переменной курсора [SCROLL] [STATIC | DYNAMIC] CURSOR FOR SELECT_оператор Открытие курсора OPEN @имя_переменной_курсора Выборка из курсора FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE {номер_строки | @переменная_номера_строки} | RELATIVE {номер_строки | @переменная_номера_строки}] FROM имя_курсора [INTO @имя_переменной [, . . . n]] Проверка статуса курсора @@fetch_status Закрытие курсора CLOSE @имя_переменной_курсора Освобождение курсора DEALLOCATE @имя_переменной_курсора