Similar presentations:
Структурированный язык запросов
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 TAbonentsWHERE ПАСПОРТ = 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 TAbonentsWHERE NOT (ФИО = “Иванов Иван Иванович”);
или
SELECT * FROM TAbonents
WHERE Not (ФИО="Иванов Иван Иванович" and
id=1)
12. Оператор IN предиката Where
SELECT * FROM TAbonentsWHERE ФИО In ("Иванов Иван Иванович","Петров");
SELECT * FROM TAbonents
WHERE ПАСПОРТ In (675537,326757,656729);
13. Оператор BETWEEN предиката Where
SELECT * FROM TAbonentsWHERE ПАСПОРТ Between 628513 and 679237;
SELECT * FROM TAbonents WHERE ФИО Between "А" and "К";
14. Оператор LIKE предиката Where
SELECT * FROM TAbonentsWHERE АДРЕС 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]);