'

SQL (диалект Oracle). Выборки с помощью SELECT лекция-семинар

Понравилась презентация – покажи это...





Слайд 0

SQL (диалект Oracle). Выборки с помощью SELECT лекция-семинар Фильтрация (where) и сортировка (order by) Многотабличные запросы, inner join Single row functions. Псевдонимы (alias) Group functions, группировка (group by, having) Внешние соединения (left, right, full outer join) Операции над выборками (union, minus, intersect) Подзапросы, в т.ч. многострочные (in, any, all) Иерархические запросы в Oracle (connect by)


Слайд 1

1. Простейшие запросы select table_name from user_tables select sysdate from dual общий вид простых запросов: SELECT [DISTINCT] {*, column [alias],...} [WHERE … [AND …] [OR …]] FROM table;


Слайд 2

DISTINCT Выражение вида SELECT DISTINCT позволяет выбрать только уникальные записи (Использовать только тогда, когда это действительно необходимо: сильно падает скорость) select distinct salary from employee


Слайд 3

Структура используемых таблиц


Слайд 4

Наполнение используемых таблиц


Слайд 5

Выражения, ограничивающие набор строк (WHERE) select name, salary from employee where department_id is null select name, salary from employee where salary between 2500 and 3000 select name, salary from employee where salary not in (2000, 2500, 3000, 3500)


Слайд 6

Сортировка (ORDER BY) select salary, name from employee order by salary, name DESC – сортировка в направлении убывания select e.salary, e.name from employee e //e - alias order by e.salary desc, e.name


Слайд 7

Можно использовать функцию вместо атрибута сортировки после order by (а также вместо выбираемого атрибута после select и в условии отбора после where) select salary, name from employee order by dbms_random.random //это функция в пакете


Слайд 8

2. Многотабличные запросы. Соединение таблиц (Inner Join) select e.name, d.name department //department – alias столбца from employee e, department d //e, d – alias таблиц where e.department_id = d.department_id order by department Упражнение 1: Выполнить аналогичный запрос c выдачей зарплат, причем зарплаты <> 5000 (AND)


Слайд 9

Функции Есть два типа функций (имена – из Oracle): Single Row Functions – функции, применяемые к конкретной строке выборки (SIN, ROUND, DECODE, NVL, SUBSTR, ..) Group Functions – функции, применяющиеся к некоторому подмножеству выборки (COUNT, AVG, STDDEV, MAX, MIN)


Слайд 10

3. Single Row Functions Функции, применяемые к одной записи (точнее, к одной ячейке) из выборки Типы Single Row Functions: Математические функции: SIN, EXP, MOD, … Функции для работы со строками, датами и др.: SUBSTR, LOWER, LPAD, NEXT_DATE, … Функции преобразования типов: TO_DATE, TO_CHAR, TO_NUMBER, … Функции, переопределяющие значения: DECODE, NVL, …


Слайд 11

Математические функции select name, mod(salary, 1000) mod from employee where name like ‘%Petrov’ //like – сравнение строк по маске select sin(1) from dual Функции работы со строками Упражнение 2: в запросе упражнения 1 выдавать имена с большой буквы (остальные - строчные), независимо от того, как они хранятся в БД. Применить SUBSTR, UPPER, LOWER, CONCAT (или оператор ||)


Слайд 12

Функции переопределения значения select name, salary, nvl(department_id, 0) department_id from employee


Слайд 13

select name, decode (salary, 1500, 'Good', 2000, 'Very Good', 'Cool!') status from employee


Слайд 14

4. Group Functions. Выражение GROUP BY Выражение GROUP BY используется для разбиения выборки на группы с равными значениями в заданном(ых) столбце(ах) Групповая функция (COUNT, AVG, …) – для подсчета одного числа по каждой группе (или по всей выборке, если group by не задано) select salary, count(*) number from employee group by salary select avg(salary) average_salary from employee


Слайд 15

select department_id, max(salary) max_salary, min(salary) min_salary from employee group by department_id Упражнение 3: на основе запроса упражнения 1 подсчитать среднюю зарплату по каждому отделу (выдавать название отдела, а не его id). Упражнение 4 (c outer join, см. далее): –//–, но также выдать ср. зарплату сотрудников без отдела (применить функцию NVL для названия несуществующего отдела)


Слайд 16

Условие HAVING select department_id, avg(salary) from employee group by department_id having max(salary) > 2000 Если требуется отфильтровать строки до группировки – where, если после группировки – having


Слайд 17

Две формы записи Inner Join Используется обычно: select e.name, d.name department from employee e, department d where e.department_id = d.department_id Стандарт ANSI: select e.name, d.name department from employee e inner join department d on e.department_id = d.department_id 5. Внутр. и внешние соединения


Слайд 18

Left Outer Join (внешнее соединение) Стандарт ANSI: select e.name, d.name department from employee e left outer join department d on e.department_id = d.department_id Используется в Oracle: select e.name, d.name department from employee e, department d where e.department_id = d.department_id(+)


Слайд 19

Right Outer Join Стандарт ANSI: select e.name, d.name department from employee e right outer join department d on e.department_id = d.department_id Используется в Oracle: select e.name, d.name department from employee e, department d where e.department_id(+) = d.department_id


Слайд 20

Full Outer Join Стандарт ANSI: select e.name, d.name department from employee e full outer join department d on e.department_id = d.department_id Так неправильно!: select e.name, d.name department from employee e, department d where e.department_id(+) = d.department_id(+)


Слайд 21

6. Теоретико-множественные операции над выборками Объединение множеств: select name, salary from employee where department_id=2 union [all] select name, salary from employee where department_id is null Вычитание множеств: select department_id from department minus select department_id from employee Упражнение 5: придумать осмысленный запрос с пересечением множеств - intersect


Слайд 22

7. Подзапросы Подзапрос с единственным результатом: select name, salary from employee where salary > (select salary from employee where name=‘Oleg Fedorov’) Многострочный подзапрос и сравнение с его результатами (in, any, all): select name, salary from employee where salary < all (select salary from employee where department_id=2) <ANY – меньше максимума; >ANY – больше минимума; <ALL – меньше минимума; >ALL – больше максимума; =ANY – эквивалентно IN; <>ALL – эквивалентно NOT IN… Упражнение 6: Выбрать сотрудников, чья зарплата превышает среднюю з/п по какому-либо отделу 2500 2000, 3500


Слайд 23

8. Иерархические запросы (Oracle) [start with условие] определяет корень(ни) дерева connect by и prior задает отношение parent-child можно использовать псевдостолбец level Пусть в таблице employee есть столбец manager references employee(employee_id). Тогда перечисление всех сотрудников с их подчиненными (если они есть): select lpad(‘ ’,3*(level-1))||name “name” from employee connect by prior employee_id = manager_id //prior – перед PK Упражнение 7: построить иерархию начальников-подчиненных, в которой корни – только топ-менеджеры


×

HTML:





Ссылка: