Файл: 1. Введение в теорию баз данных Вопрос Основные понятия.docx
Добавлен: 07.12.2023
Просмотров: 852
Скачиваний: 3
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Удаление таблицы.
Удаление таблицы выполняется командой:
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))
Пример. Создание родительской таблицы Клиент с ограничениями.
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]'))
Пример. Создание дочерней таблицы Сделка с ограничениями.
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 Клиент)
Пример. Создание таблицы Склад.
CREATE TABLE Склад
(КодТовара INT PRIMARY KEY,
Остаток INT)
Пример. Удаление ограничения внешнего ключа.
ALTER TABLE Сделка DROP CONSTRAINT fk_Товар
Пример. Добавление ограничения внешнего ключа, реализующего декларативную ссылочную целостность.
ALTER TABLE Сделка ADD CONSTRAINT fk_Товар
FOREIGN KEY (КодТовара) REFERENCES товар
ON UPDATE NO ACTION ON DELETE NO ACTION
Пример. Добавления ограничения внешнего ключа, реализующего каскадные обновления и изменения.
ALTER TABLE Сделка ADD CONSTRAINT fk_Товар
FOREIGN KEY (КодТовара) REFERENCES Товар
ON UPDATE CASCADE ON DELETE CASCADE
Пример. Пример создания и удаления вычисляемого поля.
ALTER TABLE Товар ADD Налог AS Цена*0.05
ALTER TABLE Товар DROP COLUMN Налог
Пусть создана таблица без ограничений:
CREATE TABLE Товар
(КодТовара INT,
Название VARCHAR(20),
Тип VARCHAR(20),
Дата DATETIME,
Цена MONEY,
Остаток INT)
Рассмотрим примеры внесения в таблицу всевозможных ограничений.
Пример. Поле КодТовара необходимо сделать первичным ключом. Выполнение следующей команды будет отвергнуто, поскольку поле КодТовара допускает внесение значений NULL.
ALTER TABLE Товар ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)
Сначала нужно изменить объявление столбца КодТовара, запретив внесение значений NULL:
ALTER TABLE Товар
ALTER COLUMN КодТовара INT NOT NULL
И только потом создать ограничение первичного ключа:
ALTER TABLE Товар ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)
Пример. Удалить столбец целого типа и добавить столбец-счетчик.
ALTER TABLE Товар DROP COLUMN КодТовара
ALTER TABLE Товар ADD
КодТовара INT IDENTITY(1,1)
Пример. Добавить ограничение первичного ключа.
ALTER TABLE Товар ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)
Пример. Изменить столбец, добавив ограничение NOT NULL.
ALTER TABLE Товар ALTER COLUMN
Название VARCHAR(40) NOT NULL
Пример. Добавить ограничение уникальности значения.
ALTER TABLE Товар ADD CONSTRAINT
u1 UNIQUE(Название)
Пример. Создать умолчание и добавить умолчание столбцу.
CREATE DEFAULT df1 AS 0
sp_bindefault 'df1', 'Товар.Остаток'
CREATE DEFAULT df2 AS GETDATE()
sp_bindefault 'df2', 'Товар.Дата'
Пример. Создать правило и добавить правило столбцу.
CREATE RULE r1 AS @m IN
('мебель','бытовая химия','косметика')
sp_bindrule 'r1','Товар.Тип'
Вопрос 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 сервер выдаст сообщение об ошибке и все изменения будут отклонены.
Пример. Показать в представлении клиентов из Москвы.
Создание представления:
CREATE VIEW view1 AS
SELECT КодКлиента, Фамилия, ГородКлиента
FROM Клиент
WHERE ГородКлиента='Москва'
Выборка данных из представления:
SELECT * FROM view1
Обращение к представлению осуществляется с помощью оператора SELECT как к обычной таблице.
Представление можно использовать в команде так же, как и любую другую таблицу. К представлению можно строить запрос, модифицировать его (если оно отвечает определенным требованиям), соединять с другими таблицами. Содержание представления не фиксировано и обновляется каждый раз, когда на него ссылаются в команде. Представления значительно расширяют возможности управления данными. В частности, это прекрасный способ разрешить доступ к информации в таблице, скрыв часть данных.
Так, в примере представление просто ограничивает доступ пользователя к данным таблицы Клиент, позволяя видеть только часть значений.
Выполним команду:
INSERT INTO view1 VALUES (12,'Петров', 'Самара')
Это допустимая команда в представлении, и строка будет добавлена с помощью представления view1 в таблицу Клиент. Однако, когда информация будет добавлена, строка исчезнет из представления, поскольку название города отлично от Москвы. Иногда такой подход может стать проблемой, т.к. данные уже находятся в таблице, но пользователь их не видит и не в состоянии выполнить их удаление или модификацию. Для исключения подобных моментов служит WITH CHECK OPTION в определении представления. Фраза размещается в определении представления, и все команды модификации будут подвергаться проверке.
Пример. Создание представления с проверкой команд модификации.
ALTER VIEW view1
SELECT КодКлиента, Фамилия, ГородКлиента
FROM Клиент
WHERE ГородКлиента='Москва'
WITH CHECK OPTION
Для такого представления вышеупомянутая вставка значений будет отклонена системой.
Таким образом, представление может изменяться командами модификации DML, но фактически модификация воздействует не на само представление, а на базовую таблицу.
Представление удаляется командой:
DROP VIEW имя_просмотра [,...n]
Обновление данных в представлениях.
Не все представления в SQL могут быть модифицированы. Модифицируемое представление определяется следующими критериями: