Понимание SQL (775442), страница 10
Текст из файла (страница 10)
Одна из наиболее важных особенностей запросов SQL - это их способность определять связи между многочисленными таблицами и выводить информацию из них в терминах этих связей, всю внутри одной команды.
Этот вид операции называется - объединением, которое является одним из видов операций в реляционных базах данных. Как установлено в Главе 1, главное в реляционном подходе это связи которые можно создавать между позициями данных в таблицах. Используя объединения, мы непосредственно связываем информацию с любым номером таблицы, и таким образом способны создавать связи между сравнимыми фрагментами данных.
При объединении, таблицы представленные списком в предложении FROM запроса, отделяются запятыми. Предикат запроса может ссылаться к любому столбцу любой связанной таблицы и, следовательно, может использоваться для связи между ими. Обычно, предикат сравнивает значения в столбцах различных таблиц чтобы определить, удовлетворяет ли WHERE установленному условию.
ИМЕНА ТАБЛИЦ И СТОЛБЦОВ
Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого точкой и затем именем столбца. Имеются несколько примеров имен:
Salespeople.snum
Salespeople.city
Orders.odate
До этого, вы могли опускать имена таблиц потому что вы запрашивали только одну таблицу одновременно, а SQL достаточно интеллектуален чтобы присвоить соответствующий префикс, имени таблицы. Даже когда вы делаете запрос многочисленных таблиц, вы еще можете опускать имена таблиц, если все ее столбцы имеют различные имена. Но это не всегда так бывает. Например, мы имеем две типовые таблицы со столбцами называемыми city.
Если мы должны связать эти столбцы(кратковременно), мы будем должны указать их с именами Salespeople.city или Customers.city, чтобы SQL мог их различать.
СОЗДАНИЕ ОБЪЕДИНЕНИЯ
Предположим что вы хотите поставить в соответствии вашему продавцу ваших заказчиков в городе в котором они живут, поэтому вы увидите все комбинации продавцов и заказчиков для этого города. Вы будете должны брать каждого продавца и искать в таблице Заказчиков всех заказчиков того же самого города. Вы могли бы сделать это, введя следующую команду (вывод показывается в Рисунке 8.1 ):
SELECT Customers.cname, Salespeople.sname, Salespeople.city
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city;
=============== SQL Execution Log ============
| SELECT Customers.cname, Salespeople.sname, |
| Salespeople.city |
| FROM Salespeople, Customers |
| WHERE Salespeople.city = Customers.city |
| ============================================= |
| cname cname city |
| ------- -------- ---- |
| Hoffman Peel London |
| Hoffman Peel London |
| Liu Serres San Jose |
| Cisneros Serres San Jose |
| Hoffman Motika London |
| Clemens Motika London |
=============================================
Рисунок 8.1: Объединение двух таблиц
Так как это поле city имеется и в таблице Продавцов и таблице Заказчиков, имена таблиц должны использоваться как префиксы. Хотя это необходимо только когда два или более полей имеют одно и то же имя, в любом случае это хорошая идея включать имя таблицы в объединение для лучшего понимания и непротиворечивости. Несмотря на это, мы будем, в наших примерах далее, использовать имена таблицы только когда необходимо, так что будет ясно, когда они необходимы а когда нет.
Что SQL в основном делает в объединении - так это исследует каждую комбинацию строк двух или более возможных таблиц, и проверяет эти комбинации по их предикатам. В предыдущем примере, требовалась строка продавца Peel из таблицы Продавцов и объединение ее с каждой строкой таблицы Пользователей, по одной в каждый момент времени.
Если комбинация производит значение которое делает предикат верным, и если поле city из строк таблиц Заказчика равно London, то Peel - это то запрашиваемое значение которое комбинация выберет для вывода. То же самое будет затем выполнено для каждого продавца в таблице Продавцов (у некоторых из которых не было никаких заказчиков в этих городах).
ОБЪЕДИНЕНИЕ ТАБЛИЦ ЧЕРЕЗ СПРАВОЧНУЮ ЦЕЛОСТНОСТЬ
Эта особенность часто используется просто для эксплуатации связей встроенных в базу данных. В предыдущем примере, мы установили связь между двумя таблицами в объединении. Это прекрасно.
Но эти таблицы, уже были соединены через snum поле. Эта связь называется состоянием справочной целостности, как мы уже говорили в Главе 1. Используя объединение можно извлекать данные в терминах этой связи. Например, чтобы показать имена всех заказчиков соответствующих продавцам которые их обслуживают, мы будем использовать такой запрос:
SELECT Customers.cname, Salespeople.sname
FROM Customers, Salespeople
WHERE Salespeople.snum = Customers.snum;
Вывод этого запроса показывается в Рисунке 8.2.
Это - пример объединения, в котором столбцы используются для определения предиката запроса, и в этом случае, snum столбцы из обеих таблиц, удалены из вывода. И это прекрасно.
Вывод показывает какие заказчики каким продавцом обслуживаются; значения поля snum которые устанавливают связь - отсутствуют. Однако если вы введете их в вывод, то вы должны или удостовериться что вывод понятен сам по себе или обеспечить комментарий к данным при выводе.
=============== SQL Execution Log ============
| SELECT Customers.cname, Salespeople.sname, |
| FROM Salespeople, Customers |
| WHERE Salespeople.snum = Customers.snum |
| ============================================= |
| cname sname |
| ------- -------- |
| Hoffman Peel |
| Giovanni Axelrod |
| Liu Serres |
| Grass Serres |
| Clemens Peel |
| Cisneros Rifkin |
| Pereira Motika |
=============================================
Рисунок 8.2: Объединение продавцов с их заказчикам
ОБЪЕДИНЕНИЯ ТАБЛИЦ ПО РАВЕНСТВУ ЗНАЧЕНИЙ В СТОЛБЦАХ И ДРУГИЕ ВИДЫ ОБЪЕДИНЕНИЙ
Объединения которые используют предикаты основанные на равенствах называются - объединениями по равенству. Все наши примеры в этой главе до настоящего времени, относились именно к этой категории, потому что все условия в предложениях WHERE базировались на математических выражениях использующих знак равно (=). Строки 'city = 'London' и 'Salespeople.snum = Orders.snum' - примеры таких типов равенств найденных в предикатах.
Объединения по равенству - это вероятно наиболее общий вид объединения, но имеются и другие. Вы можете, фактически, использовать любой из реляционных операторов в объединении. Здесь показан пример другого вида объединения (вывод показывается в Рисунке 8.3 ):
SELECT sname, cname
FROM Salespeople, Customers
WHERE sname < cname
AND rating < 200;
=============== SQL Execution Log ============
| SELECT sname, cname |
| FROM Salespeople, Customers |
| WHERE sname < cname |
| AND rating < 200; |
| ============================================= |
| sname cname |
| -------- ------- |
| Peel Pereira |
| Motika Pereira |
| Axelrod Hoffman |
| Axelrod Clemens |
| Axelrod Pereira |
| |
=============================================
Рисунок 8.3: Объединение основанное на неравенстве
Эта команда не часто бывает полезна. Она воспроизводит все комбинации имени продавца и имени заказчика так, что первый предшествует последнему в алфавитном порядке, а последний имеет оценку меньше чем 200.
Обычно, вы не создаете сложных связей подобно этой, и, по этой причине, вы вероятно будете строить наиболее общие объединения по равенству, но вы должны хорошо знать и другие возможности.
ОБЪЕДИНЕНИЕ БОЛЕЕ ДВУХ ТАБЛИЦ
Вы можете также создавать запросы объединяющие более двух таблиц. Предположим что мы хотим найти все порядки заказчиков не находящихся в тех городах где находятся их продавцы. Для этого необходимо связать все три наши типовые таблицы (вывод показывается в Рисунке 8.4):
SELECT onum, cname, Orders.cnum, Orders.snum
FROM Salespeople, Customers,Orders
WHERE Customers.city Salespeople.city
AND Orders.cnum = Customers.cnum
AND Orders.snum = Salespeople.snum;
=============== SQL Execution Log ==============
| |
| SELECT onum, cname, Orders.cnum, Orders.snum |
| FROM Salespeople, Customers, Orders |
| WHERE Customers.city Salespeople.city |
| AND Orders.cnum = Customers.cnum |
| AND Orders.snum = Salespeople.snum; |
| =============================================== |
| onum cname cnum snum |
| ------ ------- ----- ----- |
| 3001 Cisneros 2008 1007 |
| 3002 Pereira 2007 1004 |
| 3006 Cisneros 2008 1007 |
| 3009 Giovanni 2002 1003 |
| 3007 Grass 2004 1002 |
| 3010 Grass 2004 1002 |
===============================================
Рисунок 8. 4: Объединение трех таблиц
Хотя эта команда выглядит скорее как комплексная, вы можете следовать за логикой, просто проверяя - что заказчики не размещены в тех городах где размещены их продавцы (совпадение двух snum полей ), и что перечисленные порядки - выполнены с помощью этих заказчиков(совпадение порядков с полями cnum и snum в таблице Порядков).
РЕЗЮМЕ
Теперь вы больше не ограничиваетесь просмотром одной таблицы в каждый момент времени. Кроме того, вы можете делать сложные сравнения между любыми полями любого числа таблиц и использовать полученные результаты чтобы решать какую информацию вы бы хотели видеть. Фактически, эта методика настолько полезна для построения связей, что она часто используется для создания их внутри одиночной таблицы. Это будет правильным: вы сможете объединить таблицу с собой, а это очень удобная вещь.
Это будет темой Главы 9.
РАБОТА С SQL
1. Напишите запрос который бы вывел список номеров порядков сопровождающихся именем заказчика который создавал эти порядки.
2. Напишите запрос который бы выдавал имена продавца и заказчика для каждого порядка после номера порядков.
3. Напишите запрос который бы выводил всех заказчиков обслуживаемых продавцом с комиссионными выше 12% . Выведите имя заказчика, имя продавца, и ставку комиссионных продавца.
4. Напишите запрос который вычислил бы сумму комиссионных продавца для каждого порядка заказчика с оценкой выше 100.
(См. Приложение A для ответов. )
9. ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С СОБОЙ
В главе 8, мы показали вам как объединять две или более таблиц - вместе. Достаточно интересно то, что та же самая методика может использоваться чтобы объединять вместе две копии одиночной таблицы.
В этой главе, мы будем исследовать этот процесс. Как вы видите, объединение таблицы с самой собой, далеко не простая вещь, и может быть очень полезным способом определять определенные виды связей между пунктами данных в конкретной таблице.
КАК ДЕЛАТЬ ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С СОБОЙ ?
Для объединения таблицы с собой, вы можете сделать каждую строку таблицы, одновременно, и комбинацией ее с собой и комбинацией с каждой другой строкой таблицы. Вы затем оцениваете каждую комбинацию в терминах предиката, также как в объединениях мультитаблиц. Это позволит вам легко создавать определенные виды связей между различными позициями внутри одиночной таблицы - с помощью обнаружения пар строк со значением поля, например.
Вы можете изобразить объединение таблицы с собой, как объединение двух копий одной и той же таблицы. Таблица на самом деле не копируется, но SQL выполняет команду так, как если бы это было сделано. Другими словами, это объединение - такое же, как и любое другое объединение между двумя таблицами, за исключением того, что в данном случае обе таблицы идентичны.
ПСЕВДОНИМЫ
Синтаксис команды для объединения таблицы с собой, тот же что и для объединения многочисленных таблиц, в одном экземпляре. Когда вы объединяете таблицу с собой, все повторяемые имена столбца, заполняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, вы должны иметь два различных имени для этой таблицы.
Вы можете сделать это с помощью определения временных имен называемых - .переменными диапазона, переменными корреляции или просто - псевдонимами. Вы определяете их в предложении FROM запроса. Это очень просто: вы набираете имя таблицы, оставляете пробел, и затем набираете псевдоним для нее.
Имеется пример который находит все пары заказчиков имеющих один и тот же самый рейтинг (вывод показывается в Рисунке 9.1):
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating;
=============== SQL Execution Log ==============
| |
| Giovanni Giovanni 200 |
| Giovanni Liu 200 |
| Liu Giovanni 200 |
| Liu Liu 200 |
| Grass Grass 300 |
| Grass Cisneros 300 |
| Clemens Hoffman 100 |
| Clemens Clemens 100 |
| Clemens Pereira 100 |
| Cisneros Grass 300 |
| Cisneros Cisneros 300 |
| Pereira Hoffman 100 |
| Pereira Clemens 100 |
| Pereira Pereira 100 |
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
