Как составлять запросы в sql

Руководство по SQL: Как лучше писать запросы (Часть 1)

Предлагаю вашему вниманию перевод статьи Karlijn Willems SQL Tutorial: How To Write Better Queries». Оригинал доступен по ссылке https://www.datacamp.com/community/tutorials/sql-tutorial-query. Узнайте о антипаттернах, планах выполнения, time complexity, настройке запросов и оптимизации в SQL.

Язык структурированных запросов (SQL) является незаменимым навыком в индустрии информатики, и вообще говоря, изучение этого навыка относительно просто. Однако большинство забывают, что SQL — это не только написание запросов, это всего лишь первый шаг дальше по дороге. Обеспечение производительности запросов или их соответствия контексту, в котором вы работаете, — это совсем другая вещь.

Во-первых, вы начнете с краткого обзора важности обучения SQL для работы в области науки о данных;
Далее вы сначала узнаете о том, как выполняется обработка и выполнение запросов SQL, чтобы понять важность создания качественных запросов. Конкретнее, вы увидите, что запрос анализируется, переписывается, оптимизируется и окончательно оценивается.
С учетом этого, вы не только перейдете к некоторым антипаттернам запросов, которые начинающие делают при написании запросов, но и узнаете больше об альтернативах и решениях этих возможных ошибок; Кроме того, вы узнаете больше о методическом подходе к запросам на основе набора.
Вы также увидите, что эти антипаттерны вытекают из проблем производительности и что, помимо «ручного» подхода к улучшению SQL-запросов, вы можете анализировать свои запросы также более структурированным, углубленным способом, используя некоторые другие инструменты, которые помогают увидеть план запроса; И,
Вы вкратце узнаете о time complexity и big O notation, для получения представления о сложности плана выполнения во времени перед выполнением запроса;
Вы кратко узнаете о том, как оптимизировать запрос.

Почему следует изучать SQL для работы с данными?

SQL далеко не мертв: это один из самых востребованных навыков, который вы находите в описаниях должностей из индустрии обработки и анализа данных, независимо от того, претендуете ли вы на аналитику данных, инженера данных, специалиста по данным или на любые другие роли. Это подтверждают 70% респондентов опроса О ‘Рейли (O’ Reilly Data Science Salary Survey) за 2016 год, которые указывают, что используют SQL в своем профессиональном контексте. Более того, в этом опросе SQL выделяется выше языков программирования R (57%) и Python (54%).

Вы получаете картину: SQL — это необходимый навык, когда вы работаете над получением работы в индустрии информатики.

Неплохо для языка, который был разработан в начале 1970-х, верно?

Но почему именно так часто используется? И почему он не умер, несмотря на то, что он существует так долго?

Есть несколько причин: одной из первых причин могло бы стать то, что компании в основном хранят данные в реляционных системах управления базами данных (RDBMS) или в реляционных системах управления потоками данных (RDSMS), и для доступа к этим данным нужен SQL. SQL — это lingua franca данных: он дает возможность взаимодействовать практически с любой базой данных или даже строить свою собственную локально!

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

Кроме того, можно с уверенностью сказать, что к SQL также присоединились более новые технологии, такие как Hive, интерфейс языка запросов, похожий на SQL, для запросов и управления большими наборами данных, или Spark SQL, который можно использовать для выполнения запросов SQL. Опять же, SQL, который вы там найдете, будет отличаться от стандарта, который вы могли бы узнать, но кривая обучения будет значительно проще.

Если вы хотите провести сравнение, рассматривайте его как обучение линейной алгебре: приложив все эти усилия в этот один предмет, вы знаете, что вы сможете использовать его, чтобы также освоить машинное обучение!

Короче говоря, вот почему вы должны изучить этот язык запросов:

Его довольно легко освоить, даже для новичков. Кривая обучения довольно проста и постепенна, поэтому вы будете писать запросы в кратчайшие сроки.
Он следует принципу «учись один раз, используй везде», так что это отличное вложение твоего времени!
Это отличное дополнение к языкам программирования; В некоторых случаях написание запроса даже предпочтительнее написания кода, потому что он более производительный!
.

Чего вы все еще ждете? 🙂

Обработка SQL и выполнение запросов

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

Сначала запрос разбирается в «дерево разбора» (parse tree); Запрос анализируется на предмет соответствия синтаксическим и семантическим требованиям. Синтаксический анализатор создает внутреннее представление входного запроса. Затем эти выходные данные передаются в механизм перезаписи.

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

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

Теперь вы, вероятно, задаетесь вопросом, что считается «хорошим планом запроса».

Как вы уже читали, качество стоимости плана играет немаловажную роль. Более конкретно, такие вещи, как количество дисковых операций ввода-вывода (disk I/Os), которые требуются для оценки плана, стоимость CPU плана и общее время отклика, которое может наблюдать клиент базы данных, и общее время выполнения, имеют важное значение. Вот тут-то и возникнет понятие сложности времени (time complexity). Подробнее об этом вы узнаете позже.

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

Написание SQL-запросов

