Язык SQL продолжение Псевдонимы Теоретико множественные операции рассмотренные

Язык SQL (продолжение) Псевдонимы. Теоретико множественные операции, рассмотренные в реляционной алгебре Напоминаю, что в лекциях излагаются стандарты языков SQL 92 и SQL 2003. Язык SQL, используемый в ACCESS, имеет от них отличия, многие (но не все) из которых здесь упоминаются. Вам следует учитывать эти отличия!

ПСЕВДОНИМЫ СТОЛБЦОВ И ТАБЛИЦ Псевдонимы столбцов пишутся в списке столбцов после соответствующего столбца в виде выражения: AS заголовок_столбца. Например: SELECT Client. Name AS Клиент, Address AS Адрес FROM Клиенты; Псевдонимы таблиц пишут через пробел после имени таблицы: SELECT Т 1. Имя, Т 2. Адрес FROM Клиенты Т 1, Контакты Т 2; MS ACCESS автоматически добавит ключевое слово AS перед каждым псевдонимом таблицы.

Пример псевдонимов для вычисляемых столбцов: SELECT Регион, SUM(Сумма_заказа) AS [Общая сумма заказа], AVG(Сумма_заказа) AS [Средняя сумма заказа], МАХ(Сумма_заказа) AS Максимум, MIN(Сумма_заказа) AS Минимум FROM Клиенты GROUP BY Регион • Итоговые функции можно использовать в выражениях select и having, но их нельзя применять в выражении where. • Оператор having аналогичен оператору where, но в отличие от where он отбирает записи в группах. • Допустим, требуется определить, в каких регионах более одного клиента. С этой целью можно воспользоваться таким запросом: SELECT Регион, Count(*) FROM Клиенты GROUP BY Регион HAVING COUNT(*) > 1;

Уточнения запроса SELECT список. Столбцов FROM список. Таблиц; Для уточнения запроса на выборку данных служат операторы: • WHERE (где) — указывает записи, которые должны войти в результатную таблицу (фильтр записей); • GROUP BY (группировать по) — группирует записи по значениям определенных столбцов; • HAVING (имеющие, при условии) — указывает группы записей, которые должны войти в результатную таблицу (фильтр групп); • ORDER BY (сортировать по) — сортирует (упорядочивает) записи. SELECT список. Столбцов FROM имя. Таблицы WHERE условие. Поиска GROUP BY столбец. Группировки HAVING условие. Поиска ORDER BY условие. Сортировки;

Порядок перечисления операторов в SQL-выражении не совпадает с порядком их выполнения. Операторы SQLвыражения выполняются в следующем порядке, передавая другу результат в виде таблицы: 1. 2. 3. from — выбирает таблицу из базы данных; если указано несколько таблиц, то выполняется их декартово произведение и результирующая таблица передается для обработки следующему оператору. where — из таблицы выбираются записи, отвечающие условию поиска, и отбрасываются все остальные. group by — создаются группы записей, отобранных с помощью оператора where (если он присутствует в SQL выражении); каждая группа соответствует какому нибудь значению столбца группирования. Столбец группирования может быть любым столбцом таблицы, заданной в операторе from, а не только тем, который указан в select.

4. having — обрабатывает каждую из созданных групп записей, оставляя только те из них, которые удовлетворяют условию поиска; этот оператор используется как правило вместе с оператором GROUP BY. 5. select — выбирает из таблицы, полученной в результате применения перечисленных операторов, только указанные столбцы. 6. order by — сортирует записи таблицы При этом в условии сортировки можно обращаться лишь к тем столбцам, которые указаны в операторе select.

Теоретико-множественные операции Декартово произведение наборов записей Этот запрос возвращает набор записей, полученный в результате декартового произведения наборов записей из таблиц Т 1, Т 2, . . . , Tn SELECT список. Столбцов FROM Tl, T 2, . . . , Тn; Декартово произведение таблицы самой на себя Необходимо применить различные псевдонимы для этой таблицы SELECT список. Столбцов FROM Mytab Tl, Mytab T 2; Допустим, что имеется таблица Рейсы (НАЧАЛЬНЫЙ_ПУНКТ, КОНЕЧНЫЙ_ПУНКТ), содержащая сведения о том, из каких пунктов и в какие можно попасть с помощью того или иного авиарейса.

Граф и таблица достижимости пунктов Следующий запрос возвращает таблицу, содержащую сведения о достижимости пунктов в точности через один транзитный пункт: SELECT Т 1. НАЧАЛЬНЫЙ_ПУНКТ, Т 2. КОНЕЧНЫЙ_ПУНКТ FROM Рейсы Т 1, Рейсы Т 2 WHERE Tl. КОНЕЧНЫЙ_ПУНКТ = Т 2. НАЧАЛЬНЫЙ_ПУНКТ;

Объединение наборов записей (UNION) Запрос1 UNION Запрос2; SELECT Имя, Адрес FROM Клиенты UNION SELECT Имя, Адрес FROM Контакты; Оператор union можно применять только к таблицам, удовлетворяющим условиям совместимости. SELECT Имя FROM Клиенты UNION SELECT CAST(Сумма_заказа AS CHAR(10)) FROM Клиенты; Когда требуется объединить записи двух таблиц, имеющих одноименные столбцы с совместимыми типами SELECT * FROM Таблица! UNION CORRESPONDING (список. Столбцов) SELECT * FROM Таблица 2;

Требуется получить сведения о том, в какие пункты можно попасть, сделав не более одной пересадки (т. е. без пересадок или с одной пересадкой) SELECT НАЧАЛЬНЫЙ_ПУНКТ, КОНЕЧНЫЙ_ПУHKT FROM Рейсы UNION SELECT Т 1. НАЧАЛЬНЫЙ_ПУНКТ, Т 2. КОНЕЧНЫЙ_ПУНКТ FROM Рейсы Т 1, Рейсы Т 2 WHERE Т 1. КОНЕЧНЫЙ_ПУНКТ = Т 2. НАЧАЛЬНЫЙ_ПУНКТ;

Нам нужны сведения о достижимости только из одного пункта, например, А SELECT НАЧАЛЬНЫЙ_ПУНКТ, КОНЕЧНЫЙ_ПУНКТ FROM Рейсы UNION SELECT Т 1. НАЧАЛЬНЫЙ_ПУНКТ, Т 2. КОНЕЧНЫЙ_ПУНКТ FROM Рейсы Т 1, Рейсы Т 2 WHERE Т 1. КОНЕЧНЫЙ_ПУНКТ = Т 2. НАЧАЛЬНЫЙ_ПУНКТ AND Т 1. НАЧАЛЬНЫЙ_ПУНКТ = 'А'; Вместе с тем, допустим и такой эквивалентный запрос: SELECT * FROM ( SELECT НАЧАЛЬНЫЙ_ПУНКТ, КОНЕЧНЫЙ_ПУНКТ FROM Рейсы UNION SELECT Т 1. НАЧАЛЬНЫЙ_ПУНКТ, Т 2. КОНЕЧНЫЙ_ПУНКТ FROM Рейсы Т 1, Рейсы Т 2 WHERE Т 1. КОНЕЧНЫЙ_ПУНКТ = Т 2. НАЧАЛЬНЫЙ_ПУНКТ) Т WHERE Т. НАЧАЛЬНЫЙ_ПУНКТ = 'А';

Пересечение наборов записей (INTERSECT) Запрос1 INTERSECT Запрос2; SELECT * FROM Система 1 INTERSECT SELECT * FROM Система 2; SELECT * FROM Система 1 INTERSECT CORRESPONDING (ID_компонента, Тип_компонента) SELECT * FROM Система 2;

Вычитание наборов записей (EXCEPT) Запрос1 ЕХЕРТ Запрос2; SELECT * FROM Клиенты EXCEPT CORRESPONDING (Имя, Адрес) SELECT * FROM Контакты; Операции соединения Для этого используется оператор JOIN (соединить). Существуют несколько разновидностей соединения, которым соответствуют определенные ключевые слова, добавляемые к слову JOIN. Так, например, декартово произведение является операцией перекрестного соединения. В SQL выражении для обозначения этой операции используется оператор CROSS JOIN. Впрочем, декартово произведение можно получить и без использования этих ключевых слов. В основе любого соединения наборов записей лежит операция их декартового произведения.

Естественное соединение (NATURAL JOIN) Продажи (ID_товара, Количество, ID_клиента); Клиенты (ID_клиента, Имя, Телефон). Общим столбцом для этих таблиц является ID_клиента. Декартово произведение этих таблиц получается с помощью следующего запроса: SELECT * FROM Продажи, Клиенты;

Таблицы Продажи, Клиенты и их декартово произведение ПРОДАЖИ КЛИЕНТЫ ДЕКАРТОВО ПРОИЗВЕДЕНИЕ

Нас будут интересовать только те записи, в которых идентичные столбцы имеют одинаковые значения, т. е. Продажи. ID_клиента = Клиенты. ID_клиента. SELECT Продажи. *, Клиенты. Имя, Клиенты. Телефон FROM Продажи, Клиенты WHERE Продажи. ID_клиента = Клиенты. ID_клиента; Данный запрос можно переписать, используя псевдонимы: SELECT Т 1. *, Т 2. Имя, Т 2. Телефон FROM Продажи Т 1, Клиенты Т 2 WHERE Т 1. ID_клиента = Т 2. ID_клиента;

Естественное соединение таблиц Продажи и Клиенты

Эквивалентный запрос с оператором natural join выглядит следующим образом: SELECT Т 1. *, Т 2. Имя, Т 2. Телефон FROM Продажи Tl NATURAL JOIN Клиенты Т 2; В Microsoft Access оператор NATURAL JOIN не поддерживается. Вместо него используется INNER JOIN (внутреннее соединение) и ключевое слово ON (при), за которым следует условие отбора записей.

Условное соединение (JOIN. . . ON) Продажи (ID_товара, Количество, ID_клиента); Клиенты (ID_клиента, Имя, Телефон). Тогда эти таблицы можно соединить, используя, например, сле дующий запрос SELECT * FROM Продажи JOIN Клиенты ON (Продажи. ID_клиента = Клиенты. ID_клиента) AND (Продажи. Количество > 50); В Microsoft Access используется оператор inner join. . . on.

Соединение по именам столбцов (JOIN. . . USING) Допустим, имеются две таблицы с одинаковыми структурами Болты (Тип, Количество, Материал); Гайки (Тип, Количество, Материал). Это соединение может быть представлено так: SELECT * FROM Болты JOIN Гайки USING (Тип, Количество); Данный запрос можно сформулировать иначе: SELECT * FROM Болты, Гайки WHERE (Болты. Тип = Гайки. Тип) AND (Болты. Количество = Гайки. Количество);

Таблицы Болты и Гайки и результат их соединения по столбцам Тип и Количество БОЛТЫ КОМПЛЕКТЫ: запрос на выборку ГАЙКИ

В Microsoft Access оператор join. . . using не поддерживается. Вместо него можно использовать inner join. . . on. Рассмотренный запрос в Access можно сформулировать так: SELECT * FROM Болты INNER JOIN Гайки ON (Болты. Тип = Гайки. Тип) AND (Болты. Количество = Гайки. Количество);

Внешние соединения Левое соединение (LEFT OUTER JOIN) Допустим, в базе данных есть две таблицы: Предложение (ID_товара, Цена, Описание); Склад (ID_товара, Количество). Список всех продаваемых товаров с указанием их количества на складе : SELECT Предложение. IDтовара, Предложение. Описание, Склад. Количество FROM Предложение LEFT OUTER JOIN Склад ON Предложение. ID_товара = Склад. ID_товара; В Microsoft Access ключевое слово outer не допускается Поскольку левого внутреннего объединения не существует, то можно просто писать left join.

Таблицы Предложение, Склад и результат их левого внешнего соединения ПРЕДЛОЖЕНИЕ СКЛАД НАЛИЧИЕ ПРЕДЛАГАЕМ. ТОВАРОВ

Правое соединение {RIGHT OUTER JOIN) SELECT Предложение. ID_товара, Предложение. Описание, Склад. Количество FROM Склад RIGHT OUTER JOIN Предложение ON Предложение. ID_товapa = Склад. ID_товара; В Microsoft Access ключевое слово outer не допускается. Поскольку правого внутреннего объединения не существует, то можно просто писать right join