18102010 (Лекции)

SeregaProMai.Narod.ru ©

Лекция от 18.10.2010. (на основе лекции Димы + фото с лекции-презентации)

Подзапросы.

Подзапрос, как правило, можно написать в том месте, где требуется получить/вычислить какое-либо одно значение. В этом случае просто на месте значения пишут подзапрос в скобках. При этом фраза select этого подзапроса должна возвращать ровно одно поле, а логика остальных частей должна обеспечивать, чтобы возвращалось не более одной записи. Если не будет сформировано ни одной, то подзапрос возвращает null, если же несколько, то возникнет ошибка. Подзапросы подобного рода могут фигурировать, в частности, в вычисляемых выражениях или в операциях сравнения.

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

- выражение IN (подзапрос)

- выражение =ALL (подзапрос)

- выражение =SOME (подзапрос)

- выражение =ANY (подзапрос) (интернет)

SELECT COUNT (A) AS dbo.Поставщик

FROM (SELECT DISTINCT НомерП FROM Поставки)

AS ??

Обязательно подзапросу присваивается имя. ???

SELECT НомерП, СтатусП FROM Поставщики

WHERE СтатусП (SELECT MAX(СтатусП) FROM Поставщики)

При использовании подзапросов часто используется оператор IN. Подзапрос возвращает список значений с помощью IN, проверяется, совпадает ли значение заданного статуса с одним из этих значений.

???

SELECT * FROM Поставщики

WHERE НомерП IN

(SELECT DISTINCT НомерП

FROM Поставки

WHERE НомерП=2)

В зависимости от того, как подзапрос взаимодействует с основным запросом, различают простые и контролируемые запросы.

Простой сначала выполняется подзапрос, после выполняется основной запрос, который использует ??? подзапрос. Подзапрос выполняется один раз.

???

EXISTS и NOT EXISTS

Если подзапрос вообще возвращает какие-нибудь значения, то EXISTS подзапрос возвращает TRUE, a NOT EXISTS подзапрос - FALSE, например:
SELECT columnl FROM tl WHERE EXISTS (SELECT * FROM t2);
Традиционно подзапрос в EXISTS начинается с SELECT *, но он может начинаться с SELECT 5 или SELECT columnl, либо с еще чего-нибудь. MySQL игнорирует список SELECT в таком подзапросе, потому это не важно.
Ниже представлены примеры:

• Какие типы магазинов есть в одном или более городов?
  SELECT DISTINCT store_type FROM Stores
  WHERE EXISTS (SELECT * FROM Cities_Stores
  WHERE Cities_Stores.store_type = Stores.store_type);

• Каких типов магазинов нет ни в одном городе?
  SELECT DISTINCT store_type FROM Stores
  WHERE NOT EXISTS (SELECT * FROM Cities_Stores
  WHERE Cities_Stores.store_type = Stores.store_type);

• Какой тип магазинов есть во всех городах?

SELECT DISTINCT store_type FROM Stores SI

WHERE NOT EXISTS (SELECT * FROM Cities WHERE NOT EXISTS

(SELECT * FROM Cities_Stores

WHERE Cities_Stores.city = Cities.city AND Cities_Stores.store_type = Stores.store_type));

В последнем примере представлен дважды вложенный подзапрос NOT EXISTS. To есть конструкция NOT EXISTS содержится внутри другой конструкции NOT EXISTS. Формаль­но он отвечает на вопрос "есть ли город с магазином, которого нет в Stores?". Но проще сказать, что вложенный NOT EXISTS отвечает на вопрос "истинно ли х для всех у?". (интернет)

SELECT * FROM Поставщики

WHERE EXISTS

(SELECT * FROM Поставки

WHERE Поставки.НомерП=Поставщики.НомерП AND Поставки.НомерД=2)

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

Схема выполнения:

- выбирается первая строка

- номер поставщика передается в подзапрос, который выполняется поставлял ли этот поставщик деталь номер 2

- если за то, что результат подзапроса не идет и EXISTS вернет значение Истина и сведения номер поставщика будет помещены в результат основного запроса и т. д. остальные записи.

В некоторых случаях возникает необходимость использовать несколько копий одной таблицы. Для этого в SQL используются псевдонимы

SELECT *

FROM Таблица T1, Таблица T2.

Пример:

Необходимо найти детали, для которых можно найти другие детали, изготовленные из того же материала. (все выше набрано по Диминым лекциям + интернет, далее все мои лекции набраны по фото)

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

Результат выполнения этого запроса означает, что детали с номерами 3,4,5 поставляются только одним поставщиком.

Представление.

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

Если в качестве источника данных указано имя представления, то СУБД выполняет содержащийся в нем запрос и возвращает результат его выполнения.

По логике использования представления имеют много общего с подпрограммами. Если какую либо обработку данных приходится использовать многократно в различных запросах, то целесообразно реализовать её в виде представления и указать его имя там, где это необходимо.

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

CREATE VIEW dbo.Имя_представления

AS

SELECT ( текст запроса )

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

Уничтожение ненужных представлений выполняется с помощью инструкции DROP VIEW (уничтожить представление), имеющей следующий формат:

DROP VIEW dbo.Имя_представления

Целесообразность и правила использования представлений рассмотрим на примере следующей задачи. Необходимо дополнить таблицу Поставки, сведениями о статусе поставщиков и деталей. Ввести эти атрибуты в таблицу Поставки нельзя, так как это приведет к избыточному дублированию данных. Использование таблиц Поставщики, Детали, Поставки с условиями их соединения приводит к усложнению структуры запросов.

На рисунке приведен пример создания представления ПоставкиСТ, которое кроме данных содержащихся в таблице Поставки, возвращает значение статуса поставщиков и деталей.

Рассмотрим примеры запросов, которые используют это представление.

Отобразить данные о поставках деталей, статус которых > 2.

Упорядочить данные, содержащиеся в таблице Поставки, в порядке убывания статуса поставщиков.

Операторы модификации данных.

Модификация данных может выполняться с помощью операторов:

- INSERT (вставить)

- UPDATE (изменить)

- DELETE (удалить)

Оператор INSERT.

C помощью оператора INSERT можно добавить одну или несколько записей, выбранных в результате выполнения подзапроса.

Для добавления одной записи используется следующий формат:

INSERT имя таблицы [список столбцов]

VALUES (список констант или переменных)

Рассмотрим пример добавления нового поставщика.

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

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

INSERT имя таблицы [список столбцов]

SELECT [список столбцов] текст запроса

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

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

Заполнить эту таблицу данными можно при помощи следующего запроса.

Оператор UPDATE.

Этот оператор используется для изменения значения столбцов и имеет два формата.

Первый формат имеет следующий вид:

UPDATE имя таблицы

SET столбец = значение / выражение

WHERE условия

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

Увеличить на 1 статус поставщиков, поставляющих деталь №2.

Второй формат имеет следующий вид:

UPDATE Таблицы1

SET Таблица1.столбец = Таблица2.столбец

FROM Таблица2

WHERE условия

Он используется в тех случаях, когда определённым столбцам Таблицы1 нужно присвоить значения столбцов Таблицы2, в тех строках, для которых выполняются заданные условия.

В качестве примера использования данного формата оператора UPDATE, рассмотрим следующую задачу.

Добавим в таблицу Поставщики столбец Расстояние и присвоим ему значение расстояния до того города, в котором находится данный поставщик. Это значение будет извлечено из таблицы Города.

Оператор DELETE.

Используется для удаления строк из заданной таблицы и имеет следующий формат:

DELETE

FROM таблица

[WHERE условия];

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

Рассмотрим особенности использования данного оператора на конкретных примерах.

Необходимо удалить сведения о поставщике с номером 6. Пример выполнения этого запроса приведен на рисунке.

Из текста сообщения следует, что запрос успешно выполнен. Теперь попробуем удалить поставщика с номером 4.

Поставок от поставщика с номером 6 не было и его удаление не нарушает условий ссылочной целостности. Удаление поставщика с номером 4, от которого были поставки, нарушило бы условие ссылочной целостности. В таблице Поставки появились бы несвязанные записи. Поэтому СУБД прервало выполнение этого запроса.

Рассмотрим еще один пример. Необходимо удалить поставки от поставщиков, статус которых > 5.

7