A magic keyword — VALUES…
Синтаксис конструкции INSERT может показаться весьма тривиальным, поскольку стандарт T-SQL рассматривал ключевое слово VALUES лишь в контексте вставки данных – INSERT INTO … VALUES ….
С выходом SQL Server 2008 существенно расширился синтаксис T-SQL, благодаря чему стало возможным использовать многострочную конструкцию VALUES, при этом не только в контексте вставки.
В данном топике будет рассмотрена сравнительная эффективность использования конструкции VALUES в различных типовых ситуациях. Чтобы дать объективную оценку полученных результатов, для каждого примера, будет рассмотрен его план выполнения.
Для наглядности, создадим и заполним тестовыми данными таблицу, которая будет содержать информацию о среднем бале среди учащихся в разрезе четвертей.
IF OBJECT_ID('dbo.GradePointAverage', 'U') IS NOT NULL DROP TABLE dbo.GradePointAverage GO CREATE TABLE dbo.GradePointAverage ( StudentID INT , I SMALLINT NOT NULL , II SMALLINT NOT NULL , III SMALLINT NOT NULL , IV SMALLINT NOT NULL , CONSTRAINT PK_GradePointAverage PRIMARY KEY (StudentID) ) INSERT INTO dbo.GradePointAverage (StudentID, I, II, III, IV) SELECT sv.number, sv.number % 94, sv.number % 83, sv.number % 72, sv.number % 61 FROM [master].dbo.spt_values sv WHERE sv.type = 'P' AND sv.number BETWEEN 1 AND 2000 Предположим, что требуется узнать минимальный и максимальный бал по каждому из учащихся.
Чтобы сделать сравнение более интересным, каждый из предложенных подходов будет выполнен в разных ситуациях: 1) когда у таблицы есть первичный ключ и 2) когда таблица является неупорядоченной кучей.
Сначала приведем самый неудачный пример реализации:
SELECT StudentID , MaxGradePoint = MAX(GradePoint) , MinGradePoint = MIN(GradePoint) FROM ( SELECT StudentID, GradePoint = I FROM dbo.GradePointAverage UNION ALL SELECT StudentID, II FROM dbo.GradePointAverage UNION ALL SELECT StudentID, III FROM dbo.GradePointAverage UNION ALL SELECT StudentID, IV FROM dbo.GradePointAverage ) t GROUP BY StudentID Вместо того, чтобы прочитать данные один раз, обращение к исходной таблице происходит 4 раза – это, мягко говоря, не рационально:
Попробуем избавится от повторные чтений, применяя конструкцию UNPIVOT:
SELECT StudentID , MaxGradePoint = MAX(GradePoint) , MinGradePoint = MIN(GradePoint) FROM ( SELECT * FROM dbo.GradePointAverage UNPIVOT ( GradePoint FOR Grade IN (I, II, III, IV) ) unpvt ) t GROUP BY StudentID Повторные чтения ушли, но план усложнился:
При этом, в случае, когда у таблице нет кластерного индекса, серверу приходится применять сортировку для упорядочивания данных.
Посмотрим как ведет себя конструкция VALUES:
SELECT gpa.StudentID , t.MaxGradePoint , t.MinGradePoint FROM dbo.GradePointAverage gpa CROSS APPLY ( SELECT MaxGradePoint = MAX(GradePoint) , MinGradePoint = MIN(GradePoint) FROM ( VALUES (I), (II), (III), (IV) ) t (GradePoint) ) t При изменении условий, план остается очень простым и неизменным:
Значение Query Cost, полученный из SSMS, также наглядно подтверждает преимущества конструкции VALUES:
Применение конструкции VALUES не ограничивается задачами по преобразованию строк в столбцы. Еще одним из эффективных применений данной конструкции является форматированный вывод запроса.
Сразу уточню, что не считаю это хорошей практикой, поскольку данная задача должна выполнятся на клиенте. Тем не менее, подобные задачи часто были полезны, например, при разработке отчетов.
Предположим, для каждой исходной строки необходимо вывести данные в следующем виде:
Можно решить эту задачу вычитывая данные несколько раз применяя UNION ALL c сортировкой:
SELECT StudentID , GradePoint , AverageGradePoint FROM ( SELECT StudentID, GradePoint = I, AverageGradePoint = (I + II + III + IV) / 4., RN = 1 FROM dbo.GradePointAverage UNION ALL SELECT StudentID, NULL, II, NULL, 2 FROM dbo.GradePointAverage UNION ALL SELECT StudentID, NULL, III, NULL, 3 FROM dbo.GradePointAverage UNION ALL SELECT StudentID, NULL, IV, NULL, 4 FROM dbo.GradePointAverage ) t ORDER BY ID, RN Опять мы получаем повторные чтения. При этом обратите внимание на сортировку, в случае, когда таблица не имеет кластерного индекса:
Как вариант, можно вернутся к конструкции UNPIVOT, проверяя при этом номер строки:
SELECT StudentID = CASE WHEN RN = 1 THEN StudentID END , GradePoint , AverageGradePoint = CASE WHEN RN = 1 THEN AverageGradePoint END FROM ( SELECT StudentID , GradePoint , AverageGradePoint , RN = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY 1/0) FROM ( SELECT *, AverageGradePoint = (I + II + III + IV) / 4. FROM dbo.GradePointAverage ) gpa UNPIVOT ( GradePoint FOR Grade IN (I, II, III, IV) ) unpvt ) t Повторные чтения ушли, но сортировка при использовании кучи никуда не исчезла:
Применим конструкцию VALUES, написав более элегантный запрос:
SELECT t.* FROM dbo.GradePointAverage OUTER APPLY ( VALUES (StudentID, I, (I + II + III + IV) / 4.) , (NULL, II, NULL) , (NULL, III, NULL) , (NULL, IV, NULL) ) t (StudentID, GradePoint, AverageGradePoint) Мы получили простой и эффективный план выполнения:
Согласно Query Cost, конструкция VALUES в очередной раз демонстрирует свою эффективность, по сравнению с соперниками:
Краткие выводы:
Конструкция VALUES не является полной заменой UNPIVOT, тем не менее, в некоторых ситуациях, она бывает очень полезной – позволяя существенно упрощять запросы.
Надеюсь, что у меня получилось, наглядно, это продемонстрировать.
- SQL
- Microsoft SQL Server
Оператор INSERT
Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT ; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора следующий:
Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE :
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
Если задать список столбцов, то можно изменить «естественный» порядок их следования:
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — PC). Теперь мы могли бы написать:
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL , запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL .
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT :
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK , ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:
insert-into
Вы можете указать список столбцов для вставки, используя синтаксис (c1, c2, c3) . Также можно использовать выражение cо звездочкой и/или модификаторами, такими как APPLY, EXCEPT, REPLACE.
В качестве примера рассмотрим таблицу:
SHOW CREATE insert_select_testtable CREATE TABLE insert_select_testtable ( `a` Int8, `b` String, `c` Int8 ) ENGINE = MergeTree() ORDER BY a INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1) Если вы хотите вставить данные во все столбцы, кроме ‘b’, вам нужно передать столько значений, сколько столбцов вы указали в скобках:
INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2) SELECT * FROM insert_select_testtable ┌─a─┬─b─┬─c─┐ │ 2 │ │ 2 │ └───┴───┴───┘ ┌─a─┬─b─┬─c─┐ │ 1 │ a │ 1 │ └───┴───┴───┘ В этом примере мы видим, что вторая строка содержит столбцы a и c , заполненные переданными значениями и b , заполненный значением по умолчанию. Также можно использовать ключевое слово DEFAULT для вставки значений по умолчанию:
INSERT INTO insert_select_testtable VALUES (1, DEFAULT, 1) ; Если список столбцов не включает все существующие столбцы, то все остальные столбцы заполняются следующим образом:
- Значения, вычисляемые из DEFAULT выражений, указанных в определении таблицы.
- Нули и пустые строки, если DEFAULT не определены.
В INSERT можно передавать данные любого формата, который поддерживает ClickHouse. Для этого формат необходимо указать в запросе в явном виде:
INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set Например, следующий формат запроса идентичен базовому варианту INSERT … VALUES:
INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13), (v21, v22, v23), ... ClickHouse отсекает все пробелы и один перенос строки (если он есть) перед данными. Рекомендуем при формировании запроса переносить данные на новую строку после операторов запроса (это важно, если данные начинаются с пробелов).
INSERT INTO t FORMAT TabSeparated 11 Hello, world! 22 Qwerty С помощью консольного клиента или HTTP интерфейса можно вставлять данные отдельно от запроса. Как это сделать, читайте в разделе «Интерфейсы».
Ограничения (constraints)
Если в таблице объявлены ограничения, то их выполнимость будет проверена для каждой вставляемой строки. Если для хотя бы одной строки ограничения не будут выполнены, запрос будет остановлен.
Вставка результатов SELECT
Синтаксис
INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] SELECT ... Соответствие столбцов определяется их позицией в секции SELECT. При этом, их имена в выражении SELECT и в таблице для INSERT, могут отличаться. При необходимости выполняется приведение типов данных, эквивалентное соответствующему оператору CAST.
Все форматы данных кроме Values не позволяют использовать в качестве значений выражения, такие как now() , 1 + 2 и подобные. Формат Values позволяет ограниченно использовать выражения, но это не рекомендуется, так как в этом случае для их выполнения используется неэффективный вариант кода.
Не поддерживаются другие запросы на модификацию части данных: UPDATE , DELETE , REPLACE , MERGE , UPSERT , INSERT UPDATE . Вы можете удалять старые данные с помощью запроса ALTER TABLE . DROP PARTITION .
Для табличной функции input() после секции SELECT должна следовать секция FORMAT .
Чтобы вставить значение по умолчанию вместо NULL в столбец, который не позволяет хранить NULL , включите настройку insert_null_as_default.
Вставка данных из файла
Синтаксис
INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] FROM INFILE file_name [COMPRESSION type] FORMAT format_name Используйте этот синтаксис, чтобы вставить данные из файла, который хранится на стороне клиента. file_name и type задаются в виде строковых литералов. Формат входного файла должен быть задан в секции FORMAT .
Поддерживаются сжатые файлы. Формат сжатия определяется по расширению файла, либо он может быть задан в секции COMPRESSION . Поддерживаются форматы: ‘none’ , ‘gzip’ , ‘deflate’ , ‘br’ , ‘xz’ , ‘zstd’ , ‘lz4’ , ‘bz2’ .
Пример
Выполните следующие запросы, используя клиент командной строки:
echo 1,A > input.csv ; echo 2,B >> input.csv clickhouse-client --query="CREATE TABLE table_from_file (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;" clickhouse-client --query="INSERT INTO table_from_file FROM INFILE 'input.csv' FORMAT CSV;" clickhouse-client --query="SELECT * FROM table_from_file FORMAT PrettyCompact;" ┌─id─┬─text─┐ │ 1 │ A │ │ 2 │ B │ └────┴──────┘ SQL запрос INSERT INTO — наполнить базу данных информацией
sql запрос INSERT INTO имеет смысл при созданной таблицы базы данных. То есть, таблица существует, имеет название, созданные строки и столбцы. создается таблица оператором: CREATE TABLE, модифицируется таблица оператором ALTER TABLE.
sql запрос INSERT INTO — синтаксис запроса
sql запрос INSERT INTO имеет следующий синтаксис:
INSERT INTO название_таблицы (в скобки, если нужно, вставляем список колонок куда нужно вставить данные) VALUES вставляемые данные1, вставляемые данные2, вставляемые данные3.
Между INSERT и INTRO можно вставить опцию IGNORE. Она не обязательная. Нужна для защиты первичных ключей при редактировании таблицы. Иначе, если при редактировании происходит дублирование первичных ключей, то при вставке опции IGNORE первая строка с первичным ключом останется в изменяемой таблице, Другие первичные ключи удаляются. По умолчанию эту опцию опускаем.
Статьи по теме: Что такое база данных — понятие база данных в информатике
Есть необязательные опции LOW_PRIORITY и DELAYED. Они определяют приоритеты добавления информации в БД. Первая задает ожидание освобождения БД, вторая означает буферизацию информации.
Строка в запросе: INSERT с фразой VALUES позволит добавить одиночную строку в таблицу БД. Фраза VALUES содержит значения этих данных.
Вместо фразы VALUES могут указываться подзапросы. INSERT с подзапросом добавляет к таблице строки, возвращенные подзапросом. Сервер базы данных обрабатывает подзапрос и вставляет все возвращенные строки в таблицу. Сервер не вставляет строки, если подзапрос их не выбирает.
- subquery_1 — подзапрос, который сервер обрабатывает тем же способом что и представление
- subquery_2 — подзапрос, который возвращает строки, вставляемые в таблицу. Список этого подзапроса должен иметь количество столбцов, совпадающее со списком столбцов INSERT.
Подзапросы практически не используются в базе данных MySQL.
Статьи по теме: Устройство реляционной базы данных
Примеры sql запроса INSERT INTO в базе данных MySQL
Вставляем новые строки в БД MySQL командой INSERT INTRO.
Первый пример.
Вставляем новые строки в таблицу table_name.
INSERT INTO table_name VALUES (‘2′,’145′,’1′,’name’);
Это значит, что мы хотим в таблицу table_name вставить в столбцы значения 2,145,1,name. Так как, столбцы не указаны значения заполяются во все столбцы таблицы.
Пример второй.
Вставка информации в нужные (указанные) столбцы таблицы table_name.
INSERT INTO table_name (client_customer, client_subclient, client_mail) VALUES (‘name1′,’subname1′,’name1@mail.ru′), (‘name2′,’subname2′,’name2@mail.ru′), (‘name3′,’subname3′,(’name3@mail.ru′);
Игорь Серов специально для сайта «Интернет технологии».
Другие статьи раздела: СУБД
- PhpMyAdmin на локальном сервере
- Что такое база данных — понятие база данных в информатике
- Функции СУБД обеспечивающие управление базой данных
- Устройство реляционной базы данных
- Первичный ключ и внешний ключ таблиц реляционных баз данных
- Концептуальная модель базы данных — диаграмма связи между объектами
- Понятие и назначение SQL запроса
- SQL запрос для создания таблицы базы данных — оператор CREATE TABLE
- SQL ALTER TABLE — sql запрос на модификацию таблицы базы данных