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

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

Запросы без соединения таблиц

Вывести веса деталей

Пример таблицы $$P$$

Номер детали	Название	Цвет	Вес	Город
P1	болт	чёрный	0.1	Париж
P2	гайка	чёрный	0.05	Париж

SELECT номер_детали, вес
FROM P;

Номер детали	Вес
P1	0.1
P2	0.05

Выборка с ограничением

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

Пример таблицы $$S$$

Номер поставщика	Имя	Состояние	Город
S1	Смит	5	Париж
S2	Пит	25	Париж

SELECT номер_поставщика, состояние, город
FROM S
WHERE город = 'Париж' AND состояние > 20;

Номер поставщика	Состояние	Город
S2	25	Париж

Ещё пример: выдать номера поставщиков с состоянием от 10 до 20:

SELECT номер_поставщика, состояние, город
FROM S
WHERE состояние BETWEEN 10 AND 20;

Выборка с упорядочиванием

Упорядочивание:

• ASC - по возрастание (стоит по умолчанию);
• DESC - по убыванию.

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

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

Пример: выдать номера поставщиков, города проживания и состояния поставщиков с именем Смит в алфавитном порядке городов и в порядке убывания состояния.

Таблица $$S$$:

Номер поставщика	Имя	Состояние	Город
S4	Смит	25	Манчестер
S1	Смит	25	Лондон
S2	Смит	50	Лондон
S3	Смит	40	Манчестер

SELECT номер_поставщика, город, состояние
FROM S
WHERE имя = 'Смит'
ORDER BY город ASC, состояние DESC;

Номер поставщика	Город	Состояние
S2	Лондон	50
S1	Лондон	25
S3	Манчестер	40
S4	Манчестер	25

Выборка с конструкцией LIKE

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

Пример: выдать номера поставщиков, названия городов которых называются с "Л".

SELECT номер_поставщика
FROM S
WHERE город LIKE "Л%";

Выборка с конструкцией IN

Проверка принадлежности атрибута какому-либо множеству.

Выдать поставщиков, состояние которых равно 25, 40, 60 или 70.

SELECT *
FROM S
WHERE состояние IN(25, 40, 60, 70);

Запросы с использованием соединения таблиц

Соединение трёх таблиц

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

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

$$S$$ Номер поставщика Имя Состояние Город S1 Смит 15 Лондон S2 Джонс 10 Париж S3 Блейк 15 Чикаго

$$P$$ Номер детали Название Цвет Вес Город P1 болт чёрный 0.01 Лондон P2 гайка чёрный 0.02 Париж

$$SPJ$$ Номер поставщика Номер детали Номер изделия Количество S1 P1 J1 10^6 S1 P2 J1 10^5 S2 P2 J2 10^4 S3 P1 J3 10^7

SELECT DISTINCT S.город, P.город
FROM S, P, SPJ
WHERE SPJ.номер_поставщика = S.номер_поставщика AND SPJ.номер_детали = P.номер_детали;

Город поставщика (S.город)	Город детали (P.город)
Лондон	Лондон
Лондон	Париж
Париж	Париж
Чикаго	Лондон

Соединение таблицы с самой собой

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

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

$$S$$

Номер поставщика	Имя	Состояние	Город
S1	Смит	15	Лондон
S2	Джонс	10	Париж
S3	Блейк	15	Лондон

Используются псевдонимы.

SELECT PS1.номер_поставщика, PS2.номер_поставщика
FROM S PS1, S PS2
-- чтобы исключить дубли, вводим отношение порядка
WHERE PS1.город = PS2.город AND PS1.номер_поставщика < PS2.номер_поставщика;

PS1.номер_поставщика	PS2.номер_поставщика
S1	S3

Использование конструкции INNER JOIN

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

SELECT имя
FROM S JOIN SPJ ON S.номер_поставщика = SPJ.номер_поставщика JOIN P ON SPJ.номер_детали = P.номер_детали AND цвет = 'красный';

Подзапросы

Это выражение, которое вложено в другое выражение. Сначала обрабатывается внутренний подзапрос, потом основной запрос (внешний).

Пример

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

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

$$S$$ Номер поставщика Имя Состояние Город S1 Смит 15 Лондон S2 Джонс 10 Париж

$$SPJ$$ Номер поставщика Номер детали Номер изделия Количество S1 P1 J1 100 S2 P2 J1 100 S3 P3 J2 100

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

Имя
Джонс