Добавить строку в таблицу sql oracle

Обновлено: 03.07.2024

Оператор SQL INSERT предназначен для вставки значений столбцов в таблицу баз данных. В результате его применения в таблице базы данных появляется новая строка.

Оператор INSERT имеет следующий синтаксис:

INSERT INTO ИМЯ_ТАБЛИЦЫ [(ИМЕНА СТОЛБЦОВ)] VALUES (ВСТАВЛЯЕМЫЕ_ЗНАЧЕНИЯ)

Квадратные скобки [], в которые заключен элемент запроса (ИМЕНА СТОЛБЦОВ), означают, что этот элемент является необязательным.

Вставка значений в таблицу с указанием или без указания столбцов

Рассмотрим подробнее применение оператора INSERT с указанием имён столбцов и без указания, остановимся на случаях, когда указывать имена столбцов всё же требуется.

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

А скрипт для создания базы данных "Портал объявлений 1", её таблицы и заполения таблицы данных - в файле по этой ссылке .

Для использующих же MySQL приводим содержание оператора CREATE для создания таблицы:

CREATE TABLE ADS ( Id INT (11) NOT NULL DEFAULT '100', Category varchar (25) DEFAULT 'Some Category', Part varchar (25) DEFAULT 'Some Part', Units INT (5) DEFAULT NULL , Money INT (10) DEFAULT NULL , PRIMARY KEY (Id) )

Пример 1. Итак, есть база данных портала объявлений. Таблица ADS выглядит так:

IdCategoryPartUnitsMoney
1ТранспортАвтомашины11017600
2НедвижимостьКвартиры8918690
3НедвижимостьДачи5711970
4ТранспортМотоциклы13120960
5СтройматериалыДоски687140
6ЭлектротехникаТелевизоры1278255
7ЭлектротехникаХолодильники1378905
8СтройматериалыРегипс11211760
9ДосугКниги966240
10НедвижимостьДома479870
11ДосугМузыка1177605
12ДосугИгры412665

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

INSERT INTO ADS (Id, Category, Part, Units, Money) VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620)

Или без указания имён столбцов:

INSERT INTO ADS VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620) USE adportal1; INSERT INTO ADS (Category, Part, Units, Money) VALUES ('Недвижимость', 'Гаражи', 22, 4620);

В результате выполнения запроса в таблице появится новая строка:

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

В запросе на вставку данных можно список столбцов можно указать не в том порядке, который задан при создании таблицы, и тогда данные следует указывать также в изменённом порядке.

Пример 2. Таким будет запрос на MySQL, в котором порядок следования столбцов изменён:

INSERT INTO ADS (Category, Id, Money, Part, Units) VALUES ('Недвижимость', 13, 4620, 'Гаражи', 22)

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

Если вы используете MS SQL Server, то в запросе не нужно указывать столбец Id и запрос с изменённым порядком следования столбцов будет таким:

USE adportal1; INSERT INTO ADS (Category, Money, Part, Units) VALUES ('Недвижимость', 4620, 'Гаражи', 22)

Вставка значений по умолчанию (DEFAULT) и неопределённых значений (NULL)

Заметим, что при создании таблицы было предусмотрено, что значения столбцов могут иметь значения по умолчанию (DEFAULT). На MySQL для столбца Id можно предусмотреть значение 100. На MS SQL Server со строгим автоматическим приращением идентификатора это не допускается. В остальном же всё одинаково: для стобцов Category и Part значения по умолчанию - соответственно Some Category и Some Part, для столбцов Units и Money - значения NULL. Если в запросе на вставку данных на MySQL некоторые столбцы отсутствуют, то в них будут вставлены значения по умолчанию. На MS SQL в качестве значений нужно указать DEFAULT.

Пример 3. База данных и таблица - те же.

Запрос на MySQL. Вставим новые значения, указывая лишь столбец Id и его значение:

Все столбцы кроме Id получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:

На MS SQL Server такой запрос недопустим.

Поскольку все столбцы могут иметь значения по умолчанию, можно использовать в запросе на вставку данных ключевое слово DEFAULT и не указывать имена столбцов.

