Выборка из нескольких таблиц

 

Для связи таблиц можно использовать то же ключевое слово WHERE, как и для условий отбора. При выборке из нескольких таблиц рекомендуется всегда использовать псевдонимы таблиц. Дело в том, что если в разных таблицах имеются одинаковые поля, то всегда нужно уточнять, к какой таблице они относятся, т.е., использовать синтаксис имя_таблицы.имя_поля. А так как имена таблиц обычно длинные, удобно заменять их псевдонимами.

Напечатать список договоров с указанием названия предприятия.

SELECT firm_name, contract_num, contract_date

FROM k_firm f, k_contract c

WHERE f.firm_num=c.k_firm_firm_num

 

 

То же самое можно получить, если использовать синтаксис JOIN...ON. Это так называемое внутреннее (INNER) соединение. Строки соединяются, если совпадают значения полей в условии ON.

 

SELECT firm_name, contract_num, contract_date

FROM k_firm f JOIN k_contract c ON f.firm_num=c.k_firm_firm_num

 

Для соединения трех и более таблиц синтаксис в этом формате следующий:

Напечатать список сотрудников, номера и даты договоров, которые они заключили, с указанием названия предприятия.

SELECT staff_name, contract_num, contract_date, firm_name

FROM k_firm f JOIN k_contract c ON f.firm_num=c.k_firm_firm_num

         JOIN k_staff s ON s.staff_num=c.k_staff_staff_num

 

Кроме внутреннего, бывают еще левое (LEFT), правое (RIGHT) и полное (FULL) соединения.

Рассмотрим, например, левое соединение. В результат попадут строки, в которых совпадают значения полей в условии ON, и те строки из левой таблицы, для которых не нашлось соответствующих строк в правой таблице. Поля из правой таблицы будут заполнены значениями NULL.

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

 

SELECT firm_name, contract_num, contract_date

FROM k_firm f LEFT JOIN k_contract c ON    

         f.firm_num=c.k_firm_firm_num

 

 

А что будет в том случае, если условие связи вообще не указывать? Получится так называемое декартово произведение таблиц, в котором каждая строка первой таблицы будет сцеплена с каждой строкой второй таблицы. Результат получается обычно очень большим и не имеющим смысла.

SELECT firm_name, contract_num, contract_date

FROM k_firm f, k_contract c

# Предыдущий запрос вернул 35 строк,

# т.е. 5 предприятий умножить на 7 договоров.

 

Разумеется, в одном и том же запросе можно связывать не только две, а три и более таблицы, использовать в этих запросах подзапросы, группировки и т.п. Например, запрос к 4 таблицам:

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

SELECT firm_name, payment_date, payment_sum

 FROM k_firm f, k_contract c, k_bill b, k_payment p

 WHERE f.firm_num=c.k_firm_firm_num AND 

  c.contract_num=b.k_contract_contract_num AND  

  b.bill_num=p.k_bill_bill_num

 

Объединение запросов

 

Для объединения результатов двух и более запросов нужно использовать ключевое слово UNION. Объединяемые запросы должны иметь одинаковое количество и тип полей. Параметр ORDER BY, если он нужен, следует указывать только в последнем запросе.

 

Получить список договоров и общую сумму счетов по каждому договору, а также строку с итоговой суммой:

 

SELECT CONCAT('Договор № ',

          CONVERT(k_contract_contract_num, CHAR),

          ' на сумму ') AS " Номер",

  SUM(bill_sum) AS " Сумма" FROM k_bill

GROUP BY k_contract_contract_num

UNION

SELECT 'ИТОГО: ', SUM(bill_sum) FROM k_bill ORDER BY 1

 

И еще несколько примеров

 

Получить прайс-лист с суммой заказов по каждому товару. Обратите внимание, что название и цена товара могут использоваться в списке полей для выбора, поскольку они функционально (однозначно) зависят от номера товара, по которому проводится группировка.

 

SELECT pr.price_name, pr.price_sum,   

  SUM(prot.kolvo*prot.price_sum)

FROM k_price pr, k_protokol prot

WHERE pr.price_num=prot.k_price_price_num

GROUP BY pr.price_num ORDER BY 1

 

 

Полностью оплаченные счета, т.е., счета, сумма платежей по которым больше или равна сумме счета. Обратите внимание на применение подзапроса.

SELECT b.bill_num AS " Номер счета",

b.bill_date AS " Дата счета",

b.bill_sum AS " Сумма счета",

SUM(p.payment_sum) AS " Сумма оплаты"

FROM k_bill b, k_payment p

WHERE b.bill_num=p.k_bill_bill_num AND

b.bill_sum< =

(SELECT SUM(payment_sum) FROM k_payment p2

WHERE b.bill_num=p2.k_bill_bill_num)

GROUP BY b.bill_num

 

Полностью неоплаченные счета, по которым вообще нет платежей.

SELECT b.bill_num AS " Номер счета",

b.bill_date AS " Дата счета",

b.bill_sum AS " Сумма счета",

0 AS " Сумма оплаты"

FROM k_bill b

WHERE b.bill_num NOT IN (SELECT k_bill_bill_num FROM k_payment)

Частично оплаченные счета. Обратите внимание, что в этом примере в параметре FROM вместо второй таблицы используется вложенный SELECT

SELECT b.bill_num AS " Номер счета",

b.bill_date AS " Дата счета",

b.bill_sum AS " Сумма счета",

p.pay_sum AS " Сумма оплаты"

FROM k_bill b,

(SELECT k_bill_bill_num, SUM(payment_sum) as pay_sum 

FROM k_payment

GROUP BY k_bill_bill_num) p

WHERE b.bill_sum > p.pay_sum AND b.bill_num=p.k_bill_bill_num

 

Задание. Напишите несколько (не менее 10) интересных запросов к вашей базе данных. Используйте вложенные подзапросы, группировки, итоговые значения, выборки из нескольких таблиц. Если ваш запрос требует ввода параметра, замените его пока на константу, запросы с параметрами можно будет в дальнейшем реализовать с помощью хранимых процедур.

Дополнительная информация. Подробнее о команде SELECT, используемых в ней операциях и функциях  можно прочитать в [1, Глава 2, параграф 2.5, Глава 3 ], [3, Главы 5, 6, 7 ].

 

 

 

⇐ Предыдущая3456789101112Следующая ⇒

Поделиться: