СПАСОИ (10) - Лекция №10 - SQL (продолжение)

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество
Перейти к навигации Перейти к поиску

...начало

Некоторые возможности языка SQL

Подзапросы

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

Выдать имена поставщиков, которые поставляют по крайней мере одну красную деталь.

Примеры таблиц:

$$S$$
Номер поставщика Имя Состояние Город
S1 Смит 15 Лондон
S2 Джонс 10 Париж
$$P$$
Номер детали Название Вес Город Цвет
P1 болт 0.01 Лондон красный
P2 гайка 0.02 Париж красный
P3 винт 0.03 Лондон чёрный
$$SPJ$$
Номер поставщика Номер детали Номер изделия Количество
S1 P1 J1 100
S2 P2 J1 100
S3 P3 J2 100
SELECT имя
FROM S
WHERE номер_поставщика IN
    (
        SELECT номер_поставщика
        FROM SPJ
        WHERE номер_детали IN
            (
                SELECT номер_детали
                FROM P
                WHERE цвет_детали = 'красный'
            )
    );

Вычисление начинается с самого внутреннего подзапроса и наружу до самого внешнего.

Имя
Смит
Джонс

Коррелирование подзапросов

Выдать имена поставщиков, которые поставляют детали с номером P2.

SELECT имя_поставщика
FROM S
WHERE 'P2' IN
    (
        SELECT номер_детали
        FROM SPJ
        WHERE номер_поставщика = S.номер_поставщика
    );

Просматривается таблица S, и для каждой записи из неё номер поставщика подставляется во внутренний подзапрос; он выполняется, и система проверяет, принадлежит ли P2 найденному множеству.

Подзапросы в качестве таблицы в выражении FROM

Выдать имена поставщиков, которые поставляют по крайней мере одну красную деталь.

SELECT имя_поставщика
FROM S, (
            SELECT номер_поставщика
            FROM SPJ, P
            WHERE SPJ.номер_детали = P.номер_детали
                AND цвет = 'красный'
        ) NP -- псевдоним
WHERE S.номер_поставщика = NP.номер_поставщика;

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

Некоторые законы математической логики:

$$$\forall a(P) = \overline{\exists} a(\overline{P})$$$

$$$A\rightarrow B = \overline{A}\vee B$$$

$$$\overline{A\vee B} = \overline{A}\wedge\overline{B}$$$

NOT EXISTS

Выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером S2.

$$\forall\ деталь\ (деталь\ поставляет\ S2)\rightarrow деталь\ поставляет\ SX$$

$$=^1 \overline{\exists}\ деталь (\overline{деталь\ поставляет\ S2\rightarrow деталь\ поставляет\ SX})$$

$$=^2 \overline{\exists}\ деталь (\overline{\overline{деталь\ поставляет\ S2}\vee деталь\ поставляет\ SX})$$

$$=^3 \overline{\exists}\ деталь (деталь\ поставляет\ S2 \wedge \overline{деталь\ поставляет\ SX})$$

В SQL это будет:

SELECT DISTINCT номер_поставщика
FROM SPJ SPJX
WHERE NOT EXISTS (
                  SELECT номер_детали
                  FROM SPJ SPJY
                  WHERE номер_поставщика = 'S2'
                    AND NOT EXISTS (
                                    SELECT *
                                    FROM SPJ
                                    WHERE номер_поставщика = SPJX.номер_поставщика
                                      AND номер_детали= SPJY.номер_детали
                                   )
                 );

Некоторые стандартные функции SQL

COUNT(атрибут) - возвращает число строк таблицы по атрибуту. COUNT(DISTINCT атрибут) - возвращает мощность атрибута. COUNT(*) - возвращает число строк в таблице.

SUM(атрибут) - сумма значений атрибута.

AVG(атрибут) - среднее значение атрибута.

MAX(атрибут) - максимальное значение атрибута.

MIN(атрибут) - минимальное значение атрибута.

Пример

Выдать информацию по поставщикам, состояние которых не ниже среднего.

SELECT имя, состояние, город
FROM S SX
WHERE состояние >=
    (
        SELECT AVG(состояние)
        FROM S
        WHERE город = SX.город
    );

Ещё пример

Выдать номера деталей и общее их количество для всех деталей, поставляемых более чем одним поставщиком.

$$SPJ$$
Номер поставщика Номер детали Номер изделия Количество
S1 P2 J1 100
S1 P1 J1 200
S2 P2 J2 300
SELECT номер_детали, SUM(количество), COUNT(DISTINCT номер_поставщика)
FROM SPJ
-- HAVING выполняется после того, как группа сформирована
GROUP BY номер_детали HAVING COUNT(DISTINCT номер_поставщика) > 1;

Особенности разработки приложений для работы с БД в сети

Сервер БД должен обеспечивать:

  1. блокировку обновляемых записей;
  2. ведение транзакций;
  3. обработку тупиковых ситуаций.

Блокировка обновляемых записей

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

На языке PL/SQL:

1)

CREATE PROCEDURE проводка -- создание процедуры 
(
    номер1 IN NUMBER, -- дебет
    номер2 IN NUMBER, -- кредит
    сумма IN NUMBER
) IS
остаток1 NUMBER;

CURSOR курсор -- объявление курсора
(
    номер_счёта_1 NUMBER,
    номер_счёта_2 NUMBER,
) IS
SELECT номер, остаток
FROM счёт
WHERE номер = номер_счёта_1
    OR номер = номер_счёта_2
FOR UPDATE of остаток;

запись курсор%ROWTYPE; -- переменная того же типа, что и запись курсора: номер, остаток

BEGIN -- начало исполнительной части
    OPEN курсор(номер1, номер2);
    LOOP -- вход в цикл
        FETCH курсор INTO запись;
        EXIT WHEN курсор%NOTFOUND; -- выход из цикла, если записей больше нет
        IF запись.номер = номер.1 THEN остаток1 = запись.остаток - сумма;
            ELSE остаток1 = запись.остаток + сумма;
        END IF;
        UPDATE счёт SET остаток = остаток1;
        WHERE CURRENT OF курсор;
    END LOOP;
    CLOSE CURSOR; -- после этого оператора доступ к записям курсора становится недоступен
    COMMIT;
END;

Схема блокировки и обновления записи:

1 рабочая станция
проводка(100,1001,300)
2 рабочая станция
(1001,100,150)
Оператор процедуры Действие на сервере Оператор процедуры Действие на сервере
OPEN SELECT блокировка записей 100, 1001 OPEN SELECT, 100, 1001
UPDATE сегмент отката
номер-100-1001
остаток-200-1200
записи БД не обновляются
- ожидание
COMMIT записи из сегмента отката переписываются в БД
записи с номерами 100 и 1001 разблокируются
SELECT блокировка записей 100, 1001
- - UPDATE сегмент отката
номер-100-1001
остаток-350-1050
записи БД не обновляются
- - COMMIT записи из сегмента отката переписываются в БД
записи с номерами 100 и 1001 разблокируются

Первый процесс открывает курсор. На сервере выполняется оператор SELECT. Он найдёт записи с номерами 100 и 1001 и заблокирует их, так как указано FOR UPDATE.

При открытии курсора вторым процессом система найдёт (опять выполнится SELECT) записи 100 и 1001, но процесс перейдёт в состояние ожидания, так как записи уже заблокированы.

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

По оператору COMMIT данные из сегмента отказа переписываются в БД, то есть становятся видны всем пользователям. Записи с номерами 100 и 1001 разблокируются.

Теперь второй процесс может работать с записями 100 и 1001, тут повторяется всё то же самое.

Ведение транзакций

Логическая транзакция - совокупность изменений в БД, которые либо должны быть зафиксированы все, либо ни одно. Атомарная операция.

Схема ведения транзакций:

Предположим, Процесс 1 выполняет обновление данных, а Процесс 2 - чтение.

По оператору UPDATE в сегменте отката запоминаются записи до и после обновления. По оператору COMMIT система выполняет следующие действия:

  1. записи после обновления переписываются в общесистемный буфер (в БД) и становятся видны всем пользователям. В заголовок записи вносится время её обновления;
  2. записи после обновления переписываются в файл журнала транзакций. Размеры этого файла фиксированы, и если журнал на диске1 переполняется, то соответствующий файл закрывается, и система переключается на файл на диске2. Если включена специальная опция СУБД, то журнал транзакций на диске1 архивируется на внешний носитель. Важно, чтобы время архивации было меньше времени заполнения журнала транзакций на диске2.

продолжение...