Анализ       Справочники       Сценарии       Рефераты       Курсовые работы       Авторефераты       Программы       Методички       Документы     опубликовать

Бд и субд. Классификация и примеры




Скачать 326.53 Kb.
НазваниеБд и субд. Классификация и примеры
страница2/3
Дата01.10.2014
Размер326.53 Kb.
ТипДокументы
1   2   3

^ Операции над отношениями, основные термины. 
n-арным (отношением степени n) называется подмножество полного декартового произведения. R включает D1 * D2* … * Dn. Di – множество, необязательно различное, которое называется домен. Домены именуются и элементы домена относятся к 1 типу данных. Полное декартово произведение – набор всевозможных сочетаний, где каждый элемент из своего домена. N=2, D1 = {Иванов, Петров, Сидоров}, D2 = {2,3,4,5}. Множество всевозможных сочетаний: {(Ив 2) (Ив 3)… (Сид5)}. Отношение отражает реальную ситуацию, несет информацию, содержит конкретные факты. R = {(Ив 2) (Пет 4) (Сид 5)}. Элементы отношения – кортежи. Вхождение домена в кортеж – атрибут. Порядок кортежей или атрибутов несущественен. Мощность – количество кортежей. Ранг отношения – количество атрибутов. Схема отношений – перечень атрибутов с указанием доменов. SR = (A1, A2, … An), Ai принадлежит Di. 2 отношения совместимы, если их схемы имеют одинаковое количество и тип атрибутов, т.е. степени должны совпадать, мощности могут отличаться. Задача любой СУБД – поиск информации. Причем над результатом поиска требуется проводить дополнительный поиск. Задача состоит в выводе одних отношений из других. Требуется система операций: реляционная алгебра (теория множеств), реляционные источники (исчисление предметов). Реальные языки, работающие с данными, являются гибридами. В целом приоритет у реляционной алгебры.
^ Реляционная алгебра. 
Алгеброй называется множество объектов, заданных на нем системой операций, замкнутой на нем относительно этого множества. Результат операции принадлежит этому же множеству, т.е. должен быть отношением. Кодд предложил 8 операций: 4 теорико-множественные, 4 специфические. 1. Объединение . 2. Пересечение ∩. R1 = {Иванов}, R2 = {Петров, Сидоров}, : {Ив, Пет, Сид}, ∩: . 3. Разность. R1\R2 = {r: r R1 ∩ r R2}. 4. Расширенное декартово произведение. R = R1 R2 = {(r1, r2): r1 R1, r2 R2}. Результат – всевозможные сочетания кортежей из обоих отношений. Преподаватели = {Ив, Пет, Сид}, предметы = {Ист, филос, матем}. Результат = {(Ив Ист) (Ив Филос) … (Сид матем)}. 5. Фильтрация (выборка). R[(R)] = {r|rR (r) = Ист}. Входят все кортежи, удовлетворяющие данному условию. (r) = «ФИО = Иванов». 6. Проекция. Customers [Address]; R[] = {‘Москва’, ‘Воронеж’}. Дубли исключаются. 7. Соединение. R1 и R2 – исходные отношения, соединение – подмножество расширенного декартового произведения, удовлетворяющее заданному условию . R = R1[]R2 = {(r1, r2)|r1R1, r2R2, (r1, r2) = Ист}. Обычно в качестве условия – совпадение ключей этих отношений РК и FK. Операция используется для выборки информации из нескольких связанных таблиц. 8. Деление отношений R:Q. Делитель должен содержать подмножество атрибутов делимого. Результат должен содержать только те атрибуты делимого, которых нет в делителе. Только те кортежи, декартово произведение которых в делителе, содержатся в делимом.

Студент

Предмет

Оц

Ив

Ив

Сид

Сид

Пет

Пет

Ист

Филос

Ист

Филос

Ист

Филос

5

4

4

5

5

4

Найти студента, имеющего 5 по ист, 4 по филос. Студент = {Ив, Пет}.

С помощью этих операций можно формулировать запрос к БД. Например: найти фамилию заказчиков, сделавших заказ в определенную дату. R1 = Customers, R2 = Orders. ((R1 [R1.CustID = R2.CustID]R2) [SaleDate = 10.01.09])[CustName]. На реляционной алгебре основан SQL. Конкретный язык запросов – реляционно полный, если то, что можно выразить одной конструкцией реляционной алгебры или исчисления, можно выразить одной конструкцией языка.
^ Оператор выборки, поиск и сортировка. 
SQL состоит из 4 частей: DDL – определение данных, DML – управление данными, ACL – управление доступом, DCL – управление транзакциями. Операторы языка DML. Select. Имеет сложный синтаксис. В упрощенном варианте:

Select {All|Distinct} <список столбцов или выражений>

From <список таблиц>

[where <логическое выражение>]

[group by <список столбцов>

[Having <логическое выражение>]]

[order by <список столбцов>]

All – выводит все строки (по умолчанию), distinct – если есть совпадающий строки, то выводится 1 строка, * вместо названия столбцов таблицы, from – из каких таблиц выбираются столбцы, where – фильтрация (только те, для который выражение истинно), group by – операция группировки строк, Having – условие отбора среди сгруппированных строк, order by – условие сортировки строк. Возвращается всегда множество строк, кортежей.

Select * from Customers

CustID

CustName

Address

1

2

Ив

Пет

Москва

Москва

Для FireBird инструмент IB Expert SQL Editor.

Условие отбора where. В выражении допускаются логические операции: and, or, not, операторы: <, >, <=, >=, <>, =, in – вхождение во множество. Проверка попадания в диапазон: [not] between A and B. Проверка принадлежности ко множеству: [not] in <список значений>. Проверка на пустоту: [not] is null. Пример: вывод заказчиков, проживающий в Москве.

Select * from Customers

Where Address = ‘Москва’

В IB Expert: диалект 1 – регистр не существенен, диалект 2 – регистр существенен и тогда надо использовать двойные кавычки, т.е. “Address” = ‘Москва’. В Oracle регистр существенен, в SQL Server не существенен. Пример: вывод всех граждан, проживающих в Москве и Воронеже.

Select * from Customers

Where (Address = ‘Москва’) or (Address = ‘Воронеж’).

Компактная форма записи: where Address in [‘Москва’, ‘Воронеж’]

Вывод товаров стоимостью от 100 до 200 рублей:

Select * from Products

Where Price between 100 and 200

Вывести заказчиков, не указывая адрес:

Select * from Customers

Where Address is Null

Чтобы поиск был нечувствителен к регистру нужно выполнить:

Select * from Customers

Where upper (CustName) = ‘ИВАНОВ’

Upper – стандартная функция, приводит к верхнему регистру. В InterBase функция с русскими буквами не работает. Приближенный поиск. Делается по шаблону: <столбец> like ‘шаблон’. В шаблоне могут встречаться: % - любое количество символов, _ - одиночный символ. Пример:

Select * from Customers

Where CustName like ‘И%’

<столбец> staring (containing) <подстрока>

Staring – в начале содержит подстроку, containing – в любом месте.

Сортировка Select. По умолчанию строки сортируются по первичному ключу.

Select * from Customers

Order by CustName (по алфавиту)

Order by CustName desk (по убыванию)

Order by CustName, Address (сортирует сначала по 1 столбцу, затем те, у которых одинаковые CustName, будут сортироваться по Address)
^ Агрегатные ф-ции и их свойства

В диалекте языка SQL есть множество встр-х ф-ций: мат-х, календарных, строковых, но среди них основное место занимают агрегатные

Count() – общее кол-во строк в столбце(учитывая повторные, не учитывая пустые)

Avg() - среднее арифм-кое в указ столбце

Sum(), Max(), Min() – в стлб, численного типа; макс и мин мб календарного типа

Вычисляются по множеству(по целому столбцу)

Исп-ся в списке выражений после select или в секции Having

Если указано count(*) – подсчет к-ва всех строк

☼подсчитать к-во заказчиков

Select count(*) from customers

☼Подсчитать к-во городов, где живут заказчики

Select count(distinct address) from customers

Если в списке после селект вместе с агрегатной функцией присутствует стлб, то он обязательно д. присутствовать в предложении группировки. В общем случае – все стлбцы

Секция group by <стлб1>, <стлб2>, ..

Группировка ожначает, что агр ф-ция вычисляется для каждого указ стлбца или для различных сочетаний стлбцов(если их несколько)

Products

prodId

prodName

Price

Rest

Category

country

1

2

