Базы данных Тема 3 2 Основы SQL Запросы

Базы данных Тема 3. 2 Основы SQL. Запросы

SQL Команда SELECT

SQL Команда SELECT Многотабличные запросы • Соединение таблиц. Выдать список сотрудников с детьми: select e. emp_name, c. name as child, c. born from emp e, children c where e. tabno = c. tabno order by e. emp_name, c. born; select e. emp_name, c. name as child, c. born from emp e inner join children c on e. tabno = c. tabno order by e. emp_name, c. born;

SQL n n Результатом запроса select на нескольких таблицах будет декартово произведение исходных таблиц. Если с части where при этом указать условие соответствия значений полей разных таблиц, то получится соединение таблиц. Для полей с одинаковыми именами необходимо указывать имя таблицы (или псевдоним) перед именем поля, разделяя их точкой. Другой формой записи операции соединения является использование ключевых слов inner join.

SQL Команда SELECT Многотабличные запросы • Подсчитать количество сотрудников по отделам: select d. depno , d. depname, count(*) from emp e, depart d where e. depno = d. depno group by d. depno; Замечание: Данные о 4 -м отделе, в котором нет сотрудников, не выведены.

SQL Команда SELECT Многотабличные запросы • Типы внешнего соединения (OUTER JOIN): Ø Ø Ø LEFT JOIN – левое внешнее соединение; RIGHT JOIN – правое внешнее соединение; FULL JOIN – полное внешнее соединение;

SQL n n Кроме внутреннего соединения (inner join) существует еще внешнее соединение (outer join). Различают 3 типа внешнего соединения: Ø Левое внешнее соединение таблиц R и S, при котором в результат входят все строки левой таблицы R и те строки правой таблицы S, которые удовлетворяют условию соединения таблиц; Ø Правое внешнее соединение таблиц R и S, при котором в результат входят все строки правой таблицы S и те строки левой таблицы R, которые удовлетворяют условию соединения таблиц; Ø Полное внешнее соединение таблиц R и S, которое является объединением левого и правого внешнего соединения;

SQL Команда SELECT Многотабличные запросы • LEFT JOIN: select id, o. city, oid from departs d left join obj o on d. city = o. city;

SQL Команда SELECT Многотабличные запросы • RIGHT JOIN: select id, o. city, oid from departs d right join obj o on d. city = o. city;

SQL Подзапросы Команда SELECT • Могут располагаться в разных частях команды SELECT: Ø в части FROM – табличные некоррелированные; Ø в части WHERE – любые; Ø в части HAVING - любые; Ø в части SELECT – скалярные. • Операторы, используемые для модификации операторов сравнения: Ø ALL – оператор, эквивалентный понятию «все» . > ALL (< ALL) – больше (меньше) каждого значения. Ø ANY - оператор, эквивалентный понятию «любой» . > ANY (< ANY) – больше (меньше) любого значения. = ANY – ровно одному из значений Ø EXISTS оператор, эквивалентный понятию «существует» . Ø NOT EXISTS - «не существует» .

SQL n n n Подзапросом называется запрос SELECT, который находится внутри другой команды SQL. Подзапросы можно разделить на следующие группы в зависимости от возвращаемых результатов: Ø Скалярные – подзапросы, возвращающие единственное значение; Ø Векторные - подзапросы, возвращающие от 0 до нескольких однотипных элементов; Ø Табличные - подзапросы, возвращающие таблицу. Подзапросы бывают коррелированные и некоррелированные. Коррелированные подзапросы содержат ссылки на значения полей в запросе верхнего уровня, а некоррелированные – не содержат. Некоррелированный подзапрос вычисляется один раз для запроса верхнего уровня, а коррелированный – для каждой строки запроса верхнего уровня. Сначала рассмотрим использование подзапросов в команде SELECT. Подзапросы могут располагаться в разных частях этой команды (см. слайд 18). Подзапрос всегда стоит справа от оператора сравнения или предиката. Операторы, используемые для модификации операторов сравнения - см слайд 18. Если список, модифицированный оператором ALL, содержит NULL-значение, то результирующий запрос будет пуст, так как нельзя сравнивать никакое значение с NULL-значением.

SQL Команда SELECT Примеры подзапросов в части WHERE • Выдать список сотрудников, имеющих детей (с помощью операции соединения таблиц): select distinct e. * from emp e, children c where e. tabno = c. tabno;

SQL Команда SELECT Примеры подзапросов в части WHERE • Выдать список сотрудников, имеющих детей (некоррелированный векторный подзапрос): select * from emp where tabno = ANY (select tabno from children);

SQL Команда SELECT Примеры подзапросов в части WHERE • Выдать список сотрудников, имеющих детей (коррелированный табличный подзапрос): select * from emp e where EXISTS (select * from `children` c where e. tabno = c. tabno);

SQL Команда SELECT Примеры подзапросов в части WHERE • Выдать список сотрудников, оклад которых выше среднего на предприятии (некоррелированный скалярный подзапрос): select depno, Emp_name, post, salary from `emp` where salary > (select AVG(salary) from `emp`);

SQL Команда SELECT Примеры подзапросов в части WHERE • Выдать список сотрудников, имеющих оклады выше среднего по отделу (коррелированный подзапрос): select e. depno, Emp_name, post, salary from `emp` e where salary > (select AVG(salary) from emp m where m. depno = e. depno);

SQL Команда SELECT Примеры подзапросов в части FROM • Выдать список сотрудников, имеющих оклады выше среднего по отделу (некоррелированный табличный подзапрос): select e. depno, Emp_name, post, salary from `emp` e, (select depno, AVG(salary) avgsal from emp group by depno) m where e. depno = m. depno and e. salary > avgsal;

SQL Команда SELECT Примеры подзапросов в части HAVING • Выдать список отделов, в которых средние оклады ниже среднего оклада по предприятию (некоррелированный скалярный подзапрос): select depno, avg(salary) from `emp` group by depno having avg(salary) < (select avg(salary) from emp);

SQL Команда SELECT Пример использования команды UNION • Подсчитать количество сотрудников по всем отделам (включая те отделы, в которых нет сотрудников): select depno, count(*), ' сотрудник(а)' from emp group by depno UNION select depno, 0, ' сотрудников' from depart where depno not in (select depno from `emp`) order by 1;

SQL Команда SELECT Пример подзапроса в части SELECT • Подсчитать количество сотрудников по всем отделам (включая те отделы, в которых нет сотрудников): select depno, (select COUNT(*) from emp e where e. depno = d. depno) as cnt from depart d order by 1;

SQL Команда SELECT Самосоединение • Вывести список детей сотрудников, у которых есть младшие братья или сестры: select e. emp_name, c 1. name as child 1, c 1. born as born 1, c 2. name as child 2, c 2. born as born 2 from children c 1, children c 2, emp e where c 1. tabno = e. tabno – первое условие соединения and c 1. tabno = c 2. tabno -- второе условие соединения and c 1. born < c 2. born -- условие исключения order by 1, 3;

SQL n В команде select можно обратиться к одной и той же таблице несколько раз. При этом для каждой таблицы необходимо задать свой псевдоним, это позволяет обращаться к полям этих таблиц. Система будет выполнять такой запрос на основе декартова произведения таблиц, поэтому необходимо указать условие соединения. А для того чтобы исключить соединение записи таблицы с самой собой в запросе на самосоединение, необходимо также указывать условие типа «не равно» (<>, >, <).

SQL Команда SELECT Оператор СASE • 1 -я форма: case <проверяемое выражение> when <сравниваемое выражение 1> then <возвращаемое значение 1> …. when <сравниваемое выражение N> then <возвращаемое значение N> [else <возвращаемое значение> ] end • 2 -я форма: case when <предикат 1> then <возвращаемое значение 1> …. when <предикат N> then <возвращаемое значение N> [else <возвращаемое значение> ] end

SQL n n Многие СУБД поддерживают оператор CASE. Этот оператор может быть использован в одной из двух синтаксических форм записи (см. слайд). Все команды when должны иметь одинаковую синтаксическую форму, то есть нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие when удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в команде when. При использовании второй синтаксической формы условие when удовлетворяется, как только предикат принимает значение true. При удовлетворении условия оператор case возвращает значение, указанное в соответствующем предложении then. Если ни одно из условий when не выполнилось, то будет использовано значение, указанное в команде else. При отсутствии else будет возвращено значение NULL. Если удовлетворены несколько условий, то будет возвращено значение предложения then первого из них, так как остальные просто не будут проверяться.

SQL Команда SELECT Оператор СASE • Вывести список сотрудников с указанием тех, у которых самая высокая и самая низкая зарплата на предприятии: select e. emp_name, salary, (case salary when (select MAX(salary) from emp) then 'самая высокая' when (select Min(salary) from emp) then 'самая низкая' else 'средняя' end) as note from emp e;

SQL Команда SELECT Оператор СASE • Вывести список сотрудников и количество имеющихся у них сыновей и дочерей: select e. emp_name, COUNT(case when sex ='м' then 1 else null END) sons, COUNT(case when sex ='ж' then 1 else null END) daughers from emp e, children c where e. tabno = c. tabno group by e. emp_name;

Контрольные вопросы 1. Назовите способы соединения таблиц в команда select. Приведите примеры. 2. Понятие подзапроса. Типы подзапросов. Укажите разделы команды select, где могут быть использованы подзапросы. Приведите примеры.