Главная | Обратная связь | Поможем написать вашу работу!
МегаЛекции

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




 

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

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

SELECT firm_name, contract_num, contract_date

FROM k_firm f, k_contract c

WHERE f.firm_num=c.firm_num

Результат:

firm_name contract_num contract_date

-----------------------------------------------------------

Альфа 1 2011-11-01 00:00:00.000

Альфа 2 2011-10-01 00:00:00.000

Альфа 3 2011-09-01 00:00:00.000

Бета 4 2011-11-15 00:00:00.000

Бета 5 2011-08-01 00:00:00.000

Гамма 6 2011-07-15 00:00:00.000

Дельта 7 2011-11-12 00:00:00.000

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

SELECT firm_name, contract_num, contract_date

FROM k_firm f JOIN k_contract c ON f.firm_num=c.firm_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.firm_num

Результат:

firm_name contract_num contract_date

-----------------------------------------------------------Альфа 1 2011-11-01 00:00:00.000

Альфа 2 2011-10-01 00:00:00.000

Альфа 3 2011-09-01 00:00:00.000

Бета 4 2011-11-15 00:00:00.000

Бета 5 2011-08-01 00:00:00.000

Гамма 6 2011-07-15 00:00:00.000

Дельта 7 2011-11-12 00:00:00.000

Омега NULL NULL

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

SELECT firm_name, contract_num, contract_date

FROM k_firm f, k_contract c

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

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

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.firm_num AND

c.contract_num=b.contract_num AND b.bill_num=p.bill_num

Результат:

firm_name payment_date payment_sum

-----------------------------------------------------------Альфа 2011-12-01 00:00:00.000 1000.00

Альфа 2011-12-15 00:00:00.000 1000.00

Альфа 2012-01-13 00:00:00.000 1500.00

Альфа 2012-01-12 00:00:00.000 1000.00

Бета 2012-01-05 00:00:00.000 100.00

Бета 2011-12-25 00:00:00.000 1000.00

Альфа 2012-01-15 00:00:00.000 500.00

Бета 2012-01-12 00:00:00.000 900.00

 

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

 

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

 

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

SELECT 'Договор № '+CONVERT(CHAR(6),contract_num)+

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

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

GROUP BY contract_num

UNION

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

Результат:

Номер Сумма

------------------------- ------------

Договор № 1 на сумму 5000

Договор № 2 на сумму 8000

Договор № 3 на сумму 2500

Договор № 4 на сумму 1500

Договор № 5 на сумму 11200

ИТОГО: 28200

 

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

 

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

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.price_num

GROUP BY pr.price_num, pr.price_name, pr.price_sum

Результат:

price_name price_sum

-------------------------------------------------------

Материализация духов 1000.00 7000.00

Раздача слонов 100.00 3700.00

Слоновий бивень 3000.00 6000.00

Моржовый клык 1500.00 1500.00

Копыто Пегаса 5000.00 10000.00

 

Полностью оплаченные счета:

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.bill_num AND

b.bill_sum<=

(SELECT SUM(payment_sum) FROM k_payment p2

WHERE b.bill_num=p2.bill_num)

GROUP BY b.bill_num, b.bill_date, b.bill_sum

Результат:

Номер счета Дата счета Сумма счета Сумма оплаты

-----------------------------------------------------------

1 2011-11-12 00:00:00.000 1000 1000.00

3 2012-01-12 00:00:00.000 2000 2000.00

 

Полностью неоплаченные счета

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 bill_num FROM k_payment)

Результат:

Номер счета Дата счета Сумма счета Сумма оплаты

-----------------------------------------------------------

5 2012-01-12 00:00:00.000 2000 0

6 2012-01-12 00:00:00.000 2500 0

9 2012-01-12 00:00:00.000 10000 0

 

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

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

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

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

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

FROM k_bill b,

(SELECT bill_num, SUM(payment_sum) as pay_sum

FROM k_payment

GROUP BY bill_num) p

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

 

Результат:

Номер счета Дата счета Сумма счета Сумма оплаты

-----------------------------------------------------------

2 2011-12-12 00:00:00.000 2000 1000.00

4 2011-12-12 00:00:00.000 6000 1000.00

7 2011-12-12 00:00:00.000 1500 1000.00

8 2011-12-12 00:00:00.000 1200 1000.00

Вопрос

Какие функции есть в языке SQL server? Изучите данный вопрос самостоятельно по MSDN или другим источникам.

 

Задание для индивидуальной работы 5

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

 


DDL. Представления

 

Представления – это объекты базы данных, которые можно рассматривать как виртуальные таблицы. На самом деле хранится только формулировка команды SELECT, с помощью которой производится выборка данных из реальных таблиц.

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

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

Представление, как и запрос, может содержать информацию из разных таблиц.

Представления могут быть обновляемыми (т.е., предоставлять возможность не только чтения, но и изменения данных в исходных таблицах) и необновляемыми. Представление будет обновляемым только в том случае, если его структура такова, что SQL server может точно определить, в какие строки каких таблиц нужно поместить измененные данные. Необновляемыми будут, например, представления, содержащие итоговые данные и группировки.

Для создания представлений используется команда CREATE VIEW.

Краткий формат этой команды:

CREATE VIEW имя_представления AS

Команда_SELECT

Команду создания представления нужно либо выполнять отдельно от других команд, либо сразу после нее поставить команду GO, как в следующем примере.

Например, создадим представление, содержащее список договоров и их кураторов для отдела с номером 1. Будет ли это представление обновляемым?

CREATE VIEW k_contract1

AS

SELECT k_contract.contract_num, k_contract.contract_date,

k_contract.contract_type, k_contract.firm_num,

k_staff.staff_name

FROM k_contract INNER JOIN

k_staff ON k_contract.staff_num = k_staff.staff_num

WHERE dept_num = 1

 

GO

Для просмотра представления следует выполнить команду

SELECT * FROM k_contract1

Результат выполнения команды:

contract_num contract_date contract_type firm_num staff_name

-----------------------------------------------------------

1 2011-11-01 00:00:00.000 A 1 Иванов

3 2011-09-01 00:00:00.000 C 1 Иванов

6 2011-07-15 00:00:00.000 C 3 Иванов

7 2011-11-12 00:00:00.000 A 4 Иванов

 

Создадим вспомогательное представление для запросов о полностью оплаченных и частично оплаченных счетах (см. предыдущее занятие). Это представление для каждого счета содержит его номер и сумму оплаты.

CREATE VIEW k_pay_sum

AS

SELECT bill_num, SUM(payment_sum) AS pay_sum

FROM k_payment

GROUP BY bill_num

 

GO

 

Для просмотра представления следует выполнить команду

SELECT * FROM k_pay_sum.

Это представление не будет обновляемым.

 

Результат выполнения команды:

bill_num pay_sum

----------------------

1 1000.00

2 1000.00

3 2000.00

4 1000.00

7 1000.00

8 1000.00

 

Теперь с помощью данного представления можно переформулировать сам запрос, он станет проще:

Полностью оплаченные счета

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

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

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

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

FROM k_bill b, k_pay_sum p

WHERE b.bill_num=p.bill_num AND

b.bill_sum<=p.pay_sum

 

Поделиться:





Воспользуйтесь поиском по сайту:



©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...