3

4

5

Гайка

Болт

Телефон

Батарейка

Пиво

1

2

100

1

10

100

110

5

1

100

Хозтовары

Хозтовары

Электроника

Электроника

Продукты

КНР

КНР

КНР
РФ
РФ

☼Подсчитать сумму товаров по каждой стране

Select country? Sum(Rest) from products group by country

Country

Sum(rest)

КНР

РФ

215

101

Select country, category, sum(Rest) from products group by country, category

Country

Category

Sum(rest)

КНР
КНР
КНР
Россия

Россия

Россия

Хозтовары

Электроника

Продукты

Хоз

Элект

прод

210

5

0

0

1

100

Зам: после селект мб указано неск агрег ф-ций, а также ни одной

Секция having <выражение>

Условие having ограничивает ч. слов, в сгруппир таблице остаются только строки, удовл условию, м исп-ть агрег ф-цию

☼для каждой страны найти сумму товаров и оставить с списке ту строку, у кот сумма товаров > 200

Select country? Sum(Rest) from products group by country having (Sum(Rest)>200)

Country

Sum(Rest)

КНР

Россия

225

101

Having count(*)>2 при условии, что ч товаров для кажд страны>2 , получ попутно агрег ф-ция having м отлич от агрег ф-ции select

Select country, Sum(rest) from products group by country having (sum(rest)>200 count(*)>2)
^ Соединение таблиц

В языке SQL соотв реляц операции алгебры с этим же назв, но с некот расширениями, а именно

Различают внутреннее соединение и внешнее

Внешнее в свою очередь делится на 2: левое и правое

^ Внутреннее соединение

Select <список ст> from <список табл> when(условия соед)

☼teachers

teachId

FIO

1

2

3

Иванов

Петров

Сидоров

Items

ID

ItemName

TeachId

1

2

3

4

История

Философия

Политология

Культурология

1

1

2

Select T.TeachName, T.ItemName from teachers T, Items I where (T.TeachId=I.TeachId)

Иванов

Иванов

Петров

История

Философия

Политология

Расширенный синтаксис соединения

Select T.TeacherName I.ItemName from Teachers[inner|left|right|full] join Items I on T.TeachId = I.TeachId

По умолчанию inner – внутр соед

Left- в рез-т войдут все строки из левой, те первой таблицы.если для них нет связ строк из 2 табл, то вместо последних подст-ся знач null

Right – в рез-т войдут строки из правой табл и аналогично


Teacher

Item

Иванов

Петров

Сидоров

История

Политология

Null







TeachName

ItemName

1

2

3

4

5

Иванов

Иванов

Петров

Сидоров

-

История

Философия

Социология

-

культурология


^ Вложенный запрос

Часто бывает так, что значения, с кот необх сравнивать стлб заранее не известны и мб выч-но это значение только с помощью запрса select. Такой запрос назыв вложенным или подзапросом. Исп-ть подзапрос можно по стандарту, т. в секции where или having

Кроме того подзапросы примен в ограничении check, оператора create при создании доменов и в операторе модификации данных ♫рисуночек♫

☼найти идентификатор заказчика, сделанного max разовый заказ

Cust

Total

1

2

1

3

100

200

50

400

1) Select custId from Orders where Total = (Select max(Total) from Orders)

☼найти фамилию заказчика, сделавшего max заказ

2) Select CustName from Customers where CustId in(”1”)

Внутр запрос работает как функция, возвр значение после select ,оно дб одно

В случае, если внутр запрос возвращает мн-во значений(столбец), вместо оператора = применяется оператор принадлежномти ко мн-ву in

В более общем случае, когда значение сравнивается с рез-м вложенного запроса опер-р сравнения должен иметь след синтаксис

<столбец> оператор {ALL|ANY|SOME}

”in”<=>”=ANY” (>,<,<>,>=,<=) ☼>All

☼найти фамилию преподавателя, кот читает культурологию

1вар) внутреннее соединение

Select TeachName, ItemName from Teachers, Items where (Teachers.TeachId=Items.TeachId) and (ItemName=’социология’)

2вар)

Select TeachName from Teachers where TeachId in (Select TeachId from Items where ItemName=’социология’)

Недостатком внутр по сравнению с соединением явл вывод данных только из 1 таблицы

Вместо = следует писать in

☼найти покупателя, чей разовый заказ превышает средний заказ среди всех покупателей

Select custId from Orders where Total>= ^ All (необязательно) (Select avg(Total) from Orders)

☼найти покупателя, чей разовый заказ превышает средний заказ по разному покупателю

Select CustId From Orders where Total>= All(обязательно) (Select avg(Total) from Orders group by CustId)

☼найти покупателя, общая сумма заказов которого наибольшая

Select CustId, Sum(Total) from Orders group by CustId

1

2

3

150

200

400

Having sum(Total)>=All(Select Sum(Total) from Orders group by CustId)

Внутренний запрос находит сумму товара по каждому покупателю, в рез-те остается лишь то, что > чем все остальное – 400

☼самост☼вывести препод, кот ведет > всего предметов

Select TeachID, Count(*) from Teachers group by TeachId having count(*)>=all(select count(*) from Teachers group by TeachID)

☼самост☼найти преподавателя, кот ведет занятия

Select TeachName from Teachers where TeachID in (select TeachID from Items where Items.TeachID=Teachers.TeachID)

Иванов

Петров

В данном запросе внутри встреч стлб внешнего столбца Teachers.TeachID, те для каждого внешнего запроса внутренний запрос пересчитывается заново

Такие запросы называются карелированными, время их выполнения выше, чем у обычных, в частности карел-ми явл запросы с проверкой подзапроса на пустоту

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

[not] EXISTS(подзапрос)

Ex возвращает нет, если пожзапрс не пуст

☼найти товар, который не продавался ни разу

Select prodName from products where not exists (select * from orderDetails where products.ProdID=orderdetails.ProdID)

Подзапрос явл кар.

[not] Singulare

Истина, если подзапрос содержит только одну строку
^ Операторы модификации данных

Delete, update, insert

Delete – удаляет записи из таблиц, удолетворяющие условию

Delete from <таблица> [where <условие>]

Update заменяет значение столбца для записи, уд словию

update <таблица> set стлб1=знач1 [, ст2=зн2, .. ] where <условие>

update products set price=price*1.5 where productName=”молоко”

оператор вставки данных insert into <таблица> [<сп_столбцов>] values ()

опер-р доб-т новую запись и присваивает значения указанным столбцам

число значений должно совпадать с числом столбцов, ук-х в списке

☼insert into customers values(5, ‘Лебедев’, ‘Москва’)

Если хотим присваивать значения не всем столбцам, то список не надо указывать явно

☼insert into customers (CustName, Address) values (‘лебедев’, ‘москва’)

Если исп-ся естественные первичные ключи, их пишут в сп.зачений

Но для суррогатных ключей возможны способы авт.заполнения, в различных СУБД разные

В SQLserver первичным ключам можно дать атрибут identity(1,1)-нач знач 1,приращение 1

В этом случае они б.заполняться авт-ки

Кроме того в SQLserver для первичных ключей м. исп-ть тип GUID(глобальный уникальный идентификатор). В нем для заполнения первичных ключей обычно исп-ся генераторы Create Generator CustGer, для каждой таблицы обычно свой

Установление начального значения генератора set generation custGen to 1

Заполнение двумя способами: 1) ч\з триггер

2)явным способом: вызовом ф GenId(имя генератора приращения): значение генератора

В нашем случае м исп-ть insert into customers(custId, CustName, Address) values (GenId(CustGen, 1), ‘сидоров’, ‘москва’)

Если ук столбец, ему необх что то присвоить, в противном случае исп-ся авт мех-м

1)SQL Identity 2)IB триггеры стр38?

Триггер- проц, авт-ки вызывающийся в ответ на какое либо событие, в данном случае – заполняет первичный ключ

В СУБД Оракл для созд авто инкрементных значений существуют некоторые последовательности

Create sequence custSQ increment by 1 start with 1

Обращение к ней либо в триггере либо в опер-ре insert: insert into … values (CustSQ,Next, ‘Лебедев’, ‘Москва’)

Послдовательности в оракл это объекты, в ост работа как с генераторами
1   2   3



Разместите кнопку на своём сайте:
Документы




База данных защищена авторским правом ©kiev.convdocs.org 2000-2013
При копировании материала обязательно указание активной ссылки открытой для индексации.
обратиться к администрации
Похожие:
Документы