Оператор INSERT
Оператор insert используется для вставки в таблицу базы данных новых строк. Могут использоваться две версии оператора INSERT в зависимости от того, нужно ли вставить в таблицу одну строку или много строк.
.1. Вставка одной строки
Команда INSERT может применяться для вставки отдельных строк в таблицу. В ее простейшей форме используется ключевое слово VALUES для ввода значений столбцов новой строки. Если какое-то значение в столбце не известно, может использоваться ключевое слово NULL для установки этого значения в NULL (для выполнения этого могут также быть указаны две запятые, но использование ключевого слова NULL является более наглядным).
В следующем примере показано, как вставить в таблицу отдельную строку.
SQL> DESC delegates
Name Null? Type
DJDELEGATEJD NOT NULL NUMBER(5) D_LASTNAME NOT NULL VARCHAK2(20) D_FIRSTNAME VARCHAR2 (20)
D_ORGANISAT!ON VARCHAR2 (20)
D_SEX VARCHAR2 (1)
D_DATE_LAST_CONTACTED DATE
SQL> INSERT INTO DELEGATES
- 2 VALUES (1,’Hayre’,’Mandip’,NULL,’M’,NULL);
- 1 row created.
SQL> SELECT * FROM delegates;
D DELEGATE ID D LASTMAME D FIRSTNAME ORGANISATION D SEX DATE LAST CONTACTED 1 Hayre Mandip M
Два столбца этой таблицы были описаны как NOT NULL. Это означает, что при вставке новой строки (или модификации строки) значение столбца должно всегда быть известно; оно не может быть оставлено неопределенным. При попытке вставить строку в таблицу с пустым значением в столбце NOT NULL будет выдано сообщение об ошибке:
SQL> INSERT INTO delegates VALUES (NULL, NULL,
‘Hayre’, ‘Yasmin’,’P’, NULL);
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or
NULL during insert
Это — очень распространенная ошибка. Один из очевидных способов ее исправления состоит в том, чтобы ввести значения для всех столбцов NOT NULL. Другой способ состоит в том, чтобы позволить триггерам базы данных каждый раз при вставке новой строки автоматически вводить значения для таких столбцов.
Операторы модификации данных
Язык манипуляции данными (DML — Data Manipulation Language) помимо оператора SELECT, осуществляющего извлечение информации из базы данных, включает операторы, изменяющие состояние данных. Этими операторами являются:
Оператор INSERT
Оператор INSERT вставляет новые строки в таблицу. При этом значения столбцов могут представлять собой литеральные константы либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора
Как видно из представленного синтаксиса, список столбцов не является обязательным. В том случае, если он отсутствует, список вставляемых значений должен быть полный, т.е. обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку столбцов, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, каждое из этих значений должно быть того же типа (или приводиться к нему), что и тип, определенный для соответствующего столбца в операторе 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 даже, если гарантировано отсутствие строк-дубликатов, т.к. в этом случае не будет выполняться проверка для исключения дубликатов.
Вставка строк в таблицу, содержащую автоинкрементируемое поле
Многие коммерческие продукты допускают использование автоинкрементируемых столбцов в таблицах, т.е. полей, значение которых формируется автоматически при добавлении новых записей. Такие столбцы широко используются в качестве первичных ключей таблицы, т.к. они автоматически обеспечивают уникальность. Типичным примером столбца такого типа является последовательный счетчик, который при вставке строки генерирует значение на единицу большее предыдущего значения (значения, полученного при вставке предыдущей строки).
Ниже приводится пример создания таблицы с автоинкрементируемым столбцом (code) в MS SQL Server.
Автоинкрементируемое поле определяется посредством конструкции IDENTITY (1, 1). При этом первый параметр свойства IDENTITY (1) определяет, с какого значения начнется отсчет, а второй — какой шаг будет использоваться для приращения значения. Таким образом, в нашем примере первая вставленная запись будет иметь в столбце code значение 1, вторая — 2 и т.д.
Поскольку в поле code значение формируется автоматически, оператор
приведет к ошибке, даже если в таблице нет строки со значением в поле code, равным 15. Поэтому для вставки строки в таблицу просто не будем указывать это поле точно так же, как и в случае использования значения по умолчанию, т.е.
В результате выполнения этого оператора в таблицу Printer_Inc будет вставлена информация о модели 3111 цветного лазерного принтера, стоимость которого равна $2599. В поле code окажется значение, которое только случайно может оказаться равным 15. В большинстве случаев этого оказывается достаточно, т.к. значение автоинкрементируемого поля, как правило, не несет никакой информации; главное, чтобы оно было уникальным.
Однако бывают случаи, когда требуется подставить вполне конкретное значение в автоинкрементируемое поле. Например, нужно перенести уже имеющиеся данные во вновь создаваемую структуру; при этом эти данные участвуют в связи «один-ко-многим» со стороны «один». Таким образом, мы не можем допустить тут произвола. С другой стороны, нам не хочется отказываться от автоинкрементируемого поля, т.к. оно упростит обработку данных при последующей эксплуатации базы данных.
Поскольку стандарт языка SQL не предполагает наличия автоинкрементируемых полей, то соответственно не существует и единого подхода. Здесь мы покажем, как это реализуется в MS SQL Server. Оператор
отключает (значение ON) или включает (OFF) использование автоинкремента. Поэтому чтобы вставить строку со значением 15 в поле code, нужно написать
Обратите внимание, что список столбцов в этом случае является обязательным, т.е. мы не можем написать так:
ни, тем более, так
В последнем случае в пропущенный столбец code значение не может быть подставлено автоматически, т.к. автоинкрементирование отключено.
Важно отметить, что если значение 15 окажется максимальным в столбце code,то далее нумерация продолжится со значения 16. Естественно, если включить автоинкрементирование: SET IDENTITY_INSERT Printer_Inc OFF.
Наконец, рассмотрим пример вставки данных из таблицы Product в таблицу Product_Inc, сохранив значения в поле code:
По поводу автоинкрементируемых столбцов следует еще сказать следующее. Пусть последнее значение в поле code было равно 16, после чего строка с этим значением была удалена. Какое значение будет в этом столбце после вставки новой строки? Правильно, 17, т.к. последнее значение счетчика сохраняется, несмотря на удаление строки, его содержащей. Поэтому нумерация значений в результате удаления и добавления строк не будет последовательной. Это является еще одной причиной для вставки строки с заданным (пропущенным) значением в автоинкрементируемом столбце.
Оператором который используется для добавления строки в таблицу является
Для добавления строк в таблицу используется оператор INSERT :
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
Заметьте, что для всех типов данных применяются довольно очевидные форматы. Константы, за исключением простых числовых значений, обычно заключаются в апострофы ( ‘ ), как в данном примере. Тип date на самом деле очень гибок и принимает разные форматы, но в данном введении мы будем придерживаться простого и однозначного.
Тип point требует ввода пары координат, например таким образом:
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
Показанный здесь синтаксис требует, чтобы вы запомнили порядок столбцов. Можно также применить альтернативную запись, перечислив столбцы явно:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
Вы можете перечислить столбцы в другом порядке, если желаете опустить некоторые из них, например, если уровень осадков (столбец prcp) неизвестен:
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
Многие разработчики предпочитают явно перечислять столбцы, а не полагаться на их порядок в таблице.
Пожалуйста, введите все показанные выше команды, чтобы у вас были данные, с которыми можно будет работать дальше.
Вы также можете загрузить большой объём данных из обычных текстовых файлов, применив команду COPY . Обычно это будет быстрее, так как команда COPY оптимизирована для такого применения, хотя и менее гибка, чем INSERT . Например, её можно использовать так:
COPY weather FROM '/home/user/weather.txt';
здесь подразумевается, что данный файл доступен на компьютере, где работает серверный процесс, а не на клиенте, так как указанный файл будет прочитан непосредственно на сервере. Подробнее об этом вы можете узнать в описании команды COPY .
| Пред. | Наверх | След. |
| 2.3. Создание таблицы | Начало | 2.5. Выполнение запроса |
Оператор 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 , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов: