Вставить (SQL) - Insert (SQL)
SQL INSERT оператор добавляет одну или несколько записей в какой - либо одной таблицы в реляционной базе данных .
Основная форма
Операторы вставки имеют следующую форму:
ВСТАВИТЬ В таблицу ( столбец1 [, столбец2 , столбец3 ...]) ЗНАЧЕНИЯ ( значение1 [, значение2 , значение3 ...])
Количество столбцов и значений должно быть одинаковым. Если столбец не указан, используется значение по умолчанию для столбца. Значения , указанные (или подразумеваемые) по INSERT заявления должны удовлетворять все применимые ограничения (например, первичные ключей , ПРОВЕРИТЬ ограничения , и NOT NULL ограничений). Если возникает синтаксическая ошибка или нарушаются какие-либо ограничения, новая строка не добавляется в таблицу и вместо нее возвращается ошибка.
Пример:
INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');
Также можно использовать сокращение, используя порядок столбцов при создании таблицы. Не требуется указывать все столбцы в таблице, так как любые другие столбцы будут принимать значения по умолчанию или останутся пустыми :
ВСТАВИТЬ В таблицу ЗНАЧЕНИЯ ( значение1 ; [ значение2 ; ...])
Пример вставки данных в 2 столбца в таблице phone_book и игнорирования любых других столбцов, которые могут быть после первых 2 в таблице.
INSERT INTO phone_book VALUES ('John Doe', '555-1212');
Расширенные формы
Многорядные пластины
Особенностью SQL (начиная с SQL-92 ) является использование конструкторов значений строк для вставки нескольких строк за раз в одном операторе SQL:
INSERT INTO tablename (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
...
Эта функция поддерживается DB2 , SQL Server (начиная с версии 10.0 - т.е. 2008), PostgreSQL (начиная с версии 8.2), MySQL , SQLite (начиная с версии 3.7.11) и H2 .
Пример (предполагается, что "имя" и "номер" - единственные столбцы в таблице "phone_book"):
INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe', '555-2323');
что можно рассматривать как сокращение для двух утверждений
INSERT INTO phone_book VALUES ('John Doe', '555-1212');
INSERT INTO phone_book VALUES ('Peter Doe', '555-2323');
Обратите внимание, что два отдельных оператора могут иметь разную семантику (особенно в отношении триггеров операторов ) и могут не обеспечивать такую же производительность, как одиночная многострочная вставка.
Чтобы вставить несколько строк в MS SQL, вы можете использовать такую конструкцию:
INSERT INTO phone_book
SELECT 'John Doe', '555-1212'
UNION ALL
SELECT 'Peter Doe', '555-2323';
Обратите внимание, что это недопустимый оператор SQL в соответствии со стандартом SQL ( SQL: 2003 ) из-за неполного предложения подзапроса.
Чтобы сделать то же самое в Oracle, используйте таблицу DUAL , которая всегда состоит только из одной строки:
INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM DUAL
UNION ALL
SELECT 'Peter Doe','555-2323' FROM DUAL
Соответствующая стандарту реализация этой логики показывает следующий пример или как показано выше:
INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM LATERAL ( VALUES (1) ) AS t(c)
UNION ALL
SELECT 'Peter Doe','555-2323' FROM LATERAL ( VALUES (1) ) AS t(c)
Oracle PL / SQL поддерживает оператор INSERT ALL , в котором несколько операторов вставки завершаются оператором SELECT :
INSERT ALL
INTO phone_book VALUES ('John Doe', '555-1212')
INTO phone_book VALUES ('Peter Doe', '555-2323')
SELECT * FROM DUAL;
В Firebird вставка нескольких строк осуществляется следующим образом:
INSERT INTO phone_book (name, number)
SELECT 'John Doe', '555-1212' FROM RDB$DATABASE
UNION ALL
SELECT 'Peter Doe', '555-2323' FROM RDB$DATABASE;
Firebird, однако, ограничивает количество строк, которые можно вставить таким образом, поскольку существует ограничение на количество контекстов, которые можно использовать в одном запросе.
Копирование строк из других таблиц
INSERT заявление также может быть использовано для извлечения данных из других таблиц, при необходимости измените его и вставить его непосредственно в таблицу. Все это делается в одном операторе SQL, который не требует какой-либо промежуточной обработки в клиентском приложении. Вместо предложения VALUES используется подзапрос. Подвыбор может содержать объединения, вызовы функций и даже запрашивать ту же таблицу, в которую вставлены данные. Логически выбор оценивается до начала фактической операции вставки. Пример приведен ниже.
INSERT INTO phone_book2
SELECT *
FROM phone_book
WHERE name IN ('John Doe', 'Peter Doe')
Вариант необходим, когда некоторые данные из исходной таблицы вставляются в новую таблицу, но не всю запись. (Или когда схемы таблиц не совпадают.)
INSERT INTO phone_book2 (name, number)
SELECT name, number
FROM phone_book
WHERE name IN ('John Doe', 'Peter Doe')
Оператор SELECT создает (временную) таблицу, и схема этой временной таблицы должна совпадать со схемой таблицы, в которую вставляются данные.
Значения по умолчанию
Можно вставить новую строку без указания каких-либо данных, используя значения по умолчанию для всех столбцов. Однако некоторые базы данных отклоняют оператор, если данные не указаны, например Microsoft SQL Server, и в этом случае можно использовать ключевое слово DEFAULT .
INSERT INTO phone_book
VALUES ( DEFAULT )
Иногда базы данных также поддерживают для этого альтернативный синтаксис; например, MySQL позволяет опускать ключевое слово DEFAULT , а T-SQL может использовать ЗНАЧЕНИЯ ПО УМОЛЧАНИЮ вместо ЗНАЧЕНИЙ (ПО УМОЛЧАНИЮ) . DEFAULT ключевым слово также может быть использовано в обычной вставке явно заполнить столбец , используя значение этого столбца по умолчанию:
INSERT INTO phone_book VALUES ( DEFAULT, '555-1212' )
Что происходит, когда в столбце не указано значение по умолчанию, зависит от базы данных. Например, MySQL и SQLite заполнят пустое значение (кроме случаев, когда они находятся в строгом режиме), в то время как многие другие базы данных отклонят оператор.
Получение ключа
Разработчики баз данных, которые используют суррогатный ключ в качестве первичного ключа для каждой таблицы, будут время от времени сталкиваться с сценарием, когда им необходимо автоматически извлекать первичный ключ, сгенерированный базой данных, из оператора SQL INSERT для использования в других операторах SQL. Большинство систем не позволяют операторам SQL INSERT возвращать данные строки. Следовательно, в таких сценариях необходимо реализовать обходной путь. Общие реализации включают:
- Использование хранимой процедуры для конкретной базы данных, которая генерирует суррогатный ключ, выполняет операцию INSERT и, наконец, возвращает сгенерированный ключ. Например, в Microsoft SQL Server ключ извлекается с помощью специальной функции SCOPE_IDENTITY () , а в SQLite функция называется last_insert_rowid () .
- Использование оператора SELECT для конкретной базы данных для временной таблицы, содержащей последнюю вставленную строку (строки). DB2 реализует эту функцию следующим образом:
SELECT * FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) ) AS t
- DB2 для z / OS реализует эту возможность следующим образом.
SELECT EMPNO, HIRETYPE, HIREDATE FROM FINAL TABLE ( INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL) VALUES('Mary Smith', 35000.00, 11, 'Associate') );
- Использование оператора SELECT после оператора INSERT с функцией, зависящей от базы данных, которая возвращает сгенерированный первичный ключ для последней вставленной строки. Например, LAST_INSERT_ID () для MySQL .
- Использование уникальной комбинации элементов из исходного SQL INSERT в последующем операторе SELECT .
- Использование GUID в инструкции SQL INSERT и получение его в инструкции SELECT .
- Использование предложения OUTPUT в инструкции SQL INSERT для MS-SQL Server 2005 и MS-SQL Server 2008.
- Использование оператора INSERT с предложением RETURNING для Oracle .
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) RETURNING phone_book_id INTO v_pb_id
- Использование оператора INSERT с предложением RETURNING для PostgreSQL (начиная с версии 8.2). Возвращенный список идентичен результату INSERT .
-
Firebird имеет тот же синтаксис в операторах языка модификации данных (DSQL); оператор может добавить не более одной строки. В хранимых процедурах, триггерах и исполнительных блоках (PSQL) используется вышеупомянутый синтаксис Oracle.
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) RETURNING phone_book_id
-
Firebird имеет тот же синтаксис в операторах языка модификации данных (DSQL); оператор может добавить не более одной строки. В хранимых процедурах, триггерах и исполнительных блоках (PSQL) используется вышеупомянутый синтаксис Oracle.
- Использование функции IDENTITY () в H2 возвращает последний вставленный идентификатор.
SELECT IDENTITY();
Триггеры
Если триггеры определены в таблице, с которой работает оператор INSERT , эти триггеры оцениваются в контексте операции. Триггеры BEFORE INSERT позволяют изменять значения, которые должны быть вставлены в таблицу. Триггеры AFTER INSERT больше не могут изменять данные, но могут использоваться для инициирования действий с другими таблицами, например, для реализации механизма аудита.