Основы SQL. Урок №1

Содержание:

1.       Базы данных 1С 8.3

2.       Соединение двух таблиц

3.       Несколько простых примеров в системе 1С:Предприятие 

1.    Базы данных 1С 8.3

Эта статья написана с целью на простых доступных примерах показать, что представляет собой SQL (Structured Query Language) – структурированный язык запросов.

База данных – это структурированный набор постоянно хранимых данных.

В реляционной базе данных информация хранится в виде двумерных таблиц.

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

Сотрудники

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

                Для решения данной задачи необходимо присвоить каждому сотруднику уникальный идентификатор. Такое значение, которое должно быть уникальным для каждого сотрудника, называется первичным ключом в базе (primary key). Каждая создаваемая таблица базы данных должна иметь первичный ключ. Он служит для логической идентификации отдельных строк. Добавим столбец для первичного ключа в нашу таблицу.

Сотрудники

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

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

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

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

Телефоны

Столбец id_num в таблице Телефоны называется внешним ключом (foreign key). Говорят, что он ссылается на первичный ключ таблицы Сотрудники. Ключи, на которые ссылаются внешние ключи, называются родительскими (parent) ключами. Если все значения внешнего ключа таблицы Телефоны ссылаются на значения, которые действительно присутствуют в таблице Сотрудники, то система обладает ссылочной целостностью (referential integrity).

                Для таблицы Телефоны также необходим первичный ключ – его должна иметь каждая таблица. Мы не можем использовать id_num, поскольку он не является уникальным (повторяется для телефонных номеров одного сотрудника, как показано в таблице для сотрудника 1008 по имени Васина).

                Первичный или внешний ключ необязательно должен состоять из одного столбца. Можно скомбинировать столбцы id_num и phone. Такая комбинация будет уникальной. Итак, комбинация id_numи phone является логическим первичным ключом для таблицы Телефоны. Ключ, состоящий более чем из одного столбца, в разных СУБД называется составным (composite). 

2.    Соединение двух таблиц

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

                Операция извлечения информации из базы данных называется запросом (query).

В 70-х годах XX века, был создан специальный язык SEQUEL, позволявший относительно просто управлять данными в этой СУБД. Аббревиатура SEQUEL расшифровывалась как Structured English QUEry Language — «структурированный английский язык запросов».

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

Позже он был переименован в SQL – Structured Query Language – структурированный язык запросов. Позднее, с принятием стандартов, SQL превратился в самостоятельный язык, применяемый в разработке баз данных.

В основном SQL-запросы реализуются с помощью оператора SEL ECT. Запрос, извлекающий данные из более чем одной таблицы путем сопоставления столбцов одной таблицы столбцам других таблиц, называется соединением (join).

В общем виде синтаксис минимальной команды такой:

SELECT имена полей таблиц FROM имена таблиц

В 1С обычно пишут запросы 1С 8.3 в русскоязычном синтаксисе :

ВЫБРАТЬ имена полей таблиц ИЗ имена таблиц

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

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 

3.    Несколько простых примеров в системе 1С:Предприятие

Система 1С:Предприятие. Создадим справочники Сотрудники и Города. Заполним их данными.

Сотрудники

Города

Здесь связь между таблицами Сотрудники – Города, характеризуется как Многие к Одному (m:1), то есть каждый сотрудник может жить только в одном городе.

Если бы сотрудник мог жить в нескольких городах, то связь была бы Многие ко Многим (m:m).

Если в одном городе может проживать не более одного сотрудника, то связь получается Один к Одному (1:1)

Откроем консоль запросов. Создадим с помощью Конструктора запроса следующий запрос: выберем Наименования сотрудников из справочника Сотрудники.

ВЫБРАТЬ

                Сотрудники.Наименование КАК Наименование

ИЗ

                Справочник.Сотрудники КАК Сотрудники

Заменим служебные слова в запросе на оригинал языка SQL:

SELECT

                Сотрудники.Наименование AS Наименование

FR OM

                Справочник.Сотрудники AS Сотрудники

Результат получаем тот же.

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

ВЫБРАТЬ

                Сотрудники.Наименование КАК Сотрудник,

                Города.Наименование КАК Город

ИЗ

                Справочник.Сотрудники КАК Сотрудники,

                Справочник.Города КАК Города

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

Давайте посмотрим, как будет строиться результирующая таблица пошагово:

Сначала будет выбрано поле Наименование первой записи таблицы Сотрудники

Затем рядом, в этой же строке, будет выведено поле Наименование таблицы Город

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

Движение же по таблице Города продолжается, поэтому будет выбрано поле Наименование следующей строки (записи) этой таблицы

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

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

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

Для значения Васина, выбираются все оставшиеся города

Теперь выведем всю результирующую таблицу:

Для каждого сотрудника (5 сотрудников) выводятся все города (3 города), в итоге получается 5*3 = 15 записей в результирующей таблице.

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

Если выбрать сначала города, то на каждый город выводятся все сотрудники из второй таблицы (Сотрудники).

ВЫБРАТЬ

                Города.Наименование КАК Город,

                Сотрудники.Наименование КАК Сотрудник

ИЗ

                Справочник.Города КАК Города,

                Справочник.Сотрудники КАК Сотрудники

Количество записей в результате 3*5 = 15. От перемены расположения таблиц результат не меняется!

Теперь соединим таблицы по ключевому полю, предусмотрительно для этого созданному: Код города = ID_C

ВЫБРАТЬ

                Сотрудники.Наименование КАК Сотрудник,

                Города.Наименование КАК Город

ИЗ

                Справочник.Города КАК Города

                               ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники КАК Сотрудники

                               ПО (Сотрудники.ID_C = Города.ID_C)

Отсюда сразу становится видно, кто где проживает.

Добавим еще двух сотрудников в таблицу Сотрудники и зададим им коды городов, которых нет в таблице Города.

Обратим внимание на связь. Строка “Города Левое” означает, что с таблицей Города происходит левое соединение другой таблицы. В данном случае она находится на строку выше и является более приоритетной. Это таблица Сотрудники. Приоритетность этой таблицы выражается в более близком расположении к корневому элементу Таблицы. А менее приоритетная таблица Города раскрывается из элемента Сотрудники. Таким образом выделенная строка означает, что таблица Города расположена … справа.

Обязательно! в условии соединения

Сотрудники.ID_C = Города.ID_С

типы сравниваемых полей должны быть полностью идентичны!

Если слева у нас тип Строка(10), то справа тоже должен быть тип Строка (10).

Например, если слева тип Строка(10), а справа стоит тип Строка(9), то это соединение некорректно, даже если в сравниваемых полях по 9 символов!

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

ЛЕВОЕ СОЕДИНЕНИЕ наиболее распространенное среди всех видов соединений. Объясняется это тем, что текст мы читаем слева направо, и первую таблицу мы располагаем сначала, а затем располагаем следующую таблицу. Нам же не придет в голову расположить первую таблицу справа, это неудобно.

Нам не важно, есть ли соответствие города какому-либо сотруднику. Нам принципиально важно, чтобы из таблицы Сотрудники были выбраны все сотрудники, независимо от того, есть ли у них соответствие поля с кодом города в таблице Города.

ВЫБРАТЬ

                Сотрудники.Наименование КАК Наименование,

                Города.Наименование КАК Наименование1

ИЗ

                Справочник.Сотрудники КАК Сотрудники

                               ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Города КАК Города

                               ПО (Сотрудники.ID_C = Города.ID_С)

В результате видно, что для новых двух сотрудников нет городов, то есть в таблице Города нет городов с кодом ID_C равным 5 и 7. Какой же тип полей, в которых нет городов? Типа у этих полей нет. Тип у них – неопределенный. В SQL этот тип обозначается как NULL.

Выполним такое действие: в запросе в условии соединения поменяем местами сравниваемые поля Города.ID_С = Сотрудники.ID_C

ВЫБРАТЬ

                Сотрудники.Наименование КАК Наименование,

                Города.Наименование КАК Наименование1

ИЗ

                Справочник.Сотрудники КАК Сотрудники

                               ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Города КАК Города

                               ПО (Города.ID_С = Сотрудники.ID_C)

Как видим, результат запроса не изменился.

Теперь создадим запрос, где нам важно вывести все города.

ВЫБРАТЬ

Города.Наименование КАК Наименование1,

                Сотрудники.Наименование КАК Наименование         

ИЗ

                Справочник.Города КАК Города

                               ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники КАК Сотрудники

                               ПО (Города.ID_С = Сотрудники.ID_C)

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

Проверим это следующим образом: переназначим для сотрудников Петров и Петренко код города = 1 (Москва). То есть городу Тула не соответствует ни один сотрудник. И выполним запрос еще раз.

Условие выполняется! Каждый город выводится минимум один раз, а поскольку городу Тула никто не соответствует, то ему соответствует поле с типом NULL.

Поменяем ЛЕВОЕ СОЕДИНЕНИЕ на ПРАВОЕ СОЕДИНЕНИЕ

ВЫБРАТЬ

                               Сотрудники.Наименование КАК Наименование,

                               Города.Наименование КАК Наименование1

ИЗ

                Справочник.Города КАК Города

                               ПРАВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники КАК Сотрудники

                               ПО (Города.ID_С = Сотрудники.ID_C)

Сотрудники выводятся все, город Тула не выводится – ему не соответствует ни один сотрудник, а приоритет у таблицы Сотрудники.

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

Выделенная строка означает, что мы соединяемся с таблицей Сотрудники правым соединением. То есть другая таблица (в нашем случае таблица Города) располагается теперь справа! А слева располагается таблица Сотрудники. Таким экспериментальным путем узнаем, что приоритет отдается таблице, располагающейся все равно слева. Это видно по результату запроса.

ПРАВОЕ СОЕДИНЕНИЕ удобно использовать при отладке запроса, когда надо поменять приоритет таблиц. Это гораздо быстрее, чем переписывать все соединение. Также оно используется в других отдельных случаях (это здесь мы рассматривать не будем).

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

Еще один пример

Есть три таблицы A(3 записи), B(4 записи)и C (5 записей). Если соединяем таблицы A и B, то нет необходимости соединять A c B и одновременно A с C, поскольку после соединения таблиц A и B результат можно рассматривать как таблицу. И достаточно одного соединения таблицы C с таблицей (A-B). Для этого соединяем C c A ИЛИ C с B.

Если выводить все таблицы A, B и C без связей, то получаем 3*4*5 = 60 записей.

Пусть связь между таблицами будет Один к Одному (1:1), тогда:

Если соединяем (A-B) по приоритету A, получим таблицу (AB) = 3 записи. Добавив таблицу C (без соединения) получаем 3*5 = 15 записей. Если соединим (AB) и С по приоритету таблицы (AB)получим 3 записи.

Если соединяем (A-B) по приоритету B, получим таблицу (BA) = 4 записи. Добавив таблицу C (без соединения) получаем 4*5 = 20 записей. Если соединим (BA) и С по приоритету таблицы (BA) получим 4 записи.

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

Задания для самостоятельного решения

Задание 1.

Что представляет собой запрос?

Расскажите про первичный ключ и внешний ключ.

Задание 2.

Выберите поля Наименование и ID_C из таблицы Сотрудники

Задание 3.

Соедините таблицу Города с таблицей Сотрудники правым соединением по полю ID_C. При этом выведите поля ID_C и Наименование из таблицы Города и только Наименование из таблицы Сотрудники

Задание 4.

Соедините таблицу Сотрудники с таблицей Города левым соединением по полю ID_C, при этом выведите поля ID_E, Наименование из таблицы Сотрудники и только Наименование из таблицы Города

Задание 5.

Добавьте город Астрахань с кодом 7.

Удалите из таблицы Сотрудники фамилии Петренко и Петров.

Добавьте в таблицу Сотрудники фамилии Мишина, код города = 7,

Федоров, код города = 3.

Соедините таблицы Сотрудники и Города левым соединением, выведите все поля.

///////////////////////////////////////////////////////////

Есть 3 таблицы: A (3 записи), B (5 записей), С (7 записей).

Задание 1.

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

Задание 2.

Те же 3 таблицы. Связь между таблицами Один к Одному (1:1)

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

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

Задание 3.

Те же 3 таблицы. Связь между таблицами Один к Одному (1:1)

Выбираем данные из таблиц A и C без связи. Соединяем полученную таблицу с таблицей B левым соединением. Сколько записей будет в результирующей таблице? А если правым?

Задание 4.

Те же 3 таблицы. Связь между таблицами Один к Одному (1:1)

Соединяем таблицу С и B левым соединением. Таблицу А соединяем с полученной таблицей правым соединением. Сколько в итоге записей? А левым соединением?

Задание 5. SuperTask

Те же 3 таблицы. Связь между таблицами B и A Многие к Одному (m:1). Таблица C связана с A и B (1:1)

В таблице B есть только две записи, относящиеся к одному и тому же элементу таблицы A.

И в таблице B есть одна запись, не связанная ни с чем.

Соединяем таблицу B c A левым соединением.

К полученной таблице (BA)соединяем таблицу C правым соединением.

Сколько записей в результате?

Ответ дать без создания таблиц в базе.

Ответы будут даны в уроке №2.

Специалист компании ООО «Кодерлайн»

Ильдар Мингалеев