Пример 4. Запрос на MySQL. Вставим новые значения, используя ключевое слово DEFAULT и не указывая имён столбцов:

INSERT INTO ADS VALUES ( DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT )

Теперь все столбцы получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:

Запрос на MS SQL Server (без указания столбца Id):

USE adportal1; INSERT INTO ADS VALUES ( DEFAULT , DEFAULT , DEFAULT , DEFAULT )

Вместо многократного использования слова DEFAULT можно использовать конструкцию DEFAULT VALUES, которая есть во многих диалектах SQL. Следует помнить, что в MySQL эта конструкция отсутствует.

Пример 5. Вставим новые значения, используя констукцию DEFAULT VALUES (запрос можно использовать и на MS SQL Server c предваряющей конструкцией USE adportal1):

После выполнения этого запроса новая строка будет содержать следующие данные:

Вставка значений с использованием оператора SET в MySQL

В MySQL дополнительно существует альтернативная конструкция для вставки значений в таблицу с использованием оператора SET. Она похожа на конструкцию оператора UPDATE и имеет следующий синтаксис:

INSERT INTO ИМЯ_ТАБЛИЦЫ SET ИМЯ_СТОЛБЦА_1=ЗНАЧЕНИЕ, ИМЯ_СТОЛБЦА_2=ЗНАЧЕНИЕ, . ИМЯ_СТОЛБЦА_N=ЗНАЧЕНИЕ

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

Пример 6. База данных и таблица - те же, что и в предыдущих примерах.

Вставим в таблицу строку, при этом столбцы Units и Money примут значения по умолчанию:

INSERT INTO ADS SET Category='Недвижимость', Part='Гаражи'

В результате выполнения запроса в таблице появится новая строка:

Использование механизма автоматического приращения при вставке данных

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

В разных диалектах SQL синтаксис автоматического приращения различается. В MySQL используется ключевое слово AUTO_INCREMENT. Таблица с использованием механизма AUTO_INCREMENT в MySQL создаётся так:

CREATE TABLE ADS ( Id INT (11) NOT NULL AUTO_INCREMENT , Category varchar (25) DEFAULT 'Some Category', Part varchar (25) DEFAULT 'Some Part', Units INT (5) DEFAULT NULL , Money INT (10) DEFAULT NULL , PRIMARY KEY (Id) )

В SQL Server используется ключевое слово IDENTITY (N, M), где N - начальное значение столца, M - шаг приращения. Так, указав IDENTITY (1, 1) мы обеспечим начальное значение первичного ключа 1 и приращение на 1 значения при каждой вставке новой строки:

CREATE TABLE ADS ( Id int IDENTITY (1, 1) PRIMARY KEY , Category varchar (25) DEFAULT 'Some Category', Part varchar (25) DEFAULT 'Some Part', Units INT (5) DEFAULT NULL , Money INT (10) DEFAULT NULL )

Вставка нескольких строк в таблицу

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

Для этого применяется либо однострочный, либо многострочный оператор INSERT. Рассмотрим сначала синтаксис однострочного варианта.

Пример 7. Если используется механизм автоматического приращения значений первичного ключа, то на MySQL вставить новые строки в таблицу можно, применив несколько раз оператор INSERT и указав в качестве значений первичного ключа 0 или NULL:

INSERT INTO ADS VALUES ( NULL , 'Электротехника', 'Телевизоры', 127, 8255); INSERT INTO ADS VALUES ( NULL , 'Электротехника', 'Холодильники', 137, 8905); INSERT INTO ADS VALUES ( NULL , 'Стройматериалы', 'Регипс', 112, 11760); INSERT INTO ADS VALUES ( NULL , 'Досуг', 'Книги', 96, 6240);

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

6ЭлектротехникаТелевизоры1278255
7ЭлектротехникаХолодильники1378905
8СтройматериалыРегипс11211760
9ДосугКниги966240

На MS SQL Server нет необходимости указывать значения столбца Id. Аналогичный запрос будет следующим:

USE adportal1; INSERT INTO ADS VALUES ('Электротехника', 'Телевизоры', 127, 8255); INSERT INTO ADS VALUES ('Электротехника', 'Холодильники', 137, 8905); INSERT INTO ADS VALUES ('Стройматериалы', 'Регипс', 112, 11760); INSERT INTO ADS VALUES ('Досуг', 'Книги', 96, 6240);

В MySQL и SQL Server существует многострочный оператор INSERT. Его отличие от однострочного варианта в том, что для вставки нескольких строк он используется один раз, а после ключевого слова VALUES указывается не один, а несколько списков значений добавляемых строк.

Пример 8. Вставим строки с теми же значениями, что и в предыдущем примере, используя многострочный оператор INSERT.

Запрос на MySQL:

INSERT INTO ADS VALUES ( NULL , 'Электротехника', 'Телевизоры', 127, 8255), ( NULL , 'Электротехника', 'Холодильники', 137, 8905), ( NULL , 'Стройматериалы', 'Регипс', 112, 11760), ( NULL , 'Досуг', 'Книги', 96, 6240);

Запрос на MS SQL Server:

USE adportal1; INSERT INTO ADS VALUES ('Электротехника', 'Телевизоры', 127, 8255), ('Электротехника', 'Холодильники', 137, 8905), ('Стройматериалы', 'Регипс', 112, 11760), ('Досуг', 'Книги', 96, 6240);

Результат применения - тот же, что и в предыдущем примере.

Примеры запросов к базе данных "Портал объявлений-1" есть также в уроках об операторах UPDATE, DELETE, HAVING и UNION.

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

  • NUMBER – Тип данных, используемый для хранения числовых данных. В столбцах этого типа не допускаются дефисы, текст или любая другая нечисловая информация.
  • DATE – Тип данных, используемый для хранения информации о датах. Во внутреннем представлении Oracle хранит даты как числа, которые могут быть затем конвертированы в любой формат DATE по вашему желанию. По умолчанию информация в формате DATE представляется в формате DD-MON-YY (например, 25-DEC-79)/
  • VARCHAR2 – Этот тип данных используется для хранения текстовых данных. В столбце типа VARCHAR2 могут храниться любые текстовые символы (включая спецсимволы, числа, буквы, дефисы и тому подобное).
  • CHAR – Этот тип данных используется для хранения текстовых данных. В столбце типа CHAR могут храниться любые текстовые символы (включая спецсимволы, числа, буквы, дефисы и тому подобное). В этом случае, если записанный текст имеет длину, меньшую указанной в определении переменной, он будет дополнен справа пробелами. Следовательно, фамилия SCOTT, если ее поместить в столбец, определенный как CHAR(10), будет дополнена справа пять пробелами.

Главное различие между столбцами CHAR и VARCHAR2 состоит в том, что размер памяти, требующейся для хранения текстовых данных в столбце CHAR, всегда превышает размер памяти для хранения той же информации в столбце VARCHAR2. Это связано с тем, что столбцы CHAR имеют фиксированную длину и всегда содержат одинаковое количество байтов, в то время как столбцы VARCHAR2 имеют переменную длину и содержат ровно столько байтов, сколько вы предоставили для записи.

В Oracle существуют и типы данных для хранения других типов информации; однако их не так много, как в базах данных других производителей. Например, в Oracle отсутствует тип данных для хранения валют. Значение этого типа рассматриваются как простые числа и как таковые их можно хранить в столбцах типа NUMBER.

SQL оператор INSERT используется для вставки записей в существующую таблицу.

Синтаксис этого оператора следующий:

Создадим тестовую таблицу

Давайте создадим таблицу table1 со столбцами a, b, c в нашей MySQL базе данных:

Запрос на вставку строки

Простой запрос, который вставляет строку со столбцами 111, 222 и 333 выглядит так:

Еще один способ сделать то же самое:

Столбцы, которые вы не перечислите заполняются значениями по умолчанию, которые вы предусматриваете при создании таблицы, даже если это просто NULL.

У таблиц обычно есть поле id с первичным ключом (PRIMARY KEY) таблицы. Если этому полю установлено значение AUTOINCREMENT т.е. оно заполняется автоматически, то в таком случае вы не должны его перечислять в списке столбцов оператора INSERT.

Вставка без перечисления столбцов

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

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

Вставка сразу нескольких строк с помощью INSERT INTO

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

Таким образом мы вставили 3 строки в нашу таблицу table1. Их может быть и больше. В MySQL четкого предела нет, однако он все таки существует и зависит от параметра max_allowed_packet который ограничивает размер запроса. Если вы установите SET GLOBAL max_allowed_packet=524288000; то размер запроса будет ограничен 500MB но делайте это в очень крайнем случае. Обычно всегда можно найти решение и разделить 1 большой запрос, на несколько более мелких и вставлять например не больше 1000 строк за один цикл.

Как вставить значение из другой таблицы INSERT INTO . SELECT .

Допустим у нас есть еще одна таблица table2 которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.

Вставляем значения из table1 в таблицу table2:

Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.

Как не рекомендуется делать (без перечисления столбцов):

Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:

Либо еще хуже: значения вставятся не в те столбцы.

Вставка из другой таблицы с условием INSERT INTO . SELECT . WHERE .

А теперь представим, что нам нужно вставить только те строки из table1, у которых столбец "c" равен 333. Тогда наш запрос будет выглядеть так

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

Теперь представим, что у нас в таблице table2 - 4 столбца, а в table1 - 3. При этом четвертый столбец в table2 обязательный. Чтобы выйти из этой ситуации, нужно передать какое-нибудь подходящее значение в этот лишний столбец. У нас чисто абстрактная задача, поэтому давайте передадим туда просто единицу.

Теперь в столбец d у нас записалась единица и проблема решена.

Вставка в определенный раздел INSERT INTO . PARTITION .

Если вам нужно вставить строки в определенный раздел таблицы, то нужно после таблицы указать PARTITION (название раздела), например так:

Вставка в несколько разделов. Первая строка вставляется в раздел p1, а вторая в p2

Вставка строк, некоторые из которых уже существуют в целевой таблице

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

Игнорирование INSERT IGNORE INTO

Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:

Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:

Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться

Вставка с заменой существующих значений REPLACE INTO

REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.

В таком случае наш пример выглядит следующим образом:

Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE

При совпадении ключей, мы можем также заменить некоторые или все поля в строке.

Наш запрос будет выглядеть так:

В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу "c" значения, которое у нас перечислено в VALUES.

Иными словами, если ключ совпадает, то мы просто обновим данные столбца "с" а остальные столбцы трогать не будем.

Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:

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

Выражения для вставляемых значений в VALUES

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

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

Таким образом мы для формирования столбца "c" использовали столбцы "a" и "b".

Приоритет вставки INSERT LOW_PRIORITY / HIGH_PRIORITY

Установление приоритета нужно для решение проблем с конкурентными вставками. При вставках происходит блокировка строк и если 2 INSERT запроса требуют блокировки одних и тех же строк, для своего выполнения, то иногда может потребоваться повысить или понизить приоритет некоторых запросов, по отношению к другим. Это можно сделать указав приоритет LOW_PRIORITY или HIGH_PRIORITY

Оператор 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 , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:

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

В данном случае в столбец type таблицы Product_D будет подставлено значение по умолчанию PC для всех вставляемых строк.

Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN !). Другими словами, если бы столбец type в таблице Product допускал бы NULL -значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Product_D.

Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL . Так если нам требуется вставить несколько строк при помощи одного оператора INSERT , можно написать:

Использование UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, так как в этом случае не будет выполняться проверка для исключения дубликатов.

Следует отметить, что вставка нескольких кортежей с помощью конструктора строк уже реализована в Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server 2008. С учетом этой возможности, последний запрос можно переписать в виде:

Заметим, что MySQL допускает еще одну нестандартную синтаксическую конструкцию, выполняющую вставку строки в таблицу в стиле оператора UPDATE:

Рассмотренный в начале параграфа пример с помощью этого оператора можно переписать так:

Читайте также: