Дисциплина Базы и банки данных Маркова Ирина Васильевна

Зарегистрируйтесь, чтобы просмотреть полный документ!

Дисциплина «Базы и банки данных» Маркова Ирина Васильевна, начальник управления информатизации markova@miit. ru

Раздел 6. SQL. Лекция SELECT Группировка данных Предложение GROUP BY разделяет таблицу на минимальное количество таких групп, что в пределах одной группы все строки имеют одинаковое значение для комбинации столбцов, указанных в списке группировки. C предложением GROUP BY часто используют агрегирующие функции – получение обобщающих значений по группе. Агрегирующую функцию можно применить: ко всем строкам; к строкам, которые определены выражением WHERE; к группам строк, которые определены выражением GROUP BY. Место агрегирующих функций: в списке выбора; в составе условия (предложение HAVING). 2

Раздел 6. SQL. Лекция SELECT Агрегирующие функции SUM ([DISTINCT] выражение); AVG ([DISTINCT] выражение); MAX ([DISTINCT] выражение); MIN ([DISTINCT] выражение); COUNT (*); 3

Раздел 6. SQL. Лекция SELECT Агрегирующие функции (примеры) SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE WHERE SALARY > 2500; SELECT department_id, MAX(salary), MIN(salary) FROM EMPLOYEE GROUP BY department_id; SELECT department_id, COUNT(dapatment_id) FROM EMPLOYEE GROUP BY department_id; SELECT department_id, COUNT(distinct job_id) FROM EMPLOYEE GROUP BY department_id, job_id; 4

Раздел 6. SQL. Лекция SELECT Предложение HAVING работает аналогично предложению WHERE, но применяется к группам и соответственно чаще используется совместно с предложением GROUP BY. Если в списке_выбора имеются агрегирующие функции, то предложение HAVING выполняется после вычисления функций и разбиения на группы. В отношении синтаксиса условного выражения, предложения HAVING и WHERE идентичны. SELECT department_id, MAX(salary), MIN(salary) FROM EMPLOYEE GROUP BY department_id HAVING MIN(salary) < 5000; 5

Раздел 6. SQL. Лекция SELECT Список порядка ORDER BY Предложение ORDER BY позволяет улучшить представление полученных результатов запроса. С его помощью можно отсортировать результаты по любому столбцу или выражению, указанному в списке_выбора. Данные могут сортироваться по убыванию (DESC) или по возрастанию (ASC). По умолчанию данные сортируются в порядке возрастания. В качестве выражений сортировки можно использовать: целое число, описывающее позицию выражения в списке_выбора; заголовок столбца, определённый в списке_выбора; выражение целиком. Может быть осуществлена сортировка внутри сортировки (вложенная сортировка). Поэтому количество уровней вложенности не ограничено. 6

Раздел 6. SQL. Лекция SELECT Список порядка ORDER BY (примеры) SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY salary DESC; SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC, last_name; SELECT last_name, department_id, salary FROM employees ORDER BY 2 ASC, 3 DESC, 1; 7

Раздел 6. SQL. Лекция SELECT Подзапросы Подзапрос - запрос, содержащийся внутри другого оператора SQL. Классификация подзапросов: 1. по месту нахождения: в предложении WHERE или HAVING (вложенный запрос); в предложении FROM (неявное представление). 2. по характеру связи: коррелированный; некоррелированный. Механизм подзапросов позволяет использовать результаты одного запроса в качестве составляющей части другого. В Oracle нет ограничения на количество уровней подзапросов в предложении FROM верхнего уровня, а в WHERE может быть не более 250 уровней вложенности. 8

Раздел 6. SQL. Лекция SELECT Подзапросы (продолжение) Основные причины использования подзапросов: оператор SQL с подзапросом является естественным способом выражения запроса, так как наиболее близок к его словесному описанию. подзапросы позволяют облегчить описание операторов SQL, т. к. разбивают сложный запрос на части. существуют запросы, которые иначе сформулировать нельзя. 9

Раздел 6. SQL. Лекция SELECT Вложенный запрос Отличия вложенного запроса от обычного оператора SELECT: таблица результата всегда состоит из одного столбца; как правило, отсутствует ORDER BY; отсутствуют операции над множествами. Коррелированный подзапрос – подзапрос, ссылающийся на столбцы таблицы главного запроса (внешняя ссылка). Значение в столбце внешней ссылки берется из строки, проверяемой в текущий момент главным запросом. select e. department_id, e. last_name, e. salary from hr. employees e where e. salary > (select avg(salary) from hr. employees where e. department_id=department_id) order by department_id; 10

Раздел 6. SQL. Лекция SELECT Условия поиска во вложенном запросе сравнение с результатом подзапроса; проверка на принадлежность результатам подзапроса; проверка на существование; многократное (групповое) сравнение. 11

Раздел 6. SQL. Лекция SELECT Сравнение с результатом подзапроса Предполагает сравнение значения выражения с одним значением возвращенного подзапросом. select emp. last_name, emp. id_department from employees emp where emp. id_department = ( select emp. id_department from employees emp where emp. last_name = 'МИЛЛЕР' ); 12

Раздел 6. SQL. Лекция SELECT Проверка на принадлежность Является видоизмененной формой простой проверки на вхождение во множество: проверяемое значение сравнивается со столбцом данных возвращенных подзапросом и если значение равно хотя бы единице, то проверка возвращает TRUE. SELECT * FROM EMPLOYEE t WHERE t. salary IN ( SELECT e. salary FROM EMPLOYEE e WHERE e. depatment = 30 ); 13

Раздел 6. SQL. Лекция SELECT Проверка на существование Позволяет выяснить содержится ли в результатах вложенного подзапроса хотя бы одна строка, эта проверка не может вернуть NULL. Особенность проверки на существование заключается в том, что условие поиска EXISTS в действительности не использует результаты, возвращенные вложенным подзапросом – проверяется только наличие. Во вложенном запросе при проверке на существование всегда имеется внешняя ссылка. 14

Раздел 6. SQL. Лекция SELECT Проверка на существование (примеры) select * from hr. departments d where not exists ( select 1 from hr. employees e where d. department_id=e. department_id and e. department_id is not null ); ------------------------select * from hr. departments d where d. department_id NOT IN ( select e. department_id from hr. employees e where e. department_id is not null ); 15

Раздел 6. SQL. Лекция SELECT Многократное сравнение – использование ANY Расширяет проверку на равенство по одному значению из списка. В ANY проверочное выражение поочередно сравнивается с каждым значением, содержащимся в возвращенном столбце. Если любое из этих сравнений дает TRUE, то проверка в целом дает TRUE. SELECT * FROM EMPLOYEE t WHERE t. salary = ANY (SELECT e. SALARY FROM EMPLOYEE e WHERE e. DEPATMENT = 30); 16

Раздел 6. SQL. Лекция SELECT Многократное сравнение – использование ANY Например, вывести данные о служащих, которые не руководят офисами. SELECT t. * FROM EMPLOYEE t WHERE t. EMPLOYEE_ID <> ANY (SELECT e. MANAGER_ID FROM EMPLOYEE e); Более удачная формулировка SQL-запроса: SELET * FROM EMPLOYEE t WHERE NOT(t. EMPLOYEE_ID = ANY (SELECT DISINCT e. MANAGER_ID FROM EMPLOYEE e WHERE e. MANAGER_ID IS NOT NULL)); Запрос с проверкой на ANY всегда можно преобразовать в запрос с проверкой на существование EXISTS, перенося операцию сравнения во вложенный запрос: SELECT T. * FROM EMPLOYEE T WHERE NOT EXISTS (SELECT 1 FROM EMPLOEE WHERE L. MANAGER_ID = T. EMPLOYEE_ID); 17

Раздел 6. SQL. Лекция SELECT Многократное сравнение – использование ALL Проверка ALL, как и ANY, использует один из шести операторов для сравнения одного проверяемого значения со столбцом данных, возвращаемых вложенным запросом. Проверяемое значение поочередно сравнивается с каждым значением, содержащимся в столбце. Если все сравнения дают значение TRUE, то проверка ALL тоже возвращает TRUE. Смысл условия проверки …WHERE X < ALL (SELECT Y…) становится яснее, если читать его иначе: «… где для всех Y X меньше, чем Y …» . Вывести сотрудников, у которых зарплата меньше всех в отделе номер 30: SELECT T. * FROM EMPLOYEE T WHERE T. SALSRY < ALL (SELECT E. SALSRY FROM EMPLOYEE E WHERE E. DEPATMENT = 30); 18

Раздел 6. SQL. Лекция SELECT Вложенный запрос в предложении HAVING Когда вложенный запрос содержится в HAVING, он выполняется один раз для каждой группы строк. SELECT d. DEPATMENT_NAME, AVG(E. SALARY) FROM DEPATMENT d, EMPLOYEE e WHERE e. DEPATMENT_ID = d. DEPATMENT_ID GROUP BY d. DEPATMENT_NAME HAVING AVG(e. SALARY) > (SELECT AVG(e. SALARY) FROM EMPLOYEE e); 19

Раздел 6. SQL. Лекция SELECT Неявное представление Место расположения – предложение FROM. Выбрать долю сотрудников и окладов по отделам по отношению к общим значениям соответственно: select a. ном_отдела "Отдел" , round(a. "сотрудников в отделе" / b. "Количество сотрудников", 1) "%_Сотрудников" , round(a. "Сумма в отделе" / "Сумма окладов", 1) "%_Окладов" from ( select ном_отдела , count(*) "сотрудников в отделе" , sum(оклад) "Сумма в отделе" from сотрудники group by ном_отдела ) a , ( select count(*) "Количество сотрудников" , sum(оклад) "Сумма окладов" from сотрудники ) b; 20

Раздел 6. SQL. Лекция SELECT Операции над множествами Служат для объединения данных нескольких запросов. UNION ALL INTERSECT MINUS 21

Раздел 6. SQL. Лекция SELECT Операции над множествами (примеры 1) Следующий запрос объединяет результаты запросов с помощью UNION, которая удаляет дубликаты строк. Здесь необходимо согласование типов данных, если в одной из таблиц нет столбца, входящего в список выборки другого запроса. SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse" FROM departments UNION SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name FROM warehouses; LOCATION_ID -----1400 1500 1600 1700 1700. . . Department Warehouse ---------------IT Southlake, Texas Shipping San Francisco New Jersey Accounting Administration Benefits Construction Contracting Control And Credit 22

Раздел 6. SQL. Лекция SELECT Операции над множествами (примеры 2) SELECT product_id FROM order_items UNION SELECT product_id FROM inventories ORDER BY product_id; SELECT location_id FROM locations UNION ALL SELECT location_id FROM departments ORDER BY location_id; SELECT product_id FROM inventories INTERSECT SELECT product_id FROM order_items ORDER BY product_id; SELECT product_id FROM inventories MINUS SELECT product_id FROM order_items ORDER BY product_id; 23

Скачать презентацию Дисциплина Базы и банки данных Маркова Ирина Васильевна

part lecture 14.pptx

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