allow nulls sql что это

Alter Table portal_tree_access ALTER COLUMN site_id int default (0) not null

������ ������ 🙁

Alter Table portal_tree_access ALTER COLUMN site_id int default (0) not null

Источник

SQL-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

Столбцы, допускающие NULL-значения, и производительность

Яд NULL

Теперь рассмотрим несколько простых запросов с IN(). Начнем с этого:

Он приводит к действительно хорошему соединению merge join, поскольку обе таблицы имеют одинаковые кластеризованные индексы на столбце, по которому выполняется соединение.

Замечательная простота

Что произойдет, если мы поменяем IN() на NOT IN()?

Почему так сложно?

всегда возвращает false, поскольку NULL может представлять что-угодно, в том числе x. И это имеет отношение к внутренней таблице, если там содержатся NULL-значения.

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

Упрощение плана

Устранение NULL

Вы могли бы изменить тип столбеца, чтобы он не допускал NULL-значений (тогда SQL Server не будет сначала проверять наличие NULL-значений), или вы могли бы просто сообщить SQL Server’у игнорировать NULL-значения, исключив их в предложении WHERE:

Назад к использованию Merge Join

Использование NOT EXISTS вместо NOT IN

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

Left Anti Join

Вы можете написать запрос с LEFT JOIN и предложением WHERE, но то, что улучшает читабельность, приведет к добавлению лишнего оператора Filter, который может замедлить запрос.

Сначала собираем, потом фильтруем

Чудеса теоретико-множественных операций

Вы можете использовать оператор EXCEPT. Он генерирует в этом случае тот же самый план; недостатком является то, что вы можете вернуть только ключевые столбцы. Но зато EXCEPT не сравнивает NULL-значения в отличии от оператора равенства в обычном соединении.

Соединение Left Anti Semi, но сканирование вместо поиска

Другим способом является использование CREATE TABLE без явного указания NOT NULL.

Если вы не укажите ни «NULL», ни «NOT NULL», то по умолчанию используется «NULL».

Почему об этом стоит беспокоиться

На самом деле запрос в этом примере будет проверять внутренюю таблицу на NULL значения только один раз (оператор Spool), и для этой проверки будет использоваться поиск по индексу, т.к. столбец индексирован. Поэтому вряд ли следует ожидать здесь проблем с производительностью.
Однако ситуация гораздо хуже в следующем примере, который использует базу данных Stack Overflow, где ничего этого нет:

Столбец ParentId в Posts допускает NULL и не индексирован, поэтому получаемый план выполнения завершается выполнением Nested Loop для каждой строки в Posts с каждой строкой в «p«. Пофиксив проблему NULL-значений рассмотренными выше способами, для выполнения запроса потребуется лишь несколько секунд вместо часов.

Исходный запрос. Выполняется часами на всех ядрах

Простое добавление “AND ParentId IS NOT NULL” к подзапросу в NOT IN(), дает:

Улучшенный запрос. Секунды на одном ядре

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Источник

Памятка/шпаргалка по SQL

Доброго времени суток, друзья!

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

Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.

При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.

Содержание

Что такое SQL?

SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).

Почему SQL?

Процесс SQL

При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.

В данном процессе участвует несколького компонентов:

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

Команды SQL

Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

Что такое поле?

Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.

Что такое запись или строка?

Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.

Что такое колонка?

Что такое нулевое значение?

Ограничения

Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

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

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

Нормализация БД

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

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

Синтаксис SQL

Примеры синтаксиса

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

Арифметические

Операторы сравнения

Логические операторы

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

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

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

Предположим, что в таблице users имеются следующие записи:

Выполняем поиск активных пользователей:

Числовые

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

Простой пример использования числового выражения:

Также существует несколько встроенных функций для работы со строками:

Выражения для работы с датами

Эти выражения, как правило, возвращают текущую дату и время.

Другие функции для получения текущей даты и времени:

Функции для разбора даты и времени:

Функции для манипулирования датами:

Создание БД

Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.

Название БД должно быть уникальным в пределах СУБД.

Получаем список БД:

Удаление БД

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

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

Проверяем, что БД удалена:

Выбор БД

Создание таблицы

Проверяем, что таблица была создана:

Удаление таблицы

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

Удаляем таблицу users :

Добавление колонок

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

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

В таблицу можно добавлять несколько строк за один раз.

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

Заполнение таблицы с помощью другой таблицы

Выборка полей

Для выборки всех полей используется такой синтаксис:

Предложение WHERE

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

Возвращаемые записи должны удовлетворять всем указанным условиям.

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

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

Обновление полей

Обновим возраст пользователя с именем Igor :

Удаление записей

Удалим неактивных пользователей:

Предложения LIKE и REGEX

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

% означает 0, 1 или более символов. _ означает точно 1 символ.

Сделаем выборку неактивных пользователей:

Сделаем выборку пользователей 30 лет и старше:

REGEX

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

В регулярное выражении могут использоваться следующие специальные символы:

Сделаем выборку пользователей с именами Igor и Vika :

Предложение TOP / LIMIT / ROWNUM

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

Сделаем выборку первых трех пользователей:

Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:

Предложения ORDER BY и GROUP BY

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

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

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

Теперь выполним сортировку по убыванию:

Определим собственный порядок сортировки по убыванию:

GROUP BY

Сгруппируем активных пользователей по городам:

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

Сделаем выборку городов проживания пользователей:

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Существуют разные типы объединений:

Предложение UNION

Однако, они могут быть разной длины.

Объединим наши таблицы users и orders :

Предложение UNION ALL

Существует еще два предложения, похожих на UNION :

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

Синтаксис синонима колонки:

Пример использования синонимов таблиц:

Пример использования синонимов колонок:

Индексы

Создание индексов

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

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

Синтаксис создания индекса:

Синтаксис создания индекса для одной колонки:

Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):

Синтаксис создания индексов для нескольких колонок (композиционный индекс):

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

Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.

Удаление индексов

Для удаления индексов используется инструкция DROP INDEX :

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

К таким ситуациям относится следующее:

Обновление таблицы

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

Добавляем в таблицу users новую колонку — пол пользователя:

Удаляем эту колонку:

Очистка таблицы

Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы ( DROP TABLE полностью удаляет таблицу и все ее данные).

Очищаем таблицу users :

Проверяем, что users пустая:

Представления

Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.

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

Представления — это виртутальные таблицы, позволяющие делать следующее:

Создание представления

Создаем представление для имен и возраста пользователей:

Получаем данные с помощью представления:

WITH CHECK OPTION

Если условие не удовлетворяется, выбрасывается исключение.

Обновление представления

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

Пример обновления возраста пользователя с именем Igor в представлении:

Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.

С помощью команды DELETE можно удалять строки из представления.

Удаляем из представления пользователя, возраст которого составляет 26 лет:

Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.

Удаление представления

Для удаления представления используется инструкция DROP VIEW :

Удаляем представление usersView :

HAVING

Транзакции

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

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

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

Свойства транзакции

Транзакции имеют 4 стандартных свойства (ACID):

Управление транзакцией

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

Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:

Удаляем пользователя с именем Oleg и отменяем эту операцию:

Контрольные точки создаются с помощью такого синтаксиса:

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

Делаем выборку пользователей:

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

Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):

Временные таблицы

Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.

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

Клонирование таблицы

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

Подзапросы

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

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

Правила использования подзапросов:

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

Последовательности

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

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

Источник

• ← фитнес батут видео тренировки
• anti rubella igg повышен при беременности что это значит →