SQL INSERT оператор добавляет одну или несколько записей в какой - либо одной таблицы в реляционной базе данных .
Основная форма
Операторы вставки имеют следующую форму:
ВСТАВИТЬ В таблицу ( столбец1 [, столбец2 , столбец3 ...]) ЗНАЧЕНИЯ ( значение1 [, значение2 , значение3 ...])
Количество столбцов и значений должно быть одинаковым. Если столбец не указан, используется значение по умолчанию для столбца. Значения, указанные (или подразумеваемые) оператором INSERT, должны удовлетворять всем применимым ограничениям (таким как первичные ключи ,ПРОВЕРИТЬ ограничения и NOT NULL ограничения). Если возникает синтаксическая ошибка или нарушаются какие-либо ограничения, новая строка не добавляется в таблицу и вместо нее возвращается ошибка.
Пример:
INSERT INTO phone_book ( имя , номер ) 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 имя таблицы ( столбец - a , [ столбец - b , ...]) VALUES ( 'значение-1a' , [ 'значение-1b' , ...]), ( 'значение-2a' , [ 'значение- 2б ' , ...]), ...
Эта функция поддерживается 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 ВЫБЕРИТЕ 'John Doe' , '555-1212' СОЮЗ ВСЕ ВЫБЕРИТЕ «Питер Доу» , «555-2323» ;
Обратите внимание, что это недопустимый оператор SQL в соответствии со стандартом SQL ( SQL: 2003 ) из-за неполного предложения подзапроса.
Чтобы сделать то же самое в Oracle, используйте таблицу DUAL , которая всегда состоит только из одной строки:
ВСТАВИТЬ В телефонную книгу ВЫБРАТЬ 'John Doe' , '555-1212' ИЗ ДВОЙНОГО СОЕДИНЕНИЯ ВСЕ ВЫБРАТЬ 'Peter Doe' , '555-2323' ИЗ ДВОЙНОГО
Соответствующая стандарту реализация этой логики показывает следующий пример или как показано выше:
ВСТАВИТЬ В телефонную книгу ВЫБЕРИТЕ 'John Doe' , '555-1212' ИЗ БЕРЕГОВОГО ( ЗНАЧЕНИЯ ( 1 ) ) КАК t ( c ) ОБЪЕДИНЕНИЕ ВСЕ ВЫБЕРИТЕ 'Peter Doe' , '555-2323' ОТ БОКОВОГО ( ЗНАЧЕНИЯ ( 1 ) ) КАК t ( c )
Oracle PL / SQL поддерживает INSERT ALL оператор, где несколько операторов вставки завершаются ВЫБРАТЬ : [1]
INSERT ALL INTO phone_book VALUES ( 'John Doe' , '555-1212' ) INTO phone_book VALUES ( 'Питер Doe' , '555-2323' ) ВЫБРАТЬ * FROM DUAL ;
В Firebird вставка нескольких строк осуществляется следующим образом:
INSERT INTO phone_book ( "name" , "number" ) ВЫБРАТЬ 'John Doe' , '555-1212' ИЗ RDB $ DATABASE UNION ВСЕ ВЫБРАТЬ 'Peter Doe' , '555-2323' FROM RDB $ DATABASE ;
Firebird, однако, ограничивает количество строк, которые можно вставить таким образом, поскольку существует ограничение на количество контекстов, которые можно использовать в одном запросе.
Копирование строк из других таблиц
An Оператор INSERT также может использоваться для извлечения данных из других таблиц, изменения их при необходимости и вставки непосредственно в таблицу. Все это делается в одном операторе SQL, который не требует какой-либо промежуточной обработки в клиентском приложении. Подзапрос используется вместо Пункт VALUES . Подвыбор может содержать соединения, вызовы функций и даже запрашивать ту же таблицу, в которую вставлены данные. Логически выбор оценивается до начала фактической операции вставки. Пример приведен ниже.
INSERT INTO phone_book2 SELECT * FROM phone_book WHERE name IN ( 'John Doe' , 'Peter Doe' )
Вариант необходим, когда некоторые данные из исходной таблицы вставляются в новую таблицу, но не всю запись. (Или когда схемы таблиц не совпадают.)
INSERT INTO phone_book2 ( [ name ], [ phoneNumber ] ) SELECT [ name ], [ phoneNumber ] FROM phone_book WHERE name IN ( 'John Doe' , 'Peter Doe' )
В Оператор SELECT создает (временную) таблицу, и схема этой временной таблицы должна совпадать со схемой таблицы, в которую вставляются данные.
Значения по умолчанию
Можно вставить новую строку без указания каких-либо данных, используя значения по умолчанию для всех столбцов. Однако некоторые базы данных отклоняют оператор, если данные не указаны, например Microsoft SQL Server, и в этом случае Можно использовать ключевое слово DEFAULT .
INSERT INTO phone_book ЗНАЧЕНИЯ ( DEFAULT )
Иногда базы данных также поддерживают для этого альтернативный синтаксис; например, MySQL позволяет опускать Ключевое слово DEFAULT , и T-SQL может использовать ЗНАЧЕНИЯ ПО УМОЛЧАНИЮ вместо ЗНАЧЕНИЯ (ПО УМОЛЧАНИЮ) . В Ключевое слово DEFAULT также может использоваться при обычной вставке для явного заполнения столбца с использованием значения по умолчанию для этого столбца:
INSERT INTO phone_book ЗНАЧЕНИЯ ( DEFAULT , '555-1212' )
Что происходит, когда в столбце не указано значение по умолчанию, зависит от базы данных. Например, MySQL и SQLite заполнят пустое значение (кроме случаев, когда они находятся в строгом режиме), в то время как многие другие базы данных отклонят оператор.
Получение ключа
Разработчики баз данных, которые используют суррогатный ключ в качестве первичного ключа для каждой таблицы, будут время от времени сталкиваться с сценарием, когда им необходимо автоматически извлекать первичный ключ, сгенерированный базой данных, из SQL. Оператор INSERT для использования в других операторах SQL. Большинство систем не поддерживают SQL Операторы INSERT для возврата данных строки. Следовательно, в таких сценариях необходимо реализовать обходной путь. Общие реализации включают:
- Используя специфичную для базы данных хранимую процедуру, которая генерирует суррогатный ключ, выполняет INSERT и, наконец, возвращает сгенерированный ключ. Например, в Microsoft SQL Server ключ извлекается через SCOPE_IDENTITY () , а в SQLite функция называется last_insert_rowid () .
- Использование конкретной базы данных Оператор SELECT для временной таблицы, содержащей последнюю вставленную строку (строки). DB2 реализует эту функцию следующим образом:
ВЫБРАТЬ * FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Питер Doe' , '555-2323' ) ) AS т
- DB2 для z / OS реализует эту возможность следующим образом.
ВЫБРАТЬ EMPNO , HIRETYPE , HIREDATE ИЗ FINAL ТАБЛИЦЫ ( INSERT INTO EMPSAMP ( NAME , ЗАРПЛАТА , DEPTNO , LEVEL ) VALUES ( ' Mary Smith ' , 35 000 . 00 , 11 , ' Associate ' ) );
- Используя Оператор SELECT после Оператор INSERT с функцией для конкретной базы данных, которая возвращает сгенерированный первичный ключ для последней вставленной строки. Например, LAST_INSERT_ID () для MySQL .
- Использование уникального сочетания элементов исходного SQL ВСТАВИТЬ в последующем Оператор SELECT .
- Использование GUID в SQL INSERT и получение его в Оператор SELECT .
- С помощью Предложение OUTPUT в SQL Оператор INSERT для MS-SQL Server 2005 и MS-SQL Server 2008.
- Используя Оператор INSERT с Предложение RETURNING для Oracle .
INSERT INTO phone_book VALUES ( 'Питер Doe' , '555-2323' ) возвращающийся phone_book_id INTO v_pb_id
- Используя Оператор INSERT с Предложение RETURNING для PostgreSQL (начиная с версии 8.2). Возвращенный список идентичен результату ВСТАВИТЬ .
- Firebird имеет тот же синтаксис в операторах языка модификации данных (DSQL); оператор может добавить не более одной строки. [2] В хранимых процедурах, триггерах и исполнительных блоках (PSQL) используется вышеупомянутый синтаксис Oracle. [3]
INSERT INTO phone_book VALUES ( 'Peter Doe' , '555-2323' ) ВОЗВРАЩЕНИЕ phone_book_id
- Firebird имеет тот же синтаксис в операторах языка модификации данных (DSQL); оператор может добавить не более одной строки. [2] В хранимых процедурах, триггерах и исполнительных блоках (PSQL) используется вышеупомянутый синтаксис Oracle. [3]
- С помощью Функция IDENTITY () в H2 возвращает последний вставленный идентификатор.
ВЫБРАТЬ ИДЕНТИЧНОСТЬ ();
Триггеры
Если триггеры определены в таблице, на которой Оператор INSERT работает, эти триггеры оцениваются в контексте операции. Триггеры BEFORE INSERT позволяют изменять значения, которые должны быть вставлены в таблицу. Триггеры AFTER INSERT больше не могут изменять данные, но могут использоваться для инициирования действий с другими таблицами, например, для реализации механизма аудита.
Рекомендации
- ^ «Oracle PL / SQL: ВСТАВИТЬ ВСЕ» . psoug.org . Проверено 2 сентября 2010 .
- ^ «Обновление справочника по языку Firebird 2.5» . Проверено 24 октября 2011 .
- ^ "Словарь языка SQL Firebird" .