Оператор определения представлений create VIEW

Операторы определения таблицы

Операторы определения объектов базы данных

Оператор обновления данных UPDATE

Оператор UPDATE имеет формат:

UPDATE <базовая таблица | представление>SET <столбец>= <значение> [,<столбец>= <значение>]...[WHERE <предикат>];

где <значение>::=<столбец> |< выражение> | <константа> | <переменная>

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

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

Пример 4.47 Предположим, что продавец Мотика ушел на пенсию, и необходимо переназначить его номер новому продавцу:

UPDATE Продавцы SET sname = 'Гибсон', city = 'Бостон', comm =.10 WHERE snum = 1004;

Эта команда передаст новому продавцу Гибсону, всех текущих заказчиков бывшего продавца Мотика и их порядки.

Пример 4.48 Можно, используя коррелированный подзапрос, увеличить комиссионные всех продавцов, которые были назначены по крайней мере двум заказчикам: UPDATE Продавцы SET comm = comm +.01 WHERE 2 < = (SELECT COUNT (cnum) FROM Заказчики WHERE Заказчики.snum = Продавцы.snum); Теперь продавцы Пил и Серенс, имеющие нескольких заказчиков, получат повышение своих комиссионных.

Базовые таблицы описываются в SQL с помощью предложения CREATE TABLE (создать таблицу). Рассмотрим синтаксис этого предложения:

CREATE TABLE <имя_таблицы> (<элемент_таблицы> [,<элемент_таблицы >...] [ограничения_целостности_таблицы]);< элемент_таблицы >::= <определение_столбца>< определение_столбца >::= <имя_столбца> <тип_данных> [DEFAULT <значение>] [<ограничения_ целостности_столбца>...]< ограничения_ целостности_столбца >::= NULL | NOT NULL [UNIQUE <спецификация>] | REFERENCES <спецификация> | CHECK (<проверочное_ограничение>)|PRIMARY KEY|FOREIGN KEY

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

Ограничение – это свойство, назначенное столбцу или таблице, которое запрещает ввод в указанный столбец (или столбцы) недопустимых значений. Основные виды ограничений: NULL, NOT NULL, DEFAULT, PRIMARY KEY, FOREIGN KEY, REFERENCES, CHECK, UNIQUE. Ограничения могут быть без имени или с именем, тогда перед ограничением вставляется слово CONSTRAINT <имя_ограничения>. Наличие имени ограничения позволяет ссылаться на него в операторе изменения таблицы, например:

ALTER TABLE Tab1 ADD CONSTRAINT col1 CHECK (col1 BETWEEN 0 AND 1);

В разделе значения по умолчанию DEFAULT указывается значение, которое должно быть помещено в строку, заносимую в данную таблицу, если значение данного столбца явно не указано. Значение по умолчанию может быть указано в виде литеральной константы с типом, соответствующим типу столбца, или путем задания ключевого слова NULL, означающего, что значением по умолчанию является неопределенное значение. Если значение столбца по умолчанию не специфицировано, и в разделе ограничений целостности столбца указано NOT NULL, то попытка занести в таблицу строку с NULL-значением данного столбца приведет к ошибке.

Указание в разделе ограничений целостности NOT NULL приводит к неявному порождению проверочного ограничения целостности для всей таблицы «CHECK (C IS NOT NULL)» (где C - имя данного столбца). Если ограничение NOT NULL не указано, и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL. Если указана спецификация уникальности, то порождается соответствующая спецификация уникальности для таблицы.

Если в разделе ограничений целостности указано ограничение по ссылкам данного столбца (REFERENCES <спецификация>), то порождается соответствующее определение ограничения по ссылкам для таблицы:

FOREIGN KEY(C) REFERENCES <спецификация> Пример 4.49 Создать таблицу: CREATE TABLE Заказчики (cnum integer NOT NULL PRIMARY KEY, cname char (10) NOT NULL, city char (10) DEFAULT = 'Лондон', rating integer, snum integer NOT NULL, UNIQUE (cnum, snum));UNIQUE (cnum, snum) – это ограничение целостности таблицы, утверждающее, что комбинация номеров должна быть уникальной, т.е. у каждого заказчика только один продавец. Пример 4.50 В следующем примере для задания составного первичного ключа используется ограничение целостности таблицы PRIMARY KEY для пар: CREATE TABLE Имена (firstname char (10) NOT NULL, lastname char (10) NOT NULL city char (10), PRIMARY KEY (firstname, lastname)); Пример 4. 51 В данном примере использовано ограничение по ссылкам:CREATE TABLE Продавцы (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), comm decimal, cnum integer REFERENCES Customers);

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

DROP TABLE <имя_таблицы>;

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

В SQL существует также предложение ALTER TABLE (изменить таблицу), которое позволяет добавить справа к таблице новый столбец, изменить или удалить столбец, т.е. модифицировать описание таблицы.

Для построения индекса в SQL существует предложение CREATE INDEX (создать индекс), имеющее формат:CREATE [UNIQUE] INDEX <имя_индекса> ON < имя_таблицы >

(<столбец >[[ASC] | DESC] [, <столбец> [[ASC] | DESC]]...);

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

Для удаления индекса используется предложение:

DROP INDEX <имя_индекса>;

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

Представление - это виртуальная таблица, которая сама по себе не существует, но для пользователя выглядит таким образом, как будто она существует. Представление не поддерживаются его собственными физическими хранимыми данными. Вместо этого в каталоге таблиц хранится определение, оговаривающее, из каких столбцов и строк других таблиц оно должно быть сформировано. Механизм представлений (view) является мощным средством языка SQL, позволяющим скрыть реальную структуру БД от некоторых пользователей за счет определения представления БД, которое реально является некоторым хранимым в БД запросом с именованными столбцами, а для пользователя ничем не отличается от базовой таблицы БД (с учетом технических ограничений). Любая реализация должна гарантировать, что состояние представляемой таблицы точно соответствует состоянию базовых таблиц, на которых определено представление. Синтаксис предложения CREATE VIEW имеет вид:

CREATE VIEW <имя_представления> [(<столбец>[,<столбец>]...)] AS подзапрос [WITH CHECK OPTION];

где подзапрос, следующий за AS и являющийся определением данного представления, не исполняется, а просто сохраняется в каталоге;

необязательная фраза «WITH CHECK OPTION» (с опцией проверки) указывает, что для операций INSERT и UPDATE над этим представлением должна осуществляться проверка, обеспечивающая удовлетворение WHERE фразы подзапроса;

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

а) хотя бы один из столбцов подзапроса не имеет имени (создается с помощью выражения, SQL-функции или константы);

б) два или более столбцов подзапроса имеют одно и то же имя;

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

Пример 4.52 Например, создадим представление Лондон_продавцы, которое может рассматриваться пользователем как новая таблица в базе данных:

CREATE VIEW Лондон_продавцы AS SELECT * FROM Продавцы WHERE city = 'Лондон'; Пример 4. 53 Следующее представление содержит данные о количестве заказчиков с каждым значением рейтинга: CREATE VIEW Оценка (rating, number) AS SELECT rating, COUNT (*) FROM Заказчики GROUP BY rating; Пример 4. 54 Предположим, что компания предусматривает премию для тех продавцов, которые имеют заказчика с самым высоким порядком для любой указанной даты. Можно проследить эту информацию с помощью представления: CREATE VIEW Максимум AS SELECT b.odate, a.snum, a.sname, FROM Продавцы a, Порядки b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Порядки c WHERE c.odate = b.odate);

Представляемая таблица V является модифицируемой (т.е. по отношению к V можно использовать операторы DELETE, UPDATE, INSERT) в том и только в том случае, если выполняются следующие условия для спецификации запроса:

• в списке выборки не указано ключевое слово DISTINCT;
• каждое арифметическое выражение в списке выборки представляет собой одну спецификацию столбца, и спецификация одного столбца не появляется более одного раза (не должно быть агрегатных функций и выражений);
• в разделе FROM указана только одна таблица, являющаяся либо базовой таблицей, либо модифицируемым представлением;
• в условии выборки раздела WHERE не используются подзапросы;
• отсутствуют разделы GROUP BY и HAVING.

Таким образом, могут быть модифицируемые представления (пример 4.52) и представления только для чтения, которые разрешается использовать только в команде SELECT (примеры 4.53, 4.54). С помощью представленийможно создать библиотеку сложных запросов и работать с сохраненными представлением как с таблицами.

Возможна ситуация, когда в модифицируемое представление добавляются данные, которые «проглатываются» (swallowed) в базовой таблице. Пример 4. 55 Рассмотрим такое представление: CREATE VIEW Рейтинг AS SELECT cnum, rating FROM Заказчики WHERE rating = 300; Это - представление модифицируемое. Оно просто ограничивает доступ к определенным строкам и столбцам в таблице. Предположим, что вы вставляете (INSERT) следующую строку: INSERT INTO Рейтинг VALUES (2018, 200); Это - допустимая команда INSERT в этом представлении. Строка будет вставлена с помощью представления Рейтинг в таблицу Заказчики. Однако когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300. Это - обычная проблема. Пользователь не сможет понять, почему введя строку, он не может ее увидеть, и будет неспособен при этом удалить ее. Вы можете быть гарантированы от модификаций такого типа с помощью предложения WITH CHECK OPTION в определение представления. Пример 4. 56 Добавим это предложение в команду примера 4.55: CREATE VIEW Рейтинг AS SELECT cnum, rating FROM Заказчики WHERE rating = 300 WITH CHECK OPTION;Вышеупомянутая вставка будет отклонена.

Замечание. Требование WITH CHECK OPTION в определении представления имеет смысл только в случае определения модифицируемой представляемой таблицы.

Для удаления представления используется оператор:

DROP VIEW <имя_представления>;