Лекции по базам данных

25,09,2012

Контроль транзакций важен в однопользовательских и многопользовательских СУБД, где транзакции могут быть запущены параллельно. В последнем случае говорят о сериалезуемости транзакции, под сериалезацией параллельно выполняемых транзакций понимается составление такого плана их выполнения (сериального плана), при котором суммарный эффект реализации транзакций эквивалентен эффекту их последовательного выполнения. При параллельном выполнении смеси транзакций возможно возникновение конфликтов (блокировок), решение которых является функцией СУБД. При обнаружении таких случаев обычно производится «откат» путем отмены изменений, произведенных одной или несколькими транзакциями.

Ведение журнала изменений в базе данных – функция СУБД (журнализация изменений). Выполняется СУБД для обеспечения надежности хранения данных в базе при наличии аппаратных сбоев и отказов, а так же ошибок в программном обеспечении. Журнал СУБД – это особая БД или часть основной БД непосредственно недоступная пользователю и используемая для записи информации обо всех изменениях базы данных. В различных СУБД в журнал могут вноситься записи, соответствующие изменениям в СУБД на разных уровнях: от минимальной внутренней операции – модификации страницы внешней памяти до логической операции модификации БД (например, ставки записи, удаление столбца, изменение значения в поле) и даже транзакции. Для эффективной реализации функции ведения журнала изменений в БД необходимо обеспечить повышенную надежность хранения и поддержание в рабочем состоянии самого журнала.

Обеспечение целостности базы данных – функция СУБД.
Составляет необходимое условие успешного функционирования БД, особенно для случая использования БД в сетях.

Целостность БД – есть свойство БД, означающее, что вы ней содержится полная непротиворечивая и адекватно отражающая предметную область информация. Поддержание целостности БД включает проверку целостности и ее восстановление в случае обнаружения противоречий в БД. Целостное состояние БД описывается с помощью ограничений целостности в виде условий, которым должны удовлетворять хранимые в базе данные. Примером таких условий может служить ограничение диапазонов возможных значений атрибутов объектов, сведения о которых хранятся в БД или отсутствие повторяющихся записей в таблицах реляционных баз данных.

Если в одном месте записано значение, то и в другом месте должно быть написано такое же значение, это означает целостность БД. Номер по порядку, фамилия, оклад – атрибуты базы данных.

Обеспечение безопасности – функция СУБД.
Достигается в СУБД шифрованием прикладных программ, данных, защиты паролем, поддержкой уровней доступа к БД и к отдельным ее элементам (таблицам, формам, отчетам).

Пункт 5.

Схема обмена данными при работе с БД

(в электронном)

Лекция №2

Модели данных

База данных содержит данные, используемые некоторой информационной системой, в зависимости от вида организации данных различают следующие основные модели представления данных в базе:

1. Иерархическую

2. Сетевую

3. Реляционную

4. Объектно-ориентированную

Пункт 1. Иерархическая модель данных.

В иерархической модели данные представлены в виде иерархической (древовидной) структуры

А1

С3

С2

С1

В2

В1

С4

С5

Основные понятия модели:

1. Узел (элемент) – совокупность атрибутов данных, описывающих некоторый объект. На дереве узлы – это вершины графов. Каждый узел на более низком уровне связан только с одним узлом на более высоком уровне. Дерево имеет только одну вершину(корень дерева), не подчиненную никакой другой вершине и находящуюся на самом верхнем (первом) уровне.

2. Уровень (связь)

Зависимые (подчиненные) узлы находятся на втором, третьем и т.д. уровнях. Количество деревьев в БД определяется числом корневых записей. К каждому узлу БД существует только 1 (иерархический)путь от корневого узла.

Например, школа.

Школа( номер, адрес, директор)

Номер 123, улица Гоголя, 45. Иванова Ирина Степановна

Класс

(номер, буква,

руководитель, количество

учащихся)

2б, Ковалева Нина Евгеньевна, 22 человека

2а, Лебедева Светлана Петровна, 19 человек

1б, Фадеева Ирина Алексеевна, 20 человек

1а, Петрова Валентина Николаевна, 21 человек

1. Сетевая модель.
   Основные понятия как и в иерархической модели, но каждый элемент может быть связан с любым другим элементом данных

H

G

E

D

F

C

B

A

Например, школьники и факультативы, которые они посещают.

Школьник (код, фамилия,Имя, класс)

2, история, Павлова И.А.

3, английский, Левченко А.П.

1, Алгебра, Васильева Е.С.

104, Иванов Петр, 9б

103, Данилов Иван, 9а

102, Смирнов Олег, 9а

101, Иванов Петр, 9а

1. Объектно-ориентированная.
   Объектная модель данных наиболее близка к объектам реального мира. Объекты можно хранить и использовать, непосредственно не раскладывая их по таблицам. Типы данных определяются разработчикам и неограниченны наборами определенных типов. В такой модели данных используется наследование и полиморфизм объектов, но, в большинстве случаев, декомпозиция объектов – их разложение по таблицам не представляет труда и вполне логично, поэтому реляционная модель данных используется чаще, чем объектная.

Пункт 4. Реляционная модель.

Понятие реляционный (relation – отношение) связано с разработками американского специалиста в области баз данных Кодда. Реляционная модель ориентирована на организацию данных в виде двумерных таблиц. Каждая реляционная таблица представляет собой двумерный массив и обладает следующими свойствами:

1. Каждый элемент таблицы – один элемент данных

2. Все элементы в столбце однородные, т.е. имеют одинаковый тип (текстовый, числовой, логический и т.д.)

3. Каждый столбец имеет уникальное имя

4. Одинаковые строки в таблице отсутствуют

5. Одинаковые столбики в таблице отсутствуют (ключевые поля)

6. Порядок следования строк и столбцов может быть произвольны

02, 10, 2012

Информация о студенте (отношение студент)

Основные понятия реляционной модели данных:

1. Тип данных – в реляционной модели данных полностью эквивалентен соответствующему понятию в алгоритмических языках. Набор поддерживаемых типов может отличаться, но следующие типы поддерживаются практически всегда:

   1. Числовые (целочисленные, вещественные)

   2. Строковые

   3. Денежные

   4. Временные (дата\время)

   5. Типы двоичных объектов (не имеют аналогов в языках программирования.)

2. Домен – наименьшая единица данных в реляционной модели – это отдельная атомарная (неразложимая) для данной модели значение данных. Доменом называется множество атомарных значений одного и того же типа, иным словами домен представляет собой допустимое потенциальное множество значений данного типа.
   
   например, для столбца фамилия домен фамилия будет базироваться на строковом типе данных и в него будут входить только те строки, которые могут обозначать фамилию (в честности такие строки не могут начинаться с «ь»). Домен дата рождения для столбца дата рождения определяется на базовом временном типе данных. Данный домен содержит только допустимый диапазон дат рождения студентов. В самом общем виде домен определяется заданием базового типа данных, к которому относятся все элементы домена и произвольного логического выражения, применяемого к элементу. Если при проверке этого логического выражения получается результат «истина», то элемент данных является элементом домена.

3. Атрибут (поля), схема отношений, схема БД. Столбцы отношения называются атрибутами (полями), им присваиваются имена, по которым к ним затем производится обращение.

   1. Список имен атрибутов отношения с указанием имен доменов (или типов, если домены не поддерживаются) называется схемой отношения.
      Например, отношение студент запишется так:
      студент {№ студенческого из номера студенческих билетов
      фамилия фамилия
      имя имя
      отчество отчество
      дата рождения дата рождения
      группа номер группы }

   2. Степень отношения – это число его атрибутов. Отношение степени 1 называют унарным, степени 2 бинарным, степени 3 тернарным, а степени n n-арным. Степень отношения студент равно 6. Схемой БД называется множество именованных схем отношений.

4. Кортеж (запись), соответствующий, данной схеме отношений представляет собой множество пар {имя атрибута, значение}, которое содержит одно вхождение каждого имени атрибута, принадлежащего схеме отношения. Значение должно принадлежать домену. Степень кортежа равна соответствующей степени отношения. Кардинальным числом или мощностью отношения называется число его кортежей. Мощность отношения студент равна 3 (три строки).
   Пустые значения – в некоторых случаях какой-либо атрибут отношения может быть не применим, например если мы при помощи отношения студент собираемся хранить информацию об абитуриентах, посещающих подготовительные курсы, мы не сможем определить группу, № студенческого билета. Кроме того, при вводе данных в строку реляционной таблицы некоторые данные могут быть неизвестны. В таком случае в поля соответствующие неприменимым или неизвестным атрибутам ничего не заносится. Такое пустое значение – это не ноль, или непустая строка, а неизвестное значение атрибута, которое не определено в данный момент времени. Для обозначения пустых значений полей используется слово NULL.

5. Ключ отношения. Никакие 2 кортежа отношения не могут быть дубликатами друг друга в произвольно заданный момент времени. Таким образом, в отношении всегда должен присутствовать некоторый атрибут (или набор атрибутов), однозначно определяющий каждый кортеж отношения и обеспечивающий уникальность строки таблицы. Такой атрибут (или набор атрибутов) называется первичным ключом отношения. Первичный ключ отношения обладает следующими свойствами:

   1. Уникальность. В произвольный момент времени никакие 2 различных кортежа отношения не имеют одного и того же значения для атрибутов первичного ключа.

   2. Минимальность. Ни один из атрибутов не может быть исключен из первичного ключа без нарушения уникальности. Для каждого отношения свойством уникальности обладает, по крайней мере, полный набор его атрибутов. Однако требуется обеспечить и условия минимальности, поэтому, как правило, всегда имеется один атрибут, обладающий свойством уникальности и являющийся первичным ключом.

В зависимости от количества атрибутов, входящих в ключ, различают простые и сложные (составные) ключи. Простой ключ – ключ, содержащий только один атрибут. В качестве типа данных лучше использовать самый простой и короткий тип данных, например, целочисленный. Составной ключ – состоит из нескольких атрибутов. В отношении студент в первичный ключ может быть включен атрибут – номер студенческого билета.

Связанные отношения.

В реляционной модели данных данные представляются в виде совокупности взаимосвязанных таблиц. Рассмотрим отношение успеваемость , в котором содержатся сведения об успеваемости студентов по различным предметам

Отношение успеваемость

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

Внешние ключи отношения

В БД одни и те же имена атрибутов часто используются в разных отношениях. В рассмотренных примерах атрибут номер студенческого билета присутствует как в отношении студенты, так и в отношении успеваемости. Внешний ключ – это атрибут или несколько атрибутов одного отношения, являющееся ключом другого отношения. Таким образом, атрибут номер студенческого билета отношения успеваемость является внешним ключом. Внешние ключи используются для установления логических связей между отношениями. Связь между двумя таблицами устанавливается путем присваивания значений ключа одной таблицы значения внешнего ключа другой таблицы. Внешние ключи могут быть простыми, либо составными.

09, 10, 2012

Условия целостности данных.

Для непротиворечивости и однозначности информации в БД реляционной модели устанавливаются ограничительные условия – это правила, определяющие возможные значения данных. Ограничение целостности позволяют свести к минимуму ошибки, возникающие при обновлении и обработке данных. Важнейшими ограничениями целостности являются:

1. Категорийная целостность (никакой атрибут первичного ключа строки не может быть пустым)

2. Ссылочная целостность (если две таблицы связаны между собой, то внешний ключ таблицы должен содержать только те значения, которые уже имеются средти значений ключа, по которому осуществляется связь)

При обновлении ссылающегося значения необходимо следить за тем, чтобы не появлялись некорректные значения внешнего ключа, а при удалении картежа их отношения, на которое ведется ссылка возможно использование одного из трёх подходов:

1. Запрещается удалять картежа, на котором присутствуют ссылки

2. При удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится неопределенным

3. Каскадное удаление – при удалении кортежа из отношения, на которое ведет ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи

Типы связей между таблицами.

При установлении связи между двумя таблицами одна из них будет являться главной, а другая подчиненной.

Различают 4 типа связи между таблицами реляционной базы данных:

1. Один к одному – к каждой записи одной таблицы соответствует только одна запись другой таблицы

2. Один ко многим – одной записи главной таблицы могут соответствовать несколько записей подчиненной таблицы.

3. Многие к одному - нескольким записям может соответствовать одна и та же запись подчиненной таблицы

4. Многие ко многим – одна запись главной таблицы связана с несколькими записями подчиненной таблицы, а одна запись подчиненной таблицы связана с несколькими записями главной таблицы

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

Индексы.

При создании БД ключи часто реализуются с помощью индексов. Индекс представляет собой указатель на данные, размещенные в реляционной таблице. Можно привести аналогию индекса таблицы БД с указателем, помещаемого в конце книги. Чтобы найти в книге страницы, относящиеся к некоторой теме, проще обратиться к указателю, в котором устанавливается соответствие между перечисленными в алфавитном порядке темами и номерами страниц, и сразу определить страницы, которые следует просмотреть. Чтобы без указателя найти все страницы, относящиеся к нужной теме, пришлось бы просматривать всю книгу. Индекс БД предназначен для аналогичных целей, чтобы ускорить поиск информации в таблице БД. Индекс предоставляет информацию о точном физическом расположении данных в таблице. При добавлении и удалении записей в таблице индекс так же изменяется. При поиске по индексированному столбцу, поиск производится в индексе. Если поиск прошел успешно, то в индексе устанавливается точное местоположение искомых данных в таблице БД.

Различают несколько типов индексов:

1. Простые индексы – представляют собой простейший и наиболее распространенный тип индексов, он строится на основе одного столбца таблицы.

2. Составные индексы – строятся по двум и более столбцам реляционной таблицы, при этом последовательность столбцов, по которым создается индекс влияет на скорость поиска данных.

Лекция 3

Реляционные операторы

Способы манипулирования содержимым таблицы определяется операторами:

1. Union – объединение. Объединяет все строки из двух таблиц. Чтобы оператор можно было использовать, таблицы должны иметь одинаковые свойства по атрибутам (столбцы и домены должны быть идентичны). Когда таблицы (2 или более) использую одни и те же столбцы, и домены говорят, что они совместимы по объединению.
   А

В

Объединение А и В:

2. Пересечение – InterSet – выводит все строки, которые встречаются в обоих таблицах, таблицы должны быть совместимы по объединению. Результат:

3. Difference – разность – выводит все строки первой таблицы, которые отсутствуют во второй. Таблицы должны быть совместимы по объединению.

А-В

Объединение А и В:

4. Product – произведение – выводит все возможные пары строк из двух таблиц. Так же называется декартовым произведением.

С:

А*С

И так далее

5. Select – (выбор).

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

6. Project (проекция).

Выводит все значения выбранных атрибутов.

7. Join (соединение).

Позволяет компоновать информацию из двух или более таблиц. Операция выполняется несколькими способами
- естественное соединение. Связывает таблицы, выбирая только строки с общими значениями их общих атрибутов. Эта операция является результатом 3-х ступенчатой процедуры Product-Select-Project.

Особенности:

• Если совпадений между строками таблицы нет, то в новую таблицу не совпадающие строки не включаются.

• Столбцы, над которыми выполняется операция join, в новой таблице встречается 1 раз.

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

Товар

Поставщики:

Product – произведение

Select – выбираются те строки, в которых совпадают коды поставщиков.

Project (столбец «Код поставщика» остается один)

- эквисоединение (соединение по эквивалентности)

Связывает таблицы по условия равенства, при котором сравниваются указанные столбцы каждой таблицы. В результирующей таблице дублирующие столбцы не удаляются.

- внешнее соединение.

Остаются пары, а все несовпадающие значение должны принимать пустые значения. Существует левостороннее внешнее соединение. Оно включает все строки первой таблицы, дополняя данными из второй. Правостороннее внешнее соединение включает строки второй таблицы, дополняя сведениями из первой.

Левостороннее внешнее соединение.

Правосторонне внешнее соединение

8. Divide (деление)

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

При делении находятся значения, которые соответствуют одновременно А и В. Общее значение 5. Соответственно, ответ: 5.

Если поделить на столбец АЕ, то ответом будет пустой столбец (нет общих значений).

В реально существующих СУБД обычно не поддерживаются все 8 операций, так как одна операция может быть записана через другие. Обычно набор операций, существующих во всех СУБД это Select, Join, Project.

Лекция 4

Построение запросов в SQL

Одним из наиболее эффективных и универсальных способов выборки данных из таблицы БД является использование запросов языка SQL (Structured Query Language). Команды SQL подразделяются на несколько категорий. Для выборки данных используются команды, относящиеся к так называемому языку запросов - DQL (Data Query Language). SQL – запросы можно использовать как при работе с локальными БД, так и с SQL серверами баз данных, причем при формировании SQL-запросов не имеет особого значения какая СУБД используется, так как команды языка стандартизированы. Команда Select является ядром SQL, она используется для формирования запросов к БД. Запросы формируются для извлечения из таблиц БД информации, соответствующей некоторым требованиям, задаваемым пользователем.

П1. Простейшая форма оператора Select

Оператор Select вместе с предложением From используется для получения информации из БД. Синтаксис простейшей формы следующий:

SELECT {*| ALL| DISTINCT field1, field2, …, fieldN,}

FROM table1 {, table2, …, tableN}

За ключевым словом Select следует список полей (field1, field2, …, fieldN), которые возвращаются в результате выполнения запроса:

• Имена полей в списке разделяются через запятую;

• Для выборки всех полей таблицы (таблиц) используется символ *;

• Операция ALL (задана по умолчанию) означает, что результат выборки будет содержать все записи, включая дублирующие друг друга;

• При использовании опции DISTINCT результат запроса не будет содержать дублирующие строки.

Совместно с командой SELECT всегда используется предложение FROM, с помощью которого указывается имя таблицы (таблиц), из которой производится выборка. Если в предложении FROM указывается несколько таблиц, то их имена разделяются запятыми.

При использовании в операторе SELECT имен полей и имен таблиц, в которых содержатся пробелы, такие имена следует заключать в квадратные скобки.

Например.

Выбираются все поля из таблицы Заказчики, и в результате запрос будет иметь вид:

SELECT*FROM Заказчики. Результат:

Исходная таблица аналогична

23, 10,2012.

Выбираются неповторяющиеся значения из поля Код товара таблицы Заказы

SELECT DISTINCT [Код товара] FROM Заказы– позволяет выбрать все неповторяющиеся значения

П. 2. Задание условий для выборки данных

Для ограничений отбираемой из БД информации оператор Select позволяет использовать условие, которое задается с помощью предложения Where. Операторы языка SQL, применяемые для задания условия, можно разделять на группы:

• Операторы сравнения

• Логические операторы

• Операторы объединения

• Операторы отрицания

Результатом выполнения каждого из этих операторов является логическое значение true или false. Если для некоторой записи оператор возвращает значение true, то запись включается в результат выборки, если false – то не включается.

Операторы сравнения

Используются для ограничения отбора записей таблицы: =,,, =

Например, в таблице Заказы отбирается информация о заказчиках, не оплативших заказ.

SELECT DISTINCT [Код заказчика]

FROM Заказы

WHERE Оплата=False

Логические операторы

К логическим относятся операторы, в которых для задания ограничений на отбор данных используются специальные ключевые слова. В SQL определены следующие логические операторы: Is Null, Between…and..., In, Like, Exists, Unique, All, Any.

ОПЕРАТОР IS NULL

Предназначен для сравнения текущего значения поля со значением Null. Он используется для отбора записей, в некоторое поле которых не занесено никакое значение.

Например, отберем заказчиков, у которых не указана фамилия директора:

SELECT [Код заказчика], [Название заказчика]

FROM Заказчики

WHERE Директор Is Null

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

SELECT [Код заказчика], [Название заказчика]

FROM Заказчики

WHERE Директор Is Not Null

Оператор Between…and...

Применяется для отбора записей, в которых значение поля находится внутри заданного диалога. Границы диапазона включаются в условие отбора.

Например: Отберем информацию о товарах, цена которых находится ы диапазоне от 10 до 1000 рублей.

SELECT [Код товара], [Наименование товара], [Цена]

FROM

WHERE BETWEEN 10and 100

Оператор In

Используется для выборки записей, в которых значение некоторого поля соответствует хотя бы одному из значений заданного списка.

Например: Отберем информацию о ручках, листиках и маркерах.

SELECT [Код товара], [Наименование товара], [Цена]

FROM Товары

WHERE [Наименование товара] IN («Ластик», «Ручка», «Маркер»)

Оператор Like

Используется для сравнения значение поля со значением, заданным при помощи шаблона. Для шаблона используются символы:

• «%» (или *) – заменяет последовательность символов любой длины (в том числе и нулевой)

• «_» (или ?) – заменяет любой единичный символ

Например: отберем все товары, названия которых начинается на букву Т.

SELECT [Код товара], [Наименование товара]

FROM Товары

WHERE [Наименование товара] Like «Т*»

Оператор Exists

Оператор используется для отбора записей на основе подчиненного запроса. Подчиненный запрос выполняется для каждой записи основной таблицы.

Например, имеются две связанные по полю Код заказчика таблицы Заказчики и Заказы. Необходимо отобрать из таблицы Заказчики информацию о тех заказчиках, которые не оплатили заказ.

SELECT [Код заказчика], [Наименование заказчика], [Директор]

FROM

WHERE EXISTS (SELECT [Оплата] FROM Заказы

WHERE оплата=False and Заказчики. [Код заказчика]=Заказы. [Код заказчика])

Оператор ALL

Этот оператор используется для сравнения исходного значения со всеми другими значениями, входящими в некоторый набор данных.

Например: отберем из таблицы Товары только те, которые имеют цену большую, чем цена товаров, наименование которых начинается на букву К.

SELECT *

FROM Товары

WHERE Цена ALL (SELECT [Цена] FROM Товары

WHERE [Наименование товара] LIKE («К*»))

Оператор ANY

Применяется для сравнения заданного значения со значениями некоторого набора данных, определяемых подзапросов. Запись удовлетворяет запросу, если условие выполняется хотя бы для одного сравнения с результатами подзапросов.

Например: отберем из таблицы Товары те, которые имеют цену большую, чем цена хотя бы одного товара с наименованием, начинающимся на букву К.

SELECT *

FROM Товары

WHERE ЦенаANY (SELECT [Цена]

FROM Товары WHERE [Наименование товара] Like («К*»))

Оператор отрицания NOT

Для большинства из ранее операторов (кроме ALL, ANY) может быть выполнена операция отрицания, меняющая результат выполнения оператора на противоположный.

Например: IS NOT NULL

NOT BETWEEN

NOT IN

NOT LIKE

NOT EXISTS

30, 10, 2012

П.3 Сортировка данных

Для упорядочивания данных в выборке, полученной в результате выполнения запроса используются предложения ORDER BY. Синтаксис оператора STLTCT в этом случае будет следующим:

SELECT {*|ALL| DISTINCT field 1, field 2, …, field N}

FROM table 1 {table 1, table2, …, table N}

WHERE условия

ORDER BY field {ASC|DESC}

После предложения ORDER BY указываются имена полей, по которым производится сортировка, а затем указывается режим сортировки:

• ASC – по возрастанию (алфавиту)

• DESC – по убыванию (в обратном порядке алфавита).

Например: Отсортируем данные по заказам в соответствии с датой заказа.

П.4 Выборка данных из нескольких таблиц

Язык SQL позволяет создавать запросы, извлекающие данные из нескольких таблиц. При этом выполняется операция соединения, состоящая в объединении нескольких таблиц с целью поиска в них запрошенных данных.

Существуют несколько видов соединения таблиц:

• Соединение равенства

• Соединения неравенства

• Внешнее соединение

Для задания вида соединения используется предложение WHERE, в котором вид соединения указывается с помощью операторов сравнения или логических операторов.

Соединение равенства

Используется наиболее часто. Производится по общему для нескольких таблиц полю (по первичному ключу).

Синтаксис опрелатора выборки для этого способа соединения таблиц будет следующим:

SELECT table1.field1, table2.field2 {, …, tableN.fieldN}

FROM table1, table2, {, …, tableN}

WHERE table1.common_field= table2. common_field

{and… and table (N-1). common_field=tableN. common_field}

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

Например: отберем из таблицы Товары и Заказы следующие поля: Наименование товара, Цена, Количество. Таблицы связаны по полю – Код товара.

SELECT [Товары].[Наименование], [Товары] .[Цена], [Заказы].[Количество]

FROM Товары, Заказы

WHERE Товары. [Код товара]=Заказы. [Код заказа]

Соединение неравенства

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

Синтаксис запроса при соединении неравенства отличается от предыдущего тем, что в предложении WHERE вместо равенства полей используются операторы , .

В большинстве случаев такое соединение невозможно.

Внешнее соединение

При использовании внешнего соединения результат запроса будет содержать все записи одной из таблиц, даже в том случае, если в связанной таблице отсутствует совпадающее значение. Этот тип соединения реализуется с помощью оператора JOIN.

Внешнее соединение подразделяется на группы:

• Левое внешнее соединение, LEFT JOIN – выборка будет содержать все записи таблицы, имя которой указано слева от оператора JOIN.

• Правое внешнее соединение, RIGHT JOIN – выборка будет содержать все записи таблицы, имя которых указано справа от оператора JOIN.

Синтаксис оператора SELECT при внешнем соединении:

SELECT table1.field1, table2.field2 {, …, tableN.fieldN}

FROM table1 LEFT\RIGTN JOIN table2 On условие {…LEFT|RIGHT JOIN tableN ON условие}

Например: Выполним левое соединения. В левой таблице должны быть отобраны все записи. В таблице Товары есть товары, которые никогда не входили в заказ. Но при этом также есть товары (Ластик, Ручка шариковая), которые продавались несколько раз. Информация о них будет повторяться.

SELECT [Товары].[Наименование товара], [Товары]. [Цена], [Заказы]. [Количество]

FROM Товары LEFT JOIN

Заказы ON [Товары]. [Код товара] = [Заказы]. [Код заказа]

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

П.5 Использование вычисляемых полей.

Язык SQL позволяет создавать вычисляемые поля в тексте запроса. Для реализации этой функции ы запросе приводится выражение, в котором используется арифметические операции, математические и строковые функции, а также имена полей в качестве переменных. В результате выполнения запроса с вычисляемыми полями выборка будет содержать не только ту информацию, которая содержится в таблицах БД, но и дополнительную информацию, полученную в результате вычисления заданного выражения.

Например: Отберем информацию о наименовании заказного товара, дате заказа, цене, количестве и сумме, на которую сделан заказ.

SELECT [Товары].[Наименование товара], [Заказы]. [Дата заказа], [Товары].[Цена], [Заказы].[Количество], Цена*Количество

FROM Товары, Заказы

WHERE Товары. [Код товара]= Заказы.[Код заказа]

20, 11, 2012

Псевдонимы полей.

В запросах SQL можно изменять имена полей. Задаваемые при этом новые имена называются псевдонимами. Их удобно применять при создании вычисляемых полей. Псевдоним помещается после имени поля или после вычисляемого выражения через ключевое слово AS. Переименование поля с помощью псевдонима действительно только в пределах конкретного запроса.

Например: В предыдущем запросе зададим псевдоним вычисляемому полю Сумма заказа.

П.6 Функции агрегирования

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

В SQL определены следующие стандартные функции агрегирования:

• COUNT – выполняет подсчет количества записей

• SUM – возвращает

• MIN – возвращает минимальное значение

• MAX – возвращает максимальное значение

• AVG – возвращает среднее значение

Например:

Необходимо подсчитать количество заказов

SELECT Count ([Код заказа]) AS

[Количество заказов]

FROM Заказы

П.7 Группировка данных

Группировка данных – это объединение записей в соответствии со значениями некоторого заданного поля. Для группировки результатов выборки совместно с оператором SELECT используется предложение GROUP BY. Данное предложение должно следовать после предложения WHERE , но перед предложением ORDER BY. После предложения GROUP BY указывается список полей, по которым должна выполняться группировка записей. Если поле указано в списке после SELECT, в случае групповой операции, к нему должна быть применена функция агрегирования, или оно должно использоваться для группировки, т.е. должно быть записано после предложения GROUP BY.

Например: подсчитаем общие суммы, на которые был сделан заказ каждым из заказчиков.

SELECT [Код заказчика],

SUM (Цена*Количество) AS [Общая сумма]

FROM Товары, Заказы

WHERE Товары.[Код товара] = Заказы.[Код товара]

GROUP BY [Код заказчика]

ORDER BY SUM (Цена*Количество) DESC

Для задания ограничений на создаваемые группы совместно с ключевым словом GROUP BY может использоваться предложение HAVING, оно должно следовать после GROUP BY, но до предложения ORDER BY (если оно присутствует в запросе).

Например: изменим предыдущий запрос так, чтобы отбирались только заказы с суммой больше 1000 рублей.

SELECT [Код заказчика],

SUM (Цена*Количество) AS [Общая сумма заказа]

FROM Товары, Заказы

WHERE Товары.[Код товара]= Заказы.[Код товара]

GROUP BY [Код заказчика]

HAVING (Цена*Количество)1000

ORDER BY 2 DESC

П.8 Подзапросы

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

• Подзапросы должны заключаться в круглые скобки

• Предложение ORDER BY может быть использовано только в основном запросе

• Подзапросы, возвращающие более одной записи, могут использоваться только с многозначными операторами (EXSISTS, ALL, ANY)

• В основном запросе нельзя использовать оператор BETWEEN.

Синтаксис оператора SELECT с подзапросом:

SELECT {* | ALL|DISTINCT field1, field2, …, fieldN }

FROM table 1 {, table 2, …, tableN}

where условие (SELECT {*| ALL| field1, field2, …, fieldN }

FROM table 1 {, table 2, …, tableN}

WHERE (условие)

Например: по наименованию товара Ластик необходимо отобрать из таблицы Заказы информацию о Коде заказчика, Дате заказа, Дате поставки и Количестве. В таблице Заказы нет поля Наименование товара, поэтому приходится строить подчиненный запрос, который отберет Код товара по его Наименованию.

SELECT [Код заказчика], [Дата заказа], [Дата поставки], [Количество]

FROM Заказы

WNERE [Код товара]=(SELECT [Код товара]

FROM Товары

WHERE [Наименование товара]= «Ластик»)

П.9 Объединение запросов

Язык SQL позволяет объединять несколько запросов с помощью специальных операторов. Запросы, включающие в себя несколько операторов SELECT, принято называть составными. Составные запросы формируют один набор данных на основе результатов, полученных при выполнении каждого отдельного запроса, входящего в объединение. Во многих случаях составные запросы целесообразно использовать вместо простых запросов со сложным условием выборки. Для объединения запросов часто используются операторы UNION и UNION ALL.

При объединении запросов, необходимо следовать правилам:

• Каждый запрос, входящий в объединение, должен возвращать одинаковое количество полей (в том числе и вычисляемых)

• Типы полей, возвращаемых в результате выполнения каждого запроса должны совпадать.

27, 11, 2012

Оператор UNION

При использовании оператора UNION результаты выполнения отдельных запросов объединяются, при этом дублирующие друг друга записи исключаются из результирующего набора данных.

Например: выберем из таблицы Товары список товаров, цена которых больше 8 или больше 50ю Такую выборку можно сделать, используя объединение условий в предложении WHERE с помощью оператора OR, либо путем объединения запросов.

SELECT *

FROM Товары

WHERE Цена