Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

SQL

Глава 8. Запрашивание нескольких таблиц так же, как одной

До этого каждый запрос, который мы рассматривали, основывался на одиночной таблице. В этой главе вы узнаете, как сделать запрос любого числа таблиц с помощью одной команды. Это чрезвычайно мощное средство, потому что оно не только объединяет вывод из многочисленных таблиц, но и определяет связи между ними. Вы обучитесь различным формам, использующим эти связи, а также их настройке и использованию, чтобы удовлетворять возможным специальным требованиям.

Объединение таблиц

Одна из наиболее важных особенностей запросов 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.

               
   ===============  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 Объединение продавцов с их заказчикам

Это пример объединения, в котором столбцы используются для определения предиката запроса, и в этом случае snum-столбцы из обеих таблиц удалены из вывода. И это прекрасно. Вывод показывает, какие заказчики каким продавцом обслуживаются; значения поля snum, которые устанавливают связь, отсутствуют. Однако, если вы введёте их в вывод, то вы должны или удостовериться, что вывод понятен сам по себе, или должны обеспечить комментарий данных при выводе.

Объединение таблиц по равенству значений в столбцах и другие виды объединений

Объединения, которые используют предикаты, основанные на равенствах, называются объединениями по равенству. Все наши примеры в этой главе до настоящего времени относились именно к этой категории, потому что все условия в предложениях 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.)