 В цикле статей "SQL - Учимся писать скрипты" будет рассказано, как поэтапно создать скрипт для SQL, который бы для заданной таблицы получал бы информацию о транспортном средстве, номере прибора и так далее, научит составлять запросы, использовать базовые умения в Managment Studio
Подготовка: Для работы вам потребуется установить SQL Management Studio (можно expres), узнать сервер, имя и пароль для доступа к базе данных SQL. Все описание будет касаться именно среды SQL Management Studio. Желательно выбрать русифицированную версию. Статья направлена на привитие элементарных навыков построение SQL запросов и работе с Management Studio.
Занятие №1. Подключение, создание простых скриптов, базовые таблицы, базовое соединение разных таблиц в одну.
| При запуске Management Studio программа предложит вам создать новое соединение, в нем следует указать данные, необходимые для подключения к вашей копии SQL, которая отвечает за базу данных CyberFleet-а. Если вы не знаете данные о сервере, пользователе и пароле, то их можно "подсмотреть", как написано вот тут.
Для подключения так же можно использовать меню Файл - Подключиться к обозревателю объектов. После удачного подключения будет открыто окно "Обозреватель объектов". В верхней части которого будет указан адрес сервера, куда вы подключились. |
В базе данных содержится две базовых таблицы:
| Список транспорта | dbo.FLEET_Object | | Список приборов | dbo.SYS_DEV_Device |
Для просмотра сведений, содержащихся в таблице, можно быстро создать для этого скрипт при помощи мышки. Для этого, раскройте в "Обозревателе объектов" узел Базы данных, найдите запись "BN", "Таблицы", в списке найдите "dbo.FLEET_Object", нажмите на нее правую кнопку мышки и вы увидите примерно такое
|
Это контекстное меню, через него можно быстро создавать шаблоны скриптов для выполнения действий.
Если у вас в списке есть "Выбрать первые 1000 строк" - используйте именно данную возможность.
Аналогом ее является "Создать сценарий для таблицы" - "Используя SELECT". Однако, желательно перед выполнением скрипта вставить в него после "select" "top 1000", что бы ограничить вывод данных только первой 1000 записей.
Дело в том, что некоторые таблицы могут содержать и миллионы записей (например таблица sys_Dev_archivedata - содержит информацию о перемещении ТС), и получение такого объема данных на долго "положит" вашу базу данных. |
Выберите "Выбрать первые 1000 строк", в результате у вас откроется окно с шаблоном скрипта, и запустится на выполнение.
Посмотрим на окно более подробно:  | Любой запрос начинается со слова "SELECT"
далее обычно идет список колонок=данных, которые следует получать из таблицы, разделенных запятыми.
после идет указание имени таблицы после "FROM"
Если требуется получить не все строки таблицы, а только первые, то после SELECT пишется TOP, с указанием далее сколько именно строк будет получено. Так, например, SELECT TOP 1000 ... получит первую 1000 строк.
Под окном запросов, на закладке "Результаты" указан результат запроса (после его выполнения).
Если запрос использует конструкции print, то так же будут сообщения.
Для выполнения запроса можно использовать кнопку  , или жать F5
Для прерывания  или Alt+Break на клавиатуре
Текущее состояние запроса отображается в статусной строке снизу. Как и время прошедшее от запуска на его выполнение. |
Под окном, в статусной строке указано, что скрипт завершил свою работу, и указано время его выполнения.
После получения шаблона можно можно его изменить по вашему усмотрению, например, для начала, сократим количество выводимых столбцов, приведя запрос к такому виду:
| Скрипт 1 |
SELECT TOP 1000 [ObjectID]
FROM [BN].[dbo].[FLEET_Object] |
Как видно, запрос получается из таблицы [dbo].[FLEET_Object] база данных [BN] первые 1000 значений из колонок: [ObjectID], [Device_ID],[ObjectGarNum],[ObjectGosNum],[ObjectActive]
Запустите скрипт на выполнение, и в результате получается что-то типа такого  | Назначение столбцов в базе данных CyberFleet можно почитать тут.
Для целей статьи интересно вот что:
ObjectID - это внутренний номер (как инвентарный номер в бухгалтерии) о записи о нашем транспортном средстве. Если вы видите такое же название столбца в какой либо таблице, то значит оно указывает именно на таблицу со списком ТС, на указанный в значении номер.
Device_ID - это внутренний номер записи об абонентском терминале, установленном на данный прибор. Так, например для записи о ТС №100 (ObjectID=100, гос номер к378нн) следует смотреть запись о приборе 98 (Device_ID=98). Информация о приборе не содержится в данной таблице, а только "ссылка" на номер записи.
ObjectGosNum - государственный номер ТС
ObjectGarNum - гаражный номер ТС
ObjectActive - признак, что ТС активно в базе данных. При удалении пользователем записи о транспортном средстве в справочнике, в данном поле пишется 0, однако запись в справочнике остается. Потому данное поле следует в качестве фильтра, при получении списка ТС.
|
Для того, что бы ограничить список строк, получаемых из таблицы следует использовать фильтры. Фильтры пишутся после WHERE.
Модернизируем наш запрос так, что бы получить только транспорт, не удаленный в КиберФлите Для этого в конце запроса добавим предложение WHERE [ObjectActive]=1
| Скрипт 2 |
SELECT TOP 1000 [ObjectID] FROM [BN].[dbo].[FLEET_Object] |
в результате список транспорта будет ограничен только теми ТС, которые видны пользователю в справочнике. Теперь добавим условие на государственный номер - отберем только ТС, у которых в государственном номере есть цифра 9 для этого, после where [ObjectActive]=1 напишем and [ObjectGosNum] like '%9%'
| Скрипт 3 |
SELECT TOP 1000 [ObjectID] FROM [BN].[dbo].[FLEET_Object] where [ObjectActive]=1 and [ObjectGosNum] like '%9%' |
Как видим, два условия были соединены через "слово" "and" - это означает, что должны выполнять оба условия одновременно. Если "перевести" данный запрос на человеческий, то получится примерное такое:
Получи (select) первые 1000 строк (top 1000) из таблицы [dbo].[FLEET_Object] в базе данных BN (FROM), значения из колонок [ObjectID],[Device_ID],[ObjectGarNum],[ObjectGosNum],[ObjectActive], для строк которых (where) значение в колонке ObjectActive=1, и при этом (and), значение в колонке ObjectGosNum выглядит как: любые символы, "9", любые символы.
Теперь получим запрос для таблицы приборов, так же воспользовавшись правой кнопкой на таблице SYS_DEV_Device. В результате будет открыта новая закладка с запросом Удалим не нужные столбцы, и добавим условие по колонке [DeviceActive], приведем запрос к данному виду:
Скрипт 4
SELECT TOP 1000 [ID]
,[code]
,[tel_number]
,[DeviceActive]
FROM [BN].[dbo].[SYS_DEV_Device]
| ID - внутренний номер прибора в базе данных.
Во всех таблицах, в которых будет ссылка на данный прибор будет указан именно он. Обычно колонка связи с прибором называется Devce_id, или похожим образом
code - номер прибора
tel_number - телефон симкарты
DeviceActive - признак, что прибор виден в базе данных |
Примерный результат работы представлен на рисунке ниже:
 | Как видно из рисунка, прибору со внутренним номером (ID)=96 соответствует запись с серийным номером 00166096, 97=00166452, 98=00157791 и так далее.
Используя данную информацию можно "в голове" дополнить таблицу со списком ТС информацией о номерах терминалов.
Так, гос номер к378нн ссылается на Device_id=98, что соответствует прибору с кодом 00157791, а в655ук 68 ссылается на Device_ID=99, что соответствует прибору с кодом 00162747 (см. рисунок выше) |
Следующий шаг, это объединение этих двух таблиц в одну новую, посредством запроса.
Для этого, запрос должен опираться на две таблицы, при этом запросу нужно обязательно сказать, как именно должны быть связаны таблицы - значениям каких столбцов одной и другой таблицы как должны соответствовать.
Для этого, "скрипт 2" получения данных о списке ТС и дополним. перед словом where, вставим
inner join [BN].[dbo].[SYS_DEV_Device] on [BN].[dbo].[SYS_DEV_Device].id=[BN].[dbo].[FLEET_Object].device_id
Так же дополним список столбцов в верхней части (после select, перед from) из таблицы [dbo].[SYS_DEV_Device] столбцами [code] ,[tel_number], а список условий ограничим только [ObjectActive]=1
В результате должно получиться вот такое:
| Скрипт 5 |
[ObjectID] FROM [BN].[dbo].[FLEET_Object]
inner join [BN].[dbo].[SYS_DEV_Device] on [BN].[dbo].[SYS_DEV_Device].id=[BN].[dbo].[FLEET_Object].device_id |
Рассмотрим более подробно, что было сделано
после названия таблицы [BN].[dbo].[FLEET_Object] появилось предложение "inner join", после которого указано название таблицы приборов, с которой связывается таблица ТС. После стоит указание, значение в каких колонках должны совпадает: В таблице приборов значение из колонки ID должно быть равно значению из колонки device_id в таблице Машин.
Общий принцип соединения такой:
from Таблица1 inner join Таблица2 on Таблица1.Колонка=Таблица2.Колонка
Результат работы представлен на рисунке Результат Скрипта 5. На рисунке цветными блоками выделены колонки, относящиеся к соответствующей таблице.
И так, у нас получилось получить "скрестить" список Транспорта со списком приборов, в результате мы видим для каждой ТС запись о номере прибора - примерно так же делает и КиберФлит, когда показывает вам в главном окне информацию о списке приборов.
Это соединение двух базовых таблиц обычно используется во многих скриптах как их часть. Везде, где вы видите названия колонок ObjectID или DeviceID - значит речь идет о ссылке-связи с таблицей списка транспорта, или приборов.
И как мы видим, из списка транспорта можно выйти на связанный с ним список приборов, ну и соответственно наоборот.
Практическая работа: Попробуем для списка датчиков получить информацию о приборе (code будет достаточно). Датчики хранятся в таблице [dbo].[SYS_DEV_Sensor]. Щелчок правой кнопкой на таблице [dbo].[SYS_DEV_Sensor], "Выбрать первые 1000 строк"
Оставляем в списке колонки со 2 по 6, правим запятые между колонками. Вот что должно получиться:
| Скрипт 6.1 | SELECT TOP 1000 [device_id] ,[num] ,[sensor_type_id] ,[IsAlarm] ,[description]
FROM [BN].[dbo].[SYS_DEV_Sensor] |
Запускам, смотрим.
Ага, в таблице есть ссылка на запись о приборе, значит можно смело соединяться с таблицей приборов, указав равенство между значениями в соответствующих колонках. inner join bn.dbo.SYS_DEV_Device on [BN].[dbo].[SYS_DEV_Sensor].device_id=bn.dbo.SYS_DEV_Device.id
не забыв добавить вывод колонке code в список, после SELECT
| Скрипт 6.2, добавленные строки выделены цветом, удалим колонку [device_id] | FROM [BN].[dbo].[SYS_DEV_Sensor] inner join [bn].[dbo].[SYS_DEV_Device] on [BN].[dbo].[SYS_DEV_Sensor].[device_id]=[bn].[dbo].[SYS_DEV_Device].[id] |
Запускаем, смотрим что получилось:
В результате в таблице у нас данные о датчике, и в последней колонке номер АТ. Так как мы уже связались со список приборов, то теперь можем связаться и с машинами.
Для этого добавляем строку
inner join bn.dbo.FLEET_Object on [BN].[dbo].[SYS_DEV_Sensor].device_id=bn.dbo.FLEET_Object.Device_ID
И выводим гос номер ТС из колонки ObjectGosNum присоединенной таблицы
| Скрипт 6.3 Датчики с прибором и гос номером. Добавленные колонки и соответствующие таблицы выделены цветом. | SELECT TOP 1000 [num] ,[sensor_type_id] ,[IsAlarm] ,[description] ,code ,ObjectGosNum FROM [BN].[dbo].[SYS_DEV_Sensor] inner join [bn].[dbo].[SYS_DEV_Device] on [BN].[dbo].[SYS_DEV_Sensor].[device_id]=[bn].[dbo].[SYS_DEV_Device].[id] inner join [bn].[dbo].[FLEET_Object] on [BN].[dbo].[SYS_DEV_Sensor].[device_id]=bn.dbo.FLEET_Object.Device_ID |
В результате таблица дополнится сведениями не только о номере прибора, но и о гос номере ТС. Обращаю ваше внимание, что список ТС присоединился через связь с таблицей Датчиков (SYS_DEV_Sensor.device_id), а не через таблицу приборов (bn.dbo.SYS_DEV_Device.id). Т.е., если номер прибора (или какая еще информация о приборе) не интересны, то строки, отмеченные зеленым можно просто удалить, сократив скрипт до:
SELECT TOP 1000 [num] ,[sensor_type_id] ,[IsAlarm] ,[description] ,ObjectGosNum FROM [BN].[dbo].[SYS_DEV_Sensor] inner join [bn].[dbo].[FLEET_Object] on [BN].[dbo].[SYS_DEV_Sensor].[device_id]=bn.dbo.FLEET_Object.Device_ID
Источник: SQL, объединение таблиц, пример, CyberFleet, уроки, обучение |
