Вставка (SQL)
Оператор SQL INSERT добавляет одну или несколько записей в любую таблицу данных реляционной базы .
Основная форма
[ редактировать ]Операторы вставки имеют следующую форму:
INSERT INTO table (column1 [, column2, column3 ... ]) VALUES (value1 [, value2, value3 ... ])
Количество столбцов и значений должно быть одинаковым. Если столбец не указан, для него используется значение по умолчанию. Значения, указанные (или подразумеваемые) в Оператор INSERT должен удовлетворять всем применимым ограничениям (таким как первичные ключи , ПРОВЕРИТЬ ограничения и ограничения NOT NULL ). Если возникает синтаксическая ошибка или нарушаются какие-либо ограничения, новая строка не добавляется в таблицу, а вместо этого возвращается ошибка.
Пример:
INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');
Также можно использовать сокращение, используя порядок столбцов при создании таблицы. Не обязательно указывать все столбцы в таблице, поскольку любые другие столбцы примут значение по умолчанию или останутся нулевыми :
INSERT INTO table VALUES (value1, [value2, ... ])
Пример вставки данных в два столбца таблицы phone_book и игнорирования любых других столбцов, которые могут находиться после первых двух в таблице.
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', ...]),
...
Эта функция поддерживается IBM 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 , где несколько операторов вставки завершаются ВЫБИРАТЬ : [1]
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, который не требует какой-либо промежуточной обработки в клиентском приложении. Подзапрос используется вместо Пункт ЦЕННОСТИ . Подзапрос может содержать соединения, вызовы функций и даже может запрашивать ту же таблицу, в которую вставляются данные. Логично, что выбор оценивается до начала фактической операции вставки. Пример приведен ниже.
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')
The Инструкция 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 for 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. ВСТАВИТЬ в последующий Оператор ВЫБРАТЬ .
- Использование GUID в SQL Оператор INSERT и получение его в Оператор ВЫБРАТЬ .
- Используя Предложение 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). Возвращаемый список идентичен результату ВСТАВЛЯТЬ .
- Firebird имеет тот же синтаксис в операторах языка модификации данных (DSQL); оператор может добавить не более одной строки. [2] В хранимых процедурах, триггерах и блоках выполнения (PSQL) используется вышеупомянутый синтаксис Oracle. [3]
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) RETURNING phone_book_id
- Firebird имеет тот же синтаксис в операторах языка модификации данных (DSQL); оператор может добавить не более одной строки. [2] В хранимых процедурах, триггерах и блоках выполнения (PSQL) используется вышеупомянутый синтаксис Oracle. [3]
- Используя Функция IDENTITY() в H2 возвращает последний вставленный идентификатор.
SELECT IDENTITY();
Триггеры
[ редактировать ]Если триггеры определены в таблице, в которой Оператор INSERT работает, эти триггеры оцениваются в контексте операции. Триггеры BEFORE INSERT позволяют изменять значения, которые должны быть вставлены в таблицу. Триггеры AFTER INSERT больше не могут изменять данные, но могут использоваться для инициирования действий над другими таблицами, например, для реализации механизма аудита.
Ссылки
[ редактировать ]- ^ «Oracle PL/SQL: ВСТАВИТЬ ВСЕ» . psoug.org . Архивировано из оригинала 16 сентября 2010 г. Проверено 2 сентября 2010 г.
- ^ «Обновление справочника по языку Firebird 2.5» . Проверено 24 октября 2011 г.
- ^ «Словарь языка SQL Firebird» .