- •Курс за третий семестр. Введение в субд. Базы данных как аппарат моделирования.
- •Базы данных
- •Классификация бинарных отношений
- •Реляционные базы данных
- •Сжатие избыточной информации
- •Нормализация баз данных
- •Моделирование бд. Нарушение целостности
- •К эволюции сетевых бд (этапы)
- •Определение бд в рамках архитектуры «клиент-сервер»
- •Язык sql (Structured Query Language)
- •Структура sql
- •Ограничение ссылочной целостности
- •Команды dml
- •Предикаты в sql
- •Выборка из нескольких таблиц
- •Опции group by и having Группировка и групповые вычисления
- •Опции order by и union
- •Предикаты, использующие выборку Вложенные подзапросы
- •Создание представлений
- •Проблемы модификации представлений
- •Проблема исчезающих значений
- •Транзакции
- •Примеры транзакций
Выборка из нескольких таблиц
В самом простом случае список таблиц задаётся перечислением:
Select * /*все поля исходной таблицы*/ from Customer, Employee
Случай выборки из нескольких таблиц сводится к случаю выборки из одной таблицы. Таковым является именованное декартово произведение таблиц.
-
Поля таблицы 1
Поля таблицы 2
Запись 1
Запись 1
Запись 1
Запись 2
Запись 1
Запись 3
Именно в силу этого данный пример совершенно непотребный: mn – это очень много.
select * from Orders, Item, Product
where ID=Order_Ref, ID=Product_Ref;
Коллизия имён различных таблиц разрешается, как обычно, использованием полных, или квалифицированных, имён.
where Order.Id=Order_Ref
Product.Id=Product_Ref;
Правда такой приём не спасает при выборке из декартовых степеней.
Выделим однофамильцев:
select cust1.name, cust1.Id, cust2.Id
from Customer.cust1, Customer.cust2
where (cust1.name=cust2.name) and (cust1.Id<cust2.Id);
В таких случаях используют псевдонимы, локальные имена, или алиасы. В более сложном синтаксисе условие связи отделяется от условия фильтрации и записывается в списке выборки: (имя таблицы 1) join (имя таблицы 2) on (условие связи).
select cust1.name, cust1.Id, cust2.Id
from Cusromer. cust1 join
Customer. cust2 on
cust1.name=cust2.name
where cust1.Id<cust2.Id;
Опция join имеет дополнительные опции, а именно: Inner, Left, Right, Full.
Inner – по умолчанию.
Что делать с официальными сиротами, то есть значениями ключей, равными null? Внутреннее соединение inner join игнорирует такие записи. Left join добавляет в результирующую выборку все записи из левой таблицы со значениями ключа null в комбинации с фиктивной нулевой записью второй таблицы.
select (что-то) from T1,T2 where B(r1,r2) /*предикат*/ /*ri – поле таблицы Ti*/
То же самое:
select (что-то) from T1 join T2 on B(r1,r2)
T1 [inner] join T2 on B(r1,r2)
{r1r2: r1T1 & r2T2 /*все возможные комбинации записей*/ & B(r1,r2)}
T1 left join T2 on B(r1,r2) = {r1r2: r1T1 & r2T2 & B(r1,r2)} U
U{r1Null2: r1T1 & r2T2 : B(r1,r2)}
- not - не существует.
Null2 – фиктивная запись таблицы T2, то есть запись с именами из T2 со значениями, равными null.
select cust1.name, cust1.Id, cust2.Id
from Customer.cust1 left join Customer.cust2
on (cust1.name=cust2.name) and (cust1.Id>cust2.Id)
T1 right join T2 on B(r1,r2) = {r1r2: r1T1 & r2T2 & B(r1,r2)} U
U{r1Null1r2: r1T1 & r1T1 : B(r1,r2)}
T1 full join T2 on B(r1,r2) = Left U Right U Inner – объединение.
Опции group by и having Группировка и групповые вычисления
Group by (список полей)
Поля могут задаваться именем или порядковым номером поля в списке выборке.
Семантика. Записи базовой таблицы разбиваются на классы эквивалентности. В один класс – группу, агрегат – попадают записи, значения которых на полях заданного списка равны.
Пример. Группировка по внешнему ключу разбивает дочерние записи по группам; в одну группу попадут дети одного родителя, затем по каждой группе порождается единственная запись результирующей таблицы.
Функции, определённые на группах (не записях), называют агрегатными. При использовании опции group by список выборки обязан состоять только из обращений к таким функциям.
К групповым функциям относятся (по определению):
-
поля, входящие в список группировки, а также следующие функции:
-
count (выражение над полями записи) – количество значений данного выражения в группе;
-
count (*) – число записей в группе;
-
min (выражение), max (выражение) – соответственно минимальное и максимальное значение заданного выражения для каждой группы; определены для значений произвольного скалярного типа;
-
sum (выражение числового типа) – сумма значений по группе
-
avg (выражение числового типа) – среднее арифметическое значение по группе.
Примечание. Обращение к агрегатным функциям возможно и без явной группировки, то есть использования опции group by; в этом случае группой считается вся таблица.
Пример. Для каждого покупателя подсчитать количество заказов.
select Cust_Ref , count (Id)
from Orders
group by Cust_Ref;
Не выдаст записи со значением null, то есть покупателей без заказов. В реальности синтаксис практически свободный.
Опция having употребляется только вместе с group by. Это предикат, ссылающийся на значения групповых функций.
Синтаксис: having (условие на группу)
Как правило, сравнение значений агрегатной функции с константой.
Семантика опции having часто путается с семантикой опции where. Having включает условие, показывающее, вычислять ли значения соответствующих агрегатных функций для группы.
Пример. Вычислить среднее число покупок казанских покупателей – для достаточно крупных покупателей (то есть тех, у кого больше ста покупок).
select count(order.Id)
from Custoner, Orders
where (customer.Id=orders.Cust_Ref) and (customer.city=’Казань’)
group by Cust_Ref
having count(orders.Id)>100;