<имя столбца> типа CHAR.
<строковая константа> - это образец, в котором:
- "_" - любой одиночный символ,
- "%" - любое количество любых символов, в том числе нулевое, остальные символы означают сами себя.
Тема 7. Выборка данных. Соединение
Операция, в которой выборка данных проводится более чем из одной таблицы называется "соединение". Результирующая таблица также называется соединением.
Простое эквисоединение
Пример:
Выбрать все данные о поставщиках и деталях, размещенных в одном и том же городе, то есть соразмещенных.
SELECT S.*, P.* FROM S, P WHERE S.gorod=P.gorod;
Очевидно, что нужные данные получаются из двух таблиц S и P - они указаны в части FROM, а условие соединения записывается в части WHERE.
| Поля таблицы "S" | Поля таблицы "P" | |||||||
| nomer | familia | kapital | gorod | nomer | nazvan | zvet | ves | gorod |
| S1 | Смит | 20 | Лондон | P1 | Гайка | Красный | 12 | Лондон |
| S1 | Смит | 20 | Лондон | P4 | Винт | Красный | 14 | Лондон |
| S1 | Смит | 20 | Лондон | P6 | Блюм | Красный | 19 | Лондон |
| S2 | Джонс | 10 | Париж | P2 | Болт | Зеленый | 17 | Париж |
| S2 | Джонс | 10 | Париж | P5 | Кулачок | Голубой | 12 | Париж |
| S3 | Блейк | 30 | Париж | P2 | Болт | Зеленый | 17 | Париж |
| S3 | Блейк | 30 | Париж | P5 | Кулачок | Голубой | 12 | Париж |
| S4 | Кларк | 20 | Лондон | P1 | Гайка | Красный | 12 | Лондон |
| S4 | Кларк | 20 | Лондон | P4 | Винт | Красный | 14 | Лондон |
| S4 | Кларк | 20 | Лондон | P6 | Блюм | Красный | 19 | Лондон |
Поставщик из Сарова не попал в результирующую таблицу, так как в этом городе не хранятся никакие детали. Аналогично, результат не содержит детали P3, так как ее город - Рим.
Некоторые замечания
- Оба столбца в условии соединения должны быть либо числовыми, либо строковыми.
- Совсем не обязательно, чтобы столбцы в условии соединения назывались одинаково, хотя это бывает часто.
- В случае операции равенства ("=") в условии соединения, оно называется эквисоединением; в условии соединения могут быть и другие операции.
- Часть WHERE может включать и другие дополнительные условия, помимо условия соединения.
- Можно предусмотреть выборку не всех, а некоторых столбцов; кроме того, первоначальный оператор можно еще более упростить:
SELECT * FROM S, P WHERE S.gorod=P.gorod;
- По определению, эквисоединение должно порождать два полностью одинаковых столбца; если исключить один из этих столбцов, то результат будет называться естественным соединением.
- Можно образовать соединение любого числа таблиц.
Соединение по условию "больше, чем"
Пример:
Выбрать все данные о поставщиках и деталях таких, что город поставщика следует за городом, где хранится деталь в алфавитном порядке.
SELECT S.*, P.* FROM S, P WHERE S.gorod > P.gorod;
Соединение с дополнительным условием
Пример:
Выбрать все комбинации номеров поставщиков, номеров деталей и состояний такие, что поставщик и деталь соразмещены. Опустить поставщиков с капиталом 20.
SELECT S.nomer, P.nomer, S.kapital FROM S, P
WHERE (S.gorod = P.gorod) AND (kapital <> 20);
S.gorod = P.gorod - условие соединения
kapital <> 20 - дополнительное условие
Результат:
| nomer | nomer | kapital |
| S2 | P2 | 10 |
| S2 | P5 | 10 |
| S3 | P2 | 30 |
| S3 | P5 | 30 |
Соединение таблицы с ней самой
Пример:
Выбрать все пары номеров поставщиков такие, что оба поставщика находятся в одном и том же городе.
SELECT PERV.nomer, VTOR.nomer FROM S PERV, S VTOR
WHERE PERV.gorod = VTOR.gorod;
Таблица S дважды упоминается в части FROM, чтобы различать два ее упоминания вводятся произвольные псевдонимы.
Результат:
| nomer | nomer |
| S1 | S1 |
| S1 | S4 |
| S2 | S2 |
| S2 | S3 |
| S3 | S3 |
| S3 | S2 |
| S4 | S4 |
| S4 | S1 |
| S5 | S5 |
Можно привести этот результат в порядок, если расширить часть WHERE.
SELECT PERV.nomer, VTOR.nomer
FROM S PERV, S VTOR
WHERE (PERV.gorod = VTOR.gorod) AND (PERV.nomer < VTOR.nomer);
Последнее условие ("<") даст двоякий эффект:
а) оно исключает пары номеров вида (x,x);
б) гарантирует, что пары вида (x,y) и (y,x) не будут появляться одновременно.
Результат:
| nomer | nomer |
| S1 | S4 |
| S2 | S3 |
Подзапрос - это SELECT, который вложен во внешний оператор SELECT.
Обычно подзапросы требуются для выборки значений, когда во внешнем запросе применяется операция принадлежности (IN).
Простой подзапрос
Пример:
Выбрать фамилии поставщиков, которые поставляют деталь P2.
SELECT familia FROM S
WHERE nomer IN (SELECT nomer_s FROM SP WHERE nomer_p = 'P2')
Результат:
| familia |
| Смит |
| Джонс |
| Блейк |
| Кларк |
При обработке полного запроса сначала обрабатывается подзапрос. Этот подзапрос возвращает множество номеров поставщиков поставляющих деталь 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 = 'красный'))
Результат:
| familia |
| Смит |
| Джонс |
| Кларк |
Если использовать соединение трех таблиц, то результат следующий:
Результат:
| familia |
| Смит |
| Джонс |
| Кларк |
| Смит |
| Смит |
Подзапрос с операцией отличной от IN
Пример:
Выбрать номера поставщиков, находящихся в том же городе, что и поставщик S1.
SELECT nomer FROM S WHERE gorod =
(SELECT gorod FROM S WHERE nomer = 'S1');
Результат:
| nomer |
| S1 |
| S4 |
Если заранее известно, что подзапрос возвратит в точности 1 значение, то вместо IN можно употребить операцию сравнения (=, <>, >, ...). Однако, если подзапрос вернет более одного значения, то возникнет ошибка.
Стандартные функции
На вопросы типа "сколько строк в таблице?" отвечают стандартные функции языка SQL (агрегирующие функции):
COUNT - количество строк
SUM - сумма значений в столбце
AVG - среднее арифметическое по значениям в столбце
MAX - наибольшее значение в столбце
MIN - наименьшее значение в столбце
SUM и AVG применяются только к столбцам числового типа.
После имени функции, в скобках, нужно записывать имя столбца, а перед ним может присутствовать слово DISTINCT. Оно указывает, что дубликаты следует исключить перед тем как будет применяться функция.
Специальный вид функции COUNT(*) предназначен для подсчета всех строк в таблице, без исключения дубликатов.
Функция в части SELECT
Пример:
Подсчитать общее количество поставщиков.
SELECT COUNT(*) FROM S
Результат:
| COUNT |
| 5 |
Функция в части SELECT с исключением дубликатов
Пример:
Подсчитать общее количество поставщиков, поставляющих детали.
SELECT COUNT(DISTINCT nomer_s) FROM SP;
Результат:
| COUNT |
| 4 |
Функция в части SELECT с условием в части WHERE
Пример:
Подсчитать общее количество поставляемых деталей P2 (сколько деталей по всем поставкам?).
SELECT SUM (kol) FROM SP WHERE nomer_p = 'P2';
Результат:
| SUM |
| 1000 |
Группирование записей
Пример:
Требуется подсчитать общее количество поставляемых деталей по каждому виду деталей. То есть, по каждому виду выдать номер детали и суммарный объем поставок.
SELECT nomer_p, SUM(kol) FROM SP GROUP BY nomer_p;
Результат:
| nomer_p | SUM |
| P1 | 600 |
| P2 | 1000 |
| P3 | 400 |
| P4 | 500 |
| P5 | 500 |
| P6 | 100 |
Как исполняется такой оператор?
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.