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

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

...начало

Некоторые возможности языка 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 1000000
S1 P2 J1 100000
S2 P2 J2 10000
S3 P1 J3 10000000
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'
);
Имя
Джонс

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