- •Основы sql. Курс лекций
- •Лекция 1: Введение в структурированный язык запросов sql
- •Основные понятия
- •Реляционные базы данных
- •Введение в технологию клиент-сервер
- •Типы команд sql
- •Запись sql-операторов
- •Описание учебной базы данных
- •Типы данных языка sql, определенные стандартом
- •Символьные данные
- •Varchar][длина]}
- •Округленные числа
- •Дата и время
- •Понятие домена
- •Создание пользовательского типа данных
- •If лог_выражение
- •Основные объекты структуры базы данных sql-сервера
- •Создание базы данных в среде ms sql Server
- •Индексы в среде ms sql Server
- •Уникальный индекс
- •Index имя_индекса on имя_таблицы(имя_столбца
- •Предложение from
- •Предложение where
- •Is not null используется для проверки присутствия значения в поле.
- •Пример 4.16. Список клиентов в алфавитном порядке.
- •Пример 4.17. Список фирм и клиентов. Названия фирм в алфавитном порядке, имена клиентов в каждой фирме в обратном порядке.
- •Операция соединения по двум отношениям (таблицам)
- •Операция тета-соединения
- •Лекция 6: Вычисления и подведение итогов в запросах
- •Insert into – запрос добавления ;
- •Запрос добавления
- •Пример 8.1. Добавление в таблицу товар новой записи.
- •Insert into Итог
- •Ссылочная целостность
- •Создание таблицы
- •Изменение и удаление таблицы
- •Ограничение первичного ключа (primary key)
- •Ограничение внешнего ключа (foreign key)
- •Ограничение уникального ключа (unique)
- •Ограничение по умолчанию (default)
- •Изменение таблицы
- •Удаление таблицы
- •Пример 10.1. Представление клиентов из Москвы.
- •Insert into view1 values (12,'Петров', 'Самара')
- •Независимость от данных
- •Понятие функции пользователя
- •Inline – функции содержат всего одну команду select и возвращают пользователю набор данных в виде значения типа данных table ;
- •Функции Scalar
- •Пример 11.3. Создание функции, которая для некоторого сотрудника выводит список всех его подчиненных.
- •Создание, изменение и удаление хранимых процедур
- •Выполнение хранимой процедуры
- •Пример 12.6. Процедура с входными и выходными параметрами. Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.
- •Лекция 13: Курсоры: принципы работы
- •Реализация курсоров в среде ms sql Server
- •Изменение и удаление данных
- •0, Если выборка завершилась успешно;
- •Пример 13.5. Курсор для вывода списка фирм и клиентов из Москвы.
- •Пример 13.6. Курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости.
- •Определение триггера в стандарте языка sql
- •Реализация триггеров в среде ms sql Server
- •If (columns_updates(){оператор_бит_обработки}
- •Типы триггеров
- •Программирование триггера
- •Пример 14.1. Использование триггера для реализации ограничений на значение.
- •Пример 14.2. Использования триггера для сбора статистических данных.
- •Пример 14.3. Триггер для обработки операции удаления записи из таблицы
- •Пример 14.4. Триггер для обработки операции изменения записи в таблице
- •Пример 14.5. Исправленный вариант триггера для обработки операции изменения записи в таблице
- •Реализация правил целостности данных
- •Добавление записи в рекурсивную структуру
- •Пример 15.2. Триггер для изменения записи в таблице.
- •Удаление записи из рекурсивной структуры
- •Пример 15.3. Триггер для удаления записи из таблицы.
- •Введение в транзакции
- •Acid-свойства транзакций
- •Блокировки
- •Управление транзакциями
- •Явные транзакции
- •Пример 16.1. Использование точек сохранения
- •Вложенные транзакции
- •"Мертвые" блокировки
- •Уровни изоляции sql Server
- •Insert – право вставлять в таблицу новые строки;
- •Предоставление привилегий пользователям
- •Отмена предоставленных пользователям привилегий
- •Предоставление прав
- •Права на выполнение команд sql
- •Неявные права
- •Запрещение доступа
- •Неявное отклонение доступа
- •Конфликты доступа
- •Пример 17.1. Создание новой базы данных, нового пользователя для этой базы данных, с предоставлением ему всех прав.
- •Sql и прикладные программы
- •Архитектура odbc
- •Источники данных и odbc
- •Взаимодействие с базой данных в Java-программах
- •Загрузка драйвера
- •Создание соединения: класс Connection
- •Создание оператора: класс Statement
- •Получение результатов: класс ResultSet
- •Item - извлечение определенного объекта Parameter.
- •Объект Connection
- •Пример 18.3. Выполнение статического sql-запроса к учебной базе данных из vbScript-сценария.
- •Пример 18.4. Выполнение динамического sql-запроса к учебной базе данных из vbScript-сценария.
Изменение и удаление данных
Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате:
UPDATE имя_таблицы SET {имя_столбца={
DEFAULT | NULL | выражение}}[,...n]
WHERE CURRENT OF {{[GLOBAL] имя_курсора}
|@имя_переменной_курсора}
За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице.
Для удаления данных посредством курсора используется команда DELETE в следующем формате:
DELETE имя_таблицы
WHERE CURRENT OF {{[GLOBAL] имя_курсора}
|@имя_переменной_курсора}
В результате будет удалена строка, установленная текущей в курсоре.
Закрытие курсора
CLOSE {имя_курсора | @имя_переменной_курсора}
После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки, установленные в процессе его работы. Закрытиеможет применяться только к открытым курсорам. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.
Освобождение курсора
Закрытие курсора необязательно освобождает ассоциированную с ним память. В некоторых реализациях нужно явным образом освободить ее с помощью оператораDEALLOCATE. После освобождения курсора освобождается и память, при этом становится возможным повторное использование имени курсора.
DEALLOCATE { имя_курсора |
@имя_переменной_курсора }
Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS
Функция @@FETCH_STATUS возвращает:
0, Если выборка завершилась успешно;
-1, если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора ;
-2, если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.
DECLARE abc CURSOR SCROLL FOR
SELECT * FROM Клиент
Пример 13.1. Объявление курсора.
DECLARE @MyCursor CURSOR
SET @MyCursor=CURSOR LOCAL SCROLL FOR
SELECT * FROM Клиент
Пример 13.2. Использование переменной для объявления курсора.
DECLARE abc CURSOR GLOBAL SCROLL FOR
SELECT * FROM Клиент
OPEN abc
Пример 13.3. Объявление и открытие курсора.
DECLARE @MyCursor CURSOR
SET @MyCursor=abc
Пример 13.4. Использование переменной для переприсваивания курсора.
Пример 13.5. Разработать курсор для вывода списка фирм и клиентов из Москвы.
DECLARE @firm VARCHAR(50),
@fam VARCHAR(50),
@message VARCHAR(80)
PRINT ' Список клиентов'
DECLARE klient_cursor CURSOR LOCAL FOR
SELECT Фирма, Фамилия
FROM Клиент
WHERE Город='Москва'
ORDER BY Фирма, Фамилия
OPEN klient_cursor
FETCH NEXT FROM klient_cursor INTO @firm, @fam
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message='Клиент '+@fam+
' Фирма '+ @firm
PRINT @message
-- переход к следующему клиенту--
FETCH NEXT FROM klient_cursor
INTO @firm, @fam
END
CLOSE klient_cursor
DEALLOCATE klient_cursor
Пример 13.5. Курсор для вывода списка фирм и клиентов из Москвы.
Пример 13.6. Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В один курсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.
DECLARE @id_kl INT,
@firm VARCHAR(50),
@fam VARCHAR(50),
@message VARCHAR(80),
@nam VARCHAR(50),
@d DATETIME,
@p INT,
@s INT
SET @s=0
PRINT ' Список покупок'
DECLARE klient_cursor CURSOR LOCAL FOR
SELECT КодКлиента, Фирма, Фамилия
FROM Клиент
WHERE Город='Москва'
ORDER BY Фирма, Фамилия
OPEN klient_cursor
FETCH NEXT FROM klient_cursor
INTO @id_kl, @firm, @fam
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message='Клиент '+@fam+
' Фирма '+ @firm
PRINT @message
SELECT @message='Наименование товара Дата
покупки Стоимость'
PRINT @message
DECLARE tovar_cursor CURSOR FOR
SELECT Товар.Название, Сделка.Дата,
Товар.Цена*Сделка.Количество AS
Стоимость
FROM Товар INNER JOIN Сделка ON Товар.
КодТовара=Сделка.КодТовара
WHERE Сделка.КодКлиента=@id_kl
OPEN tovar_cursor
FETCH NEXT FROM tovar_cursor
INTO @nam, @d, @p
IF @@FETCH_STATUS<>0
PRINT ' Нет покупок'
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message=' '+@nam+' '+
CAST(@d AS CHAR(12))+' '+
CAST(@p AS CHAR(6))
PRINT @message
SET @s=@s+@p
FETCH NEXT FROM tovar_cursor
INTO @nam, @d, @p
END
CLOSE tovar_cursor
DEALLOCATE tovar_cursor
SELECT @message='Общая стоимость '+
CAST(@s AS CHAR(6))
PRINT @message
-- переход к следующему клиенту--
FETCH NEXT FROM klient_cursor
INTO @id_kl, @firm, @fam
END
CLOSE klient_cursor
DEALLOCATE klient_cursor