Синтаксисе sql что это как исправить

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

Основные виды ошибок SQL:

1. Синтаксические ошибки
2. Ошибки времени выполнения
3. Логические ошибки

Синтаксические ошибки

a. Неправильное использование ключевых слов (H4 Heading) b. Ошибки в структуре запроса (H4 Heading) c. Проблемы с кавычками и скобками (H4 Heading)

Ошибки времени выполнения

a. Ошибки доступа к данным (H4 Heading) b. Ошибки ограничений целостности (H4 Heading) c. Проблемы с производительностью (H4 Heading)

Логические ошибки

a. Неправильный выбор операторов (H4 Heading) b. Ошибки в вычислениях (H4 Heading) c. Проблемы с агрегацией данных (H4 Heading)

Чтобы разобраться подробнее – приходите на бесплатный курс

1. Определение типа ошибки (H3 Heading)
2. Анализ причин ошибки (H3 Heading)
3. Применение соответствующего решения (H3 Heading)

Определение типа ошибки

• Используйте сообщения об ошибках
• Отслеживайте контекст запроса

Анализ причин ошибки

• Проверьте синтаксис
• Проверьте права доступа
• Убедитесь, что данные корректны

Применение соответствующего решения

• Исправьте синтаксические ошибки
• Решите проблемы с данными
• Оптимизируйте запросы

Часто задаваемые вопросы

1. Как наиболее эффективно найти и исправить ошибки SQL?
2. Какие инструменты могут помочь в диагностике и исправлении ошибок SQL?
3. Влияет ли версия базы данных на возникновение ошибок SQL?
4. Как предотвратить ошибки SQL при разработке приложений?

Чтобы разобраться подробнее – приходите на бесплатный курс

Как наиболее эффективно найти и исправить ошибки SQL?

• Используйте подходящие инструменты и ресурсы для отладки
• Разбивайте сложные запросы на простые
• Протестируйте запросы с разными наборами данных

Какие инструменты могут помочь в диагностике и исправлении ошибок SQL?

• Редакторы кода с поддержкой SQL, такие как Visual Studio Code или Sublime Text
• Среды разработки баз данных, такие как SQL Server Management Studio или MySQL Workbench
• Специализированные инструменты для анализа и оптимизации запросов, такие как SQL Sentry Plan Explorer или EverSQL

Влияет ли версия базы данных на возникновение ошибок SQL?

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

Как предотвратить ошибки SQL при разработке приложений?

• Используйте хорошие практики проектирования баз данных и написания запросов
• Тестируйте ваш код на разных этапах разработки
• Внедряйте контроль версий и процессы код-ревью для обеспечения качества кода
• Обучайте разработчиков основам SQL и принципам работы с базами данных

Заключение:

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

Время на прочтение
6 мин

Количество просмотров 64K

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

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

Кстати, иногда такая же участь постигает и специалистов более высокого полета. 

Сегодня мы решили собрать 7 таких ошибок в одном месте, чтобы как можно меньше людей их совершали. 

Примечание: Ошибки будут 2 видов — реальные ошибки и своего рода best practices, которым часто не следуют.

Но, обо всем по порядку

Кстати, будем рады видеть вас в своих социальных сетях — ВКонтакте Телеграм Инстаграм

1. Преобразование типов

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

В SQL это не всегда так работает. Например, в PostgreSQL деление двух целых чисел друг на друга даст целочисленный ответ. Это можно проверить как для целочисленных столбцов, так и для чисел. 

SELECT a/b FROM demo
# столбец целых чисел

SELECT 1 / 2
# 0

Аналогичные запросы, например, в MySQL дадут дробное число, как и положено. 

Если Вы точно не уверены или хотите подстраховаться, то лучше всегда явно делать преобразование типов. Например:

SELECT a::NUMERIC/b FROM demo

SELECT a*1.0/b FROM demo

SELECT CAST(1 AS FLOAT)/2 FROM demo

Все перечисленные примеры дадут нужный ответ. 

2. HAVING вместо WHERE

Часто встречается ошибка — оператор HAVING используется вместо WHERE в запросах с агрегацией. Это неверно!

WHERE производит фильтрацию строк в исходном наборе данных, отсеивая неподходящие. После этого GROUP BY формирует группы и оператор HAVING производит фильтрацию уже целых групп (будто группа — одно запись). 

Например:

SELECT date, COUNT(*)
FROM transactions t 
WHERE date >= '2019-01-01'
GROUP BY date
HAVING COUNT(*) = 2  

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

Некоторые же пишут так:

SELECT date, COUNT(*)
FROM transactions t 
GROUP BY date
HAVING COUNT(*) = 2  AND date >= '2019-01-01'

Так делать не нужно

Кстати, для закрепления этой темы мы специально делали задачку «Отфильтрованные продажи» у себя на платформе. Если интересно порешать и другие задачки по SQL — welcome

3. Алиасы и план запроса

Если «проговаривать SQL-запрос» словами, то получится что-то такое:

