Простой подзапрос

    Пример:

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

    SELECT familia FROM S

    WHERE nomer IN (SELECT nomer_s FROM SP WHERE nomer_p = 'P2') 

    Результат:

 

    При обработке полного запроса сначала  обрабатывается подзапрос. Этот подзапрос  возвращает множество номеров поставщиков  поставляющих деталь P2, а именно множество: ('S1', 'S2', 'S3', 'S4').

    Поэтому первоначальный запрос эквивалентен следующему простому запросу: 

    SELECT familia FROM S WHERE nomer_s IN ('S1', 'S2', 'S3', 'S4'); 

    Задачу  из примера можно решить, применяя соединение. 

    SELECT familia FROM S, SP WHERE (S.nomer = SP.nomer_s) AND (nomer_p = 'P2'); 

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

    Пример:

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

    SELECT familia FROM S WHERE nomer_s IN

    (SELECT nomer_s FROM SP WHERE nomer_p IN

    (SELECT nomer FROM P WHERE zvet = 'красный')) 

    Результат:

 

    Если  использовать соединение трех таблиц, то результат следующий: 

    Результат:

 

    Подзапрос с операцией отличной от IN 

    Пример:

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

    SELECT nomer FROM S WHERE gorod =

    (SELECT gorod FROM S WHERE nomer = 'S1'); 

    Результат:

 

    Если  заранее известно, что подзапрос возвратит в точности 1 значение, то вместо IN можно употребить операцию сравнения (=, <>, >, ...). Однако, если подзапрос вернет более одного значения, то возникнет ошибка. 

 

     Стандартные функции

    На  вопросы типа "сколько строк  в таблице?" отвечают стандартные функции языка SQL (агрегирующие функции): 

    COUNT - количество строк

    SUM - сумма значений в столбце

    AVG - среднее арифметическое по значениям  в столбце

    MAX - наибольшее значение в столбце

    MIN - наименьшее значение в столбце 

    SUM и AVG применяются только к столбцам числового типа. 

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

    Специальный вид функции COUNT(*) предназначен для подсчета всех строк в таблице, без исключения дубликатов. 

    Функция в части SELECT 

    Пример:

    Подсчитать  общее количество поставщиков. 

    SELECT COUNT(*) FROM S 

    Результат:

 

    Функция в части SELECT с исключением  дубликатов 

    Пример:

    Подсчитать  общее количество поставщиков, поставляющих детали. 

    SELECT COUNT(DISTINCT nomer_s) FROM SP; 

    Результат:

 

    Функция в части SELECT с условием в части WHERE 

    Пример:

    Подсчитать  общее количество поставляемых деталей P2 (сколько деталей по всем поставкам?). 

    SELECT SUM (kol) FROM SP WHERE nomer_p = 'P2'; 

    Результат:

    Группирование записей 

    Пример:

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

    SELECT nomer_p, SUM(kol) FROM SP GROUP BY nomer_p; 

    Результат:

 

    Как исполняется такой оператор? 

    1) Часть GROUP BY перекомпоновывает таблицу,  указанную в части FROM в группы, таким образом, чтобы в каждой группе все строки имели одно и то же значение в столбце, указанном в GROUP BY.

    2) К каждой группе перекомпонованной  таблицы (а не к каждой исходной  строке) применяется часть SELECT. 

    Каждое  выражение в части SELECT должно принимать единственное значение для группы. То есть, оно может быть либо самим столбцом в части GROUP BY, либо арифметическим выражением, содержащим такой столбец, либо константой, либо функцией (например, SUM), которая сводит разные значения к одному.

    Тема 9. Обновление строк  в таблице

    Оператор  обновления (модификации) строк в  одной таблице имеет вид: 

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

    SET <имя столбца> = <выражение>{, <имя  столбца> = <выражение>}

    [ WHERE <условие> ]; 

    Все строки в таблице, которые удовлетворяют <условию>, изменяются в соответствии с присваиваниями в части SET. 

    Примеры: 

    Обновление  единственной строки 

    Изменить  цвет детали P2 на желтый и увеличить  ее вес на 5. 

    UPDATE P SET zvet='желтый', ves=ves+5 WHERE nomer='P2'; 

    Обновление нескольких строк 

    UPDATE S SET kapital=kapital*2 WHERE gorod='Лондон' 

    Обновление  нескольких таблиц 

    Изменить  номер поставщика с S2 на S9. 

    UPDATE S SET nomer='S9' WHERE nomer='S2'; 

    UPDATE SP SET nomer='S9' WHERE nomer='S2'; 

    В одном операторе невозможно обновить две таблицы! 

    После первого оператора БД стала противоречивой. Теперь в ней есть поставки у которой  нет поставщика. В этом противоречивом состоянии БД будет находиться до завершения второго оператора. Изменения  порядка операторов принципиально  ничего не меняет. Поэтому, с точки зрения целостности БД важно обеспечить завершение обоих операторов, а не одного из них (см. дальше).

    Тема 10. Удаление строк

 

    DELETE

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

    [ WHERE <условие> ]; 

    Этот  оператор удаляет все строки из таблицы, которые удовлетворяют <условию>.

    Тема 11. Вставка строк

    Две формы оператора INSERT

    а)

      INSERT

      INTO <имя таблицы> [ ( <имя столбца>{, <имя столбца>} ) ]

      VALUES ( <константа>{, <константа>} ); 

    б)

      INSERT

      INTO <имя таблицы> [ ( <имя столбца>{, <имя столбца>} ) ]

      <подзапрос>; 

    В форме (а) в таблицу вставляется  строка из констант, перечисленных  в части VALUES. Причем, i-ая константа  соответствует i-му столбцу в части INTO.

    В форме (б) сначала вычисляется <подзапрос> (результат которого - таблица) и  результат подзапроса вставляется в таблицу <имя таблицы>. 

    Пример:

    Добавить  в таблицу P деталь P7: город Москва, вес 2, название и цвет пока не известны. 

    INSERT INTO P (nomer, gorod, ves) VALUES ('P7', 'Москва', 2); 

    В таблице P появляется новая строка. Порядок столбцов в части INTO не обязательно должен совпадать с порядком столбцов при создании таблицы. 

 

     Вставка единственной строки с опущенными именами столбцов 

    Добавить  в таблицу P деталь P8: название "звездочка", цвет розовый, вес 14, город Берлин. 

    INSERT INTO P VALUES ('P8', 'звездочка', 'розовый', 14, 'Берлин'); 

    Если  имена столбцов не заданы, то предполагается перечисление всех столбцов таблицы  в порядке их перечисления в операторе CREATE. 

    Вставка множества строк 

    Для каждой поставляемой детали получить ее номер и объем ее поставки, сохранить результат в отдельной таблице. 

    CREATE TABLE VREMEN

    (nomer CHAR(6),

     sum_postavki INTEGER) 

    INSERT INTO VREMEN SELECT nomer_p, SUM(kol) FROM SPGROUP BY nomer_p

    Тема 12. Управление транзакциями

    Вернемся  к примеру в котором заменяется номер поставщика S2 на S9. Обобщим этот пример. Допустим, есть некий язык программирования, внутри которого можно выполнять операторы SQL. Напишем процедуру, которая будет менять номер поставщика с SX на SY. 

    Trans: proc(SX, SY);

      ON ERROR: begin ROLLBACK; RETURN end;

      EXEC UPDATE S SET nomer=SY WHERE nomer=SX;