Из предыдущего раздела, возможно, не стало ясно, что принцип Garbage In, Garbage Out (GIGO) естественным образом проявляется в процессе обработки и выполнения запроса: тот, кто формулирует запрос, также имеет ключи к производительности ваших запросов SQL. Если оптимизатор получит плохо сформулированный запрос, он сможет сделать только столько же…

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

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

Условие WHERE ;
Любые ключевые слова INNER JOIN или LEFT JOIN ; А также,
Условие HAVING ;

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

Тем не менее, вы также должны понимать, что производительность — это нечто, что должно стать значимым. Однако просто сказать, что эти предложения и ключевые слова плохи — это не то, что нужно, когда вы думаете о производительности SQL. Наличие предложения WHERE или HAVING в запросе не обязательно означает, что это плохой запрос…

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

1. Извлекате только необходимые данные

Умозаключение «чем больше данных, тем лучше» — не обязательно должна соблюдаться при написании SQL: вы рискуете не только запутаться, получив больше данных, чем вам действительно нужно, но и производительность может пострадать от того, что ваш запрос получает слишком много данных.

Вот почему, как правило, стоит обратить внимание на оператор SELECT , предложение DISTINCT и оператор LIKE .

Оператор SELECT

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

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

Помните, что коррелированный подзапрос является подзапросом, использующим значения из внешнего запроса. И обратите внимание, что, несмотря на то, что NULL может работать в этом контексте как «константа», это очень запутанно!

Рассмотрим следующий пример, чтобы понять, что подразумевается под использованием константы:

SELECT driverslicensenr, name FROM Drivers WHERE EXISTS (SELECT '1' FROM Fines WHERE fines.driverslicensenr = drivers.driverslicensenr);

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

SELECT driverslicensenr, name FROM drivers INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr;

Операция DISTINCT

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

Оператор LIKE

При использовании оператора LIKE в запросе индекс не используется, если шаблон начинается с % или _ . Это не позволит базе данных использовать индекс (если он существует). Конечно, с другой точки зрения, можно также утверждать, что этот тип запроса потенциально оставляет возможность для получения слишком большого количества записей, которые не обязательно удовлетворяют цели запроса.

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

2. Ограничьте свои результаты

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

Операторы TOP , LIMIT и ROWNUM

Можно добавить операторы LIMIT или TOP в запросы, чтобы задать максимальное число строк для результирующего набора. Вот несколько примеров:

 SELECT TOP 3 * FROM Drivers;

Обратите внимание, что вы можете дополнительно указать PERCENT , например, если вы измените первую строку запроса с помощью SELECT TOP 50 PERCENT * .

SELECT driverslicensenr, name FROM Drivers LIMIT 2;

Кроме того, можно добавить предложение ROWNUM , эквивалентное использованию LIMIT в запросе:

SELECT * FROM Drivers WHERE driverslicensenr = 123456 AND ROWNUM 3;

Преобразования типов данных

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

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

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

3. Не делайте запросы более сложными, чем они должны быть

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

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

Оператор OR

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

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

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

Рассмотрим следующий запрос:

SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr = 123456 OR driverslicensenr = 678910 OR driverslicensenr = 345678;

Оператор можно заменить на:

Условие с IN ; или

SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr IN (123456, 678910, 345678);

Две инструкции SELECT с UNION .

Совет: здесь вы должны быть осторожны, чтобы не использовать ненужную операцию UNION , потому что вы просматриваете одну и ту же таблицу несколько раз. В то же время вы должны понимать, что когда вы используете UNION в своем запросе, время выполнения увеличивается. Альтернативы операции UNION : переформулировка запроса таким образом, чтобы все условия были помещены в одну инструкцию SELECT , или использование OUTER JOIN вместо UNION .

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

Оператор NOT

Когда ваш запрос содержит оператор NOT , вполне вероятно, что индекс не используется, как и с оператором OR . Это неизбежно замедлит ваш запрос. Если вы не знаете, что здесь подразумевается, рассмотрите следующий запрос:

SELECT driverslicensenr, name FROM Drivers WHERE NOT (year > 1980);

Этот запрос, безусловно, будет выполняться медленнее, чем вы, возможно, ожидаете, в основном потому, что он сформулирован гораздо сложнее, чем может быть: в таких случаях, как этот, лучше всего искать альтернативу. Рассмотрите возможность замены NOT операторами сравнения, такими как > , <> или !> ; Приведенный выше пример действительно может быть переписан и выглядеть примерно так:

SELECT driverslicensenr, name FROM Drivers WHERE year 1980;

Это уже выглядит лучше, не так ли?

Оператор AND

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

SELECT driverslicensenr, name FROM Drivers WHERE year >= 1960 AND year 1980;

Лучше переписать этот запрос, используя оператор BETWEEN :

SELECT driverslicensenr, name FROM Drivers WHERE year BETWEEN 1960 AND 1980;

Операторы ANY и ALL

Кроме того, операторы ANY и ALL — это те операторы, с которыми вам следует быть осторожным, поскольку, если включить их в свои запросы, индекс не будет использоваться. Здесь пригодятся альтернативные функции агрегирования, такие как MIN или MAX .

