Запрос на выборку

Соединение трех и более таблиц

Соединение таблиц в запросах

Понятие запроса

Лекция 5. Проектирование и создание запросов. Структурированный язык запросов (SQL).

Нормальные формы

Нормализация таблиц производится постепенно.

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

Всего известно 6 нормальных форм:

1 НФ (первая нормальная форма)

2 НФ (вторая нормальная форма)

3 НФ (третья нормальная форма)

НФ БК (нормальная форма Бойса-Кодда)

4 НФ (четвертая нормальная форма)

5 НФ (пятая нормальная форма)

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

Говорят, что таблица находится в первой нормальной форме, если она удовлетворяет принципу атомарности полей и принципу отсутствия повторяющихся групп полей.

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

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

1. Понятие запроса

2. Соединение таблиц в запросы

3. Соединение трех и более таблиц

4. Запрос на выборку

5. Запрос на выборку с группировкой

6. Перекрестный запрос

7. Перекрестный запрос с составным заголовком строк

8. Запрос на обновление

9. Запрос на удаление

10. Запрос на добавление

11. Логически выраженные команды SQL

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

Кортежи, входящие в запрос, могут быть сгруппированы и отсортированы по определенному признаку. Слово «запрос» может использоваться также в понятии «команда» или «инструкция», которая написана на языке SQL (Structured Query Language). В Microsoft Access запросы могут создаваться в режиме конструктора, при помощи мастера или в режиме SQL, однако хранятся они в виде команд SQL.

Если две физические таблицы имеют общие поля, то по этим полям данные из рассматриваемых таблиц могут быть объединены в одну общую таблицу. В принципе, все физические таблицы могут быть объединены в одну общую. Соединение таблиц может быть внутренним (inner join) и внешним (outer join). Внешнее соединение бывает левым (left join) и правым (right join). Перед созданием внешнего соединения всегда требуется определить, левым или правым оно является. Самого по себе внешнего соединения таблиц (без уточнения типа) не бывает. Рассмотрим пример соединения таблиц Т1 и Т2.

В таблице T1 поле F1 является ключевым, а поле F2 – информационным, в таблице Т2 ключевым является поле F3, а информационными - поля F1, F4. Результаты внутреннего соединения таблиц Т1 и Т2 представим в табл. Т3.

Порядок таблиц при внутреннем соединении значения не играет (то есть соблюдается свойство коммутативности). Внутреннее соединение таблиц Т1 и Т2 будет эквивалентно внутреннему соединению таблиц Т2 и Т1. Отметим, что в представленном примере не соблюдается ссылочная целостность и последнему пятому кортежу таблицы Т2 нет соответствия в таблице Т1.

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

Рассмотрим левое внешнее соединение таблиц Т1 и Т2, результаты представим в табл. Т4.

Кортежи таблицы Т4 получаются следующим образом: для каждого кортежа в таблице Т1 подбираются соответствующие кортежи в таблице Т2. Соответствие означает совпадение значения поля в таблицах Т1 и Т2, по которому производится соединение, то есть по полю F1. Если кортеж в таблице Т1 не имеет ни одного совпадения кортежей в таблице Т2, то он все равно попадает в результирующую таблицу. При левом соединении важен порядок таблиц.

Рассмотрим внешнее правое соединение таблиц Т1 и Т2, результаты представим в табл. Т5.

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

Рассмотрим примеры соединений таблиц по полю F1 и запишем их в виде инструкций:

T1 INNER JOIN T2 ON T1.F1=T2.F1

T1 LEFT JOIN T2 ON T1.F1=T2.F1

T1 RIGHT JOIN T2 ON T1.F1=T2.F1

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

Рис. 5.3.1 Схема данных для базы данных, состоящей из четырех таблиц

Для наглядности введем следующие условные обозначения:

Т1 INNER JOIN T2 – T1 + T2 (внутреннее соединение таблиц Т1 и Т2)

Т1 LEFT JOIN T2 – T1 T2 (левое соединение таблиц Т1 и Т2)

Т1 RIGHT JOIN T2 – T1 T2 (правое соединение таблиц Т1 и Т2)

С использованием введенных обозначений внутреннее соединение таблиц Т1 и Т2 можно записать следующим образом:

Т1 + Т2 on <условие>.

Внутреннее соединение трех таблиц Т1, Т2, Т3 можно записать при помощи следующих инструкций:

(Т1 + Т2 on <условие1>) + Т3 on <условие2>;

(Т2 + Т1 on <условие1>) + Т3 on <условие2>;

Т3 + (Т1 + Т2 on <условие1>) on <условие2>;

Т3 + (Т2 + Т1 on <условие1>) on <условие2>.

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

Рассмотрим левое соединение таблицы Т1 с внутренним соединением таблиц Т2 и Т3:

Т1 (Т1 + Т3 on <условие1>) on <условие2>.

Если поменять местами таблицу Т1 и внутреннее соединение таблиц Т2 и Т3, необходимо левое соединение заменить на правое:

(Т1 + Т3 on <условие1>) Т1 on <условие2>.

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

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

Рассмотрим порядок написания запросов на выборку с использованием инструкций языка SQL. Для этого можно использовать следующий шаблон:

Select <список полей>;

From < связка таблиц >;

[Where < условие отбора записей>];

[Order by <ключи сортировки>].

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

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

Инструкция Order by используется для сортировки результата запроса по определенному ключу.

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

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

В приведенном шаблоне запроса на выборку инструкции Select и From являются обязательными, в то время как инструкции Where и Order by могут отсутствовать.

Перечисление списка полей начинается с ключевого поля Select и может включать имена полей физических таблиц, имена полей из других запросов (виртуальных таблиц) и выражения. Если одно и тоже имя используется в нескольких таблицах, в списке полей обязательно необходимо уточнить, из какой таблицы будет получено данное поле. При этом сначала указывается имя таблицы, а затем имя поля: [ИмяТаблицы].[ИмяПоля]. Квадратные скобки в данном случае являются символами, обозначающими, что используется системные имена. В списке полей могут также присутствовать выражения. При написании выражения могут использоваться константы различных типов, имена полей таблиц, входящих в запрос, функции (как системные, так и пользовательские). При включении выражения в текст запроса используется следующий шаблон: <выражение> as <псевдоним>. Сначала пишется само выражение, а затем после служебного слова Аs указывается псевдоним. Псевдонимы можно указать и для полей, которые не являются выражениями, чтобы заменить системные имена на более понятные. Например, для определения суммы поставки выражение будет выглядеть следующим образом: [Количество]*[Цена] as [Сумма].

В связке таблиц указываются имена всех таблиц и запросов и параметры их объединения. При указании параметров объединения указывается вид объединения: внутреннее, или внешнее. При создании внешнего соединения в обязательном порядке уточняется, какое это соединение: левое или правое. В условии объединения обязательно указываются имена полей, по которым будут связаны таблицы. При необходимости в запросе может быть указано условие отбора записей, например, Where[Сумма]>=100000. Приведенная команда выбирает все записи, сумма для которых превышает 100000. Условие отбора может быть составным. При этом используются логические операторы and, or, not.

Ключи сортировки позволяют упорядочить результаты запроса и вывести эти результаты на экран в определенном порядке. Упорядочить поставки по датам можно следующим образом: Order by[Дата Поставки].

Рис.5.4.1 Схема структуры предметной области базы данных “Поставки”

Следующим шагом по проектированию структуры предметной области является составление словаря имен. Сокращения из словаря имен будут использоваться для образования системных имен полей таблиц. Составим словарь имен для базы данных “Поставки”, результаты представим в табл. 5.4.1.

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

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

Для этого необходимо написать следующий запрос на языке SQL:

Select Поставка.КодПост, ДатаПост, НаимПост, АдрПост, Товар.КодТов, НаимТов,

Цена, КолТов, КолТов*Цена as Сумма

From (Поставщик Inner Join Поставка on Поставщик.КодПост = Поставка.КодПост)

Inner Join Товар on Товар.КодТов = Поставка.КодТов

Order by ДатаПост