Лекция №6 Структурированный язык запросов
Вопросы:
Структура запросов
БД Abonets.mdb
Запросы, реализующие операцию проекции
Запросы, поясняющие исключение дубликатов
Запросы, поясняющие выполнение операции фильтрации в одной таблице
Выборка данных из двух связанных таблиц
Выборка данных из трех связанных таблиц
Операторы сравнения
Оператор Not предиката Where
Оператор IN предиката Where
Оператор BETWEEN предиката Where
Оператор LIKE предиката Where
Оператор IS Null предиката Where
АГРЕГАТНЫЕ ФУНКЦИИ
Примеры использования агрегатов
Структура БД Work.mdb
Примеры использования агрегатов
Примеры использования агрегатов
Упорядочивание данных
Упорядочивание данных
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование запросов с использованием конструктора
Проектирование динамических запросов с использованием конструктора
513.50K
Category: databasedatabase

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

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]);
English     Русский Rules