Билеты и ответы, страница 10

SELECT товар.название, фирма_поставщик.название FROM товар,фирма_поставщик

WHERE товар.id_фирмы = фирма_поставщик.id_фирмы;

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

SELECT фирма.название, фирма.адрес, сотрудники.фамилия, сотрудники.имя FROM фирма, сотрудники WHERE фирма.id_фирмы = сотрудники.id_фирмы;

SELECT будет работать следующим образом. Он последовательно будет перебирать все записи из таблицы «Фирма». Для каждой записи из «Фирма» будет просмотрена таблица «Сотрудники». Как только будет найдена запись из «Сотрудники», удовлетворяющая условию WHERE, в результат добавится новый ряд, сформированный из полей название и адрес записи из «Фирма» и полей фамилия и имя записи из «Сотрудники». Если же в «Сотрудники» не будет найдено ни одной записи, то информация о текущей записи из «Фирма» в результат не попадет.

Таблица «Фирма»

Таблица «Сотрудники»

Рис.8.1. Пример таблиц «Фирма» и «Сотрудники»

INNER JOIN, LEFT JOIN, RIGHT JOIN

SELECT DISTINCTROW фирма.название, фирма.адрес, сотрудники.фамилия, сотрудники.имя FROM фирма INNER JOIN сотрудники ON фирма.id_фирмы = сотрудники.id_фирмы;

INNER JOIN – самый обычный тип связывания, объединяет записи двух таблиц, если связующие поля обеих таблиц содержат одинаковые значения.

Рис.8.2. Результат связывания двух таблиц после выполнения запроса

При выборке из двух таблиц мы получаем все возможные комбинации значений, при этом из всех таблиц выбираются только те записи, которые удовлетворяют заданному условию. Например, в приведенном выше примере на получение информации о фирмах и людях, в них работающих, мы получим список только тех компаний, для которых в таблице «Сотрудники» есть хотя бы один человек. А если мы хотим в этом запросе получить информацию обо всех фирмах, в том числе и тех, в которых нет ни одного сотрудника?

SELECT фирма.название, фирма.адрес, сотрудники.фамилия, сотрудники.имя FROM фирма LEFT JOIN сотрудники ON фирма.id_фирмы = сотрудники.id_фирмы;

LEFT JOIN - используется для создания левого внешнего объединения, при котором все записи из первой (левой) таблицы включаются в динамический набор, даже если во второй (правой) таблице нет соответствующих им записей.

Рис.8.3. Результат оператора SELECT с использованием указателя LEFT JOIN

Для отбора всех сотрудников, в том числе и тех, которые не приписаны ни к одной из фирм, надо выполнить запрос:

SELECT фирма.название, фирма.адрес, сотрудники.фамилия, сотрудники.имя FROM фирма RIGHT JOIN сотрудники ON фирма.id_фирмы = сотрудники.id_фирмы;

RIGHT JOIN - используется для создания правого внешнего объединения, при котором все записи из второй (правой) таблицы включаются в динамический набор, даже если в первой (левой) таблице нет соответствующих им записей.

Рис.8.4. Результат оператора SELECT с использованием указателя RIGHT JOIN

Использование BETWEEN

С помощью BETWEEN ... AND ... (находится в интервале от ... до ...) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне.

Например, выдать перечень продуктов, в которых значение содержания белка находится в диапазоне от 10 до 50:

SELECT Продукт, Белки FROM Продукты WHERE Белки BETWEEN 10 AND 50;

Можно задать и NOT BETWEEN (не принадлежит диапазону между), например

SELECT Продукт, Белки, Жиры FROM Продукты

WHERE Белки NOT BETWEEN 10 AND 50 AND Жиры > 100;

BETWEEN особенно удобен при работе с данными, задаваемыми интервалами, начало и конец которых расположен в разных столбцах.

Если, например, потребовалось узнать, какие изменения минимальных окладов производились в 1993/94 учебном году, то можно выдать запрос

SELECT Начало, Оклад FROM Оклад

WHERE Начало BETWEEN #01-09-93# AND #31-08-94#;

Отметим, что при формировании запросов значения дат следует заключать в #.

Для выявления всех значений минимальных окладов, которые существовали в 1993/94 учебном году, можно сформировать запрос

SELECT * FROM Оклад WHERE Начало BETWEEN #01-09-93# AND #31-08-94#

OR Конец BETWEEN #01-09-93# AND #31-08-94#;

Проверка на вхождение во множество IN

Логическая операция, которую можно использовать в условии для операторов SELECT, UPDATE и DELETE ­это операция IN. Эта логическая операция возвращает истину, когда значение слева от слова IN входит во множество значений, указанного справа от слова IN. Множество возможных значений может быть указано явно - через запятую в скобках, а может формироваться другим оператором SELECT:

<выражение> IN (<значение 1>, <значение 2>, ....)

<выражение> IN (<оператор SELECT>)

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

SELECT* FROM Блюда WHERE Основа IN (Яйца Крупа Овощи);

Форма IN является в действительности просто краткой записью последовательности отдельных сравнений, соединенных операторами OR. Предыдущее предложение эквивалентно такому:

SELECT * FROM Блюда WHERE Основа = Яйца OR Основа = Крупа OR Основа = Овощи;

Например, получить адреса фирм «АО Сделай Сам» и «ОСО Довгань»:

SELECT название,адрес FROM фирма WHERE название IN ("АО Сделай Сам", "ООО Довгань");

Это запрос можно сформулировать и с помощью операций "=" и "OR", но во многих случаях использование операции IN более наглядно и компактно.

Например, найти названия и адреса всех фирм, поставляющих молоко. Этот запрос выполняется одним SQL-оператором:

SELECT название, адрес FROM фирма WHERE id_фирмы IN (SELECT товар.id_фирмы FROM товар WHERE название = "молоко");

Найти все фирмы, которые еще не поставляют молоко:

SELECT название, адрес FROM фирма WHERE id_фирмы NOT IN(SELECT товар.id_фирмы FROM товар WHERE название = "молоко");

Если известно, что оператор SELECT возвращает одну запись, то можно вместо слова IN использовать проверку на равенство.

Например, получить список самых дорогих товаров, а именно их названия и цену (цена будет для всех одна - максимальная), то надо выполнить запрос:

SELECT название, цена FROM товар WHERE цена = (SELECT MAX(цена) FROM товар);

Использование LIKE

Используется для сравнения строкового выражения с образцом в выражении SQL.

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

SELECT название, цена FROM товар WHERE название Like "сыр*";

Следовательно, в приведенном примере SELECT будет осуществлять выборку записей из таблицы товар, для которых значение в столбце название начинается сочетанием «сыр» и содержит любую последовательность из нуля или более символов, следующих за сочетанием «сыр». Если бы среди продуктов были «Голанский сыр», «Тильзицкий сыр» и т.п., то они не были бы найдены. Для их отыскания надо изменить фразу WHERE:

WHERE название LIKE "*сыр*";

Это позволит отыскать все сыры.

Предложение GROUP BY

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого пол, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять пол и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Порядков для каждого значения пол snum. GROUP BY, однако, позволит Вам поместить их все в одну команду:

=============== SQL Execution Log ==============

| |

| SELECT snum, MAX (amt) |

| FROM Orders |

| GROUP BY snum; |

| =============================================== |

| snum |

| ------ -------- |

| 1001 767.19 |

| 1002 1713.23 |

| 1003 75.75 |

| 1014 1309.95 |

================================================

GROUP BY применяет агрегатные функции независимо от серий групп которые определяются с помощью значения поля в целом. В этом случае, каждая группа состоит из всех строк с тем же самым значением пол snum, и MAX функция применяется отдельно для каждой такой группы. Это значение пол, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, также как это делает агрегатная функция. Результатом является совместимость которая позволяет агрегатам и полям объединяться таким образом. Вы можете также использовать GROUP BY с многочисленными полями. Совершенству вышеупомянутый пример далее, предположим что вы хотите увидеть наибольшую сумму приобретений получаемую каждым продавцом каждый день. Чтобы сделать это, вы должны сгруппировать таблицу Порядков по датам продавцов, и применить функцию MAX к каждой такой группе, подобно этому:

=============== SQL Execution Log ==============

| |

| SELECT snum, odate, MAX (amt) |

| FROM Orders |

| GROUP BY snum, odate; |

| =============================================== |

| snum odate |

| ------ ---------- -------- |