Структурированный язык запросов

1. Лекция №6 Структурированный язык запросов

2. Вопросы:

Структура запросов
Операторы выборки и
фильтрации
Агрегатные функции
Сортировка данных

3. Структура запросов

SELECT T1.FAM,SUM(T2.WORK)
FROM T1, T2
WHERE T1.ID=T2.ID_T1
GROUP BY T1.FAM
HAVING SUM(T2.WORK)>1
ORDER BY T1.FAM

4. БД Abonets.mdb

5. Запросы, реализующие операцию проекции

SELECT * FROM TPOL;
SELECT ФИО, Адрес FROM TAbonents;

6. Запросы, поясняющие исключение дубликатов

SELECT DISTINCT Дата_установки
FROM TAbonents;
SELECT ALL Дата_установки
FROM TAbonents;

7. Запросы, поясняющие выполнение операции фильтрации в одной таблице

SELECT * FROM TAbonents
WHERE ПАСПОРТ = 679237
SELECT * FROM TAbonents
WHERE ПАСПОРТ=679237 OR
ФИО="Жуйченко Женя";

8. Выборка данных из двух связанных таблиц

SELECT TAbonents.ФИО, TPol.ПОЛ
FROM TAbonents, TPol
WHERE (TAbonents.Pol_ID = TPol.ID) AND
(TPol.ПОЛ = “м”) ;

9. Выборка данных из трех связанных таблиц

SELECT TAbonents.ФИО, Tpol.ПОЛ, NomTel.НОМЕР_ТЕЛЕФОНА
FROM Tpol, TAbonents, NomTel
WHERE TNomTel.id = TAbonents.NomTel_ID and
Tpol.id = TAbonents.Pol_ID and
TAbonents.ДАТА_УСТАНОВКИ=#12/5/1997#;

10. Операторы сравнения