В таблице есть старая цена, а есть новая цена. Их разность я назову diff. Я хочу отобрать только те строки, где значение diff больше 100. 

Звучит вполне логично. Но в SQL прям так реализовать не получится — и многие попадаются в эту ловушку. 

Вот неправильный запрос:

SELECT old_price - new_price AS diff
FROM goods
WHERE diff > 100

Ошибка его заключается в том, что мы используем алиас столбца diff внутри оператора WHERE. 

Да, это выглядит вполне логичным, но мы не можем так сделать из-за порядка выполнения операторов в SQL-запросе. Дело в том, что фильтр WHERE выполняется сильно раньше оператора SELECT (а значит и AS). Соответственно, в момент выполнения столбца diff просто не существует. Об этом, кстати, и говорит ошибка:

ERROR: column "diff" does not exist

Правильно будет использовать подзапрос или переписать запрос следующим образом:

SELECT old_price - new_price AS diff
FROM goods
WHERE old_price - new_price > 100

Важно: Внутри ORDER BY вы можете указывать алиас — этот оператор выполняется уже после SELECT.

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

4. Не использовать COALESCE

Пришло время неочевидных пунктов. Но сейчас мы поясним свои чаяния. 

COALESCE — это оператор, который принимает N значений и возвращает первое, которое не NULL. Если все NULL, то вернется NULL. 

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

Мы же рассмотрим нечисловой пример, а вполне себе бизнесовый. Например, есть таблица клиентов Clients. В поле name заносится имя пользователя. 

Отдел маркетинга решил сделать email-рассылку, которая начинается с фразы:

Приветствуем, имя_пользователя!

Очевидно, что если name is NULL, то это превратится в тыкву:

Приветствуем, !

Вот в таких случаях и помогает COALESCE:

SELECT COALESCE(name, 'Дорогой друг') FROM Clients

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

5. Игнорирование CASE

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

Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0. 

Пользователь предложил такое решение:

SELECT id, sum FROM transactions t WHERE type = 0
UNION ALL
SELECT id, -sum FROM transactions t WHERE type = 1

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

А вот то же самое с CASE:

SELECT id, CASE WHEN type = 0 THEN sum ELSE -sum END FROM transactions t 

Согласитесь, получше?

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

А еще, кстати, COALESCE, который мы обсуждали выше — это просто «синтаксический сахар» и обертка вокруг CASE. Если интересно — мы подробно это описали в статье.

6. Лишние подзапросы

Из-за того, что многие пишут SQL-запросы также, как это «звучит» в их голове, получается нагромождение подзапросов. 

Это проходит с опытом — начинаешь буквально «мыслить на SQL» и все становится ок. Но первое время появляются такие штуки:

SELECT id, LAG(neg) OVER(ORDER BY id) AS lg
FROM (
  SELECT id, sm, -sm AS neg
  FROM ( 
    SELECT id, sum AS sm FROM transactions t 
  ) t
) t1

И это еще не все — можно и побольше накрутить. Но зачем так, если можно так:

SELECT id, LAG(-sum) OVER(ORDER BY id) FROM transactions t 

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

Как говорил дядюшка Кнут:

Преждевременная оптимизация — корень всех зол

7. Неправильное использование оконных функций

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

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

Одна из таких вещей — поведение оконной функции LAST_VALUE и прочих. 

Например, когда мы пишем запрос:

WITH cte AS (
    SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
    UNION
    SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
    union
    SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
    UNION
    SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (PARTITION BY department ORDER BY year) AS emp
FROM cte c

Мы ожидаем увидеть 2 раза по 10 для департамента Маркетинг и 2 раза по 25 для Продаж. Однако такой запрос дает иную картину:

Получается, что запрос тупо продублировал значения из столбца employees. Как так?

Лезем в документацию PostgreSQL и видим:

Заметьте, что функции first_value, last_value и nth_value рассматривают только строки в «рамке окна», которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей.

Ага, вот и ответ. То есть каждый раз у нас окно — это не весь набор строк, а только до текущей строки.

Получается, есть два способа вылечить такое поведение:

• Убрать ORDER BY

• Добавить определение рамки

Вот, например, второй вариант:

WITH cte AS (
    SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
    UNION
    SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
    union
    SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
    UNION
    SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (
  PARTITION BY department
  ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS emp
FROM cte c

Кстати, такую тему подняла наша подписчица в Телеграме под постом «7 самых важных оконных функций». Спасибо ей!

А вас рады будем видеть в числе подписчиков

Эпилог

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

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

1. Забытые первичные ключи

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

Первичные ключи — ваш первый шаг к реляционным базам данных. Они ссылаются на внешние ключи в реляционных таблицах. Например, если у вас есть таблица со списком клиентов, столбец «CustomerId» будет уникальным для каждого клиента. Это может быть ваш столбец первичного ключа. Ваше значение CustomerId будет затем помещено в таблицу “Order”, ​​чтобы связать две таблицы вместе. Всегда используйте первичный ключ в каждой создаваемой вами таблице независимо от ее размера.

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

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

Например, предположим, что у вас есть таблица клиентов, которая содержит адрес клиента. Поскольку адрес относится к клиенту, он находится в правильном месте. Затем вы создаете таблицу “Order” и добавляете адрес клиента в таблицу “Order”. Этот тип избыточности данных плохо спроектирован. Таблица Customer и “Order” могут связываться друг с другом, используя связи между первичным и внешним ключами. Что произойдет, если вы забудете обновить адрес клиента в таблице заказов? В результате у вас теперь есть два адреса для клиента, и вы не знаете, какой из них является точным.

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

3. Избегайте NOT IN или IN и используйте вместо этого JOIN

Операторы NOT IN и IN плохо оптимизированы. Они удобны, но обычно их можно заменить простым оператором JOIN. Посмотрите на пример запроса.

SELECT *FROM Customer
WHERE NOT IN (SELECT CustomerId FROM Order)

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

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

SELECT * FROM Customer c
LEFT JOIN Order o on c.CustomerId = o.CustomerId
WHERE o.CustomerId IS NULL

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

4. Забытые значения NULL и пустые строковые значения

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

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

5. Символ звездочки в операторах SELECT

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

SELECT * FROM Customer

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

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

SELECT CustomerId, FirstName, LastName FROM Customer

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

6. Цикл с слишком многими курсорами

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

Лучше всего написать процедуру по-другому, чтобы избежать негативного влияния на производительность базы данных, если это возможно. Большинство курсоров можно заменить хорошо написанным оператором SQL. Если вы не можете избежать этого, то курсоры следует сохранить для запланированных заданий, которые выполняются в непиковые часы. Курсоры используются в отчетах о запросах и заданиях на преобразование данных, поэтому их не всегда можно избежать. Просто ограничьте их как можно больше в производственных базах данных, которые ежедневно выполняют запросы к вашей базе данных.

7. Несоответствия данных в процедурах назначения на местах

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

Например, вам, вероятно, нужна десятичная точность на 2-3 пункта в столбце, в котором хранится общая стоимость заказа. Если вы назначите этот столбец как целое число, ваша база данных сможет хранить только целые числа без десятичных значений. Что происходит с десятичными знаками зависит от вашей платформы базы данных. Он может автоматически обрезать значения или выдать ошибку. Любая альтернатива может создать серьезную ошибку в вашем приложении. Всегда учитывайте то, что вам нужно хранить при разработке ваших таблиц.

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

8. Логические операции OR и AND

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

Давайте посмотрим на оператор SQL, который смешивает операторы AND и OR.

SELECT CustomerId
FROM Customer
WHERE FirstName = 'AndreyEx' AND LastName = 'Destroyer' OR CustomerId > 0

Цель приведенного выше утверждения состоит в том, чтобы получить любых клиентов с именем и фамилией «AndreyEx» и «Destroyer», а идентификатор клиента больше нуля. Однако, поскольку мы смешали оператор AND с OR, возвращаются все записи, в которых идентификатор клиента больше нуля. Мы можем преодолеть это логическое препятствие, используя круглые скобки. Давайте добавим их к приведенному выше утверждению.

SELECT CustomerId
FROM Customer
WHERE (FirstName = 'AndreyEx' OR LastName = 'Destroyer') AND CustomerId > 0

Мы изменили логику для этого утверждения. Теперь первый набор скобок возвращает все записи, в которых имя клиента — AndreyEx или фамилия Destroyer. С помощью этого фильтра мы сообщаем SQL, чтобы он возвращал только те значения, где CustomerId больше нуля.

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

9. Подзапросы должны возвращать одну запись

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

Давайте посмотрим на пример.

SELECT CustomerId,
(SELECT OrderId FROM Order o WHERE c.CustomerId = o.CustomerId)
FROM Customer c

В приведенном выше запросе мы получаем список идентификаторов клиентов из таблицы Customer. Обратите внимание, что мы получаем идентификатор заказа из таблицы заказов, где совпадает идентификатор клиента. Если есть только один заказ, этот запрос работает нормально. Однако, если для клиента существует более одного заказа, подзапрос возвращает более одной записи, и запрос не выполняется. Вы можете избежать этой проблемы, используя оператор «Top 1».

Давайте изменим запрос на следующий.

SELECT CustomerId,
(SELECT Top 1 OrderId FROM Order o WHERE c.CustomerId = o.CustomerId ORDER BY OrderDate)
FROM Customer c

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

10. JOIN к индексам

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

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

Заключение

Реляционные базы данных идеально подходят для большинства внутренних процедур, но вам необходимо создать правильный оператор SQL и оптимизировать таблицы и запросы для достижения максимальной производительности. Избегайте этих десяти ошибок SQL, и вы будете на пути к созданию быстрой и эффективной базы данных для любого малого, среднего или крупного онлайн-бизнеса.