- •Основы 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-сценария.
Изменение таблицы
Изменения в таблицу можно внести командой:
<изменение_таблицы> ::=
ALTER TABLE имя_таблицы
{[ALTER COLUMN имя_столбца
{ тип_данных [(точность[,масштаб])]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }]
| ADD { [<определение_столбца>]
| имя_столбца AS выражение } [,...n]
| [WITH CHECK | WITH NOCHECK ]
ADD { <ограничение-таблицы> } [,...n]
| DROP
{ [CONSTRAINT ] имя_ограничения
| COLUMN имя_столбца}[,...n]
| {CHECK | NOCHECK } CONSTRAINT
{ALL | имя_ограничения[,...n]}
| {ENABLE | DISABLE } TRIGGER
{ALL | имя_триггера [,...n]}}
В дополнение к уже названным параметрам определим параметр {ENABLE | DISABLE } TRIGGER ALL, предписывающий задействовать или отключить конкретный триггер или все триггера, связанные с таблицей.
Удаление таблицы
Удаление таблицы выполняется командой:
DROP TABLE имя_таблицы
Удалить можно любую таблицу, даже системную. К этому вопросу нужно подходить очень осторожно. Однако удалению не подлежат таблицы, если существуют объекты, ссылающиеся на них. К таким объектам относятся таблицы, связанные с удаляемой таблицей посредством внешнего ключа. Поэтому, прежде чем удалять родительскую таблицу, необходимо удалить либо ограничение внешнего ключа, либо дочерние таблицы. Если с таблицей связано хотя бы одно представление, то таблицу также удалить не удастся. Кроме того, связь с таблицей может быть установлена со стороны функций и процедур. Следовательно, перед удалением таблицы необходимо удалить все объекты базы данных, которые на нее ссылаются, либо изменить их таким образом, чтобы ссылок на удаляемую таблицу не было.
CREATE TABLE Товар
(КодТовара INT IDENTITY(1,1) PRIMARY KEY,
Название VARCHAR(50) NOT NULL UNIQUE,
Цена MONEY NOT NULL,
Тип VARCHAR(50) NOT NULL,
Сорт VARCHAR(50) NOT NULL
CHECK(сорт in('первый','второй','третий')),
Город VARCHAR(50) NOT NULL,
Остаток INT
CHECK(остаток>=0))
Пример 9.1. Создание родительской таблицы Товар с ограничениями.
CREATE TABLE Клиент
(КодКлиента INT IDENTITY(1,1) PRIMARY KEY,
Фирма VARCHAR(50) NOT NULL,
Фамилия VARCHAR(50) NOT NULL,
Город VARCHAR(50) NOT NULL,
Телефон CHAR(10) NOT NULL
CHECK(Телефон LIKE
'[1-9][0-9]-[0-9][0-9]-[0-9][0-9]'))
Пример 9.2. Создание родительской таблицы Клиент с ограничениями.
CREATE TABLE Сделка
(КодСделки INT IDENTITY(1,1) PRIMARY KEY,
КодТовара INT NOT NULL,
КодКлиента INT NOT NULL,
Количество INT NOT NULL DEFAULT 0,
Дата DATETIME NOT NULL DEFAULT
GETDATE(),
CONSTRAINT fk_Товар
FOREIGN KEY(КодТовара) REFERENCES Товар,
CONSTRAINT fk_Клиент
FOREIGN KEY(КодКлиента) REFERENCES Клиент)
Пример 9.3. Создание дочерней таблицы Сделка с ограничениями.
CREATE TABLE Склад
(КодТовара INT PRIMARY KEY,
Остаток INT)
Пример 9.4. Создание таблицы Склад.
ALTER TABLE Сделка DROP CONSTRAINT fk_Товар
Пример 9.5. Удаление ограничения внешнего ключа.
ALTER TABLE Сделка ADD CONSTRAINT fk_Товар
FOREIGN KEY (КодТовара) REFERENCES товар
ON UPDATE NO ACTION ON DELETE NO ACTION
Пример 9.6. Добавление ограничения внешнего ключа, реализующего декларативную ссылочную целостность.
ALTER TABLE Сделка ADD CONSTRAINT fk_Товар
FOREIGN KEY (КодТовара) REFERENCES Товар
ON UPDATE CASCADE ON DELETE CASCADE
Пример 9.7. Добавления ограничения внешнего ключа, реализующего каскадные обновления и изменения.
ALTER TABLE Товар ADD Налог AS Цена*0.05
ALTER TABLE Товар DROP COLUMN Налог
Пример 9.8. Пример создания и удаления вычисляемого поля.
Пусть создана таблица без ограничений:
CREATE TABLE Товар
(КодТовара INT,
Название VARCHAR(20),
Тип VARCHAR(20),
Дата DATETIME,
Цена MONEY,
Остаток INT)
Рассмотрим примеры внесения в таблицу всевозможных ограничений.
Пример 9.9. Поле КодТовара необходимо сделать первичным ключом. Выполнение следующей команды будет отвергнуто, поскольку поле КодТовара допускает внесение значений NULL.
ALTER TABLE Товар ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)
Пример 9.9. Поле КодТовара необходимо сделать первичным ключом.
Сначала нужно изменить объявление столбца КодТовара, запретив внесение значений NULL:
ALTER TABLE Товар
ALTER COLUMN КодТовара INT NOT NULL
И только потом создать ограничение первичного ключа:
ALTER TABLE Товар ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)
Пример 9.10. Удалить столбец целого типа и добавить столбец-счетчик.
ALTER TABLE Товар DROP COLUMN КодТовара
ALTER TABLE Товар ADD
КодТовара INT IDENTITY(1,1)
Пример 9.10. Удаление столбца целого типа и добавление столбца-счетчика.
Пример 9.11. Добавить ограничение первичного ключа.
ALTER TABLE Товар ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)
Пример 9.11. Добавление ограничений первичного ключа.
Пример 9.12. Изменить столбец, добавив ограничение NOT NULL.
ALTER TABLE Товар ALTER COLUMN
Название VARCHAR(40) NOT NULL
Пример 9.12. Добавление ограничения NOT NULL.
Пример 9.13. Добавить ограничение уникальности значения.
ALTER TABLE Товар ADD CONSTRAINT
u1 UNIQUE(Название)
Пример 9.13. Добавление ограничения уникальности значения.
Пример 9.14. Создать умолчание и добавить умолчание столбцу.
CREATE DEFAULT df1 AS 0
sp_bindefault 'df1', 'Товар.Остаток'
CREATE DEFAULT df2 AS GETDATE()
sp_bindefault 'df2', 'Товар.Дата'
Пример 9.14. Создание и добавление умолчания столбцу.
Пример 9.15. Создать правило и добавить правило столбцу.
CREATE RULE r1 AS @m IN
('мебель','бытовая химия','косметика')
sp_bindrule 'r1','Товар.Тип'
Пример 9.15. Создание и добавление правила столбцу.
Лекция 10: Представления Дается понятие представлений. Определяется роль представлений в вопросах безопасности данных. Описывается процесс управления представлениями: создание, изменение, применение, удаление представлений.
Определение представления
Представления, или просмотры ( VIEW ), представляют собой временные, производные (иначе - виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним. Обычные таблицы относятся к базовым, т.е. содержащим данные и постоянно находящимся на устройстве хранения информации. Представление не может существовать само по себе, а определяется только в терминах одной или нескольких таблиц. Применение представлений позволяет разработчику базы данных обеспечить каждому пользователю или группе пользователей наиболее подходящие способы работы с данными, что решает проблему простоты их использования и безопасности. Содержимое представлений выбирается из других таблиц с помощью выполнения запроса, причем при изменении значений в таблицах данные в представлении автоматически меняются. Представление - это фактически тот же запрос, который выполняется всякий раз при участии в какой-либо команде. Результат выполнения этого запроса в каждый момент времени становится содержанием представления. У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.
У СУБД есть две возможности реализации представлений. Если его определение простое, то система формирует каждую запись представления по мере необходимости, постепенно считывая исходные данные из базовых таблиц. В случае сложного определения СУБД приходится сначала выполнить такую операцию, как материализацияпредставления, т.е. сохранить информацию, из которой состоит представление, во временной таблице. Затем система приступает к выполнению пользовательской команды и формированию ее результатов, после чего временная таблица удаляется.
Представление - это предопределенный запрос, хранящийся в базе данных, который выглядит подобно обычной таблице и не требует для своего хранения дисковой памяти. Для хранения представления используется только оперативная память. В отличие от других объектов базы данных представление не занимает дисковой памяти за исключением памяти, необходимой для хранения определения самого представления.
Создания и изменения представлений в стандарте языка и реализации в MS SQL Server совпадают и представлены следующей командой:
<определение_представления> ::=
{ CREATE| ALTER} VIEW имя_представления
[(имя_столбца [,...n])]
[WITH ENCRYPTION]
AS SELECT_оператор
[WITH CHECK OPTION]
Рассмотрим назначение основных параметров.
По умолчанию имена столбцов в представлении соответствуют именам столбцов в исходных таблицах. Явное указание имени столбца требуется для вычисляемых столбцов или при объединении нескольких таблиц, имеющих столбцы с одинаковыми именами. Имена столбцов перечисляются через запятую, в соответствии с порядком их следования в представлении.
Параметр WITH ENCRYPTION предписывает серверу шифровать SQL-код запроса, что гарантирует невозможность его несанкционированного просмотра и использования. Если при определении представления необходимо скрыть имена исходных таблиц и столбцов, а также алгоритм объединения данных, необходимо применить этот аргумент.
Параметр WITH CHECK OPTION предписывает серверу исполнять проверку изменений, производимых через представление, на соответствие критериям, определенным в операторе SELECT. Это означает, что не допускается выполнение изменений, которые приведут к исчезновению строки из представления. Такое случается, если дляпредставления установлен горизонтальный фильтр и изменение данных приводит к несоответствию строки установленным фильтрам. Использование аргумента WITH CHECK OPTION гарантирует, что сделанные изменения будут отображены в представлении. Если пользователь пытается выполнить изменения, приводящие к исключению строки изпредставления, при заданном аргументе WITH CHECK OPTION сервер выдаст сообщение об ошибке и все изменения будут отклонены.
Пример 10.1. Показать в представлении клиентов из Москвы.
Создание представления:
CREATE VIEW view1 AS
SELECT КодКлиента, Фамилия, ГородКлиента
FROM Клиент
WHERE ГородКлиента='Москва'