SELECT SUM(Quantity) AS Qty_Canada FROM Sumproduct WHERE City IN (SELECT City FROM Sellers WHERE Country = 'Canada')
Видим, что мы получили аналогичные данные, как и с помощью двух отдельных запросов. Таким же образом, мы можем увеличивать глубину вложенности запросов, вкладывая подзапросы сколько угодно раз.
Использование подзапросов в качестве расчетных полей
Мы также можем использовать подзапросы в качестве расчетных полей. Отразим, например, количество реализованной продукции по каждому продавцу с помощью следующего запроса:
SELECT Seller_name, (SELECT SUM(Quantity) FROM Sumproduct WHERE Sellers. City = Sumproduct. City) AS Qty FROM Sellers
Первый оператор SELECT отражает два столбца - Seller_name и Qty. Поле Qty является расчетным, оно формируется в результате выполнения подзапроса, который взят в круглые скобки. Этот подзапрос выполняется по одному разу для каждой записи в поле Seller_name и в общем будет выполнен четыре раза, поскольку выбрано имена четырех продавцов.
Также в подзапросе, предложение WHERE выполняет функцию объединения, поскольку с помощью WHERE мы соединили две таблицы по полю City, использовав полные названия столбцов (Таблиця. Поле).
Объединение таблиц (INNER JOIN)
Наиболее мощной особенностью языка SQL есть возможность сочетать различные таблицы в оперативной памяти СУБД при выполнении запросов. Объединение очень часто используются для анализа данных. Как правило, данные находятся в разных таблицах, что позволяет их более эффективно хранить (поскольку информация НЕ дублируется), упрощает обработку данных и позволяет масштабировать базу данных (возможно добавлять новые таблицы с дополнительной информацией). Таблицы баз данных, которые используются в СУБД Access являются реляционными таблицами, т.е. все таблицы можно связать между собой по общим полям.
Создание объединения таблиц
Объединение таблиц очень простая процедура. Нужно указать все таблицы, которые будут включены в объединение и «объяснить» СУБД, как они будут связаны между собой. Объединение делается с помощью слова WHERE, например:
SELECT DISTINCT Seller_name, Product FROM Sellers, Sumproduct WHERE Sellers. City = Sumproduct. City
Соединив две таблицы, мы смогли увидеть какие товары реализует каждый продавец. Рассмотрим код запроса подробнее, поскольку он немного отличается от обычного запроса. Оператор SELECT начинается с указанием столбцов, которые мы хотим вывести, однако эти поля находятся в разных таблицах, предложение FROM содержит две таблицы, которые мы хотим объединить в операторе SELECT, таблицы объединяются с помощью слова WHERE, указывающее столбцы для объединения. Обязательно нужно указывать полное название поля (Таблиця. Поле), поскольку поле City есть в обоих таблицах.
Внутреннее объединение
В предыдущем примере для объединения таблиц мы использовали слово WHERE, которое осуществляет проверку на основе эквивалентности двух таблиц. Объединение такого типа называется также «внутренним объединением». Существует также и другой способ объединения таблиц, который явно указывает на тип объединения. Рассмотрим следующий пример:
SELECT DISTINCT Seller_name, Product FROM Sellers INNER JOIN Sumproduct ON Sellers. City = Sumproduct. City
В этом запросе вместо WHERE мы использовали конструкцию INNER JOIN… ON…, которая дала аналогичный результат. Несмотря на то, что объединение с предложением WHERE короче, все же лучше использовать INNER JOIN, поскольку она является более гибкой, о чем будет подробнее рассказано в следующих разделах.
Расширенное объединение таблиц (OUTER JOIN)
В предыдущем разделе мы рассмотрели самые простые способы объединения таблиц - с помощью предложений WHERE и INNER JOIN. Эти объединения называются внутренними объединениями или объединениями по эквивалентности. Однако SQL имеет в своем арсенале гораздо больше возможностей объединить таблицы, а именно существуют также и другие виды объединений: внешние объединения, природные объединения и самообъединения. Но для начала рассмотрим, каким образом мы можем присваивать таблицам псевдонимы, поскольку в дальнейшем, мы будем вынуждены использовать полные названия полей (Таблиця. Поле), которыми без сокращений будет очень трудно оперировать из-за их большой длины.
Использование псевдонимов таблиц
В предыдущем разделе мы узнали, как можно использовать псевдонимы для ссылки на определенные поля таблицы или на расчетные поля. SQL так же дает нам возможность использовать псевдонимы вместо имен таблиц. Это дает нам такие преимущества, как более короткий синтаксис SQL и позволяет много раз использовать одну и ту же таблицу в операторе SELECT.
SELECT Seller_name, SUM(Amount) AS Sum1
FROM Sellers AS S, Sumproduct AS SP
WHERE S. City = SP. City
GROUP BY Seller_name
Мы отобразили общую сумму реализованного товара по каждому продавцу. В нашем SQL запросе мы использовали такие псевдонимы: для расчетного поля SUM (Amount) псевдоним Sum1 для таблицы Sellers псевдоним S и для Sumproduct псевдоним SP. Заметим, что псевдонимы таблиц могут быть применены и в других предложениях, как ORDER BY, GROUP BY и других.
Самообъединения
Рассмотрим пример. Предположим, нам нужно узнать адрес продавцов, торгующих в той же стране, что и JohnSmith. Для этого создадим такой запрос:
SELECT City, Country, Seller_name
FROM Sellers
WHERE Country = (SELECT Country FROM Sellers WHERE Seller_name = 'John Smith')
Также, эту задачу мы можем решить и через самообъединения, прописав следующий код:
SELECT S1. Address, S1. City, S1. Country, S1. Seller_name
FROM Sellers AS S1, Sellers AS S2
WHERE S1. Country = S2. Country AND S2. Seller_name = 'John Smith'
Для решения этой задачи использовались псевдонимы. Первый раз для таблицы Sellers присвоили псевдоним S1, второй раз - псевдоним S2. После этого эти псевдонимы можно применять в качестве имен таблиц. В операторе WHERE мы с названием каждого поля добавляем префикс S1, для того, чтобы СУБД понимала поля которой таблицы нужно выводить (поскольку мы из одной таблицы сделали две виртуальные). Предложение WHERE сначала объединяет таблицы, а затем фильтрует данные второй таблицы по полю Seller_name, чтобы вернуть только необходимые значения.
Самообъединения часто используют для замены подзапросов, которые выбирают данные из той же таблицы, что и внешний оператор SELECT. Хотя конечный результат получается тем самым, многие СУБД обрабатывают объединения гораздо быстрее подзапросов. Стоит поэкспериментировать, чтобы определить, какой запрос работает быстрее.
Естественное объединение
Естественное объединение - это объединение, в котором вы выбираете только те столбцы, которые не повторяются. Обычно это делается с помощью записи (SELECT *) для одной таблицы и указанием перечня полей - для остальных таблиц. Например:
SELECT SP.*, S. Country
FROM Sumproduct AS SP, Sellers AS S
WHERE SP. City = S. City
В этом примере метасимвол (*) используется только для первой таблицы. Все остальные столбцы указаны явно, поэтому дубликаты столбцов не выбираются.
Внешнее объединение (OUTER JOIN)
Обычно при объединении связывают строки одной таблицы с соответствующими строками другой, однако в некоторых случаях может потребоваться включать в результат строки, не имеющие связанных строк в другой таблице (т.е. выбираются совершенно все строки из одной таблицы и добавляются только связанные строки из другой). Объединение такого типа называется внешним. Для этого используются ключевые слова OUTER JOIN… ON… с приставкой LEFT или RIGHT. Рассмотрим пример, предварительно добавив в таблицу Sellers нового продавца - SemuelPiter, который не имеет продаж:
SELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers LEFT OUTER JOIN Sumproduct ON Sellers. City=Sumproduct. City
GROUP BY Seller_name
Данным запросом мы вытащили перечень всех продавцов в базе и подсчитали для них общее количество проданного товара за все месяцы. Видим что по новому продавцу SemuelPiter отсутствуют продажи. Если бы мы использовали внутреннее объединение, то нового продавца мы бы не увидели, поскольку он не имеет записей в таблице Sumproduct. Мы можем изменять направление объединения не только прописывая LEFT или RIGHT, но и просто меняя порядок таблиц (т.е. две записи будут давать одинаковый результат: Sellers LEFT OUTER JOIN Sumproduct та Sumproduct RIGHT OUTER JOIN Sellers).
Также некоторые СУБД позволяют осуществлять внешнее объединение по упрощенной записи, используя знаки * = и = *, что соответствует LEFT OUTER JOIN и RIGHT OUTER JOIN соответственно. Таким образом предыдущий запрос можно было бы переписать так:
SELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers, Sumproduct
WHERE Sellers. City *= Sumproduct. City
К сожалению Access не поддерживает сокращенную запись для внешнего объединения.
Полное внешнее объединение (FULL OUTER JOIN)
Также существует и другой тип внешнего объединения - полное внешнее объединение, которое отражает все строки из обеих таблиц и связывает только те, которые могут быть связаны. Синтаксис полного внешнего объединения следующий:
SELECT Seller_name, Product
FROM Sellers FULL OUTER JOIN Sumproduct ON Sellers. City=Sumproduct. City
Опять же, полное внешнее объединение не поддерживают такие СУБД: Access, MySQL, SQL Server и Sybase. Как обойти эту несправедливость, мы рассмотрим в следующем разделе.
SQL-Урок 12. Комбинированные запросы (UNION)
В большинстве SQL-запросов используется один оператор, с помощью которого возвращаются данные из одной или нескольких таблиц. SQL также позволяет выполнять одновременно несколько отдельных запросов и отображать результат в виде единого набора данных. Такие комбинированные запросы обычно называют сочетаниями или сложными запросами.
Использование оператора UNION
Запросы в языке SQL комбинируются с помощью оператора UNION. Для этого необходимо указать каждый запрос SELECT и разместить между ними ключевое слово UNION. Ограничений по количеству использованного оператора UNION в одном общем запросе нет. В предыдущем разделе мы отмечали, что Access не имеет возможности создавать полное внешнее объединение, теперь мы посмотрим, как можно этого достичь через оператор UNION.
SELECT *
FROM Sumproduct LEFT JOIN Sellers ON Sumproduct. City = Sellers. City
UNION
SELECT *
FROM Sumproduct RIGHT JOIN Sellers ON Sumproduct. City = Sellers. City
Видим, что запрос отобразил как все колонки из первой таблицы - так и с другой, независимо от того, все ли записи имеют соответствия в другой таблице.
Также стоит отметить, что во многих случаях вместо UNION мы можем использовать предложение WHERE со многими условиями, и получать аналогичный результат. Однако из-за UNION записи выглядят более лаконичными и понятными. Также необходимо соблюдать определенные правила при написании комбинированных запросов:
· запрос UNION должен включать два и более операторов SELECT, отделенных между собой ключевым словом UNION (т.е. если в запросе используется четыре оператора SELECT, то должно быть три ключевых слова UNION)
· каждый запрос в операторе UNION должен иметь одни и те же столбцы, выражения или статистические функции, которые, к тому же, должны быть перечислены в одинаковом порядке
· типы данных столбцов должны быть совместимыми. Они не обязательно должны быть одного типа, однако обязаны иметь подобный тип, чтобы СУБД могла их однозначно преобразовать (например, это могут быть различные числовые типы данных или различные типы даты).
Включение или выключение повторяющихся строк
Запрос с UNION автоматически удаляет все повторяющиеся строки из набора результатов запроса (то есть, ведет себя как предложения WHERE с несколькими условиями в одном операторе SELECT). Такое поведение оператора UNION по умолчанию, но при желании мы можем изменить это. Для этого нам следует использовать оператор UNION ALL вместо UNION.
Сортировка результатов комбинированных запросов
Результаты выполнения оператора SELECT сортируются с помощью предложения ORDER BY. При комбинировании запросов с помощью UNION только одно предложение ORDER BY может быть использовано, и оно должно быть проставлено в последнем операторе SELECT. Действительно, на практике нет особого смысла часть результатов сортировать в одном порядке, а другую часть - в другом. Поэтому несколько предложений ORDER BY применять не разрешается.
Добавление данных (INSERT INTO)
В предыдущих разделах мы рассматривали работу по получению данных с заранее созданных таблиц. Теперь пора разобрать, каким же образом мы можем создавать / удалять таблицы, добавлять новые записи и удалять старые. Для этих целей в SQL существуют такие операторы, как: CREATE - создает таблицу, ALTER - изменяет структуру таблицы, DROP - удаляет таблицу или поле, INSERT - добавляет данные в таблицу. Начнем знакомство с данной группой операторов из оператора INSERT.
Добавление целых строк
Как видно из названия, оператор INSERT используется для вставки (добавления) строк в таблицу базы данных. Добавление можно осуществить несколькими способами:
· - добавить одну полную строку
· - добавить часть строки
· - добавить результаты запроса.
Итак, чтобы добавить новую строку в таблицу, нам необходимо указать название таблицы, перечислить названия колонок и указать значение для каждой колонки с помощью конструкции INSERT INTO название_таблицы (поле1, поле2…) VALUES (значение1, значение2…). Рассмотримнапримере.
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES ('6', '1st Street', 'Los Angeles', 'Harry Monroe', 'USA')
Также можно изменять порядок указания названий колонок, однако одновременно нужно менять и порядок значений в параметре VALUES.