>
Больше чем
< Меньше чем
>= Больше чем или равно
<= Меньше чем или равно
<> Не равно
SELECT * FROM TAbonents WHERE
(ДАТА_ВЫДАЧИ <> #6/1/1997#);

11. Оператор Not предиката Where

SELECT * FROM TAbonents
WHERE NOT (ФИО = “Иванов Иван Иванович”);
или
SELECT * FROM TAbonents
WHERE Not (ФИО="Иванов Иван Иванович" and
id=1)

12. Оператор IN предиката Where

SELECT * FROM TAbonents
WHERE ФИО In ("Иванов Иван Иванович","Петров");
SELECT * FROM TAbonents
WHERE ПАСПОРТ In (675537,326757,656729);

13. Оператор BETWEEN предиката Where

SELECT * FROM TAbonents
WHERE ПАСПОРТ Between 628513 and 679237;
SELECT * FROM TAbonents WHERE ФИО Between "А" and "К";

14. Оператор LIKE предиката Where

SELECT * FROM TAbonents
WHERE АДРЕС Like "ул.*";
SELECT * FROM TAbonents
WHERE АДРЕС Like "*ма*";

15. Оператор IS Null предиката Where

SELECT *FROM TAbonents WHERE АДРЕС iS Null;
SELECT * FROM TAbonents
WHERE Not АДРЕС Is Null;

16. АГРЕГАТНЫЕ ФУНКЦИИ

COUNT- подсчет количества (не NULL
значений полей) записей;
SUM- подсчет арифметической суммы
всех значений поля;
AVG- усреднение всех выбранных
значений данного поля;
MAX- нахождение наибольшего из всех
выбранных значений;
MIN- нахождение наименьшее из всех
выбранных значений.

17. Примеры использования агрегатов

SELECT SUM (Объем) as Сумма
FROM TWork;
SELECT Avg(TWork.Объем) AS Среднее
FROM TWork;
SELECT Max(TWork.Объем) AS Max_V
FROM Twork,Tday
WHERE TWork.День=Tday.id and
Tday.День Like "п*";
SELECT Count(id) AS Количество
FROM Twork,TFIO
WHERE TFIO.id=Twork.Смена and
TFiO.ФИО Like "*ов";
17

18. Структура БД Work.mdb

Преобразуем нашу таблицу к более
рациональному виду. Обратите
внимание, что в ее структуру были
добавлены два новых столбца:
Цена - за деталь (руб.)
Брак - испорчено деталей (шт.)
Они позволят продемонстрировать
возможность несложных расчетов.
В литературе вы можете встретить это
действие под называнием "построение
агрегатов, основанных на скалярном
выражении".

19. Примеры использования агрегатов

Найдем наибольшую долю брака при работе токаря Иванова.
SELECT MAX(Twork.Брак / (Twork.Объем + Twork.Брак ))
FROM Twork, TFIO
WHERE (TFIO.ID = Twork.Смена) AND (TFIO.ФИО = “Иванов” )
Шаг №1
Результат

20. Примеры использования агрегатов

Найдем максимальную стоимость деталей произведенных за первые
три дня недели токарем в фамилии которого присутствуют
сочетание «ро».
SELECT Max(TWork.Объем*TWork.Цена) AS Стоимость
FROM TFIO,TDay,TWork
WHERE TDay.ID = TWork.День and TFIO.id = TWork.Смена
And TDay.День In ("понедельник","вторник","среда“) and
TFIO.ФИО) Like "*ро*”;
Шаг №1
Результат

21. Упорядочивание данных

Найдем отсортированную таблицу о величине брака
ФИО – ДЕНЬ – БРАК
SELECT TFIO.ФИО, TDay.День, TWork.Брак
FROM TFIO, TDay, TWork
WHERE TDay.ID=TWork.День AND TFIO.id=TWork.Смена
ORDER BY TFIO.ФИО, TDay.День DESC , TWork.Брак;

22. Упорядочивание данных

Найдем отсортированную таблицу о величине брака
ДЕНЬ - ФИО – Объем
SELECT TDay.День, TFIO.ФИО, TWork.ОБЪЕМ
FROM TFIO, TDay, TWork
WHERE TDay.ID=TWork.День AND TFIO.id=TWork.Смена
ORDER BY TDay.День, TFIO.ФИО, TWork.ОБЪЕМ;

23. Проектирование запросов с использованием конструктора

24. Проектирование запросов с использованием конструктора

Определить
работников
цехов
занимающих должность рабочий в
возрасте от 20 до 45 лет, которые в
течение последних 5 лет получили
выговор за опоздание

25. Проектирование запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет

26. Проектирование запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет
Вариант №1
SELECT Т_Работник.ФИО, Т_подразделение.МестоРаботы,
Т_должность.Должность,
Format(DateDiff("m“,Т_Работник.ДатаРождения,Now())/12,”##0.00”)
AS Возраст
FROM Т_подразделение INNER JOIN
(Т_должность INNER JOIN Т_Работник ON Т_должность.id =
Т_Работник.id_должность) ON Т_подразделение.id =
Т_Работник.id_подразделение
WHERE Т_подразделение.МестоРаботы Like "цех*“ AND
Т_должность.Должность)="рабочий" AND
DateDiff("m“,Т_Работник.ДатаРождения,Now())/12
Between 20 And 45;

27. Проектирование запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет
Вариант №2
SELECT Т_Работник.ФИО, Т_подразделение.МестоРаботы,
Т_должность.Должность,
Format(DateDiff("m“,Т_Работник.ДатаРождения,Now())/12,”##0.00”)
AS Возраст
FROM Т_подразделение, T_должность, Т_Работник
WHERE Т_должность.id =Т_Работник.id_должность) AND
Т_подразделение.id = Т_Работник.id_подразделение AND
Т_подразделение.МестоРаботы Like "цех*“ AND
Т_должность.Должность="рабочий" AND
DateDiff("m“,Т_Работник.ДатаРождения,Now())/12
Between 20 And 45));

28. Проектирование запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет
Результат

29. Проектирование запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет, которые в течение
последних 5 лет получили выговор за опоздание

30. Проектирование запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет, которые в течение
последних 5 лет получили выговор за опоздание

31. Проектирование запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет, которые в течение
последних 5 лет получили выговор за опоздание
SELECT DISTINCT Т_Работник.ФИО,
Т_подразделение.МестоРаботы, Т_должность.Должность,
Format(DateDiff("m",[Т_Работник]![ДатаРождения],Now())/12,"Fixed")
AS Возраст
FROM (Т_подразделение INNER JOIN (Т_должность INNER JOIN
Т_Работник ON Т_должность.id = Т_Работник.id_должность) ON
Т_подразделение.id = Т_Работник.id_подразделение) INNER JOIN
Т_Выговоры ON Т_Работник.ID = Т_Выговоры.ID_Работник
WHERE Т_подразделение.МестоРаботы Like "цех*” AND
Т_должность.Должность="рабочий” AND
DateDiff("m",[Т_Работник]![ДатаРождения],Now())/12
Between 20 And 45 AND
Т_Выговоры.Выговор="опоздание» AND
Year(Now())-Year([Т_Выговоры]![Дата]<=5;

32. Проектирование динамических запросов с использованием конструктора

Определить работников цехов занимающих должность
рабочий в возрасте от 20 до 45 лет, которые в течение
последних N лет получили выговор за опоздание
SELECT DISTINCT Т_Работник.ФИО,
Т_подразделение.МестоРаботы, Т_должность.Должность,
Format(DateDiff("m",[Т_Работник]![ДатаРождения],Now())/12,"Fixed")
AS Возраст
FROM (Т_подразделение INNER JOIN (Т_должность INNER JOIN
Т_Работник ON Т_должность.id = Т_Работник.id_должность) ON
Т_подразделение.id = Т_Работник.id_подразделение) INNER JOIN
Т_Выговоры ON Т_Работник.ID = Т_Выговоры.ID_Работник
WHERE Т_подразделение.МестоРаботы Like "цех*” AND
Т_должность.Должность="рабочий” AND
DateDiff("m",[Т_Работник]![ДатаРождения],Now())/12
Between 20 And 45 AND
Т_Выговоры.Выговор="опоздание» AND
Year(Now())-Year([Т_Выговоры]![Дата]<=CINT([Укажите N]);