Совет: в тех случаях, когда вы используете предлагаемые альтернативы, вы должны знать о том, что все функции агрегации, такие как SUM , AVG , MIN , MAX над многими строками, могут привести к длительному запросу. В таких случаях можно попытаться минимизировать количество строк для обработки или предварительно вычислить эти значения. Вы еще раз видите, что важно знать о своей среде, своей цели запроса,… Когда вы принимаете решение о том, какой запрос использовать!

Изолируйте столбцы в условиях

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

SELECT driverslicensenr, name FROM Drivers WHERE year + 10 = 1980;

Это выглядит забавно, а? Вместо этого попробуйте пересмотреть расчет и переписать запрос примерно так:

SELECT driverslicensenr, name FROM Drivers WHERE year = 1970;

4. Отсутствие грубой силы

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

Порядок таблиц в соединениях

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

Избыточные условия при соединениях

При добавлении слишком большого количества условий к соединениям SQL обязан выбрать определенный путь. Однако может быть, что этот путь не всегда является более эффективным.

Условие HAVING

Условие HAVING было первоначально добавлено в SQL, так как ключевое слово WHERE не могло использоваться с агрегатными функциями. HAVING обычно используется с операцией GROUP BY , чтобы ограничить группы возвращаемых строк только теми, которые удовлетворяют определенным условиям. Однако, если это условие используется в запросе, индекс не используется, что, как вы уже знаете, может привести к тому, что запрос на самом деле не так хорошо работает.

Если вы ищете альтернативу, попробуйте использовать условие WHERE .

Рассмотрим следующие запросы:

SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state

SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state

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

Читать:

Yandex go доставка как работает

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

Следует отметить, что различие между этими двумя условиями заключается в том, что предложение WHERE вводит условие для отдельных строк, в то время как предложение HAVING вводит условие для агрегаций или результатов выбора, где один результат, такой как MIN , MAX , SUM ,… был создан из нескольких строк.

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

Этот список был лишь небольшим обзором некоторых антипаттернов и советов, которые, надеюсь, помогут начинающим; Если вы хотите получить представление о том, что более старшие разработчики считают наиболее частыми антиобразцами, ознакомьтесь Часть 2)

Скрипты Simple-Scada

В базе данных можно создавать таблицы, добавлять в них строки с данными, удалять, редактировать их и т.д. Все эти действия выполняются через SQL-запросы к БД. Например, чтобы добавить новую строку в таблицу «my_table» с двумя целочисленными столбцами, нужно выполнить соответствующий SQL-запрос:

INSERT INTO `my_table` (col1, col2) VALUES (11, 65);

Чтобы получить содержимое таблицы «my_table», нужно выполнить SQL-запрос на выборку:

SELECT * FROM `my_table`;

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

Важно! Обработать ошибки в SQL-запросах можно в скрипе «Ошибка SQL-запроса», например выдать ошибку в компонент «Текст». Также, для отладки скриптов работы с БД можно активировать опцию «Лог пользовательских SQL-запросов», тогда все пользовательские SQL-запросы будут записываться в лог-файл сервера. Данную опцию можно использовать только для отладки проекта, иначе она будет создавать лишнюю нагрузку на сервер скады .

Рекомендуем предварительно убедиться в том, что запрос не содержит ошибок. Например, чтобы составить и проверить SQL-запрос для MySQL, можно использовать MySQLWorkbench.

Когда SQL-запрос подготовлен и проверен, можно вызвать его из скрипта Simple-Scada. Для выполнения пользовательских SQL-запросов в Simple-Scada используется процедура RunSQL — в нее нужно передать код SQL-запроса и скада автоматически отправит этот запрос на выполнение. Допустим у нас имеется следующий SQL-запрос:

INSERT INTO `my_table` (col1, col2) VALUES (11, 65);

Требуется выполнить его, когда пользователь нажмет на кнопку. Для этого нужно выделить кнопку и на событие OnClick написать скрипт со следующим кодом:

var
aQuery: string ;
begin
< Формируем запрос к БД >
aQuery := ‘INSERT INTO `my_table` (col1, col2) VALUES (11, 65);’ ;
< Отправляем запрос на выполнение c тегом = 0>
RunSQL(aQuery, nil , 0 );
end .

Сначала мы записали текст SQL-запроса в строковую переменную aQuery, а затем вызвали процедуру RunSQL с этой строковой переменной. Далее, скада автоматически отправит наш запрос в БД. Если запрос выполнится успешно (без ошибок), то скада вызовет все скрипты с типом события «Выполнен SQL-запрос». Если при выполнении запроса что-то пойдет не так (например, в запросе имеется ошибка), то скада вызовет скрипты с типом события «Ошибка SQL-запроса».

Порядок выполнения запросов

Все запросы к БД выполняются асинхронно. БД выполняет каждый запрос в отдельном потоке, т.е. если отправить в БД несколько запросов подряд, то они будут выполняться одновременно в разных потоках и то, какой запрос выполнится быстрее зависит от множества факторов, например нагрузки на процессор, нагрузки на саму БД, количества других запросов выполняющихся в данный момент и т.д. Поэтому, если обязательно требуется выполнить запросы последовательно, то нужно отправить в БД первый запрос, пометив его уникальным тегом. Затем в скрипте «Выполнен SQL-запрос» необходимо проверить, что выполнился запрос помеченный нужным тегом(if DataSet.Tag = ?? then), после чего можно отправить в БД второй запрос.

