Скачать презентацию Лекция 3 Структурированный язык запросов Вопросы Скачать презентацию Лекция 3 Структурированный язык запросов Вопросы

L_6_SQL_zaprosy.ppt

  • Количество слайдов: 32

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

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

Структура запросов SELECT T 1. FAM, SUM(T 2. WORK) FROM T 1, T 2 Структура запросов SELECT T 1. FAM, SUM(T 2. WORK) FROM T 1, T 2 WHERE T 1. ID=T 2. ID_T 1 GROUP BY T 1. FAM HAVING SUM(T 2. WORK)>1 ORDER BY T 1. FAM

БД Abonets. mdb БД Abonets. mdb

Запросы, реализующие операцию проекции SELECT * FROM TPOL; SELECT ФИО, Адрес FROM TAbonents; Запросы, реализующие операцию проекции SELECT * FROM TPOL; SELECT ФИО, Адрес FROM TAbonents;

Запросы, поясняющие исключение дубликатов SELECT DISTINCT Дата_установки FROM TAbonents; SELECT ALL Дата_установки FROM TAbonents; Запросы, поясняющие исключение дубликатов SELECT DISTINCT Дата_установки FROM TAbonents; SELECT ALL Дата_установки FROM TAbonents;

Запросы, поясняющие выполнение операции фильтрации в одной таблице SELECT * FROM TAbonents WHERE ПАСПОРТ Запросы, поясняющие выполнение операции фильтрации в одной таблице SELECT * FROM TAbonents WHERE ПАСПОРТ = 679237 SELECT * FROM TAbonents WHERE ПАСПОРТ=679237 OR ФИО="Жуйченко Женя";

Выборка данных из двух связанных таблиц SELECT TAbonents. ФИО, TPol. ПОЛ FROM TAbonents, TPol Выборка данных из двух связанных таблиц SELECT TAbonents. ФИО, TPol. ПОЛ FROM TAbonents, TPol WHERE (TAbonents. Pol_ID = TPol. ID) AND (TPol. ПОЛ = “м”) ;

Выборка данных из трех связанных таблиц SELECT TAbonents. ФИО, Tpol. ПОЛ, Nom. Tel. НОМЕР_ТЕЛЕФОНА Выборка данных из трех связанных таблиц SELECT TAbonents. ФИО, Tpol. ПОЛ, Nom. Tel. НОМЕР_ТЕЛЕФОНА FROM Tpol, TAbonents, Nom. Tel WHERE TNom. Tel. id = TAbonents. Nom. Tel_ID and Tpol. id = TAbonents. Pol_ID and TAbonents. ДАТА_УСТАНОВКИ=#12/5/1997#;

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

Оператор Not предиката Where SELECT * FROM TAbonents WHERE NOT (ФИО = “Иванович”); или Оператор Not предиката Where SELECT * FROM TAbonents WHERE NOT (ФИО = “Иванович”); или SELECT * FROM TAbonents WHERE Not (ФИО="Иванович" and id=1)

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

Оператор BETWEEN предиката Where SELECT * FROM TAbonents WHERE ПАСПОРТ Between 628513 and 679237; Оператор BETWEEN предиката Where SELECT * FROM TAbonents WHERE ПАСПОРТ Between 628513 and 679237; SELECT * FROM TAbonents WHERE ФИО Between "А" and "К";

Оператор LIKE предиката Where SELECT * FROM TAbonents WHERE АДРЕС Like Оператор LIKE предиката Where SELECT * FROM TAbonents WHERE АДРЕС Like "ул. *"; SELECT * FROM TAbonents WHERE АДРЕС Like "*ма*";

Оператор IS Null предиката Where SELECT *FROM TAbonents WHERE АДРЕС i. S Null; SELECT Оператор IS Null предиката Where SELECT *FROM TAbonents WHERE АДРЕС i. S Null; SELECT * FROM TAbonents WHERE Not АДРЕС Is Null;

АГРЕГАТНЫЕ ФУНКЦИИ n COUNT- подсчет количества (не NULL значений полей) записей; n SUM- подсчет АГРЕГАТНЫЕ ФУНКЦИИ n COUNT- подсчет количества (не NULL значений полей) записей; n SUM- подсчет арифметической суммы всех значений поля; n AVG- усреднение всех выбранных значений данного поля; n MAX- нахождение наибольшего из всех выбранных значений; n MIN- нахождение наименьшее из всех выбранных значений.

Примеры использования агрегатов SELECT SUM (Объем) as Сумма FROM TWork; SELECT Avg(TWork. Объем) AS Примеры использования агрегатов SELECT SUM (Объем) as Сумма FROM TWork; SELECT Avg(TWork. Объем) AS Среднее FROM TWork; SELECT Max(TWork. Объем) AS Max_V FROM TWork WHERE TWork. День Like "п*"; SELECT Count(id) AS Количество FROM TWork WHERE Смена Like "*ов";

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

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

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

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

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

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

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

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

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

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

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

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

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

Проектирование запросов с использованием конструктора Определить работников цехов занимающих должность рабочий в возрасте от Проектирование запросов с использованием конструктора Определить работников цехов занимающих должность рабочий в возрасте от 20 до 45 лет, которые в течение последних 5 лет получили выговор за опоздание SELECT DISTINCT Т_Работник. ФИО, Т_подразделение. Место. Работы, Т_должность. Должность, Format(Date. Diff("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 Date. Diff("m", [Т_Работник]![Дата. Рождения], Now())/12 Between 20 And 45 AND Т_Выговоры. Выговор="опоздание» AND Year(Now())-Year([Т_Выговоры]![Дата]<=5;

Проектирование динамических запросов с использованием конструктора Определить работников цехов занимающих должность рабочий в возрасте Проектирование динамических запросов с использованием конструктора Определить работников цехов занимающих должность рабочий в возрасте от 20 до 45 лет, которые в течение последних N лет получили выговор за опоздание SELECT DISTINCT Т_Работник. ФИО, Т_подразделение. Место. Работы, Т_должность. Должность, Format(Date. Diff("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 Date. Diff("m", [Т_Работник]![Дата. Рождения], Now())/12 Between 20 And 45 AND Т_Выговоры. Выговор="опоздание» AND Year(Now())-Year([Т_Выговоры]![Дата]<=CINT([Укажите N]);