5.4. Пример оформления главы 3 «Основные команды sql для извлечения, добавления и изменения данных»

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

Общий вид команды:

Select <список полей> from <имя таблицы>

[inner join <имя связанной таблицы> on <условие связи>]

[where <условное выражение>]

[group by <группировочный признак>]

[order by <выражение> [asc|desc]];

Для добавления строки в таблицу используется команда INSERT, относящаяся к командам DML (Data Manipulation Language). Добавляемые значения можно вводить с указанием имен столбцов, либо без указания имен столбцов, но в этом случае вводимые значения должны по количеству, последовательности и типу соответствовать структуре полей таблицы.

Общий вид команды:

Insert into <имя таблицы> [(список полей)] values (<список значений>);

Для изменения данных в таблице или представлении применяется команда UPDATE, также относящаяся к командам DML. Эта команда заменяет значения указанных полей на заданные значения в строках, удовлетворяющих условию (или во всех строках, если условие не задано).

Общий вид команды:

Update <имя таблицы> set <имя поля1>=<значение1>,… [WHERE <условное выражение>];

Задание 1. Показать список выпущенных изделий в заданном цехе. В запросе вывести поля: код и наименование цеха, код и наименование изделия, количество выпущенных изделий, дату выпуска.

Вид команды:

select v.kod_c as Код_цеха, c.name_c as Наим_цеха, i.kod_i as Код_изделия, i.name_i as Наим_изделия, v.kol_v as Количество, v.dat as Дата from vypusk v inner join cex c on v.kod_c=c.kod_c inner join izdel I on v.kod_i=i.kod_i where v.kod_c=1

Р езультат выполнения запроса приведен на рис. 5.14.

Рис. 5.14. Результат запроса по заданию 1

Задание 2. Преобразовать предыдущий запрос таким образом, чтобы он выводил список выпущенных изделий за заданный календарный период.

Вид команды:

SELECT v.kod_c AS Код_цеха, c.name_c AS Наим_цеха,

i.kod_i AS Код_изделия, i.name_i AS Наим_изделия, v.kol_v AS Количество,

v.dat AS Дата FROM vypusk v INNER JOIN cex c ON v.kod_c=c.kod_c

INNER JOIN izdel I ON v.kod_i=i.kod_i WHERE v.kod_c=1

and v.dat between '26/01/2008' and '29/01/2008'

Контрольный пример:

Зададим период с 26.01.08 по 29.01.08. Очевидно, что список выпущенных изделий будет тем же, что в предыдущем примере, за исключением последних трех строк (рис. 5.15).

Р ис. 5.15. Результат выполнения запроса по заданию 2

Задание 3. В новую таблицу поместить сведения об общем выпуске изделий каждого наименования. Показать список изделий, выпуск которых превышает среднее количество выпущенных изделий. Показать поля: наименование изделия, общий выпуск данного изделий, средний выпуск среди всех изделий.

Создадим новую таблицу ob_vp с двумя полями kod_i (код изделия), sumkol (общее количество данных изделий) из сгруппированного по коду изделия запроса.

SELECT kod_i,sum(kol_v) AS sumkol into ob_vp FROM vypusk

GROUP BY kod_i (1)

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

Команда запроса, показанная ниже, обращается к двум таблицам: izdel и ob_vp. Подзапрос в списке полей показывает среднее количество всех выпущенных изделий, такой же подзапрос в правой части условия позволяет вывести только те изделия, общий выпуск которых (поле ob_vp.sumkol) превышает средний выпуск по всем изделиям (рис. 5.17).

select o.kod_i, i.name_i,o.sumkol,

(select avg(sumkol) from ob_vp) as Средний_выпуск

from ob_vp o INNER JOIN izdel i

on o.kod_i=i.kod_i where o.sumkol>(select avg(sumkol) from ob_vp) (2)

Р ис. 5.17. Результат выполнения запроса по заданию 3

Контрольный пример:

Произведем подсчет общего выпуска каждого изделия по данным из таблицы «Выпуск»:

Таблица 5.7

Расчет общего выпуска для каждого вида изделия

Код изделия	Количество
10	100
20	50+10=60
30	90+100=190
40	71
50	25+80+50+50=205
60	18+70=88
70	90
80	43+60=103
90	20
100	50+5+5=60
110	30
120	24+40=64
130	20
140	15

Результат выполнения команды создания таблицы ob_vp команда (1) совпадает с результатом, представленным в табл. 5.7.

Найдем среднее количество выпуска:

(100+60+190+71+155+88+90+103+20+60+30+64+20+15)/14≈79,71.

Очевидно, что выпуск выше среднего имеют изделия с кодами: 10, 30, 50, 60, 70 и 80. Обратившись к данным таблицы, найдем, что это изделия: стол обеденный, стол компьютерный, стул кухонный, стул, шкаф-гардероб и шкаф книжный. Нетрудно убедиться, что команда 2 выводит список именно этих изделий.

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

Вид команды:

SELECT i.kod_i AS Код_изд, i.name_i AS Наим_изд, d.kod_d AS Код_дет,

d.name_d AS Наим_дет, id.kol AS Количество, d.cena AS Цена,

id.kol*d.cena AS Стоимость

FROM izd_det id INNER JOIN izdel i

ON (id.kod_i=i.kod_i) INNER JOIN detal d

ON (id.kod_d=d.kod_d) WHERE id.kod_i=90

Р ис. 5.18. Результат выполнения запроса по заданию 4

Контрольный пример:

Н айдем список всех деталей для изготовления изделия с кодом 100 (шкаф-буфет), и их количество на 1 изделие в таблице IZD_DET (см. рис. 5.19)

Рис. 5.19. Список кодов деталей для изделия с кодом 90

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

Таблица 5.8

Код изделия	Наименование изделия	Код детали	Наименование детали	Кол-во деталей	Цена детали	Общая стои-мость
90	шкаф-стеллаж	124	полка	4	110	440
90	шкаф-стеллаж	116	стенка боковая	2	80	160
90	шкаф-стеллаж	117	стенка верхняя	1	80	80
90	шкаф-стеллаж	118	стенка нижняя	1	100	100

Как видно из рис. 5.18, запрос дает аналогичный результат, следовательно, он работает правильно.

Задание 5. Изменим цену определенной детали на 10%.

Вид команды:

Update detal set cena=cena*1.1 where kod_d=122

Р ис. 5.19. Результат выполнения команды UPDATE

Задание 6. Изменить цену на 2% тех деталей, цена которых ниже средней.

Вид команды:

Update detal set cena=cena*1.02 where cena<(select avg(cena) from detal)

Р ис. 5.20. Результат выполнения задания 6 На рис. 5.20 первая команда рассчитывает и выводит среднее значение цены деталей. Вторая команда запроса выводит список кодов деталей и их цен для тех деталей, цена которых ниже средней. Команда UPDATE увеличивает цены деталей на два процента для тех деталей, цена которых ниже средней. А последним запросом мы выводим коды и новые цены для двух первых деталей из полученного списка (см. результат второй команды). Мы видим, что цена их увеличена на 2%, следовательно, команда UPDATE проработала правильно.

Результаты выборки с помощью запроса после изменения цен изделий (рис. 5.27) и вычислений, проведенных вручную (см. рис.5.26), совпадают. Это означает, что выполненная нами команда UPDATE сработала правильно.