Одинарные кавычки в SQL-запросах

Пусть мы хотим выполнить такой SQL-запрос: » INSERT INTO `my_table` (col1, col2) VALUES (‘привет’, ‘мир’); «. В этом запросе есть одинарные кавычки, в них мы передаём строковые константы (‘привет’ и ‘мир’). Теперь, если мы подставим этот запрос в процедуру RunSQL, то получим ошибку компилятора, например:

var
aQuery: string ;
begin
< Формируем запрос к БД >
aQuery := ‘INSERT INTO `my_table` (col1, col2) VALUES (‘ привет ‘, ‘ мир ‘);’ ; // ошибка синтаксиса!
< Отправляем запрос на выполнение c тегом = 0>
RunSQL(aQuery, nil , 0 );
end .

Дело в том, что SQL-запросы передаются в RunSQL в виде строки, а строки должны быть заключены в одинарные кавычки. Наши одинарные кавычки внутри SQL-запроса мешают компилятору правильно воспринимать строку (это видно даже по подсветке синтаксиса в примере выше). Чтобы решить проблему, нужно продублировать одинарные кавычки в SQL-запросе, вот так:

var
aQuery: string ;
begin
< Формируем запрос к БД >
aQuery := ‘INSERT INTO `my_table` (col1, col2) VALUES (»привет», »мир»);’ ;
< Отправляем запрос на выполнение c тегом = 0>
RunSQL(aQuery, nil , 0 );
end .

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

Есть ещё один вариант для решения проблемы с одинарными кавычками. Можно использовать функцию QuotedStr, которая добавляет одинарные кавычки по краям строки. Например:

var
aQuery: string ;
begin
< Формируем запрос к БД >
aQuery := ‘INSERT INTO `my_table` (col1, col2) VALUES (‘ + QuotedStr( ‘привет’ ) + ‘, ‘ + QuotedStr( ‘мир’ ) + ‘);’ ;
< Отправляем запрос на выполнение c тегом = 0>
RunSQL(aQuery, nil , 0 );
end .

Длинные SQL-запросы в коде

Пусть у нас есть длинный SQL-запрос: » INSERT INTO `my_table` (col1, col2, col3, col4, col5, col6) VALUES (11, 65, 25.15, ‘текст1’, ‘текст2’, ‘текст3’); «. Если вписать его одной строкой, то на экране может просто не хватить места по ширине из-за чего придётся пользоваться горизонтальным скроллом. Правильнее будет разбить текст запроса на три строки, каждую строку заключить в одинарные кавычки и сложить их знаком «+». Вот так:

var
aQuery: string ;
begin
< Формируем запрос к БД >
aQuery := ‘INSERT INTO `my_table` ‘ +
‘(col1, col2, col3, col4, col5, col6) VALUES ‘ +
‘(11, 65, 25.15, »текст1», »текст2», »текст3»);’ ;
< Отправляем запрос на выполнение c тегом = 0>
RunSQL(aQuery, nil , 0 );
end .

Разбиение на строки полезно, так как улучшает читаемость кода.

Как понять что запрос выполнился

Пусть в проекте есть кнопка, по нажатию на которую в таблице «my_table» создаётся новая строка. Т.е. на событие OnClick написан такой код:

Теперь мы добавили на мнемосхему компонент текст с именем «Text1» и хотим, чтобы после выполнения нашего SQL-запроса текст изменился на «Мой запрос выполнен!». Тогда мы должны, во-первых, при вызове RunSQL пометить наш запрос каким-то уникальным тегом, который больше никогда нами в RunSQL не использовался, например 55:

var
aQuery: string ;
begin
< Формируем запрос к БД >
aQuery := ‘INSERT INTO `my_table` (col1, col2) VALUES (11, 65);’ ;
< Отправляем запрос на выполнение c тегом = 55 >
RunSQL(aQuery, nil , 55 );
end .

Во-вторых, необходимо создать в меню скриптов новый скрипт с типом события «Выполнен SQL-запрос». Такой скрипт будет вызываться скада-системой каждый раз, когда выполнился любой пользовательский SQL-запрос. Также, этот скрипт будет содержать параметр «DataSet» — результат выполнения запроса. У параметра DataSet имеется свойство Tag. Это и есть тот самый тег, который мы назначили при выполнении RunSQL. Таким образом, по значению тега мы можем узнать в скрипте какой именно из наших SQL-запросов выполнился. Например:

begin
if DataSet.Tag = 55 then
Text1.Text := ‘Запрос выполнен!’ ;
end .

Блокировка кнопки на время выполнения запроса

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

Далее, создадим новый скрипт с типом события «Выполнен SQL-запрос» и следующим кодом:

begin
if DataSet.Tag = 77 then // если запрос помеченный тегом 77 выполнен, то
Button1.Enabled := True; // разблокируем кнопку
end .

Метод RunSQL компонента «Таблица»

У компонента «Таблица» есть свой внутренний метод RunSQL, который удобно использовать для того чтобы отобразить результат выполнения SQL-запроса в таблице на мнемосхеме. Например можно создать на мнемосхеме таблицу с именем «Table1», разместить рядом кнопку «Считать» и на событие OnClick кнопки написать такой код:

Теперь при нажатии на кнопку «Считать» скада выполнит SQL-запрос, а после выполнения автоматически отобразит результат в таблице Table1. Если запрос выполнится с ошибкой, то текст ошибки отобразится в таблице. Другие примеры для метода RunSQL таблицы можно найти по ссылке.

Работа с выборкой из множества строк

Пример работы с выборкой из множества строк можно найти по ссылке.

SQL запросы (версия 3.xx)/1C

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

Поддерживаются SQL — операторы:

select или ВЫБРАТЬ — выборка данных. В списке полей можно указать только * — все поля. Пример: ВЫБРАТЬ ПЕРВЫЕ 10 * ИЗ Справочник.Контрагенты

where или ГДЕ — позволяет указать условие выборки. Пример: select * from Справочник.Контрагенты where Наименование = «Тест»

Помимо этого SQL-запросы к этому источнику данных могут содержать комментарии в виде /* */ и содержать несколько запросов разделенных символами // на новой строке.

1 Пример SQL-запросов 1C на выборку данных
2 Пример SQL-запросов 1C на изменение данных
3 Использование функций для доступа к данным 1C
4 Полезные примеры для работы с 1С

Пример SQL-запросов 1C на выборку данных

SQL-запросы на выборку данных полностью аналогичны запросам Новый Запрос на языке 1C. Например, запрос на выборку контрагентов с номерами телефонов выглядит так:

ВЫБРАТЬ * ИЗ Справочник.Контрагенты КАК Контрагенты ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КонтактнаяИнформация КАК Телефон ПО (Телефон.Объект = Контрагенты.Ссылка) И (Телефон.Тип = ЗНАЧЕНИЕ(Перечисление.ТипыКонтактнойИнформации.Телефон)) И (Телефон.Вид = ЗНАЧЕНИЕ(Справочник.ВидыКонтактнойИнформации.ТелефонКонтрагента)) ГДЕ Телефон.Представление ЕСТЬ НЕ null УПОРЯДОЧИТЬ ПО Контрагенты.Наименование

Или на привычном языке SQL этот же запрос выглядит так:

select * from Справочник.Контрагенты AS Контрагенты left join РегистрСведений.КонтактнаяИнформация as Телефон on (Телефон.Объект = Контрагенты.Ссылка) and (Телефон.Тип = VALUE(Перечисление.ТипыКонтактнойИнформации.Телефон)) and (Телефон.Вид = VALUE(Справочник.ВидыКонтактнойИнформации.ТелефонКонтрагента)) where Телефон.Представление is not null order by Контрагенты.Наименование

Более подробно о том как составлять запросы на выборку данных 1C можно посмотреть здесь.

Пример SQL-запросов 1C на изменение данных

SQL-запросы на изменение данных не поддерживаются 1C, поэтому функционал таких запросов эмулируется средой Call Office. Следует учитывать, что из-за особенностей 1C, имена таблиц в запросах на выборку данных выглядят одним образом (например, Справочник.Контрагенты), а при работе на изменение данных — другим (например, Справочники.Контрагенты).

Поэтому, SQL-запрос на запись результатов звонка в поле Комментарий объекта Контрагенты выглядит так:

update Справочники.Контрагенты set Комментарий = [ColumnResult] where Наименование = '[Наименование]'

Использование функций для доступа к данным 1C

Если возможностей SQL-запросов недостаточно, то вы можете работать напрямую с функциями объектов 1C на языке javascript. Такие функции необходимо заключать между символами /! и !/. Например, следующая функция найдет контрагента с кодом 000000001, запишет 11455 в его ИНН:

/! function (Connection) < var Find = Connection.Справочники.Контрагенты.НайтиПоКоду ('000000001'); if (Find != Connection.Справочники.Контрагенты.ПустаяСсылка ()) < var Contractor = Find.ПолучитьОбъект (); Contractor.ИНН = 11455; Contractor.Записать (); >else alert ('Контрагент не найден !'); return null; > !/

А эта выберет первые 10 контрагентов:

/! function (Connection) < var Query = Connection.NewObject ('Query'); Query.text = "ВЫБРАТЬ ПЕРВЫЕ 10 * ИЗ Справочник.Контрагенты"; return Query.execute (); >!/

Вариант для записи результатов звонка:

/! function (Connection) < var nz = Connection.РегистрыСведений.КакойтоРегистр.СоздатьНаборЗаписей(); nz.Отбор.УИД.Установить ('[Выборка.УИД]'); // Ищем по уникальному идентификатору nz.Прочитать(); if (nz.Количество() >0) < var curz = nz.Получить(0); curz.Статус = '[ColumnResult]'; nz.Записать (); >return null; > !/

В качестве параметра функции Connection передается COM-соединение с базой данных 1С.

Полезные примеры для работы с 1С

Получение текущей даты-времени для 1С, например для установки документу перед его записью:

/! function (Connection) < var GetCurrentDate = function () < var Dt = new Date; var TwoChar = function (Num) return '' + Dt.getFullYear () + TwoChar (Dt.getMonth () + 1) + TwoChar (Dt.getDate ()) + TwoChar (Dt.getHours ()) + TwoChar (Dt.getMinutes ()) + TwoChar (Dt.getSeconds ()); >; var Doc = Connection.Документы.ИмяВашегоДокумента.СоздатьДокумент (); Doc.Дата = GetCurrentDate (); Doc.Ответственный = Connection.ПараметрыСеанса.ТекущийПользователь; // Пользователь, через которого осуществляется подключение к 1С Doc.Записать (); return null; > !/

Выполнение произвольного запроса в 1С:

Примечание: для создания многострочного запроса, необходимо в конце каждой строки добавлять «\»

/! function (Connection) < var Query = Connection.NewObject ('Запрос'); // Запрос выбирает настройку "Основная организация" у пользователя, через которого осуществляется подключение к 1С Query.Текст = "ВЫБРАТЬ НастройкиПользователей.Значение \ ИЗ РегистрСведений.НастройкиПользователей КАК НастройкиПользователей \ ГДЕ НастройкиПользователей.Пользователь = &Пользователь \ И НастройкиПользователей.Настройка = ЗНАЧЕНИЕ (ПланВидовХарактеристик.НастройкиПользователей.ОсновнаяОрганизация)"; Query.УстановитьПараметр ("Пользователь",Connection.ПараметрыСеанса.ТекущийПользователь); var Select = Query.Выполнить ().Выбрать (); var Doc = Connection.Документы.ИмяВашегоДокумента.СоздатьДокумент (); if (Select.Следующий () === true) < Doc.Организация = Select.Значение; >Doc.Записать (); return null; > !/

Пример передачи в запрос текущей даты и времени.

/! function (Connection) < var GetCurrentDateForQuery = function () < var Dt = new Date; var TwoChar = function (Num) return '' + Dt.getFullYear () + ','+ TwoChar (Dt.getMonth () + 1) + ','+ TwoChar (Dt.getDate ()) + ',' + TwoChar (Dt.getHours ()) + ','+ TwoChar (Dt.getMinutes ()) + ','+ TwoChar (Dt.getSeconds ()); >; var Query = Connection.NewObject ('Запрос'); Query.Текст = "ВЫБРАТЬ КурсыВалютСрезПоследних.Курс \ ИЗ РегистрСведений.КурсыВалют.СрезПоследних(ДАТАВРЕМЯ("+GetCurrentDateForQuery()+"), ) КАК КурсыВалютСрезПоследних"; return Query.Выполнить (); > !/

Пример запроса на запись показания прибора учета.

/! function (Connection) < var GetCurrentDate = function () < var Dt = new Date; var TwoChar = function (Num) return '' + Dt.getFullYear () + TwoChar (Dt.getMonth () + 1) + TwoChar (Dt.getDate ()) + TwoChar (Dt.getHours ()) + TwoChar (Dt.getMinutes ()) + TwoChar (Dt.getSeconds ()); >; var Doc = Connection.Документы.ПоказанияПриборовУчетаАбонентов.СоздатьДокумент(); var Query = Connection.NewObject ('Запрос'); Query.Текст = 'ВЫБРАТЬ ПоказанияПриборовУчетаАбонентов.ВидПоказаний.Наименование КАК Наименование, \ ПоказанияПриборовУчетаАбонентов.ДатаОперации КАК ДатаПоказаний, \ ПоказанияПриборовУчетаАбонентов.ВидПоказаний КАК ВидПоказаний \ ИЗ Документ.ПоказанияПриборовУчетаАбонентов КАК ПоказанияПриборовУчетаАбонентов \ ГДЕ ПоказанияПриборовУчетаАбонентов.ВидПоказаний.Наименование = "по Телефону"'; var Select = Query.Выполнить ().Выбрать (); if (Select.Следующий () === true) < Doc.ВидПоказаний = Select.ВидПоказаний; >var Bd = Connection.NewObject ('Запрос'); Bd.Текст = 'ВЫБРАТЬ ПоказанияПриборовУчетаАбонентов.НаселенныйПункт.Наименование КАК НаселенныйПунктНаименование, \ ПоказанияПриборовУчетаАбонентов.НаселенныйПункт КАК НаселенныйПункт \ ИЗ Документ.ПоказанияПриборовУчетаАбонентов КАК ПоказанияПриборовУчетаАбонентов \ ГДЕ ПоказанияПриборовУчетаАбонентов.НаселенныйПункт.Наименование = "[НаселенныйПункт]"'; var Set = Bd.Выполнить ().Выбрать (); if (Set.Следующий () === true) < Doc.НаселенныйПункт = Set.НаселенныйПункт; >Doc.ДатаОперации = GetCurrentDate (); Doc.Дата = GetCurrentDate (); var Search = Doc.ПриборыУчета.Добавить (); Search.Абонент = Connection.Справочники.Абоненты.НайтиПоКоду ("[Account]"); var Pu = Connection.Справочники.ПриборыУчета.НайтиПоНаименованию ("[ПриборУчетаСсылка]"); Search.Показания = "[Reading]"; Search.ПриборУчета = Pu; Doc.Записать (Connection.РежимЗаписиДокумента.Проведение); return null; > !/

Пример многострочного кода на языке 1С

ВЫБРАТЬ РАЗРЕШЕННЫЕ ВЫБОР КОГДА ПОДСТРОКА(УТВ_ЛицевыеСчета.Код, 7, 1) = " " ТОГДА ПОДСТРОКА(УТВ_ЛицевыеСчета.Код, 1, 6) ИНАЧЕ ПОДСТРОКА(УТВ_ЛицевыеСчета.Код, 1, 7) КОНЕЦ КАК ЛицевойСчетКод, УТВ_ЛицевыеСчета.Ссылка КАК ЛицевойСчет, УТВ_ЛицевыеСчета.Здание КАК Здание, УТВ_ЛицевыеСчета.Помещение КАК Помещение, УТВ_ЛицевыеСчета.ФизическоеЛицо КАК ФизическоеЛицо ПОМЕСТИТЬ втВыбранныеЛС ИЗ Справочник.УТВ_ЛицевыеСчета КАК УТВ_ЛицевыеСчета ГДЕ НЕ УТВ_ЛицевыеСчета.ПометкаУдаления И ВЫБОР КОГДА ПОДСТРОКА(УТВ_ЛицевыеСчета.Код, 7, 1) = " " ТОГДА ПОДСТРОКА(УТВ_ЛицевыеСчета.Код, 1, 6) ИНАЧЕ ПОДСТРОКА(УТВ_ЛицевыеСчета.Код, 1, 7) КОНЕЦ = "[Account]" ; ВЫБРАТЬ РАЗРЕШЕННЫЕ ВыбранныеЛС.ЛицевойСчет КАК ЛицевойСчет, ВыбранныеЛС.ЛицевойСчетКод КАК ЛицевойСчетКод, ДействующиеСчетчики.Помещение КАК Помещение, ДействующиеСчетчики.Счетчик КАК Счетчик, ДействующиеСчетчики.ДатаОкончания КАК ДатаОкончания, ВыбранныеЛС.Здание КАК Здание ПОМЕСТИТЬ втДействующиеСчетчикиОтбор ИЗ (ВЫБРАТЬ Помещения.Помещение КАК Помещение, Помещения.Счетчик КАК Счетчик, Помещения.ДатаОкончания КАК ДатаОкончания ИЗ РегистрСведений.УТВ_Помещения_Счетчики.СрезПоследних( КОНЕЦПЕРИОДА(ДАТАВРЕМЯ([GetTime]), МЕСЯЦ), Помещение В (ВЫБРАТЬ ВыбранныеЛС.Помещение КАК Помещение ИЗ втВыбранныеЛС КАК ВыбранныеЛС)) КАК Помещения ГДЕ (Помещения.ДатаОкончания > НАЧАЛОПЕРИОДА(ДАТАВРЕМЯ([GetTime]), МЕСЯЦ) ИЛИ Помещения.ДатаОкончания = ДАТАВРЕМЯ(1, 1, 1))) КАК ДействующиеСчетчики ЛЕВОЕ СОЕДИНЕНИЕ втВыбранныеЛС КАК ВыбранныеЛС ПО ДействующиеСчетчики.Помещение = ВыбранныеЛС.Помещение ; ВЫБРАТЬ РАЗРЕШЕННЫЕ УТВ_РегистрацияПоказанийСчетчиков.Счетчик КАК Счетчик, УТВ_РегистрацияПоказанийСчетчиков.ПериодРасчета КАК ПериодРасчета, СУММА(УТВ_РегистрацияПоказанийСчетчиков.Потребление) КАК Потребление, МАКСИМУМ(УТВ_РегистрацияПоказанийСчетчиков.ПоказаниеТекущее) КАК ПоказаниеТекущее ПОМЕСТИТЬ втПоказанияСчетчиков ИЗ РегистрСведений.УТВ_РегистрацияПоказанийСчетчиков КАК УТВ_РегистрацияПоказанийСчетчиков ГДЕ УТВ_РегистрацияПоказанийСчетчиков.Счетчик В (ВЫБРАТЬ ДействующиеСчетчикиОтбор.Счетчик ИЗ втДействующиеСчетчикиОтбор КАК ДействующиеСчетчикиОтбор) И УТВ_РегистрацияПоказанийСчетчиков.ПериодРасчета.ДатаОкончания < НАЧАЛОПЕРИОДА(ДАТАВРЕМЯ([GetTime]), МЕСЯЦ) И УТВ_РегистрацияПоказанийСчетчиков.мкэ_ТипРасчета = 0 СГРУППИРОВАТЬ ПО УТВ_РегистрацияПоказанийСчетчиков.Счетчик, УТВ_РегистрацияПоказанийСчетчиков.ПериодРасчета ; ВЫБРАТЬ ПоследниеДаннныеПоПУ.Счетчик КАК Счетчик, ПоследниеДаннныеПоПУ.ПериодРасчетаДатаНачала КАК ПериодРасчетаДатаНачала, ПоказанияСчетчиков.Потребление КАК Потребление, ПоказанияСчетчиков.ПоказаниеТекущее КАК ПоказаниеТекущее ПОМЕСТИТЬ втПоследниеДаннныеПоПУ ИЗ (ВЫБРАТЬ ПоказанияСчетчиков.Счетчик КАК Счетчик, МАКСИМУМ(ПоказанияСчетчиков.ПериодРасчета.ДатаНачала) КАК ПериодРасчетаДатаНачала ИЗ втПоказанияСчетчиков КАК ПоказанияСчетчиков СГРУППИРОВАТЬ ПО ПоказанияСчетчиков.Счетчик) КАК ПоследниеДаннныеПоПУ ЛЕВОЕ СОЕДИНЕНИЕ втПоказанияСчетчиков КАК ПоказанияСчетчиков ПО ПоследниеДаннныеПоПУ.Счетчик = ПоказанияСчетчиков.Счетчик И ПоследниеДаннныеПоПУ.ПериодРасчетаДатаНачала = ПоказанияСчетчиков.ПериодРасчета.ДатаНачала ИНДЕКСИРОВАТЬ ПО Счетчик ; ВЫБРАТЬ УТВ_ПоказанияСчетчиковПоказания.Счетчик КАК Счетчик, УТВ_ПоказанияСчетчиковПоказания.ПоказаниеТекущее КАК ПоказаниеТекущее ПОМЕСТИТЬ втПоказанияСчетчиковТекущие ИЗ Документ.УТВ_ПоказанияСчетчиков.Показания КАК УТВ_ПоказанияСчетчиковПоказания ГДЕ УТВ_ПоказанияСчетчиковПоказания.Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(ДАТАВРЕМЯ([GetTime]), МЕСЯЦ) И КОНЕЦПЕРИОДА(ДАТАВРЕМЯ([GetTime]), МЕСЯЦ) И УТВ_ПоказанияСчетчиковПоказания.Счетчик В (ВЫБРАТЬ ДействующиеСчетчикиОтбор.Счетчик ИЗ втДействующиеСчетчикиОтбор КАК ДействующиеСчетчикиОтбор) ИНДЕКСИРОВАТЬ ПО Счетчик ; ВЫБРАТЬ ДействующиеСчетчики.ЛицевойСчетКод КАК ЛицевойСчетКод, ДействующиеСчетчики.Счетчик.Код КАК СчетчикКод, ДействующиеСчетчики.Счетчик.Наименование КАК СчетчикНаименование, ЕСТЬNULL(ПоследниеДаннныеПоПУ.ПоказаниеТекущее, 0) КАК ПредыдущееПоказание, ЕСТЬNULL(ПоказанияСчетчиковТекущие.ПоказаниеТекущее, 0) КАК ТекущиеПереданныеПоказания, ДействующиеСчетчики.Счетчик.Наименование КАК Счетчик, ЕСТЬNULL(ПоследниеДаннныеПоПУ.ПериодРасчетаДатаНачала, ДАТАВРЕМЯ(1, 1, 1)) КАК ПериодПредПоказаний, ДействующиеСчетчики.ЛицевойСчет.Наименование КАК ЛицевойСчет, ДействующиеСчетчики.Помещение.Наименование КАК Помещение, ДействующиеСчетчики.Здание.Наименование КАК Здание ИЗ втДействующиеСчетчикиОтбор КАК ДействующиеСчетчики ЛЕВОЕ СОЕДИНЕНИЕ втПоследниеДаннныеПоПУ КАК ПоследниеДаннныеПоПУ ПО ДействующиеСчетчики.Счетчик = ПоследниеДаннныеПоПУ.Счетчик ЛЕВОЕ СОЕДИНЕНИЕ втПоказанияСчетчиковТекущие КАК ПоказанияСчетчиковТекущие ПО ДействующиеСчетчики.Счетчик = ПоказанияСчетчиковТекущие.Счетчик

Как писать оптимизированные SQL запросы?

Здравствуйте коллеги =). Вот читал я статьи про запросы sql от специалистов (еще года два назад), но случайным образом увидел не стыковки со статьей. Это касается оптимизация запроса. Использование "*" в запросе не рекомендуется, что снижает производительность выполнение запроса сервером.
сделал запроса:

SELECT * FROM user . // Выполняется около 0,004 секунд( Проверял тест 10 раз) SELECT `x`, `x1`, `x2`, `xn` FROM user // Выполняется 0,0065 ( В среднем). Примерно полей 14.

И маленький вопрос еще. У меня есть одна таблица user. Эта таблица используется на несколько сайтов, какие находятся на одном сервере. У каждого сайта, есть свои данные какие хранятся в таблице user. Но всего лишь 30% данных, что хранятся в таблице user совпадают со всеми проектами. А остальные 70% от каждого проекта. Суть вопроса: стоит ли разбивать таблицу юзер, как основную + специальную для каждого проекта используя при запросе LEFT JOIN. Прокомментируйте как будет лучше в виде производительности?