Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 48)

Колонки, специфицированные в ORDER BY, должны быть вклю­чены в SELECT.

Запросы, затрагивающие несколько таблиц

В SQL запросы, затрагивающие несколько таблиц, могут реализовываться различными способами. Во-первых, условия соединения таблиц могут быть заданы в предложении WHERE. Во-вторых, воз­можно применение вложенных запросов. В-третьих, можно исполь­зовать встроенные операторы JOIN, включаемые в предложение FROM (что рассматривалось выше). Последняя из указанных возмож­ностей была включена в SQL сравнительно недавно (начиная с SQL-92). Использование встроенных операторов JOIN часто помогает упрос­тить написание запроса. Встроенные операторы JOIN реализованы не во всех диалектах SQL. В случае их отсутствия (или в силу при­вычки) можно запросы реализовать и без использования JOIN.

Один и тот же запрос может быть реализован несколькими спосо­бами.

Рассмотрим следующий пример. Пусть имеются две таблицы. Первая («Postavka») содержит сведения о поставках продукции и включает колонки: Код поставщика (kod_post), Код продукции {kod_prod), Дата поставки (dat_post), Количество поставленной про­дукции (kolv). Вторая таблица («Sp_post») содержит колонки Код по­ставщика и Наименование поставщика (naim_pst). Требуется выдать наименования поставщиков, которые поставляют товар с кодом Р2.

Возможны следующие варианты задания этого запроса:

1 - пример простого вложенного подзапроса

SELECT naim_post

FROM sp_post

WHERE kod_post IN

(SELECT kod_post FROM postavka WHERE kod_prod="P2");

2 - пример коррелированного подзапроса

SELECT naim_post

FROM sp_post

WHERE "P2" IN

(SELECT kod__prod FROM postavka

WHERE sp_post.kod_post=postavka.kod_post);

3 - вместо вложенных подзапросов используется задание усло­вия связывания таблиц

SELECT naim_post

FROM sp_post,postavka

WHERE postavka.kod_post=sp_post .kod_post

AND postavka.kod_prod="P2";

4 - возможность использования квантора существования при задании запроса

SELECT naim_post

FROM sp_post

WHERE EXISTS (SELECT * FROM postavka

WHERE kod_post=sp_post.kod_post

AND kod_prod="P2");

5 - использование встроенного оператора JOIN

SELECT naim_post

FROM sp_post INNER JOIN postavka ON sp_post.kod_post= postavka.kod_post

WHERE kod_prod="P2";

Следует обратить внимание, что оптимизатор должен выбирать оптимальный план реализации запросов, и в идеале время выполне­ния правильно заданного запроса не должно зависеть от способа его написания. Однако в действительности дело обстоит иначе, и по-раз­ному записанные запросы, в результате выполнения которых получа­ется один и тот же ответ, могут потребовать разного количества вре­мени для своей реализации.

Корректирующие операторы

Оператор INSERT. Он позволяет включить в таблицу новые строки

INSERT INTO имя таблицы

[(имя столбца .,..)]

выражение запроса | конструктор значений таблицы

|{DEFAULT VALUES}

Если список колонок не задан, то значения должны вводиться в каждую колонку таблицы; если список колонок задан, то значения соответственно должны вводиться в те колонки, которые перечисле­ны в списке, и в том порядке, в котором они расположены в нем.

Элементы в списке значений могут быть константами, функция­ми, переменными памяти. Если эти элементы являются константами, то при их задании используются определенные разделители в зависи­мости от типа вводимых данных: символьные данные заключаются в кавычки, даты - в фигурные скобки, логические - в точки, числовые данные вводятся без разделителей.

Пример использования оператора INSERT:

INSERT INTO cennik

VALUES ("железо", "MOOOl", "T", 33.50);

В данном примере значения вводятся во все колонки таблицы {см. описание таблицы «Cennik»), поэтому <список колонок> не указан.

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

Оператор UPDATE. Командой, позволяющей корректировать со­держание таблицы, является оператор UPDATE, имеющий следую­щий формат:

UPDATE <имя таблицы> SET <имя столбца>=<новое значение>

[,<имя столбца>=<новое значение:»...]

[<предложение WHERE>];

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

Оператор

UPDATE vrbt SET rascen=rasscen*10;

увеличивает расценки для всех записей в таблице «Vrbt» в 10 раз.

Оператор

UPDATE vrbt SET vrbt=1 WHERE koddet="B11";

устанавливает для записей, удовлетворяющих условию KODDET = "В 11", значение поля vrbt, равное 1.

Оператор DELETE. Его можно использовать для удаления строк таблицы:

DELETE

FROM <имя таблицы>

[<предложение WHERE>];

Например, оператор

DELETE

FROM cennik

WHERE naimmat="железо";

удалит из таблицы «Cennik» строку с naimmat=”железо”.

Следует быть осторожным при использовании оператора DELETE, поскольку, если фраза WHERE в операторе DELETE отсутствует, будут удалены все строки таблицы. То же самое произойдет, если неправильно указать условие отбора и в результате не будет ото­брано ни одной строки в таблице. Оператор DELETE физически уда­ляет строки таблицы.

7.8.3. Создание представлений (VIEW)

Оператор CREATE VIEW

Оператор для создания представления выглядит следующим об­разом (синтаксис SQL-92)⁴:

CREATE VIEW <имя представления> [(<список столбцов выборки>)]

AS < SELECT оператор>

[WITH [CASCADED |LOCAL] CHECK OPTION]);

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

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

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

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

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

Представления не хранятся в памяти в виде физических таблиц. Когда используется оператор CREATE VIEW, подзапрос, следующий за AS, не исполняется. Просто в каталоге сохраняется соответствую­щее описание.

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

Следует иметь в виду, что запрос к виртуальной таблице может оказаться невыполнимым, поскольку при его преобразовании полу­чается некорректный оператор. Является ли заданная операция вы­полнимой - зависит от того, как реализован алгоритм преобразова­ния запросов в конкретной системе.

Как видно из описания формата оператора CREATE VIEW, столб­цы виртуальной таблицы могут быть заданы явно, а могут быть и опу­щены. В последнем случае представление будет содержать те столб­цы, которые определены в подзапросе. В явном виде необходимо задавать имена столбцов в случае, если какой-нибудь столбец пред­ставления получен путем вычисления арифметического выражения либо является результатом стандартной функции или константой, т.е. в том случае, когда столбец не имеет имени и не может его нас­ледовать. В явном виде следует задавать имена столбцов также в том случае, если без этого два (или более) столбца имели бы одинако­вое имя.

Представления могут быть изменяемыми и неизменяемыми.

Уничтожить представление можно командой

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

Цели использования представлений

Представления можно использовать для достижения ряда целей.

1. Освобождение пользователя от просмотра не относящейся к нему информации. Причем это может быть как вертикальное усече­ние таблицы, когда в представление включаются не все поля, а толь­ко те, которые интересуют пользователя, так и горизонтальное - ког­да отбираются определенные записи. Например, пусть в отделе снаб­жения функции сотрудников закреплены в соответствии с группами материалов. Предположим, что коды материалов группы «металлы» начинаются с буквы М. Тогда можно определить представление, в которое входят только те строки, для которых код материала начина­ется с буквы М. Сотрудник, отвечающий за соответствующую группу материалов, будет работать с ним так, как если бы таблица содержала сведения только о металлах.

Допустим, что имеется базовая таблица «Post», в которой хранят­ся сведения о поставках всех материалов. Код материала содержится в столбце Kodmat. Тогда представление, отображающее поставку ме­таллов, будет иметь следующий вид:

CREATE VIEW postmet

AS SELECT *

FROM post

WHERE kodmat LIKE "M%";

1. Улучшение защиты данных: пользователь видит (и может с ними работать) только часть данных, определенных для него представле­нием. Разработчики могут ограничить для конкретных пользовате­лей доступ к отдельным столбцам или строкам таблицы, а также за­дать для представления допустимые для него привилегии.

2. Упрощение сложных запросов. Рассмотрим следующий при­мер. Пусть у нас имеются три таблицы. Одна из них («Postavka») содержит сведения о поставках продукции и включает колонки: Код по­ставщика (kod_post), Код материала (kod_mat), Дата поставки (dat_post), Количество поставленной продукции (kolv), Цена (cena). Вторая таблица («Sp_post») содержит колонки Код поставщика (kod_post) и Наименование поставщика (naim_pst). Третья таблица («Sp_mat») содержит Код (kod_mat) и Наименование (naim_mat) материала. В ответах на запросы и в самих запросах обычно использу­ются не коды, а названия соответствующих сущностей. В этом случае можно создать следующее представление:

CREATE VIEW postr

AS SELECT naim_post, naim_mat, dat_post, kolv

FROM sp_post, postavka, sp_mat

WHERE postavka.kod_post=sp_post.kod_post

AND sp_mat.kod_mat=postavka.kod_mat;

Запрос на выдачу информации о поставке стали может выглядеть в этом случае следующим образом:

SELECT *

FROM postr

WHERE naim_mat="сталь";

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

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

CREATE VIEW post2 (kod_post, dat_post, kolv,summa)

AS SELECT kod_post, dat_post, kolv, kolv*cena

FROM postavka;

Практически цели 1 и 4 являются частными случаями цели 3.

При определении VIEW можно задать любой правильный опера­тор SELECT за некоторыми исключениями. Поскольку синтаксис как команды SELECT, так и команды CREATE VIEW несколько различа­ется в разных СУБД, то затруднительно дать эти ограничения в об­щем виде. Поэтому при создании представлений следует вниматель­но посмотреть, какие ограничения накладывает конкретная реализа­ция'языка.

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

Ограничения при использовании представлений

Характеристики

Список файлов книги